CREATE VIEW语句简介
CREATE
[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]
VIEW view_name [(column_list)]
AS
select-statement;
视图的处理算法
- 使用
MERGE
算法,MySQL首先将输入查询与SELECT定义视图的语句组合成单个查询。然后MySQL执行组合查询以返回结果集。 如果SELECT语句包含聚合函数,例如 MIN,MAX,SUM,COUNT,AVG 或DISTINCT,GROUP BY,HAVING,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算法效率更高。
视图名称
在数据库中,视图和表共享相同的命名空间,因此视图和表不能具有相同的名称。此外,视图的名称必须遵循表的命名规则。
SELECT 语句
在SELECT 语句中,可以查询数据库中存在的任何表或视图中的数据。SELECT声明必须遵循以下几条规则:
- SELECT语句可以在WHERE子句中包含子查询,但不包含在FROM子句中。
- SELECT语句不能引用任何变量 包括局部变量,用户变量和会话变量。
- 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语句不得包含以下任何元素:
- 聚合函数 ,如MIN,MAX,SUM,AVG和 COUNT。
- DISTINCT
- GROUP BY子句。
- HAVING子句。
- UNION或UNION ALL子句。
- 左连接或外连接。
- 子查询 中的SELECT子句或在引用表WHERE语句出现在FROM子句中。
- 引用FROM子句中的不可更新视图
- 仅引用文字值
- 对基表的任何列的多次引用
如果使用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;