Mysql(进阶篇)(三)——视图（存储对象）

 

 Cascaded 和local的区别

比如create view v2 as select id,name from v1 where id>10 with local check option;语句中v1是没有增加检查选项的

Cascaded 在检查是否满足v2条件之后还会检查是否满足v1的条件

local在检查是否满足v2条件之后，不会检查是否满足v1的条件，因为v1没有加检查选项

  

-- 创建视图
create or replace view stu_v_1 as select id,name from student where id <= 10;

-- 查询视图
show create view stu_v_1;

select * from stu_v_1;

select * from stu_v_1 where id < 3;

-- 修改视图
create or replace view stu_v_1 as select id,name,no from student where id <= 10;

alter view stu_v_1 as select id,name from student where id <= 10;


-- 删除视图
drop view if exists stu_v_1;


-- ----------------------------------------------------------------------------------------
create or replace view stu_v_1 as select id,name from student where id <= 10 with cascaded check option ;

select * from stu_v_1;

insert into stu_v_1 values(6,'Tom');

insert into stu_v_1 values(17,'Tom22');

-- cascaded
create or replace view stu_v_1 as select id,name from student where id <= 15 ;

insert into stu_v_1 values(5,'Tom');

insert into stu_v_1 values(16,'Tom');


create or replace view stu_v_2 as select id,name from stu_v_1 where id >= 10 with cascaded check option ;

insert into stu_v_2 values(13,'Tom');

insert into stu_v_2 values(17,'Tom');


create or replace view stu_v_3 as select id,name from stu_v_2 where id < 20 ;

insert into stu_v_3 values(14,'Tom');


-- local
create or replace view stu_v_4 as select id,name from student where id <= 15 with local check option;

insert into stu_v_4 values(5,'Tom');

insert into stu_v_4 values(16,'Tom');


create or replace view stu_v_5 as select id,name from stu_v_4 where id >= 10 with local check option ;

insert into stu_v_5 values(13,'Tom');

insert into stu_v_5 values(17,'Tom');

insert into stu_v_5 values(18,'Tom');

create or replace view stu_v_6 as select id,name from stu_v_5 where id < 20 ;

insert into stu_v_6 values(14,'Tom');



-- 创建视图, 使用聚合函数
create view stu_v_count as select count(*) from student;

insert into stu_v_count values(10);


-- 案例 :
-- 1. 为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏蔽手机号和邮箱两个字段。
create view tb_user_view as select id,name,profession,age,gender,status,createtime from tb_user;

select * from tb_user_view;

-- 2. 查询每个学生所选修的课程（三张表联查），这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图。

create view tb_stu_course_view as select s.name student_name , s.no student_no , c.name course_name from student s, student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id;

select * from tb_stu_course_view;