《MySQL數(shù)據(jù)庫(kù)應(yīng)用案例教程》教學(xué)課件第12章視圖

1、12視圖第章第1頁(yè)，共33頁(yè)。12.1視圖概述第2頁(yè)，共33頁(yè)。視圖是從數(shù)據(jù)庫(kù)中的一張或多張表中導(dǎo)出的表。創(chuàng)建視圖時(shí)所引用的表稱為基表。視圖中的數(shù)據(jù)并不是像表中那樣存儲(chǔ)在數(shù)據(jù)庫(kù)中，它只是讀取基表中的數(shù)據(jù)。對(duì)視圖的操作與對(duì)表的操作一樣，可以對(duì)其進(jìn)行查詢、修改和刪除等操作。在對(duì)視圖中的數(shù)據(jù)進(jìn)行修改時(shí)，相應(yīng)的基本表中的數(shù)據(jù)也會(huì)發(fā)生變化；同時(shí)，若基本表的數(shù)據(jù)發(fā)生變化，則這種變化也會(huì)反映到視圖中。與直接操作數(shù)據(jù)表相比，視圖主要有以下優(yōu)勢(shì)：簡(jiǎn)單：視圖中看到的即是需要的，用戶完全不需要考慮對(duì)應(yīng)的表結(jié)構(gòu)、關(guān)聯(lián)條件和篩選條件。對(duì)于用戶來(lái)說(shuō)，經(jīng)常使用的查詢可以被定義為視圖。安全：視圖可以作為一種安全機(jī)制。通過(guò)視圖

2、，用戶只能查看和修改他們所看到的數(shù)據(jù)，數(shù)據(jù)庫(kù)中的其他數(shù)據(jù)或表既看不見(jiàn)也不可以訪問(wèn)，這樣用戶就被限制在了數(shù)據(jù)的不同子集上。數(shù)據(jù)獨(dú)立：視圖可以使應(yīng)用程序和數(shù)據(jù)表在一定程度上獨(dú)立，創(chuàng)建視圖之后，應(yīng)用程序可以建立在視圖之上，從而使程序與數(shù)據(jù)表分割開(kāi)來(lái)，這樣就可以屏蔽表結(jié)構(gòu)變化帶來(lái)的影響。第3頁(yè)，共33頁(yè)。12.2創(chuàng)建視圖第4頁(yè)，共33頁(yè)。視圖可以以一張表為基礎(chǔ)創(chuàng)建，也可以以多張表為基礎(chǔ)創(chuàng)建。創(chuàng)建視圖使用CREATE VIEW語(yǔ)句，其基本語(yǔ)法格式如下：CREATE OR REPLACE VIEW view_name (column_list) AS SELECT_statement;創(chuàng)建視圖時(shí)應(yīng)注意以下

3、幾點(diǎn)：定義中引用的任何表或視圖都必須存在。如果在創(chuàng)建視圖后，刪除定義時(shí)引用的表或視圖，則使用視圖時(shí)將導(dǎo)致錯(cuò)誤。創(chuàng)建視圖不能引用臨時(shí)表。SELECT語(yǔ)句中最大列名長(zhǎng)度為64個(gè)字符。第5頁(yè)，共33頁(yè)。在MySQL中，可以在單個(gè)數(shù)據(jù)表上創(chuàng)建視圖，這也是最簡(jiǎn)單的一種創(chuàng)建方法。12.2.1 在單表上創(chuàng)建視圖CREATE VIEW v_table AS SELECT id,name FROM goods;【實(shí)例12-1】以第5章中“實(shí)例5-1”創(chuàng)建的goods表為基表創(chuàng)建視圖。步驟1 啟動(dòng)并登錄MySQL。步驟3 執(zhí)行SQL語(yǔ)句，基于goods表創(chuàng)建視圖v_table。步驟2 執(zhí)行以下語(yǔ)句，選擇數(shù)據(jù)庫(kù)db

4、_shop。USE db_shop;mysql SELECT * FROM v_table;+-+-+| id | name|+-+-+| 4| 休閑西服| 5| 果汁| 3| 水果糖| 9| 牛仔褲 | 2| 牛奶糖 | 10| 紅樓夢(mèng) | 8| 蘋果 | 1| 西游記 | 7| 西瓜 | 6| 論語(yǔ) |+-+-+10 rows in set (0.05 sec)步驟4 執(zhí)行SELECT語(yǔ)句，查詢視圖v_table。SQL語(yǔ)句及其執(zhí)行結(jié)果如下：第6頁(yè)，共33頁(yè)。默認(rèn)情況下，視圖的字段名與基表的字段名相同。但是，為了增加數(shù)據(jù)安全性，也可以為視圖字段指定不同的名稱。12.2.1 在單表上創(chuàng)建視圖

