SQL学习笔记1

本期分享跟随戴戴戴师兄的个人空间-戴戴戴师兄个人主页-哔哩哔哩视频学习SQL的笔记，在sqlzoo上进行练习，并将其记录下来。

<world>表基本信息如下所示：

<nobel>表基本信息如下所示：

<covid>表基本信息如下所示：

知识点一：select&from

1.1 简单查询

SELECT name, continent, population FROM world
SELECT * FROM world
SELECT name as 国家名, continent 大洲, population 人口 FROM world

1.2 select中使用distinct去重

select distinct continent from world
select distinct name,continent from world
select name,distinct continent name from world #报错

1.3 select中计算字段的运用

select name,gdp,population,gdp/population 人均gdp from world

知识点二：where

2.1 运算符

例题1：查询人口数至少2亿的国家名和人均gdp

select name, gdp/population 人均gdp from world
where population >= 200000000

例题2：查询德国（Germany）的人口

select population from world where name = 'Germany'

例题3：查询瑞典(Sweden)、挪威 (Norway) 和丹麦 (Denmark)的国家名和人口

select name,population from world
where name in ('Sweden','Norway','Denmark')

例题4：查询面积(area)在250000和300000之间的国家名和面积

select name, area from world
where area between 250000 and 300000

2.2 模糊查询like

例题5：查询国家名中以C开头ia结尾的国家

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

例题6：查询国家名中第二个字符为't'的国家

select name from world where name like '_t%'

例题7：查询国家名中含有两个o且被两个字符隔开的国家名

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

2.3 多条件查询

例题8：查询国家名中含有三个a且面积大于60万(600000)的国家及其面积

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

例题9：查询国家名中含有三个a且面积大于60万(600000)的国家及其面积，或者人口大于13亿(1300000000)且面积大于500万(5000000)的国家及其面积

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

知识点三：order by

语法：select 字段名 from 表名 [where 表达式] [order by 字段名 asc|desc]

注：asc指定该字段升序排序，desc为降序排序，不写则默认为升序排序

例题10：查询姓名以Sir开头的获奖者(winner)，获奖年份(yr)和科目(subject)，查询结果按照年份从近到远排序，再按照姓名顺序升序排序

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

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

order by subject in ('chemistry','physics') , subject, winner

subject in (‘*’) 在括号里面的记为“1”，不在记为“0”，然后order by默认升序。

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

知识点四：limit

语法：select 字段名 from 表名 [where 表达式] [order by 字段名 asc|desc] [limit [位置偏移量,]行数]

注：

limit [位置偏移量,]行数 限制查询结果集显示的行数；

limit子句是可选项，行数是子句中的必选参数，参数位置偏移量是可选参数

例题12：查询面积排名前三的国家

select name from world order by area desc limit 3

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

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

例题14：查询nobel表中第100行到120行的数据

select * from nobel limit 99,21

知识点五：聚合函数&group by

语法：select 字段名 from 表名 [where 表达式] [group by 字段名1] [order by 字段名 asc|desc] [limit [位置偏移量,]行数]

注：

group by 字段名 规定依据哪个字段分组聚合；

group by核心子句是可选项，使用该子句是为了依据相同字段值分组后进行聚合运算，常和聚合函数联用

5.1 单独使用聚合函数

例题15：查询非洲总人口数

select sum(population) from world where continent = 'Africa'

将sum函数依次修改为avg()、max()、min()，依次计算平均人口数、最大人口数、最小人口数

例题16：计算表格行数

count()、avg()、max()、min()忽略空值行。

select count(*) from world

5.2 单独使用group by

例题17：用group by 对大洲进行分组

group by：先分区，在分组（去重）

select continent from world group by continent

5.3 聚合函数和group by联用1

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

注：运行顺序理解：order by --> limit --> select

select yr, subject, count(winner) sum_winner from nobel
where yr in (2013,2014,2015)
group by yr, subject
order by yr desc, sum_winner desc

例题19：计算Estonia, Latvia, Lithuania这几个国家的总人口数

select sum(population) 总人口 from world 
where name in ('Estonia','Latvia','Lithuania')

例题20：查询每个大洲和该大洲里人口数超过1千万的国家的数量

select continent, 
count(if(population <= 10000000,0,1)) sum_name 
from world
group by continent

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

语法：select 字段名 from 表名 [where 表达式] [group by 字段名1] [having 表达式] [order by 字段名 asc|desc] [limit [位置偏移量,]行数]

注：

having 表达式 限定分组聚合后的查询行必须满足的条件

having核心子句是可选项，使用该子句是为了对group by分组后的数据进行筛选

运行顺序：from--where--group by--having--order by--limit--select

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

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

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

select continent, sum(gdp)/sum(population) 人均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

知识点七：部分常见函数

【数学函数】

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

【字符串函数】

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

        concat函数返回连接参数s1、s2等产生的字符串

        任一参数为null时，则返回null

replace(s,s1,s2)——替换函数

        replace函数使用字符串s2代替s中所有的s1

        例如：replace('MySQLMySQL','SQL','sql')返回MysqlMysql

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)：如果表达式expr是true返回值v1，否则返回v2

        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 when v1 then r1 [when v2 then r2]...[else rn] end

                        例如：case when 1<0 then 'T' else 'F' end返回F

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

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

例题24：查询首都和名称，其中首都需是国家名称的扩展

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

例题25：若累计治愈人数为1，返回“one”；大于1，返回“more”；其他情况返回"0"

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

例题26：提取更新时间（whn）的年月日

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

例题27：给更新时间（whn）添加两天

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

心得体会

在此之前已经自学过《SQL必知必会》（第5版），也在牛客上刷过一些习题。但是自学确实有一些知识点无法完全理解，通过戴戴戴师兄的个人空间-戴戴戴师兄个人主页-哔哩哔哩视频，我对SQL的执行顺序、group by的使用、循环语句和条件语句的使用等更有了深一步的理解，算是温故而知新。