SQL_DDL

# 목표

• 데이터를 저장하는 테이블을 생성할 수 있다
• 테이블 구조를 변경할 수 있다
• 제약 조건을 사용하여 데이터 무결성을 보장할 수 있다
• 뷰를 생성하고 활용할 수 있다
• 시퀀스 생성 및 활용할 수 있다
• 인덱스 개념을 이해학 쿼리 성능 향상을 위해 인덱스를 활용할 수 있다

# TABLE 

CREATE TABLE 

COLUMN CONSTARINT

TABLE CONSTRAINT

 

Naming Rule > 30자 이하, 문자로 시작, 키워드 사용불가 등

-- TABLE 만들기
CREATE TABLE ORDERS
(ORDERNO	CHAR(4)
CUSTNO		CHAR(4)
ORDERDATE	DATE DEFAULT SYSDATE
SHIPDATE	DATE
SHIPADDRESS 	DATE
QUANTITY	NUMBER);

# CREATE 생성

데이터 무결성

> DB에 저장되어 있는 데이터가 손상되거나 원래의 의미를 잃지 않는 상태

 

제약 조건 설정 레벨 > COLUMN, TABLE

데이터 무결성 제약조건(NOT NULL, UNIQUE, PRIMARY KEY, REFERENCES TABLE, CHECK)

UNIQUE + NOT NULL 이 합쳐지면 PRIMARY KEY가 된다!

(CHECK를 통해 유효한 값들만 들어올 수 있도록 할 수 있다)

-- NOT NULL(COLUMN 레벨의 제약조건)
CREATE TABLE TEST_TBL(
	ID 	VARCHAR2(20) NOT NULL ,
	PWD	VARCHAR2(20)
) ;

-- UNIQUE ( TABLE 레벨 제약)
CREATE TABLE TEST_TBL(
	ID 	VARCHAR2(20) NOT NULL ,
	PWD	VARCHAR2(20), 
	UNIQUE (ID)
) ;

-- NOT NULL + UNIQUE = PRIMARY KEY
CREATE TABLE TEST_DEPT(
	DEPT_ID 	NUMBER PRIMARY KEY,
	DEPT_NAME 	VARCHAR2(20) NOT NULL
) ;

-- FOREIGN KEY(REFENCES)
CREATE TABLE TEST_EMP(
	EMP_ID		NUMBER PRIMARY KEY ,
	SALARY		NUMBER ,
	DEPT_ID		NUMBER REFERENCES TEST_DEPT(DEPT_ID)
) ;

-- FOREIGN KEY(TABLE 레벨 제약)
CREATE TABLE TEST_EMP(
	EMP_ID		NUMBER PRIMARY KEY ,
	SALARY		NUMBER ,
	DEPT_ID		NUMBER ,
	FOREIGN KEY(DEPT_ID) REFERENCES TEST_DEPT(DEPT_ID)
) ;

-- COMPOSIT PRIMARY KEY , REFERNCES
CREATE 	TABLE	TEST_ORDERS(
	ORDERNO		VARCHAR2(50) PRIMARY KEY ,
	ADDRESS		VARCHAR2(50) ,
	STATUS		VARCHAR2(50) CHECK ( STATUS IN ('상품준비중', '배송중', '배송완료'))
) ;

CREATE	TABLE	TEST_PRODUCTS(
	PNO		VARCHAR2(50) PRIMARY KEY ,
	PNAME		VARCHAR2(50) NOT NULL ,
	COST		NUMBER	CHECK( COST > 100 )
) ;

CREATE	TABLE	TEST_ORDERDETAIL(
	ODERNO		VARCHAR2(50) ,
	PNO		VARCHAR2(50) ,
	QTY		NUMBER ,
	PRIMARY KEY(ORDERNO, PNO)
	FOREIGN KEY(ORDERNO) REFERENCES TEST_ORDERS (ORDERNO) ,
	FOREIGN KEY(PNO) REFERENCES TEST_PRODUCTS (PNO) 
) ;

CF. COMPOSITE KEY를 외래키로 잡을 때 COLUMN 레벨로 잡으면 절대 안된다!

무조건 TABLE 레벨로 두개를 잡아야 한다!

