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

데이터 베이스

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

ImKDM 2023. 4. 26. 16:11

728x90

- 관계 해석과 SQL의 차이 : 관계 해석을 응용한 것이 SQL이다?

- SQL문 직접 작성해보기

_ 테이블 생성 (제약조건, 외래키 설정)

_ 원하는 데이터 조작하기

- 집계 함수의 종류와 주의 사항

SUM, AVG, COUNT, MAX, MIN

SUM, AVG 는 숫자 데이터 타입만 사용할 수 있고, COUNT, MIN, MAX는 모든 데이터 타입에 사용 가능하다.

NULL 값은 연산에 포함시키지 않으며, SELECT와 HAVING 절에서만 사용 가능하다. WHERE 절에선 사용 불가!

- 뷰를 만들 수 있는 기반은 총 몇개이며 뭐라고 하는가?

기본 테이블, 뷰

- 뷰에 새로운 데이터를 추가할 때 WHERE 절에 정의된 조건의 데이터만 허용하는 키워드는 무엇인가?

WITH CHECK OPTION;

- 뷰에서 검색, 삽입, 수정, 삭제 연산이 되는 원리

결과적으로 기본 테이블에 대한 연산으로 변환되어 수행된다.

- 뷰를 사용하는 이유

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

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

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

SQL의 소개

사용자가 데이터베이스 관리 시스템을 통해 데이터의 삽입, 삭제, 수정, 검색 등의 작업을 수행하고 싶다면 어떻게 DBMS와 소통할 수 있을까? 앞에서 배운 관계 대수, 관계 해석을 사용할 수 있지만 일반 사용자에겐 불친절하다. 조금 더 유용하고 효과적이며 수월한 대안이 SQL이다.

SQL은 사용자가 처리를 원하는 데이터가 무엇인지만 제시하고 데이터를 어떻게 처리해야 하는지를 언급할 필요가 없다. 비절차적 데이터 언어의 특징을 가진다.

SQL은 데이터베이스 관리 시스템에 직접 접근하여 대화식으로 질의를 작성할 수도 있고, Java 같은 언어로 작성한 응용 프로그램에 삽입하여 사용할 수도 있다.

- 데이터 정의어(DDL : Data Definition Language) : 데이터베이스 개체의 생성, 수정, 삭제 기능 제공

- 데이터 조작어(DML : Data Manipulation Language) : 데이터의 생성, 수정, 삭제 기능 제공

- 데이터 제어어(DCL : Data Control Language) : 보안을 위해 데이터 접근 및 사용 권한 부여 및 취소 기능 제공

SQL을 이용한 데이터 정의 (DDL)

- CREATE TABLE : 테이블 생성
- ALTER TABLE : 테이블 변경
- DROP TABLE : 테이블 삭제

테이블의 생성 (CREATE)

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

(1) 테이블을 구성하는 각 속성의 이름과 데이터 타입, 기본적인 제약 사항 정의

(2) 기본키는 테이블에 하나만 존재할 수 있음

(3) 대체키는 테이블에 여러 개 존재할 수 있음

(4) 외래키는 테이블에 여러 개 존재할 수 있음

(5) 데이터 무결성을 위한 제약조건은 테이블에 여러 개 존재할 수 있음

속성의 정의

테이터 타입	의미
INT 또는 INTEGER	정수
SMALLINT	INT보다 작은 정수
CHAR(n)	길이가 n인 고정 길이의 문자열
VARCHAR(n)	최대 길이가 n인 가변 길이의 문자열
DECIMAL(p, s)	고정 소수점 실수 (p: 소수점 제외한 숫자 길이, s: 소수점 이하 숫자 길이)
FLOAT(n)	길이가 n인 부동 소수점 실수
DATE	연, 월, 일로 표현되는 날짜
TIME	시, 분, 초로 표현되는 시간
DATETIME	날짜와 시간

- NOT NULL

테이블을 구성하는 각 속성의 데이터 타입을 선택한 후 널 값 허용 여부를 정한다.

