DAY04. SQL Basic DDL (테이블 생성,구조 변경,삭제)

데이터분석가 과정/SQL

DAY04. SQL Basic DDL (테이블 생성,구조 변경,삭제)

LEE_BOMB 2021. 9. 14. 19:55

- 테이블 생성, 구조 변경, 삭제

- 자동 커밋 (=auto commit)

Oracle에서 제공되는 데이터형의 종류

VARCHAR2(n) 가변 길이 문자 데이터(1~4000byte), 최소크기 1byte

CHAR(n) 고정 길이 문자 데이터(1~2000byte) , 최소크기 1byte

INT 4Byte 크기의 정수 데이터

NUMBER(p,s) 전체 p자리 중 소수점 이하 s자리(p:1~38, s:-84~127)

DATE 7Byte(BC 4712년 1월 1일부터 AD 9999년 12월 31일)

RAW(n) n Byte의 원시 이진 데이터(1~2000)

LONG 가변 길이 문자 데이터(1~2Gbyte)

LONG RAW 가변 길이 원시 이진 데이터(1~2Gbyte

1) 의사칼럼(Pseudo Column)

- 실제 테이블에 존재하지 않은 가짜 칼럼(가상 공간에서 사용)

- SELECT와 WHERE절에서만 사용 가능하다.

대표적인 의사칼럼 : ROWNUM과 ROWID

* ROWNUM : 레코드 조회 순번

* ROWID : 테이블 생성과정에서 자동으로 만들어지는 식별가능한 고유값

SELECT ROWNUM, empno, ename, ROWID -- 임의 식별자 값 부여
FROM emp
WHERE ROWNUM <=10 ; --1번부터 10번까지 가장 최근의 데이터 확인

레코드 순번 역순 검색

SELECT ROWNUM, empno, ename, ROWID
FROM emp
WHERE ROWNUM <=5
ORDER BY ROWNUM DESC;

5번~10번째 입사자 : 특정 범위 조회는 불가능

SELECT ROWNUM, empno, ename, ROWID
FROM emp
WHERE ROWNUM >=5 AND ROWNUM <=10;

특정 범위 : subquery + 별칭 이용

SELECT rnum, empno, ename, sal
FROM (SELECT emp.*, ROWNUM rnum FROM emp) -- 테이블 대체
WHERE rnum >= 5 AND rnum <=10;

sub query 별칭 : main query SELECT, WHERE절 이용

emp.* : main query에 emp의 칼럼을 넘기는 역할

2. 서브쿼리를 이용해서 테이블 생성

기존 테이블을 이용해서 새로운 테이블을 생성

main qeury -- 2차 실행

sub query; -- 1차 실행

CREATE TABLE emp02 -- 전체내용+구조가 모두 같고, 이름만 다름
AS
SELECT * FROM emp;

SELECT * FROM emp02;

일부분의 칼럼으로만 테이블 생성

CREATE TABLE emp03
AS
SELECT empno, ename FROM emp;

특정 행을 대상으로 서브쿼리를 이용한 테이블 생성

CREATE TABLE emp05
AS
SELECT * FROM emp
WHERE deptno=10;

테이블의 구조(스키마)만 복사하기

CREATE TABLE emp06
AS
SELECT * FROM emp WHERE 1=0; -- 1=0은 거짓이므로 메인쿼리에 반영되지 않고, 구조만 복사됨

[과제] DEPT 테이블과 동일한 구조의 빈 테이블을 생성하시오. (테이블의 이름은 DEPT02)

CREATE TABLE dept02
AS
SELECT*FROM dept WHERE 1=0;

3. 실수형 테이블 생성

CREATE TABLE emp01(
empno number(4),
ename varchar2(20),
sal number(7,2) -- (전체 자릿수, 소수점 자릿수)
);

