项目六 视图

任务1 认识和查看视图

一、数据库的三级模式

1.模式

2.外模式

3.内模式

4.三级模式间的映射

二、视图简介

1.基本概念

2.视图的作用

三、查看数据库所有视图名

1.查看db_shop数据库的表和视图名。

SHOWFULLTABLESIN db_shop;

2.查看db_shop数据库的所有视图名。

SHOWFULLTABLESIN db_shop WHERE Table_type='VIEW';

四、查看视图结构信息

说明：DESCRIBE关键字可用其缩写DESC，用于显示视图中的字段名称、数据类型、是否为空等数据字段定义信息。

1.查看db_shop数据库下的v_sell_dept视图信息。

USE db_shop;
DESC v_sell_dept;

五、查看视图状态信息

说明：show table status[LIKE'视图名'] 不带[LIKE'视图名']时，显示的是当前数据库的表和视图的状态信息。

1.查看db_shop数据库的视图状态。

USE db_shop;
SHOWTABLESTATUSLIKE'v_sell_dept'\G

六、查看指定视图定义

语法：show create view 视图名

说明：查看视图的详细定义

1.查看db_shop数据库中v_dp_staffer视图的详细定义

USE db_shop;
SHOWCREATEVIEW v_sell_dept;

七、查看所有视图详细信息

说明：Information_schema是MYSQL数据库的元数据信息系统数据库，用于存储有关数据库、表、视图等数据对象的定义信息。VIEWS表存储了所有视图详细信息。

1.查看db_shop数据库所有视图详细信息

SELECT*FROM information_schema. VIEWSWHERE TABLE_SCHEMA='db_shop'\G

八、查看用户创建视图的相关权限

作用：查看各用户的各类权限

1.查看各用户有关视图创建的相关权限

SELECT*FROM mysql.user;

九、任务实施

(1)选择db_shop数据库

USE db_shop;

(2)查看db_shop数据库的所有视图对象

SHOWFULLTABLESIN db_shop WHERE Table_type='VIEW';

(3)查看v_sell_dept视图的结构信息

DESC v_sell_dept;

(4)查看v_shell_dept视图的状态信息

SHOWTABLESTATUSLIKE'v_sell_dept'\G

(5)查看v_sell_dept视图的定义语句

SHOWCREATEVIEW v_sell_dept;

(6)查看db_shop数据库的所有视图详细信息

SELECT*FROM information_schema. VIEWSWHERE TABLE_SCHEMA='db_shop'\G

任务2 创建视图

一、创建语句

执行HELP CREATE VIEW 可以查看帮助文档

创建视图的用户必须具有执行CREATE VIEW 的权限，若加了【OR REPLACE】则用户还必须具有执行DROP VIEW 的权限

二、创建单表数据视图

单表数据视图指视图显示的数据只来自一个表。定义视图的SQL语句可以检索单表，也可以进行跨表连接的检索。只检索单表的视图可以像表一样，用来增加、修改、删除、查询记录、

1.建立部门的视图v_dept，并使用它来查询部门记录。

第一步：创建视图

USE db_shop;
CREATEVIEW v_dept1(Dname,phone,memo)ASSELECT dept_name,dept_phone,dept_memo FROM department;

说明：创建的单表视图若计划用来更新基表，则基表中不允许空且没有默认值的字段必须出现在视图对应列

第二步：查询视图

视图建立好以后，可以像查询表一样查询视图，通过查询视图来查询基表的数据。

SELECT*FROM v_dept1;

2.建立女员工的视图vf_staffer,并使用它来查询女员工记录

第一步：创建视图

CREATE VIEW vf_staffer(username,deptID,Sname,sex) AS SELECT staff_name,dept_id,staff_name,sex FROM staffer WHERE sex='F'; 

第二步：查询视图

SELECT*FROM vf_staffer;

三、创建多表数据视图

多表数据视图即视图显示的数据是来自多个表的字段，视图的SQL定义语句使用了跨表连接查询。来自多个表字段的视图一般不能用来处理数据，只能用来检索数据。

1.建立部门员工视图，显示部门名称和员工对外查阅资料

第一步：创建视图

USE db_shop;
CREATEVIEW v_staffer ASSELECT dept_name,staff_name,sex,phone FROM staffer INNERJOIN department ON staffer.dept_id=department.id;

第二步：查询视图

SSELECT * FROM v_staffer;

四、创建带约束检查的视图

当使用WITH CHECK OPTION 子句创建视图时，MYSQL 会通过视图检查正在更改操作的每个行，例如插入、更新、删除操作，以使其符合视图的定义条件，不符合则不能通过视图更新记录。

