[데이터베이스] (7) SQL ( DDL / DML / DCL )

CS/데이터베이스

[데이터베이스] (7) SQL ( DDL / DML / DCL )

dltjdn 2022. 3. 19. 17:05

< SQL ( Structed Query Language ) >

관계 데이터베이스를 위한 표준 질의어

대화식 SQL : 데이터베이스 관리 시스템에 직접 접근해 질의를 작성하며 실행 ( SQL문만 집어넣고 실행하면 DB에서 가져옴)
삽입 SQL : 프로그래밍 언어 안에 SQL문 넣어 응용프로그램 작성

SQL의 분류

1. 데이터 정의어 ( DDL = Data Definition Language )

테이블을 생성하고 변경, 제거하는 기능 제공

※ DBMS를 실제 물리적으로 구현할때는 릴레이션(수학적인 데이터모델에서 사용하는 용어)을 테이블이라 표현

※ 릴레이션은 튜플의 순서가 없지만, 테이블은 튜플의 순서가 있다

2. 데이터 조작어 ( DML = Data Manipulation Language )

테이블에 데이터를 삽입, 삭제, 수정, 검색하는 기능 제공

SQL은 대부분 데이터 조작어이고, 데이터 조작어는 대부분 검색 기능을 수행한다

3. 데이터 제어어 ( DCL = Data Control Language )

보안을 위해 데이터에 대한 접근 및 사용 권한을 사용자별로 부여하거나 취소하는 기능 제공 ( 전체 데이터 권한 제어 )

< 데이터 정의어 ( DDL ) >

테이블 생성 : CREATE TABLE 문

[ ]의 내용은 생략 가능
SQL 질의문은 세미콜론(;)으로 문장의 끝을 표시, 대소문자 구분 X

CREATE TABLE 테이블_이름(

1. 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값]

2. [PRIMARY KEY ( 속성_리스트 ) ]

3. [UNIQUE ( 속성_리스트 )]

4. [FOREIGN KEY (속성_리스트) REFERENCES 테이블_이름 (속성_리스트)] [ON DELETE 옵션] [ON UPDATE 옵션]

5. [CONSTRAINT 이름] [CHECK(조건)]

);

ex)

CREATE_TABLE 제품 (

제품번호 CHAR(3) NOT NULL,

제품명 VARCHAR(20),

재고량 INT,

제조업체 VARCHAR(20),

PRIMARY KEY(제품번호),

FOREIGN KEY(주문고객) REFERENCES 고객(고객아이디),

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

);

1. 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값]

- 속성의 정의

각 속성의 이름과 데이터 타입 결정
NOT NULL : 속성이 NULL값을 허용하지 않음 ex) 고객아이디 VARCHAR(20) NOT NULL
DEFAULT : 속성의 기본 값 지정 ex) 적립금 INT DEFAULT 0 ex) 담당자 VARCHAR(10) DEFAULT '김땡땡'

※ 문자열, 날짜 데이터는 작은 따옴표로 묶어서 표현 ( 작은 따옴표로 묶어진 문자열은 대소문자를 구분)

- 주요 데이터 타입

INT, INTEGER : 정수
CHAR(n) : 길이가 n인 고정 길이의 문자열
VARCHAR(n) : 최대 길이가 n인 가변 길이의 문자열
DECIMAL(p,s) : 고정(소수점 위 아래 숫자 정해져있음) 소수점 실수, p는 소수점 제외한 전체 숫자 길이이고 s는 소수점 이하 숫자의 길이 ex) 123.45 이면 p는 5이고 s는 2이다
DATE : 연, 원, 일로 표현되는 날짜
TIME : 시, 분, 초로 표현되는 시간
DATETIME : 날짜와 시간, DATE와 TIME 합친 것

2. [ PRIMARY KEY ( 속성_리스트 ) ]

- 기본키를 지정

ex) PRIMARY KEY(고객아이디) ex) PRIMARY KEY(주문고객, 주문제품)

3. [ UNIQUE ( 속성_리스트 ) ]

- 대체키를 지정

- 대체키로 지정되는 속성의 값은 유일성을 가지며 기본키와 달리 NULL 허용된다

