SQLD 2과목 정리 PART3. 관리 구문

SQLD 정리

SQLD 2과목 정리 PART3. 관리 구문

choco2706 2024. 8. 16. 17:44

17. DML

● DML(Data Manipulation Language)

데이터의 삽입(INSERT), 수정(UPDATE), 삭제(DELETE), 병합(MERGE)
저장(COMMIT) 혹은 취소(ROLLBACK) 반드시 필요

● INSERT

테이블을 행에 삽입할 때 사용
한 번에 한 행만 입력 가능(SQL Server, 여러 행 동시 삽입 가능)
하나의 컬럼에는 한 값만 삽입 가능
컬럼별 데이터 타입과 사이즈에 맞게 삽입
INTO 절에 컬럼명을 명시하여 일부 컬럼만 입력 가능. 작성하지 않은 컬럼은 NULL이 입력됨
☞ NOT NULL 컬럼의 경우 오류가 발생
전체 컬럼에 대한 데이터 입력 시 테이블명 뒤의 컬럼명 생략 가능

** 문법

INSERT INTO 테이블명 VALUES(값1, 값2, ... );						-- 전체 컬럼의 값을 입력
INSERT INTO 테이블명(컬럼1, 컬럼2, ... ) VALUES(값1, 값2, ... )	-- 선택한 컬럼만 데이터 입력

예제) 테이블에 데이터 INSERT(한 행씩)

INSERT INTO MERGE_OLD VALUES(1, 'AMERICANO', 1000);
INSERT INTO MERGE_OLD VALUES(2, 'LATTE', 2000);
INSERT INTO MERGE_OLD VALUES(3, 'MILK', 3000);
COMMIT;

☞ 테이블의 각 컬럼별 데이터 타입과 사이즈에 맞게 입력

☞ 문자 컬럼에 숫자값 입력 가능(권장 X)

☞ 숫자 컬럼에 '001'처럼 숫자처럼 생긴 문자값 입력 가능

예제) 서브 쿼리를 사용한 여러 행 INSERT

INSERT INTO EMP3(EMPNO, ENAME, DEPTNO)
SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = 20;

예제) INSERT 시 컬럼 명시 생략으로 인한 오류 발생

INSERT INTO EMP3 VALUES(2, 'B');

☞ 테이블명 뒤에 INSERT 할 컬럼명을 명시하지 않으면 모든 컬럼을 INSERT 시도하는데 입력된 값은 2, 'B' 2개밖에 없기 때문에 에러가 난다.

< 해결 >

INSERT INTO EMP3(EMPNO, ENAME) VALUES(2, 'B');

● UPDATE

데이터를 수정할 때 사용
컬럼 단위 수행
다중 컬럼 수정 기능

** 문법

1. 단일 컬럼 수정

UPDATE 테이블명
SET 수정할컬럼명 = 수정값
WHERE 조건;

☞ WHERE 절로 수정 대상을 선택 가능

예제) AMERICANO의 PRICE를 1500으로 변경

UPDATE MERGE_OLD
SET PRICE = 1500
WHERE NAME = 'AMERICANO';

2. 다중 컬럼 수정

● 방법 1

UPDATE 테이블명
SET 수정컬럼명1 = 수정값1, 수정컬럼명2 = 수정값2, ...
WHERE 조건;

예제) 3번의 NAME을 HOT_MILK로, PRICE를 2500으로 변경

UPDATE MERGE_OLD
SET NAME = 'HOT_MILK', PRICE = 2500
WHERE NO = 3;

● 방법 2

UDPATE 테이블명
SET (수정컬렴명1, 수정컬럼명2, ... ) = (SELECT 수정값1, 수정값2, ... )
WHERE 조건;

※ 서브쿼리의 결과가 수정할 각 행의 값마다 하나씩 전달되어야 함

예제) 서브쿼리를 사용한 여러 컬럼 동시 수정

UPDATE EMP
SET (SAL, COMM) = (SELECT MAX(SAL), MAX(COMM)
				   FROM EMP)
WHERE ENAME = 'SMITH';

SELECT ENAME, SAL, COMM
FROM EMP
WHERE ENAME = 'SMITH';

☞ 서브쿼리 결과가 각 컬럼마다 한 값으로 정의되지 않으면 수행 불가

● DELETE

데이터를 삭제할 때 사용
행 단위 실행

** 문법

DELETE FROM 테이블명
WHERE 조건;

☞ WHERE 절로 삭제할 행 선택 가능

예제) NO가 3인 행 삭제

DELETE FROM EMP
WHERE NO = 3;

☞ 1개의 행이 삭제되었다는 것을 알 수 있음

● MERGE

데이터 병합
참조 테이블과 동일하게 맞추는 작업(참조테이블의 데이터 입력, 참조 테이블의 값으로 수정 등)
☞ INSERT, UPDATE, DELETE 작업을 동시에 수행

** 문법

