[데이터베이스 개론] 07장. 데이터베이스 언어 SQL

SQL의 소개

SQL(Structured Query Language)은 관계 데이터베이스를 위한 표준 질의어로 사용되는 언어로써 사용자가 처리하길 원하는 데이터가 무엇인지만 제시하고 데이터를 어떻게 처리해야하지를 언급할 필요가 없는 비절차적 데이터 언어의 특징을 가진다. 즉, SQL은 데이터베이스 관리 시스템에 직접 접근하여 대화식으로 질의를 작성하거나, Java 같은 어로 작성된 응용 프로그램에도 삽입해서 사용할 수 있다. SQL은 기능에 따라 데이터 정의어(DDL), 데이터 조작어(DML), 데이터 제어어(DCL)로 나눌수 있다.

SQL의 분류

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

 

SQL을 이용한 데이터 정의

SQL의 데이터 정의 기능

SQL의 데이터 정의 기능은 테이블 생성, 생성된 테이블 구조의 변경, 테이블 삭제로 분류할 수 있다.

 

테이블의 생성

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

테이블을 생성하는 SQL 명령어는 CREATE TABLE이다. CREATE TABLE문은 생성할 테이블을 구성하는 속성들의 이름과 데이터 타입 및 제약 사항에 대한 정의, 기본키, 대체키, 외래키의 정의, 데이터 무결성을 위한 제약 조건의 정의 등을 포함한다. 기본 형식에서는 테이블을 구성하는 속성의 이름과 데이터 타입, 제약사항을 정의한다.

속성의 정의

속성의 데이터 타입

CREATE TABLE 문에서는 각 속성의 특성을 고려하여 적절한 데이터 타입을 정의해야 한다. 속성의 타입에는 위의 표와 같은 것들이 존재하고 추가적으로 CREATE TABLE 문에서는 기본적으로 NULL을 허용하기 때문에 NULL을 허용하지 않기위해 NOT NULL을 선언해줘야 한다. 

키의 정의

CREATE TABLE 문에서는 테이블을 정의할 때 기본키, 대체키, 외래키를 지정할 수 있다. 기본키 같은 경우 PRIMARY KEY 키워드를 사용하여 지정한다. 기본키는 각 투플을 유일하게 식별하는 특성을 지니며 모든 테이블에서 기본키는 반드시 하나지정해야 하고, 하나또는 여러개의 속성으로 구성할 수 있다. 대체키의 경우 UNIQUE 키워드를 사용하여 지정한다. 대체키는 기본키와 같이 투플을 유일하게 식별하는 특성을 가지고 있지만 기본키와 달리 NULL 값을 가질 수 있다. 외래키의 경우 FOREIGN KEY 키워드를 사용하여 지정한다. 외래키를 지정할 때는 어떤 테이블의 무슨 속성을 참조하는지 REFERNCES 키워드를 이용하여 출처를 명확히 밝혀야 한다. 추가적으로 다음과 같은 키워드를 이용하여 참조되는 테이블에서 투플을 삭제하거나 변경할때 처리하는 방법을 선택할 수 있다.

ON UPDATE/DELETE NO ACTION : 투플을 변경,삭제하지 못하게 한다.
ON UPDATE/DELETE CASCADE : 관련 투플을 함께 변경,삭제 한다.
ON UPDATE/DELETE SET NULL : 관련 투플의 외래키 값을 NULL로 변경한다.
ON UPDATE/DELETE SET DEFAULT : 관련 투플의 외래키 값을 미리 지정한 기본값으로 변경한다.

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

CREATE TABLE 문으로 테이블을 정의할ㄷ 때 CHECK 키워드를 사용하여 특정 속성애 데한 제약 조건을 지정할 수 있다. 또한 CHECK 키워드를 사용해 지정한 제약조건에 CONSTRANINT 키워드와 함께 고유의 이름을 부여할 수도 있다. 

 

테이블의 변경

