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

毛媛媛

已于 2023-11-16 14:48:34 修改

阅读量273

点赞数

分类专栏：数据分析四大工具之二：SQL 文章标签： sql 数据库数据分析程序人生学习数据挖掘

于 2023-09-07 18:02:17 首次发布

本文链接：https://blog.csdn.net/myy12456789/article/details/132742613

版权

数据分析四大工具之二：SQL 专栏收录该内容

10 篇文章 3 订阅

订阅专栏

文章目录

前言
一、看前须知
二、sqlzoo数据介绍
三、基础语法和运行原理
四、如何继续提升
系列文章

前言

本文主要介绍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行
  - 字段类型

三、基础语法和运行原理

6. having&简单运行原理

（1）知识点引入

标准语法
- select 字段名
- from 表名
- [where 表达式]
- [group by 字段名]
- [having 表达式]
- [order by 字段名 asc|desc]
- [limit [位置偏移量,]行数]
语法解释
- having 表达式： 限定分组聚合后的查询行必须满足的条件
- having核心子句是可选项，使用该子句是为了对group by分组后的数据进行筛选

（2）例题讲解

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

【例题26】查询总人口数量至少为1亿（100000000）的大洲

运行代码

select 
	continent
from world
group by continent
having sum(population) >= 100000000

运行结果
知识点讲解
- 1.只有使用了group by子句后才会使用having子句，having子句不能脱离group by子句单独使用，因为having子句本质上是对group by分组的筛选
- 2.having子句中只能使用聚合函数和group by作为分组依据的字段
- 3.having的表达式和where的表达式基本相同，但是having的表达式中可以使用聚合函数，where的表达式中不可以，因为where是对原表的行数据筛选，having是对group by分组后的数据筛选
- 4.建议对行数据进行筛选时使用where子句，对含有聚合函数的筛选表达式使用having子句

【例题27】（运行原理）查询总人口数至少为3亿的大洲和其平均gdp，其中只有gdp高于200亿且人口数大于6000万或者gdp低于80亿且首都中含有三个a的国家的计入计算，最后按国家数从大到小排序，只显示第一行

运行代码

select
	continent
	,avg(gdp) 平均gdp
from world
where (gdp > 20000000000 and population > 60000000)
or (gdp <8000000000 and capital like '%a%a%a%')
group by continent
having sum(population) >= 300000000
order by count(name) desc
limit 1

运行结果
SQL运行原理
- from–where–group by–having–order by–limit–select
  - 执行from语句：从数据库中调取复制一份表格
  - 执行where语句：在复制的表格中筛选出符合条件的数据行
  - 执行group by语句：依据指定字段对筛选后的数据分区，将依据的字段去重分组，相当于Excel建立了一个数据透视表，添加了行标签
  - 执行having语句：筛选满足条件的分组
  - 执行order by语句：对筛选后的数据进行排序
  - 执行limit语句：对排序后的数据限制显示的行
  - 执行select语句：提取最后要显示的字段
Excel演示
结合SQL运行原理，讲解例题代码查询数据的过程
- 首先先从数据库中调取复制一张world表
- 通过where子句筛选满足gdp高于200亿且人口数大于6000万或者gdp低于80亿且首都中含有三个a的国家
- group by continent类似建立一个数据透视表，依据continent字段分组，相当于透视表中continent作为行标签
- having sum(population)>= 300000000通过依据continent分组聚合运算出sum(population)，再筛选出大于等于3亿的分组
  - 类似于对Excel透视表中行标签进行值筛选
- order by count(name) desc通过依据continent分组聚合运算算出count(name)，对计数值从大到小即降序排序
- limit1只显示排序后的结果集的前一行
- select进行最后的计算（包括算式和函数）并选取最后要显示的字段

（3）总结

标准语法
- select 字段名
- from 表名
- [where 表达式]
- [group by 字段名]
- [having 表达式]
- [order by 字段名 asc|desc]
- [limit [位置偏移量,]行数]
运行原理
- from–where–group by–having–order by–limit–select

（4）练习题

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

题目1：查询人均gdp大于3000的大洲及其人口数，仅gdp在200亿和300亿之间的国家计入计算

运行代码

select 
	continent,
	sum(population) 人口数,
	sum(gdp)/sum(population) 人均GDP
from world
where gdp between 20000000000 and 30000000000
group by continent
having sum(gdp)/sum(population) > 3000

运行结果

7. 部分常见函数

（1）知识点引入

a. 数学函数

