7장 데이터베이스 언어 SQL

reference: 데이터베이스 개론 2판

01 SQL의 소개

01-1 의미

관계 데이터베이스를 위한 표준 질의어
비절차적 데이터 언어

01-2 발전 역사

SEQUEL(Structured English QUEry Language)에서 유래
- SEQUEL : 연구용 관계 데이터베이스 관리 시스템인 SYSTEM R을 위한 언어
미국 표준 연구소인 ANSI와 국제 표준화 기구인 ISO에서 표준화 작업을 진행, 계속 수정 및 보완되고 있음

01-3 사용 방식

대화식 SQL : 데이터베이스 관리 시스템(DBMS - 수업에서는 mssql)에 직접 접근해 질의를 작성하여 실행
삽입 SQL : 프로그래밍 언어로 작성된 응용 프로그램에 삽입

01-4. 분류

데이터 정의어(DDL): 테이블을 생성하고 변경, 제거하는 기능을 제공

데이터 조작어(DML): 테이블에 새 데이터를 삽입하거나, 테이블에 저장된 데이터를 수정, 삭제,검색 하는 기능을 제공

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

02 SQL을 이용한 데이터 정의

02-1. 테이블 생성

CREATE TABLE 테이블이름 (
    ① 속성이름 데이터타입 [NOT NULL] [DEFAULT 기본값]
    ② [PRIMARY KEY (속성리스트)]
    ③ [UNIQUE (속성리스트)]
    ④ [FOREIGN KEY (속성리스트) REFERENCES 테이블이름(속성리스트)] [ON DELETE 옵셥] [ON UPDATE 옵션]
    ⑤ [CONSTRAIN 이름] [CHECK(조건)]
)

CREATE TABLE 문

[ ]의 내용은 생략이 가능
SQL 질의문은 세미콜론(;)으로 문장의 끝을 표시
SQL 질의문은 대소문자를 구분하지 않음
① : 테이블을 구성하는 각 속성의 이름, 데이터 타입, 기본 제약 사항 정의
② : 기본키 정의
③ : 대체키 정의
④ : 외래키 정의
⑤ : 데이터 무결성을 위한 제약조건 정의

속성의 정의

테이블을 구성하는 각 속성의 데이터 타입을 선택한 다음 널 값 허용 여부와 기본 값 필요 여부를 결정
NOT NULL
- 속성이 널 값을 허용하지 않음을 의미하는 키워드
- 예) 고객아이디 VARCHAR(20) NOT NULL
DEFAULT
- 속성의 기본 값을 지정하는 키워드
- 예) 적립금 INT DEFAULT 0
- 예) 담당자 VARCHAR(10) DEFAULT '방경아'
- 문자열이나 날짜 데이터는 작은 따옴표로 묶어서 표현 (작은 따옴표로 묶여진 문자열은 대소문자를 구분함)

속성의 데이터 타입

키의 정의

PRIMARY KEY
- 기본키를 지정하는 키워드
- 예) PRIMARY KEY(고객아이디)
- 예) PRIMARY KEY(주문고객, 주문제품)
UNIQUE
- 대체키를 지정하는 키워드
- 대체키로 지정되는 속성의 값은 유일성을 가지며 기본키와 달리 널 값이 허용됨
- 예) UNIQUE(고객이름)
FOREIGN KEY
- 외래키를 지정하는 키워드
- 외래키가 어떤 테이블의 무슨 속성을 참조하는지 REFERENCES 키워드 다음에 제시
- 참조 무결성 제약조건 유지를 위해 참조되는 테이블에서 투플 삭제 시 처리 방법을 지정하는 옵션
  - ON DELETE CASCADE: 관련 투플을 함께 삭제함
  - ON DELETE SET DEFAULT: 관련 투플의 외래키 값을 미리 지정한 기본 값으로 변경함
  - ON DELETE SET NULL: 관련 투플의 외래키 값을 NULL로 변경함
  - ON DELETE NO ACTION: 투플을 삭제하지 못하게 함