ALTER TABLE 테이블이름 ADD 속성이름 데이터타입 [NOT NULL] [DEFAULT 기본값];
ALTER TABLE 테이블이름 DROP COULMN 속성이름;
ALTER TABLE 테이블이름 ADD CONSTRAINT 제약조건이름 제약조건내용;
ALTER TABLE 테이블이름 DROP CONSTRAINT 제약조건이름;

테이블은 ALTER TABLE 문으로 변경할 수 있다. ALTER TABLE 문을 이용해 새로운 속성 추가, 기존 속성 삭제, 새로운 제약조건 추가, 기존 제약조건 삭제 등이 가능하다.

 

테이블의 삭제

DROP TABLE 테이블이름;

CREATE TABLE 문으로 생성한 테이블은 DROP TABLE 명령어로 삭제할 수 있다.

 

SQL을 이용한 데이터 조작

SQL의 데이터 조작 기능

SQL의 조작 기능은 SELECT를 이용한 데이터 검색, INSERT를 이용한 데이터 삽입, UPDATE를 이용한 데이터 수정, DELETE를 이용한 데이터 삭제로 분류할 수 있다.

 

데이터의 검색

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

테이블에서 원하는 데이터를 검색하기 위해 필요한 SQL문은 SELECT이다. 가장 기본적으로는 SELECT, FROM 두개의 키워드를 사용하여 데이터를 검색할 수 있고, 추가적으로 WHERE 키워드와 조건을 이용할 수 있다. 위와 같은 형태에서 SQL 실행 순서는 FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY의 순서를 가진다.

조건 검색

대표적인 조건 연산자에는 다음과 같은 것이 있다.

비교 연산자와 논리 연산자

위와 같은 비교 연산자와 논리 연산자를 활용하여 WHERE 키워드에 적절한 속성 조건을 추가할 수 있다. 또한 추가적으로 문자열 검색 조건에 있어서 LIKE 키워드를 할용할 수 도 있다. 

LIKE 키워드의 사용 예

그리고 NULL 값에 대한 조건에 있어서 IS NULL 또는 IS NOT NULL 등을 활용할 수 있다.

집계 함수를 이용한 검색

특정 속성 값을 통계적으로 계산한 결과를 검색하기 위해 집계함수를 이용할 수 있다. 집계함수는 개수, 합계, 평균, 최댓값, 최솟값의 계산 기능을 제공한다. 다음과 같은 키워드들이 있다.

집계 함수

집계 함수를 사용할땐 다음 두가지 사항에 주의해야한다. 첫째로 집계함수는 널인 속성 값은 제외하고 계산을 한다. 둘째로 집계 함수는 WHERE 절에서는 사용할 수 없고 SELECT 절이나 HAVING 절에서만 사용할 수 있다.

그룹별 검색

테이블에서 특정 속성의 값이 같은 투플을 모아 그룹을 만들고, 그룹별로 검색을 하기 위해 GROUP BY 키워드를 사용한다. 그룹에 대한 조건을 추가하려면 HAVING 키워드를 추가적으로 사용할 수 있다. 

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

여러 개의 테이블을 연결하여 데이터를 검색하는 것을 조인 검색이라고 하며 조인 검색을 하기 위해서는 테이블을 연결해주는 속성이 필요하고 이 속성을 조인 속성이라고 한다. 조인속성의 이름은 달라도 되지만 도메인은 같아야 한다. 일반적으로는 테이블의 관계를 나태나는 외래키를 조인 속성으로 이용한다.

부속 질의문을 이용한 검색

