SQL (Structured Query Language) - DDL 총정리

방식

• 대화식 SQL : workbench, datagrip 등에서 직접 DBMS에 접근해 질의를 작성하여 실행
• 삽입 SQL : python, java 등의 언어로 작성된 application에 들어가서 DB와 소통

분류

• 데이터 정의어 DDL (Data Define Language) : 테이블 생성 / 변경 / 제거에 사용하는 명령어
• 데이터 조작어 DML (Data Manipulate Language) : 데이터 삽입 / 삭제 / 변경/ 수정/ 검색에 사용하는 명령어
• 데이터 제어어 DCL (Data Controll Language) : 데이터에 접근 권한 부여 및 취소하는 명령어

 

데이터 정의어 DDL

테이블 생성 CREATE TABLE

CREATE TABLE 상품(
	고유번호 VARCHAR(10) NOT NULL,
	상품명 VARCHAR(20) NOT NULL,
    	상품부제목 VARCHAR(20),
    	가격 INT DEFAULT 0,
    	재고량 INT DEFAULT 0,
    	PRIMARY KEY(고유번호), # 기본키
        UNIQUE(상품명, 상품부제목) # 대체키
    	CHECK (재고량>=0 AND 재고량<=1000) # 제약조건
);

• NOT NULL : 속성이 아무것도 없는 NULL 값을 혀용하지 않음 - 제약사항
• DEFAULT : 레코드가 생겼을 때, 기본으로 어떠한 값이 들어갈지
• PRIMARY KEY : 기본키를 지정 - 괄호 안에 들어가는 속성이 기본키
• UNIQUE : 대체키를 지정 - 괄호 안에 들어가는 속성이 대체키 - NULL 허용 (대체키가 없을 수도 있으니까!)

데이터 무결성을 위한 제약조건 정의

테이블의 속성에 정확하고 유효한 데이터 삽입 / 유지 하기 위해 제약조건을 지정

두가지 키워드를 통해 제약 조건 선정 가능

• CHECK 

CHECK(재고량>= 0 AND 재고량<=10000)

• CONSTRAINT

CONSTRAINT 제약조건_이름 CHECK(재고량>=0 AND 재고량<=10000)

 

 

CREATE TABLE 주문(
	주문번호 VARCHAR(20) NOT NULL,
    	결제수단 VARCHAR(10) NOT NULL,
    	고객아이디 VARCHAR(10), # 참조하는 고객 기본키
    	상품고유번호 VARCHAR(10), # 참조하는 상품 기본키
        수량 INT,
        배송지 VARCHAR(30),
        주문일자 DATE,
    	PRIMARY KEY(주문번호),
    	FOREIGN KEY(고객아이디) REFERENCES 고객(고객아이디) # 외래키 선언
)

하나의 고객은 여러개의 주문을 할 수 있고 한개의 상품에 대해서도 여러개의 주문이 존재할 수 있다.

주문 table을 통해 고객과 상품 과의 N:M관계를 표현할 수 있다

고객 : 주문 = 1 : N  /  상품 : 주문 = 1 : N

주문은 두 관계에서 모두 N쪽에 있으므로 [고객아이디]와 [상품고유번호]를 속성으로 가진다

테이블 정의 시에 두 속성에 대해 외래키 선언을 하여서 어떤 테이블을 각각 참조하고 있는지 알려주어야 한다.

 

 

외래키  ON DELETE / UPDATE옵션

참조가 되는 테이블에서 해당 튜플이 삭제 혹은 수정 시, 처리 방법을 지정

• NO ACTION (디폴트) : 튜플을 삭제 혹은 변경하지 못하게 함
• CASCADE : 관련 튜플을 삭제 / 변경 같이 - 폭포라는 뜻으로 폭포 흐르듯이 같이 바꾸라
• SET NULL : 관련 튜플이 삭제될 시에 NULL 값으로 납두고 유지 - ex) 상품 튜플이 사라져도 주문은 남아있는 시스템
• SET DEFAULT : 관련 튜플의 외래키 값을 사전에 지정한 디폴트 값으로 변경 - ex) 상품 튜플이 삭제되었을 때, 기본 상품으로 주문을 연결시킴

FOREIGN KEY(상품고유번호) REFERENCES 상품(고유번호) ON DELETE CASCADE

 

 

---

 

테이블 변경 ALTER TABLE

속성 추가 ADD

ALTER TABLE 주문 ADD 수령인 VARCHAR(10);

 

기존 속성 삭제 DROP COLUMN

ALTER TABLE 주문 DROP COLUMN 수령인 [CASCADE|RESTRICT];

• RESTRICT : 참조하는 무언인가가 있다면 속성 삭제가 수행되지 않음
• CASCADE  : 관련된 제약조건은 모두 삭제

 

제약 조건 추가 ADD CONSTRAINT

ALTER TABLE 상품 ADD CONSTRAINT CHK_REST CHECK(재고>=0 AND 재고<=10000);

 

제약 조건 삭제 DROP CONSTRAINT

ALTER TABLE 상품 DROP CONSTRAINT CHK_REST;

 

 

테이블 삭제 DROP TABLE

DROP TABLE 주문 [CASCADE | RESTRICT];

RESTRICT : 삭제할 테이블을 참조하는 테이블이 있는 경우 삭제가 진행되지 않음

CASCADE : 관련테이블까지 모두 삭제

 

 

---