CREATE TABLE 문으로 생성되는 테이블의 속성은 기본적으로 널 값을 허용한다. 그러므로 널 값을 허용하지 않으려면 NOT NULL 키워드를 포함해야 한다.

- DEFAULT

속성에 기본 값을 지정해두지 않으면 사용자가 속성에 값을 입력하지 않았을 때 해당 속성에 널 값이 저장된다. 하지만 DEFAULT 키워드를 사용해 기본 값을 지정해두면 이 기본 값이 저장된다.

숫자 데이터는 그냥 표현하고, 문자열이나 날짜 데이터는 작은 따옴표로 묶어줘야 한다.

키의 정의

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

- 기본키

PRIMARY KEY 키워드를 이용해 지정한다. 기본키가 없어도 테이블을 정의할 수 있지만 기본키는 가능한 한 선택하는 것이 좋다.

모든 테이블에서 기본키는 반드시 하나만 지정할 수 있고, 여러 개의 속성으로 구성할 수도 있다.

- 대체키

UNIQUE 키워드를 사용해 지정한다. 대체키는 기본키와 같이 각 투플을 유일하게 식별한다.

대체키로 지정된 속성의 값은 테이블에서 중복이 안되고, 유일성을 가진다. 그러나 기본키와 다르게 널 값을 가질 수 있다. 대체키는 한 테이블에서 여러 개를 지정할 수 있다.

- 외래키

FOREIGN KEY 키워드를 이용해 지정한다. 외래키는 다른 테이블의 기본키뿐 아니라, UNIQUE 제약조건으로 지정된 대체키를 참조할 수도 있다.

외래키를 지정할 때는 REFERENCES 키워드로 출처를 분명히 밝혀야 한다. 이렇게 하면 참조되는 테이블에서 튜플을 함부로 삭제하거나 변경하지 못해 참조 무결성 제약조건을 지킬 수 있다.

만약 기본키를 삭제하거나 변경하려고 한다면 어떻게 해야할까? 크게 4가지 방법이 존재한다.

(1) ON DELETE NO ACTION : 튜플을 삭제하지 못하게 함 [기본 설정]

(2) ON DELETE CASCADE : 관련 튜플을 함께 삭제 함

(3) ON DELETE SET NULL : 관련 튜플의 외래키를 NULL로 변경 함

(4) ON DELETE SET DEFAULT : 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경 함

만약 부서 테이블의 "홍보부" 튜플을 삭제하려고 한다면,

ON DELETE NO ACTION : 부서 테이블을 참조하는 사원 테이블이 있으므로 부서 테이블 튜플 삭제 안됨!

ON DELETE CASCADE : 부서 테이블을 참조하는 사원 테이블의 튜플까지 함께 삭제!

ON DELETE SET NULL : 사원 테이블에서 홍보부에 소속된 "정소화" 튜플의 소속부서 속성 값을 NULL로 변경!

ON DELETE SET DEFAULT : 사원 테이블에서 홍보부에 소속된 "정소화" 튜플의 소속부서 속성 값을 기본값으로 변경!

기본키 튜플을 변경할 때도 다음 네 가지 방법 중 하나를 선택한다.

(1) ON UPDATE NO ACTION : 튜플을 변경하지 못하게 함 [기본 설정]

(2) ON UPDATE CASCADE : 관련 튜플의 외래키 값을 함께 변경 함

(3) ON UPDATE SET NULL : 관련 튜플의 외래키 값을 NULL로 변경 함

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

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

- CHECK

CHECK 키워드를 사용하면 특정 속성에 대한 제약조건을 지정할 수 있다. 테이블에 새로운 튜플을 삽입하거나 기존 튜플을 수정할 때도 이 제약 조건을 반드시 지켜야 하기 때문에 테이블에는 항상 유효한 데이터만 유지될 수 있다.

CONSTRAINT 키워드를 추가하면 지정한 제약조건에 고유 이름을 부여할 수 있다. 그러면 테이블을 생성한 이후 제약조건을 수정하거나 제거할 때 식별하기 편리하다.

테이블의 변경 (ALTER)

새로운 속성 추가 (ADD)

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

- ALTER TABLE 고객 ADD 가입날짜 DATE;

