[DB - 데이터베이스 개론] Chapter 07. 데이터베이스 언어 SQL

목차

 

01. SQL의 소개

• SQL(Structured Query Language)은 관계 데이터베이스를 위한 표준 질의어로 많이 사용되는 언어이다.
• 사용자가 처리를 원하는 데이터가 무엇인지만 제시하고 데이터를 어떻게 처리해야 하는지를 언급할 필요가 없어 비절차적 데이터 언어의 특징을 띤다고 할 수 있다. 
• SQL은 기능에 따라 데이터 정의어(DDL), 데이터 조작어(DML), 데이터 제어어(DCL)로 나눈다.
  • 데이터 정의어 : 테이블을 생성하고 변경&제거하는 기능을 제공한다.
  • 데이터 조작어 : 테이블에 새 데이터를 삽입하거나, 테이블에 저장된 데이터를 수정,삭제,검색하는 기능을 제공한다. 
  • 데이터 제어어 : 보안을 위해 데이터에 대한 접근 및 사용 권한을 사용자별로 부여하거나 취소하는 기능을 제공하는 언어다. 데이터베이스 관리자가 주로 사용한다.

 

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

1. SQL의 데이터 정의 기능 

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

SQL의 데이터 정의 기능

 

2. 테이블의 생성

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

CREATE TABLE문의 기본 형식

1. 테이블을 구성하는 각 속성의 이름과 데이터 타입, 기본적인 제약 사항을 정의한다.
2. 기본키로 테이블에 하나만 존재할 수 있다.
3. 대체키로 테이블에 여러 개 존재할 수 있다.
4. 외래키로 테이블에 여러 개 존재할 수 있다.
5. 데이터 무결성을 위한 제약조건으로 테이블에 여러 개 존재할 수 있다. 

 [ ]로 표시한 항목은 생략이 가능하며 모든 SQL 문은 세미콜론(;)으로 문장 끝을 표시한다. 

 

2.1 속성의 정의

• 표준 SQL에서 지원하는 대표적인 데이터 타입은 아래의 표와 같다.

데이터 타입	의미
INT 또는 INTEGER	정수
SMALLINT	INT보다 작은 정수
CHAR(n) 또는 CHARACTER(n)	길이가 n인 고정 길이의 문자열
VARCHAR(n) 또는 CHARACTER VARYING(n)	최대 길이가 n인 가변 길이의 문자열
NUMERIC(p, s) 또는 DECIMAL(p, s)	고정 소수점 실수  p는 소수점을 제외한 전체 숫자의 길이고, s는 소수점 이하 숫자의 길이
FLOAT(n)	길이가 n인 부동 소수점 실수
REAL	부동 소수점 실수
DATE	연, 월, 일로 표현되는 날짜
TIME	시, 분, 초로 표현되는 시간
DATETIME	날짜와 시간

 

• CREATE TALBE 문은 기본적으로 속성에 널 값이 허용된다. 널 값을 허용하지 않으려면 속성을 정의할 때 NOT NULL 키워드를 포함해야 한다. 
• 기본키의 경우 자동으로 NOT NULL 특성을 갖지만 명시적으로 표현해줘도 된다. 
• 속성에 기본 값을 지정해두지 않으면 사용자가 속성에 값을 입력하지 않았을 때 해당 속성에 널 값이 기본값으로 저장된다. DEFAULT 키워드를 사용해 기본 값을 지정하면 기본 값이 저장된다.

 

2.2 키의 정의

• 기본키는 PRIMARY KEY 키워드로 사용해 지정한다. 
• 기본키가 없어도 테이블을 정의할 수 있지만 가능한 한 선택하는 것이 좋다. 모든 테이블에서 기본키는 반드시 하나만 지정할 수 있고, 여러 개의 속성으로 구성할 수도 있다.
• 대체키는 UNIQUE 키워드를 사용해 지정한다. 
• 대체키로 지정된 속성의 값은 테이블에서 중복되면 안 되고 유일성을 가져야 한다. 하지만 기본키와 달리 널 값을 가질 수 있다. 한 테이블에 여러 개 지정할 수 있다. 
• 외래키는 FOREIGN KEY 키워드를 사용해 지정한다. 
• 외래키는 REFERENCES 키워드를 사용하여 어떤 테이블의 무슨 속성을 참조하는지를 명확히 제시해야 한다. 
• 참조되는 테이블의 튜플을 삭제할 때 다음 네 가지 중 한 가지 방법으로 처리하도록 선택할 수 있다.
  • ON DELETE NO ACTION : 튜플을 삭제하지 못하게 한다 (DEFAULT).
  • ON DELETE CASCADE : 관련 튜플을 함께 삭제한다. 
  • ON DELETE SET NULL : 관련 튜플의 외래키 값을 NULL로 변경한다.
  • ON DELETE SET DEFAULT : 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경한다.
