数据分析SQL | order by, limit, 聚合函数&group by, having 和SQL的运行原理

dacan18

1635人浏览 · 2025-04-03 21:35:09

dacan18 · 2025-04-03 21:35:09 发布

3.order by

4.limit

5.聚合函数&group by【掌握薄弱】

3.order by

select 字段名
from 表名
[where 表达式]
[order by 字段名 asc|desc]
asc升序，desc降序【默认升序】

//查询姓名以Sir开头的获奖者winner，获奖年份yr和科目subject，查询结果按照年份从远到近排序，再按照姓名顺序升序排序
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

4.limit

标准语法

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

语法解释

limit [位置偏移量,] 行数 ->限制查询结果集显示的行数
limit子句为可选项，行数为必选参数，位置偏移量为可选参数

//查询面积排名前三的国家
SELECT name 
FROM world
ORDER BY area DESC
LIMIT 3

//查询人口数第4到第7的国家和人口
SELECT name, population
FROM world
ORDER BY population DESC
LIMIT 3,4

//查询nobel表中第100行到第120行的数据
SELECT *
FROM nobel
LIMIT 99,21

查询结果返回前n行

select 字段名
from 表名
[where 表达式]
[order by 字段名 asc|desc]
[limit n]

查询结果返回第x+1行开始的n行

select 字段名
from 表名
[where 表达式]
[order by 字段名 asc|desc]
[limit x,n]

5.聚合函数&group by【掌握薄弱】

聚合函数：适用于需要获取数据的汇总信息，例如某字段行数、某字段平均值、某字段中最大最小数等【忽略空值】

AVG() 返回某列的均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列的和

标准语法

select 字段名1,聚合函数（字段名）
from 表名
[where 表达式]
[group by 字段名1]
[order by 字段名 asc|desc]
[limit[位置偏移量,]行数]

注意事项

group by 子句中有多字段时，依据写的字段顺序依次对数据分区，因此group by 字段1，字段2 与 group by 字段2，字段1 不一样
使用group by子句时，select和order by只能使用聚合函数和group by引用过的字段，否则会报错

5.1单独使用聚合函数

//查询非洲总人口数
SELECT sum(population)
FROM world
WHERE continent='Africa'

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

SELECT max(population)
FROM world

SELECT min(population)
FROM world

//计算表格行数
SELECT count(*)
FROM world

//运行以下代码
SELECT count(name),count(continent),count(area),count(population),count(gdp),count(*)
FROM world

//运行以下代码
SELECT SUM(gdp)/195,SUM(gdp)/192,AVG(gdp)
FROM world

5.2单独使用group by

//对大洲进行分组
SELECT continent
FROM world
GROUP BY continent

5.3聚合函数和group by联用

//查询每个大洲continent和大洲内的国家name数量
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

5.4 练习

//计算Estonia,Latvia,Lithuania这几个国家的总人口数
SELECT SUM(population)
FROM world
WHERE name IN ('Estonia','Latvia','Lithuania')

//查询每个大洲和该大洲里人口数超过1千万的国家的数量
SELECT continent,count(name) 'number of countries'
FROM world
WHERE population >=10000000
GROUP BY continent //对大洲进行分组

6.having&简单运行原理

标准语法

select 字段名1,聚合函数（字段名）
from 表名
[where 表达式]
[group by 字段名1]
[having 表达式]
[order by 字段名 asc|desc]
[limit[位置偏移量,]行数]

语法解释

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

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

//查询总人口至少为1亿的大洲
SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population)>=100000000

//（运行原理，非常重要，好好理解）查询总人口数至少为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.SQL运行原理总结

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

执行from语句从数据库中调取复制一份表格
执行where语句在复制的表格中筛选出符合条件的数据行
执行group by语句依据指定字段对筛选后的数据分区，将依据的字段去重分组，相当于Excel建立了一个数据透视表，添加了行标签
执行having语句筛选满足条件的分组
执行order by语句对筛选后的数据进行排序
执行limit语句对排序后的数据限制显示的行
执行select语句，提取最后要显示的字段

//查询人均gdp大于3000的大洲及其人口数，仅gdp在200亿和300亿之间的国家计入计算；注意使用sum
SELECT continent,SUM(population) 人口数,SUM(gdp)/SUM(population) 人均gdp
FROM world
WHERE gdp BETWEEN 20000000000 AND 30000000000
GROUP BY continent
HAVIN SUM(gdp)/SUM(population) >3000