round(x,y)—四舍五入函数
- round函数对x值进行四舍五入，精确到小数点后y位
- y为负值时，保留小数点左边相应的位数为0，不进行四舍五入
  - 例如：round(3.15,1)返回3.2，round(14.15,-1)返回10

b. 字符串函数

concat(s1,s2,…)—连接字符串函数
- concat函数返回连接参数s1、s2等产生的字符串
- 任一参数为null时，则返回null
  - 例如：concat(‘My’,’ ',‘SQL’)返回My SQL，concat(‘My’,null,‘SQL’)返回null
replace(s,s1,s2)—替换函数
- replace函数使用字符串s2代替s中所有的s1
  - 例如：replace(‘MySQLMySQL’,‘SQL’,‘sql’)返回MysqlMysql
left(s,n)、right(s,n)、substring(s,n,len)—截取字符串一部分的函数
- left函数返回字符串s最左边n个字符
- right函数返回字符串s最右边n个字符
- substring函数返回字符串s从第n个字符起取长度为len的子字符串，n也可以为负值，则从倒数第n个字符起取长度为len的子字符串，没有len值则取从第n个字符起到最后一位
  - 例如：left(‘abcdefg’,3)返回abc，right(‘abcdefg’,3)返回efg，substring(‘abcdefg’,2,3)返回bcd，substring(‘abcdefg’,-2,3)返回fg，substring(‘abcdefg’,2)返回bcdefg

c. 数据类型转换函数

cast(x as type)—转换数据类型的函数
- cast函数将一个类型的x值转换为另一个类型的值
- type参数可以填写char(n)、date、time、datetime、decimal等转换为对应的数据类型

d. 日期时间函数

year(date)、month(date)、day(date)—获取年月日的函数
- date可以是年月日组成的日期，也可以是年月日时分秒组成的日期时间
- year(date)返回日期格式中的年份
- month(date)返回日期格式中的月份
- day(date)返回年日期格式中的日份
  - 例如：year(‘2021-08-03’)返回2021，month(‘2021-08-03’)返回8，day(‘2021-08-03’)返回3
date_add(date,interval expr type)、date_sub(date,interval expr type)—对指定起始时间进行加减操作
- date用来指定起始时间
- date可以是年月日组成的日期，也可以是年月日时分秒组成的日期时间
- expr用来指定从起始时间添加或减去的时间间隔
- type指示expr被解释的方式，type可以可以是以下值
  - 主要使用红框中的值
- date_add函数对起始时间进行加操作，date_sub函数对起始时间进行减操作
  - 例如：date_add(‘2021-08-03 23:59:59’,interval 1 second)返回2021-08-04 24:00:00，date_sub(‘2021-08-03 23:59:59’,interval 2 month)返回2021-06-03 23:59:59
datediff(date1,date2)—计算两个日期之间间隔的天数
- datediff函数由date1-date2计算出间隔的时间，只有date的日期部分参与计算，时间不参与
  - 例如：datediff(‘2021-06-08’,‘2021-06-01’)返回7，datediff(‘2021-06-08 23:59:59’,‘2021-06-01 21:00:00’)返回7，datediff(‘2021-06-01’,‘2021-06-08’)返回-7
date_format(date,format)—将日期和时间格式化
- date_format函数根据format指定的格式显示date值
- 可以换使用的格式有
  - 例如：date_format(‘2018-06-01 16:23:12’,‘%b %d %Y %h:%i %p’)返回Jun 01 2018 04:23 PM，date_format(‘2018-06-01 16:23:12’,‘%Y/%d/%m’)返回2018/01/06

e. 条件判断函数
根据满足不同条件，执行相应流程

if(expr,v1,v2)
- 如果表达式expr是true返回值v1，否则返回v2
  - 例如：if(1<2,‘Y’,‘N’)返回Y，if(1>2,‘Y’,‘N’)返回N
case when
- case expr when v1 then r1 [when v2 then r2] …[else rn] end
  - 例如：case 2 when 1 then ‘one’ when 2 then ‘two’ else ‘more’ end 返回two
  - case后面的值为2，与第二条分支语句when后面的值相等相等，因此返回two
- case when v1 then r1 [when v2 then r2]…[else rn] end
  - 例如：case when 1<0 then ‘T’ else ‘F’ end返回F
  - 1<0的结果为false，因此函数返回值为else后面的F

（2）例题讲解

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

【例题28】尝试使用刚学习的函数

a. case when和if函数

运行代码

select
	recovered 累计治愈人数
	,case when recovered = 1 then 'one' 
	      when recovered > 1 then 'more' 
	      else '0' end
from covid
where recovered > 0

运行结果

b. year、month、day函数

运行代码

select
	whn 更新时间
	,year(whn) 年
	,month(whn) 月
	,day(whn) 日
from covid
where recovered > 0

运行结果

c. date_add函数

运行代码

select
	whn 更新时间
	,date_add(whn,interval 2 day) 加2天
from covid
where recovered > 0

运行结果

d. round和concat嵌套得到百分比数据

运行代码

select
	confirmed
	,deaths
	,recovered
	,recovered/confirmed
	,concat(round((recovered/confirmed)*100,2),'%') 治愈率
from covid
where recovered/confirmed > 0.3

运行结果

e. replace函数

运行代码

select distinct
	name
	,replace(name,'a','替换') 替换
from covid

运行结果

f. substring函数

运行代码

select distinct
	name
	,substring(name,2,3)
	,substring(name,2)
from covid
where recovered/confirmed > 0.3

运行结果

（3）总结

数学函数
- round(x,y)—四舍五入函数
字符串函数
- concat(s1,s2,…)—连接字符串函数
- replace(s,s1,s2)—替换函数
- left(s,n)—从左截取字符串一部分的函数
- right(s,n)—从右截取字符串一部分的函数
- substring(s,n,len)—从指定位置截取字符串一部分的函数
数据类型转换函数
- cast(x as type)—转换数据类型的函数
日期时间函数
- year(date)—获取年的函数
- month(date)—获取月的函数
- day(date)—获取日的函数
- date_add(date,interval expr type)—对指定起始时间进行加操作
- date_sub(date,interval expr type)—对指定起始时间进行减操作
- datediff(date1,date2)—计算两个日期之间间隔的天数
- date_format(date,format)—将日期和时间格式化
条件判断函数—根据满足不同条件，执行相应流程
- if(expr,v1,v2)
- case when
  - case expr when v1 then r1 [when v2 then r2] …[else rn] end
  - case when v1 then r1 [when v2 then r2]…[else rn] end

（4）练习题

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

题目1：查询国家名称及其首都名称都以相同的字母开头的国家名及其首都，且不能包括国家名称和首都名称完全相同的情况

运行代码

select
	name
	,capital
from world
where left(name, 1) = left(capital, 1)
and name != capital

运行结果

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

题目2：查询首都和名称，其中首都需是国家名称的扩展
例如：答案中应该包括墨西哥城（Mexico City），因为它比墨西哥（Mexico）更长，而不应该将卢森堡（Luxembourg）包括在内，因为首都名与国家名相同

运行代码

select
	capital
	,name
from world
where capital like concat('%',name,'%')
and capital != name

运行结果

8. 窗口函数

（1）知识点引入

标准语法
- 窗口函数 over([partition by 字段名] [order by 字段名 asc|desc])
语法讲解
- over()中两个子句为可选项，partition by指定分区依据，order by指定排序依据
排序窗口函数
- rank() over()：12245…
- dense_rank() over()：12234…
- row_number() over()：12345…
偏移分析函数
- lag(字段名,偏移量[,默认值]) over()：向前
- lead(字段名,偏移量[,默认值]) over()：向后

（2）例题讲解

a. 排序窗口函数

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

【例题29】查询每一年S14000021选区中所有候选人所在的团体(party)和得票数(votes)，并对每一年中的所有候选人根据选票数的高低赋予名次，选票数最高则为1，第二名则为2，后续以此类推，最后根据团体(party)和年份(yr)排序

运行代码

select
	yr
	,party
	,votes
	,rank()over(partition by yr order by votes desc) as posn
from ge
where constituency = 'S14000021'
order by party,yr

运行结果
Excel演示：演示排序窗口函数如何赋予排序值
知识点讲解
- 1.窗口函数只能写在select字句中
- 2.窗函数中的partition by子句可以指定数据的分区，和group by要去重分组不同的是，partition by只分区不去重
- 3.窗口函数中没有partition by子句时，即不对数据分区，直接整个表为一个区
- 4.排序窗口函数中order by子句是必选项，窗口函数中order by子句在分区内，依据指定字段和排序方法对数据行排序
- 5.rank()、dense_rank)、row_number()指定排序赋值方法，对比三个排序窗口函数的异同
  - rank()：跳跃式排序——比如数值为99，99，90，89，那么通过这个
    函数得到的排名为1，1，3，4
  - dense_rank()：并列连续型排序——比如数值为99，99，90，89，那么通过这个函数得到的排名为1，1，2，3
  - row_number()：连续型排序——比如数值为99，99，90，89，那么通过这个函数得到的排名为1，2，3，4
  - 根据对排序值的需求选择相应的排序窗口函数，由于值的不同特性(比如数值不重复)，这三个函数可以通用

