Sql-50

jiaojiao_bigdata
224 阅读2分钟 
https://blog.csdn.net/Robin_Pi/article/details/123332978?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522167551191816800225562386%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=167551191816800225562386&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~top_positive~default-1-123332978-null-null.142^v73^control_1,201^v4^add_ask,239^v1^insert_chatgpt&utm_term=sql%E7%BB%8F%E5%85%B850%E9%A2%98&spm=1018.2226.3001.4187

总结

求平均/聚合，记得分组
使用if(条件，0，列名)把null值替换为0
IF(sc.s_score IS NULL, 0, sc.s_score) 
select distinct + not in : 否则会出现多个id
round():四舍五入
floor():向下取整  floor(3.4) = 3 floor(5.7) = 5
datediff(current_date,s_birth):计算相差天数
内连接 inner join = join  左连接  右连接  全连接（笛卡尔集） 所有的最多都是笛卡尔集
内连接最少的就是没有匹配，左联与右连最少的是表的列数

笛卡尔积效果

image.png

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

  • 内连接：相当于自己与自己笛卡尔集
select * from score s1 inner join score s2 on s1.s_id = s2.s_id 
and s1.c_id = "01" and s2.c_id = "02"
and s1.s_score > s2.s_score

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

  • 求平均分：先进行分组，再使用聚合函数
select s_id, AVG(s_score) avg_score 
from Score
group by s_id
having avg_score > 60;

2.1、所有成绩小于60分的学生信息

  • 在sql中，where是存在，对于所有，需要使用not in
  • in,not in的子表中，不用加t1,t2,t3....但需要加( )
select * from student where s_id in (
select s_id from score
where s_id not in(
select s_id from score where s_score > 60) );

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

  • 选错左右表，导致成绩那一列不能完全显示
  • 使用from的时候必须加t1,t2,t3 + ( )
select a.s_id,avg(score) a_vg
from (
select student.s_id,
if(score.s_score is null,0,s_score) score
from student left join score on student.s_id = score.s_id) a
group by a.s_id
having a_vg < 60;

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

  • 注意这里使用count(列名)
select a.s_id,a.s_name,if(count(c_id) is null,0,count(c_id)),sum(score) 
from 
(select student.s_id,s_name, c_id,if(s_score is null,0,s_score) score
from student left join score on student.s_id = score.s_id) a
group by a.s_id;

5、查询没学过“张三”老师课的学生的学号、姓名

select stu.s_id,stu.s_name from student stu
where stu.s_id not in (
select s_id from score join course on score.c_id = course.c_id join teacher on course.t_id = teacher.t_id
and teacher.t_name = "张三");

6、最难：查询学过“张三”老师所教的所有课的同学的学号、姓名

  • 先查询选过张三老师的课的记录
  • 学张三老师可的count = 张三老师教课的count
select a.s_id,count(a.c_id) cnt from (
select * from score where c_id in(
select c_id from course
join teacher on course.t_id = teacher.t_id and t_name = "张三")) a
group by a.s_id
having cnt = (select count(c_id) from course
join teacher on course.t_id = teacher.t_id and t_name = "张三");

最最难：7.1查询学过编号为“01“但是没有学过编号为“02“的课程的同学的信息

  • 使用s_id排除，而不是使用c_id排除
select * from score where c_id = "01"
and s_id not in (
select s_id from score where c_id = "02");

难但写出来了 12、查询和“01”号同学所学课程完全相同的其他同学的学号（！）

select s_id from score where s_id not in(
select s_id from score where c_id not in(
select c_id from score where s_id = "01"))
group by s_id 
having count(score.c_id) = (select count(c_id) from score where s_id = "01")

极难：不会13、查询没学过"张三"老师讲授的任一门课程的学生姓名

select s_name from student join(
#没学过
select distinct s_id from score where s_id not in(
#学过张三老师教的课的学生
select s_id from score where c_id in(
#张三老师教的课
select c_id from course join teacher
on course.t_id = teacher.t_id
and teacher.t_name = "张三")))a
on student.s_id = a.s_id;

# 直接从学生入手，与课程名无关，可以直接查询没学过张三老师课的学生信息
select s_name from student
join(
select distinct s_id from score where s_id not in(
#学过张三老师教的课
select score.s_id from course
join score on 
course.c_id = score.c_id
join teacher on course.t_id = teacher.t_id
where t_name = "张三" ))a
on student.s_id = a.s_id;

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

select a.s_id,s_name,avg_score
from (
select s_id,avg(s_score) avg_score
from score
where s_id in(
#两门以上不及格的学号
select s_id from score 
where s_score < 60
group by s_id
having count(s_score) >= 2)
group by s_id)a
join student on a.s_id = student.s_id;

16、检索"01"课程分数小于60，按分数降序排列的学生信息

# 01课程小于60的学生id
select s_id,s_score from score where s_score < 60
and c_id = "01"
order by c_id desc;

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

  • 课程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 sc.c_id

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

select score.c_id '课程ID',c_name '课程名称',
sum(if(s_score < 100 and s_score >= 85,1,0)) '[100-85]',
sum(if(s_score < 85 and s_score >= 70,1,0)) '[85-70]',
sum(if(s_score < 70 and s_score >= 60,1,0)) '[70-60]',
sum(if(s_score < 60,1,0)) '[<60]'
from  score join course
on score.c_id = course.c_id
group by score.c_id;


select course.c_id '课程ID',c_name '课程名称',
sum(case when s_score >= 85 and s_score < 100 then 1 else 0 end) '[100-85]',
sum(case when s_score >= 70 and s_score < 85 then 1 else 0 end) '[85-70]',
sum(case when s_score >= 60 and s_score < 70 then 1 else 0 end) '[70-60]',
sum(case when s_score < 60 then 1 else 0 end) '[<60]'
from course join score on course.c_id = score.c_id
group by course.c_id;

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

select t_name,c_name,a.avg_score
from course join(
select c_id,avg(s_score) avg_score
from score
group by c_id)a
on course.c_id = a.c_id
join teacher on course.t_id = teacher.t_id
order by avg_score desc;

28、查询男生、女生人数

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

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

select s1.s_id,s1.c_id,s1.s_score
from score s1 
join score s2
on s1.s_score = s2.s_score
and s1.s_id != s2.s_id
and s1.c_id != s2.c_id;

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

与排序相关的SQL

难：窗口函数 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

窗口排序函数19、按各科成绩进行排序，并显示排名

窗口排序函数20、查询学生的总成绩并进行排名

很难22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

24、查询学生平均成绩及其名次

25、查询各科成绩前三名的记录（不考虑成绩并列情况）

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

42、查询每门功成绩最好的前两名

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

select s_id,avg(s_score)
from score
group by s_id
having s_id in(
select s_id from (
select * from score
where s_score < 60)a
group by s_id
having count(s_score) >= 2)

与年份相关的SQL

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

select * from student 
where year(s_birth) = '1990'

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

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

极难 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) month
from student)m;
avatar
文章
阅读
粉丝