文章目录
一、创建表格二、使用步骤1.引入库2.读入数据3-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数3-- 2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩3-- 3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩3-- 4、查询学过"张三"老师授课的同学的信息3-- 5、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息3 -- 6、查询没有学全所有课程的同学的信息3-- 7、查询没学过"张三"老师讲授的任一门课程的学生姓名3 -- 8、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩3-- 9.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率3--10、按各科成绩进行排序
一、创建表格
代码如下（示例）:
二、使用步骤
1.引入库
代码如下（示例）：
2.读入数据
代码如下（示例）：
3-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
方式1:这样慢而且效率低
SELECT
st.*,
su1.s_score AS '语文',
su2.s_score AS '数学'
FROM student st ,
(SELECT * FROM score WHERE c_id = '01')AS su1 ,
(SELECT * FROM score WHERE c_id = '02')AS su2
WHERE su1.s_id=st.s_id AND su2.s_id=st.s_id AND
su1.s_score > su2.s_score;
方式2:这样快而且效率高
SELECT
st.*,
sc.s_score AS '语文',
sc2.s_score AS '数学'
FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id AND sc.c_id='01'
LEFT JOIN score sc2 ON sc2.s_id=st.s_id AND sc2.c_id='02'
WHERE sc.s_score > sc2.s_score;
3-- 2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT b.s_id, b.s_name,a FROM student AS b JOIN
(SELECT s_id,AVG(s_score) AS a FROM score GROUP BY s_id HAVING a>=60) AS c
ON b.s_id = c.s_id;
3-- 3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
方式1:
SELECT st.s_id,st.s_name,COUNT(se.c_id),SUM(se.s_score)
FROM student st LEFT JOIN score se ON st.s_id=se.s_id
GROUP BY st.s_id;
方式2:
SELECT st.s_id,st.s_name,
COUNT(sc.c_id),
CASE WHEN SUM(sc.s_score)
IS NULL THEN 0.00 ELSE ROUND(SUM(sc.s_score),2) END AS '总成绩'
FROM student st
LEFT JOIN score sc
ON sc.s_id=st.s_id
GROUP BY st.s_id;
3-- 4、查询学过"张三"老师授课的同学的信息
方式1:
SELECT st.*
FROM student st,score sc,course c,teacher t
WHERE sc.s_id=st.s_id AND
c.c_id=sc.c_id AND
t.t_id=c.t_id AND
t.t_name='张三';
方式2:
SELECT st.*
FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
LEFT JOIN course c ON c.c_id=sc.c_id
LEFT JOIN teacher t ON t.t_id=c.t_id
WHERE t.t_name='张三';
3-- 5、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT st.*
FROM student st
INNER JOIN score sc ON sc.s_id=st.s_id AND sc.c_id='01'
WHERE st.s_id NOT IN (
SELECT st2.s_id
FROM student st2
INNER JOIN score sc2 ON sc2.s_id=st2.s_id AND sc2.c_id='02'
);
3 – 6、查询没有学全所有课程的同学的信息
SELECT st.*,COUNT(se.c_id)
FROM student st
LEFT JOIN score se ON st.s_id=se.s_id
GROUP BY st.s_id
HAVING COUNT(se.c_id) <3;
3-- 7、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT s_name FROM student
WHERE s_id NOT IN(
SELECT s_id FROM score WHERE c_id IN(
SELECT c_id FROM course,teacher
WHERE course.t_id=teacher.t_id AND t_name='张三'));
3 – 8、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT student.s_id,
SUM(CASE WHEN c_id='01' THEN s_score ELSE NULL END ) cid1,
SUM(CASE WHEN c_id='02' THEN s_score ELSE NULL END ) cid2,
SUM(CASE WHEN c_id='03' THEN s_score ELSE NULL END ) cid3,
AVG(s_score) average
FROM student,score
WHERE student.s_id=score.s_id
GROUP BY student.s_id
ORDER BY average DESC;
3-- 9.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
SELECT score.c_id,course.c_name,MAX(s_score),MIN(s_score),AVG(s_score),
SUM(CASE WHEN s_score>=60 THEN 1 ELSE 0 END)/COUNT(s_score) '及格率',
SUM(CASE WHEN s_score>=70 AND s_score<80 THEN 1 ELSE 0 END)/COUNT(s_score) '中等率',
SUM(CASE WHEN s_score>=80 AND s_score<90 THEN 1 ELSE 0 END)/COUNT(s_score) '优良率',
SUM(CASE WHEN s_score>=90 THEN 1 ELSE 0 END)/COUNT(s_score) '优秀率'
FROM score,course WHERE score.c_id=course.c_id
GROUP BY score.c_id;
3–10、按各科成绩进行排序
SELECT * FROM (SELECT * FROM score WHERE c_id = '01'ORDER BY s_score DESC ) a
UNION ALL
SELECT * FROM (SELECT * FROM score WHERE c_id = '02'ORDER BY s_score DESC ) a
UNION ALL
SELECT * FROM (SELECT * FROM score WHERE c_id = '03'ORDER BY s_score DESC LIMIT 0,100) a
或者
SELECT * FROM score ORDER BY c_id,s_score DESC
