8查询进阶

1.sum(case when c_id='01' then s_score else NULL end ) cid1,

加行记录为单列值,该新列和值

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(难)

-- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(难)

SELECT s_id,AVG(s_score) FROM score GROUP BY s_id ORDER BY AVG(s_score) DESC;#平均成绩从高到低显示所有学生的平均成绩

#sum作用加行记录

SELECT 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) FROM score GROUP BY s_id ORDER BY AVG(s_score) DESC;

#sum作用加行记录

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;

select student.s_id,

(case when c_id='01' then s_score else NULL end ) cid1,

(case when c_id='02' then s_score else NULL end ) cid2,

(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;

 

 2及格率等等sum(case when s_score>=60 then 1 else 0 end)/count(s_score) '及格率'

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

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>=90 then 1 else 0 end)/count(s_score) '优秀率'

from score,course where score.c_id=course.c_id

group by score.c_id;

-- 按sid  查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

SELECT score.s_id,MAX(s_score),MIN(s_score),AVG(s_score),

SUM(CASE WHEN s_score>=60 THEN 1 ELSE 0 END)/COUNT(s_id) "及格率",

SUM(CASE WHEN s_score>=90 THEN 1 ELSE 0 END)/COUNT(s_id) "优秀率"

FROM score,course WHERE score.c_id = course.c_id GROUP BY score.s_id;

 

3平均成绩名次

笛卡尔积cross join

SELECT * FROM course CROSS JOIN student

sum(case when s_score>0 then s_score else 0 end)/count(a.c_id)

空记录作为0运算

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

 row_number() over(order by savg desc) AS rank_cid

对savg字段标序号并查询出列值

#平均成绩按3门
SELECT s_name,s_id,savg,
row_number() over(order by savg desc) AS rank_cid FROM(
#平均成绩按所有课程
SELECT s_name,a.s_id,
sum(case when s_score>0 then s_score else 0 end)/count(a.c_id) AS savg
FROM (SELECT * FROM course CROSS JOIN student) AS a
LEFT JOIN score AS s ON a.c_id = s.c_id AND a.s_id = s.s_id
GROUP BY a.s_id 
) as b;

 SELECT s_id,AVG(s_score) FROM score GROUP BY s_id ORDER BY SUM(s_score) DESC; #总成绩下排名
SELECT s_id,AVG(s_score) FROM score GROUP BY s_id ORDER BY AVG(s_score)  DESC;#平均成绩下排名 有的两门

 -- 序号问题
#row_number 不重复123, rank 存在相同排名重复113, dense_rank 相同排名重复 112 

select s_id,c_id,s_score, 
row_number() over(order by s_score desc) rank_cid  #只给成绩标序号
from score ORDER BY s_score;

select s_id,c_id,s_score, 
rank() over(order by s_score desc) rank_cid  #只给成绩标序号
from score ORDER BY s_score;

select s_id,c_id,s_score, 
dense_rank() over(order by s_score desc) rank_cid  #只给成绩标序号
from score ORDER BY s_score;
 

 

 4分组排序partition by c_id

row_number() over(partition by c_id order by s_score desc) rank_cid

select s_id,c_id,s_score, 
row_number() over(partition by c_id order by s_score desc) rank_cid #按课程分组给序号
from score ORDER BY s_id;

select s_id,c_id,s_score, 
row_number() over(order by s_score desc) rank_cid  #只给成绩标序号
from score ORDER BY s_id;

5自连接

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

SELECT  DISTINCT a.* FROM score a

INNER JOIN score b ON

a.s_id = b.s_id

AND a.c_id != b.c_id

AND a.s_score = b.s_score

6日期相关，年龄TIMESTAMPDIFF(year,b,n)

年龄：TIMESTAMPDIFF(year,s_birth,NOW()) age

SELECT YEAR("1990-01-01");
SELECT YEAR(NOW());
SELECT MONTH(NOW());#一年之内
SELECT WEEK(NOW());#一年之内

 7分段case when  then else写法

SELECT *,CASE

WHEN s_score<60 THEN "D[<60]"

WHEN s_score>=60  AND s_score<70  THEN "C[70-60]"

WHEN s_score>=70  AND s_score<85  THEN "B[85-70]"

WHEN s_score>=85  AND s_score<100  THEN "A[100-85]"

ELSE "不合法" END AS "成绩情况"

FROM(

分段可以解决union需要多个select语句情况

 8插入-进阶版

insert into tablename (cols)  select .....

可加union

字段对应，自增可略该字段

 insert into score values("10","10",90);

#除了自动增长的字段auto_increment
insert into teacher (t_name) SELECT s_name from student WHERE s_id="01";