[정처기 실기] 8. SQL 응용

(1) SQL - DDL

---

DDL(Data Define Language) - 데이터 정의어

- DB 구축 및 수정
- 데이터 사전이라는 파일에 여러 테이블로 저장
🔍명령어 종류
- CREATE : SCHEMA, DOMAIN, TABLE, VIEW, INDEX 정의
- ALTER : TABLE에 대한 정의 변경
- DROP : SCHEMA, DOMAIN, TABLE, VIEW, INDEX 삭제

CREATE SCHEMA

- 스키마 정의

❓스키마란?
: DB 구조와 제약 조건에 관한 전반적인 명세를 기술한 것

예 ) 소유권자의 사용자 ID가 ‘홍길동’인 스키마 ‘대학교’를 정의하는 SQL문

CREATE SCHEMA 대학교 AUTHORIZATION 홍길동

CREATE DOMAIN

- 도메인을 정의

❓도메인이란?
: 하나의 속성이 취할 수 있는 동일한 유형의 원자값들의 집합

CREAE DOMAIN SEX CHAR(1)
	DEFAULT '여‘
	CONSTRAINT VALID-SEX CHECK(VALUE IN('남’,‘여’));

CREATE TABLE

- 테이블 정의

❓테이블이란?
: 설계 = 릴레이션(Relation)
: 조작/검색 = 테이블(Table)

예 ) ‘이름’, ‘학번’, ‘전공’, ‘성별’, ‘생년월일’로 구성된 <학생> 테이블을 정의하는 SQL문
*제약조건
‘이름’은 NULL이 올 수 없고, ‘학번’이 기본키
‘전공’은 <학과> 테이블의 ‘학과코드’를 참조하는 외래키로 사용
<학과> 테이블에서 삭제가 일어나면 관련된 튜플들의 전공 값을 NULL
<학과> 테이블에서 ‘학과코드’가 변경되면 전공 값도 같은 값으로 변경
‘생년월일’은 1980-01-01 이후의 데이터만 저장 가능
제약 조건의 이름은 ‘생년월일제약’으로 한다.
각 속성의 데이터 타입은 적당하게 지정한다.(단, ‘성별’ 도메인은 ’SEX')

CREATE TABLE 학생
	(이름 VARCHAR(15) NOT NULL,
	 학번 CHAR(8),
	 전공 CHAR(5),
	 성별 SEX,
	 생년월일 DATE,
	 PRIMARY KEY(학번),
	 FOREIGN KEY(전공) REFERENCES 학과(학과코드)
		ON DELETE SET NULL,
		ON UPDATE CASCADE,
	 CONSTRAINT 생년월일제약
		CHECK(생년월일)>=‘1980-01-01’));

CREATE VIEW

- 뷰 정의

❓뷰란?
: 가상 테이블로 실제로 데이터가 물리적으로 구현되지 않음

예 ) <고객> 테이블에서 ‘주소’가 ‘안산시’인 고객들의 ‘성명’과 ‘전화번호’를 ‘안산고객’이라는 뷰로 정의

CREATE VIEW 안산고객(성명, 전화번호)
AS SELECT 성명, 전화번호
FROM 고객
WHERE 주소=‘안산시’;

CREATE INDEX

- 인덱스 정의

❓인덱스란?
: 검색시간을 단축하기 위한 보조적인 데이터 구조

예 ) <학생> 테이블에서 UNIQUE한 특성을 갖는 ‘학번’속성에 대해 내림차순으로 정렬하여 ‘학번_idx'라는 이름으로 인덱스를 정의

CREATE UNIQUE INDEX 학번_idx
ON 학생(학번 DESC);

• UNIQUE : 중복 값이 없도록 인덱스 생성
• 정렬  : ASC(오름차순) / DESC(내림차순)
• CLUSTER : 인덱스 키의 순서에 따라 데이터가 정렬되어 저장 => 원하는 데이터를 빠르게 검색 가능

ALTER TABLE

- 테이블에 대한 정의 변경
예) <학생> 테이블에 최대 3문자로 구성되는 ‘이름’ 속성을 추가

ALTER TABLE 학생 ADD 이름 VARCHAR(3);

DROP

- 스키마, 도메인, 테이블, 뷰, 인덱스, 계약조건 등을 제거
예) <학생> 테이블을 제거하되, <학생> 테이블을 참조하는 모든 데이터를 함께 제거

