SQL自学三部曲_Part2：十大必学语法（一）

---

前言

本文主要介绍SQL基础语法和运行原理，但是因为内容较多，阅读起来时间较长，所以分成两篇文章

---

一、看前须知

• 本文主要适用于MySQL数据库，不过核心语句也通用于其他数据库

• 只学习SQL语句的查询语句部分，对于数据分析师而言完全足够（数据库就像一个图书馆，里面放了很多表；开发相当于图书管理员，会去管理图书馆；分析师只是去图书管里面选择数据进行使用，只需要知道数在哪并把它拿出来就可以了）

• 主要使用资源：基础语法部分基于公开网站sqlzoo的MySQL数据库引擎，必须使用英文界面练习代码（不同语言界面不同引擎可能导致数据库数据不同），并切换至Mysql引擎；小进阶部分基于自行搭建的云端数据库和外卖场景数据

• SQL查询语句语法结构和运行顺序

  • 语法结构：select–from–where–group by–having–order by–limit
  • 运行顺序：from–where–group by–having–order by–limit–select
    

• SQL语句本身并不难，更难的是理清实际需求中的逻辑转换为SQL代码和每个表格所呈现的业务信息及多个表之间的不同连接逻辑及应用

• 本文是为了帮助快速入门SQL，理解SQL主要语句、语法和使用方法，可以快速解决80%以上场景的SQL问题，但仍然需要在实战中继续练习，文章的最后也提供了对应的提升路径

二、sqlzoo数据介绍

1. world

• 数据库引擎-MySQL
• 一张包含每个国家信息（大洲、面积、人口、gdp、国家首都等）的表
• 每一行数据记录了一个国家的相关信息
• 数据部分示例
  
• 字段名-译名-解释
  • name-国家名
  • continent-大洲-共有七大洲分别为：亚洲(Asia)、欧洲(Europe)、北美洲(North America)、南美洲(South America)、非洲(Africa)、大洋洲(Oceania)与南极洲(Antarctica)
  • area-面积-国家面积
  • population-人口
  • gdp-gdp-Gross Domestic Product(GDP，国内生产总值)，一个国家（或地区）所有常住单位在一定时期内生产活动的最终成果，是国民经济核算的核心指标
  • capital-首都-国家首都
  • tld-顶级域名-国家顶级域名，中国顶级域名为.cn
  • flag-旗帜
• 数据库表信息
  • 列数：8列
  • 行数：195行
  • 字段类型
    
    
    

2. nobel

• 数据库引擎-MySQL
• 一张诺贝尔奖获得者的名单表
• 每一行数据记录了一名诺贝尔奖获得者及这个诺贝尔奖所属科目和年份
• 数据部分示例
  
• 字段名-译名-解释
  • yr-年份-获奖年份
  • subject-科目-获得的诺贝尔奖所属科目
  • winner-获奖者-诺贝尔奖获得者的姓名
• 数据库表信息
  • 列数：3列
  • 行数：895行
  • 字段类型
    
    
    

3. covid

• 数据库引擎-MySQL
• 一张记录了2020年1月22号到10月9号各国每天新冠相关信息的表
• 每一行数据记录了一个国家到截至时间的累计确诊人数、累计死亡人数、累计治愈人数
• 数据部分示例
  
• 字段名-译名-解释
  • name-国家名
  • whn-截至时间
  • confirmed-累计确诊-截至该时间的累计确诊人数
  • deaths-累计死亡-截至该时间的累计死亡人数
  • recovered-累计治愈-截至该时间的累计治愈人数
• 数据库表信息
  • 列数：5列
  • 行数：47946行
  • 字段类型
    
    
    

4. ge

• 数据库引擎-MySQL
• 一张记录了2015、2017、2019年的竞选的候选人信息表
• 每一行数据记录了当年竞选候选人的名字、姓名、所在选区编号、所属团体和得票数
• 数据部分示例
  - 字段名-译名-解释
  • yr-年份-举办的年份
  • firstName-名字-候选人名字
  • lastName-姓氏-候选人姓氏
  • constituency-选区-所在选区编号
  • party-团体-所属团体
  • votes-得票数-获得选票的数量
