09_DDL(ALTER, DROP)
-- DDL(Data Definition Language)
-- 객체를 만들고(CREATE), 바꾸고(ALTER), 삭제(DROP)하는 데이터 정의 언어
/*
* ALTER(바꾸다, 수정하다, 변조하다)
*
* - 테이블에서 수정할 수 있는 것
*
* 1) 제약 조건(추가/삭제) -- 수정구문이 따로 존재하지 않음
* 2) 컬럼(추가/수정/삭제)
* 3) 이름 변경(테이블명, 제약조건명, 컬럼명)
*
* */
-----------------------------------------------
-- 1. 제약조건(추가/삭제)
-- [작성법]
-- 1) 추가 : ALTER TABLE 테이블명
-- ADD [CONSTRAINT 제약조건명] 제약조건(컬럼명)
-- [REFERENCES 테이블명[(컬럼명)]] <- FK 인 경우 추가
-- 2) 삭제 : ALTER TABLE 테이블명
-- DROP CONSTRAINT 제약조건명;
-- * 수정은 별도 존재하지 않음! --> 삭제 후 추가를 이용해서 수정 *
-- DEPARTMENT 테이블 복사 (컬럼명, 데이터타입, NOT NULL 만 복사)
CREATE TABLE DEPT_COPY
AS SELECT * FROM DEPARTMENT;
SELECT * FROM DEPT_COPY;
-- DEPT_COPY의 DEPT_TITLE 컬럼에 UNIQUE 추가
ALTER TABLE DEPT_COPY
ADD CONSTRAINT DEPT_COPY_TITLE_U UNIQUE(DEPT_TITLE);
-- DEPT_COPY의 DEPT_TITLE 컬럼에 UNIQUE 삭제
ALTER TABLE DEPT_COPY
DROP CONSTRAINT DEPT_COPY_TITLE_U;
-- *** DEPT_COPY의 DEPT_TITLE 컬럼에 NOT NULL 제약조건 추가/삭제 ***
ALTER TABLE DEPT_COPY
ADD CONSTRAINT DEPT_COPY_TITLE_NN NOT NULL(DEPT_TITLE);
--> NOT NULL 제약조건은 새로운 제약조건을 추가하는 것이 아닌
-- 컬럼 자체에 NULL 허용/비허용을 제어하는 성질 변경의 형태로 인식됨.
-- MODIFY (수정하다) 구문을 사용해서 NULL 제어
ALTER TABLE DEPT_COPY
MODIFY DEPT_TITLE NOT NULL; -- DEPT_TITLE 컬럼을 NOT NULL 로 수정
ALTER TABLE DEPT_COPY
MODIFY DEPT_TITLE NULL;
----------------------------------------------------------------------------------
-- 2. 컬럼(추가/수정/삭제)
-- 컬럼 추가
-- ALTER TABLE 테이블명 ADD(컬럼명 데이터타입 [DEFAULT '값']);
-- 컬럼 수정
-- ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입; -> 데이터 타입 변경
-- ALTER TABLE 테이블명 MODIFY 컬럼명 DEFAULT '값'; -> DEFAULT 값 변경
-- ALTER TABLE 테이블명 MODIFY 컬럼명 NULL / NOT NULL; -> NULL 여부 변경
-- 컬럼 삭제
-- ALTER TABLE 테이블명 DROP(삭제할컬럼명);
-- ALTER TABLE 테이블명 DROP COLUMN 삭제할컬럼명;
--> * 컬럼 삭제 시 주의 사항 *
--> 테이블이란? 행과 열로 이루어진 DB의 가장 기본 객체
-- 테이블은 최소 1개 이상의 컬럼이 존재해야 되기 때문에
-- 모든 컬럼을 다 삭제할 수는 없다!
SELECT * FROM DEPT_COPY;
-- CNAME 컬럼 추가
ALTER TABLE DEPT_COPY ADD(CNAME VARCHAR2(30));
SELECT * FROM DEPT_COPY;
-- LNAME 컬럼추가(기본값 '한국')
ALTER TABLE DEPT_COPY ADD(LNAME VARCHAR2(30) DEFAULT '한국');
SELECT * FROM DEPT_COPY;
--> 컬럼이 생성되면서 DEFAULT 값이 자동 삽입됨.
-- D10 개발1팀 추가
INSERT INTO DEPT_COPY
VALUES('D10', '개발1팀', 'L1', DEFAULT, DEFAULT);
-- ORA-12899: "KH"."DEPT_COPY"."DEPT_ID" 열에 대한 값이 너무 큼(실제: 3, 최대값: 2)
-- DEPT_ID 컬럼 수정
ALTER TABLE DEPT_COPY MODIFY DEPT_ID VARCHAR2(5);
--> 다시 위 INSERT 실행 --> 성공!
SELECT * FROM DEPT_COPY;
-- LNAME 의 기본값을 'KOREA' 로 수정
ALTER TABLE DEPT_COPY MODIFY LNAME DEFAULT 'KOREA';
SELECT * FROM DEPT_COPY;
--> 기본값을 변경했다고 해서 기존 데이터가 변하지는 않는다.
-- LNAME '한국' -> 'KOREA'로 변경
UPDATE DEPT_COPY SET
LNAME = DEFAULT
WHERE LNAME = '한국';
SELECT * FROM DEPT_COPY;
COMMIT;
-- 모든 컬럼 삭제
ALTER TABLE DEPT_COPY DROP(LNAME);
ALTER TABLE DEPT_COPY DROP COLUMN CNAME;
ALTER TABLE DEPT_COPY DROP(LOCATION_ID);
ALTER TABLE DEPT_COPY DROP(DEPT_TITLE);
ALTER TABLE DEPT_COPY DROP(DEPT_ID);
-- ORA-12983: 테이블에 모든 열들을 삭제할 수 없습니다
SELECT * FROM DEPT_COPY;
-- 테이블 삭제
DROP TABLE DEPT_COPY;
-- DEPARTMENT 테이블 복사해서 DEPT_COPY 생성
CREATE TABLE DEPT_COPY
AS SELECT * FROM DEPARTMENT;
--> 컬럼명, 데이터타입, NOT NULL 여부만 복사
-- DEPT_COPY 테이블에 PK 추가 (컬럼 : DEPT_ID, 제약조건명 : D_COPY_PK)
ALTER TABLE DEPT_COPY ADD CONSTRAINT D_COPY_PK PRIMARY KEY(DEPT_ID);
-- 3. 이름변경(컬럼, 제약조건, 테이블)
-- 1) 컬럼명 변경 (DEPT_TITLE -> DEPT_NAME)
ALTER TABLE DEPT_COPY RENAME COLUMN DEPT_TITLE TO DEPT_NAME;
SELECT * FROM DEPT_COPY;
-- 2) 제약조건명 변경(D_COPY_PK -> DEPT_COPY_PK)
ALTER TABLE DEPT_COPY RENAME CONSTRAINT D_COPY_PK TO DEPT_COPY_PK;
-- 3) 테이블명 변경 (DEPT_COPY -> DCOPY)
ALTER TABLE DEPT_COPY RENAME TO DCOPY;
SELECT * FROM DEPT_COPY;
SELECT * FROM DCOPY;
---------------------------------------------------------------------
-- 4. 테이블 삭제
-- DROP TABLE 테이블명 [CASCADE CONSTRAINTS];
-- 1) 관계가 형성되지 않은 테이블 삭제
DROP TABLE DCOPY;
-- 2) 관계가 형성된 테이블 삭제
CREATE TABLE TB1(
TB1_PK NUMBER PRIMARY KEY,
TB1_COL NUMBER
);
CREATE TABLE TB2(
TB2_PK NUMBER PRIMARY KEY,
TB2_COL NUMBER REFERENCES TB1
);
-- TB1에 샘플 데이터 삽입
INSERT INTO TB1 VALUES(1, 100);
INSERT INTO TB1 VALUES(2, 200);
INSERT INTO TB1 VALUES(3, 300);
COMMIT;
-- TB2에 샘플 데이터 삽입
INSERT INTO TB2 VALUES(11, 1);
INSERT INTO TB2 VALUES(22, 2);
INSERT INTO TB2 VALUES(33, 3);
-- TB1 삭제
DROP TABLE TB1;
-- ORA-02449: 외래 키에 의해 참조되는 고유/기본 키가 테이블에 있습니다
--> 해결 방법
-- 1) 자식, 부모 테이블 순서로 삭제
-- 2) ALTER 를 이용해서 FK 제약조건을 삭제후 TB1 삭제
-- 3) DROP TABLE 삭제옵션 CASCADE CONSTRAINTS 사용
--> 삭제하려는 테이블과 연결된 FK 제약조건을 모두 삭제
DROP TABLE TB1 CASCADE CONSTRAINTS;
--> 삭제 성공
--------------------------------------------------------------------------------
/* DDL 주의사항 */
-- 1) DDL 은 COMMIT/ROLLBACK 이 되지 않는다.
--> ALTER, DROP 을 신중하게 진행해야함.
-- 2) DDL과 DML 구문을 섞어서 수행하면 안된다! (중요)
--> DDL 수행중에 DML 수행을 하면 DML 이 COMMIT 을 수행하지 않아도, 자동 COMMIT;
SELECT * FROM TB2;
COMMIT;
INSERT INTO TB2 VALUES(14, 4);
INSERT INTO TB2 VALUES(15, 5);
SELECT * FROM TB2;
-- 컬럼명 변경 DDL
ALTER TABLE TB2 RENAME COLUMN TB2_COL TO TB2_COLUMN;
ROLLBACK;
SELECT * FROM TB2;10_1_VIEW
/* VIEW
- SELECT문의 실행 결과(RESULT SET)를 저장하는 객체
- 논리적 '가상 테이블'
-> 테이블 모양을 하고는 있지만
실제로 값을 저장하고 있지는 않음.
** VIEW 사용 목적 **
1) 복잡한 SELECT문을 쉽게 '재사용'하기 위해서 사용.
2) 테이블의 진짜 모습을 감출 수 있어 '보안'상 유리함.
***** VIEW 사용 시 주의사항 *****
1) 가상의 테이블(실제 테이블 X) -> ALTER 구문 사용 불가
2) VIEW를 이용한 DML(INSERT/UPDATE/DELETE)가
가능한 경우도 있지만
많은 제약+위험성이 따르기 때문에 SELECT 용도로 사용 하는 것을 권장.
[VIEW 생성 방법]
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름 [(alias[,alias]...)]
AS subquery
[WITH CHECK OPTION]
[WITH READ ONLY];
-- 1) OR REPLACE 옵션 : 기존에 동일한 뷰 이름이 존재하는 경우 덮어쓰고,
-- 존재하지 않으면 새로 생성. (중요)
-- 2) FORCE / NOFORCE 옵션
-- FORCE : 서브쿼리에 사용된 테이블이 존재하지 않아도 뷰 생성
-- NOFORCE : 서브쿼리에 사용된 테이블이 존재해야만 뷰 생성(기본값)
-- 3) WITH CHECK OPTION 옵션 : 옵션을 설정한 컬럼의 값을 수정 불가능하게 함.
-- 4) WITH READ ONLY 옵션 : 뷰에 대해 조회만 가능(DML 수행 불가) (중요)
*/
-- 사번, 이름, 부서명, 직급명 조회 결과를 저장하는 VIEW 생성
CREATE OR REPLACE VIEW V_EMP
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE);
-- ORA-01031: 권한이 불충분합니다
--> 1) SYS 관리자 계정 접속
--> 2) ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;
--> 3) GRANT CREATE VIEW TO kh;
--> VIEW 생성 구문 다시 실행
ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;
GRANT CREATE VIEW TO kh;
-- 생성된 view 통해서 조회
SELECT * FROM V_EMP;
-----------------------------------------------------------------------------
-- OR REPLACE 확인 + 별칭
CREATE OR REPLACE VIEW V_EMP
AS SELECT EMP_ID 사번, EMP_NAME 이름, DEPT_TITLE 부서명, JOB_NAME 직급명
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE);
-- ORA-00955: 기존의 객체가 이름을 사용하고 있습니다.
-- OR REPLACE 추가 후 실행 -> 성공
--> 기존 V_EMP를 새로운 VIEW 덮어쓰기
SELECT * FROM V_EMP;10_3_SEQUENCE
/* SEQUENCE(순서, 연속, 수열)
- 순차적 번호 자동 발생기 역할의 객체
-> SEQUENCE 객체를 생성해서 호출하게되면
지정된 범위 내에서 일정한 간격으로 증가하는 숫자가
순차적으로 출력됨.
EX) 1부터 10까지 1씩 증가하고 반복하는 시퀀스 객체
1 2 3 4 5 6 7 8 9 10 1 2 3 4 5 6 7 8 9 10
** SEQUENCE는 주로 'PK역할의 컬럼'에 삽입되는 값을 만드는 용도로 사용 **
--> 인위적 주식별자
[작성법]
CREATE SEQUENCE 시퀀스이름
[STRAT WITH 숫자] -- 처음 발생시킬 시작값 지정, 생략하면 자동 1이 기본
[INCREMENT BY 숫자] -- 다음 값에 대한 증가치, 생략하면 자동 1이 기본
[MAXVALUE 숫자 | NOMAXVALUE] -- 발생시킬 최대값 지정 (10의 27승 -1)
[MINVALUE 숫자 | NOMINVALUE] -- 최소값 지정 (-10의 26승)
[CYCLE | NOCYCLE] -- 값 순환 여부 지정
[CACHE 바이트크기 | NOCACHE] -- 캐쉬메모리 기본값은 20바이트, 최소값은 2바이트
-- 시퀀스의 캐시 메모리는 할당된 크기만큼 미리 다음 값들을 생성해 저장해둠
-- --> 시퀀스 호출 시 미리 저장되어진 값들을 가져와 반환하므로
-- 매번 시퀀스를 생성해서 반환하는 것보다 DB속도가 향상됨.
** 시퀀스 사용 방법 **
1) 시퀀스명.NEXTVAL : '다음 시퀀스' 번호를 얻어옴. (INCREMENT BY만큼 증가된 값)
단, 시퀀스 생성 후 첫 호출인 경우 START WITH의 값을 얻어옴.
2) 시퀀스명.CURRVAL : '현재 시퀀스' 번호 얻어옴.
단, 시퀀스 생성 후 NEXTVAL 호출 없이 CURRVAL를 호출하면 오류 발생.
*/
CREATE SEQUENCE SEQ_TEST;
-- * CURRVAL 주의 사항 *
--> CURRVAL는 마지막 NEXTVAL 호출값을 다시 보여주는 기능!
--> NEXTVAL를 먼저 호출해야 CURRVAL 호출이 가능!
SELECT SEQ_TEST.CURRVAL FROM DUAL;
--ORA-08002: 시퀀스 SEQ_TEST.CURRVAL은 이 세션에서는 정의 되어 있지 않습니다
SELECT SEQ_TEST.NEXTVAL FROM DUAL; -- 1
SELECT SEQ_TEST.CURRVAL FROM DUAL; -- 1
SELECT SEQ_TEST.NEXTVAL FROM DUAL; -- 2
SELECT SEQ_TEST.NEXTVAL FROM DUAL; -- 3
SELECT SEQ_TEST.NEXTVAL FROM DUAL; -- 4
SELECT SEQ_TEST.NEXTVAL FROM DUAL; -- 5
SELECT SEQ_TEST.CURRVAL FROM DUAL; -- 5
-------------------------------------------------------------------------------
-- 실제 사용 예시
CREATE TABLE EMP_TEMP
AS SELECT EMP_ID, EMP_NAME FROM EMPLOYEE;
SELECT * FROM EMP_TEMP;
-- 223 번부터 10씩 증가하는 시퀀스 생성
CREATE SEQUENCE SEQ_TEMP
START WITH 223 -- 223 번 시작
INCREMENT BY 10 -- 10씩 증가
NOCYCLE -- 반복 X (기본값)
NOCACHE; -- 캐시 X (기본 20)
-- EMP_TEMP 테이블에 사원 정보 삽입
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL , '홍길동'); -- 223
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL , '고길동'); -- 233
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL , '김길동'); -- 243
SELECT * FROM EMP_TEMP
ORDER BY EMP_ID DESC;
---------------------------------------------------
-- SEQUENCE 수정
/*
ALTER SEQUENCE 시퀀스이름
[INCREMENT BY 숫자] -- 다음 값에 대한 증가치, 생략하면 자동 1이 기본
[MAXVALUE 숫자 | NOMAXVALUE] -- 발생시킬 최대값 지정 (10의 27승 -1)
[MINVALUE 숫자 | NOMINVALUE] -- 최소값 지정 (-10의 26승)
[CYCLE | NOCYCLE] -- 값 순환 여부 지정
[CACHE 바이트크기 | NOCACHE] -- 캐쉬메모리 기본값은 20바이트, 최소값은 2바이트
*/
-- SEQ_TEMP를 1씩 증가하는 형태로 변경
ALTER SEQUENCE SEQ_TEMP
INCREMENT BY 1;
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL , '이길동'); -- 244
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL , '박길동'); -- 245
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL , '최길동'); -- 246
SELECT * FROM EMP_TEMP
ORDER BY EMP_ID DESC;
-- 테이블(EMP_TEMP), 뷰(V_EMP), 시퀀스(SEQ_TEMP) 삭제
DROP TABLE EMP_TEMP;
DROP VIEW V_EMP;
DROP SEQUENCE SEQ_TEMP;10_2_INDEX
/* INDEX(색인) (= 목차)
- SQL 명령문 중 'SELECT의 처리 속도를 향상' 시키기 위해
컬럼에 대해서 생성하는 객체
- 인덱스 내부 구조는 B* 트리 형식으로 되어있음.
* 인덱스 장점
- 이진 트리 형식으로 구성되어 있어 자동 정렬 및 검색 속도가 빠름.
- 조회 시 전체 테이블 내용을 조회하는 것이 아닌
인덱스가 지정된 컬럼만을 이용해서 조회하기 때문에
시스템 부하가 낮아져 전체적인 성능이 향상된다.
* 인덱스 단점
- 데이터 변경(INSERT, UPDATE, DELETE) 작업 빈번한 경우 오히려 성능이 저하되는 문제가 발생.
- 인덱스도 하나의 객체이다보니 이를 저장하기 위한 별도 공간.
- 인덱스 생성 시간이 필요
--인덱스 생성 방법--
[작성법]
CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명 (컬럼명, 컬럼명, ... | 함수명, 함수계산식);
-- '인덱스가 자동으로 생성되는 경우'
--> 'PK' 또는 'UNIQUE' 제약조건이 설정되는 경우
*/
-- ** 인덱스를 이용한 검색 방법 **
--> WHERE 절에 인덱스가 지정된 컬럼을 언급하면 된다.
SELECT * FROM EMPLOYEE
WHERE EMP_ID = 215; -- 인덱스 사용 O
SELECT * FROM EMPLOYEE
WHERE EMP_NAME = '대북혼'; -- 인덱스 사용 X
-- 인덱스 확인용 테이블 생성
CREATE TABLE TB_IDX_TEST (
TEST_NO NUMBER PRIMARY KEY, -- 자동으로 인덱스가 생성됨
TEST_ID VARCHAR2(20) NOT NULL
);
-- TB_IDX_TEST 테이블에 샘플데이터 100만개 삽입 (PL/SQL 사용)
BEGIN
FOR I IN 1..1000000
LOOP
INSERT INTO TB_IDX_TEST VALUES(I, 'TEST'||I);
END LOOP;
COMMIT;
END;
/
-- 샘플데이터 100만개 확인
SELECT COUNT(*) FROM TB_IDX_TEST;
-- 'TEST500000' 찾기
SELECT * FROM TB_IDX_TEST
WHERE TEST_ID = 'TEST500000'; -- 31ms -- INDEX 사용 X
SELECT * FROM TB_IDX_TEST
WHERE TEST_NO = 500000; -- 2ms -- INDEX 사용 O