MySQL 视图的基础操作（五）

2024-03-13 14:56:03 44

1.为什么使用视图：

为了提高复杂SQL语句的复用性和表操作的安全性（例如：工资字段不想展示给所有能查看该查询结果的人），MySQL提供了视图特性。所谓视图，本质上是一种虚拟表，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储数据值的形式存在。行和列的数据来自定义视图的查询所引用基本表，并且在具体使用视图时动态生成。

视图有如下特点;

1.视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系。
2.视图是由基本表（实表）产生的表（虚表）。
3.视图的建立和删除不影响基本表。
4.对视图内容的更新（添加、删除和修改）直接影响基本表。
5.当视图来自多个基本表时，不允许添加和删除数据。

2.创建视图：

在创建视图时，首先要确保拥有CREATEVIEW的权限，并且同时确保对创建视图所引用的表也具有相应的权限。

2.1创建视图的语法形式：

虽然视图可以被看成是一种虚拟表，但是其在物理上是不存在的，即数据库管理系统没有专门的位置为视图存储数据。根据视图的概念发现其数据来源于查询语句，因此创建视图的语法为：

CREATEVIEWview_nameAS查询语句
//说明：和创建表一样，视图名不能和表名、也不能和其他视图名重名。视图的功能实际就是封装了复杂的查询语句。

示例：

usezhaojd_test;//选择一个自己创建的库
createtablet_product(//创建表
idintprimarykey,
pnamevarchar(20),
pricedecimal(8,2)
);
insertintot_productvalues(1,'apple',6.5);//向表中插入数据
insertintot_productvalues(2,'orange',3);//向表中插入数据
createviewview_productasselectid,namefromt_product;//创建视图
select*fromview_product;

结果为：
+------+--------+
|id |name |
+------+--------+
|1 |apple |
|2 |orange|
+------+--------+
//其实在创建视图时实际代码里写的是一个表查询语句，只不过把这个查询语句封装起来重新起了一个名字，方便可以重复使用。
//再者，安全性方面可以隐藏一些不希望看到的字段，比如这里的价格字段。
//注意：在SQL语句的命名规范中，视图一般以view_xxx或者v_xxx的样式来命名。视图的查询语句和表的查询语句相同。

2.2创建各种视图：
由于视图的功能实际上时封装查询语句，那么是不是任何形式的查询语句都可以封装在视图中呢？

2.2.1封装实现查询常量语句的视图（常量视图）：

示例：

mysql>createviewview_test1asselect3.1415926;
QueryOK,0rowsaffected(0.07sec)
mysql>select*fromview_test1;
+-----------+
|3.1415926|
+-----------+
|3.1415926|
+-----------+
1rowinset(0.00sec)

2.2.2封装使用聚合函数（SUM、MIN、MAX、COUNT等）查询语句的视图：

示例：
首先准备需要用到的两张表及其初始化数据;

CREATETABLEt_group(
idINTPRIMARYKEYAUTO_INCREMENT,
NAMEVARCHAR(20)
);
CREATETABLEt_student(
idINTPRIMARYKEYAUTO_INCREMENT,
NAMEVARCHAR(20),
sexCHAR(1),
group_idINT,
FOREIGNKEY(group_id)REFERENCESt_group(id)
);
//t_group表中插入数据
INSERTINTOt_group(NAME)VALUES('group_1');
INSERTINTOt_group(NAME)VALUES('group_2');
INSERTINTOt_group(NAME)VALUES('group_3');
INSERTINTOt_group(NAME)VALUES('group_4');
INSERTINTOt_group(NAME)VALUES('group_5');
//t_student表中插入数据
INSERTINTOt_student(NAME,sex,group_id)VALUES('zjd_1','M',1);
INSERTINTOt_student(NAME,sex,group_id)VALUES('zjd_2','M',1);
INSERTINTOt_student(NAME,sex,group_id)VALUES('zjd_3','M',2);
INSERTINTOt_student(NAME,sex,group_id)VALUES('zjd_4','W',2);
INSERTINTOt_student(NAME,sex,group_id)VALUES('zjd_5','W',2);
INSERTINTOt_student(NAME,sex,group_id)VALUES('zjd_6','W',2);
INSERTINTOt_student(NAME,sex,group_id)VALUES('zjd_7','M',3);
INSERTINTOt_student(NAME,sex,group_id)VALUES('zjd_8','W',4);
INSERTINTOt_student(NAME,sex,group_id)VALUES('zjd_9','W',4);
================================================================
mysql>createviewview_test2asselectcount(name)fromt_student;
QueryOK,0rowsaffected(0.71sec)
mysql>select*fromview_test2;
+-------------+
|count(name)|
+-------------+
|9|
+-------------+
1rowinset(0.01sec)

2.2.3封装了实现排序功能（ORDERBY）查询语句的视图：

