二级MySQL数据库程序设计（七）

你的名字 2023-07-10 14:23 22阅读 0赞

**本博客为《全国计算机等级考试二级MySQL数据库程序设计教程》读书笔记，请勿转载用于其他商业用途。**

**课程目录**  
[第1章 数据库的基本概念与设计方法][1_]  
[第2章 MySQL简介][2_ MySQL]  
[第3章 数据库和表][3_]  
[第4章 表数据的基本操作][4_]  
[第5章 数据库的查询][5_]  
[第6章 索引][6_]  
[第7章 视图][7_]  
第8章 数据完整性约束与表维护语句  
第9章 触发器  
第10章 事件  
第11章 存储过程与存储函数  
第12章 访问控制与安全管理  
第13章 备份与恢复  
第14章 PHP和MySQL数据库编程  
第15章 开发实例

### 文章目录 ###

*  本章学习流程图
 *  本章学习大纲
 *  7.1 视图简介
 *  7.2 创建和删除视图
 *  7.3 修改和查看视图定义
 *  7.4 更新视图数据
 *  7.5 查询视图数据
 *  7.6 视图进阶

# 本章学习流程图 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyNTc0MDY5_size_16_color_FFFFFF_t_70]

# 本章学习大纲 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyNTc0MDY5_size_16_color_FFFFFF_t_70 1]  
视图是数据库系统中一种非常有用的数据库对象。

> 视图：一个虚拟表，其内容由查询定义。同真实表一样，视图包含一系列带有名称的列和数据行，但视图并不是数据库真实存储的信息。

# 7.1 视图简介 #

视图是从一个、多个表或者视图中导出的表，包含一系列带有名称的数据列和若干条数据行。但视图不同于表，区别如下：

*  视图不是数据库中真实的表，是一张虚拟表，其结构和数据是建立在对数据中真实表的查询基础上的。
 *  存储在数据库中的查询操作SQL语句定义了视图的内容，列数据和行数据来自于视图查询所引用的实际表，引用视图时动态生成这些数据。
 *  视图没有实际的物理记录，不是以数据集的形式存储在数据库中，它所对应的数据实际上是存储在视图所引用的真实表中。
 *  视图是数据的窗口，而表是内容。
 *  视图是查看数据表的一种方法，安全性更高。
 *  视图的建立和删除只影响视图本身，不影响对应的基本表。

视图与表在本质上虽然不同，但视图经过定义以后，结构形式和表一样，可以进行查询、修改、更新和删除等操作。同时，视图具有以下优点：

*  定制用户数据，聚焦特定的数据。
 *  简化数据操作。
 *  提高基表数据的安全性。
 *  共享所需数据。
 *  更改数据格式。
 *  重用SQL语句。

# 7.2 创建和删除视图 #

❶ 创建视图  
可以使用`CREATE VIEW`语句来创建视图。

**语法格式：**

CREATE VIEW <视图名> AS <SELECT语句>

**语法说明：**

*  <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
 *  <SELECT语句>：指定创建视图的SELECT语句。它可用于查询多个基础表或源视图。注意，对于SELECT语句的指定存在以下一些限制：  
    ① 用户除了用于CREATE VIEW权限，还具有操作中设计的基础表和其他视图的相关权限。  
    ② SELECT语句不能引用系统或用户变量。  
    ③ SELECT语句不能包含FROM子句中的子查询。  
    ④ SELECT语句不能引用预处理语句参数。  
    ⑤ 视图定义中引用的表或视图必须存在。但是，创建完视图后，可以删除定义引用的表或视图。可使用CHECK TABLE语句检查视图定义是否存在这类问题。  
    ⑥ 视图定义中允许使用ORDER BY语句，但是，如果从特定视图进行了选择，而该视图使用了自己的ORDER BY 语句，视图定义中的ORDER BY将被忽略。  
    ⑦ 定义中不能引用TEMPORARY表，不能创建TEMPORATY视图。

**【例7-1】在数据库 my\_test 中创建视图 students\_view，该视图包含学生基本信息表 students 中所有男生的姓名、专业和年龄，并且要求今后对视图数据的修改都必须符合“性别=男”这个条件。**

Database changed
    mysql> use my_test;
    Database changed
    mysql> create view my_test.students_view
        -> as
        -> select student_name,student_age,student_sex
        -> from students
        -> where student_sex='0'
        -> with check option;
    Query OK, 0 rows affected (0.36 sec)

`WITH CHECK OPTION`的意思是：修改视图时，检查插入的数据是否符合WHERE设置的条件。

❷ 删除视图  
可以使用`DROP VIEW`语句来删除视图。

**语法格式：**

DROP VIEW <视图名1> [,视图名2]...

**语法说明：**

*  <视图名>：指定要删除的视图名。`DROP VIEW`语句可以一次删除多个视图，但是必须在每个视图上拥有DROP权限。

例如，删除例7-1中创建的视图 students\_view，输入以下代码即可：

DROP VIEW students_view;

# 7.3 修改和查看视图定义 #

❶ 修改定义视图  
可以使用`ALTER VIEW`语句来对已有的视图进行修改。

**语法格式：**

VIEW <视图名> AS <SELECT语句>

`ALTER VIEW`语句的语法和`CREATE VIEW`类似，需要注意的是，对于`ALTER VIEW`语句的使用，需要用户具有针对视图的`CREATE VIEW`和`DROP`权限，以及由SELECT语句选择的每一列上的某些权限。  
修改视图的定义，除了通过`ALTER VIEW`，也可以使用`DROP VIEW`语句先删除视图，然后再使用`CREATE VIEW`语句来实现。