5、mysql SELECT * FROM v_table2;+-+-+| goods_id| goods_name|+-+-+| 4| 休閑西服 | 5| 果汁 | 3 | 水果糖 | 9 | 牛仔褲 | 2 | 牛奶糖 | 10 | 紅樓夢(mèng) | 8 | 蘋果 | 1 | 西游記 | 7 | 西瓜 | 6 | 論語(yǔ) |+-+-+10 rows in set (0.00 sec)【實(shí)例12-2】以第5章中“實(shí)例5-1”創(chuàng)建的goods表為基表創(chuàng)建視圖，并重新為視圖字段命名。SQL語(yǔ)句如下：CREATE VIEW v_table2(goods_id,goods_name) AS SELECT id,

6、name FROM goods;語(yǔ)句執(zhí)行成功，查看v_table2視圖中的數(shù)據(jù)，結(jié)果如下：第7頁(yè)，共33頁(yè)。12.2.2 在多表上創(chuàng)建視圖mysql SELECT * FROM v_table3;+-+-+| o_id| name |+-+-+| 2 | 牛奶糖 | 4 | 休閑西服 | 6 | 論語(yǔ) | 3 | 水果糖 | 1 | 西游記 | 5 | 果汁 |+-+-+6 rows in set (0.03 sec)【實(shí)例12-3】以第5章中“實(shí)例5-1”創(chuàng)建的goods表和“實(shí)例5-2”創(chuàng)建的orders表為基表，創(chuàng)建視圖，并重新為視圖字段命名。SQL語(yǔ)句如下：CREATE VIEW v_

7、table3(o_id,name) AS SELECT orders.o_id, FROM goods JOIN orders ON goods.id=orders.o_id;語(yǔ)句執(zhí)行成功后，查看v_table3視圖中的數(shù)據(jù)，結(jié)果如下：第8頁(yè)，共33頁(yè)。使用Navicat for MySQL也可以創(chuàng)建視圖，具體操作步驟如下：12.2.3 使用圖形化工具創(chuàng)建視圖步驟1 啟動(dòng)Navicat for MySQL并連接MySQL后，雙擊需要操作的數(shù)據(jù)庫(kù)，此處為db_shop。步驟2 單擊“視圖”按鈕，顯示前面創(chuàng)建的視圖。單擊“新建視圖”按鈕，可以打開(kāi)視圖編輯界面，如圖12-1所示。

8、第9頁(yè)，共33頁(yè)。使用Navicat for MySQL也可以創(chuàng)建視圖，具體操作步驟如下：12.2.3 使用圖形化工具創(chuàng)建視圖步驟3 切換到“視圖創(chuàng)建工具”選項(xiàng)卡，在左側(cè)雙擊要?jiǎng)?chuàng)建視圖的基本表，將其添加至右側(cè)窗口，之后在右側(cè)相應(yīng)字段的復(fù)選框中單擊可選擇要在視圖中顯示的字段，最后在右下角的窗口中進(jìn)一步編輯創(chuàng)建視圖的SQL語(yǔ)句，如圖12-2所示。步驟4 編輯好后單擊“保存”按鈕，在彈出的“視圖名”對(duì)話框中輸入視圖名，并單擊“確定”按鈕即可。第10頁(yè)，共33頁(yè)。12.3查看視圖第11頁(yè)，共33頁(yè)。使用DESC語(yǔ)句可以查看視圖的結(jié)構(gòu)，其基本語(yǔ)法格式如下：DESC 視圖名;mysql DESC v_ta

9、ble;+-+-+-+-+-+-+| Field| Type | Null| Key | Default | Extra|+-+-+-+-+-+-+| id | int(11) | NO | | 0 | | name | char(30)| YES | | NULL| |+-+-+-+-+-+-+2 rows in set (0.02 sec)【實(shí)例12-4】使用DESC語(yǔ)句查看視圖v_table的結(jié)構(gòu)。SQL語(yǔ)句及其執(zhí)行結(jié)果如下：12.3.1 查看視圖基本信息第12頁(yè)，共33頁(yè)。mysql DESC goods;+-+-+-+-+-+-+| Field | Type | Null | Key