示例：

mysql>createviewview_test3asselectnamefromt_studentorderbyiddesc;
QueryOK,0rowsaffected(0.06sec)
mysql>select*fromview_test3;
+-------+
|name|
+-------+
|zjd_9|
|zjd_8|
|zjd_7|
|zjd_6|
|zjd_5|
|zjd_4|
|zjd_3|
|zjd_2|
|zjd_1|
+-------+
9rowsinset(0.00sec)

2.2.4封装了实现表内连接查询语句的视图：

示例：（第二组学生的姓名）

mysql>createviewview_test5asselects.namefromt_students,t_groupgwheres.group_id=g.idandg.id=2;
QueryOK,0rowsaffected(0.07sec)
mysql>select*fromview_test5;
+-------+
|name|
+-------+
|zjd_3|
|zjd_4|
|zjd_5|
|zjd_6|
+-------+
4rowsinset(0.00sec)

2.2.5封装了实现表外连接（LEFTJOIN和RIGHTJOIN）查询语句的视图：

示例：（第二组学生姓名）

mysql>createviewview_test6asselects.namefromt_studentsleftjoint_groupgons.group_id=g.idwhereg.id=2;
QueryOK,0rowsaffected(0.09sec)
mysql>select*fromview_test6;
+-------+
|name|
+-------+
|zjd_3|
|zjd_4|
|zjd_5|
|zjd_6|
+-------+
4rowsinset(0.01sec)

2.2.6封装了实现子查询相关查询语句的视图：

示例：

mysql>createviewview_test7asselects.namefromt_studentswheres.idin(selectidfromt_group);
QueryOK,0rowsaffected(0.08sec)
mysql>select*fromview_test7;
+-------+
|name|
+-------+
|zjd_1|
|zjd_2|
|zjd_3|
|zjd_4|
|zjd_5|
+-------+
5rowsinset(0.00sec)

2.2.7封装了实现记录联合（UNION和UNIONALL）查询语句的视图：

mysql>createviewview_test8asselectid,namefromt_studentunionallselectid,namefromt_group;
QueryOK,0rowsaffected(0.08sec)
mysql>select*fromview_test8;
+----+---------+
|id|name|
+----+---------+
|1|zjd_1|
|2|zjd_2|
|3|zjd_3|
|4|zjd_4|
|5|zjd_5|
|6|zjd_6|
|7|zjd_7|
|8|zjd_8|
|9|zjd_9|
|1|group_1|
|2|group_2|
|3|group_3|
|4|group_4|
|5|group_5|
+----+---------+
14rowsinset(0.01sec)

3.查看视图：
3.1SHOWTABLES语句查看视图名：
执行SHOWTABLES语句时不仅可以显示表的名字，同时也是显示出视图的名字。

示例：

mysql>showtables;
+------------------+
|Tables_in_zhaojd|
+------------------+
|t_group|
|t_product|
|t_student|
|v_product|
|view_test1|
|view_test2|
|view_test3|
|view_test4|
|view_test5|
|view_test6|
|view_test8|
+------------------+
11rowsinset(0.00sec)

3.2SHOWTABLESTATUS语句查看视图详细信息：

和SHOWTABLES语句一样，SHOWTABLESTATUS语句不仅会显示表的详细信息，同时也会显示视图的详细信息。

语法如下：
SHOWTABLESTATUS[FROMdb_name][LIKE'pattern']
//参数db_name用来设置数据库，SHOWTABLESSTATUS表示将显示所设置库的表和视图的详细信息。
//设置LIKE关键字，可以查看某一个具体表或视图的详细信息。例如：SHOWTABLESTATUSFROMzhaojdLIKE't_group'\G
示例：

mysql>showtablestatusfromzhaojd\G
***************************1.row***************************
Name:t_group
Engine:InnoDB
Version:10
Row_format:Compact
Rows:5
Avg_row_length:3276
Data_length:16384
Max_data_length:0
Index_length:0
Data_free:7340032
Auto_increment:6
Create_time:2016-08-1916:26:06
Update_time:NULL
Check_time:NULL
Collation:utf8_general_ci
Checksum:NULL
Create_options:
Comment:
=============================================================
Name:view_test8
Engine:NULL
Version:NULL
Row_format:NULL
Rows:NULL
Avg_row_length:NULL
Data_length:NULL
Max_data_length:NULL
Index_length:NULL
Data_free:NULL
Auto_increment:NULL
Create_time:NULL
Update_time:NULL
Check_time:NULL
Collation:NULL
Checksum:NULL
Create_options:NULL
Comment:VIEW

3.3SHOWCREATEVIEW语句查看视图定义信息：

语法为：
SHOWCREATEVIEWviewname;
示例：