MERGE INTO 테이블명
USING 참조테이블
ON (연결 조건)			-- 괄호 필수
WHEN MATCHED THEN
	UPDATE				-- UPDATE 테이블명 생략
    SET 수정내용		-- COL1 = 1과 같은 형식
    DELETE (조건)		  -- 괄호 생략 가능
WHEN NOT MATCHED THEN
	INSERT VALUES(값1, 값2, ... );

예제) OLD 테이블을 NEW 테이블과 동일하게 MERGE 문 작성

1. MERGE 할 TABLE 확인

** MERGE_NEW 데이터 확인

SELECT *
FROM MERGE_NEW

** MERGE_OLD 테이블 확인

2. MERGE 문 작성

MERGE INTO MERGE_OLD M1
USING MERGE_NEW M2
ON (M1.NO = M2.NO)
WHEN MATCHED THEN
	UPDATE
    SET M1.PRICE = M2.PRICE
WHEN NOT MATCHED THEN
	INSERT VALUES(M2.NO, M2.NAME, M2.PRICE);

☞ 수정할 테이블명을 MERGE INTO 절에 명시, 참조 테이블을 USING 절 명시

☞ 두 테이블의 데이터를 참조할 참조 조건을 ON 절 명시(괄호 필수)

☞ UPDATE 문에서는 테이블명 명시 X

☞ SET 절의 왼쪽이 수정 테이블, 오른쪽이 참조 테이블 컬럼

☞ INSERT 문에는 INTO 절 없이 VALUES로 참조 컬럼명 전달

18. TCL

● TCL(Transaction Control Language)

트랜잭션 제어어로 COMMIT, ROLLBACK 이 포함됌
DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어
DML 수행 후 트랜잭션을 정상 종료하지 않는 경우 LOCK 발생할 수 있음

※ 잠금(LOCK)

트랜잭션이 수행되는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한
잠금이 걸린 데이터는 잠금을 실행한 트랜잭션만이 접근 및 해제 가능(관리자 권한 계정 제외)

● 트랜잭션

트랜잭션은 데이터베이스의 논리적 연산 단위(하나의 연속적인 업무 단위)
하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함
분할할 수 없는 최소의 단위
ALL OR NOTHING 개념(모두 COMMIT 하거나 ROLLBACK 처리 해야 함)

※ 트랜잭션의 특성

원자성(atimicity) : 트랜잭션 정의된 연산들 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아있어야 함
일관성(consistency) : 트랜잭션 실행 전 데이터베이스 내용이 잘못되어 있지 않다면 트랜잭션 실행 이후에도 데이터베이스 내용에 잘못이 있으면 안됌
고립성(isolation) : 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안됨
지속성(durability) : 트랜잭션이 성공적으로 수행되면 갱신한 데이터베이스 내용이 영구적으로 저장

● COMMIT

입력, 수정, 삭제한 데이터에 이상이 없을 경우 데이터를 저장하는 명령어
한번 COMMIT 을 수행하면 COMMIT 이전에 수행된 DML은 모두 저장되며 되돌릴 수 없음
ORACLE은 DDL시 AUTO COMMIT(23c 버전부터 비활성화 가능)이지만 SQL Server는 AUTO COMMIT 비활성화 설정 가능

● ROLLBACK

테이블 내 입력한 데이터나 수정한 데이터, 삭제한 데이터에 대해 변경을 취소하는 명령어
데이터베이스에 저장되지 않고 최종 COMMIT 지점 / 변경 전 / 특정 SAVEPOINT 지점으로 원복됨
최종 COMMIT 시점 이전까지 ROLLBACK 가능
SAVEPOINT를 설정하여 최종 COMMIT 시점이 아닌, 그 이후의 원하는 시점으로 원복 가능

※ SAVEPOINT

트랜잭션 내에서 롤백을 부분적으로 수행하기 위해 사용되는 지점을 지정하는 데 사용
사용자가 원하는 위치에 원하는 이름으로 설정 가능
ROLLBACK TO savepoint_name 으로 원하는 지점으로 원복 가능(단, COMMIT 이전으로는 원복 불가)

** 문법

SAVEPOINT savepoint_name;

예제) COMMIT과 ROLLBACK 후 최종 데이터 상태

INSERT INTO ROLLBACK_TEST(EMPNO, ENAME, DEPTNO) VALUES(9999, '', 10);
INSERT INTO ROLLBACK_TEST(EMPNO, ENAME, DEPTNO) VALUES(9999, '', 10);
COMMIT;

UPDATE ROLLBACK_TEST SET SAL = 3000 WHERE EMPNO = 9999;
ROLLBACK;

UPDATE ROLLBACK_TEST SET SAL = 3000 WHERE EMPNO = 9998;
COMMIT;

UPDATE ROLLBACK_TEST SET SAL = 4000 WHERE EMPNO = 9999;
SAVEPOINT A;