1.创建销售部的视图，并查看能否超出视图条件添加记录

分析：当定义表单视图的SELECT 语句带有WHERE条件时，如果没有使用WITH CHECK OPTION进行约束检查，那么是可以通过视图越过视图的定义条件添加记录的。

第一步：创建视图

USE db_shop;
CREATEVIEW v_sell_dept1 ASSELECT*FROM department WHERE dept_name='销售部';

第二步：查看视图

SELECT*FROM v_sell_dept1;

第三步：通过视图添加记录

这是一个单表视图，在符合添加表记录的情况下也可以用来添加记录。先尝试为销售部视图添加一条记录（部门名称：公关部；电话：020-38128912）。

INSERT INTO v_sell_dept(dept_name,dept_phone) VALUES ('公关部','020-38128912');

第四步：查看视图和基础表（部门表）

SELECT * FROM v_sell_dept;
SELECT * FROM department;

说明：公关部记录通过销售部视图添加到视图基表的部门表中，但查看销售部视图是看不到的，相当于越过视图条件添加了记录，要解决这个问题，定义视图时要使用WITH CHECK OPTION子句

2.建立市场部视图，做到不能超越视图条件添加记录

分析：创建视图时，WITH CHECK OPTION 可以检查对视图的更新是否符合视图定义条件，若不符合条件，则添加记录失败。

第一步：创建视图

CREATEVIEW v_mk_dept ASSELECT*FROM department WHERE dept_name='市场部'WITHCHECKOPTION;

第二步：查看视图

SELECT*FROM v_mk_dept;

第三步：添加部门记录

部门名称：公关部2；电话：020-38230126

INSERTINTO v_mk_dept(dept_name,dept_phone)VALUES('公关部2','020-38230126')

说明：可以看到添加失败，就是添加的记录“公关部2”不能满足视图定义的条件，从而阻止了该记录的添加。这是WITH CHECK OPTION的作用

五、在视图上建立视图

可以在已经建立好的视图上建立视图。在一个视图上建立一个新的视图，这两个视图之间就建立了依赖与被依赖的父子关系，也就是继承关系，在应用了WITH CHECK OPTION 组件的情况下，具有级联约束关系。

1.在db_shop数据库中，建立一个男员工的视图，再基于这个视图创建薪水超过8000元的职员视图。

第一步：建立男员工的视图

CREATE VIEW v_male_staffer AS SELECT * FROM staffer WHERE sex='M'; 

第二步：在视图上建立视图

CREATE VIEW v_highSalary_male_staffer AS SELECT * FROM v_male_staffer WHERE salary>8000 WITH CHECK OPTION;

第三步：通过v_highSalary_male_staffer分别添加2个员工记录

分别增加一个薪水为8500元的男员工信息、一个薪水为8500元的女员工信息

INSERT INTO v_highSalary_male_staffer(staff_name,dept_id,sex,birthday,phone,salary,staff_memo) VALUES ('XST','1','M','2001-07-17','123456789','8500','NULL');

INSERT INTO v_highSalary_male_staffer(staff_name,dept_id,sex,birthday,phone,salary,staff_memo) VALUES ('XXX','1','F','2001-07-17','123456789','8500','NULL');

说明：符合当前高新条件同时符合父视图的男员工才能添加成功，只符合高新条件的女员工记录在CHECK检查时失败。

六任务实施

完成db_shopping数据库视图的创建

(1)选择db_shopping数据库

USE db_shopping;

(2)建立一个部门表的员工视图，显示部门名称、员工姓名、性别、职工电话，并查看建立好的视图。

CREATE VIEW v_dept_staffer AS SELECT dept_name,staff_name,sex,phone FROM staffer INNER JOIN department ON staffer.dept_id=department.id;
SELECT * FROM v_dept_staffer;

(3)建立一个详细订单表视图，显示订单编号、订购号、商品名称、供应商名称、单项订购数量、单项总价，并查看建立好的视图。

CREATEVIEW v_goods_sup_item ASSELECT id,order_id,goods_name,supplier_name,quantity,total_price FROM item INNERJOIN goods ON goods.id=item.goods_idINNERJOIN supplier ON supplier.id=goods.supplier_id;
SELECT*FROM v_goods_sup_item;

(4)建立一个订购视图，显示订购编号、订购总额、生成时间、支付状态、处理职员姓名

CREATEVIEW v_staf_cus_orders ASSELECT id,amount_money,create_time,status,staff_name FROMorderINNERJOIN staffer ON staffer.id=order.staff_id;
SELECT*FROM v_staf_cus_orders;

