5. SQL(Structured Query Language) - 데이터 조작어(DML)

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

• 데이터베이스 내의 원하는 데이터를 검색, 수정, 삽입, 삭제한다. 대부분의 데이터 조작어는 SUM, COUNT, AVG와 같은 내장 함수들을 갖고 있다. 일반적으로 프로그램에 내포(코드에 포함)되어 사용된다.
• 데이터 검색 : SELECT [col1], [col2], ... FROM [tablename] [option1] [option2], ...;

ex) SELECT * FROM [tablename]; -> 전체 데이터 조회

• 데이터 수정 : UPDATE [tablename] SET [col1=new_data1], [col2=new_data2], ... WHERE [target_col]=[value];
• 데이터 삭제 : DELETE FROM [tablename] WHERE [target_col]=[value];

ex) DELETE FROM [tablename]; -> 전체 데이터 삭제

• 데이터 삽입 : INSERT INTO [tablename] ([col1], [col2], ...) VALUES ([data1], [data2], ...);

- 컬럼명을 지정하지 않을 경우, 스키마 구조의 순서에 맞게 데이터를 삽입해야 한다.

- 컬럼명을 지정할 경우, 지정한 컬럼명의 순서에 맞게 데이터를 삽입해야 한다.

* 아래의 모든 예제는 MSSQL을 기반으로 작성됩니다.

  SELECT 문

• 관계 데이터베이스에서 정보를 검색하는 SQL문
• 관계 대수의 실렉션(Selection) 연산자와는 의미가 완전히 다르다.
• 관계 대수의 실렉션(Selection), 프로젝션(Projection), 조인(Join), 카티션 곱(Cartisian Product) 등을 결합한 것
• 관계 데이터베이스에서 가장 자주 사용된다.

빨간색은 필수적인 절이고, 나머지는 선택 사항

선택 사항은 모두 포함할 필요는 없으나 순서는 매우 중요

• 별칭(Alias) : 서로 다른 릴레이션에 동일한 이름을 가진 애트리뷰트가 속해 있을 때 애트리뷰트의 이름을 구분하는 방법

[tablename1.col] FROM [tablename1] AS [alias1], [tablename2] AS [alias2];

 

 ex) EMPLOYEE.DNO FROM EMPLOYEE AS E, DEPARTMENT AS D;

 

이후 E.DNO 또는 D.DNO 로 애트리뷰트를 구분할 수 있다.

 

질의 : 전체 부서의 모든 애트리뷰트들을 검색하라.

 

이 질의는 좋은 질의가 아닌데, 그 이유는 컬럼 추가 시 응용 프로그램 입장에서 오류가 발생할 수 있기 때문이다.

따라서 아래와 같이 조회할 컬럼을 지정하는 것이 적절하다.

* 빨간색 물결 밑줄은 스키마(ex. KIM.DEPARTMENT)를 명시하지 않아서 뜨는 겁니다.

질의 : 모든 부서의 부서 번호와 부서 이름을 검색하라.

 

질의 : 모든 사원들의 직급을 검색하라.

  

 

질의 : 모든 사원들의 상이한 직급을 검색하라.

 

DISTINCT 절은 중복을 없애주는데, 해당 쿼리는 성능이 저하되므로 불필요한 사용은 자제해야 한다.

 

질의 : 2번 부서에 근무하는 사원들에 관한 모든 정보를 검색하라.

 

WHERE 절은 조건문에 해당하며, 주로 기본 키와 함께 사용된다.

 

질의 : 이씨 성을 가진 사원들의 이름, 직급, 소속 부서 번호를 검색하라.

문자열 탐색 시 MSSQL 내장 함수 SUBSTR을 사용해도 상관없지만, DBMS 공용 함수인 LIKE를 사용하였다.

LIKE 뒤에 %의 위치에 따라 검색하고자 하는 문자열의 위치를 정할 수 있다.

... LIKE '김%' : 김으로 시작하는 문자열 검색

... LIKE '%김%' : 김을 포함하는 문자열 검색

... LIKE '%김' : 김으로 끝나는 문자열 검색

 

질의 : 직급이 과장이면서 1번 부서에서 근무하는 사원들의 이름과 급여를 검색하라.

 

질의 : 직급이 과장이면서 1번 부서에 속하지 않은 사원들의 이름과 급여를 검색하라.

