[실기핵심요약] - SQL 응용

SQL 특징 

1. 관계대수와 관계해석을 기초로 한 고급 데이터 언어

2. 이해하기 쉬운 형태

3. 대화식 질의어로 사용 가능

4. 데이터 정의, 조작, 제어 기능 제공

5. COBOL, C, PASCAL 등의 언어에 삽입 -> 내장 SQL
6. DBMS에서 사용되는 비절차적 대화형 언어

 

테이블 생성

CREATE TABLE 직원 (
이름 CHAR(15),
주민번호 CHAR(13) NOT NULL,
직원번호 CHAR(15),
PRIMARY KEY(직원번호),
UNIQUE (주민번호)
);

CREATE TABLE 학생 (
학번 CHAR(15),
이름 CHAR(15) NOT NULL,
학과코드 CHAR(10),
성별 SUNG,
생년월일 DATE,
PRIMARY KEY(학번),
UNIQUE(이름),
FOREIGN KEY(학과코드) REFERENCES 학과(학과코드),
CHECK (생년월일>='1970-01-01')
);

칼럼 검색  (2020년 3회 실기 기출)

SELECT 과목이름, MIN(점수) AS 최소점수, MAX(점수) AS 최대점수 
FROM 성적
GROUP BY 과목이름 AVG(점수) >= 90;

 

컬럼(속성) 변경/수정

- 칼럼(속성) 추가, 기본값 변경, 칼럼(속성) 제거

ALTER TABLE 학과 ADD 연락처 CHAR(18);

ALTER TABLE 학과 ALTER 학과명 SET DEFAULT '정보';

ALTER TABLE 학과 DROP 학과명;

 

자료(튜플) 입력

INSERT INTO 학과 VALUES('A001', '정보');

[직원]테이블에서 직급이 사원인 이름, 나이, 주소, 월급을 SELECT해서 [사원]테이블 속성인 이름, 나이, 주소, 월급에 추가하시오.

INSERT INTO 사원(이름, 나이, 주소, 월급)
SELECT 이름, 나이, 주소, 월급 
FROM 직원
WHERE 직급='사원';

자료(튜플) 수정

UPDATE 학과 SET 학과명='사무' WHERE 학과코드='A001';

자료(튜플) 삭제

DELETE FROM 학과;

 

테이블 삭제

DROP TABLE 학과;

 

뷰 생성

CREATE VIEW 여학생_view(이름,성별) AS
SELECT 이름, 성별
FROM 학생
WHERE 성별='여'
WITH CHECK OPTION;

인덱스 생성 20년 2회 실기 기출

중복허용X, 이름 기준으로 오름차순

CREATE UNIQUE INDEX 이름_idx ON 학생(이름 ASC);

GROUP BY, HAVING

 

[직원]테이블에서 월급이 200이상이고, 직급별 직원이 2명 이상인 직급을 검색하시오.

SELECT 직급 FROM 직원 WHERE 월급>=200 
GROUP BY 직급 HAVING COUNT(*)>=2;

 

* LIKE '윤%' : '윤'으로 시작하는 모든 튜플

* LIKE '윤__' : '윤'으로 시작하는 세 글자

 

---

절차형 SQL 특징

- 제어가 가능한 SQL

1. BEGIN/END의 Block화 된 구조 -> 모듈화 가능

2. 단일 SQL 문장으로 실행하기 어려운 연속적인 작업 처리하는 데에 적합

구성)

DECLARE : 대상이 되는 프로시저, 사용자 정의 함수 등을 정의

BEGIN : 프로시저, 사용자 정의함수가 실행되는 시작점

END : 프로시저, 사용자 정의함수가 실행되는 종료점

 

제어문

DECLARE 
    점수 INT := 80;
BEGIN
    IF 점수>=80 THEN
    	DBMS_OUTPUT.PUT_LINE('A학점');
    ELSIF 점수>=60 THEN
    	DBMS_OUTPUT.PUT_LINE('B학점');
    ELSE
    	DBMS_OUTPUT.PUT_LINE('불합격');
    END IF;
END;

반복문

-LOOP문 (1에서 5까지 합계)

DECLARE
    i INT:=0;
    합계 INT:=0;
BEGIN 
    LOOP
        i:=i+1;
    	합계:=합계+i;
        EXIT WHEN i>=5;
    END LOOP;
END;

- FOR 문 (1에서 5까지 합계)

DECLARE
    합계 INT:=0;
BEGIN 
    FOR i IN 1..5
    LOOP
    	합계:=합계+i;
    END LOOP;
END;

- WHILE 문 (1에서 5까지 합계)

