資料庫MySQL-視圖
- 2020 年 3 月 27 日
- 筆記
1.4 視圖
1.4.1 概述
1、視圖是一張虛擬表，它表示一張表的部分數據或多張表的綜合數據，其結構和數據是建立在對錶的查詢基礎上
2、視圖中並不存放數據，而是存放在視圖所引用的原始表（基表）中
3、同一張原始表，根據不同用戶的不同需求，可以創建不同的視圖
1.4.2 作用
1、篩選表中的行
2、防止未經許可的用戶訪問敏感數據
3、隱藏數據表的結構
4、降低數據表的複雜程度
1.4.3 創建視圖
語法：
-- 創建視圖 create view 視圖名 as select 語句； -- 查詢視圖 select 列名 from 視圖
例題
-- 創建視圖 mysql> create view view1 -> as -> select * from stu where ch>=60 and math>=60; Query OK, 0 rows affected (0.00 sec) -- 查詢視圖 mysql> select * from view1; +--------+----------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+----------+--------+--------+---------+------------+------+------+ | s25302 | 李文才 | 男 | 31 | 3 | 上海 | 77 | 76 | | s25318 | 爭青小子 | 男 | 26 | 6 | 天津 | 86 | 92 | | s25319 | 梅超風 | 女 | 23 | 5 | 河北 | 74 | 67 | | s25320 | Tom | 男 | 24 | 8 | 北京 | 65 | 67 | | s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 | +--------+----------+--------+--------+---------+------------+------+------+ 5 rows in set (0.02 sec) -- 視圖可以使得降低SQL語句的複雜度 mysql> create view view2 -> as -> select stuno,stusex,writtenexam,labexam from stuinfo natural join stumarks; Query OK, 0 rows affected (0.01 sec)
1.4.4 修改視圖
語法
alter view 視圖名 as select 語句
例題：
mysql> alter view view2 -> as -> select stuname from stuinfo; Query OK, 0 rows affected (0.00 sec)
1.4.5 刪除視圖
語法
drop view [if exists ] 視圖1，視圖,...
例題
mysql> drop view view2; Query OK, 0 rows affected (0.00 sec)
1.4.6 查看視圖資訊
-- 方法一； mysql> show tables; -- 顯示所有的表和視圖 -- 方法二：精確查找視圖（視圖資訊存儲在information_schema下的views表中） mysql> select table_name from information_schema.views; +------------+ | table_name | +------------+ | view1 | +------------+ 1 row in set (0.05 sec) -- 方法三：通過表的comment屬性查詢視圖 mysql> show table statusG; -- 查詢所有表和視圖的詳細狀態資訊 mysql> show table status where comment='view'G -- 只查找視圖資訊
查詢視圖的結構
mysql> desc view1;
查詢創建視圖的語法
mysql> show create view view1G
1.4.7 視圖演算法
場景：找出語文成績最高的男生和女生
方法一：
mysql> select * from (select * from stu order by ch desc) t group by stusex; +--------+----------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+----------+--------+--------+---------+------------+------+------+ | s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 | | s25318 | 爭青小子 | 男 | 26 | 6 | 天津 | 86 | 92 | +--------+----------+--------+--------+---------+------------+------+------+ 2 rows in set (0.00 sec)
方法二：
mysql> create view view3 -> as -> select * from stu order by ch desc; Query OK, 0 rows affected (0.00 sec) mysql> select * from view3 group by stusex; +--------+---------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+---------+--------+--------+---------+------------+------+------+ | s25301 | 張秋麗 | 男 | 18 | 1 | 北京 | 80 | NULL | | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 | +--------+---------+--------+--------+---------+------------+------+------+ 2 rows in set (0.00 sec)
結論：方法一和方法二的結果不一樣，這是因為視圖的演算法造成的。
視圖的演算法有：
1、merge：合併演算法（將視圖語句和外層語句合併後再執行）
2、temptable:臨時表演算法（將視圖作為一個臨時表來執行）
3、undefined：未定義演算法（用哪種演算法有MySQL決定，這是默認演算法，視圖一般會選merge演算法）
重新通過視圖實現
-- 創建視圖，指定演算法為臨時表演算法 mysql> create or replace algorithm=temptable view view3 -> as -> select * from stu order by ch desc; Query OK, 0 rows affected (0.00 sec) mysql> select * from view3 group by stusex; +--------+----------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+----------+--------+--------+---------+------------+------+------+ | s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 | | s25318 | 爭青小子 | 男 | 26 | 6 | 天津 | 86 | 92 | +--------+----------+--------+--------+---------+------------+------+------+ 2 rows in set (0.00 sec)
結論：和子查詢結果一致。