DROP TABLE 학생 CASCADE;

 

(2) SQL - DCL

---

DCL (Data Control Language) - 데이터 제어어

- 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는데 사용
- 데이터베이스 관리자(DBA)가 데이터 관리 목적으로 사용
🔍 명령어 종류
- COMMIT : DB 조작 작업이 정상적으로 완료되었음을 관리자에게 알려줌
- ROLLBACK : DB 조작 작업이 비상적으로 종료되었을 때, 원래 상태로 복구
- GRANT : DB 사용자에게 사용 권한 부여
- REVOKE : DB 사용자에게 사용 권한 취소

예) 사용자 ID가 ‘890101’ 인 사람에게 DB 및 테이블을 생성할 수 있는 권한을 부여하는 SQL문

GRANT RESOURCE TO '890101';

예) 사용자 ID가 ‘890101’인 사람에게 단순히 정보를 검색할 수 있는 권한을 부여하는 SQL문

GRANT CONNECT TO '890101';

예) 사용자 ID가 ‘890101’인 사람에게 <고객> 테이블에 대한 모든 권한과 다른 사람에게 권한을 부여할 수 있는 권한까지 부여하는 SQL문

GRNAT ALL ON 고객 TO '890101' WITH GRANT OPTION;

예) 사용자 ID가 ‘890101’인 사람에게 부여한 <고객> 테이블에 대한 권한 중, UPDATE 권한을 다른 사람에게 부여할 수 있는 권한만 취소하는 SQL문

REVOKE GRANT OPTION FOR UPDATE ON 고객 FROM '890101';

COMMIT

- 트랜잭션이 수행한 내용을 DB에 반영하는 명령
- 명령을 실행하지 않아도 DML문이 성공적으로 완료되면 자동으로 COMMIT 되고, DML이 실패하면 자동으로 ROLLBACK이 되도록 Auto Commit 기능을 설정 가능

DELETE FROM 학생 WHERE 나이=20;
COMMIT;

ROLLBACK

- COMMIT 되지 않은 모든 내용들을 취소하고 DB를 이전 상태로 되돌리는 명령어

SAVEPOINT

- ROLLBACK 할 위치인 저장점을 지정하는 명령어

예 ) SAVEPOINT 'S1' 을 설정하고, 나이가 20인 학생의 정보를 삭제하시오

SAVEPOINT S1;
DELETE FROM 학생 WHERE 나이 = 20;

예 ) SAVEPOINT 'S2'를 설정하고, 나이가 21인 사원의 정보를 삭제하시오

SAVEPOINT S2;
DELETE FROM 학생 WHERE 나이 = 21;

예 ) SAVEPOINT 'S2'까지 ROLLBACK을 수행하시오

ROLLBACK TO S2;

TCL(Transaction Control Language)

- COMMIT, ROLLBACK, SAVEPOINT

(3) SQL - DML

---

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

- DB 사용자가 저장된 데이터를 실질적으로 관리하는데 사용되는 언어
- 사용자와 DB 관리 시스템 간의 인터페이스 제공
🔍 명령어 종류
- SELECT : 테이블에서 튜플 검색
- INSERT : 테이블에서 새로운 튜플 삽입
- DELETE : 테이블에서 튜플 삭제
- UPDATE : 테이블에서 튜플의 내용을 갱신

삽입문(INSERT INTO~)

- 새로운 튜플을 삽입할 때 사용
- 대응하는 속성과 데이터는 개수와 데이터 유형이 일치해야 한다.
예) <사원> 테이블에 (이름 - 홍승현, 부서 - 인터넷)을 삽입하시오

INSERT INTO 사원(이름, 부서) VALUES('홍승현‘,’인터넷‘);

예) <사원> 테이블에 (‘장보고’, ‘기획’, #05/03/73#, ’홍제동‘, 90)을 삽입하시오

INSERT INTO 사원 VALUES('장보고‘, ’기획‘, #05/03/73#, ’홍제동‘, 90);

예) <사원> 테이블에 있는 편집부의 모든 튜플을 편집부원(이름, 생일, 주소, 기본급) 테이블에 삽입하시오

INSERT INTO 편집부원(이름, 생일, 주소, 기본급)
SELECT 이름, 생일, 주소, 기본급
FROM 사원
WHERE 부서 = ’편집‘;

 

삭제문(DELETE FROM~)

- 특정 튜플(행)을 삭제할 때 사용
- 모든 레코드를 삭제 시, WHERE 절 생략
- DELETE문은 테이블 구조나 테이블 자체는 남겨두고 튜플들만 삭제
   => 완전히 제거하기 위해서는 DROP문 사용

예) <학생> 테이블에서 ‘홍길동‘에 대한 튜플을 삭제하시오