CREATE	TABLE	TEST_ODD(
	NUM		NUMBER PRIMARY KEY ,
	ORDERNO		VARCHAR2(50) ,
	PNO		VARCHAR2(50) ,
	FOREIGN KEY (ORDERNO, PNO) REFERENCES TEST_ORDERDETAIL(ORDERNO, PNO)
);

CF. FOREIGN KEY를 잡을 때, 명시를 하지 않으면 PRIMARY KEY로 잡는다.

만약 PRIMARY KEY가 없다면 UNIQUE 제약조건이 걸린 COLUMN을 잡게 된다.

 

FOREIGN KEY 삭제 제약조건 옵션

FOREIGN KEY (COL) REFERENCES TABLE(COL2) ON DELETE SET NULL
FOREIGN KEY (COL) REFERENCES TABLE(COL2) ON DELETE CASADE

CHECK 는 COL 이름과 연산자를 통해 처리

CREATE 	TABLE	TEST_ORDERS(
	ORDERNO		VARCHAR2(50) PRIMARY KEY ,
	ADDRESS		VARCHAR2(50) ,
	STATUS		VARCHAR2(50) CHECK ( STATUS IN ('상품준비중', '배송중', '배송완료'))
) ;

CF. 변하는 조건으로 CHECK 제약 조건에서 비교를 수행할 수 없다

 

DROP 순서도 중요하다(자식부터 차례로 수행) 

물론 다음과 같은 것도 가능

DROP TABLE TEST_DEPT CASCADE CONSTRAINT;

CASCADE 통해 삭제하면 기존의 관계에 있는 데이터들은 관계만 끊어진다(외래키 적용만 해제)

CREATE TABLE CONSTRAINT_EMP(
	EID		CHAR(3) CONSTRAINT PKEID PRIMARY KEY ,
	ENAME		VARCHRA2(20) CONSTRAINT NENAME NOT NULL ,
	ENO		CHAR(14) CONSTRAINT NENO NOT NULL CONSTRAINT UENO UNIQUE ,
	EMAIL		VARCHAR2(25) CONSTRAINT UEMAIL UNIQUE ,
	PHONE		VARCHAR2(12) ,
	HIRE_DATE 	DATE DEFAULT SYSDATE ,
	JID		CHAR(2) CONSTRAINT FKJID REFERENCES JOB ON DELETE SET NULL ,
	SALARY 		NUMBER ,
	BONUS_PCT 	NUMBER ,
	MARRIAGE 	CHAR(1) DEFAULT 'N' CONSTRAINT CHK CHECK (MARRIAGE IN ('Y', 'N')) ,
	MID		CHAR(3) CONSTRAINT FKMID REFERENCES CONSTRAINT_EMP ON DELETE SET NULL ,
	DID		CHAR(2) ,
	CONSTRAINT	FKDID FOREIGN KEY (DID) REFERENCES ON DELETE CASCADE
) ;

 

CF. INSERT 구문(삽입)

-- 일반적인 INSERT 구문
INSERT INTO TABLE_NAME([ COLUMN , COLUMN , ...]) VALUES(VALUE, VALUE);
INSERT INTO TEST_TBL VALUES('SWYOON', 'SWYOON') ;

-- 다른 테이블로부터 가져온 SUBQUERY 결과로 INSERT 하기
INSERT INTO TABLE_NAME AS SUBQUERY ;

CREATE TABLE도 SUBQUERY로 가져와서 데이터도 카피 가능

CREATE TABLE TABLE_NAME AS SUBQUERY ;

-- SUBQUERY 통한 CREATE TABLE
CREATE TABLE TABLE_SUBQUERY2 ( EID, ENAME, SALARY, DNAME, JTITLE)
AS  	SELECT 	EMP_ID, EMP_NAME, SALARY, DEPT_NAME, JOB_TITLE
	FROM	EMPLOYEE
	LEFT JOIN DEPARTMENT USING (DEPT_ID)
	LEFT JOIN JOB USING (JOB_ID);

제약조건들도 가지고 오게 된다(단, 못가져오는 것들도 존재)

NOT NULL 조건만 자동 반영(기본키, 외래키 등)

