MYSQL面试必练50题练习题

MYSQL面试50题练习

目录

• MYSQL面试50题练习
  • • 1、总共有多少表？
    • 2、表之间的关系
    • 3、准备建表和插入数据
    • 4、题目开始
      • 1、查询课程编号为01的课程比02的课程成绩高的所有学生的学号（重要）
        • 结果如下图：
      • 2、查询平均成绩大于60分的学生的学号和平均成绩
        • 方法1：
        • 结果如下图：
        • 方法2：
      • 3、查询所有学生的学号、姓名、选棵数、总成绩
        • 思路：
        • 接下来就是对两表进行连接，我们选择左连接，为什么不选择内连接？
        • 结果：
        • 注意：
      • 5、查询没有学过张三老师课的学生的学号和姓名（重要）

1、总共有多少表？

四张表

2、表之间的关系

3、准备建表和插入数据

–1.学生表

Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别

–2.课程表

Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号

–3.教师表

Teacher(t_id,t_name) –教师编号,教师姓名

–4.成绩表

Score(s_id,c_id,s_score) –学生编号,课程编号,分数
测试数据
--建表
--学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

4、题目开始

1、查询课程编号为01的课程比02的课程成绩高的所有学生的学号（重要）

-- 1、查询课程编号为01的课程比02的课程成绩高的所有学生的学号(重点)

-- 查询出01课程编号的学生的成绩
select s_id,c_id,s_score from Score where c_id='01';

-- 查询出02课程编号的学生的成绩
select s_id,c_id,s_score from Score where c_id='02';

-- 两表内联，查询

select t1.s_id,Student.s_name as '姓名',t1.s_score as '01',t2.s_score as '02'
 from
 (select s_id,c_id,s_score from Score where c_id='01') t1 
inner JOIN 
(select s_id,c_id,s_score from Score where c_id='02') t2
on t1.s_id=t2.s_id
inner join Student on t1.s_id=Student.s_id
where t1.s_score>t2.s_score;

结果如下图：

2、查询平均成绩大于60分的学生的学号和平均成绩

方法1：

-- 2、查询平均成绩大于60分的学生的学号和平均成绩

 -- 用到了两张表 student 和score
 
 -- 1).先查询出每个学生的平均成绩，按照学号进行分组
select s_id,AVG(s_score) as avg_score from score GROUP BY s_id;

 -- 2)然后将1的结果作为子查询，从中查出学号和平均成绩，并进行筛选
select s_id,ROUND(t1.avg_score,2) from 
(
select s_id,AVG(s_score) as avg_score from score GROUP BY s_id
) t1
where t1.avg_score >60;

结果如下图：

方法2：

-- 简单的方法建议使用这个
select s_id,avg(s_score)
from score
group by s_id
having avg(s_score) >60;

3、查询所有学生的学号、姓名、选棵数、总成绩

首先我们要明确我们使用到了几张表

这里涉及到了两张表：student和score

思路：

首先我们可以想一下，我们需要一张什么样的表，才更容易去求上面的结果
通过下面的图我们就知道，我们只需这些字段就足够了

接下来就是对两表进行连接，我们选择左连接，为什么不选择内连接？

是因为内连接其实求得是交集，当有的学生没有成绩没有选课的时候，这条数据就没了。

select a.s_id ,a.s_name,count(b.c_id),
sum(case when b.s_score is null then 0 else b.s_score end)
from student a
left join score b
on a.s_id=b.s_id 
GROUP BY s_id,a.s_name;

结果：

注意：

一般语句中有 group by 的时候，建议select 中检索的字段要么是聚合函数，要么是group by中包含的字段才建议放在select中。

5、查询没有学过张三老师课的学生的学号和姓名（重要）

-- 5、查询没有学过张三老师课的学生的学号和姓名（重要）

方法1
-- 01查出张三老师的id
select t_id from teacher
where t_name='张三'

-- 02 查出张三老师教的课程是
select c_id
 from course
where t_id=(
select t_id from teacher
where t_name='张三'
)

-- 03 通过score找出考过02中张三老师的课程的学生
select s_id
 from score 
where c_id in (
select c_id
 from course
where t_id=(
select t_id from teacher
where t_name='张三'
)
)

-- 04 
select s_id,s_name
from student
where s_id not in (
select s_id
 from score 
where c_id in (
select c_id
 from course
where t_id=(
select t_id from teacher
where t_name='张三'
)
)
)

方法2

select s_id,s_name from student
where s_id not in 
(
select s_id from score as s 
left JOIN course as c 
on s.c_id=c.c_id
left JOIN teacher t
on c.t_id=t.t_id
where t.t_name ='张三' 
)