• 참조되는 테이블의 튜플이 변경될 때도 다음 네 가지 중 한 가지 방법으로 처리하도록 선택할 수 있다.
  • ON UPDATE NO ACTION : 튜플을 변경하지 못하도록 한다 (DEFAULT).
  • ON UPDATE CASCADE : 관련 튜플에서 외래키 값을 함께 변경한다.
  • ON UPDATE SET NULL : 관련 튜플의 외래키 값을 NULL로 변경한다.
  • ON UPDATE SET DEFAULT : 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경한다.

 

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

• CREATE TABLE 문으로 테이블을 정의할 때 CHECK 키워드를 사용해 특정 속성에 대한 제약조건을 지정할 수 있다.
• 테이블에는 CHECK 키워드로 지정한 제약조건을 만족하는 튜플만 존재하게 된다. 테이블에 새로운 튜플을 삽입하거나 기존 튜플을 수정할 때도 이 제약 조건을 반드시 지켜야 한다. 
• CHECK 키워드를 사용해 지정한 제약조건에 CONSTRAINT 키워드와 함께 고유의 이름을 부여할 수도 있다. 

 

3. 테이블의 변경

• ALTER TABLE 문을 이용해 새로운 속성 추가, 기존 속성 삭제, 새로운 제약조건 추가, 기존 제약조건 삭제 등이 가능하다. 

 

3.1 새로운 속성의 추가

• 새로운 속성을 추가하는 ALTER TABLE 문의 기본 형식은 다음과 같다.

 

3.2 기존 속성의 삭제

• 기존 속성을 삭제하는 ALTER TABLE 문의 기본 형식은 다음과 같다.

• 만약 삭제할 속성과 관련된 제약조건이 존재하거나 이 속성을 참조하는 다른 속성이 존재하는 경우에는 속성을 삭제할 수 없다. 관련된 제약조건이나 참조하는 다른 속성을 먼저 삭제한 후 해당 속성을 삭제해야 한다. 

 

3.3 새로운 제약조건의 추가 

• 새로운 제약조건을 추가하는 기본 형식은 다음과 같다.

  

3.4 기존 제약조건의 삭제 

• 기존 제약조건을 삭제하는 기본 형식은 다음과 같다.

 

4. 테이블의 삭제

• 테이블을 삭제하는 DROP TABLE 문의 기본 형식은 다음과 같다.

• 삭제할 테이블을 참조하는 테이블이 있다면 삭제가 수행되지 않는다. 따라서 삭제하고자 하는 테이블을 참조하는 외래키 제약조건을 먼저 삭제해야 한다.

 

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

1. SQL의 데이터 조작 기능

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

SQL의 데이터 조작 기능

 

2. 데이터의 검색

 SQL은 SELECT 문을 사용하여 다양한 검색 유형을 지원한다. 

 

2.1 기본 검색

 기본 검색을 위한 SELECT 문의 기본 형식은 다음과 같다. 

• SELECT 문은 테이블을 대상으로 하고 수행 결과도 테이블이다. 
• 테이블에 존재하는 모든 속성을 검색하기 위해 *를 사용할 수도 있다. *를 사용하면 결과 테이블의 속성 순서가 원본 테이블이 정의한 속성 순서와 같다. 
• SELECT 문의 수행 결과로 반환되는 결과 테이블에서는 동일한 튜플이 중복될 수 있다. 
• 결과 테이블이 중복을 허용하도록 ALL 키워드를 명시적으로 사용해도 된다. 
• 결과 테이블에서 튜플의 중복을 제거하고 한 번씩만 출력되도록 하려면 DISTINCT 키워드를 사용한다. 
• 결과 테이블에 출력되는 속성의 이름을 다른 이름으로 바꾸어 출력하려면 AS 키워드를 변경할 이름과 함께 지정하면 된다. AS 키워드는 생략할 수도 있다.

 

2.2 산술식을 이용한 검색

