SQL必知必会
陈旸
清华大学计算机博士
立即订阅
10179 人已学习
课程目录
已完结 49 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词丨SQL可能是你掌握的最有用的技能
免费
第一章:SQL语法基础篇 (19讲)
01丨了解SQL:一门半衰期很长的语言
02丨DBMS的前世今生
03丨学会用数据库的方式思考SQL是如何执行的
04丨使用DDL创建数据库&数据表时需要注意什么?
05丨检索数据:你还在SELECT * 么?
06丨数据过滤:SQL数据过滤都有哪些方法?
07丨什么是SQL函数?为什么使用SQL函数可能会带来问题?
08丨什么是SQL的聚集函数,如何利用它们汇总表的数据?
09丨子查询:子查询的种类都有哪些,如何提高子查询的性能?
10丨常用的SQL标准有哪些,在SQL92中是如何使用连接的?
11丨SQL99是如何使用连接的,与SQL92的区别是什么?
12丨视图在SQL中的作用是什么,它是怎样工作的?
13丨什么是存储过程,在实际项目中用得多么?
14丨什么是事务处理,如何使用COMMIT和ROLLBACK进行操作?
15丨初识事务隔离:隔离的级别有哪些,它们都解决了哪些异常问题?
16丨游标:当我们需要逐条处理数据时,该怎么做?
17丨如何使用Python操作MySQL?
18丨SQLAlchemy:如何使用Python ORM框架来操作MySQL?
19丨基础篇总结:如何理解查询优化、通配符以及存储过程?
第二章:SQL性能优化篇 (18讲)
20丨当我们思考数据库调优的时候,都有哪些维度可以选择?
21丨范式设计:数据表的范式有哪些,3NF指的是什么?
22丨反范式设计:3NF有什么不足,为什么有时候需要反范式设计?
23丨索引的概览:用还是不用索引,这是一个问题
24丨索引的原理:我们为什么用B+树来做索引?
25丨Hash索引的底层原理是什么?
26丨索引的使用原则:如何通过索引让SQL查询效率最大化?
27丨从数据页的角度理解B+树查询
28丨从磁盘I/O的角度理解SQL查询的成本
29丨为什么没有理想的索引?
30丨锁:悲观锁和乐观锁是什么?
31丨为什么大部分RDBMS都会支持MVCC?
32丨查询优化器是如何工作的?
33丨如何使用性能分析工具定位SQL执行慢的原因?
34丨答疑篇:关于索引以及缓冲池的一些解惑
35丨数据库主从同步的作用是什么,如何解决数据不一致问题?
36丨数据库没有备份,没有使用Binlog的情况下,如何恢复数据?
37丨SQL注入:你的SQL是如何被注入的?
第三章:认识DBMS (7讲)
38丨如何在Excel中使用SQL语言?
39丨WebSQL:如何在H5中存储一个本地数据库?
40丨SQLite:为什么微信用SQLite存储聊天记录?
41丨初识Redis:Redis为什么会这么快?
42丨如何使用Redis来实现多用户抢票问题
43丨如何使用Redis搭建玩家排行榜?
44丨DBMS篇总结和答疑:用SQLite做词云
第四章:SQL项目实战 (3讲)
45丨数据清洗:如何使用SQL对数据进行清洗?
46丨数据集成:如何对各种数据库进行集成和转换?
47丨如何利用SQL对零售数据进行分析?
结束语 (1讲)
结束语 | 互联网的下半场是数据驱动的时代
SQL必知必会
登录|注册

09丨子查询:子查询的种类都有哪些,如何提高子查询的性能?

陈旸 2019-07-01
上节课我讲到了聚集函数,以及如何对数据进行分组统计,可以说我们之前讲的内容都是围绕单个表的 SELECT 查询展开的,实际上 SQL 还允许我们进行子查询,也就是嵌套在查询中的查询。这样做的好处是可以让我们进行更复杂的查询,同时更加容易理解查询的过程。因为很多时候,我们无法直接从数据表中得到查询结果,需要从查询结果集中再次进行查询,才能得到想要的结果。这个“查询结果集”就是今天我们要讲的子查询。
通过今天的文章,我希望你可以掌握以下的内容:
子查询可以分为关联子查询和非关联子查询。我会举一个 NBA 数据库查询的例子,告诉你什么是关联子查询,什么是非关联子查询;
子查询中有一些关键词,可以方便我们对子查询的结果进行比较。比如存在性检测子查询,也就是 EXISTS 子查询,以及集合比较子查询,其中集合比较子查询关键词有 IN、SOME、 ANY 和 ALL,这些关键词在子查询中的作用是什么;
子查询也可以作为主查询的列,我们如何使用子查询作为计算字段出现在 SELECT 查询中呢?

