07. SQL(Structure Query Language)

제1항 SQL 언어(DDL, DML, DCL, Embedded SQL)

(１) 개요

1) SQL은 현재 DBMS 시장에서 관계 DBMS가 압도적인 우위를 차지하는데 중요한 요인 중 하나이다.

2) SQL은 IBM 연구소에서 1974년에 System R이라는 관계 DBMS 시제품을 연구할 때 관계 대수와 관계 해석을 기반으로 집단함수, 그룹화, 갱신 연산등을 추가하여 개발된 언어이다.

3) 1986년에 ANSI(미국 표준 기구)에서 SQL 표준을 채택함으로써 SQL이 널리 사용되는데 기여

4) 다양한 상용 관계 DBMS마다 지원하는 SQL 기능에 다소 차이가 있음.

 

(２) 정의 (Structure Query Language)

1) 데이터베이스에서 정보를 얻거나 갱신하기 위한 표준화된 언어로서 대화형으로 이용하거나, 또는 프로그램내에 삽입하여 쓸수 있다.

 

(３) 분류

그림 58. SQL 분류

 

1) 데이터 정의어(DDL, Data Definition Language)

① 정의

ü 데이터베이스의 구조나 스키마를 가진 오브젝트를 다루는(생성(create), 삭제(drop), 변경(alter) 등) 데이터베이스 언어이다.

ü 키 제약조건, 개체(Entity) 무결성 제약조건, 참조 무결성 제약조건 등을 포함하는 관계 데이터베이스 스키마를 생성한다.

ü 데이터베이스 관리 시스템(DBMS) 에서 사용자의 편의와 구현상의 편의를 위해 명령어를 제공함

 

2) 데이터 조작어(DML, Data Manipulation Language)

① 정의

ü 사용자나 응용 프로그램과 데이터베이스 관리 시스템(DBMS) 간의 인터페이스를 제공

ü 데이터베이스 사용자에게 적절한 명령어를 사용하여 데이터를 수정하거나 데이터를 질의

 

3) 데이터 제어어(DCL, Data Control Language)

① 정의

ü 데이터의 보안, 무결성, 데이터 회복, 병행 수행 제어에 관련하여 데이터를 관리하기 위한 데이터 제어를 정의한다.

ü 데이터 관리자가 데이터를 관리할 목적으로 사용한다.

 

(４) 데이터 정의어(DDL, data definition language)

1) 제약조건

릴레이션 정의에서 다양한 제약 조건을 명시

 

① 무결성 제약조건

ü 속성(attribute) 제약조건

• Not null

- 널 값을 허용하지 않을 때

• Unique

- 동일한 속성값을 갖는 튜플이 두개 이상 존재하지 않도록 보장

• default

- default 값을 지정

• check

- 속성이 가질수 있는 값들의 범위 지정

 

ü 기본키(primary key) 제약조건

• 각 릴레이션마다 최소 한 개의 기본키 지정

 

ü 참조무결성 제약조건

• 외래키의 무결성 보장

• On delete no action

- 다른 테이블의 기존행에 있는 외래키에서 참조하는 키가 포함된 행을 삭제하려고 하면 오류가 발생하고 delete 문이 롤백되도록 지정

• On delete cascade

-  다른 테이블의 기존행에 있는 외래키에서 참조하는 키가 포함된 행을 삭제하려고 하면 해당 외래키가 포함되어 있는 모든 행도 삭제되도록 지정합니다.

• On delete set null

- 내용이 삭제된 외래키의 값을 null로 만든다.

• On delete set default

- 내용이 삭제된 외래키의 값을 default로 설정한다.

 

• On update no action

- 다른 테이블의 기존행에 있는 외래키에서 참조하는 키가 포함된 행에서 키 값이 업데이트하려고 하면 오류가 발생하고 update 문이 롤백되도록 지정합니다.

• On update cascade

