sql语句练习题（mysql版）

众所周知，学习完sql的基本语法之后，是需要一定量的练习才能更好的掌握sql语句的书写。因此，这里整理了几十道sql语句的练习题用于巩固sql语句的书写；当然这里只考虑能得到查询结果，不考虑sql语句的性能优化之类的问题。有需要的小伙伴们可以练习一下，一定会有收获的。本文所有的题目都是整理自互联网。

1.创建表

-- 1.学生表
create table students (
    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)    
);


-- 2.课程表
create table courses (
    c_id         varchar(20),
    c_name       varchar(20) not null default '',
    t_id         varchar(20) not null,
    primary key(c_id)
);


-- 教师表
create table teachers (
    t_id        varchar(20),
    t_name      varchar(20) not null default '',
    primary key(t_id)
);


-- 成绩表
create table scores (
    s_id        varchar(20),
    c_id        varchar(20),
    s_score     int(3),
    primary key(s_id, c_id)
);

2.插入初始数据

-- 插入学生表数据
insert into students values('01' , '赵雷' , '1990-01-01' , '男');
insert into students values('02' , '钱电' , '1990-12-21' , '男');
insert into students values('03' , '孙风' , '1990-05-20' , '男');
insert into students values('04' , '李云' , '1990-08-06' , '男');
insert into students values('05' , '周梅' , '1991-12-01' , '女');
insert into students values('06' , '吴兰' , '1992-03-01' , '女');
insert into students values('07' , '郑竹' , '1989-07-01' , '女');
insert into students values('08' , '王菊' , '1990-01-20' , '女');


-- 插入课程表数据
insert into courses values('01' , '语文' , '02');
insert into courses values('02' , '数学' , '01');
insert into courses values('03' , '英语' , '03');


-- 插入教师表数据
insert into teachers values('01' , '张三');
insert into teachers values('02' , '李四');
insert into teachers values('03' , '王五');

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

各表数据如下：

students:

 courses:

teachers: 

scores: 

3.sql语句练习

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

/*
使用学生表分别和两个成绩表连接，其中一个查询出01课程的成绩，另一个查询出02课程的成绩；最后使用where选出01课程成绩大于02课程成绩

*/

select st.*, sc1.s_score as '语文', sc2.s_score as '数学'
from students st 
left join scores sc1 on st.s_id = sc1.s_id and sc1.c_id = '01' 
left join scores sc2 on st.s_id = sc2.s_id and sc2.c_id = '02'
where sc1.s_score > sc2.s_score;

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

-- 思路与第一题类似

select st.*, sc1.s_score as '语文', sc2.s_score as '数学'
from students st 
left join scores sc1 on st.s_id = sc1.s_id and sc1.c_id = '01' 
left join scores sc2 on st.s_id = sc2.s_id and sc2.c_id = '02'
where sc1.s_score < sc2.s_score;

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

/*
    课程表和学生表连接，课程表中以学生id分组，然后计算成绩平均值，最后使用having过滤平均分及格的行
    avg()函数求平均值
    round()函数用于数据的四舍五入，后面的2表示保留2位小数
*/

select st.s_id, st.s_name, round(avg(sc.s_score), 2) as '平均成绩' from students st 
left join scores sc on sc.s_id = st.s_id 
group by st.s_id having avg(sc.s_score) >= 60;

4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

/*
    思路以上一题类似，这里多了一个处理无成绩的情况；
    case 
        when   then
        else
    end 
    类似于java中的分支结构
*/

select st.s_id, st.s_name, (case when round(avg(sc.s_score), 2) is null then 0 else round(avg(sc.s_score), 2) end) as '平均成绩' from students st 
left join scores sc on sc.s_id = st.s_id 
group by st.s_id 
having avg(sc.s_score) < 60 or avg(sc.s_score) is null;

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

/*
    连接学生表和课程表，以学生id分组，统计每个学生的选棵数，统计每个学生的所有课程分数
*/

