MySQL必知必会——第二十二章使用视图

使用视图

本章将介绍视图究竟是什么,它怎样工作,何时使用它。我们还将了解,如何利用视图简化我们所学的某些SQL操作。

视图

视图是虚拟的表,它只包含使用时动态检索数据的查询。

例如第十五章中的SELECT语句:

mysql> SELECT cust_name, cust_contact
    -> FROM customers, orders, orderitems
    -> WHERE customers.cust_id = orders.cust_id
    -> AND orderitems.order_num = orders.order_num
    -> AND prod_id = 'TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.01 sec)

此查询用来检索订购了某个特定产品的客户。为了检索其他产品的相同数据,必须修改最后的WHERE子句。

如果我们把整个查询包装成一个名为productcustomers的虚拟表,则可以轻松检索出相同的数据:

SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

这就是视图的作用。productcustomers是一个视图,它不包含表中应该有的任何列或数据,它包含的是一个SQL查询

为什么使用视图

视图的常见应用:

  • 重用SQL语句。
  • 简化复杂的SQL操作。 且可以方便地重用它而不必知道它的基本查询细节。
  • 使用表的组成部分而不是整个表。
  • 保护数据。 可以给用户授权表的特定部分的访问权限,而不是整个表的访问权限。
  • 更改数据格式和表示。 视图可以返回与底层表的表示和格式不同的数据。

视图创建后,可以用与表基本相同的方式利用它们。例如,执行SELECT操作,过滤和排序数据。将视图联结到其他视图或表,甚至可以添加和更新数据(存在某些限制)。

性能问题 视图不包含数据,每次使用视图时,都必须处理查询执行时所需的检索。如果多个联结和过滤创建了复杂的视图或嵌套了视图,性能可能会下降得很厉害。

视图的规则和限制

视图的创建和使用有些常见的规则和限制:

  • 视图名具有唯一性,不能存在与其他视图或表的命名相同。
  • 视图数目没有限制,只要合法,可创建任意个视图。
  • 访问权限,为了创建视图,必须具有足够的访问权限。
  • 视图可以嵌套,可以利用其他视图的数据来构造视图。
  • 可以排序,ORDER BY可以用在视图中。如果使用视图的SELECT语句也含有ORDER BY,依SELECT语句的ORDER BY为准。
  • 视图不能索引,也不能有关联的触发器或默认值。
  • 视图可以和表一起使用。 例如,表和视图可以联结。

使用视图

视图的创建:

  • 视图用CREATE VIEW语句来创建。
  • 使用SHOW CREATE VIEW viewname;来查看创建视图的语句。
  • 用DROP VIEW viewname;来删除视图。
  • 更新视图,可以先用DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW。如果视图不存在,就创建新视图,如果视图存在,就覆盖旧视图。

用视图简化复杂的联结

视图常用于隐藏复杂的SQL语句,通常会涉及联结:

mysql> CREATE VIEW productcustomers AS
    -> SELECT cust_name, cust_contact, prod_id
    -> FROM customers, orders, orderitems
    -> WHERE customers.cust_id = orders.cust_id
    -> AND orderitems.order_num = orders.order_num;
Query OK, 0 rows affected (0.04 sec)

此语句创建了一个名为productcustomers的视图,它联结三个表,返回已订购产品的所有客户列表。

创建视图后,可以这样检索订购了产品TNT2的客户:

mysql> SELECT cust_name, cust_contact
    -> FROM productcustomers
    -> WHERE prod_id = 'TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.02 sec)

此语句通过WHERE子句从视图中检索特定数据。MySQL处理此查询时,它将指定的WHERE子句添加到视图查询中已有的WHERE子句中,以便正确过滤数据。

  • 创建可重用的视图 创建不受特定数据限制的视图是一种不错的方法。这样不需要创建和维护多个类似的视图。

用视图重新格式化检索出的数据

视图的常见用途还有重新格式化检索出的数据。下面的SELECT语句在单个组合计算列中返回供应商名和位置:

mysql> SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
    -> FROM vendors
    -> ORDER BY vend_name;
