mysql练习题（做溜这几十道题，面试不再怕mysql任何问题）

有如下4张表：

表结构如下：

1、学生表

mysql> select * from student;
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 01   | 赵雷 | 1990-01-01 | 男   |
| 02   | 钱电 | 1990-12-21 | 男   |
| 03   | 孙风 | 1990-05-20 | 男   |
| 04   | 李云 | 1990-08-06 | 男   |
| 05   | 周梅 | 1991-12-01 | 女   |
| 06   | 吴兰 | 1992-03-01 | 女   |
| 07   | 郑竹 | 1989-07-01 | 女   |
| 08   | 王菊 | 1990-01-20 | 女   |
+------+--------+------------+-------+

2、课程表

mysql> select * from course; 
+------+--------+------+
| c_id | c_name | t_id |
+------+--------+------+
| 01   | 语文 | 02   |
| 02   | 数学 | 01   |
| 03   | 英语 | 03   |
+------+--------+------+

3、老师表

mysql> select * from teacher;
+------+--------+
| t_id | t_name |
+------+--------+
| 01   | 张三 |
| 02   | 李四 |
| 03   | 王五 |
+------+--------+

4、成绩表

mysql> select * from score; 
+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 01   | 01   |      80 |
| 01   | 02   |      90 |
| 01   | 03   |      99 |
| 02   | 01   |      70 |
| 02   | 02   |      60 |
| 02   | 03   |      80 |
| 03   | 01   |      80 |
| 03   | 02   |      80 |
| 03   | 03   |      80 |
| 04   | 01   |      50 |
| 04   | 02   |      30 |
| 04   | 03   |      20 |
| 05   | 01   |      76 |
| 05   | 02   |      87 |
| 06   | 01   |      31 |
| 06   | 03   |      34 |
| 07   | 02   |      89 |
| 07   | 03   |      98 |
+------+------+---------+

练习题如下：

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

方法一：先将成绩表score进行行转列，找出课程表符合要求的s_id，在将此表和学生信息表进行关联，得出结果。
mysql> SELECT student.*,01score,02score
    -> FROM student
    -> JOIN 
    -> (
    -> SELECT s_id,01score,02score
    -> FROM
    -> (
    -> SELECT s_id,
    -> MAX(01score) AS 01score,
    -> MAX(02score) AS 02score,
    -> MAX(03score) AS 03score
    -> FROM
    -> (
    -> SELECT s_id,
    -> CASE c_id WHEN '01' THEN s_score ELSE null END AS 01score,
    -> CASE c_id WHEN '02' THEN s_score ELSE null END AS 02score,
    -> CASE c_id WHEN '03' THEN s_score ELSE null END AS 03score
    -> FROM score
    -> ) a
    -> GROUP BY s_id
    -> ) b
    -> WHERE 01score>02score 
    -> ) c
    -> ON student.s_id=c.s_id;
+------+--------+------------+-------+---------+---------+
| s_id | s_name | s_birth    | s_sex | 01score | 02score |
+------+--------+------------+-------+---------+---------+
| 02   | 钱电 | 1990-12-21 | 男   |      70 |      60 |
| 04   | 李云 | 1990-08-06 | 男   |      50 |      30 |
+------+--------+------------+-------+---------+---------+


方法2：先将课程表进行自连接，形成包含s_id和01，02课程的新表，在和student表进行join连接，即可得出结果。
mysql> SELECT s.*,c.01score,c.02score
    -> FROM 
    -> (
    -> SELECT a.s_id,a.c_id acid,a.s_score 01score,b.c_id bcid,b.s_score 02score
    -> FROM score a,score b
    -> WHERE a.s_id=b.s_id
    -> AND a.c_id="01"
    -> AND b.c_id="02"
    -> AND a.s_score>b.s_score
    -> ) c
    -> JOIN student s
    -> ON c.s_id=s.s_id;
+------+--------+------------+-------+---------+---------+
| s_id | s_name | s_birth    | s_sex | 01score | 02score |
+------+--------+------------+-------+---------+---------+
| 02   | 钱电 | 1990-12-21 | 男   |      70 |      60 |
| 04   | 李云 | 1990-08-06 | 男   |      50 |      30 |
+------+--------+------------+-------+---------+---------+


方法三：直接一次性将学生表，score成绩表a 和score成绩表b 进行连接，一步到位。注意条件不要少。
mysql> select a.*,b.s_score as 01_score,c.s_score as 02_score 
    -> from student a,score b,score c 
    -> where a.s_id=b.s_id 
    -> and a.s_id=c.s_id 
    -> and b.c_id='01' 
    -> and c.c_id='02' 
    -> and b.s_score>c.s_score;
+------+--------+------------+-------+----------+----------+
| s_id | s_name | s_birth    | s_sex | 01_score | 02_score |
+------+--------+------------+-------+----------+----------+
| 02   | 钱电 | 1990-12-21 | 男   |       70 |       60 |
| 04   | 李云 | 1990-08-06 | 男   |       50 |       30 |
+------+--------+------------+-------+----------+----------+


方法四：
mysql> select a.* ,b.s_score as 01_score,c.s_score as 02_score 
    -> from student a 
    -> join score b on a.s_id=b.s_id and b.c_id='01'
    -> left join score c on a.s_id=c.s_id and c.c_id='02' or c.c_id = NULL where b.s_score>c.s_score;
