[SQL] DDL(Data Definition Language, 데이터 정의어)

봄·2025년 6월 1일
0

SQL

목록 보기
10/126

📌 이름 지정 규칙

  • 문자로 시작해야 함
    • ex) emp1은 가능, 1emp는 불가능
  • 길이는 1-30자 사이
  • A-Z, a-z (대소문자 구분 x), 특수문자는 _, $, #만 가능
  • 같은 테이블 안에서 이름 중복 x
  • Oracle 서버 예약어 사용 x

✔️ CREATE TABLE

테이블 생성

CREATE TABLE copy_dept
		(deptno       NUMBER(2),
		 dname       VARCHAR2(14),
 		loc         VARCHAR2(13),
 		create_date DATE DEFAULT SYSDATE);

테이블 생성 확인

DESCRIBE copy_dept
데이터 유형설명
VARCHAR2(size)가변 길이 문자
CHAR(size)내용 6
NUMBER(p,s) 전체자리수, 소수점자리수가변 길이 숫자
DATE날짜 및 시간 값
LONG가변 길이 문자(최대 2GB)
CLOB최대 크기는 (4GB - 1) * (DB_BLOCK_SIZE)
RAW 및 LONG RAWRaw binary data
BLOB최대 크기는 (4GB - 1) * (DB_BLOCK_SIZE 초기화 파라미터(8TB - 128TB))
BFILE외부 파일에 저장된 바이너리 데이터(최대 4GB)
ROWID행의 주소값. 테이블에 있는 행의 고유한 주소를 나타내는 base-64 숫자 체계
  • 짧은 문자: VARCHAR2(), CHAR()
  • 긴 문자: LONG, CLOB
    • LONG 권장 X

ex) 예를 들어, A라는 문자를 저장하면
VARCHAR2(5) --> A
CHAR(5) --> A____
(5byte 길이만큼을 유지하자는 의미라서, 빈 공백문자를 4개 붙여서 5개의 길이가 같이 저장되도록 함)
🔸여기서 size는 최댓값이라 더 작은 길이의 문자 저장 가능

NUMBER(7,2) --> 전체 7자리 중 소수점이 2자리 (정수부에 5자리 사용가능)
--> 저장할 수 있는 최댓값: 99999.99

RAW나 LONG RAW 보다 현재는 BLOB나 BFILE을 권장

Datetime

데이터 유형설명
TIMESTAMP소수 표시 초 단위의 날짜
INTERVAL YEAR TO MONTH년, 월 간격으로 저장됨
INTERVAL DAY TO SECOND일, 시, 분, 초 간격으로 저장됨

✔️ DEFAULT

기본값 지정

create table hire_dates
        (id 	number(8),
        hire_date date default sysdate);
CREATE TABLE copy_dept
		(deptno       NUMBER(2),
		 dname       VARCHAR2(14),
		 loc         VARCHAR2(13),
		 create_date DATE DEFAULT SYSDATE);

DESC copy_dept 

INSERT INTO copy_dept (deptno, dname, loc) 
VALUES (10, 'ACCOUNTING', 'NEW YORK') ; 

INSERT INTO copy_dept 
VALUES (20,	'RESEARCH', 'DALLAS', DEFAULT) ; 

INSERT INTO copy_dept 
VALUES (30,	'SALES', 'CHICAGO', NULL); 

SELECT * FROM copy_dept ;

COMMIT ;

INSERT INTO copy_dept (deptno, dname, loc) VALUES (10, 'ACCOUNTING', 'NEW YORK') ;

  • 비워둔 값은 null로 들어가지 않고 기본 값으로 설정해둔
    DEFAULT값 즉, SYSDATE가 자동으로 삽입됨

📌 제약조건

  • DB의 일관성 및 무결성 보장

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK

✔️ NOT NULL

  • null값 허용 X

CREATE TABLE copy_emp 
	(empno   NUMBER(4)    CONSTRAINT cpemp_pk PRIMARY KEY,
	 ename   VARCHAR2(10) CONSTRAINT cpemp_nn NOT NULL, 
	 email   VARCHAR2(20) CONSTRAINT cpemp_uk UNIQUE, 
	 sal     NUMBER(7,2)  CONSTRAINT cpemp_ck CHECK(sal > 0), 
	 deptno  NUMBER(2)    CONSTRAINT cpemp_fk REFERENCES copy_dept(deptno)) ; 

-- 오류발생
UPDATE copy_emp 
SET ename = NULL 
WHERE empno = 7788 ;

✔️ UNIQUE

  • 중복 허용 X

  • null값은 가능

-- null값 가능
UPDATE copy_emp 
SET email = NULL 
WHERE empno = 7788 ; 

-- 오류발생: 무결성 제약조건 위배(중복 허용 x)
UPDATE copy_emp 
SET email = 'jones'
WHERE empno = 7788 ;

✔️ PRIMARY KEY

  • 중복 허용 X

  • null값 허용 X

-- 현재 empno가 primary key임(위쪽 create문 참고)
-- 오류발생: 중복 허용 X
-- empno가 7566인 값을 7788로 바꿔라 -> 중복됨
UPDATE copy_emp 
SET empno = 7788
WHERE empno = 7566 ; 

-- 오류발생: null값 허용 X
UPDATE copy_emp 
SET empno = NULL
WHERE empno = 7566 ;

✔️ FOREIGN KEY

다른 테이블의 값을 참조함

-- 오류발생: copy_dept에는 deptno가 55번인 값이 없음
-- -> 참조할 부모 키가 없으므로 오류
UPDATE copy_emp 
SET deptno = 55
WHERE empno = 7566 ; 

