DML - SELECT
01 일반형식
SELECT [PREDICATE] [테이블명.] 속성명 [AS 별칭][, [테이블명.]속성명, ...]
[, 그룹함수(속성명) [AS 별칭]]
[, WINDOW 함수 OVER (PARTITION BY 속성명1, 속성명2, ...
ORDER BY 속성명3, 속성명4, ...)]
FROM 테이블명 [, 테이블명, ...]
[WHERE 조건]
[GROUP BY 속성명, 속성명, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC | DESC]];
- SELECT절
- PREDICATE: 검색할 튜플 수를 제한하는 명령어를 기술함
▶️ DISTINCT: 중복된 튜플이 있으면 그 중 첫 번째 한 개만 표시함
- 속성명: 검색하여 불러올 속성(열) 또는 속성을 이용한 수식을 지정함
- AS: 속성이나 연산의 이름을 다른 이름으로 표시하기 위해 사용함
- FROM절
- 검색할 데이터가 들어있는 테이블 이름을 기술함
- WHERE절
- 검색할 조건을 기술함
- ORDER BY
- 데이터를 정렬하여 검색할 때 사용함
- 속성명: 정렬의 기준이 되는 속성명을 기술함
- [ASC | DESC]: 정렬 방식으로, 'ASC'는 오름차순, 'DESC'는 내림차순이다. 디폴트는 ASC
- 그룹함수
- GROUP BY절에 지정된 그룹별로 속성의 값을 집계할 함수를 기술함
- WINDOW 함수
- GROUP BY절을 이용하지 않고 속성의 값을 집계할 함수를 기술함
- PARTITION BY: WINDOW 함수의 적용 범위가 될 속성을 지정함
- ORDER BY: PARTITION 안에서 정렬 기준으로 사용할 속성을 지정함
- GROUP BY절
- 특정 속성을 기준으로 그룹화하여 검색할 때 사용한다. 일반적으로 GROUP BY 절은 그룹 함수와 함께 사용됨
- HAVING절
- GROUP BY와 함께 사용되며, 그룹에 대한 조건을 지정함
02 조건 연산자
비교 연산자
| 연산자 | = | <> | > | < | >= | <= |
| 의미 | 같다 | 같지 않다 | 크다 | 작다 | 크거나 같다 | 작거나 같다 |
논리 연산자 : NOT, AND, OR
LIKE 연산자
대표 문자를 이용해 지정된 속성의 값이 문자 패턴과 일치하는 튜플을 검색하기 위해 사용된다.
| 대표 문자 | % | _ | # |
| 의미 | 모든 문자를 대표함 | 문자 하나를 대표함 | 숫자 하나를 대표함 |
03 기본 검색
SELECT 절에 원하는 속성을 지정하여 검색한다.
예제) <사원> 테이블에서 '주소'만 검색하되 같은 '주소'는 한 번만 출력하시오
SELECT DISTINCT 주소
FROM 사원;
04 조건 지정 검색
WHERE 절에 조건을 지정하여 조건에 만족하는 튜플만 검색한다.
예제) <사원> 테이블에서 성이 '김'인 사람의 튜플을 검색하시오
SELECT *
FROM 사월
WHERE 이름 LIKE "김%";
05 정렬 검색
ORDER BY 절에 특정 속성을 지정하여 지정된 속성으로 자료를 정렬하여 검색한다.
예제) <사원> 테이블에서 '부서'를 기준으로 오름차순 정렬하고, 같은 '부서'에 대해서는 '이름'을 기준으로 내림차순 정렬시켜서 정렬하시오
SELECT *
FROM 사원
ORDER BY 부서 ASC, 이름 DESC;
06 하위 질의
하위 질의는 조건절에 주어진 질의를 먼저 수행하여 그 검색 결과를 조건절의 피연산자로 사용한다.
예제) 취미활동을 하지 않는 사원들을 검색하시오
SELECT *
FROM 사원
WHERE 이름 NOT IN (SELECT 이름 FROM 여가활동);
예제) 취미활동을 하는 사원들의 부서를 검색하시오
SELECT 부서
FROM 사원
WHERE EXISTS (SELECT 이름 from 여가활동 WHERE 여가활동.이름 = 사원.이름);
07 복수 테이블 검색
여러 테이블을 대상으로 검색을 수행한다.
예제) '경력'이 10년 이상인 사원의 '이름', '부서', '취미', '경력'을 검색하시오
SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력
FROM 사원, 여가활동
WHERE 여가활동.경력 >= 10 AND 사원.이름 = 여가활동.이름;
08 그룹 함수
그룹 함수는 GROUP BY절에 지정된 그룹별로 속성의 값을 집계할 때 사용된다.
| 함수 | 기능 |
| COUNT(속성명) | 그룹별 튜플 수를 구하는 함수 |
| SUM(속성명) | 그룹별 합계를 구하는 함수 |
| AVG(속성명) | 그룹별 평균을 구하는 함수 |
| MAX(속성명) | 그룹별 최대값을 구하는 함수 |
| MIN(속성명) | 그룹별 최소값을 구하는 함수 |
| STDDEV(속성명) | 그룹별 표준편차를 구하는 함수 |
| VARIANCE(속성명) | 그룹별 분산을 구하는 함수 |
| ROLLUP(속성명, 속성명, ...) | - 인수로 주어진 속성을 대상으로 그룹별 소계를 구하는 함수 - 속성의 개수가 n개이면, n+1 레벨까지, 하위 레벨에서 상위 레벨 순으로 데이터가 집계됨 |
| CUBE(속성명, 속성명, ...) | - ROLLUP과 유사한 형태지만 CUBE는 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구함 - 속성의 개수가 n개이면, 2^n 레벨까지, 상위 레벨에서 하위 레벨 순으로 데이터가 집계됨 |
GROUP B절에 지정한 속성을 기준으로 자료를 그룹화하여 검색한다.
예제) <상여금> 테이블에서 '부서'별 '상여금'의 평균을 구하시오
SELECT 부서, AVG(상여금) AS 평균
FROM 상여금
GROUP BY 부서;
예제) <상여금> 테이블에서 부서별 튜플 수를 검색하시오
SELECT 부서, COUNT(*) AS 사원수
FROM 상여금
GROUP BY 부서;
예제) <상여금> 테이블에서 '상여금'이 100이상인 사원이 2명 이상인 '부서'의 튜플을 구하시오
SELECT 부서, COUNT(*) AS 사원수
FROM 상여금
WHERE 상여금 >= 100
GROUP BY 부서
HAVING COUNT(*) >= 2;
WINDOW 함수
WINDOWS 함수는 GROUP BY 절을 이용하지 않고 함수의 인수를 지정한 속성의 값을 집계한다.
함수의 인수로 지정한 속성인 집계할 범위가 되는데, 이를 윈도우(WINDOW)라고 부른다.
- ROW_NUMBER(): 윈도우별로 각 레코드에 대한 일련번호를 반환한다.
- RANK(): 윈도부별로 순위를 반환하며, 공동 순위를 반영한다.
- DENSE_RANK(): 윈도우별로 순위를 반환하며, 공동 순위를 무시하고 순위를 부여한다.
예제) <상여금> 테이블에서 '상여내역'별로 '상여금'에 대한 일련 번호를 구하시오 (단 순서는 내림차순이며 속성명은 'NO'로 할 것)
SELECT 상여내역, 상여금,
ROW_NUMBER() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO
FROM 상여금;
프로시저(Procedure)
프로시저는 SQL을 사용하여 작성한 일련의 작업을 저장해두고 호출을 통해 원할 때마다 저장한 작업을 수행하도록 하는 절차형 SQL이다.
데이터베이스에 저장되어 수행되기 때문에 스토어드(Stored) 프로시저라고도 불린다.
시스템의 일일 마감 작업, 일괄(Batch) 작업 등에 주로 사용된다.
01 프로시저의 구성도
- DECLARE: 프로시저의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부이다.
- BEGIN/END: 프로시저의 시작과 종료를 의미한다.
- CONTROL: 조건문 또는 반복문이 삽입되어 순차적으로 처리된다.
- SQL: DML, DCL이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업을 수행한다.
- EXCEPTION: BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의한다.
- TRANSACTION: 수행된 데이터 작업들을 DB에 적용할지 취소할지를 결정하는 처리부이다.
02 프로시저 생성
프로시저를 생성하기 위해서는 CREATE PROCEDURE 명령어를 사용한다.
CREATE [OR REPLACE] PROCEDURE 프로시저(파라미터)
[지역변수 선언]
BEGIN
프로시저 BODY;
END;- OR REPLACE: 선택적인(Optional) 예약어이다. 이 예약어를 사용하면 동일한 프로시저 이름이 이미 존재하는 경우, 기존의 프로시저를 대체할 수 있다.
- 프로시저명: 생성하려는 프로시저의 이름을 지정한다.
- 파라미터: 프로시저 파라미터로는 다음과 같은 것들이 올 수 있다.
IN: 호출 프로그램이 프로시저에게 값을 전달할 때 지정한다.
OUT: 프로시저가 호출 프로그램에게 값을 반환할 때 지정한다.
INOUT: 호출 프로그램이 프로시저에게 값을 전달하고, 프로시저 실행후 호출 프로그램에 값을 반환할 때 지정한다.
매개변수명: 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정한다.
자료형: 변수의 자료형을 지정한다.
- 프로시저 BODY
- 프로시저의 코드를 기록하는 부분이다.
- BEGIN에서 시작하여 END로 끝나며, BEGIN과 END 사이에는 적어도 하나의 SQL문이 있어야 한다.
03 프로시저 실행
프로시저를 실행하기 위해서는 EXECUTE 명령어 또는 CALL 명령어를 사용하며, EXECUTE 명령어를 줄여서 EXEC로 사용하기로 한다.
표기 형식
EXECUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명;
예제) '사원번호' 32를 인수로 하여 위에서 생성된 emp_change_s 프로시저를 실행하시오
EXECUTE emp_change_s(32);
04 프로시저 제거
프로시저를 제거하기 위해서는 DROP PROCEDURE 명령어를 사용한다.
표기형식
DROP PROCEDURE 프로시저명;
트리거(Trigger)
트리거는 데이터베이스 시스템에서 데이터의 삽입(Insert), 갱신(Update), 삭제(Delete) 등의 이벤트(Event)가 발생할 때 관련 작업이 자동으로 수행되게 하는 절차형 SQL이다.
트리거는 데이터베이스에 저장되며, 데이터 변경 및 무결성 유지, 로그 메시지 출력 등의 목적으로 사용된다.
트리거의 구문에는 DCL(데이터 제어어)를 사용할 수 없으며, DCL이 포함된 프로시저나 함수를 호출하는 경우에 오류가 발생한다.
01 트리거의 구성도
- DECLARE: 트리거의 명칭, 변수 및 상수, 데이터 타입을 정의하는 선언부이다.
- EVENT: 트리거가 실행되는 조건을 명시한다.
- BEGIN/END: 트리거의 시작과 종료를 의미한다.
- CONTROL: 조건문 또는 반복문이 삽입되어 순차적으로 처리된다.
- SQL: DML문이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업을 수행한다.
- EXCEPTION: BEGIN ~ END안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의한다.
02 트리거 생성
트리거를 생성하기 위해서는 CREATE TRIGGER 명령어를 사용한다.
표기형식
CREATE[OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명
REFERENCING [NEW | OLD] AS 테이블명
FOR EACH ROW
[WHEN 조건식]
BEGIN
트리거 BODY;
END;- OR REPLACE: 선택적인 (Optional) 예약어이다. 이 예약어를 사용하면 동일한 트리거 이름이 이미 존재하는 경우, 기존의 트리거를 대체할 수 있다.
- 동작시기 옵션: 트리거가 실행될 때를 지정한다
AFTER: 테이블이 변경된 후에 트리거가 실행된다.
BEFORE: 테이블이 변경되기 전에 트리거가 실행된다.
- 동작 옵션:트리거가 실행되게 할 작업의 종류를 지정한다.
INSERT: 테이블에 새로운 튜플을 삽입할 때 트리거가 실행된다.
DELETE: 테이블의 튜플을 삭제할 때 트리거가 실행된다.
UPDATE: 테이블의 튜플을 수정할 때 트리거가 실행된다.
- NEW | OLD: 트리거가 적용될 테이블의 별칭을 지정한다.
NEW: 추가되거나 수정에 참여할 튜플들의 집합(테이블)을 의미한다.
OLD: 수정되거나 삭제 전 대상이 되는 튜플들의 집합(테이블)을 의미한다.
- FOR EACH ROW: 각 튜플마다 트리거를 적용한다는 의미이다.
- WHEN 조건식: 선택적인 (Optional) 예약어이다. 트리거를 적용할 튜플의 조건을 지정한다.
- 트리거 BODY
트리거의 본문 코드를 입력하는 부분이다.
BEGIN으로 시작해서 END로 끝나는데, 적어도 하나 이상의 SQL문이 있어야 한다. 그렇지 않으면 오류가 발생한다.
03 트리거의 제거
트리거를 제거하기 위해서는 DROP TRIGGER 명령어를 사용한다.
표기형식
DROP TRIGGER 트리거명;
'정보처리기사' 카테고리의 다른 글
| 정보처리기사 06. 통합 구현 (0) | 2021.09.21 |
|---|---|
| 정보처리기사 05. 데이터 입출력 구현 (0) | 2021.09.21 |
| 정보처리기사 04. SQL 응용-3 (제어문, 커서, DBMS 접속, SQL 테스트, ORM) (0) | 2021.09.21 |
| 정보처리기사 02. SQL 응용-1 (DDL, DCL, DML) (0) | 2021.09.18 |
| 정보처리기사 01. UML 다이어그램 (0) | 2021.09.16 |
