count 求某列的行数
sum 对某列数据求和
avg 求某列数据的平均值
max 求某列数据的最大值
min 求某列数据的最小值
一.汇总分析
计数函数count:
![v2-29b5d93bd50b5af85c7fa494167c9877_b.jpg](http://img-01.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic4.zhimg.com/v2-29b5d93bd50b5af85c7fa494167c9877_b.jpg)
![v2-eb9e18ee3adbf2350246c694bb3f5382_b.jpg](http://img-03.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic3.zhimg.com/v2-eb9e18ee3adbf2350246c694bb3f5382_b.jpg)
*注 如果count函数中输入的是列名,得到的是去除空值后的行数,其他所有的汇总函数也是,如果输入的是列名会提前把NULL排除在外,再进行计算。
![v2-5ab7ec7025c20b4e9bcf5d1b947883c8_b.jpg](http://img-02.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic1.zhimg.com/v2-5ab7ec7025c20b4e9bcf5d1b947883c8_b.jpg)
输入*会计算空值
求和函数sum:
![v2-0b567c67abce356fa76468ac6900f661_b.jpg](http://img-01.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic2.zhimg.com/v2-0b567c67abce356fa76468ac6900f661_b.jpg)
最大值max 最小值min:
![v2-90ddb30223a6948917b95599b02bdcd0_b.jpg](http://img-01.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic1.zhimg.com/v2-90ddb30223a6948917b95599b02bdcd0_b.jpg)
去重计数count(distinct 列名 ):
![v2-0f2f04a373758b9ad073150151005137_b.jpg](http://img-02.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic4.zhimg.com/v2-0f2f04a373758b9ad073150151005137_b.jpg)
其他汇总函数也可以这么用,在列名前面加上distinct
总结
![v2-d269aa847d3ed34b745664a86026e9ed_b.jpg](http://img-01.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic2.zhimg.com/v2-d269aa847d3ed34b745664a86026e9ed_b.jpg)
1.汇总函数输入列的时候,是把NULL去除后计算
2.sum avg 只能对数值类型计算
3.max min 可以对任何类型进行计算(日期、字符串)
课后练习:
查询课程编号为“0002”的总成绩
![v2-a186201cbf3e774b0a6c19602dd09ed1_b.jpg](http://img-02.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic2.zhimg.com/v2-a186201cbf3e774b0a6c19602dd09ed1_b.jpg)
查询选了课程的学生人数
![v2-fd29491ffe1c433989ddad0933e0d536_b.jpg](http://img-03.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic3.zhimg.com/v2-fd29491ffe1c433989ddad0933e0d536_b.jpg)
二.分组 group by
![v2-2908339d6db718380aa24e1bf9a56830_b.jpg](http://img-03.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic1.zhimg.com/v2-2908339d6db718380aa24e1bf9a56830_b.jpg)
![v2-131094aabc6075d01e0d081db89e4410_b.jpg](http://img-02.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic1.zhimg.com/v2-131094aabc6075d01e0d081db89e4410_b.jpg)
![v2-0a640df2aada5ff9386f27529fa77c75_b.jpg](http://img-01.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic2.zhimg.com/v2-0a640df2aada5ff9386f27529fa77c75_b.jpg)
![v2-2155679fea44d2ed305d82f8d5fb078a_b.jpg](http://img-02.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic3.zhimg.com/v2-2155679fea44d2ed305d82f8d5fb078a_b.jpg)
查询各科成绩最高和最低的分
![v2-f913ae2c542bb88aa2b8ec4f44402e22_b.jpg](http://img-01.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic3.zhimg.com/v2-f913ae2c542bb88aa2b8ec4f44402e22_b.jpg)
查询每门课程被选修的学生数
![v2-1b2ab5cb324fe38fa4fa86701137dd03_b.jpg](http://img-02.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic4.zhimg.com/v2-1b2ab5cb324fe38fa4fa86701137dd03_b.jpg)
查询男生、女生人数
![v2-cd7542f2008a4ee8740851462a2b8c9f_b.jpg](http://img-02.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic4.zhimg.com/v2-cd7542f2008a4ee8740851462a2b8c9f_b.jpg)
三.对分组结果指定条件
where只能指定行的条件,having能够指定分组后需要哪一组
![v2-a386b64e2e8e1440c7d461429115d7ff_b.jpg](http://img-03.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic4.zhimg.com/v2-a386b64e2e8e1440c7d461429115d7ff_b.jpg)
练习:
查询平均成绩大于60分学生的学号和平均成绩
![v2-9dd1fd9a325e16503ed8e43506998229_b.jpg](http://img-02.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic2.zhimg.com/v2-9dd1fd9a325e16503ed8e43506998229_b.jpg)
查询至少选修两门课程的学生学号
![v2-f317cf4e8e7ef5e8e523b94206e75628_b.jpg](http://img-01.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic1.zhimg.com/v2-f317cf4e8e7ef5e8e523b94206e75628_b.jpg)
查询同名同姓学生名单并统计同名人数
![v2-10931e9ea867b3d93cb02a9574ec9695_b.jpg](http://img-01.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic2.zhimg.com/v2-10931e9ea867b3d93cb02a9574ec9695_b.jpg)
四.用sql解决业务问题
1.翻译成大白话
2.写出分析思路
3.写出对应的sql子句
![v2-89b05e90efe2a53a5306ad971b92fab0_b.jpg](http://img-03.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic1.zhimg.com/v2-89b05e90efe2a53a5306ad971b92fab0_b.jpg)
分析思路:
如何计算各科的平均成绩
翻译:
如何计算每门课程的平均成绩
![v2-2fdb3934590bf191dbb7b429c8c73d48_b.jpg](http://img-03.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic1.zhimg.com/v2-2fdb3934590bf191dbb7b429c8c73d48_b.jpg)
分析思路:把关键词放到对应的sql关键词后面
![v2-69bb83d19b77df6cb9911f01976c2448_b.jpg](http://img-01.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic1.zhimg.com/v2-69bb83d19b77df6cb9911f01976c2448_b.jpg)
![v2-87d09e6cb920dd701fbab621504012f4_b.jpg](http://img-03.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic1.zhimg.com/v2-87d09e6cb920dd701fbab621504012f4_b.jpg)
五.对查询结果排序 order by
降序desc 升序asc
![v2-c73c69421eed9ceb476701cb96a61683_b.jpg](http://img-02.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic4.zhimg.com/v2-c73c69421eed9ceb476701cb96a61683_b.jpg)
运行顺序:
![v2-ffb71d6e7c15b28db0e8198039420d19_b.jpg](http://img-02.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic2.zhimg.com/v2-ffb71d6e7c15b28db0e8198039420d19_b.jpg)
指定多个排序列名:
order by 成绩 asc,课程号 desc; 从左到右依次排列
![v2-ba8827a56af7d47e54cb8ef879edd4c9_b.jpg](http://img-02.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic2.zhimg.com/v2-ba8827a56af7d47e54cb8ef879edd4c9_b.jpg)
从查询结果中取出指定行:limit
![v2-981995d6add175a95ff1b079957104f9_b.jpg](http://img-03.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic2.zhimg.com/v2-981995d6add175a95ff1b079957104f9_b.jpg)
SQL运行顺序
1.先运行篮框里的子句,子句按照书写顺序运行
2.select 子句
3.最后运行红框里的子句
![v2-2fb39e9b74cac3136ae3f498a9cbb582_b.jpg](http://img-03.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic3.zhimg.com/v2-2fb39e9b74cac3136ae3f498a9cbb582_b.jpg)
练习:理解sql的运行顺序
查询不及格的课程并按课程号从大到小排列
![v2-e0dfac8c90ba247a75f4b3313227b632_b.jpg](http://img-01.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic3.zhimg.com/v2-e0dfac8c90ba247a75f4b3313227b632_b.jpg)
查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同的按课程号降序排列
![v2-64de7b4aa141611c4aa748b009342c5e_b.jpg](http://img-01.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic3.zhimg.com/v2-64de7b4aa141611c4aa748b009342c5e_b.jpg)
六.如何读懂报错
1.在group by 中使用了select里的别名
![v2-d0f27a1426adcd6cb13cefd205afcea4_b.jpg](http://img-02.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic1.zhimg.com/v2-d0f27a1426adcd6cb13cefd205afcea4_b.jpg)
因为having子句在select子句之前运行,所以不知道平均成绩是什么
2.常见问题:字符串类型的数字
字符串类型和数值类型的排序规则不同,需要先修改字符串类型为数值类型
![v2-b179fb9d077ec38a6bae6474a16adf09_b.jpg](http://img-02.proxy.5ce.com/view/image?&type=2&guid=dca2d5b7-192f-eb11-8da9-e4434bdf6706&url=https://pic2.zhimg.com/v2-b179fb9d077ec38a6bae6474a16adf09_b.jpg)