DELETE
FROM 학생
WHERE 이름 = ‘홍길동’;

갱신문(UPDATE ~ SET ~)

- 특정 튜플(행)의 내용을 변경
예) <학생> 테이블에서 ‘홍길동’의 ‘학번’을 ‘202301’으로 수정하시오.

UPDATE 학생
SET 학번 = ‘202301’
WHERE 이름 = ‘홍길동’;

예) <학생> 테이블에서 ‘홍길동’의 ‘학과’를 ‘경영학과’로 변경하고, ‘학점’을 AO로 변경하시오.

UPDATE 학생
SET 학과 = ‘경영학과’, 학점=‘AO'
WHERE 이름 = ’홍길동‘;

 

(4) DML - SELECT-1

---

SELECT [PREDICATE][테이블명.]속성명[AS 별칭][,[테이블명.]속성명,...]
[, 그룹함수(속성명)[AS 별칭]]
	[,WINDOW 함수 OVER(PARTITION BY 속성명1, 속성명2,...
			OREDER BY 속성명3, 속성명4,...)]
	FROM 테이블명[,테이블명,...]
	[WHERE 조건]
	[GROUP BY 속성명, 속성명,...]
	[HAVING 조건]
	[ORDER BY 속성명 [ASC|DESC]];

• PREDICATE : 검색할 튜플 수를 제한하는 명령어 기술
• DISTINCT : 중복된 튜플 존재 시, 첫 번째 하나만 표시
• 속성명 : 검색하여 불러올 속성(열) 또는 속성을 이용한 수식 지정
• AS : 다른 이름으로 표시

조건 연산자

1. 비교
= <> > < >= <=
2. 논리
NOT, AND, OR
3. LIKE

• % : 모든 문자
• _ : 문자 하나
• # : 숫자 하나

기본 검색

- SELECT 절에 원하는 속성을 지정하여 검색

예 ) <사원> 테이블의 모든 튜플을 검색하시오

SELECT * FROM 사원;
SELECT 사원.* FROM 사원;
SELECT 이름, 부서, 주소, 급여 FROM 사원;
SELECT 사원.이름, 사원.부서, 사원.주소, 사원.급여 FROM 사원;

예 ) <사원> 테이블에서 ‘주소’만 검색하고 같은 ‘주소’는 한 번만 출력

SELECT DISTINCT 주소
FROM 사원;

조건 지정 검색

- WHERE 절에 조건을 지정하여 조건에 만족하는 튜플만 검색

예 ) <사원> 테이블에서 ‘기획’ 부서의 모든 튜플 검색

SELECT *
FROM 사원
WHERE 부서 = ‘기획’;

예 ) <사원> 테이블에서 ‘기획’부서에 근무하면서 ‘서초동‘에 사는 사람의 튜플 검색

SELECT *
FROM 사원
WHERE 부서 = ‘기획’ AND 주소 = ‘서초동’;

예 ) <사원> 테이블에서 ‘부서’가 ‘기획’이 이거나 ‘인터넷’인 튜플 검색

SELECT *
FROM 사원
WHERE 부서 = ‘기획’ OR 부서 = ‘인터넷’;

예 ) <사원> 테이블에서 성이 ’김‘인 사람의 튜플 검색

SELECT *
FROM 사원
WHERE 이름 LIKE '김%‘;

예 ) <사원> 테이블에서 ‘급여’가 200에서 300 사이인 튜플 검색

SELECT *
FROM 사원
WHERE 급여 BETWEEN #200# AND #300#;

예 ) <사원> 테이블에서 ‘주소’가 NULL인 튜플 검색

SELECT *
FROM 사원
WHERE 주소 IS NULL;

 

정렬 검색

- ORDER BY 절에 특정 속성을 지정하여 지정된 속성으로 자료를 정렬하여 검색