b. 偏移分析函数

例题链接：（链接中标号2）https://sqlzoo.net/wiki/Window_LAG 涉及数据表：covid

【例题30】查询法国和德国1月每天新增确诊人数，最后显示国家名、标准日期（2020-01-27）、当天截至时间累计确诊人数、昨天截至时间累计确诊人数、每天新增确诊人数，按照截至时间排序

运行代码

select
	name
	,date_format(whn,'%Y-%m-%d') date
	,confirmed 当天截至时间累计确诊人数
	,lag(confirmed,1)over(partition by name order by whn) 昨天截至时间累计确诊人数
	,(confirmed - lag(confirmed,1)over(partition by name order by whn)) 每天新增确诊人数
from covid
where name in ('France','Germany') and month(whn) = 1
order by whn

运行结果
Excel演示
知识点讲解
- 1.偏移分析窗口函数中order by子句是必选项
- 2.lag()和lead()指定偏移的方向，lag是向上偏移，行向上取数据，lead是向下偏移，行向下取数据
- 3.lag(字段名,偏移量[,默认值])over()还有一个参数为默认值，是可选项，在分区中没有前一行的情况下填充默认值，不填的情况下默认是null

（3）总结

排序窗口函数语法
- rank() over([partition by 字段名] order by 字段名 asc|desc)
- dense_rank() over([partition by 字段名] order by 字段名 asc|desc)
- row_number() over([partition by 字段名] order by 字段名 asc|desc)
偏移分析函数语法
- lag(字段名,偏移量[,默认值]) over([partition by 字段名] order by 字段名 asc|desc)
- lead(字段名,偏移量[,默认值]) over([partition by 字段名] order by 字段名 asc|desc)

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

