mysql—面试50题—1

注：面试50题将分为5个部分，每部分10题

一、查询数据

学生表 Student

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

insert into Student values('02' , '钱电' , '1990-12-21' , '男');

insert into Student values('03' , '孙风' , '1990-12-20' , '男');

insert into Student values('04' , '李云' , '1990-12-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , '1992-01-01' , '女');

insert into Student values('07' , '郑竹' , '1989-01-01' , '女');

insert into Student values('09' , '张三' , '2017-12-20' , '女');

insert into Student values('10' , '李四' , '2017-12-25' , '女');

insert into Student values('11' , '李四' , '2012-06-06' , '女');

insert into Student values('12' , '赵六' , '2013-06-13' , '女');

insert into Student values('13' , '孙七' , '2014-06-01' , '女');

科目表 Course

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

教师表 Teacher

create table Teacher(TId varchar(10),Tname varchar(10));

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

成绩表 SC

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));

insert into SC values('01' , '01' , 80);

insert into SC values('01' , '02' , 90);

insert into SC values('01' , '03' , 99);

insert into SC values('02' , '01' , 70);

insert into SC values('02' , '02' , 60);

insert into SC values('02' , '03' , 80);

insert into SC values('03' , '01' , 80);

insert into SC values('03' , '02' , 80);

insert into SC values('03' , '03' , 80);

insert into SC values('04' , '01' , 50);

insert into SC values('04' , '02' , 30);

insert into SC values('04' , '03' , 20);

insert into SC values('05' , '01' , 76);

insert into SC values('05' , '02' , 87);

insert into SC values('06' , '01' , 31);

insert into SC values('06' , '03' , 34);

insert into SC values('07' , '02' , 89);

insert into SC values('07' , '03' , 98);

二、问题练习

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

mysql> select * from student as stu ,sc where stu.SId=sc.SId and (CId=1 or CId=2) ;

+------+--------+---------------------+------+------+------+-------+
| SId  | Sname  | Sage                | Ssex | SId  | CId  | score |
+------+--------+---------------------+------+------+------+-------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   | 01   | 01   |  80.0 |
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   | 01   | 02   |  90.0 |
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   | 02   | 01   |  70.0 |
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   | 02   | 02   |  60.0 |
| 03   | 孙风   | 1990-12-20 00:00:00 | 男   | 03   | 01   |  80.0 |
| 03   | 孙风   | 1990-12-20 00:00:00 | 男   | 03   | 02   |  80.0 |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   | 04   | 01   |  50.0 |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   | 04   | 02   |  30.0 |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   | 05   | 01   |  76.0 |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   | 05   | 02   |  87.0 |
| 06   | 吴兰   | 1992-01-01 00:00:00 | 女   | 06   | 01   |  31.0 |
| 07   | 郑竹   | 1989-01-01 00:00:00 | 女   | 07   | 02   |  89.0 |
+------+--------+---------------------+------+------+------+-------+
12 rows in set (0.00 sec)

当然，我们用上面的方法查不出来，上面的查询是我用来验证思路的，有个思路（逻辑）很重要，就是分别查询01、02的成绩，再关联查询，最后再把关联查询的表结合student表查询输出数据，具体如下

mysql> select * from Student RIGHT JOIN (
    ->     select t1.SId, class1, class2 from
    ->           (select SId, score as class1 from sc where sc.CId = '01')as t1,
    ->           (select SId, score as class2 from sc where sc.CId = '02')as t2
    ->     where t1.SId = t2.SId AND t1.class1 > t2.class2
    -> )r
    -> on Student.SId = r.SId;

+------+--------+---------------------+------+------+--------+--------+
| SId  | Sname  | Sage                | Ssex | SId  | class1 | class2 |
+------+--------+---------------------+------+------+--------+--------+
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   | 02   |   70.0 |   60.0 |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   | 04   |   50.0 |   30.0 |
+------+--------+---------------------+------+------+--------+--------+
2 rows in set (0.00 sec)

1.1查询同时存在" 01 "课程和" 02 "课程的情况

这题的思路就简单了，01、02分别查询，再关联查询就可以了，并不会再把其结果与其它表再关联查询

mysql> select * from
    ->     (select * from sc where sc.CId = '01') as t1,
    ->     (select * from sc where sc.CId = '02') as t2
    -> where t1.SId = t2.SId;

+------+------+-------+------+------+-------+
| SId  | CId  | score | SId  | CId  | score |
+------+------+-------+------+------+-------+
| 01   | 01   |  80.0 | 01   | 02   |  90.0 |
| 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 03   | 01   |  80.0 | 03   | 02   |  80.0 |
| 04   | 01   |  50.0 | 04   | 02   |  30.0 |
| 05   | 01   |  76.0 | 05   | 02   |  87.0 |
+------+------+-------+------+------+-------+
5 rows in set (0.00 sec)

1.2查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