- 다른 테이블의 기존행에 있는 외래키에서 참조하는 키값이 포함된 행에서 키값을 업데이트하려고 하면 해당 외래키를 구성하는 모든값도 키에 지정된 새 값으로 수정되도록 지정합니다.

 

• On update set null

- 내용이 수정된 외래키의 값을 null로 만든다.

• On update set default

- 내용이 수정된 외래키의 값을 default로 만든다.

 

 

2) CREATE

SQL에서는 동일한 데이터베이스 응용에 속하는 릴레이션, 도메인, 제약조건, 뷰, 권한등을 구룹화하기 위해서 스키마 개념을 지원한다.

 

① CREATE SCHEMA

ü 스키마를 정의한다. 스키마에 대한 정보는 연결한 데이터베이스의 시스템 카탈로그 테이블에 저장된다.

ü 스키마의 식별을 위한 스키마와 해당 스키마의 소유권자/허가권자를 정의함

ü 기본구조

• CREATE SCHEMA schema-name [ AUTHORIZATION schema-owner-name ]

• 여기서 schema-name은 스키마 이름입니다. 이 이름은 카탈로그에 이미 기록된 스키마 내에서 고유해야 합니다. 선택적 authorization절이 지정되어 있으면 schema-owner-name이 스키마의 소유자가 됩니다.

 

② CREATE DOMAIN

ü 도메인을 정의

ü 정의된 도메인명은 일반적인 데이터 타입처럼 사용

ü 기본구조

• Create domain 도메인이름 data-type default ‘기본값지정’

• Data-type의 종류

- 정수, 실수, 날짜, 시간

- 형식화 된 숫자

- 고정길이 문자, 가변길이 문자

- 고정길이 비트열, 가변길이 비트열

• 예제

- Create domain sung char(1) default ‘여’ constraint valid sung check (value in (‘남’, ‘여’));

 

③ CREATE TABLE

ü 테이블을 정의

ü 기본 구조

create table 테이블 명칭 {

(속성명 data-type),

Primary key (기본키 속성명),

Unique (대체키 속성명),

Foreign key (외래키 속성명),

References 참조테이블(기본키 속성명),

Check (조건식);

};

ü 예제

Create table 학생 {

학번 char(20),

이름 char(20) not null,

생년월일 date,

Primary key(학번),

Unique(생년월일),

Foreign key(과목코드) references 과목(과목코드)

On delete set null on update cascade,

Check 생년월일>=’1995-03-02’

};

 

                 

 

④ CREATE VIEW

ü 사용자에게 접근이 허용된 자료만 보여주기 위해 하나 이상이 테이블에서 유도된 가상 테이블

ü 물리적으로 존재하지 않고 논리적으로만 존재

ü 기본 구조

Create view 뷰이름(속성명, …)

As select 속성명, …

From 테이블명

Where 조건;

[with check option]

ü 예제

Create view 컴퓨터(이름, 과목) as

             Select 이름, 과목

             From 학생

             Where 과목=’DB’;

 

 

⑤ CREATE INDEX

ü 검색 성능 향상 위해 사용

ü 기본 구조

• Create [unique] index 인덱스이름 on 기본테이블이름(속성이름 정렬방식)

ü 예제

• Create unique index 인덱스이름 on 고객(속성이름 정렬방식);

• 정렬방식: asc(오름차순),  desc(내림차순)

 

3) ALTER TABLE

① 테이블, 인덱스, 스키마 등에 대한 구조를 변경

② 기본 구조

ü Alter table 테이블이름 add 속성이름 data-type [default 값];

ü Alter table 테이블이름 alter 속성이름 data-type [set default 값];

ü Alter table 테이블이름 drop 속성이름 data-type [cascade];

③ 예제

ü Alter table 학생 add 주민번호 char(18);

ü Alter table 학생 drop 전화번호;

 

 

 

 

4) DROP

① 스키마, 도메인, 테이블, 뷰, 인덱스를 제거하는 명령문

② 기본 구조

Drop         schema 테이블 이름 [cascade | restrict]

                  Domain

                  Table

                  View

                  Index

 

