[데이터베이스] 7. 데이터베이스 언어 SQL

김연희 저자님의 "데이터베이스 개론" 책을 학습하고 정리한 글입니다.

1. SQL의 소개

📌 SQL (Structured Query Language)

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

기능에 따라 데이터 정의어, 데이터 조작어, 데이터 제어어로 나눈다.
데이터 정의어 (DDL, Data Definition Language)
테이블을 생성하고 변경, 삭제하는 기능을 제공한다.
데이터 조작어 (DML, Data Manipulation Language)
테이블에 새 데이터를 삽입하거나, 테이블에 저장된 데이터를 수정, 삭제, 검색하는 기능을 제공한다.
데이터 제어어 (DCL, Data Control Language)
보안을 위해 데이터에 대한 접근 및 사용 권한을 사용자별로 부여하거나 취소하는 기능을 하는 언어다.

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

2-1. 테이블의 생성

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

① 테이블을 구성하는 각 속성의 이름과 데이터 타입, 기본적인 제약 사항을 정의한다.
② 기본키로 테이블에 하나만 존재할 수 있다.
③ 대체키로 테이블에 여러 개 존재할 수 있다.
④ 외래키로 테이블에 여러 개 존재할 수 있다.
⑤ 데이터 무결성을 위한 제약조건으로 테이블에 여러 개 존재할 수 있다.
[]로 표시한 항목은 생략이 가능하다.
모든 SQL문은 세미콜론(;}으로 문장 끝을 표시한다.
CREATE, TABLE, NOT NULL 과 같은 키워드는 대소문자를 구분하지 않는다.

✍🏻 속성의 정의

각 속성의 특성을 고려해 적절한 데이터 타입을 선택하여 정의한다.

CREATE TABLE 문으로 생성하는 테이블의 속성은 기본적으로 널 값을 허용한다.
- 널 값을 허용하지 않으려면 속성을 정의할 때 속성의 이름과 데이터 타입 다음에 NOT NULL 키워드를 포함해야 한다.
- 기본키를 구성하는 모든 속성은 널 값을 가질 수 없도록 반드시 NOT NULL 키워드를 표기한다.
기본키를 구성하는 속성은 널 값을 가질 수 없다는 개체 무결성 제약조건을 실제로 구현하기 위한 방법이다.
- 기본키로 지정한 속성은 굳이 NOT NULL을 표기하지 않아도 자동으로 NOT NULL 속성을 갖지만,
- 좀 더 제약조건을 명시하기 위해 NOT NULL을 표기하기도 한다.
속성에 기본 값을 지정하지 않으면 사용자가 속성에 값을 입력하지 않았을 때 해당 속성에 널 값이 기본으로 저장된다.
- DEFAULT 키워드를 사용해 기본 값을 명확히 지정해두면 이 기본 값이 저장된다.
- DEFAULT 키워드로 기본 값을 지정할 때 숫자 데이터는 그대로 표현하고,
- 문자열이나 날짜 데이터는 '작은따옴표'로 묶어줘야 한다. '작은따옴표'로 묶인 문자열은 대소문자를 구분한다.

# 속성의 길이가 최대 20인 가변 길이의 문자열 데이터로 구성하고, 널 값을 허용하지 않음
고객아이디 VARCHAR(20) NOT NULL

# 졍수 데이터로 구성하고, 미입력시 0이 기본으로 저장
적립금 INT DEFAULT 0

# 속성의 길이가 최대 10인 가변 길이의 문자열 데이터로 구성하고, 미입력시 '방경아'가 기본 값으로 지정
담당자 VARCHAR(10) DEFAULT '방경아'

# a0와 A0 값은 다른 값으로 취급됨
학점 CHAR(2) DEFAULT 'a0'
학점 CHAR(2) DEFAULT 'A0'

✍🏻 키의 정의

CREATE TABLE 문으로 테이블을 정의할 때는 기본키, 대체키, 외래키를 지정할 수 있다.

기본키는 PRIMARY KEY 키워드를 사용해 지정한다.
- 기본키가 없어도 테이블을 정의할 수 있지만, 각 튜플을 식별할 수 있는 기본키는 가능한 한 선택하는 것이 좋다.
- 모든 테이블에서 기본키는 반드시 하나만 지정할 수 있고, 여러 개의 속성으로 구성할 수 있다.
대체키는 UNIQUE 키워드를 사용해 지정한다.
- 대체키는 기본키와 같이 각 튜플을 유일하게 식별하는 특성이 있다.
- 대체키로 지정된 속성의 값은 테이블에서 중복되면 안되고, 유일성을 가져야 한다.
- 기본키로 지정된 속성과 달리 널 값을 가질 수 있고, 한 테이블에서 여러 개를 지정할 수 있다.
외래키는 FOREIGN KEY 키워드를 사용해 지정한다.
- 외래키를 지정할 때는 참조 무결성 제약조건을 유지하기 위해 어떤 테이블의 무슨 속성을 참조하는지 REFERENCES 키워드를 다음에 정확히 명시해야 한다.
- 이렇게 하면 참조되는 테이블에서 튜플을 함부로 삭제하거나 변경하지 못한다.
- 또한, 참조되는 테이블에서 튜플을 삭제하거나 변경할 때 처리하는 방법을 다양하게 선택할 수 있다.
- 외래키는 다른 테이블의 기본키뿐 아니라 UNIQUE 제약조건으로 지정된 대체키를 참조하도록 정의할 수도 있다.

# 기본키를 설정하는 방법 (속성 하나 이상)
PRIMARY KEY(고객아이디)
PRIMARY KEY(주문고객, 주문제품)