SELECT 문 안에 다른 SELECT 문을 부속 질의문 또는 서브 질의문이라고 한다. 부속 질의문은 괄호로 묶어 작성하고 ORDER BY 키워드를 사용할 수 없고, 상의 질의문보다 먼저 수행된다. 부속 질의문을 이용한 검색은 이어 달리기처럼 부속 질의문을 먼저 수행하고, 그 결과를 이용해 상위 질의문을 수행하여 최종 결과 테이블을 반환한다. 부속 질의문은 하나의 행을 결과로 반환하는 단일 행 부속 질의문과 하나 이상의 행을 결과로 반환하는 다중 행 부속 질의문으로 분류한다. 다만 이때, 단일 행 부속 질의문은 일반 비교 연산자를 사용할 수 있지만, 다중 행 부속 질의문은 일반 비교 연산자를 사용할 수 없다. 

다중 행 부속 질의문에는 다음과 같은 연산자들을 활용할 수 있다.

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

 

데이터의 삽입

테이블에 새로운 투플을 삽입하기 위해 필요한 SQL문은 INSERT이다. INSERT 문을 이용하여 테이플에 투플을 직접 삽입할 수도 있고, 부속 질의문을 이용해 투플을 삽입할 수 도 있다.

데이터 직접 삽입

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

테이블에 투플을 직접 삽입하는 INSERT 문의 기본 형식은 위와 같다. INTO 키워드와 함께 투플을 삽입할 테이블 이름을 제시 후 속성의 이름을 나열하여 VALUES 키워드와 순서대로 대응대도록 입력한다. 이때 INTO 절의 속성리스트와 VALUES 절의 속성값리스트는 일대일로 대응이 되어야한다. 다만 INTO 절에서 속성리스트는 생략할 수 있는데 생략하면 테이블을 정의할 때 지정한 속성의 순서대로 VALUES 절의 속성 값이 삽입된다.

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

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

부속 질의문인 SELECT 문을 이용하여 다른 테이블에서 검색한 데이터를 투플로 삽입하는 INSERT문의 기본형식의 위와 같다.

 

데이터의 수정

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

테이블에 저장된 데이터를 수정하기 위해 필요한 SQL문은 UPDATE 문으로써 기본 형식은 위와 같다. UPDATE 문은 테이블에 저장된 투플에서 특정 속성의 값을 수정할 때 사용되면 WHERE 절을 이용하여 조건을 제시할 수 있다. 다만 WHERE 절이 없다면 모든 투플이 대상이 된다.

 

데이터의 삭제

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

테이블에 저장된 데이터를 삭제하기 위해 필요한 SQL문은 DELETE 문으로써 기본 형식은 위와 같다. DELETE 문은 WHERE 절에 제시한 조건을 만족하는 투플을 삭제하는데, WHERE 절을 생략하면 모든 투플을 삭제하여 빈테이블이 된다.

 

뷰

뷰의 개념

뷰는 다른 테이블을 기반으로 만들어진 가상 테이블이다. 뷰가 가상 테이블인 이유는 일반 테이블과 달리 실제로 데이터를 저장하고 있지 않기 때문이다. 뷰는 CREATE TABLE 문으로 생성한 테이블 또는 다른 뷰를 기반으로 생성할 수 있다. 뷰를 통해서 기본 테이블의 내용을 쉽게 검색할 수 있지만 기본 테이블의 내용을 바꾸는 제한은 제한적으로 이루어진다. 

 

뷰의 생성

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

뷰를 생성하기 위해 필요한 SQL 명령어는 CREATE VIEW이고 기본 형식은 위와 같다. CREATE VIEW 명령어와 함께 새로 생성할 뷰의 이름을 제시한 후 뷰를 구성하는 속성의 이름을 괄호 안에 나열한다. 그 후 AS 키워드와 함께 기본 테이블에 대한 SELECT 문을 제시한다. WITH CHECK OPTION은 생성한 뷰에 삽입이나 수정 연산을 할 때 SELECT 문에서 제시한 뷰의 정의 조건을 위반하면 수행되지 않도록 하는 제약조건이다.

 

뷰의 활용

