一、SQL练习公开网站sqlzoo网址:SELECT from WORLD Tutorial - SQLZoo
使用英文和Mysql
二、基础语句
1.select&from查询
1)标准语法:select 字段名
from 表名
2)语法解释:select决定查哪个字段,from指定查询哪段数据
3)在select后面加入distinct去重
4)select中可以直接进行字段计算(字段必须是数值)
5)例:从world表里查询name、continent字段并进行字段计算人均gdp=gdp/人口,依次以别名国家名、大洲、人均gdp显示,只要查询出来的三列数据三个字段全部一样,则去重
2.where筛选
1)标准语法:select 字段名
from 表名
where 筛选条件
2)运算符一览
注:为空值应写为:='null'
3)模糊查询like
Where 字段名 like '通配符+字符'
4)例子:
从world表里查询人口不为空且人口在2亿到3亿之间且国家名为Brazil且name以B开头,il结尾的国家,显示名称
3.order by排序
1)标准语法:select 字段名
from 表名
order by 字段名 asc|desc,字段 asc|desc
(asc升序,desc降序)
2)特殊用法:
Order by 字段名 in('数据','数据'), asc
在括号范围内的数据将会被看做1,其余为0,升序01排序
3)例:
从world表里查询name、continent字段并进行字段计算人均gdp=gdp/人口,依次以别名国家名、大洲、人均gdp显示,只要查询出来的三列数据三个字段全部一样,则去重,然后根据人均GDP进行降序排序,对人均GDP相同的数据按照name升序排序
4.limit限制
1)标准语法:select 字段名
from 表名
limit 位置偏移量(可选),行数
2)解释:
Limit x , n
返回查询结果的x+1行到n行
3)注意,limit只能在MySQL数据库中使用,limit语句写在整个查询语句的最后一行
5.聚合函数&group by分组
1)聚合函数
注:查询数据表行数时用select count(*),速度最快,最准确
2)标准语法:select 字段名
from 表名
group by 字段名
3)语法解释:规定依据哪个字段进行分组聚合
group by常与聚合函数连用
4)差别:虽然group by和distinct都可以对数据去重,但是二者逻辑不同,distinct仅返回不同行,group by本质是先对指定的字段中相同的值分为一个区,然后再对字段进行去重分组并返回组
5)例:
在world表里将continent聚合,分组,查询并显示continent和每个continent里面的name数量
6.having聚合后筛选
1)having是为了对group by分组后的数据进行筛选
2)标准语法:select 字段名
from 表名
group by 字段名
having 表达式
3)差异:where是聚合前筛选,having是聚合后筛选
4)注:having后只能使用聚合函数和group by作为分组依据的字段
5)例:
在world表中将大洲聚合分组,查询总人口数大于一亿的大洲并显示
7.综合示例:
查询条件:查询总人口数至少为3亿的大洲和其平均gdp,其中只有gdp高于200亿且人口数大于6000万或者gdp低于80亿且首都中含有三个a的国家的计入计算,最后按国家数从大到小排序,只显示第一行:
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 0,1;
8.部分常见函数:
1)round(x , y)——四舍五入
round函数对x值进行四舍五入,精确到小数点后y位,y为负值时,保留小数点左边相应的位数为0,不进行四舍五入
2)replace(s , s1 , s2)——替换函数
在s字符串中使用字符串s2代替s中所有的s1
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个字符起到最后一位
4)cast(x as type)——转换数据类型
cast函数将一个类型的x值转换为另一个类型的值,type参数可以写char(n)、date、time、datetime、decimal等转换为对应的数据类型
5)year(date)、month(date)、day(date)——获取年月日
date可以是年月日组成的日期,也可以是年月日时分秒组成的日期时间
year(date)返回日期格式中的年份
month(date)返回日期格式中的月份
day(date)返回年日期格式中的日份
6)date_add/sub(date,interval expr type)——对指定起始时间进行加减
date用来指定起始时间
date可以是年月日组成的日期,也可以是年月日时分秒组成的日期时间
interval是函数组成,照搬就行
expr用来指定从起始时间添加或减去的时间间隔
type是expr的单位,如year、month、second等
7)datediff(date1,date2)——计算两个日期之间间隔
datediff函数由date1-date2计算出间隔的时间,只有date的年月日部分参与计算,时分秒不参与
8)date_format(date,format)——将日期和时间格式化
date_format函数根据format指定的格式显示date值
格式如下:
9)if(expr , v1 , v2)——条件判断
如果表达式expr是true返回值v1,否则返回v2
10)case when ——case判断
标准格式:case expr when v1 then r1 when v2 then r2 else
r3 end
如果case后面的值(expr)为v1,则进行r1操作,为v2,则进行r2操作,都不是,则进行r3操作,执行操作完后结束
三、高级语句
1.窗口函数
1)标准语法:select 字段名
<窗口函数> over(partition by 字段名 order by 字段名 asc/desc) 别名
from 表名
2)语法解释:partion by根据什么分区
order by在各个分区内根据什么排序
3)差别:
group by分组汇总后改变了表的行数,一行只有一个类别
而窗口函数不会减少原表中的行数,例如:
4)专用窗口函数:
rank() over(。。。)
dense_rank() over(。。。)
row_number() over(。。。)
三者区别:rank函数出现并列情况时,并列的第二行会占用下一名次
dense_rank函数并列时不占用下一名次
row_number函数不考虑并列情况
例如在班级内按成绩排序时:
select 学号,班级,成绩
rank()(或另外俩)over(partition by 班级 order by 成绩 )
from 成绩单
5)聚合窗口函数
1)用法:与专用窗口函数相同,但是函数后括号内不能为空
2)注:聚合窗口函数每一行是对自身记录、及自身记录以上的数据进行聚合计算的结果,比如0004号,在使用sun窗口函数后的结果,是对0001,0002,0003,0004号的成绩求和,若是0005号,则结果是0001号~0005号成绩的求和,以此类推。
3)例如:
select 学号,班级,成绩
sum(成绩) over (order by 学号) as current_sum
from 成绩表
6)偏移分析函数
1)lag(字段名,偏移量[,默认值])over() :统计分组内向上第n行的值
lead(字段名,偏移量[,默认值])over():统计分组内向下第n行的值
2)偏移量指往上/下多少行,默认值指往上/下多少行为NULL时取默认值,如不指定,则为NULL
2.表连接
1)内连接 inner join
左连接 left join
右连接 right join
2)语法: select 字段名
from 表名1
inner/left/right join 表名2
on 表名1.字段名 = 表名2.字段名
注:join前不加inner、left、right默认为内连接
3)语法解释:
3.子查询
1)语义解释:子查询本身就是一段完整的查询语句,然后用括号英文括号()包裹嵌套在主查询语句中,子查询可以多层嵌套
2)子查询优先于主查询
3)例:
标签:数据分析,语句,函数,查询,字段名,gdp,Sql,date,select From: https://blog.csdn.net/weixin_63441443/article/details/142371238