- 참조 무결성 제약조건 유지를 위해 참조되는 테이블에서 투플 변경 시 처리 방법을 지정하는 옵션
  - ON UPDATE CASCADE : 관련 투플에서 외래키 값을 함께 변경함
  - ON UPDATE SET DEFAULT : 관련 투플의 외래키 값을 미리 지정한 기본 값으로 변경함
  - ON UPDATE SET NULL : 관련 투플의 외래키 값을 NULL로 변경함
  - ON UPDATE NO ACTION: 투플을 변경하지 못하게 함
- 예) FOREIGN KEY(소속부서) REFERENCES 부서(부서번호) ON DELETE CASCADE ON UPDATE CASCADE
- 예) FOREIGN KEY(소속부서) REFERENCES 부서(부서번호)

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

CHECK
- 테이블에 정확하고 유효한 데이터를 유지하기 위해 특정 속성에 대한 제약조건을 지정
- CONSTRAINT 키워드와 함께 고유의 이름을 부여할 수도 있음
- 예) CHECK(재고량 >= 0 AND 재고량 <= 10000)
- 예) CONSTRAINT CHK_CPY CHECK(제조업체 = ‘한빛제과’)

02-2. 테이블 변경

새로운 속성 추가

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

예제: 'customer' 테이블에 '가입날짜' 속성을 추가해보자
ALTER TABLE customer 
ADD 가입날짜 DATE;

기존 속성 삭제

ALTER TABLE table_name
DROP COLUMN 속성이름;

만약 삭제할 속성과 관련된 제약 조건이 존재한다면?

속성 삭제가 수행되지 않음
관련된 제약 조건을 먼저 삭제해야 함

예제: 'customer' 테이블에 '가입날짜' 속성을 삭제해보자
ALTER TABLE customer 
DROP COLUMN 가입날짜;

새로운 제약 조건의 추가

ALTER TABLE table_name
ADD CONSTRAINT 제약조건이름 제약조건내용;

예제: 'customer' 테이블에 `20세 이상의 고객만 가입할 수 있다`는 데이터 무결성 제약조건을 추가해보자
ALTER TABLE customer
ADD CONSTRAINT check_age CHECK(20<=age);

기존 제약 조건의 삭제

ALTER TABLE table_name
DROP CONSTRAINT 제약조건이름;

예제: 'customer' 테이블에서 위에서 추가했던 데이터 무결성 제약조건을 삭제해보자
ALTER TABLE customer
DROP CONSTRAINT check_age;

02-3. 테이블 삭제

DROP TABLE 테이블이름;

만약 삭제할 테이블을 참조하는 테이블이 있다면?

테이블 삭제가 수행되지 않음.
관련된 외래키 제약조건을 먼저 삭제해야 함.

예제: 'customer' 테이블을 삭제해보자

DROP TABLE customer;

03 SQL을 이용한 데이터 조작

03-1 데이터 검색

검색 결과는 테이블 형태로 반환된다.

SELECT [ ALL | DISTINCT ] 속성리스트
FROM 테이블리스트
[ WHERE 조건 ]
[ GROUP BY 속성리스트 [ HAVING 조건 ] ]
[ ORDER BY 속성리스트 [ ASC | DESC ] ];

ALL | DISTINCT

ALL: 결과 테이블이 투플의 중복을 허용하도록 지정, 생략 가능
DISTINCT: 결과 테이블이 투플의 중복을 허용하지 않도록 지정
- ```
SELECT DISTINCT 제조업체 FROM 제품
```

모든 속성을 검색할 때는 모든 속성의 이름을 나열하지 않고 * 사용 가능
- ```
SELECT * FROM 제품
```

AS

AS 키워드는 생략 가능

새로운 이름에 공백이 포함되어 있으면 큰따옴표나 작은따옴표로 묶어주어야 함
오라클에서는 큰따옴표, MS SQL 서버에서는 작은따옴표 사용
AS 키워드를 이용해 결과 테이블에서 속성의 이름을 바꾸어 출력 가능

SELECT 제품명, 단가 AS 가격 FROM 제품

산술식 사용 가능