# 대체키를 설정하는 방법
UNIQUE(고객이름)

부서 테이블에서 홍보부 튜플을 삭제하려고 할 때 다음 네 가지 방법 중 한 가지 방법으로 처리하도록 선택할 수 있다.

별도로 지정하지 않으면 ON DELETE NO ACTION 이 기본으로 선택된다.

① ON DELETE NO ACTION : 튜플을 삭제하지 못하게 한다.

부서 테이블을 참조하는 사원 테이블이 존재하므로 부서 테이블의 튜플을 삭제하지 못하게 함

② ON DELETE CASCADE : 관련 튜플을 함께 삭제한다.

사원 테이블에서 홍보부에 근무하고 있는 정소화 사원에 대한 튜플도 함께 삭제함

③ ON DELETE SET NULL : 관련 튜플의 외래키 값을 NULL로 변경한다.

사원 테이블에서 홍보부에 근무하는 정소화 사원 튜플의 소속부서 속성의 값을 NULL로 변경함

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

사원 테이블에서 홍보부에 근무하는 정소화 사원 튜플의 소속부서 속성의 값을 미리 지정한 기본 값으로 변경함

참조되는 테이블의 튜플이 변경될 때도 다음 네 가지 중 한 가지 방법으로 처리하도록 선택할 수 있다.

별도로 지정하지 않으면 ON UPDATE NO ACTION이 기본으로 선택된다.

① ON UPDATE NO ACTION : 튜플을 변경하지 못하도록 한다.

② ON UPDATE CASCADE : 관련 튜플에서 외래키 값을 함께 변경한다.

③ ON UPDATE SET NULL : 관련 튜플의 외래키 값을 NULL로 변경한다.

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

# 소속부서 속성이 부서 테이블의 부서번호 속성을 참조하는 외래키이고
# ON DELETE CASCADE와 ON UPDATE CASCADE 방법으로 처리함
FOREIGN KEY(소속부서) REFERENCES 부서(부서번호)
ON DELETE CASCADE ON UPDATE CASCADE

# 소속부서 속성이 부서 테이블의 부서번호 속성을 참조하는 외래키이고,
# ON DELETE NO ACTION과 ON UPDATE NO ACTION 처리 방법이 자동으로 선택됨
FOREIGN KEY(소속부서) REFERENCES 부서(부서번호)

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

CREATE TABLE 문으토 테이블을 정의할 때 CHECK 키워드를 사용해 특정 속성에 대한 제약조건을 지정할 수 있다.
- 그러면 테이블에는 CHECK 키워드로 지정한 제약조건을 만족하는 튜플만 존재하게 된다.
- 테이블에 새로 튜플을 삽입하거나 기존 튜플을 수정할 때도 이 제약조건을 반드시 지켜야 한다.
- 이는 테이블에서 항상 정확하고 유효한 데이터를 유지하기 위해 데이터 무결성을 위한 제약조건을 표현하는 방법이다.
CHECK 키워드를 사용해 지정한 제약조건에 CONSTRAINT 키워드와 함께 고유의 이름을 부여할 수도 있다.
- 제약조건을 여러 개 지정할 때 고유의 이름을 부여하면 테이블이 생성된 이후에 제약조건을 수정하거나 제거할 때 식별하기 쉽다.

# 모든 제품의 재고량은 항상 0개 이상, 10,000개 이하로 유지되어야 한다는 데이터 무결성 제약조건
CHECK(재고량>=0 AND 재고량<=10000)

# 모든 제품의 제조업체로 한빛제과만 허용된다는 데이터 무결성 제약조건에 CHK_CPY이라는 고유의 이름을 부여함.
# 다른 테이블에는 CHK_CPY이라는 이름으로 정의된 제약조건이 있으면 안됨
CONSTRAINT CHK_CPY CHECK(제조업체 = '한빛제과')

2-2. 테이블의 변경

테이블은 ALTER TABLE 문으로 변경할 수 있고 새로운 속성 추가, 기존 속성 삭제, 새로운 제약조건 추가, 기존 제약조건 삭제 등이 가능하다.

기존 속성을 삭제할 때 만약 삭제할 속성과 관련된 제약조건이 존재하거나,
이 속성을 참조하는 다른 속성이 존재할 경우 속성을 삭제할 수 없다.
관련 제약조건이나 참조하는 다른 속성을 먼저 삭제한 후 해당 속성을 삭제해야 한다.

# 새로운 속성의 추가
ALTER TABLE 테이블_이름
    ADD 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값];
		
# 기존 속성의 삭제
ALTER TABLE 테이블_이름 DROP COLUMN 속성_이름;

# 새로운 제약조건 추가
ALTER TABLE 테이블_이름 ADD CONSTRAINT 제약조건_이름 제약조건_내용;

# 기존 제약조건의 삭제
ALTER TABLE 테이블_이름 DROP CONSTRAINT 제약조건_이름;

2-3. 테이블의 삭재

CREATE TABLE 문으로 생성한 테이블은 DROP TABLE 명령어로 삭제할 수 있다.

삭제할 테이블을 참조하는 테이블이 있다면 삭제가 수행되지 않는다.
따라서 삭제하고자 하는 테이블을 참조하는 외래키 제약조건을 먼저 삭제해야 한다.

DROP TABLE 테이블_이름;

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

3-1. 데이터의 검색

테이블에서 원하는 데이터를 검색하기 위해 필요한 SQL문은 SELECT다.

✍🏻 기본 검색

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

