2021-04-19

&11、视图(View)

一、什么是视图（虚拟表）

1.视图通过以定制的方式显示来自一个或多个表的数据

2.视图是一种数据库对象，用户可以像查询普通表一样查询视图

3.视图内其实没有存储任何数据，它只是对表的一个查询

4.视图的定义保存在数据字典内，创建视图所基于对表称为“基表”

二、为什么需要视图

1.安全性：例如经常要对emp和dept表进行连接查询，每次都要做表的连接，写同样的一串语句，同时由于工资列队数据比较敏感，对外要求不可见。对这样的问题就可以通过视图来解决。

三、作用与安全

1.作用

a.控制安全

b.保存查询数据

2、优点

a.提供了灵活一致级别安全性。

b.隐藏了数据的复杂性

c.简化了用户的SQL指令

d.通过重命名列，从另一个角度提供数据

四、创建视图

1、定义视图——视图是数据库对象，使用create语句定义

create view view_name as sql语句

mysql> select * from t_use;
+---------+------------+------+----------+---------+---------------------+---------------------+
| bumenid | yuangongid | name | zzmm     | vage    | workday             | birthday            |
+---------+------------+------+----------+---------+---------------------+---------------------+
|     101 |       1001 | 张三 | 共产党员 | 3500.00 | 2015-03-04 00:00:00 | 1990-07-01 00:00:00 |
|     101 |       1002 | 李四 | 共青团员 | 3200.00 | 2017-02-06 00:00:00 | 1997-02-08 00:00:00 |
|     102 |       1003 | 王五 | 共产党员 | 5500.00 | 2016-10-10 00:00:00 | 1983-06-08 00:00:00 |
|     102 |       1003 | 赵六 | 群众     | 8500.00 | 2011-01-04 00:00:00 | 1994-09-05 00:00:00 |
|     102 |       1005 | 钱七 | 共产党员 | 4800.00 | 2014-04-01 00:00:00 | 1992-12-30 00:00:00 |
|     102 |       1006 | 孙八 | 共产党员 | 4500.00 | 2017-05-05 00:00:00 | 1996-09-02 00:00:00 |
+---------+------------+------+----------+---------+---------------------+---------------------+
6 rows in set (0.00 sec)

mysql> create view v_use as select bumenid,yuangongid,name,zzmm from t_use;
***创建视图v_use
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v_use;
***查看视图
+---------+------------+------+----------+
| bumenid | yuangongid | name | zzmm     |
+---------+------------+------+----------+
|     101 |       1001 | 张三 | 共产党员 |
|     101 |       1002 | 李四 | 共青团员 |
|     102 |       1003 | 王五 | 共产党员 |
|     102 |       1003 | 赵六 | 群众     |
|     102 |       1005 | 钱七 | 共产党员 |
|     102 |       1006 | 孙八 | 共产党员 |
+---------+------------+------+----------+

2、使用规则

1. 视图必须有唯一命名

2. 在mysql中视图的数量没有限制

3. 创建视图必须从管理员那里获得必要的权限

4. 视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图

5.在视图中可以使用OREDR BY，但是如果视图内已经使用该排序子句，则视图的ORDER BY将覆盖前面的OREDR BY.

6.视图不能索引，也不能关联触发器或默认值

7. 视图可以和表同时使用

三、修改视图——本质是修改表

使用CREATE OR REPLACE VIEW 语句修改视图. 为每个列指定列名.

mysql> create or replace view v_use as select * from t_use;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v_use;
+---------+------------+------+----------+---------+---------------------+---------------------+
| bumenid | yuangongid | name | zzmm     | vage    | workday             | birthday            |
+---------+------------+------+----------+---------+---------------------+---------------------+
|     101 |       1001 | 张三 | 共产党员 | 3500.00 | 2015-03-04 00:00:00 | 1990-07-01 00:00:00 |
|     101 |       1002 | 李四 | 共青团员 | 3200.00 | 2017-02-06 00:00:00 | 1997-02-08 00:00:00 |
|     102 |       1003 | 王五 | 共产党员 | 5500.00 | 2016-10-10 00:00:00 | 1983-06-08 00:00:00 |
|     102 |       1003 | 赵六 | 群众     | 8500.00 | 2011-01-04 00:00:00 | 1994-09-05 00:00:00 |
|     102 |       1005 | 钱七 | 共产党员 | 4800.00 | 2014-04-01 00:00:00 | 1992-12-30 00:00:00 |
|     102 |       1006 | 孙八 | 共产党员 | 4500.00 | 2017-05-05 00:00:00 | 1996-09-02 00:00:00 |
+---------+------------+------+----------+---------+---------------------+---------------------+
***修改视图，把所有的列出来