<>는 부정 연산자이다.

 

질의 : 급여가 3,000,000원 이상이고 4,500,000원 이하인 사원들의 이름, 직급, 급여를 검색하라.

 

 

질의 : 1번 부서나 3번 부서에 소속된 사원들에 관한 모든 정보를 검색하라.

DNO IN (1, 3) : DNO=1 OR DNO=3 의 간결한 표현

질의 : 직급이 과장인 사원들에 대하여 이름과, 현재의 급여, 급여가 10% 인상되었을 때의 값을 검색하라.

  

AS [new_col] 절이 없을 경우 빈 컬럼명이 나타나므로, 수식 사용 시 별칭(Alias)이 필수적으로 요구된다.

  

  NULL 값

• 널값을 포함한 다른 값과 널값을 +, - 등을 사용하여 연산하면 결과는 널값이다.
• COUNT(*)을 제외한 집단 함수(AVG, MAX, MIN ..., etc)들은 널값을 무시한다.
• 어떤 애트리뷰트에 들어 있는 값이 NULL인가를 비교하려면 아래와 같은 구문을 사용해야 한다.

 

  ORDER BY 절

• 하나 이상의 애트리뷰트를 사용하여 검색 결과를 정렬할 때 사용한다.
• 해당 절을 사용하지 않을 경우 릴레이션에 투플들이 삽입된 순서대로 보여진다.
• SELECT 문에서 가장 마지막에 사용되는 절이다.
• 디폴트 정렬 순서는 오름차순(ASC)이고, DESC를 지정하여 내림차순으로 지정할 수 있다.
• 널값은 오름차순에서는 가장 마지막에 나타나고, 내림차순에서는 가장 먼저 나타난다.
• SELECT 절에서 조회할 애트리뷰트들을 사용해서 정렬해야 한다.

 

질의 : 2번 부서에 근무하는 사원들의 급여, 직급, 이름을 검색하여 급여의 오름차순으로 정렬하라.

  

 

  집단 함수

• 데이터베이스에서 검색된 여러 투플들의 집단에 적용되는 함수
• 한 릴레이션의 한 개의 애트리뷰트에 적용되어 단일값을 반환한다.
• SELECT 절과 HAVING 절에만 나타날 수 있다.
• COUNT(*)는 결과 릴레이션의 모든 행들의 총 개수를 구하는 반면에 COUNT(애트리뷰트)는 해당 애트리뷰트에서 널값이 아닌 값들의 개수를 구한다.
• COUNT(*)를 제외하고는 널값을 제거한 후 남아 있는 값들에 대해서 집단 함수의 값을 구한다.
• SQL문에서 키워드 DISTINCT가 집단 함수와 함께 사용되면 집단 함수가 적용되기 전에 먼저 중복을 제거한다.
• COUNT : 투플이나 값들의 개수
• SUM : 값들의 합
• SVG : 값들의 평균값
• MAX : 값들의 최댓값
• MIN : 값들의 최솟값

 

질의 : 모든 사원들의 평균 급여와 최대 급여를 검색하라.

 

 

  GROUP BY 절

• 그룹화를 위해 사용된 애트리뷰트에 동일한 값을 갖는 투플들을 하나의 그룹으로 묶는다.
• 이 애트리뷰트를 그룹화 애트리뷰트(grouping attribute)라고 한다.
• 각 그룹에 대하여 결과 릴레이션에 하나의 투플이 생성된다.
• SELECT 절에는 각 그룹마다 하나의 값을 갖는 애트리뷰트, 집단 함수, 그룹화에 사용된 애트리뷰트들만 나타날 수 있다.
• 아래의 질의는 그룹화를 하지 않고 EMPLOYEE 릴레이션의 모든 투플에 대해서 사원번호와 모든 사원들의 평균 급여를 검색하므로 잘못된 것이다.

[ 질의 결과 ]

올바른 질의는 아래의 경우다.

     [ 질의 결과 ]

 

질의 : 모든 사원들에 대해서 사원들이 속한 부서 번호 별로 그룹화하고, 

각 부서마다 부서 번호, 평균 급여, 최대 급여를 검색하라

 

 

  HAVING 절