mysql>showcreateviewview_test8\G
***************************1.row***************************
View:view_test8
CreateView:CREATEALGORITHM=UNDEFINEDDEFINER=`root`@`localhost`SQLSECURITYDEFINERVIEW`view_test8`ASselect`t_student`.`id`AS`id`,
`t_student`.`NAME`AS`name`from`t_student`unionallselect`t_group`.`id`AS`id`,`t_group`.`NAME`AS`name`from`t_group`
character_set_client:utf8
collation_connection:utf8_general_ci
1rowinset(0.00sec)
//根据执行结果可以发现，SHOWCREATEVIEW语句返回两个字段，分别为表示视图名的View字段和关于视图定义的Createview字段。

3.4DESCRIBE|DESC语句查看视图定义信息：

语法为：
DESCRIBE|DESCviewname;
示例：

mysql>descview_test8;
+-------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+-------------+------+-----+---------+-------+
|id|int(11)|NO||0||
|name|varchar(20)|YES||NULL||
+-------+-------------+------+-----+---------+-------+
2rowsinset(0.02sec)

3.5通过系统表查看视图信息：
当MySQL安装成功后，会自动创建系统数据库infomation_schema。在该数据库中存在一个包含视图信息的表格，可以通过查看表格views来查看所有视图的相关信息。

示例：

mysql>useinformation_schema;
Databasechanged
mysql>select*fromviewswheretable_name='view_test8'\G
***************************1.row***************************
TABLE_CATALOG:def
TABLE_SCHEMA:zhaojd
TABLE_NAME:view_test8
VIEW_DEFINITION:select`zhaojd`.`t_student`.`id`AS`id`,`zhaojd`.`t_student`.`NAME`AS`name`from`zhaojd`.`t_student`unionallselect`zhaojd`.`t_group`.`id`AS`id`,`zhaojd`.`t_group`.`NAME`AS`name`from`zhaojd`.`t_group`
CHECK_OPTION:NONE
IS_UPDATABLE:NO
DEFINER:root@localhost
SECURITY_TYPE:DEFINER
CHARACTER_SET_CLIENT:utf8
COLLATION_CONNECTION:utf8_general_ci
1rowinset(0.01sec)

4.删除视图：
在删除视图时首先要确保拥有删除视图的权限。

语法为：
DROPVIEWview_name[,view_name]......
//从语法可以看出，DROPVIEW一次可以删除多个视图
示例：

mysql>usezhaojd;
Databasechanged
mysql>showtables;
+------------------+
|Tables_in_zhaojd|
+------------------+
|t_group|
|t_product|
|t_student|
|v_product|
|view_test1|
|view_test2|
|view_test3|
|view_test4|
|view_test5|
|view_test6|
|view_test8|
+------------------+
11rowsinset(0.00sec)
mysql>dropviewview_test1,view_test2;
QueryOK,0rowsaffected(0.01sec)
mysql>showtables;
+------------------+
|Tables_in_zhaojd|
+------------------+
|t_group|
|t_product|
|t_student|
|v_product|
|view_test3|
|view_test4|
|view_test5|
|view_test6|
|view_test8|
+------------------+
9rowsinset(0.00sec)

5.修改视图：
5.1CREATEORREPLACEVIEW语句修改视图：
对于已经创建好的表，尤其是已经有大量数据的表，通过先删除，然后再按照新的表定义重新建表的方式来修改表，需要做很多额外的工作，例如数据的重载等。可是对于视图来说，由于是“虚表”，并没有存储数据，所以完全可以通过该方式来修改视图。

实现思路就是：先删除同名的视图，然后再根据新的需求创建新的视图即可。

DROPVIEWview_name;
CREATEVIEWview_nameas查询语句;

但是如果每次修改视图，都是先删除视图，然后再次创建一个同名的视图，则显得非常麻烦。于是MySQL提供了更方便的实现替换的创建视图的语法，完整语法为：

CREATEORREPLACEVIEWview_nameas查询语句；

5.2ALTER语句修改视图：

语法为：

ALTERVIEW view_nameas查询语句;

6.利用视图操作基本表：
再MySQL中可以通常视图检索基本表数据，这是视图最基本的应用，除此之后还可以通过视图修改基本表中的数据。
6.1检索（查询）数据：
通过视图查询数据，与通过表进行查询完全相同，只不过通过视图查询表更安全，更简单实用。只需要把表名换成视图名即可。
6.2利用视图操作基本表数据：
由于视图是“虚表”，所以对视图数据进行的更新操作，实际上是对其基本表数据进行的更新操作。在具体更新视图数据时，需要注意以下两点;
1.对视图数据进行添加、删除直接影响基本表。
2.视图来自于多个基本表时，不允许添加、删除数据。
视图中的添加数据操作、删除数据操作、更新数据操作的语法同表完全相同。只是将表名换成视图名即可。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。

MySQL 视图的基础操作（五）

热门推荐

随机推荐