INSERT INTO emp01 VALUES (1001, 'hong', 1234.1);
INSERT INTO emp01 VALUES (1002, 'lee', 1234.123); -- 전체 자리수가 넘지 않는다면, 소수점 3번째 자리에서 반올림되어 반영된다
INSERT INTO emp01 VALUES (1002, 'kang', 123456.123); -- 전체가 8자리이므로 error 발생

Constraint (테이블 제약조건)

1. PRIMARY KEY(PK) 유일하게 테이블의 각행을 식별(NOT NULL과 UNIQU E조건 만족)

2. FOREIGN KEY(FK) 열과 참조된 열 사이의 외래키 관계를 적용하고 설정

3. UNIQUE key(UK) 테이블의 모든 행을 유일하게 하는 값을 가진 열(NUL L 허용)

4. NOT NULL(NN) 열은 NULL값을 포함할 수 없다.

5. CHECK(CK) 참이어야 하는 조건을 지정함(대부분 업무 규칙 설정)

제약 조건 정의 방법

1) 칼럼 LEVEL 제약 조건(COLUMN LEVEL CONSTRAINT)

열(칼럼)별로 제약 조건을 정의한다.

무결성 제약 조건 5가지를 모두 적용할 수 있다.

NOT NULL제약 조건은 칼럼 LEVEL에서만 가능하다.

2) 테이블 LEVEL 제약 조건(TABLE LEVEL CONSTRAINT)

테이블의 칼럼 정의와는 개별적으로 정의한다.

하나 이상의 열을 참조할 경우에 사용한다.

NOT NULL을 제외한 나머지 제약 조건만 정의

(1) PRIMARY KEY (PK)

column datatype [CONSTRAINT constraint_name] PRIMARY KEY (col1[,col2,..])

테이블에 대한 기본 키를 생성한다.

하나의 기본 키만이 테이블에 존재할 수 있다.

PRIMARY KEY 제약 조건은 테이블에서 각행을 유일하게 식별하는 열 또는 열의 집합이다.(UNIQUE와 NOT NULL조건을 만족)

이 제약 조건은 열 또는 열의 집합의 유일성을 요구하고 NULL값을 포함할 수 없다.

UNIQUE INDEX가 자동 생성된다.

1) 기본키 (primary key) : 자료 검색용 칼럼

CREATE TABLE test_tab1(
id number(2) PRIMARY KEY, -- 칼럼 레벨로 기본키 설정
name varchar(10)
);

CREATE TABLE test_tab2(
id number(2),
name varchar(10),
PRIMARY KEY(id) -- 테이블 레벨로 기본키 설정
);

(2) FOREIGN KEY (FK)

column datatype [CONSTRAINT constraint_name] REFERENCES table_ name (column1[,column2,..] [ON DELETE CASCADE])

외래키(FOREIGN KEY)는 참조하는 테이블에서 정의한다.

MASTER TABLE의 PRIMARY KEY,UNIQUE KEY로 정의된 열을 지정할 수 있으며 열의 값과 일치하거나 NULL값이어야 한다.

FOREIGN KEY는 열 또는 열의 집합을 지정할 수 있으며 동일 테이블 또는 다른 테이블간의 관계를 지정할 수 있다.

ON DELETE CASCADE을 사용하여 DETAIL TABLE에서 관련된 행을 삭제하 고 MASTER TABLE에서 삭제를 허용할 수 있다

외래키 포함된 table 생성 (=거래 테이블)

CREATE TABLE emp_tab(
empno number(4) PRIMARY KEY, -- 거래테이블 자체의 기본키
ename varchar(30),
sal number(7),
deptno number(2) NOT NULL, -- 외래키
FOREIGN KEY(deptno) REFERENCES dept_tab(deptno) -- 참조하고자 하는 테이블 명시
);

INSERT INTO emp_tab VALUES (1001, '홍길동', 2500000, 1);
INSERT INTO emp_tab VALUES (1002, '유관순', 3500000, 2);
INSERT INTO emp_tab VALUES (1003, '강호동', 4500000, 3); -- error : 참조무결성 제약조건 위배
SELECT * FROM emp_tab;