예 ) <사원> 테이블에서 ‘주소’를 기준으로 내림차순 정렬시켜 상위 2개 튜플만 검색

SELECT TOP 2 *
FROM 사원
ORDER BY 주소 DESC;

예 ) <사원> 테이블에서 ‘부서’를 기준으로 오름차순 정렬하고, 같은 ‘부서’에 대해서는 ‘이름’을 기준으로 내림차순 정렬시켜 검색

SELECT *
FROM 사원
ORDER BY 부서 ASC, 이름 DESC;

하위 질의

- 검색 결과를 조건절의 피연산자로 사용

예 ) ‘취미’가 ‘나이트댄스’인 사원의 ‘이름’과 ‘주소’를 검색

SELECT 이름, 주소
FROM 사원
WHERE 이름 = (SELECT 이름 FROM 여가활동 WHERE 취미 = ‘나이트댄스’);

예 ) 취미 활동이 없는 사원들을 검색

SELECT *
FROM 사원
WHERE 이름 NOT IN (SELECT 이름 FROM 여가활동);

예 ) 취미 활동을 하는 사원들의 부서 검색

SELECT 부서
FROM 사원
WHERE EXISTS (SELECT 이름 FROM 여가활동 WHERE 여가활동.이름 = 사원.이름);

복수 테이블 검색

예 ) ‘경력’이 10년 이상인 사원의 ‘이름’, ‘부서’, ‘취미’, ‘경력’을 검색

SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력
FROM 사원, 여가활동
WHERE 여가활동.경력 >= 10 AND 사원.이름 = 여가활동.이름;

 

(5) DML - SELECT-2

---

그룹 함수

- COUNT : 튜플 수를 구하는 함수
- SUM : 그룹별 합계
- AVG : 평균
- MAX : 최대값
- MIN : 최솟값
- STDDEV : 표준편차
- VARIANCE : 분산
- ROLLUP : 그룹별 소계 (하위 ~상위레벨 순)
- CUBE : 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구함 (상위 ~ 하위레벨 순)

WINDOW 함수

- ROW_NUMBER() : 윈도우별로 각 레코드에 대한 일련번호를 반환
- RANK() : 윈도우별로 순위를 반환하며, 공동순위를 반영
- DENSE_RANK() : 윈도우별로 순위를 반환하며 공동순위 무시

(6) DML - JOIN

---

JOIN

- 2개의 릴레이션에서 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션을 반환
- INNER JOIN
- OUTER JOIN

INNER JOIN
- 일반적으로 EQUI JOIN과 NON-EQUI JOIN으로 구분
- 조건이 없는 INNER JOIN을 수행하면 CROSS JOIN과 동일한 결과를 얻음
- CROSS JOIN(교차 조인)
- 두 테이블의 행 수를 곱한 것과 같다.
- EQUI JOIN

(7) 프로시저(Procedure)

---

- SQL을 사용하여 작성한 일련의 작업을 저장해두고 호출을 통해 원할 때마다 저장한 작업을 수행하도록 하는 절차형 SQL
- DB에 저장되어 수행되므로 stored procedure라고도 불림
- 시스템의 일일 마감 작업, 일괄 작업 등에 주로 사용

프로시저 구성도

• DECLARE : 프로시저 명칭, 변수, 인수, 데이터 타입 정의 선언부
• BEGIN / END : 프로시저의 시작 / 종료
• CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리
• SQL : DML, DCL이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 수행
• EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의
• TRANSACTION : 수행된 데이터 작업들을 DB에 적용할지 취소할지 결정

프로시저 생성

예 ) ‘사원번호’을 입력받아 해당 사원의 ‘지급방식’을 ‘S’로 변경하는 프로시저를 생성하시오

CREATE OR REPLACE PROCEDURE emp_change_s(i_사원번호 IN INT)
IS
BEGIN
	UPDATE 급여 SET 지급방식 = 'S'WHERE 사원번호 = i_사원번호;
	EXCEPTION
		WHEN PROGRAM_ERROR THEN
			ROLLBACK;
		COMMIT;
END;

프로시저 실행

예 ) ‘사원번호’ 32를 인수로 하여 위에서 생성된 emp_change_s 프로시저를 실행하시오

EXECUTE emp_change_s(32);

프로시저 제거

예 ) 위에서 생성된 프로시저 emp_change_s를 제거하시오

