视图

  • 需要MySQL 5：MySQL 5添加了对视图的支持。

  • 视图是虚拟的表。

    与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 检索订购了某个特定产品的用户
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';
  
-- 假如把整个查询包装成名为productcustomers的虚拟表，可以检索出相同的数据
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

/*
    productcustomers是一个视图，作为视图，它不包含表中应该有的任何列或数据；
    它包含的是一个SQL查询（与上面用以正确联结表的相同的查询）。
*/

为什么使用视图

  • 下面是视图的一些常见应用：

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

  • 视图仅仅是用来查看存储在别处的数据的一种设施。

    视图本身不包含数据，因此他们返回的数据是从其他表中检索出来的。

    在添加或更改这些表中的数据时，视图将返回改变过的数据。

  • 性能问题：因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。

    在部署使用了大量视图的应用前，应该进行测试。

视图的规则和限制

  1. 与表一样，视图必须唯一命名。

    不能给视图取与别的视图或表相同的名字。

  2. 对于可以创建的视图数目没有限制。

  3. 为了创建视图，必须具有足够的访问权限。

    这些限制通常由数据库管理人员授予。

  4. 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。

  5. ORDER BY可以用在视图中，但如果从该视图检索数据的SELECT语句中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。

  6. 视图不能索引，也不能有关联的触发器或默认值。

  7. 视图可以和表一起使用。

    例如，编写一条联结表和视图的SELECT语句。

使用视图

  • 视图的创建：

    1. 视图用CREATE VIEW语句来创建。

    2. 使用SHOW CREATE VIEW viewname;来查看创建视图的语句。

    3. 用DROP删除视图，其语法为DROP VIEW viewname;。

    4. 更新视图时，可以先用DROP在用CREATE，也可以直接用CREATE OR REPLACE VIEW。

      如果要更新的视图不存在，则第2条更新语句会创建一个视图；

      如果要更新的视图存在，则第2条更新语句会替换原有视图。

利用视图简化复杂的联结

  • 视图的最常见的应用之一是隐藏复杂的SQL，这通常都会设计联结。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建视图，返回已订购了任意产品的所有客户的列表
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;

-- 检索订购了产品TNT2的客户
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';
/*
    此语句通过WHERE子句从视图中检索特定数据。
    MySQL处理此查询时，它将指定的WHERE子句添加到视图查询中的已有WHERE子句中，以便正确过滤数据。
*/

  • 创建可重用的视图：创建不受特定数据限制的视图是一种好办法（如上面的例子）。

    扩展视图的范围不仅使得它能被重用，而且甚至更有用。

    这样做不需要创建和维护多个类似的视图。

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

  • 视图的另一常见用途是重新格式化检索出的数据。
1
2
3
4
5
6
7
8
9
10
-- 使用与以前的SELECT语句相同的查询创建视图
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
       AS vend_title
FROM vendors
ORDER BY vend_name;

-- 检索出已创建所有邮件标签的数据
SELECT *
FROM vendorlocations;

用视图过滤不想要的数据

1
2
3
4
5
6
7
8
9
-- 定义customeremaillist视图，它过滤没有电子邮件地址的客户
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

-- 使用视图
SELECT *
FROM customeremaillist;
  • WHERE子句与WHERE子句：如果从视图检索数据时使用了一条WHERE子句 ，则两组子句（一组在视图中，另一组是传递给视图的）将自动结合。

使用视图与计算字段

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 检索某个特定订单中的物品，计算每种物品的总价格
CREATE VIEW orderitemsexpanded AS
SELECT order_num,
       prod_id,
       quantity,
       item_price,
       quantity * item_price AS expanded_price
FROM orderitems;

-- 检索订单20005的详细内容
SELECT *
FROM orderitemsexpanded
WHERE order_num = 20005;

更新视图

  • 通常，视图是可更新的（即，可以对他们使用INSERT、UPDATE和DELETE）。

    更新一个视图将更新其基表（视图本身没有数据）。

    如果对视图增加或删除行，实际上是对其基表增加或删除行。

  • 并非所有视图都是可更新的。

    如果MySQL不能正确的确定被更新的基数据，则不允许更新（包括插入和删除）。

    这意味着，如果视图定义中有以下操作，则不能进行视图的更新：

    1. 分组（使用GROUP BY和HAVING）；
    2. 联结；
    3. 子查询；
    4. 并；
    5. 聚集函数（Min()、Count()、Sum()等）；
    6. DISTINCT；
    7. 导出（计算）列。

    这听上去好像是严重的限制，实际上不是，因为视图主要用于数据检索。

  • 可能的变动：上面列出的限制自MySQL 5以来是正确的，未来有可能会取消某些限制。

  • 将视图用于检索：应该将视图用于检索（SELECT）而不用于更新（INSERT、UPDATE和DELETE）。