SELECT 키워드와 함께 검색하고 싶은 속성의 이름을 콤마( , )로 구분하여 차례로 나열한다.
그리고 FROM 키워드와 함께 검색하고 싶은 속성이 있는 테이블의 이름을 콤마( , )로 구분하여 차례로 나열한다.
테이블에 존재하는 모든 속성을 검색하기 위해 속성의 이름을 전부 나열하지 않고 * 를 사용할 수도 있다.
SELECT 문의 결과 테이블은 관계 데이터 모델의 일반 릴레이션과 큰 차이가 있다.
- 관계 데이터 모델의 일반 릴레이션은 튜플의 집합 개념으로 이해할 수 있으며,
- 튜플의 유일성을 만족해야 하기 때문에 릴레이션 하나에서 동일한 튜플이 중복되면 안된다.
- 하지만, SELECT 문의 수행 결과로 반환되는 결과 테이블에서는 동일한 튜플이 중복될 수 있다.
- 결과 테이블이 중복을 허용하도록 ALL 키워드를 명시적으로 사용해도 된다.
- 결과 테이블에서 튜플의 중복을 제거하고 한 번씩만 출력되도록 하려면 DISTINCT 키워드를 사용한다.
결과 테이블에 출력되는 속성의 이름을 다른 이름으로 바꾸어 출력할 수도 있다.
- AS 키워드를 변경할 이름과 함께 지정하면 된다.
- 원래 테이블의 속성 이름이 실제로 바뀌는 것은 아니며, 결과 테이블에서만 지정한 이름으로 출력된다.
- 지정하는 이름에 공백이 포함되어 있으면 오라클에서는 큰따옴표로 묶어주고, MS SQL에서는 작은따옴표로 묶어주어야 한다.
- AS 키워드는 생략할 수 있다.

# 고객 테이블에서 고객아이디, 고객이름, 등급 속성 검색
SELECT 고객아이디, 고객이름, 등급
FROM 고객;

# 고객 테이블에 존재하는 모든 속성 검색
SELECT *
FROM 고객;

# 제품 테이블에서 제조업체를 검색할 때 중복을 허용한다.
# 중복을 허용한다는 의미에서 ALL 키워드를 명시적으로 사용해도 된다.
SELECT [ALL] 제조업체
FROM 제품;

# 제품 테이블에서 제조업체 속성을 중복 없이 검색
SELECT DISTINCT 제조업체
FROM 제품;

# 제품 테이블에서 제품명과 단가를 검색하되, 단가를 가격이라는 새 이름으로 출력
SELECT 제품명, 단가 AS 가격
FROM 제품;

✍🏻 산술식을 이용한 검색

SELECT 키워드와 함께 산술식을 제시할 수 있다.

산술식은 속성의 이름과 + - * / 등의 산술 연산자, 상수로 구성한다.

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

✍🏻 조건 검색

WHERE 키워드와 함께 비교 연산자와 논리 연산자를 이용한 검색 조건을 제시하면 된다.
조건은 비교 연산자를 이용해 숫자뿐 아니라 문자나 날짜 값을 비교할 수 있다.
- 예를 들어, ‘A’와 ‘C’를 비교하면 ‘C’을 더 큰 값으로 판단한다.
- ‘2022-12-01’과 ‘2022-12-02’를 비교하면 ‘2022-12-02’을 더 큰 값으로 판단한다.
- 단, 조건에서 숫자 값은 그대로 작성해도 되지만, 문자나 날짜 값은 속성의 이름과 구별할 수 있도록 작은따옴표로 묶어야 한다.

# 제품 테이블에서 한빛제과가 제조한 제품의 제품명, 재고량, 단가 검색
SELECT 제품명, 재고량, 단가
FROM 제품
WHERE 제조업체 = '한빛제과';

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

# 주문 테이블에서 apple 고객이 주문했거나, 15개 이상 주문된 제품의 주문제품, 수량, 주문일자 검색
SELECT 주문제품, 수량, 주문일자
FROM 주문
WHERE 주문고객 = 'apple' OR 수량 >= 15;

✍🏻 LIKE를 이용한 검색

검색 조건을 부분적으로만 알고 있다면 LIKE 키워드를 이용해 검색할 수 있다.

검색 조건을 정확히 알면 = 연산자로 조건을 표현하면 되지만 부분적으로만 알고 있다면 = 대신 LIKE 키워드를 사용한다.
단, LIKE 키워드는 문자열을 이용하는 조건에만 사용할 수 있다.

# 고객 테이블에서 성이 김 씨인 고객의 고객이름, 나이, 등급, 적립금 검색
SELECT 고객이름, 나이, 등급, 적립금
FROM 고객
WHERE 고객이름 LIKE '김%';

# 고객 테이블에서 고객아이디가 5자인 고객의 고객아이디, 고객이름, 등급 검색
SELECT 고객아이디, 고객이름, 등급
FROM 고객
WHERE 고객아이디 LIKE '_____';

✍🏻 NULL을 이용한 검색

검색 조건에서 특정 속성의 값이 널 값인지를 비교하려면 IS NULL 키워드를 사용한다.

반대로 특정 속성의 값이 널 값이 아닌지를 비교하려면 IS NOT NULL 키워드를 사용한다.

# 고객 테이블에서 나이가 아직 입력되지 않은 고객의 고객이름 검색
SELECT 고객이름
FROM 고객
WEHRE 나이 IS NULL;

# 고객 테이블에서 나이가 이미 입력된 고객의 고객이름 검색
SELECT 고객이름
FROM 고객
WHERE 나이 IS NOT NULL;

