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

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

- 자동 커밋 (=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차 실행

AS

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

AS

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