百度生成数据库

问题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()
BEGIN
    DECLARE 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 DO
        SET 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);

        -- 获取刚刚插入的学生ID
        SET studentId = LAST_INSERT_ID();

        -- 为该学生插入10条成绩信息
        SET i_score := 1;
        WHILE i_score <= 10 DO
            SET 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
BEGIN
    IF NEW.scoreName = '数学' THEN
        SET 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))
BEGIN
    DECLARE 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.result
        FROM 
            student s
        INNER JOIN 
            score sc ON s.id = sc.studentId
        WHERE 
            s.userName = targetName;
    
    -- 声明NOT FOUND处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO student_id, createDate, userName, phone, age, sex, introduce, score_id, scoreName, result;
        
        IF done THEN
            LEAVE 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('目标学生姓名');