Cascade : 릴레이션을 참조하는 뷰, 인덱스, 제약조건, 외래키 모두 삭제

Restrict : 다른 릴레이션에서 참조되지 않는 릴레이션만 제거

 

③ 예제

ü Drop schema 학교 cascade;

ü Drop table 학생 restrict;

 

 

※ 데이터 제어어(dcl)은 데이터의 보안, 무결성, 데이터 회복, 병행 수행 제어 등을 정의하는데 사용하는 언어이다.

 

(５) 데이터 조작어(DML, data manipulation language)

1) SELECT

① 테이블을 구성하는 튜플들 중에서 조건에 만족하는 튜플을 검색하여 임시 테이블을 만드는 명령

② 기본 구조

Select [all | distinct] 검색대상

From 테이블명

[where 조건식]

[group by 열명칭]

[having 검색조건]

[order by 열명칭 [asc | desc];

• Distinct : 중복된 데이터 한번만 출력

• Asc : 오름차순

• Desc : 내림차순

 

③ 설명

ü Select

• 질의 결과를 포함하려는 속성들을 열거

• Distinct 절을 사용해서 중복 제거

ü from

• 질의에서 필요로 하는 릴레이션들을 열거

ü where

• 관계대수의 셀렉션 연산의 조건에 해당

ü Group by

• 동일한 값을 갖는 튜플들을 한 그룹으로 묶는다

ü having

• 튜플들의 그룹이 만족해야 하는 조건

 

④ 별칭(alias)

ü 서로 다른 릴레이션에 동일한 이름을 가진 속성이 속해 있을 때 속성의 이름을 구분하는 방법

ü Select e.dno, d.dno from employee as e, department as d

 

⑤ 예제

ü 테이블의 전체 속성(*) 보기

• Select * from 테이블명

• 테이블의 존재하는 모든 튜플과 모든 속성을 보여준다.

ü 테이블에서 중복을 없앤 속성(attribute) 보기

• Select distinct 속성명 from 테이블명

• 테이블에서 지정한 속성만을 보여주는데, 중복된 속성은 하나만 보여준다.

ü 테이블에서 조건에 만족하는 튜플만 보기

• Select * from 테이블명 where 속성명 <> ’값’

• 테이블에서 지정된 속성의 값이 일치하는 튜플만을 보여준다.

• 부정연산자 : <>

ü 테이블에서 여러 개의 조건을 만족하는 튜플만 보기

• Select * from 테이블명 where 속성명=’값’and 속성명=’값’

• 테이블에서 지정된 속성들의 값과 일치하는 튜플만을 보여준다.

• 연산자들의 우선순위

ü 테이블에서 % 조건에 만족하는 튜플만 보기

• Select * from 테이블명 where 속성명 like ‘류%’

• 테이블에서 지정된 속성의 값이 ‘류’로 시작하는 모든 튜플을 보여준다.

• ‘%’는 모든 문자를 말하며, ‘_’는 한 문자를 의미함

ü 테이블에서 범위 조건에 만족하는 튜플만 보기

• Select * from 테이블명 where 속성명 between 0 and 100

• Select * from 테이블명 where 속성명 >= 0 and 속성명 <= 100

• 속성명의 값이 0보다 크거나 같고 100보다 작거나 같은 튜플만 보여준다.

ü 테이블에서 지정된 속성의 값이 null인 모든 튜플 보기

• Select * from 테이블명 where 속성명 is null

• Select * from 테이블명 where 속성명 is not null

 

 

⑦ Group by 속성 [having 조건]

ü 조건에 맞게 속성들을 그룹별로 처리한다.

ü Group by는 특정 속성을 기준으로 그룹화하여 검색할 때 그룹화 할 속성을 지정한다.

ü Having은 group by와 함께 사용되며, 그룹에 대한 조건을 지정한다.

ü 예제

• Select 속성명, count(*) as 표시명 from 테이블명 where 속성명 >= 90 group by 과목 having count(*)>=2

 

   

⑧ Order by 속성 [asc | desc]

ü 지정된 속성으로 정렬을 한다.

ü Asc: 오름차순(a-z, ㄱ-ㅎ), desc: 내림차순(z-a, ㅎ-ㄱ)

ü 예제

• Select * from 속성명 where 속성명=’값’ order by 속성명 desc

• 속성명이 ‘값’이 튜플을 보여주는데, order by에 지정된 속성명으로 내림차순 정렬을 해서 보여준다.

 

⑨ in

ü 조건절에 명시된 속성값이 in 다음에 나열되는 값들과 일치되는 튜플들만 표시한다.

ü Select * from 테이블명 where 속성명 in (select 속성명 from 테이블명)

 

⑩ Null 값

ü 널값을 포함한 다른 값과 널값을 +, - 등을 사용하여 연산하면 결과는 널이 됨

ü Count(*)를 제외한 집단 함수들은 널값을 무시함

ü 어떤 속성에 들어 있는 값이 널인지 비교하기 위해서 ‘속성=null’ 처럼 사용하면 안됨

ü 다음과 같은 비교 결과는 모두 거짓

• Null > 300

• Null = 300

• Null <> 300

• Null = null

• Null <> null

 

ü 바른 표현

• Select * from 테이블명 where 속성 is null;

 

⑪ 집단함수

ü 정의

• 각 집단함수는 한 릴레이션의 한 개의 속성에 적용되어 단일값을 변환함

• Select절과 having절에만 나타날 수 있음

• count(*)를 제외하고는 모든 집단함수들이 널값을 제거한 후 남아 있는 값들에 대해서 집단 함수의 값을 구함

• count(*)는 결과 릴레이션의 모든 행들의 총 개수를 구함

• count(속성명)는 해당 속성에서 널값이 아닌 값들의 개수를 구함

• distinct가 집단 함수 앞에 사용되면 집단 함수가 적용되기 전에 먼저 중복을 제거함

 

ü 종류

• Count(속성): 튜플이나 값들의 개수

• Avg(속성): 값들의 평균값

• Sum(속성): 값들의 합

• Max(속성): 값들의 최대값

• Min(속성): 값들의 최소값

 

⑫ Exists(q), not exists(q)

ü Exists: 질의 q의 결과에 최소한 한 개의 튜플이 있으면 참, 그렇지 않으면 거짓을 반환한다.

ü Not exists: 질의 q의 결과에 튜플이 없다면 참, 그렇지 않으면 거짓을 반환한다.

 

⑬ 비교연산자

ü = : 같다

ü <> : 같지 않다

ü > :크다

ü < :작다

ü >= : 크거나 같다

ü <= : 작거나 같다

ü In : 포함되어 있다

 

⑭ select에서의 집합 연산

ü 정의

• 집합연산을 적용하려면 두 릴레이션이 합집합 호환성을 가져야 함

• 입력 릴레이션 결과 릴레이션에서 중복 튜플 배제

- Union(합집합)

- Except(차집합)

- Intersect(교집합)

• 입력 릴레이션 결과 릴레이션에서 중복 튜플 허용

- Union all(합집합)

- Except all(차집합)

- Intersect all(교집합)

⑮ select에서의 조인(join)

ü 정의

• 조인은두개의 릴레이션으로부터 연관된 튜플들을 결합

• 조인의 일반적인 형식은 아래의 select문과 같이 from절에 두개 이상의 릴레이션들이 열거되고, 두 릴레이션에 속하는 애트리뷰트들을 비교하는 조인 조건이 where절에 포함됨

• 조인 조건은 두 릴레이션 사이에 속하는 속성값들을 비교 연산자로 연결한 것

• 가장 흔히 사용되는 비교 연산자 ‘=’

 

• 조인 조건을 생략했을 때와 조인 조건을 틀리게 표현했을 때는 카티션 곱이 생성됨

• 조인 질의가 수행되는 과정을 개념적으로 살펴보면 먼저 조인 조건을 만족하는 튜플들을 찾고, 이 튜플들로부터 SELECT절에 명시된 속성들만 프로젝트하고, 필요하다면 중복을 배제하는 순서로 진행됨

• 조인 조건이 명확해지도록 속성 이픔 앞에 릴레이션 이름이나 튜플변수를 사용하는 것이 바람직

• 두 리레이션의 조인 속성명이 동일하다면 반드시 속성명앞에 릴레이션 이름이나 튜플변수를 사용해야 함

 

 

 

2) INSERT

