MySQL数据库之视图
视图
-
概述
- 视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上
- 视图中并不存放数据，而是存放在视图所引用的原始表（基表）中
- 同一张原始表，根据不同用户的不同需求，可以创建不同的视图
-
作用
- 筛选表中的行
- 防止未经许可的用户访问敏感数据
- 隐藏数据表的结构
- 降低数据表的复杂程度
创建视图
- 语法
-- 创建视图
create view 视图名
as
select 语句；
-- 查询视图
select 列名 from 视图
MariaDB [sel]> create view best
-> as
-> select * from grades;
# `Query OK, 0 rows affected (0.012 sec)`
MariaDB [sel]> select math from best;
+------+
| math |
+------+
| 96 |
| 91 |
| 94 |
| 94 |
+------+
# `4 rows in set (0.008 sec)`
- 视图可以使得降低SQL语句的复杂度
mysql> create view view2
-> as
-> select stuno,stusex,writtenexam,labexam from stuinfo natural join stumarks;
# `Query OK, 0 rows affected (0.01 sec)`
修改视图
- 语法
alter view 视图名
as
select 语句
mysql> alter view view2
-> as
-> select stuname from stuinfo;
# `Query OK, 0 rows affected (0.00 sec)`
删除视图
- 语法
drop view [if exists ] 视图1，视图,...
mysql> drop view view2;
# `Query OK, 0 rows affected (0.00 sec)`
查看视图信息
- 方法一
- 显示所有的表和视图
mysql> show tables;
- 方法二
- 精确查找视图（视图信息存储在information_schema下的views表中）
mysql> select table_name from information_schema.views;
+------------+
| table_name |
+------------+
| view1 |
+------------+
# `1 row in set (0.05 sec)`
- 方法三
- 通过表的comment属性查询视图
-- 查询所有表和视图的详细状态信息
mysql> show table status\G;
-- 只查找视图信息
mysql> show table status where comment='view'\G
查询视图的结构
mysql> desc view1;
查询创建视图的语法
mysql> show create view view1\G;
视图算法
- 一般场景
- 找出数学成绩最高的男生和女生
MariaDB [sel]> select * from (select * from grades order by math desc) tab group by sex;
+-------+------+---------+------+
| name | sex | chinese | math |
+-------+------+---------+------+
| Sunny | boy | 93 | 96 |
| Marry | girl | 95 | 94 |
+-------+------+---------+------+
# `2 rows in set (0.001 sec)`
MariaDB [sel]> create view bestMath
-> as
-> select * from grades order by math desc;
# `Query OK, 0 rows affected (0.010 sec)`
MariaDB [sel]> select * from bestMath group by sex;
+-------+------+---------+------+
| name | sex | chinese | math |
+-------+------+---------+------+
| Sunny | boy | 93 | 96 |
| Marry | girl | 95 | 94 |
+-------+------+---------+------+
# `2 rows in set (0.001 sec)`
视图的算法
-
视图的算法有
merge合并算法- 将视图语句和外层语句合并后再执行
temptable临时表算法- 将视图作为一个临时表来执行
undefined未定义算法- 用哪种算法有MySQL决定，这是默认算法，视图一般会选merge算法
- 重新通过视图实现
-
场景
- 找出语文成绩最高的男生和女生
-
方法一
mysql> select * from (select * from stu order by ch desc) t group by stusex;
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | 86 | 92 |
+--------+----------+--------+--------+---------+------------+------+------+
# `2 rows in set (0.00 sec)`
- 方法二
mysql> create view view3
-> as
-> select * from stu order by ch desc;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from view3 group by stusex;
+--------+---------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+---------+--------+--------+---------+------------+------+------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | 80 | NULL |
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 |
+--------+---------+--------+--------+---------+------------+------+------+
# `2 rows in set (0.00 sec)`
- 方法三
mysql> create or replace algorithm=temptable view view3
-> as
-> select * from stu order by ch desc;
# `Query OK, 0 rows affected (0.00 sec)`
mysql> select * from view3 group by stusex;
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 |
| s25318 | 争青小子 | 男 | 26 | 6 | 天津 | 86 | 92 |
+--------+----------+--------+--------+---------+------------+------+------+
# `2 rows in set (0.00 sec)`
- 结论
- 方法一和方法二的结果不一样，这是因为视图的算法造成的
- 方法三指定算法为临时表算法，和子查询结果一致