select st.s_id, st.s_name, count(sc.c_id) as '选课总数', sum(case when sc.s_score is null then 0 else sc.s_score end) '总成绩' from students st 
left join scores sc on sc.s_id = st.s_id 
group by st.s_id;

 6.查询"李"姓老师的数量

select count(t_id) as '人数' from teachers where t_name like '李%';

7. 查询学过"张三"老师授课的同学的信息

-- 连接4个表，筛选条件是教师姓名是张三

select st.* from students st 
left join scores sc on st.s_id = sc.s_id 
left join courses c on c.c_id = sc.c_id 
left join teachers t on t.t_id = c.t_id
where t.t_name = '张三';

8. 查询没学过"张三"老师授课的同学的信息

-- 先查询张三教的课的id
select c.c_id from courses c left join teachers t on t.t_id = c.t_id where t.t_name = '张三';

-- 查询成绩表中有张三教的课的成绩的学生id
select sc.s_id from scores sc where in(
    select c.c_id from courses c left join teachers t on t.t_id = c.t_id where t.t_name = '张三'
);

-- 在学生表中查询学生id不在上面查询的结果中的行
select st.* from students st where st.s_id not in(
    select sc.s_id from scores sc where sc.c_id in(
        select c.c_id from courses c left join teachers t on t.t_id = c.t_id where t.t_name = '张三'
    )
);

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

-- 查询成绩表,获得同时学过课程01和课程02的学生id
-- 这里分两步，先找到课程01对应的学生id，然后找到课程02对应的学生id，取其公共部分
select s_id from scores where c_id = '01';

select s_id from scores where c_id = '02';

-- 取其公共部分
select temp.s_id from (select s_id from scores where c_id = '01') temp where temp.s_id in(
    select s_id from scores where c_id = '02'
);


-- 查询学生表获取学生信息，筛选条件就是上面查询出来的学生id

select st.* from students st where st.s_id in(
 select temp.s_id from (select s_id from scores where c_id = '01') temp where temp.s_id         
 in(
    select s_id from scores where c_id = '02'
  )  
);

 10.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

-- 思路与上一题类似，不同点在于这次不是取公共部分，而是取01中有02中没有的一部分

select s_id from scores where c_id = '01';

select s_id from scores where c_id = '02';

-- 取01中有02中没有的一部分
select temp.s_id from (select s_id from scores where c_id = '01') temp where temp.s_id not in(
    select s_id from scores where c_id = '02'
);


-- 查询学生信息
select st.* from students st where st.s_id in(
    select temp.s_id from (select s_id from scores where c_id = '01') temp where temp.s_id         
    not in(
       select s_id from scores where c_id = '02'
    )
);

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

-- 先查询课程表，得出有多少门课
select count(c_id) from courses;

-- 再查询成绩表，按学生id分组，统计成绩是否有缺少的, 得出不缺少的学生id
-- 这种方案有一个前提，就是学生不能有两门同样的课程成绩，事实上正常情况也确实如此
select s_id from scores group by s_id having count(c_id) = (select count(c_id) from courses);


-- 查询学生信息
select st.* from students st where st.s_id not in(
    select s_id from scores group by s_id having count(c_id) = (select count(c_id) from 
    courses)
);

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

-- 查询学号01学生所学的课程id
select c_id from scores where s_id = '01';


-- 查询学过01学生学过的课程的学生id
select distinct sc.s_id from scores sc where sc.c_id = any(select c_id from scores where s_id = '01') and sc.s_id != '01';


-- 查询学生信息
select st.* from students st where st.s_id in(
    select distinct sc.s_id from scores sc where sc.c_id = any(select c_id from scores         
    where s_id = '01') and sc.s_id != '01'
);

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

-- 查询学号01学生所学的课程id,然后将所有课程id连接
-- 这里使用一个函数group_concat()函数，可以将每列的值连接起来，这里连接结果为01,02,03
select group_concat(c_id) from scores where s_id = '01' order by c_id;


-- 查询学生id
select sc.s_id from scores sc group by sc.s_id having group_concat(sc.c_id) = (
    select group_concat(c_id) from scores where s_id = '01' order by c_id
) and sc.s_id != '01';