✍🏻 정렬 검색

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

SELECT 문의 검색 결과 테이블은 일반적으로 DBMS가 정한 순서로 출력된다.

결과 테이블의 내용을 사용자가 원하는 순서로 출력하려면 ORDER BY 키워드를 사용한다.
ORDER BY 키워드와 함께 정렬 기준이 되는 속성을 지정하고,
오름차순 정렬이면 ASC, 내림차순 정렬이면 DESC로 표현한다.
특별히 지정하지 않으면 오름차순으로 기본 정렬한다.

# 고객 테이블에서 고객이름, 등급, 나이를 검색하되, 나이를 기준으로 내림차순 정렬
SELECT 고객이름, 등급, 나이
FROM 고객
ORDER BY 나이 DESC;

# 주문 테이블에서 수량이 10개 이상인 주문의 주문고객, 주문제품, 수량, 주문일자 검색
# 단, 주문제품을 기준으로 오름차순 정렬하고, 동일 제품은 수량을 기준으로 내림차순 정렬
SELECT 주문고객, 주문제품, 수량, 주문일자
FROM 주문
WHERE 수량 >= 10
ORDER BY 주문제품 ASC, 수량 DESC;

✍🏻 집계 함수를 이용한 검색

특정 속성 값을 통계적으로 계산한 결과를 검색하기 위해 집계 함수(aggregate function)를 이용할 수 있다.

집계 함수는 열 함수(column function)라고도 하며, 개수, 합계, 평균, 최댓값, 최솟값의 계산 기능을 제공한다.
SUM과 AVG 함수는 숫자 데이터 타입의 속성에만 적용할 수 있고
나머지 함수는 숫자뿐 아니라 문자와 날짜 데이터 타입의 속성에도 적용할 수 있다.

# 제품 테이블에서 모든 제품의 단가 평균
SELECT AVG(단가)
FROM 제품;

# 한빛제과에서 제조한 제품의 재고량 합계
SELECT SUM(재고량) AS "재고량 합계"
FROM 제품
WHERE 제조업체 = '한빛제과';

# 고객 테이블에 고객이 몇 명 등록되어 있는지
SELECT COUNT(고객아이디) AS 고객수
FROM 고객;

# 제품 테이블에서 제조업체의 수
SELECT COUNT(DISTINCT 제조업체) AS "제조업체 수"
FROM 제품;

✍🏻 그룹별 검색

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

테이블에서 특정 속성의 값이 같은 튜플을 모아 그룹을 만들고, 그룹별로 검색을 하기 위해 GROUP BY 키워드를 사용한다.

그룹에 대한 조건을 추가하려면 GROUP BY 키워드를 HAVING 키워드와 함께 사용하면 된다.
GROUP BY 키워드가 없는 SELECT 문은 테이블 전체를 하나의 그룹으로 보고 검색하는 것이다.

# 주문 테이블에서 주문제품별 수량의 합계 검색
SELECT 주문제품, SUM(수량) AS 총주문수량
FROM 주문
GROUP BY 주문제품;

# 제품 테이블에서 제조업체별로 제조한 제품의 개수와 제품 중 가장 비싼 단가 검색
# 단 제품의 개수는 제품수라는 이름으로 출력하고 가장 비싼 단가는 최고가라는 이름으로 출력
SELECT 제조업체, COUNT(*) AS 제품수, MAX(단가) AS 최고가
FROM 제품
GROUP BY 제조업체;

# 제품 테이블에서 제품을 3개 이상 제조한 제조업체별로 제품의 개수와, 제품 중 가장 비싼 단가 검색
SELECT 제조업체, COUNT(*) AS 제품수, MAX(단가) AS 최고가
FROM 제품
GROUP BY 제조업체 HAVING COUNT(*)>=3;

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

여러 개의 테이블을 연결하여 데이터를 검색하는 것을 조인 검색이라고 한다.

조인 검색을 하려면 테이블을 연결해주는 속성인 조인 속성을 필요로 한다.
테이블을 연결하려면, 조인 속성의 이름은 달라도 되지만 도메인은 반드시 같아야 한다.
일반적으로 테이블의 관계를 나타내는 외래키를 조인 속성으로 이용한다.

조인 검색을 위한 SQL 문은 FROM 절에 검색에 필요한 모든 테이블을 나열하고, WHERE 절에는 조인 속성의 값이 같아야 함을 의미하는 조인 조건을 제시한다.

여러 테이블을 이용하는 조인 검색은 이름이 같은 속성이 서로 다른 테이블에 존재할 수도 있기 때문에 속성의 이름 앞에 해당 속성이 소속된 테이블의 이름을 표시해주는 것이 좋다.
테이블의 이름과 속성의 이름은 . 기호로 연결한다.

# 판매 데이터베이스에서 banana 고객이 주문한 제품 이름 검색
SELECT 제품.제품명
FROM 제품, 주문
WHERE 주문.주문고객 = 'banana' AND 제품.제품번호 = 주문.주문제품;

# 판매 데이터베이스에서 나이가 30세 이상인 고객이 주문한 제품의 번호와 주문일자 검색
SELECT o.주문제품, o.주문일자
FROM 고객 c, 주문 o
WHERE c.나이 >= 30 AND c.고객아이디 = o.주문고객;

# 판매 데이터베이스에서 고명석 고객이 주문한 제품의 제품명 검색
SELECT 제품.제품명
FROM 고객, 제품, 주문
WHERE 고객.고객이름 = '고명석' AND 고객.고객아이디 = 주문.주문고객 AND 제품.제품번호 = 주문.주문제품;

