MySQL視圖(view)的基本用法及說明
更新時間：2023年12月21日 08:57:43 作者：睿思達DBA_WGX
這篇文章主要介紹了MySQL視圖(view)的基本用法及說明,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
MySQL中的視圖（view）是一種虛擬表，其內(nèi)容由查詢定義，是一個邏輯表，本身并不包含數(shù)據(jù)。
視圖看起來和真實的表完全相同，但其中的數(shù)據(jù)來自定義視圖時用到的基本表，并且在打開視圖時動態(tài)生成。
視圖可以在基本表上定義，也可以使用其他視圖定義。與直接操作基本表相比，視圖具備以下優(yōu)點：
（1）簡化操作：通過視圖可以使用戶將注意力集中在他所關(guān)心的數(shù)據(jù)上。使用視圖的用戶完全不需要關(guān)心后面對應(yīng)的表的結(jié)構(gòu)、關(guān)聯(lián)條件和篩選條件。
（2）提高數(shù)據(jù)的安全性：在設(shè)計數(shù)據(jù)庫時可以針對不同的用戶定義不同的視圖，使用視圖的用戶只能訪問他們被允許查詢的結(jié)果集。
（3）數(shù)據(jù)獨立：視圖的結(jié)構(gòu)定義好之后，如果增加新的關(guān)系或?qū)υ械年P(guān)系增加新的字段對用戶訪問的數(shù)據(jù)都不會造成影響。
一、創(chuàng)建視圖的語法
創(chuàng)建視圖的基本語法如下：
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
說明：
1、OR REPLACE：表示該語句能夠替換已有視圖；
2、ALGORITHM：可選參數(shù)，表示視圖選擇的算法，默認算法是UNDEFINED；
3、view_name：要創(chuàng)建的視圖名稱；
4、column_list：可選參數(shù)，表示視圖的字段列表。如果省略，則使用select語句中的字段列表；
5、AS select_statement：創(chuàng)建視圖的select語句；
6、WITH CHECK OPTION：表示更新視圖時要保證該視圖的WHERE子句為【真】。比如：定義視圖create view v1 as select * from salary>5000;如果要更新視圖，則必須保證salary字段的值在5000以上，否則報錯；
二、數(shù)據(jù)準備
創(chuàng)建兩張表：部門（dept）和員工（emp），并插入數(shù)據(jù)，代碼如下：
create table dept(
dept_id int primary key auto_increment comment '部門編號',
dept_name char(20) comment '部門名稱'
);
insert into dept(dept_name) values('銷售部'),('財務(wù)部'),('生產(chǎn)部'),('人事部');
create table emp(
emp_id int primary key auto_increment comment '員工號',
emp_name char(20) not null default '' comment '員工姓名',
gender char(2) not null default '男' comment '性別',
birth datetime not null default '1990-1-1' comment '出生日期',
salary decimal(10,2) not null default 0 comment '工資',
address varchar(200) not null default '' comment '通訊地址',
dept_id int comment '部門編號'
);
create index idx_name on emp(emp_name);
create index idx_birth on emp(birth);
create index idx_deptid_name on emp(dept_id,emp_name);
insert into emp(emp_name,gender,birth,salary,address,dept_id)
values('張曉紅','女','1980-1-23',5800,'河南省鄭州市中原路10號',1),
('張靜靜','女','1987-10-3',5400,'河南省新鄉(xiāng)市平原路38號',1),
('王云飛','男','1992-11-15',5600,'河南省新鄉(xiāng)市人民路28號',1),
('王鵬飛','男','1987-10-1',6800,'河南省新鄉(xiāng)市東明大道12號',1),
('王大鵬','男','1989-2-11',5900,'河南省鄭州市東風路15號',1),
('王萌萌','女','1986-12-30',5000,'河南省開封市五一路14號',2),
('王大光','男','1988-11-8',6200,'河南省開封市八一路124號',2),
('王小明','男','1998-1-3',4800,'河南省駐馬店市雪松路128號',2),
('王娜娜','女','1994-3-5',5200,'河南省駐馬店市車站路2號',2),
('劉云飛','男','1992-8-13',6800,'河南省南陽市民生路255號',3),
('張陸軍','男','1991-9-6',6200,'河南省南陽市張仲景路14號',3);
三、創(chuàng)建視圖
（一）創(chuàng)建一個單表視圖
mysql> create or replace view v_emp_dept_id_1 as select emp_name,address
from emp where dept_id=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_emp_dept_id_1;
+-----------+-------------------------------------+
| emp_name | address |
+-----------+-------------------------------------+
| 張曉紅 | 河南省鄭州市中原路10號 |
| 張靜靜 | 河南省新鄉(xiāng)市平原路38號 |
| 王云飛 | 河南省新鄉(xiāng)市人民路28號 |
| 王大鵬 | 河南省鄭州市東風路15號 |
| 王鵬飛 | 河南省新鄉(xiāng)市東明大道12號 |
+-----------+-------------------------------------+
5 rows in set (0.00 sec)
（二）創(chuàng)建一個多表視圖
mysql> create or replace view v_emp_dept as select emp_name,address,dept_name
from emp join dept on emp.dept_id=dept.dept_id where dept.dept_id=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_emp_dept;
+-----------+-------------------------------------+-----------+
| emp_name | address | dept_name |
+-----------+-------------------------------------+-----------+
| 張曉紅 | 河南省鄭州市中原路10號 | 銷售部 |
| 張靜靜 | 河南省新鄉(xiāng)市平原路38號 | 銷售部 |
| 王云飛 | 河南省新鄉(xiāng)市人民路28號 | 銷售部 |
| 王大鵬 | 河南省鄭州市東風路15號 | 銷售部 |
| 王鵬飛 | 河南省新鄉(xiāng)市東明大道12號 | 銷售部 |
+-----------+-------------------------------------+-----------+
5 rows in set (0.00 sec)
（三）指定視圖中的列名
mysql> create or replace view v_dept_emp_count(dept_name,emp_count,avg_salary)
as select dept_name,count(*),avg(salary)
from empp join dept on emp.dept_id=dept.dept_id group by dept.dept_name;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from v_dept_emp_count;
+-----------+-----------+-------------+
| dept_name | emp_count | avg_salary |
+-----------+-----------+-------------+
| 生產(chǎn)部 | 2 | 6500.000000 |
| 財務(wù)部 | 4 | 5300.000000 |
| 銷售部 | 5 | 5900.000000 |
+-----------+-----------+-------------+
3 rows in set (0.00 sec)
（四）指定WITH CHECK OPTION參數(shù)
mysql> create view v_emp_dept_produce as select emp_name,salary from emp
-> where dept_id=(select dept_id from dept where dept_name='生產(chǎn)部')
-> with check option;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from v_emp_dept_produce;
+-----------+---------+
| emp_name | salary |
+-----------+---------+
| 劉云飛 | 6800.00 |
| 張陸軍 | 6200.00 |
+-----------+---------+
2 rows in set (0.01 sec)
四、查看視圖
（一）利用show tables查看視圖名稱
mysql> show tables; +--------------------+ | Tables_in_test | +--------------------+ | area | | dept | | emp | | goods | | orders | | orders_logs | | t1 | | v1 | | v_dept_emp_count | | v_emp_dept | | v_emp_dept_id_1 | | v_emp_dept_produce | +--------------------+ 12 rows in set (0.00 sec)
（二）利用desc查看視圖的字段信息
mysql> desc v_emp_dept; +-----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | emp_name | char(20) | NO | | | | | address | varchar(200) | NO | | | | | dept_name | char(20) | YES | | NULL | | +-----------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
（三）利用show table status語句查看視圖的基本信息
mysql> show table status like 'v_emp_dept'\G
*************************** 1. row ***************************
Name: v_emp_dept
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)
（四）利用show create view命令查看視圖的詳細信息
mysql> show create view v_emp_dept\G
*************************** 1. row ***************************
View: v_emp_dept
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_emp_dept` AS select `emp`.`emp_name` AS `emp_name`,`emp`.`address` AS `address`,`dept`.`dept_name` AS `dept_name` from (`emp` join `dept` on((`emp`.`dept_id` = `dept`.`dept_id`))) where (`dept`.`dept_id` = 1)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
五、修改視圖
（一）使用create or replace view命令修改視圖
mysql> create or replace view v_emp_dept as select emp_name,address,salary,dept_name
-> from emp join dept on emp.dept_id=dept.dept_id where dept.dept_id=1
-> with check option;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_emp_dept;
+-----------+-------------------------------------+---------+-----------+
| emp_name | address | salary | dept_name |
+-----------+-------------------------------------+---------+-----------+
| 張曉紅 | 河南省鄭州市中原路10號 | 5800.00 | 銷售部 |
| 張靜靜 | 河南省新鄉(xiāng)市平原路38號 | 5400.00 | 銷售部 |
| 王云飛 | 河南省新鄉(xiāng)市人民路28號 | 5600.00 | 銷售部 |
| 王大鵬 | 河南省鄭州市東風路15號 | 5900.00 | 銷售部 |
| 王鵬飛 | 河南省新鄉(xiāng)市東明大道12號 | 6800.00 | 銷售部 |
+-----------+-------------------------------------+---------+-----------+
5 rows in set (0.00 sec)
（二）使用alter view命令修改視圖
mysql> alter view v_emp_dept as select emp_name,address,salary,dept_name
-> from emp join dept on emp.dept_id=dept.dept_id where dept.dept_id=1
-> order by salary desc with check option;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_emp_dept;
+-----------+-------------------------------------+---------+-----------+
| emp_name | address | salary | dept_name |
+-----------+-------------------------------------+---------+-----------+
| 王鵬飛 | 河南省新鄉(xiāng)市東明大道12號 | 6800.00 | 銷售部 |
| 王大鵬 | 河南省鄭州市東風路15號 | 5900.00 | 銷售部 |
| 張曉紅 | 河南省鄭州市中原路10號 | 5800.00 | 銷售部 |
| 王云飛 | 河南省新鄉(xiāng)市人民路28號 | 5600.00 | 銷售部 |
| 張靜靜 | 河南省新鄉(xiāng)市平原路38號 | 5400.00 | 銷售部 |
+-----------+-------------------------------------+---------+-----------+
5 rows in set (0.00 sec)
六、更新視圖數(shù)據(jù)
因為視圖時虛擬表，所以更新視圖中的數(shù)據(jù)實際上是更新創(chuàng)建視圖時用到的基本表中的數(shù)據(jù)。
（一）更新單表視圖
mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 銷售部 |
| 2 | 財務(wù)部 |
| 3 | 生產(chǎn)部 |
| 4 | 人事部 |
+---------+-----------+
4 rows in set (0.00 sec)
mysql> select * from emp;
+--------+-----------+--------+---------------------+---------+--------------------------------------+---------+
| emp_id | emp_name | gender | birth | salary | address | dept_id |
+--------+-----------+--------+---------------------+---------+--------------------------------------+---------+
| 1 | 張曉紅 | 女 | 1980-01-23 00:00:00 | 5800.00 | 河南省鄭州市中原路10號 | 1 |
| 2 | 張靜靜 | 女 | 1987-10-03 00:00:00 | 5400.00 | 河南省新鄉(xiāng)市平原路38號 | 1 |
| 3 | 王云飛 | 男 | 1992-11-15 00:00:00 | 5600.00 | 河南省新鄉(xiāng)市人民路28號 | 1 |
| 4 | 王鵬飛 | 男 | 1987-10-01 00:00:00 | 6800.00 | 河南省新鄉(xiāng)市東明大道12號 | 1 |
| 5 | 王大鵬 | 男 | 1989-02-11 00:00:00 | 5900.00 | 河南省鄭州市東風路15號 | 1 |
| 6 | 王萌萌 | 女 | 1986-12-30 00:00:00 | 5000.00 | 河南省開封市五一路14號 | 2 |
| 7 | 王大光 | 男 | 1988-11-08 00:00:00 | 6200.00 | 河南省開封市八一路124號 | 2 |
| 8 | 王小明 | 男 | 1998-01-03 00:00:00 | 4800.00 | 河南省駐馬店市雪松路128號 | 2 |
| 9 | 王娜娜 | 女 | 1994-03-05 00:00:00 | 5200.00 | 河南省駐馬店市車站路2號 | 2 |
| 10 | 劉云飛 | 男 | 1992-08-13 00:00:00 | 6800.00 | 河南省南陽市民生路255號 | 3 |
| 11 | 張陸軍 | 男 | 1991-09-06 00:00:00 | 6200.00 | 河南省南陽市張仲景路14號 | 3 |
+--------+-----------+--------+---------------------+---------+--------------------------------------+---------+
11 rows in set (0.00 sec)
mysql> create view v_emp_1 as select emp_name,salary,address
from emp where salary>6000; --不帶 with check option 參數(shù)
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_emp_1;
+-----------+---------+-------------------------------------+
| emp_name | salary | address |
+-----------+---------+-------------------------------------+
| 王鵬飛 | 6800.00 | 河南省新鄉(xiāng)市東明大道12號 |
| 王大光 | 6200.00 | 河南省開封市八一路124號 |
| 劉云飛 | 6800.00 | 河南省南陽市民生路255號 |
| 張陸軍 | 6200.00 | 河南省南陽市張仲景路14號 |
+-----------+---------+-------------------------------------+
4 rows in set (0.00 sec)
mysql> update v_emp_1 set salary=3000 where emp_name='王鵬飛';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from v_emp_1;
+-----------+---------+-------------------------------------+
| emp_name | salary | address |
+-----------+---------+-------------------------------------+
| 王大光 | 6200.00 | 河南省開封市八一路124號 |
| 張陸軍 | 6200.00 | 河南省南陽市張仲景路14號 |
| 劉云飛 | 6800.00 | 河南省南陽市民生路255號 |
+-----------+---------+-------------------------------------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+--------+-----------+--------+---------------------+---------+--------------------------------------+---------+
| emp_id | emp_name | gender | birth | salary | address | dept_id |
+--------+-----------+--------+---------------------+---------+--------------------------------------+---------+
| 1 | 張曉紅 | 女 | 1980-01-23 00:00:00 | 5800.00 | 河南省鄭州市中原路10號 | 1 |
| 2 | 張靜靜 | 女 | 1987-10-03 00:00:00 | 5400.00 | 河南省新鄉(xiāng)市平原路38號 | 1 |
| 3 | 王云飛 | 男 | 1992-11-15 00:00:00 | 5600.00 | 河南省新鄉(xiāng)市人民路28號 | 1 |
| 4 | 王鵬飛 | 男 | 1987-10-01 00:00:00 | 3000.00 | 河南省新鄉(xiāng)市東明大道12號 | 1 |
| 5 | 王大鵬 | 男 | 1989-02-11 00:00:00 | 5900.00 | 河南省鄭州市東風路15號 | 1 |
| 6 | 王萌萌 | 女 | 1986-12-30 00:00:00 | 5000.00 | 河南省開封市五一路14號 | 2 |
| 7 | 王大光 | 男 | 1988-11-08 00:00:00 | 6200.00 | 河南省開封市八一路124號 | 2 |
| 8 | 王小明 | 男 | 1998-01-03 00:00:00 | 4800.00 | 河南省駐馬店市雪松路128號 | 2 |
| 9 | 王娜娜 | 女 | 1994-03-05 00:00:00 | 5200.00 | 河南省駐馬店市車站路2號 | 2 |
| 10 | 劉云飛 | 男 | 1992-08-13 00:00:00 | 6800.00 | 河南省南陽市民生路255號 | 3 |
| 11 | 張陸軍 | 男 | 1991-09-06 00:00:00 | 6200.00 | 河南省南陽市張仲景路14號 | 3 |
+--------+-----------+--------+---------------------+---------+--------------------------------------+---------+
11 rows in set (0.00 sec)
mysql> create or replace view v_emp_2 as select emp_name,salary,address
-> from emp where salary>5500 with check option; --添加 with check option 參數(shù)
Query OK, 0 rows affected (0.01 sec)
mysql> select * from v_emp_2;
+-----------+---------+-------------------------------------+
| emp_name | salary | address |
+-----------+---------+-------------------------------------+
| 張曉紅 | 5800.00 | 河南省鄭州市中原路10號 |
| 王云飛 | 5600.00 | 河南省新鄉(xiāng)市人民路28號 |
| 王大鵬 | 5900.00 | 河南省鄭州市東風路15號 |
| 王大光 | 6200.00 | 河南省開封市八一路124號 |
| 劉云飛 | 6800.00 | 河南省南陽市民生路255號 |
| 張陸軍 | 6200.00 | 河南省南陽市張仲景路14號 |
+-----------+---------+-------------------------------------+
6 rows in set (0.00 sec)
mysql> update v_emp_2 set salary=3000 where emp_name='王云飛';
ERROR 1369 (HY000): CHECK OPTION failed 'test.v_emp_2'
---由于違反了視圖定義的條件，則更新失敗
（二）更新多表視圖
mysql> create view v_emp_dept_11 as select emp_name,salary,dept.* from emp,dept where emp.dept_id=dept.dept_id; Query OK, 0 rows affected (0.01 sec) mysql> select * from v_emp_dept_11; +-----------+---------+---------+-----------+ | emp_name | salary | dept_id | dept_name | +-----------+---------+---------+-----------+ | 張曉紅 | 5800.00 | 1 | 銷售部 | | 張靜靜 | 5400.00 | 1 | 銷售部 | | 王云飛 | 5600.00 | 1 | 銷售部 | | 王鵬飛 | 3000.00 | 1 | 銷售部 | | 王大鵬 | 5900.00 | 1 | 銷售部 | | 王萌萌 | 5000.00 | 2 | 財務(wù)部 | | 王大光 | 6200.00 | 2 | 財務(wù)部 | | 王小明 | 4800.00 | 2 | 財務(wù)部 | | 王娜娜 | 5200.00 | 2 | 財務(wù)部 | | 劉云飛 | 6800.00 | 3 | 生產(chǎn)部 | | 張陸軍 | 6200.00 | 3 | 生產(chǎn)部 | +-----------+---------+---------+-----------+ 11 rows in set (0.00 sec) mysql> update v_emp_dept_11 set salary=10000 where emp_name='張靜靜'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update v_emp_dept_11 set dept_name='銷售事業(yè)部' where emp_name='張靜靜'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from v_emp_dept_11; +-----------+----------+---------+-----------------+ | emp_name | salary | dept_id | dept_name | +-----------+----------+---------+-----------------+ | 張曉紅 | 5800.00 | 1 | 銷售事業(yè)部 | | 張靜靜 | 10000.00 | 1 | 銷售事業(yè)部 | | 王云飛 | 5600.00 | 1 | 銷售事業(yè)部 | | 王鵬飛 | 3000.00 | 1 | 銷售事業(yè)部 | | 王大鵬 | 5900.00 | 1 | 銷售事業(yè)部 | | 王萌萌 | 5000.00 | 2 | 財務(wù)部 | | 王大光 | 6200.00 | 2 | 財務(wù)部 | | 王小明 | 4800.00 | 2 | 財務(wù)部 | | 王娜娜 | 5200.00 | 2 | 財務(wù)部 | | 劉云飛 | 6800.00 | 3 | 生產(chǎn)部 | | 張陸軍 | 6200.00 | 3 | 生產(chǎn)部 | +-----------+----------+---------+-----------------+ 11 rows in set (0.00 sec)
七、刪除視圖
drop view [if exists] view_name; drop view v_emp_dept_11;
總結(jié)
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關(guān)文章
這篇文章主要給大家介紹了關(guān)于mysql自聯(lián)去重的一些筆記記錄，文中通過示例代碼介紹的非常詳細，對大家學習或者使用mysql具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-06-06
索引可以說是數(shù)據(jù)庫中的一個大心臟了，如果說一個數(shù)據(jù)庫少了索引，那么數(shù)據(jù)庫本身存在的意義就不大了，和普通的文件沒什么兩樣,本文從細節(jié)和實際業(yè)務(wù)的角度看看在MySQL中B+樹索引好處2021-11-11
Mysql存儲過程如何實現(xiàn)歷史數(shù)據(jù)遷移
這篇文章主要介紹了Mysql存儲過程如何實現(xiàn)歷史數(shù)據(jù)遷移，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-01-01
MySQL Installer 8.0.21安裝教程圖文詳解
這篇文章主要介紹了MySQL Installer 8.0.21安裝教程,本文通過圖文并茂的形式給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2020-08-08
在某些情況下,即使存在索引,查詢也可能不會使用到索引,從而導致查詢效率降低,下面就來介紹一下Mysql什么情況下不會命中索引,感興趣的可以了解一下2025-04-04
Mysql如何對json數(shù)據(jù)進行查詢及修改
這篇文章主要介紹了Mysql如何對json數(shù)據(jù)進行查詢及修改，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-07-07
數(shù)據(jù)庫市場需要細分,行式數(shù)據(jù)庫不再滿足所有的需求,而有很多需求需要,下面這篇文章主要給大家介紹了關(guān)于mysql單列多值存儲的相關(guān)資料,文中通過示例代碼介紹介紹的非常詳細,需要的朋友可以參考下2022-04-04
MySQL 提供了?IS NULL?關(guān)鍵字,用來判斷字段的值是否為空值,本文主要介紹了MySQL IS NULL空值查詢的實現(xiàn),文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2024-08-08
MySQL提供了多種日期和時間類型,在處理時間時需要根據(jù)不同類型選擇不同的格式化方法,常用的日期類型有DATE、YEAR、TIME；常用的日期時間類型有DATETIME和TIMESTAMP,本文就來介紹一下MySQL 格式化時間,感興趣的可以了解一下2023-10-10
錯誤原因：使用雙引號定義表名和列名導致MySQL報錯,應(yīng)使用反引號,修改方案：將雙引號改為反引號,避免語法沖突,總結(jié)：在MySQL中,正確使用反引號引用標識符,確保SQL語句符合MySQL語法規(guī)則2024-10-10