10、| Default| Extra|+-+-+-+-+-+-+| id | int(11) | NO | PRI | NULL| auto_increment| type | char(30) | YES | | NULL| | name | char(30) | YES | UNI| NULL | | price | decimal(7,2) unsigned| YES | | NULL | | num | int(11) | YES | | 0 | | add_time| datetime | YES | | NULL| |+-+-+-+-+-+-+6 rows in set (0.00 s

11、ec)【實(shí)例12-5】使用DESC語(yǔ)句查看基本表goods的結(jié)構(gòu)。SQL語(yǔ)句及其執(zhí)行結(jié)果如下：12.3.1 查看視圖基本信息第13頁(yè)，共33頁(yè)。使用SHOW CREATE VIEW語(yǔ)句可以查看視圖定義語(yǔ)句，其基本語(yǔ)法格式如下：SHOW CREATE VIEW 視圖名;mysql SHOW CREATE VIEW v_table2 G* 1. row * View: v_table2 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=rootlocalhost SQL SECURITY DEFINER VIEW v_table2 AS select

12、goods.id AS goods_id, AS goods_name from goods character_set_client: gbkcollation_connection: gbk_chinese_ci1 row in set (0.00 sec)【實(shí)例12-6】使用SHOW CREATE VIEW語(yǔ)句查看視圖v_table2的定義語(yǔ)句。SQL語(yǔ)句及其執(zhí)行結(jié)果如下：12.3.2 查看視圖定義語(yǔ)句第14頁(yè)，共33頁(yè)。在MySQL中，所有視圖的詳細(xì)信息都存儲(chǔ)在系統(tǒng)數(shù)據(jù)庫(kù)information_schema下的views表中。通過(guò)views表，可以查看數(shù)據(jù)庫(kù)中所有視

13、圖的詳細(xì)信息。mysql SELECT * FROM information_schema.views G* 1. row * TABLE_CATALOG: def TABLE_SCHEMA: db_shop TABLE_NAME: v_table VIEW_DEFINITION: select db_shop.goods.id AS id,db_ AS name from db_shop.goods CHECK_OPTION: NONE IS_UPDATABLE: YES DEFINER: rootlocalhost SECURITY_TYPE: DEFINER

14、CHARACTER_SET_CLIENT: gbkCOLLATION_CONNECTION: gbk_chinese_ci* 2. row * TABLE_CATALOG: def TABLE_SCHEMA: db_shop TABLE_NAME: v_table2【實(shí)例12-7】通過(guò)views表查看數(shù)據(jù)庫(kù)中視圖的詳細(xì)信息。SQL語(yǔ)句及其執(zhí)行結(jié)果如下：12.3.3 通過(guò)views表查看視圖詳細(xì)信息第15頁(yè)，共33頁(yè)。12.3.4 使用圖形化工具查看視圖使用Navicat for MySQL也可以查看視圖，具體操作步驟如下：步驟1 啟動(dòng)Navicat for MySQL并連接MySQL后，雙擊需

15、要操作的數(shù)據(jù)庫(kù)，此處為db_shop。步驟2 單擊“視圖”按鈕，然后右擊要查看的視圖，在彈出的快捷菜單中選擇“對(duì)象信息”，如圖12-3所示。第16頁(yè)，共33頁(yè)。12.3.4 使用圖形化工具查看視圖步驟3 “常規(guī)”選項(xiàng)卡中顯示了視圖的相關(guān)參數(shù)信息，“DDL”選項(xiàng)卡中顯示視圖的定義語(yǔ)句，如圖12-4所示。第17頁(yè)，共33頁(yè)。12.4修改視圖第18頁(yè)，共33頁(yè)。當(dāng)基本表的某些字段發(fā)生改變時(shí)，可以通過(guò)修改視圖來(lái)保持與基本表的一致性。MySQL提供CREATE OR REPLACE VIEW和ALTER語(yǔ)句來(lái)修改視圖。使用CREATE OR REPLACE VIEW語(yǔ)句修改視圖的語(yǔ)法形式如下：12.4.

