[Day 12 | DB] 뷰(View)

y♡ding·2024년 10월 29일

데브코스 TIL

목록 보기
77/163

뷰(View)

뷰(View)는 데이터베이스에서 특정 쿼리의 결과를 마치 하나의 테이블처럼 사용할 수 있도록 만들어진 가상의 테이블입니다. 뷰는 실제 데이터를 저장하지 않고, 기존 테이블의 데이터에서 추출된 결과를 저장하는 쿼리로 구성됩니다. 뷰를 통해 테이블의 복잡한 데이터를 쉽게 조회하고, 특정 사용자에게 필요한 정보만 제한적으로 제공할 수 있습니다.

뷰의 특징

  • 가상 테이블:
    • 뷰는 물리적으로 데이터를 저장하지 않으며, 그 자체로 데이터베이스 내에 가상의 테이블로 존재합니다.
    • 실제 테이블에 접근하지 않고도, 복잡한 데이터를 손쉽게 조회할 수 있습니다.
  • 특정 쿼리의 결과를 테이블처럼 사용:
    • 뷰는 하나의 쿼리를 통해 특정 데이터를 추출하고, 이 결과를 뷰로 저장합니다.
    • 여러 테이블을 조인하여 복잡한 쿼리를 단순화하고, 필요한 데이터만을 반환하도록 할 수 있습니다.
  • 보안 및 접근 제한:
    • 뷰를 통해 특정 사용자에게 필요한 정보만 제공하고, 직접 테이블에 접근할 수 없도록 하여 보안을 강화할 수 있습니다.
    • 예를 들어, 고객 정보를 관리할 때 고객의 주소나 민감한 정보는 제외하고 이름과 연락처만 표시하는 뷰를 생성할 수 있습니다.
  • 수정 편의성:
    • 데이터 원본이 변경되더라도, VIEW는 일관성을 유지하며 동일한 구조로 결과를 제공하므로 쿼리 수정이 필요 없습니다.

기본구조

CREATE VIEW 뷰이름 AS
SELECT 열1, 열2, ...
FROM 테이블명
WHERE 조건;
  • 데이터베이스 내 뷰 확인
-- 테이블의 유형을 확인하는 명령 ( 뷰와 테이블 구분 가능 )
SHOW FULL TABLES;

예제

-- 뷰 생성
CREATE VIEW emp_vu AS
SELECT *
FROM emp;

예제 1: 뷰에서 데이터 조회

뷰를 통해 emp 테이블의 데이터를 조회할 수 있습니다.

SELECT * FROM emp_vu;
  • 설명: emp_vu 뷰에서 모든 데이터를 조회합니다. 이 명령어는 emp 테이블을 직접 조회하는 것과 동일한 결과를 반환하지만, 뷰를 통해 간접적으로 데이터를 접근하게 됩니다.

예제 2: 특정 열만 조회

뷰에서도 특정 열만 선택적으로 조회할 수 있습니다.

SELECT ename, job FROM emp_vu;
  • 설명: emp_vu 뷰에서 ename(이름)과 job(직업) 열만 조회하여 필요한 정보만 출력할 수 있습니다.

예제 3: 조건을 추가하여 데이터 조회

뷰에서도 WHERE 절을 사용하여 조건을 추가할 수 있습니다.

SELECT * FROM emp_vu WHERE job = 'MANAGER';
  • 설명: emp_vu 뷰에서 job 열이 MANAGER인 직원들만 조회합니다. 뷰는 테이블과 마찬가지로 필터링된 데이터를 반환할 수 있습니다.

예제 : 부서 번호 10번에 속한 직원들만 조회할 수 있는 제한적인 뷰 만들기

  • 데이터 제한: 부서 10번에 속한 직원의 정보만 포함하도록 제한함으로써, 데이터 보안을 강화하고 불필요한 정보 노출을 방지합니다. 
CREATE VIEW emp_vu_10 AS
SELECT empno, ename, mgr, job
FROM emp
WHERE deptno = 10;

SELECT * FROM emp_vu_10;
+-------+--------+------+-----------+
| empno | ename  | mgr  | job       |
+-------+--------+------+-----------+
|  7782 | CLARK  | 7839 | MANAGER   |
|  7839 | KING   | NULL | PRESIDENT |
|  7934 | MILLER | 7782 | CLERK     |
+-------+--------+------+-----------+

예제 : 부서 번호가 20인 직원 정보만 조회할 수 있도록 별칭을 설정한 뷰

CREATE VIEW emp_vu_20 AS
SELECT empno AS no, ename AS name, mgr, job
FROM emp
WHERE deptno = 20;
+------+-------+------+---------+
| no   | name  | mgr  | job     |
+------+-------+------+---------+
| 7369 | SMITH | 7902 | CLERK   |
| 7566 | JONES | 7839 | MANAGER |
| 7788 | SCOTT | 7566 | ANALYST |
| 7876 | ADAMS | 7788 | CLERK   |
| 7902 | FORD  | 7566 | ANALYST |
+------+-------+------+---------+

실습 : 사원번호, 사원이름, 급여, 연봉 정보를 조회할 수 있는 뷰 emp_sal을 생성

CREATE VIEW emp_sal AS
SELECT
   empno,
   ename,
   sal,
   (sal * 12 + IFNULL(comm, 0)) AS annual_salary
FROM emp;
  • 실제 테이블에는 존재하지 않는 연봉 정보를 쉽게 조회하고 계산할 수 있도록 하여, 급여 데이터를 보다 효율적으로 관리하는 데 유용

