mysql视图之创建视图（CREATE VIEW）和使用限制实例详解

本文实例讲述了mysql视图之创建视图（create view）和使用限制。分享给大家供大家参考，具体如下：

mysql5.x 版本之后支持数据库视图，在mysql中，视图的几乎特征符合sql：2003标准。 mysql以两种方式处理对视图的查询：

• 第一种方式，mysql会根据视图定义语句创建一个临时表，并在此临时表上执行传入查询。
• 第二种方式，mysql将传入查询与查询定义为一个查询并执行组合查询。

mysql支持版本系统的视图，当每次视图被更改或替换时，视图的副本将在驻留在特定数据库文件夹的arc(archive)文件夹中备份。备份文件的名称为view_name.frm-00001。如果再次更改视图，mysql将创建一个名为view_name.frm-00002的新备份文件。mysql允许基于其他视图创建视图，就是在视图定义的select语句中，可以引用另一个视图。

好啦，多的咱就不赘述了，接下来咱们尝试使用create view语句创建视图，先来看下语法结构：

create 
  [algorithm = {merge | temptable | undefined}]
view [database_name].[view_name] 
as
[select statement]

然后我们来详细看下上面的sql中的各种词是什么意思。首先，第一个中括号里代表的就是创建视图是的算法属性，它允许我们控制mysql在创建视图时使用的机制，并且mysql提供了三种算法：merge，temptable和undefined。我们来分别看下：

• 使用merge算法，mysql首先将输入查询与定义视图的select语句组合成单个查询。 然后mysql执行组合查询返回结果集。 如果select语句包含集合函数(如min，max，sum，count，avg等)或distinct，group by，havaing，limit，union，union all，子查询，则不允许使用merge算法。 如果select语句无引用表，则也不允许使用merge算法。 如果不允许merge算法，mysql将算法更改为undefined。我们要注意，将视图定义中的输入查询和查询组合成一个查询称为视图分辨率。
• 使用temptable算法，mysql首先根据定义视图的select语句创建一个临时表，然后针对该临时表执行输入查询。因为mysql必须创建临时表来存储结果集并将数据从基表移动到临时表，所以temptable算法的效率比merge算法效率低。 另外，使用temptable算法的视图是不可更新的。
• 当我们创建视图而不指定显式算法时，undefined是默认算法。 undefined算法使mysql可以选择使用merge或temptable算法。mysql优先使用merge算法进行temptable算法，因为merge算法效率更高。

然后就是view后面的词组了，它就是名称的意思，在数据库中，视图和表共享相同的命名空间，因此视图和表不能具有相同的名称。 另外，视图的名称必须遵循表的命名规则。

最后就是select语句了。在select语句中，可以从数据库中存在的任何表或视图查询数据，同时select语句必须遵循以下几个规则：

• select语句可以在where 语句中包含子查询，但from子句中的不能包含子查询。
• select语句不能引用任何变量，包括局部变量，用户变量和会话变量。
• select语句不能引用准备语句的参数。

在这里我们得稍稍注意下，select语句不需要引用任何表。完事呢，我们来尝试基于orderdetails表来创建一个表示每个订单的总销售额的视图：

create view saleperorder as
  select 
    ordernumber, sum(quantityordered * priceeach) total
  from
    orderdetails
  group by ordernumber
  order by total desc;

我们如果使用show tables命令来查看示例数据库(yiibaidb)中的所有表，还会看到salesperorder视图也显示在表的列表中：

mysql> show tables;
+--------------------+
| tables_in_yiibaidb |
+--------------------+
| article_tags    |
| contacts      |
| customers     |
| departments    |
| employees     |
| offices      |
| offices_bk     |
| offices_usa    |
| orderdetails    |
| orders       |
| payments      |
| productlines    |
| products      |
| saleperorder    |
+--------------------+
14 rows in set

这是因为视图和表共享相同的命名空间。要知道哪个对象是视图或表，就得使用show full tables命令，如下所示：

mysql> show full tables;
+--------------------+------------+
| tables_in_yiibaidb | table_type |
+--------------------+------------+
| article_tags    | base table |
| contacts      | base table |
| customers     | base table |
| departments    | base table |
| employees     | base table |
| offices      | base table |
| offices_bk     | base table |
| offices_usa    | base table |
| orderdetails    | base table |
| orders       | base table |
| payments      | base table |
| productlines    | base table |
| products      | base table |
| saleperorder    | view    |
+--------------------+------------+
14 rows in set

