[데이터베이스] 07. SQL

1. SQL

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

 

1) 데이터 정의어(DDL) : 테이블 생성/변경/제거

2) 데이터 조작어(DML) : 데이터 삽입/수정/삭제/검색

3) 데이터 제어어(DCL) : 데이터 접근 및 사용권한 부여/취소

 

2. 데이터 정의

테이블 생성/변경/제거

 

1) 테이블 생성

CREATE TABLE 테이블이름 (
    /* 속성 정의 부분. 데이터 타입을 정의하고 널값의 허용여부와 default값을 설정 */
    속성이름 데이터타입 [NOT NULL] [DEFAULT 기본값] 
    /* 기본키 */
    PRIMARY KEY (속성리스트) 
    /* 대체키 */
    UNIQUE (속성리스트) 
    /* 외래키. 참조무결성제약조건을 위해 참조하는 테이블 이름, 삭제, 변경 시 제약조건을 명세 */
    FOREIGN KEY (속성리스트) REFERENCES 테이블이름(속성리스트) [ON DELETE 옵션] [ON UPDATE 옵션] 
    /* 데이터 무결성 제약조건 정의 */
    CONSTRAINT 이름 [CHECK(조건)] 
)

 

2) 테이블 변경

/* 새로운 속성 추가 */
ALTER TABLE 테이블이름
    ADD 속성이름 [NOT NULL] [DEFAULT 기본값];

/* 속성 삭제 */
ALTER TABLE 테이블이름
    DROP COLUMN 속성이름;

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

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

 

3) 테이블 삭제

DROP TABLE 테이블이름;

 

3. 데이터 조작

데이터 검색/삽입/수정/삭제

 

1) 데이터 검색

/* 일반 검색. 튜플의 중복을 허용할 경우 ALL, 허용하지 않을 경우 DISTINCT. AS키워드를 사용해 속성을 다른이름으로 나타냄 */
SELECT [ALL | DISTINCT] 속성리스트 AS 별칭
FROM 테이블리스트;

/* 조건 검색. 비교연산자와 논리연산자를 통해 조건 정의 */
SELECT [ALL | DISTINCT] 속성리스트
FROM 테이블리스트
WHERE 조건[ex : 속성 = '속성값' AND 속성 >= 속성값];

/* LIKE를 이용한 검색 */
SELECT 속성리스트
FROM 테이블리스트
WHERE 속성 LIKE ['X___'] ['X%'];

/* NULL을 이용한 검색 */
SELECT 속성리스트
FROM 테이블리스트
WHERE 속성[IS NULL][IS NOT NULL];

/* 정렬 검색. ASC 오름차순. DESC 내림차순 */
SELECT [ALL | DISTINCT] 속성리스트
FROM 테이블리스트
WHERE 조건
ORDER BY 속성 ASC, 속성 DESC;

/* 집계함수를 사용한 검색. 집계함수 : SUM, COUNT, MAX, MIN, AVG */
SELECT SUM(속성)
FROM 테이블리스트
WHERE 조건;

/* 그룹별 검색. 그룹에 대한 조건은 HAVING 절 안에 작성하며, 집계함수를 사용해도 무방 */
SELECT [ALL | DISTINCT] 속성리스트
FROM 테이블리스트
[WHERE 조건]
GROUP BY 속성리스트[HAVING 조건];

/* 조인 검색. 조건절에 조인 속성을 제시 */ 
SELECT 속성리스트
FROM 테이블1, 테이블2
WHERE 테이블1.속성 == 테이블2.속성;

/* 부속질의문을 이용한 검색. A를 상위질의문 또는 주 질의문. B를 하위질의문 또는 서브질의문이라 하며 하위질의문이 먼저 수행 */
/* 부속질의문의 결과가 하나의 행이면 단일 행 부속질의문이라하며 일반 비교연산자를 사용가능 */
/* 부속질의문의 결과가 여러 행이면 다중 행 부속질의문이라하며 일반 비교연산자를 사용할 수 없고, IN/NOT IN과 같은 다른 연산자를 사용해야한다 */
SELECT 속성리스트(A)
FROM 테이블이름
WHERE 속성1 = (SELECT 속성1(B) 
                FROM 테이블이름
                WHERE 조건);

 

2) 데이터 삽입

/* 직접 삽입 */
INSERT
INTO 테이블이름[속성리스트]
VALUES 속성값리스트;

/* 부속질의문을 이용한 삽입 */
INSERT
INTO 테이블이름[속성리스트]
SELECT 속성리스트
FROM 테이블이름
WHERE 조건;

 

3) 데이터 수정

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

 

4) 데이터 삭제

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

 

4. 뷰(VIEW)

뷰는 사용자에게 접근이 허용된 자료만 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된 가상테이블.

 

1) 뷰의 특징

a. 기본 테이블로부터 유도된 테이블이기 때문에 기본테이블과 같은 구조를 가지며 조작 또한 기본테이블과 거의 같다.

b. 물리적으로 존재하지않고 논리적으로만 존재한다.

c. 데이터의 논리적 독립성을 제공한다.

d. 관리가 용이하다.

e. 뷰에 나타나지 않는 데이터를 보호할 수 있다.

f. 기본키를 포함한 속성으로 뷰를 구성해야 삽입, 삭제, 갱신 연산이 가능하다.

g. 일단 정의된 뷰는 다른 뷰의 기초가 될 수 있다.

h. 다른 뷰의 기초가 되는 뷰를 삭제 시 연쇄 삭제가 일어난다.

 

2) 뷰의 장점

a. 논리적 데이터 독립성

b. 여러 사용자의 상이한 요구 지원

c. 간단한 데이터 관리

d. 접근 제어를 통한 보안

 

3) 뷰의 생성과 삭제

/* 뷰 생성 */
CREATE VIEW 뷰이름[속성리스트]
/* 기본 테이블에 대한 SELECT문 제시. ORDER BY를 사용할 수 없음. */
AS SELECT 문
/* 뷰에 삽입/수정 등의 연산을 수행할 때 제약조건 검사 */
[WITH CHECK OPTION]

/* 뷰 삭제 */
DROP VIEW 뷰이름

 

5. 삽입 SQL(ESQL, Embedded SQL)

응용프로그램 안에 삽입하여 사용하는 SQL.

 

1) 삽입 SQL의 특징

a. 일반 명령문이 위치할 수 있는 곳이면 어디든 삽입 가능

b. 삽입 SQL문 앞에는 EXEC SQL을 붙임

c. 일반 변수를 삽입 SQL문에 사용 가능

 

2) 커서가 필요 없는 삽입 SQL

결과 테이블을 반환하지 않거나(CREATE TABLE, INSERT, DELETE, UPDATE), 결과로 하나의 행을 반환하는 경우(SELECT)

 

3) 커서가 필요한 삽입 SQL

결과로 여러개의 행을 반환하는 경우