初学者⼊门基础经典SQL练习题（MySQL精简版）1，有把⼀些题⽬放到⼀起，为了更好地对⽐分析；删除了⼀些简单的题⽬
2，练习过程中，为了更好地查看数据不同时返回的查询效果，所以中间会对数据做⼀些修改符合，请注意
建表的过程
create table student(
sid int not null primary key,
sname varchar(20) not null,
sborn date,
ssex varchar(20) not null);
create table course(
cid int not null primary key,
cname varchar(20) not null,
tid int not null);
create table teacher(
tid int not null primary key,
tname varchar(20));
create table sc(
sid int not null,
cid int not null,
score int not null,
primary key( sid, cid)  );
插⼊数据
insert into Student values(1 , '赵雷' , '1990-01-01' , '男');
insert into Student values(2 , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '⼥');
insert into Student values('06' , '吴兰' , '1992-01-01' , '⼥');
insert into Student values('07' , '郑⽵' , '1989-01-01' , '⼥');
insert into Student values('08' , '张三' , '2017-12-20' , '⼥');
insert into Student values('9' , '李四' , '2017-12-25' , '⼥');
insert into Student values('10' , '李四' , '2012-06-06' , '⼥');
insert into Student values('11' , '赵六' , '2013-06-13' , '⼥');
insert into Student values('12' , '孙七' , '2014-06-01' , '⼥');
insert into Course values('01' , '语⽂' , '02'),('02' , '数学' , '01'),('03' , '英语' , '03'),(4,'物理',4); insert into Teacher values('01' , '张三'),('02' , '李四'),('03' , '王五'),(4,'孙杨');
insert into SC values('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
(1,4,46),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
(2,4,76),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
(4,4,87),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
(6,4,93),
('07' , '02' , 89),
('07' , '03' , 98);
1、查询" 01 "课程⽐" 02 "课程成绩⾼的学⽣的信息及课程分数
select student.*,a.score
from  (sc a  join sc b  on  a.sid=b.sid
and a.cid=1
and b.cid=2
and a.score>b.score)
join student on a.sid=student.sid;
或
select student.* , a.score
from (sc a left join sc b on a.sid=b.sid) inner join student on a.sid=student.sid
where a.cid=1 and b.cid=2 and a.score>b.score;
2、查询同时选修" 01 "课程和" 02 "课程的学⽣情况
from sc left join student on sc.sid=student.sid
where cid=1 or cid=2  #注意这⾥只能⽤or，不能⽤and
group by sc.sid
having count(cid)=2;
或
select student.*
from (sc  a join sc b on a.sid=b.sid and a.cid=1 and b.cid=2) join student on a.sid=student.sid;
3、查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显⽰为 null )
#思路：题⽬的意思是选修1号课程的学⽣必须全部列出来，同时查看⼀下选修了1号课程的学⽣中有哪些选修了2号课程
select * from
(select *  from sc  where cid=1 ) a  left join  (select * from sc where cid=2) b on a.sid=b.sid ;
4、查询不存在" 01 "课程但存在" 02 "课程的情况
select a.sid  from
(select sid from sc where cid=2 ) a left join (select sid from sc where cid=1) b on a.sid=b.sid
where b.sid is null;
5、查询⾄少有⼀门课与学号为" 01 "的同学所学相同的同学的信息
select  distinct sid
from sc
where cid in (select cid from sc where sid=1) and sid!=1;
或
select  distinct t2.sid
from
(select * from sc where sid=1 ) t1 left join (select * from sc where sid<>1) t2
on t1.cid=t2.cid;
6、查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
PS：使⽤了group_concat( )函数，将分组后指定字段的值连接起来，感觉使⽤时最好对连接的字段排序，以免出错
from
(select sid, group_concat(cid order by cid) as  tt from sc  where sid=1  group by sid) a  #1号同学选修的课程
#为了避免cid插⼊顺序引起的group_concat()结果有差异，所以对cid进⾏了排序
left join
( select sid, group_concat(cid order by cid) as rr  from sc  where sid!=1  group by sid) b    =b.rr
#除1号同学外，其他同学选修的课程，将两张表进⾏连接，按照选修课程相同为条件进⾏连接
join student on b.sid=student.sid;
7、检索" 01 "课程分数⼩于 60的学⽣，按分数降序排列学⽣
select student.*
from sc join student on sc.sid=student.sid
where sc.cid=1 and sc.score<60
order by sc.score desc;
8、按平均成绩降序查询所有学⽣的课程成绩，按如下形式显⽰：学号、姓名、课程名、总成绩、课程数、平均成绩
sql容易学吗select sid, sum(score) as '总成绩', avg(score) as '平均成绩'
from sc
group by sid
order by  平均成绩  desc;
select  student.sid as '学号',student.sname as '姓名',
max(case when cname='语⽂' then  sc.score else  NULL end) as '语⽂', #已经按学号分组了，所以每组就是每个同学的所有课程成绩max(case when cname='数学' then  sc.score else NULL end ) as '数学',
max(case when cname='英语' then  sc.score else NULL end) as '英语',
max(case when cname='物理' then  sc.score else NULL end) as '物理',
max(case when cname='⾳乐' then  sc.score else NULL end) as '⾳乐',
sum(score) as '总成绩',
count(sc.cid) as '选课数',
round(avg(score),2) as '平均成绩'
from  (student left join sc on student.sid=sc.sid) left join course on sc.cid=course.cid
group by sc.sid ,student.sname
order by avg(score) desc;
9、查询「李」姓⽼师的数量
select count(tid) as '姓李的⽼师个数'
from teacher
where tname like '李%' ;
PS：这⾥的%可以指代多个字符
10、查询名字中含有「风」字的学⽣信息
select *
from student
where sname like '%风%';
或使⽤正则表达式 REGEXP
select *
from student
where sname regexp  '风';
运⾏结果：
11、查询学过「张三」⽼师授课的同学的信息
select student.*
from (( sc left join course on sc.cid=course.cid )
left join teacher on course.tid=teacher.tid) left join student on sc.sid=student.sid
where tname="张三"
group by sc.sid;
12、查询没学过"张三"⽼师讲授的任⼀门课程的学⽣姓名
#差集, SC表⾥的所有学⽣-选修了张三⽼师课程的学⽣，即为没有选修过张三⽼师任⼀课程的学⽣
select student.*
from
(select distinct sid from sc ) a  left join  #SC表全部的学号，去重
(select  sc.sid from ((sc left join course on sc.cid=course.cid) left join  teacher on course.tid=teacher.tid) ame='张三')  b
on a.sid=b.sid
left join student on a.sid=student.sid
where b.sid is null;
或
select distinct sid
from sc
where sc.sid not in
(select  sid
from ( sc left join course on sc.cid=course.cid ) left join teacher on course.tid=teacher.tid
where tname="张三") ;
13、查询选修「张三」⽼师所授课程的学⽣中，成绩最⾼的学⽣信息及其成绩

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。