mysql数据库—视图

1、视图的介绍

        视图是一张虚拟表，是从数据库中一个或多个表中导出来的表，其内容由查询定义。同真实表一样，视图包含一系列带有名称的列和行数据，数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

2、视图的优点

        简单：用户无需关心视图中的数据如何查询获得的，视图中的数据已经是过滤好的符合条件的结果集

        安全：用户只能看到视图中的数据

        数据独立：一旦视图结构确定，可以屏蔽表结构对用户的影响

3、操作案例

3.1、创建视图：create view 库.视图名称（字段名列表） as  SQL查询；

mysql> create database viewst;
mysql> create view viewst.a1 as select name,uid from tarena.user;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_viewst |
+------------------+
| a1               |
+------------------+
1 row in set (0.00 sec)

mysql> select * from a1;
+-----------------+-------+
| name            | uid   |
+-----------------+-------+
| root            |     0 |
| bin             |     1 |
| daemon          |     2 |
| adm             |     3 |
………………………………


mysql> create view viewst.a2(姓名,家目录,登录状态) as select name,homedir,shell from tarena.user;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_viewst |
+------------------+
| a1               |
| a2               |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from viewst.a2;
+-----------------+--------------------+----------------+
| 姓名            | 家目录             | 登录状态       |
+-----------------+--------------------+----------------+
| root            | /root              | /bin/bash      |
| bin             | /bin               | /sbin/nologin  |
……………………………………

3.2、查看视图：查询视图表里的数据是从哪个基表获取的

mysql> use viewst;
Database changed
mysql> show table status where comment="view" \G       #查询视图表名
mysql> show create view viewst.a1 \G                   #查询视图表的详细信息
*************************** 1. row ***************************
                View: a1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `a1` AS select `tarena`.`user`.`name` AS `name`,`tarena`.`user`.`uid` AS `uid` from `tarena`
.`user`character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

3.3、操作视图 select  insert  update  delete

        当对视图里的数据做 insert 、update 、delete，对应的基本数据也会跟着改变。

3.4、删除视图

mysql> drop view viewst.a2;
Query OK, 0 rows affected (0.00 sec)

3.5、视图进阶：创建视图时，select命令查询的表里有同名的字段时，就需要设置查询语句中的字段别名，视图的表头名不能重要。

mysql> create table tarena.t1 select name,uid from tarena.user limit 1;
mysql> create table tarena.t2 select name,uid from tarena.user limit 3;
mysql> use tarena;
mysql> create view a3 as select t1.name as username,t2.name as 姓名 from t1 inner join t2 on t1.name=t2.name;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a3;
+----------+--------+
| username | 姓名   |
+----------+--------+
| root     | root   |
+----------+--------+
1 row in set (0.00 sec)

3.6、覆盖的方式创建视图，达到修改已有视图的目的，

        or replace：覆盖原有视图，能够实现修改视图的目的

mysql> create or replace view viewst.a1 as select name,uid,gid from tarena.user;
Query OK, 0 rows affected (0.00 sec)

3.7、修改视图进阶，默认情况下通过视图修改数据是不受限制，可以设置通过视图修改数据受限制，限制的方式如下：

格式：

        with [选项] check option：表示更新视图时要保证在该视图的范围之内。

选项：

•         local：首先满足自身的限制 ，同时要满足基表的限制
  •         cascaded：默认值，满足视图自身限制即可

with check option支持的检查选项

mysql> create view viewst.b1 as select name,uid from tarena.user where uid > 10 with check option;
Query OK, 0 rows affected (0.00 sec)
mysql> use viewst;

mysql> update b1 set uid=1 where name="root";      #条件不成立，不会执行修改     
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
mysql> select * from b1 where name="root";
Empty set (0.00 sec)

mysql> select * from b1 where name="ftp";
+------+------+
| name | uid  |
+------+------+
| ftp  |   14 |
+------+------+
1 row in set (0.00 sec)

mysql> update b1 set uid=8 where name="ftp";              #条件>10不成立，执行不成功
ERROR 1369 (HY000): CHECK OPTION failed 'viewst.b1'
mysql> update b1 set uid=11 where name="ftp";             #条件>10不成立，执行成功
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from b1 where name="ftp";
+------+------+
| name | uid  |
+------+------+
| ftp  |   11 |
+------+------+
1 row in set (0.00 sec)

with local check option首先满足自身的限制 ，同时要满足基表的限制

mysql> create view  b2  as select name ,uid  from  b1 where uid>=18 with  local check option;
Query OK, 0 rows affected (0.01 sec)

#条件要满足基表>10同时也要满足视图本身的>=18的条件
mysql> update b2 set uid=15 where name="sshd";   
ERROR 1369 (HY000): CHECK OPTION failed 'viewst.b2'
mysql> update b2 set uid=20 where name="sshd";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0