표준 SQL에서는 다음과 같이 INNER JOIN과 ON 키워드를 이용해 작성하는 방법도 제공하고 있다.

SELECT 속성_리스트
FROM 테이블1 INNER JOIN 테이블2 ON 조인조건
[ WHERE 검색조건 ];

# 나이가 30세 이상인 고객이 주문한 제품의 주문제품과 주문일자 검색
SELECT 주문.주문제품, 주문.주문일자
FROM 고객 INNER JOIN 주문 ON 고객.고객아이디 = 주문.주문고객
WHERE 고객.나이 >= 30;

지금까지 설명한 조인 검색은 조인 속성의 값이 같은 튜플에 대해서만 검색을 수행하므로 동등 조인에 해당한다.
만약 조인 조건을 만족하지 않는 튜플에 대해서도 검색을 수행하려면 외부 조인을 요청하도록 OUTER JOIN과 ON 키워드를 이용해 SQL 문을 작성하면 된다.

SELECT 속성_리스트
FROM 테이블1 LEFT | RIGHT | FULL OUTER JOIN 테이블2 ON 조인조건
[ WHERE 검색조건 ];

# 주문하지 않은 고객도 포함하여 고객이름과 주문제품, 주문일자 검색
SELECT 고객.고객이름, 주문.주문제품, 주문.주문일자
FROM 고객 LEFT OUTER JOIN 주문 ON 고객.고객아이디 = 주문.주문고객;

고객 테이블의 모든 튜플을 검색 대상으로 하여 다음과 같이 양쪽 테이블의 위치를 교환하고 왼쪽 외부 조인을 오른쪽 외부 조인으로 바꿔도 결과는 같다.

SELECT 고객.고객이름, 주문.주문제품, 주문.주문일자
FROM 주문 RIGHT OUTER JOIN 주문.주문고객 = 고객.고객아이디;

✍🏻 부속 질의문을 이용한 검색

SELECT 문 안에 또 다른 SELECT 문을 포함할 수 있다.

📌 부속 질의문 또는 서브 질의문(sub query)

다른 SELECT 문 안에 들어 있는 SELECT 문

다른 SELECT 문을 포함하는 SELECT 문을 상위 질의문 또는 주 질의문(main query)이라고 한다.
부속 질의문은 괄호로 묶어 작성하고 ORDER BY 절을 사용할 수 없으며, 상위 질의문보다 먼저 수행한다.
하나의 행을 결과로 반환하는 단일 행 부속 질의문과 하나 이상의 행을 결과로 반환하는 다중 행 부속 질의문으로 분류한다.
부속 질의문과 상의 질의문을 연결하는 연산자가 필요한데 부속 질의문의 종류에 따라 사용할 수 있는 연산자가 다르므로 주의해야 한다. 단일 행 부속 질의문은 일반 비교 연산자를 사용할 수 있지만, 다중 행 부속 질의문은 일반 비교 연산자를 사용할 수 없다.

# 판매 데이터베이스에서 달콤비스킷을 생산한 제조업체가 만든 제품들의 제품명과 단가 검색
SELECT 제품명, 단가
FROM 제품
WHERE 제조업체 = (SELECT 제조업체
		FROM 제품
		WHERE 제품명 = '달콤비스킷');

# 판매 데이터베이스에서 적립금이 가장 많은 고객의 고객이름과 적립금 검색
SELECT 고객이름, 적립금
FROM 고객
WHERE 적립금 = (SELECT MAX(적립금) FROM 고객);

# 판매 데이터베이스에서 banana 고객이 주문한 제품의 제품명과 제조업체 검색
SELECT 제품명, 제조업체
FROM 제품
WHERE 제품번호 IN (SELECT 주문제품
		 FROM 주문
		 WHERE 주문고객 = 'banana');

# 판매 데이터베이스에서 banana 고객이 주문하지 않은 제품의 제품명과 제조업체 검색
SELECT 제품명, 제조업체
FROM 제품
WHERE 제품번호 NOT IN (SELECT 주문제품
		    FROM 주문
		    WHERE 주문고객 = 'banana');

# 판매 데이터베이스에서 대한식품이 제조한 모든 제품의 단가보다 비싼 제품의 제품명, 단가, 제조업체 검색
SELECT 제품명, 단가, 제조업체
FROM 제품
WHERE 단가 > ALL (SELECT 단가
		 FROM 제품
		 WHERE 제조업체 = '대한식품');
								 
# 판매 데이터베이스에서 2022년 3월 15일에 제품을 주문한 고객의 고객이름을 검색해보자.
SELECT 고객이름
FROM 고객
WHERE EXISTS(SELECT *
	     FROM 주문
	     WHERE 주문일자 = '2022-03-15'
			   AND 주문.주문고객 = 고객.고객아이디);

# 판매 데이터베이스에서 2022년 3월 15일에 제품을 주문하지 않은 고객의 고객이름 검색
SELECT 고객이름
FROM 고객
WHERE NOT EXISTS (SELECT *
		  FROM 주문
		  WHERE 주문일자 = '2022-03-15'
				  AND 주문.주문고객 = 고객.고객아이디);

3-2. 데이터의 삽입

테이블에 새로운 튜플을 삽입하기 위해 필요한 SQL 문은 INSERT로 크게 두 가지다.

테이블에 튜플을 직접 삽입하는 방법
부속 질의문을 이용해 튜플을 삽입하는 방법

✍🏻 데이터 직접 삽입

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

