AI创作——多表操作

DDL

-- 创建student表
CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    createDate DATE NOT NULL,
    userName VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    sex ENUM('男', '女') NOT NULL,
    introduce TEXT
);


-- 创建score表
CREATE TABLE score (
    id INT AUTO_INCREMENT PRIMARY KEY,
    scoreName VARCHAR(50) NOT NULL,
    result DECIMAL(5, 2) NOT NULL,
    studentId INT,
    FOREIGN KEY (studentId) REFERENCES student(id) ON DELETE CASCADE
);

DML

-- 向student表中插入5条学生信息
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-01-01', '张三', '13800138000', 18, '男', '张三是一个热爱学习的学生。'),
('2023-02-02', '李四', '13900139000', 19, '女', '李四对编程非常感兴趣。'),
('2023-03-03', '王五', '13700137000', 20, '男', '王五擅长数学和物理。'),
('2023-04-04', '赵六', '13600136000', 18, '女', '赵六喜欢阅读，尤其喜欢历史书籍。'),
('2023-05-05', '孙七', '13500135000', 19, '男', '孙七是班级的学习委员，成绩优异。');


-- 向score表中插入10条成绩信息，确保每个学生ID有2个科目的成绩

-- 假设已经插入的student表中id顺序为1, 2, 3, 4, 5
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 90.50, 1), -- 张三的数学成绩
('语文', 85.75, 1), -- 张三的语文成绩
('英语', 88.00, 2), -- 李四的英语成绩
('物理', 92.25, 2), -- 李四的物理成绩
('化学', 78.50, 3), -- 王五的化学成绩
('生物', 86.00, 3), -- 王五的生物成绩
('历史', 94.00, 4), -- 赵六的历史成绩
('地理', 89.25, 4), -- 赵六的地理成绩
('政治', 75.50, 5), -- 孙七的政治成绩
('体育', 82.00, 5); -- 孙七的体育成绩

问题2：

帮我创建一个多表联合查询以student为主表，查看学生的所有科目成绩总分。

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

 如果你的数据库系统默认将NULL值的和视为NULL（而不是0），你可能需要使用COALESCE函数来处理这种情况，将NULL转换为0，再进行求和

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

问题3：

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

DELIMITER //

CREATE PROCEDURE InsertRandomStudentAndScore()
BEGIN
    -- 设置学生表id的起始自增值为6
    ALTER TABLE student AUTO_INCREMENT = 6;
    
    -- 插入10条随机学生信息
    DECLARE v_student_count INT DEFAULT 10;
    DECLARE v_index INT DEFAULT 0;
    
    WHILE v_index < v_student_count DO
        SET v_index = v_index + 1;
        
        INSERT INTO student (createDate, userName, phone, age, sex, introduce)
        VALUES (
            NOW() - INTERVAL FLOOR(RAND() * 3653) DAY, -- 随机日期，近10年内的
            CONCAT('Student', FLOOR(RAND() * 10000)), -- 随机用户名
            CONCAT('13', FLOOR(RAND() * 9000000000)), -- 随机手机号，以13开头
            FLOOR(18 + (RAND() * 10)), -- 随机年龄，18到27岁
            IF(RAND() > 0.5, '男', '女'), -- 随机性别
            CONCAT('This is a random introduction for student ', FLOOR(RAND() * 1000)) -- 随机介绍
        );
    END WHILE;
    
    -- 插入对应每个学生的10条随机成绩信息
    DECLARE v_student_id INT;
    DECLARE v_score_count_per_student INT DEFAULT 10;
    DECLARE v_score_index INT DEFAULT 0;
    DECLARE done INT DEFAULT FALSE;
    
    -- 声明游标以遍历学生id
    DECLARE cur CURSOR FOR SELECT id FROM student WHERE id >= 6 ORDER BY id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_student_id;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        SET v_score_index = 0;
        
        WHILE v_score_index < v_score_count_per_student DO
            SET v_score_index = v_score_index + 1;
            
            INSERT INTO score (scoreName, result, studentId)
            VALUES (
                CONCAT('Subject', FLOOR(RAND() * 10)), -- 随机科目名称
                ROUND(RAND() * 100, 2), -- 随机成绩，0到100分
                v_student_id -- 对应的学生id
            );
        END WHILE;
    END LOOP;
    
    CLOSE cur;
END //

DELIMITER ;

要调用这个存储过程并插入数据，只需执行以下命令：

CALL InsertRandomStudentAndScore();

 

问题4：

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

并且给出测试示例。

 

DELIMITER //

CREATE TRIGGER trg_update_scoreName_to_tianshu
BEFORE UPDATE ON score
FOR EACH ROW
BEGIN
    IF NEW.scoreName = '数学' THEN
        SET NEW.scoreName = '天书';
    END IF;
END;
//

DELIMITER ;

现在，你可以测试这个触发器是否按预期工作。首先，插入一些测试数据：

INSERT INTO score (scoreName, result, studentId) VALUES ('数学', 90.50, 1);
INSERT INTO score (scoreName, result, studentId) VALUES ('语文', 85.75, 1);

然后，尝试更新scoreName为“数学”的记录：

UPDATE score SET scoreName = '数学' WHERE id = 1; -- 假设id为1的记录是我们刚才插入的“数学”成绩

最后，你可以通过查询score表来验证结果：

SELECT * FROM score WHERE id = 1;

问题5：

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

DELIMITER //

CREATE PROCEDURE FetchStudentAndScoresByUserName(IN p_userName VARCHAR(50))
BEGIN
    -- 声明变量来存储从游标中检索到的值
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_student_id INT;
    DECLARE v_createDate DATE;
    DECLARE v_userName VARCHAR(50);
    DECLARE v_phone VARCHAR(20);
    DECLARE v_age INT;
    DECLARE v_sex ENUM('男', '女');
    DECLARE v_introduce TEXT;
    DECLARE v_score_id INT;
    DECLARE v_scoreName VARCHAR(50);
    DECLARE v_result DECIMAL(5, 2);
    
    -- 声明游标
    DECLARE cur 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 = p_userName;
    
    -- 声明一个处理程序，当游标完成时设置done变量
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 打开游标
    OPEN cur;
    
    -- 获取游标中的每一行数据
    read_loop: LOOP
        FETCH cur INTO v_student_id, v_createDate, v_userName, v_phone, v_age, v_sex, v_introduce, v_score_id, v_scoreName, v_result;
        
        -- 如果游标结束，退出循环
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 在这里处理每一行数据，例如输出到控制台或执行其他操作
        SELECT v_student_id, v_createDate, v_userName, v_phone, v_age, v_sex, v_introduce, v_score_id, v_scoreName, v_result;
    END LOOP;
    
    -- 关闭游标
    CLOSE cur;
END //

DELIMITER ;

要调用这个存储过程并传递一个学生姓名作为参数，你可以这样做

CALL FetchStudentAndScoresByUserName('目标学生姓名');