学习目标：

1.了解什么是视图，为什么使用视图？

2.掌握创建视图、查看视图、修改视图、删除视图的操作方法。

3.会用SQL语句创建和管理视图。

4.掌握使用视图查询数据。

5.会用视图更新和维护数据。

一、基本概念（3W）

任务情景：经常要根据用户姓名查询订单详情，如何写查询？

1.什么是视图，解决哪些问题？

视图是从数据库中一个或多个表（或视图）中导出来的表，其关联的数据由SQL语句定义。 •视图与表一样由行和列组成，但它不存储实际的数据内容，当使用视图查询数据时，数据库系统会从视图引用的表中提取相应的数据。

2.为什么要使用视图？

视图的优点：
  1. 简单性 封装复杂的查询为视图
  2. 安全性 聚焦可访问的数据，提供安全性
  3. 逻辑数据独立性 针对视图处理数据

二、创建与管理online多表数据库的视图

画板

E-R模型：

6.1使用视图（VIEW）优化查询操作 - 图3

（一）创建视图

1.创建名为view_users_orders的视图，用于查询会员的订单及订单详情。列出用户id，用户名，订单编号，下单日期，订单金额，商品id，商品名称，购买数量和单价。
  • 使用Navicat图形工具创建视图
点击视图工具—》新建视图—》视图创建工具—》先拉表（有顺序）—》勾选列—》保存视图

•使用SQL语句创建视图

语法： CREATE [OR REPLACE] VIEW [数据库名.]视图名[(列名列表)] AS select语句 [WITH [CASCADED|LOCAL] CHECK OPTION] 
  1. -- （一）创建视图
  2. -- 1.创建名为view_users_orders的视图，用于查询会员的订单及订单详情。
  3. -- 列出用户id，用户名，订单编号，下单日期，订单金额，商品id，商品名称，
  4. -- 购买数量和单价。
  5. -- Navicat工具操作
  6. -- SQL语句创建
  7. -- 1.2  创建视图封装查询
  8. CREATE VIEW view_users_orders
  9. AS
  10.     -- 1.1 先写查询
  11.     SELECT u.uid,u.uname,o.oid,o.ordertime,o.oamount,
  12.                 g.gid,g.gname,oi.inum,g.gprice
  13.     FROM users u    -- 连接表
  14.     NATURAL JOIN orders o
  15.     NATURAL JOIN ordersitem oi
  16.     NATURAL JOIN goods g;

6.1使用视图（VIEW）优化查询操作 - 图4

说明： . OR REPLACE：当指定OR REPLACE子句时，若视图存在则修改定义，否则创建新视图。 · 列名列表：视图自定义的列名，该列表中名称必须与视图体中SELECT语句查询的结果列一一对应，若使用与源表或视图中相同的列名，则可以省略列名列表。 · select语句：视图定义的SELECT语句。 · WITH [CASCADED|LOCAL]CHECK OPTION：可选参数，表示更新视图时要保证在该视图的权限范围之内。其中CASCADED是默认值，表示更新视图时要满足所有相关视图和表的条件，LOCAL表示更新视图时满足该视图本身定义的条件。 2.创建名为view_cart的视图，用来显示购物车信息，列出用户id、用户名、商品id、商品名称、购买数量以及商品价格。 
  1. -- 2.创建名为view_cart的视图，用来显示购物车信息，
  2. -- 列出用户id、用户名、商品id、商品名称、购买数量以及商品价格。
  3. -- 步骤：
  4. -- 2.2 创建视图，封装查询
  5. CREATE OR REPLACE 
  6. VIEW view_cart(用户id,用户名,商品id,商品名称,购买数量,商品价格)
  7. AS
  8.     -- 2.1 查询用户id、用户名、商品id、商品名称、购买数量以及商品价格
  9.     -- 需要连接users、cart、goods
  10.         SELECT u.uid,u.uname,g.gid,g.gname,c.cnum,g.gprice
  11.         FROM users u
  12.         NATURAL JOIN cart c
  13.         NATURAL JOIN goods g;

6.1使用视图（VIEW）优化查询操作 - 图5

（二）查看视图

•查看视图的方法与查看表的方法基本相似。

1.查看数据库中所有的视图 
  1. SELECT TABLE_NAME
  2. FROM information_schema.VIEWS
  3. WHERE TABLE_SCHEMA = 'onlinedb';
2.使用SHOW TABLE STATUS语句查看视图 
  1. SHOW TABLE STATUS LIKE 'view%';
3.使用DESCRIBE语句查看视图 
  1. DESC view_users_orders1;

（三）通过视图查询数据

1.查询用户段湘林购物车中的商品id, 商品名称，价格和购买数量。 
  1. -- 1.查询用户段湘林购物车中的商品id, 商品名称，价格和购买数量。
  2. SELECT vc.`商品id` ,vc.`商品名称`,vc.`商品价格` ,vc.`购买数量`
  3. FROM view_cart vc
  4. WHERE vc.`用户名` = '段湘林';

