《MySQL數(shù)據(jù)庫應(yīng)用與管理項目化教程》課件-項目6-視圖

項目六視圖教學(xué)目標(biāo)能力目標(biāo)◎能熟練按需求查看、建立和修改視圖；◎能熟練應(yīng)用視圖進(jìn)行數(shù)據(jù)查詢和數(shù)據(jù)處理。知識目標(biāo)◎熟悉視圖作用，掌握視圖查看、建立和修改刪除的語法；◎掌握視圖的查詢和數(shù)據(jù)處理的應(yīng)用。學(xué)習(xí)重點◎熟練視圖的建立和應(yīng)用。學(xué)習(xí)難點◎視圖上建立視圖和約束檢查的級聯(lián)。任務(wù)目標(biāo)任務(wù)一認(rèn)識視圖任務(wù)二建立視圖任務(wù)三查看視圖任務(wù)四修改刪除視圖任務(wù)五應(yīng)用視圖任務(wù)一認(rèn)識視圖認(rèn)識視圖

主要內(nèi)容:閱讀視圖文檔；視圖作用數(shù)據(jù)庫系統(tǒng)的三級模式視圖概念重點難點:視圖的應(yīng)用視圖文檔查閱：官網(wǎng)文檔：/doc/refman/8.0/en/create-view.html語法：CREATE[ORREPLACE][ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}][DEFINER=user][SQLSECURITY{DEFINER|INVOKER}]

VIEWview_name[(column_list)]

ASselect_statement[WITH[CASCADED|LOCAL]CHECKOPTION]認(rèn)識視圖簡化業(yè)務(wù)查詢由于涉及的數(shù)據(jù)來自多個數(shù)據(jù)表，查詢較復(fù)雜，我們在設(shè)計時通常把這些復(fù)雜的數(shù)據(jù)查詢編寫成視圖。將查詢編成視圖，既隱藏了數(shù)據(jù)庫的復(fù)雜性，簡化業(yè)務(wù)查詢處理；安全性隱藏了訪問的基表，提高了數(shù)據(jù)表的安全性，邏輯對象的獨立性而且還可以使用戶程序與數(shù)據(jù)庫保持相對獨立，不因為數(shù)據(jù)表的修改而造成應(yīng)用程序的修改。權(quán)限控制同時還可方便控制用戶訪問數(shù)據(jù)的權(quán)限，保護(hù)商業(yè)秘密。數(shù)據(jù)庫系統(tǒng)的三級模式應(yīng)用A應(yīng)用C應(yīng)用B應(yīng)用E應(yīng)用D外模式1外模式2外模式n模式內(nèi)模式模式/外模式映象模式/內(nèi)模式映像DBMS數(shù)據(jù)庫數(shù)據(jù)庫系統(tǒng)的三級模式數(shù)據(jù)庫系統(tǒng)的三級模式結(jié)構(gòu)數(shù)據(jù)庫系統(tǒng)的三級模式結(jié)構(gòu)是指數(shù)據(jù)庫系統(tǒng)是由外模式、模式和內(nèi)模式三級組成。外模式。即用戶模式，是用戶能夠看見和使用的數(shù)據(jù)視圖,不同權(quán)限用戶的外模式也不同，如:SQL中的視圖。如:SQL中的視圖。模式。即邏輯模式，是所有用戶的公共數(shù)據(jù)視圖，定義數(shù)據(jù)項的名字、類型、安全性、完整性等。如：SQL中的表數(shù)據(jù)庫系統(tǒng)的三級模式內(nèi)模式。內(nèi)模式也稱存儲模式，它是數(shù)據(jù)物理和存儲結(jié)構(gòu)的描述，是數(shù)據(jù)在數(shù)據(jù)庫內(nèi)部的表示方式。一個數(shù)據(jù)庫只有一個內(nèi)模式，如：SQL中的數(shù)據(jù)庫文件。數(shù)據(jù)庫系統(tǒng)的二級映像數(shù)據(jù)庫系統(tǒng)在這三級模式之間提供了兩層映像：外模式/模式映像和模式/內(nèi)模式映像。正是這兩層映像保證了數(shù)據(jù)庫系統(tǒng)的數(shù)據(jù)能夠具有較高的邏輯獨立性和物理獨立性。數(shù)據(jù)庫系統(tǒng)的三級模式

外模式/模式映像

定義外模式與模式之間的對應(yīng)關(guān)系，保證模式改變時，由數(shù)據(jù)庫管理員對各個外模式/模式的映像作相應(yīng)改變，可以使外模式保持不變，從而應(yīng)用程序不必修改，保證了數(shù)據(jù)的邏輯獨立性。模式/內(nèi)模式映像