산술식: 속성의 이름과 +, -, *, / 등의 산술 연산자와 상수로 구성
속성의 값이 실제로 변경되는 것은 아니고 결과 테이블에서만 계산된 결과 값이 출력됨

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

WHERE 조건 검색

조건을 만족하는 데이터만 검색
WHERE 키워드와 함께 비교 연산자와 논리 연산자를 이용한 검색 조건 제시
- 논리 연산자
- LIKE
- NULL
  - IS NULL 키워드를 이용해 특정 속성의 값이 널 값인지를 비교
  - IS NOT NULL 키워드를 이용해 특정 속성의 값이 널 값이 아닌지를 비교
  - 검색 조건에서 널 값은 다른 값과 크기를 비교하면 결과가 모두 거짓이 됨

SELECT 제품명, 재고량, 단가
FROM 제품
WHERE 제조업체 = '한빛제과';

SELECT 주문제품, 수량, 주문일자
FROM 주문
WHERE 주문고객 = 'apple' AND 수량 >= 15;

SELECT 고객이름, 나이, 등급, 적립금
FROM 고객
WHERE 고객이름 LIKE '김%';
WHERE 고객이름 LIKE '_____';

SELECT 고객이름
FROM 고객
WHERE 나이 IS NULL;
WHERE 나이 IS NOT NULL;

ORDER BY 정렬 검색

ORDER BY 키워드를 이용해 결과 테이블 내용을 사용자가 원하는 순서로 출력
ORDER BY 키워드와 함께 정렬 기준이 되는 속성과 정렬 방식을 지정
- 오름차순(기본): ASC / 내림차순: DESC
- 널 값은 오름차순에서는 맨 마지막에 출력되고, 내림차순에서는 맨 먼저 출력됨
- 여러 기준에 따라 정렬하려면 정렬 기준이 되는 속성을 차례대로 제시

SELECT 고객이름, 등급, 나이 FROM 고객 ORDER BY 나이 DESC;

집계 함수 (aggregate function)

특정 속성 값을 통계적으로 계산한 결과를 검색하기 위해 집계 함수를 이용
- 집계 함수: 열 함수(column function)라고도 함. 개수, 합계, 평균, 최댓값, 최솟값의 계산 기능을 제공
집계 함수 사용 시 주의 사항
- 집계 함수는 널인 속성 값은 제외하고 계산함
- 집계 함수는 WHERE 절에서는 사용할 수 없고, SELECT 절이나 HAVING 절에서만 사용 가능

SELECT COUNT(나이) AS 고객수 FROM 고객;
SELECT COUNT(*) AS 고객수 FROM 고객; 
-- 정확한 개수를 계산하기 위해서는 보통 기본키 속성이나 *를 주로 이용
SELECT COUNT(DISTINCT 제조업체) AS "제조업체 수" FROM 제품;
-- DISTINCT 키워드를 이용해 중복을 없애고 서로 다른 제조업체의 개수만 계산

GROUP BY 그룹별 검색

GROUP BY 키워드를 이용해 특정 속성의 값이 같은 투플을 모아 그룹을 만들고, 그룹별로 검색
- GROUP BY 키워드와 함께 그룹을 나누는 기준이 되는 속성을 지정
HAVING 키워드를 함께 이용해 그룹에 대한 조건을 작성
그룹을 나누는 기준이 되는 속성을 SELECT 절에도 작성하는 것이 좋음
집계 함수나 GROUP BY 절에 명시된 속성 외의 속성은 SELECT 절에 작성 불가

Q. 제품 테이블에서 제품을 3개 이상 제조한 제조업체별로 제품의 개수와, 제품 중 가장 비싼 단가를 검색

SELECT 제조업체, COUNT(*) AS 제품수, MAX(단가) AS 최고가
FROM 제품
GROUP BY 제조업체 HAVING COUNT(*) >= 3;
-- 집계 함수를 이용한 조건은 WHERE 절에는 작성할 수 없고 HAVING 절에서 작성

여러 테이블에 대한 조인 검색

