mysql视图代码_mysql创建视图的实例代码

1，创建视图的语法

复制代码 代码示例:

CREATE

[ALGORITHM = {MERGE  | TEMPTABLE | UNDEFINED}]

VIEW [database_name].[view_name]

AS

[SELECT  statement]

2，mysql创建视图的例子

1，创建一个简单的视图

复制代码 代码示例:

CREATE VIEW SalePerOrder

AS

SELECT orderNumber,

SUM  (quantityOrdered * priceEach) total

FROM orderDetails

GROUP by orderNumber

ORDER BY total DESC

调用示例：

复制代码 代码示例:

SELECT total

FROM salePerOrder

WHERE orderNumber = 10102

2，结合INNER JOIN语句创建一个视图。

该视图包含订单号，客户名称和总销售额。

复制代码 代码示例:

CREATE VIEW customerOrders AS

SELECT  D.orderNumber,

customerName,

SUM(quantityOrdered * priceEach) total

FROM orderDetails D

INNER JOIN orders O ON O.orderNumber = D.orderNumber

INNER JOIN customers C ON O.customerNumber =  C.customerNumber

GROUP BY D.orderNumber

ORDER BY total DESC

3，创建子查询视图

创建一个视图子查询。、

该视图包含的产品，其购买价格高于平均价格的所有产品。

复制代码 代码示例:

CREATE VIEW vwProducts  AS

SELECT productCode,

productName,

buyPrice

FROM products

WHERE buyPrice > (

SELECT AVG  (buyPrice)

FROM  products

)

ORDER BY buyPrice DESC