뷰

📌 뷰

SELECT i.id, i.name, AVG(star) AS avg_star, COUNT(*) AS count_star
FROM item AS i LEFT OUTER JOIN review AS r ON r.item_id = i.id
	LEFT OUTER JOIN member AS m ON r.mem_id = m.id
WHERE m.gender = 'f'
GROUP BY i.id, i.name
HAVING COUNT(*) >= 2 AND avg_star =
(
SELECT MAX(avg_star) FROM(
	SELECT i.id, i.name, AVG(star) AS avg_star, COUNT(*) AS count_star
	FROM item AS i LEFT OUTER JOIN review AS r ON r.item_id = i.id
		LEFT OUTER JOIN member AS m ON r.mem_id = m.id
	WHERE m.gender = 'f'
	GROUP BY i.id, i.name
	HAVING COUNT(*) >= 2
    ORDER BY AVG(star) DESC, COUNT(*) DESC
	) AS final
)
ORDER BY AVG(star) DESC, COUNT(*) DESC;

서브쿼리를 중첩하여 별점의 평균값이 가장 높은 상품들을 조회했다.
그러나 SQL문이 길어지고 중복되는 부분이 생겨 해석하기 힘들다.
매번 서브쿼리를 중첩하다보면 SQL문을 쓰고 읽을 때 힘들어진다.
이 문제는 SQL에서 뷰 라는 기능을 사용하여 해결할 수 있다.

뷰는 조인 등의 작업을 해서 만든 결과 테이블이 가상으로 저장된 형태를 의미한다.

위 코드에서 서브쿼리 final 테이블을 두 번 사용하고 있다.
서브쿼리로 일시적으로 생성한 final 결과 테이블을 뷰로 저장할 수 있다.

CREATE VIEW three_tables_joined AS
SELECT i.id, i.name, AVG(star) AS avg_star, COUNT(*) AS count_star
	FROM item AS i LEFT OUTER JOIN review AS r ON r.item_id = i.id
		LEFT OUTER JOIN member AS m ON r.mem_id = m.id
	WHERE m.gender = 'f'
	GROUP BY i.id, i.name
	HAVING COUNT(*) >= 2
    ORDER BY AVG(star) DESC, COUNT(*) DESC;

CREATE VIEW three_tables_joined AS 결과 테이블

위 코드는 뷰를 만들어주는 SQL문이다.
three_talbes_joined라는 이름의 뷰를 만들라는 의미이다.
AS 뒤에는 뷰로 저장할 결과 테이블을 만들어주는 SELECT문을 작성한다.

오른쪽 상단의 새로고침을 누르면 Views라는 탭에 생성된다.
뷰는 다른 말로 가상 테이블이라고도 한다.

이제 이 뷰를 마치 원래 존재했던 테이블처럼 사용할 수 있다. 뷰를 조회해보자.

SELECT * FROM copang_main.three_tables_joined;

맨 처음 서브쿼리를 중첩하여 사용했던 문제들을 뷰를 통해 해결해보자.

✅ 별점의 평균값이 가장 높은 상품들 조회

SELECT * FROM copang_main.three_tables_joined
WHERE avg_star = (
	SELECT MAX(avg_star) FROM copang_main.three_tables_joined
);

서브쿼리를 중첩했을 때와 뷰를 사용했을 때의 코드를 비교해보자.

💻 서브쿼리 중첩

SELECT i.id, i.name, AVG(star) AS avg_star, COUNT(*) AS count_star
FROM item AS i LEFT OUTER JOIN review AS r ON r.item_id = i.id
	LEFT OUTER JOIN member AS m ON r.mem_id = m.id
WHERE m.gender = 'f'
GROUP BY i.id, i.name
HAVING COUNT(*) >= 2 AND avg_star =
(
SELECT MAX(avg_star) FROM(
	SELECT i.id, i.name, AVG(star) AS avg_star, COUNT(*) AS count_star
	FROM item AS i LEFT OUTER JOIN review AS r ON r.item_id = i.id
		LEFT OUTER JOIN member AS m ON r.mem_id = m.id
	WHERE m.gender = 'f'
	GROUP BY i.id, i.name
	HAVING COUNT(*) >= 2
    ORDER BY AVG(star) DESC, COUNT(*) DESC
	) AS final
)
ORDER BY AVG(star) DESC, COUNT(*) DESC;

