Mysql之數據庫視圖

Mysql之數據庫視圖

        數據庫中的視圖是一個虛擬表。同真實表一樣，包含行和列數據，行和列的數據來自 定義視圖查詢所引用的表，并且在引用視圖時動態生成。可以隔離用戶與數據保持安全，且快速查詢數據。

        Mysql5.0后版本支持視圖，可以使用select、insert into、update、delete等。無論修改基本表還是視圖都對應發生變化。

視圖的作用：

比直接從數據表中讀取相比：

1、簡化了：看到的就是需要的。

2、安全性：通過視圖用戶只能查詢和修改他們所能看到的數據。其他數據看不到也取不到。

3、邏輯數據獨立性：視圖可以幫助用戶屏蔽真實表結構變化帶來的影響。

創建視圖：

視圖包含了select查詢的結果，因此視圖的創建基于select語句和已存在的數據表，視圖可以建立在一張表上，也可以建立在多張表上。

語法：

create [or replace] [algorithm = {undefined | merge | temptable }]
view view_name [(column_list)]
as select_statment
[with [cascaded | local] check option]
create    # 創建視圖
replace   # 替換視圖
algorithm  # 視圖算法：undefined系統自己選擇算法；merge表示視圖語句與視圖定義合并，使視圖定義部分替代語句對應部分；temptable表示將視圖結果存入臨時表，用臨時表來執行語句。
view_name  # 視圖名稱
column_list     # 屬性列
select_statment   # select語句
[with [cascaded | local] check option]    # 表示權限范圍之內：cascaded表示滿足所有條件。local表示更新時滿足視圖本身條件即可。

單表創建視圖：

mysql> create database view;
mysql> use view;
mysql> create table t (quantity INT,price INT);
mysql> insert into t values(3,50);
mysql> create view view_t as select quantity,price,quantity * price from t;
mysql> select * from view_t;

# 默認情況下創建的視圖和基本表的字段一樣，也可以通過指定視圖字段的名稱來創建視圖。

mysql> create view view_t2(qty,price,total) as select quantity,price,quantity * price from t;
mysql> select * from view_t2;

# view_t和view_t2兩個視圖中的字段名稱不同，但是數據卻是相同的。因此在使用視圖的時候，可能用戶根本就不需要了解表的機構，更接觸不到實際表中的數據，從而保證了數據庫的安全。

在多表上創建視圖：

mysql> create database stud;
mysql> use stud;
mysql> create table student (id int,name char(20));
mysql> create table stu_info (id int,name char(20),addr char(20));
mysql> show tables;
mysql> insert into student values(1,'zhouyi'),(2,'zhaoer'),(3,'zhangsan');
mysql> insert into student values(1,'zhouyi','beijing'),(2,'zhaoer','shanghai'),(3,'zhangsan','chengdu');
mysql> select * from stu_info;
mysql> select * from student;
mysql> create view stu_glass(id,name,glass) as select student.id,student.name,stu_info.addr from student,stu_info where student.id=stu_info.id;
mysql> select * from stu_glass;

# 可以看出視圖id是student.id

# name是student.name

# glass是stu_info.addr

查看視圖：

mysql> show table status like 'stu_glass'\G;    # 全是NULL，Comment: VIEW表示虛表
mysql> desc stu_glass;    # 查看視圖基本信息
mysql> show create view stu_glass\G;     # 查看視圖創建信息
mysql> select * from information_schema.views\G;    # 查看詳細信息

修改視圖：

mysql> use view;
mysql> desc view_t;
mysql> alter view view_t as select quantity from t;

或者：
mysql> create or replace view view_t as select * from t;
mysql> desc view_t;

更新視圖：

mysql> alter view view_t as select quantity from t;  # 修改視圖和元數據不同
mysql> select * from view_t;
mysql> select * from t;
mysql> update view_t set quantity=5;
mysql> select * from view_t;
mysql> select * from view_t2;
mysql> select * from t;
# 全部更新

插入數據：

mysql> insert into t values (3,5);    # 插入數據
mysql> select * from view_t;
mysql> select * from view_t2;
mysql> select * from t;        # 全部插入


mysql> delete from view_t2 where price=5;	# 刪除view_t2視圖中price=5的數據；
mysql> select * from view_t2;
mysql> select * from t;
# 在view_t2中刪除price=5的操作最終是通過基本表中的相關記錄實現的。
注意：
當視圖中包含有如下內容時，視圖的更新操作將不被執行：
1、視圖中不包含基本表中被定義為非空的列；
2、在定義視圖的select語句后的字段列表中使用數學表達式；
3、在定義視圖的select語句后的字段列表中使用了聚合函數；
4、在定義視圖的select語句中使用了distinct、union、top、group、having子句。

刪除視圖：

mysql> drop view if exists view_t2;
mysql> show create view view_t2; 
ERROR 1146 (42S02): Table 'view.view_t2' doesn't exist