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

• DB를 구축하거나 수정할 목적으로 사용하는 언어

DDL의 3가지 유형

• CREATE: SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의함
• ALTER: TABLE에 대한 정의를 변경하는 데 사용
• DROP: SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제함

CREATE SCHEMA

• 스키마를 정의하는 명령문
• 표기 형식

CREATE SCHEMA 스키마명 AUTHORIZATION 사용자_id

• 예시

CREATE SCHEMA college AUTHORIZATION Minseol;

CREATE DOMAIN

• 도메인을 정의하는 명령문
• 표기 형식

CREATE DOMAIN 도메인명 [AS] 데이터_타입
	[DEFAULT 기본값]
    [CONSTRAINT 제약조건명 CHECK (범위값)];

• 예시

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

CREATE TABLE

• 테이블을 정으하는 명령문

• 표기 형식

CREATE TABLE 테이블명
	(속성명 데이터_타입 [DEFAULT 기본값][NOT NULL], ...
    [, PRIMARY KEY(기본키_속성명, ...)]
    [, UNIQUE(대체키_속성명, ...)],
    [. FOREIGN KEY(외래키_속성명, ...)]
    	REFERENCES 참조테이블(기본키_속성명, ...)]
        [ON DELETE 옵션]
        [ON UPDATE 옵션]
    [, CONSTRAINT 제약조건명][CHECK (조건식)]);

• 예시

	CREATE TABLE student
    	(name VARCHAR(15) NOT NULL,
        s_num CHAR(6),
        major CHAR(5),
        sex SEX,
        birth DATE,
        PRIMARY KEY(s_num),
        FOREIGN KEY(major) REFERENCES department(dCode)
        	ON DELETE SET NULL
            ON UPDATE CASCADE,
        CONSTRAINT BIRTH-REGULATION
        	CHECK(birth >= '1980-01-01'));

CREATE VIEW

• 뷰를 정의하는 명령문
• 표기 형식

CREATE VIEW 뷰명[(속성명[, 속성명, ...])]
AS SELECT문;

• 예시

CREATE VIEW ansan_customer(name, phone)
AS SELECT name, phone
FROM customer
WHERE address = '안산시';

• WITH CHECK OPTION: 뷰에 갱신이나 삽입 연산이 수행될 때 뷰 정의 조건을 따르게 함

CREATE INDEX

• 인덱스를 정의하는 명령문

• 표기 형식

CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명 [ASC | DESC] [, 속성명 [ASC | DESC]])
[CLUSTER];

• 예시

CREATE UNIQUE INDEX c_no_idx
ON customer(c_no DESC);

ALTER TABLE

• 테이블에 대한 정의를 변경하는 명령문
• 표기 형식

ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [DEFAULT '기본값'];
ALTER TABLE 테이브명 ALTER 속성명 [SET DEFAULT '기본값'];
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];

• 예시

ALTER TABLE student ADD grade VARCHAR(3);
ALTER TABLE student ALTER s_num VARCHAR(10) NOT NULL;

DROP

• 스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스, 제약 조건 등을 제거하는 명령문

• 표기 형식

DROP SCHEMA 스키마명 [CASCADE | RESTRICT];
DROP DOMAIN 도메인명 [CASCADE | RESTRICT];
DROP TABLE 테이블명 [CASCADE | RESTRICT];
DROP VIEW 뷰명 [CASCADE | RESTRICT];
DROP INDEX 인덱스명 [CASCADE | RESTRICT];
DROP CONSTRINT 제약조건명;

• 예시

DROP TABLE student CASCADE;

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

• 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는 데 사용하는 언어

DCL의 종류

• COMMIT
• ROLLBACK
• GRANT
• REVOKE

GRANT / REVOKE

• 데이터베이스 사용자에게 권한을 부여하거나 취소하기 위한 명령어

사용자 등급 지정 및 해제

GRANT 사용자등급 TO 사용자_ID_리스트 [IDENTIFIED BY 암호];
REVOKE 사용자등급 FROM 사용자_ID_리스트;

• 예시

GRANT RESOURCE TO NABI; // 사용자등급은 DBA, RESOURCE, CONNECT로 나뉨

테이블 및 속성에 대한 권한 부여 및 취소

GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];

• 예시

GRANT ALL ON 고객 TO NABI WITH GRANT OPTION;

REVOKE GRANT OPTION FOR UPDATE ON 고객 FROM STAR;

COMMIT