定義了數(shù)據(jù)庫模式（即模式）與內(nèi)模式之間的對應(yīng)關(guān)系。保證了內(nèi)模式改變了，模式保持不變，從而應(yīng)用程序也不必改變，保證了數(shù)據(jù)的物理獨立性。任務(wù)二建立視圖建立視圖建立視圖?；菊Z法：CREATE[ORREPLACE]VIEWview_name[(col_name_list)]ASselect_statement例1：mysql>USEdb_shop;

mysql>CREATEVIEWv_staffer

AS

SELECTstaff_id,staff_name,sex,dept_name

FROMstaffer

INNERJOINdepartmentON

staffer.dept_id=department.dept_id;建立視圖例2：使用字段別名為視圖的字段名稱mysql>USEdb_shop;

mysql>ALTERVIEWv_staffer2ASSELECTstaff_idASsid,staff_nameASsname,dept_nameASdnameFROMstafferINNERJOINdepartmentONstaffer.dept_id=department.dept_id;

mysql>SELECT*FROMv_staffer2;

mysql>DESCv_staffer2;建立視圖例3-1、可以超越視圖添加記錄mysql>USEdb_shop;mysql>CREATEVIEWv_deptASSELECT*FROMdepartmentWHEREdept_name='銷售部'；mysql>INSERTINTOv_dept(dept_id,dept_name)

VALUES('007','攻關(guān)部')；mysql>SELECT*FROMdepartment；建立視圖例3-2、無法超越視圖添加記錄mysql>ALTERVIEWv_deptASSELECT*FROMdepartmentWHEREdept_name='銷售部'WITHCHECKOPTION；mysql>INSERTINTOv_dept(dept_id,dept_name)VALUES('008','公關(guān)部')；考考你1、建立詳細(xì)訂單表的商品名稱、供應(yīng)商名稱的詳細(xì)訂單信息視圖2、建立訂單項目表的顧客名稱、處理員工的訂單項目信息。任務(wù)實施按下列操作完成db_shopping數(shù)據(jù)庫視圖的創(chuàng)建。1.選擇db_shopping數(shù)據(jù)庫，執(zhí)行語句如下：mysql>USEdb_shopping;2.建立一個部門表的職工視圖，顯示部門名稱、職工名稱、性別、職工電話，并查看建立好的視圖，執(zhí)行語句如下：mysql>CREATEVIEWv_dept_staffer ASSELECTdept_name,staff_name,sex,phoneFROMstaffer INNERJOINdepartmentONstaffer.dept_id=department.id;mysql>SELECT*FROMv_dept_staffer;3.建立一個詳細(xì)訂單表視圖，顯示訂單編號、訂購號、商品名稱、供應(yīng)商名稱、單項訂購數(shù)量、單項總價,并查看建立好的視圖，執(zhí)行語句如下：mysql>CREATEVIEWv_goods_sup_item ASSELECTid,order_id,goods_name,supplier_name,quantity,total_priceFROMitem INNERJOINgoodsONgoods.id=item.goods_idINNERJOINsupplierONsupplier.id=goods.supplier_id;mysql>SELECT*FROMv_goods_sup_item;4.建立一個訂購視圖，顯示訂購編號、訂購總額、生成時間、支付狀態(tài)、處理員工姓名，執(zhí)行語句如下：mysql>CREATEVIEWv_staf_cus_orders ASSELECTid,amount_money，create_time，status，staff_nameFROMorders INNERJOINstafferONstaffer.id=orders.staff_id;mysql>SELECT*FROMv_staf_cus_orders;任務(wù)三查看視圖查看視圖查看視圖基本信息語法：DESC視圖名例如：mysql>USEdb_shop;

mysql>DESCv_staffer查看視圖定義語句語法：SHOWCREATEVIEW視圖名;例如：mysql>USEdb_shop;mysql>SHOWCREATEVIEWv_staffer;查看視圖查看所有視圖信息SELECT*FROMinformation_schema.VIEWS任務(wù)實施按下列操作完成db_shop數(shù)據(jù)庫的視圖信息查詢。1.選擇db_shop數(shù)據(jù)庫，執(zhí)行語句如下：mysql>USEdb_shop;2.查看db_shop數(shù)據(jù)庫的所有視圖對象，執(zhí)行語句如下：mysql>SHOWFULLTABLESINdb_shopWHERETable_type='VIEW';3.查看v_dp_staffer視圖的結(jié)構(gòu)信息，執(zhí)行語句如下：mysql>DESCv_dp_staffer;4.查看v_dp_staffer視圖的狀態(tài)信息，執(zhí)行語句如下：mysql>SHOWTABLESTATUSLIKE'v_dp_staffer'\G5.查看v_dp_staffer視圖的定義語句，執(zhí)行語句如下：mysql>SHOWCREATEVIEWv_dp_staffer;6.查看db_shop數(shù)據(jù)庫的所有視圖詳細(xì)信息，執(zhí)行語句如下：mysql>SELECT*FROMinformation_schema.VIEWSWHERETABLE_SCHEMA='db_shop'\G任務(wù)四修改刪除視圖修改視圖使用CREATEORREPLACEVIEW修改語法：CREATEORREPLACEVIEWview_name[(col_name_list)] ASselect_statement例：

