《MySQL数据库应用与管理项目化教程》课件-项目6-视图

项目六视图教学目标能力目标◎能熟练按需求查看、建立和修改视图；◎能熟练应用视图进行数据查询和数据处理。知识目标◎熟悉视图作用，掌握视图查看、建立和修改删除的语法；◎掌握视图的查询和数据处理的应用。学习重点◎熟练视图的建立和应用。学习难点◎视图上建立视图和约束检查的级联。任务目标任务一认识视图任务二建立视图任务三查看视图任务四修改删除视图任务五应用视图任务一认识视图认识视图

主要内容:阅读视图文档；视图作用数据库系统的三级模式视图概念重点难点:视图的应用视图文档查阅：官网文档：/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]认识视图简化业务查询由于涉及的数据来自多个数据表，查询较复杂，我们在设计时通常把这些复杂的数据查询编写成视图。将查询编成视图，既隐藏了数据库的复杂性，简化业务查询处理；安全性隐藏了访问的基表，提高了数据表的安全性，逻辑对象的独立性而且还可以使用户程序与数据库保持相对独立，不因为数据表的修改而造成应用程序的修改。权限控制同时还可方便控制用户访问数据的权限，保护商业秘密。数据库系统的三级模式应用A应用C应用B应用E应用D外模式1外模式2外模式n模式内模式模式/外模式映象模式/内模式映像DBMS数据库数据库系统的三级模式数据库系统的三级模式结构数据库系统的三级模式结构是指数据库系统是由外模式、模式和内模式三级组成。外模式。即用户模式，是用户能够看见和使用的数据视图,不同权限用户的外模式也不同，如:SQL中的视图。如:SQL中的视图。模式。即逻辑模式，是所有用户的公共数据视图，定义数据项的名字、类型、安全性、完整性等。如：SQL中的表数据库系统的三级模式内模式。内模式也称存储模式，它是数据物理和存储结构的描述，是数据在数据库内部的表示方式。一个数据库只有一个内模式，如：SQL中的数据库文件。数据库系统的二级映像数据库系统在这三级模式之间提供了两层映像：外模式/模式映像和模式/内模式映像。正是这两层映像保证了数据库系统的数据能够具有较高的逻辑独立性和物理独立性。数据库系统的三级模式

外模式/模式映像

定义外模式与模式之间的对应关系，保证模式改变时，由数据库管理员对各个外模式/模式的映像作相应改变，可以使外模式保持不变，从而应用程序不必修改，保证了数据的逻辑独立性。模式/内模式映像

定义了数据库模式（即模式）与内模式之间的对应关系。保证了内模式改变了，模式保持不变，从而应用程序也不必改变，保证了数据的物理独立性。任务二建立视图建立视图建立视图。基本语法：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','攻关部')；mysql>SELECT*FROMdepartment；建立视图例3-2、无法超越视图添加记录mysql>ALTERVIEWv_deptASSELECT*FROMdepartmentWHEREdept_name='销售部'WITHCHECKOPTION；mysql>INSERTINTOv_dept(dept_id,dept_name)VALUES('008','公关部')；考考你1、建立详细订单表的商品名称、供应商名称的详细订单信息视图2、建立订单项目表的顾客名称、处理员工的订单项目信息。任务实施按下列操作完成db_shopping数据库视图的创建。1.选择db_shopping数据库，执行语句如下：mysql>USEdb_shopping;2.建立一个部门表的职工视图，显示部门名称、职工名称、性别、职工电话，并查看建立好的视图，执行语句如下：mysql>CREATEVIEWv_dept_staffer ASSELECTdept_name,staff_name,sex,phoneFROMstaffer INNERJOINdepartmentONstaffer.dept_id=department.id;mysql>SELECT*FROMv_dept_staffer;3.建立一个详细订单表视图，显示订单编号、订购号、商品名称、供应商名称、单项订购数量、单项总价,并查看建立好的视图，执行语句如下：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.建立一个订购视图，显示订购编号、订购总额、生成时间、支付状态、处理员工姓名，执行语句如下：mysql>CREATEVIEWv_staf_cus_orders ASSELECTid,amount_money，create_time，status，staff_nameFROMorders INNERJOINstafferONstaffer.id=orders.staff_id;mysql>SELECT*FROMv_staf_cus_orders;任务三查看视图查看视图查看视图基本信息语法：DESC视图名例如：mysql>USEdb_shop;