• 트랜잭션 처리가 정상적으로 완료된 후 트랜잭션이 수행한 내용을 데이터베이스에 반영하는 명령

ROLLBACK

• 변경되었으나 아직 COMMIT되지 않은 모든 내용들을 취소하고 데이터베이스를 이전 상태로 되돌리는 명령어
• 트랜잭션 전체가 성공적으로 끝나지 못하면 일부 변경된 내용만 데이터베이스에 반영되는 비일관성(Inconsistency) 상태가 될 수 있기 때문에 일부분만 완료된 트랜잭션은 롤백(Rollback)되어야 한다.

SAVEPOINT

• 트랜잭션 내에 ROLLBACK 할 위치인 저장점을 지정하는 명령어

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

• 저장된 데이터를 실질적으로 관리하는데 사용되는 언어

DML의 유형

• SELECT
• INSERT
• DELETE
• UPDATE

삽입문(INSERT INTO~)

• 일반 형식

INSERT INTO 테이블명([속성명1, 속성명2, ...])
VALUES (데이터1, 데이터2, ...);

• 예시

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

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

삭제문(DELETE FROM~)

• 일반 형식

DELETE
FROM 테이블명
[WHERE 조건];

갱신문(UPDATE~ SET~)

• 일반 형식

UPDATE 테이블명
SET 속성명 = 데이터[, 속성명=데이터, ...]
[WHERE 조건];

DML - SELECT

일반 형식

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

조건 연산자

비교 연산자

• <>: 같지 않다

LIKE 연산자

• %: 모든 문자를 대표함
• _: 문자 하나를 대표함
• #: 숫자 하나를 대표함

하위 질의

• 조건절에 주어진 질의를 먼저 수행하여 그 검색 결과를 조건절의 피연산자로 사용

그룹 함수

• GROUP BY절에 지정된 그룹별로 속성의 값을 집계할 때 사용

COUNT(속성명)
SUM(속성명)
AVG(속성명)
MAX(속성명)
MIN(속성명)
STDDEV(속성명)
VARIANCE(속성명)
ROLLUP(속성명, 속성명, ...)
CUBE(속성명, 속성명, ...)

WINDOW 함수

• GROUP BY절을 이용하지 않고 함수의 인수로 지정한 속성의 값을 집계
• 함수의 인수로 지정한 속성이 집계할 범위가 되는데, 이를 윈도우(WINDOW)라고 부른다.

ROW_NUMBER()

• 윈도우별로 각 레코드에 대한 일련번호를 반환

RANK()

• 윈도우별로 순위를 반환하며, 공동 순위를 반영

DENSE_RANK()

• 윈도우별로 순위를 반환하며, 공동 순위를 무시하고 순위를 부여

WINDOW 함수 이용 검색

• GROUP BY절을 이용하지 않고 함수의 인수로 지정한 속성을 범위로 하여 속성의 값을 집계

SELECT 상여내역, 상여금
	ROW_NUMBER() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO
FROM 상여금;

SELECT 상여내역, 상여금
	RANK() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS 상여금순위
FROM 상여금;

그룹 지정 검색

SELECT 부서, AVG(상여금) AS 평균
FROM 상여금
GROUP BY 부서;

SELECT 부서, COUNT(*) AS 사원수
FROM 상여금
WHERE 상여금 >= 100
GROUP BY 부서
HAVING COUNT(*) >= 2;

집합 연산자를 이용한 통합 질의

• 집합 연산자를 사용하여 2개 이상의 테이블을 하나로 통합한다.

UNION

• 두 SELECT문의 조회 결과를 통합하여 모두 출력함
• 중복된 행은 한 번만 출력함

UNION ALL

• 두 SELECT문의 조회 결과를 통합하여 모두 출력함
• 중복된 행도 그대로 출력함

INTERSECT

• 두 SELECT문의 조회 결과 중 공통된 행만 출력함

EXCEPT

• 첫 번째 SELECT문의 조회 결과에서 두 번째 SELECT문의 조회 결과를 제외한 행을 출력함

DML - JOIN

• 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션을 반환

INNER JOIN

• 일반적으로 EQUI JOIN과 NON-EQUI JOIN으로 구분
• 조건이 없는 INNER JOIN을 수행하면 CROSS JOIN과 동일한 결과를 얻을 수 있다.

EQUI JOIN

