SQL
database
- SQL is not programming language but a
data sublanguage
- MS Access 89에서는
UNIQUE, CHECK, DEFAULT
,NUMERIC(m,n)
,LIKE
,on update casacade, on delete casacade
제약조건을 지원하지 않음 - MS Access에서는
AutoNumber
를 지원하지 않음. 대신Identity
를 사용 - MS Access에서는 database 생성을 지원하지 않음
DDL (Data Definition Language
)
- CREATE (database, tables, views, indexes)
- ALTER: modify columns / constraints
- DROP (database, tables, views, indexes)
- TRUNCATE: delete table data while keeping structure.
MS Access에서는 지원하지 않음
=>DELETE FROM table
CREATE TABLE student (
id INT NOT NULL,
INT NOT NULL,
CourseID VARCHAR(100) UNIQUE, # unique는 자동으로 index 생성
Name INT,
Age CONSTRAINT STUDENT_PK PRIMARY KEY (id),
CONSTRAINT
FOREIGN KEY (CourseID)
COURSE_FK REFERENCES Course(CourseID)
ON UPDATE CASACADE
ON DELETE NO ACTION
);ALTER TABLE student ADD COLUMN major VARCHAR(100);
ALTER TABLE student ADD CONSTRAINT STUDENT_FK FOREIGN KEY (CourseID) REFERENCES Course(CourseID) ON DELETE CASCADE;
ALTER TABLE student ADD CONSTRAINT AGE_CHECK CHECK (Age > 0);
ALTER TABLE student DROP CONSTRAINT AGE_CHECK;
DROP TABLE student;
TRUNCATE TABLE student;
CREATE VIEW [view name] AS SELECT * FROM student;
DML (Data Manipulation Language
)
INSERT INTO student VALUES (1, 'Alice', 20);
UPDATE student SET age = 21, Name = 'babo' WHERE id = 1;
DELETE FROM student WHERE id = 1;
DQL (Data Query Language)
A query create temporarily a new table.
this allows a query to create a new relation
and feed information to another query as a subquery
SELECT * FROM student;
SELECT name
FROM student
WHERE age > 20
ORDER BY name DESC, age ASC;
SELECT DISTINCT name FROM student;
SELECT name, age FROM student WHERE Age > (SELECT AVG(Age) FROM student);
JOIN
- inner join(equijoin)
- explicit join:
FROM table1 INNER JOIN table2 ON table1.id = table2.id
(MS Access에서는 INNER를 명시해야됨) - implicit join:
FROM table1, table2 WHERE table1.id = table2.id
- explicit join:
- outer join
- left outer join:
FROM table1 LEFT JOIN table2 ON table1.id = table2.id
- right outer join:
FROM table1 RIGHT JOIN table2 ON table1.id = table2.id
- left outer join: