SQL知识点汇总

sql语法结构：select-from-where-group by-having-order by-limit

SQL整体语句的逻辑理解如下：

from语句为选择要查询的表格：

where语句：针对 未进行分组聚合的 行数据进行筛选，选择出满足条件的行数据，优先级最高

group by 语句：对表格中的数据 依据指定的字段进行不断 分组，聚合

having语句：对group by 聚合之后所得到的所有分组，进行筛选，从所有分组中选出满足条件的分组

order by语句：对筛选、分组 这些操作之后所得到的数据进行排序

limit 语句：显示出哪些行的数据

待上述所有语句执行完之后，select再从结果中选出需要的结果。select为最后进行的，作用是选择展示哪些字段，或者计算哪些字段

where语句：

作用：条件匹配

主要有：运算符匹配、模糊匹配以及多条件匹配

between and 函数的用法：

如果不想包含边界值，那么就运用多条件进行处理

比如：  Brazil的人口为202794000，而China的人口为1365370000

如果不想包括China的人口的话，代码如下：

SELECT name, population 
from world
WHERE population between 202794000 and 1365370000
and population != 1365370000

in函数

select name,area
from world
where name ='Germany' or name = 'China'

运用多条件同样可以得到一样的结果。

因为in函数这个函数的本质上就是多个 or 进行组合所得到

模糊查询：like函数

第一种通配符：%

作用：任意字段出现任意次数，可用 % 进行代替

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

第二种通配符（也叫占位符）：_

注意：这是下划线_ 而不是中文格式下的 -

作用：任意字符只出现一次

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

结果：输出Ethiopia和Italy这两个国家

多条件查询：

语法：使用and 或 or 对多个条件进行组合筛选

eg：查询国家名字中含有3个a且面积大于60万的国家以及面积

做这道题目时产生了一个有关通配符的误区：% 一定代表了一些字符，不可能为空

所以会产生一些奇怪的思路，比如：name like '%aaa%' and name like '%a%aa%' 这种

正确思路：

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

%本身也能够代表什么都没有。比如结果中就有：Canada，Australia

再比如例子：

查询国家名字中含有3个a且面积大于60万的国家以及面积    或者   人口数量大于13亿且面积大于960万的国家及其面积

select name,area
from world
where name like '%a%a%a%' and area > 600000 
or population > 1300000000 and area > 960000

and的优先级高于or，为了好看好区分一些，可以手动加上一些括号

此时结果就会多一个China

如果要求：两个条件同时满足时就要将该字段排除在外，此时该怎么弄？

比如中国人口大于13亿，且面积大于60万，这两个条件都满足了，而我只需要选择出满足一个条件的国家，此时如何操作？

select name, population, area
from world
where area > 3000000 xor population > 250000000

order by 语句：

作用：排序

语法：order by 字段句 asc| desc

order by 的核心子句是

例如：将诺贝尔奖中姓名以 ‘ Sir ’ 开头的获奖者信息，并且将信息先按照年份由近到远排序，然后再按照姓名升序排列

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

注意：排序字段使用 逗号 隔开排序的关键词

新用法：

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

红色字体是难点，涉及到了新用法

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

上述语句会首先判断 有没有哪些subject在 ( ) 里面，如果有，那么就会记为0或1，然后再进行排序。

这儿subject中的'chemistry','physics'都在集合中，因此会依次记录1，而不在集合里面的则会被记为0，然后按照默认的0和1进行排序。

limit用法：

作用：限制查询结果展示多少行

语法：limit 位置偏移量, 行数

相关知识点：

1.limit n；n表示第n行，查询结果为第1行到第n行。

2.limit n，k；这个语法表示的是，从第n+1行开始，k行的数据

（要注意，并不是从第n行开始；例如limit 3，2。表示意思是，返回 4，5两行的数据，而并不是3，4这两行的数据）

3.limit一定是放在最后一行

4.limit语句仅仅只能在MySQL中使用

例如：查询面积前三的国家

select name
from world
order by area desc
limit 3

这儿直接  limit 3 就可以了，并不是  name, 3  这样

例题：查询人口数在第4到第7的国家和人口

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

注意理解下 limit 3, 4 的意思。

需要注意的是：并非是从第3行开始然后返回之后的4行，也即是说，并不是返回3，4，5，6行。而是，从第3行之后开始的4行，也即是4，5，6，7这4行！注意和python这些区分开

聚合函数以及group by：

作用：将数据按照一定的字段进行分组聚合

语法：group by 字段名

聚合函数就是指：avg()  count()  sum()  max() min()这些函数

通常，聚合函数会和group by 函数结合起来使用，而不会和where结合使用，因为where子句所作用的对象只是行，更多是起到一种过滤数据的作用

单独使用聚合函数：

例如：计算非洲的总人口数

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

例如：计算表格中一共有多少数据

select count(*)
from world

count()函数相关知识点

1. count(*) 和count(字段名)之间有所区别。

