MySQL 視圖
[toc]
視圖概述
視圖介紹
什么是視圖
- 虛擬表
- 內(nèi)容與真實(shí)的表相似,有字段有記錄
- 視圖并不在數(shù)據(jù)庫(kù)中以存儲(chǔ)的數(shù)據(jù)的形式存在
- 行和列的數(shù)據(jù)來(lái)自定義視圖時(shí)查詢所引用的基表,并且在具體引用視圖時(shí)動(dòng)態(tài)生成
- 更新視圖的數(shù)據(jù),就是更新基表的數(shù)據(jù)
- 更新基表的數(shù)據(jù),仕途的數(shù)據(jù)也會(huì)跟著改變
視圖優(yōu)點(diǎn)
簡(jiǎn)單
用戶不需關(guān)心視圖中的數(shù)據(jù)如何查詢獲得
視圖中的數(shù)據(jù)已經(jīng)是過(guò)濾好的符合條件的結(jié)果集
安全
- 用戶只能看到視圖中的數(shù)據(jù)
數(shù)據(jù)獨(dú)立
- 一旦視圖結(jié)構(gòu)確定,可以屏蔽表結(jié)構(gòu)對(duì)用戶的影響
視圖使用限制
- 不能在視圖上創(chuàng)建索引
- 在視圖的 FROM 子句中不能使用子查詢
- 以下情形中的視圖是不可更新的
- 包含以下關(guān)鍵字的 SQL 語(yǔ)句: 聚合函數(shù)( SUM, MIN, MAX,COUNT等), DISTINCT, GROUP BY, HAVING, UNION 或 UNION ALL
- 常量視圖, JOIN, FROM 一個(gè)不能更新的視圖
- WHERE 子句的子查詢引用了 FROM 子句中的表
- 使用了臨時(shí)表
視圖 基本使用
創(chuàng)建視圖
create view 視圖名稱 as SQL查詢;
create view 試圖名稱(字段名列表) as SQL查詢;
注意: 在視圖表中不定義字段名的話,默認(rèn)使用基表的字段名,若定義字段名,視圖表中的字段必須和基表的字段個(gè)數(shù)相等.
示例
#準(zhǔn)備基表
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
#創(chuàng)建視圖 不指定視圖字段名稱
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 |
+--------+------+------+
#創(chuàng)建視圖 指定視圖字段名稱
#視圖表里字段名和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 |
+--------+---------+---------+
#數(shù)據(jù)目錄下 視圖 只有表結(jié)構(gòu),數(shù)據(jù)來(lái)源自基礎(chǔ)表
[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
查看創(chuàng)建視圖具體命令
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)
#查看視圖的數(shù)據(jù)基于哪個(gè)物理表
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 條件;
對(duì)視圖操作即是對(duì)基表操作,反之依然 !!!
示例
修改
修改基表會(huì)影響視圖,修改視圖會(huì)影響基表
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 |
增加
增加視圖會(huì)增加基表
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 視圖名;
刪除視圖表不會(huì)影響基表
視圖 進(jìn)階
創(chuàng)建視圖的完全格式
命令格式
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]
設(shè)置字段別名
視圖中的字段名不可以重復(fù),所以要定義別名
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;
重要選項(xiàng)說(shuō)明
OR REPLACE
create or replace view 視圖名 as select 查詢;
創(chuàng)建時(shí),若視圖已存在,會(huì)替換已有視圖
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
#無(wú)提示,已覆蓋
mysql> create or replace view t1 as select * from info;
Query OK, 0 rows affected (0.01 sec)
ALGORITHM
ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE }
| 參數(shù) | 意義 |
|---|---|
| MERAGE | 替換方式 |
| TEMPTABLE | 具體化方式 |
| UNDEFINED | 未定義 |
LOCAL 和 CASCADED
LOCAL 和 CASCADED關(guān)鍵字決定檢查的范圍
| 關(guān)鍵字 | 檢查范圍 |
|---|---|
| LOCAL | 僅檢查當(dāng)前視圖的限制 |
| CASCADED | 同時(shí)要滿足基表的限制 ( 默認(rèn)值 ) |
示例
# 創(chuàng)建 info 表
mysql> create table info select name,uid,shell from user limit 5;
# 創(chuàng)建視圖 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 |
+-----------------+--------+-------+------+
