问题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('张三'）
