数据库MySQL-视图
- 2020 年 3 月 27 日
- 笔记
1.4 视图
1.4.1 概述
1、视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上
2、视图中并不存放数据，而是存放在视图所引用的原始表（基表）中
3、同一张原始表，根据不同用户的不同需求，可以创建不同的视图
1.4.2 作用
1、筛选表中的行
2、防止未经许可的用户访问敏感数据
3、隐藏数据表的结构
4、降低数据表的复杂程度
1.4.3 创建视图
语法：
-- 创建视图 create view 视图名 as select 语句； -- 查询视图 select 列名 from 视图
例题
-- 创建视图 mysql> create view view1 -> as -> select * from stu where ch>=60 and math>=60; Query OK, 0 rows affected (0.00 sec) -- 查询视图 mysql> select * from view1; +--------+----------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+----------+--------+--------+---------+------------+------+------+ | s25302 | 李文才 | 男 | 31 | 3 | 上海 | 77 | 76 | | s25318 | 争青小子 | 男 | 26 | 6 | 天津 | 86 | 92 | | s25319 | 梅超风 | 女 | 23 | 5 | 河北 | 74 | 67 | | s25320 | Tom | 男 | 24 | 8 | 北京 | 65 | 67 | | s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 | +--------+----------+--------+--------+---------+------------+------+------+ 5 rows in set (0.02 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)
1.4.4 修改视图
语法
alter view 视图名 as select 语句
例题：
mysql> alter view view2 -> as -> select stuname from stuinfo; Query OK, 0 rows affected (0.00 sec)
1.4.5 删除视图
语法
drop view [if exists ] 视图1，视图,...
例题
mysql> drop view view2; Query OK, 0 rows affected (0.00 sec)
1.4.6 查看视图信息
-- 方法一； 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 statusG; -- 查询所有表和视图的详细状态信息 mysql> show table status where comment='view'G -- 只查找视图信息
查询视图的结构
mysql> desc view1;
查询创建视图的语法
mysql> show create view view1G
1.4.7 视图算法
场景：找出语文成绩最高的男生和女生
方法一：
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)
结论：方法一和方法二的结果不一样，这是因为视图的算法造成的。
视图的算法有：
1、merge：合并算法（将视图语句和外层语句合并后再执行）
2、temptable:临时表算法（将视图作为一个临时表来执行）
3、undefined：未定义算法（用哪种算法有MySQL决定，这是默认算法，视图一般会选merge算法）
重新通过视图实现
-- 创建视图，指定算法为临时表算法 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)
结论：和子查询结果一致。
