[데이터베이스] 9. SQL

SQL (Structured Query Language)에 관한 내용이다. 데이터베이스의 꽃이라고 할 수 있다. 그럼 시작!

---

1. SQL의 소개

1) SQL이란?

- 의미 : 관계 데이터베이스를 위한 표준 질의어이다. 비절차적 언어에 속한다.

- 사용 방식: 대화식 SQL, 삽입 SQL이 있다.

 

2) SQL의 분류

SQL 분류

 

- DDL : 테이블을 생성하고 변경 및 삭제하는 기능을 제공하는 언어이다.

- DML : 테이블에 새 데이터를 삽입하거나, 테이블에 저장된 데이터를 수정, 삭제, 검색하는 기능을 제공하는 언어이다.

- DCL : 데이터에 대한 접근 및 사용 권한을 사용자별로 부여하거나 취소하는 기능을 제공하는 언어이다.

---

2. SQL을 이용한 데이터 정의

데이터베이스 예시

1) 테이블 생성 (CREATE TABLE)

CREATE TABLE 테이블_이름 (
	속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본값] -- 1
    [PRIMARY KEY (속성_리스트)] -- 2
    [UNIQUE (속성_리스트)] -- 3
    [FOREIGN KEY (속성_리스트) REFERENCES 테이블_이름(속성_리스트)]
    [ON DELETE 옵션] [ON UPDATE 옵션] -- 4
    [CONSTRAINT 이름] [CHECK(조건)] -- 5
 ); -- []은 중복 가능

 

1) 테이블을 구성하는 테이블의 이름, 속성의 이름 및 데이터 타입, 기본값 정의

- NOT NULL : 널 값의 허용 여부, NOT NULL은 널 값을 허용하지 않는다는 것이다.

- DEFAULT : 속성의 기본값을 지정

2) 기본키 : PRIMARY KEY라는 키워드를 통해 기본키 지정

3) 대체키

4) 외래키 : FOREIGN KEY라는 키워드를 통해 외래키 지정

i. REFERENCES : 참조하는 테이블의 어떤 속성을 참조하는지 제시

ii. ON DELETE 옵션

> ON DELETE NO ACTION : 튜플 삭제를 못하게 함

> ON DELETE CASCADE : 관련 튜플을 함께 삭제

> ON DELETE SET NULL : 관련 튜플의 외래키 값을 NULL로 변경

> ON DELETE SET DEFAULT : 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경
iii. ON UPDATE 옵션

> ON UPDATE NO ACTION : 튜플 변경을 못하게 함

> ON UPDATE CASCADE : 관련 튜플을 함께 변경

> ON UPDATE SET NULL : 관련 튜플의 외래키 값을 NULL로 변경

> ON UPDATE SET DEFAULT : 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경

5) 제약 조건

- CHECK : 제약 조건 지정

 

2) 테이블 변경 (ALTER TABLE)

- 기존 속성 추가

: 새로운 속성을 추가할 수 있는 문이다.

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

 

- 기존 속성 삭제

ALTER TABLE 테이블_이름 DROP COLUMN 속성명;

 

- 새로운 제약조건 추가

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

 

- 기존 제약조건의 삭제

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

 

3) 테이블 삭제 (DROP TABLE)

DROP TABLE 테이블_이름;

 

- 삭제할 테이블을 참조하는 테이블이 있다면 테이블 삭제가 수행되지 않음

- 관련된 외래키 제약조건을 먼저 삭제해야 함

---

3. SQL을 이용한 데이터 조작

1) 데이터 검색 : SELECT 문

SELECT [ALL | DISTINCT] 속성_리스트
FROM 테이블_리스트;

 

- DISTINCT : 중복을 제외하여 검색

- ALL : 중복을 허용 (안 써도 무방)

- AS : 결과 테이블에서 속성의 이름을 바꾸어 출력 가능, 공백이 포함되어 있으면 따옴표(작은, 큰 둘 다 무방)로 묶어주어야 함.

- 산술식을 이용한 검색 : 산술 연산자와 같이 제시 -> 계산된 결과 값이 출력된다 (속성의 값이 변경되는 것은 아니다)

ex. 제품 테이블에서 제품명과 단가 속성을 검색하되, 단가에 500원을 더해 '조정 단가'라는 새 이름으로 출력
>> SELECT 제품명, 단가 + 500 AS "조정 단가" FROM 제품;

 

- 조건 검색 : WHERE 뒤에 비교 연산자와 논리 연산자 등을 이용하여 검색 조건 제시, 조건에서 문자나 날짜 값은 작은따옴표로 묶는다.

ex. 주문 테이블에서 apple 고객이 15개 이상 주문한 주문제품, 수량, 주문일자를 검색
> SELECT 주문제품, 수량, 주문일자 FROM 주문 WHERE '고객' = 'apple' AND 수량 >= 15;

 

- LIKE를 이용한 검색

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

%	0개 이상의 문자 (문자 내용과 개수는 상관 없음)
_	1개의 문자 (문자의 내용과는 상관 없음)

 

- 예시

LIKE '데이터%'	데이터로 시작하는 문자열 (데이터로 시작하기만 하면 길이는 상관 없음)
LIKE '%데이터'	데이터로 끝나는 문자열 (데이터로 끝나기만 하면 길이는 상관 없음)
LIKE '%데이터%'	데이터가 포함된 문자열
LIKE '데이터___'	데이터로 시작하는 6자 길이의 문자열
LIKE '__한%'	세 번째 글자가 한인 문자열

 

