参考资料: 《SQL基础教程》
复杂查询
从SQL的角度来看,视图就是一张表,两者的区别在于是否保存了实际的数据。
INSERT
语句将数据保存到数据库中,而数据库中的数据实际上会保存到计算机的存储设备。SELECT
语句。从视图读取数据时,视图会在内部执行该SELECT
语句并创建出一张临时表。视图和表
总结成一句话:表中存储的是实际数据,视图本质上就是SELECT
语句。
视图的优点
SELECT
语句保存成视图,就不用每次都重新书写了。创建视图的CREATE VIEW
语句
CREATE VIEW 视图名称 (<视图列名1>, <视图列名2>, ...)
AS
<SELECT语句>
注意:SELECT
语句中列的排列顺序和视图中列的排列顺序相同,SELECT
语句中的第1列就是视图中的第1列,以此类推。视图的列名在视图名称之后的列表中定义。
创建ProductSum视图
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
使用视图
SELECT
product_type,
cnt_product
FROM ProductSum;
执行结果:
在FROM
子句中使用视图查询的两个步骤
SELECT
语句;FROM
子句中使用视图的SELECT
语句。当然,我们还可以以视图为基础再创建视图,因此,使用视图的查询通常需要执行2条以上的SELECT
语句。但是,多重视图会降低SQL的性能,因此希望大家使用单一视图。
ORDER BY
子句为什么不能使用ORDER BY
子句?
因为视图和表一样,数据行都是没有顺序的。
在SELECT
语句中,视图可以和表一样使用,那么,更新语句(INSERT
、DELETE
、UPDATE
)呢?
标准SQL中规定:如果定义视图的SELECT
语句能够满足某些条件,那么这个视图就可以被更新。
几个具有代表性的条件:
SELECT
子句中未使用DISTINCT
FROM
子句中只有一张表GROUP BY
子句HAVING
子句删除视图的DROP VIEW
语句
DROP VIEW 视图名称
删除视图ProductSum
DROP VIEW ProductSum;
子查询就是一次性的视图(SELECT
语句),子查询在SELECT
语句执行完毕之后就会消失。
子查询
-- 和上面使用ProductSum视图实现相同功能的子查询语句
SELECT
product_type,
cnt_product
FROM (
SELECT
product_type,
COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type
) AS ProductSum;
执行结果:
子查询SELECT
语句的执行顺序
由内到外:
FROM
子句中的SELECT
语句;SELECT
语句。注意:子查询的层数原则上没有限制,可以无限嵌套下去,但是,随着层数增加,SQL语句会变得越来越难读懂,性能也会越来越差。因此,尽量避免使用多层嵌套的子查询。
子查询的名称
原则上子查询必须设定名称,设定名称是需要使用AS
关键字,有时也可以省略。
标量子查询就是返回单一值的子查询,必须而且只能返回表中某一行的某一列的值。
WHERE
子句中使用标量子查询如何查询出销售单价高于平均销售单价的商品? 我们可能会想到以下错误的SQL语句:
-- 在WHERE子句中不能使用聚合函数
SELECT
product_id,
product_name,
sale_price
FROM Product
WHERE sale_price > AVG(sale_price);
虽然这样的SELECT
语句看上去能够满足我们的要求,但是由于在WHERE
子句中不能使用聚合函数,因此这样的SELECT
语句是错误的。
这时,标量子查询就可以发挥它的功效了。
计算平均销售单价的标量子查询
SELECT AVG(sale_price)
FROM Product;
执行结果:
选出销售单价高于平均单价的商品
SELECT
product_id,
product_name,
sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product);
执行结果:
通常任何可以使用单一值的位置都可以使用标量子查询,也就是说,能够使用常数或者列名的地方,无论是SELECT
子句、GROUP BY
子句、HAVING
子句,还是ORDER BY
子句,都可以使用。
注意:如果子查询返回了多行结果,那么它就不再是标量子查询,而仅仅只是一个普通的子查询,因此不能被用在需要单一输入值的地方。
通过关联子查询按照商品种类对平均销售单价进行比较
SELECT
product_id,
product_name,
sale_price
FROM Product AS P1
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product AS P2
WHERE P1.product_type = P2.product_type);
执行结果:
这里起关键作用的就是在子查询中添加WHERE
子句的条件,该条件的意思就是,在同一商品种类中对各商品的销售单价和平均单价进行比较。