+---------+------------+------+----------+---------+---------------------+---------------------+
| bumenid | yuangongid | name | zzmm     | vage    | workday             | birthday            |
+---------+------------+------+----------+---------+---------------------+---------------------+
|     101 |       1001 | 张三 | 共产党员 | 3500.00 | 2015-03-04 00:00:00 | 1990-07-01 00:00:00 |
|     101 |       1002 | 李四 | 共青团员 | 3200.00 | 2017-02-06 00:00:00 | 1997-02-08 00:00:00 |
|     102 |       1003 | 王五 | 共产党员 | 5500.00 | 2016-10-10 00:00:00 | 1983-06-08 00:00:00 |
|     102 |       1003 | 赵六 | 群众     | 8500.00 | 2011-01-04 00:00:00 | 1994-09-05 00:00:00 |
|     102 |       1005 | 钱七 | 共产党员 | 4800.00 | 2014-04-01 00:00:00 | 1992-12-30 00:00:00 |
|     102 |       1006 | 孙八 | 共产党员 | 4500.00 | 2017-05-05 00:00:00 | 1996-09-02 00:00:00 |
+---------+------------+------+----------+---------+---------------------+---------------------+

mysql> alter view v_use as select bumenid,yuangongid,name from t_use where zzmm="共产党员";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v_use;
+---------+------------+------+
| bumenid | yuangongid | name |
+---------+------------+------+
|     101 |       1001 | 张三 |
|     102 |       1003 | 王五 |
|     102 |       1005 | 钱七 |
|     102 |       1006 | 孙八 |
+---------+------------+------+
***找出里面zzmm="共产党员"的人，列出bumenid,yuangongid,name

四、删除视图

DROP VIEW view_name;

五、查看视图详细结构

mysql> show create view v_use;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View  | Create View                                                                                                                                                                                                                                     | character_set_client | collation_connection |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v_use | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_use` AS select `t_use`.`bumenid` AS `bumenid`,`t_use`.`yuangongid` AS `yuangongid`,`t_use`.`name` AS `name` from `t_use` where (`t_use`.`zzmm` = '共产党员') | gbk                  | gbk_chinese_ci       |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

mysql> show create view v_use\G
*************************** 1. row ***************************
           View: v_use（表名字）
     （SQL语句）  Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_use` AS select `t_use`.`bumenid` AS `bumenid`,`t_use`.`yuangongid` AS `yuangongid`,`t_use`.`name` AS `name` from `t_use` where (`t_use`.`zzmm` = '共产党员')
character_set_client: gbk
collation_connection: gbk_chinese_ci

六、更新视图

mysql> insert into v_use values (102,1007,"赵八");
Query OK, 1 row affected (0.00 sec)
mysql> select * from v_use;
+---------+------------+------+----------+---------+---------------------+---------------------+
| bumenid | yuangongid | name | zzmm     | vage    | workday             | birthday            |
+---------+------------+------+----------+---------+---------------------+---------------------+
|     101 |       1001 | 张三 | 共产党员 | 3500.00 | 2015-03-04 00:00:00 | 1990-07-01 00:00:00 |
|     101 |       1002 | 李四 | 共青团员 | 3200.00 | 2017-02-06 00:00:00 | 1997-02-08 00:00:00 |
|     102 |       1003 | 王五 | 共产党员 | 5500.00 | 2016-10-10 00:00:00 | 1983-06-08 00:00:00 |
|     102 |       1003 | 赵六 | 群众     | 8500.00 | 2011-01-04 00:00:00 | 1994-09-05 00:00:00 |
|     102 |       1005 | 钱七 | 共产党员 | 4800.00 | 2014-04-01 00:00:00 | 1992-12-30 00:00:00 |
|     102 |       1006 | 孙八 | 共产党员 | 4500.00 | 2017-05-05 00:00:00 | 1996-09-02 00:00:00 |
|     102 |       1007 | 赵八 | NULL     |    NULL | NULL                | NULL                |
+---------+------------+------+----------+---------+---------------------+---------------------+