DELETE ROLLBACK_TEST WHERE EMPNO = 9999;
UPDATE ROLLBACK TEST SET SAL = 9000 WHERE EMPNO = 9998;
ROLLBACK TO A;
COMMIT

☞ SAVEPOINT 이전 수행한 UPDATE는 취소되지 않음

19. DDL

● DDL(Date Definition Language)

데이터 정의어
데이터 구조 정의(객체 생성, 삭제, 변경) 언어
CREATE(객체 생성), ALTER(객체 변경), DROP(객체 삭제), TRUNCATE(데이터 삭제)
AUTO COMMIT(명령어 수행하면 즉시 저장, 원복 불가)

● CREATE

테이블이나 인덱스와 같은 객체를 생성하는 명령어
테이블 생성 시 테이블명, 컬럼명, 컬럼순서, 컬럼크기, 컬럼의 데이터 타입 정의 필수
테이블 생성 시 각 컬럼의 제약조건 및 기본값은 생략 가능
테이블 생성 시 소유자 명시 가능(생략 시 명령어 수행 계정 소유)
숫자 컬럼의 경우 컬럼 사이즈 생략 가능(날짜 컬럼은 사이즈 명시 X)

** 문법 1

CREATE TABLE [소유자,] 테이블명(
컬럼1 데이터 타입 [DEFAULT 기본값] [제약조건],
컬럼2 데이터 타입 [DEFAULT 기본값] [제약조건],
...
);

** 문법 2

CREATE TABLE 테이블명
AS 
SELECT * FROM 복제테이블명;

** 특징

복제 테이블의 컬럼명과 컬럼의 데이터 타입이 복제됨
SELECT 문에서 컬럼 별칭 사용 시 컬럼 별칭 이름으로 생성
CREATE 문에서 컬럼명 변경 가능
NULL 속성도 복제됨
테이블에 있는 제약조건, INDEX 등은 복제되지 않음

● 데이터 타입

데이터 타입	설명
CART(n)	고정형 문자 타입으로 사이즈 전달 필수, 사이즈만큼 확정형 데이터가 입력됨(빈자리수는 공백으로 채움)
VARCHAR2(n)	가변형 문자 타입으로 사이즈 전달 필수, 사이즈보다 작은 문자값이 입력되더라도 입력값 그대로 적용
NUMBER(p, s)	숫자형 타입으로 자리수 생략 가능, 소숫점 자리 제한 시 s 전달(p는 총 자리수)
DATE	날짜 타입으로 사이즈 전달 불가

☞ SQL Server의 경우도 유사, VARCHAR2 -> VARCHAR 사용, NUMBER -> NUMERIC 사용

☞ SQL Server의 경우 문자 타입도 사이즈 생략 가능(생략 시 1)

※ NUMBER(7, 2)의 경우 총 자리수가 7을 초과할 수 없음

INSERT INTO TEST1(B) VALUES(12345.67);		-- 입력 가능
INSERT INTO TEST2(B) VALUES(123457.78);		-- 입력 불가(사이즈 초과)

예제) MERGE_OLD 테이블 만들기

CREATE TABLE MERGE_OLD(
NO NUMBER,
NAME VARCHAR2(10),
PRICE NUMBER
);

예제) EMP 테이블을 복제하여 TEST 테이블 만들기

CREATE TABLE TEST2(A, B)
AS
SELECT EMPNO, ENAME
FROM EMP;

< 결과 >

DESC TEST2;

● ALTER

테이블 구조 변경(컬럼명, 컬럼 데이터 타입, 컬럼 사이즈, default값, 컬럼 삭제, 컬럼 추가, 제약 조건)
컬럼 순서 변경 불가(재생성으로 해결)

1. 컬럼 추가

새로 추가된 컬럼 위치는 맨 마지막(절대 중간 위치에 추가 불가)
컬럼 추가 시 데이터 타입 필수, default 값, 제약 조건을 명시할 수 있음
여러 컬럼 동시 추가 가능(반드시 괄호 사용)

** 문법

ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [DEFAULT] [제약조건];

예제) 컬럼 추가

CREATE TABLE EMP_T1
AS
SELECT *
FROM EMP;

SELECT *
FROM EMP_T1;

※ 동시에 여러 컬럼을 추가할 경우 반드시 괄호와 함께 전달

ALTER TABLE EMP_T1 ADD BIRTHDAY DATE;						-- 정상
ALTER TABLE EMP_T1 ADD (BIRTHDAY2 DATE);					-- 정상
ALTER TABLE EMP_T1 ADD (BIRTHDAY3 DATE, BIRTHDAY4 DATE);	-- 정상
ALTER TABLE EMP_T1 ADD BIRTHDAY3 DATE, BIRTHDAY4 DATE;		-- 에러(여러 컬럼 추가 시 괄호 필수)

※ 컬럼 추가 시 NOT NULL 속성 전달 불가(컬럼 추가 시 모두 NULL인 값을 가지고 추가되므로)

ALTER TABLE EMP_T1 ADD PHONE VARCHAR2(20) NOT NULL;		-- 불가

