MySQL视图
MySQL视图
视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
一、视图概述
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 'zutuanxue.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_zutuanxue |
+--------------------+
| 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_zutuanxue (%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_zutuanxue (%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_zutuanxue (%view%) |
+-----------------------------+
| view_s |
+-----------------------------+
1 row in set (0.01 sec)