• 数据库表信息
  • 列数：6列
  • 行数：10595行
  • 字段类型
    
    
    

5. game、goal、eteam

• 数据库引擎-MySQL
• game
  • 一张记录赛事的表
  • 每一条数据记录一场赛事的编号（id），赛事的举办时间（mdate），赛事举办的场地（stadium），参加本场比赛的两个队伍（team1，team2）
    
    
• goal
  • 一张记录球员进球得分的表
  • 每一条数据记录一名进球的球员的姓名（player），进球时间（gtime），所在队伍编号（teamid），当时的赛事编号（matchid）
    
    
• eteam
  • 一张队伍表
  • 每一条数据记录一个队伍的编号（id）、名字（teamname）和教练姓名（coach）
    
    
• 数据部分示例
  • game
    
  • goal
    
  • eteam
    
    
    
• 字段名-译名-解释
  • game
    • id-编号-赛事编号
    • mdate-日期-举办赛事的日期
    • stadium-场馆-赛事场馆
    • team1-队伍1-参与赛事队伍1
    • team2-队伍2-参与赛事队伍2
  • goal
    • matchid-赛事编号
    • teamid-队伍编号-入球球员所在的队伍编号
    • player-入球球员
    • gtime-入球时间-比赛开始到入球时间的分钟数
  • eteam
    • id-编号-队伍编号
    • teamname-队名-队伍名字
    • coach-教练-队伍的教练
      
      
• 数据库表信息
  • game
    • 列数：5行
    • 行数：31行
    • 字段类型
      
  • goal
    • 列数：4列
    • 行数：76行
    • 字段类型
      
  • eteam
    • 列数：3列
    • 行数：16行
    • 字段类型
      

6. teacher、dept

• 数据库引擎-MySQL
  • teacher
    • 一张记录教师信息的表
    • 每一条数据记录一名教师的编号、教授科目编号、教师名、教师电话号码和教师手机号
      
      
  • dept
    • 一张记录科目信息的表
    • 每一条数据记录一个科目名及其对应的编号
      
      
• 数据部分示例
  • teacher
    
  • dept
    
    
    
• 字段名-译名-解释
  • teacher
    • id-编号-教师编号
    • dept-科目编号
    • name-名字-教师名字
    • phone-电话号码-教师的电话号码
    • mobile-手机号-教师的手机号
  • dept
    • id-科目编号
    • name-科目名
      
      
• 数据库表信息
  • teacher
    • 列数：5列
    • 行数：6行
    • 字段类型
      
  • dept
    • 列数：2列
    • 行数：3行
    • 字段类型
      
      
      

7. movie、actor、casting

• 数据库引擎-MySQL

  • movie
    • 一张记录电影相关信息的表
    • 每一行数据记录了一部电影的id、电影名称（title）、首影年份（yr）、导演（director）、制作费（budget）、票房收入（gross）
      
      
  • actor
    • 一张记录了演员信息的表
    • 每一行数据记录了一名演员的id号和姓名
      
      
  • casting
  • 一张记录每部电影中参演演员的角色次序的表，也是一张中间表用来连接movie和actor表
    • 每一行数据记录了一部电影中的一个演员及其所对应的角色次序
      
      

• 数据部分示例

  • movie
    
  • casting
    
  • actor
    
    
    

• 字段名-译名-解释

  • movie
    • id-编号-电影编号
    • title-电影名称
    • yr-首影年份-电影第一次上映年份
    • director-导演
    • budget-制作费
    • gross-票房收入
      
      
  • casting
    • movieid-电影编号
    • actorid-演员编号
    • ord-角色次序-角色次序代表第1主角是1, 第2主角是2…如此类推
      
      
  • actor
    • id-演员编号
    • name-演员姓名
      
      

• 数据库表信息

  • movie
    • 列数：6列
    • 行数：12026行
    • 字段类型
      
  • casting
    • 列数：3列
    • 行数：120172行
    • 字段类型
      
  • actor
    • 列数：2列
    • 行数：48148行
    • 字段类型
      
      
      

三、基础语法和运行原理

1. select&from

