SQL

목표

1.SQL의 개요에 대해서 알아본다.

2.DDL을 살펴보고 무결성 제약조건을 이해한다.

3.DML의 여러 조건과 예시를 살펴본다.

4.트리거(Trigger)와 주장(Assertioin)에 대해서 이해한다.

   

SQL의 개요

SQL(Structed Query Language)은 현재 DBMS 시장에서 관계 DBMS가 압도적인 우위를 차지하는 중요한 요인중 하나이다. 다양한 상용 DBMS마다 지원하는 SQL은 다소 차이가 있으며 여기서 다루는SQL은 SQL2를 따른다.

SQL은 비절차적 언어(선언적)으로 사용자는 자신이 원하는 바(what)만 명시하며, 원하는 것을 처리하는 방법(How)는 명시할 수 없다. 대화식SQL과 내포된SQL이 있는데 대화식 SQL을 다룬다. 

DDL과 무결성 제약조건

<DDL의 종류>

*스키마를 생성과 제거할 수 있다. 동일한 데이터베이스 응용에 속하는 릴레이션, 도메인. 제약조건, 뷰 권한 등을 그륩화하기 위함이다.

릴레이션 생성

데이터 타입으로는 여러가지가 있는데 VARCHAR에 대해서 잠깐 짚고 넘어가자.

릴레이션 제거

DROP TABLE DEPARTMENT;

릴레이션 변경

ALTER TABLE EMPLOYEE ADD PHONE CHAR(13);

인덱스 생성

CRATE INDEX EMPDNO_IDX ON EMPLOYEE(DNO)

릴레이션을 생성하면서 무결성 제약조건을 명시할 수 있다. 

무결성 제약조건은 추가, 삭제가 가능하다. 

DML

SELECT 문

관계 데이터베이스에서 정보를 검색하는 SQL문이다. SELECT절과 FROM절만 필수이며, 나머지는 선택이다. 예시를 보자.

복잡한 SELECT문을 질의하다보면 서로 다른 릴레이션에 동일한 이름을 가진 애트리뷰트가 속할 수도 있는데, 이들을 구분하기 위해 '별칭(alias)'를 사용한다.

릴레이션의 모든 애트리뷰트나 일부 애트리뷰트의 모든 튜플들을 검색할때는 * 기호를 사용한다. 예시는 넘어간다.

만약 SELECT질의의 결과가 중복된 튜플이라면 unique한 값만 보고 싶다면 DISTINCT절을 쓰면 된다. 예시를 보자.

 

특정한 조건의 튜플을 검색할 땐 WHERE절을 쓴다.

튜플들의 문자열을 비교하여 검색하고 싶을때는 % 기호를 사용한다. 

다수의 검색조건을 사용할 수도 있다. 다만, 아래의 조건은 잘못되었다. 

부정 검색 조건의 예시를 보자.

범위를 사용한 검색의 예시를 보자.

검색할 때 리스트가 조건의 대상일 수도 있다. 

SELET절은 기본 산술연산을 제공한다. 

어떤 튜플의 애트리뷰트값이 널값일 경우 널 값을 포함한 다른 값과 널 값을 +,- , 비교 등을 사용하여 연산하면 결과는 항상 널이다.

마찬가지로, WHERE DNO=NULL 같은 질의는 실행되지 않는다. 올바늘 표현은 WHERE DNO IS NULL;이다.

사용자가 SELECT문에서 질의 결과에 대한 순서를 명시하지 않으면 튜플의 삽입 순서대로 결과가 나타난다. 이 때 ORDER BY절에서 하나 이상의 애트리븉를 사용하여 결과를 정렬할 수 있다. SELECT문에서 가장 마지막에 쓰이며 디폴트 정렬 순서는 오름차순(ASC)이다. DESC로 내림차순으로 정렬할 수 있다. 

널 값은 오름차순에서 가장 마지막에 나타나고, 내림차순에서는 가장 앞에 나타난다. 예시를 보자.

SQL에서는 집단함수를 제공한다. 여러 튜플들의 집단에 적용되며, 한 릴레이션의 한 개의 애트리뷰트에 적용되어 단일 값만 반환한다. SELECT절과 HAVING절에서만 쓸 수 있으며 DISTINCT 를 쓸 경우 집단함수가 적용되기 전에 중복을 제거한다.  COUNT , SUM , AVG , MAX, MIN 함수가 있다.

GROUP BY절에 사용된 애트리뷰트에 동일한 값을 갖틑 투플들이 각각 하나의 그륩으로 묶이게 된다. 이 애트리뷰트를 그륩화 애트리뷰트라고 한다.

각 그륩에 대하여 결과 릴레이션에 하나의 튜플이 생성된다. SELECT절에는 각 그륩마다 하나의 값을 갖는 애트리뷰트, 집단 함수, 그륩화에 사용된 애트리뷰트들만 나타날 수 있다. 다음 질의는 그륩화를 하지 않은 채 EMPLOYEE 릴레이션의 모든 투플에 대해서 사원번호와 모든 사원들의 평균 급여를 검색하므로 잘못되었다.