mysql>USEdb_shop;mysql>CREATEORREPLACEVIEWv_staffer

AS

SELECTstaff_name,dept_name

FROMstaffer

INNERJOINdepartmentON

staffer.dept_id=department.dept_id;

mysql>SELECT*FROMv_staffer?修改視圖使用ALTERVIEW修改語法：ALTERVIEWview_name[(col_name_list)] ASselect_statement例：

mysql>USEdb_shop;mysql>ALTERVIEWv_staffer AS SELECTstaff_id,staff_name,dept_name FROMstaffer INNERJOINdepartmentONstaffer.dept_id=department.dept_id;mysql>SELECT*FROMv_staffer刪除視圖使用DROPVIEW刪除語法：DROPVIEW[IFEXISTS]view1[,view,...]例1：

mysql>USEdb_shop;

mysql>

DROPVIEWv_staffer;任務(wù)實施按下列操作完成db_shopping數(shù)據(jù)庫已建視圖進(jìn)行修改和刪除。1.選擇db_shopping數(shù)據(jù)庫，執(zhí)行語句如下：mysql>USEdb_shopping;2.應(yīng)用前面建立的部門視圖v_dept進(jìn)行修改，執(zhí)行語句如下：mysql>ALTERVIEWv_dept(Did,Dname,phone,memo)ASSELECTid,dept_name,dept_phone,dept_memoFROMdepartment;3.并查看修改后的視圖結(jié)構(gòu)和記錄，執(zhí)行語句如下：mysql>DESCv_dept;mysql>SELECT*FROMv_dept;4.應(yīng)用前面建立的部門視圖v_dept刪除，執(zhí)行語句如下：mysql>DROPVIEWv_dept;;5.查看刪除視圖v_dept后的數(shù)據(jù)庫所有視圖名，執(zhí)行語句如下：mysql>SHOWFULLTABLESINdb_shopWHERETable_type='VIEW';任務(wù)五應(yīng)用視圖應(yīng)用視圖檢索數(shù)據(jù)視圖可像表一樣用SELECT檢索基本資料、統(tǒng)計資料，為簡化數(shù)據(jù)檢索或提高數(shù)據(jù)庫的安全性，通常將查詢對象做成視圖，再從視圖中來選擇查詢數(shù)據(jù)。例1：視圖銷售部工員檢索男職工

mysql>USEdb_shop;

mysql>SELECT*FROMv_tableWHEREdept_name='銷售部'；例2：視圖銷售部檢索職工人數(shù)

mysql>USEdb_shop;

mysql>SELECTCOUNT(*)AS人數(shù)FROMv_tableWHEREdept_name='銷售部'；應(yīng)用視圖添加數(shù)據(jù)視圖來自一個表時可以更新記錄mysql>USEdb_shop;

mysql>CREATEVIEWv_deptASSELECT*FROMdepartmentmysql>SELECT*FROMv_deptmysql>DESCv_deptmysql>INSERTINTOv_dept(dept_id,dept_name)VALUES('008','信息部')應(yīng)用視圖修改數(shù)據(jù)例1：

mysql>USEdb_shop;

mysql>UPDATEv_staffer

SETsname='李張南'

WHEREsid='10501'mysql>SELECT*FROMv_staffer;mysql>SELECT*FROMstaffer;#差基表內(nèi)容是否變化應(yīng)用視圖刪除數(shù)據(jù)例1：

mysql>USEdb_shop;

mysql>CREATEORREPLACEVIEWv_deptASSELECT*FROMdepartmentmysql>SELECT*FROMv_deptmysql>DELETEFROMv_deptWHERE

dept_id='006'考考你1、建立顧客消費視圖，顯示出顧客姓名及其消費商品名稱、數(shù)量、金額；4、建立部門員工視圖，顯示出部門名稱、員工名稱；按下列操作完成db_shopping數(shù)據(jù)庫視圖的創(chuàng)建和應(yīng)用。（1）選擇db_shopping數(shù)據(jù)庫，執(zhí)行語句如下：mysql>USEdb_shopping;（2）應(yīng)用前面建立的訂單詳細(xì)表視圖，進(jìn)行查詢統(tǒng)計。訂單詳細(xì)表視圖定義如下（前一任務(wù)已經(jīng)建立的可以不用再建），查看視圖，執(zhí)行語句如下：mysql>CREATEVIEWv_goods_sup_itemASSELECTid,order_id,goods_name,supplier_name,quantity,total_priceFROMitem INNERJOINgoodsONgoods.id=item.goods_idINNERJOINsupplierONsupplier.id=goods.supplier_id;mysql>SELECT*FROMv_goods_sup_item;從視圖v_goods_sup_item統(tǒng)計各份訂單的總額,并按總額高低排序，執(zhí)行語句如下：mysql>SELECTord