题目1：查询2017年选区为 ‘S14000024’ 的所有候选人所在团体（party）和其选票数（votes）、还有候选人得票数在选区内对应的的排名

运行代码

select
	party
	,votes
	,rank() over (order by votes desc) as posn
from ge
where constituency = 'S14000024' and yr = 2017
order by party

运行结果

高考排名：rank()排名

习题链接2：https://sqlzoo.net/wiki/Window_LAG（链接中标号6）涉及数据表：covid

题目2：查询截至时间为2020年4月20日的国家名，确诊人数，确诊人数排名，死亡人数，死亡人数排名，按照确诊人数降序排名

运行代码

select
name
	,confirmed
	,rank()over(order by confirmed desc) rk
	,deaths
	,rank()over(order by deaths desc) deathrk
from covid
where whn = '2020-04-20'
order by confirmed desc

运行结果

习题链接3：https://sqlzoo.net/wiki/Window_LAG（链接中标号4）涉及数据表：covid

题目3：查询意大利每周新增确诊数（显示每周一的数值 weekday(whn) = 0）；最后显示国家名，标准日期（2020-01-27），每周新增人数；按照截至时间排序

运行代码

select
	name
	, date_format(whn,'%Y-%m-%d') date
	, (confirmed - lag(confirmed,1)over(partition by name order by whn))  New
from covid
where name = 'Italy'
and weekday(whn) = 0
order by whn

运行结果

9. 表连接

（1）知识点引入

基础语法
- 内连接
  - select 字段名
  - from 表名1 inner join 表名2 on 表名1.字段名 = 表名2.字段名
  - 注意内连接inner可以省略，直接使用join默认为内连接
- 左连接
  - select 字段名
  - from 表名1 left join 表名2 on 表名1.字段名 = 表名2.字段名
- 右连接
  - select 字段名
  - from 表名1 right join 表名2 on 表名1.字段名 = 表名2.字段名
语法解释
- join表连接通过on 表名1.字段名= 表名2.字段名，将两个表格各自的字段等值连接来匹配连接，无法匹配的填充null值
- Excel演示 连接其实是先连接再剔除的过程
- inner join内连接，连接两个表留下同时互相匹配上的行得到一张新表
  - Excel演示
    
    将左右两表有null值的行全部剔除，只保留连接上的行（不允许任何null值存在）