SELECT EMPNO , AVG(SALARY) FROM EMPLOYEE;

올바른 예시를 보자.

HAVING절

어떤 조건을 만족하는 그륩들에 대해서만 집단 함수를 적용할 수 있다.

각 그륩마다 하나의 값을 갖는 애트리뷰트를 사용하여 각 그륩이 만족해야 하는 조건을 명시한다. 다시 말해 그륩화 애트리뷰트에 같은 값을 갖는 튜플들의 그륩에 대해 조건을 나타내고, 이 조건을 만족하는 그륩들만 질의결과에 나타난다. HAVING절에 나타나는 애트리뷰트는 반드시 GROUP BY 절에 나타나거나, 집단 함수에 포함되어야 한다.

집합연산

집합연산을 적용하기 위해선 두 릴레이션이 합집합 호환성을 가져야 한다.

UNION(합집합) , EXCEPT(차집합) , INTERSECT(교집합) , UNION ALL(합집합) , EXCEPRT ALL(차집합) , INTERSECT ALL(교집합)

조인

두 개 이상의 릴레이션으로부터 연관된 투플들의 모음이다. 일반적인 형식은 아래의 SELECT문과 같이 FROM절에 두 개 이상의 릴레이션들이 열거되고, 두 릴레이션에 속하는 애트리뷰트들을 비교하는 조인 조건이 WHERE절에 포함된다. 한 마디로 조인 조건은 두 릴레이션 사이이 애트리뷰트 값들의 비교 연산자로 연걸한다. 

조인 조건이 명확해지도록 애트리뷰트 이름 앞에 릴레이션 이름이나 튜플 변수를 사용하는것이 바람직하다. 예시를 보자.

조인의 종류별로 살펴보자.

자체조인

한 릴레이션에 속하는 튜플을 동일 릴레이션의 튜플과 조인하는 것이다. 

실제로는 하나의 릴레이션에 접근되지만 질의에서는 두 릴레이션이 참조되므로 별칭을 지칭해야 한다.

최종결과는 다음과 같다.

 

INNER JOIN

OUTER JOIN

조인과 ORDERY BY의 결합 예시를 살펴보자.

중첩질의

외부 질의의 WHERE절에 다시 SELECT문을 활용된 SELECT문을 말한다. 부질의(subquery)라고도 함. 

이 때 한 개의 애트리뷰트로 이루어진 릴레이션이 반환되는 경우가 있다.

외부 질의의 WHERE절에서 IN , ANY(SOME) , ALL , EXISTS와 같은 연산자를 사용해야 한다. 

키워드 IN은 한 애트리뷰트가 값들의 집합에 속하는가를 테스트 할 때 사용된다.

한 애트리뷰트가 값들의 집합에 속하는 하나 이상의 값들과 어떤 관계를 갖는가를 테스트하는 경우에는 ANY를,

한 애트리뷰트가 값들의 집합에 속하는 모든 값들과 어떤 관계를 갖는가를 테스트하는 경우에는 ALL을 사용한다.

사실 이 질의는 중첩질의를 쓰지 않고, 조인 질의로도 표현할 수 있다. 

INSERT문

한 번에 하나의 튜플을 삽입할 수도 있고, 여러개의 튜플을 삽입 할 수도 있다. 

DELETE문

UPDATE문

트리거와 주장

트리거란 명시된 이벤트(데이터베이스 갱신)가 발생할 때마다 DBMS가 자동적으로 수행하는, 사용자가 정의하는 문(프로시져)이다.

데이터베이스의 일관성을 유지하기 위한 일반적이고 강력한 도구이다. 

트리거를 명시하기 위해서는 트리거를 활성화시키는 이벤트, 트리러가 활성화되었을 떄 수행되는 테스트 조건, 트리거가 활성화되고 조건이 참일 때 수행되는 문(프로지서)인 동작을 명시해야 한다.

이벤트-조건-동작(ECA)규칙이라고도 부른다. 

이벤트의 예시로는 릴레이션의 튜플 삽입, 튜플 삭제, 수정 등이 있다. 조건은 임의의 프레디키트, 동작은 데이터베이스의 임의의 갱신이다. 

하나의 트리거가 활성화되어 이 트리거 내의 SQL문이 수행되고, 그 결과로 다른 트리거를 활성화하여 SQL문이 수행될 수도 있다.

SQL3에는 있으나 대부분 상용DBMS에서는 지원하지 않는다. 트리거는 제약조건을 위반했을 때 수행할 동작을 명시하는 것이라면, 주장은 제약조건을 위반하는 연산이 수행되지 않도록 하는 것이다. DBMS는 주장의 프레디키트를 검사하여 만일 참이면 주장을 위배하지 않으므로 데이터베이스 수정이 혀용되는 것이다. 

참고

데이터베이스 배움터(생능출판)