자주 사용하는 테이블 JOIN 구문은 CREATE TABLE을 통한 물리적인 공간보다 VIEW를 통한 논리적인 공간이 적절

-- SUBQUERY 통한 CREATE TABLE 제약 조건 걸기 추가
CREATE TABLE TABLE_SUBQUERY3(
	EID PRIMARY KEY ,
	ENAME ,
	SALARY CHECK(SALARY > 2000000) ,
	DNAME ,
	JTITLE NOT NULL
)
AS  	SELECT 	EMP_ID, EMP_NAME, SALARY, DEPT_NAME, JOB_TITLE
	FROM	EMPLOYEE
	LEFT JOIN DEPARTMENT USING (DEPT_ID)
	LEFT JOIN JOB USING (JOB_ID);

가져온 데이터가 만약 제약조건에 맞지 않는다면 에러 발생하므로
애초에 데이터를 가져올 때 제약조건에 맞는 데이터만 WHERE를 통해 가져올 수 있다

# ALTER 변경

ALTER TABLE ADD/MODIFY/DROP

 

COLUMN과 CONSTRAINT에 대해서 두 가지의 ADD, DROP이 존재

COLUMN column_name 또는 (column_name)을 통해 컬럼 지정 가능!

테이블 레벨의 제약조건이기에 COLUMN 레벨의 제약조건 적용 불가!

(ALTER TABLE ADD COLUMN 하면서 제약조건 적용 가능)

-- CREATE 후 ALTER
CREATE TABLE EMP3 AS SELECT * FROM EMPLOYEE;
ALTER TABLE EMP3
ADD	PRIMARY KEY (EMP_ID)
ADD UNIQUE (EMP_NO)
MODIFY HIRE_DATE NOT NULL;

> 참고로 NOT NULL 제약 조건은 COLUMN 레벨 제약조건만 가능

그래서 ALTER TABLE의 ADD가 아니라 MODIFY를 통해 COLUMN에 대해 제약조건을 명시해야 한다

 

TABLE과 COLUMN에 대해 RENAME 을 통해 이름 변경 가능

# DROP 삭제

DROP TABLE table_name [CASCADE CONSTRAINTS] ;

# VIEW 뷰

• 다른 테이블이나 뷰에 포함된 데이터의 맞춤 표현
• 하나 또는 하나 이상의 테이블/ 뷰에 포함된 데이터 부분 집합을 나타내는 논리적인 객체(선택적인 정보만 제공 가능)
• 자체적으로 데이터를 포함하지 않는다
• 베이스 테이블(Base Table): 뷰를 통해 보여지는 데이터를 포함하고 있는 실제 테이블

CF. 특정 데이터를 숨기는 방법으로 VIEW를 활용할 수도 있음

VIEW에 DML 가능할까? > 단일뷰는 INSERT, UPDATE, DELETE 가능하지만 복합테이블로 나온 복합뷰는 불가능

통상적으로 수행하게 되면 원본 테이블에 영향을 주기에 잘 수행하지는 않음

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name [( alias [, alias ...])]
AS Subquery
WITH CHECK OPTION CONSTRAINT
WITH READ ONLY CONSTRAINT

VIEW(논리적 테이블)와 물리적 테이블도 JOIN 가능 -> INLINE VIEW

CREATE OR REPLACE VIEW 에서 주의점은 특정 SUBQUERY를 실행하기 위해서는 별칭이 반드시 필요하다!

# Top N 분석 개념

Top N 분석 : 조건에 맞는 최상위 레코드 N 개를 추출하기(ROWNUM)

원하는 순서대로 정렬, ROWNUM이라는 가상컬럼 통해 순번 부여, 부여된 순번을 이용하여 필요한 수 만큼 식별

-- 부서별 평균급여 보다 많이 받는 사원의 정보
-- Top N 분석
SELECT	ROWNUM, EMP_NAME, SALARY
FROM	(SELECT 	NVL(DEPT_ID, 'N.A') AS "Did",
			ROUND(AVG(SALARY), -3) AS "Davg"
	FROM   		EMPLOYEE
	GROUP BY 	DEPT_ID) INLV
