sql语句的使用（四）

GROUP BY 子句——数据的分组
聚合函数 (比如 SUM) 常常需要添加 GROUP BY 语句。
一、GROUP BY 子句
GROUP BY 子句用于结合聚合函数，根据一个或多个列对结果集进行分组。在 SELECT 语句里，GROUP BY 子句在 WHERE 子句之后，在 ORDER BY 子句之前。
语法：SELECT 列名, 聚合函数(列名) FROM 表名 [ WHERE 条件 ] GROUP BY 列名 [ ORDER BY 列名 ];
前面的例子中我们用 SUM 这个函数来算出所有店的营业总额。如果我们的需求变成是要算出每一间店(store_name)的营业额(Sales)，就可以利用GROUP BY 子句。
例：由 Store_Information 表里求出每一间店的营业总额：
SELECT store_name, SUM(Sales) FROM Store_Information GROUP BY store_name;
结果:store_name SUM(Sales)
------------------------------------------
Los Angeles 1800
San Diego 250
Boston 700
当我们查询不只一个字段，且其中至少一个字段有聚合函数的运用时，我们就需要用到 GROUP BY 子句。这时，我们需要确定 GROUP BY 子句中包含所有其他的字段。
像 ORDER BY 子句一样，GROUP BY 子句里也可以用整数代表字段名称。
用 GROUP BY 子句实现排序操作与用 ORDER BY 子句的区别是：
-> 所有被选中的、非聚合函数的字段必须列在 GROUP BY 子句里；
-> 除非需要所有聚合函数，否则使用 GROUP BY 子句进行排序通常没有必要；
-> GROUP BY 子句用于对相同的数据进行分组，而 ORDER BY 子句基本上只用于让数据形成次序。
二、HAVING 子句
那么，我们如何对函数产生的值设定条件呢？举例来说，我们可能需要知道哪些店的营业额超过1,500 。这时，我们不能使用WHERE 子句，因为WHERE 无法与聚合函数一起使用。那该怎么办呢？很幸运地，SQL 提供一个 HAVING 的指令，而我们就可以用这个指令来达到这个目的。
HAVING 子句在 SELECT 语句里与 GROUP BY 子句联合使用时，用于告诉 GROUP BY 子句在输出里包含哪些分组。HAVING 对 GROUP BY 的作用相当于 WHERE 对于 SELECT 的作用。
语法：SELECT 列名, 聚合函数(列名) FROM 表名 [ WHERE 条件 ] GROUP BY 列名 HAVING 聚合函数(列名) 操作符 值;
注意：如果被 SELECT 的只有函数栏，那就不需要 GROUP BY 子句。
例：在 Store_Information 表里，找出 Sales 大于1,500 的 store_name ，可以用：
SELECT store_name, SUM(sales) FROM Store_Information GROUP BY store_name HAVING SUM(sales) > 1500;
结果:store_name SUM(Sales)
-------------------------------------------
Los Angeles 1800
三、查询举例
有学生表 Student(Sno, Sname, Sage, Ssex)，包括学号、姓名、年龄、性别四个字段；
课程表 Course(Cno, Cname, Tno)，包括课程编号、课程名、教师编号三个字段；
成绩表 SC(Sno, Cno, Score)，包括学号、课程编号、成绩三个字段；
教师表 Teacher(Tno, Tname)，包括教师编号、姓名两个字段；
完成如下查询：
1、查询姓"张"的学生名单
SELECT Sname FROM Student WHERE Sname like '张%'；
2、求选了课程的学生人数
SELECT count(*) FROM SC;
3、查询姓"李"的老师的个数
SELECT count(distinct(Tname))FROM Teacher WHERE Tname like '李%'；
4、查询不及格的课程，并按课程号从大到小排列
SELECT Cno FROM SC WHERE Score <60 ORDER BY Cno;
5、检索"004"课程分数小于60，按分数降序排列的同学学号
SELECT Sno FROM SC WHERE Cno='004' and Score <60 ORDER BY Score desc;
6、查询各个课程及相应的选修人数
SELECT count(*) FROM SC GROUP BY Cno;
7、查询每门课程被选修的学生数
SELECT Cno,count(Sno) FROM SC GROUP BY Cno;
8、统计每门课程的学生选修人数，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT Cno as 课程号，count(*) as 人数 FROM SC GROUP BY Cno ORDER BY count(*) desc, Cno;
9、检索至少选修两门课程的学生学号
SELECT Sno FROM SC GROUP BY Sno HAVING count(*) >=2;
10、查询平均成绩大于60分的同学的学号和平均成绩
SELECT Sno,avg(Score) FROM SC GROUP BY Sno HAVING avg(Score) >60;
11、查询男生、女生人数
SELECT count(Ssex) as 男生人数 FROM Student GROUP BY Ssex HAVING Ssex='男';
SELECT count(Ssex) as 女生人数 FROM Student GROUP BY Ssex HAVING Ssex='女';
12、查询同名同姓学生名单，并统计同名人数
SELECT Sname,count(*) FROM Student GROUP BY Sname HAVING count(*) >1;
13、删除"002"同学的"001"课程的成绩
DELECT FROM SC WHERE Sno='002' AND Cno='001';
14、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
SELECT Cno, avg(score) FROM SC GROUP BY Cno ORDER BY avg(score), Cno DESC;