기존 속성 삭제 (DROP COLUMN)

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

- ALTER TABLE 고객 DROP COLUMN 가입날짜;

새로운 제약조건 추가 (ADD CONSTRAINT)

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

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

기존 제약조건 삭제 (DROP CONSTRAINT)

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

- ALTER TABLE 고객 DROP CONSTRAINT CHK_AGE;

테이블의 삭제 (DROP)

DROP TABLE 키워드를 통해 테이블을 삭제할 수 있다.

DROP TABLE 테이블_이름;

테이블을 삭제할 때도 참조 무결성 제약조건을 지켜야하기 때문에 참조되고 있는 테이블이 있는 경우 삭제가 수행되지 않을 수 있다.

SQL을 이용한 데이터 조작 (DML)

- SELECT : 데이터 검색
- INSERT : 데이터 삽입
- UPDATE : 데이터 수정
- DELETE : 데이터 삭제

데이터의 검색 (SELECT)

테이블에서 원하는 데이터를 검색하기 위해선 SELECT 가 필요하다.

기본 검색

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

검색하고 싶은 속성의 이름은 콤마(,)로 구분하여 차례로 나열한다. 그리고 FROM 키워드와 함께 검색하고 싶은 속성이 있는 테이블의 이름을 콤마(,)로 구분하여 차례로 나열한다.

SELECT 문은 테이블을 대상으로 하고 수행 결과도 테이블이다.

테이블에 존재하는 모든 속성을 검색하기 위해 *를 사용할수도 있다. *을 사용하면 결과 테이블의 속성 순서가 원본 테이블이 정의한 속성 순서와 같다.

DISTINCT 키워드를 사용하면 결과 테이블에서 튜플의 중복을 제거하고 한 번씩만 출력되도록 한다.

AS 키워드를 사용하면 결과 테이블에 출력되는 속성의 이름을 다른 이름으로 바꾸어 출력할 수 있다.

산술식을 이용한 검색

산술식을 함께 제시할 수 있다. 산술식은 속성의 이름과 +, -, *, / 등의 산술 연산자, 상수로 구성한다.

조건 검색

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

WHERE 키워드를 함께 사용하면 조건을 제시할 수 있다.

조건에는 비교 연산자, 논리 연산자를 이용할 수 있는데, 숫자 값은 그대로 작성해도 되지만 문자나 날짜 값은 속성의 이름과 구별할 수 있도록 작은 따옴표로 묶어야 한다.

LIKE를 이용한 검색

검색 조건을 부분적으로만 알고 있다면 LIKE 키워드를 이용할 수 있다. 대신 LIKE 키워드는 문자열을 이용하는 조건에만 사용할 수 있다.

기호	설명	사용 예	설명
%	0개 이상의 문자	LIKE '데이터%' LIKE '%데이터' LIKE '%데이터%'	데이터로 시작하는 문자열(길이 상관 없음) 데이터로 끝나는 문자열(길이 상관 없음) 데이터가 포함된 문자열
_	1개의 문자	LIKE '데이터_ _ _'	데이터로 시작하는 6자 길이의 문자열

NULL을 이용한 검색

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

검색 조건에서 '나이 = NULL'의 형태로 표현하는 것은 안 된다. 반드시 IS NULL 키워드를 사용해야 한다.

검색 조건에서 다른 값들과 널 값을 비교하면 결과는 언제나 거짓(false)가 된다. 예를 들어 재고량 값이 널일 때 어떤 비교 연산자를 사용해도 결과가 모두 거짓이 된다.

정렬 검색

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

기본적으로 검색 결과는 DBMS가 정한 순서로 출력된다. 따라서 사용자가 원하는 순서로 출력하려면 ORDER BY 키워드를 사용한다.

ORDER BY 키워드와 함께 정렬 기준이 되는 속성을 지정하고, 오름차순 정렬이면 ASC, 내림차순 정렬이면 DESC로 표현한다. 기본 정렬은 오름차순(ASC)이므로 굳이 작성해주지 않아도 된다.

