建表sql

# Student 学生表
CREATE TABLE Student
(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL,
s_birth VARCHAR(20) NOT NULL, 
s_sex VARCHAR(10) NOT NULL,
PRIMARY KEY(s_id)
);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', '女');# Course 课程表
CREATE TABLE Course
(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL,
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);INSERT INTO Course VALUES('01', '语文', '02');
INSERT INTO Course VALUES('02', '数学', '01');
INSERT INTO Course VALUES('03', '英语', '03');# Teacher 教师表
CREATE TABLE Teacher
(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)
);INSERT INTO Teacher VALUES('01', '张三');
INSERT INTO Teacher VALUES('02', '李四');
INSERT INTO Teacher VALUES('03', '王五');# Score 分数表
CREATE TABLE Score
(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id, c_id)  # 注意这里是联合主键
);INSERT INTO Score VALUES('01', '01', 80);
INSERT INTO Score VALUES('01', '02', 90);
INSERT INTO Score VALUES('01', '03', 99);
INSERT INTO Score VALUES('02', '01', 70);
INSERT INTO Score VALUES('02', '02', 60);
INSERT INTO Score VALUES('02', '03', 80);
INSERT INTO Score VALUES('03', '01', 80);
INSERT INTO Score VALUES('03', '02', 80);
INSERT INTO Score VALUES('03', '03', 80);
INSERT INTO Score VALUES('04', '01', 50);
INSERT INTO Score VALUES('04', '02', 30);
INSERT INTO Score VALUES('04', '03', 20);
INSERT INTO Score VALUES('05', '01', 76);
INSERT INTO Score VALUES('05', '02', 87);
INSERT INTO Score VALUES('06', '01', 31);
INSERT INTO Score VALUES('06', '03', 34);
INSERT INTO Score VALUES('07', '02', 89);
INSERT INTO Score VALUES('07', '03', 98);# 四张表
SELECT * FROM Student;
SELECT * FROM Course;
SELECT * FROM Teacher;
SELECT * FROM Score;

1. 查询课程编号为01的课程比02的课程成绩高的所有学生的学号和成绩

select b.*,a.score1,a.score2 from (select s1.s_id,s1.s_score score1,s2.s_score score2from Score s1 inner join Score s2on s1.s_id = s2.s_idwhere s1.c_id = '01' and s2.c_id = '02' and s1.s_score > s2.s_score)a 
inner join Student b 
on a.s_id = b.s_id;

2. 查询平均成绩大于60分的学生的学号和平均成绩

#解题思路：分组查询出平均成绩，再过滤掉小于平均分小于60的学生
select s_id,avg(s_score) avg_score from Score
group by s_id
having avg_score > 60;

3. 查询所有成绩小于60分的学生信息

#解题思路：首先先分组查询出每个学生的最大成绩，然后再判断最大成绩是否大于等于60进行过滤
select st.id,s_name,s_birth,s_sex from Student st join(select s_id,max(s_score) max_score from Scoregroup by s_idhaving max_score < 60) a
on a.s_id = st.s_id;

4. 查询平均成绩小于60分的学生的学号和平均成绩，考虑没参加考试的情况

select a.s_id,avg(a.score) avg_score from(select st.s_id,if(sc.s_score is null,0,sc.s_score) score from Student st left join Score scon st.s_id = sc.s_id) a
group by a.s_id
having avg_score < 60;

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

select st.s_id,st.s_name,if(a.num is null,0,a.num),if(a.all_score is null,0,a.all_score)
from Student st left join(select sc.s_id,count(1) num,sum(sc.s_score) all_scorefrom Score sc group by sc.s_id) a
on st.s_id = a.s_id;

6. 查询姓"猴"的老师的个数

select count(1) from Teacher where t_name like '%猴%';

7.查询没学过"张三"老师课的学生的学号、姓名

select st.s_id,st.s_name 
from Student st
where s_id not in(select sc.s_id from Score scjoin Course con sc.c_id = c.c_idjoin Teacher t on c.t_id = t.t_idwhere t.t_name = '张三');

8. 查询学过"张三"老师所教的所有课的同学的学号、姓名