-- 오류발생: copy_dept가 참조하고 있는 copy_emp에 deptno가 10인 값이 있기 때문에
-- delete 되어버리면 copy_dept는 없는 키값을 참조하는 상황이 되어버림
-- 무결정 제약조건 위배: 자식 레코드 발견
delete copy_dept
where deptno = 10;

📌 foreign key 제약으로 참조 중인 테이블이 있다면,
delete 명령 사용 불가능

FOREIGN KEY 제약조건: 키워드

  • FOREIGN KEY: 테이블 제약조건 레벨에서 하위 테이블의 열을 정의
  • REFERENCES: 테이블 및 상위 테이블의 열을 식별
  • ON DELETE CASCADE: 상위 테이블의 행이 삭제될 때 하위 테이블의 종속 행을 삭제함
  • ON DELETE SET NULL: 종속 Foreign key 값을 null로 변환함

🔸ON DELETE SET NULL : delete 되면 그 값을 null로 업데이트

ALTER TABLE copy_emp 
ADD CONSTRAINT cpemp_fk FOREIGN KEY(deptno)
                        REFERENCES copy_dept(deptno) ON DELETE SET NULL ; 

SELECT * FROM copy_emp ; 

-- delete 되고 그 부분은 null로 업데이트 됨
DELETE copy_dept 
WHERE deptno = 10 ; 

SELECT * FROM copy_emp ;

REFERENCES copy_dept(deptno) ON DELETE SET NULL;
: copy_dept테이블의 deptno를 참조하는데
deptno가 delete 되는 상황이 생기면, 그 값을 null로 업데이트 해놓겠다는 의미

🔸ON DELETE CASCADE : 행 자체를 같이 delete 시킴

ALTER TABLE copy_emp 
ADD CONSTRAINT cpemp_fk FOREIGN KEY(deptno)
                        REFERENCES copy_dept(deptno) ON DELETE CASCADE ;

✔️ CHECK

조건식 정의

..., salary NUMBER(2)
	CONSTRAINT emp_salary_min
    		CHECK (salary > 0), ...

서브쿼리를 사용하여 테이블 생성

-- 오류발생: salary*12의 별칭을 지어줘야함(특수문자 *은 출력 불가능)
CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12
FROM employees
WHERE department_id = 80;

-- alias로 오류해결
CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 as ANNSAL
FROM employees
WHERE department_id = 80;

✔️ ALTER TABLE 문

  • 새 열 추가
  • 기존 열 정의 수정
  • 새 열에 기본값 정의
  • 열 삭제
  • 열 이름 바꾸기
  • 읽기 전용 상태로 테이블 변경

🔸열 추가(ADD)

ALTER TABLE copy_emp 
ADD (hiredate DATE) ;

🔸열 수정(MODIFY)

기본값을 변경하면 이후에 테이블에 삽입하는 항목에만 적용됨

ALTER TABLE copy_emp 
MODIFY (hiredate NUMBER(4));

ALTER TABLE copy_emp 
MODIFY (hiredate DATE DEFAULT SYSDATE);

🔸열 삭제(DROP)

drop하면 삭제는 되지만 공간은 그대로 남아있음

ALTER TABLE copy_emp 
DROP (hiredate) ;

🔸SET UNUSED 옵션

좀 더 빠르게 특정 컬럼을 사용하지 않는 상태로 표시해줌

  • SET UNUSED: 하나 이상의 열을 unused로 표시
  • DROP UNUSED COLUMNS: unused로 표시된 열 제거
    ONLINE 키워드를 지정하여 열을 UNUSED로 표시하는 동안 테이블에서 DML 작업이 허용됨을 나타낼 수 있음
ALTER TABLE copy_emp 
SET UNUSED (email) ; 

SELECT * 
FROM user_unused_col_tabs ; 

ALTER TABLE copy_emp 
DROP UNUSED COLUMNS ;

drop하면 삭제는 되지만 공간은 그대로 남아있기 때문에
DROP UNUSED COLUMNS를 통해 공간까지 완전히 제거

🔸읽기 전용 테이블

  • 테이블을 읽기 전용 모드로 설정하여 테이블 유지
  • DDL문 또는 DML문에 의한 변경을 방지

READ ONLY
READ WRITE

ALTER TABLE employees READ ONLY;
ALTER TABLE employees READ WRITE;

✔️ 테이블 삭제

  • 테이블을 Recycle bin(휴지통)으로 이동
    • 실수로 잘못 DROP 하면 다시 꺼내올 수 있다는 의미
  • PURGE 절이 지정되면 테이블 및 해당 데이터를 완전히 제거
  • 종속 객체 무효화 및 테이블의 객체 권한 제거
-- 테이블 삭제(휴지통에는 아직 있음)
DROP TABLE copy_emp ; 

-- 검색해도 안나옴
SELECT * FROM copy_emp; 

-- 휴지통 검색 시 copy_emp 검색됨
SHOW RECYCLEBIN

-- 휴지통에 있는 copy_emp를 다시 가져옴
FLASHBACK TABLE copy_emp TO BEFORE DROP ; 

-- copy_emp 테이블이 검색됨
SELECT * FROM copy_emp; 

-- 완전 제거됨
DROP TABLE copy_emp PURGE ;
profile
봄
이전 포스트

[SQL] DML(Data Manipulation Language, 데이터 조작어)

다음 포스트

[SQL] 데이터 딕셔너리 뷰

0개의 댓글