문제) sub query를 이용해서 사번이 1002인 사원의 부서정보 출력

sub 테이블 : emp_tab, main 테이블 : dept_tab

SELECT *
FROM dept_tab
WHERE deptno =
(SELECT deptno FROM emp_tab WHERE empno=1002);

(3) UNIQUE KEY (UK)

column datatype [CONSTRAINT constraint_name] UNIQUE

UNIQUE Key 무결성 제약 조건은 열 또는 열 집합의 값들이 유일해야 한다.

중복된 값을 가지는 행이 존재할 수 없음을 의미한다.

PRIMARY KEY와 유사하나 NULL을 허용한다.

열이 하나 이상 포함되어 있다면 composite unique key라 부른다.

UNIQUE Key에 대하여 UNIQUE INDEX가 자동 생성된다.

CREATE TABLE UNI_TAB1 (
DEPTNO NUMBER(2) CONSTRAINT UNI_TAB_DEPTNO_UK UNIQUE, -- 칼럼 level : 중복 불가
DNAME CHAR(14),
LOC CHAR(13));

INSERT INTO UNI_TAB1 VALUES(1,'AAAA','BBBB');
INSERT INTO UNI_TAB1(dname, loc) VALUES('BBBB','CCCC'); -- 중복은 불가능하지만 null(생략)은 허용
INSERT INTO UNI_TAB1(dname, loc) VALUES('1','BBBB','CCCC'); -- error발생 : 중복불가 제약조건때문에
SELECT * FROM uni_tab1;

(4) NOT NULL (NN)

column datatype [CONSTRAINT constraint_name] NOT NULL

NOT NULL 제약 조건은 열에서 NULL을 허용하지 않도록 보증한다.

NOT NULL 제약 조건이 없는 열은 DEFAULT로 NULL을 허용한다.

NOT NULL 제약 조건은 칼럼 제약조건에서만 가능하다.

(5) CHECK (CK)

column datatype [CONSTRAINT constraint_name] CHECK (condition)

특정 열(칼럼)에 대해서 조건을 지정하여 조건에 만족하지는지를 점검한다.

조건에 만족하지 않은 값이 저장될 경우 오류를 반환한다.

다음과 같은 표현식은 허용되지 않는다.

– CURRVAL, NEXTVAL, LEVEL, ROWNUM에 대한 참조

– SYSDATE, UID, USER, USERENV 함수에 대한 호출

– 다른 행에 있는 값을 참조하는 질의

– ORACLE SERVER가 사용하는 예약어

CREATE TABLE CK_TAB (
DEPTNO NUMBER(2) NOT NULL CHECK (DEPTNO IN (10,20,30,40,50)), -- 첫 번째 제약조건 : NOT NULL, 두 번째 제약조건 CEHCK절
DNAME CHAR(14),
LOC CHAR(13));

INSERT INTO CK_TAB VALUES(10,'AAAA','BBBB'); -- 레코드 삽입
INSERT INTO CK_TAB VALUES(60,'AAAA','BBBB'); -- CHECK제약 조건 위배. '60'을 CEHCK절 list에 추가하면 성립

SQL DDL (데이터 정의어) 테이블 구조변경

1. ALTER TABLE 명령문

칼럼 추가, 삭제, 타입이나 길이를 변경할 때 사용

테이블의 구조를 변경하게 되면 기존에 저장되어 있던 데이터에 영향을 준다.

1) ALTER TABLE ADD

ALTER TABLE table_name ADD (column_name, data_type expr, …)

기존 테이블에 새로운 칼럼 추가

새로운 칼럼은 테이블 맨 마지막에 추가되므로 자신이 원하는 위치 에 만들어 넣을 수 없다.

또한 이미 이전에 추가해 놓은 로우가 존재한다면 그 로우에도 칼럼 이 추가되지만, 칼럼 값은 NULL 값으로 입력된다.