+------+--------+------------+-------+----------+----------+
| s_id | s_name | s_birth    | s_sex | 01_score | 02_score |
+------+--------+------------+-------+----------+----------+
| 02   | 钱电 | 1990-12-21 | 男   |       70 |       60 |
| 04   | 李云 | 1990-08-06 | 男   |       50 |       30 |
+------+--------+------------+-------+----------+----------+

2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

方法一：先在score成绩表中找出符合条件的sid和平均成绩，在和student学生表关联，可以就结果。
mysql> SELECT student.s_id,s_name,avg_score
    -> FROM student
    -> JOIN 
    -> (
    -> SELECT s_id,AVG(s_score) avg_score
    -> FROM score
    -> GROUP BY s_id
    -> HAVING avg_score>=60
    -> ) a
    -> ON student.s_id=a.s_id;
+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
| 01   | 赵雷 |   89.6667 |
| 02   | 钱电 |   70.0000 |
| 03   | 孙风 |   80.0000 |
| 05   | 周梅 |   81.5000 |
| 07   | 郑竹 |   93.5000 |
+------+--------+-----------+


方法二：一步到位，学生表和成绩表直接关联。
mysql> select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score 
    -> from student b 
    -> join score a 
    -> on b.s_id = a.s_id
    -> GROUP BY b.s_id,b.s_name
    -> HAVING avg_score >=60;
+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
| 01   | 赵雷 |     89.67 |
| 02   | 钱电 |     70.00 |
| 03   | 孙风 |     80.00 |
| 05   | 周梅 |     81.50 |
| 07   | 郑竹 |     93.50 |
+------+--------+-----------+

3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

mysql> SELECT s.s_id,s.s_name,COUNT(DISTINCT(c_id)),SUM(s_score)
    -> FROM student s
    -> LEFT JOIN score sc
    -> on s.s_id=sc.s_id
    -> GROUP BY s.s_id,s.s_name;
+------+--------+-----------------------+--------------+
| s_id | s_name | COUNT(DISTINCT(c_id)) | SUM(s_score) |
+------+--------+-----------------------+--------------+
| 01   | 赵雷 |                     3 |          269 |
| 02   | 钱电 |                     3 |          210 |
| 03   | 孙风 |                     3 |          240 |
| 04   | 李云 |                     3 |          100 |
| 05   | 周梅 |                     2 |          163 |
| 06   | 吴兰 |                     2 |           65 |
| 07   | 郑竹 |                     2 |          187 |
| 08   | 王菊 |                     0 |         NULL |
+------+--------+-----------------------+--------------+

所有的学生，所以必须用left join

4、查询学过"张三"老师授课的同学的信息

方法一：直接查询，一步到位
mysql> SELECT s.*
    -> FROM student s,teacher t,score sc,course co
    -> WHERE t.t_name='张三'
    -> AND t.t_id=co.t_id
    -> AND co.c_id=sc.c_id
    -> AND sc.s_id=s.s_id;
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 01   | 赵雷 | 1990-01-01 | 男   |
| 02   | 钱电 | 1990-12-21 | 男   |
| 03   | 孙风 | 1990-05-20 | 男   |
| 04   | 李云 | 1990-08-06 | 男   |
| 05   | 周梅 | 1991-12-01 | 女   |
| 07   | 郑竹 | 1989-07-01 | 女   |
+------+--------+------------+-------+

方法二：先根据老师名字张三，从teacher表里找出t_id，再根据t_id从course表里找出c_id，从score表里找出s_id，然后从student表里查出学生信息。
mysql> select a.* 
    -> from student a 
    -> join score b on a.s_id=b.s_id 
    -> where b.c_id IN
    -> (select c_id from course where t_id =(select t_id from teacher where t_name = '张三'));
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 01   | 赵雷 | 1990-01-01 | 男   |
| 02   | 钱电 | 1990-12-21 | 男   |
| 03   | 孙风 | 1990-05-20 | 男   |
| 04   | 李云 | 1990-08-06 | 男   |
| 05   | 周梅 | 1991-12-01 | 女   |
| 07   | 郑竹 | 1989-07-01 | 女   |
+------+--------+------------+-------+

5、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

方法 一：
先对score表进行自连接，找出符合条件的s_id，再和student表进行join连接，求出学生信息。
mysql> select * 
    -> from student 
    -> join 
    -> (select a.s_id sid
    -> from score a,score b
    -> where a.s_id=b.s_id and a.c_id="01" and b.c_id="02") c
    -> on student.s_id=c.sid;
+------+--------+------------+-------+-----+
| s_id | s_name | s_birth    | s_sex | sid |
+------+--------+------------+-------+-----+
| 01   | 赵雷 | 1990-01-01 | 男   | 01  |
| 02   | 钱电 | 1990-12-21 | 男   | 02  |
| 03   | 孙风 | 1990-05-20 | 男   | 03  |
| 04   | 李云 | 1990-08-06 | 男   | 04  |
| 05   | 周梅 | 1991-12-01 | 女   | 05  |
+------+--------+------------+-------+-----+

方法二：
直接将student表和两个score表进行连接，根据条件一步到位
mysql> select a.* from 
    -> student a,score b,score c 
    -> where a.s_id = b.s_id  and a.s_id = c.s_id and b.c_id='01' and c.c_id='02';
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 01   | 赵雷 | 1990-01-01 | 男   |
| 02   | 钱电 | 1990-12-21 | 男   |
| 03   | 孙风 | 1990-05-20 | 男   |
| 04   | 李云 | 1990-08-06 | 男   |
| 05   | 周梅 | 1991-12-01 | 女   |
+------+--------+------------+-------+