※ 컬럼 추가 시 DEFAULT 을 선언하면 NOT NULL 속성을 갖는 컬럼 추가 가능

ALTER TABLE EMP_T1
ADD HPAGE VARCHAR2(30)
DEFAULT 'WWW.ITWILL.CO.KR' NOT NULL;

※ 순서 주의(NOT NULL은 DEFAULT값 선언 뒤)

2. 컬럼(속성) 변경

컬럼 사이즈, 데이터 타입, default 값 변경 가능
여러 컬럼 동시 변경 가능

** 문법

ALTER TABLE 테이블명 MODIFY(컬럼명 DEFAULT 값)

☞ 괄호 생략 가능

1) 컬럼 사이즈 번경

컬럼 사이즈 증가는 항상 가능
컬럼 사이즈 축소는 데이터 존재 여부에 따라 제한(데이터가 있는 경우 데이터의 최대 사이즈 만큼 축소 가능)
동시 변경 가능(반드시 괄호 필요)

예제) 여러 컬럼 사이즈 수정

ALTER TABLE TEST MODIFY (COL_A NUMBER(10), COL_B VARCHAR(6));

☞ 최대 길이보다 크거나 같은 사이즈로는 변경 가능

2) 데이터 타입 변경

빈 컬럼일 경우 데이터 타입 변경 가능
CHAR, VARCHAR 타입일 경우 데이터가 서로 있어도 변경 가능

예제) 데이터 타입 변경

ALTER TABLE EMP_T2 MODIFY DEPTNO NUMBER(4);		-- 가능
ALTER TABLE EMP_T2 MODIFY SAL VARCHAR2(4);		-- 에러

3) DEFAULT 값 변경

DEFAULT 값이란 특정 컬럼에 값이 생략될 경우(입력 시 언급되지 않을 경우) 자동으로 부여되는 값
INSERT 시 DEFAULT 값이 선언된 컬럼에 NULL을 직접 입력할 때는 DEFAULT 값이 아닌 NULL이 입력됨
이미 데이터가 존재하는 테이블에 DEFAULT 값 선언 시 기존 데이터 수정 안됨(이후 입력된 데이터부터 적용)
DEFAULT 값 해제 시 DEFAULT 값을 NULL로 선언

예제) DAFAULT 값 변경 및 적용

< DEFAULT 값 수정 >

ALTER TABLE EMP_T2 MODIFY (SAL DEFAULT 3000);

< 새로운 값 입력 >

INSERT INTO EMP_T2 VALUES('PARK', NULL, NULL, SYSDATE);
INSERT INTO EMP_T2(ENAME, DEPTNO, HIREDATE) VALUES('CHOI', NULL, SYSDATE);
COMMIT;

< 데이터 확인 >

☞ PARK 는 SAl 값이 DEFAULT 값이 아닌 NULL 로 입력됨

☞ CHOI 는 SAL 값이 DEFAULT 값으로 입력됨(입력 시 SAL 컬럼 언급 안됐기 때문)

3. 컬럼 이름 변경

항상 사용 가능
동시 여러 컬럼 이름 변경 불가(괄호 전달 불가)
ALTER ... RENAME 명령어로 처리

** 문법

ALTER TABLE TABLE_NAME RENAME COLUMN 기존컬럼명 TO 새컬럼명;

예제) 컬럼 이름 변경

< 컬럼 이름 변경 >

ALTER TABLE EMP_T1 RENAME COLUMN ENAME TO NAME;
DESC EMP_T1;

4. 컬럼 삭제

데이터 존재 여부와 상관 없이 언제나 가능
RECYCLEBIN 에 남지 않음(FLASHBACK 으로 복구 불가)
동시 삭제 불가

예제) COL_A 컬럼의 삭제

ALTER TABLE TEST DROP COLUMN COL_A;		-- 정상

예제 2) 2개 이상 컬럼 삭제 시도 시 에러 발생

ALTER TABLE TEST DROP COLUMN COL_A, COL_B;		-- 에러

☞ 괄호로 묶어서 전달해도 동시 삭제 불가

● DROP

객체(인덱스, 테이블 등) 삭제
DROP 후에는 조회 불가

** 문법

DROP TABLE 테이블명 [PURGE];

※ PURGE 로 테이블 삭제 시 RECYCLEBIN 에서 조회 불가

예제) TEST 테이블 DROP 후 조회 결과

DROP TABLE TEST;

☞ 테이블 또는 뷰가 존재하지 않는다는 에러 발생

● TRUNCATE

구조 남기고 데이터만 즉시 삭제, 즉시 반영(AUTO COMMIT)
RECYCLEBIN 에 남지 않음

** 문법

TRUNCATE TABLE 테이블명;

예제) TRUNCATE 사용하여 데이터 전부 삭제

TRUNCATE TABLE TEST;

☞ 구조(테이블 명, 컬럼 등) 만 남고 데이터는 삭제됨