什么是关联子查询,什么是非关联子查询

子查询虽然是一种嵌套查询的形式,不过我们依然可以依据子查询是否执行多次,从而将子查询划分为关联子查询和非关联子查询。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《SQL必知必会》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(106)

  • 看,有只猪
    IN表是外边和内表进行hash连接,是先执行子查询。
    EXISTS是对外表进行循环,然后在内表进行查询。
    因此如果外表数据量大,则用IN,如果外表数据量小,也用EXISTS。
    IN有一个缺陷是不能判断NULL,因此如果字段存在NULL值,则会出现返回,因为最好使用NOT EXISTS。
    2019-07-01
    3
    62
  • Sam
    not in是先执行子查询,得到一个结果集,将结果集代入外层谓词条件执行主查询,子查询只需要执行一次;
    not exists是先从主查询中取得一条数据,再代入到子查询中,执行一次子查询,判断子查询是否能返回结果,主查询有多少条数据,子查询就要执行多少次。
             
    2019-07-01
    17
  • humor
    exist和in的区别这一块,感觉没有讲清楚呀,我听下来还是不太明白哪种情况应该用in,哪种情况应该用exists。选择的标准是看能否使用表的索引吗?

    作者回复: 索引是个前提,其实和选择与否 还是要看表的大小。选择的标准,你可以理解为: 小表驱动大表。这种方式下效率是最高的。比如
    SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
    SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)
    当A小于B时,用EXIST。因为EXIST的实现,相当于外表循环,实现的逻辑类似于:
    for i in A
        for j in B
            if j.cc == i.cc then ...

    当B小于A时,用IN,因为实现的逻辑类似于:
    for i in B
        for j in A
            if j.cc == i.cc then ...
    所以哪个表小就用哪个表来驱动,A表小 就用EXIST,B表小 就用IN

    2019-07-03
    8
    12
  • Hero
    1. SELECT
    player_id,
    team_id,
    player_name
    FROM
    player a
    WHERE
    EXISTS ( SELECT b.player_id FROM player_score b GROUP BY b.player_id HAVING AVG( b.score ) > 20 and a.player_id = b.player_id);
    2.SELECT
    player_id,
    team_id,
    player_name
    FROM
    player a
    WHERE
    EXISTS ( SELECT b.player_id FROM player_score b WHERE a.player_id = b.player_id GROUP BY b.player_id HAVING AVG( b.score ) > 20);
    3.SELECT
    player_id,
    team_id,
    player_name
    FROM
    player
    WHERE
    player_id IN ( SELECT player_id FROM player_score GROUP BY player_id HAVING AVG( score ) > 20 );
    推荐3,因为子查询只会执行一次。2比1好,因为where会先过滤数据行,然后分组,然后对分组过滤。
    2019-07-01
    1
    11
  • 悟空
    # 使用exists
    SELECT player_name,player_id,team_id from player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id and score >20)

    # 使用 in
    SELECT player_name,player_id,team_id from player WHERE player_id IN (SELECT player_id FROM player_score WHERE score >20)

    使用:join 语句
    SELECT p.player_name,p.player_id,p.team_id,t.score from player AS p JOIN player_score as t WHERE p.player_id = t.player_id and t.score >20
    2019-07-01
    8
  • Serendipity
    我怎么感觉简单的地方讲的比较多,难的地方一笔带过了?看的好懵逼。
    2019-09-01
    1
    5
  • 华夏
    SELECT player_id, team_id, player_name FROM player WHERE player_id in (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id);
    老师,文稿中这句的WHERE player.player_id = player_score.player_id可以不要了哈。
    2019-07-01
    5
  • Liam
    老师您好,关于exist和in的差别,总结和举例的不符吧?主表大于从表不应该是使用in更好吗
    2019-07-01
    3
    5
  • Mr.H
    最后的总结in和exist写反了吧
    2019-07-01
    5
  • 小星星
    SELECT player_name, height, team_id FROM player AS a
    WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id);

    SELECT player_name, height, team_id FROM player WHERE height > (SELECT avg(height) FROM player as b);
    这两句执行的结果一样的;请问为什么需要加上a.team_id = b.team_id 有什么区别吗?
    2019-07-09
    1
    4
  • Geek_669849
    SELECT
    t1.player_id,
    t1.player_name,
    t1.team_id
    FROM
    player t1
    WHERE
    t1.player_id IN (
    SELECT
    t2.player_id
    FROM
    player_score t2
    GROUP BY
    t2.player_id
    HAVING
    ( AVG( t2.score ) > 20 )
    )

    作者回复: 正确,而且SQL代码结构清晰

    2019-07-07
    4
  • Geek_669849
    实际工作中没用到果any all。什么样的情况下会可能用到呢?老师举的例子的话,直接比较最大值或者最小值就好了吧
    2019-07-07
    4
  • 华夏
    SELECT player_id, player_name, team_id FROM player AS a WHERE (SELECT score FROM player_score AS b WHERE a.player_id = b.player_id) > 20;
    +-----------+------------------+---------+
    | player_id | player_name | team_id |
    +-----------+------------------+---------+
    | 10001 | 韦恩-艾灵顿 | 1001 |
    | 10002 | 雷吉-杰克逊 | 1001 |
    +-----------+------------------+---------+
    2 rows in set (0.01 sec)
    2019-07-01
    3
  • hsj
    SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
    SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)
    A 表有 n 条数据,B 表有 m 条数据
    两条语句的执行效率(假设都用 btree 索引)
    用 in :m * log (n)
    用 exists: n * log (m)
    所以小表驱动大表能够大幅度提高执行速度
    2019-09-29
    2
  • 峻铭
    前提条件:
    1、我理解的场均得分大于20,第一场得了40分,第二场得了2分,场均为21分满足条件
    2、一场比赛中,球员可以出现多次
    解析思路,先得出球员在每场比赛中的总分数,然后对球员分组,计算球员在所参加比赛中的平均数
    SELECT
    t2.player_id,
    t2.player_name,
    t2.team_id,
    t3.v
    FROM
    player AS t2
    JOIN (
    SELECT
    t1.player_id,
    avg( t1.total ) AS v
    FROM
    (
    SELECT
    player_id,
    sum( score ) AS total
    FROM
    player_score
    WHERE
    game_id IN ( SELECT game_id FROM player_score GROUP BY game_id )
    GROUP BY
    player_id #对球员分组,计算在每场比赛中的总分数
    ) AS t1
    GROUP BY
    t1.player_id #对球员分组,计算所参加比赛的平均数
    HAVING
    v > 20 #场均得分大于20
    ) AS t3 ON t2.player_id = t3.player_id;

    +-----------+------------------+---------+---------+
    | player_id | player_name | team_id | v |
    +-----------+------------------+---------+---------+
    | 10001 | 韦恩-艾灵顿 | 1001 | 26.0000 |
    | 10002 | 雷吉-杰克逊 | 1001 | 22.0000 |
    +-----------+------------------+---------+---------+

    2019-09-03
    1
    2
  • 苏籍
    WHERE a.team_id = b.team_id
    想确定一下这个条件 是在两个相同的表做笛卡尔积后 做了啥操作,能帮梳理一下思路吗
    2019-07-02
    1
    2
  • Fred
    SELECT player_id , player_name, team_id FROM player AS a WHERE a.player_id IN (SELECT player_id FROM play_score AS b WHERE a.player_id=b.player_id AND AVG(b.score)>20)
    2019-07-01
    2
  • 太精
    SELECT player_id, player_name, team_id FROM player WHERE player_id IN (SELECT player_id FROM player_score WHERE score > 20);
    +-----------+------------------+---------+
    | player_id | player_name | team_id |
    +-----------+------------------+---------+
    | 10001 | 韦恩-艾灵顿 | 1001 |
    | 10002 | 雷吉-杰克逊 | 1001 |
    +-----------+------------------+---------+
    2019-07-01
    2
  • Cary
    in 和 exist 上面和最后总结的好像不一致
    2019-07-01
    2
  • 番茄
    第二个例子,照这意思,对比的是整体平均身高,非关联不是就能出来了吗,为什么要用a.team_id=b.team_id

    select player_name,team_id,height
    from `player`
    where height > (select avg(height) from `player`)
    2019-09-05
    2
    1
收起评论
99+
返回
顶部