1、汇总分析
- 汇总分析常用函数
![25d65af9565c99ebcf039caa5f644450.png](https://i-blog.csdnimg.cn/blog_migrate/d4711e9001e4b8aa8ea18e34dba027bd.png)
SQL中的函数和Excel中的函数是一样的,通过函数的功能得到输出数据。
- count的基本用法
count (*)为查找一共有多少列;count (列名)为查找该列非null值有多少列
/*
查找教师表中教师姓名有多少列
*/
select count(教师姓名)as 人数
from 教师表;
![8382de6020f8825858d127e09f2e997f.png](https://i-blog.csdnimg.cn/blog_migrate/379c1ad16497495dd9770948878724f9.png)
/*
查找教师表中有多少列
*/
select count(*)as 人数
from 教师表;
![fe4f26725b8be9113c11849d53b29740.png](https://i-blog.csdnimg.cn/blog_migrate/4781a716f28a3d4a9eb8d39ba34626c0.png)
- sum的基本用法
sum(列名),对某列的数值进行计算总值,如果存在字符型数值会导致计算结果出现错误
/*
计算表中的工资总额
分析思路
select 查询结果(工资总数,汇总函数sum)
from 哪个表(客户表)
*/
select sum(工资)
from 客户表;
![a914f72b721b7d7a181d1062b09cfdb9.png](https://i-blog.csdnimg.cn/blog_migrate/470e8ac49bb2c06a0abf9a81ea4dec27.png)
- avg的基本用法
avg(列名),求某列的平均值;同样只能对数值类型进行计算。
/*
计算表中的平均工资
分析思路
select 查询结果(平均工资,汇总函数avg)
from 哪个表(客户表)
*/
select AVG(工资)as 平均工资
from 客户表;
![a691911e033926a918283995370ccde0.png](https://i-blog.csdnimg.cn/blog_migrate/cfb6cb69bb83feeeb9713eac5eb348c2.png)
- max和min的基本用法
max(列名)min(列名),求某列的最大值或者最小值
/*
求客户表中的最高工资和最低工资
分析思路
select 查询结果(最值,汇总函数max,min)
from 哪个表(客户表)
*/
select max(工资)as 最高工资,min(工资)as 最低工资
from 客户表;
![dcfc66229e4795081f7af5a350a0f2ed.png](https://i-blog.csdnimg.cn/blog_migrate/dd80e14ad2801d06c6a00d0dd401c042.png)
- distinct在汇总函数的用法
count (distinct 列名),某列中除去重复值一共有多少列;
/*
存款表中银行标识由几个不同的
分析思路
select 查询结果(不同银行标识个数,汇总函数count,删除重复项 distinct)
FROM 哪个表(存款表)
*/
select count(DISTINCT 银行标识)
from 存款表;
![3d455833c59b67f1a73d11f99c1d8102.png](https://i-blog.csdnimg.cn/blog_migrate/fdd534e5e0319e04894f6c74feb222c3.jpeg)
练习一:
1)查询银行标识为“B0002”的存款总金额
/*
查询银行标识为“B0002”的存款总金额
分析思路
select 查询结果(存款总金额,汇总函数sum)
FROM 哪个表(存款表)
where 条件(银行标识为B0002)
*/
select sum(存款金额)
from 存款表
where 银行标识='B0002';
![dfaf4c665091708f758c81cae69e0a71.png](https://i-blog.csdnimg.cn/blog_migrate/7c4ef047b871001cf81657ebe85ac92b.png)
2)查询存款的客户人数
/*
查询存款的客户人数
分析思路
select 查询结果(人数,汇总函数count,删除重复存款的客户distinct)
FROM 哪个表(存款表)
*/
select count(distinct 客户标识)as 客户人数
from 存款表;
![94cca1afc6987e785239b72c23fab58d.png](https://i-blog.csdnimg.cn/blog_migrate/343f21b0fd5cda8e975ccfd3678e7328.png)
2、分组
group by是SQL中常用的分组函数,基本用法如下:
group by (列名),书写顺序是在where条件之后。
练习二:
1)查询客户男女人数
/*
查询客户男女人数
分析思路
select 查询结果(人数,汇总函数count)
FROM 哪个表(客户表)
group by 分组(性别)
*/
select 性别 ,count(性别)as 人数
from 客户表
GROUP BY (性别);
![b05c37ba69a578614493866946095ac8.png](https://i-blog.csdnimg.cn/blog_migrate/96325ec610813ba62a2cf3030b933c05.png)
2)查询工资数在3500以上的男女客户数
/*
查询工资数在3500以上的男女客户数
分析思路
select 查询结果(人数,汇总函数count)
FROM 哪个表(客户表)
where 条件(工资>3500)
group by 分组(性别)
*/
select 性别 ,count(性别)as 人数
from 客户表
where 工资>3500
GROUP BY (性别);
![3f9471da691d5d2e3ff495a865d5b72a.png](https://i-blog.csdnimg.cn/blog_migrate/b97cd22e23c27d330f1c42979d101357.jpeg)
3)查询各个银行存款的最高值和最低值
/*
查询各个银行存款的最高值和最低值
分析思路
select 查询结果(最值,汇总函数max,min)
FROM 哪个表(存款表)
where 条件(无)
group by 分组(银行标识)
*/
select 银行标识,max(存款金额)as 最大值,min(存款金额) as 最小值
from 存款表
group by 银行标识;
![dab39904db7a3816bf559c39ab7c7b27.png](https://i-blog.csdnimg.cn/blog_migrate/f573429568d45491e006bdf57fe7650a.jpeg)
4)查询每个银行的存款人数
/*
查询每个银行的存款人数
分析思路
select 查询结果(人数,汇总函数count)
FROM 哪个表(存款表)
where 条件(无)
group by 分组(银行标识)
*/
select 银行标识,count(DISTINCT 客户标识)as 人数
from 存款表
group by 银行标识;
![4f2f7cd2b21bf2c0115248ec9b814db5.png](https://i-blog.csdnimg.cn/blog_migrate/0f076092220afbc34f4dad239a739223.jpeg)
3、对分组结果指定条件
having是SQL中常用的条件指定语句,基本用法如下:
having (分组结果指定条件),一般用在分组函数之后;
/*
查询客户性别人数在量人以上的组
分析思路
select 查询结果(人数,汇总函数count)
FROM 哪个表(客户表)
group by 分组(性别)
having 分组条件(人数>2)
*/
select 性别 ,count(性别)as 人数
from 客户表
GROUP BY (性别)
having count(性别)>2;
![688231d81723a31e955f399c152a9840.png](https://i-blog.csdnimg.cn/blog_migrate/512847072c71e5aca5f658463ae872f4.jpeg)
练习三:
1)查询存款总额在20万以上的银行标识和存款总额
/*
查询存款总额在20万以上的银行标识和存款总额
分析思路
select 查询结果(银行标识,存款总额,汇总函数sum)
FROM 哪个表(存款表)
group by 分组(银行标识)
having 分组条件(存款总额>20万)
*/
select 银行标识,sum(存款金额)as 存款总额
from 存款表
GROUP BY 银行标识
having sum(存款金额)>200000;
![86a25c689044b72ad1c6d2cccb3855e9.png](https://i-blog.csdnimg.cn/blog_migrate/5a36fa3ec1f99c8924415ef8b4d0f469.jpeg)
2)查询银行客户数在3个以上的银行标识
/*
查询银行客户数在3个以上的银行标识
分析思路
select 查询结果(银行标识,客户数,汇总函数count,distinct删除重复客户)
FROM 哪个表(存款表)
group by 分组(银行标识)
having 分组条件(客户数3个以上)
*/
select 银行标识,count(distinct 客户标识)as 人数
from 存款表
GROUP BY 银行标识
having count(客户标识)>=3;
![ecfa6f6e070ef4a134644a8b082d8a8f.png](https://i-blog.csdnimg.cn/blog_migrate/c2074484cc447b54a9ec1fad7f14071c.jpeg)
3)查询同名同姓学生名单并统计同名人数
/*
查询同名同姓学生名单并统计同名人数
分析思路
select 查询结果(姓名,人数,汇总函数count)
FROM 哪个表(学生表)
group by 分组(姓名)
having 分组条件(重复姓名数>=2)
*/
select 姓名,count(姓名)
from 学生表
GROUP BY 姓名
having count(姓名)>=2;
![c27f5c340a4a86fd76ba246666451efc.png](https://i-blog.csdnimg.cn/blog_migrate/2ca537982887641a0fbac3fa8d7da7f4.jpeg)
4、用SQL解决业务问题
练习四:
计算每个客户的存款总额并且存款总额大于250000
/*
计算每个客户的存款总额并且存款总额大于250000
分析思路
select 查询结果(客户标识,存款总额,汇总函数sum)
FROM 哪个表(存款表)
group by 分组(客户标识)
having 分组条件(存款总额>250000)
*/
select 客户标识,sum(存款金额)as 存款总额
from 存款表
GROUP BY 客户标识
having sum(存款金额)>=250000;
![33d24edd948265f8af98e8af19cfcb6d.png](https://i-blog.csdnimg.cn/blog_migrate/3db968a27b22c21f372edbf08bcf7a22.jpeg)
5、对查询结果进行排序
order by是SQL常用的排序函数,desc为降序,asc为升序;limit对查询结果取出指定行。
![7e2e149ba85d5a4a90db92db1c8838ef.png](https://i-blog.csdnimg.cn/blog_migrate/b1fb6ba7dd8a8127609a0b138854ab1c.jpeg)
练习五:
1)查询工资高于1000的客户并按客户标识从大到小排列
/*
查询工资高于1000的客户并按客户标识从大到小排列
分析思路
select 查询结果(客户标识)
FROM 哪个表(客户表)
where 条件(工资高于1000)
group by 分组(无)
having 分组条件(无)
ORDER BY 对查询结果排序(按照客户号从大到小排序,desc)
*/
select *
from 客户表
where 工资>1000
ORDER BY 客户标识 desc;
![a1c287b67b4791660cfc39bdc251ab6b.png](https://i-blog.csdnimg.cn/blog_migrate/f01d0a2ed05f409b871e6fd76c2b56b6.jpeg)
2)查询每个客户的存款总额,结果按存款总额绩升序排序,存款总额相同时,按客户标识降序排列
/*
查询每个客户的存款总额,结果按存款总额绩升序排序,存款总额相同时,按客户标识降序排列
分析思路
select 查询结果(客户标识,存款总额,汇总函数sum)
FROM 哪个表(存款表)
where 条件(无)
group by 分组(客户标识)
having 分组条件(无)
ORDER BY 对查询结果排序(按照按存款总额绩升序排序asc,存款总额相同时,按客户标识降序排列desc)
*/
select 客户标识,sum(存款金额)as 存款总额
from 存款表
GROUP BY 客户标识
ORDER BY 存款总额 asc,客户标识 desc;
![7193161879c6dca68756e11bb409dc49.png](https://i-blog.csdnimg.cn/blog_migrate/573f09c9c75d788e4f4e33936c00bf8c.jpeg)
3)检索银行标识为“B0004”且存款金额大于50000的客户标识,结果按存款金额降序排列
/*
检索银行标识为“B0004”且存款金额大于50000的客户标识,结果按存款金额降序排列
分析思路
select 查询结果(客户标识)
FROM 哪个表(存款表)
where 条件(存款金额>50000)
group by 分组(无)
having 分组条件(无)
ORDER BY 对查询结果排序(按照按存款金额降序排列desc)
*/
select 客户标识,存款金额
from 存款表
where 银行标识='B0004' and 存款金额>50000
ORDER BY 存款金额 desc;
![9d445c070752d3506b5ef294c4730ec2.png](https://i-blog.csdnimg.cn/blog_migrate/286005db7bd74f963ed32e62aa0255ae.jpeg)
4)统计每门课程的学生选修人数(超过2人的课程才统计),要求输出课程号和选修人数,查询结果按人数降序排序,若人数相同,按课程号升序排序
/*
统计每门课程的学生选修人数(超过2人的课程才统计)
要求输出课程号和选修人数,查询结果按人数降序排序,若人数相同,按课程号升序排序
SELECT 查询结果(课程号。选修人数,汇总函数count)
from 哪个表(成绩表)
where 条件(无)
GROUP BY 分组(课程号)
having 查询条件(选修两门以上)
ORDER BY 排序(查询结果按人数降序排序,若人数相同,按课程号升序排序)
*/
select 课程号,count(学号)as 人数
from 成绩表
GROUP BY 课程号
having count(学号)>2
order by 人数 desc,课程号 asc;
![de075a7068208da306475b371a62f5de.png](https://i-blog.csdnimg.cn/blog_migrate/6e7daad64f9902604dc7f9879c0b4a7c.jpeg)
5)查询两门以上不及格课程的同学的学号及不及格课程的平均成绩
/*
查询两门以上不及格课程的同学的学号及不及格课程的平均成绩
selece 查询结果(学号,平均成绩,选修课程数)
from 成绩表
where 查询条件(成绩<60)
GROUP BY 分组(学号)
having 对分组结果指定条件(选修课程数>2)
*/
select 学号,avg(成绩)as 平均成绩,count(课程号)as 选修课程数
from 成绩表
where 成绩<60
group by 学号
having count(课程号)>2;
![405b2a3cd86509e16cdf773ac248e495.png](https://i-blog.csdnimg.cn/blog_migrate/54fb95293756e2477656f1ffd8e82386.jpeg)
练习六:
SQLzoo的练习截图
![e639babfcfe3f23332fddc71ca00ad89.png](https://i-blog.csdnimg.cn/blog_migrate/78878247d4c30ac2042c38f4229a91ae.jpeg)
![9e8193bb0ed61eb3e4f1a72570ac675d.png](https://i-blog.csdnimg.cn/blog_migrate/61ae652d7993dcbab7b9d2dbba820223.jpeg)
![0ce3461ea722c578972611f1b239e0c2.png](https://i-blog.csdnimg.cn/blog_migrate/80a9e0c926c4ae4a4b5cab7b0e0d8eb6.jpeg)
![ba21200b890278d84c9bd3491917e68b.png](https://i-blog.csdnimg.cn/blog_migrate/e265fc2079de30acf9bb8707960062e5.jpeg)
![dee833ab313d0ded093676a2569a0dde.png](https://i-blog.csdnimg.cn/blog_migrate/f237af8c67dcf667c7de9910b2b6b084.jpeg)
![917a2567f6d1ca04bc50bd8eb38c8572.png](https://i-blog.csdnimg.cn/blog_migrate/82c183014daeba86b79395d44b7c80eb.jpeg)
![66df1922b3c7efd5dad512c1a8aa54e2.png](https://i-blog.csdnimg.cn/blog_migrate/faabb58a3617f3eaff908ec73f80f4dc.jpeg)
![b2fbd0036d745fc4911739de614a6d36.png](https://i-blog.csdnimg.cn/blog_migrate/6a7468cb617b3babdd0e9e7c98998cf5.jpeg)
![bc97a8985347a0a69dcf0c0c43d8d9bf.png](https://i-blog.csdnimg.cn/blog_migrate/787a9e4f421c7122564c941913f9e87f.jpeg)
![f3b63adc7f7a4ba112ad06f8e6491641.png](https://i-blog.csdnimg.cn/blog_migrate/98c72d6cc15e4d5b7aa5143c6163818f.jpeg)
![0ae4b668bf5a20f75818037899a05b16.png](https://i-blog.csdnimg.cn/blog_migrate/b976eb9a5b33cc4da8955cf035f27125.jpeg)
![213d3b36e638868a25c566a6f79129b6.png](https://i-blog.csdnimg.cn/blog_migrate/e44c9b768f4f70e5eb5d5955b6aff2a0.jpeg)
![b451b2f929e6e71fde27d4c4bbe57925.png](https://i-blog.csdnimg.cn/blog_migrate/097356d2c47c4644a2de1cc34acea938.jpeg)
![60fe8a94fec9d38c6f2e38197da6ac7e.png](https://i-blog.csdnimg.cn/blog_migrate/9f0e70a8fd3ce8bccf42600731698696.jpeg)