python 查询数据

查找课程不及格学生最多的前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)