问题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 PRIMARY KEY AUTO_INCREMENT,
createDate DATETIME NOT NULL,
userName VARCHAR(50) NOT NULL,
phone VARCHAR(15) NOT NULL,
age INT NOT NULL,
sex ENUM('男', '女') NOT NULL,
introduce TEXT
);
-- 创建score表
CREATE TABLE score (
id INT PRIMARY KEY AUTO_INCREMENT,
scoreName VARCHAR(50) NOT NULL,
result DECIMAL(5,2) NOT NULL,
studentId INT,
FOREIGN KEY (studentId) REFERENCES student(id) ON DELETE CASCADE ON UPDATE CASCADE
);
问题2:
帮我创建一个多表联合查询以student为主表,查看学生的所有科目成绩总分。
SELECT
s.userName,
COALESCE(SUM(sc.result), 0) AS totalScore
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 InsertRandomStudentsAndScores()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE student_id INT;
DECLARE random_name VARCHAR(50);
DECLARE random_phone VARCHAR(15);
DECLARE random_age INT;
DECLARE random_sex ENUM('男', '女');
DECLARE random_introduce TEXT;
DECLARE random_score_name VARCHAR(50);
DECLARE random_result DECIMAL(5,2);
-- 设置AUTO_INCREMENT的起始值为6
ALTER TABLE student AUTO_INCREMENT = 6;
-- 插入10条学生信息
WHILE i < 10 DO
-- 生成随机学生信息
SET random_name = CONCAT('学生', FLOOR(RAND() * 1000));
SET random_phone = CONCAT('1', FLOOR(RAND() * 10000000000), FLOOR(RAND() * 10000));
SET random_age = FLOOR(18 + (RAND() * 10));
SET random_sex = IF(RAND() > 0.5, '男', '女');
SET random_introduce = CONCAT('学生', FLOOR(RAND() * 1000), '的自我介绍...');
-- 插入学生信息
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES (NOW(), random_name, random_phone, random_age, random_sex, random_introduce);
-- 获取刚插入的学生的ID
SELECT LAST_INSERT_ID() INTO student_id;
-- 为该学生插入10条成绩信息
SET i = 0;
WHILE i < 10 DO
-- 生成随机成绩信息
SET random_score_name = CONCAT('科目', FLOOR(RAND() * 10));
SET random_result = FLOOR(60 + (RAND() * 41)) / 10.0; -- 生成60到100之间的随机小数
-- 插入成绩信息
INSERT INTO score (scoreName, result, studentId)
VALUES (random_score_name, random_result, student_id);
SET i = i + 1;
END WHILE;
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
调用:
CALL InsertRandomStudentsAndScores();
问题4:
帮我创建一个修改score表scoreName的触发器,当修改scoreName的时候触发,判断修改的scoreName是否是数学,如果是数学就改成(天书)。
并且给出测试示例。
DELIMITER //
CREATE TRIGGER trg_update_scoreName_to_tianshu
BEFORE UPDATE ON score
FOR EACH ROW
BEGIN
IF NEW.scoreName = '数学' THEN
SET NEW.scoreName = '(天书)';
END IF;
END;
//
DELIMITER ;
-- 插入学生数据
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES (NOW(), '张三', '1234567890', 20, '男', '这是张三的个人介绍');
-- 假设上面插入的学生id为1,插入成绩数据
INSERT INTO score (scoreName, result, studentId)
VALUES ('数学', 90.50, 1);
-- 尝试更新scoreName为数学
UPDATE score SET scoreName = '数学' WHERE id = 1;
-- 检查scoreName是否被正确修改为(天书)
SELECT * FROM score WHERE id = 1;
问题5:
创建一个两个表的综合【游标查询】,要求根据学生姓名进行查询,查询的结果返回两个表的综合信息。
DELIMITER //
CREATE PROCEDURE GetStudentScoresByName(IN p_userName VARCHAR(50))
BEGIN
-- 声明变量来存储从游标中检索到的数据
DECLARE done INT DEFAULT FALSE;
DECLARE v_student_id INT;
DECLARE v_createDate DATETIME;
DECLARE v_userName VARCHAR(50);
DECLARE v_phone VARCHAR(15);
DECLARE v_age INT;
DECLARE v_sex ENUM('男', '女');
DECLARE v_introduce TEXT;
DECLARE v_score_id INT;
DECLARE v_scoreName VARCHAR(50);
DECLARE v_result DECIMAL(5,2);
-- 声明游标
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
LEFT JOIN
score sc ON s.id = sc.studentId
WHERE
s.userName = p_userName;
-- 声明一个处理器,当游标完成遍历时设置done为TRUE
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
OPEN cur;
-- 遍历游标中的数据
read_loop: LOOP
FETCH cur INTO v_student_id, v_createDate, v_userName, v_phone, v_age, v_sex, v_introduce,
v_score_id, v_scoreName, v_result;
IF done THEN
LEAVE read_loop;
END IF;
-- 这里你可以根据需要处理检索到的数据,例如输出到控制台或返回给客户端等
-- 在这里我们仅打印出数据
SELECT
v_student_id, v_createDate, v_userName, v_phone, v_age, v_sex, v_introduce,
v_score_id, v_scoreName, v_result;
END LOOP;
-- 关闭游标
CLOSE cur;
END //
DELIMITER ;
调用游标:
CALL GetStudentScoresByName('张三');