① 데이터베이스에 저장된 튜플을 삽입하기 위한 언어

② 기본 구조

Insert into 테이블명

Values (데이터 값, 데이터 값2, …);

③ 예제

ü 테이블에 튜플을 추가해라

• Insert into 테이블명 values (‘값1’, ‘값2’, ‘값3’)

• 지정된 테이블에 튜플을 추가한다.

 

ü 테이블에 지정된 속성들의 값을 추가해라

• Insert into customers (customerName, ContactName, Address, City, PostalCode, Country) values (‘Cardinal’, “Tom’, ‘Skagen’, ‘Stavanger’, ‘4006’, ‘Norway’);

 

ü 테이블에 지정된 속성들의 값을 추가해라

• Insert into Customers (CustomerName, City, Country) values (‘Cardinal’, ‘Stavanger’, ‘Norway’)

 

ü 테이블에 지정된 속성들의 값을 추가해라

• Insert into Customers (CustomerName, City, Country) values (‘Cardinal’, ‘Stavanger’, ‘Norway’)

 

Customers 테이블

Suppliers 테이블

ü 여러 개의 튜플들을 추가해라

• Insert into Customers (CustomerName, Country) select SupplierName, Country from Suppliers;

• Insert into Customers (CustomerName, Country) select SupplierName, Country from Suppliers where country=’USA’;

 

