视图的基本操作——笔记用

# 视图
# 创建视图
use base;
# CREATE [ORREPLACE] VIEW view_name(column_name) AS SELECT语句[WITH [CASCADED|LOCAL] CHECK OPTION];
create view st_v as select id, name from stu where id> 2;

# 查询视图
# 查看创建视图语句：SHOW CREATE VIEW view_name;
# 查看视图数据：SELECT * FROM view_name;
show create view st_V;
select * from st_V;

# 修改视图
# 法一：CREATE [OR REPLACE] VIEW view_name(column_name) AS SELECT语句 [WITH [CASCADED|LOCAL] CHECK OPTION];
# 法二：ALTER VIEW view_name(column_name) AS SELECT语句 [WITH [CASCADED|LOCAL] CHECK OPTION];
create or replace view st_V as select id, name, gender from stu where id > 1;
alter view st_v as select id, name, gender from stu where id > 1;

#删除视图
# DROP VIEW [IF EXISTS] view_name;
drop view if exists st0;

# 视图检查选项

-- CASCADED：修改视图时，需要满足有此视图的父视图且的包含此视图的条件，不用满足子视图的条件
create view worker_v_1 as select id, name from worker where id < 20;
create view worker_V_2 as select id, name from worker_v_1 where id >10 with cascaded check option;
create view worker_v_3 as select id, name from worker_v_2 where id <15;

insert into worker_v_2(id,name) values(3, '美雪'); # 插入失败 原因：v_2视图中，where id > 10
insert into worker_v_2(id,name) values(30, '美雪'); # 插入失败 原因：v_1视图中，where id < 30
insert into worker_v_2 values(19, '美雪'); # 插入成功，符合v_1和v_表的条件

insert into worker_v_3 values(11,'直人'); # 插入成功 符合v_3、v_2、v_1视图中条件
insert into worker_v_3 values(17, 'ES'); # 插入成功 符合v_2、v_1视图中条件
insert into worker_v_3 values(7, 'HIBIKI'); # 插入失败 不符合v_2视图中条件(where id > 10)
insert into worker_v_3 values(21, 'MAI');# 插入失败 不符合 v_1视图中条件(where id < 20)


-- LOCAL：修改视图时，需要满足此视图与父视图中包含检查选项的视图，只用检查父视图中有检查选项的条件，不用满足没有检查选项的父视图条件，不用满足子视图的条件

create or replace view worker_v_4 as select id, name from worker where id < 15;
insert into worker_v_4 values(16, 'izanami'); # 插入成功 v_4没有检查选项,所以不用满足v_4条件但视图里没有id为16的数据

create or replace view worker_v_5 as select id, name from worker_v_4 where id > 10 with local check option;
insert into worker_v_5 values(3, '123'); # 插入失败 不符合v_5条件
insert into worker_v_5 values(17, '098'); # 插入成功 符合v_5条件，由于v_4没有检查选项，所以不用满足v_4的条件

create or replace view worker_v_6 as select id, name from worker_v_5 where id <20 ;
insert into worker_v_6 values(14, '877'); # 插入成功，不用满足v_6、v_4条件，而v_5条件满足
insert into worker_v_6 values(9,'798'); # 插入失败，不满足v_5条件

# 要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果包含以下任何一项，则视图不可更新：
# 1.聚合函数或窗口函数(SUM()、MIN()、MAX()、COUNT()等)
# 2. DISTINCT
# 3.GROUP BY
# 4.HAVING
#5. UNION或则UNION ALL

create or replace view stu_v as select count(*) from stu;
insert into stu_v values(10); # 插入失败，使用了聚合函数


# 视图案列

-- 1.屏蔽手机号和邮箱两个字段(tb_user)
create or replace view tb_uset_v_1 as select id, name, profession, age, gender, status, createtime from tb_user;

-- 2.三表联查(stud,courses,stud_courses)
create or replace view stud_courses_v_1 as select s.name as student_name, s.no as student_no, c.name as courses_name from stud s, courses c, stud_courses sc where s.id = sc.stuId and c.id = sc.coursesId;
select * from stud_courses_v_1;