• 그룹화된 애트리뷰트에 대하여 그룹이 만족해야 하는 조건을 명시한다.
• 어떤 조건을 만족하는 그룹들에 대해서만 집단 함수를 적용하기 사용된다.
• HAVING 절에 나타나는 애트리뷰트는 반드시 GROUP BY 절에 나타나거나 집단 함수에 포함되어야 한다.

 

질의 : 모든 사원들에 대해서 사원들이 속한 부서 번호 별로 그룹화하고, 

평균 급여가 2500000원 이상인 부서에 대해서 부서 번호, 평균 급여, 최대 급여를 검색하라.

 

 

  집합 연산

• 집한 연산을 적용하려면 두 릴레이션이 합집합 호환성을 가져야 한다.
• UNION : 합집합
• EXCEPT : 차집합
• INTERSECT : 교집합
• UNION ALL : 중복된 결과를 포함한 합집합
• EXCEPT ALL : 중복된 결과를 포함한 차집합
• INTERSECT ALL : 중복된 결과를 포함한 교집합

 

질의 : 김창숙이 속한 부서이거나 개발 부서의 부서 번호를 검색하라.

아래는 다른 예시이다.

  조인(Join)

• 조인은 두 개 이상의 릴레이션으로부터 연관된 투플들을 결합한다.
• 조인의 일반적인 형식

• 조인 조건 : 두 릴레이션 사이에 속하는 애트리뷰트 값들을 비교 연산자로 연결한 것
• 가장 흔히 사용되는 비교 연산자 : =
• 조인 조건 생략 및 잘못된 표현을 할 경우 카티션 곱이 적용된다.
• 조인 질의가 수행되는 과정

조인 조건을 만족하는 투플 검색

↓

검색한 투플들 중 SELECT 절에 명시된 애트리뷰트들만 프로젝트

↓

DISTINCT 키워드 사용 시 중복 배제

• 조인 조건이 명확해지도록 애트리뷰트 이름 앞에 릴레이션 이름이나 투플 변수를 사용하는 것이 바람직하다. 특히, 두 릴레이션의 조인 애트리뷰트 이름이 동일하다면 반드시 애트리뷰트 이름 앞에 릴레이션 이름이나 투플 변수를 사용해야 한다. 그렇지 않으면 오류 발생

-> [tablename] AS [tuple_variable]

• 결과 릴레이션의 투플 수는 외래 키의 투플 수와 동일하거나 외래 키에 널값이 있으면 나타나지 않으므로 외래 키의 투플 수보다 작을 수 있다.

 

질의 : 모든 사원의 이름과 이 사원이 속한 부서 이름을 검색하라.

EMPLOYEE의 투플 변수는 E, DEPARTMENT의 투플 변수는 D이다.

위의 릴레이션은 아래의 결과 릴레이션에서 EMPNAME과 DEPTNAME을 프로젝션한 결과이다.

• 자체 조인(Self Join)

- 한 릴레이션에 속하는 투플을 동일한 릴레이션에 속하는 투플들과 조인하는 것

- 자기 자신을 참조하는 릴레이션처럼 포함 관계나 순환 관계가 있을 때만 사용한다.

- 실제로는 한 릴레이션이 접근되지만 FROM 절에 두 릴레이션이 참조되는 것처럼 나타내기 위해서 그 릴레이션에 대한 별칭(Alias)을 두 개 지정해야 한다.

 

질의 : 모든 사원의 이름과 이 사원이 속한 부서 이름을 검색하라.

질의 : 모든 사원에 대해서 소속 부서 이름, 사원의 이름, 직급, 급여를 검색하라. 

부서 이름에 대해서 오름차순, 부서 이름이 같은 경우에는 급여에 대해서 내림차순으로 정렬하라.

 

  중첩 질의(Nested Query)

• 외부 질의의 WHERE 절에 다시 SELECT ... FROM ... WHERE 형태로 포함된 SELECT 문
• 부질의(Subquery)라고도 한다.
• INSERT, DELETE, UPDATE문에도 사용될 수 있다.
• 중첩 질의의 결과로 한 개의 단일값, 한 개의 애트리뷰트로 이루어진 릴레이션, 여러 애트리뷰트로 이루어진 릴레이션이 반환될 수 있다.
• 한 개의 단일값을 가진 결과 릴레이션은 비교 연산자의 우항으로 사용될 수 있다.
• 중첩 질의의 형식

 

