学习SQL【6】-复杂查询
学习.png
到目前为止，我们学习了表的创建、查询和更新等数据库的基本操作方法。现在我们将会在这些基本方法的基础上，学习一些实际应用的方法。
一：视图
1：视图和表
表中存储的是实际数据，而视图中保存的是从表中获取数据所使用的SELECT语句。从SQL的角度来看，视图和表是一样的，只是视图并不存储数据，而是存储SELECT语句。
视图的优点：
视图的优点大体上有两点。
● 第一点是由于视图无需保存数据，因此可以节省存储设备的容量。
● 第二点是可以将频繁使用的SELECT语句保存成视图，这样就不用每次重新书写了。
所以应该将经常使用的SELECT语句做成视图。
2：创建视图的方法
创建视图需要使用CREATE VIEW语句，其语法如下：
CREATE VIEW 视图名称 （<视图列名1>, <视图列名2>,...）
AS <SELECT语句>
注释：
SELECT语句需要书写在AS关键字之后，SELECT语句中列的排列顺序和视图中列的排列顺序相同。
接下来，我们仍然使用最开始创建的Product表为基础进行下面的演示。Product表的结构和内容如下所示：
product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
0001 | T衫 | 衣服 | 1000 | 500 | 2017-09-20
0002 | 打孔器 | 办公用品 | 500 | 320 | 2017-09-11
0003 | 运动T衫 | 衣服 | 4000 | 2800 |
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2017-09-20
0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2017-01-15
0006 | 叉子 | 厨房用具 | 500 | | 2017-09-20
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2016-04-28
0008 | 圆珠笔 | 办公用品 | 100 | | 2017-11-11
(8 行记录)
下面就让我们试着创建视图吧：
--ProductSum视图
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
这样我们就创建了一个名为ProductSum的视图。
视图和表一样，可以书写在SELECT语句的FROM子句中。
--使用视图
SELECT product_type, cnt_product FROM ProductSum;
执行结果：
product_type | cnt_product--------------+-------------
衣服 | 2
办公用品 | 2
厨房用具 | 4
(3 行记录)
使用视图的查询的步骤：
① 首先执行定义视图的SELECT语句。
② 根据得到的结果，再执行在FROM子句中使用视图的SELECT语句。
还可以创建多重视图，即是在视图的基础上再创建视图。
--视图ProductSumJim
CREATE VIEW ProductSumJim (product_type, cnt_product)
AS
SELECT product_type, cnt_product
FROM ProductSum
WHERE product_type = '办公用品';
确认创建好的视图:
SELECT * FROM ProductSumJim;
执行结果：
product_type | cnt_product--------------+-------------
办公用品 | 2
(1 行记录)
注释：
● 对大多数DBMS来说，多重视图会降低SQL性能，所以我们应该避免使用多重视图。
● 定义视图不要使用ORDER 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, '2017-11-30');
确认数据是否已经添加到视图中:
SELECT * FROM ProductJim;
执行结果：
product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
0002 | 打孔器 | 办公用品 | 500 | 320 | 2017-09-11
0008 | 圆珠笔 | 办公用品 | 100 | | 2017-11-11
0009 | 印章 | 办公用品 | 95 | 10 | 2017-11-30
(3 行记录)
确认数据是否添加到原表中:
SELECT * FROM Product;
执行结果：
product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
0001 | T衫 | 衣服 | 1000 | 500 | 2017-09-20
0002 | 打孔器 | 办公用品 | 500 | 320 | 2017-09-11
0003 | 运动T衫 | 衣服 | 4000 | 2800 |
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2017-09-20
0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2017-01-15
0006 | 叉子 | 厨房用具 | 500 | | 2017-09-20
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2016-04-28
0008 | 圆珠笔 | 办公用品 | 100 | | 2017-11-11
0009 | 印章 | 办公用品 | 95 | 10 | 2017-11-30
(9 行记录)
3：删除视图
删除视图需要使用DROP VIEW语句
例如，删除视图ProductSum：
DROP VIEW ProductSum;
但是在PostgreSQL中，由于视图ProductSum存在关联视图ProductSumJim,因此会发生如下错误：
错误: 无法删除 视图 productsum 因为有其它对象倚赖它
描述: 视图 productsumjim 倚赖于 视图 productsum
提示: 使用 DROP .. CASCADE 把倚赖对象一并删除.
这时可以使用CASCADE选项来删除关联视图：
DROP VIEW ProductSum CASCADE;
我们再次将Product表恢复到初始状态（8行），因此我们要删掉刚才添加进的第九行：
DELETE FROM Product WHERE product_id = '0009';
二：子查询
1：子查询与视图
一言以蔽之，子查询就是一次性视图（SELECT语句）。与视图不同，子查询在SELECT语句执行完毕之后就会消失。
子查询的特点：将用来定义视图的SELECT语句直接用于FROM子句中。
例如：
--在FROM子句中直接书写定义视图的SELECT语句
SELECT product_type, cnt_product
FROM (SELECT product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type) AS ProductSum;
两种方法得到的结果完全相同。
product_type | cnt_product--------------+-------------
衣服 | 2
办公用品 | 2
厨房用具 | 4
(3 行记录)
注释：
子查询作为内层查询会首先执行。
增加子查询的层数：
由于子查询的层数原则上没有限制，因此可以在子查询的FROM子句中再继续使用子查询语句。
--增加子查询的嵌套层数
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;
执行结果：
product_type | cnt_product--------------+-------------
厨房用具 | 4
(1 行记录)
但是随着子查询的层数增加，SQL语句会变得愈发地难以读懂，所以应该避免使用多层嵌套的子查询语句。
2：子查询的名称
原则上子查询必须设定名称。为子查询设定名称时需要使用关键字AS。
3：标量子查询
标量就是单一的意思，而标量子查询则有一个特殊的限制，那就是必须而且只能返回1行1列的结果。
也就是说标量子查询是返回单一值的子查询。
在WHERE子句中使用标量子查询：
比如，我们需要查出销售单价高于平均销售单价的商品：
先计算出平均销售单价：
--计算平均销售单价的标量子查询
SELECT AVG(sale_price) FROM Product;
执行结果：
avg-----------------------
2097.5000000000000000
(1 行记录)
然后完整的SQL代码如下所示：
--选取出销售单价高于全部商品的平均单价的商品
SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product);
执行结果：
product_id | product_name | sale_price------------+--------------+------------
0003 | 运动T衫 | 4000
0004 | 菜刀 | 3000
0005 | 高压锅 | 6800
(3 行记录)
4：标量子查询的书写位置
能够使用常数或者列名的地方，无论是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;
执行结果：
product_id | product_name | sale_price | avg_price------------+--------------+------------+-----------------------
0001 | T衫 | 1000 | 2097.5000000000000000
0002 | 打孔器 | 500 | 2097.5000000000000000
0003 | 运动T衫 | 4000 | 2097.5000000000000000
0004 | 菜刀 | 3000 | 2097.5000000000000000
0005 | 高压锅 | 6800 | 2097.5000000000000000
0006 | 叉子 | 500 | 2097.5000000000000000
0007 | 擦菜板 | 880 | 2097.5000000000000000
0008 | 圆珠笔 | 100 | 2097.5000000000000000
(8 行记录)
在HAVING子句中使用标量子查询:
SELECT product_type, AVG(sale_price)
FROM Product
GROUP BY product_type
HAVING AVG(sale_price) > (SELECT AVG(sale_price)
FROM Product);
执行结果：
product_type | avg--------------+-----------------------
衣服 | 2500.0000000000000000
厨房用具 | 2795.0000000000000000
(2 行记录)
三：关联子查询
1：普通子查询与关联子查询的区别
按此前所学，使用子查询就能选出销售单价高于全部商品平均销售单价的商品，这次我们稍微更改一下需求，选取出各种商品中高于该类商品平均销售单价的商品。
如果我们使用标量子查询的方法就会发生错误：
SELECT product_type, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product
GROUP BY product_type);
发生错误的原因就是该子查询会返回3行结果，并不是标量子查询，而在WHERE子句中使用子查询时，必须是标量子查询。
这个时候就可以使用关联子查询来解决上述问题。
--通过关联子查询按照商品种类对平均销售单价进行比较
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);
执行结果：
product_type | product_name | sale_price--------------+--------------+------------
办公用品 | 打孔器 | 500
衣服 | 运动T衫 | 4000
厨房用具 | 菜刀 | 3000
厨房用具 | 高压锅 | 6800
(4 行记录)
这里的关键就是在子查询中添加WHERE子句的条件。该条件的意思就是，在同一商品种类中对各个商品的销售单价和平均单价进行比较。
因此，在细分的组内进行比较时，需要使用关联子查询。
2：关联子查询也是用来对集合进行切分的
换个角度来看，其实关联子查询也和GROUP BY子句一样，可以对集合进行切分。
3：关联条件一定要写在子查询内
关联名称就是像P1，P2这样作为表的别名的名称，它存在一个有效的范围，也就是它的作用域。具体来说，子查询内部设定的关联名称，只能在该子查询内部使用。
每天学习一点点，每天进步一点点。
