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

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: 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);

问题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;

问题3:

帮我根据两个DDL创建一个插入信息的存储过程,先插入10条学生表信息,再根据插入的学生表信息插入100条对应studentId的score成绩,

所有信息随机生成,学生表的自增序号从6开始。

DELIMITER //
CREATE PROCEDURE InsertRandomData()
BEGIN
    DECLARE v_counter INT DEFAULT 0;
    DECLARE v_student_id INT;
    DECLARE v_random_age INT;
    DECLARE v_random_score FLOAT;
    DECLARE v_random_sex ENUM('男', '女');
    DECLARE v_date DATE;
    DECLARE v_user_name VARCHAR(255);
    DECLARE v_phone VARCHAR(20);
    DECLARE v_score_name VARCHAR(255);
    
    -- 设置自增ID从6开始
    SET @@auto_increment_increment = 1;
    ALTER TABLE student AUTO_INCREMENT = 6;
 
    -- 插入学生信息
    WHILE v_counter < 10 DO
        SET v_random_age = FLOOR(RAND() * 10 + 15);  -- 假设年龄在15到25之间
        SET v_random_sex = IF(RAND() > 0.5, '男', '女');
        SET v_date = CURDATE() - INTERVAL FLOOR(RAND() * 365) DAY;  -- 随机生成一个过去的日期
        SET v_user_name = CONCAT('User', FLOOR(RAND() * 1000));  -- 随机用户名
        SET v_phone = CONCAT('138', LPAD(FLOOR(RAND() * 10000000), 7, '0'));  -- 随机电话
        
        INSERT INTO student (createDate, userName, phone, age, sex, introduce)
        VALUES (v_date, v_user_name, v_phone, v_random_age, v_random_sex, 'Random Introduction');
        
        SET v_counter = v_counter + 1;
    END WHILE;
    
    SET v_counter = 0;  -- 重置计数器
    
    -- 插入成绩信息
    WHILE v_counter < 100 DO
        SET v_student_id = FLOOR(RAND() * 10) + 6;  -- 随机选择一个已存在的学生ID(6到15之间)
        SET v_random_score = FLOOR(RAND() * 101);  -- 随机分数0到100
        SET v_score_name = CONCAT('Subject', FLOOR(RAND() * 10));  -- 随机科目名
        
        INSERT INTO score (scoreName, result, studentId)
        VALUES (v_score_name, v_random_score, v_student_id);
        
        SET v_counter = v_counter + 1;
    END WHILE;
    
    -- 恢复自增增量为默认值
    SET @@auto_increment_increment = 1;
END //
DELIMITER ;

调用

call InsertRandomData();

问题4:

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

并且给出测试示例。

DELIMITER //
 
CREATE TRIGGER tr_before_update_scoreName
BEFORE UPDATE ON score
FOR EACH ROW
BEGIN
    IF NEW.scoreName = '数学' THEN
        SET NEW.scoreName = '天书';
    END IF;
END;
 
//
 
DELIMITER ;
 
-- 查询
SELECT * FROM score where id=5;
-- 修改
UPDATE score SET scoreName = '数学' WHERE id = 5; 
-- 验证查询
SELECT * FROM score where id=5;

问题5:

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

DELIMITER //
 
CREATE PROCEDURE GetStudentInfoByName(IN studentName VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE student_id INT;
    DECLARE student_userName VARCHAR(255);
    DECLARE student_phone VARCHAR(20);
    DECLARE student_age INT;
    DECLARE student_sex ENUM('男', '女');
    DECLARE student_introduce TEXT;
    DECLARE score_id INT;
    DECLARE score_scoreName VARCHAR(255);
    DECLARE score_result FLOAT;
    -- 声明游标
    DECLARE cur CURSOR FOR 
        SELECT 
            s.id,
            s.userName,
            s.phone,
            s.age,
            s.sex,
            s.introduce,
            sc.id,
            sc.scoreName,
            sc.result
        FROM student s
        LEFT JOIN score sc ON s.id = sc.studentId
        WHERE s.userName = studentName;
    -- 声明结束处理器,当游标完成后设置done变量为TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
    -- 打开游标
    OPEN cur;
 
    -- 循环遍历游标中的所有记录
    read_loop: LOOP
        -- 从游标中取出数据
        FETCH cur INTO 
            student_id,
            student_userName,
            student_phone,
            student_age,
            student_sex,
            student_introduce,
            score_id,
            score_scoreName,
            score_result;
        -- 如果游标已经处理完所有记录,则退出循环
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 在这里可以对每一行数据进行处理,例如打印或进行其他操作
        SELECT 
            student_id,
            student_userName,
            student_phone,
            student_age,
            student_sex,
            student_introduce,
            score_id,
            score_scoreName,
            score_result;
    END LOOP;
 
    -- 关闭游标
    CLOSE cur;
END //
 
DELIMITER ;

调用游标

CALL GetStudentInfoByName('张三')

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
存储 SQL 关系型数据库
MySQL数据库进阶第四篇(视图/存储过程/触发器)
MySQL数据库进阶第四篇(视图/存储过程/触发器)
|
1月前
|
存储 关系型数据库 MySQL
MySQL周内训参照5、存储过程创建
MySQL周内训参照5、存储过程创建
33 1
|
1月前
|
存储 SQL 关系型数据库
MySQL周内训参照4、触发器-插入-修改-删除
MySQL周内训参照4、触发器-插入-修改-删除
20 1
|
23天前
|
存储 关系型数据库 分布式数据库
PolarDB产品使用问题之在将RDS迁移到PolarDB后,原先由root用户创建的视图、存储过程等是否可以继续使用的
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
27天前
|
存储 SQL 关系型数据库
在MySQL中使用存储过程返回更新前的记录
在MySQL中使用存储过程返回更新前的记录
15 0
|
1月前
|
存储 关系型数据库 MySQL
索引的威力--记一次MySQL存储过程优化
在MySQL存储过程中,一个`INSERT INTO SELECT`语句起初执行超过130秒,优化后,执行时间降低到1秒内,实现了100倍的性能提升。问题在于`NOT IN`子查询导致的慢查询,最终通过创建单列索引获得了最佳效果。文章还介绍了索引创建的基本语法,并讨论了单列索引与组合索引的优缺点。作者强调,随着数据量增加,索引对于查询性能的重要性,计划未来采用读写分离来进一步优化处理大量插入和查询的场景。
|
2月前
|
关系型数据库 MySQL 数据处理
轻松入门MySQL:数据库之触发器,自动守护你的数据宝库(17)
轻松入门MySQL:数据库之触发器,自动守护你的数据宝库(17)
|
1月前
|
存储 关系型数据库 MySQL
MySQL触发器实战:自动执行的秘密
MySQL触发器实战:自动执行的秘密
44 3
|
1月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(2)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
1月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(1)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】