count(*)会计算表格中的总行数，而count(字段名) 则只会对一个字段名进行计数，如果该字段中某些行的值为空值，那么计算时会忽略空值的行。

所以，count(*)和count(字段名)所得到的结果不一定相同。

同时，avg()  sum()这两个函数也会忽略空值行

单独使用group by：

group by的原理：

先将该字段句中相同的数据放到一起，可以理解为 ” 分区“ ，

再将” 分区 “后各个区的名称取出来，相当于去重。

例如：对大洲进行分组

select continent
from world
group by continent

结果就会返回  大洲 这一列中有哪些数据。

结果和去重后得到的结果一样，说明group by也有去重的操作。

聚合函数和group by 结合使用：

例题：查询每个大洲和大洲内的国家数量

select continent,count(name)
from world
group by continent
查询得到的结果是：每个州

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

select yr, subject, count(winner)
from nobel
where yr between 2013 and 2015
group by yr, subject
order by yr desc, count(winner) desc

注意分析清楚：每年每个科目  的意思，这代表的就是分组

此外，要注意  where 和group by 两个语句之间的优先级，语法上是优先where？还是group by？

自己在这儿犯过错误。

group by注意事项：

使用group by子句时，select只能使用聚合函数和group by引用过的字段，否则会报错

group by 理解起来就像：大括号{  里面可以包含多个小括号{， 就像思维导图的样式一样，一直分区下去（也有些像是分类）

主知识点六：having 和 简单运行原理

having的作用是：聚合后筛选，也即是group by 分组或者聚合之后，查询行所必须要满足的条件。

理解起来就是：将所有的分组进行筛选，选出满足条件的分组

例如：查询总人口数量至少为1亿的大洲

分析题目：在分组选择出大洲以后，再从中筛选出人口数量1亿的

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

需要注意的是，having只能是使用了group by之后才能够再使用having，因为having是对分组之后的数据进行条件筛选。

此外需要注意的是，having的优先级高于order by 和 limit

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

这题要好好分析下题目：要分清楚哪部分属于聚合前，哪部分属于聚合后

第一个条件：‘查询总人口数量至少为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：部分常见函数

主要有如下几类函数：

数学类函数、字符串函数、数据类型转换函数、日期时间函数、条件判断函数

数学函数：

round(x, y)——四舍五入函数；x表示四舍五入的数值，y表示小数点后多少位

字符串函数：

concat(s1, s2, s3...)——连接字符串函数

concat函数返回连接参数s1,s2等产生的字符串；若有任意一个参数为null 空值的时候，结果都会返回null。

例题：

查询首都和名称，其中首都是国家名称的拓展。比如Mexico的首都是 Mexico City

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

初次做时想到了concat和通配符，但是没想到该如何与模糊匹配对应起来。

这儿，name作为一个变量，是时刻都在发生变化的，一直都是国家的名字，再加上两个符号组成字符串，再将该字符串组成模糊匹配的字符串

replace(s, s1, s2)——s表示要进行处理的字符串，s1表示需要处理替换的  旧字符串，s2表示新字符串。作用便是：用s2字符串 替换掉  所有的 s1字符串

left(s,n)  right(s,n)  substring(s,n,len)

作用：前两个函数表示是提取字符串 s 左右两边的n个字符串

substring函数作用在于：对字符串s，从第n个字符起，取len个长度的字符

例如：

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

分析：筛选条件是：国家名称及其首都都以相同的字母开头。

思考以后不难发现：left函数刚好可以获取开头字母

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

 

条件判断函数：

if(expr, v1, v2)函数：如果表达式expr是true，返回v1，否则返回v2

case when函数：

有两种表示方法

第一种表示方法：

case expr when v1 then r1  when v2 then r2  end

解释：case 表示条件，expr就是条件的具体形式；when v1 then r1 的解读方式为：

当expr等于v1这个值的时候， then 然后返回 r1 这个数值；

当expr 等于v2这个值的时候，then 然后返回 r2 这个数值

比如：case 2 when 1 then ' one ' when 2 then ' two '  end

返回的结果会是2

第二种表示方法：

case when v1 then r1 when v2 then r2... end

这个语句的意思就是，只要满足后面对应的语句，就返回相应的值

例如：

当获得个国家的疫情数据时，通常会做如下处理：若累计感染人数大于11，等级为 I；当感染人数大于10时，等级为II；当感染人数大于100时，等级为III...

select province, number case when (number >= 1 and number < 10) then 'I' when (number >= 10 and number <100) then 'II' end

from covid

在这个语句中犯过很多错误：

第一点：(number >= 1 and number < 10) 这个判断语句的语法使用错误，和python搞混了；

之前错误的表示为：10 > number >= 1，出现语法错误

第二点，case when 语句本身就和count()函数一样，得到的结果单独作为一列进行展示，所以可以在end 后面运用中文直接命名，而不需要进行其他的操作

时间函数：

year month day函数