- left join左连接，左边的表返回所有行，右边的表只留下匹配上的行得到一张新表
  - Excel演示
    将左边表null值剔除（左边表不允许null值存在）
- right join右连接，右边的表返回所有行，左边的表只留下匹配上的行得到一张新表
  - Excel演示
    
    将右边表null值剔除（右边表不允许null值存在）
- from 表1 left join 表2 on 表1字段A=表2.字段B 等价于 from 表2 right join 表1 on 表1.字段A=表2.字段B

（2）例题讲解
例题链接：（链接中标号4）https://sqlzoo.net/wiki/The_JOIN_operation 涉及数据表：game、goal、eteam

【例题31】查询有球员名叫Mario进球的队伍1（team1），队伍2（team2）及球员姓名

运行代码

select
	team1
	,team2
	,player
from game
join goal
on game.id = goal.matchid
where player like 'Mario%'

运行结果

【例题32】查询队伍1（team1）的教练是“Fernando Santos”的球队名称（teamname）、比赛日期（mdate）和赛事编号（id）

运行代码

select
	mdate
	,teamname
	,game.id
from game
join eteam
on eteam.id = game.team1
where coach = 'Fernando Santos'

运行结果

例题链接：（链接中标号3）https://sqlzoo.net/wiki/Using_Null 涉及数据表：teacher、dept

【例题33】使用合适的连接显示所有教师及其所教授的科目名

运行代码

select
	t.name
	,d.name
from teacher t
left join dept d
on t.dept = d.id

运行结果
知识点讲解
- 1.左连接可以保留左边表的所有数据行，同理右连接保留右边表的所有数据行
- 2.from teacher left join dept等同于from dept right join teacher
- 3.对比以下连接运行结果，可以说表连接也有筛选数据的功能
  - join内连接
  - left join左连接
  - right join右连接

（3）总结

内连接inner join语法
- select 字段名
- from 表名1 inner join 表名2 on 表名1.字段名 = 表名2.字段名
- 注意内连接inner可以省略，直接使用join默认为内连接
左连接left join语法
- select 字段名
- from 表名1 left join 表名2 on 表名1.字段名 = 表名2.字段名
右连接right join语法
- select 字段名
- from 表名1 right join 表名2 on 表名1.字段名 = 表名2.字段名

（4）练习题

习题链接1：https://sqlzoo.net/wiki/More_JOIN_operations（链接中标号13）涉及数据表：movie、casting、actor

题目1：查询至少出演过第1主角30次的演员名

运行代码

select 
	name
from casting c
join actor a
on c.actorid = a.id
where ord = 1
group by name
having count(movieid)>=30

运行结果

习题链接2：https://sqlzoo.net/wiki/The_JOIN_operation（链接中标号5）涉及数据表：game、goal、eteam

题目2：查询在比赛前十分钟有进球记录的球员，他的队伍编号(teamid)，教练(coach), 进球时间(gtime)

运行代码

select
	player
	,teamid
	,coach
	,gtime
from goal
join eteam
on goal.teamid = eteam.id
where goal.gtime  <= 10

运行结果

习题链接3：https://sqlzoo.net/wiki/The_JOIN_operation（链接中标号13）涉及数据表：game、goal、eteam

题目3：查询每场比赛，每个球队的得分情况，按照以下格式显示，最后按照举办时间（mdate）、赛事编号（matchid）、队伍1（team1）和队伍2（team2）排序
在这里插入图片描述

运行代码

select
	ga.mdate
	,ga.team1
	,sum(case when ga.team1=go.teamid then 1 else 0 end) score1
	,ga.team2
	,sum(case when ga.team2=go.teamid then 1 else 0 end) score2
from game ga
left join goal go
on ga.id = go.matchid
group by ga.mdate,ga.team1,ga.team2
order by ga.mdate, go.matchid, ga.team1, ga.team2

运行结果

10. 子查询

（1）知识点引入

子查询本身就是一段完整的查询语句，然后用括号英文括号()包裹嵌套在主查询语句中，子查询可以多层嵌套
最常用的子查询运用在from和where子句中

（2）例题讲解

a. where基于子查询条件筛选（比较运算符&in关键字）

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

【例题34】查询出gdp高于欧洲每个国家的所有国家名，有一些国家gdp值可能为NULL，请排除这些国家

运行代码

select 
	name
from world
where gdp is not null
and gdp > (
             select 
             	max(gdp)
             from world
             where continent = 'Europe'
)

