视图

mysql> create database db9;
mysql> create table db9.user(
-> name char(30),
-> password char(1),
-> uid int,
-> gid int,
-> comment char(150),
-> homedir char(150),
-> shell char(30)
-> );

mysql> alter table db9.user
-> add id int primary key auto_increment first;

mysql> select * from db9.user;
########################################################################################################
视图
虚拟表(假表)

不同用户访问同一个表格时，能看到的字段是不同的。视图限定了他们看到的字段。

缺点：
不能在视图上创建索引

创建视图
create view 视图名称 as SQL查询;
create view 视图名称(字段名列表) as SQL查询;

mysql> use db9;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;
+---------------+
| Tables_in_db9 |
+---------------+
| user |
+---------------+

mysql> create view v1 as select * from user;
mysql> show tables;
+---------------+
| Tables_in_db9 |
+---------------+
| user |
| v1 |
+---------------+

mysql> select * from v1;
mysql> desc v1;
+----------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name | char(30) | YES | | NULL | |
| password | char(1) | YES | | NULL | |
| uid | int(11) | YES | | NULL | |
| gid | int(11) | YES | | NULL | |
| comment | char(150) | YES | | NULL | |
| homedir | char(150) | YES | | NULL | |
| shell | char(30) | YES | | NULL | |
+----------+-----------+------+-----+---------+-------+

mysql> desc db9.user;
+----------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(30) | YES | | NULL | |
| password | char(1) | YES | | NULL | |
| uid | int(11) | YES | | NULL | |
| gid | int(11) | YES | | NULL | |
| comment | char(150) | YES | | NULL | |
| homedir | char(150) | YES | | NULL | |
| shell | char(30) | YES | | NULL | |
+----------+-----------+------+-----+---------+----------------+

---------------------------------------------------------------------------------------------------------------------------------------
# cd /var/lib/mysql/db9 这里看到视图只有表结构，没有表里面的数据
# ls
db.opt user.frm user.ibd v1.frm #user.frm(存放表user的结构) user.ibd(存放表user的数据)，而视图只有一个结构,没有数据v1.frm
------------------------------------------------------------------------------------------------------------------------------------------
mysql> create view v1 as select name,uid,shell from user where shell="/bin/bash";
mysql> grant select on db9.v1 to yaya300@"localhost" identified by "123qqq...A";
--------------------------------------------------------------------------------------------------------------------------------------------
2个终端打开，一个以用户yaya300登陆，只有查询权限。
一个是用户root登陆，拥有所有权限，root对基表user进行插入，更新，删除操作，客户端yaya300能看到v1里的内容也会跟着变化。
root对视图表v1进行插入，更新，删除操作，可以看到基表user也会跟着变化。

因为创建视图时，显示的条件时shell="/bin/bash"，所以当我们不论往基表user或者往视图表v1里，插入shell是/sbin/nologin的新数据时，基表user里能查询得到这个新数据，但是视图表v1里查不到，因为它只能看到shell="/bin/bash"的数据！
#############################################################################################
mysql> create table t1 select name,uid,shell from user limit 3;
mysql> select * from t1;
+--------+------+---------------+
| name | uid | shell |
+--------+------+---------------+
| admin | 0 | /bin/bash |
| bin | 1 | /sbin/nologin |
| daemon | 2 | /sbin/nologin |
+--------+------+---------------+

mysql> create table t2 select name,uid,homedir,shell from user limit 6;
mysql> select * from t2;
+--------+------+----------------+---------------+
| name | uid | homedir | shell |
+--------+------+----------------+---------------+
| admin | 0 | /root | /bin/bash |
| bin | 1 | /bin | /sbin/nologin |
| daemon | 2 | /sbin | /sbin/nologin |
| adm | 3 | /var/adm | /sbin/nologin |
| lp | 4 | /var/spool/lpd | /sbin/nologin |
| sync | 5 | /sbin | /bin/sync |
+--------+------+----------------+---------------+

mysql> select * from t1,t2 where t1.name=t2.name;
+--------+------+---------------+--------+------+---------+---------------+
| name | uid | shell | name | uid | homedir | shell |
+--------+------+---------------+--------+------+---------+---------------+
| admin | 0 | /bin/bash | admin | 0 | /root | /bin/bash |
| bin | 1 | /sbin/nologin | bin | 1 | /bin | /sbin/nologin |
| daemon | 2 | /sbin/nologin | daemon | 2 | /sbin | /sbin/nologin |
+--------+------+---------------+--------+------+---------+---------------+

视图中的字段名不能重复
mysql> create view v3 as select * from t1,t2 where t1.name=t2.name;
ERROR 1060 (42S21): Duplicate column name 'name'

mysql> create view v3(a,b,c,d,e,f,g) as select * from t1,t2 where t1.name=t2.name;
mysql> select * from v3;
+--------+------+---------------+--------+------+-------+---------------+
| a | b | c | d | e | f | g |
+--------+------+---------------+--------+------+-------+---------------+
| admin | 0 | /bin/bash | admin | 0 | /root | /bin/bash |
| bin | 1 | /sbin/nologin | bin | 1 | /bin | /sbin/nologin |
| daemon | 2 | /sbin/nologin | daemon | 2 | /sbin | /sbin/nologin |
+--------+------+---------------+--------+------+-------+---------------+
###########################################################################################
查询时给字段取别名
查询时也可以给表取别名
mysql> select t1.name as aname,t2.name as bname from t1,t2 where t1.name=t2.name;
+--------+--------+
| aname | bname |
+--------+--------+
| admin | admin |
| bin | bin |
| daemon | daemon |
+--------+--------+