select st.s_id,st.s_name
from Student st
where s_id in (select sc.s_id from Score scjoin Course c on sc.c_id = c.c_idjoin Teacher ton c.t_id = t.t_idwhere t.t_name = '张三');

9. 查询学过编号为'01'的课程并且也学过编号为'02'的课程的学生的学号、姓名

select st.s_id,st.s_name
from Student st
left join(select distinct sc1.s_id from Score sc1 join Score sc2on sc1.s_id = sc2.s_idwhere sc1.c_id ='01' and sc2.c_id = '02') a
on st.s_id = a.s_id;

10. 查询学过编号为'01'的课程但没有学过编号为'02'的课程的学生的学号、姓名

select st.s_id,st.s_name
from Student stleft join(select distinct sc1.s_id from Score sc1join Score sc2on sc1.s_id = sc2.s_idwhere sc1.c_id = '01'and sc2.c_id != '02') a
on st.s_id = a.s_id;select st.s_id,st.s_name 
from Student st
join (select s_id,group_concat(c_id) cfrom Score scgroup by s_id) a
on st.s_id = a.s_id
where c like '%01%' and c not like '%02%';

11.查询课程编号为'02'的总成绩

select sum(s_score)
from Score
group by c_id
having c_id = '02';

12. 查询所有课程成绩小于60分的学生的学号、姓名

select st.s_id,st.s_name 
from Student st
join (select s_id,min(s_score) min_scorefrom Score group by s_idhaving min_score < 60) a
on st.s_id = a.s_id;

13. 查询没有学全所有课的学生的学号、姓名

#解题思路：先查询出课程表的所有课程，再分组查询出每个学生的课程数量，再判断课程数量是否和总课程数相等
select distinct st.s_id,st.s_name
from Student st
join 
(select s_id,count(c_id) count_cid
from Score
group by s_id) a
on st.s_id = a.s_id
where a.count_cid =
(select count(c_id)
from Course c);

14. 查询至少有一门课与学号为'01'的学生所学课程相同的学生的学号、姓名

#解题思路：先查询出学号为'01'的学生的课程号，再连表查出课程号包含在集合里的学生
select distinct st.s_id,st.s_name from Student stjoin Score sc1on st.s_id = sc1.s_idjoin Course con sc1.c_id = c.c_idwhere sc1.c_id in(select sc.c_id from Score sc where sc.s_id = '01')and sc1.s_id != '01';

15. 查询和'01'号同学所学课程完全相同的其他同学的学号

select st.s_id 
from Student stselect c_id 
from Course c 

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

select s_name from Student 
where s_id not in(select sc.s_idfrom Student stjoin Score scon st.s_id = sc.s_idjoin Course c1 on c1.c_id = sc.c_idwhere c1.c_name = (select c.c_name from Course cjoin Teacher ton c.t_id = t.t_idand t.t_name = '张三'));

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

select d.s_id,d.s_name,d.avg_score from (select st.s_id,st.s_name,avg(sc.s_score) avg_scorefrom Student stjoin Score scon st.s_id = sc.s_idgroup by st.s_id) d
right join (select  a.s_id,count(1) numfrom (select s_id,s_scorefrom Scorehaving s_score < 60) agroup by a.s_idhaving num >= 2) b
on d.s_id = b.s_id;

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

select * from Student st
join Score sc 
on st.s_id = sc.s_id
where sc.c_id = '01'
and sc.s_score < 60
order by sc.s_score desc;

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

#解题思路：先查询出所有学生的平均成绩，再左连接成绩表，最后再根据平均成绩降序排列
select sc1.s_id,a.s_name,sc1.s_score,a.avg_score from Score sc1
left join (select st.s_id,st.s_name,avg(s_score) avg_scorefrom Score scjoin Student ston sc.s_id = st.s_idgroup by st.s_id) a
on sc1.s_id = a.s_id
order by a.avg_score desc;

20. 查询各科成绩最高分、最低分和平均分：以如下形式显示：

– 课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
– （及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90） （！！）

