5复杂查询
5-1视图
视图和表
当我们创建表时，会通过INSERT语句将数据保存到数据库中，而数据库中的数据会被保存到计算机的存储设备中；但是使用视图不会将数据保存在数据库中，实际上视图只保存SELECT语句，从视图中读取数据时，视图会在内部执行该SELECT语句并创建出一张临时表。
视图的优点：
1.无需保存数据，节省存储设备的容量
2.可以将频繁使用的SELECT语句保存为视图
创建视图的方法
--创建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;
5-1.png
在FROM子句中使用视图的查询通常有两个步骤：
1.首先执行定义视图的SELECT子句；
2.根据得到的结果，再执行在FROM子句中使用视图的SELECT子句
定义视图时，可以使用WHERE，GROUP BY，HAVING等语句
此外应该避免下列的多重视图（以视图的基础创建视图）：
CREATE VIEW ProductSumJim (product_type, cnt_product)
AS
SELECT product_type, cnt_product
FROM ProductSum
WHERE product_type = '办公用品';
视图的限制
1.定义视图时不能使用ORDER BY子句
这是因为视图和表一样，数据行是没有顺序的
2.只在某些条件下可以进行更新
更新语句：INSERT,DELETE,UPDATE
比较有代表性的条件：
SELECT子句未使用DISTINCT；
FROM子句中只有一张表；
未使用GROUP BY子句；
未使用HAVING子句。
视图和表需要同时进行更新，因而通过汇总(GROUP BY)得到的视图无法进行更新。
--能够进行更新的视图
CREATE VIEW ProductJim (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
AS
SELECT *
FROM Product
WHERE product_type = '办公用品';
--向视图中添加数据行
INSERT INTO ProductJim VALUES ('0009', '印章', '办公用品', 95, 10, '2009-11-30');
另外，需要注意的是，在PostgreSQL运行上面的插入语句会报错，由于PostgreSQL初始设定视图为只读，我们通过下列语句来允许PostgreSQL对视图进行更新：
CREATE OR REPLACE RULE insert_rule
AS ON INSERT
TO ProductJim DO INSTEAD
INSERT INTO Product VALUES(
new.product_id,
new.product_name,
new.product_type,
new.sale_price,
new.purchase_price,
new.regist_date);
执行的结果：视图及原表中数据均已被插入
删除视图
DROP VIEW ProductSum;
在PostgreSQL中此句会出错，由于关联视图存在的原因，修改为：
DROP VIEW ProductSum CASCADE;
5-2子查询
首先来复习一下前面是怎么使用视图来查询的：
--创建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语句
SELECT product_type, cnt_product
FROM (SELECT product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type) AS ProductSum;
这两种方式得到的结果完全一致。
执行顺序是先执行括号内的SELECT语句，再执行括号外的SELECT语句。
--尝试增加子查询的层数
SELECT product_type, cnt_product
FROM ( SELECT *
FROM ( SELECT product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type) AS ProductSum
WHERE cnt_product = 4) AS ProductSum2;
5-2.png
子查询的名称
原则上，子查询必须使用AS设定名称
标量子查询
标量子查询指的是必须且只能返回1行1列的子查询
假设我们想要“查询出销售单价高于平均销售单价的商品”
--错误的语句
--在WHERE子句中不能使用聚合函数
SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > AVG(sale_price); --错误
要解决这个问题，我们可以使用标量子查询语句
--计算平均销售单价的标量子查询
SELECT AVG(sale_price)
FROM Product;
--选取出销售单价高于全部商品的平均单价的商品
SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product);
5-3.png
标量子查询的书写位置
能够使用常数或者列名的地方，无论是SELECT子句、GROUP BY子句、HAVING子句还是ORDER BY子句，几乎所有的地方都可以使用
--在SELECT子句中使用标量子查询
SELECT product_id,
product_name,
sale_price,
(SELECT AVG(sale_price)
FROM Product) AS avg_price
FROM Product;
--在HAVING子句中使用标量子查询
SELECT product_type, AVG(sale_price)
FROM Product
GROUP BY product_type
HAVING AVG(sale_price) > (SELECT AVG(sale_price)
FROM Product);
使用标量子查询时的注意事项
标量子查询只能够返回单一值，如果返回多行值，它就不再是标量子查询，而仅仅是一个普通的子查询
--由于不是标量子查询，因此不能在SELECT子句中使用
SELECT product_id,
product_name,
sale_price,
(SELECT AVG(sale_price)
FROM Product
GROUP BY product_type) AS avg_price
FROM Product;
该段会报错，这是由于子查询的结果是三个而不是一个，不再是标量子查询，不可以写在SELECT子句中
5-3关联子查询
普通的子查询和关联子查询的区别
假设这次我们想要“查询出某个商品类中高于此类商品平均销售单价的商品”
--按照商品种类计算平均价格
SELECT AVG(sale_price)
FROM Product
GROUP BY product_type;
--发生错误的子查询
SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product
GROUP BY product_type);
这种方法显然是错误的，因为普通的子查询被当做了标量子查询。
为了解决这个问题，可以利用关联子查询：
SELECT product_type, 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
GROUP BY product_type);
5-4.png
在这里起到关键作用的是在子查询中添加的WHERE子句的条件，它的意思是在同一商品种类中，对商品的销售单价和平均单价进行比较。
由于作为比较对象的都是同一张Product表，因此要使用P1和P2两个别名。
需要注意的是：
1.关联子查询也是用来对集合进行切分的
2.结合条件一定要写在子查询中
网友评论