继续SQL

主知识点六：having

聚合前的筛选用where，聚合后的筛选用having

Having和where的区别是：运行顺序和对象不用

Having是在group by聚合后的基础上进行筛选。

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

分析：

（1）查询的是大洲和其平均gdp

（2）where限制是总人口数至少为3亿

（3）只有gdp高于200亿且人口数大于6000万

或者

gdp低于80亿且首都中含有三个a的国家的计入计算

（4）最后按国家数从大到小排序，order by … desc

（5）只显示第一行 limit 1

我的代码：

select continent,avg(gdp)

from world

where gdp>20000000000 and population>60000000

or gdp <8000000000 and name like '%a%a%a'  //首都名，不是国家名

group by continent

having sum(population) >=300000000   //总人数，sum不要忘了

order by count(name) desc

limit 1

师兄代码：

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

总结：where在group by前，having在group by后

●   【SQL运行原理】from--where--group by--having--order by--limit—select

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

分析：

（1）查询大洲及其人口数

（2）限制是人均gdp大于3000——having

（3）仅gdp在200亿和300亿之间的国家计入计算（分组）。——where

我的代码：

select continent,population   // 大洲的人口数=洲内各国人口数之和，sum(population)

from world

where gdp between 20000000000 and 30000000000

group by continent

having (gdp/population)>3000  //不对，导致结果错误，是洲内！不是国，所以要求和

正确代码：

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   //洲人均GDP

运行结果：

主知识点七：常见函数

●   【数学函数】

round(x,y)——四舍五入函数

round函数对x值进行四舍五入，精确到小数点后y位

y为负值时，保留小数点左边相应的位数为0，不进行四舍五入

例如：round(3.15,1)返回3.2，round(14.15,-1)返回10

●   【字符串函数】

       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

●   【数据类型转换函数】

       cast(x as type)——转换数据类型的函数

       cast函数将一个类型的x值转换为另一个类型的值

       type参数可以填写char(n)、date、time、datetime、decimal等转换为对应的数据类型

●   【日期时间函数】

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

●   【条件判断函数】——根据满足不同条件，执行相应流程

       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

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

分析：

（1）查询国家名及其首都

（2）限制是：国家名称及其首都名称都以相同的字母开头（首字母相同）——where 左边第一个字母相等

（3）且不能包括国家名称和首都名称完全相同的情况——！=

代码：

select name,capital

from world

where left(name,1)=left(capital,1)

and name != capital

●   【题目】查询首都和国家名称，其中首都需是国家名称的扩展

分析：

（1）查询首都和国家名称 

（2）首都=国家名称+a%??——利用concat函数！例如：concat('My',' ','SQL')返回My SQL

代码：

selectcapital,name

from world

where capital like concat('%',name,'%')

and capital != name;