百度生成数据库

问题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语句。

-- 创建student表
CREATE TABLE student (id INT AUTO_INCREMENT PRIMARY KEY,createDate DATETIME NOT NULL,userName VARCHAR(50) NOT NULL,phone VARCHAR(20) NOT NULL UNIQUE,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 FLOAT NOT NULL,studentId INT,FOREIGN KEY (studentId) REFERENCES student(id) ON DELETE CASCADE
);-- 插入5条学生信息
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-03-15 10:00:00', '张三', '13800138000', 20, '男', '张三来自北京，喜欢阅读和编程。'),
('2023-03-16 11:30:00', '李四', '13900139000', 21, '女', '李四来自上海，擅长数学和物理。'),
('2023-03-17 09:15:00', '王五', '13700137000', 19, '男', '王五来自广州，热爱运动和音乐。'),
('2023-03-18 14:45:00', '赵六', '13600136000', 22, '女', '赵六来自深圳，对历史和旅游感兴趣。'),
('2023-03-19 08:30:00', '钱七', '13500135000', 20, '男', '钱七来自杭州，喜欢旅游和摄影。');-- 插入对应studentId的10条成绩信息-- 假设第一条和第二条是张三的成绩，第三条和第四条是李四的成绩，以此类推...
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 90, (SELECT id FROM student WHERE userName = '张三')),
('语文', 85, (SELECT id FROM student WHERE userName = '张三')),
('英语', 92, (SELECT id FROM student WHERE userName = '李四')),
('物理', 88, (SELECT id FROM student WHERE userName = '李四')),
('历史', 89, (SELECT id FROM student WHERE userName = '王五')),
('政治', 93, (SELECT id FROM student WHERE userName = '王五')),
('化学', 87, (SELECT id FROM student WHERE userName = '赵六')),
('生物', 91, (SELECT id FROM student WHERE userName = '赵六')),
('地理', 94, (SELECT id FROM student WHERE userName = '钱七')),
('体育', 86, (SELECT id FROM student WHERE userName = '钱七'));

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

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

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

 ALTER TABLE student AUTO_INCREMENT = 6;
DELIMITER //CREATE PROCEDURE InsertStudentsAndScores()
BEGINDECLARE i INT DEFAULT 1;DECLARE studentId INT;DECLARE studentName VARCHAR(50);DECLARE studentPhone VARCHAR(20);DECLARE studentAge INT;DECLARE studentSex ENUM('男', '女');DECLARE scoreName VARCHAR(50);DECLARE scoreResult FLOAT;-- 插入10条学生信息WHILE i <= 10 DOSET studentName = CONCAT('Student', FLOOR(RAND() * 1000));SET studentPhone = CONCAT('1', FLOOR(RAND() * 900000000) + 10000000);SET studentAge = FLOOR(RAND() * 30) + 5;SET studentSex = IF(RAND() > 0.5, '男', '女');SET createDate = NOW();-- 插入学生信息INSERT INTO student (createDate, userName, phone, age, sex) VALUES (createDate, studentName, studentPhone, studentAge, studentSex);-- 获取刚刚插入的学生IDSET studentId = LAST_INSERT_ID();-- 为该学生插入10条成绩信息SET i_score := 1;WHILE i_score <= 10 DOSET scoreName = CONCAT('Subject', FLOOR(RAND() * 10));SET scoreResult = FLOOR(RAND() * 100);-- 插入成绩信息INSERT INTO score (scoreName, result, studentId) VALUES (scoreName, scoreResult, studentId);SET i_score := i_score + 1;END WHILE;SET i := i + 1;END WHILE;
END //DELIMITER ;CALL InsertStudentsAndScores();

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

DELIMITER //CREATE TRIGGER tr_change_score_name_to_tianshu
BEFORE UPDATE ON score
FOR EACH ROW
BEGINIF NEW.scoreName = '数学' THENSET NEW.scoreName = '天书';END IF;
END;
//DELIMITER ;-- 假设score表中已经存在一条记录，其scoreName为'数学'-- 插入一个学生记录（如果尚未存在）
INSERT INTO student (createDate, userName, phone, age, sex) VALUES (NOW(), 'TestStudent', '1234567890', 20, '男');-- 插入一个score记录，其scoreName为'数学'
INSERT INTO score (scoreName, result, studentId) SELECT '数学', 90, LAST_INSERT_ID() FROM student WHERE id = LAST_INSERT_ID();-- 查询score表，确认scoreName为'数学'
SELECT * FROM score WHERE scoreName = '数学';-- 更新scoreName为'数学'的记录，触发触发器
UPDATE score SET scoreName = '数学' WHERE scoreName = '数学';-- 再次查询score表，确认scoreName已经被修改为'天书'
SELECT * FROM score WHERE scoreName = '天书';

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

SELECT s.id AS student_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
INNER JOIN score sc ON s.id = sc.studentId
WHERE s.userName = '目标学生姓名';DELIMITER //CREATE PROCEDURE FetchStudentAndScoreInfo(IN targetName VARCHAR(50))
BEGINDECLARE done INT DEFAULT FALSE;DECLARE student_id INT;DECLARE createDate DATETIME;DECLARE userName VARCHAR(50);DECLARE phone VARCHAR(20);DECLARE age INT;DECLARE sex ENUM('男', '女');DECLARE introduce TEXT;DECLARE score_id INT;DECLARE scoreName VARCHAR(50);DECLARE result FLOAT;-- 声明游标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.resultFROM student sINNER JOIN score sc ON s.id = sc.studentIdWHERE s.userName = targetName;-- 声明NOT FOUND处理程序DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN cur;read_loop: LOOPFETCH cur INTO student_id, createDate, userName, phone, age, sex, introduce, score_id, scoreName, result;IF done THENLEAVE read_loop;END IF;-- 在这里处理每一行的数据，例如打印或进行其他操作SELECT student_id, createDate, userName, phone, age, sex, introduce, score_id, scoreName, result;END LOOP;CLOSE cur;
END //DELIMITER ;CALL FetchStudentAndScoreInfo('目标学生姓名');