sql语句练习题（mysql版）

admin管理员组

文章数量:1794759

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

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

本文标签： 练习题语句SQLmySQL