CREATE VIEW ビューの作成 AS SELECT カラム名 FROM テーブル名 WHERE 条件;
ALTER TABLE tb ADD bikou VARCHAR(100); UPDATE tb SET bikou='制約なし'; SELECT * FROM tb;
★
CREATE TABLE tb1L SELECT * FROM tb1; CREATE VIEW v1 AS SELECT nama,tosi FROM tb1L; SELECT * FROM v1;
★
UPDATE v1 SET nama='主任・佐藤' WHERE nama='佐藤';
★
CREATE VIEW v2 AS SELECT tb.bang,tb1L.nama,tb.uria FROM tb JOIN tb1L USING(bang) WHERE tb.uria>=100; SELECT * FROM v2;
★
UPDATE tb SET uria=777 WHERE uria=54; SELECT * FROM v2;
★
SHOW TABLES;
DESC ビュー名;
★
DESC v2;
SHOW CREATE VIEW ビュー名;
★
INSERT INTO v1 VALUES('アルバイト・石田',18); SELECT * FROM v1; SELECT * FROM tb1L;
★
CREATE VIEW v3 AS SELECT bang,uria FROM tb WHERE uria>=100; SELECT * FROM v3;
★
INSERT INTO v3 VALUES('意地悪',50); SELECT * FROM v3; SELECT bang,uria FROM tb;
★
CREATE VIEW v4 AS SELECT bang,uria FROM tb WHERE uria>100 WITH CHECK OPTION; INSERT INTO v4 VALUES ('意地悪',50);
★
CREATE OR REPLACE VIEW v1 AS SELECT NOW();
ALTER VIEW ビュー名 AS SELECT カラム名 FROM テーブル名;
★
ALTER VIEW v1 AS SELECT nama,tosi FROM tb1;
DROP VIEW ビューの名前
DROP VIEW IF EXISTS v1;
次のテーブル「tb」で、カラム「uria」が50以上で、「bang」ごとの「uria」の平均で120以上のものだけを、「uria」の平均が多いものから表示するビュー「v_uria」を作成してください。
CREATE VIEW v_uria AS SELECT bang,AVG(uria) FROM tb WHERE uria>=50 GROUP BY bang HAVING AVG(uria)>=120 ORDER BY AVG(uria) DESC;