● DELETE / DROP / TRUNCATE 차이점

DELETE : 데이터 일부 또는 전체 삭제, 롤백 가능
TRUNCATE : 데이터만 전체 삭제 가능(일부 삭제 불가), 즉시 반영(롤백 불가)
DROP : 데이터와 구조를 동시 삭제, 즉시 반영(롤백 불가)

● 제약 조건

데이터 무결성을 위해 각 컬럼에 생성하는 데이터의 제약 장치
테이블 생성 시 정의 가능, 컬럼 추가 시 정의 가능, 이미 생성된 컬럼에 제약 조건만 추가 가능

1. PRIMARY KEY(기본 키)

유일한 식별자(각 행을 구별할 수 있는 식별자 기능)
중복 허용 X, NULL 허용 X => UNIQUE + NOT NULL
특정 컬럼에 PRIMARY KEY 생성하면 NOT NULL 속성 자동 부여(CTAS 로 테이블 복사 시 복사되지 않음)
하나의 테이블에 여러 기본키를 생성할 수 없음
하나의 기본키를 여러 컬럼을 결합하여 생성할 수 있음
PRIMARY KEY 생성 시 자동으로 UNIQUE INDEX 생성

** 문법

1) 테이블 생성 시 제약 조건 생성

CREATE TABLE 테이블명(
컬럼1 데이터타입 [DEFAULT 기본값] [[CONSTRAINT 제약조건명] 제약조건종류],
컬럼2 데이터타입 [DEFAULT 기본값] [[CONSTRAINT 제약조건명] 제약조건종류],
...
[[CONSTRAINT 제약조건명] 제약조건종류]
);

2) 컬럼 추가 시 제약 조건 생성

ALTER TABLE 테이블명
ADD 컬럼명 데이터타입 [DEFAULT 기본값] [[CONSTRAINT 제약조건명] 제약조건종류];

3) 이미 생성된 컬럼에 제약 조건만 추가

ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] 제약조건종류;

4) 제약 조건 삭제

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

예제) 테이블 생성 시 제약 조건 설정(이름 전달 없이)

CREATE TABLE TEST_1(NO NUMBER(10) PRIMARY KEY,
					NAME VARCHAR2(20));

☞ 제약조건 생성 시 이름을 설정하지 않으면 자동으로 부여

CREATE TABLE TEST_2(
NO1 NUMBER,
NO2 NUMBER,
NAME VARCHAR2,
CONSTRAINT TEST2_PK PRIMARY KEY(NO1, NO2)
);

☞ CREATE 문 빝에 제약 조건 이름과 함께 전달 가능

예제) 테이블 생성 시 제약조건 설정(이름과 함께 전달)

CREATE TABLE TEST_2 (NO NUMBER(10) CONSTRAINT TEST_NO_PK PRIMARY KEY,
					 NAME VARCHAR2(20));

예제) 컬럼 추가 시 제약 조건 생성

CREATE TABLE TEST_3 (SUBJECT VARCHAR2(20))
ALTER TABLE TEST_3 ADD NO NUMBER(10) PRIMARY KEY;

예제) 이미 컬럼에 제약조건만 생성

CREATE TABLE TEST_4 (NO NUMBER(10),
					 NAME VARCHAR2(20));
ALTER TABLE TEST_4 ADD CONSTRAINT TEST4_NO_PK PRIMARY KEY(NO);

2. UNIQUE

중복을 허용하지 않음
NULL은 허용
UNIQUE INDEX 자동 생성

예제) UNIQUE KEY 가 생성된 컬럼의 값 입력

< UNIQUE KEY가 생성 >

CREATE TABLE TEST_5(
NO NUMBER,
SUBJECT VARCHAR2(20) UNIQUE
);

< 값의 입력 >

INSERT INTO TEST_5 VALUES(1, 'A');		-- 정상
INSERT INTO TEST_5 VALUES(2, 'A');		-- 에러
INSERT INTO TEST_5 VALUES(2, NULL);		-- 정상
INSERT INTO TEST_5 VALUES(3, NULL);		-- 정상

3. NOT NULL

다른 제약조건과 다르게 컬럼의 특징을 나타냄 => CTAS 로 복제 시 따라감
컬럼 생성 시 NOT NULL 을 선언하지 않으면 Nullable 컬럼으로 생성됨
이미 만들어진 컬럼에 NOT NULL 선언 시 제약 조건 생성이 아닌 컬럼 수정(MODIFY)으로 해결

예제) NOT NULL 선언

ALTER TABLE PROFESSOR_TEST1 ADD NOT NULL(COL12);				-- 불가
ALTER TABLE PROFESSOR_TEST1 MODIFY COL12 NOT NULL;				-- 컬럼 수정으로 NOT NULL 처리
ALTER TABLE PROFESSOR_TEST1 MODIFY COL12
							CONSTRAINT PROFESSOR_COL_12_NN NOT NULL	 -- 제약조건 이름 부여 가능

