[우리FISA] Database 개체

데이터베이스 개체의 개념

• 데이터베이스 개체는 데이터베이스에서 관리하는 데이터의 논리적 단위이다.
• 데이터의 구조와 제약 조건을 정의한다.

VIEW

• 데이터베이스 개체 중 하나로, 하나 이상의 테이블을 기반으로 만들어진 가상 테이블이다.
• CTE라는 문법을 활용하면 파생테이블을 AS 별칭 으로 부를 수 있다 → 쿼리 안에서만 사용 가능
• VIEW는 테이블과 동일하게 사용할 수 있으며, 데이터를 조회하는 용도로 사용된다.
• VIEW를 사용하면 특정 사용자에게만 특정 컬럼을 제공할 수 있다.
  • 보안에 도움이 되고, 긴 SQL을 간략하게 만들 수 있어 많이 사용.

INDEX

빠른 검색을 위함

• 데이터베이스 개체 중 하나로, 테이블의 검색 성능을 향상시키기 위해 사용된다.
• 책의 제일 뒤에 수록되는 색인과 비슷한 개념.
  • 책의 색인: 책의 내용을 빠르게 찾을 수 있게 도와주는 목차
• 일반적으로 인덱스는 B-Tree(Balanced Tree) 자료구조를 사용하여 구현되고, 검색 속도를 향상시키는 데 큰 역할을 한다.

클러스터형 인덱스와 보조 인덱스

클러스터형 인덱스

• 테이블의 데이터를 인덱스 순서로 정렬하는 방식
• 기본 키를 기준으로 생성되는 인덱스.
  • 한 테이블 당 하나의 클러스터형 인덱스만 생성될 수 있다.

보조 인덱스

• 테이블의 데이터를 인덱스 순서로 정렬하지 않는 방식.
• 데이터베이스 테이블에서 기본 키 이외의 다른 열을 기준으로 생성되는 인덱스.
  • 중복울 허용하는 단순 보조 인덱스,
  • 중복을 허용하지 않는 고유 보조 인덱스로 나뉜다.
• 한 테이블 당 여러 개의 보조 인덱스를 생성할 수 있습니다.

인덱스를 효과적으로 쓰는 경우

• WHERE 절에 많이 사용되는 열에 사용해야 속도가 빠르다
  • 검색을 자주 하는 열에 인덱스를 생성하는 것이 효과적이다.
• 자주 사용하는 컬럼에 사용되어야 한다.

인덱스를 쓰지 말아야 하는 경우

• 인덱스를 사용하면 데이터를 저장하는 공간이 더 필요하게 되므로, 인덱스를 지정하는 컬럼의 크기가 크거나, 인덱스의 수가 많은 경우에는 성능이 떨어질 수있다.
  • 성별처럼 중복된 데이터가 많은 열에 인덱스를 생성하는 것은 효과적이지 않다.
• 또한 인덱스를 생성하면 데이터의 삽입, 수정, 삭제 작업이 느려지는 단점이 있다.
  • 데이터의 변경이 빈번하게 일어나는 열에 인덱스를 생성하는 것은 효과적이지 않다.

시퀀스

• 데이터베이스 개체 중 하나로, 일련번호를 생성하기 위해 사용된다.
• 일련번호는 순차적으로 증가하거나 감소할 수 있다.

Procedure

스토어드 프로시저는 SQL에서 프로그래밍이 가능하게 해주는 서버의 기능.
자주 사용하는 여러개의 SQL문을 한데 묶어 함수처럼 일괄적으로 처리하는 용도로 사용한다.

내부 메소드 들도 사실 미리 저장된 PROCEDURE라고 할수있다

• 첫 행과 마지막 행에 구분문자라는 의미의 DELIMITER ~ DELIMITER 문으로 감싼 후, 사이에 BEGIN과 END 사이에 SQL문을 작성.
  • DELIMITER에는 $$ // && @@ 등의 부호를 구분문자로 사용할 수 있다.
• 이후에는 CALL 프로시저명(); 으로 위의 SQL 묶음을 호출할 수 있다.

DELIMITER ~
CREATE PROCEDURE 프로시저이름()
BEGIN
  SQL문1;
  SQL문2;
  ...
END ~
DELIMITER ;

장점

하나의 요청으로 여러 SQL문을 실행 할 수 있다.
네트워크 소요 시간을 줄일 수 있다.

• 긴 여러개의 쿼리를 Stored Procedure를 이용해 구현한다면, SP를 호출할 때 한 번만 네트워크를 경유한다.
  • 네트워크 소요시간을 줄이고 성능을 개선할 수 있다.
• 업무를 구분할 수 있다.
  • 순수한 애플리케이션만 개발하는 조직과 DBMS 관련 코드를 개발하는 조직이 따로 있다면, DBMS 개발하는 조직에서는 데이터베이스 관련 처리하는 SP를 만들어 API처럼 제공하고 애플리케이션 개발자는 SP를 호출해서 사용하는 형식으로 개발할 수 있다.
  • 이렇게 하면 애플리케이션 개발자는 데이터베이스 처리에 대한 지식이 없어도 SP를 호출해서 사용할 수 있다.

