[DB] 데이터베이스 개론 - 7장 데이터베이스 언어 SQL

7장, 데이터베이스 언어 SQL

 

 

데이터베이스 언어

• 데이터 정의어 : 테이블을 생성하고 변경·제거하는 기능
• 데이터 조작어 : 테이블에 새 데이터 삽입, 테이블에 저장된 데이터 수정·삭제·검색 기능
• 데이터 제어어 : 보안을 위해 데이터에 대한 접근 및 사용 권한을 부여·취소하는 기능

 

 

데이터 정의어(DDL)

 

CREATE, 생성

테이블 생성 명령어 : CREATE TABLE

테이블을 구성하는 속성들의 이름, 데이터 타입 및 제약 사항에 대한 정의, 기본키·대체키·외래키의 정의, 데이터 무결성을 위한 제약조건 정의 등

 

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

▶ NOT NULL 키워드

속성 정의 시, 널 값을 허용하지 않으려면 NOT NULL 키워드를 포함해야 한다. 기본키를 구성하는 모든 속성은 널값을 가질 수 없으므로, 기본키에 포함되는 속성에는 모두 NOT NULL 키워드를 표기한다.

 

▶ DEFAULT 키워드

속성에 값을 입력하지 않았을 때 기본 값 지정

 

▶ PRIMARY KEY 키워드

기본키를 지정하는 키워드

 

▶ UNIQUE 키워드

대체키를 지정하는 키워드

 

▶ FOREIGN KEY 키워드, REFERENCES 키워드

외래키를 지정하는 키워드. 어떤 테이블의 무슨 속성을 참조하는지 REFERENCES 키워드를 사용하여 명확히 제시해야 한다.

•  ON DELETE 옵션
  - ON DELETE NO ACTION : 튜플을 삭제하지 못하게 한다.
  - ON DELETE CASCADE : 관련 튜플을 함께 삭제한다.
  - ON DELETE SET NULL : 관련 튜플의 외래키 값을 NULL로 변경한다.
  - ON DELETE SET DEFAULT : 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경한다.
• ON UPDATE 옵션
  - ON UPDATE NO ACTION : 튜플을 변경하지 못하게 한다.
  - ON UPDATE CASCADE : 관련 튜플에서 외래키 값을 함께 변경한다.
  - ON UPDATE SET NULL : 관련 튜플의 외래키 값을 NULL로 변경한다.
  - ON UPDATE SET DEFAULT : 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경한다.

 

▶ CONSTRAINT 키워드, CHECK 키워드

제약조건 이름 부여, 제약조건 정의를 위한 키워드. CONSTRAINT로 제약조건에 고유의 이름을 부여하면, 이후 삭제나 수정 시 식별이 쉽다.

 

 

ALTER, 변경

 

테이블 변경 명령어 : ALTER TABLE

새로운 속성 추가, 기존 속성 삭제, 새로운 제약조건 추가, 기존 제약조건 삭제 등

// 속성 추가
ALTER TABLE 테이블이름
        ADD 속성이름 데이터타입 [NOT NULL] [DEFAULT 기본값];
        
       
// 속성 삭제
ALTER TABLE 테이블이름
       DROP COLUMN 속성이름;
       
       

// 제약 조건 추가
ALTER TABLE 테이블이름
        ADD CONSTRAINT 제약조건이름 제약조건내용;


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

속성 삭제 시 이 속성을 참조하는 다른 속성이 존재하는 경우, 삭제할 수 없다. 관련 제약조건이나 참조하는 다른 속성을 먼저 삭제해야 한다.

 

 

DROP, 삭제

 

테이블 삭제 명령어 : DROP TABLE

테이블 삭제 

DROP TABLE 테이블이름;

삭제할 테이블을 참조하는 테이블이 있으면 삭제할 수 없다. 외래키 제약조건을 먼저 삭제해야 한다.

 

 

 

데이터 조작어(DML)

 

SELECT, 검색

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

▶ DISTINCT 키워드

중복 제거

 

▶ AS 키워드
속성의 이름을 변경하여 출력하고 싶을 때 사용(출력 결과에서만 변경됨)

 

▶ 산술식

+, -, *, / 등의 산술 연산자, 상수로 산술식을 사용하여 출력할 수 있다.

▶ WHERE 키워드

데이터 검색 시 조건을 설정할 수 있다

SELECT 속성리스트
  FROM 테이블리스트
 WHERE 조건

날짜나 문자도 비교가 가능하다.

 

▶ LIKE 키워드

검색조건을 부분적으로 알고 있을 때 사용

예시) 학생 테이블에서 이름이 홍으로 시작하는 3글자 이름의 학생
SELECT *
  FROM 학생
 WHERE 이름 LIKE '홍__'
 
 
예시) 학생 테이블에서 이름에 홍이 들어가는 학생
SELECT *
  FROM 학생
 WHERE 이름 LIKE '%홍%'

 

▶ IS NULL, IS NOT NULL 키워드

