创建一个学生成绩登记表,插入一些记录,编写一个统计不及格学生数的存储过程,要求采用循环语句和条件判断语句实现。

创建学生成绩登记表：

CREATE TABLE student_scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    score INT NOT NULL
);

插入一些记录：

INSERT INTO student_scores (name, score) VALUES
('张三', 80),
('李四', 90),
('王五', 60),
('赵六', 75),
('钱七', 55),
('孙八', 85),
('周九', 95),
('吴十', 65);

编写统计不及格学生数的存储过程：

DELIMITER //

CREATE PROCEDURE count_fail_students(OUT fail_count INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE score INT;
    SET fail_count = 0;
    WHILE i <= (SELECT COUNT(*) FROM student_scores) DO
        SET score = (SELECT score FROM student_scores WHERE id = i);
        IF score < 60 THEN
            SET fail_count = fail_count + 1;
        END IF;
        SET i = i + 1;
    END WHILE;
END//

DELIMITER ;

调用存储过程并输出不及格学生数：

CALL count_fail_students(@fail_count);
SELECT @fail_count;

输出结果为：

+------------+
| @fail_count |
+------------+
|          2 |
+------------+