CREATE VIEW 문으로 생성된 뷰에서도 일반 테이블처럼 원하는 데이터를 검색할 수 있다. 뷰에 대한 SELECT 문이 내부적으로는 기본 테이블에 대한 SELECT 문으로 변환되어 수행이 된다. 따라서 INSERT, UPDATE, DELETE 연산또한 뷰를 대상으로 수행할 수 있고, 이 연산은 기본 테이블에도 반영이 된다. 따라서 뷰에 대한 삽십, 삭제, 수정 연산은 주의해야한다.

다만 삽입, 삭제, 수정 여산이 모든 뷰에 허용되는 것이 아니라 제한적이다. 기본 테이블에서 어떤 투플을 어떻게 변경해야 할지 명확히 제시하지 못하는 뷰는 변경이 허용되지 않는다. 즉 다음과 같은 것들이 있다.

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

다만 이러한 제약조건에도 뷰는 다음과 같은 장점이 있다.

• 질의문을 좀 더 쉽게 작성할 수 있다.
• 데이터의 보안 유지에 도움이 된다.
• 데이터를 좀 더 편리하게 관리할 수 있다.

 

뷰의 삭제

DROP VIEW 뷰이름;

뷰를 삭제하기 위한 SQL 명령어는 DROP VIEW이고, 기본 형식은 위와 같다. 뷰를 삭제하더라도 기본 테이블은 영향을 받지 않는다. 그리고 삭제할 뷰를 참조하는 제약조건이 존재하면 삭제가 수행되지 않기에 제약조건을 우선 제거해야한다.

 

삽입 SQL

삽입 SQL의 개념과 특징

C, JAVA 등과 같은 프로그래밍 언어로 작성된 응용 프로그램 안에 삽입하여 사용하는 SQL 문을 삽입 SQL(Embedded SQL, ESQL)이라고 한다. 다음과 같은 특징이 있다.

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

수행 결과로 여러개의 행을 반환하는 SELECT 문을 삽입 SQL 문으로 사용하는 경우에서는 커서(Cursor)라는 도구가 필요하다. 커서는 수행 결과로 반환된 여러 행을 한번에 하나씩 가리키는 포인터 역할을 한다.

 

커서가 필요 없는 삽입 SQL

SQL 문을 실행했을 때 특별히 결과 테이블을 반환하지 않는 CREATE TABLE, INSERT, DELETE, UPDATE, 결과로 행 하나만 반환하는 SELECT 문은 커서가 필요없다. 삽입 SQL 문에서 사용할 변수는 미리 선언해야 하는데, 이는 BEGIN DECLARE SECTION 문장과 END DECLARE SECTION 문장사이에 선언하면 된다.

 

커서가 필요한 삽입 SQL

SELECT 문의 실행 결과로 여러 행이 검색되는 경우에는 한 번에 한행씩 차례로 접근할 수 있게 해주는 커서가 필요하다. 커서를 사용하기 전에 커서의 이름과 커서가 필요한 SELECT 문을 선언해야한다.

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

커서를 선언하는 삽입 SQL 문의 기본 형식은 위와 같다. 커서를 선언한 후에는 SELECT 문을 실행하는 명령이 필요하다.

EXEC SQL OPEN 커서이름;

커서에 연결된 SELECT 문을 실행하는 삽입 SQL의 기본 형식은 위와 같다. 그 후 검색된 행들을 차례로 처리하기 위해 커서를 이동시키는 명령어는 FETCH이다.

EXEC SQL FETCH 커서이름 INTO 변수리스트;

커서리를 이동해 처리할 다음 행을 가리키도록 하고, 커서가 가르키는 행으로부터 속성 값들을 가져와 변수에 저장하는 FETCH 문의 기본 형식은 위와 같다. 

EXEC SQL CLOSE 커서이름;

커서를 종료하는 CLOSE 문의 기본 형식은 위와 같다.

 

 

출처
데이터베이스 개론 2판 - 한빛아카데미 출판, 김연희 저

데이터베이스 개론 - 교보문고