조인 검색: 여러 개의 테이블을 연결하여 데이터를 검색하는 것
조인 속성: 조인 검색을 위해 테이블을 연결해주는 속성
- 연결하려는 테이블 간에 조인 속성의 이름은 달라도 되지만 도메인은 같아야 함
- 일반적으로 외래키를 조인 속성으로 이용함
FROM 절에 검색에 필요한 모든 테이블을 나열
WHERE 절에 조인 속성의 값이 같아야 함을 의미하는 조인 조건을 제시
같은 이름의 속성이 서로 다른 테이블에 존재할 수 있기 때문에 속성 이름 앞에 해당 속성이 소속된 테이블의 이름을 표시 예) 주문.주문고객

SELECT 주문제품, 주문일자 -- 이름이 같은 속성이 없다면 테이블 이름 없이 속성 이름으로만 작성해도 됨
FROM 제품 p , 주문 o
WHERE o.주문고개 = 'banana' AND o.제품번호 = p.주문제품;
-- 테이블의 이름을 대신하는 단순한 별명을 제시하여 질의문을 작성하는 것도 좋다.

-- 여러 테이블에 대한 조인 검색
SELECT 제품, 제품명
FROM 고객, 제품 , 주문
WHERE 고객.고객이름 = '고명석' AND 고객.고객아이디 = 주문.주문고객 AND 제품.제품번호 = 주문.주문제품;

부속 질의문을 이용한 검색

SELECT 문 안에 또 다른 SELECT 문을 포함하는 질의
- 상위 질의문(주 질의문): 다른 SELECT 문을 포함하는 SELECT 문
- 부속 질의문(서브 질의문): 다른 SELECT 문 안에 들어 있는 SELECT 문
  - 괄호로 묶어서 작성, ORDER BY 절을 사용할 수 없음
  - 단일 행 부속 질의문: 하나의 행을 결과로 반환
  - 다중 행 부속 질의문: 하나 이상의 행을 결과로 반환
부속 질의문을 먼저 수행하고, 그 결과를 이용해 상위 질의문을 수행
부속 질의문과 상위 질의문을 연결하는 연산자가 필요
- 단일 행 부속 질의문은 비교 연산자(=, <>, >, >=, <, <=) 사용 가능
- 다중 행 부속 질의문은 비교 연산자 사용 불가, 다중 행 부속 질의문에 사용 가능한 연산자

-- 최대 적립금은 단일 값이므로 단일 행 부속 질의문 (비교 연산자 = 를 이용)
SELECT 고객이름, 적립금
FROM 고객
WHERE 적립금 = (SELECT MAX(적립금) FROM 고객);

-- 'banana' 고객이 주문한 제품은 여러 개이므로 다중 행 부속 질의문 (IN 연산자를 이용)
SELECT 제품명, 제조업체
FROM 제품
WHERE 제품번호 IN (SELECT 주문번호 FROM 주문 WHERE 주문고객 = 'banana');

1. 조인 질의를 이용한 select 문
SELECT 제품.제품명, 제품.제조업체
FROM 제품, 주문
WHERE 제품.제품번호 = 주문.주문제품 AND 주문.주문고객 = 'banana';

2. EXISTS 연산자를 이용한 SELECT 문 -- 여기서 exists는 부속 질의문에서 table을 하나만 씀.
SELECT 제품명, 제조업체
FROM 제품
WHERE EXISTS (SELECT * FROM 주문 WHERE 제품.제품번호 = 주문.주문제품 AND 주문.주문고객 = 'banana');

3. IN 연산자를 이용한 SELECT 문
SELECT 제품명, 제조업체
FROM 제품
WHERE 제품번호 IN (SELECT 주문제품 FROM 주문 WHERE 주문고객 = 'banana');

03-2 데이터 삽입

1. 데이터 직접 삽입

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

INTO 키워드와 함께 투플을 삽입할 테이블의 이름과 속성의 이름을 나열
- 속성 리스트를 생략하면 테이블을 정의할 때 지정한 속성의 순서대로 값이 삽입됨
VALUES 키워드와 함께 삽입할 속성 값들을 나열
INTO 절의 속성 이름과 VALUES 절의 속성 값은 순서대로 일대일 대응되어야 함