mysql> select a.name as aname,b.name as bname,a.uid as auid,b.uid as buid from t1 a left join t2 b on a.uid=b.uid;
+--------+--------+------+------+
| aname | bname | auid | buid |
+--------+--------+------+------+
| admin | admin | 0 | 0 |
| bin | bin | 1 | 1 |
| daemon | daemon | 2 | 2 |
+--------+--------+------+------+

查询时同时给表和字段取别名，然后将它的结果来创建一个视图v100

mysql> create view v100 as select a.name as aname,b.name as bname,a.uid as auid,b.uid as buid from t1 a left join t2 b on a.uid=b.uid;

mysql> select * from v100;
+--------+--------+------+------+
| aname | bname | auid | buid |
+--------+--------+------+------+
| admin | admin | 0 | 0 |
| bin | bin | 1 | 1 |
| daemon | daemon | 2 | 2 |
+--------+--------+------+------+
##################################################################################################
算法UNDEFINED未定义时，用的是MERGE替换方式
具体化方式：先执行创建视图表时对基表的操作，再执行查询的操作。查了2遍。
MERGE替换方式：直接执行查询的操作。只查1遍。(默认方式) MERGE(合并)

with check option决定检查的范围。
local仅满足检查当前视图的限制
cascaded 同时满足对基表的限制(默认值。未指定时，默认cascaded关联的)

mysql> create table user2 select name,uid,gid,homedir,shell from user where uid between 10 and 50;
mysql> select * from user2;

mysql> create view v6 as
-> select name,uid,shell from user2
-> where uid<=30;

mysql> create view v6 as select * from user2 where uid<=30 with check option;
Query OK, 0 rows affected (0.05 sec)

mysql> show create view v6;
+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v6 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v6` AS select `user2`.`name` AS `name`,`user2`.`uid` AS `uid`,`user2`.`gid` AS `gid`,`user2`.`homedir` AS `homedir`,`user2`.`shell` AS `shell` from `user2` where (`user2`.`uid` <= 30) WITH CASCADED CHECK OPTION | utf8 | utf8_general_ci |
+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

WITH CASCADED CHECK OPTION

mysql> drop view v6;
Query OK, 0 rows affected (0.00 sec)

mysql> create view v6 as select * from user2 where uid<=30 with local check option;
Query OK, 0 rows affected (0.04 sec)

mysql> show create view v6;
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v6 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v6` AS select `user2`.`name` AS `name`,`user2`.`uid` AS `uid`,`user2`.`gid` AS `gid`,`user2`.`homedir` AS `homedir`,`user2`.`shell` AS `shell` from `user2` where (`user2`.`uid` <= 30) WITH LOCAL CHECK OPTION | utf8 | utf8_general_ci |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

WITH LOCAL CHECK OPTION
##################################################################################################
创建一个视图时，它的基表也是一个视图

mysql> create view v7 as select name,uid from user where uid<=1000; 视图表v7的uid的值只能<=1000
mysql> create view v8 as select name,uid from v7 where uid>=500 with check option; 在视图表v7的基础上创建视图表v8的uid的值只能>=500

mysql> select * from v8; 查看视图表v8里的所有信息
+---------------------+------+
| name | uid |
+---------------------+------+
| polkitd | 999 |
| libstoragemgmt | 998 |
| colord | 997 |
| saslauth | 996 |
| chrony | 995 | chrony这个用户的uid是995
| geoclue | 994 |
| setroubleshoot | 993 |
| gnome-initial-setup | 992 |
| viewuser1 | 999 |
+---------------------+------+

mysql> update v8 set uid=1002 where name="chrony"; 想把chrony这个用户的uid改为1002
ERROR 1369 (HY000): CHECK OPTION failed 'db9.v8'

mysql> update v8 set uid=449 where name="chrony";
ERROR 1369 (HY000): CHECK OPTION failed 'db9.v8'

mysql> update v8 set uid=800 where name="chrony";
Query OK, 1 row affected (0.03 sec)

mysql> create view v9 as select name,uid from v7 where uid>=500 with local check option;

mysql> select * from v9;
+---------------------+------+
| name | uid |
+---------------------+------+
| polkitd | 999 |
| libstoragemgmt | 998 |
| colord | 997 |
| saslauth | 996 |
| chrony | 800 |
| geoclue | 994 |
| setroubleshoot | 993 |
| gnome-initial-setup | 992 |
| viewuser1 | 999 |
+---------------------+------+

mysql> update v9 set uid=5000 where name="chrony";
mysql> select * from v9;
+---------------------+------+
| name | uid |
+---------------------+------+
| polkitd | 999 |
| libstoragemgmt | 998 |
| colord | 997 |
| saslauth | 996 |
| geoclue | 994 |
| setroubleshoot | 993 |
| gnome-initial-setup | 992 |
| viewuser1 | 999 |
+---------------------+------+

mysql> select name,uid from user where name="chrony";
+--------+------+
| name | uid |
+--------+------+
| chrony | 5000 |
+--------+------+

posted @ 2019-04-30 22:33 安于夏阅读(211) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

视图

公告