MySQL 视图
[toc]
视图概述
视图介绍
什么是视图
- 虚拟表
- 内容与真实的表相似,有字段有记录
- 视图并不在数据库中以存储的数据的形式存在
- 行和列的数据来自定义视图时查询所引用的基表,并且在具体引用视图时动态生成
- 更新视图的数据,就是更新基表的数据
- 更新基表的数据,仕途的数据也会跟着改变
视图优点
简单
用户不需关心视图中的数据如何查询获得
视图中的数据已经是过滤好的符合条件的结果集
安全
- 用户只能看到视图中的数据
数据独立
- 一旦视图结构确定,可以屏蔽表结构对用户的影响
视图使用限制
- 不能在视图上创建索引
- 在视图的 FROM 子句中不能使用子查询
- 以下情形中的视图是不可更新的
- 包含以下关键字的 SQL 语句: 聚合函数( SUM, MIN, MAX,COUNT等), DISTINCT, GROUP BY, HAVING, UNION 或 UNION ALL
- 常量视图, JOIN, FROM 一个不能更新的视图
- WHERE 子句的子查询引用了 FROM 子句中的表
- 使用了临时表
视图 基本使用
创建视图
create view 视图名称 as SQL查询;
create view 试图名称(字段名列表) as SQL查询;
注意: 在视图表中不定义字段名的话,默认使用基表的字段名,若定义字段名,视图表中的字段必须和基表的字段个数相等.
示例
#准备基表
mysql> create database db9;
mysql> use db9;
mysql> create table user(
-> name char(20),
-> password char(1),
-> uid int(2),
-> gid int(2),
-> comment char(150),
-> homedir char(150),
-> shell char(30)
-> );
mysql> system cp /etc/passwd /var/lib/mysql-files
mysql> load data infile "/var/lib/mysql-files/passwd" into table user fields terminated by ":" lines terminated by "\n";
mysql> alter table user add id int(2) primary key auto_increment first
#创建视图 不指定视图字段名称
mysql> create view t1 as select name,shell from user where uid<=20;
Query OK, 0 rows affected (0.32 sec)
mysql> show tables;
+---------------+
| Tables_in_db9 |
+---------------+
| t1 |
| user |
+---------------+
2 rows in set (0.01 sec)
mysql> create view t3 as select name,uid,gid from user limit 3;
mysql> select * from t3;
+--------+------+------+
| name | uid | gid |
+--------+------+------+
| root | 0 | 0 |
| bin | 1 | 1 |
| daemon | 2 | 2 |
+--------+------+------+
#创建视图 指定视图字段名称
#视图表里字段名和SQL查询里字段名必须一致
mysql> create view t4(user,stu_uid,stu_gid) as select name,uid,gid from user limit 3;
mysql> select * from t4;
+--------+---------+---------+
| user | stu_uid | stu_gid |
+--------+---------+---------+
| root | 0 | 0 |
| bin | 1 | 1 |
| daemon | 2 | 2 |
+--------+---------+---------+
#数据目录下 视图 只有表结构,数据来源自基础表
[root@test6 db9]# ls
db.opt t1.frm user.frm user.ibd
mysql> select * from t1;
+----------+----------------+
| name | shell |
+----------+----------------+
| root | /bin/bash |
| bin | /sbin/nologin |
| daemon | /sbin/nologin |
| adm | /sbin/nologin |
| lp | /sbin/nologin |
| sync | /bin/sync |
| shutdown | /sbin/shutdown |
| halt | /sbin/halt |
| mail | /sbin/nologin |
| operator | /sbin/nologin |
| games | /sbin/nologin |
| ftp | /sbin/nologin |
+----------+----------------+
12 rows in set (0.00 sec)
查看视图
show table status;
show table status where comment="view"\G
查看创建视图具体命令
show create view 视图名;
示例
# 查看哪些表是视图
mysql> show table status where comment="view"\G;
*************************** 1. row ***************************
Name: t1
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
1 row in set (0.01 sec)
#查看视图的数据基于哪个物理表
mysql> show create view t1\G;
*************************** 1. row ***************************
View: t1
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t1` AS select `user`.`name` AS `name`,`user`.`shell` AS `shell` from `user` where (`user`.`uid` <= 20)
character_set_client: utf8
collation_connection: utf8_general_ci
使用视图
查询记录
select 字段名列表 from 视图名 where 条件;
插入记录
insert into 视图名(字段名列表) values(字段值列表);
更新记录
update 视图名 set 字段名=值 where 条件;
删除记录
Delete from 视图名 where 条件;
对视图操作即是对基表操作,反之依然 !!!
示例
修改
修改基表会影响视图,修改视图会影响基表
mysql> create view v5 as select id,name,shell,password from user;
mysql> select * from v5;
+----+-----------------+----------------+----------+
| id | name | shell | password |
+----+-----------------+----------------+----------+
| 1 | root | /bin/bash | x |
mysql> update v5 set name="tom" where id=1;
mysql> select * from v5;
+----+-----------------+----------------+----------+
| id | name | shell | password |
+----+-----------------+----------------+----------+
| 1 | tom | /bin/bash | x |
mysql> select id,name,shell,password from user;
+----+-----------------+----------------+----------+
| id | name | shell | password |
+----+-----------------+----------------+----------+
| 1 | tom | /bin/bash | x |
增加
增加视图会增加基表
mysql> insert into v5(name,shell,password) values("leo","shelltest","x");
mysql> select * from v5 where name="leo";
+----+------+-----------+----------+
| id | name | shell | password |
+----+------+-----------+----------+
| 24 | leo | shelltest | x |
+----+------+-----------+----------+
mysql> select id,name,shell from user where name="leo";
+----+------+-----------+
| id | name | shell |
+----+------+-----------+
| 24 | leo | shelltest |
+----+------+-----------+
删除视图
drop view 视图名;
删除视图表不会影响基表
视图 进阶
创建视图的完全格式
命令格式
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]
设置字段别名
视图中的字段名不可以重复,所以要定义别名
create view 视图名
as
select 表别名.源字段名 as 字段别名 from 源表名 表别名 left join 源表名 表别名 on 条件;
create view v2
as
select a.name as aname, b.name as bname,a.uid as auid,b.uid as buid from user a left join info b on a.uid=b.uid;
重要选项说明
OR REPLACE
create or replace view 视图名 as select 查询;
创建时,若视图已存在,会替换已有视图
mysql> create view t1 as select * from info;
Query OK, 0 rows affected (0.01 sec)
#提示已存在
mysql> create view t1 as select * from info;
ERROR 1050 (42S01): Table 't1' already exists
#无提示,已覆盖
mysql> create or replace view t1 as select * from info;
Query OK, 0 rows affected (0.01 sec)
ALGORITHM
ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE }
| 参数 | 意义 |
|---|---|
| MERAGE | 替换方式 |
| TEMPTABLE | 具体化方式 |
| UNDEFINED | 未定义 |
LOCAL 和 CASCADED
LOCAL 和 CASCADED关键字决定检查的范围
| 关键字 | 检查范围 |
|---|---|
| LOCAL | 仅检查当前视图的限制 |
| CASCADED | 同时要满足基表的限制 ( 默认值 ) |
示例
# 创建 info 表
mysql> create table info select name,uid,shell from user limit 5;
# 创建视图 v2 ,user表 别名a,字段重命名,
mysql> create view v2 as select a.name as aname, b.name as bname, a.uid as auid, b.uid as buid from user a left join info b on a.uid=b.uid;
mysql> select * from v2;
+-----------------+--------+-------+------+
| aname | bname | auid | buid |
+-----------------+--------+-------+------+
| tom | tom | 0 | 0 |
| bin | bin | 1 | 1 |
| daemon | daemon | 2 | 2 |
| adm | adm | 3 | 3 |
| lp | lp | 4 | 4 |
| sync | NULL | 5 | NULL |
| shutdown | NULL | 6 | NULL |
| halt | NULL | 7 | NULL |
| mail | NULL | 8 | NULL |
| operator | NULL | 11 | NULL |
| games | NULL | 12 | NULL |
| ftp | NULL | 14 | NULL |
| nobody | NULL | 99 | NULL |
| systemd-network | NULL | 192 | NULL |
| dbus | NULL | 81 | NULL |
| polkitd | NULL | 999 | NULL |
| sshd | NULL | 74 | NULL |
| postfix | NULL | 89 | NULL |
| ntp | NULL | 38 | NULL |
| mysql | NULL | 27 | NULL |
| rpc | NULL | 32 | NULL |
| rpcuser | NULL | 29 | NULL |
| nfsnobody | NULL | 65534 | NULL |
+-----------------+--------+-------+------+