INTO 키워드와 함께 튜플을 삽입할 테이블의 이름을 제시한 후, 속성의 이름을 나열하는데 이 나열 순서대로 VALUES 키워드 다음의 속성 값들이 차례로 삽입된다.
- INTO 절의 속성 이름과 VALUES 절의 속성 값은 순서대로 일대일 대응되고 개수도 같아야 한다.
- INTO 절에서 속성 이름의 리스트는 생략할 수 있는데, 생략한 경우에는 테이블을 정의할 때 지정한 속성의 순서대로 VALUES 절의 속성 값이 삽입된다.
- VALUES 절에 나열되는 속성 값은 문자나 날짜 타입의 데이터인 경우에는 작은따옴표로 묶어야 한다.

# 판매 데이터베이스의 고객 테이블에 고객 아이디가 strawberry, 고객이름이 최유경, 나이가 30세,
# 등급이 vip, 직업이 공무원, 적립금이 100원인 새로운 고객의 정보를 삽입
INSERT
INTO   고객(고객아이디, 고객이름, 나이, 등급, 직업, 적립금)
VALUES ('strawberry', '최유경', 30, 'vip', '공무원', 100);

# 그런 다음 고객 테이블에 있는 모든 내용을 검색하여 삽입된 새로운 튜플 확인
SELECT * FROM 고객;

# 판매 데이터베이스의 고객 테이블에 고객아이디가 tomato, 고객이름이 정은심, 나이가 36세,
# 등급이 gold, 적립금이 4,000원, 직업은 아직 모르는 새로운 고객 정보 삽입
INSERT
INTO   고객(고객아이디, 고객이름, 나이, 등급, 적립금)
VALUES ('tomato', '정은심', 36, 'gold', 4000);

# 고객 테이블에 있는 모든 내용을 검색하여 삽입된 정은심 고객의 직업 속성 값이 널 값인지 확인
SELECT * FROM 고객;

✍🏻 부속 질의문을 이용한 데이터 삽입

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

# 판매 데이터베이스에서 제시한 3개의 테이블 외에 제품명, 재고량, 단가 속성으로 구성된 
# 한빛제품 테이블이 존재한다고 가정하고, 부속 질의문을 이용한 INSERT 문 예시
INSERT
INTO   한빛제품(제품명, 재고량, 단가)
SELECT 제품명, 재고량, 단가
FROM   제품
WHERE  제조업체 = '한빛제과';

3-3. 데이터의 수정

테이블에 저장된 데이터를 수정하기 위해 필요한 SQL 명령어는 UPDATE다.

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

UPDATE 문은 테이블에 저장된 튜플에서 특정 속성의 값을 수정한다.
- 값을 어떻게 수정할지는 SET 키워드 다음에 지정한다.
- WHERE 절에 제시된 조건을 만족하는 튜플만 속성 값을 수정하는데, WHERE 절을 생략하면 모든 튜플을 대상으로 수정하게 된다.

# 제품 테이블에서 제품번호가 p03인 제품의 제품명을 통큰파이로 수정
UPDATE 제품
SET    제품명 = '통큰파이'
WHERE  제품번호 = 'p03';

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

# 판매 데이터베이스에 정소화 고객이 주문한 제품의 주문수량을 5개로 수정
UPDATE 주문
SET    수량 = 5
WHERE  주문고객 IN (SELECT 고객아이디
		  FROM 고객
		  WHERE 고객이름 = '정소화');

3-4. 데이터의 삭제

테이블에 저장된 데이터를 삭제하기 위해 필요한 SQL 명령어는 DELETE다.

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

DELETE 문은 WHERE 절에 제시한 조건을 만족하는 튜플만 삭제한다.
WHERE 절을 생략하면 테이블에 존재하는 모든 튜플을 삭제하여 빈 테이블이 된다.

# 주문 테이블에서 주문일자가 2022년 5월 22일인 주문 내역 삭제
DELETE
FROM   주문
WHERE  주문일자 = '2022-05-22';

# 판매 데이터베이스에서 정소화 고객이 주문한 내역을 주문 테이블에서 삭제
DELETE 
FROM   주문
WHERE  주문고객 IN (SELECT 고객아이디
		  FROM   고객
		  WHERE  고객이름 = '정소화');
									
# 판매 데이터베이스의 주문 테이블에 존재하는 모든 튜플을 삭제
DELETE
FROM   주문;

4. 뷰

4-1. 뷰의 개념

📌 뷰 (View)

다른 테이블을 기반으로 만들어진 가상 테이블 (virtual table)

일반 테이블과 달리 데이터를 실제로 저장하고 있지 않기 때문에 가상 테이블이라고 부른다.
뷰는 논리적으로만 존재하면서도 일반 테이블과 동일한 방법으로 사용할 수 있어 사용자는 그 차이를 느끼기 어렵다.
뷰를 만드는 데 기반이 되는 물리적인 테이블을 기본 테이블(base table)이라고 하는데, CREATE TABLE 문으로 정의한 테이블이 기본 테이블로 사용된다.
일반적으로 뷰는 기본 테이블을 기반으로 만들어지지만 다른 뷰를 기반으로 새로운 뷰를 만들 수도 있다.

4-2. 뷰의 생성

뷰를 생성하기 위해 필요한 SQL 명령어는 CREATE VIEW 다.

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