4. FOREIGN KEY

참조테이블의 참조 컬럼에 있는 데이터를 확인하면서 본 테이블 데이터를 관리할 목적으로 생성
반드시 참조(부모)테이블의 참조 컬럼(REFERENCE KEY)이 사전에 PK 혹은 UNIQUE KEY를 가져야 함

** 문법

CREATE TABLE 테이블명(
컬럼1 데이터타입 [DEFAULT 값] REFERENCE 참조테이블(참조키),
...
);

예제) FOREIGN KEY 테스트

** 테스트 테이블 생성

CREATE TABLE EMP_TEST1 AS SELECT * FROM EMP;
CREATE TABLE DEPT_TEST1 AS SELECT * FROM DEPT;

** 부모 테이블(DEPT_TEST1)에 REFERENCE KEY(참조 대상)에 PK 설정

ALTER TABLE DEPT_TEST1 ADD CONSTRAINT DEPT_TEST1_DEPTNO_PK PRIMARY KEY(DEPTNO);

** 자식 테이블(EMP_TEST1)에 FOREIGN KEY 생성

ALTER TABLE EMP_TEST1 ADD CONSTRAINT EMP_TEST1_DEPTNO_FK
	FOREIGN KEY(DEPTNO) REFERENCE DEPT_TEST1(DEPTNO);

☞ ' CONSTRAINT EMP_TEST1_DEPTNO_FK' 생략 가능

TEST1) 자식 테이블(EMP_TEST1)에서 10번 부서원 삭제 시도

DELETE EMP_TEST1 WHERE DEPTNO = 10;		-- 가능

TEST2) 자식 테이블(EMP_TEST1)에서 20번 부서원 50번으로 변경 시도(불가)

UPDATE EMP_TEST1 SET DEPTNO = 50 WHERE DEPTNO = 20;		-- 에러

☞ 부모 테이블에 50번 부서번호가 정의되어 있지 않아 자식 테이블에 해당 값으로 수정 불가

TEST3) 자식 테이블(EMP_TEST1)에서 50번 부서원(나머지 정보 자유) 입력 시도(불가)

INSERT INTO EMP_TEST1(EMPNO, ENAME, DEPTNO) VALUES(1111, 'A', 50);		-- 에러

☞ 부모 테이블에 50번 부서번호가 정의되어 있지 않아 자식 테이블에 해당 값으로 입력 불가

● FOREIGN KEY 옵션(생성 시 정의, 변경 불가 -> 재생성)

ON DELETE CASCADE : 부모 데이터 삭제 시 자식 데이터 함께 삭제
ON DELETE SET NULL : 부모 데이터 삭제 시 자식 데이터의 참조값은 NULL 로 수정

예제) FOREIGN KEY 옵션 TEST(ON DELETE CASCADE)

< FOREIGN KEY 재생성(ON DELETE CASCADE) >

ALTER TABLE EMP_TEST1 DROP CONSTRAINT EMP_TEST1_DEPTNO_FK;
ALTER TABLE EMP_TEST1 ADD CONSTRAINT EMP_TEST1_DEPTNO_FK
	FOREIGN KEY(DEPTNO) REFERENCES DEPT_TEST1(DEPTNO) ON DELETE CASCADE;

< 부모 데이터 삭제 >

DELETE DEPT_TEST1 WHERE DEPTNO = 10;
SELECT * FROM EMP_TEST1 WHERE DEPTNO = 10;

☞ 부모 데이터 삭제 시, 자식 데이터도 함께 삭제됨

예제) FOREIGN KEY 옵션 TEST(ON DELETE SET NULL)

< FOREIGN KEY 재생성(ON DELETE SET NULL) >

ALTER TABLE EMP_TEST1 DROP CONSTRAINT EMP_TEST3_DEPTNO_FK;
ALTER TABLE EMP_TEST1 ADD CONSTRAINT EMP_TEST3_DEPTNO_FK
	FOREIGN KEY(DEPTNO) REFERENCES DEPT_TEST1(DEPTNO) ON DELETE SET NULL;
SELECT * FROM EMP_TEST1 WHERE JOB = 'MANAGER';

< 부모 데이터 삭제 >

DELETE DEPT_TEST1 WHERE DEPTNO = 30;
SELECT * FROM EMP_TEST1 WHERE JOB = 'MANAGER';

☞ 자식 테이블의 데이터도 함께 삭제되지 않음(NULL로 수정)

5. CHECK

직접적으로 데이터의 값 제한(양수, (1, 2, 3, 4) 중 하나)

예제) EMP_TEST1 테이블의 SAL 값은 0 이상이여야 한다는 CHECK 제약 조건 추가

ALTER TABLE EMP_TEST1 ADD CONSTRAINT EMP_TEST1_SAL_CK CHECK (SAL > 0);

● 기타 오브젝트

1. 뷰(View)