널 값의 경우 오름차순일 경우 맨 위에 출력되고, 내림차순일 땐 마지막에 출력된다.

정렬 기준을 여러 속성에 적용하고 싶을 때는 정렬 우선순위대로 속성 이름을 작성하면 된다.

집계 함수를 이용한 검색

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

집계 함수는 열 함수(column function)라고도 하며 개수, 합계, 평균, 최대값, 최솟값의 계산 기능을 제공한다. SELECT 문과 함께 자주 사용되는 집계 함수는 총 5가지이다.

함수	의미	사용 가능한 속성의 타입
SUM	속성 값의 합계	숫자 데이터
AVG	속성 값의 평균	숫자 데이터
COUNT	속성 값의 개수	모든 데이터
MAX	속성 값의 최대값
MIN	속성 값의 최소값

집계 함수를 사용할 시 주의사항이 있다.

- 널 값은 연산에 포함시키지 않는다. 아예 배제!!

- SELECT, HAVING 절에서만 사용할 수 있다. WHERE 절에선 사용 불가!!

집계 함수의 결과는 계산을 통해 새로 생성된 값이다. 따라서 속성의 이름이 별도로 지정되어 있지 않다. 집계 함수를 이용해 계산된 결과 값을 출력할 때는 AS 키워드를 사용해 새 이름을 부여해주는 것이 좋다.

집계 함수는 널 값을 포함하지 않기 때문에 널 값을 가지고 있는 튜플의 총 개수를 COUNT(속성 이름)으로 찾을 시 전체 개수가 출력되지 않는다. 대신 *는 모든 속성을 의미하는 기호이므로 모든 속성의 값으로 구성된 튜플을 대상으로 개수를 계산한다. 따라서 COUNT(*) 의 결과는 널 값까지 포함하여 테이블에 있는 모든 튜플의 개수가 된다.

원한다면 DISTINCT 키워드를 함께 사용해 특정 속성 값의 중복을 없앤 후 집계 함수를 적용할 수도 있다.

그룹별 검색

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

테이블에서 특정 속성의 값이 같은 튜플을 모아 그룹을 만들고, 그룹별로 검색을 하기 위해 GROUP BY 키워드를 사용한다. GROUP BY 키워드가 없는 SELECT 문은 테이블 전체를 하나의 그룹으로 보고 검색하는 것이다.

GROUP BY 키워드와 함께 그룹을 나누는 기준이 되는 속성을 지정한다. 그리고 그룹에 대한 조건은 HAVING 키워드와 함께 작성한다.

그룹을 나누는 기준이 되는 속성을 SELECT 절에도 작성하는 것이 권고된다. 해당 내용이 없으면 어떤 그룹에 대한 검색 결과인지 알기 힘들어진다. 예를 들어 주문제품별 수량의 합계를 구할 때 SELECT 문에 기준이 되는 속성을 적시하지 않으면 어떤 제품에 해당하는 총주문수량인지 알 수 없다.

일반적인 검색 조건은 WHERE 절에 작성하지만, 그룹에 대한 조건은 HAVING 절에 작성한다. 다시 이야기하지만 집계 함수는 SELECTE와 HAVING 절에서만 사용 가능하다.

GROUP BY 키워드를 사용할 때 주의사항이 있다.

그룹별로 검색할 때는 집계 함수나 GROUP BY 절에 있는 속성 외의 속성은 SELECT 절에 사용할 수 없다. 다시 말해 GROUP BY 키워드를 사용할 때는 SELECT 문에서 집계 함수 또는 GROUP BY 절에 포함된 속성만 검색할 수 있다.

그룹화시키는 건 복수로 가능하다. 기준이 되는 속성을 차례대로 기입하면 1차 그룹화, 2차 그룹화... N차 그룹화까지 진행할 수 있다.

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

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

여러 개의 테이블을 연결하여 데이터를 검색할 수도 있다. 그러기 위해선 테이블을 연결해주는 속성이 필요하다. 이러한 속성을 조인 속성이라 한다. 조인 속성의 이름은 달라도 되지만 도메인은 반드시 같아야 한다. 일반적으로 테이블의 관계를 나타내는 외래키를 조인 속성으로 이용한다.

