这篇文章主要介绍了mysql视图之创建视图(CREATE VIEW)和使用限制,结合实例形式详细分析了mysql视图创建于使用相关原理与操作注意事项,需要的朋友可以参考下

本文实例讲述了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是不支持物理视图的。

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

更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL查询大全》、《MySQL事务操作汇总》、《MySQL存储过程大全》、《MySQL数据库锁相关汇总》及《MySQL常用函数大汇总》

希望本文所述对大家MySQL数据库计有所帮助。

最新资讯
“天才少年”与企业,谁成就了谁?

“天才少年”与企业,谁

华为“天才项目”是由任正非发起,通过顶级挑战与顶级薪
为什么细胞不能无限长大?

为什么细胞不能无限长

科学家发现,细胞体积增长,细胞内抑制细胞分裂的蛋白却不
Y 染色体正在消亡?新研究认为它其实很稳定,男同胞们可以放心了

Y 染色体正在消亡?新研

Y 染色体未来会消亡吗?最近发表的一篇观点文章提出了一
小鹏汽车年收入暴增238倍 研发投入20.7亿

小鹏汽车年收入暴增23

招股书文件显示,小鹏汽车年收入暴增238倍 研发投入20.7
小鹏汽车在美正式提交IPO 何小鹏持股31.6%为最大股东

小鹏汽车在美正式提交

IPO前,小鹏汽车管理层持股达40.9%,其中何小鹏持股31.6%
小鹏汽车在美递交IPO申请 将在纽交所挂牌融资规模1亿美元

小鹏汽车在美递交IPO

小鹏汽车递交美国IPO申请,拟按代码“XPEV”在纽交所挂
最新文章
mysql日期处理函数实例解析

mysql日期处理函数实

这篇文章主要介绍了mysql日期处理函数实例解析,文中通
mysql存储过程之错误处理实例详解

mysql存储过程之错误

这篇文章主要介绍了mysql存储过程之错误处理,结合实例
mysql 8.0.18 mgr 搭建及其切换功能

mysql 8.0.18 mgr 搭

这篇文章主要介绍了mysql 8.0.18 mysql搭建及其切换功
win10 mysql导出csv的两种方式

win10 mysql导出csv的

win10导出csv有两种方式,第一种是借助工具,第二种是原始
mysql慢查询操作实例分析【开启、测试、确认等】

mysql慢查询操作实例

这篇文章主要介绍了mysql慢查询操作,结合实例形式分析
MySQL多表连接的入门实例教程

MySQL多表连接的入门

这篇文章主要给大家介绍了关于MySQL多表连接的相关资