学习SQL【6】-复杂查询

到目前为止,我们学习了表的创建、查询和更新等数据库的基本操作方法。现在我们将会在这些基本方法的基础上,学习一些实际应用的方法。

一:视图

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  F
 ROM 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) 
ASSELECT * 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这样作为表的别名的名称,它存在一个有效的范围,也就是它的作用域。具体来说,子查询内部设定的关联名称,只能在该子查询内部使用。

每天学习一点点,每天进步一点点。

原文发布于微信公众号 - 小白客(youcoding)

原文发表时间:2017-12-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

MySQL 5.6,5.7的优化器对于count(*)的处理方式

最近看了很多阿里同学的MySQL文章,阿里内核同学的文章一言不合就上代码,不光让我们看到了结果,还能有代码可读,如果碰到了类似的问题,这样的解读确实是很难...

3706
来自专栏个人随笔

那些年我们的(具有含金量)MySQL测试题目

 请耐心阅读,下面有惊喜! 1.创建数据库 CREATE DATABASE QQDB; 2.创建各表(表结构;约束) /*******************创...

5618
来自专栏james大数据架构

你真的会玩SQL吗?你所不知道的 数据聚合

你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接、外连接 你真的会玩SQL吗?三范式、数据完整性 你真...

2157
来自专栏数据和云

SQL优化:紧急情况下提高SQL性能竟是这样实现的!

作者 | 黄堋 ,多年一线 Oracle DBA 经验,长期服务电信、电网、医院、政府等行业客户。擅长数据库优化、数据库迁移升级、数据库故障处理。

936
来自专栏小怪聊职场

MySQL(八)|MySQL中In与Exists的区别(2)

1773
来自专栏闻道于事

数据库 105道题目整理与吐血总结

第一波题目 drop table PRODUCT cascade constraints; create table PRODUCT ( id NUMBER n...

6149
来自专栏Grace development

老项目重构手记之用户系统

重构首先要注意几个点 – 重构后功能的可扩展性 – 业务互相依赖的复杂度 – 脱离本身的业务进行重构 – 重构后的代码可读性与可维护性 – 性能的提升...

1222
来自专栏小怪聊职场

MySQL(七)|MySQL中In与Exists的区别(1)

2582
来自专栏杨建荣的学习笔记

通过图表简化sql语句的表关联(r4笔记第70天)

在之前的博文中分享过一个执行了两天的一条sql语句,走了两个大表的扫描,导致执行时间很长,通过简化sql做了不小的改进,今天我们来看看还可以做些什么。 上次简化...

3634
来自专栏互联网开发者交流社区

SQL触发器实例(下)

1484

扫码关注云+社区

领取腾讯云代金券