[DATABASE] 데이터베이스 개론_CH7

데이터베이스 개론2판(김연희)를 읽고 복습목적으로 내용을 정리한 글입니다.

1. SQL이란

SQL은 관계 데이터베이스를 위한 표준 질의어로 많이 사용되는 언어이다.

• SQL은 직접 DBMS에 접근할 수도 있고, C++이나 Java와 같은 언어로 작성한 응용프로그램에 삽입하여 사용할 수도 있다.

• 기능에 따라 데이터 정의어(DDL), 데이터 조작어(DML), 데이터 제어어(DCL)로 나눈다.

  • 데이터 정의어: 테이블 생성, 변경, 제거
  • 데이터 조작어: 테이블에 데이터 삽입, 수정, 삭제, 검색
  • 데이터 제어어: 데이터베이스 접근 및 사용권한 관리

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

2.1 테이블 생성 (CREATE TABLE)

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. 데이터 무결성을 위한 제약조건을 정의한다.

- [ ]로 표시된 항목은 생략 가능

2.1.1 속성의 정의

오라클 DB2 데이터 타입

• SQL에서 지원하는 데이터 타입을 이용하여 속성의 도메인을 정의한다.
• 기본적으로 널 값을 허용하므로 필요시 NOT NULL 제약조건을 함께 정의한다.

2.1.2 키의 정의

기본키

• 기본키 없이도 테이블을 정의할 수 있지만, 가능한 한 선택하는 것이 좋다.
• 테이블 당 하나

대체키

• 기본키와 달리 널 값을 가질 수 있다.
• 여러개 지정 가능

외래키

• 키값의 출처 테이블을 REFERENCES키워드를 뒤에 밝혀야 한다.
• 참조 무결성 제약조건을 유지하기 위해, 참조 관계에 속한 투플이 삭제될 때 취할 조치를 선택할 수 있다.
  • ON DELETE/UPDATE NO ACTION: 투플 삭제/변경 금지
  • ON DELETE/UPDATE CASCADE: 관련 투플 함께 삭제/변경
  • ON DELETE/UPDATE SET NULL: 관련 투플의 외래키 값을 NULL로 변경
  • ON DELETE/UPDATE DEFAULT: 관련 투플의 외래키 값을 미리 지정한 기본 값으로 변경

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

• CHECK키워드로 정의한다.
• 해당 테이블에는 제약조건을 만족하는 투플만 존재한다.
• CONSTRAINT키워드로 제약조건의 이름을 명시할 수 있다.

예시

CRAET TABLE 제품(
	제품번호	CHAR(3)		NOTNULL,
    제품명	VARCHAR(20),
    재고량	INT,
    단가		INT,
    PRIMARY KEY(제품번호),
    CHECK (재고량>=0 AND 재고량 <=10000)
);

2.2 테이블 변경 (ALTER TABLE)

ALTER TABLE문으로 새로운 속성 추가, 변경, 삭제와 제약조건 추가, 삭제 등이 가능하다.

새로운 속성 추가

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

기존 속성 삭제

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

• 삭제할 속성과 관련된 제약조건이 존재하면 삭제할 수 없다.
• 이 속성을 참조하는 다른 속성이 존재할 경우 삭제할 수 없다.

제약조건 추가

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

2.3 테이블 삭제 (DROP TABLE)

DROP TABLE 테이블_이름;

• 해당 테이블을 참조하는 테이블이 있다면 삭제가 불가능할 수 있다. (외래키 제약조건에 따라 달라진다.)

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

원하는 데이터를 검색, 새로운 데이터 삽입, 데이터 수정, 삭제로 분류할 수 있다.

3.1 검색(SELECT)

3.1.1 기본 검색

SELECT [ALL: DISTINCT] 속성_리스트 [AS 바꿀_속성_이름]
FROM 테이블_리스트

• 기본적으로 SELECT문의 결과는 테이블이며 중복된 투플을 허용한다.
  • DISTINCT 키워드로 중복을 제거할 수 있다.
• AS 키워드로 속성의 이름을 바꾸어 출력할 수 있다.
• *은 속성 리스트 전체를 의미한다.

3.1.2 산술식 검색

• +,-,*,/ 등의 산술 연산자와 상수를 SELECT문에 사용할 수 있다.

ex

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

3.1.3 조건 검색

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

• WHERE키워드와 조건을 이용해 특정 조건을 만족하는 투플만 결과로 가져올 수 있다.

3.1.4 LIKE를 이용한 검색

• 문자열의 일부를 이용해 검색하는 경우 LIKE키워드를 확인할 수 있다.

%: 0개 이상의 문자
_: 1개의 문자

사용 예시

• LIKE 데이터%
• LIKE 데이터___
• LIKE %데이터%

3.1.5 NULL을 이용한 검색

• 특정 값이 널인지 확인하려면 IS NULL키워드를 이용한다.
• 반대는 IS NOT NULL
• NULL값은 어떤 연산자를 이용해 비교하더라도 false를 반환한다. 따라서 널값인지 확인하기 위해서는 반드시 위의 키워드를 사용한다.

3.1.6 정렬 검색

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

• ORDER BY키워드를 이용해 결과를 정렬한다.
  • ASC: 오름차순(기본값)
  • DESC: 내림차순
• NULL값은 오름차순 기준 마지막에 출력된다.

3.1.7 집계 함수를 이용한 검색

SELECT AVG(단가)
FROM 제품;

