Day08. 뷰 테이블

뷰(VIEW)

뷰는 사용자 입장에서 테이블과 동일하게 사용하는 개체다.

USE sqlDB;
CREATE VIEW v_userbuytbl
AS
SELECT U.userid AS 'USER ID', U.name AS 'USER NAME', B.prodName AS 'PRODUCT NAME',
        U.addr, CONCAT(U.mobile1, U.mobile2) AS 'MOBILE PHONE'
FROM usertbl U
         INNER JOIN buytbl B
                    ON U.userid = B.userid;

SELECT `USER ID`, `USER NAME` FROM v_userbuytbl; -- 주의! 백틱을 사용한다.

 

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
         INNER JOIN buytbl B
                    ON U.userid = B.userid ;

SELECT `이름`,`전화 번호` FROM v_userbuytbl;

 

DROP VIEW v_userbuytbl;

CREATE OR REPLACE VIEW v_usertbl
AS
SELECT userid, name, addr FROM usertbl;

DESCRIBE v_usertbl;
SHOW CREATE VIEW v_usertbl;
UPDATE v_usertbl SET addr = '부산' WHERE userid='JKW' ;

select * from v_usertbl;
select * from usertbl;

 

INSERT INTO v_usertbl(userid, name, addr) VALUES('KBM','김병만','충북') ;
-- birth Year열은 NOT NULL 설정 insert가 안됨

CREATE VIEW v_sum
AS
SELECT userid AS 'userid', SUM(price*amount) AS 'total'
FROM buytbl GROUP BY userid;

SELECT * FROM v_sum;

CREATE VIEW v_height177
AS
SELECT * FROM usertbl WHERE height >= 177 ;

SELECT * FROM v_height177 ;
select * from usertbl;

DELETE FROM v_height177 WHERE height < 177 ;

ALTER VIEW v_height177
AS
SELECT * FROM usertbl WHERE height >= 177
    WITH CHECK OPTION ;

INSERT INTO v_height177 VALUES('KBM', '김병만', 1977 , '경기', '010', '5555555', 158, '2023-01-01');