ex) UNIQUE(고객이름)

4. [ FOREIGN KEY (속성_리스트) REFERENCES 테이블_이름 (속성_리스트)] [ON DELETE 옵션] [ON UPDATE 옵션 ]

- 외래키를 지정

- 외래키가 어떤 테이블의 무슨 속성을 참조하는지 REFERENCES 키워드 다음에 제시

- ON DELETE 옵션, ON UPDATE 옵션 : 참조 무결성 제약조건 유지를 위해 참조되는 테이블에서 튜플 삭제 혹은 변경시 처리 방법을 지정하는 옵션

NO ACTION ( 디폴트 ) : 튜플을 삭제 혹은 변경하지 못하게 함
CASCADE : 관련 튜플을 함께 삭제 혹은 변경함
SET NULL : 관련 튜플의 외래키 값을 NULL로 변경 ( NULL로 변경한다고 참조무결성 조건 위배하지 않는다 )
SET DEFAULT : 관련 튜플의 외래키 값을 미리 지정한 기본값으로 변경함 c.f) 지원하지 않는 DBMS가 많다

ex) FOREIGN KEY(소속부서) REFERENCES 부서(부서번호) ON DELETE CASCADE

5. [CONSTRAINT 이름] [CHECK(조건)]

- 테이블에 정확하고 유효한 데이터를 유지하기 위해 특정 속성에 대한 제약조건을 지정

- CONSTRAINT 키워드와 함께 제약조건에 이름을 부여할 수도 있음

ex) CHECK ( 재고량 >= 0 AND 재고량 <= 1000 )

ex) CONSTRAINT CHK_CPY CHECK ( 제조업체 = '한빛제과' ) → CHECK ( 제조업체 = '한빛제과' )의 이름은 CHK_CPY

테이블 변경 : ALTER TABLE 문

1. 새로운 속성 추가

ALTER TABLE 테이블_이름 ADD 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값];

ex) ALTER TABLE 고객 ADD 가입날짜 DATE;

2. 기존 속성 삭제

ALTER TABLE 테이블_이름 DROP COLUMN 속성_이름 [ CASCADE | RESTRICT ] ;

삭제할 속성과 관련된 제약조건이 존재하는 경우

→ RESTRICT : 속성 삭제가 수행되지 않음 / CASCADE : 관련된 제약 조건도 모두 삭제함

ex) ALTER TABLE 고객 DROP COLUMN 가입날짜;

3. 새로운 제약조건의 추가

ALTER TABLE 테이블_이름 ADD CONSTRAINT 제약조건_이름 제약조건_내용;

ex ) ALTER TABLE 고객 ADD CONSTRAINT CHK_AGE CHECK(나이 >= 20);

4. 기존 제약조건의 삭제

ALTER TABLE 테이블_이름 DROP CONSTRAINT 제약조건_이름;

※ 제약조건의 이름 없으면 DROP 못한다

테이블 삭제 : DROP TABLE 문

DROP TABLE 테이블_이름 [CASCADE | RESTRICT ];

삭제할 테이블을 참조하는 테이블이 있는 경우

→ RESTRICT : 테이블 삭제가 수행되지 않음 ( 삭제되지 않는 원인인 테이블을 해결하고 시도해야 함)

/ CASCADE : 관련된 테이블도 모두 삭제함

데이터 조작어 ( DML )

데이터 검색 : SELECT

1. 기본 검색

SELECT [ ALL | DISTINCT ] 속성_리스트

FROM 테이블_리스트;

SELECT : 검색하고 싶은 속성의 이름을 나열

ALL ( 디폴트 ) : 결과 테이블이 튜플의 중복을 허용하도록 지정
DISTINCT : 결과 테이블이 튜플의 중복을 허용하지 않도록 지정

FROM : 검색하고 싶은 속성이 있는 테이블의 이름을 나열

ex)

SELECT 제조업체

FROM 제품;

→ 제조업체가 중복됨

SELECT DISTINCT 제조업체

FROM 제품;

→ 제조업체 중복이 제거됨

ex2)

SELECT 제품명, 단가+500 AS "조정 단가"

FROM 제품;

※ AS는 결과 테이블에서 속성 이름을 변경하여 출력

