sql视图;视图的作用,创建视图,修改视图,删除视图,查询视图,以及视图的算法

sql视图相关概念

1、视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上

2、视图中并不存放数据，而是存放在视图所引用的原始表（基表）中

3、同一张原始表，根据不同用户的不同需求，可以创建不同的视图

视图作用

1、筛选表中的行

2、防止未经许可的用户访问敏感数据--安全

3、隐藏数据表的结构 - 安全

视图往往在大项目中使用，而且是多系统使用：可以对外提供有用的数据，但是隐藏关键（无用）的数据：数据安全。

4、降低数据表的复杂程度;视图可以节约SQL语句：将一条复杂的查询语句使用视图进行保存，以后可以直接对视图进行操作。 -简洁

创建视图

语法：

-- 创建视图
create view 视图名
as 
    select 语句；
    
-- 查询视图
select 列名 from 视图

--在创建的时候就把视图算法指定上
create algorithm = temptable view 视图名 as

使用场景:如果视图的select语句中包含一个查询子句（order by,limit）,而且很可能该 关键字 执行顺序 比外部查询语句 关键字 靠后 一定要使用使用temptable算法，其他情况下不用指定，默认即可。

案例:

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)

修改视图

语法:

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

视图算法

视图的算法有：

1、merge：合并算法（将视图语句和外层语句合并后再执行,效率高）

2、temptable:临时表算法（先执行视图的select语句，然后在执行外部查询Select语句。）

3、undefined：未定义算法（用哪种算法有MySQL决定，这是默认算法，视图一般会选merge算法）

如果视图的select语句中包含一个查询子句（order by,limit）,而且很可能该 关键字 执行顺序 比外部查询语句 关键字 靠后 一定要使用使用temptable算法，其他情况下不用指定，默认即可。

场景：视图算法造成的查询结果不一致的情况

找出语文成绩最高的男生和女生

方法一:

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)

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