SHOW PROCEDURE STATUS; -- 프로시저 목록 확인
SHOW CREATE PROCEDURE 프로시저이름; -- 프로시저 내용 확인
DROP PROCEDURE 프로시저이름; -- 프로시저 삭제

IF 문 사용하기

use student_mgmt;

-- gender가 'man'인 학생들의 수를 세는 스토어드 프로시저 예제
DELIMITER //
CREATE PROCEDURE count_men()
BEGIN
    DECLARE count INT;
    SELECT COUNT(*) INTO count FROM students WHERE gender = 'man';
    IF count > 3 THEN
        SELECT 'Many men' AS result;
    ELSE
        SELECT 'Few men' AS result;
    END IF;
END //
DELIMITER ;

select * from students;
call count_men();

IF ELSEIF ELSE 문 사용하기

use student

-- gender가 man인 학생들의 수를 세는 스토어드 프로시저 예제
DELIMITER //
CREATE PROCEDURE count
BEGIN
    DECLARE count INT;
    SELECT COUNT(*) INTO count FROM students WHERE gender = 'man';
    IF count > 3 THEN
        SELECT 'Many Man' AS result;
    ELSEIF count > 1 THEN
        SELECT 'Few Man' AS result;
    ELSE
        SELECT 'No Man' AS result;
    END IF;
END //
DELIMITER ;

LOOP 문 사용하기

use student

-- 각 학생의 이름을 출력하는 스토어드 프로시저 예제
DELIMITER //
CREATE PROCEDURE print_name()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE name VARCHAR(20);
    DECLARE count INT;
    SELECT COUNT(*) INTO count FROM students;
    WHILE i <= count DO
        SELECT name INTO name FROM students WHERE id = i;
        SELECT name;
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

• DECLARE i INT DEFAULT 1; : i라는 변수를 선언하고 1로 초기화
• DECLARE name VARCHAR(20); : name이라는 변수를 선언
• DECLARE count INT; : count라는 변수를 선언
• SELECT COUNT(*) INTO count FROM students; : students 테이블의 행의 수를 count에 저장
• WHILE i <= count DO : i가 count보다 작거나 같을 때까지 반복
• SELECT name INTO name FROM students WHERE id = i; : students 테이블에서 id가 i인 행의 name을 name에 저장
• SELECT name; : name 출력
• SET i = i + 1; : i에 1을 더함
• END WHILE; : 반복문 종료

동적 SQL

• 상황에 따라 내용 변경이 필요할 때는 동적 SQL을 사용하면 변경되는 내용을 실시간으로 적용해 사용할 수 있다.
  • PREPARE~EXECUTE가 대표적
    • PREPARE문에서는 ?로 향후 입력될 값을 비워놓고, EXECUTE문에서 USING으로 ?에 값을 전달할 수 있다.
    • 실행 후에는 DEALLOCATE PREPARE로 문장을 해제해야 한다.
• 동적 쿼리는 보안상 취약할 수 있으므로, 사용 전 반드시 적절한 검증과 예외 처리를 수행해야 한다.

DROP TABLE IF EXISTS gate_table;
CREATE TABLE gate_table (id INT AUTO_INCREMENT PRIMARY KEY, entry_time DATETIME);

SET @curDate = CURRENT_TIMESTAMP(); -- 현재 날짜와 시간

PREPARE myQuery FROM 'INSERT INTO gate_table VALUES(NULL, ?)';
EXECUTE myQuery USING @curDate;
DEALLOCATE PREPARE myQuery;

SELECT * FROM gate_table;

SET @count = 5;
PREPARE mySQL FROM 'SELECT code, name, continent, region, population
  FROM country
 WHERE population > 100000000
 ORDER BY 1 ASC
 LIMIT ?';
EXECUTE mySQL USING @count;

TRIGGER

어떤 테이블에서 특정한 이벤트(update, insert, delete)가 발생했을 때,
실행시키고자 하는 추가 쿼리 작업들을 자동으로 수행할 수 있게끔 트리거를 미리 설정해 두는 것.

트리거는 직접 실행시킬 수 없고 오직 해당 테이블에 이벤트가 발생할 경우에만 실행된다.
DML에만 작동되며, MySQL에서는 VIEW에는 트리거를 사용할 수 없다.

DDL 에도 사용 가능하다!

DELIMITER $$

CREATE TRIGGER 트리거이름
    AFTER DELETE -- 트리거를 달 동작
    ON 테이블 FOR EACH ROW
BEGIN
    -- 문장
END $$    

DELIMITER ;

• BEFORE/AFTER는 명령 키워드가 사용된 후에 처리할지 아니면 끝난 후 처리할지를 나타낸다.
• 처리할 내용 부분에서 OLD, NEW로 명령 키워드로 변경되는 테이블에 접근할 수 있다.
  • ( OLD : 변경되기 전 테이블, NEW : 변경된 후 테이블 )
• 프로시저는 특정 경우에만 동작시킨다면, 트리거는 매번 DML이 실행될 때마다 동작합니다.
• truncate는 commit까지 완료되므로 trigger를 부착할 수 없다.