mysql常用查询（2）

#9.查询‘95031’班的学生人数，使用count关键字
SELECT COUNT(*) FROM student WHERE class = '95031';
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

#10.查询最高分学生学号和课程号以及成绩
SELECT sno,cno,dgree FROM score WHERE dgree=(SELECT MAX(dgree) from score);
+-----+-------+-------+
| sno | cno   | dgree |
+-----+-------+-------+
| 103 | 3-103 |    99 |
| 105 | 3-103 |    99 |
+-----+-------+-------+
#排序的做法
SELECT sno,cno,dgree from score ORDER BY dgree DESC limit 0,1;
+-----+-------+-------+
| sno | cno   | dgree |
+-----+-------+-------+
| 103 | 3-103 |    99 |
+-----+-------+-------+
1 row in set (0.01 sec)

#11.查询总的平均成绩
SELECT avg(dgree) FROM score;
+-------------------+
| avg(dgree)        |
+-------------------+
| 81.66666666666667 |
+-------------------+
#查询每门课
SELECT avg(dgree),cno from score GROUP by cno;
+------------+-------+
| avg(dgree) | cno   |
+------------+-------+
|       77.5 | 3-101 |
|         72 | 3-102 |
|       89.5 | 3-103 |
|       81.4 | 3-104 |
+------------+-------+

#12.查询score表中至少有5名学生选修并且以3开头的平均分数
#以3开头：（模糊查找）LIKE  '3%'
#至少有5名学生选修：HAVING count(cno)>=5;
SELECT cno ,AVG(dgree),count(*) from score group BY cno HAVING count(cno)>=5 and cno like '3%';
+-------+------------+----------+
| cno   | AVG(dgree) | count(*) |
+-------+------------+----------+
| 3-103 |       89.5 |        6 |
| 3-104 |       81.4 |        5 |
+-------+------------+----------+