DDL (데이터 정의 언어 : Data Define Language)
Schema, Domain,Table, View, Index를 정의하거나 변경 또는 삭제할 때 사용하는 언어
DDL 유형
CREATE, ALTER, DROP...
[ CREATE ]
1. 테이블 생성
SQL > CREATE TABLE TBL_PANAME
(PDCODE VARCHAR2(10),
PANDAY DATE DEFAULT SYSDATE,
PANSU NUMBER,
CONSTRAINT TBL_PANAME_PDCODE_FK --- 테이블 레벨의 제약조건
FOREIGN KEY(PDCODE)
REFERENCES TBL_PRODUCT(PDCODE)
ON DELETE CASCADE );* CREATE, SELECT, FROM 등을 '키워드' 또는 '예약어'라고 한다
키워드 + 요소를 ---절이라고 한다. ( 예: SELECT + 요소 -> SELECT 절)
2. 제약조건
컬럼레벨의 제약조건은 , 없이 뒤에 이어서 CONSTRAINT문법을 적음
테이블 레벨의 제약조건은 컬럼 정의가 끝나고 (, 로 종결) 추가
제약조건 명명규칙은 테이블이름(약식)_컬럼명_제약조건종류
* [constraint 제약조건명] 생략가능
다만 관리가 어렵기때문에 권장하지 않음
1) NOT NULL 제약 조건 - 컬럼 레벨의 문법만 가능
SQL> create table test1
( id number(10) constraint t1_id_nn not null,
name varchar2(30) constraint t1_name_nn not null,
job varchar2(20),
email varchar2(20),
phone varchar2(20) constraint t1_ph_nn not null,
start_date date );2) UNIQUE 제약조건
중복값 허용 안함
SQL> create table test2
(id number(10) constraint t2_id_nn not null,
constraint t2_id_uk unique, ---comlumn-level constraint
(...)
email varchar2(20),
start_date date,
constraint t2_email_uk unique(email) ); --- table-level constraint3) PRIMARY KEY 제약조건
- NOT NULL + UNIQUE 의 성격을 가짐
- 테이블의 대표 컬럼에 적용하는 제약조건 (구별을 할 수 있는 키)
- 주민번호같이 민감한 정보에는 사용하지 않는다.
SQL> CREATE TABLE test3
2 (id number(10) constraint t3_id_pk primary key,
3 name varchar2(30) constraint t3_name_nn not null,
4 job varchar2(20),
5 email varchar2(20),
6 phone varchar2(20) constraint t3_ph_nn not null
7 constraint t3_ph_uk unique,
8 start_date DATE,
9 constraint t3_email_uk UNIQUE(email));
Table created.
4) FOREIGN_KEY 제약조건
자기 자신 테이블이나 다른 테이블 특정칼럼(PK, UK)을 참조하는 제약조건
* 예) 없는 부서 아이디(department_id)를 사원정보(employees)에 입력해선 안된다.
4-1) 테이블레벨 제약조건
employess.department_id 컬럼에 제약조건을 걸되, 참조하는 컬럼은 departments.department_id
CREATE TABLE employess (
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pet NUMBER(2,2),
hire_date DATE NOT NULL,
(...)
department_id NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_email_uk Unique(email) );4-2) 컬럼레벨 제약조건
* REFERENCES 절이 있으므로 컬럼레벨에선 제약조건종류(FOREIGN KEY) 키워드를 적지 않는다.
CREATE TABLE employess
(
(...)
department_id NUMBER(4) CONSTRAINT emp_dept_fk
REFERENCES departments(department_id),
(...)
)4-3) 자기 자신 컬럼을 참조하는 제약조건
ex) 매니저 아이디는 반드시 사원정보 테이블의 사원ID(employee_id)에 존재하는 값이어야 함
CREATE TABLE employess
(
(...)
manager_id NUMBER(6)
CONSTRAINT emp_manager_fk REFERENCES
employee (employee_id)
(...)
)
5) CHECK 제약조건
(...)
salary NUMBER(2)
CONSTRAINT emp_salary_min
CHECK (salary > 0) --- salary의 값이 0 이상이어야 함
(...)
3. 제약조건 조회 (데이터 딕셔너리)
(* 테이블 이름은 DB에 저장될 때 무조건 대문자로 저장되기 때문에 조회 시 대문자로 적는다)
SELECT constraint_name, constraint_type, search_condition
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = '<테이블이름>';
4. 서브쿼리 구문으로 테이블 생성
CREATE TABLE <테이블명>
AS
SELECT
FROM
WHERE예) 서브쿼리로 테이블 생성하되, WHERE절 조건에 맞는 VALUES도 가져오기
CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;SELECT결과
EMPLOYEE_ID LAST_NAME ANNSAL HIRE_DAT
----------- ------------------------- ---------- --------
145 Russell 168000 04/10/01
146 Partners 162000 05/01/05
147 Errazuriz 144000 05/03/10
148 Cambrault 132000 07/10/15
149 Zlotkey 126000 08/01/29
150 Tucker 120000 05/01/30
151 Bernstein 114000 05/03/24
152 Hall 108000 05/08/20
153 Olsen 96000 06/03/30
154 Cambrault 90000 06/12/09
155 Tuvault 84000 07/11/23제약조건 조회결과
SELECT constraint_name, constraint_type, search_condition, r_constraint_name
FROM user_constraints
WHERE table_name = 'DEPT80';* 제약조건은 NOT NULL 외에 복사되지 않으며 NOT NULL도 제약조건명은 임의로 붙여진다.
CONSTRAINT_NAME C SEARCH_CONDITION R_CONSTRAINT_NAME
-------------------------------------------------------------------------------
SYS_C007745 C "LAST_NAME" IS NOT NULL NULL
SYS_C007746 C "HIRE_DATE" IS NOT NULL NULL
예) 서브쿼리로 테이블 생성 시 구조만 가져오기
* WHERE절에 항상 false가 되는 조건을 넣으면 된다.
CREATE TABLE dept80_2
AS
SELECT employee_id, last_name, salary * 12 annsal, hire_date
FROM employees
WHERE 1 = 2;5. 테이블 생성 시 대소문자 구분 또는 띄어쓰기 사용하여 생성
소문자 이름 (큰 따옴표 사용)으로 테이블을 특별히 생성하지 않은 경우 테이블 이름은 기본적으로 대문자로 설정된다.
1) 대소문자 구분
CREATE TABLE "myTest" (id CHAR(10));
2) 대소문자 구분 및 띄어쓰기
CREATE TABLE "my Test" (id CHAR(10));
위와 같이 ""안에 테이블명을 입력할 경우 대소문자가 구분되고, 띄어쓰기를 사용할 수 있게 된다.
[ ALTER ]
1. 테이블 수정
1) 테이블에 컬럼 추가
ALTER TABLE dept80
ADD (job_id VARCHAR2(0) ); -- job_id컬럼추가2) 테이블 기존 컬럼 삭제
ALTER TABLE dept80
MODIFY (last_name VARCHAR2(30) ); -- 데이터타입,컬럼사이즈,기본값 등3) 테이블 기존 컬럼에 제약조건 추가
ALTER TABLE emp2
ADD CONSTRAINT emp_mgr_fk ---emp_mgr_fk 이름으로 제약조건 추가
FOREIGN KEY(manager_id) -- manager_id컬럼을 외래키로 지정
REFERENCES emp2(employee_id); -- emp2테이블의 employee_id컬럼 참조4) 테이블 제약조건 삭제
ALTER TABLE emp2
DROP CONSTRAINT emp_mgr_fk; ---emp_mgr_fk이란 이름의 제약조건 삭제
[ DROP ]
1. 테이블 삭제
1) 테이블 삭제
1-1) 휴지통을 거치고 삭제
DROP TABLE emp2;
1-2) 휴지통을 거치지 않고 삭제 (PURGE키워드 추가)
DROP TABLE emp2 PURGE;[ FLASHBACK ]
2) 플래쉬백 테이블
실수로 삭제 시 recyclebin으로부터 복원
2-1) 휴지통에서 기존이름, 마지막 동작, 삭제 시간 조회
SELECT original_name, operation, droptime FROM
recyclebin;select * from tab; 으로 테이블을 조회했을 때 아래와 같이 이상한 이름의 테이블명이 보일 것이다. 이는 휴지통에 들어가있는 테이블을 뜻한다. 휴지통의 가용용량은 DB마다 설정마다 다를 수 있다.
BIN$veCJQvVBOgTgVbkzEzPhBQ==$0
BIN$vj9KzAO5DMjgVbkzEzPhBQ==$0
BIN$vj9KzAO9DMjgVbkzEzPhBQ==$0
2-2) 복구
FLASHBACK TABLE emp2 TO BEFORE DROP;[ TRUNCATE ]
1. 테이블 절단 (TRUNCATE)
- 테이블 구조는 남기고 모든 행 제거
* DDL구문이므로 ROLLBACK 불가
TRUNCATE TABLE <테이블이름>;* DELETE 문과 차이
- DELETE는 테이블의 특정 행 삭제 또는 WHERE절 생략 시 모든 행 삭제
- DELETE는 DML(데이터 조작어)이나 TRUNCATE는 DDL(데이터 정의어)
- DELETE는 저장공간은 그대로이나 TRUNCATE는 저장공간 제거
- DELETE는 ROLLBACK이 가능하나 TRUNCATE는 ROLLBACK
* 프로그래머가 테이블 객체 자체는 필요하나 공간을 지우고 더 이상 새 데이터를 삽입할 필요없을 때 쓸 수 있다.
[ VIEW ]
뷰는 하나 이상의 Base table로 생성되나 물리적으로 존재하는 것은 아니다.
SELECT 구문형태로 정의만 되어있는 가상 논리적 테이블을 말한다.
- 뷰의 장점
- 특정 사용자에게 필요한 필드만 제공가능
- 복잡한 쿼리 단순화
- 쿼리 재사용 - 뷰의 단점
- 한 번 정의된 뷰는 변경 불가
- 삽입, 삭제, 갱신 작업에 제한 있음
- 자신만의 인덱스를 가질 수 없음
1. 뷰 생성
1) 서브 쿼리 이용
CREATE VIEW empvu80 AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;구조 보기
desc empvu80;이름 널? 유형
----------- -------- ------------
EMPLOYEE_ID NOT NULL NUMBER(6)
LAST_NAME NOT NULL VARCHAR2(25)
SALARY NUMBER(8,2) 2) 서브쿼리에 AS키워드 이용
CREATE VIEW salvu50 AS
SELECT employee_id id_number,
last_name name,
salary * 12 ann_salary
FROM employees
WHERE department_id = 50;데이터 조회
select * from salvu50; ID_NUMBER NAME ANN_SALARY
---------- ------------------------- ----------
120 Weiss 96000
121 Fripp 98400
122 Kaufling 94800- 표현식이나 함수를 사용한 컬럼은 AS키워드 이용한 컬럼 앨리어스가 필요함.
2. 뷰 수정 (OR REPLACE)
VIEW의 생성구문에 OR REPLACE 키워드를 사용하는 경우 동일한 이름의 뷰가 없다면 그대로 생성되고 동일한 이름을 사용하는 뷰가 있다면 새롭게 정의하는 뷰의 내용으로 대체하는 동작을 하게된다.
1) AS 사용 (base table이 하나인 simple view)
- 뷰에서 앨리어스 리스트를 작성한 경우 서브쿼리의 컬럼명보다 우선적으로 적용되어 뷰의 컬럼명으로 사용된다.
CREATE OR REPLACE VIEW empvu80 (
id_number, name, sal, department_id
) AS
SELECT employee_id,
first_name || ' ' || last_name,
salary, department_id
FROM employees
WHERE department_id = 80;조회
ID_NUMBER NAME SAL DEPARTMENT_ID
---------- ---------------------------------------------- ---------- -------------
145 John Russell 14000 80
146 Karen Partners 13500 80
147 Alberto Errazuriz 12000 802) JOIN, 함수 사용 (base table이 하나 이상인 complex view)
CREATE OR REPLACE VIEW dept_sum_vu (
name, minsal, maxsal, avgsal
) AS
SELECT d.department_name, MIN(e.salary), MAX(e.salary), AVG(e.salary)
FROM employees e
JOIN departments d ON ( e.department_id = d.department_id )
GROUP BY d.department_name;조회
NAME MINSAL MAXSAL AVGSAL
------------------------------ ---------- ---------- ----------
Administration 4400 4400 4400
Accounting 8300 12008 10154
Purchasing 2500 11000 41503. 뷰 사용
select salary from employees where employee_id = 145; SALARY
----------
14000update 후 조회
UPDATE empvu80
SET
sal = sal + 500
WHERE id_number = 145; SALARY
----------
14500insert 시 다음과 같이 제약이 따른다.
INSERT INTO empvu80 (
employee_id, last_name, salary
) VALUES (
80, 'test', 8000
);NOT NULL컬럼이 있지만 해당 컬럼의 정보를 가진 뷰를 가지고 있지 않아 삽입할 수 없다.
명령의 99 행에서 시작하는 중 오류 발생 -
insert into empvu80 (employee_id, last_name, salary)
values (80, 'test', 8000)
오류 보고 -
ORA-01400: NULL을 ("HR"."EMPLOYEES"."EMAIL") 안에 삽입할 수 없습니다4. 뷰 삭제
DROP VIEW empvu80;
[ SEQUENCE ]
자동으로 고유 번호를 반환하는 오브젝트
1. 시퀀스 생성
CREATE SEQUENCE dept_deptid_seq2
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;옵션
- INCREMENT BY 생성할 숫자의 증감 간격을 설정하는 옵션절
- START WITH 숫자 생성을 시작할 값을 설정
- MAXVALUE 최대값 지정
NOMAXVALUE 최대값 지정하지 않음 10^27 - MINVALUE 최소값 지정
NOMINVALUE 최소값 지정하지 않음 -(10^26) - CYCLE 최대|최소값 도달 시 초기화
NOCYCLE 한계값 도달 시 멈춤
CACHE 미리 지정된 개수의 값을 생성 기본값
NOCACHE 값을 미리 생성하지 않음. 권장하지 않음
2. 시퀀스 사용
아래 쿼리 조회 시 INCREMENT BY만큼 증가한다.
select dept_deptid_seq.nextval
from dual;INSERT 활용
INSERT INTO departments (
department_id, department_name
) VALUES (
dept_deptid_seq.NEXTVAL, 'LAST'
);alter sequence
- start with 를 제외한 나머지 옵션은 수정 가능
'DB' 카테고리의 다른 글
| [오라클 DB] 일반 함수/그룹함수 (0) | 2021.03.10 |
|---|---|
| [오라클 DB] 함수-숫자 (0) | 2021.03.10 |
| [오라클 DB] 함수-문자) (0) | 2021.02.26 |
| [오라클 DB] DQL 심화학습 (0) | 2021.02.19 |
| [오라클 DB] 온라인에서 Oracle SQL 테스트하기 (0) | 2021.02.19 |