（1）知识点引入

• 标准语法
  • select 字段名
  • from 表名
    
    
• 语法解释
  • select 字段名：决定这一段查询最后展示的字段
  • from 表名：指定这段查询语句涉及的数据来源
    
    
• 这是一段查询语句中必不可少的两个核心语句，select和from分别是两个核心语句中的关键字
  
  

（2）例题讲解

例题链接：（链接中标号1）https://sqlzoo.net/wiki/SELECT_from_WORLD_Tutorial 涉及数据表：world

a. 基础查询select单列&多列&所有列&别名应用

【例题1】基础查询select单&多列

• 运行代码
  

SELECT name, continent, population FROM world

• 运行结果
  
• 知识点讲解
  • 1.该题为从单表world中查询多列，在select后指定要查询的字段名称，多个字段名之间用英文逗号(,)隔开，最后一个字段名后不需要加逗号
  • 2.select和from关键字后不要忘记添加空格
  • 3.查询结果的字段顺序，按照select后的字段名顺序显示
  • 4.SQL语句不区分大小写，select和SELECT完全相同，但是实际场景中没空大小写切换，部分数据库管理工具会对select、from这类关键字做标色高亮处理，所以这里推荐选择全部小写，后面本文提供的SQL代码都为小写
  • 5.一段标准的查询语句的最后应当添加英文分号(;)向数据库声明这一段查询语句已结束，但是现在的数据库管理工具（向数据库传递SQL代码的软件）比较智能，可以不写分号依然能正常运行代码，甚至同时传递多段代码依次输出多个查询结果（该功能取决于使用的数据库管理工具是否支持）
  • 6.查询单列，在select后指定要查询的那一个字段名称即可，例如select name from world，建议自行尝试，这里不再整述
    
    

【例题2】基础查询select所有列

• 运行代码
  

select * from world

• 运行结果
  
• 知识点讲解
  • 1.该题为从单表world中查询所有列，select和星号(*)通配符联用返回查询表中所有的列
  • 2.返回的所有列的显示顺序按照定义表时的顺序显示
    
    

【例题3】基础查询select多列&别名应用

• 运行代码
  

select name as 国家名,continent 大洲,population 人口 
from world

• 运行结果
  
• 知识点讲解
  • 1.select核心语句中，在字段名后加as别名，可以给字段名在最后显示前赋予别名
  • 2.注意as前后加空格
  • 3.这个别名不会修改该字段在数据库表中的原名，仅影响最后的显示
  • 4.as可以省略，写成字段名别名，注意字段名和别名之间有空格，别名和下一个字段名之间为逗号
    
    

b. select中使用distinct去重

【例题4】列出world表中所有大洲（continent），且仅出现一次

• 运行代码
  

select distinct continent from world

• 运行结果
  
  
  

【例题5】尝试运行以下两段代码

• 运行代码1
  

select distinct name,continent from world

• 运行结果1
  
  
  
• 运行代码2
  

select name,distinct continent name from world

• 运行结果2
  
  翻译：您的SQL语法有错误；请查看与您的服务器版本对应的手册，以了解在第1行“distinct continental name from world”附近使用的正确语法
  
  

• 知识点讲解

  • 1.在select核心语句中加distinct关键字，可以对最后显示的数据去重。
  • 2.在select distinct后加多个字段时，是对重复的行数据进行去重。
  • 3.将distinct放在第二个字段时，出现了语法错误，因为显示多个字段时distinct无法对单一字段去重，只能对这几个字段组成的行中重复的行数据去重，所以distinct本质是加在select后，而不是字段前。

c. select中计算字段的运用

【例题6】查询国家名、gdp、人口及其人均gdp

• 运行代码
  

select name,gdp,population,gdp/population 人均gdp 
from world

• 运行结果
  
• 知识点讲解
  • 1.在select的核心语句中，可以对数据库表中有的字段进行计算形成新的字段
  • 2.每行的人均gdp是由当前行的gdp除以当前行的人口得到的，因此字段计算得到的每行数据仅涉及当前行，不会行与行之间交叉计算
  • 3.同理，加减乘除等数学运算都可以进行，当然，字段值必须是数值才可以，可以自己尝试一下各种计算方法
  • 4.此外，也可以便用函数对字段进行处理，后续有专门的部分介绍函数
    
    

