SQL

database
공개

2024년 9월 27일

DDL (Data Definition Language)

  1. CREATE (database, tables, views, indexes)
  2. ALTER: modify columns / constraints
  3. DROP (database, tables, views, indexes)
  4. TRUNCATE: delete table data while keeping structure.
    MS Access에서는 지원하지 않음 => DELETE FROM table
CREATE TABLE student (
    id INT NOT NULL,
    CourseID INT NOT NULL,
    Name VARCHAR(100) UNIQUE, # unique는 자동으로 index 생성
    Age INT,
    CONSTRAINT STUDENT_PK PRIMARY KEY (id),
    CONSTRAINT 
    COURSE_FK FOREIGN KEY (CourseID) 
    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
  • 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
맨 위로