【Mysql】基本命令(二)

 

练习题:

 链表案例

1.查询成绩表显示如下:

 

一、

SELECT
    score.sid,
    student.sname,
    student.gender,
    course.cname,
    score.number 
FROM
    score,
    student,
    course 
WHERE
    score.student_id = student.sid 
    AND score.corse_id = course.cid;

 二、

SELECT
    score.sid,
    student.sname,
    student.gender,
    course.cname,
    score.number 
FROM
    score
    LEFT JOIN student ON score.student_id = student.sid
    LEFT JOIN course ON score.corse_id = course.cid;

 

 组合与链表查询:

2.查询每个学生的平均成绩并显示【学号、姓名、平均成绩】:

一、

SELECT
score.sid AS 学号, student.sname AS 姓名, avg( score.number ) AS 平均成绩 FROM score LEFT JOIN student ON score.sid = student.sid GROUP BY score.sid

二、

SELECT
    学号,
    student.sname AS 姓名,平均成绩 
FROM
    ( SELECT sid AS 学号, avg( number ) AS 平均成绩 FROM score GROUP BY sid ) AS T
    LEFT JOIN student ON T.学号 = student.sid

 

3.查询学生学习的科目大于1门的学生学号、姓名、学习科目数量

SELECT
    学号,
    sname AS 姓名,学习科目数量 
FROM
    ( SELECT student_id AS 学号, count( corse_id ) AS 学习科目数量 FROM score GROUP BY student_id HAVING 学习科目数量 > 1 ) AS v
    LEFT JOIN student ON v.`学号` = student.sid

4.查询所有课程的平均成绩和及格率

 

SELECT
    score.corse_id AS 课程 ID,
    course.cname AS 课程名称,
    avg( score.number ) AS 平均成绩,
    sum( CASE WHEN score.number >= 60 THEN 1 ELSE 0 END )/ score.corse_id * 100 AS "及格率%" 
FROM
    score
    LEFT JOIN course ON score.corse_id = course.cid 
GROUP BY
    score.corse_id

 

280

posted @ 2020-07-22 21:45  Shuranima  阅读(67)  评论(0)    收藏  举报