mysql

一、创建表格

代码如下（示例）:

二、使用步骤

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