• JOIN 대상 테이블에서 공통 속성을 기준으로 '='(equal) 비교에 의해 같은 값을 가지는 행을 연결하여 결과를 생성하는 JOIN 방법
• JOIN 조건이 '='일 때 동일한 속성이 두번 나타나게 되는데, 이 중 중복된 속성을 제거하여 같은 속성을 한 번만 표기하는 방법을 NATURAL JOIN이라고 한다.
• WHERE절을 이용한 EQUI JOIN의 표기 형식

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2, ...
WHERE 테이블명1.속성명 = 테이블명2.속성명;

• NATURAL JOIN절을 이용한 EQUI JOIN의 표기 형식

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 NATURAL JOIN 테이블명2;

• JOIN ~ USING절을 이용한 EQUI JOIN의 표기 형식

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 JOIN 테이블명2 USING(속성명);

NON-EQUI JOIN

• JOIN 조건에 '=' 조건이 아닌 나머지 비교 연산자, 즉 >, <, <>, >=, <=- 연산자를 사용하는 JOIN 방법이다.

• 표기 형식

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2, ...
WHERE (NON-EQUI JOIN 조건);

OUTER JOIN

• 릴레이션에 JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 JOIN 방법

LEFT OUTER JOIN

• INNER JOIN의 결과를 구한 후, 우측 항 릴레이션의 어떤 튜플과도 맞지 않는 좌측 항의 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN의 결과에 추가
• 표기 형식

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 LEFT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명 = 테이블명2.속성명(+);

RIGHT OUTER JOIN

• INNER JOIN의 결과를 구한 후, 좌측 항 릴레이션의 어떤 튜플과도 맞지 않는 우측 항의 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN의 결과에 추가
• 표기 형식

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 RIGHT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명(+) = 테이블명2.속성명;

FULL OUTER JOIN

• LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합쳐 놓은 것
• 표기 형식

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 FULL OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

프로시저(Procedue)

• SQL을 사용하여 작성한 일련의 작업을 저장해두고 원할 때마다 저장한 작업을 수행하도록 하는 절차형 SQL

프로시저의 구성도

DECLARE
BEGIN
	CONTROL
    SQL
    EXCEPTION
    TRANSACTION
END

프로시저 생성

• CREATE PROCEDURE 명령어를 사용

• 표기 형식

CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)
[지역변수 선언]
BEGIN
	프로시저 BODY;
END;

