【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