（3）总结

• 基础语法
  • select 字段名
  • from 表名
    
    
• 别名语法
  • select 字段名 as 别名
  • from 表名
  • 注意：as可以省略
    
    
• 查询多列
  • select 字段名1,字段名2,字段名3
  • from 表名
    
    
• 查询所有列
  • select *
  • from 表名
    
    
• 数据去重
  • select distinct 字段名
  • from 表名
    
    
• select中的计算字段
  • select 字段名,计算字段
  • from 表名称
  • 注意：计算字段中的算式所涉及的字段必须是表格中包含的，或者算式本身可以独立运算
    
    

（4）练习题

• 自己完整敲一遍知识点中出现的代码，也可以直接在sqlzoo中写代码尝试自己的想法

2. where

（1）知识点引入

• 标准语法

  • select 字段名
  • from 表名
  • [where 表达式]
    
    

• 语法解释

  • where表达式：限定查询行必须满足的条件
  • where核心子句：是可选项，使用该子句是为了通过表达式筛选出符合查询条件的行数据
    
    

（2）例题讲解

例题链接：（链接中标号3）https://sqlzoo.net/wiki/SELECT_from_WORLD_Tutorial 涉及数据表：world

a. 运算符

• where 表达式中运算符标准语法
  • select 字段名
  • from 表名
  • where 字段名 运算符 值
• 运算符一览
  
  • =、>、<、>=、<=、<>、!=为比较运算符，用于判断表中的哪些数据符合条件
• and、or和not为逻辑运算符
  • and ：一般用于组合两个及以上表达式，要求同时满足多个查询条件
  • or：一般用于组合两个及以上表达式，要求满足多个查询条件中的一个
  • not：not一般和与其他连用例如not in，用于条件取反
  • and和or可以一起使用，但是默认and优先级高于or，可以使用括号来界定优先级
• between and：用于查询两个值之间范围的值（包含这两个值）
• in： 用于查询指定条件范围内的数据，一般为in (xxx,xxx,…)，用括号将条件括起来
• is null：用于查询空值（NULL），空值不同于0，也不同于null字符串
  • 空值（is null）
    
  • null 字符串（=‘null’）
    
    
    

【例题7】查询人口数至少2亿的国家名和人均gdp

• 运行代码
  

select name,gdp/population 人均gdp
from world
where population>=200000000

• 运行结果
  
  
  

【例题8】查询德国（Germany）的人口

• 运行代码
  

select population
from world
where name = 'Germany'

• 运行结果
  
• 知识点讲解
  • 1.找寻字段值等于某个值时使用等于号(=)，这里的值不是一个数字，而是交本，文本需要用英文单引号(”)包裹，数字不需要
  • 2.该题是筛选name字段中值于Germany的行数据，最后按照select显示行数据的population字段
    
    

【例题9】查询瑞典(Sweden)、挪威 (Norway) 和丹麦 (Denmark)的国家名和人口

• 运行代码
  

selectname,population
from world
where name in ('Sweden', 'Norway', 'Denmark')

• 运行结果
  
• 知识点讲解
  • 1.in会筛选出字段值中所有与括号内数据相等的行
  • 2.在这题中in筛选的是名为name的这列字段中值等于Sweden或Norway或Denmark的行
    
    

【例题10】查询面积(area)在250000和300000之间的国家名和面积

• 运行代码
  

selectname,area
from world
where area between 250000 and 300000

• 运行结果
  
• 知识点讲解
  • 1.betweenand主要用于选取介于两个值之间的数据，这些值主要是数值和日期
  • 2.between and 包含了这两个值
  • 3.and前必须写两个数值中较小的那个，后必须写较大的那个，不然会出现无法显示数的情况，可以尝试将代码中的where子句看换成where area between 300000 and 250000
    • 结果显示无数据
      
      
      

b. 模糊查询like