ALTER TABLE emp01
ADD (job varchar(9)); -- 이미 사용중인 테이블에 새 칼럼 job을 추가하면 해당 칼럼에는 null값이 채워진다

2) ALTER TABLE MODIFY문

칼럼에 대해서 데이터 타입이나 크기, 기본 값들을 변경가능하다 (제약조건은 수정불가)

칼럼수정: 자료형, 크기변경

ALTER TABLE table_name MODIFY (column_name, data_type expr, …);
ALTER TABLE emp01
MODIFY(JOB VARCHAR2(30)); -- varvhar(9) -> (30)
DESC emp01;

칼럼명 수정 : sal -> pay

ALTER TABLE emp01 RENAME COLUMN sal to pay;
SELECT * FROM emp01;

3) ALTER TABLE DROP문

ALTER TABLE table_name DROP COLUMN column_name;

칼럼 삭제 :

ALTER TABLE emp01
DROP COLUMN job;

4) ALTER TABLE RENAME문

테이블 이름 변경 :

ALTER TABLE emp01 RENAME TO emp01_copy;

* SCOTT사용자의 전체 테이블 목록 보기

SELECT * FROM tab; --tab은 의사테이블

SQL DDL (데이터 정의어) 전체 레코드 제거

TRUNCATE

기존에 사용하던 테이블의 모든 레코드(row)를 제거

TRUNCATE TABLE table_name

SELECT * FROM emp01_copy;
TRUNCATE TABLE emp01_copy;

SQL DDL (데이터 정의어) 전체 테이블 제거, 데이터 복원

1) 전체 테이블 목록 보기

SELECT * FROM tab;
SELECT tname FROM tab;

-- 테이블 검색 기능

SELECT tname FROM tab WHERE tname = 'EMP'; -- 테이블명 : 대문자

2) 테이블 + 임시파일 제거

DROP TABLE emp01_copy PURGE; -- 복구 불가능

3) 테이블 제거

CREATE TABLE emp01_copy

SELECT*FROM emp;

DROP TABLE emp01_copy; -- 임시파일 존재

4) 임시파일 제거

PURGE RECYCLEBIN;
SELECT*FROM tab;

Data Dictionary, View (데이터 사전과 뷰)

데이터 사전 (Data Dictionary) = 메타 데이터

데이터베이스 자원을 효율적으로 관리하기 위해 다양한 정보를 저장하는 시스템 테이블

사용자가 테이블을 생성하거나 사용자를 변경하는 등의 작업을 할 때 데이터베이스 서버에 의해 자동으로 갱신되는 테이블

사용자는 데이터 딕셔너리의 내용을 직접 수정하거나 삭제할 수 없다. (조회 불가)

데이터 사전 뷰(DATA Dictionary View)

데이터 사전에서 만들어진 정보 조회 기능 제공

DBA_XXXX : 데이터베이스 관리자만 접근 가능한 객체 등의 정보 조회 (DBA는 모두 접근 가능하므로 결국 디비에 있는 모든 객체에 관한 조회)

ALL_XXXX : 자신 계정 소유 또는 권한을 부여 받은 객체 등에 관한 정보 조회

USER_XXXX : 자신의 계정이 소유한 객체 등에 관한 정보 조회

SHOW USER; -- command line;

1) 테이믈 목록 조회 : USER_TABLES

SELECT * FROM USER_TABLES;
SELECT TABLE_NAME FROM USER_TABLES;

2) 임시파일 조회 뷰 : USER_RECYCLEBIN

DESC dept01;
DROP TABLE dept01; -- 테이블 삭제
SELECT * FROM USER_RECYCLEBIN; -- 삭제된 임시파일 조회

OBJECT_NAME : 임시파일명

ORIGINAL_NAME : 원본파일명

3) 테이블 복원

FLASHBACK TABLE "임시파일명" TO BEFORE DROP;

FLASHBACK TABLE "BIN$KhiwkkL3Q9+7pzWAUWjg1A==$0" TO BEFORE