SQL数据库面试经典50题
面试题目涉及表的关联、分组、排序、函数，以及时间的处理等等。
·
注意：本文所有的SQL语句是基于MySQL数据库。
0、数据准备：
create table student(sid varchar(10),sname nvarchar(10),sage datetime,ssex nvarchar(10));
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' , '女');
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');
create table teacher(tid varchar(10),tname nvarchar(10));
insert into teacher values('01' , '张三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');
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"课程成绩高的学生的信息及课程分数
1.1、查询同时存在"01"课程和"02"课程的情况
select a.*, b.score as "课程01的分数", c.score as "课程02的分数"
from student a ,sc b, sc c
where a.sid = b.sid and a.sid = c.sid
and b.cid = '01' and c.cid = '02' and b.score > c.score;
+------+--------+---------------------+------+-------------------+-------------------+
| sid | sname | sage | ssex | 课程01的分数 | 课程02的分数 |
+------+--------+---------------------+------+-------------------+-------------------+
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 70.0 | 60.0 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 50.0 | 30.0 |
+------+--------+---------------------+------+-------------------+-------------------+
1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)
select a.*, b.score as "课程01的分数", c.score as "课程02的分数"
from student a
left join sc b on a.sid = b.sid and b.cid = '01'
left join sc c on a.sid = c.sid and c.cid = '02'
where b.score > ifnull(c.score,0);
+------+--------+---------------------+------+-------------------+-------------------+
| sid | sname | sage | ssex | 课程01的分数 | 课程02的分数 |
+------+--------+---------------------+------+-------------------+-------------------+
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 70.0 | 60.0 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 50.0 | 30.0 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | 31.0 | NULL |
+------+--------+---------------------+------+-------------------+-------------------+
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
2.1、查询同时存在"01"课程和"02"课程的情况
select a.*, b.score as "课程01的分数", c.score as "课程02的分数"
from student a ,sc b, sc c
where a.sid = b.sid and a.sid = c.sid and b.cid = '01' and c.cid = '02' and b.score < c.score;
+------+--------+---------------------+------+-------------------+-------------------+
| sid | sname | sage | ssex | 课程01的分数 | 课程02的分数 |
+------+--------+---------------------+------+-------------------+-------------------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 80.0 | 90.0 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 76.0 | 87.0 |
+------+--------+---------------------+------+-------------------+-------------------+
2.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)
select a.*, b.score as "课程01的分数", c.score as "课程02的分数"
from student a
left join sc b on a.sid = b.sid and b.cid = '01'
left join sc c on a.sid = c.sid and c.cid = '02'
where ifnull(b.score,0) < c.score;
+------+--------+---------------------+------+-------------------+-------------------+
| sid | sname | sage | ssex | 课程01的分数 | 课程02的分数 |
+------+--------+---------------------+------+-------------------+-------------------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 80.0 | 90.0 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 76.0 | 87.0 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | NULL | 89.0 |
+------+--------+---------------------+------+-------------------+-------------------+
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select a.sid, a.sname, convert(avg(b.score), decimal(4,2)) as avg_score
from student a, sc b
where a.sid = b.sid
group by a.sid,a.sname
having avg(b.score) >= 60
order by a.sid;
+------+--------+-----------+
| sid | sname | avg_score |
+------+--------+-----------+
| 01 | 赵雷 | 89.67 |
| 02 | 钱电 | 70.00 |
| 03 | 孙风 | 80.00 |
| 05 | 周梅 | 81.50 |
| 07 | 郑竹 | 93.50 |
+------+--------+-----------+
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
4.1、查询在sc表存在成绩的学生信息的SQL语句
select a.sid, a.sname, convert(avg(b.score), decimal(4,2)) as avg_scoe
from student a, sc b
where a.sid = b.sid
group by a.sid, a.sname
having avg(b.score) < 60
order by a.sid;
+------+--------+----------+
| sid | sname | avg_scoe |
+------+--------+----------+
| 04 | 李云 | 33.33 |
| 06 | 吴兰 | 32.50 |
+------+--------+----------+
4.2、查询在sc表中不存在成绩的学生信息的SQL语句
select a.sid, a.sname, ifnull(cast(avg(b.score) as decimal(18,2)),0) as avg_scoe
from student a left join sc b on a.sid = b.sid
group by a.sid, a.sname
having ifnull(cast(avg(b.score) as decimal(18,2)),0) < 60
order by a.sid;
+------+--------+----------+
| sid | sname | avg_scoe |
+------+--------+----------+
| 04 | 李云 | 33.33 |
| 06 | 吴兰 | 32.50 |
| 08 | 王菊 | 0.00 |
+------+--------+----------+5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
5.1、查询所有有成绩的SQL
select a.sid, a.sname, count(b.score) score_count, sum(b.score) as score_sum
from student a, sc b
where a.sid = b.sid
group by a.sid, a.sname
order by a.sid;
+------+--------+-------------+-----------+
| sid | sname | score_count | score_sum |
+------+--------+-------------+-----------+
| 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 |
+------+--------+-------------+-----------+
5.2、查询所有(包括有成绩和无成绩)的SQL
select a.sid, a.sname, count(b.score) score_count, sum(b.score) as score_sum
from student a left join sc b on a.sid = b.sid
group by a.sid, a.sname
order by a.sid;
+------+--------+-------------+-----------+
| sid | sname | score_count | score_sum |
+------+--------+-------------+-----------+
| 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 |
| 08 | 王菊 | 0 | NULL |
+------+--------+-------------+-----------+
6、查询"李"姓老师的数量
-- 方法1
select count(*) t_count from teacher t where t.tname like '李%';
-- 方法2
select count(*) t_count from teacher t where left(t.tname,1) = '李';
+---------+
| t_count |
+---------+
| 1 |
+---------+
7、查询学过"张三"老师授课的同学的信息
select a.sid, a.sname, a.sage, a.ssex
from student a, sc b, course c, teacher d
where a.sid = b.sid and b.cid = c.cid and c.tid = d.tid
and d.tname = '张三';
+------+--------+---------------------+------+
| sid | sname | sage | ssex |
+------+--------+---------------------+------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |
+------+--------+---------------------+------+
8、查询没学过"张三"老师授课的同学的信息
select a.sid, a.sname, a.sage, a.ssex
from student a
where a.sid not in (
select distinct b.sid
from sc b, course c, teacher d
where b.cid = c.cid and c.tid = d.tid and d.tname = '张三');
+------+--------+---------------------+------+
| sid | sname | sage | ssex |
+------+--------+---------------------+------+
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 |
+------+--------+---------------------+------+9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
-- 方法一
select a.sid, a.sname, a.sage, a.ssex
from student a, sc b
where a.sid = b.sid and b.cid = '01'
and exists(select 1 from sc c where b.sid = c.sid and c.cid = '02');
-- 方法二
select a.sid, a.sname, a.sage, a.ssex
from student a
where a.sid in
(
select t.sid
from
(
select a.sid from student a, sc b where a.sid = b.sid and b.cid = '01'
union all
select a.sid from student a, sc b where a.sid = b.sid and b.cid = '02'
) t
group by t.sid
having count(t.sid) = 2
);
+------+--------+---------------------+------+
| sid | sname | sage | ssex |
+------+--------+---------------------+------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
+------+--------+---------------------+------+
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
-- 方法一
select a.sid, a.sname, a.sage, a.ssex
from student a, sc b
where a.sid = b.sid and b.cid = '01'
and not exists(select 1 from sc c where b.sid = c.sid and c.cid = '02');
-- 方法二
select a.sid, a.sname, a.sage, a.ssex
from student a, sc b
where a.sid = b.sid and b.cid = '01'
and a.sid not in (select c.sid from sc c where b.sid = c.sid and c.cid = '02');
+------+--------+---------------------+------+
| sid | sname | sage | ssex |
+------+--------+---------------------+------+
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
+------+--------+---------------------+------+
11、查询没有学全所有课程的同学的信息
-- 学过课程，并且没有学全的同学
select a.sid, a.sname, a.sage, a.ssex
from student a
where a.sid in (select b.sid from sc b group by b.sid having count(b.cid) < (select count(c.cid) from course c));
+------+--------+---------------------+------+
| sid | sname | sage | ssex |
+------+--------+---------------------+------+
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |
+------+--------+---------------------+------+
-- 包含一门课也没有学过的同学，没有学全所有课程的同学
select a.sid, a.sname, a.sage, a.ssex
from student a left join sc b on a.sid = b.sid
group by a.sid, a.sname, a.sage, a.ssex
having count(b.cid) < (select count(c.cid) from course c);
+------+--------+---------------------+------+
| sid | sname | sage | ssex |
+------+--------+---------------------+------+
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 |
+------+--------+---------------------+------+
12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select distinct a.sid, a.sname, a.sage, a.ssex
from student a, sc b
where a.sid = b.sid and b.cid in (select cid from sc b where sid = '01')
and a.sid <> '01';
+------+--------+---------------------+------+
| sid | sname | sage | ssex |
+------+--------+---------------------+------+
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |
+------+--------+---------------------+------+
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select a.sid, a.sname, a.sage, a.ssex
from student a where a.sid in
(
select distinct b.sid from sc b where b.sid <> '01' and b.cid in (select c.cid from sc c where c.sid = '01')
group by b.sid
having count(b.sid) = (select count(*) from sc d where d.sid = '01')
);
+------+--------+---------------------+------+
| sid | sname | sage | ssex |
+------+--------+---------------------+------+
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
+------+--------+---------------------+------+
-- 如果01学习了3门课，其他人可以也学习3门课，但是不相同的课程，就使用下面的语句
select a.sid, a.sname, a.sage, a.ssex
from student a where a.sid in
(
select distinct b.sid from sc b,sc d where b.cid = d.cid and d.sid = '01' and b.sid <> '01' and b.cid in (select c.cid from sc c where c.sid = '01')
group by b.sid
having count(b.sid) = (select count(*) from sc d where d.sid = '01')
);
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select a.sid, a.sname, a.sage, a.ssex
from student a
where a.sid not in (
select b.sid
from sc b,course c, teacher d
where b.cid = c.cid and c.tid = d.tid and d.tname = '张三'
);
+------+--------+---------------------+------+
| sid | sname | sage | ssex |
+------+--------+---------------------+------+
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 |
+------+--------+---------------------+------+
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select a.sid, a.sname, convert(avg(b.score), decimal(6,2)) as avg_score
from student a left join sc b on a.sid = b.sid and ifnull(b.score,0) < 60
group by a.sid, a.sname
having count(b.sid) >= 2;
select a.sid, a.sname, convert(avg(b.score), decimal(6,2)) as avg_score
from student a left join sc b on a.sid = b.sid
where ifnull(b.score,0) < 60
group by a.sid, a.sname
having count(1) >= 2;
+------+--------+-----------+
| sid | sname | avg_score |
+------+--------+-----------+
| 04 | 李云 | 33.33 |
| 06 | 吴兰 | 32.50 |
+------+--------+-----------+
16、检索"01"课程分数小于60，按分数降序排列的学生信息
select a.sid, a.sname, a.sage, a.ssex, b.score
from student a, sc b
where a.sid = b.sid and b.cid = '01' and b.score < 60
order by b.score desc;
+------+--------+---------------------+------+-------+
| sid | sname | sage | ssex | score |
+------+--------+---------------------+------+-------+
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 50.0 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | 31.0 |
+------+--------+---------------------+------+-------+
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.sid, a.sname,
max(case when c.cname = '语文' then b.score else null end) as yuwen,
max(case when c.cname = '数学' then b.score else null end) as shuxue,
max(case when c.cname = '英语' then b.score else null end) as yingyu,
convert(avg(ifnull(b.score,0)), decimal(4,2)) as avg_score
from student a left join sc b on a.sid = b.sid left join course c on b.cid = c.cid
group by a.sid, a.sname
order by avg(ifnull(b.score,0)) desc;
+------+--------+-------+--------+--------+-----------+
| sid | sname | yuwen | shuxue | yingyu | avg_score |
+------+--------+-------+--------+--------+-----------+
| 07 | 郑竹 | NULL | 89.0 | 98.0 | 93.50 |
| 01 | 赵雷 | 80.0 | 90.0 | 99.0 | 89.67 |
| 05 | 周梅 | 76.0 | 87.0 | NULL | 81.50 |
| 03 | 孙风 | 80.0 | 80.0 | 80.0 | 80.00 |
| 02 | 钱电 | 70.0 | 60.0 | 80.0 | 70.00 |
| 04 | 李云 | 50.0 | 30.0 | 20.0 | 33.33 |
| 06 | 吴兰 | 31.0 | NULL | 34.0 | 32.50 |
| 08 | 王菊 | NULL | NULL | NULL | 0.00 |
+------+--------+-------+--------+--------+-----------+
-- 动态sql
declare @sql nvarchar(4000)
set @sql = 'select a.sid ' + '学生编号' + ' , a.sname ' + '学生姓名'
select @sql = @sql + ',max(case c.cname when '''+cname+''' then b.score else null end) '+cname+' '
from (select distinct cname from course) as t
set @sql = @sql + ' , cast(avg(b.score) as decimal(18,2)) ' + '平均分' + ' from student a left join sc b on a.sid = b.sid left join course c on b.cid = c.cid
group by a.sid , a.sname order by ' + '平均分' + ' desc'
exec(@sql)
18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
-- 方法1
select a.cid, a.cname,
max(b.score) as max_score,
min(b.score) as min_score,
convert(avg(b.score), decimal(4,2)) as avg_score,
convert(sum(case when b.score >= 60 then 1 else 0 end) * 100 / sum(case when b.score then 1 else 0 end), decimal(6,2)) as pre_60,
convert(sum(case when b.score >= 70 and b.score < 80 then 1 else 0 end) * 100 / sum(case when b.score then 1 else 0 end), decimal(6,2)) as pre_70_80,
convert(sum(case when b.score >= 80 and b.score < 90 then 1 else 0 end) * 100 / sum(case when b.score then 1 else 0 end), decimal(6,2)) as pre_80_90,
convert(sum(case when b.score >= 90 then 1 else 0 end) * 100 / sum(case when b.score then 1 else 0 end), decimal(6,2)) as pre_90
from course a, sc b where a.cid = b.cid
group by a.cid, a.cname;
-- 方法2
select a.cid, a.cname,
(select max(b1.score) from sc b1 where b1.cid = a.cid) as max_score,
(select min(b2.score) from sc b2 where b2.cid = a.cid) as min_score,
(select convert(avg(b3.score), decimal(6,2)) from sc b3 where b3.cid = a.cid) as avg_score,
convert(((select count(1) from sc b4 where b4.cid = a.cid and b4.score >= 60) / (select count(1) from sc b4 where b4.cid = a.cid)) * 100, decimal(6,2)) as prd_60,
convert(((select count(1) from sc b5 where b5.cid = a.cid and b5.score >= 70 and b5.score < 80) / (select count(1) from sc b5 where b5.cid = a.cid)) * 100, decimal(6,2)) as prd_70_80,
convert(((select count(1) from sc b6 where b6.cid = a.cid and b6.score >= 80 and b6.score < 90) / (select count(1) from sc b6 where b6.cid = a.cid)) * 100, decimal(6,2)) as prd_80_90,
convert(((select count(1) from sc b7 where b7.cid = a.cid and b7.score >= 90) / (select count(1) from sc b7 where b7.cid = a.cid)) * 100, decimal(6,2)) as prd_90
from course a;
+------+--------+-----------+-----------+-----------+--------+-----------+-----------+--------+
| cid | cname | max_score | min_score | avg_score | pre_60 | pre_70_80 | pre_80_90 | pre_90 |
+------+--------+-----------+-----------+-----------+--------+-----------+-----------+--------+
| 01 | 语文 | 80.0 | 31.0 | 64.50 | 66.67 | 33.33 | 33.33 | 0.00 |
| 02 | 数学 | 90.0 | 30.0 | 72.67 | 83.33 | 0.00 | 50.00 | 16.67 |
| 03 | 英语 | 99.0 | 20.0 | 68.50 | 66.67 | 0.00 | 33.33 | 33.33 |
+------+--------+-----------+-----------+-----------+--------+-----------+-----------+--------+
19、按各科成绩进行排序，并显示排名
select a.sid,a.cid, a.score, row_number() over(partition by a.cid order by a.score desc) as score_order from sc a;
+------+------+-------+-------------+
| sid | cid | score | score_order |
+------+------+-------+-------------+
| 01 | 01 | 80.0 | 1 |
| 03 | 01 | 80.0 | 2 |
| 05 | 01 | 76.0 | 3 |
| 02 | 01 | 70.0 | 4 |
| 04 | 01 | 50.0 | 5 |
| 06 | 01 | 31.0 | 6 |
| 01 | 02 | 90.0 | 1 |
| 07 | 02 | 89.0 | 2 |
| 05 | 02 | 87.0 | 3 |
| 03 | 02 | 80.0 | 4 |
| 02 | 02 | 60.0 | 5 |
| 04 | 02 | 30.0 | 6 |
| 01 | 03 | 99.0 | 1 |
| 07 | 03 | 98.0 | 2 |
| 03 | 03 | 80.0 | 3 |
| 02 | 03 | 80.0 | 4 |
| 06 | 03 | 34.0 | 5 |
| 04 | 03 | 20.0 | 6 |
+------+------+-------+-------------+
20、查询学生的总成绩并进行排名
select a.sid,a.sname, sum(ifnull(b.score, 0)) sum_score, row_number() over(order by sum(ifnull(b.score, 0)) desc) sum_order
from student a left join sc b on a.sid = b.sid
group by a.sid,a.sname;
+------+--------+-----------+-----------+
| sid | sname | sum_score | sum_order |
+------+--------+-----------+-----------+
| 01 | 赵雷 | 269.0 | 1 |
| 03 | 孙风 | 240.0 | 2 |
| 02 | 钱电 | 210.0 | 3 |
| 07 | 郑竹 | 187.0 | 4 |
| 05 | 周梅 | 163.0 | 5 |
| 04 | 李云 | 100.0 | 6 |
| 06 | 吴兰 | 65.0 | 7 |
| 08 | 王菊 | 0.0 | 8 |
+------+--------+-----------+-----------+
21、查询不同老师所教不同课程平均分从高到低显示
select a.tid, a.tname, b.cid, b.cname, convert(avg(c.score), decimal(6,2)) as avg_score
from teacher a, course b, sc c
where a.tid = b.tid and b.cid = c.cid
group by a.tid, a.tname, b.cid, b.cname
order by avg(c.score) desc;
+------+--------+------+--------+-----------+
| tid | tname | cid | cname | avg_score |
+------+--------+------+--------+-----------+
| 01 | 张三 | 02 | 数学 | 72.67 |
| 03 | 王五 | 03 | 英语 | 68.50 |
| 02 | 李四 | 01 | 语文 | 64.50 |
+------+--------+------+--------+-----------+
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select a.sid, a.sname,a.sage, a.ssex, b.cid, b.score, b.score_order
from student a left join
(select a.sid, a.cid, a.score, row_number() over(partition by a.cid order by a.score desc) score_order from sc a) b
on a.sid = b.sid
where b.score_order in (2,3)
order by b.cid, b.score_order;
+------+--------+---------------------+------+------+-------+-------------+
| sid | sname | sage | ssex | cid | score | score_order |
+------+--------+---------------------+------+------+-------+-------------+
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 | 01 | 80.0 | 2 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 01 | 76.0 | 3 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | 02 | 89.0 | 2 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 02 | 87.0 | 3 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | 03 | 98.0 | 2 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 | 03 | 80.0 | 3 |
+------+--------+---------------------+------+------+-------+-------------+
23、统计各科成绩各分数段人数：课程编号,课程名称, 100-85 , 85-70 , 70-60 , 0-60 及所占百分比
select a.cid, b.cname,
sum(case when a.score >= 85 and a.score <= 100 then 1 else 0 end) as sc_100_85,
convert(sum(case when a.score >= 85 and a.score <= 100 then 1 else 0 end) * 100 / sum(case when a.score then 1 else 0 end), decimal(4,2)) as pre_sc_100_85,
sum(case when a.score < 85 and a.score >= 70 then 1 else 0 end) as sc_85_70,
convert(sum(case when a.score < 85 and a.score >= 70 then 1 else 0 end) * 100 / sum(case when a.score then 1 else 0 end), decimal(4,2)) as pre_sc_85_70,
sum(case when a.score < 70 and a.score >= 60 then 1 else 0 end) as sc_70_60,
convert(sum(case when a.score < 70 and a.score >= 60 then 1 else 0 end) * 100 / sum(case when a.score then 1 else 0 end), decimal(4,2)) as pre_sc_70_60,
sum(case when a.score < 60 then 1 else 0 end) as sc_60,
convert(sum(case when a.score < 60 then 1 else 0 end) * 100 / sum(case when a.score then 1 else 0 end), decimal(4,2)) as pre_sc_60
from sc a, course b
where a.cid = b.cid
group by a.cid, b.cname;
+------+--------+-----------+---------------+----------+--------------+----------+--------------+-------+-----------+
| cid | cname | sc_100_85 | pre_sc_100_85 | sc_85_70 | pre_sc_85_70 | sc_70_60 | pre_sc_70_60 | sc_60 | pre_sc_60 |
+------+--------+-----------+---------------+----------+--------------+----------+--------------+-------+-----------+
| 01 | 语文 | 0 | 0.00 | 4 | 66.67 | 0 | 0.00 | 2 | 33.33 |
| 02 | 数学 | 3 | 50.00 | 1 | 16.67 | 1 | 16.67 | 1 | 16.67 |
| 03 | 英语 | 2 | 33.33 | 2 | 33.33 | 0 | 0.00 | 2 | 33.33 |
+------+--------+-----------+---------------+----------+--------------+----------+--------------+-------+-----------+
24、查询学生平均成绩及其名次
select a.sid, a.sname, convert(avg(ifnull(b.score,0)), decimal(4,2)) as avg_score, row_number() over(order by avg(b.score) desc) as cc
from student a left join sc b on a.sid = b.sid
group by a.sid, a.sname;
+------+--------+-----------+----+
| sid | sname | avg_score | cc |
+------+--------+-----------+----+
| 07 | 郑竹 | 93.50 | 1 |
| 01 | 赵雷 | 89.67 | 2 |
| 05 | 周梅 | 81.50 | 3 |
| 03 | 孙风 | 80.00 | 4 |
| 02 | 钱电 | 70.00 | 5 |
| 04 | 李云 | 33.33 | 6 |
| 06 | 吴兰 | 32.50 | 7 |
| 08 | 王菊 | 0.00 | 8 |
+------+--------+-----------+----+
25、查询各科成绩前三名的记录
select a.sid, a.sname, b.cid, b.score, b.cc from student a,
(select a.sid, a.cid, a.score, row_number() over(partition by a.cid order by a.score) cc from sc a) b
where a.sid = b.sid
and b.cc in (1,2,3)
order by b.cid, b.cc;
+------+--------+------+-------+----+
| sid | sname | cid | score | cc |
+------+--------+------+-------+----+
| 06 | 吴兰 | 01 | 31.0 | 1 |
| 04 | 李云 | 01 | 50.0 | 2 |
| 02 | 钱电 | 01 | 70.0 | 3 |
| 04 | 李云 | 02 | 30.0 | 1 |
| 02 | 钱电 | 02 | 60.0 | 2 |
| 03 | 孙风 | 02 | 80.0 | 3 |
| 04 | 李云 | 03 | 20.0 | 1 |
| 06 | 吴兰 | 03 | 34.0 | 2 |
| 03 | 孙风 | 03 | 80.0 | 3 |
+------+--------+------+-------+----+
26、查询每门课程被选修的学生数
select a.cid, a.cname,count(b.sid) stu_count
from course a, sc b
where a.cid = b.cid
group by a.cid, a.cname;
+------+--------+-----------+
| cid | cname | stu_count |
+------+--------+-----------+
| 01 | 语文 | 6 |
| 02 | 数学 | 6 |
| 03 | 英语 | 6 |
+------+--------+-----------+
27、查询出只有两门课程的全部学生的学号和姓名
select a.sid, a.sname, b.sid from student a, sc b where a.sid = b.sid
group by a.sid, a.sname, b.sid
having count(b.cid) = 2;
+------+--------+------+
| sid | sname | sid |
+------+--------+------+
| 05 | 周梅 | 05 |
| 06 | 吴兰 | 06 |
| 07 | 郑竹 | 07 |
+------+--------+------+
28、查询男生、女生人数
select count(*) from student a where a.ssex = '男';
select count(*) from student a where a.ssex = '女';
select sum(case when ssex = '男' then 1 else 0 end) as m_sum, sum(case when ssex = '女' then 1 else 0 end) as w_sum from student;
+-------+-------+
| m_sum | w_sum |
+-------+-------+
| 4 | 4 |
+-------+-------+
29、查询名字中含有"风"字的学生信息
select * from student a where a.sname like '%风%';
+------+--------+---------------------+------+
| sid | sname | sage | ssex |
+------+--------+---------------------+------+
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
+------+--------+---------------------+------+
30、查询同名同性学生名单，并统计同名人数
select a.sname, a.ssex, count(a.sname) from student a
group by a.sname, a.ssex
having count(*) > 1;
Empty set (0.00 sec)
31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
select * from student a where left(convert(a.sage, char(20)), 4) = '1990';
select * from student where year(sage) = '1990';
+------+--------+---------------------+------+
| sid | sname | sage | ssex |
+------+--------+---------------------+------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 |
+------+--------+---------------------+------+
32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select a.cid, convert(avg(a.score), decimal(6,2)) as avg_score from sc a
group by a.cid
order by avg(a.score) desc, a.cid;
+------+-----------+
| cid | avg_score |
+------+-----------+
| 02 | 72.67 |
| 03 | 68.50 |
| 01 | 64.50 |
+------+-----------+
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select a.sid, a.sname, avg(b.score) avg_score from student a, sc b
where a.sid = b.sid
group by a.sid, a.sname
having avg(b.score) >= 85
order by a.sid;
+------+--------+-----------+
| sid | sname | avg_score |
+------+--------+-----------+
| 01 | 赵雷 | 89.66667 |
| 07 | 郑竹 | 93.50000 |
+------+--------+-----------+
34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select a.sname, c.cname, b.score from student a, sc b, course c
where a.sid = b.sid and b.cid = c.cid
and c.cname = '数学' and b.score < 60;
+--------+--------+-------+
| sname | cname | score |
+--------+--------+-------+
| 李云 | 数学 | 30.0 |
+--------+--------+-------+
35、查询所有学生的课程及分数情况
select a.sid, a.sname, a.sage, a.ssex, b.cid, c.cname, b.score
from student a, sc b, course c
where a.sid = b.sid and b.cid = c.cid
order by a.sid, b.cid;
+------+--------+---------------------+------+------+--------+-------+
| sid | sname | sage | ssex | cid | cname | score |
+------+--------+---------------------+------+------+--------+-------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 01 | 语文 | 80.0 |
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 02 | 数学 | 90.0 |
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 03 | 英语 | 99.0 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 01 | 语文 | 70.0 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 02 | 数学 | 60.0 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 03 | 英语 | 80.0 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 | 01 | 语文 | 80.0 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 | 02 | 数学 | 80.0 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 | 03 | 英语 | 80.0 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 01 | 语文 | 50.0 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 02 | 数学 | 30.0 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 03 | 英语 | 20.0 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 01 | 语文 | 76.0 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 02 | 数学 | 87.0 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | 01 | 语文 | 31.0 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | 03 | 英语 | 34.0 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | 02 | 数学 | 89.0 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | 03 | 英语 | 98.0 |
+------+--------+---------------------+------+------+--------+-------+
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select a.sid, a.sname, a.sage, a.ssex, b.cid, c.cname, b.score
from student a, sc b, course c
where a.sid = b.sid and b.cid = c.cid and b.score > 70
order by a.sid, b.cid;
+------+--------+---------------------+------+------+--------+-------+
| sid | sname | sage | ssex | cid | cname | score |
+------+--------+---------------------+------+------+--------+-------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 01 | 语文 | 80.0 |
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 02 | 数学 | 90.0 |
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 03 | 英语 | 99.0 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 03 | 英语 | 80.0 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 | 01 | 语文 | 80.0 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 | 02 | 数学 | 80.0 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 | 03 | 英语 | 80.0 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 01 | 语文 | 76.0 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 02 | 数学 | 87.0 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | 02 | 数学 | 89.0 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | 03 | 英语 | 98.0 |
+------+--------+---------------------+------+------+--------+-------+
37、查询不及格的课程
select a.sid, a.sname, a.sage, a.ssex, b.cid, c.cname, b.score
from student a, sc b, course c
where a.sid = b.sid and b.cid = c.cid and b.score < 60
order by a.sid, b.cid;
+------+--------+---------------------+------+------+--------+-------+
| sid | sname | sage | ssex | cid | cname | score |
+------+--------+---------------------+------+------+--------+-------+
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 01 | 语文 | 50.0 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 02 | 数学 | 30.0 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 03 | 英语 | 20.0 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | 01 | 语文 | 31.0 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | 03 | 英语 | 34.0 |
+------+--------+---------------------+------+------+--------+-------+
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select a.sid, a.sname, b.score from student a, sc b
where a.sid = b.sid and b.cid = '01' and b.score >= 80
order by a.sid;
+------+--------+-------+
| sid | sname | score |
+------+--------+-------+
| 01 | 赵雷 | 80.0 |
| 03 | 孙风 | 80.0 |
+------+--------+-------+
39、求每门课程的学生人数
select a.cid, b.cname, count(a.sid) from sc a, course b
where a.cid = b.cid
group by a.cid, b.cname
order by a.cid;
+------+--------+--------------+
| cid | cname | count(a.sid) |
+------+--------+--------------+
| 01 | 语文 | 6 |
| 02 | 数学 | 6 |
| 03 | 英语 | 6 |
+------+--------+--------------+
40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select a.sid, a.sname, a.sage, a.ssex, b.score from student a, sc b, course c, teacher d
where a.sid = b.sid and b.cid = c.cid and c.tid = d.tid
and d.tname = '张三' and b.score = (select max(score) from sc a, course b, teacher c where a.cid = b.cid and b.tid = c.tid and c.tname = '张三');
+------+--------+---------------------+------+-------+
| sid | sname | sage | ssex | score |
+------+--------+---------------------+------+-------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 90.0 |
+------+--------+---------------------+------+-------+
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select a.sid, a.cid, a.score from sc a, (select a.cid,a.score from sc a group by a.cid, a.score having count(*) > 1) b
where a.score = b.score and a.cid = b.cid
order by a.sid, a.cid;
+------+------+-------+
| sid | cid | score |
+------+------+-------+
| 01 | 01 | 80.0 |
| 02 | 03 | 80.0 |
| 03 | 01 | 80.0 |
| 03 | 03 | 80.0 |
+------+------+-------+
42、查询每门功成绩最好的前两名
select a.sid, a.cid, a.score, a.cc
from (select a.sid, a.cid, a.score, row_number() over(partition by a.cid order by a.score desc) cc from sc a) a where a.cc in (1, 2);
+------+------+-------+----+
| sid | cid | score | cc |
+------+------+-------+----+
| 01 | 01 | 80.0 | 1 |
| 03 | 01 | 80.0 | 2 |
| 01 | 02 | 90.0 | 1 |
| 07 | 02 | 89.0 | 2 |
| 01 | 03 | 99.0 | 1 |
| 07 | 03 | 98.0 | 2 |
+------+------+-------+----+
43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select a.cid, count(a.cid) from sc a group by a.cid having count(a.cid) >= 5
order by count(a.cid) desc, a.cid;
+------+--------------+
| cid | count(a.cid) |
+------+--------------+
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
+------+--------------+
44、检索至少选修两门课程的学生学号
select * from student a where a.sid in
(select a.sid from sc a group by a.sid having count(a.cid) >= 2);
+------+--------+---------------------+------+
| sid | sname | sage | ssex |
+------+--------+---------------------+------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 |
+------+--------+---------------------+------+
45、查询选修了全部课程的学生信息
-- 方法1：通过选修总数来确定学生信息
select * from student a where a.sid in
(select sid from sc group by sid having count(sid) = (select count(*) from course));
-- 方法2：双重否定
select * from student a where a.sid not in
(
select distinct sid
from (select sid, cid from student, course) a
where not exists(select 1 from sc b where a.sid = b.sid and a.cid = b.cid)
);
-- 方法3：双重否定 not exists
select * from student s where not exists(
select 1 from
(select distinct sid
from (select sid, cid from student, course) a where not exists(select 1 from sc b where a.sid = b.sid and a.cid = b.cid)
) m where s.sid = m.sid);
+------+--------+---------------------+------+
| sid | sname | sage | ssex |
+------+--------+---------------------+------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
+------+--------+---------------------+------+
46、查询各学生的年龄
46.1 只按照年份来算
select a.*, convert(date_format(now(), '%Y'), decimal(4, 0)) - convert(date_format(a.sage, '%Y'), decimal(4, 0)) y_age from student a;
+------+--------+---------------------+------+-------+
| sid | sname | sage | ssex | y_age |
+------+--------+---------------------+------+-------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 35 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 35 |
| 03 | 孙风 | 1990-05-20 00:00:00 | 男 | 35 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 | 35 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 | 34 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 | 33 |
| 07 | 郑竹 | 1989-07-01 00:00:00 | 女 | 36 |
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 | 35 |
+------+--------+---------------------+------+-------+
46.2 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
-- 方法1:
select case when (convert(date_format(now(), '%m'), decimal(2, 0)) < convert(date_format(a.sage, '%m'), decimal(2, 0)))
or (convert(date_format(now(), '%m'), decimal(2, 0)) = convert(date_format(a.sage, '%m'), decimal(2, 0)) and
convert(date_format(now(), '%d'), decimal(2, 0)) < convert(date_format(a.sage, '%d'), decimal(2, 0)))
then convert(date_format(now(), '%Y'), decimal(4, 0)) - convert(date_format(a.sage, '%Y'), decimal(4, 0)) -1
else convert(date_format(now(), '%Y'), decimal(4, 0)) - convert(date_format(a.sage, '%Y'), decimal(4, 0))
end as age_year from student a;
-- 方法2：
SELECT a.*, DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(sage)), '%Y')+0 AS y_age from student a ;
-- 方法3：
select a.sid, a.sname, a.ssex, a.sage, timestampdiff(year, a.sage, now()) y_age from student a;
+------+--------+------+---------------------+-------+
| sid | sname | ssex | sage | y_age |
+------+--------+------+---------------------+-------+
| 01 | 赵雷 | 男 | 1990-01-01 00:00:00 | 35 |
| 02 | 钱电 | 男 | 1990-12-21 00:00:00 | 34 |
| 03 | 孙风 | 男 | 1990-05-20 00:00:00 | 34 |
| 04 | 李云 | 男 | 1990-08-06 00:00:00 | 34 |
| 05 | 周梅 | 女 | 1991-12-01 00:00:00 | 33 |
| 06 | 吴兰 | 女 | 1992-03-01 00:00:00 | 32 |
| 07 | 郑竹 | 女 | 1989-07-01 00:00:00 | 35 |
| 08 | 王菊 | 女 | 1990-01-20 00:00:00 | 34 |
+------+--------+------+---------------------+-------+
47、查询本周过生日的学生
select a.* from student a where date_format(a.sage, '%v') = date_format(now(), '%v');48、查询下周过生日的学生
select a.* from student a where date_format(a.sage, '%v') = date_format(now(), '%v') + 1;
+------+--------+---------------------+------+
| sid | sname | sage | ssex |
+------+--------+---------------------+------+
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 |
+------+--------+---------------------+------+
49、查询本月过生日的学生
select a.* from student a where date_format(a.sage, '%m') = date_format(now(), '%m');
+------+--------+---------------------+------+
| sid | sname | sage | ssex |
+------+--------+---------------------+------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 |
+------+--------+---------------------+------+
50、查询下月过生日的学生
select a.* from student a where date_format(a.sage, '%m') = date_format(now(), '%m') + 1;
一站式 AI 云服务平台
更多推荐
14
0- 0
已为社区贡献1条内容
所有评论(0)