DECLARE
    i INT:=0;
    합계 INT:=0;
BEGIN 
    WHILE i<5
    LOOP
        i:=i+1;
    	합계:=합계+i;
    END LOOP;
END;

- CONTINUE 문 (3,4,5인 경우에만 A출력)

DECLARE
BEGIN 
    FOR i IN 1..5
    WHILE i<5
    LOOP
        CONTINUE WHEN i<=2;
        DBMS_OUTPUT.PUT_LINE('A');
    END LOOP;
END;

- GOTO 문 (반복문 수행 중에 GOTO문이 수행되어 'gisafirst' 출력)

DECLARE
BEGIN 
    FOR i IN 1..5
    LOOP
        GOTO gisafirst;
        DBMS_OUTPUT.PUT_LINE('A');
    END LOOP;
    <<gisafirst>>
    DBMS_OUTPUT.PUT_LINE('gisafirst');
END;

---

커서(CURSOR) (PL/SQL문법)

- 쿼리문의 처리 결과가 저장되어 있는 메모리 공간을 가리키는 포인터

커서 처리 순서)

커서 선언(CURSOR 커서명)->커서 열기(OPEN 커서명)->커서에서 데이터 가져오기(FETCH)->커서닫기(CLOSE 커서명)

종류) 묵시적 커서, 명시적 커서

묵시적 커서 - 선언 없이 사용, DBMS 자체적으로 OPEN, FETCH, CLOSE 됨

명시적 커서 - 사용자가 직접 정의해서 사용, 직접 구현해야함

 -1. 커서 선언

CURSOR 커서명 IS
SELECT문;

 -2. 커서 열기

OPEN 커서명(매개변수);

 -3. 패치

FETCH 커서명 INTO 변수;
- 데이터 가져오기 & 변수에 저장 & 다음 튜플로 이동

 -4. 커서 닫기

CLOSE 커서명;

---

프로시저 

: 절차형 SQL을 활용하여 특정 기능을 수행할 수 있는 트랜잭션 언어

- 프로시저 호출을 통해 실행되며, 이를 통해 일련의 SQL작업을 포함하는 데이터 조작어 DML을 수행하는 것이 일반적

- 시스템에서의 일일 마감 작업, 일련의 배치 작업 등을 프로시저를 활용해 관리하고 주기적으로 수행함.

DECLARE : 프로시저 명칭, 변수와 인수, 그에 대한 데이터 타입 정의하는 선언부

BEGIN : 프로시저의 시작

CONTROL : 기본적으로는 순차 러리, 특정 조건에 따라 문장 실행, 반복 수행 가능

SQL : DML, DCL이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업 수행

EXCEPTION : SQL문이 실행될 때 예외 발생 시, 예외 처리 방법 정의

TRANSACTION : 프로시저에서 수행된 DML 수행 내역의 DBMS의 적용 또는 취소 여부 결정하는 처리부

- 일반 SQL과 동일하게 최종 COMMIT/ROLLBACK 시점 이후 실행된 DML의 적용/취소 수행

END : 프로시저의 끝

CREATE [OR REPLACE] PROCEDURE 프로시저명(PARAMETER [MODE] DATA_TYPE) [IS|AS] 변수선언
BEGIN
    프로시저 BODY;
END;

프로시저 호출문 작성

-EXECUTE, EXEC, CALL 명령어 사용

EXECUTE 프로시저명 (PARAMETER_1, PARAMETER_2,...);

프로시저 제거 : DROP PROCEDURE 프로시저명;

---

사용자 정의 함수 

: 기본적인 개념 및 사용법, 문법 등은 프로시저와 동일하며, 종료 시 단일값을 반환한다는 것이 프로시저와의 차이점

- 주로 SELECT문에 포함되어 실행

- RETURN을 통해 값을 반환하므로 출력 파라미터(OUT) 없음

CREATE [OR REPLACE] FUNCTION 사용자정의함수명(PARAMETER1 [MODE] DATA_TYPE)
    RETURN 데이터형식
[IS|AS]
    변수 선언
BEGIN
    사용자정의함수 BODY;
    RETURN 반환값;
END;

사용자 정의 함수 호출

SELECT 사용자정의함수명 FROM 테이블명;

UPDATE 테이블명 SET 속성명=사용자정의함수명;

INSERT INTO 테이블명(속성명) VALUES(사용자정의함수명);

DELETE FROM 테이블명 WHERE 속성명 = 사용자정의함수명;

---

트리거(TRIGGER)

: 특정 테이블에 삽입, 수정, 삭제 등의 데이터 변경 이벤트가 발생하면 DBMS에서 자동으로 실행되도록 구현된 프로그램