16、1 使用CREATE OR REPLACE VIEW語(yǔ)句修改視圖CREATE OR REPLACE VIEW view_name (column_list) AS SELECT_statement;【實(shí)例12-8】使用CREATE OR REPLACE VIEW語(yǔ)句修改視圖v_table。步驟1 執(zhí)行以下語(yǔ)句，選擇數(shù)據(jù)庫(kù)db_shop，并使用DESC語(yǔ)句查看視圖結(jié)構(gòu)。USE db_shop;mysql DESC v_table;+-+-+-+-+-+-+| Field| Type | Null| Key | Default | Extra|+-+-+-+-+-+-+| id | int(11)

17、 | NO | | 0 | | name | char(30)| YES | | NULL| |+-+-+-+-+-+-+2 rows in set (0.02 sec)第19頁(yè)，共33頁(yè)。12.4.1 使用CREATE OR REPLACE VIEW語(yǔ)句修改視圖mysql DESC v_table;+-+-+-+-+-+-+| Field | Type | Null| Key| Default| Extra|+-+-+-+-+-+-+| id | int(11) | NO | | 0 | | name | char(30) | YES | | NULL | | price | decimal

18、(7,2) unsigned| YES | | NULL | |+-+-+-+-+-+-+3 rows in set (0.00 sec)步驟3 使用DESC語(yǔ)句查看視圖結(jié)構(gòu)。SQL語(yǔ)句及其執(zhí)行結(jié)果如下：步驟2 使用CREATE OR REPLACE VIEW語(yǔ)句修改視圖v_table。SQL語(yǔ)句及其執(zhí)行結(jié)果如下：mysql CREATE OR REPLACE VIEW v_table AS SELECT id,name,price FROM goods;Query OK, 0 rows affected (0.03 sec)第20頁(yè)，共33頁(yè)。12.4.2 使用ALTER語(yǔ)句修改視圖ALTE

19、R VIEW view_name (column_list) AS SELECT_statement;【實(shí)例12-9】使用ALTER語(yǔ)句修改視圖v_table2。步驟1 執(zhí)行以下語(yǔ)句，選擇數(shù)據(jù)庫(kù)db_shop，并使用DESC語(yǔ)句查看視圖結(jié)構(gòu)。USE db_shop;mysql DESC v_table2;+-+-+-+-+-+-+| Field | Type | Null | Key | Default | Extra |+-+-+-+-+-+-+| goods_id | int(11) | NO | | 0 | | goods_name | varchar(30) | YES | | NUL

20、L | |+-+-+-+-+-+-+2 rows in set (0.00 sec)第21頁(yè)，共33頁(yè)。12.4.2 使用ALTER語(yǔ)句修改視圖步驟2 執(zhí)行ALTER語(yǔ)句，修改視圖v_table2。SQL語(yǔ)句及其執(zhí)行結(jié)果如下：mysql ALTER VIEW v_table2(goods_id,goods_name,goods_price) AS SELECT id,name,price FROM goods;Query OK, 0 rows affected (0.04 sec)步驟3 使用DESC語(yǔ)句查看視圖結(jié)構(gòu)。SQL語(yǔ)句及其執(zhí)行結(jié)果如下：mysql DESC v_table2;+-+-

21、+-+-+-+-+| Field | Type | Null | Key| Default | Extra|+-+-+-+-+-+-+| goods_id | int(11) | NO | | 0 | | goods_name| char(30) | YES| | NULL | | goods_price| decimal(7,2) unsigned| YES| | NULL | |+-+-+-+-+-+-+3 rows in set (0.00 sec)第22頁(yè)，共33頁(yè)。12.4.3 使用圖形化工具修改視圖步驟1 啟動(dòng)Navicat for MySQL并連接MySQL后，雙擊需要操作的數(shù)據(jù)

22、庫(kù)，此處為db_shop。步驟2 單擊“視圖”按鈕，然后選中需要修改的視圖，并單擊“設(shè)計(jì)視圖”按鈕，如圖12-5所示。圖12-5 修改視圖第23頁(yè)，共33頁(yè)。12.4.3 使用圖形化工具修改視圖步驟3 打開(kāi)視圖編輯界面，可在“視圖創(chuàng)建工具”選項(xiàng)卡右下方編輯SQL語(yǔ)句，如圖12-6所示。圖12-6 修改視圖定義語(yǔ)句第24頁(yè)，共33頁(yè)。12.5刪除視圖第25頁(yè)，共33頁(yè)。使用DROP VIEW語(yǔ)句可刪除一個(gè)或多個(gè)視圖。其基本語(yǔ)法格式如下：DROP VIEW IF EXISTS 視圖1,視圖n;【實(shí)例12-10】刪除視圖v_table。步驟1 執(zhí)行以下語(yǔ)句，選擇數(shù)據(jù)庫(kù)db_shop。步驟2 執(zhí)行以下

