MySQL 더 깊이 알아가기 > 뷰 - 가상의 테이블

https://inf.run/JvXX

갖고노는 MySQL 데이터베이스 by 얄코 - 인프런 | 강의

뷰

실제 테이블이 아니라 SELECT문을 수행한 결과 또는 실제 테이블을 JOIN하거나 서브쿼리로 이어서 출력한 것을 테이블처럼 사용하는 가상의 테이블.

 

-- 뷰 생성
CREATE VIEW section_view AS
SELECT section_id, section_name
FROM sections;

-- 뷰 조회
SELECT * FROM section_view;

-- 뷰 삭제
DROP VIEW section_view;

 

뷰를 사용하는 이유

1. 보안 - 테이블 구조 및 내용 숨기기

-- business_name을 앞 글자 하나 빼고 *로 가리고 컬럼명도 likes에서 evaluation로 변경
CREATE VIEW menu_view AS
SELECT
  menu_id,
  menu_name,
  CONCAT(
    SUBSTRING(business_name, 1, 1),
    REPEAT('*', CHAR_LENGTH(business_name) - 1)
  ) AS business,
  price,
  likes AS evaluation
FROM businesses B
INNER JOIN menus M
  ON M.fk_business_id = B.business_id;

 

 

2. 편의 - 복잡한 쿼리 사전 구현

-- 뷰를 만들어놓으면 아래처럼 SELECT문을 매번 실행할 필요 없이 가공된 데이터를 살펴볼 수 있다.
CREATE VIEW business_view AS
SELECT
  section_name,
  business_name,
  (SELECT COUNT(*) FROM menus M
    WHERE M.fk_business_id = B.business_id
  ) AS menu_count,
  (SELECT AVG(likes) FROM menus M
    WHERE M.fk_business_id = B.business_id
  ) AS menu_avg_likes,
  (SELECT AVG(stars) FROM ratings R
    WHERE R.fk_business_id = B.business_id
  ) AS avg_stars,
  (SELECT comment FROM ratings R
     WHERE R.fk_business_id = B.business_id
   ORDER BY created DESC LIMIT 1
  ) AS recent_comment
FROM businesses B
INNER JOIN sections S
  ON S.section_id = B.fk_section_id;

 

 

내용수정이 가능한 뷰

• 집계함수 사용하지 않음(MAX, MIN, AVG...)
• GROUP BY 사용하지 않음
• UNION, DISTINCT 사용하지 않음
• SELECT절에 서브쿼리 없음
• WHERE절의 서브쿼리가 FROM절의 테이블 참조하지 않음
• 조인은 INNER만 가능

-- 가능
UPDATE section_view
	SET section_name = '서양식'
WHERE section_id = 5;

-- 가능
UPDATE menu_view
  SET price = price + 1000
WHERE menu_name LIKE '%냉면';

-- 불가
-- 앞에서 business_view를 만들 때 AVG 같은 집계 함수를 사용했기 때문에
UPDATE business_view
  SET business_name = '화룡반점'
WHERE business_name = '화룡각';