-- 查询学生信息
select st.* from students st where st.s_id in(
    select sc.s_id from scores sc group by sc.s_id having group_concat(sc.c_id) = (
        select group_concat(c_id) from scores where s_id = '01' order by c_id
    ) and sc.s_id != '01'
);

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

-- 查询张三所教课程的课程id
select c.c_id from courses c inner join teachers t on c.t_id = t.t_id and t.t_name = '张三';

-- 再连接成绩表，得出学生id
select sc.s_id from scores sc inner join courses c on sc.c_id = c.c_id 
inner join teachers t on c.t_id = t.t_id and t.t_name = '张三';


-- 查询学生信息
select st.s_name from students st where st.s_id not in(
    select sc.s_id from scores sc inner join courses c on sc.c_id = c.c_id 
    inner join teachers t on c.t_id = t.t_id and t.t_name = '张三'
);

 15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

-- 先挑选出分数小于60或者为null的行，然后按照学生id分组，组数要大于2 
select sc.s_id from scores sc where sc.s_score < 60 or sc.score is null 
group by sc.s_id having count(1) >=2;


-- 查询学生表和成绩表，筛选条件是上面查询出来的学生id，最后按学生id分组求平均成绩
select  st.s_id, st.s_name, avg(sc.s_score) from students st 
left join scores sc on sc.s_id = st.s_id
where sc.s_id in(                                              -- 这里筛选条件是sc.s_id
    select sc.s_id from scores sc where sc.s_score < 60 or sc.s_score is null 
    group by sc.s_id having count(1) >=2
)
group by st.s_id;

16. 检索"01"课程分数小于60，按分数降序排列的学生信息

-- 连接学生表和成绩表即可

select st.*, sc.s_score from students st 
left join scores sc on st.s_id = sc.s_id 
where sc.c_id = '01' and sc.s_score < 60
order by sc.s_score desc;

 17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

-- 每门课都使用一个表与学生表连接，然后按学生id分组

select st.s_id, st.s_name, avg(sc4.s_score) '平均分', sc1.s_score '语文', sc2.s_score '数学', sc3.s_score '英语' from students st 
left join scores sc1 on sc1.s_id = st.s_id and sc1.c_id = '01'
left join scores sc2 on sc2.s_id = st.s_id and sc2.c_id = '02'
left join scores sc3 on sc3.s_id = st.s_id and sc3.c_id = '03'
left join scores sc4 on sc4.s_id = st.s_id
group by st.s_id 
order by avg(sc4.s_score) desc;

18. 查询学生的总成绩并进行排名

-- 连接学生表和成绩表，然后按学生id分组

select st.s_id, st.s_name, (case when sum(sc.s_score) is null then 0 else sum(sc.s_score) end) '总成绩' from students st 
left join scores sc on sc.s_id = st.s_id 
group by st.s_id 
order by sum(sc.s_score) desc;

19. 查询不同老师所教不同课程平均分从高到低显示

-- 查询成绩表，按课程id分组，求课程平均分
select c_id, avg(s_score) from scores group by c_id;

-- 接着连接课程表和教师表
select t.t_id, t.t_name, c.c_name, avg(sc.s_score) from scores sc
left join courses c on c.c_id = sc.c_id 
left join teachers t on t.t_id = c.t_id 
group by sc.c_id
order by avg(sc.s_score) desc;

20.统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

-- 查询课程表连接成绩表
select c.c_id, c.c_name,
((select count(1) from scores sc where sc.c_id = c.c_id and sc.s_score <= 100 and sc.s_score > 85) / (select count(1) from scores sc where sc.c_id = c.c_id)) '[100, 85]',

((select count(1) from scores sc where sc.c_id = c.c_id and sc.s_score <= 85 and sc.s_score > 70) / (select count(1) from scores sc where sc.c_id = c.c_id)) '[85, 70]',

((select count(1) from scores sc where sc.c_id = c.c_id and sc.s_score <= 70 and sc.s_score > 60) / (select count(1) from scores sc where sc.c_id = c.c_id)) '[70, 60]',

