数据库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)

结论:和子查询结果一致。