戴师兄数据分析课程SQL部分复盘

 该文章是在学习戴师兄数据分析课程之后对笔记以及课程内容的复盘过程，用来巩固新知识。

该课程在讲解过程当中例题涉及的是sqlzoo当中的数据表格。sqlzoo当中的数据包括<world>、<nobel>、<covid>、<ge>、<game、goal、eteam>、<teacher、dept>、<movie、casting、actor>

一、基础语句

1.select&from

标准语法：select 字段名 from 表名

语法解释：

select 字段名，字段名... 决定这一段查询最后展示哪些字段。

from 表名 指定这段查询语句涉及的数据来源，从哪个表查询数据。

这是一段查询语句中必不可少的两个核心语句，select和from分别是两个核心语句中的关键字。

补：

别名语法：select 列名 as 别名(as 可以省略），select中用as给表格列取别名

查询多列：select 字段名，字段名... from 表名

查询所有列：select * from 表名

数据去重：select中使用distinct对重复行的数据进行去重(完全一模一样的行）,同时distinct只能放在select后，有多个字段时distinct不能放在字段中间。

select当中的计算字段：select当中可以直接进行计算

2.where

标准语法：

select 字段名 from 表名

where 表达式

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

补：

运算符查询语法：where 字段名 运算符  值

模糊查询语法：where 字段名 like '通配符+字符' (通配符"_"为占位符，代表一个字符，“%"代表多个字符。

使用多条件查询：where 条件代码1 and/or 条件代码2，and优先级高于or

运算符：

通配符：_占位符、%多个字符

3.order by

标准语法：

select 字段名 from 表名

[where 表达式]

[order by 字段名 asc/desc] (order后多个字段时用逗号隔开）

（asc代表升序，是默认排序可以省略，不写即默认升序，desc是降序排序）

语法解释：order by 字段名 asc/desc 规定查询出的结果集显示的顺序。order by核心子句是可选项，使用该子句是为了对被查询出的结果集，指定依据字段排序。

补：若要指定某几个相关数据放在最后可以使用[字段名 in (‘指定数据’，‘指定数据',...)],该表达式代表若该字段在括号当中的数据里时为1，不在时为0，按照0和1排序，自然可以放到后面。例子如下：

4.limit

标准语法：

select 字段名 from 表名

[where 表达式]

[order by 字段名 asc/desc]

[limit 位置偏移量，行数]

语法解释：limit 位置偏移量，行数 限制查询结果显示的行数，limit子句是可选项，行数是子句中的必选参数，位置偏移量是可选参数。

补：

limit n 表示查询结果返回前n行。若不写位置偏移量默认从第一行开始显示行数。

limit x,n 表示从x+1 行开始显示n行到x+n行

5.聚合函数&group by

聚合函数：

 标准语法group by：

select 字段名1，聚合函数（字段名） from 表名

[where 表达式]

[group by 字段名1]

[order by 字段名 asc/desc]

[limit 位置偏移量，行数]

语法解释：group by 字段名规定依据哪个字段分组聚合，group by 核心子句是可选项，使用该子句是为了依据相同字段分组后进行聚合运算，常和聚合函数连用。聚合函数和group by都可以单独使用。group by 后的字段是聚合依据，就是分组依据。非聚合字段全部要作为聚合依据。

聚合函数单独使用例子如下：

①计算非洲总人口数

此时select后只能有聚合函数，不能有其他字段名，若要有其他字段名，则必须与group by连用。

②计算表格行数

count(*) 计算表格行数

group by 单独使用的例子如下：

补：

例题如下：

①查询2013-2015每年每个科目的获奖人数，最后结果按照年份从大到小排序，获奖人数从大到小排序。

注：select 后有单独的字段名+聚合函数时，必须使用group by;只有字段名时可以单独使用group by;只有聚合函数时可以单独使用聚合函数。非聚合字段需要全部作为聚合依据。

6.having&简单运行原理

标准语法：

select 字段名 from 表名

[where 表达式]

[group by 字段名]

[having 表达式]

[order by 字段名 asc/desc]

[limit 位置偏移量，行数]

语法解释：having 表达式 限定分组聚合后的查询行必须满足的条件，having核心子句是可选项，使用该子句是为了对group by分组后的数据进行筛选。

注：聚合前的筛选用where，聚合后的筛选用having,having的表达式中可以使用聚合函数，where的表达式中不可以（where的表达式可以使用运算符）。

运行原理：

from→where→group by→having→order by→limit→select

例题如下：

①查询总人口数至少为1亿的大洲

注：having前必须有group,having是在对聚合后的数据进行筛选，group by是按照聚合依据进行聚合的语句

②查询总人口数至少为3亿的大洲和其平均GDP，其中只有GDP高于200亿且人口数大于6000万或者GDP低于80亿且首都中含有三个a的国家计入计算，最后按照国家数量进行从大到小排序，只显示第一行。

select continent, avg(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

7.部分常见函数

<1>数学函数

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

<2>.字符串函数

①concat(s1,s2,...)-连接字符串函数。concat函数返回连接参数s1,s2等产生的字符串；若任一参数为null,则返回null,例如concat('My',null)返回null。

②replace(s,s1,s2)-替换函数，replace函数使用s2字符串代替s中所有的s1。

③left(s,n)、right(s,n)、substrain(s,n,len(n)-截取字符串一部分的函数

left(s,n)函数返回字符串s最左边n个字符

right（s,n）函数返回字符串s最右边n个字符

substring(s,n,len(n))函数返回字符串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。

<3>.数据类型转换函数

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

<4>.日期时间函数

①year(date)、month(date)、day(date)-获取年月日的函数（与excel相同）。date可以是年月日组成的日期，也可以是年月日时分秒组成的日期时间。

②date_add(date,interval expr type)-对起始时间进行加操作、date_sub(date, interval expr type)-对起始时间进行减操作--对指定起始时间进行加减操作。date用来指定起始时间，date可以是年月日组成的日期，也可以是年月日时分秒组成的日期时间。expr用来指定从起始时间添加或减去的时间间隔，type指示expr被解释的方式，type可以是以下值（主要使用红框中的值）

补：sencond秒，minute分，hour时，day天，week周，month月，quarter季，year年

例：date_add('2021-08-03 23:59:59',interval 1 second)返回2021-08-04 24:00:00

date_sub('2022-08-03 23:59:59', interval 2 month)返回2021-06-03 23:59:59

③datediff(date1,date2)--计算两个日期之间间隔的天数。datediff函数由date1-date2计算出间隔的时间，只有date的日期部分参与计算，时间不参与。

④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 16:23 PM

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

if(expr,v1,v2):如果表达式expr时true返回值v1,否则返回v2

case when：

case expr when v1 then r1[when v2 then v2]...[else rn] end：例如case 2 when 1 then 'one' when 2 then 'two' else 'more' end--case后面的值为2与第二条分支语句when后面的值相等，所以返回two。观察expr与哪一个when后面的条件相同即可。

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。

二、高级语句

1.窗口函数

标准语法：

窗口函数over([partition by 字段名][order by 字段名 asc/desc])

语法解释：over()中两个子句为可选项，partition by指定分区依据，order by指定排序依据

排序窗口函数类型：

rank()over():可重复，1，1，3，4...不连续

dense_rank()over():可重复，1，1，2，3...连续

row_number（）over():不可重复，1，2，3，4...连续

偏移分析函数类型：

lag(字段名，偏移量）over：向上偏移

lead(字段名，偏移量）over：向下偏移

补：窗口函数不能应用于where，having 的筛选，但是可以应用于order by的排序

2.表连接

基础语法：

①内连接

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.字段名

3.子查询

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

子查询最后返回查询出的结果给主查询。

子查询可以在select,from,where,having子句中使用，但要注意不同自己能接受的子查询种类有差别。

暂时就到这里啦，只包括了基础知识，没有后续云端数据库的项目，接下来通过刷题巩固一下吧。