검색을 위한 데이터가 서로 다른 테이블에 있을 땐 한 가지 테이블만 검색해선 원하는 데이터를 얻어낼 수 없다. 예를 들어 특정 고객이 주문한 제품의 정보를 검색하려면, 주문 테이블과 제품 테이블 두 개가 필요하다. 그리고 두 테이블을 연결하려면 두 테이블이 공통으로 가지고 있는 조인 속성이 요구된다. 즉 주문 테이블의 제품 번호 속성과 주문 테이블의 주문제품 속성이 조인 속성의 역할을 한다.

이를 SQL문으로 작성한다면, FROM 절에 검색에 필요한 모든 테이블을 나열하고, WHERE 절에 조인 속성의 값이 같아야 함을 의미하는 조인 조건을 제시한다. 이때 두 테이블의 조인 속성 이름이 동일할 수 있기 때문에 테이블의 이름과 속성의 이름을 . 기호로 연결한다. 만약 속성 이름이 서로 다르다면 소속 테이블의 이름은 생략이 가능하다.

위 예시를 보면 FROM 절에 검색에 필요한 제품, 주문 테이블을 모두 나열한다.

그리고 WHERE 절에는 주문고객이 'banana'란 조건과 제품 및 주문 테이블의 조인 속성의 값이 같아야 한다는 조인 조건을 제시한다.

테이블의 이름이 길면 속성 이름 앞에 소속 테이블을 표기하는 일이 번거로울 수 있다. 테이블의 이름을 대신하는 단순한 별명을 사용할 수 있는데, FROM 절에 테이블의 이름과 별명을 함께 제시하면 된다. 이때 AS 키워드는 생략할 수 있다.

지금까지 WHERE 절에 조인 조건을 제시하여 조인 검색을 위한 SQL 문을 작성했다. 이것이 일반적인 방법이지만, 표준 SQL에서는 정석으로 작성하는 방법이 따로 있다. 바로 INNER JOIN과 ON 키워드를 이용해 작성하는 것이다.

INNER JOIN (내부 조인)

조인 중 가장 많이 사용되는 조인으로써 관계 대수 중 순수 관계 연산자에서의 동등 조인에 해당한다. 일반적으로 JOIN이라고 하면 이 INNER JOIN을 지칭한다.

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

만약 위 형식에서 INNER JOIN을 그냥 JOIN이라고만 써도 INNER JOIN으로 인식한다.

OUTER JOIN (외부 조인)

순수 관계 연산자에서 외부 조인과 동일하다. 즉 조인 조건이 만족되지 않는 행까지도 포함시킨다. 자주 사용되지는 않지만 가끔 유용하게 사용되는 방식이다.

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

모든 튜플을 검색 대상으로 하는 테이블이 무엇이냐에 따라 왼쪽 외부 조인(LEFT OUTER JOIN), 오른쪽 외부 조인(RIGTH OUTER JOIN), 완전 외부 조인(FULL OUTER JOIN)으로 나뉜다.

- 왼쪽 외부 조인 : 왼쪽 테이블의 것은 모두 출력되어야 한다.

- 오른쪽 외부 조인 : 오른쪽 테이블의 것은 모두 출력되어야 한다.

- 완전 외부 조인 : 양쪽 모두에 조건이 일치하지 않는 것을 모두 출력한다. (왼쪽 외부 조인 + 오른쪽 외부 조인)

흥미로운 사실은, 왼쪽 외부 조인에서 단순히 왼쪽과 오른쪽 테이블의 위치만 바꿔도 오른쪽 외부 조인과 결과가 동일하다. 즉 테이블이 놓여진 위치에 따라서 결과가 결정된다.

부속 질의문을 이용한 검색

SELECT 문 안에 또 다른 SELECT 문을 포함할 수 있다. 이처럼 다른 SELECT 문 안에 들어 있는 SELECT 문을 부속 질의문 또는 서브 질의문(sub query)이라 한다.

