CREATE VIEW语句简介

CREATE 
   [ALGORITHM = {MERGE  | TEMPTABLE | UNDEFINED}]
VIEW view_name [(column_list)]
AS
select-statement; 

视图的处理算法

1. 使用MERGE算法，MySQL首先将输入查询与SELECT定义视图的语句组合成单个查询。然后MySQL执行组合查询以返回结果集。 如果SELECT语句包含聚合函数，例如 MIN，MAX，SUM，COUNT，AVG 或DISTINCT，GROUP BY，HAVING，LIMIT，UNION，UNION ALL，子查询，则不允许使
2. 用MERGE算法。如果SELECT语句引用无表，则也不允许MERGE算法。如果不允许MERGE算法，MySQL将算法更改为UNDEFINED。请注意，将视图定义中的输入查询和查询组合到一个查询中称为视图分辨率。
   使用TEMPTABLE算法，MySQL首先根据定义视图的SELECT语句创建临时表，然后对临时表执行输入查询。因为MySQL必须创建一个临时表来存储结果集并将数据从基表移动到临时表，所以TEMPTABLE 算法的效率低于MERGE算法。此外，使用TEMPTABLE 算法的视图不可更新。
3. 在未指定显式算法的情况下创建视图时，这是默认UNDEFINED算法。UNDEFINED算法允许MySQL选择使用 MERGE或TEMPTABLE 算法。MySQL更喜欢MERGE 算法在TEMPTABLE 算法中，因为 MERGE算法效率更高。

视图名称

在数据库中，视图和表共享相同的命名空间，因此视图和表不能具有相同的名称。此外，视图的名称必须遵循表的命名规则。

SELECT 语句

在SELECT 语句中，可以查询数据库中存在的任何表或视图中的数据。SELECT声明必须遵循以下几条规则：

1. SELECT语句可以在WHERE子句中包含子查询，但不包含在FROM子句中。
2. SELECT语句不能引用任何变量 包括局部变量，用户变量和会话变量。
3. SELECT语句不能引用预准备语句的参数。

请注意，SELECT语句不需要引用任何表。

创建简单视图

CREATE VIEW SalePerOrder AS
    SELECT 
        orderNumber, SUM(quantityOrdered * priceEach) total
    FROM
        orderDetails
    GROUP by orderNumber
    ORDER BY total DESC; 

SELECT 
    *
FROM
    salePerOrder;

基于另一个视图创建视图

CREATE VIEW BigSalesOrder AS
    SELECT 
        orderNumber, ROUND(total,2) as total
    FROM
        saleperorder
    WHERE
        total > 60000; 

SELECT 
    orderNumber, total
FROM
    BigSalesOrder; 

使用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 c.customerNumber = c.customerNumber
    GROUP BY d.orderNumber
    ORDER BY total DESC; 

SELECT
    *
FROM
    customerOrders;

使用子查询创建视图

CREATE VIEW aboveAvgProducts AS
    SELECT 
        productCode, productName, buyPrice
    FROM
        products
    WHERE
        buyPrice > 
 (SELECT 
                AVG(buyPrice)
            FROM
                products)
    ORDER BY buyPrice DESC; 

SELECT 
    *
FROM
    aboveAvgProducts; 

MySQL可更新视图简介

在MySQL中，视图不仅可查询，还可以更新。这意味着您可以使用INSERT或 UPDATE语句通过可更新视图插入或更新基表的行。此外，您可以使用DELETE语句通过视图删除基础表的行。

但是，要创建可更新视图，定义视图的SELECT语句不得包含以下任何元素：

1. 聚合函数 ，如MIN，MAX，SUM，AVG和 COUNT。
2. DISTINCT
3. GROUP BY子句。
4. HAVING子句。
5. UNION或UNION ALL子句。
6. 左连接或外连接。
7. 子查询 中的SELECT子句或在引用表WHERE语句出现在FROM子句中。
8. 引用FROM子句中的不可更新视图
9. 仅引用文字值
10. 对基表的任何列的多次引用

如果使用TEMPTABLE算法创建视图，则无法更新视图。

示例

首先，我们创建一个officeInfo 基于示例数据库中的offices 表命名的视图。视图引用 offices 表的三列：officeCode， phone和city

CREATE VIEW officeInfo
 AS 
   SELECT officeCode, phone, city
   FROM offices; 

接下来，我们可以officeInfo使用以下语句从视图中查询数据：

SELECT 
    *
FROM
    officeInfo;

然后，我们可以使用以下UPDATE语句通过officeInfo视图更改officeCode为4的办公室的电话号码。

UPDATE officeInfo 
SET 
    phone = '+33 14 723 5555'
WHERE
    officeCode = 4; 

最后，为了验证更改，我们可以officeInfo 通过执行以下查询来查询视图中的数据:

SELECT 
    *
FROM
    officeInfo
WHERE
    officeCode = 4; 

检查可更新的视图信息

可以通过查询数据库中的views表中的is_updatable列来检查数据库中的视图是否可更新information_schema。

以下查询从classicmodels数据库获取所有视图，并显示哪些视图可更新。

SELECT 
    table_name, 
    is_updatable
FROM
    information_schema.views
WHERE
    table_schema = 'classicmodels'; 

通过视图删除行

首先，我们创建一个名为items 的表，在items表中插入一些行，并创建一个包含价格大于700的项的视图。

-- 判断是否存在，如果存在则删除
DROP TABLE IF EXISTS items;

-- 创建表 items
CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(11 , 2 ) NOT NULL
);
 
-- 向items 中插入数据
INSERT INTO items(name,price) 
VALUES('Laptop',700.56),('Desktop',699.99),('iPad',700.50) ;
 
-- 创建基于 items 的视图
CREATE VIEW LuxuryItems AS
    SELECT 
        *
    FROM
        items
    WHERE
        price > 700;

-- 查询 LuxuryItems 视图
SELECT 
    *
FROM
    LuxuryItems; 

其次，我们使用DELETE 语句删除id值为3的行。

DELETE FROM LuxuryItems 
WHERE
    id = 3; 

MySQL返回一条消息，说明有1行受到影响。

第三，让我们再次通过视图检查数据。

SELECT 
    *
FROM
    LuxuryItems; 

第四，我们还可以查询基表中的数据，items以验证DELETE语句是否实际删除了行。

SELECT 
    *
FROM
    items;