2. 조건 검색

조건을 만족하는 데이터만 검색

SELECT [ALL | DISTINCT] 속성_리스트

FROM 테이블_리스트

[WHERE 조건];

WHERE : 비교 연산자와 논리 연산자를 이용한 검색 조건 제시

- 숫자뿐만 아니라 문자나 날짜 값을 비교하는 것도 가능

- 문자나 날짜 값은 작은 따옴표로 묶어서 표현

ex) '2022-01-14' < '2022-01-16'

비교 연산자 : =(같다) , <>(다르다), <, >, <=, >=
논리 연산자 : AND, OR, NOT

ex)

SELECT 제품명, 단가, 제조업체

FROM 제품

WHERE 단가>=2000 AND 단가<=3000;

3. LIKE를 이용한 검색

LIKE 키워드를 이용해 부분적으로 일치하는 데이터를 검색

문자열을 이용하는 조건에만 LIKE 키워드 사용 가능

% : 0개 이상의 문자 ( 문자의 개수는 상관 X )
- : 1개의 문자
ex) LIKE '데이터%' : 데이터로 시작하는 문자열 ( 데이터로 시작하기만 하면 길이는 상관 없음 )
ex2) LIKE '데이터_ _ _' : 데이터로 시작하는 6자 길이의 문자열

ex)

SELECT 고객아이디, 고객이름, 등급

FROM 고객

WHERE 고객아이디 LIKE '_ _ _ _ _';

4. NULL을 이용한 검색

IS NULL : 특정 속성의 값이 NULL 인지 비교

IS NOT NULL : 특정 속성의 값이 NULL 이 아닌지 비교

검색 조건에서 NULL 과 다른 값의 크기를 비교하면 모두 거짓이 됨

ex)

SELECT 고객이름

FROM 고객

WHERE 나이 IS NULL;

5. 정렬 검색

SELECT [ALL | DISTINCT ] 속성_리스트

FROM 테이블_리스트

[ WHERE 조건 ]

[ ORDER BY 속성_리스트 [ASC | DESC ] ];

ORDER BY : 결과 테이블 내용을 사용자가 원하는 순서로 출력

오름차순(디폴트) : ASC / 내림차순 : DESC
NULL = 제일 큰 값 취급 → 오름차순에서는 맨 마지막, 내림차순에서는 맨 먼저 출력
정렬 기준이 되는 속성을 여러 개 적으면 맨 왼쪽부터 여러 기준에 따라 정렬 가능

ex)

SELECT 주문고객, 주문제품, 수량, 주문일자

FROM 주문

WHERE 수량>=10

ORDER BY 주문제품 ASC, 수량 DESC;

6. 집계 함수를 이용한 검색

특정 속성 값을 통계적으로 계산한 결과 검색 ( 개수, 합계, 평균, 최댓값, 최솟값의 계산 기능을 제공 )

집계 함수는 NULL 속성 값은 제외하고 계산함

SELECT 절이나 HAVING 절에서만 사용 가능 ( WHERE 절에서는 사용할 수 X )

집계함수

COUNT : 속성 값의 개수
MAX : 속성 값의 최댓값
MIN : 속성 값의 최솟값
SUM : 속성 값의 합계
AVG : 속성 값의 평균

※ COUNT, MAX, MIN은 모든 데이터에서 사용가능하지만 SUM,AVG는 숫자 데이터에만 사용 가능하다

ex)

SELECT AVG(단가)

FROM 제품;

ex2)

SELECT COUNT(DISTINCT 제조업체)

FROM 제품;

→ DISTINCT 키워드를 이용해 중복을 없애고 서로 다른 제조업체의 수만 계산

ex3)

SELECT COUNT(고객아이디)

FROM 제품;

SELECT COUNT(*)

FROM 제품;

→ COUNT(기본키)는 COUNT(*)과 같다

7. 그룹별 검색

SELECT [ALL | DISTINCT ] 속성_리스트

FROM 테이블_리스트

[WHERE 조건]

[GROUP BY 속성_리스트 [HAVING 조건]]

[ORDER BY 속성_리스트 [ASC | DESC]];

