当前位置：首页 > news >正文

MySQL——存储过程，触发器

news 来源：原创 2024/7/1 2:16:01

BaiduComate:

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

DDL

CREATE TABLE student (id INT PRIMARY KEY AUTO_INCREMENT, -- 学生ID，自增主键createDate DATETIME NOT NULL,       -- 创建日期userName VARCHAR(255) NOT NULL,     -- 用户名phone VARCHAR(20),                   -- 电话号码age INT NOT NULL,                    -- 年龄sex ENUM('男', '女', '其他'), -- 性别introduce TEXT                       -- 简介
);
CREATE TABLE score (id INT PRIMARY KEY AUTO_INCREMENT,      -- 成绩ID，自增主键scoreName VARCHAR(255) NOT NULL,        -- 成绩名称（科目）result INT NOT NULL,                     -- 成绩结果studentId INT,                           -- 学生ID，外键FOREIGN KEY (studentId) REFERENCES student(id) -- 外键约束
);

DML

INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-04-01 08:00:00', '张三', '13800138000', 20, '男', '来自计算机科学专业'),
('2023-04-01 08:10:00', '李四', '13800138001', 21, '男', '数学系高材生'),
('2023-04-01 08:20:00', '王五', '13800138002', 19, '女', '物理系新生'),
('2023-04-01 08:30:00', '赵六', '13800138003', 20, '女', '化学系学生'),
('2023-04-01 08:40:00', '孙七', '13800138004', 22, '男', '文学爱好者');-- 张三的成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 95, (SELECT id FROM student WHERE userName = '张三')),
('英语', 88, (SELECT id FROM student WHERE userName = '张三'));-- 李四的成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('物理', 92, (SELECT id FROM student WHERE userName = '李四')),
('化学', 85, (SELECT id FROM student WHERE userName = '李四'));-- 王五的成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('计算机', 90, (SELECT id FROM student WHERE userName = '王五')),
('历史', 80, (SELECT id FROM student WHERE userName = '王五'));-- 赵六的成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('生物', 85, (SELECT id FROM student WHERE userName = '赵六')),
('地理', 82, (SELECT id FROM student WHERE userName = '赵六'));-- 孙七的成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('政治', 89, (SELECT id FROM student WHERE userName = '孙七')),
('音乐', 93, (SELECT id FROM student WHERE userName = '孙七'));

DQL

多表联合

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

SELECT s.id AS studentId,s.userName,SUM(sc.result) AS totalScore
FROMstudent s
JOIN score sc ON s.id = sc.studentId
GROUP BY s.id, s.userName
ORDER BY totalScore DESC;

存储过程

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

DELIMITER //
CREATE PROCEDURE InsertStudentAndScoreData()
BEGINDECLARE v_student_id INT;DECLARE v_counter INT;DECLARE v_random_score_count INT;-- 设置学生ID自增值为6ALTER TABLE student AUTO_INCREMENT = 6;-- 插入10条随机学生信息WHILE (SELECT COUNT(*) FROM student) < 10 DOINSERT INTO student (createDate, userName, phone, age, sex, introduce)VALUES (NOW(),CONCAT('Student_', FLOOR(RAND() * 10000)),CONCAT('+1', FLOOR(RAND() * 899999999) + 100000000), -- 随机电话号码FLOOR(RAND() * (50 - 18 + 1)) + 18, -- 随机年龄在18到50之间ELT(1 + FLOOR(RAND() * 3), '男', '女', '其他'), -- 随机性别CONCAT('Introduction for ', FLOOR(RAND() * 10000)) -- 随机简介);SET v_student_id = LAST_INSERT_ID(); -- 获取最近插入的学生ID-- 为该学生随机插入1到18条成绩记录SET v_random_score_count = FLOOR(RAND() * 18) + 1; -- 随机决定成绩记录数SET v_counter = 0;WHILE v_counter < v_random_score_count DOINSERT INTO score (scoreName, result, studentId)VALUES (CONCAT('Subject_', FLOOR(RAND() * 100)), -- 随机科目名FLOOR(RAND() * 101), -- 随机分数0到100v_student_id -- 学生ID);SET v_counter = v_counter + 1;END WHILE;END WHILE;
END //
DELIMITER ;CALL InsertStudentAndScoreData();

DELIMITER //
CREATE PROCEDURE InsertStudentAndScoreData()
BEGINDECLARE v_student_id INT;DECLARE v_counter INT DEFAULT 0;DECLARE v_total_scores_to_insert INT DEFAULT 18;DECLARE v_scores_inserted INT DEFAULT 0;DECLARE v_random_student_index INT;DECLARE v_random_score_name VARCHAR(255);DECLARE v_random_result INT;-- 设置学生表的自增序号从6开始ALTER TABLE student AUTO_INCREMENT = 6;-- 插入10条随机学生信息WHILE v_counter < 10 DOINSERT INTO student(createDate, userName, phone, age, sex, introduce)VALUES (NOW(),CONCAT('User', FLOOR(RAND() * 10000)),CONCAT('+', FLOOR(RAND() * 9999999999)),FLOOR(RAND() * 10 + 18),ELT(1 + FLOOR(RAND() * 3), 'Male', 'Female', 'Other'),'Random Introduction');SET v_counter = v_counter + 1;END WHILE;-- 初始化计数器SET v_counter = 0;-- 插入成绩，直到达到18条记录WHILE v_scores_inserted < v_total_scores_to_insert DO-- 随机选择一个已插入的学生IDSET v_random_student_index = FLOOR(RAND() * 10) + 6; -- 学生ID范围从6到15-- 生成随机成绩数据和科目名称SET v_random_score_name = CONCAT('Subject', FLOOR(RAND() * 10));SET v_random_result = FLOOR(RAND() * 101); -- 随机分数从0到100-- 插入成绩记录INSERT INTO score(scoreName, result, studentId)VALUES (v_random_score_name, v_random_result, v_random_student_index);-- 更新已插入成绩计数SET v_scores_inserted = v_scores_inserted + 1;END WHILE;
END //
DELIMITER ;CALL InsertStudentAndScoreData();

#问题4: 触发器
帮我创建一个修改score表scoreName的触发器，当修改scoreNlame的时候脸发，判断修改的scoreName是否是数学，如果是数学就改成(天书).
并且给出测试示例。
#问题5:游标
创建一个两个表的综合游标查询，要求根据学生姓名进行查询，查询的结果返回两个表的综合信息。