大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说SQL语句实战学习 – 小小程序员「建议收藏」,希望您对编程的造诣更进一步.
参考：https://zhuanlan.zhihu.com/p/38354000
再次感谢作者的整理！！
1.数据已提前准备好了，已知有如下4张表：
学生表：student
成绩表：score（学号，课程号，成绩）
课程表：course
教师表：teacher
2.1 简单查询
2.1.1 查询姓”曹”的学生名单
SELECT * FROM student WHERE `name` LIKE "曹%"
2.1.2 查询姓名中最后一个是”玉”的学生名单
SELECT * FROM student WHERE `name` LIKE "%玉"
2.1.3 查询姓名中带”星”的学生名单
SELECT * FROM student WHERE `name` LIKE "%星%"
2.2 汇总查询
2.2.1 查询课程编号为”0002″的总成绩
SELECT SUM(grade) as "总成绩" FROM score WHERE course_id="0002"
2.2.2 查询选了课程的学生人数
SELECT COUNT(DISTINCT stu_id) as "选了课程的学生人数" FROM score # DISTINCT 去重
2.3 分组查询
2.3.1 查询各科成绩最高和最低得分
SELECT MAX(grade) as "最高分",MIN(grade) as "最低分" FROM score ORDER BY grade
2.3.2 查询每门课程被选修的学生数
SELECT course_id as "课程号" ,COUNT(stu_id) as "学生人数" FROM score GROUP BY course_id
2.3.3 查询学生中男、女人数
SELECT sex,COUNT(sex) as "人数" FROM student GROUP BY sex
2.4 带条件的分组查询
2.4.1 查询平均成绩大于60分学生的学号和平均成绩
SELECT stu_id as "学号", AVG(grade) as "平均成绩" FROM score GROUP BY stu_id HAVING AVG(grade) > 60
2.4.2 查询至少选修两门课程的学生学号以及课程数目
SELECT stu_id as "学生学号", COUNT(course_id) as "课程数目" FROM score GROUP BY stu_id HAVING COUNT(course_id) > 2
2.4.3 查询同名同姓学生名单并统计同名人数
SELECT name as "学生名单" ,COUNT( name) as "同名人数" FROM student GROUP BY `name`
2.4.4 查询不及格的课程并按照课程号从大到小排序
SELECT course_id,grade from score WHERE grade < 85 ORDER BY grade DESC
2.4.5 查询每门课程的平均成绩，结果按照平均成绩升序排序，平均成绩相同时，按照课程好降序排序
SELECT course_id as "课程号",AVG(grade) as "平均成绩" from score GROUP BY course_id ORDER BY AVG(grade) and course_id
2.4.6 查询其中课程编号为”0003″且分数小于90的学生学号，结果按照分数降序排列
SELECT course_id , stu_id, grade from score WHERE course_id="0003" AND grade < 90 ORDER BY grade DESC
2.4.7 查询课程号和选修此课程人数，查询结果按照人数排序降序，若人数相同，按照课程号升序排序
SELECT course_id as "课程号", COUNT(course_id) as "课程人数" from score GROUP BY course_id ORDER BY COUNT(course_id) DESC,course_id ASC
2.4.8 查询两门以上成绩不满85分的同学的学号及其平均成绩
SELECT stu_id as "学号", AVG(grade) as "平均成绩" from score WHERE grade <= 85 GROUP BY stu_id HAVING COUNT(course_id) >= 2
2.4.9 查询各科成绩前两名的记录
(SELECT * from score WHERE course_id = "0001" ORDER BY grade DESC LIMIT 2) union (SELECT * from score WHERE course_id = "0002" ORDER BY grade DESC LIMIT 2) union (SELECT * from score WHERE course_id = "0003" ORDER BY grade DESC LIMIT 2);
2.5 汇总查询
2.5.1 查询学生的成绩并进行排名
SELECT stu_id , SUM(grade) FROM score GROUP BY stu_id ORDER BY SUM(grade)
2.5.2 查询平均成绩大于80分的学生的学号和平均成绩
SELECT stu_id ,AVG(grade) FROM score GROUP BY stu_id HAVING AVG(grade) > 80
2.5.3 查询所有课程成绩小于85分的学生的学号、姓名
SELECT id , name FROM student WHERE id in (SELECT stu_id FROM score GROUP BY stu_id HAVING AVG(grade) < 85);
2.5.3 查询没有学全所有课程的学生的学号、姓名
SELECT id , name FROM student WHERE id in ( SELECT stu_id FROM score GROUP BY stu_id HAVING COUNT(course_id) < 3);
2.5.4 查询1996年出生的学生名单
SELECT * FROM student WHERE year(brith) = 1996
2.5.5 查询各学生的年龄
SELECT id,`name`,TIMESTAMPDIFF(year,brith,now()) from student;
2.6 多表查询
2.6.1 查询所有学生的学号、姓名、选课数、总成绩
SELECT s1.id , s1.`name`, COUNT(s2.course_id) as "count", SUM(grade) FROM student as s1 JOIN score as s2 WHERE s1.id = s2.stu_id GROUP BY s2.stu_id
2.6.2 查询平均成绩大于85分的所有学生的学号、姓名、平均成绩
SELECT s1.id , s1.`name`, AVG(grade) FROM student as s1 JOIN score as s2 WHERE s1.id = s2.stu_id GROUP BY s2.stu_id HAVING AVG(grade) > 85
2.6.3 查询学生的选课情况：学号、姓名、课程号、课程名称
SELECT s1.id as "学号", s1.`name` as "姓名", s2.course_id as "课程号" ,c1.`name` as "课程名称" FROM student as s1 JOIN score as s2 JOIN course as c1 WHERE s1.id = s2.stu_id and s2.course_id = c1.id;
2.6.4 查询出每门课程的大于80得人数和不大于80的人数
SELECT course_id, SUM(CASE WHEN grade > 80 THEN 1 ELSE 0 END) as "大于80", SUM(CASE WHEN grade <= 80 THEN 1 ELSE 0 END) as "小于80" FROM score GROUP BY course_id
2.6.5 使用分段[90,100],[80-90),[70,80),[60,70)区间统计各科成绩，统计各分段人数和，课程号，课程名称
SELECT s.course_id as "课程号",c1.`name` as "课程号", sum(case when s.grade >= 60 and s.grade < 70 then 1 else 0 end) as "[60,70)", sum(case when s.grade >= 70 and s.grade < 80 then 1 else 0 end) as "[70,80)", sum(case when s.grade >= 80 and s.grade < 90 then 1 else 0 end) as "[80,90)", sum(case when s.grade >= 90 and s.grade < 100 then 1 else 0 end) as "[90,100)" from score as s join course as c1 where s.course_id = c1.id group by s.course_id;
2.6.6. 查询课程编号为”0003″且课程成绩在90分以上的学生的学号和姓名
SELECT s2.id as "学号",s2.`name` as "姓名" from score as s1 join student as s2 where s1.stu_id = s2.id AND s1.course_id = "0003" AND s1.grade > 90;
2.6.7 数据的行列如何互换？
# 使用max()聚合函数将三个结果中的最大的提取出
select stu_id, MAX(case course_id when "0001" then grade else 0 end ) as "课程号0001", max((case course_id when "0002" then grade else 0 end)) as "课程号0002", max((case course_id when "0003" then grade else 0 end)) as "课程号0003" FROM score group by stu_id;
2.7 多表连接查询
2.7.1 查询课程号为”0001″的课程分数小于90的学生信息，按照分数降序排列
select s2.id,s2.name,s1.course_id,s1.grade FROM score as s1 JOIN student as s2 WHERE s1.stu_id = s2.id AND s1.course_id = "0001" HAVING grade < 90 ORDER BY grade desc
2.7.2 查询不同老师所教的不同课程的平均分从高到低显示
select c1.teacher_id, s1.course_id, c1.`name`, avg(grade)
FROM score as s1 JOIN course as c1
WHERE s1.course_id = c1.id GROUP BY c1.teacher_id ORDER BY AVG( s1.grade) DESC
2.7.3 查询课程名称为”数学”，且分数低于90的学生姓名和分数
select s2.`name` as "学生姓名", s1.grade as "分数" FROM score as s1 JOIN course as c1 JOIN student as s2 WHERE s1.course_id = c1.id AND s1.stu_id = s2.id AND c1.`name` = "数学" and s1.grade < 90
2.7.4 查询两门及其以上课程小于85的同学的学号，姓名及其平均成绩
select s1.id as "学号",s1.name as "姓名",avg(s2.grade) as "平均成绩" from student as s1 join score as s2 on s1.id = s2.stu_id and s2.grade < 85 group by s2.stu_id having count(s1.id) >= 2;
2.7.5 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT s1.stu_id as "学生编号", s1.course_id as "课程编号", s1.grade as "学生成绩" FROM score AS s1 JOIN score AS s2 ON s1.stu_id = s2.stu_id WHERE s1.course_id != s2.course_id AND s1.grade = s2.grade
2.7.6 查询课程编号为“0002”的课程比“0001”的课程成绩高的所有学生的学号
SELECT DISTINCT a.stu_id,a.grade as "0002成绩",b.grade as "0001成绩" FROM (SELECT stu_id,grade FROM score WHERE course_id = "0002" ) as a join (SELECT stu_id,grade FROM score WHERE course_id = "0001" ) as b ON a.stu_id = b.stu_id where a.grade > b.grade;
2.7.7 查询学过编号为“0001”的课程并且也学过编号为“0002”的课程的学生的学号、姓名
SELECT a.stu_id as "学号",s1.`name` as "姓名" FROM (SELECT stu_id FROM score WHERE course_id = "0002" ) as a join (SELECT stu_id FROM score WHERE course_id = "0001" ) as b ON a.stu_id = b.stu_id JOIN student as s1 on s1.id = b.stu_id
2.7.8 查询学过“陈独秀”老师所教的所有课的同学的学号、姓名
SELECT DISTINCT s1.stu_id as "学号",s2.`name` as "姓名" FROM (SELECT course_id,t1.`name` FROM teacher as t1 JOIN score as s1 on t1.id = s1.course_id WHERE t1.`name` = "陈独秀") as a join score as s1 JOIN student as s2 WHERE a.course_id = s1.course_id AND s1.stu_id = s2.id
2.7.9 查询至少有一门课与学号为“0001”的学生所学课程相同的学生的学号和姓名`
SELECT
s1.id,s1.`name`
FROM student as s1
WHERE s1.id in
(SELECT DISTINCT(stu_id) from score WHERE course_id in (SELECT course_id FROM score WHERE stu_id = “0001”))
AND s1.id != “0001”;
2.7.10 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT s1.stu_id as "学生号", MAX(CASE WHEN c1.`name` = "语文" THEN s1.grade ELSE 0 END) as "语文", MAX(CASE WHEN c1.`name` = "数学" THEN s1.grade ELSE 0 END) as "数学", MAX(CASE WHEN c1.`name` = "英语" THEN s1.grade ELSE 0 END) as "英语", avg(s1.grade) as "平均成绩" FROM course as c1 JOIN score as s1 ON c1.id = s1.course_id GROUP BY s1.stu_id
原文地址：https://www.cnblogs.com/lxxduang/archive/2022/07/23/16512481.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/4982.html