• 산술식을 사용할 수도 있다.
• 산술식은 속성의 이름과 +, -, *, / 등의 산술 연산자, 상수로 구성된다. 

 

2.3 조건 검색

 조건을 만족하는 데이터만 검색하는 SELECT 문의 기본 형식은 다음과 같다.

• WHERE 키워드와 함께 아래 표의 비교 연산자와 논리 연산자를 이용한 검색 조건을 제시하면 된다. 

 

2.4 LIKE를 이용한 검색

• 검색 조건을 부분적으로만 알고 있다면 LIKE 키워드를 이용해 검색할 수 있다. 
• 단, LIKE 키워드는 문자열을 이용하는 조건에만 사용할 수 있다. 

 

2.5 NULL을 이용한 검색

• 검색 조건에서 특정 속성의 값이 널 값인지를 비교하려면 IS NULL 키워드를 사용한다. 
• 널 값이 아닌지를 비교하려면 IS NOT NULL 키워드를 사용한다. 
• 검색 조건에서 널 값은 다른 값과 크기를 비교하면 결과가 모두 거짓이 된다.

 

2.6 정렬 검색

• 결과 테이블의 내용을 사용자가 원하는 순서로 출력하려면 ORDER BY 키워드를 사용한다. 
• 정렬 검색의 기본 형식은 다음과 같다.

• 오름차순은 ASC, 내림차순은 DESC이다. 지정하지 않으면 기본 정렬은 오름차순이다. 

 

2.7 집계 함수를 이용한 검색

• 특정 속성 값을 통계적으로 계산한 결과를 검색하기 위해 집계 함수를 이용할 수 있다. 
• 집계 함수는 열 함수라고도 하며 개수, 합계, 평균, 최댓값, 최솟값의 계산 기능을 제공한다. ㅌ

집계 함수

• 집계 함수 사용 시 주의할점은 다음과 같다.
  • 집계 함수는 널인 속성 값은 제외하고 계산한다. 
  • 집계 함수는 WHERE 절에서는 사용할 수 없고 SELECT 절이나 HAVING 절에서만 사용할 수 있다. 

 

2.8 그룹별 검색

• 테이블에서 특정 속성의 값이 같은 튜플을 모아 그룹을 만들고, 그룹별로 검색을 하기 위해 GROUP BY 키워드를 사용한다. 
• 그룹에 대한 조건을 추가하려면 GROUP BY 키워드를 HAVING 키워드와 함께 사용하면 된다. 
• 기본 형식은 다음과 같다.

• 그룹별로 검색할 때는 그룹을 나누는 기준이 되는 속성을 SELECT 절에도 작성하는 것이 좋다.

 

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

• 여러 개의 테이블을 연결하여 데이터를 검색하는 것을 조인 검색이라 한다.
• 조인 검색을 하려면 테이블을 연결해주는 속성이 필요하고 이 속성을 조인 속성이라 한다. 
• 테이블을 연결하려면, 조인 속성의 이름은 달라도 되지만 도메인은 반드시 같아야 한다. 일반적으로 외래키를 조인 속성으로 이용한다.
• 조인 검색을 위한 SQL 문은 FROM 절에 검색에 필요한 모든 테이블을 나열하고, WHERE 절에는 조인 속성의 값이 같아야 함을 의미하는 조인 조건을 제시한다. 

 

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

• SELECT 문 안에 또 다른 SELECT 문을 포함할 수도 있다.
• 다른 SELECT 문 안에 들어 있는 SELECT 문을 부속 질의문 또는 서브 질의문(sub query)이라 한다. 그리고 다른 SELECT 문을 포함하는 SELECT 문을 상위 질의문 또는 주 질의문(main query)이라 한다.
• 부속 질의문은 괄호로 묶어 작성하고 ORDER BY 절을 사용할 수 없으며, 상위 질의문보다 먼저 수행된다. 
• 부속 질의문은 하나의 행을 결과로 반환하는 단일 행 부속 질의문과 하나 이상의 행을 결과로 반환하는 다중 행 부속 질의문으로 분류한다.
• 단일 행 부속 질의문은 일반 비교 연산자를 사용할 수 있지만, 다중 행 부속 질의문은 일반 비교 연산자를 사용할 수 없다. 
• 다중 행 부속 질의문과 함께 사용할 수 있는 연산자의 종류는 다음과 같다. 

 

3. 데이터의 삽입

