[SQL] MYSQL 뷰(View) 생성, 수정, 삭제

✍️ Study Note

 

 

 

---

 

 

뷰 (VIEW)

 

뷰 생성 

• CREATE 뷰이름 AS SELECT 문
• CREATE OR REPLACE 뷰이름 AS SELECT 문 : 기존에 있는 뷰일 경우, 새로운 정보로 대체됨(Replace)
• 임시테이블과 비슷한 역할. 
• 장점 : 정보 접근권한 제한을 통해 보안 강화 / 복잡한 쿼리 단순화
• 뷰 생성 시, 집계함수(SUM, AVG, etc), UNION, GROUP BY, DISTINCT, JOIN 등 사용 가능

-- 뷰 생성
-- #1
USE TABLEDB;
CREATE OR REPLACE VIEW V_USERBUYTBL
AS 
	SELECT U.USERID AS 'USERID', U.NAME AS 'USERNAME', 
    B.PRODNAME AS 'PRODUCTNAME', U.ADDR, CONCAT(U.MOBILE1, U.MOBILE2) AS 'MOBILE PHONE'
    FROM USERTBL U LEFT JOIN BUYTBL B
    ON U.USERID = B.USERID;
SELECT * FROM V_USERBUYTBL;
SELECT USERID, USERNAME FROM V_USERBUYTBL;


-- #2. 집계함수 사용
CREATE VIEW V_SUM
AS SELECT USERID AS 'USERID', SUM(PRICE*AMOUNT) AS 'TOTAL'
    FROM BUYTBL GROUP BY USERID;

 

뷰 수정, 삭제

• ALTER VIEW 뷰이름 AS SELECT 문 : 수정
• DROP VIEW 뷰이름 : 삭제

-- 뷰 수정
ALTER VIEW V_USERBUYTBL
AS
	SELECT U.USERID AS '사용자 아이디', U.NAME AS '사용자 이름', 
    B.PRODNAME AS '제품명', U.ADDR, CONCAT(U.MOBILE1, U.MOBILE2) AS '휴대전화'
    FROM USERTBL U LEFT JOIN BUYTBL B
    ON U.USERID = B.USERID;

-- 뷰 삭제
DROP VIEW V_USERBUYTBL;

 

뷰 정보 조회 

• DESCRIBE 뷰이름 : 열 이름, 데이터 타입, NULL, KEY 조회
• SHOW CREATE VIEW 뷰이름 : VIEW가 만들어진 쿼리 조회 -> 기존 테이블 정보 확인 가능

-- 뷰 정보 조회
DESCRIBE V_USERBUYTBL;

-- 뷰 소스코드 확인 (기존 테이블)
SHOW CREATE VIEW V_USERBUYTBL;

 

뷰 데이터 수정, 입력

• UPDATE 뷰이름 SET 수정내용 WHERE 조건
• INSERT INTO 뷰이름 (열이름) VALUES (업로드값)

 

• 뷰를 통해 기존 테이블에 데이터 입력하기 위한 조건

1. 기존 테이블의 NOT NULL 열이 뷰에 모두 포함 -> 데이터가 제대로 입력되면 데이터 입력 가능
2. 기존 테이블의 NOT NULL 열이 뷰에 미포함 -> 데이터가 입력되지 않아도 에러 없도록 수정 필요
3. 에러 방지 방법 : NOT NULL 열의 DEFAULT 값 설정 / NOT NULL 조건 -> NULL 조건으로 변경
4. WITH CHECK OPTION : 뷰를 통해 데이터 입력 시, 조건에 맞는 데이터만 입력됨.

 

• '기존 테이블의 열을 수정하여 만든 뷰'를 통해서는 기존테이블의 데이터 수정, 삭제가 불가능하다. (집계함수, UNION, GROUP BY, DISTINCT, JOIN)

 

-- 뷰 데이터 입력
INSERT INTO V_USERTBL(USERID, NAME, ADDR) VALUES ('KBM', '김병만', '충북');


-- 뷰 데이터 수정
CREATE OR REPLACE VIEW V_USERTBL
AS SELECT USERID, NAME, ADDR FROM USERTBL;
UPDATE V_USERTBL SET ADDR = '부산' WHERE USERID = 'JKW';


-- WITH CHECK OPTION : 뷰를 통해 데이터 입력 시, 조건에 맞는 데이터만 입력가능
ALTER VIEW V_HEIGHT177
AS SELECT * FROM USERTBL WHERE HEIGHT >= 177
    WITH CHECK OPTION;