- 예시
```sql
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;

프로시저 실행

EXECUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명;

프로시저 제거

DROP PROCEDURE 프로시저명;

트리거(Trigger)

• 이벤트(Event)가 발생할 때 관련 작업이 자동으로 수행되게 하는 절차형 SQL
• 데이터베이스에 저장되며, 데이터 변경 및 무결성 유지, 로그 메시지 출력 등의 목적으로 사용
• 트리거의 구문에는 DCL(데이터 제어어)를 사용할 수 없으며, DCL이 포함된 프로시저나 함수를 호출하는 경우에 오류가 발생

트리거의 구성도

DECLARE
EVENT
BEGIN
	CONTROL
    SQL
    EXCEPTION
END

트리거의 생성

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

• 예시

CREATE TRIGGER 학년정보_tri BEFORE INSERT ON 학생
REFERENCING NEW AS new_table
FOR EACH ROW
WHEN (new_table.학년 IS NULL)
	BEGIN
    	:new_table.학년 := '신입생';
    END;

트리거의 제거

• 표기 형식

DROP TRIGGER 트리거명;

사용자 정의 함수

• 종료 시 처리 결과로 단일값만을 반환하는 절차형 SQL
• SELECT, INSERT, DELETE, UPDATE 등 DML문의 호출에 의해 실행
• 예약어 RETURN을 통해 단일값을 반환하며, 출력 파라미터가 없다.

사용자 정의 함수의 구성도

DECLARE
BEGIN
	CONTROL
    SQL
    EXCEPTION
    RETURN
END

사용자 정의 함수 생성

• 표기 형식

CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터)
[지역변수 선언]
BEGIN
	사용자 정의 함수 BODY;
    RETURN 반환값;
END;

• 예시

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

사용자 정의 함수 실행

• DML에서 속성명이나 값이 놓일 자리를 대체하여 사용
• 표기 형식

SELECT 사용자 정의 함수명 FROM 테이블명;
INSERT INTO 테이블명(속성명) VALUES (사용자 정의 함수명);
DELETE FROM 테이블명 WHERE 속성명 = 사용자 정의 함수명;
UPDATE 테이블명 SET 속성명 = 사용자 정의 함수명;

사용자 정의 함수 제거

DROP FUNCTION 사용자 정의 함수명;

제어문

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

IF문

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

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

• 예시

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

LOOP문

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

LOOP
	실행할 문장;
    EXIT WHEN 조건:
END LOOP;

• 예시

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

커서(Cursor)

• 쿼리문의 처리 결과가 저장되어 있는 메모리 공간을 가리키는 포인터(Pointer)
• 열기(Open), 패치(Fetch), 닫기(Close)의 세 단계로 진행

묵시적 커서(Implicit Cursor)

• 내부에서 자동으로 생성되어 사용되는 커서이다.
• 커서의 속성을 조회하여 사용된 쿼리 정보를 열람하는 것이 가능
• 수행된 커리문의 정상적인 수행 여부를 확인하기 위해 사용

속성의 종류

• SQL%FOUND
• SQL%NOTFOUND
• SQL%ROWCOUNT
• SQL%ISOPEN

명시적 커서(Explicit Cursor)

• 사용자가 직접 정의해서 사용하는 커서
• 쿼리문의 결과를 저장하여 사용함으로써 동일한 쿼리가 반복 수행되어 데이터베이스 자원이 낭비되는 것을 방지
• 기본적으로 열기(Open) - 패치(Fetch) - 닫기(Close) 순으로 이루어지며, 열기 단계 전에 선언(Declare)해야 한다.

선언(Declare) 형식

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

열기(Open) 형식

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

패치(Fetch) 형식

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

닫기(Close) 형식

CLOSE 커서명;

예시

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;

DBMS 접속(Connection)

• 응용 시스템을 이용하여 DBMS에 접근하는 것
• 인터넷을 통해 구동되는 웹 응용 프로그램은 웹 응용 시스템을 통해 DBMS에 접근한다.
• 웹 응용 시스템은 웹 서버와 웹 어플리케이션 서버(WAS)로 구성된다.

DBMS 접속 기술

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

DBMS 접속 기술의 종류

JDBC(Java DataBase Connectivity)

• Java 언어로 다양한 종류의 데이터베이스에 접속할 때 사용하는 표준 API

ODBC(Open DataBase Connectivity)

• 개발 언어에 관계없이 데이터베이스에 접근하기 위한 표준 개방형 API

MyBatis

• JDBC 코드를 단순화하여 사용할 수 있는 SQL Mapping 기반 오픈 소스 접속 프레임워크

동적 SQL(Dynamic SQL)

• SQL 구문을 동적으로 변경하여 처리할 수 있는 SQL 처리 방식
• 동적 SQL은 정적 SQL에 비해 속도가 느리지만, 상황에 따라 다양한 조건을 첨가하는 등 유연한 개발이 가능

SQL 테스트

• SQL이 작성 의도에 맞게 원하는 기능을 수행하는지 검증하는 과정

단문 SQL 테스트

• DDL, DML, DCL이 포함되어 있는 SQL과 TCL을 테스트하는 것

• DDL로 작성된 개체는 DESCRIBE 명령어를 이용하여 속성, 자료형, 옵션들을 확인할 수 있다.

• DESC [개체명]

절차형 SQL 테스트

• 디버깅을 통해 기능의 적합성 여부를 검증하고, 실행을 통해 결과를 확인하는 테스트를 수행

• SHOW 명령어를 통해 오류 내용을 확인하고 문제를 수정

• 형식: SHOW ERRORS;

ORM(Object-Relational Mapping)

• 객체(Object)와 관계형 데이터베이스(Relational DataBase)의 데이터를 연결(Mapping)하는 기술

ORM 프레임워크

• 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이 작성되었는지 확인해야 함

쿼리 성능 최적화

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

옵티마이저(Optimizer)

• 작성된 SQL이 가장 효율적으로 수행되도록 최적의 경로를 찾아 주는 모듈
• RBO(Rule Based Optimizer)와 CBO(Cost Based Optimizer) 두 종류가 있다.
• RBO는 데이터베이스 관리자(DBA)가 사전에 정의해둔 규칙에 의거하여 경로를 찾는 규칙 기반 옵티마이저
• CBO는 각 DBMS마다 고유의 알고리즘에 따라 산출되는 비용으로 최적의 경로를 찾는 비용 기반 옵티마이저

실행 계획(Execution Plan)

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

쿼리 성능 최적화 방법

• SQL문이 더 빠르고 효율적으로 작동하도록 SQL 코드와 인덱스를 재구성하는 것