MySQL视图

最新推荐文章于 2022-01-08 11:01:06 发布

九洛城雪

最新推荐文章于 2022-01-08 11:01:06 发布

阅读量167

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/weixin_43800781/article/details/85060011

版权

数据库专栏收录该内容

21 篇文章 0 订阅

订阅专栏

View-视图

只有表结构，没有数据列表，即为视图表（注：表的存储引擎为memmory，只有表结构）

视图的定义：

是一个虚拟表，内容与真实的表相似，有字段有记录，不在数据库中以存储的数据形式。

行和列的数据来自定义视图时查询所引用的基表（源表，真实的物理表），在具体引用视图时动态生成

更新视图和基表中的任意一个，对方也会相应的改变

视图优点：

简单-用户不用关系视图中的数据如何获得，视图中的数据是已经过滤好的符合条件的结果的集合

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

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

视图的限制

不能创建索引，在视图的from子句中不能使用子查询

下列情形中的视图不可更新

包含这些关键字的SQL语句：聚合函数（sum，max，min，max等），distinct，group by，having，union（all）

常量视图，join，from一个不能更新的视图

where子句的子查询引用了from子句中的表

使用了临时表

视图操作

创建视图

1.创建基表（把/etc/passwd文件的内容存储到db9库的user表里，以这个为例）

[root@mysql55 ~]# mysql -uroot -p123456
ysql> create database db9;
Query OK, 1 row affected (0.00 sec)

mysql> create table db9.user(
    -> name char(50),
    -> password char(3),
    -> uid int,
    -> gid int,
    -> comment char(150),
    -> homedir char(150),
    -> shell  char(100));
Query OK, 0 rows affected (0.35 sec)

mysql> system cp /etc/passwd   /var/lib/mysql-files

mysql> load data infile "/var/lib/mysql-files/passwd"  into table db9.user fields terminated by ":"  lines terminated by "\n";
Query OK, 41 rows affected (0.03 sec)
Records: 41  Deleted: 0  Skipped: 0  Warnings: 0

2.插入id字段存储记录的行号（在所有字段的第一个）

mysql> alter table db9.user add id int primary key auto_increment first;
Query OK, 0 rows affected (0.88 sec)
mysql> select * from db9.user;

3.创建视图

格式：create view 视图名称 as SQL查询

create view 视图名称（字段名列表） as SQL查询

mysql> use db9;
mysql> create view v1 as  select name,uid,gid from db9.user;
Query OK, 0 rows affected (0.06 sec)
mysql> create view v2(user,u,g) as select name,uid,gid from db9.user;
Query OK, 0 rows affected (0.05 sec)

mysql> desc v1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(50) | YES  |     | NULL    |       |
| uid   | int(11)  | YES  |     | NULL    |       |
| gid   | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from v1;
+---------------------+-------+-------+
| name                | uid   | gid   |
+---------------------+-------+-------+
| root                |     0 |     0 |
| bin                 |     1 |     1 |
...
+---------------------+-------+-------+
41 rows in set (0.00 sec)

查看当前库已有的视图表

mysql> show table status;
mysql> show table status where comment="view"\G;
*************************** 1. row ***************************
           Name: v1
         Engine: NULL
        Version: NULL
       ...
 Create_options: NULL
        Comment: VIEW
*************************** 2. row ***************************
           Name: v2
       ...
         Comment: VIEW
2 rows in set (0.00 sec)

查看视图表对应基表

mysql> show create view v1\G;
*************************** 1. row ***************************
                View: v1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `user`.`name` AS `name`,`user`.`uid` AS `uid`,`user`.`gid` AS `gid` from `user`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

使用视图（select，insert into，update，delete from）

mysql> select name,uid from v1 where uid<5;
+--------+------+
| name   | uid  |
+--------+------+
| root   |    0 |
| bin    |    1 |
| daemon |    2 |
| adm    |    3 |
| lp     |    4 |
+--------+------+
5 rows in set (0.00 sec)
mysql> insert into v1(uid) values(2000);
Query OK, 1 row affected (0.04 sec)
mysql> select * from user  where uid=2000;
+----+------+----------+------+------+---------+---------+-------+
| id | name | password | uid  | gid  | comment | homedir | shell |
+----+------+----------+------+------+---------+---------+-------+
| 42 | NULL | NULL     | 2000 | NULL | NULL    | NULL    | NULL  |
+----+------+----------+------+------+---------+---------+-------+
1 row in set (0.00 sec)
mysql> update user set uid=1500 where id=42;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from v1 where uid=1500;;
+------+------+------+
| name | uid  | gid  |
+------+------+------+
| NULL | 1500 | NULL |
+------+------+------+
1 row in set (0.00 sec)

删除视图

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

mysql> show tables;
+---------------+
| Tables_in_db9 |
+---------------+
| user          |
| v1            |
+---------------+
2 rows in set (0.00 sec)

创建视图必须给字段设置别名（避免重复）

1.创建两个基表

