20231020_TIL_SQL{VIEW, Stored Procedure, Stored Function, Index}

MySQL - VIEW에 대해서

mysql에서 VIEW를 사용하기 위해서는 Mysql version 5.0이상부터 사용이 가능하다.

여러 Table에서 필요한 Column(=Field)을 조회하는 Select문을 통해 하나의 테이블로 보여주는 것을 의미

>> (이미지 만든 후 추가 예정)

 

VIEW 생성 쿼리문

CREATE VIEW 이름 AS (SELECT ...)

해당 구문에서 AS로 별칭을 지어주는 것은 필수적이라고 한다. 

 

VIEW에서 Column값을 바꾸면 참조하는 Table 값도 변경이 됌 !.. (반대도 가능)

MySQL에서 Table을 확인하는 "SHOW TABLE"로도 VIEW TABLE 확인이 가능하다

 

또한, VIEW의 INSERT 하는데 제한이 있음.

UNION, JOIN, SUBquery를 사용한 SELECT문에서는 INSERT 혹은 UPDATE가 사용이 불가

>> 사용이 불가능이라기 보단 잘못 원하는 방향으로 데이터가 삽입이 안됌

>> 이를 막고자 WHERE절에서 WITH CHECK OPTION을 넣어주면 오류를 발생시켜서 막아줌

---

MySQL - Stored Procedure (저장 프로시저)

해당 기능또한 MySQL vsersion 5.0이상부터 사용이 가능함.

>> 필자는 해당 기능이 python에서의 function이랑 비슷한줄 알았는데 stored function이라는 기능이 따로 존재

 

스토어드 프로시저 작성

CREATE PROCEDURE 프로시저_이름():
  BEGIN
    SQL 쿼리문 작성 칸 ..
    ..
    ..;
  END

스토어드 프로시저가 작성이 되는 BEGIN 내부에서 세미콜론(;)으로 문제가 발생할 수 있다고 함.

그래서 다음과 같이 작성을 한다

DELIMITER // -- 구분문자를 "//"으로 변경한다는 의미
CREATE PROCEDURE 프로시저_이름():
   SQL 쿼리문 작성 ...;
   ...;
   ...;
END
DELIMITER ; -- 구분문자를 다시 ";"(세미콜론)으로 변경한다는 의미

DELIMITER를 통해서 구분문자를 변경하여 CREATE PROCEDURE와 SQL구문에 의한 쿼리 종료가 되지 않게하고

이후 다시 DELIMITER를 통해서 구분문자를 ";"(세미콜론)으로 변경해주어 Stored Procedure를 만들어준다.

 

프로시저_이름()에서 ()소괄호 안에 인수를 넣어줄수 있다.

 

** MySQL 내부에서 Stored function도 존재하는데 스토어드 프로시저랑 동일한 기능을 하지만

함수 실행시 값을 반환해야한다는 특징이 있다.

 

SUM(), COUNT() 같은 것이 내부적으로 제공하고있는 Stored Function이라고 생각하면 된다 !

>> 나중에 다시 다뤄보도록 하자 ** 

---

SQL - INDEX에 대해서

INDEX는 조회를 더 빠르게 해주기 위한 "찾아보기" 기능이다.

INDEX의 내부적인 구조는 보통 Balanced-Tree (B-tree) 구조를 가지고 있으며, DBMS마다 다르다.

(NoSQL은 Hash 구조를 통해서 INDEX를 제공한다고도 한다)

 

B-Tree는 Binary Tree와 유사하지만 다르다.

SQL에서는 Page라는 개념이 있는데, 이 Page마다 할당 가능한 데이터의 수가 있고

그 데이터의 수를 초과할 시 데이터 분할이 일어난다.

>> 이 때문에 INDEX가 있는 Table은 INSERT할 시 느릴 수 있다.

 

MySQL에서 INDEX를 만드는 방법

 

1. 자동으로 만들어지는 INDEX

해당 방법은 Table을 만들 때, CONSTRAINT(제약조건)으로 PK를 만들어주거나 UNIQUE를 지정해주면 된다.

PK는 클러스터형 index, QNIUE는 보조 index를 만들어준다.

 

 

2. 내가 만들어서 넣어주는 INDEX

직접 index를 지정하고 어느 테이블에 어떤 컬럼을 index로 지정할 것인지 만들어준다.

 

 

** 인덱스를 통한 조회구문을 작성할 때 WHERE절에서 어떠한 처리를 하면 안된다고 한다
이 점을 참고하여 현업에서 쿼리문을 잘 작성해보도록 하자 ! **

-- FULL SCAN
SELECT *
  FROM tb
  WHERE index_col * 2 > 14;
  
  
-- Index serch
SELECT *
  FROM tb
  WHERE index_col > 14/2;

 

Reference site

1. View에 대한 블로그

2. My First My SQL & PHP 책

3. 혼공SQL - index파트