DROP PROCEDURE emp_change_s;

 

(8) 트리거(Trigger)

---

- DB 시스템에서 데이터의 삽입, 갱신, 삭제 등의 이벤트가 발생할 때 관련 작업이 자동으로 수행되게 하는 절차형 SQL
- 트리거 구문에는 DCL을 사용할 수 없음

트리거 구성도

• DECLARE : 트리거의 명칭, 변수 및 상수, 데이터 타입을 정의하는 선언부
• EVENT : 트리거가 실행되는 조건
• BEGIN / END : 트리거의 시작 / 종료
• CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리
• SQL : DML 문이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업 수행
• EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의

트리거의 생성

예 ) <학생> 테이블에 새로운 튜플이 삽이될 때, 삽입되는 튜플에 학년 정보가 누락됐으면 ‘학년’ 속성에 ‘신입생’을 저장하는 트리거를 ‘학년정보_tri’라는 이름으로 정의하시오.

CREATE TRIGGER 학년정보_tri BEFORE INSERT ON 학생
REFERENCING NEW AS new_table
FOR EACH ROW
WHEN (new_table 학년 IS NULL)
BEGIN
	:new_table.학년 := ‘신입생’; /*NEW 또는 OLD로 지정된 테이블 앞엔 콜론(:)이 들어감*/
END                          /*A := B -> A에 B를 저장하라는 의미*/

트리거의 제거
예) ‘학년정보_tri'라는 트리거를 제거하는 SQL문

DROP TRIGGER 학년정보_tri;

 

 

(9) 사용자 정의 함수

---

- 프로시저와 유사하게 일련의 작업을 연속적으로 처리하지만, 종료 시 처리 결과로 단일값만을 변환하는 절차형 SQL

- 예약어 RETURN을 통해 단일값을 반환하며, 출력 파라미터가 없다.

 

사용자 정의 함수 구성도

• DECLARE : 사용자 정의 함수의 명칭, 변수 및 상수, 데이터 타입을 정의하는 선언부
• BEGIN / END : 사용자 정의 함수의 시작 / 종료
• CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리
• SQL : SELECT 문이 삽입되어 데이터 조회 작업을 수행
• EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의
• RETURN : 호출 프로그램에 반환할 값이나 변수를 정의

 

사용자 정의 함수 생성

예 ) 'i_성별코드'를 입력 받아 1이면 '남자'를, 2면 '여자'를 반환하는 사용자 정의 함수를 'Get_S_성별'이라는 이름으로 정의하시오

CREATE FUNCTION Get_S_성별(i_성별코드 IN INT)
RETURN VARCHAR2
IS
BEGIN
	IF i_성별코드 = 1 THEN
    	RETURN '남자';
    ELSE
    	RETURN '여자';
    END IF;
END;

 

사용자 정의 함수 실행

- DML에서 속성명이나 값이 놓일 자리를 대체하여 사용

 

예 ) 다음의 <사원> 테이블을 출력하되, '성별코드'는 앞에서 사용자 정의 함수 'Get_S_성별'에 대한 값을 전달하여 반환받은 값으로 대체하여 출력하여라

SELECT 이름, Get_S_성별(성별코드) FROM 사원;

 

 

사용자 정의 함수 제거

- DROP FUNCTION 명령어를 사용

예제 ) 위에서 생성된 사용자 정의 함수 'Get_S_성별'을 제거하시오

DROP FUNCTION Get_S_성별;

 

 

(10) 제어문

---

- 절차형 SQL의 진행 순서를 변경하기 위해 사용하는 명령문

- IF, LOOP

 

IF 문

- 조건에 따라 실행할 문장을 달리하는 제어문

IF 조건 THEN
	실행할 문장1;
    실행할 문장2;
    
END IF;

예) x가 10보다 크면 화면에 'true'를 출력하기

DECLARE
	x INT := 20;
BEGIN
	IF x > 10 THEN
    	DBMS_OUTPUT.PUT_LINE('true');
    END IF;
END;

 

LOOP 문

- 조건에 따라 실행할 문장을 반복 수행하는 제어문

 

예 ) 1부터 10까지의 합을 구하는 절차형 SQL을 PL/SQL로 구현하시오

DECLARE
	i INT := 0;
    i_sum INT := 0;