❷ 查看视图定义  
可以使用`SHOW CREATE VIEW`语句来查看已有视图的定义。

**语法格式：**

SHOW CREATE VIEW <视图名>

**语法说明：**

*  <视图名>：要查看视图的名称。

**【例7-2】查看数据库 my\_test 中的名为 students\_view 的视图结构。**

mysql> show create view students_view;
    +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
    | View          | Create View                                                                                                                                                                                                                                                                                                                 | character_set_client | collation_connection |
    +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
    | students_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `students_view` AS select `students`.`student_name` AS `student_name`,`students`.`student_age` AS `student_age`,`students`.`student_sex` AS `student_sex` from `students` where (`students`.`student_sex` = '0') WITH CASCADED CHECK OPTION | gbk                  | gbk_chinese_ci       |
    +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
    1 row in set (0.41 sec)

# 7.4 更新视图数据 #

视图是一个虚拟表，实际的数据来自于基础表，所以通过插入、修改和删除操作更新视图中的数据，实质上是在更新视图所引用的基础表的数据。

**注意：对视图的修改即是对基础表的修改，因此在修改时，要满足基础表的数据定义。**

某些视图是可更新的。也就是说，可以使用`UPDATE`、`DELETE`或者`INSERT`等语句，更新基表的内容。对于可更新的视图，视图中的行和基表中的行之间必须具有一对一的关系。  
还有一些特定的其他结构，这类结构会使得视图不可更新。更具体地讲，如果视图包含以下结构中的任何一种，都是不可更新的：

*  聚合函数SUM( )、MIN( )、COUNT( )等。
 *  DISTINCT 关键字。
 *  GROUP BY 子句。
 *  HAVING子句。
 *  UNION 或 UNION ALL 运算符。
 *  位于选择列表中的子查询。
 *  FROM 子句中的不可更新视图或包含多个表。
 *  WHERE 子句中的子查询，引用 FROM 子句中的表。
 *  ALGORITHM 选项为 TEMPTABLE （使用临时表总会使视图称为不可更新的）的时候。

❶ 使用 INSERT 语句通过视图向基础表插入数据

**【例7-3】在数据库 my\_test 中，向视图 students\_view 插入下面一条记录：**  
**（‘刘强’，‘地理空间专业’，‘19’，‘0’）**

mysql> insert into my_test.students_view
        -> values('刘强','地理空间专业','19','0');
    ERROR 1423 (HY000): Field of view 'my_test.students_view' underlying table doesn't have a default value

从结果可以看出，书中的方法并不能实现操作。

❷ 使用UPDATE语句通过视图修改基础表数据

**【例7-4】将视图 students\_view 的 students\_major 字段值“化学专业”改为“材料化学专业”。**

mysql> update my_test.students_view
        -> set student_major='材料化学专业'
        -> where student_major='化学专业';
    Query OK, 1 row affected (0.58 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

注意，如果一个视图依赖多个基础表，则一次视图修改操作只能改变一个基础表中的数据。

❸ 使用DELETE语句通过视图删除基础表数据

**【例7-5】删除视图 students\_view 中姓名为“王五”的学生信息。**

mysql> delete from students_view
        -> where student_name='王五';
    Query OK, 0 rows affected (0.00 sec)

注意，对于依赖多个基础表的视图，不能使用DELETE语句。

# 7.5 查询视图数据 #

视图一经定义之后，就可以如同查询数据中表一样，使用SELECT语句查询视图中的数据，语法和查询基础表的数据一样。视图用于查询主要应用在以下几个方面：

*  使用视图重新格式化检索出的数据。
 *  使用视图简化复杂的表连接。
 *  使用视图过滤数据。

**【例7-6】在视图 students\_view 中查找姓名为“蒋子涵”的学生，以及他的姓名和专业信息。**

# 7.6 视图进阶 #

使用视图的时候，还应该注意以下几点：

*  创建视图需要足够的访问权限。
 *  创建视图的数目没有限制。
 *  视图可以嵌套，即从其他视图中检索数据的查询来创建视图。
 *  视图不能索引，也不能有关联的触发器、默认值或规则。
 *  视图可以和表一起使用。
 *  视图不包含数据，所以每次使用视图时，都必须执行查询所需的任何一个检索操作。
 *  ORDER BY子句可以用在视图中，但如果从该视图检索数据的SELECT语句中也含有 ORDER BY 子句，那么该视图中的 OEDER BY 子句将被覆盖。

**【例7-7】基于视图 students\_view 创建一个只包含专业为“物理专业”的子视图。**

mysql> create view my_test.students_phy
        -> as
        -> select * from my_test.students_view
        -> where student_major='物理专业';
    Query OK, 0 rows affected (0.17 sec)

[1_]: https://chrishany.blog.csdn.net/article/details/104432053
[2_ MySQL]: https://chrishany.blog.csdn.net/article/details/104435219
[3_]: https://chrishany.blog.csdn.net/article/details/104454873
[4_]: https://chrishany.blog.csdn.net/article/details/104473980
[5_]: https://chrishany.blog.csdn.net/article/details/104503929
[6_]: https://chrishany.blog.csdn.net/article/details/104571638
[7_]: https://chrishany.blog.csdn.net/article/details/104628640
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyNTc0MDY5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200303113124769.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyNTc0MDY5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyNTc0MDY5_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2020030420510442.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyNTc0MDY5,size_16,color_FFFFFF,t_70