- NULL을 이용한 검색

i. IS NULL 키워드를 이용해 특정 속성의 값이 NULL 값인지를 비교

ii. IS NOT NULL 키워드를 이용해 특정 속성이 값이 NULL 값이 아닌 지를 비교

 

- ORDER BY를 이용한 검색

- GROUP BY를 이용한 검색

- JOIN을 이용한 검색

i. INNER JOIN

ii. LEFT | RIGHT | FULL OUTER JOIN

 

- 부속 질의문을 이용한 검색

2. 데이터 삽입 : INSERT 문

INSERT
INTO 테이블_이름 [(속성_리스트)]
VALUES (속성값_리스트);

ex. 판매 데이터베이스의 고객 테이블에 고객 아이디가 strawberry, 고객 이름이 최유경, 나이가 30세, 등급이 vip, 직업이 공무원, 적립금이 100원인 새로운 고객의 정보를 삽입하자.

INSERT
INTO 고객
VALUES ('strawberry', '최유경', 30, 'vip', '공무원', 100);

3. 데이터 수정 : UPDATE 문

UPDATE 테이블_이름
SET 속성_이름1 = 값1, 속성_이름2 = 값2
[WHERE 조건];

4. 데이터 삭제 : DELETE 문

DELETE
FROM 테이블_이름
[WHERE 조건];

---

4. 뷰

1. 뷰란?

- 뷰는 다른 테이블을 기반으로 만들어진 가상 테이블이다. 즉, 데이터를 실제로 저장하지 않고 논리적으로만 존재하는 테이블이다.

- 일반 테이블과 동일한 방법으로 사용한다. 하지만, 기본 테이블의 내용을 바꾸는 작업은 제한적으로 이루어진다.

* 기본 테이블 : 뷰를 만드는데 기반이 되는 물리적인 테이블

- 다른 뷰를 기반으로 새로운 뷰를 만드는 것도 가능하다.

2. 뷰 생성 : CREATE VIEW 문

CREATE VIEW 뷰_이름[(속성_리스트)]-- 뷰의 이름과 속성 나열
AS SELECT 문 -- AS 키워드와 함께 기본 테이블에 대한 SELECT문 제시
[WITH CHECK OPTION]; -- 뷰에 삽입이나 수정 연산을 할 때 SELECT문에 제시한 뷰의 정의 조건을 어기면 수행 X

 

이것도 예시를 한 번 들어봅시다.

고객 테이블에서 등급이 vip인 고객의 고객아이디, 고객이름, 나이, 등급으로 구성된 뷰를 우수고객이라는 이름으로 생성해보자.

CREATE VIEW 우수고객(고객아이디, 고개이름, 나이, 등급)
AS SELECT 고객아이디, 고객이름, 나이, 등급
   FROM 고객
   WHERE 등급 = 'vip'
WITH CHECK OPTION;

 

또 한 번 예시를 들어봅시다.

제품 테이블에서 제조업체별 제품수로 구성된 뷰를 업체별제품수라는 이름으로 생성해보자.

CREATE VIEW 업체별제품수(제조업체, 제품수)
AS SELECT 제조업체, COUNT(*)
	FROM 제품
    GROUP BY 제품업체
WITH CHECK OPTION;

3. 뷰 활용 : SELECT 문

- 일반 테이블과 같은 방버으로 원하는 데이터를 검색할 수 있음

- 검색 연산은 모든 뷰에서 수행 가능

4. 뷰 활용 : INSERT, UPDATE, DELETE문

- 뷰에 대한 삽입, 삭제 연산 가능. 실제로 기본 테이블에 수행되므로 결과적으로는 기본 테이블이 변경됨

- 뷰에 대한 삽입, 삭제 연산은 제한적으로 수행됨 (변경 가능한 뷰가 있고 불가능한 뷰가 있다.)

 

그렇다면, 변경 불가능한 뷰는 뭘까? 조건은 다음과 같다.

1. 기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 뷰
2. 기본 테이블에서 NOT NULL로 지정된 속성이 포함되어 있지 않은 뷰
3. 기본 테이블에 있던 내용이 아닌 집계 함수로 새로 계산된 내용을 포함하는 뷰
4. DISTINCT 키워드를 포함하여 정의한 뷰
5. GROUP BY 절을 포함하여 정의한 뷰
6. 여러 개의 테이블을 조인하여 정의한 뷰

변경 불가능한 뷰이다. 기본키 속성이 포함되어 있지 않기 때문이다.

5. 뷰의 장점

1) 질의문을 좀 더 쉽게 작성할 수 있다.

: GROUP BY, 집계 함수, 조인 등을 이용해 미리 뷰를 만들어 놓으면, 복잡한 SQL 문 대신 SELECT 절과 FROM 절만으로 원하는 데이터 검색이 가능하다.

2) 데이터의 보안 유지에 도움이 된다.

: 자신에게 제공된 뷰를 통해서만 데이터에 접근하도록 권한 설정이 가능하다.

3) 데이터를 좀 더 편리하게 관리할 수 있다.

6. 뷰 삭제 : DROP VIEW문

DROP VIEW 뷰_이름 -- 뷰를 삭제해도 기본 테이블에 영향을 주지 않는다

 

그런데 만약, 삭제할 뷰를 참조하는 제약조건이 존재한다면?

→ 뷰 삭제가 수행되지 않는다. 따라서, 관련된 제약조건을 먼저 삭제해야 한다.

---

끝!