[DB] SQL 문법 정리 - DDL, DML

1. DDL

CREATE

CREATE TABLE EMPLOYEE{
	NAME VARCHAR(15) NOT NULL,
	SSN CHAR(9) NOT NULL,
	SUPER_SSN CHAR(9),
	DNO INT NOT NULL,
	PRIMARY KEY(SSN)
);
CREATE TABLE DEPARTMENT(
	DNAME VARCHAR(15) NOT NULL,
	DNUMBER INT NOT NULL,
	MGR_SSN CHAR(9) NOT NULL,
	PRIMARY KEY(DNUMBER),
	UNIQUE(DNAME)
)
CREATE TABLE DEPT_LOCATION(
	DNUMBER INT NOT NULL,
	DLOCATION VARCHAR(15) NOT NULL,
	PRIMARY KEY(DNUMBER,DLOCATION),
	FOREIGN KEY(DNUMBER) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET NULL
);

ALTER

• 컬럼을 추가, 삭제
• 컬럼정의를 변경
• table 제약조건을 추가 혹은 삭제

-- 외래키 설정
ALTER TABLE DEPARTMENT ADD FOREIGN KEY(MGR_SSN) REFERENCES EMPLOYEE(SSN);

ALTER TALBE COMPANY.EMPLOYEE ADD COLUMN Job VARCHAR(12);
ALTER TABLE COMPANY.DEPARTMENT DROP COLUMN Address CASCADE;

ALTER TABLE COMPANY.EMPLOYEE DROP CONSTRAINT EMPSUPERFK CASCADE;

--DEFAULT VALUES
ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn DROP DEFAULT;
ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn SET DEFAULT '333445555';

• CASCADE : 컬럼을 reference하고있는 모든 제약조건과 뷰를 삭제
• RESTRICT : 컬럼을 reference하고있는 뷰와 제약조건이 없을때만 제거

DROP

• 스키마 요소들을 DROP할/대 사용 (Table, domain, constraint)
• DROP behavior options
  • CASCADE
  • RESTRICT

DROP TABLE DEPT_LOCATION;

DROP SCHEMA COMPANY CASCADE; -- 스키마와 모든 요소들을 삭제
-- RESTRICT : 요소가 없을때만 삭제

DROP TABLE DEPENDENT CASCADE; --릴레이션과 CATALOG에서 그것의 정의를 제거
-- RESTRICT : reference가 없을때만 삭제

 

2. DML

SELECT

SELECT <attribute list> --내가보고싶은 컬럼 선택
FROM <relation list>
[WHERE <condition> ]  -- where절 X -> 모든 relation으 다보겠다
-- 집계 (꺼내온 결과로부터)
[GROUP BY <attribute list> ] -- 결과로부터 group by
[HAVING <condition> ] -- 그룹의 적시 조건(특정그룹만 보고싶다)
[ORDER BY <attribute list> [DESC] ];

Simple (No Join)

-- 이름이 john이고 minit이 B, 성이 smith인 employee의 생일, 주소
SELECT Bdate, Address
FROM EMPLOYEE
WHERE Fname='John' AND Minit='B' AND Lname='Smith';

2-way Join

SELECT E.Fname, E.Lname, D.Address
FROM EMPLOYEE E, DEPARTMENT D
WHERE D.Dname='Research'
	AND D.Dnumber=E.Dno;

• column명이 다르면 약칭 쓰지 않아도 됨

3-way Join

SELECT P.Pnumber, P.Dnum, E.Lname, E.Address, E.Bdate
FROM PROJECT P, DEPARTMENT D, EMPLOYEE E
WHERE P.Dnum=D.Dnumber
AND D.Mgr_ssn= E.Ssn
AND P.Plocation='Stafford';

* (Asterisk)

SELECT * FROM EMPLOYEE WHERE Dno=5;

Table as Sets(집합)

• All (생략 가능)
  • SQL은 자동적으로 중복 튜블을 제거하지 않음

  SELECT (ALL) Salary FROM EMPLOYEE;
  

• DISTINCT
  • 중복을 제거해서 보여줌

  SELECT DISTINCT Salary FROM EMPLOYEE
  

• UNION, EXCEPT, INTERSECT
  • UNION ALL, EXCEPT ALL(MINUS), INTERSECT ALL
  • UNION

(
	SELECT DISTINCT P.Pnumber
	FROM PROJECT P, WORKS_ON W, EMPLOYEE E
	WHERE P.Pnumber=W.Pno AND W.Essn= E.Ssn
		AND E.Lname='Smith')
UNION
(
	SELECT DISTINCT P.Pnumber
	FROM PROJECT P, WORKS_ON W, EMPLOYEE E
	WHERE Dnum=Dnumber AND Mgr_ssn=Ssn
		AND Lname='Smith');

부분 문자열 패턴 매칭

-- % : 0개 이상의 문자
SELECT Fname, Lname
FROM EMPLOYEE
WHERE Address LIKE '%Houston, TX%';

-- _ : 문자개수 지정
SELECT Fname, Lname
FROM EMPLOYEE
WHERE Bdate LIKE '199_____-1_____';

산술 연산

• + - * /

SELECT E.Fname, E.Lname, 1.1 * E.Salary AS Increased_sal
FROM EMPLOYEE E, WORKS_ON W, PROJECT P
WHERE E.Ssn=W,Essn AND W.Pno=P.Pnumber
	AND P.Pname='ProductX';

-- BETWEEN AND
SELECT *
FROM EMPLOYEE
WHERE (Salary BETWEEN 30000 AND 40000)
	AND Dno=5;

ORDER BY

• DESC : 내림차순
• ASC : 오름차순 (default)

SELECT D.Dname, E.Lname, E.Fname, P.Pname
FROM DEPARTMENT D, EMPLOYEE E, WORKS_ON W, PROJECT P
WHERE D.Dnumber=E.Dno AND E.Ssn=W.Essn 
	AND W.Pno=P.Pnumber
ORDER BY D.Dname, E.Lname, E.Fname;

--
ORDER BY D.Dname DESC, E.Lname ASC, E.Fname ASC;

 

INSERT

릴레이션에 튜블 추가

INSERT INTO EMPLOYEE
VALUES ('Richard','K','Marini','653298653',
				'1962-12-30','98 Oak Forest Katy, TX',
				'M', 37000, '653298653',4);

INSERT INTO EMPLOYEE(Fname, Lname, Dno, Ssn)
VALUES ('Richard','Marini',4,'653298653');

쿼리의 결과로 INSERT

CREATE TABLE WORKS_ON_INFO(
	Emp_name VARCHAR(15),
	Proj_name VARCHAR(15),
	Hours_per_week DECIMAL(3,1));
INSERT INTO WORKS_ON_INFO(Emp_name, Proj_name, Hours_per_week) VALUES
	SELECT E.Lname, P.Pname, W.Hours
	FROM PROJECT P, WORKS_ON W, EMPLOYEE E
	WHERE P.Pnumber=W.Pno AND W.Essn=E.Ssn;


CREATE TABLE WORKS_ON_INFO AS
	SELECT E.Lname, P.Pname, W.Hours
	FROM PROJECT P, WORKS_ON W, EMPLOYEE E
	WHERE P.Pnumber=W.Pno AND W.Essn=E.Ssn;

DELETE

• 여러개의 튜플을 삭제

DELETE FROM EMPLOYEE WHERE Lname='Brown';
DELETE FROM EMPLOYEE;

UPDATE

• 여러개의 튜플을 업데이트

UPDATE PROJECT P
SET P.Plocation='Bellaire',
		P.Dnum=5,
WHERE P.Pnumber=10