• where子句的表达式中：除了使用运算符来进行条件判断，还可以使用like操作符组合通配符进行模糊查询
  
  
• 模糊查询标准语法
  • select 字段名
  • from 表名
  • where 字段名 like ‘通配符+字符’
    
    
• 通配符：用来匹配值的一部分，跟在like后面进行数据过滤常用的通配符有%和_；%用来匹配多个字符可以是零个、一个也可以是多个字符；_仅能用来匹配单个字符
  

【例题11】查询国家名中以C开头ia结尾的国家

• 运行代码
  

select name
from world
where name like 'C%ia'

• 运行结果
  
  
  

【例题12】查询国家名中第二个字符为’t’的国家

• 运行代码
  

select name
from world
where name like '_t%'

• 运行结果
  
  
  

【例题13】查询国家名中含有两个o且被两个字符隔开的国家名

• 运行代码
  

select name
from world
where name like '%o__o%'

• 运行结果
  
• 知识点讲解
  • 1.指定几个字符时就便用几个通配符_来代替要求的字符数，没有指定字符数时便用通配符%
  • 2.like后的字符和通配符的组合表达式需要用英文单引号(‘’)包裹

c. 多条件查询

使用and或者or逻辑运算符对多个条件进行组合筛选想要的数据

【例题14】查询国家名中含有三个a且面积大于60万(600000)的国家及其面积

• 运行代码
  

select
name
,area
from world
where name like '%a%a%a%'
and area >= 600000

• 运行结果
  
  
  

【例题15】查询国家名中含有三个a且面积大于60万(600000)的国家及其面积，或者人口大于13亿(1300000000)且面积大于500万(5000000)的国家及其面积

• 运行代码
  

selectname,area
from world
where name like '%a%a%a%' and area >= 600000
or population > 1300000000 and area >= 5000000

• 运行结果
  
  相比上一题的8行的查询结果多了一行china的数据
  
  

• 知识点讲解

  • 1.and的逻辑是同时满足，or的逻辑是满足其中一个条件即可
  • 2.and的运行优先级高于or，因此例题9中先运行name like ‘%a%a%a%’ and area >= 600000和population > 1300000000 and area >= 5000000，再运行条件or条件
  • 3.会有需要先运行or再运行and的条件需求，此时使用括号()来标记优先运行的部分，同时在and和or联用时最好使用括号来标记优先运行的部分，便于阅读代码，也避免条件逻辑出错，因此优化例题15的代码如下:

selectname,area
from world
where (name like '%a%a%a%' and area >= 600000)
or (population > 1300000000 and area >= 5000000)

【例题16】将例题9和例题10转换为多条件代码

【例题9】查询瑞典(Sweden)、挪威 (Norway) 和丹麦 (Denmark)的国家名和人口

• 原代码

selectname,population
from world
where name in ('Sweden', 'Norway', 'Denmark')

• 新代码

selectname,population
from world
where (name = 'Sweden' or name = 'Norway' or name ='Denmark')

【例题10】查询面积(area)在250000和300000之间的国家名和面积

• 原代码

selectname,area
from world
where area between 250000 and 300000

• 新代码

selectname,area
from world
where (area >= 250000 and area <= 300000)

• 知识点讲解
  • 1.由上面可知in多条件的逻辑是or，满足其中一个条件即可，适用于对同一个字段的值进行多条件等值判断的情况。
  • 2.由上面可知between and的逐辑是and，适用于包含边界的范围判断。
  • 3.便用between and时如果想要去掉某个边界可以便用英文符号(!=)来去除
    • 使用and来连接两个条件，本质上是通过and要求必须同时满足两个条件来排除between and的某一个边界值，排除两个边界值同理
      
      
      

（3）总结

• 标准语法

  • select 字段名
  • from 表名
  • where 表达式
    
    

• 运算符查询语法

  • select 字段名
  • from 表名
  • where 字段名 运算符 值
    
    

• 模糊查询语法

  • select 字段名
  • from 表名
  • where 字段名 like ‘通配符+字符’
    
    

• 使用多条件查询

  • select 字段名
  • from 表名
  • where 条件代码1 and|or 条件代码2
    
    

• 运算符
  
  
  

• 通配符
  
  
  