BEGIN
	LOOP
    	i := i + 1;
        i_sum := i_sum + 1;
        EXIT WHEN i >= 10;
    END LOOP;
END;

 

 

(11) 커서(Cursor)

---

커서(Cursor)

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

- 커서의 수행은 열기(Open), 패치(Fetch), 닫기(Close) 세단계로 진행

 

묵시적 커서(Implicit Cursor)

- DBMS에 의해 내부에서 자동으로 생성되어 사용하는 커서

- 커서의 속성을 조회하여 사용된 쿼리 정보를 열람하는 것이 가능

- 수행된 쿼리문의 정상적인 수행 여부를 확인하기 위해 사용

- 종류

SQL%FOUND	패치(Fetch)된 튜플 수가 1개 이상이면 TRUE
SQL%NOTFOUND	패치(Fetch)된 튜플 수가 0개이면 TRUE
SQL%ROWCOUNT	패치(Fetch)된 튜플 수를 반환
SQL%ISOPEN	커서가 열린(open) 상태이면 TRUE 묵시적 커서는 자동으로 생성된 후 자동으로 닫히기 때문에 항상 FALSE

 

명시적 커서(Explicit Cursor)

- 사용자가 직접 정의해서 사용하는 커서

- 쿼리문의 결과를 저장하여 사용함으로써 동일한 쿼리가 반복 수행되어 db 자원이 낭비되는 것을 방지

- 커서는 기본적으로 '열기 - 패치 - 닫기' 순으로 이루어지며, 명시적 커서를 사용하기 위해서는 열기단계 전에 선언(Declare) 해야 한다.

 

선언(Declare)

- SELECT 문의 WHERE 절에 사용할 수 있으며, 생략이 가능하다.

- 커서는 SELECT 문의 실행 결과가 저장된 곳의 시작 위치를 가리킨다.

CURSOR 커서명(매개변수1, 매개변수2,...)
IS
SELECT 문;

 

열기(Open)

- 커서를 사용하기 전에 반드시 적는다.

OPEN 커서명(매개변수1, 매개변수2,...);

 

패치(Fetch)

FETCH 커서명 INTO 변수1, 변수2,...;

 

닫기(Close) 

- 사용된 커서는 메모리 해제를 위해 반드시 닫아야 한다.

CLOSE 커서명;

예 ) 다음은 <employee> 테이블로부터 id가 20보다 크거나 같은 튜플의 name을 출력하는 절차형 SQL을 PL/SQL로 구현한 것이다

DECLARE
	p_name employee.name%TYPE;
    CURSOR cur_name(ff INT)
    IS
    SELECT name FROM employee WHERE id >= ff;

BEGIN
	Open cur_name(20);
    LOOP
    	FETCH cur_name INTO p_name;
        EXIT WHEN cur_name%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(p_name);
    END LOOP;
    CLOSE cur_name;
END;

 

 

(12) DBMS 접속(Connection)

---

- 응용 시스템을 이용하여 DBMS에 접근하는 것

- 웹 응용 시스템은 웹 서버와 웹 애플리케이션 서버(WAS)로 구성

 

DBMS 접속 기술

- DBMS에 접근하기 위해 사용하는 API 또는 API의 사용을 편리하게 도와주는 프레임워크

JDBC (Java DataBase Connectivity)	- java 언어로 다양한 종류의 db에 접속할 때 사용하는 표준 api - 접속하려는 DBMS에 대한 드라이버 필요
ODBC (Open DataBase Connectivity)	- 개발 언어와 관계 없이 db에 접근하기 위한 표준 개방형 API - 1992년 9월 마이크로소프트에서 출시
MyBatis	- JDBC 코드를 단순화하여 사용할 수 있는 SQL Mapping 기반 오픈소스 접속 프레임 워크

 

동적 SQL(Dynamic SQL)

- 다양한 조건에 따라 SQL 구문을 동적으로 변경하여 처리할 수 있는 SQL 처리 방식

- 프리컴파일(고급언어 => 기계어로 번역하는 컴파일 전에 수행하는 작업) 시, 구문분석, 접근권한 확인 등을 할 수 없다.

- 정적 SQL에 비해 속도가 느리지만 상황에 따라 다양한 조건을 첨가하는 등 유연한 개발이 가능

 

(13) SQL 테스트

---

- SQL이 작성의도와 맞게 수행하는 지 검증하는 과정