mysql>DESCv_staffer查看视图定义语句语法：SHOWCREATEVIEW视图名;例如：mysql>USEdb_shop;mysql>SHOWCREATEVIEWv_staffer;查看视图查看所有视图信息SELECT*FROMinformation_schema.VIEWS任务实施按下列操作完成db_shop数据库的视图信息查询。1.选择db_shop数据库，执行语句如下：mysql>USEdb_shop;2.查看db_shop数据库的所有视图对象，执行语句如下：mysql>SHOWFULLTABLESINdb_shopWHERETable_type='VIEW';3.查看v_dp_staffer视图的结构信息，执行语句如下：mysql>DESCv_dp_staffer;4.查看v_dp_staffer视图的状态信息，执行语句如下：mysql>SHOWTABLESTATUSLIKE'v_dp_staffer'\G5.查看v_dp_staffer视图的定义语句，执行语句如下：mysql>SHOWCREATEVIEWv_dp_staffer;6.查看db_shop数据库的所有视图详细信息，执行语句如下：mysql>SELECT*FROMinformation_schema.VIEWSWHERETABLE_SCHEMA='db_shop'\G任务四修改删除视图修改视图使用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;任务实施按下列操作完成db_shopping数据库已建视图进行修改和删除。1.选择db_shopping数据库，执行语句如下：mysql>USEdb_shopping;2.应用前面建立的部门视图v_dept进行修改，执行语句如下：mysql>ALTERVIEWv_dept(Did,Dname,phone,memo)ASSELECTid,dept_name,dept_phone,dept_memoFROMdepartment;3.并查看修改后的视图结构和记录，执行语句如下：mysql>DESCv_dept;mysql>SELECT*FROMv_dept;4.应用前面建立的部门视图v_dept删除，执行语句如下：mysql>DROPVIEWv_dept;;5.查看删除视图v_dept后的数据库所有视图名，执行语句如下：mysql>SHOWFULLTABLESINdb_shopWHERETable_type='VIEW';任务五应用视图应用视图检索数据视图可像表一样用SELECT检索基本资料、统计资料，为简化数据检索或提高数据库的安全性，通常将查询对象做成视图，再从视图中来选择查询数据。例1：视图销售部工员检索男职工

mysql>USEdb_shop;

mysql>SELECT*FROMv_tableWHEREdept_name='销售部'；例2：视图销售部检索职工人数

mysql>USEdb_shop;

mysql>SELECTCOUNT(*)AS人数FROMv_tableWHEREdept_name='销售部'；应用视图添加数据视图来自一个表时可以更新记录mysql>USEdb_shop;

mysql>CREATEVIEWv_deptASSELECT*FROMdepartmentmysql>SELECT*FROMv_deptmysql>DESCv_deptmysql>INSERTINTOv_dept(dept_id,dept_name)VALUES('008','信息部')应用视图修改数据例1：

mysql>USEdb_shop;

mysql>UPDATEv_staffer

SETsname='李张南'

WHEREsid='10501'mysql>SELECT*FROMv_staffer;mysql>SELECT*FROMstaffer;#差基表内容是否变化应用视图删除数据例1：

mysql>USEdb_shop;

mysql>CREATEORREPLACEVIEWv_deptASSELECT*FROMdepartmentmysql>SELECT*FROMv_deptmysql>DELETEFROMv_deptWHERE

dept_id='006'考考你1、建立顾客消费视图，显示出顾客姓名及其消费商品名称、数量、金额；4、建立部门员工视图，显示出部门名称、员工名称；按下列操作完成db_shopping数据库视图的创建和应用。（1）选择db_shopping数据库，执行语句如下：mysql>USEdb_shopping;（2）应用前面建立的订单详细表视图，进行查询统计。订单详细表视图定义如下（前一任务已经建立的可以不用再建），查看视图，执行语句如下：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统计各份订单的总额,并按总额高低排序，执行语句如下：mysql>SELECTord