저장공간을 가지지는 않지만 테이블처럼 조회 및 수정할 수 있는 객체
뷰의 종류
- 단순뷰 : 하나의 테이블 조회 뷰(View)
- 복합뷰 : 둘 이상의 테이블 조인 뷰(View)
뷰의 특징
- 뷰는 기본테이블로부터 유도된 테이블이기에 기본 테이블과 같은 형태의 구조를 가지고 있으며, 조작도 기본 테이블과 거의 같음
- 뷰는 가상의 테이블이기에 물리적으로 구현되어있지 않으며 저장공간을 차지하지 않음
- 데이터를 안전하게 보호 가능
- 이미 정의되어 있는 뷰(View)는 다른 뷰(View)의 정의에 기초가 될 수 있음
- 기본 테이블이 삭제되면 그 테이블을 참조하여 만든 뷰 역시 삭제됨
뷰의 장점
- 논리적 독립성을 제공
- 데이터의 접근을 제어함으로써 보안 유지
- 사용자와 데이터 관리 단순화
- 데이터의 다양한 지원 가능

** 문법

CREATE [OR REPLACE] VIEW 뷰이름
AS
조회쿼리;

** 뷰의 삭제

DROP VIEW 뷰명;

예제) 뷰 생성 및 조회

< 생성 >

CREATE VIEW VIEW_EMP_DEPT
AS
SELECT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

☞ EMP 테이블과 DEPT 테이블에서 가져올 데이터를 선택

< 조회 >

SELECT * FROM VIEW_EMP_DEPT

2) 시퀀스(SEQUENCE)

자동으로 연속적인 숫자를 부여해주는 객체

** 문법

CREATE SEQUENCE 시퀀스명
INCREMENT BY			-- 증가값(DEFAULT : 1)
START WITH				-- 시작값(DEFAULT : 1)
MAXVALUE				-- 마지막값(증가 시퀀스), 재사용시 시작값(감소 시퀀스)
MINVALUE				-- 재사용시 시작값(증가 시퀀스), 마지막값(감소 시퀀스)
CYCLE | NOCYCLE			-- 시퀀스 번호 재사용(DEFAULT : NOCYCLE)
CACHE N					-- 캐시값(DEFAULT : 20)
;

3) SYNONYM

테이블 별칭 생성
ex) HR 계정에서 SCOTT.EMP를 EMP로 조회하는 방법

** 문법

CREATE [OR REPLACE] [PUBLIC] SYNONYM 별칭 FOR 테이블명;

☞ OR REPLACE : 기존에 같은 이름으로 시노님이 생성되어 있는 경우 대체

☞ PUBLIC : 시노님을 생성한 유저만 사용 가능한 PRIVATE SYNONYM 의 반대(누구나 사용 가능)

☞ PUBLIC 으로 생성한 시노님은 반드시 PUBLIC 으로 삭제

예제) 시노님 생성 전 후 테이블 조회 비교(HR 계정으로 조회)

< 시노님 생성 >

CREATE PUBLIC SYNONYM EMP FOR SCOTT.EMP;

< 시노님 생성 전 HR 계정에서 EMP 조회 >

SELECT * FROM EMP;

< 시노님 생성 후 HR 계정에서 EMP 조회 >

20. DCL

● DCL(Data Control Language)

데이터 제어어로 객체에 대한 권한을 부여(GRANT)하거나 회수(REVOKE)하는 기능
테이블 소유자는 타계정에 테이블 조회 및 수정 권한 부여 및 회수 가능

● 권한

일반적으로 본인(접속한 계정) 소유가 아닌 테이블은 원칙적으로 조회 불가(권한 통제)
업무적으로 필요 시 테이블 소유자가 아닌 계정에 테이블 조회, 수정 권한 부여 가능

※ 권한 종류

오브젝트 권한
- 테이블에 대한 권한 제어
  ex) 특정 테이블에 대한 SELECT, INSERT, UPDATE, DELETE, MERGE 권한
- 테이블 소유자는 타계정에 소유 테이블에 대한 조회 및 수정 권한 부여 및 회수 가능
시스템 권한
- 시스템 작업(테이블 생성 등)을 제어
  ex) 테이블 생성 권한, 인덱스 삭제 권한
- 관리자 권한만 권한 부여 및 회수 가능

● GRANT

권한 부여 시 반드시 테이블 소유자나 관리자 계정(SYS, SYSTEM)으로 접속하여 권한을 부여하여야 함
동시에 여러 유저에 대한 권한 부여 가능
동시 여러 권한 부여 가능
동시 여러 객체 권한 부여 불가

** 문법

GRANT 권한 ON 테이불명 TO 유저;

예제) 오브젝트 권한 부여(PROFESSOR 소유자 실행)

GRANT SELECT ON PROFESSOR TO HR;					-- 가능
GRANT SELECT ON PROFESSOR TO HR, BI;				-- 가능
GRANT SELECT, UPDATE, DELETE ON PROFESSOR TO HR;	-- 가능
GRANT SELECT ON PROFESSOR, DEPT2 TO HR;				-- 에러

