目录

3.order by

4.limit

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

5.1单独使用聚合函数

5.2单独使用group by

5.3聚合函数和group by联用

5.4 练习

6.having&简单运行原理

7.SQL运行原理总结

---

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