任务三 应用视图

视图可以像表一样用SELECT语句检索基本资料、统计、排序和汇总数据。实际应用中，为简化数据检索或提高数据库的安全性，通常将查询对象建立视图，再从视图中选择查询数据。

1.在已经建立的部门视图v_dept上查询部门记录信息

第一步：建立视图（前面已经建立过的可以不用建立）

USE db_shop;
CREATEORREPLACEVIEW v_dept(Dname,phone,memo)ASSELECT dept_name,dept_phone,dept_memo FROM department;

第二步：查询视图

USE db_shop;
SELECT*FROM v_dept;

2.通过部门视图v_dept查看部门数量

USE db_shop;
SELECT COUNT(*) AS 部门数 FROM v_dept;  

3.通过员工视图v_staffer,按员工电话排序查看员工记录信息

第一步：创建视图

USE db_shop;
CREATE OR REPLACE VIEW v_staffer AS SELECT dept_name,staff_name,sex,phone FROM staffer INNER JOIN department ON staffer.dept_id=department.id;

第二步：应用视图查看基本记录数据

USE db_shop;
SELECT * FROM v_dept ORDER BY phone;

通过视图也可以分类统计数据，总之，通过视图可以像基表一样实现检索，又能简化基表的内部逻辑复杂性和保护需要对用户隐藏的数据。

二、使用视图添加记录数据

视图添加数据记录必须符合以下条件：

1.基表中未被视图引用的字段必须有默认值、自增值或允许空。

2.添加的数据必须符合基本数据的各种约束。

3.如果视图来自多个基表的字段，则一般用于查询而不作为数据处理使用。

1.利用前面建立了部门信息的视图来进行部门信息的添加

分析：若前面没有建立部门视图，则执行如下部门视图定义语句。

CREATE VIEW v_dept(Dname,phone,memo) AS SELECT dept_name,dept_phone,dept_memo FROM department;

建立好以后，直接利用视图添加部门记录

第一步：查看是否已经创建视图

USE db_shop;
SHOW FULL TABLES IN db_shop WHERE Table_type='VIEW';

第二步：查看视图结构字段描述，并添加记录

DESC v_dept;

第三步：利用视图添加1条测试部的部门记录

INSERT INTO v_dept(Dname,phone) VALUES ('测试部','020-33461783');

说明：通过视图操作，使用的字段必须是视图中的字段名，而不是基表字段名

第四步：查看视图是否已经添加了测试部的记录，也可以查看基表是否已经添加

SELECT*FROM v_dept;
SELECT*FROM department;

说明：查看基表数据是否同步

三、使用视图修改记录数据

并不是所有视图都可以用来修改记录的，通过视图修改数据必须符合以下条件：

1.在一个UPDATE语句中修改的字段必须属于同一个基表，如果要对多个基表的数据进行修改，则需要使用多各UPDATE语句完成。

2.对于基表数据的修改，必须满足在字段上设置的约束，例如是否具有唯一性、是否可以未空值。

3.如果在视图定义中用WITH CHECK OPTION子句，则通过这个视图进行修改时提供的数据必须满足视图定义中的条件，否则UPDATE语句将被终止并返回错误信息。

4.视图中汇总函数或计算字段的值不能更改。

5.视图定义中包含有UNION、DISTINCT 、GROUP BY 等关键字时，不能用来修改记录。

6.视图定义语句中包含子查询时或来自不可更新的视图时，不能用来修改记录

1.通过部门员工信息视图v_dept将部门“测试部”的memo字段改为“功能测试”。

USE db_shop;
UPDATE v_dept SET memo='功能测试'WHERE Dname='测试部';

说明：可以查看视图和基础表是否修改了记录

查看是视图和基础表是否修改了记录

SELECT*FROM v_dept;
SELECT*FROM department;

四、使用视图删除记录数据

用视图删除数据记录必须符合下列条件：

通过一个视图删除基表中的数据时，必须保证该视图定义的FROM子句中只引用了一个表。

1.删除部门“测试部”的记录。

USE db_shop;
DELETE FROM v_dept WHERE Dname='测试部';

通过查看视图和部门表，可以检查记录是否已经被删除

五、任务实施

(1)选择db_shopping数据库

USE db_shopping;

(2)应用前面建立的订单详细表视图，进行查询统计

①定义订单详细表视图（前一任务已经建立的可以不用再建立），查看视图