mysql> create table t1 select name,uid,gid,shell from user limit 3;
mysql> create table t2 select name,uid,gid,shell from user limit 8;
mysql> select * from t1;
+--------+------+------+---------------+
| name   | uid  | gid  | shell         |
+--------+------+------+---------------+
| root   |    0 |    0 | /bin/bash     |
| bin    |    1 |    1 | /sbin/nologin |
| daemon |    2 |    2 | /sbin/nologin |
+--------+------+------+---------------+
mysql> select * from t2;
+----------+------+------+----------------+
| name     | uid  | gid  | shell          |
+----------+------+------+----------------+
| root     |    0 |    0 | /bin/bash      |
| bin      |    1 |    1 | /sbin/nologin  |
| daemon   |    2 |    2 | /sbin/nologin  |
| adm      |    3 |    4 | /sbin/nologin  |
| lp       |    4 |    7 | /sbin/nologin  |
| sync     |    5 |    0 | /bin/sync      |
| shutdown |    6 |    0 | /sbin/shutdown |
| halt     |    7 |    0 | /sbin/halt     |
+----------+------+------+----------------+

2.在查询的时候给表字段定义别名

格式：create view 视图名 as select 表名.源字段名 as 新字段名 ... from 表名，表名... where 条件；

mysql> select  t1.name as a,t1.uid as u,t1.gid as g,t1.shell as sh,t2.* from t1,t2 where t1.name="root" and t2.name="root";
+------+------+------+-----------+------+------+------+-----------+
| a    | u    | g    | sh        | name | uid  | gid  | shell     |
+------+------+------+-----------+------+------+------+-----------+
| root |    0 |    0 | /bin/bash | root |    0 |    0 | /bin/bash |
+------+------+------+-----------+------+------+------+-----------+
1 row in set (0.01 sec)

mysql> create view v3 as select  t1.name as a,t1.uid as u,t1.gid as g,t1.shell as sh,t2.* from t1,t2 where t1.name="root" and t2.name="root";
Query OK, 0 rows affected (0.03 sec)
mysql> select * from v3;
+------+------+------+-----------+------+------+------+-----------+
| a    | u    | g    | sh        | name | uid  | gid  | shell     |
+------+------+------+-----------+------+------+------+-----------+
| root |    0 |    0 | /bin/bash | root |    0 |    0 | /bin/bash |
+------+------+------+-----------+------+------+------+-----------+
1 row in set (0.00 sec)

3.在创建视图的给表字段定义别名

create view 表名（新字段列表） as select 字段 from 表名，表名 where 条件；

mysql> create view v4(a,b,c,d,e,f,g,h) as select * from t1,t2 where t1.name="root" and t2.name="root";
Query OK, 0 rows affected (0.03 sec)

mysql> select * from v4;
+------+------+------+-----------+------+------+------+-----------+
| a    | b    | c    | d         | e    | f    | g    | h         |
+------+------+------+-----------+------+------+------+-----------+
| root |    0 |    0 | /bin/bash | root |    0 |    0 | /bin/bash |
+------+------+------+-----------+------+------+------+-----------+
1 row in set (0.00 sec)

4.可以在创建视图的时候定义表别名

create view 视图名 as select 表别名.源字段名 as 字段别名 from 源表名表别名 left join 源表名表别名 on 条件；

创建视图的完全格式