((select count(1) from scores sc where sc.c_id = c.c_id and sc.s_score <= 60 and sc.s_score > 0) / (select count(1) from scores sc where sc.c_id = c.c_id)) '[60, 0]'

from courses c order by c.c_id;

 21.查询学生平均成绩及其名次

-- 查询学生的平均成绩
select st.s_id, st.s_name, round((case when avg(sc.s_score) is null then 0 else avg(sc.s_score) end), 2) '平均分' from students st 
left join scores sc on sc.s_id = st.s_id
group by st.s_id order by sc.s_score desc;



-- 打印排名
set @i = 0;
select a.*, @i := @i+1 '排名' from(
    select st.s_id, st.s_name, round((case when avg(sc.s_score) is null then 0 else     
    avg(sc.s_score) end), 2) '平均分' from students st 
    left join scores sc on sc.s_id = st.s_id
    group by st.s_id order by sc.s_score desc
) a;

22.查询课程编号01的成绩前三名的记录

select st.s_id, st.s_name, c.c_name, sc.s_score from students st 
left join scores sc on sc.s_id = st.s_id 
inner join courses c on c.c_id = sc.c_id and c.c_id = '01'
order by sc.s_score desc limit 0, 2;

23.查询每门课程被选修的学生数

select c.c_id, c.c_name, count(sc.s_id) from courses c 
left join scores sc on sc.c_id = c.c_id
group by c.c_id;

 

24. 查询出只有两门课程的全部学生的学号和姓名

-- 查询成绩表中只有两个课程的学生id
select s_id from scores group by s_id having count(c_id) = 2;


-- 连接学生表
select st.s_id, st.s_name from students st 
left join scores sc on sc.s_id = st.s_id
group by sc.s_id having count(sc.c_id) = 2;

25. 查询男生、女生人数

select st.s_sex, count(1) from students st group by st.s_sex;

 26.查询名字中含有"风"字的学生信息

select st.* from students st where st.s_name like '%风%';

27. 查询同名同性学生名单，并统计同名人数

select st.*, count(1) from students st group by st.s_name, st.s_sex having count(1) > 1;

28. 查询1990年出生的学生名单

select st.* from students st where st.s_birth like '1990%';

29. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select c.c_id, c.c_name, avg(sc.s_score) from courses c 
inner join scores sc on c.c_id = sc.c_id 
group by sc.c_id order by avg(sc.s_score) desc, c.c_id asc;

30. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select st.s_id, st.s_name, avg(sc.s_score) from students st 
left join scores sc on sc.s_id = st.s_id
group by st.s_id having avg(sc.s_score) >= 85;

 31.查询课程名称为"数学"，且分数低于60的学生姓名和分数

-- 先查询课程是数学，然后成绩低于60的学生id
select sc.s_id from scores sc 
inner join courses c on sc.c_id = c.c_id 
where c.c_name = '数学' and sc.s_score < 60;


-- 连接学生表
select st.s_id, st.s_name, sc.s_score from students st 
inner join scores sc on sc.s_id = st.s_id and sc.s_score < 60
inner join courses c on c.c_id = sc.c_id and c.c_name = '数学';

32. 查询所有学生的课程及分数情况

select st.s_id, st.s_name, c.c_name, sc.s_score from students st 
left join scores sc on sc.s_id = st.s_id 
left join courses c on c.c_id = sc.c_id
order by st.s_id, c.c_name;

33.查询本周过生日的学生

select st.* from students st
where week(now()) = week(date_format(st.s_borth, '%Y%m%d'));

-- 这种处理有点问题

34.查询本月过生日的学生

select st.* from students st
where month(now()) = month(date_format(st.s_birth, '%Y%m%d'));

35. 查询下月过生日的学生

select st.* from students st 
where month(timestampadd(month, 1, now())) = month(date_format(st.s_birth, '%Y%m%d'));

最后还想说一下，只有打好基础了才能走的更远更快， 共勉~