+-------------------------+
| vend_title              |
+-------------------------+
| ACME (USA)              |
| Anvils R Us (USA)       |
| Furball Inc. (USA)      |
| Jet Set (England)       |
| Jouets Et Ours (France) |
| LT Supplies (USA)       |
+-------------------------+
6 rows in set (0.00 sec)

如果需要经常这个格式的结果,可以把此语句转换为视图:

mysql> CREATE VIEW vendorlocations AS
    -> SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
    -> FROM vendors
    -> ORDER BY vend_name;
Query OK, 0 rows affected (0.03 sec)

使用该视图:

mysql> SELECT *
    -> FROM vendorlocations;
+-------------------------+
| vend_title              |
+-------------------------+
| ACME (USA)              |
| Anvils R Us (USA)       |
| Furball Inc. (USA)      |
| Jet Set (England)       |
| Jouets Et Ours (France) |
| LT Supplies (USA)       |
+-------------------------+
6 rows in set (0.01 sec)

用视图过滤不想要的数据

视图应用普通的WHERE子句也很有用。

定义customeremaillist视图,过滤没有电子邮件地址的客户:

mysql> CREATE VIEW customeremaillist AS
    -> SELECT cust_id, cust_name, cust_email
    -> FROM customers
    -> WHERE cust_email IS NOT NULL;
Query OK, 0 rows affected (0.01 sec)

此语句可用于发送电子邮件,显然,发送邮件需要排除没有邮箱地址的用户。

使用视图:

mysql> SELECT *
    -> FROM customeremaillist;
+---------+----------------+---------------------+
| cust_id | cust_name      | cust_email          |
+---------+----------------+---------------------+
|   10001 | Coyote Inc.    | ylee@coyote.com     |
|   10003 | Wascals        | rabbit@wascally.com |
|   10004 | Yosemite Place | sam@yosemite.com    |
+---------+----------------+---------------------+
3 rows in set (0.00 sec)
  • WHERE子句与WHERE子句 如果使用视图检索数据时使用了WHERE子句,则两组子句(视图中的子句与检索时的子句)将自动组合。

使用视图与计算字段

视图对于简化计算字段的使用非常有效。

例如,检索某个特定订单中的物品,计算每种物品的总价格:

mysql> SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
    -> FROM orderitems
    -> WHERE order_num = 20005;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
+---------+----------+------------+----------------+
4 rows in set (0.00 sec)

可将其转化为视图:

mysql> CREATE VIEW orderitemsexpanded AS
    -> SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price
    -> FROM orderitems;
Query OK, 0 rows affected (0.01 sec)

使用视图:

mysql> SELECT *
    -> FROM orderitemsexpanded
    -> WHERE order_num = 20005;
+-----------+---------+----------+------------+----------------+
| order_num | prod_id | quantity | item_price | expanded_price |
+-----------+---------+----------+------------+----------------+
|     20005 | ANV01   |       10 |       5.99 |          59.90 |
|     20005 | ANV02   |        3 |       9.99 |          29.97 |
|     20005 | TNT2    |        5 |      10.00 |          50.00 |
|     20005 | FB      |        1 |      10.00 |          10.00 |
+-----------+---------+----------+------------+----------------+
4 rows in set (0.01 sec)

更新视图

通常,视图是可更新的(可对它们使用INSERT、UPDATE和DELETE)。更新视图将更新其基表(视图只是对基表的检索结果)。

但并非所有视图都可更新。如果,MySQL不能正确地确定被更新的基数据,则不允许更新。这意味着以下操作,则不能更新视图:

  • 使用了分组(GROUP BY和HAVING)。
  • 使用了联结;
  • 使用了子查询。
  • 使用了并(UNION)。
  • 使用了聚集函数(Min()、Count()、Sum()等)。
  • 使用了DISTINCT;
  • 使用了计算(导出)列。

视图的更新具有很大的限制,但视图主要用于数据检索。我们应该把重点放于检索而非更新。

  • 将视图用于检索 应该将视图用于检索(SELECT语句)而不用于更新(INSERT、UPDATE和DELETE)。
  • 3
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

霖行

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值