mysql> select * from
    -> (select * from sc where sc.CId = '01') as t1,
    -> (select * from sc where sc.CId = '02') as t2,
    -> where t1.SId = t2.SId;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where t1.SId = t2.SId' at line 4

如上，显然这道题只能用join来查询，right join或left join，都可

mysql> select * from
    -> (select * from sc where sc.CId = '02') as t2
    -> right join
    -> (select * from sc where sc.CId = '01') as t1
    -> on t1.SId = t2.SId;

+------+------+-------+------+------+-------+
| SId  | CId  | score | SId  | CId  | score |
+------+------+-------+------+------+-------+
| 01   | 02   |  90.0 | 01   | 01   |  80.0 |
| 02   | 02   |  60.0 | 02   | 01   |  70.0 |
| 03   | 02   |  80.0 | 03   | 01   |  80.0 |
| 04   | 02   |  30.0 | 04   | 01   |  50.0 |
| 05   | 02   |  87.0 | 05   | 01   |  76.0 |
| NULL | NULL |  NULL | 06   | 01   |  31.0 |
+------+------+-------+------+------+-------+
6 rows in set (0.00 sec)

1.3查询不存在" 01 "课程但存在" 02 "课程的情况

对于这道题，我们可以用in，not in去判断存不存再里面

mysql> select * from sc
    -> where sc.SId not in (
    ->     select SId from sc
    ->     where sc.CId = '01'
    -> )
    -> AND sc.CId= '02';

+------+------+-------+
| SId  | CId  | score |
+------+------+-------+
| 07   | 02   |  89.0 |
+------+------+-------+
1 row in set (0.00 sec)

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

mysql> select student.SId,sname,ss from student,(
    ->     select SId, AVG(score) as ss from sc
    ->     GROUP BY SId
    ->     HAVING AVG(score)> 60
    ->     )r
    -> where student.sid = r.sid;

+------+--------+----------+
| SId  | sname  | ss       |
+------+--------+----------+
| 01   | 赵雷   | 89.66667 |
| 02   | 钱电   | 70.00000 |
| 03   | 孙风   | 80.00000 |
| 05   | 周梅   | 81.50000 |
| 07   | 郑竹   | 93.50000 |
+------+--------+----------+
5 rows in set (0.00 sec)

查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩，这里只用根据学生ID把成绩分组，对分组中的score求平均值，最后在选取结果中AVG大于60的即可. 注意，这里必须要给计算得到的AVG结果一个别名，这里是所示，最后得到学生信息的时候既可以用join也可以用一般的联合搜索

3.查询在 SC 表存在成绩的学生信息

mysql> select DISTINCT student.*
    -> from student,sc
    -> where student.SId=sc.SId;

+------+--------+---------------------+------+
| SId  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03   | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06   | 吴兰   | 1992-01-01 00:00:00 | 女   |
| 07   | 郑竹   | 1989-01-01 00:00:00 | 女   |
+------+--------+---------------------+------+
7 rows in set (0.00 sec)

这道题简单，值得注意的是distinct（去重复字段）的使用，还有(student.*)用法

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

看到null就可以想到这道题要用join左右链接查询了，一般关联查询就不行了

mysql> select s.sid, s.sname,r.coursenumber,r.scoresum
    -> from (
    ->     (select student.sid,student.sname
    ->     from student
    ->     )s
    ->     left join
    ->     (select
    ->         sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber
    ->         from sc
    ->         group by sc.sid
    ->     )r
    ->    on s.sid = r.sid );

+------+--------+--------------+----------+
| sid  | sname  | coursenumber | scoresum |
+------+--------+--------------+----------+
| 01   | 赵雷   |            3 |    269.0 |
| 02   | 钱电   |            3 |    210.0 |
| 03   | 孙风   |            3 |    240.0 |
| 04   | 李云   |            3 |    100.0 |
| 05   | 周梅   |            2 |    163.0 |
| 06   | 吴兰   |            2 |     65.0 |
| 07   | 郑竹   |            2 |    187.0 |
| 09   | 张三   |         NULL |     NULL |
| 10   | 李四   |         NULL |     NULL |
| 11   | 李四   |         NULL |     NULL |
| 12   | 赵六   |         NULL |     NULL |
| 13   | 孙七   |         NULL |     NULL |
+------+--------+--------------+----------+
12 rows in set (0.00 sec)

4.1 查有成绩的学生信息

这道题很简单，当这里有个小知识点，我附在代码后面了，大家可以记一下

mysql> select * from student
    -> where student.sid in (select sc.sid from sc);

+------+--------+---------------------+------+
| SId  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03   | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06   | 吴兰   | 1992-01-01 00:00:00 | 女   |
| 07   | 郑竹   | 1989-01-01 00:00:00 | 女   |
+------+--------+---------------------+------+
7 rows in set (0.00 sec)

附：