NULL인지 비교하기 위해 사용. NULL 값은 다른 값과 비교할 경우 모두 거짓(false)이 된다. 비교 연산자를 사용할 수 없고, 반드시 IS NULL이나 IS NOT NULL키워드를 사용해야 한다.

 

▶ ORDER BY 키워드
정렬 기준이 될 속성을 지정하고 ASC, DESC를 사용하여 오름차순, 내림차순을 지정하여 출력할 수 있다.

 

▶ COUNT, MAX, MIN, SUM, AVG 등의 집계 함수 

집계 함수는 NULL 값은 제외하고 계산한다. WHERE 절에서는 사용할 수 없다.

 

▶ GROUP BY, HAVING 키워드
GROUP BY로 그룹을 나누는 기준이 되는 속성을 지정하고, 그룹에 대한 조건은 HAVING으로 지정한다.

HAVING 절에는 집계 함수를 사용할 수 있다.

예시) 합격자 테이블에서 합격한 학생이 10명 이상인 학과의 학생 수를 검색한다.

SELECT 학과, COUNT(DISTINCT 학번) AS 학생수
  FROM 합격자
 GROUP BY 학과 HAVING COUNT(*) >= 10;

 

▶ 조인 검색

여러 개의 테이블을 연결하여 데이터를 검색하는 것. 테이블을 연결해주는 조인 속성이 필요하다. 테이블의 연결을 위해 조인 속성의 이름은 달라도 되지만, 속성 도메인은 반드시 같아야 한다. 

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

테이블이름.속성이름


예시) 학생 테이블의 이름, 시험결과 테이블의 시험 결과

SELECT 학생.이름, 시험결과.결과
  FROM 학생, 시험결과
 WHERE 학생.학번 = 시험결과.학번;

 

▶ 부속 질의문 - 서브 쿼리(sub query)

서브 쿼리는 괄호로 묶어 작성하고, 상위 질의문보다 먼저 수행된다. 

- 단일 행 부속 질의문 : 하나의 행을 결과로 반환하는 서브 쿼리

- 다중 행 부속 질의문 : 하나 이상의 행을 결과로 반환하는 서브 쿼리

 

 

INSERT, 삽입

 

2가지 삽입 방법

- 데이터를 튜플에 직접 삽입하는 방법

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

- 부속 질의문을 이용해 튜플을 삽입하는 방법

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

 

 

UPDATE, 수정

테이블에 저장된 튜플에서 특정 속성의 값을 수정한다.

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

WHERE 조건 문이 없으면 테이블 내 모든 튜플을 대상으로 속성 값을 수정한다.

 

 

DELETE, 삭제

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

WHERE 조건 문이 없으면 테이블 내 모든 튜플을 삭제하여 빈 테이블이 된다.

 

 

뷰(VIEW)

다른 테이블을 기반으로 만들어진 가상 테이블(virtual table). 뷰는 논리적으로만 존재하면서도 일반 테이블과 동일한 방법으로 사용할 수 있다.

 

뷰의 생성

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

여기서 SELECT 문에는 ORDER BY를 사용할 수 없다.

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

 

뷰의 활용

뷰는 일반 테이블처럼 원하는 데이터를 검색할 수 있고, INSERT, UPDATE, DELETE도 수행할 수 있다. 하지만 기본키 속성을 포함하지 않는 뷰에 삽입 연산을 수행할 때, 기본키에 NULL값이 들어갈 수 없기 때문에 연산에 실패한다. 이처럼 연산이 허용되지 않는 경우를 살펴보면 아래와 같다.

- 기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 뷰는 변경할 수 없다.

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

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

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

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

 

▶ 뷰의 장점

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

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

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

 

뷰의 삭제

DROP VIEW 뷰이름;

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

삭제할 뷰를 참조하는 제약조건이 존재하면 삭제가 수행되지 않으니, 제약조건을 먼저 삭제해야 한다.

 

 

삽입 SQL(ESQL : Embedded SQL)

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

 

삽입 SQL 문의 특징

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

 

커서(CURSOR)가 필요 없는 삽입 SQL

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

SQL문에서 사용할 변수 선언

EXEC SQL BEGIN DECLARE SECTION;
// 변수 선언
EXEC SQL END DECLARE SECTION;

 

검색 결과를 변수에 넣는 방법

EXEC SQL SELECT 속성리스트 INTO 변수리스트
           FROM 테이블이름
          [WHERE 조건];

 

 

커서(CURSOR)가 필요한 삽입 SQL

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

EXEC SQL DECLARE 커서이름 CURSOR FOR SELECT 문;

커서는 선언 후에, SELECT문을 실행하는 별도의 명령이 필요하다.

//커서 실행
EXEC SQL OPEN 커서이름;

//커서 차례로 처리
EXEC SQL FETCH 커서이름 INTO 변수리스트;

//커서 종료
EXEC SQL CLOSE 커서이름;

 

 

 

---

참고자료

김연희, 『데이터베이스 개론 2판』, 한빛문화원(2019), p207-p296