视图是指计算机数据库中的视图,是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。
一、视图概述
1.1、什么是视图
在 SQL 中,视图是基于 SQL 语句的结果集的可视化的表。
视图包含行和列,就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句,我们也可以提交数据,就像这些来自于某个单一的表。
注意:
数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。
1.2、视图的作用
重复利用SQL语句
简化SQL查询,快速取数据
只用知道表的部分结构
保护数据,根据特定授权
更改数据格式和表示,视图可返回与底层表的表示和格式不同的数据
注意:
在视图创建后,可以用与表基本相同的方式使用(查询、过滤、排序数据、与其他视图或连结、(添加、更新))。
视图只是用来查看存储在别处的数据的设施,本身不包含数据,返回的数据也是从其他表检索出来的。
因为视图本身不包含数据,索引多个表连结或嵌套可能存在性能问题,需测试。
1.3、视图规则和限制
与表一样,命名必须是唯一的(不能出现同名视图或表名)。
创建视图数目无限制,但是要考虑复杂查询创建为视图之后的性能影响。
视图不能添加索引,也不能有关联的触发器或者默认值。
视图可以提高安全性,必须具有足够的访问权限。
order by可用在视图中,但是如果从该视图检索数据select中含有order by ,那么该视图中的order by将被覆盖。
视图可以和表一起使用。
1.4、视图的应用
权限控制时使用
如某几个列,允许用户查询,其他列不允许查询
可以通过视图,开放其中几列查询,起到权限控制作用
简化复杂查询时使用
查询每个栏目下商品的平均价格,并按平均价格排序,查询出平均价格前3的栏目
视图能不能更新,删除,添加
如果视图的每一行,是与物理表一一对应的则可以
视图的行是由物理表多行经过计算得到的结果,视图不可以更新的
二、视图创建
2.1、创建格式
格式:
create view 视图名 as select 字段名 from 表名;
案例:
创建一个视图
mysql> create view s_view as (select sname,sex,age from students);
Query OK, 0 rows affected (0.02 sec)
mysql> select * from s_view;
+--------+------+------+
| sname | sex | age |
+--------+------+------+
| 张三 | 男 | 19 |
| 李四 | 男 | 20 |
| 张红 | 女 | 19 |
| 张八 | 男 | 18 |
| 三李 | 男 | 19 |
| 王六 | 女 | 20 |
| 刘红 | 女 | 18 |
+--------+------+------+
8 rows in set (0.00 sec)
2.2、视图的运算规则
格式:
create [algorithm = {undefined | merge | temptable}] view 视图名 as select 字段名 from 表名;
注意:
ALGORITHM : 视图算法
undefined 系统自动选择算法
merge 当使用视图时,会把查询视图的语句和创建视图的语句合并起来,形成一条件一句,最后再从基表中查询
temptable 当使用视图时,会把创建视图的语句的查询结果当成一张临时表,再从临时表中进行筛选
案例:
用temptable创建视图
mysql> create algorithm=temptable view view_t as select sname,sex,english,math from students order by math,english desc;
Query OK, 0 rows affected (0.03 秒)
mysql> select * from view_t;
+--------+------+---------+------+
| sname | sex | english | math |
+--------+------+---------+------+
| 王六 | 女 | 50.0 | 70.0 |
| 张红 | 女 | 86.0 | 80.0 |
| 张八 | 男 | 80.0 | 85.0 |
| 张三 | 男 | 98.5 | 88.0 |
| 李四 | 男 | 80.0 | 88.0 |
| 三李 | 男 | 60.0 | 88.0 |
| 刘红 | 女 | 90.0 | 98.0 |
+--------+------+---------+------+
8 行于数据集 (0.01 秒)
mysql> select * from view_t group by sex;
+--------+------+---------+------+
| sname | sex | english | math |
+--------+------+---------+------+
| 王六 | 女 | 50.0 | 70.0 |
| 张八 | 男 | 80.0 | 85.0 |
+--------+------+---------+------+
2 行于数据集 (0.01 秒)
用merge创建视图
mysql> create algorithm=merge view view_m as select sname,sex,english,math from students order by math,english desc;
Query OK, 0 rows affected (0.04 秒)
mysql> select * from view_m;
+--------+------+---------+------+
| sname | sex | english | math |
+--------+------+---------+------+
| 王六 | 女 | 50.0 | 70.0 |
| 张红 | 女 | 86.0 | 80.0 |
| 张八 | 男 | 80.0 | 85.0 |
| 张三 | 男 | 98.5 | 88.0 |
| 李四 | 男 | 80.0 | 88.0 |
| 三李 | 男 | 60.0 | 88.0 |
| 刘红 | 女 | 90.0 | 98.0 |
+--------+------+---------+------+
8 行于数据集 (0.02 秒)
mysql> select * from view_m group by sex;
+--------+------+---------+------+
| sname | sex | english | math |
+--------+------+---------+------+
| 张三 | 男 | 98.5 | 88.0 |
| 张红 | 女 | 86.0 | 80.0 |
+--------+------+---------+------+
2 行于数据集 (0.01 秒)
#查询视图的语句和创建视图的语句合并起来,形成一条件一句,最后再从基表中查询
mysql> select sname,sex,english,math from students group by sex order by math,english desc;
+--------+------+---------+------+
| sname | sex | english | math |
+--------+------+---------+------+
| 张红 | 女 | 86.0 | 80.0 |
| 张三 | 男 | 98.5 | 88.0 |
+--------+------+---------+------+
2 行于数据集 (0.02 秒)
2.3、视图的权限范围
格式:
[with [cascaded | local ] check option]
WITH CHECK OPTION 表示对UPDATE、INSERT和DELETE操作时保持更新,插入或删除的行满足视图定义的条件(即子查询中的条件表达式)
注意:
cascaded 默认值 更新视图时要满足所有相关视图和表的条件。
local 表示更新视图时满足该视图本身定义的条件即可。
案例:
mysql> create view view_1 as select sid,sname,sex,age from students where sid<6;
Query OK, 0 rows affected (0.02 秒)
mysql> create view view_1_1 as select * from view_1 where sid>2 with cascaded check option;
Query OK, 0 rows affected (0.02 秒)
mysql> create view view_1_2 as select * from view_1 where sid>2 with local check option;
Query OK, 0 rows affected (0.02 秒)
mysql> insert into view_1_1 values(6,'lisi','男',20);
CHECK OPTION failed 'wing.view_1_1'
mysql> insert into view_1_2 values(6,'lisi','男',20);
Query OK, 1 rows affected (0.01 秒)
2.4、视图记录修改
格式:
update 数据库表名 set 字段名1=字段值1,字段名2=字段值2,...字段名n=字段值n where 条件表达式; #和表的修改一样
案例:
修改视图中王六的性别为‘男’
mysql> update s_view set sex='男' where sname='王六';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from s_view;
+--------+------+------+
| sname | sex | age |
+--------+------+------+
| 张三 | 男 | 19 |
| 李四 | 男 | 20 |
| 张红 | 女 | 19 |
| 张八 | 男 | 18 |
| 三李 | 男 | 19 |
| 王六 | 男 | 20 |
| 刘红 | 女 | 18 |
+--------+------+------+
8 rows in set (0.00 sec)
注意:
修改了视图,对基表数据也有影响
mysql> select * from students;
+------+--------+------+------+---------+------+------------+-----------------+
| sid | sname | sex | age | english | math | entertime | remark |
+------+--------+------+------+---------+------+------------+-----------------+
| 1 | 张三 | 男 | 19 | 98.5 | 88.0 | 2017-09-01 | 他来自四川 |
| 2 | 李四 | 男 | 20 | 80.0 | 88.0 | 2017-09-01 | 他来自重庆 |
| 3 | 张红 | 女 | 19 | 86.0 | 80.0 | 2017-09-01 | 他来自北京 |
| 4 | 张八 | 男 | 18 | 80.0 | 85.0 | 2017-09-01 | 他来自天津 |
| 5 | 三李 | 男 | 19 | 60.0 | 88.0 | 2017-09-01 | 他来自湖北 |
| 6 | 王六 | 男 | 20 | 50.0 | 70.0 | 2017-09-01 | 他来自湖南 |
| 7 | 刘红 | 女 | 18 | 90.0 | 98.0 | 2017-09-01 | 他来自甘肃 |
+------+--------+------+------+---------+------+------------+-----------------+
8 rows in set (0.00 sec)
修改students表中王六的年龄为21
mysql> update students set age=21 where sname='王六';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from s_view;
+--------+------+------+
| sname | sex | age |
+--------+------+------+
| 张三 | 男 | 19 |
| 李四 | 男 | 20 |
| 张红 | 女 | 19 |
| 张八 | 男 | 18 |
| 三李 | 男 | 19 |
| 王六 | 男 | 21 |
| 刘红 | 女 | 18 |
+--------+------+------+
8 rows in set (0.00 sec)
三、视图修改
3.1、修改格式
格式:
alter view 视图名称 as select 语句;
或
alter view 视图名称 as select 视图;
或
create or replace view 视图名 as select 字段名 from 表名;
3.2、select 语句 修改
案例:
修改我们的s_view视图
mysql> alter view s_view as select sname,sex,age,remark from students;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from s_view;
+--------+------+------+-----------------+
| sname | sex | age | remark |
+--------+------+------+-----------------+
| 张三 | 男 | 19 | 他来自四川 |
| 李四 | 男 | 20 | 他来自重庆 |
| 张红 | 女 | 19 | 他来自北京 |
| 张八 | 男 | 18 | 他来自天津 |
| 三李 | 男 | 19 | 他来自湖北 |
| 王六 | 女 | 20 | 他来自湖南 |
| 刘红 | 女 | 18 | 他来自甘肃 |
+--------+------+------+-----------------+
8 rows in set (0.01 sec)
3.3、select 视图 修改
案例:
修改我们的s_view视图
mysql> create view s_view_1 as(select sname,sex,age,remark from students);
Query OK, 0 rows affected (0.01 sec)
mysql> alter view s_view as select sname,remark from s_view_1;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from s_view;
+--------+-----------------+
| sname | remark |
+--------+-----------------+
| 张三 | 他来自四川 |
| 李四 | 他来自重庆 |
| 张红 | 他来自北京 |
| 张八 | 他来自天津 |
| 三李 | 他来自湖北 |
| 王六 | 他来自湖南 |
| 刘红 | 他来自甘肃 |
+--------+-----------------+
8 rows in set (0.00 sec)
3.4、create or replace
案例:
修改我们的s_view视图
mysql> create or replace view s_view as select sname from students;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from s_view;
+--------+
| sname |
+--------+
| 张三 |
| 李四 |
| 张红 |
| 张八 |
| 三李 |
| 王六 |
| 刘红 |
+--------+
8 rows in set (0.00 sec)
四、视图查看
4.1、显示视图创建情况
格式:
show create view 视图名;
案例:
mysql> show create view s_view;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| s_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `s_view` AS select `s_view_1`.`sname` AS `sname`,`s_view_1`.`remark` AS `remark` from `s_view_1` | utf8mb4 | utf8mb4_0900_ai_ci |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
4.2、查看视图
4.2.1、查看视图结构
格式:
desc 视图名;
案例:
查看视图s_view结构
mysql> desc s_view;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sname | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.02 sec)
4.2.2、查看数据库中的视图
格式:
show tables [like %字符串%];
案例:
查看数据库中所有视图
mysql> show tables;
+--------------------+
| Tables_in_wing |
+--------------------+
| classes |
| new_user |
| s_view |
| s_view_1 |
| stu |
| student |
| students |
| t1 |
| t2 |
| t3 |
| t4 |
| t5 |
| t6 |
| teacher |
| user |
+--------------------+
15 rows in set (0.00 sec)
mysql> show tables like '%view%';
+-----------------------------+
| Tables_in_wing (%view%) |
+-----------------------------+
| s_view |
| s_view_1 |
+-----------------------------+
2 rows in set (0.00 sec)
五、视图删除及重命名
5.1、视图删除
格式:
drop view 视图列表;
案例:
删除视图s_view_1
mysql> drop view s_view_1;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables like '%view%';
+-----------------------------+
| Tables_in_wing (%view%) |
+-----------------------------+
| s_view |
+-----------------------------+
1 row in set (0.00 sec)
5.2、视图重命名
格式:
rename table 视图名 to 新视图名;
案例:
修改视图s_view的名字为view_s
mysql> rename table s_view to view_s;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables like '%view%';
+-----------------------------+
| Tables_in_wing (%view%) |
+-----------------------------+
| view_s |
+-----------------------------+
1 row in set (0.01 sec)