[Database] 뷰(View)

뷰(View)

---

1. 뷰(View)

• 뷰는 테이블과 똑같은 모습을 하고 있는 가상의 테이블
• 뷰는 기본적으로 읽기 전용으로 많이 사용하지만, 뷰를 통해서 테이블의 데이터를 수정 가

2. 뷰의 생성

• 뷰를 생성할 때는 CREATE VIEW 구문을 사용

CREATE [OR REPLACE] VIEW 뷰 이름 
AS 서브 쿼리
[WITH CHECK OPTION];

-- VIEW 실습
#1 뷰 생성
CREATE VIEW v_employee
AS SELECT e.emp_id,
	e.emp_name,
	d.dept_title,
	j.job_name,
	e.hire_date
FROM employee e
LEFT OUTER JOIN department d ON e.dept_code = d.dept_id
LEFT OUTER JOIN job j ON e.job_code = j.job_code;

#1

---

-- 사원의 사번, 직원명, 성별, 급여를 조회할 수 있는 뷰를 생성
#2
# OR REPLACE : 같은 이름의 뷰가 존재한다면 해당 뷰의 내용을 해당 쿼리문으로 덮어씀
# SELECT 쿼리에 함수나, 산술연산이 기술되어 있는 경우 별칭을 지정해야 한다.

CREATE OR REPLACE VIEW v_employee
AS SELECT emp_id,
	emp_name,
	if(SUBSTRING(emp_no, 8, 1)='1', '남자', '여자') AS gender,
	salary
FROM employee;

SELECT * FROM v_employee;

3. 뷰의 수정

• 뷰를 수정할 때는 ALTER VIEW 구문을 사용

ALTER VIEW 뷰 이름 AS 서브 쿼리;

-- 뷰 수정

ALTER VIEW v_userbuy
AS SELECT u.userid,
	u.NAME,
	b.prodName,
	u.addr,
	CONCAT(u.mobile1, u.mobile2) AS 'mobile'
FROM usertbl u
INNER JOIN buytbl b ON u.userId = b.userID
;

4. 뷰의 삭제

• 뷰를 삭제할 때는 DROP VIEW 구문을 사용

DROP VIEW 뷰 이름;

 

5. 뷰의 DML 작업

• DML 작업이 가능한 경우

-- 뷰를 이용해서 DML(INSERT, UPDATE, DELETE) 사용

#1 v_job 생성

CREATE OR REPLACE VIEW v_job
AS SELECT *
	FROM job
;

 

#1 v_job 뷰 생

---

#2 VIEW INSERT (job_code = 'J8', job_name = '알바' 데이터 삽입)
INSERT INTO v_job VALUES('J8', '알바');

SELECT * FROM v_job;
SELECT * FROM job

#2 view에 데이터가 삽입됨

 

#3 원본 테이블에도 데이터가 삽입

---

#3 VIEW UPDATE (J8 직급명을 인턴으로 변경)

UPDATE v_job
SET job_name = '인턴'
WHERE job_code = 'J8'
;

#3 데이터의 내용이 변경됨

#3 원본 테이블 값도 함께 변경

---

#4 VIEW DELETE (J8 직급명인 데이터를 삭제)

DELETE
FROM v_job
WHERE job_code = 'J8'
;

#4 데이터가 삭제됨

#4 원본 테이블의 데이터도 함께 삭제

• DML 조작이 불가능한 경우

1. 뷰 정의에 포함되지 않는 열을 조작하는 경우

#1 v_job 뷰 생성

CREATE OR REPLACE VIEW v_job
AS SELECT job_code
	FROM job
;

-- 1.INSERT
#1 VIEW에 정의된 열에 값을 삽입하는 것은 불가
#  제약 조건에 위배되는 값을 넣어도 에러 발생
INSERT INTO v_job VALUES('J8', '인턴');

#2 데이터 삽입이 가능
INSERT INTO v_job VALUES('J8');

---

-- 2.UPDATE
#1 v_job 뷰는 job_code만 접근할 수 있기 때문에 에러발생
UPDATE v_job
SET job_name = '인턴'
WHERE job_code = 'J8'
;

#2 데이터 수정이 가능
UPDATE v_job
SET job_code = 'J0'
WHERE job_code = 'J8'
;