select sc.c_id "课程ID",c.c_name "课程名称",max(sc.s_score) "最高分",min(sc.s_score) "最低分",avg(sc.s_score) "平均分",sum(if(sc.s_score between 60 and 70,1,0))/count(*) "及格率",sum(if(sc.s_score between 70 and 80,1,0))/count(*) "中等率",sum(if(sc.s_score between 80 and 90,1,0))/count(*) "优良率",sum(if(sc.s_score > 90,1,0))/count(*) "优秀率"
from Score sc
join Course c
on sc.c_id = c.c_id
group by c.c_id;

21. 按各科成绩进行排序，并显示排名

#解题要点：需要用到窗口函数，rank() over(partition by 字段名 order by 字段名)
#分区排名
select s_id,c_id,s_score,rank(partition by c_id,order by s_score desc) "排名"
from Score
order by s_score desc;#全部排名
select s_id,c_id,s_score,row_number() over (partition by c_id order by s_score desc) rk_score
from Score
order by rk_score;

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

select a.*,rank() over (order by sum_score desc) "排名"
from (select s_id,sum(s_score) sum_scorefrom Score group by s_id) a

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

select a.t_id,a.t_name,avg(a.s_score) avg_score
from (select t.t_id,t.t_name,c.c_id,c.c_name,sc.s_scorefrom Teacher tjoin Course con t.t_id = c.t_idjoin Score scon c.c_id = sc.c_id) a
group by a.t_id
order by avg_score desc;

24. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

select a.s_id,a.s_name,a.s_score,a.c_id,a.rk
from (select st.s_id,st.s_name,sc.s_score,sc.c_id,rank() over (partition by sc.c_id order by sc.s_score) rkfrom Score scjoin Student ston st.s_id = sc.s_id) a
where a.rk = 2 or a.rk = 3;

25. 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

select a.c_id,a.c_name,sum(if(a.s_score between 85 and 100,1,0)), sum(if(a.s_score between 75 and 85,1,0)), sum(if(a.s_score between 60 and 75,1,0)),sum(if(a.s_score < 60,1,0)) 
from (select c.c_id,c.c_name,sc.s_scorefrom Score scjoin Course con sc.c_id = c.c_id) a
group by a.c_id;

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

select a.s_id,a.s_name,a.avg_score,rank() over (order by a.avg_score desc)
from (select st.s_id,st.s_name,avg(s_score) avg_scorefrom Score scjoin Student ston sc.s_id = st.s_idgroup by st.s_id) a

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

select st.* 
from Student st
join Score sc 
on st.s_id = sc.s_id
join Course c
on sc.c_id = c.c_id

28. 查询每门课程被选修的记录数

select c.c_id,c.c_name,count(c.c_id)
from Score sc
join Course c
on sc.c_id = c.c_id
group by c.c_id;

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

select sc.s_id,st.s_name,count(*) count_two 
from Score sc
right join Student st
on sc.s_id = st.s_id
group by st.s_id
having count_two = 2;

30. 查询男生、女生人数

select sum(if(s_sex = "男" ,1 ,0)) "男生",sum(if(s_sex = "女" ,1 ,0)) "女生"
from Student

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

select s_name
from Student
where s_birth like "%1990%";

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

select distinct st.s_id,st.s_name,a.avg_score
from Score sc
join Student st
join (select s_id,avg(s_score) avg_scorefrom Scoregroup by s_id) a
on st.s_id = a.s_id
where a.avg_score > 85;

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

select c_id,avg(s_score) avg_score
from Score 
group by c_id
order by avg_score asc,c_id desc;

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

select st.s_name,sc.s_score
from Student st
join Score sc
on st.s_id = sc.s_id
where sc.c_id = (select c_id from Course where c_name = "数学")
and s_score < 60;

35. 查询所有学生的课程及分数情况(存在学生没成绩，没选课的情况)

select  st.s_id, st.s_name, sc.c_id, c.c_name, sc.s_score
from Student st
join Score sc
on st.s_id = sc.s_id
join Course c
on c.c_id = sc.c_id;

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