예제) 시스템 권한 부여(관리자 권한으로 실행)

GRANT CREATE TABLE TO HR;					-- 가능
GRANT CREATE TABLE TO HR, BI;				-- 가능
GRANT CREATE TABLE, DROP ANY TABLE TO HR;	-- 가능

● REVOKE

동시 여러 권한 회수 가능
이미 회수된 권한 재회수 불가
동시 여러 유저로부터 권한 회수 가능

** 문법

REVOKE 권한 ON 테이블명 FROM 유저;

예제) 오브젝트 권한 회수

REVOKE SELECT, UPDATE, INSERT ON PROFESSOR FROM HR	-- 가능
REVOKE SELECT ON PROFESSOR FROM HR, BI;				-- 가능
REVOKE SELECT ON PROFESSOR FROM HR, BI;				-- 에러(이미 회수된 권한 재회수 불가)

● 롤(ROLE)

권한의 묶음(생성 가능한 객체)
SYSTEM 계정에서 ROLE 생성 가능

** 문법

CREATE ROLE 롤이름;

예제) 롤(ROLE)

< 생성 >

CREATE ROLE ROLE_SEL;

< 롤에 권한 담기 >

GRANT SELECT ON EMP TO ROLE_SEL;
GRANT SELECT ON STUDENT TO ROLE_SEL;
GRANT SELECT ON DEPT TO ROLE_SEL;
GRANT SELECT ON DEPARTMENT TO ROLE_SEL;

< 롤 부여 >

GRANT ROLE_SEL TO HR;

< HR 계정에서 수행 >

SELECT * FROM SCOTT.DEPT;

< 롤에서 권한 빼기 >

REVOKE SELECT ON DEPARTMENT FROM ROLE_SEL;

< 권한 회수 후 HR 계정에서 조회 >

SELECT * FROM SCOTT.DEPARTMENT;

☞ 권한이 불충분하다는 에러

☞ ROLE 에서 회수된 권한은 즉시 반영되므로 다시 ROLE 을 부여할 필요가 없음

< 롤을 통해 부여한 권한 직접 회수(SCOTT 에서 실행) >

REVOKE SELECT ON STUDENT FROM HR;

☞ ROLE 을 통해 부여한 권한은 직접 회수 불가

☞ ROLE 을 통한 회수만 가능

● 권한부여 옵션(중간 관리자의 권한)

WITH GRANT OPTION
- WITH GRANT OPTION 으로 받은 오브젝트 권한을 다른 사용자에게 부여할 수 있음
- 중간관리자(WITH GRANT OPTION 으로 권한을 부여받은 자)가 부여한 권한은 중간 관리자만 회수할 수 있음
- 중간관리자에게 부여된 권한 회수 시 제 3자에게 부여된 권한도 함께 회수됨
WITH ADMIN OPTION
- WITH ADMIN OPTION 을 통해 부여받은 시스템 권한 / 롤 권한을 다른 사용자에게 부여할 수 있음
- 중간 관리자를 거치지 않고 직접 회수 가능
- 중간 관리자 권한 회수 시 제 3자에게 부여된 권한을 함께 회수 하지 않음

예제) WITH GRANT OPTION / WITH ADMIN OPTION TEST

1. 권한 부여

-- SYS 계정 수행)
GRANT SELECT ON SCOTT.ROLLBACK_TEST TO HDATALAB WITH GRANT OPTION;
GRANT SELECT ANY TABLE TO HDATALAB WITH ADMIN OPTION;
GRANT ALTER ANY TABLE TO HDATALAB WITH ADMIN OPTION;

-- HDATALAB 계정 수행)
GRANT SELECT ON SCOTT.ROLLBACK_TEST TO PARK;
GRANT CREATE ANY TABLE TO PARK;
GRANT ALTER ANY TABLE TO PARK;

2. 권한 회수 시도

-- SYS 계정 수행)
REVOKE SELECT ON SCOTT.ROLLBACK_TEST FROM PARK;		-- 직접 회수 불가

☞ 중간 관리자를 통해 부여한 제 3계정의 권한은 관리자가 직접 회수 불가

-- SYS 계정 수행)
REVOKE SELECT ON SCOTT.ROLLBACK_TEST FROM PARK;		-- 직접 회수 불가
REVOKE SELECT ON SCOTT.ROLLBACK_TEST FROM HDATALAB;	-- 중간관리자회수

☞ 대신 중간 관리제에게 부여된 권한 회수(회수 시 제 3계정에 부여된 권한도 함께 회수)

-- PARK 계정 수행)
CREATE TABLE CREATE_TEST(COL1 NUMBER);		-- 가능

☞ 중간 관리자의 시스템 권한을 회수하더라도 중간 관리자가 제 3의 계정에 부여한 권한은 회수되지 않음

-> 테이블 생성이 가능함