데이터정의어 (DDL) — View
목차
📍 View 오브젝트
📍 View 오브젝트
– 하나 이상의 Base table을 기반으로 생성은 되었으나 물리적으로 존재하지 않고,
Data Dictionary에 Select 구문 형태로 정의만 되어 있는 가상의 논리적인 테이블.
→ 실질적인 데이터를 저장하는 테이블 오브젝트와는 반대되는 개념.
– 테이블에서 데이터를 가져와서 보여주는 것.
1. View 생성
CREATE VIEW (view 이름) AS
SELECT 컬럼명
FROM 테이블명
– 출력할 내용을 select구문으로 먼저 작성하고 맨 위에 CREATE + view이름 + AS 키워드 작성함.
| EX1 ) |
CREATE VIEW empvu80
AS
SELECT
employee_id,
last_name,
salary
FROM
employees
WHERE
department_id = 80;DESC empvu80;SELECT * FROM empvu80;– 데이터베이스는 'hr'을 사용함.
✓ 실행 결과
| EX2 ) |
CREATE VIEW salvu50
AS SELECT employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;SELECT * FROM salvu50;– 뷰 생성 시, 서브쿼리에 column alias가 있다면 컬럼명이 column alias로 지정됨.
– 다만 함수나 표현식을 출력하는 경우, column alias가 옵션이 아닌 필수로 사용됨.
✓ 실행 결과
2. View 생성 및 수정
CREATE OR REPLACE VIEW (view 이름) AS
SELECT 컬럼명
FROM 테이블명
• OR REPLACE
– view 이름이 겹치지 않으면 새롭게 만들고, 이름이 겹치면 기존 것을 대체하는 문법.
| EX ) |
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS SELECT employee_id,
CONCAT(first_name, ' ', last_name), salary,
department_id
FROM employees
WHERE department_id = 80;– 기존 동일한 이름의 뷰가 있다면 지금 실행하는 구문의 내용으로 갱신.
– 컬럼 리스트를 작성한다면 서브쿼리가 돌려주는 컬럼명과 column alias를 무시하고,
컬럼 리스트의 값으로 view의 컬럼명을 지정하게 됨.
✓ 실행 결과
3. View 데이터 수정 및 조회
UPDATE 테이블명 SET 컬럼명 = 변경할 값
WHERE 조건식;
– VIEW는 데이터베이스가 되는 곳에 데이터를 저장하기 때문에 최신 내용이 반영 (= 동기화)됨.
| EX1 ) |
• 뷰 생성
CREATE VIEW dept236 AS
SELECT employee_id, last_name, department_id
FROM hr.employees
WHERE department_id IN (20, 30, 60);
• 뷰 데이터 수정
UPDATE hr.employees
SET last_name = 'Fayyy'
WHERE employee_id = 202;
• 데이터 변경 확인하기
– 최신의 내용을 반영하는지 안하는지(동기화) 확인하기 위해 다시 조회해봄.
– view의 데이터베이스가 되는 곳에 데이터를 저장하기 때문에 동기화 됨을 알 수 있음.
SELECT employee_id, last_name
FROM hr.employees
WHERE employee_id = 202;✓ 실행 결과
| EX2 ) |
• 뷰 생성
CREATE VIEW avg_sal AS
SELECT department_id, MAX(salary) AS MAX, MIN(salary) AS MIN ,AVG(salary) AS AVG
FROM employees
GROUP BY department_id;
• 데이터 수정
UPDATE hr.employees
SET salary = salary * 1.1
WHERE department_id = 90;
• 데이터 변경 확인하기
SELECT * FROM avg_sal;– 변경 잘 됨.
✓ 실행 결과
• JOIN 사용
SELECT employee_id, last_name, salary, e.department_id, MAX, MIN, AVG
FROM employees e JOIN avg_sal asal
ON e.department_id = asal.department_id;– 각 사원이 속해있는 부서의 연봉 최대 최소 평균을 한번에 알 수 있음.
– 사원의 정보가 변할때마다 최신정보를 유지하게되는 가상의 테이블이 생성됨.
✓ 실행 결과
4. VIEW를 사용한 DML 작업
– view를 통한 DML작업은 가능하지만,
베이스 테이블의 제약조건 들을 고려해야하기 때문에 그냥 테이블을 다루는 것보다는 어려움.
– 특히나 베이스 테이블이 둘 이상인 경우 DML 작업은 거의 불가능 할 수 있음.
| EX ) 잘 되는 경우 & 안되는 경우 |
1 ) 잘 되는 경우
• 뷰 생성
CREATE VIEW dept_view AS
SELECT department_id, department_name
FROM hr.departments;
• 데이터 입력
INSERT INTO dept_view
VALUES (310, 'Rainy');-- 확인
SELECT * FROM departments;– 변경한 데이터는 departments 테이블, view에 저장되었음을 알 수 있음.
✓ 실행 결과
2 ) 안 되는 경우
• 뷰 생성
CREATE OR REPLACE VIEW dept_view AS
SELECT department_id, manager_id
FROM hr.departments;– view 이름이 겹치지 않으면 새롭게 만들고 이름이 겹치면 기존것을 대체하는 문법.
• 뷰 구조조회
DESC dept_view;✓ 실행 결과
• 데이터 입력
INSERT INTO dept_view
VALUES (320, 100);– 에러발생.
DESC departments;– view의 데이터베이스가 되는 departments 테이블의 구조조회를 해보니,
null값이 허용안되는 부서가 있음. (department_name)
– 해당 부서의 데이터를 입력하지 않았으므로 조작불가하여 에러 발생함.
✓ 실행 결과
5. VIEW 삭제
DROP VIEW (view 이름);