當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL视图（view）基本用法

發布時間：2023/12/20 数据库 27 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL视图（view）基本用法小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

MySQL視圖（view）基本用法

MySQL中的視圖（view）是一種虛擬表，其內容由查詢定義，是一個邏輯表，本身并不包含數據。視圖看起來和真實的表完全相同，但其中的數據來自定義視圖時用到的基本表，并且在打開視圖時動態生成。

視圖可以在基本表上定義，也可以使用其他視圖定義。與直接操作基本表相比，視圖具備以下優點：
（1）簡化操作：通過視圖可以使用戶將注意力集中在他所關心的數據上。使用視圖的用戶完全不需要關心后面對應的表的結構、關聯條件和篩選條件。
（2）提高數據的安全性：在設計數據庫時可以針對不同的用戶定義不同的視圖，使用視圖的用戶只能訪問他們被允許查詢的結果集。
（3）數據獨立：視圖的結構定義好之后，如果增加新的關系或對原有的關系增加新的字段對用戶訪問的數據都不會造成影響。

一、創建視圖的語法

創建視圖的基本語法如下：

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：可選參數，表示視圖選擇的算法，默認算法是UNDEFINED；
3、view_name：要創建的視圖名稱；
4、column_list：可選參數，表示視圖的字段列表。如果省略，則使用select語句中的字段列表；
5、AS select_statement：創建視圖的select語句；
6、WITH CHECK OPTION：表示更新視圖時要保證該視圖的WHERE子句為【真】。比如：定義視圖create view v1 as select * from salary>5000;如果要更新視圖，則必須保證salary字段的值在5000以上，否則報錯；

二、數據準備

創建兩張表：部門（dept）和員工（emp），并插入數據，代碼如下：

create table dept(dept_id int primary key auto_increment comment '部門編號',dept_name char(20) comment '部門名稱' ); insert into dept(dept_name) values('銷售部'),('財務部'),('生產部'),('人事部');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,'河南省新鄉市平原路38號',1), ('王云飛','男','1992-11-15',5600,'河南省新鄉市人民路28號',1), ('王鵬飛','男','1987-10-1',6800,'河南省新鄉市東明大道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);

三、創建視圖

（一）創建一個單表視圖

（二）創建一個多表視圖

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號 | 銷售部 | | 張靜靜 | 河南省新鄉市平原路38號 | 銷售部 | | 王云飛 | 河南省新鄉市人民路28號 | 銷售部 | | 王大鵬 | 河南省鄭州市東風路15號 | 銷售部 | | 王鵬飛 | 河南省新鄉市東明大道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 | +-----------+-----------+-------------+ | 生產部 | 2 | 6500.000000 | | 財務部 | 4 | 5300.000000 | | 銷售部 | 5 | 5900.000000 | +-----------+-----------+-------------+ 3 rows in set (0.00 sec)

（四）指定WITH CHECK OPTION參數

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='生產部')-> 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查看視圖名稱

（二）利用desc查看視圖的字段信息

（三）利用show table status語句查看視圖的基本信息

mysql> show table status like 'v_emp_dept'\G *************************** 1. row ***************************Name: v_emp_deptEngine: NULLVersion: NULLRow_format: NULLRows: NULLAvg_row_length: NULLData_length: NULL Max_data_length: NULLIndex_length: NULLData_free: NULLAuto_increment: NULLCreate_time: NULLUpdate_time: NULLCheck_time: NULLCollation: NULLChecksum: NULLCreate_options: NULLComment: VIEW 1 row in set (0.01 sec)

（四）利用show create view命令查看視圖的詳細信息