• 한 개의 단일값이 반환되는 경우

질의 : 박영창과 같은 직급을 갖는 모든 사원들의 이름과 직급을 검색하라.

- 위의 중첩 질의(괄호 안의 질의)로부터 ‘과장’ 이라는 단일값이 반환된다.

- 단일값을 반환하는 SELECT문의 조건(WHERE)에는 중복이 있는 “컬럼=값” 형태가 오면 안된다. 따라서 실제로 위의 질의는 부적절하다고 볼 수 있다. 박영창이라는 이름은 중복될 수 있기 때문이다.

- 조건에 중복이 있으면 안되는 이유 : 비교 연산자 사용이 불가능하기 때문이다.

• 한 개의 애트리뷰트로 이루어진 릴레이션이 반환되는 경우

- 중첩 질의의 결과로 한 개의 애트리뷰트로 이루어진 다수의 투플들이 반환 될 수 있다.

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

키워드 IN

한 애트리뷰트의 값이 주어진 값들의 집합에 속하는지 확인할 때 사용된다.

키워드 ANY 또는 SOME

한 애트리뷰트의 값이 주어진 값들의 집합에 속하지만 

하나 이상의 값들과 어떤 관계를 갖는지 확인할 때 사용된다.

키워드 ALL

한 애트리뷰트의 값이 주어진 값들의 집합에 속하는 모든 값들과 어떤 관계를 갖는가를 확인할 때 사용된다.

 

질의 : 영업부나 개발부에 근무하는 사원들의 이름을 검색하라.

 

위의 질의를 중첩 질의를 사용하지 않은 다음과 같은 조인 질의로 나타낼 수 있다.

 

실제로, 중첩 질의를 사용하여 표현된 대부분의 질의를 중첩 질의가 없는 조인 질의로 표현할 수 있다.

• 여러 애트리뷰트들로 이루어진 릴레이션이 반환되는 경우

- EXISTS 연산자를 사용하여 중첩 질의의 결과가 빈 릴레이션인지 여부를 검사한다.

- 중첩 질의의 결과가 빈 릴레이션이 아니면 참이 되고, 그렇지 않으면 거짓이 된다.

 

질의 : 영업부나 개발부에 근무하는 사원들의 이름을 검색하라.

 

- 외부 질의의 대상이 되는 테이블(위의 경우 EMPLOYEE)의 총 투플 수 만큼 중첩 질의가 실행된다. 이를 상관 중첩 질의라고 한다.

• 상관 중첩 질의(Correlated Nested Query)

- 중첩 질의의 WHERE절에 있는 프레디키트(조건)에서 외부 질의에 선언된 릴레이션의 일부 애트리뷰트를 참조하는 질의

- 중첩 질의의 수행 결과가 단일 값이든, 하나 이상의 애트리뷰트로 이루어진 릴레이션이든 외부 질의로 여러 번 결과를 반환한다.

- 상관 중첩 질의에서는 외부 질의를 만족하는 각 투플이 구해진 후에 중첩 질의가 수행되므로 상관 중첩 질의는 외부 질의를 만족하는 투플 수만큼 여러 번 수행될 수 있다. -> 성능 저하 야기

 

질의 : 자신이 속한 부서의 사원들의 평균 급여보다 많은 급여를 받는 사원들에 대해서 이름, 부서번호, 급여를 검색하라.

- 외부 질의에서 첫 번째 투플에 해당하는 사원이 속한 부서(E.DNO)의 평균 급여와 그 사원의 급여를 비교(SALARY > AVG)한다.

- 이후 외부 질의에 선언된 릴레이션의 다른 투플에 대해서도 처음과 같은 방식으로 비교 결과가 참인지 거짓인지 반환한다.

- 총 투플 수만큼 중첩 질의가 실행된다.

- 마지막으로 만족하는 투플들의 집합(=결과 릴레이션)을 외부 질의가 반환한다.

 

  INSERT 문

• 기존의 릴레이션에 투플을 삽입하는 데이터 조작어
• 참조되는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건의 위배가 발생하지 않으나 참조하는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건을 위배할 수 있다.
• 릴레이션에 한 번에 한 투플씩 삽입하는 것과 한 번에 여러 개의 투플을 삽입할 수 있는 것으로 구분한다.
• 구문