JOIN	EMPLOYEE ON ( NVL(DEPT_ID, 'N/A') = INLV."Did")
WHERE	SALARY > INLV."Davg"
AND 	ROWNUM = 1;

ROWNUM은 특별 숫자를 인식할 수 없다(범위 또는 순차 지정, 단 최상위 1개는 지정 가능)

ORDER BY 해서 정렬한 결과에 대해서 SUBQUERY 다시 ROWNUM 활용하기(INLINE VIEW)

SELECT	ROWNUM, EMP_NAME, SALARY
FROM	(SELECT EMP_NAME, SALARY
	FROM	(SELECT 	NVL(DEPT_ID, 'N.A') AS "Did",
					ROUND(AVG(SALARY), -3) AS "Davg"
			FROM   	EMPLOYEE
			GROUP BY 	DEPT_ID) INLV
	JOIN	EMPLOYEE ON ( NVL(DEPT_ID, 'N/A') = INLV."Did")
	WHERE	SALARY > INLV."Davg"
	ORDER BY 2 DESC)
WHERE	ROWNUM <= 5;

# RANK 함수

지정한 값의 순위 반환 , 지정한 값을 기준으로 순위를 지정

RANK(값) WITHIN GROUP을 통해 그룹에서 순위 매길 수 있다

RANK() OVER (기준) 을 통해서 값의 순위를 매길 수 있다

-- RANK
SELECT	RANK(2300000) WITHIN GROUP (ORDER BY SALARY DESC) AS RANK
FROM	EMPLOYEE;

-- RANK OVER
SELECT	EMP_NAME, SALARY,
	RANK() OVER (ORDER BY SALARY DESC) AS RANK
FROM	EMPLOYEE;

SELECT에 기준 값이 되는 값들을 RANK() OVER (기준)을 통해 순위를 매길 수 있다

# 시퀀스

순차적으로 정수 값을 자동으로 생성하는 객체(0에서부터 1씩 증가하는 값)

CREATE SEQUENCE sequence_name
[INCREMENT BY] [N START WITH N]
[{MAXVALUE N | NOMAXVALUE ] [{ MINVALUE N | NOMINVALUE }]
{[CYCLE | NOCYCLE }] {CACHE N | NOCACHE}];

CREATE SEQUENCE SEQ_EMPID
START WITH	300
INCREMENT BY 5
MAXVALUE 310
NOCYCLE
NOCACHE;

SELECT SEQ_EMPID.NEXTVAL FROM DUAL;

# INDEX 인덱스

테이블의 ROW와 하나씩 대응되는 별도의 객체(독립적인 저장 공간을 보유)

OPTIMIZER가 최적의 실행경로를 설정하는데 중요한 FACTOR가 됨

인덱스를 생성시킨 COLUMN의 VALUE + 그 행의 논리적 주소(ROWID)

성능 향상(인덱스를 이용하여 별도의 정렬 없이 결과 추출 / 물리적인 디스크 IO 감소)

CREATE [UNIQUE] INDEX index_name ON table_name (column_list | function, expr);

-- UNIQUE INDEX
CREATE UNIQUE INDEX IDX_DNM ON DEPARTMENT(DEPT_NAME) ;

-- NONUNIQUE INDEX
CREATE INDEX IDX_JID ON EMPLOYEE(JOB_ID) ;

UNIQUE INDEX는 대부분 기본키이므로 현업에서 사용하는 대부분은 NONUNIQUE INDX

DROP INDEX index_name ;

# 성능향상을 위한 JOIN

NESTED LOOP JOIN / SORT MERGE JOIN / HASH JOIN

오라클의 OPTIMIZER가 최적의 선택을 통해 성능 향상 도모

DRIVING 테이블 / DRIVEN 테이블 등의 선택 과정에 있어 테이블 JOIN을 거는 순서도 상당히 중요

WHERE 조건절에 기술되는 조건에 인덱스를 거는 것이 낫지 않을까?

 

JOIN을 걸 때는 부모부터 거는 것이 아니다

ROW 수가 작은 것 부터 걸자

 

일반적으로, EQUI JOIN은 대부분 HASH JOIN을 선택, 그 외는 NESTED LOOP JOIN