23、語(yǔ)句，刪除視圖v_table。USE db_shop;DROP VIEW IF EXISTS v_table;12.5.1 使用DROP VIEW語(yǔ)句刪除視圖步驟3 執(zhí)行SQL語(yǔ)句，查看數(shù)據(jù)庫(kù)中的數(shù)據(jù)表。SQL語(yǔ)句及其執(zhí)行結(jié)果如右：mysql SHOW TABLES;+-+| Tables_in_db_shop|+-+| goods | new_goods | orders | people | people1 | people2 | people3 | people4 | reply | test_goods | v_table2 | v_table3 |+-+12 rows in set

24、(0.00 sec)第26頁(yè)，共33頁(yè)。12.5.2 使用圖形化工具刪除視圖步驟1 啟動(dòng)Navicat for MySQL并連接MySQL后，雙擊需要操作的數(shù)據(jù)庫(kù)，此處為db_shop。步驟2 單擊“視圖”按鈕，然后選擇需要?jiǎng)h除的視圖，單擊“刪除試圖”按鈕，最后確認(rèn)刪除即可，如圖12-7所示。圖12-7 刪除視圖第27頁(yè)，共33頁(yè)。12.6操作視圖中數(shù)據(jù)第28頁(yè)，共33頁(yè)。由于視圖是一張?zhí)摂M表，所以對(duì)視圖中的數(shù)據(jù)進(jìn)行操作，實(shí)際上就是對(duì)基表進(jìn)行操作。當(dāng)視圖中的數(shù)據(jù)被修改，基表中的數(shù)據(jù)會(huì)同時(shí)被修改。同樣，當(dāng)基表中的數(shù)據(jù)被修改，視圖中的數(shù)據(jù)也會(huì)被修改。【實(shí)例12-11】修改視圖v_table2中的數(shù)

25、據(jù)。步驟1 修改視圖中數(shù)據(jù)之前，分別執(zhí)行SQL語(yǔ)句查看視圖和基表的信息。結(jié)果如下：mysql SELECT * FROM v_table2;+-+-+-+| goods_id | goods_name | goods_price |+-+-+-+| 1 | 西游記 | 50.40 | 2 | 牛奶糖 | 7.50 | 3 | 水果糖 | 2.50 | 4 | 休閑西服 | 800.00 | 5 | 果汁 | 2.50 | 6 | 論語(yǔ) | 109.00 | 7 | 西瓜 | 1.50 | 8 | 蘋果 | 3.00 | 9 | 牛仔褲 | 120.00 | 10 | 紅樓夢(mèng) | 50.50 |

26、+-+-+-+10 rows in set (0.04 sec)12.6.1 修改視圖中數(shù)據(jù)mysql SELECT id,name,price FROM goods;+-+-+-+| id | name | price |+-+-+-+| 1 | 西游記 | 50.40 | 2 | 牛奶糖 | 7.50 | 3 | 水果糖 | 2.50 | 4 | 休閑西服 | 800.00 | 5 | 果汁 | 2.50 | 6 | 論語(yǔ) | 109.00 | 7 | 西瓜 | 1.50 | 8 | 蘋果 | 3.00 | 9 | 牛仔褲 | 120.00 | 10| 紅樓夢(mèng) | 50.50 |+-+-+

27、-+10 rows in set (0.00 sec)第29頁(yè)，共33頁(yè)。步驟2 執(zhí)行SQL語(yǔ)句，修改視圖中數(shù)據(jù)。執(zhí)行結(jié)果如下：12.6.1 修改視圖中數(shù)據(jù)mysql UPDATE v_table2 SET goods_name=哈姆雷特 WHERE goods_id=1;Query OK, 1 row affected (0.10 sec)Rows matched: 1 Changed: 1 Warnings: 0步驟3 再次查看視圖和基表數(shù)據(jù)。結(jié)果如下：mysql SELECT * FROM v_table2;+-+-+-+| goods_id| goods_name| goods_price|+-+-+-+| 1 | 哈姆雷特 | 50.40| 2 | 牛奶糖 | 7.50 | 3 | 水果糖 | 2.50 | 4 | 休閑西服 | 800.00