- 단문 SQL은 SQL 코드를 직접 실행한 후 결과를 확인하는 것으로 간단히 테스트가 가능

- 절차형 SQL은 테스트 전에 생성을 통해 구문 오류(Syntax Error)나 참조 오류의 존재 여부를 확인

- 정상적으로 생성된 절차형 SQL은 디버깅을 통해 로직을 검증하고, 결과를 통해 최종적을 확인

 

단문 SQL 테스트

- DDL, DML, DCL이 포함된 SQL과 TCL을 테스트하는 것

- DDL이 작성된 개체는 DESCRIBE 명령어를 이용하여 속성, 자료형, 옵션 등을 확인

 

절차형 SQL 테스트

- 프로시저, 사용자 정의 함수, 트리거 등의 절차형 SQL은 디버깅을 통해 기능의 적합성 여부를 검증 및 실행을 통해 결과를 확인

- 경고 메세지가 상세히 출력되지 않으므로 SHOW 명령어를 통해 오류 내용을 확인

 

(14) ORM(Object-Relational Mapping)

---

- 객체 지향 프로그래밍의 객체(Object)와 관계형 데이터베이스의 데이터를 연결하는 기술

- 가상의 객체지향 데이터베이스를 만들어 프로그래밍 코드와 연결

- ORM으로 생성된 가상의 객체지향 db는 프로그래밍 코드 또는 db와 독립적 => 유지보수 및 재사용 용이

 

ORM 프레임워크

- ORM을 구현하기 위한 구조와 구현을 위해 필요한 여러 기능들을 제공하는 sw

- ORM 프레임워크 종류

JAVA	JPA, Hibernate, EclipseLink, DataNucleus, Ebean 등
C++	ODB, QxOrm 등
Python	Django, SQLAlchemy, Storm 등
.NET	NHibernate, DatabaseObjects, Dapper 등
PHP	Doctrine, Propel, RedBean 등

 

ORM의 한계

- 프레임워크가 자동으로 SQL을 작성하기 때문에 의도대로 SQL이 작성되었는지 확인

- 객체지향적인 사용을 고려하고 설계된 db가 아닌 경우, 프로젝트가 크고 복잡해질수록 ORM 기술을 적용하기 어렵다.

- 기존의 기업들은 ORM을 고려하지 않은 db를 사용하고 있기 때문에 ORM에 적립하게 변환하려면 많은 시간과 노력이 필요하다.

 

 

(15) 쿼리 성능 최적화

---

쿼리 성능 최적화

- 데이터 입/출력 애플리케이션의 성능 향상을 위해 SQL 코드를 최적화하는 것

- 쿼리 성능을 최적화하기 전에 성능 측정 도구인 APM을 사용하여 최적화할 쿼리 선정

- APM(Application Performance Management/Monitoring) : 접속자, 자원현황, 트랜잭션 수행 내역, 장애 진단 등 다양한 모니터링 기능을 제공하는 도구

 

옵티마이저(Optimizer)

- 작성된 SQL이 효율적으로 수행되도록 최적의 경로를 찾아주는 모듈

- RBO(Rule Based Optimizer) / CBO(Cost Based Optimizer)

  - RBO : DBA가 사전에 정의한 규칙에 의거하여 경로를 찾는 규칙 기반 옵티마이저

  - CBO : 각 DBMS 마다 고유의 알고리즘에 따라 산출되는 '비용'으로 최적의 경로를 찾는 비용 기반 옵티마이저

 

실행 계획

- DBMS의 옵티마이저가 수립한 SQL 코드의 실행 절차와 방법

- EXPLAIN 명령어를 통해 확인 가능

- 그래픽이나 텍스트로 표현

 

쿼리 성능 최적화 방법

- SQL문이 더 빠르고 효율적으로 작동하도록 SQL코드와 인덱스를 재구성하는 것을 의미한다.

SQL 코드 재구성	- WHERE 절 추가 - WHERE 절에 연산자 사용 제한 - IN을 EXISTS로 대체 - 힌트로 엑세스 경로 및 조인 순서 변경
인덱스 재구성	- 조회되는 속성과 조건을 고려 - 인덱스 추가 및 기존 인덱스의 열 순서 변경 - IOT(Index Organized Table) 구성 고려 - 불필요한 인덱스 제거