MySQL 視圖的基礎操作（五）
1.為什么使用視圖：
為了提高復雜SQL語句的復用性和表操作的安全性（例如：工資字段不想展示給所有能查看該查詢結果的人），MySQL提供了視圖特性。所謂視圖，本質(zhì)上是一種虛擬表，其內(nèi)容與真實的表相似，包含一系列帶有名稱的列和行數(shù)據(jù)。但是，視圖并不在數(shù)據(jù)庫中以存儲數(shù)據(jù)值的形式存在。行和列的數(shù)據(jù)來自定義視圖的查詢所引用基本表，并且在具體使用視圖時動態(tài)生成。
視圖有如下特點;
1. 視圖的列可以來自不同的表，是表的抽象和邏輯意義上建立的新關系。
2. 視圖是由基本表（實表）產(chǎn)生的表（虛表）。
3. 視圖的建立和刪除不影響基本表。
4. 對視圖內(nèi)容的更新（添加、刪除和修改）直接影響基本表。
5. 當視圖來自多個基本表時，不允許添加和刪除數(shù)據(jù)。
2.創(chuàng)建視圖：
在創(chuàng)建視圖時，首先要確保擁有CREATE VIEW的權限，并且同時確保對創(chuàng)建視圖所引用的表也具有相應的權限。
2.1 創(chuàng)建視圖的語法形式：
雖然視圖可以被看成是一種虛擬表，但是其在物理上是不存在的，即數(shù)據(jù)庫管理系統(tǒng)沒有專門的位置為視圖存儲數(shù)據(jù)。根據(jù)視圖的概念發(fā)現(xiàn)其數(shù)據(jù)來源于查詢語句，因此創(chuàng)建視圖的語法為：
CREATE VIEW view_name AS 查詢語句
//說明：和創(chuàng)建表一樣，視圖名不能和表名、也不能和其他視圖名重名。視圖的功能實際就是封裝了復雜的查詢語句。
示例：
use zhaojd_test; //選擇一個自己創(chuàng)建的庫 create table t_product( //創(chuàng)建表 id int primary key, pname varchar(20), price decimal(8,2) ); insert into t_product values(1,'apple',6.5); //向表中插入數(shù)據(jù) insert into t_product values(2,'orange',3); //向表中插入數(shù)據(jù) create view view_product as select id,name from t_product; //創(chuàng)建視圖 select * from view_product;
結果為：
+------+--------+
| id | name |
+------+--------+
| 1 | apple |
| 2 | orange |
+------+--------+
//其實在創(chuàng)建視圖時實際代碼里寫的是一個表查詢語句，只不過把這個查詢語句封裝起來重新起了一個名字，方便可以重復使用。
//再者，安全性方面可以隱藏一些不希望看到的字段，比如這里的價格字段。
//注意：在SQL語句的命名規(guī)范中，視圖一般以view_xxx或者v_xxx的樣式來命名。視圖的查詢語句和表的查詢語句相同。
2.2 創(chuàng)建各種視圖：
由于視圖的功能實際上時封裝查詢語句，那么是不是任何形式的查詢語句都可以封裝在視圖中呢？
2.2.1 封裝實現(xiàn)查詢常量語句的視圖（常量視圖）：
示例：
mysql> create view view_test1 as select 3.1415926; Query OK, 0 rows affected (0.07 sec) mysql> select * from view_test1; +-----------+ | 3.1415926 | +-----------+ | 3.1415926 | +-----------+ 1 row in set (0.00 sec)
2.2.2 封裝使用聚合函數(shù)（SUM、MIN、MAX、COUNT等）查詢語句的視圖：
示例：
首先準備需要用到的兩張表及其初始化數(shù)據(jù);
CREATE TABLE t_group(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
CREATE TABLE t_student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
sex CHAR(1),
group_id INT,
FOREIGN KEY (group_id) REFERENCES t_group (id)
);
//t_group表中插入數(shù)據(jù)
INSERT INTO t_group (NAME) VALUES('group_1');
INSERT INTO t_group (NAME) VALUES('group_2');
INSERT INTO t_group (NAME) VALUES('group_3');
INSERT INTO t_group (NAME) VALUES('group_4');
INSERT INTO t_group (NAME) VALUES('group_5');
//t_student表中插入數(shù)據(jù)
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_1','M',1);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_2','M',1);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_3','M',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_4','W',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_5','W',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_6','W',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_7','M',3);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_8','W',4);
INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_9','W',4);
================================================================
mysql> create view view_test2 as select count(name) from t_student;
Query OK, 0 rows affected (0.71 sec)
mysql> select * from view_test2;
+-------------+
| count(name) |
+-------------+
| 9 |
+-------------+
1 row in set (0.01 sec)
2.2.3 封裝了實現(xiàn)排序功能（ORDER BY）查詢語句的視圖：
示例：
mysql> create view view_test3 as select name from t_student order by id desc; Query OK, 0 rows affected (0.06 sec) mysql> select * from view_test3; +-------+ | name | +-------+ | zjd_9 | | zjd_8 | | zjd_7 | | zjd_6 | | zjd_5 | | zjd_4 | | zjd_3 | | zjd_2 | | zjd_1 | +-------+ 9 rows in set (0.00 sec)
2.2.4 封裝了實現(xiàn)表內(nèi)連接查詢語句的視圖：
示例：（第二組學生的姓名）
mysql> create view view_test5 as select s.name from t_student s,t_group g where s.group_id=g.id and g.id=2; Query OK, 0 rows affected (0.07 sec) mysql> select * from view_test5; +-------+ | name | +-------+ | zjd_3 | | zjd_4 | | zjd_5 | | zjd_6 | +-------+ 4 rows in set (0.00 sec)
2.2.5 封裝了實現(xiàn)表外連接（LEFT JOIN和RIGHT JOIN）查詢語句的視圖：
示例：（第二組學生姓名）
mysql> create view view_test6 as select s.name from t_student s left join t_group g on s.group_id=g.id where g.id=2; Query OK, 0 rows affected (0.09 sec) mysql> select * from view_test6; +-------+ | name | +-------+ | zjd_3 | | zjd_4 | | zjd_5 | | zjd_6 | +-------+ 4 rows in set (0.01 sec)
2.2.6 封裝了實現(xiàn)子查詢相關查詢語句的視圖：
示例：
mysql> create view view_test7 as select s.name from t_student s where s.id in(select id from t_group); Query OK, 0 rows affected (0.08 sec) mysql> select * from view_test7; +-------+ | name | +-------+ | zjd_1 | | zjd_2 | | zjd_3 | | zjd_4 | | zjd_5 | +-------+ 5 rows in set (0.00 sec)
2.2.7 封裝了實現(xiàn)記錄聯(lián)合（UNION和UNION ALL）查詢語句的視圖：
mysql> create view view_test8 as select id,name from t_student union all select id,name from t_group; Query OK, 0 rows affected (0.08 sec) mysql> select * from view_test8; +----+---------+ | id | name | +----+---------+ | 1 | zjd_1 | | 2 | zjd_2 | | 3 | zjd_3 | | 4 | zjd_4 | | 5 | zjd_5 | | 6 | zjd_6 | | 7 | zjd_7 | | 8 | zjd_8 | | 9 | zjd_9 | | 1 | group_1 | | 2 | group_2 | | 3 | group_3 | | 4 | group_4 | | 5 | group_5 | +----+---------+ 14 rows in set (0.01 sec)
3.查看視圖：
3.1 SHOW TABLES語句查看視圖名：
執(zhí)行SHOW TABLES 語句時不僅可以顯示表的名字，同時也是顯示出視圖的名字。
示例：
mysql> show tables; +------------------+ | Tables_in_zhaojd | +------------------+ | t_group | | t_product | | t_student | | v_product | | view_test1 | | view_test2 | | view_test3 | | view_test4 | | view_test5 | | view_test6 | | view_test8 | +------------------+ 11 rows in set (0.00 sec)
3.2 SHOW TABLE STATUS語句查看視圖詳細信息：
和SHOW TABLES語句一樣，SHOW TABLE STATUS語句不僅會顯示表的詳細信息，同時也會顯示視圖的詳細信息。
語法如下：
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
//參數(shù)db_name用來設置數(shù)據(jù)庫，SHOW TABLES STATUS表示將顯示所設置庫的表和視圖的詳細信息。
//設置LIKE關鍵字，可以查看某一個具體表或視圖的詳細信息。例如： SHOW TABLE STATUS FROM zhaojd LIKE 't_group' \G
示例：
mysql> show table status from zhaojd \G *************************** 1. row *************************** Name: t_group Engine: InnoDB Version: 10 Row_format: Compact Rows: 5 Avg_row_length: 3276 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 7340032 Auto_increment: 6 Create_time: 2016-08-19 16:26:06 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: ============================================================= Name: view_test8 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
3.3 SHOW CREATE VIEW語句查看視圖定義信息：
語法為：
SHOW CREATE VIEW viewname;
示例：
mysql> show create view view_test8 \G *************************** 1. row *************************** View: view_test8 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test8` AS select `t_student`.`id` AS `id`, `t_student`.`NAME` AS `name` from `t_student` union all select `t_group`.`id` AS `id`,`t_group`.`NAME` AS `name` from `t_group` character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0.00 sec) //根據(jù)執(zhí)行結果可以發(fā)現(xiàn)，SHOW CREATE VIEW語句返回兩個字段，分別為表示視圖名的View字段和關于視圖定義的Create view字段。
3.4 DESCRIBE | DESC 語句查看視圖定義信息：
語法為：
DESCRIBE | DESC viewname;
示例：
mysql> desc view_test8; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.02 sec)
3.5 通過系統(tǒng)表查看視圖信息：
當MySQL安裝成功后，會自動創(chuàng)建系統(tǒng)數(shù)據(jù)庫infomation_schema。在該數(shù)據(jù)庫中存在一個包含視圖信息的表格，可以通過查看表格views來查看所有視圖的相關信息。
示例：
mysql> use information_schema;
Database changed
mysql> select * from views where table_name='view_test8' \G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: zhaojd
TABLE_NAME: view_test8
VIEW_DEFINITION: select `zhaojd`.`t_student`.`id`AS`id`,`zhaojd`.`t_student`.`NAME` AS `name` from `zhaojd`.`t_student` union all select `zhaojd`.`t_group`.`id` AS`id`,`zhaojd`.`t_group`.`NAME` AS `name` from `zhaojd`.`t_group`
CHECK_OPTION: NONE
IS_UPDATABLE: NO
DEFINER: root@localhost
SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.01 sec)
4.刪除視圖：
在刪除視圖時首先要確保擁有刪除視圖的權限。
語法為：
DROP VIEW view_name [,view_name] ......
//從語法可以看出，DROP VIEW一次可以刪除多個視圖
示例：
mysql> use zhaojd; Database changed mysql> show tables; +------------------+ | Tables_in_zhaojd | +------------------+ | t_group | | t_product | | t_student | | v_product | | view_test1 | | view_test2 | | view_test3 | | view_test4 | | view_test5 | | view_test6 | | view_test8 | +------------------+ 11 rows in set (0.00 sec) mysql> drop view view_test1, view_test2; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +------------------+ | Tables_in_zhaojd | +------------------+ | t_group | | t_product | | t_student | | v_product | | view_test3 | | view_test4 | | view_test5 | | view_test6 | | view_test8 | +------------------+ 9 rows in set (0.00 sec)
5.修改視圖：
5.1 CREATE OR REPLACE VIEW語句修改視圖：
對于已經(jīng)創(chuàng)建好的表，尤其是已經(jīng)有大量數(shù)據(jù)的表，通過先刪除，然后再按照新的表定義重新建表的方式來修改表，需要做很多額外的工作，例如數(shù)據(jù)的重載等?？墒菍τ谝晥D來說，由于是“虛表”，并沒有存儲數(shù)據(jù)，所以完全可以通過該方式來修改視圖。
實現(xiàn)思路就是：先刪除同名的視圖，然后再根據(jù)新的需求創(chuàng)建新的視圖即可。
DROP VIEW view_name; CREATE VIEW view_name as 查詢語句;
但是如果每次修改視圖，都是先刪除視圖，然后再次創(chuàng)建一個同名的視圖，則顯得非常麻煩。于是MySQL提供了更方便的實現(xiàn)替換的創(chuàng)建視圖的語法，完整語法為：
CREATE OR REPLACE VIEW view_name as 查詢語句；
5.2 ALTER語句修改視圖：
語法為：
ALTER VIEW view_name as 查詢語句;
6.利用視圖操作基本表：
再MySQL中可以通常視圖檢索基本表數(shù)據(jù)，這是視圖最基本的應用，除此之后還可以通過視圖修改基本表中的數(shù)據(jù)。
6.1檢索（查詢）數(shù)據(jù)：
通過視圖查詢數(shù)據(jù)，與通過表進行查詢完全相同，只不過通過視圖查詢表更安全，更簡單實用。只需要把表名換成視圖名即可。
6.2利用視圖操作基本表數(shù)據(jù)：
由于視圖是“虛表”，所以對視圖數(shù)據(jù)進行的更新操作，實際上是對其基本表數(shù)據(jù)進行的更新操作。在具體更新視圖數(shù)據(jù)時，需要注意以下兩點;
1. 對視圖數(shù)據(jù)進行添加、刪除直接影響基本表。
2. 視圖來自于多個基本表時，不允許添加、刪除數(shù)據(jù)。
視圖中的添加數(shù)據(jù)操作、刪除數(shù)據(jù)操作、更新數(shù)據(jù)操作的語法同表完全相同。只是將表名換成視圖名即可。
以上就是本文的全部內(nèi)容，希望對大家的學習有所幫助，也希望大家多多支持腳本之家。
相關文章
這篇文章主要介紹了MySQL的幾種安裝方式及配置,然后在文章底部給大家介紹了安裝過程中的問題總結，非常不錯，具有參考借鑒價值，需要的朋友可以參考下2017-07-07
MySQL分表和分區(qū)分表是兩種常見的數(shù)據(jù)分割方案,本文主要介紹了MySQL分表和分區(qū)分表的區(qū)別小結,具有一定的參考價值,感興趣的可以了解一下2024-07-07
MySQL的的數(shù)據(jù)一致性保障與延遲問題處理
在分布式數(shù)據(jù)庫和高并發(fā)應用場景下,數(shù)據(jù)一致性 和 數(shù)據(jù)延遲 是兩個不可忽視的問題,MySQL 作為廣泛使用的關系型數(shù)據(jù)庫,提供了多種機制來保障數(shù)據(jù)一致性,本文將深入探討 MySQL 如何保障數(shù)據(jù)一致性 以及 應對數(shù)據(jù)延遲的策略,需要的朋友可以參考下2025-03-03
這篇文章主要給大家介紹了關于MySQL數(shù)據(jù)庫常見字段類型長度匯總大全的相關資料,需要的朋友可以參考下2024-05-05
這篇文章主要介紹了Mysql中varchar長度設置方法的相關資料,本文還給大家?guī)砹藇alar類型的變化及char()和varchar()的區(qū)別介紹，非常不錯，具有參考借鑒價值，需要的朋友可以參考下2016-07-07
mysql提示Changed limits: max_open_files: 2048 max_connections:
這篇文章主要介紹了mysql提示Changed limits: max_open_files: 2048 max_connections: 1910 table_cache: 64的解決,需要的朋友可以參考下2014-05-05
這篇文章主要介紹了MySQL查詢用戶權限的方法總結內(nèi)容，需要的朋友們可以參考下。2020-03-03
線上的MySQL一般都采用源碼編譯，雖然MySQL的源碼編譯挺簡單的，但是試想一下，如果你有幾百臺服務器同時要安裝MySQL，難道你還一臺臺去手動編譯、編寫配置文件嗎？這顯然太低效了，本文討論MySQL的自動化安裝部署。2014-07-07
mysql部分字符存儲報錯 Incorrect string value問題解決
MySQL中的utf-8字符集并不完全支持utf-8,本文這要介紹了mysql部分字符存儲報錯 Incorrect string value問題解決,具有一定的參考價值,感興趣的可以了解一下2023-07-07
眾所周知MySQL有很多內(nèi)置的函數(shù),下面這篇文章主要給大家介紹了關于MySQL內(nèi)部函數(shù)的相關資料,文中通過實例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2022-08-08