（4）练习题

习题链接1：https://sqlzoo.net/wiki/SELECT_from_WORLD_Tutorial（链接中标号4）涉及数据表：world

题目1：查询南美洲（south america）所有国家的名称以及它们以百万（1000000 ）为单位的人口数量

• 运行代码

selectname,population/1000000 population_in_millions
from world
where continent = 'South America';

• 运行结果
  
  
  

习题链接2：https://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial（链接中标号9）涉及数据表：nobel

题目2：查询1980年除诺贝尔化学奖和诺贝尔医学奖外其余奖项获奖者的所有信息

• 运行代码

select * 
from nobel
where yr = 1980
and subject not in ('Chemistry','Medicine')

• 运行结果
  
  
  

习题链接3：https://sqlzoo.net/wiki/SELECT_from_WORLD_Tutorial（链接中标号13）涉及数据表：world

题目3：查询既包含有所有元音字母（a,e,i,o,u），同时国家名中没有空格的国家，最后显示他们的名字

• 运行代码

select name 
from world
where name like '%a%'
and name like '%e%'
and name like '%i%'
and name like '%o%'
and name like '%u%'
and name not like '% %';

• 运行结果
  
  
  

习题链接4：https://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial（链接中标号10）涉及数据表：nobel

题目4：查询1910年以前（不含1910）诺贝尔医学奖获得者和2004年及以后诺贝尔文学奖获得者的所有信息

• 运行代码

select * 
from nobel
where (subject = 'Medicine' and yr < 1910)
or (subject = 'Literature' and yr >= 2004)

• 运行结果
  
  
  

3. order by

（1）知识点引入

• 标准语法
  • select 字段名
  • from 表名
  • [where 表达式]
  • [order by 字段名 asc|desc]
    
    
• 语法解释
  • order by 字段名 asc|desc： 规定查询出的结果集显示的顺序
  • order by核心子句是可选项，使用该子句是为了对被查询出的结果集，指定依据字段排序
  • asc指定该字段升序排序，desc为降序排序，不写则默认为升序排序
    
    

（2）例题讲解

例题链接：（链接中标号13）https://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial 涉及数据表：nobel

【例题17】查询姓名以Sir开头的获奖者(winner)，获奖年份(yr)和科目(subject)，查询结果按照年份从近到远排序，再按照姓名顺序升序排序

• 运行代码
  

selectwinner,yr,subject
from nobel
where winner like 'Sir%'
order by yr desc,winner asc

• 运行结果
  