CREATE VIEW 명령어와 함께 새로 생성할 뷰의 이름을 제시한 후, 뷰를 구성하는 속성의 이름을 괄호 안에 나열한다.
그리고 AS 키워드와 함께 기본 테이블에 대한 SELECT 문을 제시한다.
SELECT 문은 생성하고자 하는 뷰의 정의를 담고 있는데, ORDER BY를 사용할 수 없다는 점을 제외하면 일반 SELECT 문과 동일하다.
뷰를 구성하는 속성의 이름 리스트는 생략할 수 있는데, 생략하면 SELECT 절에 나열된 속성의 이름을 뷰에서도 그대로 사용한다.
WITH CHECK OPTION은 생성한 뷰에 삽입이나 수정 연산을 할 때 SELECT 문에서 WHERE 키워드와 함께 제시한 뷰의 정의 조건을 위반하면 수행되지 않도록 하는 제약조건을 의미한다.

# 고객 테이블에서 등급이 vip인 고객의 고객아이디, 고객이름, 나이, 등급으로 구성된 뷰를 우수고객이라는 이름으로 생성
CREATE VIEW  우수고객(고객아이디, 고객이름, 나이, 등급)
AS SELECT    고객아이디, 고객이름, 나이, 등급
   FROM      고객
   WHERE     등급 = 'vip'
WITH CHECK OPTION;

# 우수고객 뷰의 모든 내용 검색
SELECT * FROM 우수고객;

# 우수고객 뷰를 구성하는 속성의 이름은 기본 테이블인 고객 테이블에서 검색한 속성의 이름과 같으므로 생략해도 된다.
CREATE VIEW  우수고객
AS SELECT    고객아이디, 고객이름, 나이, 등급
   FROM      고객
   WHERE     등급 = 'vip';
WITH CHECK OPTION;

WITH CHECK OPTION을 포함하고 있으므로 뷰가 생성된 후에 우수고객 뷰에 vip 등급이 아닌 다른 등급의 고객 데이터를 삽입하거나 뷰의 정의 조건을 위반하는 수정 및 삭제 연산을 시도하면 실행하지 않고 거부한다.

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

# 업체별제품수 뷰 모든 내용 검색
SELECT * FROM 업체별제품수;

4-3. 뷰의 활용

CREATE VIEW 문으로 생성된 뷰에서도 일반 테이블처럼 원하는 데이터를 검색할 수 있다.

# 우수고객 뷰에서 나이가 20세 이상인 고객에 대한 모든 내용 검색
SELECT * FROM 우수고객 WHERE 나이 >= 20;

뷰에 대한 SELECT 문이 내부적으로는 기본 테이블에 대한 SELECT 문으로 변환되어 수행되기 때문에 검색할 수 있다.
INSERT, UPDATE, DELETE 문도 뷰를 대상으로 수행할 수 있다.
- 물론 뷰에 대한 삽입, 수정, 삭제 연산도 기본 테이블에 수행되기 때문에 결과적으로는 기본 테이블이 변한다.
- 하지만, 각 연산이 모든 뷰에 허용되는 것은 아니고, 뷰는 기본 테이블을 들여다 보는 창의 역할을 하기 때문에 뷰를 통한 기본 테이블의 변화는 제한적이다.
- 기본 테이블인 제품 테이블의 기본키 속성을 포함하고 있는 경우 INSERT UPDATE DELETE 문 모두 가능하지만,
- 기본키 속성을 포함하지 않는 경우 INSERT UPDATE DELETE 문 모두 연산을 허용하지 않느다.
뷰에 대한 삽입, 수정, 삭제 연산은 결과적으로 기본 테이블의 내용을 자동으로 변경하기 때문에 주의해서 사용해야 한다.
- 검색 연산은 모든 뷰에 수행할 수 있지만, 삽입, 수정, 삭제 연산은 허용되지 않는 뷰가 있다는 사실도 기억해야 한다.
- 즉, 기본 테이블에서 어떤 튜플을 어떻게 변경해야 할지 명확히 제시하지 못하는 뷰는 변경이 허용되지 않는다.

✍🏻 변경이 불가능한 뷰의 중요한 특징

기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 뷰는 변경할 수 없다.
- 기본 테이블에서 NOT NULL로 지정된 속성이 포함되어 있지 않은 뷰는 변경할 수 없는 경우가 있다.
기본 테이블에 있던 내용이 아니라 집계 함수로 새로 계산된 내용을 포함하고 있는 뷰는 변경할 수 없다.
DISTINCT 키워드를 포함하여 정의한 뷰는 변경할 수 없다.
GROUP BY 절을 포함하여 정의한 뷰는 변경할 수 없다.
여러 개의 테이블을 조인하여 정의한 뷰는 변경할 수 없는 경우가 많다.

🤔 뷰의 대표적인 장점

질의문을 조금 더 쉽게 작성할 수 있다.
- 특정 조건을 만족하는 튜플들로 뷰를 미리 만들어두면 사용자가 WHERE 절 없이 뷰를 검색해도 특정 조건을 만족하는 데이터를 검색할 수 있다.
- GROUP BY, 집계 함수, 조인 등을 이용해 미리 뷰를 만들어두면 복잡한 SQL 작성 없이 SELECT와 FROM 절만으로 원하는 데이터를 검색할 수 있다.
데이터의 보안 유지에 도움이 된다.
- 여러 사용자의 요구에 맞는 다양한 뷰를 미리 정의해두고 사용자가 자신에게 제공된 뷰를 통해서만 데이터에 접근하도록 권한을 설정하면, 뷰에 포함되지 않은 데이터를 사용자로부터 보호할 수 있다.
데이터를 좀 더 편리하게 관리할 수 있다.
- 제공된 뷰에 포함되지 않은 기본 테이블의 다른 부분은 사용자가 신경 쓸 필요가 없다.
- 또한, 제공된 뷰와 관련이 없는 다른 테이블의 변화에도 영향을 받지 않는다.