실습 : 사원번호, 사원이름, 담당업무, 연봉 정보, 호봉을 조회할 수 있는 emp_salgrade 뷰를 생성

CREATE VIEW emp_salgrade AS
SELECT
    e.empno,
    e.ename,
    e.job,
    (e.sal * 12 + IFNULL(e.comm, 0)) AS annsal,
    s.grade
FROM emp e
INNER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

SELECT * FROM emp_salgrade;
+-------+--------+-----------+----------+-------+
| empno | ename  | job       | annsal   | grade |
+-------+--------+-----------+----------+-------+
|  7369 | SMITH  | CLERK     |  9600.00 |     1 |
|  7499 | ALLEN  | SALESMAN  | 19500.00 |     3 |
|  7521 | WARD   | SALESMAN  | 15500.00 |     2 |
|  7566 | JONES  | MANAGER   | 35700.00 |     4 |
|  7654 | MARTIN | SALESMAN  | 16400.00 |     2 |
|  7698 | BLAKE  | MANAGER   | 34200.00 |     4 |
|  7782 | CLARK  | MANAGER   | 29400.00 |     4 |
|  7788 | SCOTT  | ANALYST   | 36000.00 |     4 |
|  7839 | KING   | PRESIDENT | 60000.00 |     5 |
|  7844 | TURNER | SALESMAN  | 18000.00 |     3 |
|  7876 | ADAMS  | CLERK     | 13200.00 |     1 |
|  7900 | JAMES  | CLERK     | 11400.00 |     1 |
|  7902 | FORD   | ANALYST   | 36000.00 |     4 |
|  7934 | MILLER | CLERK     | 15600.00 |     2 |
+-------+--------+-----------+----------+-------+

  • 이 뷰는 emp 테이블과 salgrade 테이블을 조인하여, 사원번호, 사원이름, 담당업무, 연봉, 호봉 정보를 조회할 수 있게 설정되었습니다.


뷰 정의 확인하기

  • 현재 데이터베이스의 모든 뷰 목록 확인
SHOW FULL TABLES WHERE Table_type = 'VIEW';
  • 특정 데이터베이스의 모든 뷰 정보 확인 
SELECT *
FROM information_schema.views
WHERE table_schema = '데이터베이스이름';

-- 예시
SELECT TABLE_NAME AS view_name,
       VIEW_DEFINITION AS view_definition
FROM information_schema.views
WHERE table_schema = 'sample';
  • 특정 뷰의 생성 쿼리 확인 
SHOW CREATE VIEW 뷰이름 (\G);

인라인 뷰

서브쿼리를 이용해 임시로 생성한 가상의 테이블을 말하며, 주로 SELECT 문 내에서 사용되는 일회성 뷰. 인라인 뷰는 데이터베이스에 영구적으로 저장되지 않으며, 특정 쿼리에서만 일시적으로 사용됩니다.

  • 데이터 그룹화:
    • 복잡한 그룹화 결과를 기반으로 추가 연산을 할 때 유용합니다. 예를 들어, 부서별 평균 급여보다 높은 급여를 받는 직원만 조회하는 경우 인라인 뷰로 부서별 평균 급여를 계산한 후 이를 조인할 수 있습니다.
  • 집계 함수 활용:
    • SUM, MAX, AVG와 같은 집계 함수를 사용해 요약된 데이터를 임시로 가공하고, 이를 기반으로 추가 필터링이나 계산을 수행할 수 있습니다.
  • 조건부 데이터 생성:
    • 특정 조건에 맞는 데이터 집합을 인라인 뷰로 만들어서 여러 조건을 적용하여 데이터를 필터링하고 정렬하는 데 적합합니다.
  • 예시
SELECT e.empno, e.ename, d.deptno, d.dname
FROM (SELECT * FROM emp WHERE deptno IN (10, 20)) e
INNER JOIN dept d
ON e.deptno = d.deptno;
  • 인라인 뷰 (SELECT * FROM emp WHERE deptno IN (10, 20)) e
    • 설명: 이 부분은 인라인 뷰로서, emp 테이블에서 부서 번호(deptno)가 10 또는 20인 직원만 필터링하여 선택합니다.
    • 별칭 e: 인라인 뷰에 별칭 e를 지정하여, 외부 쿼리에서 가상의 테이블로 사용할 수 있게 했습니다.
    • 결과: 부서가 10 또는 20인 직원만 포함된 임시 테이블 e가 생성됩니다
  • INNER JOIN dept d ON e.deptno = d.deptno
    • 조인 설명: e 인라인 뷰와 dept 테이블을 deptno를 기준으로 내부 조인(INNER JOIN)합니다. 즉, e.deptno와 d.deptno`가 같은 행만 결과에 포함됩니다.

CREATE OR REPLACE

CREATE OR REPLACE VIEW emp_sal AS
SELECT empno, ename, job, sal * 12 + IFNULL(comm, 0) AS annsal
FROM emp;
  • emp_sal이라는 뷰를 생성하거나, 이미 존재한다면 기존 뷰를 새 정의로 교체합니다.
  • 이를 통해 기존 뷰가 있을 경우 DROP 없이도 수정 가능합니다.
  • CREATE OR REPLACE VIEW를 통해 뷰를 재정의할 수 있으므로, emp 테이블 구조나 연봉 계산 방식이 변경되면 뷰를 쉽게 수정할 수 있습니다.
profile
y♡ding
이전 포스트

[Day 12 | DB] 제약조건 수정하기

다음 포스트

[Day 12 | DB] 데이터베이스 사용자

0개의 댓글