💻 뷰

SELECT * FROM copang_main.three_tables_joined
WHERE avg_star = (
	SELECT MAX(avg_star) FROM copang_main.three_tables_joined
);

코드가 훨씬 간결해진 것을 알 수 있다.

✅ 위에서 조회한 결과에서 리뷰가 가장 많은 상품 조회

SELECT * FROM copang_main.three_tables_joined
WHERE avg_star = (
	SELECT MAX(avg_star) FROM copang_main.three_tables_joined
) AND count_star = (
	SELECT MAX(count_star) FROM copang_main.three_tables_joined
);

---

📝 뷰에 관해 알아야할 사실

뷰는 다른 말로 가상 테이블이라고 하였다.
그렇다면 뷰와 가상 테이블은 어떤 차이가 있을까 ❓

가장 큰 차이는 뷰는 테이블과 달리 데이터가 물리적으로 컴퓨터에 저장되어 있는 건 아니라는 점이다.

뷰는 표 형식으로 내용을 본다는 점에서 테이블과 같지만, 테이블처럼 그 내용이 실제로 컴퓨터에 저장되어 있는 건 아니다. 대신에 뷰는 사용할 때 DBMS가 뷰를 생성하는 SQL문을 재실행하는 방식으로 가상의 테이블을 만들어준다.

three_tables_joined 라는 뷰를 사용할 때마다 AS 뒤의 빨간 박스 안의 SELECT문이 재실행된다는 뜻이다.

• 뷰는 사용자에게 높은 편의성을 제공해준다.
• 각 직무별 데이터 수요에 알맞은 다양한 구조의 데이터 분석 기반을 구축해둘 수 있다.
• 뷰는 데이터 보안을 제공한다.

---

📝 실무에서 첫 번째로 해야할 일

• 어떤 데이터베이스들이 있는지
• 각 데이터베이스 안에 어떤 테이블이 있는지
• 각 테이블의 컬럼 구조는 어떻게 되는지
• 테이블들 간의 Foreign Key 관계는 어떤지

📌 존재하는 데이터베이스 파악

SHOW DATABASES;

📌 한 데이터베이스 안의 테이블(뷰도 포함)들 파악

SHOW FULL TABLES IN copang_main;

📌 한 테이블의 컬럼 구조 파악

DESCRIBE item;

📌 Foreign Key(외래키) 파악

SELECT
	i.TABLE_SCHEMA, i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME
    k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME
   
FROM information_schema.TABLE_CONSTRAINTS i
LEFT JOIN information_schema.KEY_COLUMN_USAGE k
ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME
WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY';

---

✅ 과제 : 뷰와 데이터 분석 실무 과제

직원 정보는 employee라고 하는 테이블에서 관리되고 있다.
employee 테이블의 컬럼 중에는 외부로 공개되면 안 되는 컬럼들이 있다.

• address(직원의 주소) 컬럼
• rating_grade(인사고과 점수) 컬럼

이 컬럼들을 제외하고 employee 테이블을 볼 수 있는 뷰를 만들어라.

1. employee 테이블의 컬럼 구조를 살펴보는 SQL 문을 실행하라.
2. employee 테이블의 컬럼 구조를 확인했으면, 위에서 말한 두 컬럼을 제외한 컬럼들만 조회해서, v_emp 라는 이름의 뷰를 생성하는 SQL 문을 실행하라.
3. 2의 SQL 문 바로 밑에 v_emp의 모든 컬럼을 조회하는 SELECT 문을 적고 실행하라.

💻 풀이

DESCRIBE employee

CREATE VIEW v_emp AS
SELECT
    id,
    name,
    age,
    department,
    phone_num,
    hire_date
FROM employee;

SELECT * FROM v_emp;

👉 결과

---