[SQL]-ビュー

1.ビュー

CREATE VIEW 뷰_이름
AS
SELECT 문;

USE market_db;
CREATE VIEW v_member
AS
SELECT mem_id, mem_name, addr FROM member;

SELECT mem_name, addr FROM v_member
WHERE addr IN('서울', '경기');

2.ビューを使用する理由

3.ビューの実際の操作

ビューをクエリーするときにカラム名にスペースがある場合は、backtic(`)に囲む必要があります。

USE market_db;
CREATE VIEW v_viewtest1
AS
SELECT b.mem_id "Member ID", m.mem_name AS "Member Name",
b.prod_name "Product Name",
CONCAT(m.phone1, m.phone2) AS "Office Phone"
FROM buy b
INNER JOIN member m
ON b.mem_id = m.mem_id;


SELECT DISTINCT `Member ID`, `Member Name` FROM v_viewtest1;

ALTER VIEW v_viewtest1
AS
SELECT b.mem_id '회원 아이디', m.mem_name AS '회원 이름',
b.prod_name "제품 이름",
CONCAT(m.phone1, m.phone2) AS '연락처'
FROM buy b
INNER JOIN member m
ON b.mem_id = m.mem_id;

SELECT DISTINCT `회원 아이디`, `회원 이름` FROM v_viewtest1;

DROP VIEW v_viewtest1;

CREATE O REPLACE VIEWは既存のビューを上書きする効果があります

USE market_db;
CREATE OR REPLACE VIEW v_viewtest2
AS
SELECT mem_id, mem_name, addr FROM member;

DESCRIBE V_VIEWTEST2;

ビューソースコードの確認

SHOW CREATE VIEW V_VIEWTEST2;

UPDATE v_member SET addr = '부산' where mem_id='BLK';

CREATE VIEW v_height167
AS
SELECT * FROM member WHERE height >= 167;

select * FROM v_height167;

WITH CHECK OPTIONを使用して、ビューのキーが167より大きいため、入力データが167を超えることを確認します。

ALTER VIEW v_height167
AS
SELECT * FROM member WHERE height >= 167
WITH CHECK OPTION;

INSERT INTO v_height167 VALUES('TOB', '텔레토비', 4, '영국', NULL, NULL, 140, '1995-01-01');

ビューが参照されている場合でも、テーブルは削除されます。