• 테이블에 새로운 튜플을 삽입하기 위해 필요한 SQL 문은 INSERT다.
• INSERT 문을 이용해 튜플을 삽입하는 방법은 두 가지다. 직접 삽입하는 방법이 있고, 부속 질의문을 이용해 삽입하는 방법이 있다. 

 

3.1 데이터 직접 삽입

 INSERT 문의 기본 형식은 다음과 같다.

 

• INTO 절의 속성 이름과 VALUES 절의 속성 값은 순서대로 일대일 대응이 되어야하고 개수도 같아야 한다. 
• INTO 절에서 속성 이름의 리스트는 생략할 수 있는데, 생략한 경우에는 테이블을 정의할 때 지정한 속성의 순선대로 VALUES 절의 속성 값이 삽입된다. 

 

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

부속 질의문인 SELECT 문을 이용해 다른 테이블에서 검색한 데이터를 튜플로 삽입하는 INSERT 문의 기본 형식은 다음과 같다. 

 

4. 데이터의 수정

 데이터 수정을 위한 SQL 명령어는 UPDATE다. UPDATE 문의 기본 형식은 다음과 같다. 

• WHERE 절을 생략하면 테이블에 존재하는 모든 튜플을 대상으로 하여 SET 절에서 지정한 대로 속성 값을 수정한다. 

 

 

5. 데이터의 삭제 

 데이터를 삭제하기 위한 SQL 명령어는 DELETE다. DELETE 문의 기본 형식은 다음과 같다.

• WHERE 절을 생략하면 테이블에 존재하는 모든 튜플을 삭제하여 빈 테이블이 된다. DROP TABLE 문은 테이블 자체를 제거하지만 DELTE문의 경우는 테이블 자체는 남는다는 점에서 다르다.

 

04. 뷰

1. 뷰의 개념 

• 뷰는 다른 테이블을 기반으로 만들어진 가상 테이블(virtual table)이다.
• 일반 테이블과 달리 데이터를 실제로 저장하고 있지 않다. 
• 뷰는 논리적으로만 존재하면서도 일반 테이블과 동일한 방법으로 사용할 수 있어 사용자는 그 차이를 느끼기 어렵다. 
• 일반적으로 뷰는 기본 테이블을 기반으로 만들어지지만 다른 뷰를 기반으로 새로운 뷰를 만들 수도 있다. 
• 뷰를 통해 기본 테이블의 내용을 쉽게 검색할 수는 있지만 기본 테이블의 내용을 바꾸는 작업은 제한적으로 이루어진다. 

 

2. 뷰의 생성

 뷰를 생성하는 명령어는 CREATE VIEW다. 기본형식은 다음과 같다. 

• SELECT 문은 생성하고자 하는 뷰의 정의를 담고 있는데, ORDER BY를 사용할 수 없다는 점만 제외하면 일반 SELECT 문과 동일하다. 
• 뷰를 구성하는 속성의 이름 리스트를 생략하면 SELECT 절에 나열된 속성의 이름을 뷰에서도 그대로 사용한다. 
• WITH CHECK OPTION은 생성한 뷰에 삽입이나 수정 연산을 할 때 SELECT 문에서 제시한 뷰의 정의 조건을 위반하면 수행되지 않도록 하는 제약조건을 의미한다. 

 

3. 뷰의 활용

• 뷰에서도 일반 테이블처럼 원하는 데이터를 검색할 수 있다. 
• 뷰에 대한 SELECT 문은 내부적으로는 기본 테이블에 대한 SELECT 문으로 변환되어 수행된다. 
• 뷰에 대한 삽입, 수정, 삭제 연산도 기본 테이블에 수행되기 때문에 결과적으로 기본 테이블이 변한다. 
• 하지만 삽입, 수정, 삭제 연산이 모든 뷰에 허용되는 것은 아니다. 뷰를 통한 기본 테이블의 변화는 제한적이다. 
• 뷰를 통해 삽입 연산을 할 때, 뷰를 통해 값을 제시하지 않은 속성의 경우 널 값이 들어가게 된다. 하지만 뷰에 기본키 속성이 제시되어 있지 않을 경우 기본키에 널 값을 넣을 수 없기 때문에 삽입 연산에 실패하게 된다. 
• 뷰에 집계 함수에 의해 계산된 값을 포함하고 있는 경우에도 위와 같은 연산은 실패한다. 어떤 튜플을 어떻게 변화시켜야 하는지 명확하지 않기 때문이다. 
• 변경이 불가능한 뷰의 중요한 특징을 살펴보면 다음과 같다.
  • 기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 뷰는 변경할 수 없다.
  • 기본 테이블에 있던 내용이 아니라 집계 함수로 새로 계산된 내용을 포함하고 있는 뷰는 변경할 수 없다. 
  • DISTINCT 키워드를 포함하여 정의한 뷰는 변경할 수 없다. 
  • GROUP BY 절을 포함하여 정의한 뷰는 변경할 수 없다.
  • 여러 개의 테이블을 조인하여 정의한 뷰는 변경할 수 없는 경우가 많다. 
