一、MySql视图概述
1、什么是视图?
视图本质上是一种虚拟表,在物理上是不存在的,其内容与真实的表相似,包含一系列带有名称的列和行数据。但是,视图并不在数据库中以储存的数据值形式存在。行和列数据来自定义视图的查询所引用基本表,并且在具体引用视图时动态生成。
2、为什么要使用视图?
为了提高复杂SQL语句的复用性和表操作的安全性,MySQL数据库管理系统提供了视图特性。视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务,只能看到视图中所定义的数据,而不是视图所引用表中的数据,从而提高了数据库中数据的安全性。
3、视图有哪些特点?
视图的列可以来自不同的表,是表的抽象和在逻辑意义上建立的新关系。
视图是由基本表(实表)产生的表(虚表)。
视图的建立和删除不影响基本表。
对视图内容的更新(添加,删除和修改)直接影响基本表。
当视图来自多个基本表时,不允许添加和删除数据。
4、视图的使用场景有哪些?
视图根本用途:简化sql查询,提高开发效率。如果说还有另外一个用途那就是兼容老的表结构。下面是视图的常见使用场景:
重用SQL语句;
简化复杂的SQL操作。在编写查询后,可以方便的重用它而不必知道它的基本查询细节;
使用表的组成部分而不是整个表;
保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限;
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
5、视图的优点
视图与表在本质上虽然不相同,但视图经过定义以后,结构形式和表一样,可以进行查询、修改、更新和删除等操作。同时,视图具有如下优点:
定制用户数据,聚焦特定的数据
在实际的应用过程中,不同的用户可能对不同的数据有不同的要求。例如,当数据库同时存在时,如学生基本信息表、课程表和教师信息表等多种表同时存在时,可以根据需求让不同的用户使用各自的数据。学生查看修改自己基本信息的视图,安排课程人员查看修改课程表和教师信息的视图,教师查看学生信息和课程信息表的视图。
简化数据操作
在使用查询时,很多时候要使用聚合函数,同时还要显示其他字段的信息,可能还需要关联到其他表,语句可能会很长,如果这个动作频繁发生的话,可以创建视图来简化操作。
提高数据的安全性
视图是虚拟的,物理上是不存在的。可以只授予用户视图的权限,而不具体指定使用表的权限,来保护基础数据的安全。
共享所需数据
通过使用视图,每个用户不必都定义和存储自己所需的数据,可以共享数据库中的数据,同样的数据只需要存储一次。
更改数据格式
通过使用视图,可以重新格式化检索出的数据,并组织输出到其他应用程序中。
重用 SQL 语句
视图提供的是对查询操作的封装,本身不包含数据,所呈现的数据是根据视图定义从基础表中检索出来的,如果基础表的数据新增或删除,视图呈现的也是更新后的数据。视图定义后,编写完所需的查询,可以方便地重用该视图。
6、视图的缺点
性能。数据库必须把视图的查询转化成对基本表的查询,如果这个视图是由一个复杂的多表查询所定义,那么,即使是视图的一个简单查询,数据库也把它变成一个复杂的结合体,需要花费一定的时间。
修改限制。当用户试图修改视图的某些行时,数据库必须把它转化为对基本表的某些行的修改。事实上,当从视图中插入或者删除时,情况也是这样。对于简单视图来说,这是很方便的,但是,对于比较复杂的视图,可能是不可修改的
二、创建视图
1、基本语法
可以使用 CREATE VIEW 语句来创建视图。语法格式如下:
CREATE VIEW AS
语法说明如下:
:指定视图的名称。该名称在数据库中必须是唯一的,不能与其他表或视图同名。
:指定创建视图的 SELECT 语句,可用于查询多个基础表或源视图。
对于创建视图中的 SELECT 语句的指定存在以下限制:
用户除了拥有 CREATE VIEW 权限外,还具有操作中涉及的基础表和其他视图的相关权限。
SELECT 语句不能引用系统或用户变量。
SELECT 语句不能包含 FROM 子句中的子查询。
SELECT 语句不能引用预处理语句参数。
注意事项:
视图定义中引用的表或视图必须存在。但是,创建完视图后,可以删除定义引用的表或视图。可使用 CHECK TABLE 语句检查视图定义是否存在这类问题。
视图定义中允许使用 ORDER BY 语句,但是若从特定视图进行选择,而该视图使用了自己的 ORDER BY 语句,则视图定义中的 ORDER BY 将被忽略。
视图定义中不能引用 TEMPORARY 表(临时表),不能创建 TEMPORARY 视图。
WITH CHECK OPTION 的意思是,修改视图时,检查插入的数据是否符合 WHERE 设置的条件。
2、创建基于单表的视图
查看下表信息:
mysql> SELECT *FROM user_info;+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | xys | 20 |
| 2 | a | 21 |
| 3 | b | 23 |
| 4 | c | 50 |
| 5 | d | 15 |
| 6 | e | 20 |
| 7 | f | 21 |
| 8 | g | 23 |
| 9 | h | 50 |
| 10 | i | 15 |
+----+------+-----+
10 rows in set
在user_info表上创建view_user_info视图:
mysql> create view view_user_info as select *from user_info;
mysql> select *fromview_user_info;+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | xys | 20 |
| 2 | a | 21 |
| 3 | b | 23 |
| 4 | c | 50 |
| 5 | d | 15 |
| 6 | e | 20 |
| 7 | f | 21 |
| 8 | g | 23 |
| 9 | h | 50 |
| 10 | i | 15 |
+----+------+-----+
10 rows in set
3、创建基于多表的视图
在表 user_info和表 order_info上创建视图 view_user_order_info
mysql>CREATE VIEW view_user_order_info(name,product_name)-> as
-> select u.name,o.product_name from user_info u,order_info o where u.id=o.user_id;
Query OK,0 rows affected
mysql> select *fromview_user_order_info;+------+--------------+
| name | product_name |
+------+--------------+
| xys | p1 |
| xys | p1 |
| xys | p2 |
| a | p1 |
| a | p5 |
| b | p3 |
| c | p1 |
| e | p1 |
| h | p8 |
+------+--------------+
9 rows in set
三、查看视图
1、查看视图的字段信息
查看视图的字段信息与查看数据表的字段信息一样,都是使用 DESCRIBE 关键字来查看的。具体语法如下:
DESCRIBE 视图名;
或简写成:
DESC 视图名;
mysql>desc view_user_order_info;+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| name | varchar(50) | NO | | | |
| product_name | varchar(50) | NO | | | |
+--------------+-------------+------+-----+---------+-------+
2 rows in set
2、查看视图的详细信息
在 MySQL 中,SHOW CREATE VIEW 语句可以查看视图的详细定义。其语法如下所示:
SHOW CREATE VIEW 视图名;
mysql>SHOW CREATE VIEW view_user_order_info;+----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| view_user_order_info | CREATE ALGORITHM=UNDEFINED DEFINER=`devtest`@`%` SQL SECURITY DEFINER VIEW `view_user_order_info` (`name`,`product_name`) AS select `u`.`name` AS `name`,`o`.`product_name` AS `product_name` from (`user_info` `u` join `order_info` `o`) where (`u`.`id` = `o`.`user_id`) | utf8 | utf8_general_ci |
+----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set
3、拓展
所有视图的定义都是存储在 information_schema 数据库下的 views 表中,也可以在这个表中查看所有视图的详细信息,SQL 语句如下:
SELECT * FROM information_schema.views;
不过,通常情况下都是使用 SHOW CREATE VIEW 语句。
四、修改视图
修改视图是指修改 MySQL 数据库中存在的视图,当基本表的某些字段发生变化时,可以通过修改视图来保持与基本表的一致性。
1、基本语法
可以使用 ALTER VIEW 语句来对已有的视图进行修改。语法格式如下:
ALTER VIEW AS
语法说明如下:
:指定视图的名称。该名称在数据库中必须是唯一的,不能与其他表或视图同名。
:指定创建视图的 SELECT 语句,可用于查询多个基础表或源视图。
需要注意的是,对于 ALTER VIEW 语句的使用,需要用户具有针对视图的 CREATE VIEW 和 DROP 权限,以及由 SELECT 语句选择的每一列上的某些权限。
修改视图的定义,除了可以通过 ALTER VIEW 外,也可以使用 DROP VIEW 语句先删除视图,再使用 CREATE VIEW 语句来实现。
2、修改视图内容
视图是一个虚拟表,实际的数据来自于基本表,所以通过插入、修改和删除操作更新视图中的数据,实质上是在更新视图所引用的基本表的数据。
注意:对视图的修改就是对基本表的修改,因此在修改时,要满足基本表的数据定义。
某些视图是可更新的。也就是说,可以使用 UPDATE、DELETE 或 INSERT 等语句更新基本表的内容。对于可更新的视图,视图中的行和基本表的行之间必须具有一对一的关系。还有一些特定的其他结构,这些结构会使得视图不可更新。更具体地讲,如果视图包含以下结构中的任何一种,它就是不可更新的:
聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY 子句。
HAVING 子句。
UNION 或 UNION ALL 运算符。
位于选择列表中的子查询。
FROM 子句中的不可更新视图或包含多个表。
WHERE 子句中的子查询,引用 FROM 子句中的表。
ALGORITHM 选项为 TEMPTABLE(使用临时表总会使视图成为不可更新的)的时候。
(1)使用 ALTER 语句修改视图view_user_info ,输入的 SQL 语句和执行结果如下所示。
mysql>ALTER VIEW view_user_info AS SELECT id,name,age FROM user_info;
Query OK,0 rows affected
mysql>desc view_user_info;+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | bigint(20) | NO | | 0 | |
| name | varchar(50) | NO | | | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set
用户可以通过视图来插入、更新、删除表中的数据,因为视图是一个虚拟的表,没有数据。通过视图更新时转到基本表上进行更新,如果对视图增加或删除记录,实际上是对基本表增加或删除记录。
(2)使用 UPDATE 语句更新视图 view_user_info ,输入的 SQL 语句和执行结果如下所示。
mysql>update view_user_info-> set name='qxh' where id=1;
Query OK,1row affected
Rows matched:1 Changed: 1 Warnings: 0
mysql> select *from view_user_info where id=1;+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | qxh | 20 |
+----+------+-----+
1 row in set
3、修改视图名称
修改视图的名称可以先将视图删除,然后按照相同的定义语句进行视图的创建,并命名为新的视图名称。
五、删除视图
删除视图是指删除 MySQL 数据库中已存在的视图。删除视图时,只能删除视图的定义,不会删除数据。
基本语法
可以使用 DROP VIEW 语句来删除视图。语法格式如下:
DROP VIEW [ , …]
其中:指定要删除的视图名。DROP VIEW 语句可以一次删除多个视图,但是必须在每个视图上拥有 DROP 权限。
mysql>DROP VIEW IF EXISTS view_user_info,view_user_order_info;
Query OK,0 rows affected
SHOW CREATE VIEW view_user_info;1146 - Table 'qxhfx.view_user_info' doesn't exist
可以看到,view_user_info视图已不存在,将其成功删除