4-4. 뷰의 삭제

뷰를 삭제하기 위해 필요한 SQL 명령은 DROP VIEW 다.

DROP VIEW 뷰_이름;

뷰를 삭제하더라도 기존 테이블은 영향을 받지 않는다.
만약 삭제할 뷰를 참조하는 제약조건이 존재한다면 삭제가 수행되지 않는다.
따라서 삭제하고자 하는 뷰를 참조하는 제약조건을 먼저 삭제해야 한다.

5. 삽입 SQL

📌 삽입 SQL (ESQL, Embedded SQL)

프로그래밍 언어로 작성한 응용 프로그램에 삽입하여 사용하는 SQL 문

SQL 문에 익숙하지 않은 초보 사용자도 삽입 SQL을 포함한 응용 프로그램을 통해 데이터베이스 기능을 쉽게 이용할 수 있다.

🤔 응용 프로그램에 삽입 SQL 문 사용 시 주의점

삽입 SQL 문은 프로그램 안에서 일반 명령문이 위치할 수 있는 곳이면 어디든 삽입할 수 있다.
프로그램 안의 일반 명령문과 구별하기 위해 삽입 SQL 문 앞에 EXEC SQL을 붙인다.
프로그램에 선언된 일반 변수를 삽입 SQL 문에서 사용할 수 있다. 단 SQL 문에서 일반 변수를 사용할 때는 앞에 콜론(:)을 붙여 테이블 이름이나 속성의 이름과 구분한다.

수행 결과로 여러 개의 행을 반환하는 SELECT 문을 삽입 SQL 문으로 사용하는 경우에는 커서(cursor)라는 도구가 필요하다.

커서는 수행 결과로 반환된 여러 행을 한 번에 하나씩 가리키는 포인터 역할을 한다.
프로그램에서 SELECT 문의 수행 결과로 반환되는 여러 행을 한꺼번에 처리할 수 없으므로 커서를 이용해 한 번에 한 행씩 차례로 처리해야 한다.

✍🏻 커서가 필요 없는 삽입 SQL

SQL 문을 실행했을 때 특별히 결과 테이블을 반환하지 않는 CREATE TABLE, INSERT, DELETE, UPDATE, 결과로 행 하나만 반환하는 SELECT 문은 커서가 필요 없다.

✍🏻 커서가 필요한 삽입 SQL

SELECT 문의 실행 결과로 여러 행이 검색되는 경우에는 한 번에 한 행씩 차례로 접근할 수 있게 해주는 커서가 필요하다.

# 삽입 SQL 문의 기본 형식
EXEC SQL DECLARE 커서_이름 CURSOR FOR SELECT 문;

# 프로그램 안에 커서를 여러 개 사용할 수 있으므로 커서는 다음 예처럼 이름으로 구분
EXEC SQL DECLARE product_cursor CURSOR FOR SELECT 제품명, 단가 FROM 제품;

# 커서 선언 후 SELECT 문을 실행하는 명령
EXEC SQL OPEN 커서_이름;

# 커서를 이동하여 처리할 다음 행을 가리키도록 하고, 
# 커서가 가리키는 행으로부터 속성 값들을 가져와 변수에 저장하는 FETCH 문의 기본 형식
EXEC SQL FETCH 커서_이름 INT0 변수_리스트;

# 결과 테이블에는 여러 행이 존재하므로 FETCH 문을 반복해서 여러 번 수행해야 한다.
# 그래서 FETCH 문은 일반적으로 반복문과 함께 사용한다.
EXEC SQL FETCH product_cursor INTO :p_name, :price;

# 커서를 더 사용하지 않을 때
EXEC SQL CLOSE 커서_이름;

참고자료

책 - 데이터베이스 개론(김연희, 2022년 1월)

'컴퓨터과학 > 데이터베이스' 카테고리의 다른 글

[데이터베이스] 4. 데이터 모델링 (0)	2024.11.19
[데이터베이스] 3. 데이터베이스 시스템 (0)	2024.11.18
[데이터베이스] 2. 데이터베이스 관리 시스템 (0)	2024.11.14
[데이터베이스] 1. 데이터베이스 기본 개념 (0)	2024.11.14

블로그의 정보

콰이엇의 개발기록

콰이엇

[데이터베이스] 7. 데이터베이스 언어 SQL

1. SQL의 소개

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

2-1. 테이블의 생성

2-2. 테이블의 변경

2-3. 테이블의 삭재

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

3-1. 데이터의 검색

3-2. 데이터의 삽입

3-3. 데이터의 수정

3-4. 데이터의 삭제

4. 뷰

4-1. 뷰의 개념

4-2. 뷰의 생성

4-3. 뷰의 활용

4-4. 뷰의 삭제

5. 삽입 SQL

참고자료

'컴퓨터과학 > 데이터베이스' 카테고리의 다른 글

블로그의 정보

활동하기

티스토리툴바

1. SQL의 소개

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

2-1. 테이블의 생성

2-2. 테이블의 변경

2-3. 테이블의 삭재

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

3-1. 데이터의 검색

3-2. 데이터의 삽입

3-3. 데이터의 수정

3-4. 데이터의 삭제

4. 뷰

4-1. 뷰의 개념

4-2. 뷰의 생성

4-3. 뷰의 활용

4-4. 뷰의 삭제

5. 삽입 SQL

참고자료

'컴퓨터과학 > 데이터베이스' 카테고리의 다른 글

블로그의 정보

활동하기

공유하기

다른 글

티스토리툴바