SQL 语句聚合函数

2021-02-16 | 129 浏览

SQL语句聚合函数

1.GROUP BY + 聚合函数

1.常见聚合函数

SUM()    ---->统计总和
AVG()    ---->统计平均值
MAX()    ---->统计最大值
MIN()    ---->统计最小值
COUNT()  ---->统计数量

2.GROUP BY + 聚合函数公式：

1.遇到统计想函数
2.形容词前 GROUP BY
3.函数中央是名词
4.列名select后添加

3.例子:

统计世界上每个国家的总人口数.

SELECT countrycode,SUM(population) 
FROM   world.`city`
GROUP BY countrycode ;

统计中国各个省的总人口数量

SELECT  countrycode, district, SUM(population)   
FROM   world.city 
WHERE countrycode='CHN'
GROUP BY  district;

统计世界上每个国家的城市数量

SELECT  countrycode,  COUNT(id)
FROM  world.city
GROUP BY countrycode ;

2.AS别名语法

1.表别名

SELECT a.countrycode AS '国家' , 
a.district AS '省' , 
AVG(a.population) AS '平均人口'
FROM   world.city AS a
WHERE a.countrycode='CHN'
GROUP BY  a.district;

2.表别名限制:

1. 只能在一条语句中使用
2. 表别名一旦设置,所有列调用时,都要加上别名前缀

3.列别名:

SELECT a.countrycode AS '国家' , 
a.district AS '省' , 
AVG(a.population) AS '平均人口'
FROM   world.city AS a
WHERE a.countrycode='CHN'
GROUP BY  a.district;

SELECT a.countrycode , 
a.district , 
AVG(a.population) AS avg_p
FROM   world.city AS a
WHERE a.countrycode='CHN'
GROUP BY  a.district;

4.别名练习
1.统计中国各个省的平均人口数,显示结果是大于平均人口数的城市信息

SELECT 
a.name AS '城市名', 
a.population AS ap,
a.countrycode AS '国家' , 
a.district AS '省' , 
AVG(a.population) AS '平均人口'
FROM   world.city AS a
WHERE a.countrycode='CHN'
GROUP BY  a.district
HAVING ap > '平均人口';

SELECT 
any_value(a.population) AS avp,
any_value(a.countrycode) AS avc, 
a.district AS ad , 
AVG(a.population) AS avg_p
FROM   world.city AS a
WHERE a.countrycode='CHN'
GROUP BY  ad
HAVING avp > avg_p;


set session  sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';   
set global   sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

3.limit分页

11-15名
LIMIT 10,5
LIMIT 5  OFFSET 10
1.示例：查询中国人口数量排名前十的城市信息
SELECT * FROM city 
WHERE countrycode='CHN'
ORDER BY population DESC
LIMIT 10

4.FROM子句

1.语法:
SELECT    列1,列2,列3    FROM    库.表
2.查看表中的所有数据(生产不要出现这条语句)
SELECT   *  FROM   world.city;  
3.查询全世界上所有城市的名字+国家代号
SELECT NAME,countrycode FROM world.city;

5.where子句的使用

1.WHERE + 等值查询(=)
SELECT    列1,列2,列3    FROM    库.表   WHERE   列 = '值'
2.查询中国所有的城市名
SELECT NAME FROM world.city WHERE countrycode='CHN';
3.WHERE + 不等值查询(> < >= <= <>)
SELECT    列1,列2,列3    FROM    库.表   WHERE   列 < '值'
查询世界上,人口数小于100人的城市信息
SELECT * FROM world.city WHERE population < 100;
注意:
1.除了 <> 或 != ,都属于范围查询,主键除外.
2.对于辅助(普通)索引列查询,避免使用<> 或 != 
4.WHERE + 逻辑连接符 ( AND , OR)

查询中国人口数大于500w的城市信息
SELECT id, NAME ,countrycode,population
FROM world.city 
WHERE countrycode='CHN' AND population > 5000000;
查询中国吉林省的城市信息(练习)
SELECT * FROM world.city 
WHERE countrycode='CHN' AND district='jilin';
查询中国和美国的城市信息
SELECT * FROM world.`city`
WHERE countrycode ='CHN' OR countrycode='USA';
5.WHERE + IN 查询中国和美国的城市信息
SELECT * FROM world.`city`
WHERE countrycode IN ('CHN' ,'USA');
说明:
OR 和 IN 属于范围查询,我们一般替换为 UNION 或者 UNION ALL

6.WHERE + BETWEEN AND

查询城市人口500w-600w的城市信息
SELECT * FROM world.city 
WHERE population>5000000 AND population<6000000;

SELECT * FROM world.city 
WHERE population BETWEEN 5000000 AND 6000000;
7.WHERE + LIKE 模糊查询

查询国家代号为CH打头的城市信息
SELECT * FROM world.city WHERE countrycode LIKE 'CH%';
注意:
LIKE '%aa%'  不要出现
搜索框类的应用,就是典型的%aa%,建议不要使用MySQL,使用ES

6.select union all语句执行顺序

select 
from (sub_select)
join (sub_select)
on
where (sub_select)
group by 
having
order by 
desc/asc
limit 

union [all]

select 
from (sub_select)
join (sub_select)
on
where (sub_select)
group by 
having
order by 
desc/asc
limit 

union all

select 
from (sub_select)
join (sub_select)
on
where (sub_select)
group by 
having
order by 
desc/asc
limit