• 知识点讲解

  • 1.order by关键字后可以加多个字段，按照写的字段顺序，依次作为排序依据；该题中就是先按照yr排序，再按照winner排序，即在对yr排序后，yr字段中有相同值的行，这些行再根据winner排序，如果没有相同的值，则不再依据winner字段排序

  • 对比以下结果

    • 先按照y升序排序，但是yr有不少相同值，在值相同的情况下，这些行按在数据库中存储的顺序排序
      

    • 在按yr升序排序后，再按winner降序排序，此时会对yr相同值的分区内依据winner降序排序
      

  • 2.数值由大到小排序叫降序(desc)，由小到大为升序(asc），字母由Z到A为降序，由A到Z为升序

  • 3.不指定排序方式时默认为asc升序，因此该题代码中的asc可以省略

  • 4.order by子句每个字段都需要指定排序方式，排序方式desc和asc(或者不写)只对其紧邻的前面的一个字段生效

  • 5.order by可以指定不在select子句中的字段作为排序依据
    
    

（3）总结

• 标准语法
  • select 字段名1
  • from 表名
  • [where 表达式]
  • [order by 字段名 asc|desc]
    
    

（4）练习题
习题链接1：https://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial（链接中标号14）涉及数据表：nobel

题目1：查询1984年所有获奖者的姓名和奖项科目。结果将诺贝尔化学奖和物理学奖排在最后，然后按照科目排序，再按照获奖者姓名排序

• 运行代码

selectwinner,subject
from nobel
where yr = 1984
order by subject in ('chemistry','physics') , subject, winner

order by 后面如果加了判断条件，那么将在判断条件内（括号内）的数据记为1，不在记为0，然后按照0,1进行排序，以此实现将特殊值排在后面。

• 运行结果
  
  
  

4. limit

（1）知识点引入

• 标准语法
  • select 字段名
  • from 表名
  • [where 表达式]
  • [order by 字段名 asc|desc]
  • [limit [位置偏移量,]行数]
    
    
• 语法解释
  • limit [位置偏移量,]行数： 限制查询结果集显示的行数
  • limit子句是可选项，行数是子句中的必选参数，参数位置偏移量是可选参数
  • limit n：返回查询结果的前n行
  • limit x,n：意味着从x+1行开始返回n行
    
    

（2）例题讲解

例题链接：（链接中标号1）https://sqlzoo.net/wiki/SELECT_from_WORLD_Tutorial 涉及数据表：world>

【例题18】查询面积排名前三的国家

• 运行代码
  

select name
from world
order by area desc
limit 3

• 运行结果
  
  
  

【例题19】查询人口数第4到第7的国家和人口

• 运行代码
  

select name
from world
order by population desc
limit 3,4

• 运行结果
  
• 知识点讲解
  • 1.limit n：n代表参数行数，只有n时从第一行开始显示前n行
  • 2.limit x,n：x代表位置偏移量，数据表中第一行的位置偏移量为0，第二行位置偏移量为1，以此类推，因此linit 0,n等价于limit n
  • 3.limit x,n：返回第x+1行开始的n行，取到第x+n行(可以通过“从x行后开始，取n行，取到x+n行”来记忆，例如limit7,2意为从第7行后开始(第7行后为第8行)，取2行，取到第9行)
  • 4.limit子句不同于其他核心子句也可以在其他类型的数据库中使用，limit仅能在MySQL数据库中使用
  • 5.limit子句写在整段查询语句的最后一行
    
    

（3）总结

• 查询结果返回前n行
  • select 字段名
  • from 表名
  • [where 表达式]
  • [order by 字段名 asc|desc]
  • [limit n]
    
    
• 查询结果返回第x+1行开始的n行到x+n行
  • select 字段名
  • from 表名
  • [where 表达式]
  • [order by 字段名 asc|desc]
  • [limit x,n]
    
    

（4）练习题
习题链接1：https://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial（链接中标号1）涉及数据表：nobel

题目1：查询nobel表中第100行到120行的数据

• 运行代码

select * from nobel
limit 99,20

• 运行结果
  
  
  

5. 聚合函数&group by

（1）知识点引入

• 聚合函数
  
  聚合函数适用于需要获取数据的汇总信息，例如某字段行数、某字段平均值、某字段中最大最小数等
  
  

• 标准语法

  • select 字段名1
  • from 表名
  • [where 表达式]
  • [group by 字段名1]
  • [order by 字段名 asc|desc]
  • [limit [位置偏移量,]行数]
    
    

• 语法解释

  • group by 字段名：规定依据哪个字段分组聚合
  • group by核心子句是可选项，使用该子句是为了依据相同字段值分组后进行聚合运算，常和聚合函数联用

（2）例题讲解
例题链接：（链接中标号1）https://sqlzoo.net/wiki/SUM_and_COUNT 涉及数据表：world

a. 单独使用聚合函数

【例题20】查询非洲总人口数

• 运行代码
  

select sum(population) 人口总数
from world
where  continent = 'Africa'

• 运行结果
  
• 将sum函数依次修改为avg()、max()、min()，依次计算平均人口数、最大人口数、最小人口数
  
  
  

【例题21】计算表格行数

• 运行代码
  

select count(*)	from world

• 运行结果
  
  
  

【例题22】尝试运行以下代码

• 运行代码1
  

selectcount(name),count(continent),count(area),count(population),count(gdp),count(*)
from world

• 运行结果1
  

• 运行代码2
  

selectsum(gdp)/195,sum(gdp)/192,avg(gdp)
from world

• 运行结果2
  
• 知识点讲解
  • 1.通过例题22的第一段代码，对比count(*)和count(字段名)
    
    • count(字段名) 计算指定字段下的总行数，但是计算时将忽略空值的行；
    • count(*) 计算表中的总行数，不管某列是否有数值或者为空值
      因此，count(*)适用于计算表格行数，count(字段名)计算字段中非空的行数
  • 2.sum、avg、max、min函数必须指定字段进行聚合运算，无法使用通配符，同时这些指定字段名的聚合函数都会忽略空值行
    • 以avg函数为例，已知gdp字段有3个空值，通过例题22的第二段，得知avg(gdp)等同于sum(gdp)/192
      
  • 3.在不使用group by子句时使用聚合函数，select字句中只能写聚合函数或者包含了聚合函数的算式(见例题22的第二段代码)，否则会报错
    • 报错翻译：如果没有group by子句，在没有group列的情况下混合字段和聚合函数（min()、max()、count()、…）是非法的
    • Excel演示：聚合函数类似于在excel中的数据透视表中只计算聚合值（求和项、计数项等），此时不能直接放一个原表的字段，数据透视表完全不支持
      
      

b. 单独使用group by
【例题23】运行以下代码，用group by对大洲进行分组

• 运行代码
  

select continent
from world
group by continent

• 运行结果
  
• 知识点讲解
  • 1.发现例题23答案等同于例题4对continent使用distinct去重后的答案，因此group by子句有数据去重的功能
  • 2.虽然都可以对数据去重，但是distinct和group by的逻辑完全不同，distinct仅是返回不同的行，group by本质是先对指定的字段中相同的值分为一个区，然后再对字段去重分组
    
    
    
    

c. 聚合函数和group by联用1

【例题24】查询每个大洲（continent）和大洲内的国家（name）数量

• 运行代码
  

selectcontinent,count(name)
from world
group by continent

• 运行结果
  
• Excel演示：以world表中前20行数据为例，演示group by子句
  
  
  
• 类似于数据透视表
  
  
  

d. 聚合函数和group by联用2
例题链接：（链接中标号1）https://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial涉及数据表：nobel

【例题25】查询2013至2015年每年每个科目的获奖人数，结果按年份从大到小，人数从大到小排序

• 运行代码
  

selectyr,subject,count(winner) 获奖人数
from nobel
where yr between 2013 and 2015
group by yr,subject
order by yr desc,count(winner) desc

• 运行结果
  

• Excel演示：以nobel表中2013年至2015年的数据共35行为例，演示group by多个字段
  
  
  

• 类似于数据透视表依次对yr，subject分类，然后汇总计算，再排序
  
  
  

• 知识点讲解

  • 1.group by子句中有多字段时，依据写的字段顺序依次对数据分区，因此group by 字段名1,字段名2与group by 字段名2,字段名1不一样
  • 2.使用group by子句时，select只能使用聚合函数和group by引用过的字段，否则会报错
    • 报错翻译:winner字段不在group by中
      
      
      

（3）总结

• 聚合函数
  
• 标准语法
  • select 字段名1,聚合函数(字段名)
  • from 表名
  • [where 表达式]
  • [group by 字段名1]
  • [order by 字段名 asc|desc]
  • [limit [位置偏移量,]行数]
    
    

（4）练习题
习题链接1：https://sqlzoo.net/wiki/SUM_and_COUNT（链接中标号5）涉及数据表：world

题目1：计算Estonia, Latvia, Lithuania这几个国家的总人口数

• 运行代码

select sum(population) 人口数
from world
where name in ('Estonia', 'Latvia', 'Lithuania')

• 运行结果
  
  
  

习题链接2：https://sqlzoo.net/wiki/SUM_and_COUNT（链接中标号7）涉及数据表：world

题目2：查询每个大洲和该大洲里人口数超过1千万的国家的数量

• 运行代码

selectcontinent,count(name) 'number of countries'
from world
where population >= 10000000
group by continent

• 运行结果
  
  
  

---

系列文章

SQL自学三部曲_Part1：云端数据库配置&Excel/Tableau连接数据库
SQL自学三部曲_Part2：十大必学语法（一）
SQL自学三部曲_Part2：十大必学语法（二）
SQL自学三部曲_Part3：关于SQL必须要知道的一切