oracle初学50题_MySQL经典50题（初学者）

踏上SQL的旅程，一直用的MySQL，试试经典50题练练手，知乎记录，方便自己以后的查看

编译器:MySql Workbench

建表与插入数据

1. 建立Student表

create table Student( 
	Sid varchar(10)  not null default '-',
    Sname varchar(10) not null default '-',
    Sage datetime  not null default '0000-00-00',
    Ssex varchar(10) not null default '-'
);

插入数据

insert into  Student(Sid,Sname,Sage,Ssex)
values('01' , '赵雷' , '1990-01-01','男'),
('02' , '钱电' , '1990-12-21' , '男'),
('03' , '孙风' , '1990-05-20' , '男'),
('04' , '李云' , '1990-08-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吴兰' , '1992-03-01' , '女'),
('07' , '郑竹' , '1989-07-01' , '女'),
('08' , '王菊' , '1990-01-20' , '女');

2.创建科目表course

create table Course(
	Cid varchar(10) not null default'-',
    Cname varchar(10) not null default'-',
    Tid varchar(10) not null default'-'
);

插入数据

insert into Course(Cid,Cname,Tid)
values('11' , '语文' , '22'),
('12' , '数学' , '21'),
('13' , '英语' , '23');

3.创建教师表Teacher

create table Teacher(
	Tid varchar(10) not null default'-',
    Tname varchar(10) not null default'-'
);

插入数据

insert into Teacher(Tid,Tname)
values('21' , '张三'),
('22' , '李四'),
('23' , '王五');

4.创建学生成绩表

create table SC(
	Sid varchar(10) not null default '-',
    Cid varchar(10) not null default '-',
    Score decimal(18,1)#最高可长达18位，小数点后一位
);

插入数据

insert into SC(Sid,Cid,Score)
 values('01' , '11' , 80),
('01' , '12' , 90),
('01' , '13' , 99),
('02' , '11' , 70),
('02' , '12' , 60),
('02' , '13' , 80),
('03' , '11' , 80),
('03' , '12' , 80),
('03' , '13' , 80),
('04' , '11' , 50),
('04' , '12' , 30),
('04' , '13' , 20),
('05' , '11' , 76),
('05' , '12' , 87),
('06' , '11' , 31),
('06' , '13' , 34),
('07' , '12' , 89),
('07' , '13' , 98);

查询练习

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

select student.*, t.score1, t.score2 from student, (
select sc1.sid, sc1.cid, sc1.score as score1, sc2.score as score2 from 
(select sid, cid, score from sc where cid = '11') as sc1
inner join
(select sid, cid, score from sc where cid = '12') as sc2
on sc1.sid = sc2.sid where sc1.score > sc2.score) as t where student.sid = t.sid;

查询结果

1.1查询同时存在" 11 "课程和" 12 "课程的情况

内连接（inner join）

select *from (select *from sc where cid = '11')sc1
inner join (select *from sc where cid ='12')sc2 on sc1.sid=sc2.sid;

查询结果

1.2 查询存在" 11 "课程但可能不存在" 12 "课程的情况(不存在时显示为 null )

左连接（left join）

select * from (select *from sc where cid ='11') sc1
left join (select * from sc where cid='12')sc2 on sc1.sid=sc2.sid;

查询结果

1.3 查询不存在" 11 "课程但存在" 12 "课程的情况

右连接（right join）

select * from(select *from sc where cid ='11')sc1
right join (select *from sc where cid='12')sc2
on sc1.sid=sc2.sid;

查询结果

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

select student.sid,sname,sage,ssex,sscore 
from student , 
(select sid, avg(score) as sscore 
 from sc 
 group by sid 
 having avg(score) > 60) as r 
where student.sid = r.sid;

查询结果

3. 查询在 SC 表存在成绩的学生信息

distinct关键字段为去除重复值（student表与SC表是一对多的关系）

select distinct student.* from student left join sc
on sc.sid = student.sid where sc.score is not null;

查询结果

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

select student.sid,student.sname,courseCount,allScore from student 
inner join
(select sid,count(cid) as courseCount,sum(score)as allScore
 from sc group by sid) as t #先计算SC表中选课总数、课程总成绩
 on student.sid = t.sid;

查询结果

5. 查询「李」姓老师的数量

select count(Tname) from Teacher
where tname like '李%';

查询结果

6. 查询 张三 老师授课的同学信息

三个表通过内连接组成一个表，然后是根据查询条件查询

select s.*
from student as s inner join sc on s.sid=sc.sid
inner join course as c on sc.cid = c.cid
inner join teacher as t on t.tid = c.tid
where t.tname = '张三';

查询结果

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

select * from student where student.sid not in(
select sid from sc group by sc.sid
having count(cid) = (select count(*)from course));

查询结果

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

select student.* from student ,
(select distinct sid from sc where cid in #2课程号在01同学的课程号中的sid
(select cid  from sc where sid = '01')  #1查询01同学所选的课程cid
) as r where student.sid = r.sid;

查询结果

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

select student.* from student where sid in(
select sid from sc where cid in(
select cid from sc where sid = '01')
group by sid having count(cid) = (
select count(cid) from sc where sid = '01')
)and sid !='01';

查询结果

10.查询没学过“张三”老师讲授的任一门课程的学生的名字

select sname from student where sid not in 
(select sid from sc left join(
select course.cid as ccid from teacher,course
where teacher.tname = '张三' and teacher.tid = course.tid)
r on sc.cid = r.ccid where ccid is not null);

查询结果

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