GROUP BY : 특정 속성의 값이 같은 튜플을 모아 그룹을 만들고 그룹별로 검색 ( 그룹을 나누는 기준이 되는 속성 지정 )

HAVING : 그룹에 대한 조건 c.f) WHERE과 유사

ex)

SELECT 주문제품, SUM(수량)

FROM 주문

GROUP BY 주문제품;

→ GROUP BY 속성리스트는 반드시 SELECT 문에 포함되어야 한다!

ex2 )

SELECT 제조업체, COUNT(*), MAX(단가)

FROM 제품

GROUP BY 제조업체 HAVING COUNT(*)>=3;

ex3)

SELECT 주문제품, 주문고객, SUM(수량)

FROM 주문

GROUP BY 주문제품, 주문고객;

8. 조인 검색

여러 개의 테이블을 연결하여 데이터를 검색하는 것

조인속성 : 조인 검색을 위해 테이블을 연결해주는 속성
연결하려는 테이블 간에 도메인은 같아야 함 ( 이름은 달라도 된다! )
일반적으로 외래키( 다른 테이블에선 기본키)를 조인 속성으로 이용함
FROM : 검색에 필요한 모든 테이블을 나열
WHERE : 조인 속성의 값이 같아야 함을 의미하는 조인 조건 제시
같은 이름의 속성이 서로 다른 테이블에 존재할 수 있기 때문에 속성 이름 앞에 테이블의 이름도 표시 ex)주문.주문고객

ex)

SELECT 제품.제품명

FROM 제품, 주문

WHERE 주문.주문고객 = 'banana' AND 제품.제품번호 = 주문.주문제품;

→ 조인 연산 1번

ex2)

SELECT 제품.제품명

FROM 고객, 제품, 주문

WHERE 고객.고객이름 = '고명석' AND 고객.고객아이디=주문.주문고객 AND 제품.제품번호=주문.주문제품;

→ 조인 연산 2번 because 고객테이블과 제품테이블은 외래키가 없기 때문에 조인 불가

9. 부속 질의문을 이용한 검색

SELECT 문 안에 또 다른 SELECT 문을 포함하는 질의문

부속 질의문 먼저 수행하고 그 결과 이용해 상위 질의문 수행

- 상위 질의문 ( 주 질의문 ) : 다른 SELECT 문을 포함하는 SELECT

- 부속 질의문 ( 서브 질의문 ) : 다른 SELECT 문 안에 들어 있는 SELECT문

단일 행 부속 질의문 : 하나의 행을 결과로 반환 → 비교 연산자(=,<> ...) 사용 가능
다중 행 부속 질의문 : 둘 이상의 행을 결과로 반환

※ 괄호로 묶어서 작성

※ ORDER BY 절 사용 불가 because 출력하는 것이 아니라 결과만 다시 이용할 것이기 때문

ex) 단일 행 부속 질의문

다중 행 부속 질의문에 사용 가능한 연산자

IN : 부속 질의문의 결과 값 중 일치하는 것이 있으면 참
NOT IN : 부속 질의문의 결과 값 중 일치하는 것이 없으면 참
EXISTS : 부속 질의문의 결과 값이 하나라도 존재하면 참
NOT EXISTS : 부속 질의문의 결과 값이 하나도 존재하지 않으면 참
ALL : 부속 질의문의 결과 값 모두와 비교한 결과가 참이면 참 ( 비교 연산자와 함께 사용 )
ANY or SOME : 부속 질의문의 결과 값 중 하나라도 비교한 결과가 참이면 참 ( 비교 연산자와 함께 사용)

ex) 다중 행 부속 질의문

ex2) 다중 행 부속 질의문

SELECT 고객이름

FROM 고객

WHERE EXISTS ( SELECT *

FROM 주문

WHERE 주문일자='2020-01-14' AND 주문.주문고객 = 고객.고객아이디 );

→ 조인으로도 가능

ex3) 다중 행 부속 질의문

SELECT 제품명, 단가, 제조업체

FROM 제품

WHERE 단가 > ALL ( SELECT 단가

FROM 제품

WHERE 제조업체='대한식품' );

10. 질의 내용은 다양하게 표현가능하므로 선택해서 사용

