数据库管理工具之mysql的视图详解

视图 是虚表，不能实际存储数据(了解)

为什么要使用视图呢？

1.安全性

2.统计查询速度

视图的语法：

1.创建视图

CREATE [OR REPLACE] VIEW 视图名称[(字段列名)] AS SELECT 语句;

CREATE VIEW stu_view_v1 AS SELECT sno,sname,sage FROM student;

2.视图查看

SELECT * FROM 视图名;

SELECT * FROM stu_view_v1;

3.操作视图

INSERT INTO 视图名(列名) VALUES(值);

INSERT INTO stu_view_v1 VALUES(980001,tom,16);

SELECT * FROM student ;

CREATE VIEW stu_view_v2 AS SELECT sno,sname,sage FROM student WHERE sage>19;

SELECT * FROM stu_view_v2;

INSERT INTO stu_view_v2 VALUES(980002,tom,17);

4.视图的更新

CREATE [OR REPLACE] VIEW 视图名称[(字段列名)] AS SELECT 语句;

CREATE or replace VIEW stu_view_v2 AS SELECT sno,sname,sage FROM student WHERE sage<19;

CREATE VIEW stu_view_v3 AS SELECT sno,sname,sage FROM stu_view_v2 WHERE sage>12 WITH CASCADED CHECK OPTION;

INSERT INTO stu_view_v3 VALUES(980004,kkk1,20);

SELECT * FROM stu_view_v3;

创建视图时如果有with CASCADED CHECK OPTION ，with LOCAL CHECK option

我们对视图进增删 改时，mysql为自检。

总结：

1.新建视图

create view view_名称 as select 语句 with cascaded check option;

2.查看视图

select * from 视图名

3.修改视图

create or replace view view_名称 as select 语句 with cascaded check option;

4.删除视图

drop view 视图名