• 뷰의 대표적인 장점은 다음과 같다.
  • 질의문을 좀 더 쉽게 작성할 수 있다. 
    - 특정 조건을 만족하는 튜플들로 뷰를 미리 만들어놓으면, 사용자가 복잡한 SQL 문을 작성하지 않아도 쉽게 원하는 데이터를 검색할 수 있다.
  • 데이터의 보안 유지에 도움이 된다. 
    - 사용자가 자신에게 제공된 뷰를 통해서만 데이터에 접근하도록 권한을 설정하면, 뷰에 포함되지 않은 데이터를 사용자로부터 보호할 수 있다.
    
    
  • 데이터를 좀 더 편리하게 관리할 수 있다.
    - 제공된 뷰에 포함되지 않은 기본 테이블의 다른 부분은 사용자가 신경 쓸 필요가 없다. 

 

4. 뷰의 삭제

 뷰를 삭제하기 위한 명령어는 DROP VIEW다. 기본 형식은 다음과 같다.

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

 

05. 삽입 SQL 

1. 삽입 SQL의 개념과 특징

• JAVA 등과 같은 프로그래밍 언어로 작성된 응용 프로그램 안에 삽입하여 사용하는 SQL 문을 삽입 SQL이라 한다. 
• 일반 SQL 문과 달리 삽입 SQL 문을 사용할 때는 다음과 같은 특징을 염두에 두어야 한다.
  • 삽입 SQL 문은 프로그램 안에서 일반 명령문이 위치할 수 있는 곳이면 어디든 삽입할 수 있다.
  • 프로그램 안의 일반 명령문과 구별하기 위해 삽입 SQL 문 앞에 EXEC SQL을 붙인다.
  • 프로그램에 선언된 일반 변수를 삽입 SQL 문에서 사용할 수 있다. 단, SQL 문에서 일반 변수를 사용할 때는 앞에 콜론(:)을 붙여 테이블 이름이나 속성의 이름과 구분한다. 
• 수행 결과로 여러 개의 행을 반환하는 SELECT 문을 삽입 SQL 문으로 사용하는 경우에는 커서 (cursor)라는 도구가 필요하다. 
• 커서는 수행 결과로 반환된 여러 행을 한 번에 하나씩 가리키는 포인터 역할을 한다. 
• 프로그램에서는 SELECT 문의 수행 결과로 반환되는 여러 행을 한꺼번에 처리할 수 없으므로 커서를 이용해 한 번에 한 행씩 차례로 처리해야 한다. 

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

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

 

3. 커서가 필요한 삽입 SQL

• SELECT문의 실행 결과로 여러 행이 검색되는 경우에는 한 번에 한 행씩 차례로 접근할 수 있게 해주는 커서가 필요하다. 
• 커서를 선언하는 삽입 SQL 문의 기본 형식은 다음과 같다.

 

• 커서에 연결된 SELECT 문을 실행하는 삽입 SQL 문의 기본 형식은 다음과 같다.

• SELECT 문이 실행되면 검색된 행들이 반환되고, 커서는 검색된 행들 중에서 첫 번째 행의 바로 앞에 위치한다. 
• 행들을 차례로 처리하기 위해 커서를 이동시키는 명령어는 FETCH다. 
• 커서를 이동해 처리할 다음 행을 가리키도록 하고, 커서가 가리키는 행으로부터 속성 값들을 가져와 변수에 저장하는 FETCH 문의 기본 형식은 다음과 같다.

 

• FETCH 문은 반복해서 여러 번 수행해야 하기 때문에 일반적으로 반복문과 함께 사용한다. 
• 커서의 사용을 종료하려면 CLOSE 명령어를 사용한다.

 

Reference

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