부속 질의문은 괄호로 묶어 작성하며, ORDER BY 절을 사용할 수 없다. 상의 질의문보다 먼저 수행되고, 그 결과를 이용해 상의 질의문을 수행하여 최종 결과 테이블을 반환한다.

부속 질의문은 크게 2가지로 나뉜다. 부속 질의문의 종류에 따라 사용할 수 있는 연산자가 다르니 주의해야 한다.

종류	설명	가능 연산자
단일 행 부속 질의문	결과로 하나의 행을 반환	일반 비교 연산자 O
다중 행 부속 질의문	결과로 다중 행을 반환	일반 비교 연산자 X

가능한 연산자란 의미는 서브 질의문을 통해 반환된 결과를 연산할 수 있는 연산자의 종류를 말한다. 예를 들어보자.

"새콤달콤을 생산한 제조업체가 만든 제품들의 제품명과 단가를 검색하고 싶다"

일단 최종적으로 알고 싶은 건 특정 제조업체의 '제품명'과 '단가'이다. 그리고 이 질의에 답을 하기 위해선 먼저 새콤달콤이란 제품을 생산하는 업체가 무엇인지 알아야 한다. 즉 연산의 과정이 두번 이상이 필요하다.

SELECT 제품명, 단가
FROM 제품
WHERE 제조업체 = (SELECT 제조업체
                FROM 제품
                WHERE 제품명 = '새콤달콤');  // 단일 행 부속 질의문

위 SQL문에선 새콤달콤이란 제품을 생산한 제조업체가 무엇인지 먼저 질의한다. 그 결과 '해태'라는 답변이 나오고, 해태란 제조업체의 제품명과 단가가 다음으로 반환된다. 그리고 단일 행 부속 질의문이라서 '='라는 일반 비교 연산자를 사용할 수 있다.

두 번째 예시다.

"core 고객이 주문한 제품의 제품명과 제조업체를 검색하고 싶다"

SELECT 제품명, 제조업체
FROM 제품
WHERE 제품번호 IN (SELECT 주문제품
                FROM 주문
                WHERE 주문고객 = 'core');  // 다중 행 부속 질의문

최종적으로 반환되는 값은 '제품' 테이블에 있다. 하지만 제품 테이블에선 주문 내역을 알 수 없다. 따라서 주문 테이블에서 core 고객이 주문한 제품 번호를 도출한 뒤, 해당하는 여러 값에 부합되는 제품번호를 반환한다. 이 부속 질의문은 결과 값을 여러 개 반환하는 다중 행 부속 질의문이기 때문에 일반 연산자가 아닌 IN 연산자를 사용했다.

다중 행 부속 질의문에 사용한 연산자는 다음과 같다.

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

언급하고 갈 내용 중 하나는, 동일한 결과를 반환하는데 다양한 형식이 존재한다는 것이다. 예를 들어 IN 연산자를 이용한 질의문의 반환값과 조인 질의 또는 EXISTS 연산자를 이용한 결과가 똑같다.

데이터의 삽입

테이블에 새로운 튜플을 삽이하는 방법은 크게 두 가지가 있다.

테이블에 튜플을 직접 삽입

테이블에 튜플을 직접 삽입하는 키워드는 INSERT 이다.

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

INTO 키워드와 함께 튜플을 삽입할 테이블의 이름을 제시한다. 그리고 속성의 이름을 나열하는데, 이 나열 순서대로 VALUES 키워드 다음의 속성 값들이 차례로 삽입된다. 그래서 INTO 절의 속성 이름과 VALUES 절의 속성 값은 순서대로 일대일 대응되고 개수도 같아야 한다.

INTO 절에서 속성 이름의 리스트는 생략할 수 있다. 만약 생략한다면 테이블을 정의할 때 지정한 속성의 순서대로 VALUES 절의 속성 값이 삽입된다.

INSERT INTO 고객(고객아이디, 고객이름, 나이, 등급, 직업, 적립금)
VALUES ('core', '홍길동', 30, 'vip', '회사원', 1000);


INSERT INTO 고객
VALUES ('core', '홍길동', 30, 'vip', '회사원', 1000);

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

