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必知必会
登录|注册

05丨检索数据:你还在SELECT * 么?

陈旸 2019-06-21
今天我们主要学习如何使用 SQL 检索数据。如果你已经有了一定的 SQL 基础,这节课可以跳过,也可以把它当做是个快速的复习。
SELECT 可以说是 SQL 中最常用的语句了。你可以把 SQL 语句看作是英语语句,SELECT 就是 SQL 中的关键字之一,除了 SELECT 之外,还有 INSERT、DELETE、UPDATE 等关键字,这些关键字是 SQL 的保留字,这样可以很方便地帮助我们分析理解 SQL 语句。我们在定义数据库表名、字段名和变量名时,要尽量避免使用这些保留字。
SELECT 的作用是从一个表或多个表中检索出想要的数据行。今天我主要讲解 SELECT 的基础查询,后面我会讲解如何通过多个表的连接操作进行复杂的查询。
在这篇文章中,你需要重点掌握以下几方面的内容:
SELECT 查询的基础语法;
如何排序检索数据;
什么情况下用SELECT*,如何提升 SELECT 查询效率?

SELECT 查询的基础语法

SELECT 可以帮助我们从一个表或多个表中进行数据查询。我们知道一个数据表是由列(字段名)和行(数据行)组成的,我们要返回满足条件的数据行,就需要在 SELECT 后面加上我们想要查询的列名,可以是一列,也可以是多个列。如果你不知道所有列名都有什么,也可以检索所有列。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《SQL必知必会》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(103)

  • 君莫惜 置顶
    SELECT COUNT(*) > SELECT COUNT(1) > SELECT COUNT(具体字段)

    之前看到的,好像Mysql对count(*)做了单独的优化

    作者回复: 关于COUNT()的效率是一个很好的问题,欢迎探讨:
    在MySQL InnoDB存储引擎中,COUNT(*)和COUNT(1)都是对的所有结果进行的COUNT。如果有WHERE子句,则是对所有符合筛选条件的数据行进行统计。如果没有WHERE子句,则是对数据表的数据行数进行统计。
    因此COUNT(*)和COUNT(1)本质上没有区别,执行的复杂度都是O(N),也就是采用全表扫描,进行循环+计数的方式进行统计。
    如果是MySQL MyISAM存储引擎,统计数据表的行数只需要O(1)复杂度,这是因为每张MyISAM的数据表都有一个meta信息有存储了row_count值。而一致性由表级锁来保证。而InnoDB支持事务,采用行级锁和MVCC机制,所以无法像MyISAM一样,只维护一个row_count变量。因此就需要采用扫描全表,进行循环+计数的方式来完成统计。
    需要注意的是,在实际执行中COUNT(*)和COUNT(1)执行时间可能略有差别,不过你还是可以把这两个在执行效率上看成是相等的。

    另外在InnoDB引擎中,如果是采用COUNT(*)和COUNT(1)来统计数据行数,要尽量采用二级索引。
    因为主键采用的索引是聚簇索引,聚簇索引包含的信息多,明显会大于二级索引(非聚簇索引)。
    对于查找具体的行来说,采用主键索引效率更高。而对于COUNT(*)和COUNT(1)这种,不需要查找具体的行,只是统计行数来说,系统会自动采用占用空间更小的二级索引来进行统计。
    如果有多个二级索引的时候,会使用key_len小的二级索引进行扫描。当没有二级索引的时候,才会采用主键索引来进行统计。

    优化总结:
    1、一般情况下:COUNT(*) = COUNT(1) > COUNT(字段)
    所以尽量使用COUNT(*),当然如果你要统计的是就是某个字段的非空数据行数,那另当别论。毕竟执行效率比较的前提是要结果一样才行。
    2、如果要统计COUNT(*),尽量在数据表上建立二级索引,系统会自动采用key_len小的二级索引进行扫描,这样当我们使用SELECT COUNT(*)的时候效率就会提升,有时候提升几倍甚至更高都是有可能的。

    2019-06-22
    2
    68
  • C先生丶陈 置顶
    做一个搬运工,下面是从老师GitHub上找到的建表语句:
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;

    -- ----------------------------
    -- Table structure for heros
    -- ----------------------------
    DROP TABLE IF EXISTS `heros`;
    CREATE TABLE `heros` (
      `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      `hp_max` float NULL DEFAULT NULL,
      `hp_growth` float NULL DEFAULT NULL,
      `hp_start` float NULL DEFAULT NULL,
      `mp_max` float NULL DEFAULT NULL,
      `mp_growth` float NULL DEFAULT NULL,
      `mp_start` float NULL DEFAULT NULL,
      `attack_max` float NULL DEFAULT NULL,
      `attack_growth` float NULL DEFAULT NULL,
      `attack_start` float NULL DEFAULT NULL,
      `defense_max` float NULL DEFAULT NULL,
      `defense_growth` float NULL DEFAULT NULL,
      `defense_start` float NULL DEFAULT NULL,
      `hp_5s_max` float NULL DEFAULT NULL,
      `hp_5s_growth` float NULL DEFAULT NULL,
      `hp_5s_start` float NULL DEFAULT NULL,
      `mp_5s_max` float NULL DEFAULT NULL,
      `mp_5s_growth` float NULL DEFAULT NULL,
      `mp_5s_start` float NULL DEFAULT NULL,
      `attack_speed_max` float NULL DEFAULT NULL,
      `attack_range` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `role_main` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `role_assist` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `birthdate` datetime(0) NULL DEFAULT NULL,
      PRIMARY KEY (`name`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    2019-06-22
    3
    7
  • Shame
    先交作业 select name,mp_max from heros order by hp_max desc limit 5;
     然后就是楼下一个同学问的问题,我也有些疑惑,就是这个
    SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
    FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
    WHERE height > 1.80 # 顺序 2
    GROUP BY player.team_id # 顺序 3
    HAVING num > 2 # 顺序 4
    ORDER BY num DESC # 顺序 6
    LIMIT 2 # 顺序 7

    对于这个语句,我还有一点疑问:既然HAVING的执行是在SELECT之前的,那么按理说在执行HAVING的时候SELECT中的count(*)应该还没有被计算出来才对啊,为什么在HAVING中就直接使用了num>2这个条件呢?
    希望老师百忙之中能抽空帮忙解释一下,谢谢老师

    作者回复: 很好的问题,实际上在Step4和Step5之间,还有个聚集函数的计算。
    如果加上这个计算过程,完整的顺序是:
    1、FROM子句组装数据
    2、WHERE子句进行条件筛选
    3、GROUP BY分组
    4、使用聚集函数进行计算;
    5、HAVING筛选分组;
    6、计算所有的表达式;
    7、SELECT 的字段;
    8、ORDER BY排序
    9、LIMIT筛选
    所以中间有两个过程是需要计算的:聚集函数 和 表达式。其余是关键字的执行顺序,如文章所示。

    2019-06-27
    30
  • ack
    老师好,请问能把建表的sql给出来吗?
    2019-06-21
    5
    13
  • Samson
    老师,可以说下SELECT语句执行原理那个视例中HAVING关键字的作用嘛?

    作者回复: HAVING一般配合GROUP BY使用,作为筛选分组的条件。作用实际上和WHERE一样,都适用于限定条件。只是WHERE子句用于对查询结果的分组前,通过WHERE来过滤。而HAVING子句用于筛选满足条件的组,用于在分组之后进行过滤。这个我在后面也会讲到。

    2019-06-24
    8
  • hogen
    -- 注释版本
    CREATE TABLE `heros` (
      `id` SMALLINT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
      `name` VARCHAR(255) DEFAULT NULL COMMENT '英雄名称',
      `hp_max` FLOAT NULL DEFAULT NULL COMMENT '最大生命',
      `hp_growth` FLOAT NULL DEFAULT NULL COMMENT '生命成长',
      `hp_start` FLOAT NULL DEFAULT NULL COMMENT '初始生命',
      `mp_max` FLOAT NULL DEFAULT NULL COMMENT '最大法力',
      `mp_growth` FLOAT NULL DEFAULT NULL COMMENT '法力成长',
      `mp_start` FLOAT NULL DEFAULT NULL COMMENT '初始法力',
      `attack_max` FLOAT NULL DEFAULT NULL COMMENT '最高物攻',
      `attack_growth` FLOAT NULL DEFAULT NULL COMMENT '物攻成长',
      `attack_start` FLOAT NULL DEFAULT NULL COMMENT '初始物攻',
      `defense_max` FLOAT NULL DEFAULT NULL COMMENT '最大物防',
      `defense_growth` FLOAT NULL DEFAULT NULL COMMENT '物防成长',
      `defense_start` FLOAT NULL DEFAULT NULL COMMENT '初始物防',
      `hp_5s_max` FLOAT NULL DEFAULT NULL COMMENT '最大每5秒回血',
      `hp_5s_growth` FLOAT NULL DEFAULT NULL COMMENT '每5秒回血成长',
      `hp_5s_start` FLOAT NULL DEFAULT NULL COMMENT '初始每5秒回血',
      `mp_5s_max` FLOAT NULL DEFAULT NULL COMMENT '最大每5秒回蓝',
      `mp_5s_growth` FLOAT NULL DEFAULT NULL COMMENT '每5秒回蓝成长',
      `mp_5s_start` FLOAT NULL DEFAULT NULL COMMENT '初始每5秒回蓝',
      `attack_speed_max` FLOAT NULL DEFAULT NULL COMMENT '最大攻速',
      `attack_range` VARCHAR(255) COMMENT '攻击范围',
      `role_main` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '主要定位',
      `role_assist` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '次要定位',
      `birthdate` DATETIME(0) NULL DEFAULT NULL COMMENT '上线时间'
    ) ENGINE = INNODB DEFAULT CHARSET=UTF8 COMMENT='王者荣耀数据表';
    2019-06-22
    2
    7
  • 啦啦啦
    select id from table where id=5
    执行这条语句时,mysql会判断是否有缓存,有的话直接返回,如果没有则调用引擎接口查询数据,引擎比如说innodb会判断这条数据是否在内存中存在,如果存在直接返回给mysql的server层,如果不存在则会到磁盘里把id=5的这条数据所在的整个数据页都读取到内存中,这个内存就是innodb buffer pool,是一块固定大小的地方,可以由参数innodb_buffer_pool_size设置大小,既然是一块固定大小的地方,那么就要有淘汰机制,mysql是在lru算法的基础上做了优化,简单的来说就是最久未被使用的数据会被淘汰掉
    2019-06-21
    7
  • 时间是最真的答案
    MySQL
    SELECT `name`,mp_max FROM heros ORDER BY hp_max DESC LIMIT 5

    作者回复: 正确,同时也考虑到了给查询字段使用反引号

    2019-06-24
    4
  • 业余草
    create table 还没学吧,我是小白,教一下 create table 或者 create table like。就单拿 select 说,这章内容也不全啊,group,having 等都漏掉了
    2019-06-21
    1
    4
  • Samson
    SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
    FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
    WHERE height > 1.80 # 顺序 2
    GROUP BY player.team_id # 顺序 3
    HAVING num > 2 # 顺序 4
    ORDER BY num DESC # 顺序 6
    LIMIT 2 # 顺序 7

    对于这个语句,我还有一点疑问:既然HAVING的执行是在SELECT之前的,那么按理说在执行HAVING的时候SELECT中的count(*)应该还没有被计算出来才对啊,为什么在HAVING中就直接使用了num>2这个条件呢?
    2019-06-24
    1
    3
  • hlz-123
    数据库,MySQL8.0
    SELECT name as '姓名',mp_max as '最大法力' FROM heros ORDER BY hp_max LIMIT 5;
    2019-06-23
    3
  • mickey
    1.SELECT就是告诉数据库要选什么字段
    2.
      1)MySQL、PostgreSQL、MariaDB和SQLite:
    SELECT name, mp_max FROM heros ORDER BY hp_max DESC limit 5;
      2)SQL Server和Access:
    SELECT TOP 5 name, mp_max FROM heros ORDER BY hp_max DESC
      3)DB2:
    SELECT name, mp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY;
      4)Oracle:
    SELECT name, mp_max FROM (SELECT name, mp_max FROM heros ORDER BY hp_max) as t WHERE ROWNUM <=5
    2019-06-21
    3
  • 石维康
    作业: SELECT name, mp_max FROM heros ORDER BY hp_max DESC LIMIT 5;
    MySQL数据库
    2019-06-21
    1
    3
  • lincan
    老师讲得很棒,但有一处困惑:limit是最后执行的话,执行limit时全表扫描和所有的虚拟表都已生成了,那使用limit为什么还能提高效率呢?

    作者回复: 你可以通过 SHOW PROFILE 来查看 SQL 的具体执行成本,如果我们使用LIMIT进行限制,至少可以减少数据传输量,这样在Sending data这项上可以减少大量传输时间,而这一项又在整个SQL执行成本中占比比较高。

    2019-09-01
    2
    2
  • 峻铭
    从文中顺序6和顺序7描述的order by和limit的关系 与 “最后在 vt6 的基础上,取出指定行的记录,也就是 LIMIT 阶段,得到最终的结果” 的描述,说明limit是在排序完成后才执行的,那么理论上对数据排序后用的时间和加上limit 1的时间应该是几乎相等的。于是做了个小实验验证下:
    增加数据量让时间差异更明显,对5个表做了笛卡尔积
    select * from player join team join player_score join team_score join height_grades order by player.height; #用时0.123秒
    select * from player join team join player_score join team_score join height_grades order by player.height limit 1; #用时0.027秒
    说明limit不是在order by完成后执行的,应该是参与了边排序的过程中边判断是否达到了limit条件,具体过程我也不清楚 :)
    2019-08-31
    2
  • 极客时间
    老师 我有一个疑问 场景是这样的,我有三张表,一张表存储文章posts,一张表存储文章标签tags,

    一篇文章可以有多个标签,一个标签可以被多个文章拥有,文章和标签是多对多的关系,

    此时我又增加了一个关系表post_tag,这个表只有两个字段,post_id和tag_id.

    我现在有一个需求 查询出所有文章,查询出的文章数据中每篇文章都有一个tags属性,这个属性包含所有这篇文章的标签信息,这个查询应该怎么做呢? 或者说sql只能做一部分,然后在通过其他脚本语言再处理呢?

    我将问题发在了 segmentfaul 链接地址 https://segmentfault.com/q/1010000019472412
    2019-06-26
    2
    2
  • 马哲富
    讲得非常好,就这一篇文章就值专栏的价格了!另外老师能讲解一下where语句的过滤顺序吗,能理解where语句的执行顺序对sql的优化应该也会有很大的帮助,谢谢老师的解答!
    2019-06-24
    2
  • 👽
    再有一个建议,能不能在课中嵌套一些常见的面试题,例如:
    索引的作用与弊端是什么?
    2019-06-21
    2
  • crazypokerk
    SELECT是所有查询关键字最后一个执行的关键字。
    2019-06-21
    2
  • 月牙天冲
    1.作业:SELECT name,mp_max FROM heros ORDER BY hp_max DECS LIMIT 5;
    2.老师能提供下创建表的sql语句么,谢谢。
    2019-06-21
    3
    2
收起评论
99+
返回
顶部