• 집계 함수는 열 함수(colum function)라고도 하며 개수, 합계, 평균, 최대값, 최솟값의 계산 기능을 제공한다.

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

- NULL값은 제외하고 계산한다.
- WHERE 절에는 사용할 수 없다.
- SUM, AVG는 숫자형 데이터 타입에만 사용할 수 있다.

3.1.8 그룹별 검색

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

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

• HAVING키워드로 조건을 추가로 명시할 수 있다.
• GROUP BY절에는 집계함수나 GROUP BY절에 있는 속성외의 속성은 SELECT절에 사용할 수 없다.

GROUP BY로 1차로 그룹을 나누고 이후 집계함수 등을 이용해 연산을 수행한다.

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

• 여러개의 테이블을 이용해 결과를 출력하는 경우 사용한다.
• FROM절에 검색에 필요한 모든 테이블을 나열하고, WHERE절에는 조인 속성의 값이 같아야 함을 의미하는 조인 조건을 제시한다.

ex) banana고객이 주문한 제품의 이름을 검색하는 경우

SELECT 제품, 제품명
FROM 제품, 주문
WHERE 주문.주문고객 = 'banana` AND 주문.제품번호 = 제품.제품번호;

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

SELECT문(메인 쿼리) 안에 또 하나의 SELECT문(서브 쿼리)을 포함할 수 있다.

• 서브쿼리가 단일행을 반환하는 경우 일반 연산자를 사용할 수 있다.
• 서브쿼리가 다중 행을 반환하는 경우에는 일반 비교 연산자를 사용할 수 없다.

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

SELECT 제품명, 제조업체
FROM 제품
WHERE 제품번호 IN (SELECT 주문제품
				  FROM 주문
                  WHERE 주문고객='banana`);

SELECT 제품명, 단가, 제조업체
FROM 제품
WHERE 단가 > ALL (SELECT 단가 
				FROM 제품
                WHERE 제조업체 = '대한식품');

3.2 데이터 삽입(INSERT)

3.2.1 직접 삽입

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

• INTO절의 속성 리스트와 VALUES절은 속성 값은 순서대로 일대일 대응되고 개수도 같아야 한다.

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

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

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

3.3 데이터 수정(UPDATE)

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

• WHERE절을 생략하면 테이블에 존재하는 모든 투플을 대상으로 SET절에 따라서 데이터를 수정한다.

3.4 데이터 삭제(DELETE)

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

• WHERE절 생략시 모든 투플이 삭제된다. 하지만 DROP TABLE문으로 테이블 자체를 삭제하는 것과는 다르다.

4. 뷰(View)

4.1 뷰의 개념

뷰(view)는 다른 테이블을 기반으로 만들어진 가상 테이블이다.

• CREATE TABLE로 만들어진 테이블을 기본 테이블이라고 한다.
• 기본테이블을 기반으로 논리적으로만 존재하는 가상 테이블을 만든 것이 뷰이다.

4.2 뷰의 생성(CREATE VIEW)

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

• SELECT문은 생성하고자 하는 뷰의 정의를 담는다.
  • ORDER BY를 사용할 수 없다.
• WITH CHECK OPTION키워드를 포함하는 경우, 생성 시 포함한SELECT에 명시된 조건을 위반하는 데이터의 삽입, 수정 및 삭제 연산을 뷰에 시도하는 경우 거부한다.

4.3 뷰의 활용

생성된 뷰에서도 일반 테이블처럼 SELECT문을 사용하여 데이터를 검색할 수 있다.

• 뷰에 대한 SELECT문이 내부적으로 기본 테이블에 대한 SELECT문으로 변환되어 수행되기 때문이다.

뷰에 대한 연산

기본적으로 뷰에 대해서 데이터 삽입, 수정, 삭제 연산이 가능하지만 모든 뷰에 가능한 것은 아니다.

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

굳이 기본테이블 말고 이러한 제약사항이 존재하는 뷰를 생성하고 사용하는 이유는 무엇일까

뷰의 장점

1. 질의문을 더 쉽게 작성할 수 있다.
   • 특정 조건을 만족하는 뷰를 미리 만들어두면 사용자가 WHERE절 없이 특정 조건을 만족하는 데이터를 검색할 수 있다.
2. 데이터 보안 유지
   • 미리 생성해둔 뷰를 통해서만 데이터에 접근하도록 권한을 설정하면, 기본 데이터를 사용자로부터 보호할 수 있다.
3. 데이터를 좀 더 편리하게 관리할 수 있다.
   • 제공된 뷰에 포함되지 않은 데이터는 사용자가 신경 쓸 필요가 없다.

4.4 뷰의 삭제(DROP VIEW)

DROP VIEW 뷰_이름

• 뷰를 삭제하더라도 기본 테이블은 영향을 받지 않는다.
• 뷰를 참조하는 제약조건이 존재한다면 삭제가 수행되지 않는다.

5. 삽입 SQL

C, C++, Java같은 프로그래밍 언어로 작성된 응용 프로그램 안에 삽입하여 사용할 수도 있다.

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

EXEC SQL BEGIN DECLARE SECTION;
	char p_no[4], p_name[21];
    int price;
EXEC SQL END DECLARE SECTION;

...

EXEC SQL SELECT 제품명, 단가 INTO :p_name, :price
	FROM 제품
    WHERE 제품번호 = :p_no;    
...

Reference

IT CookBook, 데이터베이스 개론(2판): 기초 개념부터 빅데이터까지_김연희