这一题涉及到in和exists的用法，在这种小表中，两种方法的效率都差不多，具体请参考SQL查询中in和exists的区别分析
当表2的记录数量非常大的时候，选用exists比in要高效很多.
EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False.
结论：IN()适合B表比A表数据小的情况
结论：EXISTS()适合B表比A表数据大的情况

select * from student

where exists (select sc.sid from sc where student.sid = sc.sid);

5.查询「李」姓老师的数量

这道题同样不难，值得注意一点的是，关于函数count和模糊查询的使用

mysql> select count(*)
    -> from teacher
    -> where tname like '李%';

+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.02 sec)

6.查询学过「张三」老师授课的同学的信息

这道题，也没什么难度，值得注意的是，几张表使用一般联合查询，通过每张表之间的共同字段进行查询，简化了查询逻辑，节省了时间，一般联合查询，并不一定只有两张表之间才可以。我们要跳出这个思维误区。我认为这是这道题带给我们的最大收获。

mysql> select student.* from student,teacher,course,sc
    -> where
    ->     student.sid = sc.sid
    ->     and course.cid=sc.cid
    ->     and course.tid = teacher.tid
    ->     and tname = '张三';

+------+--------+---------------------+------+
| SId  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03   | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |
| 07   | 郑竹   | 1989-01-01 00:00:00 | 女   |
+------+--------+---------------------+------+
6 rows in set (0.00 sec)

7.查询没有学全所有课程的同学的信息

看到“没有学全”这几个字的时候，你脑海里浮现的第一印象是什么，我是第一时间想到的not in

mysql> select * from student
    -> where student.sid not in (
    ->   select sc.sid from sc
    ->   group by sc.sid
    ->   having count(sc.cid)= (select count(cid) from course)

注：这里having 相当于where，而这里不能用where的原因则是，这里使用group by进行分组了
    -> );

+------+--------+---------------------+------+
| SId  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06   | 吴兰   | 1992-01-01 00:00:00 | 女   |
| 07   | 郑竹   | 1989-01-01 00:00:00 | 女   |
| 09   | 张三   | 2017-12-20 00:00:00 | 女   |
| 10   | 李四   | 2017-12-25 00:00:00 | 女   |
| 11   | 李四   | 2012-06-06 00:00:00 | 女   |
| 12   | 赵六   | 2013-06-13 00:00:00 | 女   |
| 13   | 孙七   | 2014-06-01 00:00:00 | 女   |
+------+--------+---------------------+------+
8 rows in set (0.00 sec)

8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信

①从sc表查询01同学的所有选课cid

mysql> select sc.cid from sc
    -> where sc.sid = '01';

+------+
| cid  |
+------+
| 01   |
| 02   |
| 03   |
+------+
3 rows in set (0.00 sec)

②从sc表查询所有同学的sid如果其cid在前面的结果中

mysql> select sc.sid from sc
    ->     where sc.cid in(
    ->         select sc.cid from sc
    ->         where sc.sid = '01'
    -> );

+------+
| sid  |
+------+
| 01   |
| 01   |
| 01   |
…………
+------+
18 rows in set (0.00 sec)

③从student表查询所有学生信息如果sid在前面的结果中

mysql> select * from student
    -> where student.sid in (
    ->     select sc.sid from sc
    ->     where sc.cid in(
    ->         select sc.cid from sc
    ->         where sc.sid = '01'
    ->     )
    -> );

+------+--------+---------------------+------+
| SId  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03   | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06   | 吴兰   | 1992-01-01 00:00:00 | 女   |
| 07   | 郑竹   | 1989-01-01 00:00:00 | 女   |
+------+--------+---------------------+------+
7 rows in set (0.00 sec)

注：对于这题，我们还可以反向思考，”至少有一门“的对立面“一门也没有”（高中数学知识），再结合not in去查询

9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息

对于这题有个取巧的想法，查询sc表可以知道，01同学学了三门课，而再查询course可以看到，一共有三门课，这样思路一下就清楚了，统计其它学了三门课的同学，再结合student表输出他们的信息就可以了。具体怎么统计，我想我们可以用到group by 分组与 count函数。具体的查询语句，我就不写了。

10.查询没学过"张三"老师讲授的任一门课程的学生姓名

不多说了，自己看到办吧

mysql> select * from student
    -> where student.sid not in(
    ->     select sc.sid from sc,course,teacher
    ->     where
    ->         sc.cid = course.cid
    ->         and course.tid = teacher.tid
    ->         and teacher.tname= "张三"
    -> );

+------+--------+---------------------+------+
| SId  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 06   | 吴兰   | 1992-01-01 00:00:00 | 女   |
| 09   | 张三   | 2017-12-20 00:00:00 | 女   |
| 10   | 李四   | 2017-12-25 00:00:00 | 女   |
| 11   | 李四   | 2012-06-06 00:00:00 | 女   |
| 12   | 赵六   | 2013-06-13 00:00:00 | 女   |
| 13   | 孙七   | 2014-06-01 00:00:00 | 女   |
+------+--------+---------------------+------+
6 rows in set (0.00 sec)