INSERT
INTO 고객(고객아이디, 고객이름, 나이, 등급, 직업, 적립금)
VALUES ('strawberry', '최유경', 30, 'vip', '공무원', 100)

INSERT -- 위의 쿼리와 동일함.
INTO 고객
VALUES ('strawberry', '최유경', 30, 'vip', '공무원', 100)


INSERT -- 등급에는 null 저장됨
INTO 고객(고객아이디, 고객이름, 나이, 직업, 적립금)
VALUES ('strawberry', '최유경', 30, '공무원', 100)

INSERT -- 등급에는 null 저장됨
INTO 고객
VALUES ('strawberry', '최유경', 30, NULL, '공무원', 100)

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

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

INSERT
INTO 테이블이름[(속성리스트)]
SELECT 문;

Q. 한빛제과에서 제조한 제품의 제품명, 재고량, 단가를 제품 테이블에서 검색하여 한빛제품 테이블에 삽입
A.
INSERT
INTO 한빛제품(제품명, 재고량, 단가)
SELECT 제품명, 재고량, 단가 FROM 제품 WHERE 제조업체='한빛제과'

03-3 데이터 수정

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

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

SET 키워드 다음에 속성 값을 어떻게 수정할 것인지를 지정
WHERE 절에 제시된 조건을 만족하는 투플에 대해서만 속성 값을 수정
- WHERE 절을 생략하면 테이블에 존재하는 모든 투플을 대상으로 수정

-- Q. 제품 테이블에 있는 모든 제품의 단가를 10% 인상
UPDATE 제품 
SET 단가 = 단가 * 1.1

-- Q. 정소화 고객이 주문한 제품의 주문수량을 5개로 수정 (부속 질의문 사용)
UPDATE 제품
SET 수량 = 5
WHERE 주문고객 IN (SLECT 고객아이디 FROM 고객 WHERE 고객이름='정소화');

03-4 데이터 삭제

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

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

WHERE 절에 제시한 조건을 만족하는 투플만 삭제
- WHERE 절을 생략하면 테이블에 존재하는 모든 투플을 삭제해 빈 테이블이 됨 빈 테이블이 남음, DROP TABLE과는 다름

-- 부속 질의문을 이용한 DELETE 문
DELETE
FROM 주문
WHERE 주문고객 IN (SELECT 고객아이디 FROM 고객 WHERE 고객이름='정소화');

04 뷰

04-1 뷰란?

다른 테이블을 기반으로 만들어진 가상 테이블
뷰는 기본 테이블을 들여다 볼 수 있는 창의 역할을 담당
데이터를 실제로 저장하지 않고 논리적으로만 존재하는 테이블이지만, 일반 테이블과 동일한 방법으로 사용
- 다른 뷰를 기반으로 새로운 뷰를 만드는 것도 가능함
뷰를 통해 기본 테이블의 내용을 쉽게 검색할 수는 있지만, 기본 테이블의 내용을 변화시키는 작업은 제한적으로 이루어짐
- 기본 테이블: 뷰를 만드는데 기반이 되는 물리적인 테이블

04-2 뷰 검색: CREATE VIEW 문

CREATE VIEW 뷰이름[(속성리스트)]
AS SELECT 문
[ WITH CEHCK OPTION ]

CREATE VIEW 키워드와 함께 생성할 뷰의 이름과 뷰를 구성하는 속성의 이름을 나열
- 속성 리스트를 생략하면 SELECT 절에 나열된 속성의 이름을 그대로 사용
AS 키워드와 함께 기본 테이블에 대한 SELECT 문 작성
- SELECT 문은 생성하려는 뷰의 정의를 표현하며 ORDER BY는 사용 불가

WITH CHECK OPTION

뷰에 삽입이나 수정 연산을 할 때 SELECT 문에서 제시한 뷰의 정의 조건을 위반하면 수행되지 않도록 하는 제약조건을 지정

