9_mysql视图

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

---

1. 视图的优点

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

---

2. 创建视图

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 学号;

---

3. 删除视图

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

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

---

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

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

---

5. 查询视图
   视图定义后，就可以如同查询基本表那样对视图进行查询
   举例：查找平均成绩在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;

---

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

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

• 可更新的视图
  在视图中的行和基本表中的行之间必须具有一对一的关系。如果视图中包含下述结构中的任何一种，那么它就是不可更新的：
  （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';

---