10. 视图

USE world;

-- 查询国家名-首都名列表
SELECT country.name AS '国家名字',city.Name AS '首都名字' FROM country,city WHERE country.capital = city.id;

-- 创建视图 语法:
-- create view 视图的名字
-- as 
-- Select语句

CREATE OR REPLACE VIEW country_captial_view
AS 
SELECT country.name AS 'countryName',city.Name AS 'cityname' FROM country,city WHERE country.capital = city.id;
-- with check opntion; 检查属性

-- 删除表
DROP VIEW country_captial_view;

-- ALGORITHM = MERGE       视图语句的文本视图定义合并起来
-- ALGORITHM = TEMPTABLE   把结果放在临时表中
-- ALGORITHM = UNDEFINED   MySQL自己选择

-- 通过视图查询
SELECT * FROM country_captial_view;
SELECT * FROM country_captial_view WHERE countryName = 'China';
-- 通过视图修改了表中的数据
UPDATE country_captial_view SET cityname = 'wuxi' WHERE countryName = 'China';

-- 实际上是修改的下层数据表的数据.
-- 验证一下底层表数据有没有修改
SELECT * FROM country WHERE country.name = 'China'; -- 1891
-- 
SELECT * FROM city WHERE city.id = 1891; -- name 被改为了wuxi


-- [例子]
CREATE TABLE t1(id INT);
INSERT INTO t1 VALUES(1),(2),(3),(4),(5),(6),(7),(8);
SELECT * FROM t1;

-- 没有检查选项
CREATE VIEW v1 AS SELECT * FROM t1 WHERE id<4;
SELECT * FROM v1;

INSERT INTO v1 VALUES(9);
SELECT * FROM t1; -- 可以插入

-- 1. WITH LOCAL CHECK OPTION 当前约束条件生效
CREATE VIEW v2 AS SELECT * FROM t1 WHERE id<4 WITH LOCAL CHECK OPTION;
INSERT INTO v2 VALUES(10); -- 插不进去了

CREATE VIEW v3 AS SELECT * FROM v2 WITH LOCAL CHECK OPTION; -- WITH LOCAL CHECK OPTION 当前约束条件生效
INSERT INTO v3 VALUES(11); -- 可以插进去

-- 2. WITH CASCADED CHECK OPTION 继承上一视图约束条件
CREATE VIEW v4 AS SELECT * FROM v1 WITH CASCADED CHECK OPTION; -- WITH CASCADED CHECK OPTION 继承上一视图约束条件
INSERT INTO v4 VALUES(11); -- 插入失败  

-- 3. WITH CHECK OPTION 在任何时刻都要进行匹配
CREATE VIEW v5 AS SELECT * FROM v1 WITH CHECK OPTION;
INSERT INTO v5 VALUES(11); -- 插入失败  

CREATE VIEW v6 AS SELECT * FROM v1 WITH id <4 WITH CHECK OPTION;
INSERT INTO v6 VALUES(11); -- 插入失败  


-- 练习
-- 选课系统choose数据库
--