结果集中的table_type列指定哪个对象是视图，哪个对象是一个表(基表)。如上所示，saleperorder对应table_type列的值为：view。然而，如果要查询每个销售订单的总销售额，只需要对saleperorder视图执行一个简单的select语句，如下所示：

select 
  *
from
  saleperorder;

执行上面查询语句，得到以下结果：

+-------------+----------+
| ordernumber | total  |
+-------------+----------+
|    10165 | 67392.85 |
|    10287 | 61402.00 |
|    10310 | 61234.67 |
|    10212 | 59830.55 |
|-- 此处省略了many many数据-- |
|    10116 | 1627.56 |
|    10158 | 1491.38 |
|    10144 | 1128.20 |
|    10408 | 615.45  |
+-------------+----------+
327 rows in set

我们再来基于另一个视图创建一个视图，比如，根据salesperorder视图创建名为大销售订单(bigsalesorder)的视图，以显示总计大于60,000的每个销售订单，如下所示：

create view bigsalesorder as
  select 
    ordernumber, round(total,2) as total
  from
    saleperorder
  where
    total > 60000;

现在，我们可以从bigsalesorder视图查询数据，如下所示：

select 
  ordernumber, total
from
  bigsalesorder;

执行上面查询语句，得到以下结果：

+-------------+----------+
| ordernumber | total  |
+-------------+----------+
|    10165 | 67392.85 |
|    10287 | 61402.00 |
|    10310 | 61234.67 |
+-------------+----------+
3 rows in set

完事我们再来尝试使用inner join创建包含客户编号和客户支付的总金额的视图，如下所示：

create view customerorders as
  select 
    c.customernumber,
    p.amount
  from
    customers c
      inner join
    payments p on p.customernumber = c.customernumber
  group by c.customernumber
  order by p.amount desc;

我们使用下面的sql来查询customerorders视图中的数据：

+----------------+-----------+
| customernumber | amount  |
+----------------+-----------+
|      124 | 101244.59 |
|      321 | 85559.12 |
|      239 | 80375.24 |
| **** 此处省略了many many数据 ***|
|      219 | 3452.75  |
|      216 | 3101.4  |
|      161 | 2434.25  |
|      172 | 1960.8  |
+----------------+-----------+
98 rows in set

再来尝试使用子查询创建包含价格高于所有产品的平均价格的产品的视图，如下所示：

create view aboveavgproducts as
  select 
    productcode, productname, buyprice
  from
    products
  where
    buyprice > 
 (select 
        avg(buyprice)
      from
        products)
  order by buyprice desc;

我们来查询aboveavgproducts视图的数据：

select 
  *
from
  aboveavgproducts;

执行上面查询语句，得到以下结果：

+-------------+-----------------------------------------+----------+
| productcode | productname               | buyprice |
+-------------+-----------------------------------------+----------+
| s10_4962  | 1962 lanciaa delta 16v         | 103.42  |
| s18_2238  | 1998 chrysler plymouth prowler     | 101.51  |
| s10_1949  | 1952 alpine renault 1300        | 98.58  |
|************* 此处省略了many many数据 *********************************|
| s18_3320  | 1917 maxwell touring car        | 57.54  |
| s24_4258  | 1936 chrysler airflow          | 57.46  |
| s18_3233  | 1985 toyota supra            | 57.01  |
| s18_2870  | 1999 indy 500 monte carlo ss      | 56.76  |
| s32_4485  | 1974 ducati 350 mk3 desmo        | 56.13  |
| s12_4473  | 1957 chevy pickup            | 55.7   |
| s700_3167  | f/a 18 hornet 1/72           | 54.4   |
+-------------+-----------------------------------------+----------+
54 rows in set

好啦，到这里了，视图的创建和使用已经介绍的差不多了。但是，视图就没有什么使用的限制么？答案当然是有的，我们来分别看下。

首先，我们不能在视图上创建索引，再来就是当使用合并算法的视图查询数据时，mysql会使用底层表的索引，还有就是对于使用诱惑算法的视图，当我们针对视图查询数据时，不会使用索引。

还有就是要注意在mysql5.7.7之前版本，是不能在select语句的from子句中使用子查询来定义视图的。

再来就是如果删除或重命名视图所基于的表，则mysql不会发出任何错误。但是，mysql会使视图无效，我们可以使用check table语句来检查视图是否有效。

一个简单的视图可以更新表中数据，但是基于具有连接，子查询等的复杂select语句创建的视图无法更新。

mysql不像oracle、postgresql等其他数据库系统那样支持物理视图，mysql是不支持物理视图的。

好啦，关于视图本次就记录到这里了。