Mysql系列第十四講 檢視詳解

qwer1030274531發表於2020-10-08

需求背景

電商公司領導說:給我統計一下:當月訂單總金額、訂單量、男女訂單佔比等資訊,我們啪啦啪啦寫了一堆很複雜的sql,然後發給領導。

這樣一大片sql,發給領導,你們覺得好麼?

如果領導只想看其中某個資料,還需要修改你發來的sql,領導日後想新增其他的統計指標,你又會傳送一大坨sql給領導,對於領導來說這個sql看起來很複雜,難以維護。

實際上領導並不關心你是怎麼實現的,他關心的只是這些指標,並且方便檢視、查詢,而你卻把複雜的實現都發給了領導。

那我們有什麼辦法隱藏這些細節,只暴露簡潔的結果呢?

資料庫已經幫我們想到了:使用檢視來解決這個問題。

什麼是檢視

概念

檢視是在mysql5之後出現的,是一種虛擬表,行和列的資料來自於定義檢視時使用的一些表中,檢視的資料是在使用檢視的時候動態生成的,檢視只儲存了sql的邏輯,不儲存查詢的結果。

使用場景

多個地方使用到同樣的查詢結果,並且該查詢結果比較複雜的時候,我們可以使用檢視來隱藏複雜的實現細節。

檢視和表的區別

語法 實際中是否佔用物理空間 使用
檢視 create view 只是儲存了sql的邏輯 增刪改查,實際上我們只使用查詢
create table 儲存了資料 增刪改查

檢視的好處

  • 簡化複雜的sql操作,不用知道他的實現細節

  • 隔離了原始表,可以不讓使用檢視的人接觸原始的表,從而保護原始資料,提高了安全性

準備測試資料

測試資料比較多,放在我的個人部落格上了。

瀏覽器中開啟連結:

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章