---

-- 3.DELETE
#1 v_job 뷰는 job_code만 접근할 수 있기 때문에 에러발생
DELETE
FROM v_job
WHERE job_name IS NULL
;

#2
DELETE 
FROM v_job
WHERE job_code = 'J0'
;

 

 

2. 산술 표현법으로 정의 된 경우

#1 v_emp_salary 뷰 생성

-- 사원의 연봉 정보를 조회하는 뷰
CREATE VIEW v_emp_salary
AS SELECT emp_id,
			emp_name,
			emp_no,
			salary * 12 AS 'salary'
	FROM employee
;

---

-- 1.INSERT
#1 산술 연산으로 정의된 컬럼은 데이터 삽입이 불가능
INSERT INTO v_emp_salary
VALUES ('100', '홍길동', '940523-1111111', '30000000');

#2 산술 연산과 무관한 컬럼은 데이터 삽입 가능
INSERT INTO v_emp_salary(emp_id, emp_name, emp_no)
VALUES ('100', '홍길동', '940523-1111111');

---

-- 2.UPDATE
#1. 산술 연산으로 정의된 열은 데이터 변경 불가능
UPDATE v_emp_salary
SET salary = 30000000
WHERE emp_id = '100'
;
#2. 산술 연산과 무관한 열은 데이터 변경 가능
UPDATE v_emp_salary
SET emp_name = '고길동'
WHERE emp_id = '100'
;

---

-- 3.DELETE

# 가상으로 설정된 값을 입력하고 변경하는 것이 아닌
# 조회해서 삭제하는 것은 가능
DELETE
FROM v_emp_salary
WHERE salary = 36000000;

 

 

3. 그룹 함수나 GROUP BY  절을 포함한 경우(DISTINCT도 동일)

#1 v_emp_salary 뷰 생성
CREATE OR REPLACE VIEW v_emp_salary
	AS SELECT dept_code,
				FLOOR(SUM(salary)) AS 'sum',
				floor(AVG(salary)) AS 'avg'
		FROM employee
		GROUP BY dept_code
;

---

-- 1.INSERT
#1 그룹 함수로 생성된 열은 가상의 열이기때문에 삽입이 불가능
INSERT INTO v_emp_salary VALUES('D7', 8000000, 4000000);
#2 GROUP BY로 생성된 테이블의 dept_code열에는 삽입이 불가능
INSERT INTO v_emp_salary(dept_code) VALUES('D7');

---

-- 2.UPDATE
#1 그룹 함수로 생성된 열은 가상의 열이기때문에 수정이 불가능
UPDATE v_emp_salary
SET SUM = 6000000
WHERE dept_code = 'D2'
;

#2 GROUP BY로 생성된 테이블의 dept_code 열은 수정이 불가능
#	원본 테이블의 해당 dept_code인 열이 모두 수정 되기 때문
UPDATE v_emp_salary
SET dept_code = 'D7'
WHERE dept_code = 'D2'
;

---

-- 3.DELETE
#1 GROUP BY로 생성된 테이블의 dept_code 열은 삭제가 불가능
#	원본 테이블의 해당 dept_code인 열이 모두 삭제 되기 때문

DELETE
FROM v_emp_salary
WHERE dept_code = 'D2'
;

 

 

4. JOIN을 이용해 여러 테이블을 연결한 경우

-- 1.INSERT
#1. 에러발생
INSERT INTO v_emp_dept VALUES
(100, '홍길동', '000523-3123456',  '해외영업3부');

#2 에러발생
INSERT INTO v_emp_dept VALUES
(100, '홍길동', '000523-3123456',  '총무부');

#3 에러발생
INSERT INTO v_emp_dept(emp_id, emp_name, emp_no) VALUES
(100, '홍길동', '000523-3123456');

---

-- 2.UPDATE
#1 실행 가능
UPDATE v_emp_dept
SET dept_title = '총무1팀'
WHERE emp_id = '200'
;

#2 실행 가능
UPDATE v_emp_dept
SET emp_name = '서동일'
WHERE emp_id = '200'
;

---

-- 3.DELETE
#1 오류 발생
DELETE
FROM v_emp_dept
WHERE emp_id = '202'
;