INSERT INTO [tablename](애트리뷰트1, ..., 애트리뷰트n) VALUES (값1, ..., 값n);

• (애트리뷰트)와 (값)의 순서는 동일해야 한다.
• 애트리뷰트를 제시하지 않을 경우, 릴레이션 스키마에 명시된 애트리뷰트의 순서에 맞추어 값을 삽입해야 한다.

INSERT INTO [tablename] VALUES (값1, ..., 값n);

• 릴레이션에 한 번에 한 투플씩 삽입하는 INSERT문

질의 : DEPARTMENT 릴레이션에 (5, 연구, NULL) 투플을 삽입하시오.

형광색 펜이 삐뚤삐뚤하네요.. 죄송합니다...  : (

 

• 릴레이션에 한 번에 여러 개의 투플을 삽입하는 INSERT문

질의 : EMPLOYEE 릴레이션에서 급여가 3000000원 이상인 사원들의 이름, 직급, 급여를 검색하여

HIGH_SALARY 릴레이션에 삽입하라.

위의 질의를 위한 문장 구조는 아래와 같다.

 

  DELETE 문

• 한 릴레이션으로부터 한 개 이상의 투플들을 삭제하는 데이터 조작어
• 참조되는 릴레이션의 삭제 연산의 결과로 참조 무결성 제약조건이 위배될 수 있으나, 참조하는 릴레이션에서 투플을 삭제하면 참조 무결성 제약조건을 위배하지 않는다. -> INSERT문과 반대
• 구문

DELETE FROM [tablename] WHERE [조건];

• 전체 투플 삭제 : DELETE FROM [tablename];

 

질의 : DEPARTMENT 릴레이션에서 4번 부서를 삭제하라.

  

  UPDATE 문

• 한 릴레이션에 들어 있는 투플들의 애트리뷰트 값들을 수정하는 데이터 조작어
• 기본키나 외래키에 속하는 애트리뷰트의 값이 수정되면 참조 무결성 제약조건을 위배할 수 있다.
• 구문

 

질의 : 사원 번호가 2106인 사원의 소속 부서를 3번 부서로 옮기고, 급여를 10% 올리시오.

[ 질의 실행 전 EMPLOYEE ]

[ 질의 실행 후 EMPLOYEE ]

 

  트리거(Trigger)

• 명시된 이벤트(데이터베이스의 갱신)가 발생할 때마다 DBMS가 자동적으로 수행하는 쿼리문 또는 절차
• 데이터베이스의 무결성을 유지하기 위한 일반적이고 강력한 도구
• 트리거를 명시하려면 트리거를 활성화 시키는 사건인 이벤트(삽입, 삭제 등)와 트리거가 활성화되었을 때 수행되는 테스트인 조건, 트리거가 활성화되고 조건이 참일 때 수행되는 쿼리문(절차)인 동작을 표현해야 한다.
• 트리거를 이벤트-조건-동작(ECA) 규칙이라고도 부른다. - Event, Condition, Action
• 구문

• MSSQL에서 트리거를 정의하는 문장은 SQL3의 트리거 문장과 동일하지는 않다.
• 이벤트의 가능한 예로는 테이블에 투플 삽입, 삭제, 수정 등이 있다.
• 조건은 임의의 형태의 프레디키트
• 동작은 데이터베이스에 대한 임의의 갱신(INSERT, DELETE, UPDATE 쿼리문)
• 어떤 이벤트가 발생했을 때 조건이 참이 되면 트리거와 연관된 동작이 수행되고, 거짓이면 아무 동작도 수행되지 않는다.
• 삽입, 삭제, 수정 등이 일어나기 전에 동작하는 트리거와 일어난 후에 동작하는 트리거로 구분한다.

질의 : 새로운 사원이 입사할 대마다 사원의 급여가 1500000 미만인 경우에는 급여를 10% 인상하는 트리거를 작성하시오.

 이벤트 동작 조건

 

• 트리거는 연쇄적으로 활성화될 수 있다. 그 과정은, 하나의 트리거가 활성화되어 이 트리거 내의 한 SQL문이 수행되고 그 결과로 다른 트리거를 활성화하여 그 트리거 내의 SQL문이 수행되는 것이다.