6.1使用视图（VIEW）优化查询操作 - 图6

2.查询用户蔡静在2021年全年的总销费金额，列名为total_2021。 
  1. -- 2.查询用户蔡静在2021年全年的总销费金额，列名为total_2021。
  2. SELECT SUM(vuo.inum*vuo.gprice) total_2021
  3. FROM view_users_orders1 vuo
  4. WHERE vuo.uname = '蔡静' AND YEAR(ordertime) = 2021;

6.1使用视图（VIEW）优化查询操作 - 图7

（四）修改视图

6.1使用视图（VIEW）优化查询操作 - 图8

.**CREATE OR REPLACE VIEW语句修改视图。 **

•MySQL中，CREATE OR REPLACE VIEW语句的使用非常灵活，当要操作的视图不存在时，可以新建视图；当视图已存在时，可以实现修改视图。

1.修改名为view_cart的视图，在原有查询的基础上增加用户的邮箱 2.创建名为view_users的视图，列出用户id、登录名、用户名、密码和性别 
  1. -- 2.后建视图
  2. CREATE or REPLACE 
  3. VIEW view_users
  4. AS  -- 1.先查询
  5.     SELECT u.uid,u.ulogin,u.uname,u.upwd,u.ugender
  6.     FROM users u;

6.1使用视图（VIEW）优化查询操作 - 图9

•ALTER语句修改视图

类似于创建语句 
  1. ALTER 
  2. VIEW view_cart 
  3. AS 
  4. SELECT
  5.   u.uid AS `用户id`,
  6.   u.uname AS `用户名`,
  7.   g.gid AS `商品id`,
  8.   g.gname AS `商品名称`,
  9.   c.cnum AS `购买数量`,
  10.   g.gprice AS `商品价格`,
  11.   u.uemail
  12. from ((`users` `u` join `cart` `c` on((`u`.`uid` = `c`.`uid`))) join `goods` `g` on((`c`.`gid` = `g`.`gid`))) ;

（五）删除视图

•当不再需要视图时，可使用图形工具和SQL语句都可以删除视图。

•图形工具删除视图只需要在对象浏览器窗口右击待删除视图名，在弹出的快捷菜单中选择“删除视图”选项即可。SQL语句使用DROP VIEW语句删除视图。

•删除视图时，只会删除视图的定义，并不会删除视图关联的数据。

语法：DROP VIEW [IF EXISTS] 视图名 1.删除视图view_users_orders 
  1. drop view if exists view_users_orders;

（六）使用视图更新和维护数据

学习提示：虽然可以通过更新视图操作相关表的数据，但是限制较多。实际情况下，最好将视图仅作为查询数据的虚表，而不要通过视图更新数据。 1.通过视图修改数据 UPDATE 视图名 SET 列名1=值1, 列名2=值2,…, 列名n=值n WHERE 条件表达式

实例：通过视图view_cart修改会员李小莉的邮箱为’lixiaoli@qq.com’

  1. -- 通过视图view_cart修改会员李小莉的邮箱为'lixiaoli@qq.com'
  2. UPDATE view_cart 
  3. SET 邮箱 = 'lixiaoli@qq.com'
  4. WHERE 用户名 = '李小莉';
当通过视图修改连接的多个基表数据时，报错 sql UPDATE view_cart SET 购买数量 = 10,商品价格 = 20 -- 更新ordersitem表和goods表 WHERE 用户名 = '李小莉' and 商品ID = 7; [Err] 1393 - Can not modify more than one base table through a join view 'onlinedb.view_cart' 若视图的定义包含下情况中的任何一种，则该视图就不可更新。

•包含聚合函数。

•包含DISTINCT、UNION、ORDER BY、GROUP BY和HAVING等关键字或子句。 •包含子查询。 •由不可更新的视图导出的视图。 •视图对应的数据表上存在没有默认值且不为空的列，而该列没有包含在视图里。 2.通过视图向数据表插入数据 INSERT [INTO] 视图名[(列名列表)] VALUES(值列表1)[,( 值列表2),…,( 值列表n)] 实例：通过视图view_users，向数据表users中插入一条记录。 
  1. INSERT view_users(uid,ulogin,uname,upwd,ugender)
  2. VALUES(null,'1111111','1111111','******','男');
  1. INSERT view_users(ulogin,uname)
  2. VALUES('22222','22222');  -- upwd没有默认值，必须给值

3.通过视图删除数据表中的数据

注意：不能通过一条delete语句删除多张基本表的数据。 DELETE FROM 视图名 [WHERE 条件表达式] 实例：通过视图view_users，删除数据表users用户名为“周鹏”的记录。

三、课堂小结：

1**、进一步熟悉创建视图的基本步骤，先写查询，后加上create view的语法，select语句不能含有group by、order by子句。**

2、使用视图的好处，简化查询，提高效率和数据安全。

3、使用视图操作数据的限制条件，学会分析问题解决问题。