查找课程不及格学生最多的前5名老师的id
表:student
字段名 | 类型 | 是否为空 | 主键 | 描述 |
---|---|---|---|---|
StdID | int | 否 | 是 | 学生ID |
StdName | varchar(100) | 否 | 学生姓名 | |
Gender | enum('M','F') | 是 | 性别 | |
Age | int | 是 | 年龄 |
表:Course
字段名 | 类型 | 是否为空 | 主键 | 描述 |
---|---|---|---|---|
CouID | int | 否 | 是 | 课程ID |
CName | varchar(100) | 否 | 课程名称 | |
TID | int | 否 | 老师ID |
表:Score
字段名 | 类型 | 是否为空 | 主键 | 描述 |
---|---|---|---|---|
SID | int | 否 | 是 | 分数ID |
StdDI | int | 否 | 学生ID | |
CouID | int | 否 | 课程ID | |
Grade | int | 否 | 分数 |
表:teacher
字段名 | 类型 | 是否为空 | 主键 | 描述 |
---|---|---|---|---|
TID | int | 否 | 是 | 老师ID |
Tname | varchar(100) | 否 | 是 | 老师姓名 |
思路:
1、找出分数低于60分的数据 ( SELECT 要查找的数据 FROM 要查找的表 WHERE 查找条件; )
mysql> SELECT Score.Grade FROM Score WHERE Score.Grade < 60;
| 23 |
+-------+
5893 rows in set (0.00 sec)
2、找出低于60分的课和ID
mysql> SELECT Score.CouID, Score.Grade FROM Score WHERE Score.Grade < 60;
| 14 | 23 |
+-------+-------+
5893 rows in set (0.00 sec)
3、找出与客程ID都关联的teacher id ( Join 联查的表 ON 联查的条件; )
mysql> SELECT Score.CouID, Course.TID FROM Score
-> JOIN Course ON Score.CouID = Course.CouID and Score.Grade < 60;
| 16 | 7 |
| 14 | 3 |
+-------+-----+
5893 rows in set (0.01 sec)
4、找出与课程teacher id 相对应的 teacher name ( Join 联查的表 ON 联查的条件1 AND 联查的条件2; )
mysql> SELECT Score.CouID, Course.TID, Teacher.TName as teacher_count FROM Score
-> JOIN Course ON Score.CouID = Course.CouID and Score.Grade < 60
-> JOIN Teacher ON Course.TID = Teacher.TID;
| 16 | 7 | 3e773c |
| 14 | 3 | 23cdcaf356e |
+-------+-----+---------------+
5893 rows in set (0.01 sec)
5、将teacher id 分组、统计相同 teacher ID 出现在次数 ( COUNT(排序的内容) as 别名; GROUP BY 分组条件 )
mysql> SELECT Course.CouID, Course.TID, Teacher.TName, COUNT(Course.TID) as teacher_count FROM Course
-> JOIN Score ON Score.CouID = Course.CouID and Score.Grade < 60
-> JOIN Teacher ON Course.TID = Teacher.TID
-> GROUP BY Course.TID;
+-------+-----+--------------+---------------+
| CouID | TID | TName | teacher_count |
+-------+-----+--------------+---------------+
| 15 | 1 | 8dcd0f6f4c67 | 1195 |
| 11 | 2 | 852c304e | 615 |
| 14 | 3 | 23cdcaf356e | 534 |
| 18 | 4 | 6ab | 582 |
| 19 | 5 | 60aea3314c | 562 |
| 17 | 6 | e2376f | 575 |
| 12 | 7 | 3e773c | 1830 |
+-------+-----+--------------+---------------+
7 rows in set (0.02 sec)
6、将分组、统计后的数据排序 ( ORDER BY 要排序的内容 )
mysql> SELECT Course.CouID, Course.TID, Teacher.TName, COUNT(Course.TID) as teacher_count FROM Course
-> JOIN Score ON Score.CouID = Course.CouID and Score.Grade < 60
-> JOIN Teacher ON Course.TID = Teacher.TID
-> GROUP BY Course.TID
-> ORDER by teacher_count;
+-------+-----+--------------+---------------+
| CouID | TID | TName | teacher_count |
+-------+-----+--------------+---------------+
| 14 | 3 | 23cdcaf356e | 534 |
| 19 | 5 | 60aea3314c | 562 |
| 17 | 6 | e2376f | 575 |
| 18 | 4 | 6ab | 582 |
| 11 | 2 | 852c304e | 615 |
| 15 | 1 | 8dcd0f6f4c67 | 1195 |
| 12 | 7 | 3e773c | 1830 |
+-------+-----+--------------+---------------+
7 rows in set (0.00 sec)
5、将排序反序 ( ORDER BY 要排序的内容 DESC )
mysql> SELECT Course.CouID, Course.TID, Teacher.TName, COUNT(Course.TID) as teacher_count FROM Course
-> JOIN Score ON Score.CouID = Course.CouID and Score.Grade < 60
-> JOIN Teacher ON Course.TID = Teacher.TID
-> GROUP BY Course.TID
-> ORDER by teacher_count DESC;
+-------+-----+--------------+---------------+
| CouID | TID | TName | teacher_count |
+-------+-----+--------------+---------------+
| 12 | 7 | 3e773c | 1830 |
| 15 | 1 | 8dcd0f6f4c67 | 1195 |
| 11 | 2 | 852c304e | 615 |
| 18 | 4 | 6ab | 582 |
| 17 | 6 | e2376f | 575 |
| 19 | 5 | 60aea3314c | 562 |
| 14 | 3 | 23cdcaf356e | 534 |
+-------+-----+--------------+---------------+
7 rows in set (0.01 sec)
6、取课程不及格学生最多的前5名老师 ( LIMIT N )
mysql> SELECT Course.CouID, Course.TID, Teacher.TName, COUNT(Course.TID) as teacher_count FROM Course
-> JOIN Score ON Score.CouID = Course.CouID and Score.Grade < 60
-> JOIN Teacher ON Course.TID = Teacher.TID
-> GROUP BY Course.TID
-> ORDER by teacher_count DESC
-> LIMIT 5;
+-------+-----+--------------+---------------+
| CouID | TID | TName | teacher_count |
+-------+-----+--------------+---------------+
| 12 | 7 | 3e773c | 1830 |
| 15 | 1 | 8dcd0f6f4c67 | 1195 |
| 11 | 2 | 852c304e | 615 |
| 18 | 4 | 6ab | 582 |
| 17 | 6 | e2376f | 575 |
+-------+-----+--------------+---------------+
5 rows in set (0.01 sec)
7、取前5名老师的ID
mysql> SELECT TID FROM
-> (
#将上面查询出的结果,做为一个表,提供给另一个查询语句查询
-> (SELECT Course.CouID, Course.TID, Teacher.TName, COUNT(Course.TID) as teacher_count FROM Course
-> JOIN Score ON Score.CouID = Course.CouID and Score.Grade < 60
-> JOIN Teacher ON Course.TID = Teacher.TID
-> GROUP BY Course.TID
-> ORDER by teacher_count DESC
-> LIMIT 5)
-> as teacher_tid # 必须将结果命名为一个表
-> );
+-----+
| TID |
+-----+
| 7 |
| 1 |
| 2 |
| 4 |
| 6 |
+-----+
5 rows in set (0.00 sec)