데이터베이스 - Index, View, Triger, Procedure

인덱스(Index)

• 데이터베이스 테이블에 색인을 추가해 검색 성능의 속도를 높여주는 기능

-- Index 확인
SHOW INDEX FROM employees;

-- 0.078 초
SELECT * FROM employees WHERE first_name = 'Saniya';

-- Index 생성
ALTER TABLE employees ADD INDEX idx_employees_first_name (first_name);
 
-- Index 삭제
DROP INDEX idx_employees_first_name ON employees;

---

뷰(VIEW)

• 가상테이블로, 보안과 함꼐 사용자의 편의성을 높이기 위해서 사용
• 장점 : 복잡한 SQL를 편하게 조회 할 수 있다
  단점 : INDEX를 사용할 수 없어 조회 속도 느림

-- 사원의 사번, 생년월일, 이름, 성, 성별, 입사일, 현재급여, 현재직책 출력
SELECT 
   emp.emp_no  
   , emp.birth_date
   , emp.first_name
   , emp.last_name
   , emp.gender
   , emp.hire_date
   , sal.salary
   , tit.title
FROM employees emp
  JOIN salaries sal
     ON emp.emp_no = sal.emp_no
     AND sal.to_date >= NOW()
  JOIN titles tit
	   ON tit.emp_no = tit.emp_no
     AND tit.to_date >= NOW()
;    

	   
-- 위의 쿼리를 뷰로 작성
CREATE VIEW  view_emp_info
AS 
SELECT 
   emp.emp_no  
   , emp.birth_date
   , emp.first_name
   , emp.last_name
   , emp.gender
   , emp.hire_date
   , sal.salary
   , tit.title
FROM employees emp
  JOIN salaries sal
     ON emp.emp_no = sal.emp_no
     AND sal.to_date >= NOW()
  JOIN titles tit
	   ON tit.emp_no = tit.emp_no
     AND tit.to_date >= NOW()
;    

SELECT * FROM view_emp_info;

---

스토어드 프로시저(Stored Procedure)

• 일련의 쿼리를 모아 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합

-- 프로시저(procedure)

DELIMITER $$
CREATE PROCEDURE test()
BEGIN 
  SELECT * FROM employees WHERE emp_no <= 10005;
END $$
DELIMITER ;

-- 프로시저 호출
CALL test();

-- 프로시저 삭제
DROP PROCEDURE test;

---

스토어드 함수(Stored Function)

• 개발자가 필요에 따라 직접 만들어서 사용하는 함수

-- 함수 생성
DELIMITER $$
CREATE FUNCTION my_sum(num1 INT, num2 INT)
RETURNS INT 
BEGIN
   RETURN num1 + num2;
END $$ 
DELIMITER ;

-- 함수 호출
SELECT my_sum(100,2);

-- 함수 조회
SHOW FUNCTION STATUS;

-- 함수 삭제
DROP FUNCTION my_sum;

---

트리거(Trigger)

• 어떤 테이블에서 특정한 이벤트(update, insert, delete)가 발생했을 때, 실행시키고자 하는 추가 쿼리 작업들을 자동으로 수행되도록 설정해 두는 것

-- trigger 예제 :employees 테이블의 레코스 삭제시, 그 전에 다른 테이블 레코드 먼저 삭제
DELIMITER $$
CREATE TRIGGER trigger_employees_emp_info
BEFORE DELETE 
ON employees
FOR EACH ROW 
BEGIN 
  DELETE FROM titles WHERE emp_no = OLD.emp_no;
END $$
DELIMITER ;

DELETE FROM employees WHERE emp_no = 10002;

-- 트리거 조회 
SHOW TRIGGERS;

-- 트리거 삭제 
DROP TRIGGER trigger_employees_emp_info;

---

계정 및 권한 관리

• DB를 설치하면 기본적으로 root 계정이 생성되고, 모든 권한이 부여되어 있어 사용이 펼하지만 보안상 root계정의 사용은 바람직하지 못함

-- 권한 확인
SELECT * FROM mysql.USER;

-- 계정 생성
CREATE user 'user1'@'localhost' IDENTIFIED BY 'user1';

-- 계정에 권한 부여
GRANT ALL ON employees.* TO 'user1'@'localhost';

-- 권한 삭제
REVOKE INSERT, UPDATE, DELETE ON employees.* FROM 'user1'@'localhost';

-- 계정 삭제
DROP user 'user1'@'localhost'; 

---

커서(Cursor)

• 특정 SQL 문장을 처리한 결과를 저장하고 그 영역을 가리키는 일종의 포인터

-- 10001 사원의 모든 급여를 더한 값을 출력
DELIMITER $$
CREATE PROCEDURE test()
BEGIN 
  DECLARE sum_sal INT DEFAULT 0;
  DECLARE row_sal INT DEFAULT 0;
  DECLARE cur_sal INT DEFAULT 0;
  DECLARE end_row BOOLEAN DEFAULT FALSE;
  
  -- 커서생성
   DECLARE cur_sal CURSOR FOR 
    SELECT salary FROM salaries WHERE emp_no = 10001;
   
	-- 행이 끝나면 end_row에 true 를 대입 
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_row = TRUE; 
	
	-- 커서 오픈
   OPEN cur_sal;
	
	-- Loop 시작
	cursor_loop: LOOP 
	   -- 커서가 선택하고 있는 데이터 가져오기
	   FETCH cur_sal INTO row_sal;
	   -- 마지막 행이면 루프 종료
	   IF end_row THEN
		   LEAVE cursor_loop;
		END IF;	 
	   
	   SET sum_sal = sum_sal + row_sal;
	END LOOP cursor_loop;
	
	SELECT sum_sal;  
END $$
DELIMITER ;

CALL test();

---