CREATEVIEW v_goods_sup_item ASSELECT id,order_id,goods_name,supplier_name,quantity,total_price FROM item INNERJOIN goods ON goods.id=item.goods_idINNERJOIN supplier ON supplier.id=goods.supplier_id;
SELECT*FROM v_goods_sup_item;

②从视图v_goods_sup_item统计各份订单的总额，并按总额高低排序

SELECT order_id,SUM(total_price)AS 订单总额 FROM v_goods_sup_item GROUPBY order_id 
ORDERBYSUM(total_price);

③通过视图v_goods_sup_item 统计订单总额超过20的订单编号

SELECT order_id,SUM(total_price)AS 订单总额 FROM v_goods_sup_item GROUPBY  order_id HAVINGSUM(total_price)>=20;

(3)建立部门表的视图，并应用这个视图对部门记录进行添加、修改、删除

①建立视图v_dept

CREATEVIEW v_dept(Dname,phone,memo)ASSELECT dept_name,dept_phone,dept_memo FROM department;

②应用视图v_dept添加部门记录，并查询部门表效果

INSERTINTO v_dept (Dname,phone,memo)VALUES('测试部','87993696','负责测试');
SESLECT *FROM department;

③应用视图v_dept修改部门记录，并查询部门表效果

UPDATE v_shop SET memo='功能测试'WHERE Dname='测试部';
SELECT*FROM department;

④应用视图v_dept删除测试部的部门记录，并查询部门表效果

DELETEFROM v_dept WHERE Dname='测试部';
SELECT*FROM department;

任务四 修改和删除视图

视图建立以后，在实际应用中可能因基表的修订、视图的内容变化等原因，需要修改视图或删除无用的视图。本任务通过商品购物管理系统案例，学习使用SQL中的CREATE VIEW、ALTER VIEW和DROP VIEW等语句对视图进行修改或删除处理

一、修改视图

修改视图是指当视图基表的某些字段名称发生变化时，通过修改视图定义，以保持视图定义内容与基表的一致性。对视图的修改就是对基表的修改，因此在修改时，要满足基本表的数据定义，同时还要先查看该视图的依赖关系，是否会影响依赖此视图的其他对象的执行。

可以使用下面两种方法修改视图

1.使用CREATE OR REPLACE VIEW 修改视图

①修改员工视图v_staffer,使其只查看到员工部门、姓名和性别信息。

说明：若视图已经存在，则修改视图，否则创建视图。

USE db_shop;
CREATE OR REPLACE VIEW v_staffer AS SELECT staff_name,dept_name FROM staffer INNER JOIN department ON staffer.dept_id=department.id;
SELECT * FROM v_staffer;

2.使用ALTER VIEW修改视图

①修改员工视图v_staffer，使其能查看员工部门、姓名、性别、电话

USE db_shop;
ALTER VIEW v_staffer AS SELECT dept_name,staff_name,sex,phone FROM staffer INNER JOIN department ON staffer.dept_id=department.id;

说明：修改完成以后，查看以下视图

SELECT * FROM v_staffer;

二、删除视图

删除视图是指删除不再需要的视图。删除视图时要先查看该视图的依赖关系，是否会影响依赖此视图的其他对象的执行。删除视图只是删除视图的定义，不会对基本表造成影响。

DROP VIEW 语句来创造视图

1.删除已经建立的员工视图v_staffer

USE db_shop;
DROPVIEW v_staffer;

三、任务实施

按下列操作完成db_shopping数据库已建视图的修改和删除

(1)选择db_shopping数据库

USE db_shopping;

(2)应用前面建立的部门视图v_dept进行修改。

ALTERVIEW v_dept(Did,Dname,phone,memo)ASSELECT id,dept_name,dept_phone,dept_memo FROM departmrnt;

(3)查看修改后的视图结构和记录

DESC v_dept;
SELECT*FROM v_dept;

(4)应用前面建立的部门视图v_dept进行删除

DROPVIEW v_dept;

(5)查看删除视图v_dept后的数据库所有视图名

SHOWFULLTABLESIN db_shop WHERE Table_type='VIEW';

---

/***
 *             ,%%%%%%%%,
 *           ,%%/\%%%%/\%%
 *          ,%%%\c "" J/%%%
 * %.       %%%%/ o  o \%%%
 * `%%.     %%%%    _  |%%%
 *  `%%     `%%%%(__Y__)%%'
 *  //       ;%%%%`\-/%%%'
 * ((       /  `%%%%%%%'
 *  \\    .'          |
 *   \\  /       \  | |
 *    \\/         ) | |
 *     \         /_ | |__
 *     (___________))))))) 攻城湿
 */

​