부속 질의문인 SELECT 문을 이용해 다른 테이블에서 검색한 데이터를 튜플로 삽입할 수도 있다.

즉 내가 직접 튜플의 내용을 작성하는 것이 아니라 기존 테이블에서 연산된 결과를 가지고 새로운 튜플을 만드는 것이다.

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

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

위 쿼리문을 통해 한빛제과에서 만들어진 모든 제품의 이름, 재고량, 단가 정보들이 튜플로 삽입된다.

데이터의 수정

테이블에 저장된 데이터를 수정하기 위한 키워드는 UPDATE 이다.

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

테이블에 저장된 튜플에서 특정 속성의 값을 수정한다. 가장 먼저 어떤 테이블에서 수정을 할 것인지 정한다. 그 다음 SET 키워드 다음을 통해 어떤 속성의 값을 어떻게 바꿀지 정한다. 만약 따로 조건을 주고 싶다면 선택적으로 WHERE 조건절을 추가할 수 있다. WHERE 절이 생략되면 모든 튜플의 값을 변경한다.

UPDATE 문에 부속 질의문이 포함되는 경우도 있다.

데이터의 삭제

테이블에 저장된 데이터를 삭제하기 위한 키워드는 DELETE 이다.

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

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

뷰 (VIEW)

뷰의 개념

뷰는 다른 테이블을 기반으로 만들어진 가상 테이블이다. 이 말은 일반 테이블과 달리 데이터를 실제 저장하고 있지 않기 때문이다. 일반 테이블은 물리적으로 존재하면서 실제로 데이터를 저장한다. 반면 뷰는 논리적으로만 존재하면서도 일반 테이블과 동일한 방법으로 사용할 수 있다. 그래서 일반 사용자는 그 차이를 느끼기 어렵다.

뷰는 하늘에서 뚝딱 만들어지지 않는다. 기본적으로 물리적인 테이블이 필요하다. 이를 기본 테이블(base table)이라고 한다. 일반적으로 기본 테이블을 기반으로 뷰를 생성하지만, 다른 뷰를 기반으로 새로운 뷰를 만들 수 있다.

우리가 카페에 갔을 때 풍경이 좋으면 "와, 여기 뷰 좋네"라고 말한다. 이처럼 뷰는 테이블을 들여다 볼 수 있는 창 역할을 한다. 창을 통해 바깥 풍경을 볼 수 있듯이, 뷰를 통해 기본 테이블을 들여다볼 수 있다. 똑같은 풍경이지만 창의 크기나 위치에 따라 퓽경이 달라보이는 것처럼 동일한 기본 테이블도 어떤 뷰로 보느냐에 따라 보이는 부분이 달라진다. 그리고 창문을 통해 바깥 풍경을 볼 수 있지만 실제 밖을 만질 수 없는 것처럼 뷰을 통해 기본 테이블의 내용을 검색할 수는 있지만, 기본 테이블의 내용을 바꾸는 작업은 한계가 있다.

뷰의 생성

뷰의 생성과 삭제도 DDL에 해당하기에 CREATE 키워드를 사용한다.

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

CREATE VIEW 명령어와 함께 새로 생성할 뷰의 이름을 제시한다. 그리고 뷰를 구성하는 속성의 이름을 괄호 안에 나열한다. 속성 리스트를 생략한다면 SELECT 절에 나열된 속성의 이름을 뷰에서도 그대로 사용한다.

AS 키워드와 함께 기본 테이블에 대한 SELECT 문을 제시하는데, 여기엔 생성하고자 하는 뷰의 정의를 담는다. ORDER BY를 사용할 수 없다는 점만 빼면 일반 SELECT 문과 동일하다.

WITH CHECK OPTION 키워드는 생성한 뷰에 삽입이나 수정 연산을 할 때 SELECT 문에서 WHERE 키워드와 함께 제시한 뷰의 정의 조건을 위반하면 수행되지 않도록 하는 제약조건을 의미한다.

위 SQL 문을 보면, 고객 테이블에서 'vip' 등급을 가진 고객의 정보로 우수고객이란 뷰를 생성했다.

