SQL 개요
- SQL은 현재 DBMS 시장에서 관계 DBMS가 압도적인 우위를 차지하는데 중요한 요인 중 하나이다.
- SQL은 IBM 연구소에서 1974년에 System R이라는 관계 DBMS 시제품을 연구할 때 관계 대수와 관계 해석을 기반으로, 집단 함수, 그룹화, 갱신 연산 등을 추가하여 개발된 언어이다.
- 1986년에 ANSI(미국 표준 기구)에서 SQL 표준을 채택함으로써 SQL이 널리 사용되는데 기여
- 다양한 상용 관계 DBMS마다 지원하는 SQL 기능에 다소 차이가 있다.
- MSSQL에서는 추가기능이 존재한다.
- SQL은 비절차적 언어(선언적 언어)이므로 사용자는 자신이 원하는 바(what)만 명시하며, 원하는 것을 처리하는 방법(how)은 명시할 수 없다.
- 관계 DBMS는 사용자가 입력한 SQL문을 번역하여 사용자가 요구한 데이터를 찾는데 필요한 모든 과정(질의 처리 과정)을 담당한다.
- 자연어에 가까운 구문을 사용하여 질의를 표현할 수 있다.
- 대화식 SQL(interactive SQL)과 내포된 SQL(embedded SQL) 두 가지 인터페이스를 지원한다. 내포된 SQL이란 고급 프로그래밍 언어(C, C++, Java 등)에 내포되어 사용되는 SQL이다.
SQL의 구성요소
- 데이터 정의어 (Data Definition Language, DDL)
CREATE | DOMAIN | 도메인을 생성 |
TABLE | 테이블을 생성 | |
VIEW | 뷰를 생성 | |
INDEX | 인덱스를 생성 (SQL2 표준 X) | |
ALTER | TABLE | 테이블의 구조(스키마)를 변경 |
DROP | DOMAIN | 도메인을 제거 |
TABLE | 테이블을 제거 | |
VIEW | 뷰를 제거 | |
INDEX | 인덱스를 제거 (SQL2 표준 X) |
- 데이터 조작어 (Data Manipulation Language, DML)
- 데이터 제어어 (Data Control Language, DCL)
데이터 정의어와 무결성 제약조건
- 스키마의 생성과 제거 : SQL2에서는 동일한 데이터베이스 응용에 속하는 릴레이션, 도메인, 제약 조건, 뷰, 권한 등을 그룹화하기 위해서 스키마 개념을 지원한다.
CREATE SCHEMA DBNAME AUTHORIZATION KIM;
DROP SCHEMA DBNAME RESTRICT; // 하나 이상의 릴레이션이 존재하면 못지운다.
DROP SCHEMA DBNAME CASCADE;
- 데이터 타입
데이터 타입 | 의미 |
INTEGER 또는 INT | 정수형 |
SMALLINT | 작은 정수형 |
NUMBER(n, s) 또는 DECIMAL(n, s) | n개의 숫자에서 소수 아래 숫자 s개인 십진수 |
REAL | 실수형 |
FLOAT(n) | 적어도 n개의 숫자가 표현되는 실수형 |
CHAR(n) 또는 CHARACTER(n) | n바이트 문자열, 디폴트는 1바이트 |
VARCHAR(n) 또는 CHARACTER VARYING(n) | 최대 n바이트까지의 가변 길이 문자열 |
BIT(n) 또는 BIT VARYING(n) | n개의 비트열 또는 최대 n개까지의 가변 비트열 |
DATE | 날짜형 |
BLOB | Binary Large Object, 멀티미디어 데이터 등을 저장 |
- 릴레이션 정의
CREATE TABLE DEPARTMENT (
DEPTNO INTEGER NOT NULL,
DEPTNAME CHAR(10),
FLOOR INTEGER,
PRIMARY KEY(DEPTNO)
);
CREATE TABLE EMPLOYEE (
EMPNO INTEGER NOT NULL,
EMPNAME CHAR(10),
TITLE CHAR(10),
MANAGER INTEGER,
SALARY INTEGER,
DNO INTEGER,
PRIMARY KEY(EMPNO),
FOREIGN KEY(MANAGER) REFERENCES EMPLOYEE(EMPNO),
FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DEPTNO)
);
- 릴레이션 제거 : DROP TABLE TABLENAME;
ex) DROP TABLE DEPARTMENT;
- 릴레이션 스키마 변경 : ALTER TABLE TABLENAME [ADD] COLNAME NEW_DATATYPE;
ex) ALTER TABLE EMPLOYEE ADD PHONE CHAR(13);
- 인덱스 생성 : CREATE INDEX INDEXNAME ON TABLENAME(COLNAME);
ex) CREATE INDEX EMPDNO_IDX ON EMPLOYEE(DNO);
* 외래 키의 경우 조인 참여가 잦기 때문에 인덱스 사용률이 높은 편이다.
- 도메인 생성 : CREATE DOMAIN COLNAME DATATYPE DEFAULT ‘value’;
ex) CREATE DOMAIN DEPTNAME CHAR(10) DEFAULT ‘개발’;
- 제약 조건 예시
CREATE TABLE EMPLOYEE (
EMPNO INTEGER NOT NULL,
EMPNAME CHAR(10) UNIQUE, // 중복된 데이터를 거절(대체 키임을 의미)
TITLE CHAR(10) DEFAULT ‘사원’,
MANAGER INTEGER,
SALARY INTEGER CHECK(SALARY < 6000000), // 조건을 만족하는 데이터만 허용
DNO INTEGER CHECK(DNO IN (1, 2, 3, 4)) DEFAULT 1, // 허용 가능 데이터 표시
PRIMARY KEY(EMPNO),
FOREIGN KEY(MANAGER) REFERENCES EMPLOYEE(EMPNO),
FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DEPTNO)
ON DELETE SET DEFAULT ON UPDATE CASCADE
);
- 자기 자신을 참조하는 경우
FOREIGN KEY(MANAGER) REFERENCES EMPLOYEE(EMPNO),
-> MANAGER(외래 키) 컬럼이 EMPNO(기본 키) 컬럼을 참조한다.
- 다른 릴레이션을 참조하는 경우
FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DEPTNO)
ON DELETE SET DEFAULT ON UPDATE CASCADE
-> EMPLOYEE의 DNO(외래 키) 컬럼이 DEPARTMENT의 DEPTNO(기본 키) 컬럼을 참조한다.
=> 참조되는 릴레이션(DEPARTMENT)에서 DELETE가 발생하면 삭제된 기본 키를 참조하던 외래 키(DNO)는 디폴트값(1)을 가진다.
=> 참조되는 릴레이션(DEPARTMENT)에서 UPDATE가 발생하면 갱신된 기본 키를 참조하던 외래 키(DNO)는 갱신된 값을 상속 받는다.
- 또 다른 예시 : 조건 걸기
CREATE TABLE EMPLOYEE (
ID INTEGER,
NAME CHAR(10),
SALARY INTEGER,
MANAGER_SALARY INTEGER,
CHECK(MANAGER_SALARY > SALARY)
);
-> 매니저의 월급이 기본 사원의 월급보다 높은 투플(레코드)만 삽입을 허용함.
- 참조 무결성 제약 조건 유지하기 위한 SQL
... ON DELETE NO ACTION
... ON DELETE CASCADE : 기본 키 삭제 시 연쇄적으로 외래 키도 삭제된다.
... ON DELETE SET NULL
... ON DELETE SET DEFAULT
... ON UPDATE NO ACTION
... ON UPDATE CASCADE : 기본키 수정 시 연쇄적으로 외래키도 수정된다.
... ON UPDATE SET NULL
... ON UPDATE SET DEFAULT
- ON INSERT 가 없는 이유 : 삽입의 경우 참조 무결성 제약조건을 위배하지 않으므로 반복적으로 확인하는 것은 오버헤드를 키우기 때문이다. (성능 저하)
- CASCADE 할 때, 외래키에는 반드시 인덱스가 있어야 한다.
- 무결성 제약조건의 추가 및 삭제
ALTER TABLE STUDENT ADD CONSTRAINT STUDENT_PK
PRIMARY KEY(STNO);
ALTER TABLE STUDENT DROP CONSTRAINT STUDENT_PK;
'Database > Theory' 카테고리의 다른 글
| 6. Embeded SQL - 내포된 SQL문 (0) | 2017.12.30 |
|---|---|
| 5. SQL(Structured Query Language) - 데이터 조작어(DML) (1) | 2017.12.30 |
| 3. 관계 대수 (0) | 2017.12.25 |
| 2. 관계 데이터 모델과 제약조건 (0) | 2017.10.13 |
| 1. 데이터베이스 시스템 (0) | 2017.10.13 |