Q. 고객 테이블에서 등급이 vip인 고객의 고객아이디, 고객이름, 나이로 구성된 뷰를 우수고객이라는 이름으로 생성해보자. 그런 다음 우수고객 뷰의 모든 내용을 검색해보자.
CREATE VIEW 우수고객(고객아이디, 고객이름, 나이)
AS SELECT 고객아이디, 고객이름, 나이
	FROM 고객
WHERE 등급 = 'vip'
WITH CHECK OPTION; -- 역할: 등급이 없음. 삽입 시 등급이 null로 들어가서 안되게 막음.

select * from 우수고객;

-- 만약 이렇게 생성이 되었을 시, 
CREATE VIEW 우수고객(고객아이디, 고객이름, 나이, 등급)
AS SELECT 고객아이디, 고객이름, 나이, 등급
	FROM 고객
	WHERE 등급 = 'vip'
WITH CHECK OPTION; -- 역할: 등급이 vip가 아니면 삽입이 안되도록 막음.

Q. 제품 테이블에서 제조업체별 제품수로 구성된 뷰를 업체별제품수라는 이름으로 생성해보자
CREATE VIEW 업체별제품수(제조업체, 제품수)
AS SELECT 제조업체, COUNT(*)
	FROM 제품
	GROUP BY 제조업체
WITH CHECK OPTION;

04-3 뷰 활용: SELECT 문

뷰는 일반 테이블과 같은 방법으로 원하는 데이터를 검색할 수 있음
- 뷰에 대한 SELECT 문이 내부적으로는 기본 테이블에 대한 SELECT 문으로 변환되어 수행
검색 연산은 모든 뷰에 수행 가능

04-4 뷰 활용 : INSERT, UPDATE, DELETE 문

뷰에 대한 삽입, 수정, 삭제 연산은 실제로 기본 테이블에 수행되므로 결과적으로는 기본 테이블이 변경됨
뷰에 대한 삽입, 수정, 삭제 연산은 제한적으로 수행됨
- 변경 가능한 뷰 vs 변경 불가능한 뷰
변경 불가능한 뷰의 특징
- 기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 뷰
- 기본 테이블에 있던 내용이 아닌 집계 함수로 새로 계산된 내용을 포함하는 뷰
- DISTINCT 키워드를 포함하여 정의한 뷰
- GROUP BY 절을 포함하여 정의한 뷰
- 여러 개의 테이블을 조인하여 정의한 뷰는 변경이 불가능한 경우가 많음
→ 제품 1뷰는 변경 가능한 뷰인가? O
→ 제품 2뷰는 변경 가능한 뷰인가? X 기본키가 포함되어 있지 않음.

04-5 뷰의 장점

질의문을 좀 더 쉽게 작성할 수 있다.
- GROUP BY, 집계 함수, 조인 등을 이용해 뷰를 미리 만들어 놓으면, 복잡한 SQL 문을 작성하지 않아도 SELECT 절과 FROM 절만으로도 원하는 데이터의 검색이 가능
데이터의 보안 유지에 도움이 된다.
- 자신에게 제공된 뷰를 통해서만 데이터에 접근하도록 권한 설정이 가능
데이터를 좀 더 편리하게 관리할 수 있다.
- 제공된 뷰와 관련이 없는 다른 내용에 대해 사용자가 신경 쓸 필요가 없음

04-6 뷰 삭제 : DROP VIEW 문

DROP VIEW 뷰이름;

뷰를 삭제해도 기본 테이블은 영향을 받지 않음
만약, 삭제할 뷰를 참조하는 제약조건이 존재한다면?
- 예) 삭제할 뷰를 이용해 만들어진 다른 뷰가 존재하는 경우
- 뷰 삭제가 수행되지 않음
- 관련된 제약조건을 먼저 삭제해야 함

'DB' 카테고리의 다른 글

9장 정규화 (0)	2021.06.20
IE 표기법 (0)	2021.06.20
8장 데이터베이스 설계 (0)	2021.06.20
6장 관계 데이터 연산 (0)	2021.04.17
5장 관계 데이터 모델 (0)	2021.03.17
4장 데이터 모델링 (0)	2021.03.17