✅ DDL

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

◾️ 종류

◽️ CREATE

• schema

CREATE SCHEMA 스키마 AUTHORIZATION userid;

• domain

CREATE DOMAIN 도메인명 [AS] CHAR(1)
  [DEFAULT '기본값']
  [CONTRAINT 제약조건명 CHECK(VALUE IN('기본값', '기본값2'));

• table

CREATE TABLE 테이블명 (
  id CHAR(20) PRIMARY KEY,
  name CHAR(20) NOT NULL,
  blah INT UNIQUE,
  FOREIGN KEY(속성명) REFERENCES 테이블2(속성명)
    ON DELETE SET NULL // 테이블2의 튜플 삭제 시, 모든 테이블 속성 값 NULL로 변경
    ON UPDATE CASCAE, // 변경되면 같이 변경
  CONTRAINT 제약명
    CHECK(name >= 1);

• view

CREATE VIEW 테이블(a,b)
AS SELECT a, b
FROM 테이블2
WHERE a=1;

• index

CREATE UNIQUE INDEX 인덱스
ON 테이블(속성 ASC)
[CLUSTER]

◽️ ALTER TABLE

• 테이블 ADD 속성 데이터 타입
• 테이블 ALTER 속성
• 테리블 DROP COLUMN 속성

◽️ DROP

+[SCHEMA|INDEX...] [CASCADE|RESTRICT|

◾️ 용어

◽️ SCHEMA

데이터베이스 구조와 제약 조건에 관한 전반적인 명세를 기술한 것.

◽️ DOMAIN

하나의 속성이 취할 수 있는 동일한 유형의 원자값들의 집합

◽️ VIEW

하나 이상의 기본 테이블로부터 유도되는 이름을 갖는 가상 테이블

◽️ INDEX

검색 시간을 단축시키기 위해 만든 보조적인 데이터 구조

• CLUSTERED INDEX : 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식
• NONCLUSTERED INDEX : 인덱스의 키 값만 정렬되어 있을뿐 실제 데이터는 정렬되지 않는 방식 -> 검색 속도가 떨어지지만 삽입 수정에 용이

◽️ CONSTRAINT

제약 조건

✅ DCL

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

◾️ 종류

◽️ COMMIT

수행 결과를 실제 디스크에 저장하고 완료되었음을 관리자에게 전달
트랜잭션이 수행한 내용을 DB에 반영

◽️ ROLLBACK [TO ~]

트랜잭션이 비정상적으로 종료됐을 때 원래 상태로 복구(아직 commit 되지 않은 모든 내용을 취소하고 DB를 이전 상태로 되돌림)

◽️ SAVEPOINT

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

◽️ GRANT 권한 ON 테이블 TO 사용자

사용자에게 권한 부여

◽️ REVOKE 권한 ON 테이블 FROM 사용자

사용 권한 부여를 취소

✅ DML

📌 저장된 데이터를 실질적으로 관리하는 데 사용되는 언어. 사용자와 DBMS 간의 인터페이스를 제공함

종류

• SELECT : 테이블에서 튜플을 검색
• INSERT INTO 테이블(속성) VALUES(데이터)
• DELETE FROM 테이블
• UPDATE 테이블 SET 속성명=데이터

연산자

• 비교 연산자
  =, <>, >, <, >=, <=
• 논리 연산자
  NOT, AND, OR
• LIKE 연산자
  %, _, #

Group

• 그룹 함수
  COUNT, SUM, AVG,MAX,MIN,STDDEV,VARIANCE,ROLLUP,CUBE

JOIN

• INNER JOIN
  • EQUI JOIN
    =연산자
    • WHERE a = b
    • FROM 테이블 NATURAL JOIN 테이블2
    • FROM 테이블 JOIN 테이블2 USING(속성명)
  • NON-EQUI JOIN
    = 연산자 제외
• OUTER JOIN
  • LEFT OUTER JOIN
    좌측 릴레이션 기준으로 맞지않는 좌측 항도 결과에 추가
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN

FROM 테이블 LEFT OUTER JOIN 테이블2
ON 테이블.속성 = 테이블2.속성;

FROM 테이블, 테이블2
WHERE 테이블.속성 = 테이블2.속성(+);

✅ 절차형 SQL

연속적인 실행이나, 분기, 반복 등의 제어가 가능한 SQL

◾️ 프로시저

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

◽️ 생성

• DECLARE
• BEGIN
  • CONTROL
  • SQL(DML, DCL)
  • EXCEPTION
  • TRANSACTION
• END

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

프로시저의 파라미터로는 IN, OUT, INOUT이 올 수 있다.

◽️ 실행

EXECUTE p_name;
EXEC p_name;
CALL p_name;

◽️ 제거

DROP PROCEFURE p_name;

◾️ 트리거

이벤트가 발생할 때 관련 작업이 자동으로 수행되게 하는 절차형 SQL

◽️ 생성

• DECLARE
• EVENT
• BEGIN
  • CONTROL
  • SQL
  • EXCEPTION
• END

CREATE [OR REPLACE] TRIGGER 학년정보_tri  BEFORE INSERT ON 학생
REFERENCING NEW AS new_table // insert 되는 테이블 명칭
FOR EACH ROW // 모든 튜플을 대상으로 함
WHEN (new_table.학년 IS NULL)
BEGIN
	:new_table := '신입생';
END;

FOR EACH ROW 기억하기

◽️ 제거

DROP TABLE 트리거명;

◾️ 사용자 정의 함수

종료 시 처리 결과로 단일값만을 반환하는 절차형 SQL

◽️ 생성

• DECLAR
• BEGIN
  • CONTROL
  • SQL
  • EXCEPTION
  • RETURN(필수)
• END

CREATE FUNCTION Get_sex(i_sexcode IN INT)
RETURN VARCHAR2
IS
BEGIN
	IF i_sexcode = 1 THEN
    	RETURN '남자'
    ELSE
    	RETURN '여자'
    END IF
END

사용자 정의 함수의 파라미터로는 IN이 올 수 있다.

◽️ 실행

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

◽️ 제거

DROP FUNCTION 사용자 정의 함수명;

◾️ 제어문

차례대로 실행되는 절차형 SQL의 진행 순서를 변경하기 위해 사용하는 명령문

◽️ IF 문

IF x > 10 THEN
	DBMS_OUTPUT.PUT_LINE("true");
ELSE
	DBMS_OUTPUT.PUT_LINE("false");
END IF;

◽️ LOOF 문

LOOP
	i := i+1;
	EXIT WHEN i>=10;
END LOOP;

◾️ Cursor ⭐️⭐️⭐️

쿼리문의 처리 결과가 저장되어 있는 메모리 공간을 가리키는 포인터.
확인은 'Open - Fetch - Close' 순으로 이루어짐

◽️ 묵시적 커서

DBMS에 의해 내부에서 자동으로 생성되어 사용되는 커서. 수행된 쿼리문의 정상 수행 여부를 확인하기 위해 사용한다.

속성의 종류

• SQL%FOUND : 쿼리 수행 결과로 Fetch 된 튜플 수가 1개 이상이면 TRUE
• SQL%NOTFOUND : Fetch 된 튜플 수가 0개면 TRUE
• SQL%ROWCOUNT : Fetch 된 튜플 수 반환
• SQL%ISOPEN : 커서는 자동으로 생성된 후 자동으로 닫힘. 항상 FALSE임.

◽️ 명시적 커서

사용자가 직접 정의해서 사용하는 커서. 쿼리문의 결과를 저장하여 사용함으로써 동일한 쿼리가 반복 수행되어 데이터베이스 자원이 낭비되는 것을 방지한다.
명시적 커서는 Open 전에 Declare부터 해야 한다.

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; // cursor로부터 데이터 받아옴
        EXIT WHEN cur_name%NOTFOUND
        DBMS_OUTPUT.PUT_LINE(p_name);
    END LOOP;
    CLOSE cur_name;
END;

✅ ETC

◾️ DBMS 접속

응용시스템을 이용하여 DBMS에 접근(Connection)하는 것

◽️ 접속 기술

DBMS에 접근하기 위해 사용하는 API or API 사용을 편하게 해주는 프레임워크

• JDBC : Java로 DB 접속할 때 쓰는 표준 API. 썬 마이크로시스템에서 출시. 드라이버 필요
• ODBC : DB 접근 표준 개방형 API. 마이크로소프트에서 출시.
• MyBatis : JDBC를 단순화하여 사용할 수 있는 SQL Mapping 기반 오픈 소스 접속 프레임워크. SQL -> XML -> mapping하여 실행

◽️ Dynamic SQL

SQL 구문을 동적으로 변경하여 처리할 수 있는 SQL 처리 방식.
쉽게 말해, 사용자가 응용 프로그램을 실행시킨 후 입력란에 SQL을 직접 입력하여 결과를 확인할 수 있는 것.

◾️ SQL 테스트

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

◽️ 단문 SQL 테스트

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

• DDL : DESC 명령어로 속성, 자료형, 옵션 등 확인
• DML : SELECT 문으로 데이터의 정상적인 변경 여부 확인
• DCL : 사용자 권한 정보가 저장된 테이블을 조회하여 사용자 권한 확인

◽️ 절차형 SQL 테스트

• 디버깅을 통해 기능의 적합성 여부 판단(구문 에러, 참조 에러 판단)
• 실행을 통해 결과 확인
• SHOW ERRORS 명령어로 상세 에러 확인
• SQL은 주석으로 처리하고 테스트.

◾️ ORM

Object Relational Mapping.
객체와 관계형 데이터베이스에 데이터를 연결하는 기술

◽️ ORM 프레임워크

ORM을 구현하기 위한 여러 기능들을 제공하는 소프트웨어

• Java : JPA, Hibernate, EclipseLink
• C+++ : ODB, QxOrm
• Python : Django, SQLAlchemy
• .NET : NHibernate, DatabaseObjects
• PHP : Doctrine

◽️ ORM의 한계

• 프레임워크가 자동으로 SQL 문을 작성하기 때문에 의도대로 SQL이 작성되었는지 확인 필요.
• 객체지향적인 사용을 고려하고 설계된 DB여야 ORM 기술 적용이 쉽다.

◾️ 쿼리 성능 최적화

데이터 입출력 어플리케이션의 성능 향상을 위해 SQL 코드를 최적화하는 것.
APM으로 최적화할 쿼리를 선정해야 함.

◽️ APM(Application Performance Monitoring)

어플리케이션의 성능 관리를 위해 접속자, 자원 현황, 트랜잭션 수행 내역 등 다양한 모니터링 기능을 제공하는 도구

◽️ 쿼리 최적화

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

◽️ Optimizer

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

• RBO : 규칙 기반 옵티마이저
• CBO : 비용 기반 옵티마이저
• 실행 계획
  DMBS의 옵티마이저가 수립한 SQL 코드의 실행 절차와 방법
• 쿼리 성능 최적화 방법 :
  • SQL 코드 재구성
    • IN을 EXISTS로 대체
  • 인덱스 재구성