MySQL存储过程_触发器_游标——Baidu Comate

# 问题1： 

帮我创建2个表student与score表，要求student表有id,createDate,userName,phone,age,sex,introduce,
要求score表有id,scoreName,result,studentId(student表的id外键)。
要求student表中插入5条学生信息，都要是中文的。
要求score表中对应studentId插入10条信息，每个学生ID要有2个科目的成绩。
给出DDL与DML语句。

DDL+DML

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATE NOT NULL,
    userName VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    sex ENUM('男', '女') NOT NULL,
    introduce TEXT
);
 
CREATE TABLE score (
    id INT PRIMARY KEY AUTO_INCREMENT,
    scoreName VARCHAR(255) NOT NULL,
    result FLOAT NOT NULL,
    studentId INT,
    FOREIGN KEY (studentId) REFERENCES student(id) ON DELETE CASCADE
);
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-04-01', '张三', '13800138000', 20, '男', '学习刻苦'),
('2023-04-02', '李四', '13800138001', 21, '女', '成绩优异'),
('2023-04-03', '王五', '13800138002', 22, '男', '热爱运动'),
('2023-04-04', '赵六', '13800138003', 20, '女', '文静内向'),
('2023-04-05', '孙七', '13800138004', 21, '男', '乐于助人');
 
-- 张三的成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('数学', 90, 1);
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', 85, 1);
 
 
-- 李四的成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('数学', 88, 2);
INSERT INTO score (scoreName, result, studentId) VALUES ('物理', 92, 2);
 
 
-- 王五的成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', 80, 3);
INSERT INTO score (scoreName, result, studentId) VALUES ('化学', 85, 3);
 
 
-- 赵六的成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('物理', 95, 4);
INSERT INTO score (scoreName, result, studentId) VALUES ('化学', 89, 4);
 
 
-- 孙七的成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', 78, 5);
INSERT INTO score (scoreName, result, studentId) VALUES ('生物', 87, 5);

提问的时候一定要选中两个DDL 

# 问题2： 

根据score表的DDL添加4条语句，要求studentId为3和4，每个studentId都添加两条数据，科目是数学与英语。

 
-- 插入studentId为3的数学成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('数学', 95, 3);
 
 
-- 插入studentId为3的英语成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', 89, 3);
 
 
-- 插入studentId为4的数学成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('数学', 92, 4);
 
 
-- 插入studentId为4的英语成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', 86, 4);

# 问题3： 

帮我创建一个多表联合查询以student为主表，查看学生的所有科目成绩总分，并根据总分进行倒序排名，在查询第一列添加排名顺序。

SELECT 
    s.id AS studentId,
    s.userName,
    SUM(sc.result) AS totalScore
FROM
    student s
LEFT JOIN score sc ON s.id = sc.studentId
GROUP BY s.id, s.userName;

这条SQL语句的解释如下：

SELECT 用来指定你想从数据库中获取的字段。

s.id AS studentId 选择student表的id字段，并在结果集中将其重命名为studentId。

s.userName 选择student表的userName字段，表示学生的名字。

SUM(sc.result) AS totalScore 计算每个学生所有科目的成绩总和，并将这个总和重命名为totalScore。这里使用了SUM聚合函数来对成绩进行求和。

FROM student s 指定了主表为student，并给它设置了一个别名s，以便在查询中简化引用。

LEFT JOIN score sc ON s.id = sc.studentId 使用了左连接（LEFT JOIN）来联合score表，并给它设置了一个别名sc。连接条件是两个表中的studentId必须匹配。左连接确保即使某个学生在score表中没有成绩记录，也会出现在结果集中，其成绩总分将被计算为0。

GROUP BY s.id, s.userName 将结果集按照每个学生的id和userName进行分组，这是使用聚合函数（如SUM）时通常需要的，以确保每个学生的成绩不会被混淆在一起。

执行这条SQL语句后，你将得到一个包含每个学生ID、用户名和所有科目成绩总分的列表。

 

# 问题4：存储过程 

帮我根据两个DDL创建一个插入信息的存储过程，先插入10条学生信息，再根据插入的学生表信息插入100条对应的studentId的score成绩，
所有信息随机生成，学生表的自增序号从6开始。

调用

call InsertRandomData(); 

# 问题5：触发器 

帮我创建一个修改score表scoreName的触发器，当修改scoreName的时候触发，判断修改的scoreName是否是数学，如果是数学就改成(天书)。
并且给出测试事例。

这个触发器tr_before_update_scoreName会在score表上的UPDATE操作之前执行。当scoreName字段的新值（NEW.scoreName）是"数学"时，它会自动将scoreName的值改为"天书"。

测试示例
为了测试这个触发器，你可以按照以下步骤操作：

  1.插入测试数据

首先，向score表中插入一些测试数据，包括一个scoreName为"数学"的记录。

INSERT INTO score (scoreName, result, studentId) VALUES ('数学', 90, 1);
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', 85, 1);

  1.执行更新操作

UPDATE score SET scoreName = '数学' WHERE id = 1;

接下来，尝试更新score表中scoreName为"数学"的记录

由于触发器的存在，即使你尝试将scoreName设置为"数学"，它实际上会被自动更改为"天书"。

  1.验证结果
最后，通过查询来验证触发器是否按预期工作。

SELECT * FROM score WHERE id = 1;

你应该会看到类似下面的结果，其中scoreName字段的值已经被更改为"天书"：

id | scoreName | result | studentId
 
---|-----------|--------|----------
1  | 天书      | 90     | 1

 这样，你就成功地创建并测试了一个修改scoreName字段的触发器。

# 问题6：游标 

创建一个两个表的综合游标查询，要求根据学生姓名进行查询，查询的结果返回两个表的综合信息。

DELIMITER //
 
CREATE PROCEDURE GetStudentInfoByName(IN targetName VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE stu_id INT;
    DECLARE stu_createDate DATE;
    DECLARE stu_userName VARCHAR(255);
    DECLARE stu_phone VARCHAR(20);
    DECLARE stu_age INT;
    DECLARE stu_sex ENUM('男', '女');
    DECLARE stu_introduce TEXT;
    DECLARE sc_id INT;
    DECLARE sc_scoreName VARCHAR(255);
    DECLARE sc_result FLOAT;
    -- 声明游标
    DECLARE cur_student INFORMATION_SCHEMA.ROUTINES.RESULT_TYPE IS NOT NULL CURSOR FOR 
        SELECT s.id, s.createDate, s.userName, s.phone, s.age, s.sex, s.introduce, sc.id AS score_id, sc.scoreName, sc.result
        FROM student s
        LEFT JOIN score sc ON s.id = sc.studentId
        WHERE s.userName = targetName;
    -- 声明NOT FOUND的处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
    OPEN cur_student;
 
    read_loop: LOOP
        FETCH cur_student INTO stu_id, stu_createDate, stu_userName, stu_phone, stu_age, stu_sex, stu_introduce, sc_id, sc_scoreName, sc_result;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 这里可以处理每一行的数据，例如打印或存储等。
        SELECT stu_id, stu_createDate, stu_userName, stu_phone, stu_age, stu_sex, stu_introduce, sc_id, sc_scoreName, sc_result;
    END LOOP;
 
    CLOSE cur_student;
END //
 
DELIMITER ;

调用游标

CALL GetStudentInfoByName('张三');