mysql> show create view v_emp_dept\G *************************** 1. row ***************************View: v_emp_deptCreate 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 | 銷售部 | | 張靜靜 | 河南省新鄉市平原路38號 | 5400.00 | 銷售部 | | 王云飛 | 河南省新鄉市人民路28號 | 5600.00 | 銷售部 | | 王大鵬 | 河南省鄭州市東風路15號 | 5900.00 | 銷售部 | | 王鵬飛 | 河南省新鄉市東明大道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 | +-----------+-------------------------------------+---------+-----------+ | 王鵬飛 | 河南省新鄉市東明大道12號 | 6800.00 | 銷售部 | | 王大鵬 | 河南省鄭州市東風路15號 | 5900.00 | 銷售部 | | 張曉紅 | 河南省鄭州市中原路10號 | 5800.00 | 銷售部 | | 王云飛 | 河南省新鄉市人民路28號 | 5600.00 | 銷售部 | | 張靜靜 | 河南省新鄉市平原路38號 | 5400.00 | 銷售部 | +-----------+-------------------------------------+---------+-----------+ 5 rows in set (0.00 sec)

六、更新視圖數據

因為視圖時虛擬表，所以更新視圖中的數據實際上是更新創建視圖時用到的基本表中的數據。

（一）更新單表視圖

mysql> select * from dept; +---------+-----------+ | dept_id | dept_name | +---------+-----------+ | 1 | 銷售部 | | 2 | 財務部 | | 3 | 生產部 | | 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 | 河南省新鄉市平原路38號 | 1 | | 3 | 王云飛 | 男 | 1992-11-15 00:00:00 | 5600.00 | 河南省新鄉市人民路28號 | 1 | | 4 | 王鵬飛 | 男 | 1987-10-01 00:00:00 | 6800.00 | 河南省新鄉市東明大道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 參數 Query OK, 0 rows affected (0.00 sec)mysql> select * from v_emp_1; +-----------+---------+-------------------------------------+ | emp_name | salary | address | +-----------+---------+-------------------------------------+ | 王鵬飛 | 6800.00 | 河南省新鄉市東明大道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: 0mysql> 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 | 河南省新鄉市平原路38號 | 1 | | 3 | 王云飛 | 男 | 1992-11-15 00:00:00 | 5600.00 | 河南省新鄉市人民路28號 | 1 | | 4 | 王鵬飛 | 男 | 1987-10-01 00:00:00 | 3000.00 | 河南省新鄉市東明大道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 參數 Query OK, 0 rows affected (0.01 sec)mysql> select * from v_emp_2; +-----------+---------+-------------------------------------+ | emp_name | salary | address | +-----------+---------+-------------------------------------+ | 張曉紅 | 5800.00 | 河南省鄭州市中原路10號 | | 王云飛 | 5600.00 | 河南省新鄉市人民路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 | 財務部 | | 王大光 | 6200.00 | 2 | 財務部 | | 王小明 | 4800.00 | 2 | 財務部 | | 王娜娜 | 5200.00 | 2 | 財務部 | | 劉云飛 | 6800.00 | 3 | 生產部 | | 張陸軍 | 6200.00 | 3 | 生產部 | +-----------+---------+---------+-----------+ 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: 0mysql> update v_emp_dept_11 set dept_name='銷售事業部' where emp_name='張靜靜'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from v_emp_dept_11; +-----------+----------+---------+-----------------+ | emp_name | salary | dept_id | dept_name | +-----------+----------+---------+-----------------+ | 張曉紅 | 5800.00 | 1 | 銷售事業部 | | 張靜靜 | 10000.00 | 1 | 銷售事業部 | | 王云飛 | 5600.00 | 1 | 銷售事業部 | | 王鵬飛 | 3000.00 | 1 | 銷售事業部 | | 王大鵬 | 5900.00 | 1 | 銷售事業部 | | 王萌萌 | 5000.00 | 2 | 財務部 | | 王大光 | 6200.00 | 2 | 財務部 | | 王小明 | 4800.00 | 2 | 財務部 | | 王娜娜 | 5200.00 | 2 | 財務部 | | 劉云飛 | 6800.00 | 3 | 生產部 | | 張陸軍 | 6200.00 | 3 | 生產部 | +-----------+----------+---------+-----------------+ 11 rows in set (0.00 sec)

七、刪除視圖

drop view [if exists] view_name; drop view v_emp_dept_11;

總結

以上是生活随笔為你收集整理的MySQL视图（view）基本用法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： java 毫秒转时分秒格式（Java中将
下一篇：网络安全最常用的防护措施！