3) UPDATE

① 테이블에 있는 튜플들 중에서 지정된 튜플의 내용을 수정할 때 사용하는 언어

② 기본 구조

Update 테이블명

Set 열명칭 = 변경값

[where 조건식];

 

③ 예제

 

ü 지정된 튜플의 값을 변경하라.

• Update Customers set ContactName=’Alfred Schmidt’, City=’Hamburg’ where CustomerName=’Alfreds Futterkiste’;

 

4) DELETE

① 테이블에 있는 튜플들 중에서 지정된 튜플을 삭제할 때 사용하는 언어

② 기본 구조

Delete from 테이블명

[where 조건식];

 

③ 예제

ü 지정된 튜플을 삭제하라

• Delete from Customers where CustomerName=’Alfreds Futterkiste’;

 

ü 테이블의 모든 튜플을 삭제하라

• Delete from Customers

• Delete * from Customers

• 테이블에 있는 모든 튜플들이 삭제되는 것이지 테이블 자체가 삭제되는 것은 아니다. 테이블 자체를 삭제하는 명령어는 drop 이다.

 

 

(６) 데이터 제어어(DCL, data control language)

1) GRANT

① 데이터베이스 사용자에게 사용권한 부여

② 기본 구조

Grant 권한 on 테이블명 to 사용자명 [with grant option];

 

③ 예제

• Grant select on 테이블명 to rhk [with grant option];

• 권한: all, insert, delete, update, select 등

• With grant option : 사용자가 받은 권한을 다른 사용자에게 부여할 수 있음

 

2) REVOKE

① 데이터베이스 사용자의 사용 권한을 취소

② 기본 구조

Revoke 권한 on 테이블명 from 사용자명 [cascade];

 

③ 예제

• Revoke select on 수강생 from rhk [cascade]

• Cascade : 권한을 부여받은 사용자가 다른 사용자에게 부여한 권한도 취소

 

3) COMMIT

① 데이터베이스 조작 작업을 영구적으로 반영하여 완료함

 

4) ROLLBACK

① 데이터베이스 조작 작업이 비정상적으로 종료되었을 때 원래의 상태로 복구