当前位置：首页 > article >正文

SQL笔记#复杂查询

article 2025/2/24 6:52:36

一、视图

1、视图和表

使用试图时会执行SELECT语句并创建一张临时表。视图中保存的是SELECT语句；表中保存的是实际数据。

2、创建视图的方法

CREATE VIEW 视图名称(<视图列名1>，<视图列名2>，……)

AS

<SELECT语句>

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;

 product_type | cnt_product
--------------+-------------
 厨房用具     |           4
 衣服         |           2
 办公用品     |           2

还可以以视图为基础创建视图，但多重视图会降低SQL的性能。

-- 在视图的基础上创建视图
CREATE VIEW ProductSumJim (product_type, cnt_product)
AS
SELECT product_type, cnt_product
  FROM ProductSum
 WHERE product_type = '办公用品';

-- 确认创建好的视图
SELECT product_type, cnt_product
  FROM ProductSumJim;

 product_type | cnt_product
--------------+-------------
 办公用品     |           2

3、视图的限制1(定义视图时不能使用ORDER BY子句)

4、视图的限制2(对视图进行更新）

$\blacksquare$ 通过汇总得到的表无法更新

$\blacksquare$ 能够更新的情况(既没有聚合有没有结合)

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');

--  确认数据是否已经添加到视图中
SELECT * FROM ProductJim;

-- 确认数据是否已经添加到原表中
SELECT * FROM Product;

postgres=# SELECT * FROM ProductJim;
 product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------
 0002       | 打孔器       | 办公用品     |        500 |            320 | 2009-09-11
 0008       | 圆珠笔       | 办公用品     |        100 |                | 2009-11-11
 0009       | 印章         | 办公用品     |         95 |             10 | 2009-11-30
(3 行记录)


postgres=# SELECT * FROM Product;
 product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------
 0001       | T恤          | 衣服         |       1000 |            500 | 2009-09-20
 0002       | 打孔器       | 办公用品     |        500 |            320 | 2009-09-11
 0003       | 运动T恤      | 衣服         |       4000 |           2800 |
 0004       | 菜刀         | 厨房用具     |       3000 |           2800 | 2009-09-20
 0005       | 高压锅       | 厨房用具     |       6800 |           5000 | 2009-01-15
 0006       | 叉子         | 厨房用具     |        500 |                | 2009-09-20
 0007       | 擦菜板       | 厨房用具     |        880 |            790 | 2008-04-28
 0008       | 圆珠笔       | 办公用品     |        100 |                | 2009-11-11
 0009       | 印章         | 办公用品     |         95 |             10 | 2009-11-30
(9 行记录)

5、删除视图

-- 删除视图
DROP VIEW ProductSum;

-- 由于存在关联，所以可能会报错,可以采用下列方式

/* 
DROP VIEW ProductSum CASCADE;
*/

-- 删除商品为0009(印章)的数据
DELETE FROM Product WHERE product_id = '0009';

二、子查询

1、子查询和视图

视图的方式：

-- 根据商品种类统计商品数量的视图
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;

子查询的方式：1、内层的查询，FROM子句中的SELECT子句；2、外层的查询，SELECT子句。

SELECT product_type, cnt_product
  FROM (SELECT product_type, COUNT(*) AS cnt_product
          FROM Product
         GROUP BY product_type) AS ProductSum;

$\blacksquare$ 增加子查询的层数

-- 1、统计不同商品种类的数量
-- 2、选取商品数量为4的数据
-- 3、将选取的相关数据进行汇总

--SQL Server, DB2, PostgreSQL, MySQL
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

2、子查询的名称

之前的例子给子查询设定了ProductSum等名称，原则上要给子查询设定名称。

3、标量子查询

$\blacksquare$ 什么是标量

标量就是单一的意思，必须且只能返回1行1列的结果。

$\blacksquare$ 在WHERE子句中使用标量子查询

-- 在WHERE子句中不能使用聚合函数,比如下面的代码存在错误
SELECT product_id,product_name,sale_price
  FROM Product
 WHERE sale_price>AVG(sale_price);

postgres=# SELECT AVG(sale_price)
postgres-#      FROM Product;
          avg
-----------------------
 2097.5000000000000000

1、内层的子查询：得到括号内的内容为2097.5；

2、外层的查询：选取售价大于该值的数据信息。

-- 其中对于条件的选取采用标量子查询
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

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

-- 在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
--------------+-----------------------
 厨房用具     | 2795.0000000000000000
 衣服         | 2500.0000000000000000

5、使用标量子查询时的注意事项

在使用子查询时，要注意改子查询不能返回多行结果；例如下述的SELECT子查询会发生错误。

SELECT product_id, 
       product_name, 
       sale_price,
       (SELECT AVG(sale_price)
          FROM Product
         GROUP BY product_type) AS avg_price
  FROM Product;

三、关联子查询

1、普通的子查询和关联子查询的区别

$\blacksquare$ 按照商品种类与平均销售单价进行比较

-- 按照商品种类计算平均价格
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);

$\blacksquare$ 使用关联子查询的解决方案

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

2、关联子查询也是用来对集合进行切分的

3、结合条件一定要写在子查询中

关联条件移到子查询之外是错误的。内部可以看到外部，外部看不到内部。注意作用域！！！

-- 错误的关联子查询书写方式
SELECT product_type,product_name,sale_price
  FROM Product AS P1
 WHERE P1.product_type=P2.product_type
   AND sale_price>(SELECT AVG(sale_price)
                     FROM Product AS P2
                     GROUP BY product_type);

查看全文

http://www.kler.cn/a/558576.html