MySQL 基本精选练习题及答案

今日不肯埋头，明日何以抬头！每日一句努力自己的话哈哈~哈喽，今天我将给大家带来一篇《MySQL 基本精选练习题及答案》，主要内容是讲解等等，感兴趣的朋友可以收藏或者有更好的建议在评论提出，我都会认真看的！大家一起进步，一起学习！

表名和字段（mysql）

1. 学生桌
   学生（s_id，s_name，s_birth，s_sex）
   学生证、学生姓名、出生日期、学生性别

2. 课程表
   
   课程(c_id, c_name, t_id)
   课程id、课程名称、教师id

3. 老师桌
   
   老师（t_id，t_name）
   老师id、老师姓名

4. 分数表
   
   分数(s_id, c_id, s_score)
   学生id、课程id、分数

测试数据 - 创建表

1. 学生桌

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`)  
);

1. 课程表

create table  `course`(  
`c_id`  varchar(20),  
`c_name`  varchar(20) not null default '',  
`t_id`  varchar(20) not null,  
primary key(`c_id`)  
);

1. 老师桌

create table  `teacher`(  
`t_id`  varchar(20),  
`t_name`  varchar(20) not null default '',  
primary key(`t_id`)  
);

1. 分数表

create table  `score`(  
`s_id`  varchar(20),  
`c_id`  varchar(20),  
`s_score`  int(3),  
primary key(`s_id`,`c_id`)  
);

1. 将测试数据插入student表

insert into student values('01', 'john doe', '1990-01-01', 'male');  
insert into student values('02', 'jane smith', '1990-12-21', 'male');  
insert into student values('03', 'michael brown', '1990-05-20', 'male');  
insert into student values('04', 'emily davis', '1990-08-06', 'male');  
insert into student values('05', 'lucy johnson', '1991-12-01', 'female');  
insert into student values('06', 'sophia williams', '1992-03-01', 'female');  
insert into student values('07', 'olivia taylor', '1989-07-01', 'female');  
insert into student values('08', 'victoria king', '1990-01-20', 'female');

1. 将测试数据插入课程表

insert into course values('01', 'literature', '02');  
insert into course values('02', 'mathematics', '01');  
insert into course values('03', 'english', '03');

1. 将测试数据插入教师表

insert into teacher values('01', 'andrew');  
insert into teacher values('02', 'bethany');  
insert into teacher values('03', 'charlie');

1. 成绩单测试数据

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);

练习题和 sql 语句

1. 检索“01”课程成绩高于“02”课程成绩的学生的信息和课程成绩

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'  
where b.s_score > coalesce(c.s_score, 0); -- using coalesce instead of or c.c_id = null  

-- alternatively  
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;

1. 检索“01”课程成绩低于“02”课程成绩的学生的信息和课程成绩

select a.*, b.s_score as '01_score', c.s_score as '02_score'  
from student a  
left join score b on a.s_id = b.s_id and b.c_id = '01'  
join score c on a.s_id = c.s_id and c.c_id = '02'  
where coalesce(b.s_score, 0) < c.s_score; -- using coalesce for clarity

1. 检索平均分60分以上学生的学号、姓名、平均分

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(a.s_score) >= 60;

1. 检索平均分低于60分的学生（包括没有分数的学生）的学号、姓名、平均分

select b.s_id, b.s_name, round(avg(a.s_score), 2) as avg_score  
from student b  
left join score a on b.s_id = a.s_id  
group by b.s_id, b.s_name  
having avg(a.s_score) < 60  
union  
select a.s_id, a.s_name, 0 as avg_score  
from student a  
where a.s_id not in (select distinct s_id from score);

1. 检索学生 id、姓名、所选课程总数以及所有课程的总成绩

select a.s_id, a.s_name, count(b.c_id) as sum_course, sum(b.s_score) as sum_score  
from student a  
left join score b on a.s_id = b.s_id  
group by a.s_id, a.s_name;

1. 查询姓“smith”的老师数量

select  count(t_id) from teacher where t_name like  'smith%';

1. 查询上过“john doe”老师授课的学生信息

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 = 'john doe'  
    )  
);

1. 查询未上过“john doe”老师授课的学生信息

select *  
from student c  
where c.s_id not in (  
    select a.s_id  
    from student a  
    join score b on a.s_id = b.s_id  
    where b.c_id in (  
        select a.c_id  
        from course a  
        join teacher b on a.t_id = b.t_id  
        where t_name = 'john doe'  
    )  
);

1. 查询id为“math101”和“science101”的两门课程的学生信息

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 = 'math101'  
and c.c_id = 'science101';

1. 查询已选修过id为“math101”的课程但未选修过id为“science101”的课程的学生信息

select a.*
from student a
where a.s_id in (select s_id from score where c_id = 'math101')
and a.s_id not in (select s_id from score where c_id = 'science101');

1. 查询未修完所有课程的学生信息

-- @wendiepei's approach
select s.*
from student s
left join score s1 on s1.s_id = s.s_id
group by s.s_id
having count(s1.c_id) < (select count(*) from course);
-- @k1051785839's approach
select *  
from student  
where s_id not in (  
    select s_id   
    from score t1    
    group by s_id   
    having count(*) = (select count(distinct c_id) from course)  
);

1. 查询学过至少一门与学号‘01’共同课程的学生信息

select *   
from student   
where s_id in (  
    select distinct a.s_id   
    from score a   
    where a.c_id in (  
        select c_id   
        from score   
        where s_id = '01'  
    )  
);

1. 查询与学号‘01’选修过完全相同课程的学生信息

select
 t3.*
from
 (
  select
   s_id,
   group_concat(c_id order by c_id) group1
  from
   score
  where
   s_id <> '01'
  group by
   s_id
 ) t1
inner join (
 select
  group_concat(c_id order by c_id) group2
 from
  score
 where
  s_id = '01'
 group by
  s_id
) t2 on t1.group1 = t2.group2
inner join student t3 on t1.s_id = t3.s_id

1. 查询没有上过“tom”老师所教课程的学生姓名

select a.s_name from student a where a.s_id not in (
    select s_id from score where c_id = 
                (select c_id from course where t_id =(
                    select t_id from teacher where t_name = 'tom')));

1. 查询两门及两门以上课程不及格的学生的学号、姓名、平均分

select a.s_id, a.s_name, round(avg(b.s_score), 2) as average_score  
from student a  
left join score b on a.s_id = b.s_id  
where a.s_id in (  
    select s_id  
    from score  
    where s_score < 60  
    group by s_id  
    having count(*) >= 2  
)  
group by a.s_id, a.s_name;

1. 检索课程“01”得分低于 60 分的学生信息，按分数降序排列。

select a.*, b.c_id, b.s_score  
from student a  
join score b on a.s_id = b.s_id  
where b.c_id = '01' and b.s_score < 60  
order by b.s_score desc;

1. 显示所有课程的成绩以及每个学生的平均成绩，按平均成绩从高到低排序。

select   
    a.s_id,  
    max(case when c_id = '01' then s_score end) as chinese,  
    max(case when c_id = '02' then s_score end) as math,  
    max(case when c_id = '03' then s_score end) as english,  
    round(avg(s_score), 2) as average_score  
from score a  
group by a.s_id  
order by average_score desc;

1. 查询每门课程的最高分、最低分、平均分、及格率、中等率、良好率、优秀率。按以下格式显示：课程 id、课程名称、最高分、最低分、平均分、通过率、中等率、良好率、优秀率。 -- 通过为 >=60，中等为 70-80，良好为 80-90，优秀为 >=90

select   
    a.c_id,  
    b.c_name,  
    max(s_score) as highestscore,  
    min(s_score) as lowestscore,  
    round(avg(s_score), 2) as averagescore,  
    round(100 * (sum(case when s_score >= 60 then 1 else 0 end) / count(s_score)), 2) as passrate,  
    round(100 * (sum(case when s_score between 70 and 80 then 1 else 0 end) / count(s_score)), 2) as mediumrate,  
    round(100 * (sum(case when s_score between 80 and 90 then 1 else 0 end) / count(s_score)), 2) as goodrate,  
    round(100 * (sum(case when s_score >= 90 then 1 else 0 end) / count(s_score)), 2) as excellentrate  
from   
    score a   
left join   
    course b on a.c_id = b.c_id   
group by   
    a.c_id, b.c_name;

1. 按课程对分数进行排序并显示排名。 mysql没有内置的rank()函数，因此我们将使用变量来模拟它。

select   
    a.s_id,  
    a.c_id,  
    @rank := if(@prev_score = a.s_score, @rank, @rank + 1) as rank_without_ties,  
    @prev_score := a.s_score as score  
from   
    (select s_id, c_id, s_score from score order by c_id, s_score desc) a,  
    (select @rank := 0, @prev_score := null) r  
order by   
    a.c_id, a.rank_without_ties;

1. 查询每个学生的总成绩并进行排名

select   
    a.s_id,  
    @rank := if(@prev_score = a.sum_score, @rank, @rank + 1) as rank,  
    @prev_score := a.sum_score as total_score  
from   
    (select s_id, sum(s_score) as sum_score from score group by s_id order by sum_score desc) a,  
    (select @rank := 0, @prev_score := null) r  
order by   
    total_score desc;

1. 查询不同老师讲授的不同课程的平均分，从高到低排序

select   
    a.t_id,  
    c.t_name,  
    a.c_id,  
    round(avg(s_score), 2) as avg_score   
from   
    course a  
left join   
    score b on a.c_id = b.c_id   
left join   
    teacher c on a.t_id = c.t_id  
group by   
    a.c_id, a.t_id, c.t_name   
order by   
    avg_score desc;

1. 查询所有课程排名第二、第三名的学生信息及其成绩

(select   
    d.*,  
    c.ranking,  
    c.s_score,  
    c.c_id  
from   
    (select   
        s_id,   
        s_score,   
        c_id,   
        @rank := if(@prev_cid = c_id, @rank + 1, 1) as ranking,  
        @prev_cid := c_id  
    from   
        score,   
        (select @rank := 0, @prev_cid := null) as var_init  
    where   
        c_id = '01'  
    order by   
        c_id, s_score desc  
    ) c  
left join   
    student d on c.s_id = d.s_id  
where   
    c.ranking between 2 and 3  
)  
union  
(select   
    d.*,  
    c.ranking,  
    c.s_score,  
    c.c_id  
from   
    (select similar structure as above but with c_id = '02' in the where clause) c  
left join   
    student d on c.s_id = d.s_id  
where   
    c.ranking between 2 and 3  
)  
union  
(select similar structure as above but with c_id = '03' in the where clause);

1. 计算每个科目每个分数范围内的学生人数：

select distinct f.c_name, a.c_id,
       b.`85-100`, b.percentage as `[85-100] percentage`,
       c.`70-85`, c.percentage as `[70-85] percentage`,
       d.`60-70`, d.percentage as `[60-70] percentage`,
       e.`0-60`, e.percentage as `[0-60] percentage`
from score a
    left join (
        select c_id,
               sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) as `85-100`,
               round(100*(sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end)/count(*)),2) as percentage
        from score group by c_id
    ) b on a.c_id = b.c_id
    left join (
        select c_id,
               sum(case when s_score > 70 and s_score <= 85 then 1 else 0 end) as `70-85`,
               round(100*(sum(case when s_score > 70 and s_score <= 85 then 1 else 0 end)/count(*)),2) as percentage
        from score group by c_id
    ) c on a.c_id = c.c_id
    left join (
        select c_id,
               sum(case when s_score > 60 and s_score <= 70 then 1 else 0 end) as `60-70`,
               round(100*(sum(case when s_score > 60 and s_score <= 70 then 1 else 0 end)/count(*)),2) as percentage
        from score group by c_id
    ) d on a.c_id = d.c_id
    left join (
        select c_id,
               sum(case when s_score >= 0 and s_score <= 60 then 1 else 0 end) as `0-60`,
               round(100*(sum(case when s_score >= 0 and s_score <= 60 then 1 else 0 end)/count(*)),2) as percentage
        from score group by c_id
    ) e on a.c_id = e.c_id
    left join course f on a.c_id = f.c_id;

1. 查询学生平均成绩及排名：

select a.s_id,
       @i:=@i+1 as 'no gaps in ranking',
       @k:=(case when @avg_score=a.avg_s then @k else @i end) as 'with gaps in ranking',
       @avg_score:=avg_s as 'average score'
from (select s_id, round(avg(s_score),2) as avg_s from score group by s_id order by avg_s desc) a,
     (select @avg_score:=0, @i:=0, @k:=0) b;

1. 查询各科目前三名学生的记录：

select a.s_id, a.c_id, a.s_score from score a 
    left join score b on a.c_id = b.c_id and a.s_score < b.s_score
    group by a.s_id, a.c_id, a.s_score 
    having count(b.s_id) < 3
    order by a.c_id, a.s_score desc;

1. 查询每门课程的报名人数：

select c_id, count(s_id) from score group by c_id;

1. 查询恰好修读两门课程的学生的学号和姓名：

select s_id, s_name from student 
    where s_id in (select s_id from score group by s_id having count(c_id) = 2);

1. 查询男女学生人数：

select s_sex, count(s_sex) as count from student group by s_sex;

1. 查询姓名中包含“tom”字符的学生信息：

select * from student where s_name like '%tom%';

1. 查询同名同性别的学生列表，以及同名学生的个数：

select a.s_name, a.s_sex, count(*) as count from student a  
    join student b on a.s_id != b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex
    group by a.s_name, a.s_sex;

1. 1990年出生学生查询名单：

select s_name from student where s_birth like '1990%';

1. 查询每门课程的平均成绩，按平均成绩降序排列，如果平均成绩相同则按课程id升序排列：

select c_id, round(avg(s_score), 2) as avg_score from score group by c_id order by avg_score desc, c_id asc;

1. 查询平均分>=85的学生的学号、姓名、平均分：

select a.s_id, b.s_name, round(avg(a.s_score), 2) as avg_score from score a
    left join student b on a.s_id = b.s_id group by s_id having avg_score >= 85;

1. 查询“数学”课程成绩低于60分的学生姓名和成绩：

select a.s_name, b.s_score from student a 
    join score b on a.s_id = b.s_id 
    where b.c_id = (select c_id from course where c_name = 'mathematics') 
    and b.s_score < 60;

1. 查询所有学生的课程成绩和总成绩：

select a.s_id, a.s_name,
    sum(case c.c_name when 'history' then b.s_score else 0 end) as 'history',
    sum(case c.c_name when 'mathematics' then b.s_score else 0 end) as 'mathematics',
    sum(case c.c_name when 'politics' then b.s_score else 0 end) as 'politics',
    sum(b.s_score) as 'total score'
from student a 
left join score b on a.s_id = b.s_id 
left join course c on b.c_id = c.c_id 
group by a.s_id, a.s_name;

1. 查询任意课程70分以上学生的姓名、课程名称和成绩：

select a.s_name, b.c_name, c.s_score from student a 
    left join score c on a.s_id = c.s_id 
    left join course b on c.c_id = b.c_id 
    where c.s_score >= 70;

1. 查询学生不及格的课程：

select a.s_id, a.c_id, b.c_name, a.s_score from score a 
    left join course b on a.c_id = b.c_id 
    where a.s_score < 60;

1. 查询‘01’课程中成绩80分以上的学生的学号和姓名：

select a.s_id, b.s_name from score a 
    left join student b on a.s_id = b.s_id 
    where a.c_id = '01' and a.s_score > 80;

1. 统计每门课程的学生人数：

select count(*) from score group by c_id;

1. 查询“tom”老师所教课程中得分最高的学生信息： -- 获取教师id

select c_id from course c, teacher d where c.t_id = d.t_id and d.t_name = 'tom';

-- 获得最高分（可能有平局）

select max(s_score) from score where c_id = '02';

--获取信息

select a.*, b.s_score, b.c_id, c.c_name from student a 
    left join score b on a.s_id = b.s_id 
    left join course c on b.c_id = c.c_id 
    where b.c_id = (select c_id from course c, teacher d where c.t_id = d.t_id and d.t_name = 'tom')
    and b.s_score in (select max(s_score) from score where c_id = '02');

1. 查询学生id、课程id、成绩，不同课程成绩相同：

select distinct b.s_id, b.c_id, b.s_score from score a, score b 
    where a.c_id != b.c_id and a.s_score = b.s_score;

1. 查询每门课程的前两名成绩：

select a.s_id, a.c_id, a.s_score from score a 
    where (select count(1) from score b where b.c_id = a.c_id and b.s_score >= a.s_score) <= 2 order by a.c_id;

1. 统计每门课程的注册学生人数（超过5名学生的课程）：

select c_id, count(*) as total from score group by c_id having total > 5 order by total, c_id asc;

1. 查询已报名至少两门课程的学生id：

select s_id, count(*) as sel from score group by s_id having sel >= 2;

1. 查询已报名所有课程的学生信息：

select * from student where s_id in (select s_id from score group by s_id having count(*) = (select count(*) from course));

1. 查询每个学生的年龄： -- 根据出生日期计算年龄；如果当前月份/日期早于出生日期的月份/日期，则减一

select s_birth, (date_format(now(), '%y') - date_format(s_birth, '%y') - 
    (case when date_format(now(), '%m%d') > date_format(s_birth, '%m%d') then 0 else 1 end)) as age
    from student;

1. 查询本周生日的学生：

select * from student where week(date_format(now(), '%y%m%d')) = week(s_birth);

1. 查询下周生日的学生：

select * from student where week(date_format(now(), '%y%m%d')) + 1 = week(s_birth);

1. 查询本月生日的学生：

select * from student where month(date_format(now(), '%y%m%d')) = month(s_birth);

1. 查询下个月生日的学生：

select * from student where month(date_format(now(), '%Y%m%d')) + 1 = month(s_birth);

好的，如果您觉得这篇文章有帮助，请随时分享给更多人。

如果你想找一个sql工具来练习，可以试试我们的sqlynx，它界面简单，易于使用。 https://www.sqlynx.com/download/ 免费下载

好了，本文到此结束，带大家了解了《MySQL 基本精选练习题及答案》，希望本文对你有所帮助！关注golang学习网公众号，给大家分享更多数据库知识！