- 데이터 무결성 유지 및 로그 메시지 출력 등의 별도 처리를 위해 트리거를 사용하기도 함

- 반환이 없다, DML을 주된 목적으로 한다는 점에서 프로시저와 유사, EVENT 명령어를 통해 트리거를 실행을 위한 이벤트를 인지한다는 점, 외부 변수 IN, OUT이 없다는 점이 차이점

CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명
REPERENCING [NEW|OLD] AS 테이블명
FOR EACH ROW
[WHEN 조건식]
BEGIN
    트리거 BODY;
END;

동작시기 옵션 : AFTER, BEFORE

- AFTER : 테이블 변경 후 실행

- BEFORE : 테이블 변경 전 실행

동작 옵션 : INSERT, UPDATE, DELETE

NEW | OLD : 트리거가 적용될 테이블의 별칭 

- NEW : 추가되거나 수정에 참여할 튜플들의 집합(테이블)

- OLD : 수정되거나 삭제 전 대상이 되는 튜플들의 집합(테이블)

FOR EACH ROW : 각 튜플마다 트리거를 적용한다는 의미

WHEN 조건식 : 트리거를 적용할 튜플의 조건

---

집계성 SQL 작성

데이터 분석 함수 

: 총합, 평균 등의 데이터 분석을 위해서는 복수행 기준의 데이터를 모아서 처리하는 것이 필수적

- 단일행 기반으로 산출하지 않고 복수행을 그룹별로 모아 놓고 그룹당 단일 계산 결과를 반환

- GROUP BY 구문을 활용하여 복수행을 그룹핑

- SELECT, HAVING, ORDER BY 등의 구문에 활용

종류) 집계함수, 그룹함수, 윈도우함수 

- 집계함수는 그룹함수의 한 부분이며, 통합하여 언급하기도 한다.

 

집계함수 

COUNT(입력값) - 튜플(ROW) 수 - *이나 상수인 경우 NULL이 포함된 행의 수/ 칼럼 적용 시 NULL 포함X

SUM(입력값) - 합계 - 상수 입력 시, 해당 ROW 수*상수의 값 / 칼럼 적용 시 해당 입력 값 내의 NULL이 아닌 값의 합계

MAX(입력값) - 최대값

MIN(입력값) - 최소값

AVG(입력값) - 평균

STDDEV(입력값) - 표준편차

VARIAN(입력값) - 분산

 

그룹함수 

1. ROLLUP : 지정된 컬럼은 소계 등 중간 집계값을 산출하기 위해 사용 -> 그룹별 소계

- GROUP BY [컬럼명] ROLLUP(컬럼명1, 컬럼명2,..) HAVING 조건식(집계함수 포함)

2. CUBE : 결합 가능한 모든 값에 대해 다차원 집계 생성하는 그룹 함수 -> 다차원 그룹별 소계

- GROUP BY [컬럼명] CUBE(컬럼명1, 컬럼명2,..) HAVING 조건식(집계함수 포함)

3. GROUPING SETS : 다양한 소계 집합, 집계 대상 컬럼들에 대한 개별 집계

- GROUP BY [컬럼명] GROUPING SETS(컬럼명1, 컬럼명2,..) HAVING 조건식(집계함수 포함)

 

윈도우(WINDOW) 함수

: 데이터베이스를 사용한 온라인 분석 처리 용도, OLAP 함수라고도 함

<순위함수>

1. RANK() : 동일 순위 레코드 존재 시 후순위는 넘어간다.

EX) 1, 2, 3, 3, 5

2. DENSE_RANK() : 동일 순위 레코드 존재 시에도 후순위를 넘어가지 않는다.

EX) 1, 2, 3, 3, 4

3. ROW_NUMBER() : 동일 순위 레코드 존재해도 이와 무관하게 연속번호 부여한다.

EX) 1, 2, 3, 4, 5

 

---

응용시스템 DBMS 접속

: DBMS에서 사용되는 SQL은 DB 관리 도구를 사용하여 직접 접속해 실행 가능, 응용시스템에서도 호출 방식을 통해 사용 가능, 사용자는 응용시스템을 통해 DBMS 접속

접속 기술)

1. 응용시스템 DBMS 접속 기술

-  JDBC, MyBatis

2 동적 SQL 입력 방법

3. 절차형 SQL 호출

 

사용자 그룹

: 다수의 사용자가 접속하는 사용자를 사용자 그룹이라고 한다. 사용자 그룹은 동일한 권한과 제약을 가지는 사용자들이 공통으로 사용하는 계정이다.

- 역할기반 접근 제어 그룹 관리(RBAC)를 기반으로 한다.