ex ) 아래 세 가지는 같다

1. 조인 질의 이용

SELECT 제품.제품명, 제품.제조업체

FROM 제품, 주문

WHERE 제품.제품번호 = 주문.주문제품 AND 주문.주문고객 = 'banana';

2. EXISTS 사용한 부속질의문 이용

SELECT 제품명, 제조업체

FROM 제품

WHERE EXISTS ( SELECT *

FROM 주문

WHERE 제품.제품번호=주문.주문제품 AND 주문고객='banana');

3. IN 사용한 부속질의문 이용

SELECT 제품명, 제조업체

FROM 제품

WHERE 제품번호 IN ( SELECT 주문제품

FROM 주문

WHERE 주문고객='banana');

데이터 삽입 : INSERT

1. 데이터 직접 삽입

INSERT

INTO 테이블_이름 [ (속성_리스트) ]

VALUES (속성값_리스트)

INTO : 튜플 삽입할 테이블의 이름과 속성의 이름 나열 → 속성 리스트를 생략하면 테이블을 정의할 때 지정한 속성의 순서대로 값이 삽입된다

VALUES : 삽입할 속성 값들을 나열

INTO 절의 속성리스트와 VALUES 절의 속성값 리스트는 순서대로 일대일 대응되어야 함

ex) 위와 아래 질의문은 같다

INSERT

INTO 고객 (고객아이디, 고객이름, 나이, 등급, 직업, 적립금)

VALUES ('strawberry', '박땡땡', '30', 'vip', '공무원', 100 );

INSERT

INTO 고객

VALUES ('strawberry', '박땡땡', '30', 'vip', '공무원', 100 );

ex2)

INSERT

INTO 고객 (고객아이디, 고객이름, 나이, 등급, 적립금)

VALUES ('strawberry', '박땡땡', '30', 'vip', 100 );

INSERT

INTO 고객

VALUES ('strawberry', '박땡땡', '30', 'vip', NULL, 100 );

2. 부속 질의문을 이용한 데이터 삽입

SELECT 문을 이용해 다른 테이블에서 검색한 데이터 삽입

INSERT

INTO 테이블_이름[ (속성_리스트) ]

SELECT문;

ex)

INSERT

INTO 한빛제품 ( 제품명, 재고량, 단가)

SELECT 제품명, 재고량, 단가

FROM 제품

WHERE 제조업체 = '한빛제과';

데이터 수정 : UPDATE

테이블에 저장된 튜플에서 특정 속성의 값을 수정

UPDATE 테이블_이름

SET 속성_이름1 = 값1, 속성_이름2 = 값2, ...

[WHERE 조건];

SET : 속성 값을 어떻게 수정할 것인지 지정

WHERE 절에 제시된 조건을 만족하는 튜플에 대해서만 속성 값을 수정

→ WHERE 절을 생략하면 테이블에 존재하는 모든 튜플을 대상으로 수정

ex)

UPDATE 제품

SET 제품명='통근파이'

WHERE 제품번호='p03';

ex2)

UPDATE 주문

SET 수량=5

WHERE 주문고객 IN ( SELECT 고객아이디

FROM 고객

WHERE 고객이름='김땡땡' );

※ UPDATE나 DELETE 에서 테이블이 2개 이상 연관될때는 대상이 되는 테이블은 하나이므로 조인이 불가능하여 부속 질의문 써야한다

데이터 삭제 : DELETE

테이블에 저장된 데이터를 삭제

DELETE

FROM 테이블_이름

[WHERE 조건];

WHERE 절에 제시된 조건을 만족하는 튜플만 삭제

→ WHERE 절을 생략하면 테이블에 존재하는 모든 튜플을 삭제해 빈 테이블이 됨

ex)

DELETE

FROM 주문

WHERE 주문일자 = '2022-01-14';

ex 2)

DELETE

FROM 주문

WHERE 주문고객 IN ( SELECT 고객아이디

FROM 고객

WHERE 고객이름='김땡땡');

※ UPDATE나 DELETE 에서 테이블이 2개 이상 연관될때는 대상이 되는 테이블은 하나이므로 조인이 불가능하여 부속 질의문 써야한다