만약 우수고객 뷰에 튜플을 추가할 일이 생기면 WITH CHECK OPTION을 포함하고 있기 때문에 등급이 vip가 아닌 고객의 데이터는 실행되지 않는다. 마치 클럽 입구에서 물관리를 하는 덩치 형님들이 지키고 서 있다고 생각하자.

뷰를 생성할 때 속성 리스트를 생략할 수 있는 경우는 SELECT 문으로 반환된 기본 테이블의 속성과 뷰에서 사용할 속성이 동일할 때이다.

뷰의 활용

생성한 뷰에서도 일반 테이블처럼 원하는 데이터를 검색할 수 있다.

뷰가 가상 테이블임에도 데이터를 검색할 수 있는 이유는 무엇인가?

뷰에 대한 SELECT 문이 내부적으로는 기본 테이블에 대한 SELECT 문으로 변환되어 수행되기 때문이다. 따라서 위 예시에서 우수 고객 뷰에 대한 SELECT 문은 우수고객 뷰의 기본 테이블인 고객 테이블에 대한 SELECT 문으로 변환되어 수행된 후, 그 결과 테이블을 반환하는 것이다.

따라서 뷰를 대상으로 INSERT 문, UPDATE 문, DELETE 문을 수행했을 때 모두 기본 테이블에서 수행되기 때문에 결과적으로는 기본 테이블이 변한다는 점을 유의해야 한다. 그러나 어디까지나 뷰는 기본 테이블을 들여다 보는 창의 역할이라서 뷰를 통한 기본 테이블의 변화는 제한적이다.

뷰의 수정이 기본 테이블에 영향을 줌으로써 뷰의 변경은 다음 제약이 생긴다.

- 기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 뷰는 변경이 불가능하다. 왜냐하면 기본키를 포함하지 않는 뷰에 새로운 튜플을 추가한다고 가정했을 때 기본 테이블엔 널 값을 가지는 기본키 속성의 튜플이 생기기 때문이다. 동일한 이유로 기본 테이블에서 NOT NULL로 지정된 속성이 포함되어 있는 않은 뷰는 변경할 수 없다.

- 기본 테이블에 있던 내용이 아니라 집계 함수로 새로 계산된 내용을 포함하고 있는 뷰는 변경할 수 없다.

- DISTINCT 키워드를 포함하여 정의한 뷰는 변경할 수 없다.

- GROUP BY 절을 포함하여 정의한 뷰는 변경할 수 없다.

- 여러 개의 테이블을 조인하여 정의한 뷰는 변경할 수 없는 경우가 많다.

제약이 많은 뷰를 사용하는 이유

뷰에 대한 연산이 어짜피 기본 테이블에 대한 연산으로 변환되어 수행되는데 번거롭게 뷰를 정의하는 이유는 다음과 같다.

1) 질의문을 좀 더 쉽게 작성

특정 조건을 만족하는 튜플들로 뷰를 미리 만들어놓으면, 사용자가 별도로 WHERE 절 없이 뷰만 검색해서 특정 조건을 만족하는 데이터를 검색할 수 있다. 또한 GROUP BY, 집계 함수, 조인 등을 이용해 미리 뷰를 만들어 놓으면, 복잡한 SQL 문을 작성하지 않아도 원하는 데이터를 검색할 수 있다.

2) 데이터 보안 유지에 도움

여러 사용자의 요구에 맞는 다양한 뷰를 미리 정의해 두고 사용자가 자신에게 제공된 뷰를 통해서만 데이터에 접근하도록 권한을 설정하면, 뷰에 포함되지 않은 데이터의 노출을 사용자로부터 예방할 수 있다.

3) 데이터를 좀 더 편리하게 관리

제공된 뷰에 포함되지 않은 기본 테이블의 다른 부분은 사용자가 신경쓰지 않아도 되며, 제공된 뷰와 관련없는 다른 테이블의 변화에도 영향을 받지 않는다.

뷰의 삭제

뷰를 삭제하기 위한 키워드는 DROP VIEW 이다.

DROP VIEW 뷰_이름;

뷰를 삭제해도 기본 테이블은 영향을 받지 않는다.