MySQL学习笔记（11）——创建视图

11. 视图

11.1 创建视图

创建视图的语法
创建视图使用CREATE VIEW语句，基本语法格式如下：

create [or replace] [algorithm={undefined|merge|TEMPTABLE}]
VIEW　view_name [(column_list)]
as select_statement
[with[cascaded|local]check option]

其中，CREATE表示创建新的视图；REPLACE表示替换已经创建的视图；ALGORITHM表示视图选择的算法；view_name为视图的名称，column_list为属性列；SELECT_statement表示SELECT语句；WITH [CASCADED | LOCAL] CHECKOPTION参数表示视图在更新时保证在视图的权限范围之内。
ALGORITHM的取值有3个，分别是UNDEFINED |MERGE | TEMPTABLE。其中，UNDEFINED表示MySQL将自动选择算法；MERGE表示将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分；TEMPTABLE表示将视图的结果存入临时表，然后用临时表来执行语句。
CASCADED与LOCAL为可选参数，CASCADED为默认值，表示更新视图时要满足所有相关视图和表的条件；LOCAL表示更新视图时满足该视图本身定义的条件即可。
该语句要求具有针对视图的CREATE VIEW权限，以及针对由SELECT语句选择的每一列上的某些权限。对于在SELECT语句中其他地方使用的列，必须具有SELECT权限。如果还有OR REPLACE子句，必须在视图上具有DROP权限。

• 在单表上创建视图
  【例】在t表上创建一个名为view_t的视图，代码如下：

# 首先创建基本表并插入数据
create table t(quantity INT,price int);
insert into t values(3,50);
# 创建视图语句为：
create VIEW view_t as select quantity, price , quantity*price from t;

【例】在t表格上创建一个名为view_t2的视图，代码如下：

create view view_t2(qty,price,total) as select quantity,price,quantity *price from t;

• 在多表上创建视图
  【例】在表student和表stu_info上创建视图stu_glass，代码如下：

create view stu_glass (id,name,glass) as select student.s_id,student.name,stu_info.glass
from student,stu_info where student.s_id=stu_info.s_id;

11.2 查看视图

查看视图是查看数据库中已存在的视图的定义。查看视图必须要有SHOW VIEW的权限，MySQL数据库下的user表中保存着这个信息。查看视图的方法包括DESCRIBE、SHOW TABLE STATUS和SHOW CREATE VIEW。

• 使用DESCRIBE语句查看视图基本信息
  describe/desc 视图名；
  【例】通过DESCRIBE语句查看视图view_t的定义，代码如下

DESCRIBE view_t;

• 使用SHOW TABLE STATUS语句查看视图基本信息
  show table status like '视图名'
  【例】使用SHOW TABLE STATUS命令查看视图信息，代码如下：

show table status like 'view_t' \G

• 使用SHOW CREATE VIEW语句查看视图详细信息

show create view view_t \G

• 在views表中查看视图详细信息
  select *from infromation_schema.views

【例】在views表中查看视图的详细定义，代码如下：

select *from infromation_schema.views

11.4 修改视图

• 使用CREATE OR REPLACE VIEW语句修改视图

create [or replace] [algorithm={undefined|merge|TEMPTABLE}]
VIEW　view_name [(column_list)]
as select_statement
[with[cascaded|local]check option]

修改视图的语句和创建视图的语句是完全一样的。当视图已经存在时，修改语句对视图进行修改；当视图不存在时，创建视图。下面通过一个实例来说明。

【例】修改视图view_t，代码如下：

create or replace view ciew_t as select * from t;

• 使用ALTER语句修改视图’

alter [algorithm={undefined|merge|TEMPTABLE}]
VIEW　view_name [(column_list)]
as select_statement
[with[cascaded|local]check option]

【例】使用ALTER语句修改视图view_t，代码如下：

alter view view_t as select quantity from t;

11.5 更新视图

• UPDATE
  【例】使用UPDATE语句更新视图view_t，代码如下：

update view_t set quantity=5;

对视图view_t更新后，基本表t的内容也更新了，同样当对基本表t更新后，另外一个视图view_t2中的内容也会更新。

• INSERT
  【例】使用INSERT语句在基本表t中插入一条记录，代码如下：

insert into t values(3,5);

向表t中插入一条记录，通过SELECT查看表t和视图view_t2，可以看到其中的内容也跟着更新，视图更新的不仅仅是数量和单价，总价也会更新。
【例】使用DELETE语句删除视图view_t2中的一条记录，代码如下：

delete from view_t2 where price=5;

在视图view_t2中删除price=5的记录，视图中的删除操作最终是通过删除基本表中相关的记录实现的，查看删除操作之后的表t和视图view_t2，可以看到通过视图删除其所依赖的基本表中的数据。

当视图中包含有如下内容时，视图的更新操作将不能被执行：
（1）视图中不包含基表中被定义为非空的列。
（2）在定义视图的SELECT语句后的字段列表中使用了数学表达式。
（3）在定义视图的SELECT语句后的字段列表中使用聚合函数。
（4）在定义视图的SELECT语句中使用了DISTINCT、UNION、TOP、GROUP BY或HAVING子句。

11.6 删除视图

当视图不再需要时，可以将其删除。删除一个或多个视图可以使用DROP VIEW语句
DROP VIEW [IF EXIST] view_name [,view_name]... [RESTRICT|CASCADE]

【例】删除stu_glass视图，代码如下：

drop view if exists stu_glass;