需求背景
電商公司領導說:給我統計一下:當月訂單總金額、訂單量、男女訂單佔比等資訊,我們啪啦啪啦寫了一堆很複雜的sql,然後發給領導。
這樣一大片sql,發給領導,你們覺得好麼?
如果領導只想看其中某個資料,還需要修改你發來的sql,領導日後想新增其他的統計指標,你又會傳送一大坨sql給領導,對於領導來說這個sql看起來很複雜,難以維護。
實際上領導並不關心你是怎麼實現的,他關心的只是這些指標,並且方便檢視、查詢,而你卻把複雜的實現都發給了領導。
那我們有什麼辦法隱藏這些細節,只暴露簡潔的結果呢?
資料庫已經幫我們想到了:使用檢視來解決這個問題。
什麼是檢視
概念
檢視是在mysql5之後出現的,是一種虛擬表,行和列的資料來自於定義檢視時使用的一些表中,檢視的資料是在使用檢視的時候動態生成的,檢視只儲存了sql的邏輯,不儲存查詢的結果。
使用場景
多個地方使用到同樣的查詢結果,並且該查詢結果比較複雜的時候,我們可以使用檢視來隱藏複雜的實現細節。
檢視和表的區別
|
語法 |
實際中是否佔用物理空間 |
使用 |
檢視 |
create view |
只是儲存了sql的邏輯 |
增刪改查,實際上我們只使用查詢 |
表 |
create table |
儲存了資料 |
增刪改查 |
檢視的好處
準備測試資料
測試資料比較多,放在我的個人部落格上了。
瀏覽器中開啟連結:
mysql中執行裡面的javacode2018_employees庫部分的指令碼。
成功建立javacode2018_employees庫及5張表,如下:
表名 |
描述 |
departments |
部門表 |
employees |
員工資訊表 |
jobs |
職位資訊表 |
locations |
位置表(部門表中會用到) |
job_grades |
薪資等級表 |
建立檢視
語法
create view 檢視名as查詢語句;123
檢視的使用步驟
案例1
查詢姓名中包含a字元的員工名、部門、工種資訊
/*案例1:查詢姓名中包含a字元的員工名、部門、工種資訊*//*①建立檢視myv1*/CREATE VIEW myv1AS
SELECT
t1.last_name,
t2.department_name,
t3.job_title FROM employees t1, departments t2, jobs t3 WHERE t1.department_id = t2.department_id AND t1.job_id = t3.job_id;/*②使用檢視*/SELECT * FROM myv1 a where a.last_name like 'a%';1234567891011121314
效果如下:
mysql> SELECT * FROM myv1 a where a.last_name like 'a%';+-----------+-----------------+----------------------+| last_name | department_name | job_title |+-----------+-----------------+----------------------+| Austin | IT | Programmer || Atkinson | Shi | Stock Clerk || Ande | Sal | Sales Representative || Abel | Sal | Sales Representative |+-----------+-----------------+----------------------+4 rows in set (0.00 sec)12345678910
上面我們建立了一個檢視:myv1,我們需要看員工姓名、部門、工種資訊的時候,不用關心這個檢視內部是什麼樣的,只需要查詢檢視就可以了,sql簡單多了。
案例2
案例2:查詢各部門的平均工資級別
/*案例2:查詢各部門的平均工資級別*//*①建立檢視myv1*/CREATE VIEW myv2AS
SELECT
t1.department_id 部門id,
t1.ag 平均工資,
t2.grade_level 工資級別 FROM (SELECT
department_id,
AVG(salary) ag FROM employees GROUP BY department_id)
t1, job_grades t2 WHERE t1.ag BETWEEN t2.lowest_sal AND t2.highest_sal;/*②使用檢視*/SELECT * FROM myv2;123456789101112131415161718
效果:
mysql> SELECT * FROM myv2;+----------+--------------+--------------+| 部門id | 平均工資 | 工資級別 |+----------+--------------+--------------+| NULL | 7000.000000 | C || 10 | 4400.000000 | B || 20 | 9500.000000 | C || 30 | 4150.000000 | B || 40 | 6500.000000 | C || 50 | 3475.555556 | B || 60 | 5760.000000 | B || 70 | 10000.000000 | D || 80 | 8955.882353 | C || 90 | 19333.333333 | E || 100 | 8600.000000 | C || 110 | 10150.000000 | D |+----------+--------------+--------------+12 rows in set (0.00 sec)123456789101112131415161718
修改檢視
方式1
如果該檢視存在,就修改,如果不存在,就建立新的檢視。
create or replace view 檢視名as查詢語句;123
示例
CREATE OR REPLACE VIEW myv3AS
SELECT
job_id,
AVG(salary) javg FROM employees GROUP BY job_id;1234567
方式2
alter view 檢視名as 查詢語句;123
示例
ALTER VIEW myv3ASSELECT *FROM employees;1234
刪除檢視
語法
drop view 檢視名1 [,檢視名2] [,檢視名n];1
可以同時刪除多個檢視,多個檢視名稱之間用逗號隔開。
示例
mysql> drop view myv1,myv2,myv3;Query OK, 0 rows affected (0.00 sec)12
查詢檢視結構
/*方式1*/desc 檢視名稱;/*方式2*/show create view 檢視名稱;1234
如:
anhui/
mysql> desc myv1;+-----------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------------+-------------+------+-----+---------+-------+| last_name | varchar(25) | YES | | NULL | || department_name | varchar(3) | YES | | NULL | || job_title | varchar(35) | YES | | NULL | |+-----------------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> show create view myv1;+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+| View | Create View | character_set_client | collation_connection |+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+| myv1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `myv1` AS select `t1`.`last_name` AS `last_name`,`t2`.`department_name` AS `department_name`,`t3`.`job_title` AS `job_title` from ((`employees` `t1` join `departments` `t2`) join `jobs` `t3`) where ((`t1`.`department_id` = `t2`.`department_id`) and (`t1`.`job_id` = `t3`.`job_id`)) | utf8 | utf8_general_ci |+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+1 row in set (0.00 sec)12345678910111213141516
show create view顯示了檢視的建立語句。
更新檢視【基本不用】
檢視的更新是更改檢視中的資料,而不是更改檢視中的sql邏輯。
當對檢視進行更新後,也會對原始表的資料進行更新。
為了防止對原始表的資料產生更新,可以為檢視新增只讀許可權,只允許讀檢視,不允許對檢視進行更新。
一般情況下,極少對檢視進行更新操作。
示例
CREATE OR REPLACE VIEW myv4 AS
SELECT last_name,email from employees;/*插入*/insert into myv4 VALUES ('路人甲Java','javacode2018@163.com');SELECT * from myv4 where email like 'javacode2018%';/*修改*/UPDATE myv4 SET last_name = '劉德華' WHERE last_name = '路人甲Java';SELECT * from myv4 where email like 'javacode2018%';/*刪除*/DELETE FROM myv4 where last_name = '劉德華';SELECT * from myv4 where email like 'javacode2018%';12345678910111213141516
注意:檢視的更新我們一般不使用,瞭解即可。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30239065/viewspace-2725609/,如需轉載,請註明出處,否則將追究法律責任。