运行结果

【例题35】查询跟阿尔及尼亚（Argentina）和澳大利亚（Australia）在同一大洲的所有国家名及其所属大洲，并按照国家名进行排序

运行代码

select
	name
	,continent
from world
where continent in (
                       select 
                         	continent
                       from world
                       where name in ('Argentina','Australia')
)
order by name

运行结果

b. from基于子查询作为数据表

例题链接：（链接中标号5）https://sqlzoo.net/wiki/Window_functions 涉及数据表：ge

【例题36】查询2017年所有在爱丁堡的选区当选议员所在选区（constituency）及其团队（party），已知爱丁堡选区编号为S14000021至S14000026，当选议员即各选区得票数最高的候选人

运行代码

select
	constituency
	,party
from(
	    select
	    	constituency
	    	,party
	    	,votes
	    	,rank()over(partition by constituency order by votes desc)  posn
	    from ge
	    where constituency between 'S14000021' and 'S14000026'
	    and yr  = 2017
)  as rk
where rk.posn = 1

运行结果
知识点讲解
- 1.from子句中的子查询，本质上是通过一段查询语句得出的查询结果作为主查询的数据来源
- 2.from子查询结果可以是多行多列
- 3.from子查询必须使用别名，同样可以省略as，不使用别名会报错
- 4.where rk.posn=1要写在主查询中，不能写在子查询中，因为一段查询语句中where子句先于窗口函数运行，运行where子句时还没有posn字段，无法进行条件筛选，运行完窗口函数有posn字段时，不会再返回运行where子句，因此必须写子查询，优先运行完子查询得到的查询结果有posn列，该子查询作为主查询的数据来源，主查询就可以正常运行筛选条件语句where rk.posn=1

（3）总结

子查询本身是一个完整的查询，由括号包裹嵌套在主查询中
子查询最后返回查询出的结果给主查询
子查询可以在select，from，where，having子句（同where）中使用，但要注意不同子句能接受的子查询种类有差别
子查询可以多重嵌套（子查询可以作为主查询再嵌套子查询）

（4）练习题

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

题目1：查询在欧洲(Europe)人均gdp大于英国 (United Kingdom)的国家名

人均国内生产总值（人均GDP）=国内生产总值（GDP）/人口（populaiton）

运行代码

select 
	name
from world
where continent='europe'
and gdp/population > (
                            select 
                            	gdp/population
                            from world
                            where name='united kingdom'
)

运行结果

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

题目2：查询人口数（population）超过加拿大（Canada）但是少于波兰（Poland）的国家，结果显示这些国家名（name）和人口数（population）

运行代码

select
	name
	,population
from world
where population > (
                        select 
                        	population
                        from world
                        where name= 'Canada'
)
and population < (
                         select 
                         	population
                         from world
                         where name = 'Poland'
)

运行结果

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

题目3：查询所有国家人口均≤25000000的大洲，及其国家名（name）和人口（population）

错误代码

select
	name
	,continent
	,population
from world
where continent in (
                        select 
                        		distinct continent
                        from world
                        where population <=25000000
)

如果用正向<=，那么意思是只要有国家小于等于25000000，则该大洲就会被选择出来；不满足题目要求的所有国家均小于等于25000000，所以要反向选择

正确代码

select
	name
	,continent
	,population
from world
where continent not in (
	                        select 
	                        		distinct continent
	                        from world
	                        where population >25000000
)

运行结果

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

题目4：查找每个大陆（continent）中最大的国家（按区域area），显示该大洲（continent），国家名（name）和面积（area）

运行代码

select
	continent
	,name
	,area
from world
where (continent,area) in  (
                               select
                               		continent
                               		,max(area)
                               from world
                               group by continent
)

运行结果

习题链接5：https://sqlzoo.net/wiki/Window_LAG（链接中标号2）涉及数据表：covid

题目5：查询德国和意大利每天新增治愈人数并从高到低排名，查询结果按国家名，截至日期（输出格式为’xxxx年xx月xx日’），新增治愈人数，按排名排序

运行代码

select
	name
	,日期
	,每天新增治愈人数
	,rank()over(partition by name order by 每天新增治愈人数 desc) 排名
from(
    select
    	name
   	 	,date_format(whn,'%Y年%m月%d日') 日期
    	,(recovered - lag(recovered,1)over(partition by name order by whn)) 每天新增治愈人数
    from covid
    where name in ('France','Italy')
) re
order by 排名