MySQL集｜每日一题：使用 SQL，操作某学校入学成绩相关数据

程序员星河 2021-05-19

1207

现某学校有以下几张数据表：

1.查询所有数学成绩，并按 score（分数）列降序排序，显示 name、cname、score 三列，没有成绩显示 0。

SELECT
    s.name,
    c.cname,
    IFNULL(r.score, 0) score
FROM
    result r
    LEFT JOIN student s ON r.sid = s.sid
    LEFT JOIN course c ON r.cid = c.cid
WHERE
    c.cname = '数学' 
ORDER BY
    r.score DESC;

2.将所有得分中，存在小于 60 分或缺考某科的学生的 state 列，改为不及格。

UPDATE
    student
SET 
    state = '不及格'
WHERE sid IN 
    (
    SELECT * FROM 
        (SELECT s.sid FROM student s, course c WHERE
            NOT EXISTS (SELECT * FROM result r WHERE s.sid = r.sid AND c.cid = r.cid) 
        UNION
        SELECT DISTINCT sid FROM result WHERE score < 60) t1
    );

笛卡尔积是关系代数里的一个概念，但它在 SQL 中的实现方式是交叉连接（Cross Join），所有连接方式都会先生成临时笛卡尔积表，表示两个表中的每一行数据任意组合，下图中两个表连接即为笛卡尔积（交叉连接）。
在大多数应用中，笛卡尔积本身没有什么实际用处，只有在两个表连接时加上限制条件，才会有实际意义，像我们实现隐式内连接时的写法就是在添加限制条件。

上方 SQL 语句中，我们却恰好利用了学生表及课程表的笛卡尔积，因为这样就得到了所有学生和所有课程的一一组合数据，随后从中找到不存在于成绩表中的数据，即代表着缺考了某科的学生。

3.学生赵六考试作弊，请删除他的所有成绩。

DELETE FROM result
WHERE
    sid = (SELECT sid FROM student WHERE name = '赵六');

4.学生田七，今年 10 岁，男，办理四年级入学，入学考试成绩为：语文 65、英语 80、数学 99，请将此信息插入到对应数据表中。

-- 插入学生数据
INSERT INTO student 
    (name, age, sex, grade)
VALUES
    ('田七', 10, '男', '四年级');
 
-- 插入成绩数据
INSERT INTO result 
    (sid, cid, score)
VALUES
    (5, 1, 65),
    (5, 2, 99),
    (5, 3, 80);

5.请统计各个年级各个学科成绩总和，显示 grade、cname、总和三列。

SELECT
    s.grade,
    c.cname,
    SUM(r.score) 总和
FROM
    result r
    LEFT JOIN student s ON r.sid = s.sid
    LEFT JOIN course c ON r.cid = c.cid 
GROUP BY s.grade, r.cid;

6.请用存储过程完成，根据输入的成绩，获取大于该成绩的学生信息，显示 name、age、sex、grade、state、cname、score。

存储过程：是指一组可编程的函数，是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

-- 定义存储过程
DELIMITER $$
CREATE PROCEDURE findStuByScore(findScore int)
BEGIN
    SELECT
        s.name, s.age, s.sex, s.grade, s.state,
        c.cname, r.score
    FROM
        result r
        LEFT JOIN student s ON r.sid = s.sid
        LEFT JOIN course c ON r.cid = c.cid
    WHERE
        r.score > findScore;
END $$
DELIMITER ;

-- 调用存储过程
CALL findStuByScore(90)

友情提示：题目来源于各家真实企业，以上回答仅供参考，不能确定是否符合出题人要考查的知识点！

mysql

文章转载自程序员星河，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL集｜每日一题：使用 SQL，操作某学校入学成绩相关数据

评论