9_mysql视图

① 视图(view)是从一个或多个表中(或视图)导出的表。
② 视图与表(有时为与视图区别，也称表为基本表)不同，视图是一个虚表，即对它所对应的数据不进行实际的存储，数据库中只存储视图的定义，对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。
③ 视图一经定义后，就可以像表一样被查询、修改、删除、更新。

视图的优点

为用户集中数据，简化用户的数据查询和处理
屏蔽数据库的复杂性
简化用户权限的管理
便于数据共享
可以重新组织数据以便输出到其他应用程序中

创建视图

CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

OR REPLACE：给定了OR REPLACE子句，语句能够替换已有的同名视图。
ALGORITHM子句：可选的ALGORITHM子句是对标准SQL的MySQL扩展，规定了MySQL的算法，算法会影响MySQL处理视图的方式。
view_name：视图名。
column_list：要想为视图的列定义明确的名称，可使用可选的column_list子句，列出由逗号隔开的列名。
select_statement：用来创建视图的SELECT语句，可在SELECT语句中查询多个表或视图。
注意：
① 定义视图的用户必须对所参照的表或视图有查询(即可执行select语句)权限；
② 不能包含from子句中的子查询
③ 不能引用系统或用户变量
④ 不能引用预处理语句参数
⑤ 在定义中引用的表或视图必须存在
WITH CHECK OPTION：指出在可更新视图上所进行的修改都要符合select_statement所指定的限制条件，这样可以确保数据修改后，仍可通过视图看到修改的数据。
使用视图时，要注意下列事项：
① 在默认情况下，将在当前数据库创建新视图。要想在给定数据库中明确创建视图，创建时，应将名称指定为db_name.view_name。
② 视图的命名必须遵循标志符命名规则，不能与表同名，且对每个用户视图名必须是唯一的，即对不同用户，即使是定义相同的视图，也必须使用不同的名字。
③ 不能把规则、默认值或触发器与视图相关联。
④ 不能在视图上建立任何索引，包括全文索引。

举例一：假设当前数据库是test，创建xscj数据库上的cs_kc视图，包括计算机专业各学生的学号、其选修的课程号及成绩。要保证对该视图的修改都符合专业名为“计算机”这个条件。

create or replace view  xscj.cs_kc 
    as
    select xs.学号,课程号,成绩
         from xscj.xs,  xscj.xs_kc
         where  xs.学号 = xs_kc.学号 and xs.专业名 = '计算机'
         with check option;

举例二：创建xscj数据库上的计算机专业学生的平均成绩视图cs_kc_avg，包括学号(在视图中列名为num)和平均成绩(在视图中列名为score_avg)

use xscj
create view cs_kc_avg(num, score_avg)
    as
    select 学号,avg(成绩)
         from cs_kc
         group by 学号;

删除视图

DROP VIEW [IF EXISTS] view_name [, view_name] ...

view_name是视图名，声明了IF EXISTS，若视图不存在的话，也不会出现错误信息。使用DROP VIEW一次可删除多个视图。
drop view cs_xs;

修改视图
举例：将cs_xs视图修改为只包含计算机专业学生的学号、姓名和总学分三列。

alter view cs_xs
as
    select 学号,姓名,总学分
         from xs
         where 专业名 = '计算机';

查询视图
视图定义后，就可以如同查询基本表那样对视图进行查询
举例：查找平均成绩在80分以上的学生的学号和平均成绩。

## 创建学生平均成绩视图xs_kc_avg：
create view xs_kc_avg ( num,score_avg )
   as 
   select 学号, avg(成绩)
      from xs_kc
      group by 学号;
## 再对xs_kc_avg视图进行查询。
select *
   from xs_kc_avg
   where score_avg>=80;

更新视图（视图数据的插入、修改、删除）

由于视图是一个虚拟表，所有更新视图(包括插入、修改和删除)数据也就等于在更新与其关联的基本表的数据。

可更新的视图
在视图中的行和基本表中的行之间必须具有一对一的关系。如果视图中包含下述结构中的任何一种，那么它就是不可更新的：
（1）聚合函数；
（2）DISTINCT关键字；
（3）GROUP BY子句；
（4）ORDER BY子句；
（5）HAVING子句；
（6）UNION运算符；
（7）位于选择列表中的子查询；
（8）FROM子句中包含多个表；
（9）SELECT语句中引用了不可更新视图；
（10）WHERE子句中的子查询，引用FROM子句中的表；
（11）ALGORITHM 选项指定为TEMPTABLE(使用临时表总会使视图成为不可更新的).
插入数据
举例：创建视图cs_xs，视图中包含计算机专业的学生信息，并向cs_xs视图中插入一条记录：（'081255'，'李牧'，'计算机'，1，'1994-10-21'，50，NULL，NULL）。

# 首先创建视图cs_xs：
create or replace view cs_xs
    as 
    select *
         from xs
         where 专业名 = '计算机'
    with check option;
# 接下来插入记录：
insert into cs_xs
   values('081255', '李牧', '计算机', 1, '1994-10-14', 50, null, null);

修改数据
举例一：将cs_xs视图中所有学生的总学分增加8分。

update cs_xs
    set 总学分 = 总学分+ 8;
若一个视图依赖于多个基本表，则一次修改该视图只能变动一个基本表的数据。

举例二：将cs_kc视图中学号为081101的学生的101课程成绩改为90分。

update cs_kc
    set 成绩=90
    where 学号='081101' and 课程号='101';
# 比如，以下的修改就是错误的：
update cs_kc
    set 学号='081120',课程号='208'
    where 成绩=90;

删除数据
举例：删除cs_xs中李牧同学（学号'081255'，【例4.74】插入）的记录。

delete from cs_xs
    where 学号 = '081255';