SQL高级语句+运行原理
基于公开网站sqlzoo的MySQL数据库引擎,必须使用英文界面练习代码(不同语言界面不同引擎可能导致数据库数据不同),并切换至Mysql引擎。
网址:https://sqlzoo.net/
- 所有函数语句均放在SELECT语句后FROM语句前
1.部分常见函数
1.1 四舍五入函数
round(x,y)
- round函数对x值进行四舍五入,精确到小数点后y位;
- y为负值时,保留小数点左边相应的位数为0,不进行四舍五入
1.2 字符串函数
1.2.1 coucat函数
连接字符串函数:concat(s1,s2,…)
- concat函数返回连接参数s1、s2等产生的字符串;
- 任一参数为null时,则返回null
#语法示例:
SELECT confirmed, deaths, recovered, recovered/confirmed,
concat(round((recovered/confirmed)*100, 2),'%') 治愈率
FROM covid
WHERE recovered/confirmed > 0.3
#语法示例:查询首都和名称,其中首都需是国家名称的扩展
SELECT name,capital
FROM world
WHERE capital like concat('%', name, '%')
and name != capital
1.2.2 replace函数
替换函数:replace(s,s1,s2)
- replace函数使用字符串s2代替s中所有的s1
#语法示例:
SELECT DISTINCT name,
replace(name, 'A', 'a')
FROM covid
1.2.3 截取函数
截取字符串一部分的函数:
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个字符起到最后一位
#语法示例:
SELECT DISTINCT name,
left(name, 2),
right(name,1),
substring(name,2,3),
substring(name,2)
FROM covid
#语法示例:查询国家名称及其首都名称都以相同的字母开头的国家名及其首都,且不能包括国家名称和首都名称完全相同的情况
SELECT name,capital
FROM world
WHERE left(name, 1) = left(capital, 1)
and name != capital
1.3 数据类型转换函数
转换数据类型的函数:cast(x as type)
- cast函数将一个类型的x值转换为另一个类型的值;
- type参数可以填写char(n)、date、time、datetime、decimal等转换为对应的数据类型
1.4 日期时间函数
1.4.1 获取年月日的函数:
year(date)
month(date)
day(date)
- date可以是年月日组成的日期,也可以是年月日时分秒组成的日期时间;
- year(date)返回日期格式中的年份;
- month(date)返回日期格式中的月份;
- day(date)返回年日期格式中的日份
#语法示例:
SELECT whn 更新时间,
year(whn) 年,
month(whn) 月,
day(whn) 日
FROM covid
WHERE recovered > 0
1.4.2 对指定起始时间进行加减操作:
date_add(date,interval expr type)
date_sub(date,interval expr type)
-
date用来指定起始时间;
-
date可以是年月日组成的日期,也可以是年月日时分秒组成的日期时间;
-
expr用来指定从起始时间添加或减去的时间间隔;
-
type指示expr被解释的方式,type可以是以下值:
-
date_add函数对起始时间进行加操作,date_sub函数对起始时间进行减操作
#语法示例:
SELECT whn 更新时间,
date_add(whn, interval 2 day) 加2天
FROM covid
WHERE recovered > 0
1.4.3 计算两个日期之间间隔的天数
datediff(date1,date2)
- datediff函数由date1-date2计算出间隔的时间,只有date的日期部分参与计算,时间不参与;
#语法示例:
SELECT DATEDIFF('2024-06-01', '2024-05-01') AS DateDifference;
1.4.3 将日期和时间格式化
date_format(date,format)
- date_format函数根据format指定的格式显示date值;
- 可以替换的格式有:
1.5 条件判断函数
if(expr,v1,v2)
- 如果表达式expr是true返回值v1,否则返回v2
#语法示例:
SELECT recovered 累积治愈人数,
if (recovered > 500, 'GOOD', 'BAD')
FROM covid
case expr when v1 then r1 when v2 then r2 …else rn end
#语法示例:
SELECT recovered 累积治愈人数,
case recovered>0 when recovered=1 then 'one' else 'more' end
FROM covid
case when v1 then r1 when v2 then r2…else rn end
#语法示例:
SELECT recovered 累积治愈人数,
case when recovered = 1 then 'one' when recovered > 1 then 'more' else '0' end
FROM covid
WHERE recovered > 0
2.窗口函数
窗口函数over([partition by 字段名] [order by 字段名 asc|desc])
- over()中两个子句为可选项,partition by指定分区依据,order by指定排序依据
2.1 排序窗口函数
rank()over()
dense_rank()over()
row_number()over()
#语法示例:查询每一年S14000021选区中所有候选人所在的团体(party)和得票数(votes),并对每一年中的所有候选人根据选票数的高低赋予名次,选票数最高则为1,第二名则为2,后续以此类推,最后根据团体(party)和年份(yr)排序
SELECT yr, votes, party,
RANK()OVER(PARTITION BY yr ORDER BY votes desc) as posn
FROM ge
WHERE constituency = 'S14000021'
ORDER BY party, yr
- 窗口函数只能写在select语句中;
- 窗口函数中的partition by子句可以指定数据的分区,但其只分区不去重;partition by可以不写,此时默认整个表为一个区;
- 排序窗口函数中的order by子句是必选项,其在分区内依据指定字段和排序方法对数据进行排序;
- rank()、dense_rank()、row_number()指定排序赋值方法:
- rank():跳跃式排序,同数值同排名,但紧跟的排名跳跃+1.例如:99、99、90、88排序为1、1、3、4;
- dense_rank():并列连续排序,同数值同排名,但紧跟的排名不跳跃。例如:99、99、90、88排序为1、1、2、3;
- row_number():连续排序,同数值按随机不同排名。例如:99、99、90、88排序为1、2、3、4.
2.2 偏移分析函数
lag(字段名,偏移量[,默认值])over()
lead(字段名,偏移量[,默认值])over()
#语法示例:查询法国和德国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
- 偏移分析窗口函数中order by子句是必选项;
- lag表示向上偏移,行向上取数据;lead表示向下偏移,行向下取数据
#语法示例:查询意大利每周新增确诊数(显示每周一的数值 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
3.表连接
join表连接通过on表名1.字段名=表名2.字段名,将两个表格各自的字段等值连接来匹配连接,无法匹配的填充null值,其逻辑思想如下所示:
3.1 内连接
SELECT 字段名
FROM 表名1 INNER JOIN 表名2 ON 表名1.字段名 = 表名2.字段名
INNER JOIN表示内连接,连接两个表留下同时互相匹配上的行得到一张新表,操作思想如下所示:
#语法示例:查询有球员名叫Mario进球的队伍1(team1),队伍2(team2)及球员姓名
SELECT team1, team2, player
FROM game JOIN goal ON game.id = goal.matchid
WHERE player like 'Mario%'
- INNER JOIN时INNER可省略, JOIN默认为内连接
#语法示例:查询队伍1(team1)的教练是“Fernando Santos”的球队名称(teamname)、比赛日期(mdate)和赛事编号(id)
SELECT mdate, teamname, ga.id
FROM game ga JOIN eteam et ON et.id = ga.team1
WHERE coach = 'Fernando Santos'
- FROM子句中的表格可以起别名,并在指定连接键和指定字段来源于的表格时,可以用别名代替完整的表格名;
- 多表连接时,字段名唯一可以不指定表名,但字段名不唯一时一定要指明表名
3.2 左连接
SELECT 字段名
FROM 表名1 LEFT JOIN 表名2 ON 表名1.字段名 = 表名2.字段名
LEFT JOIN表示左连接,左边的表返回所有的行,右边的表只留下匹配上的行,得到一张新表。操作思想如下所示:
#语法示例:使用合适的连接显示所有教师及其所教授的科目名
SELECT teacher.name, dept.name
FROM teacher LEFT JOIN dept ON teacher.dept = dept.id
3.3 右连接
SELECT 字段名
FROM 表名1 RIGHT JOIN 表名2 ON 表名1.字段名 = 表名2.字段名
RIGHT JOIN表示右连接,右边的表返回所有的行,左边的表只留下匹配上的行,得到一张新表。操作思想如下所示:
#语法示例:使用合适的连接显示所有教师及其所教授的科目名
SELECT teacher.name, dept.name
FROM dept RIGHT JOIN teacher ON teacher.dept = dept.id
- 左右连接可以相互转换,某种程度上效果一致,只是表的先后顺序不同
#语法示例:查询每场比赛,每个球队的得分情况,最后按照举办时间(mdate)、赛事编号(matchid)、队伍1(team1)和队伍2(team2)排序
SELECT game.mdate, game.team1,
sum(case when game.team1=goal.teamid then 1 else 0 end) score1,
game.team2,
sum(case when game.team2=goal.teamid then 1 else 0 end) score2
FROM game LEFT JOIN goal ON game.id = goal.matchid
GROUP BY game.mdate, game.team1, game.team2
ORDER BY game.mdate, goal.matchid, game.team1, game.team2
4.子查询
子查询本身就是一段完整的查询语句,然后用英文括号()包裹嵌套在主查询语句中,子查询可以多层嵌套。最常用的子查询运用在from和where子句中。
子查询是可以自己正常独立运行的一段完整的查询语句,然后将子查询的查询结果作为主查询的一部分,因此子查询优先级高于主查询。
4.1 where基于子查询条件筛选(比较运算符&in关键字)
#语法示例:查询出gdp高于欧洲每个国家的所有国家名,有一些国家gdp值可能为NULL,请排除这些国家
SELECT name
FROM world
WHERE gdp is not null
AND gdp > (
SELECT max(gdp)
FROM world
WHERE continent = 'Europe'
)
#语法示例:查询跟阿尔及尼亚(Argentina)和澳大利亚(Australia)在同一大洲的所有国家名及其所属大洲, 并按照国家名进行排序
SELECT name, continent
FROM world
WHERE continent in (
SELECT continent
FROM world
WHERE name in ('Argentina', 'Australia')
)
ORDER BY name
- 对于带有比较运算符的子查询,要求子查询为标量子查询,即子查询结果为一行一列;
- 对于带有in关键字的子查询,要求子查询为列子查询,即子查询结果为多行一列;
- where子句中的子查询适用于查询条件无法一步到位,需要先进行一步查询得到结果再基于这个查询结果再进行条件判断的情况
4.2 from基于子查询作为数据表
#语法示例:查询2017年所有在爱丁堡的选区当选议员所在选区(constituency)及其团队(party),已知爱丁堡选区编号为S14000021至S14000026,当选议员即各选区得票数最高的候选人
SELECT constituency, party
FROM (
SELECT constituency, party, votes,
RANK()OVER(PARTITION BY constituency ORDER BY votes desc) as posn
FROM ge
WHERE yr = 2017 AND constituency between 'S14000021' and 'S14000026') as rk
WHERE rk.posn = 1
- from子句中的子查询,本质上是通过一段查询语句得出查询结果作为主查询的数据来源;
- from子查询结果可以是多行多列;
- from子查询必须使用别名,同样可以省略as
#语法示例:查询在欧洲(Europe)人均gdp大于英国 (United Kingdom)的国家名
SELECT name
FROM world
WHERE gdp/population > (
SELECT gdp/population
FROM world
WHERE name = 'United Kingdom')
AND continent = 'Europe'