select st.s_name,c.c_name,sc.s_score
from Student st
join Score sc
on st.s_id = sc.s_id
join Course c
on sc.c_id  = c.c_id
where sc.s_score > 70;

37. 查询学生不及格的课程并按课程号从大到小排列

select st.s_name,c.c_id,c.c_name,sc.s_score
from Student st
join Score sc
on st.s_id = sc.s_id
join Course c
on sc.c_id = c.c_id
where sc.s_score < 60
order by c.c_id;

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

select st.s_id,st.s_name
from Student st
join Score sc
on st.s_id = sc.s_id
where sc.c_id = 03 and sc.s_score >80;

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

select c.c_id,c.c_name,count(sc.s_id)
from Course c
join Score sc
on c.c_id = sc.c_id
group by c.c_id;

40.查询选修"张三"老师所授课程的学生中成绩最高的学生姓名及其成绩

select a.s_name,a.s_score
from (select st.s_name,sc.c_id,sc.s_score,rank() over (order by sc.s_score desc) rank_scorefrom Score scjoin Student ston st.s_id = sc.s_idwhere sc.c_id = (select c.c_id from Course cjoin Teacher ton c.t_id = t.t_id where t.t_name = '张三')) a
where a.rank_score = 1;

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

select sc1.s_id,sc1.c_id,sc1.s_score
from Score sc1
join Score sc2
on sc1.s_score = sc2.s_score
where sc1.s_score = sc2.s_score
and sc1.s_id != sc2.s_id
and sc1.c_id != sc2.c_id;

42. 查询每门课程成绩最好的前两名学生信息

select * from 
(select a.s_name,a.c_id,a.s_score,rank() over(partition by c_id order by s_score) rank_score
from 
(select st.s_name,sc.c_id,sc.s_scorefrom Student stjoin Score scon sc.s_id = st.s_id
) a) b
where b.rank_score <= 2;

43. 统计每门课程的学生选修人数(超过5人的课程才统计)

select c.c_id,c.c_name,count(s_id) cnt_sid
from Score sc
join Course c
on sc.c_id = c.c_id
group by c.c_id
having cnt_sid > 5
order by cnt_sid desc,c_id;

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

select st.s_id,st.s_name,count(sc.c_id) cnt_cid
from Student st
join Score sc
on st.s_id = sc.s_id
group by sc.s_id
having cnt_cid >= 2;

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

select distinct a.s_id,a.s_name,a.s_birth,a.s_sex,a.cnt_course
from (select st.s_id,st.s_name,st.s_birth,st.s_sex,count(1) over(partition by s_id) cnt_coursefrom Score scjoin Student ston sc.s_id = st.s_id) a
where a.cnt_course = 
(select count(c_id)
from Course);

46. 查询各学生的年龄(精确到月份)

SELECT IF(m.month != 0, CONCAT(m.year, "岁", m.month, "个月"), CONCAT(m.year, "岁"))  "年龄"
FROM (SELECT FLOOR(DATEDIFF(CURRENT_DATE, s_birth) / 365) year, ROUND((DATEDIFF(CURRENT_DATE, s_birth) % 365) / 30) monthFROM Student) m

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

select st1.s_name
from (select distinct st.s_namefrom Student stjoin Score scon st.s_id = sc.s_idwhere sc.c_id = (select c.c_id from Course cjoin Teacher ton c.t_id = t.t_idwhere t.t_name = "张三")) a
right join Student st1 
on a.s_name = st1.s_name
where a.s_name is null;

48. 查询两门以上不及格课程的同学

select b.s_name,b.count_score,c.avg_score
from (select distinct a.s_name,count(a.s_score) count_scorefrom (select st.s_name,sc.s_scorefrom Student stjoin Score scon st.s_id = sc.s_idwhere sc.s_score < 60) agroup by a.s_name) b
join (select st1.s_name,avg(sc1.s_score) avg_scorefrom Student st1join Score sc1on st1.s_id = sc1.s_idgroup by st1.s_id) c
on b.s_name = c.s_name;

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

select s_name
from Student 
where month(s_birth) = month(current_date);

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

select s_name
from Student
where week(s_birth) = week(current_date);