이 장에서는 테이블을 만들고, 테이블에 데이터를 입력하고, 테이블 명칭과 필드 속성을 바꾸고, 테이블을 삭제하고, 다시 복구하는 명령을 실습한다.
17.1 CREATE TABLE
CREATE TABLE 명령은 테이블을 만들면서 필드와 데이터 형식, 필드 자릿수, 공백 허용 여부, 기본값 등을 설정한다.
•
CREATE TABLE 테이블명 (
필드명1 데이터형식1 [제약조건1],
필드명2 데이터형식2 [제약조건2],
.
.
.
•
제약조건:
◦
PRIMARY KEY : 기본키를 선언한다. (기본키는 UNIQUE 해야 하고 NOT NULL 해야 함)
◦
FOREIGN KEY : 외부 테이블의 필드와 연관하는 참조키를 선언한다.
◦
UNIQUE KEY : 모든 레코드 행에서 유일값 속성으로 선언한다.
◦
NOT NULL : 필드 입력값에 NULL을 허용하지 않음
◦
CHECK : 필드 입력값의 범위를선언한다.
◦
DEFAULT : 필드 입력값이 없을 때 기본적으로 입력하는 값이다.
예제1. 2019년 성적 테이블을 만들고 인덱스와 기본키를 설정한다.
CREATE TABLE TB_GRADE_2019
(
TEST_MONTH CHAR(2 BYTE) NOT NULL, --월
TEST_CD CHAR(3 BYTE) NOT NULL, --시험코드
KOR NUMBER(3), -- 국어
ENG NUMBER(3), -- 영어
MAT NUMBER(3) -- 수학
);
CREATE UNIQUE INDEX PK_TB_GRADE_2019 ON TB_GRADE_2019 (TEST_MONTH, TEST_CD); -- 인덱스 설정
ALTER TABLE TB_GRADE_2019 ADD(CONSTRAINT PK_TB_GRADE_2019 PRIMARY KEY(TEST_MONTH, TEST_CD));
SQL
복사
예제2. 2019년 성적 테이블에 성적 데이터를 입력하고 확인한다.
INSERT INTO TB_GRADE_2019 VALUES('01','T01',92,98,84);
INSERT INTO TB_GRADE_2019 VALUES('01','T02',84,94,90);
COMMIT; -- 변경 사항 저장
SELECT *
FROM TB_GRADE_2019;
SQL
복사
17.2 DESC 와 DESCRIBE
DESC 와 DESCRIBE 명령은 지정한 ‘테이블명’의 구조를 보여주는 기능으로, 필드명, 데이터형식, 데이터크기, 공백 허용 여부 등을 확인한다.
•
DESC | DESCRIBE 테이블명;
예제1. 2019년 성적 테이블의 구조를 확인한다.
DESC TB_GRADE_2019; -- 또는
DESCRIBE TB_GRADE_2019;
SQL
복사
17.3 RENAME
RENAME 명령은 ‘기존 테이블명’을 ‘신규 테이블명’으로 이름을 바꾼다. 테이블명만 바뀔 뿐 테이블 내 구조는 변경되지 않는다.
•
RENAME 기존 테이블명 TO 신규 테이블명;
예제1. 테이블명 TB_GRADE_2019의 내용을 검색하고, 이름을 TB_2019_GRADE로 바꾼다.
SELECT *
FROM TB_GRADE_2019;
RENAME TB_GRADE_2019 TO TB_2019_GRADE; -- 테이블명 변경
SELECT *
FROM TB_GRADE_2019;-- 오류 발생, 이름이 바꼈기 때문에 존재 안함
SQL
복사
예제2. TB_2019_GRADE 이름을 TB_GRADE_2019로 바꾸고 테이블의 내용을 검색한다.
SELECT *
FROM TB_2019_GRADE;
RENAME TB_2019_GRADE TO TB_GRADE_2019;
SELECT *
FROM TB_GRADE_2019;
SQL
복사
17.4 ALTER TABLE
ALTER TABLE 명령은 테이블 내 필드를 조작하는 명령으로, ADD 구문으로 필드를 만들고, MODIFY 구문으로 필드의 형식을 바꾸고, RENAME COLUMN 구문으로 필드명을 바꾸고, DROP COLUMN 구문으로 필드를 삭제한다.
•
ALTER TABLE 테이블명 ADD (필드명 [ 데이터형식 ]);
•
ALTER TABLE 테이블명 MODIFY (필드명 [ 데이터 형식]);
•
ALTER TABLE 테이블명 RENAME COLUMN 기존 필드명 TO 변경 필드명;
•
ALTER TABLE 테이블명 DROP COLUMN 필드명;
ALTER TABLE 명령을 이용해서 TB_GRADE_2019을 대상으로 TOT,AVG 필드를 추가하고(ADD) 필드명과 속성을 바꾸고(MODIFY, RENAME COLUMN TO), 필드를 삭제(DROP COLUMN)하는 실습을 진행하겠다.
예제1. TB_GRADE_2019의 전체 내용과 구조를 검색한다.
SELECT *
FROM TB_GRADE_2019;
DESC TB_GRADE_2019;
SQL
복사
예제2. TB_GRADE_2019 테이블의 마지막에 수치 5자리의 TOT필드와 소수점 1자리를 포함한 수치 5자리의 AVG 필드를 만든다.
ALTER TABLE TB_GRADE_2019 ADD (TOT NUMBER(5) NULL); -- TOT 필드 생성
ALTER TABLE TB_GRADE_2019 ADD (AVG NUMBER(5,1) NULL); -- AVG 필드 생성
DESC TB_GRADE_2019;
SQL
복사
예제3. ‘TOT’ 필드명을 ‘M_TOT’로 바꾸고 자릿수는 6자리로 바꾼다. ‘AVG’ 필드명은 ‘M_AVG’로 바꾸고 소수점 2자리를 포함한 6자리로 바꾼다.
ALTER TABLE TB_GRADE_2019 MODIFY(TOT NUMBER(6));
ALTER TABLE TB_GRADE_2019 RENAME COLUMN TOT TO M_TOT;
ALTER TABLE TB_GRADE_2019 MODIFY(AVG NUMBER(6,2));
ALTER TABLE TB_GRADE_2019 RENAME COLUMN AVG TO M_AVG;
DESC TB_GRADE_2019;
SQL
복사
예제4. M_TOT와 M_AVG 필드를 삭제하고, 테이블 구조를 검색한다.
ALTER TABLE TB_GRADE_2019 DROP COLUMN M_TOT;
ALTER TABLE TB_GRADE_2019 DROP COLUMN M_AVG;
DESC TB_GRADE_2019;
SQL
복사
17.5 INDEX
인덱스는 데이터베이스를 빠르게 조작하려고 만든 색인표로, 정보를 쉽게 찾을 수 있는 필드로 구성한다. CREATE INDEX 명령으로 인덱스를 만들고, ALTER INDEX ...RENAME TO명령으로 인덱스명을 바꾸고, DROP INDEX 명령으로 인덱스를 삭제한다.
•
CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명 (필드명1, 필드명2,...필드명n);
•
ALTER INDEX 현재 인덱스명 RENAME TO 신규 인덱스명;
•
DROP INDEX 인덱스명;
UNIQUE로 인덱스의 필드 값을 중복 없이 만든다.예제1. TB_GRADE_2019 테이블의 기존 인덱스 내용을 확인하고, 시험월과 시험코드에 대한 국어, 영어, 수학 점수로 인덱스를 만든다.
SELECT INDEX_NAME, -- 인덱스명
COLUMN_NAME, -- 필드명
COLUMN_POSITION, -- 필드 위치
CHAR_LENGTH, -- 문자 크기
DESCEND -- 정렬방식
FROM USER_IND_COLUMNS -- 인덱스열만 모아논 테이블
WHERE TABLE_NAME='TB_GRADE_2019';
CREATE INDEX IDX_KOR_SCORE ON TB_GRADE_2019(TEST_MONTH,TEST_CD,KOR);
CREATE INDEX IDX_ENG_SCORE ON TB_GRADE_2019(TEST_MONTH,TEST_CD,ENG);
CREATE INDEX IDX_MAT_SCORE ON TB_GRADE_2019(TEST_MONTH,TEST_CD,MAT);
SELECT INDEX_NAME,
COLUMN_NAME,
COLUMN_POSITION,
CHAR_LENGTH,
DESCEND
FROM USER_IND_COLUMNS
WHERE TABLE_NAME='TB_GRADE_2019';
SQL
복사
예제2. TB_GRADE_2019의 IDX_KOR_SCORE 인덱스의 이름을 ‘IDX_KOR_GRADE’로, IDX_ENG_SCORE인덱스의 이름을 ‘IDX_ENG_GRADE’로, IDX_MAT_SCORE 인덱스의 이름을 IDX_MAT_GRADE로 바꾼다.
ALTER INDEX IDX_KOR_SCORE RENAME TO IDX_KOR_GRADE;
ALTER INDEX IDX_ENG_SCORE RENAME TO IDX_ENG_GRADE;
ALTER INDEX IDX_MAT_SCORE RENAME TO IDX_MAT_GRADE;
SELECT INDEX_NAME,
COLUMN_NAME,
COLUMN_POSITION,
CHAR_LENGTH,
DESCEND
FROM USER_IND_COLUMNS
WHERE TABLE_NAME='TB_GRADE_2019';
SQL
복사
예제3. TB_GRADE_2019 에서 IDX_KOR_GRADE, IDX_ENG_GRADE, IDX_MAT_GRADE 인덱스를 삭제한다.
DROP INDEX IDX_KOR_GRADE;
DROP INDEX IDX_ENG_GRADE;
DROP INDEX IDX_MAT_GRADE;
SELECT INDEX_NAME,
COLUMN_NAME,
COLUMN_POSITION,
CHAR_LENGTH,
DESCEND
FROM USER_IND_COLUMNS
WHERE TABLE_NAME='TB_GRADE_2019';
SQL
복사
17.6 PRIMARY KEY
ALTER TABLE 명령에 ADD(CONSTRAINT ...) 구문으로 기본키를 설정하고, DROP CONSTRAINT 구문으로 기본키를 삭제한다.
•
ALTER TABLE 테이블명 ADD( CONSTRAINT 기본키명 PRIMARY KEY (필드명1,필드명2,...,필드명n));
•
ALTER TABLE 테이블명 DROP CONSTRAINT 기본키명;
예제1. 2020년 성적 테이블(TB_GRADE_2020)의 기존 기본키를 확인하고 삭제한다.
SELECT A.CONSTRAINT_NAME, -- 'PK_TB_GRADE_2020'
A.CONSTRAINT_TYPE,
A.TABLE_NAME,
B.POSITION,
B.COLUMN_NAME
FROM SYS.USER_CONSTRAINTS A,SYS.USER_CONS_COLUMNS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.TABLE_NAME = 'TB_GRADE_2020' -- 테이블이 TB_GRADE_2020인
AND A.CONSTRAINT_TYPE = 'P'; -- 제약사항이 기본키인 경우
ALTER TABLE TB_GRADE_2020 DROP CONSTRAINT PK_TB_GRADE_2020; -- 기본키 삭제
SELECT A.CONSTRAINT_NAME,
A.CONSTRAINT_TYPE,
A.TABLE_NAME,
B.POSITION,
B.COLUMN_NAME
FROM SYS.USER_CONSTRAINTS A, SYS.USER_CONS_COLUMNS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.TABLE_NAME = 'TB_GRADE_2020'
AND A.CONSTRAINT_TYPE='P';
SQL
복사
SYS.USER_CONSTRAINTS 는 제약사항이 담긴 테이블에 대한 정보다. (어떤 칼럼에 제약조건이 부여됐는지 확인 불가) SYS.USER_CONS_COLUMNS 는 컬럼에 할당된 제약조건에 대한 정보가 담긴 테이블이다.(제약 조건의 종류는 확인 불가)에제2. 2020년 성적 테이블에 기본키를 만들고 확인한다.
ALTER TABLE TB_GRADE_2020 ADD(CONSTRAINT PK_TB_GRADE_2020_P1 PRIMARY KEY (TEST_MONTH, TEST_CD));
SELECT A.CONSTRAINT_NAME,
A.CONSTRAINT_TYPE,
A.TABLE_NAME,
B.POSITION,
B.COLUMN_NAME
FROM SYS.USER_CONSTRAINTS A, SYS.USER_CONS_COLUMNS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.TABLE_NAME='TB_GRADE_2020'
AND A.CONSTRAINT_TYPE='P';
SQL
복사
17.7 TRUNCATE TABLE
TRUNCATE TABLE 명령은 복구 정보를 만들지 않고 전체 데이터를 삭제하는 기능으로, ‘테이블명’에 해당하는 테이블을 비운다. 삭제할 때 복구 정보를 만들지 않으므로 저장 공간과 삭제 시간을 줄인다.
•
TRUNCATE TABLE 테이블명;
테이블을 삭제할 때 복구 정보를 만들지 않으면 실수로 지운 데이터를 다시 복구할 수 없다. 일반적으로는 복구 정보를 만들면서 삭제하고 일정 시간이 지난 후 필요 없는 복구 정보를 없앤다.예제1. 2019년 성적 테이블을 복구 정보를 만들지 않고 삭제한다.
SELECT *
FROM TB_GRADE_2019;
TRUNCATE TABLE TB_GRADE_2019;
SELECT *
FROM TB_GRADE_2019;
SQL
복사
17.8 DROP TABLE
DROP TABLE 명령은 테이블을 삭제하는 기능이다. CASCADE COONSTRAINTS 구문은 제약조건을함께 삭제하고, PURGE 구문은 테이블을 완전히 삭제(복구 정보 X)한다. FLASHBACK TABLE TO BEFORE DROP 명령은 삭제 전 상태로 복구한다. PURGE RECYCLEBIN 은 테이블 복구 정보가 있는 휴지통을 삭제한다.
•
DROP TABLE 테이블 [ CASCADE CONSTRAINTS ];
•
DROP TABLE 테이블 PURGE;
•
FLASHBACK TABLE 테이블 TO BEFORE DROP;
•
PURGE RECYCLEBIN;
예제1. 2019년 성적 테이블을 삭제하고 구조를 확인
DROP TABLE TB_GRADE_2019; -- 완전 삭제는 아님
DESC TB_GRADE_2019;
SQL
복사
오류:
ORA-04043: TB_GRADE_2019 객체가 존재하지 않습니다. 로 나온다.
예제2. 2019년 성적 테이블을 삭제 전 상태로 복구하고 구조를 확인
FLASHBACK TABLE TB_GRADE_2019 TO BEFORE DROP;
DESC TB_GRADE_2019;
SQL
복사
예제3. 2019년 성적 테이블을 삭제하고 오라클 휴지통을 비운다.(PURGE RECYCLEBIN)
DROP TABLE TB_GRADE_2019;
PURGE RECYCLEBIN; -- 테이블 복구 불가능해짐.
SQL
복사
예제4. 2019년 성적 테이블을 삭제 전 상태로 복구하고 구조를 확인한다.
FLASHBACK TABLE TB_GRADE_2019 TO BEFORE DROP;
DESC TB_GRADE_2019;
SQL
복사
오류 발생. 복구할 테이블이 없음. 앞서 PURGE RECYCLEBIN을 했기 때문