命令格式：create [or replace] [algorithm(算法） = {undefined | merge | temptable}] [definer = {user | current_user}] [SQL security {definer | invoker}] view view_name [(column_list)] as select_statement [with [cascaded | local] check option ]

选项

说明

or replace

create or replace view 视图名 as select 查询

创建时，如果视图存在，回替换已有的视图

local 和 cascaded

local 仅检查当前视图的限制

cascade 同时要满足基表的限制（默认值）

mysql> create table user2 select name,uid,gid,shell from user where uid>=5 and uid<=100;                 //创建新表，方便实验环境

mysql> select * from user2;
+----------+------+------+----------------+
| name     | uid  | gid  | shell          |
+----------+------+------+----------------+
| sync     |    5 |    0 | /bin/sync      |
| shutdown |    6 |    0 | /sbin/shutdown |
| halt     |    7 |    0 | /sbin/halt     |
| mail     |    8 |   12 | /sbin/nologin  |
| operator |   11 |    0 | /sbin/nologin  |
| games    |   12 |  100 | /sbin/nologin  |
| ftp      |   14 |   50 | /sbin/nologin  |
| nobody   |   99 |   99 | /sbin/nologin  |
| dbus     |   81 |   81 | /sbin/nologin  |
| rpc      |   32 |   32 | /sbin/nologin  |
| radvd    |   75 |   75 | /sbin/nologin  |
| tss      |   59 |   59 | /sbin/nologin  |
| rpcuser  |   29 |   29 | /sbin/nologin  |
| gdm      |   42 |   42 | /sbin/nologin  |
| sshd     |   74 |   74 | /sbin/nologin  |
| avahi    |   70 |   70 | /sbin/nologin  |
| postfix  |   89 |   89 | /sbin/nologin  |
| ntp      |   38 |   38 | /sbin/nologin  |
| tcpdump  |   72 |   72 | /sbin/nologin  |
| mysql    |   27 |   27 | /bin/false     |
+----------+------+------+----------------+
20 rows in set (0.00 sec)

mysql> create view v7 as  select * from user2 where uid<=50 with local check option;
Query OK, 0 rows affected (0.05 sec)
//加入要满足视图本身限制的操作（with local check option）
mysql> select * from v7;
+----------+------+------+----------------+
| name     | uid  | gid  | shell          |
+----------+------+------+----------------+
| sync     |    5 |    0 | /bin/sync      |
| shutdown |    6 |    0 | /sbin/shutdown |
| halt     |    7 |    0 | /sbin/halt     |
| mail     |    8 |   12 | /sbin/nologin  |
| operator |   11 |    0 | /sbin/nologin  |
| games    |   12 |  100 | /sbin/nologin  |
| ftp      |   14 |   50 | /sbin/nologin  |
| rpc      |   32 |   32 | /sbin/nologin  |
| rpcuser  |   29 |   29 | /sbin/nologin  |
| gdm      |   42 |   42 | /sbin/nologin  |
| ntp      |   38 |   38 | /sbin/nologin  |
| mysql    |   27 |   27 | /bin/false     |
+----------+------+------+----------------+
12 rows in set (0.00 sec)
mysql> update v7 set uid=51 where name="sync";
ERROR 1369 (HY000): CHECK OPTION failed 'db9.v7'       //超出视图本身的限制，不予创建
mysql> update v7 set uid=50 where name="sync";
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from v7 where name="sync"
    -> ;
+------+------+------+-----------+
| name | uid  | gid  | shell     |
+------+------+------+-----------+
| sync |   50 |    0 | /bin/sync |
+------+------+------+-----------+
1 row in set (0.01 sec)

mysql> select * from user2 where name="sync"
    -> ;
+------+------+------+-----------+
| name | uid  | gid  | shell     |
+------+------+------+-----------+
| sync |   50 |    0 | /bin/sync |
+------+------+------+-----------+
1 row in set (0.00 sec)

mysql> update user2 set uid=60 where name="shutdown";           //更新基表的数据
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user2 where name="shutdown"；
+----------+------+------+----------------+
| name     | uid  | gid  | shell          |
+----------+------+------+----------------+
| shutdown |   60 |    0 | /sbin/shutdown |
+----------+------+------+----------------+
1 row in set (0.00 sec)

mysql> select * from v7 where name="shutdown"；                   //可以看出视图表v7中的数据已经为空
Empty set (0.00 sec)

ysql> create view v8 as select * from user2 where uid>=30 and uid<=80;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from v8;
+----------+------+------+----------------+
| name     | uid  | gid  | shell          |
+----------+------+------+----------------+
| sync     |   50 |    0 | /bin/sync      |
| shutdown |   60 |    0 | /sbin/shutdown |
| rpc      |   32 |   32 | /sbin/nologin  |
| radvd    |   75 |   75 | /sbin/nologin  |
| tss      |   59 |   59 | /sbin/nologin  |
| gdm      |   42 |   42 | /sbin/nologin  |
| sshd     |   74 |   74 | /sbin/nologin  |
| avahi    |   70 |   70 | /sbin/nologin  |
| ntp      |   38 |   38 | /sbin/nologin  |
| tcpdump  |   72 |   72 | /sbin/nologin  |
+----------+------+------+----------------+
10 rows in set (0.00 sec)
mysql> create view v9 as select * from v8 where uid>=50 with check option;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from v9;
+----------+------+------+----------------+
| name     | uid  | gid  | shell          |
+----------+------+------+----------------+
| sync     |   50 |    0 | /bin/sync      |
| shutdown |   60 |    0 | /sbin/shutdown |
| radvd    |   75 |   75 | /sbin/nologin  |
| tss      |   59 |   59 | /sbin/nologin  |
| sshd     |   74 |   74 | /sbin/nologin  |
| avahi    |   70 |   70 | /sbin/nologin  |
| tcpdump  |   72 |   72 | /sbin/nologin  |
+----------+------+------+----------------+
7 rows in set (0.00 sec)

mysql> update v9 set uid=100 where name="sync";
ERROR 1369 (HY000): CHECK OPTION failed 'db9.v9'
mysql> update v9 set uid=76 where name="sync";                //需同时满足
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

九洛城雪

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL视图

View-视图只有表结构，没有数据列表，即为视图表（注：表的存储引擎为memmory，只有表结构）视图的定义：是一个虚拟表，内容与真实的表相似，有字段有记录，不在数据库中以存储的数据形式。行和列的数据来自定义视图时查询所引用的基表（源表，真实的物理表），在具体引用视图时动态生成更新视图和基表中的任意一个，对方也会相应的改变视图优点：简单-用户不用关系视图中的数据如何获得...
复制链接

扫一扫