select student.*, avgScore from student,(
select sid,avg(score) as avgScore from sc where sid in(
select sid from sc where score <60 group by sid having count(cid)>=2)
group by sid) r where student.sid = r.sid;

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

select student.* ,sc.score from student,sc
where sc.sid=student.sid and cid = 11 and sc.score<60
order by sc.score desc;

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

select r.sid,sc.score,r.avgScore from sc,
(select sid, avg(score) as avgScore from sc group by sid) as r 
where sc.sid = r.sid 
order by r.avgScore desc;

14.查询各科成绩最高分、最低分和平均分

select course.cname,r.*from course,(
select cid,max(score) as maxScore,min(Score) as minScore,avg(score)as avgScore 
from sc
group by cid
order by cid asc)r
where course.cid = r.cid;

-- 15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

 select a.sid, a.cid,
	@i:=@i+1 as r,
	@k:=(case when @score=a.score then @k else @i end) as r_no_keep,
	@score:=a.score as score
from
(select sid, cid,score from sc order by score desc)a,
(select @k=0,@i:=0,@score:=0)s;

16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺

set @sr:=0;
select @sr:=(case when @t_s=a.t_s then @sr:=@sr else @sr:=@sr+1 end) 
as stu_rank, sid, t_s from (
select sid, sum(score) as t_s from sc group by sid order by t_s desc 
) a;

17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0]

select a.cid,b.cname,
sum(case when score between 85 and 100 then 1 else 0 end) as'[100-85]',
sum(case when score >=70 and score<85 then 1 else 0 end) as'[85-70]',
sum(case when score >=60 and score<70 then 1 else 0 end) as'[70-60]',
sum(case when score <60  then 1 else 0 end) as'[60-0]'
from sc as a right join course as b
on a.cid = b.cid
group by a.cid,b.cname;

-- 18.查询各科成绩前三名的记录

select * from sc where (
select count(*) from sc as a 
where sc.cid = a.cid and sc.score<a.score
)<3 order by cid asc, sc.score desc;

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

select cid,count(sid) from sc 
group by cid;

20.查询出只选修两门课程的学生学号和姓名

select a.sid ,student.sname from 
(select sid from sc 
group by sid having count(cid)=2) a,student where  a.sid=student.sid;

21. 查询男生、女生人数

select ssex,count(ssex) from student
group by ssex;

22. 查询名字中含有 '风' 字的学生信息

select * from student
where sname like "%风%";

23. 查询同名同姓学生名单，并统计人数

select *from student s1 inner join student s2
on s1.sname = s2.sname and s1.sid!=s2.sid;

不存在这样的情况

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

select sname from student where year(sage) like '1990%';

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

select cid,avg(score) as avgScore from sc
group by cid order by avgScore desc,cid asc;

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

select student.sname,r.* from student inner join(
select sid,avg(score) as avgScore from sc where score>=85) r
 on r.sid=student.sid;

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

select student.sname, r.score from student, (
select sc.sid, sc.cid, sc.score , course.cname from sc, course 
where sc.cid = course.cid and sc.score < 60 and course.cname = '数学'
) r where student.sid = r.sid;

28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

select student.*,sc.*from student left join sc on student.sid = sc.sid;

29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

select r.sname,c.cname,r.score  from course c,(
select s.sname as sname,cid,score from sc
left join student s on s.sid = sc.sid where score>70)
r where c.cid = r.cid; 

30. 查询不及格的课程

select c.cname, r.sn from course c ,( 
select cid, count(sid) as sn from sc where score < 60 group by cid) r
where c.cid = r.cid;

31. 查询课程编号为 11 且课程成绩在 80 分以上的学生的学号和姓名

select sc.sid,student.sname from  sc ,student  
 where  sc.sid = student.sid and sc.score>=80 and sc.cid = '11';

32. 求每门课程的学生人数

 select cid,count(sid) from sc 
 group by cid;

33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

 select sc.cid,sc.sid,sc.score from sc where cid=(
 select course.cid from teacher,course where 
 teacher.tid = course.tid and tname = '张三')
 order by sc.score desc limit 1;

34.成绩有重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩(也就是要显示所有最高分学生的信息)

select student.*, r.score from student, (
 select sc.sid, sc.score as score  from sc where score = (
    select max(score) from sc where cid = (
 select course.cid from teacher,course where 
 teacher.tid = course.tid and tname = '张三'))) r 
where student.sid = r.sid;

35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select distinct  a.* from sc a 
inner join sc b on a.score = b.score and a.cid != b.cid;

36.查询各科成绩前二名的记录

select a.sid,a.cid,a.score from sc a 
where (select count(1) from sc b where b.cid = a.cid
and b.score>=a.score)<=2 
order by a.cid;

37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）

select cid,count(sid) from sc 
group by cid
having count(sid)>5;

38. 检索至少选修两门课程的学生学号

select sid,count(sid) from sc 
group by sid
having count(sid)>2;

39.查询选修了全部课程的学生信息

select student.* from student inner join(
select sid from sc group by sid having count(cid)=(
select count(*) from course ))r on student.sid = r.sid;

40.查询各学生的年龄，只按年份来算

select sname,year(now())-year(sage) as sage from student;

41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

select sname,
case when(
date_format(now(),'%m-%d')<date_format(Sage,'%m-%d')
)
then year(now())-year(sage)+1
else year(now())-year(sage) end as age
from student;

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

select sname from student where week(sage)=week(now());

43. 查询下周过生日的学生

select sname from student where week(Sage) = week(now())+1;

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

select sname from student where month(sage)=month(now());

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

select sname from student where month(sage)=month(now())+1;