问题一:DDL与DML
帮我创建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语句。
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`createDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`userName` varchar(50) NOT NULL,
`phone` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` enum('男','女') DEFAULT NULL,
`introduce` text,
PRIMARY KEY (`id`),
UNIQUE KEY `userName` (`userName`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
TE TABLE `score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`scoreName` varchar(50) NOT NULL,
`result` decimal(5,2) NOT NULL,
`studentId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `studentId` (`studentId`),
CONSTRAINT `score_ibfk_1` FOREIGN KEY (`studentId`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
问题二
帮我创建一个多表联合查询以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;
问题三
帮我根据两个DDL创建一个插入信息的存储过程,使用循环先插入10条学生表信息,再根据插入的学生表信息插入100条对应studentId的score成绩,
所有信息随机生成,学生表的自增序号从6开始。不使用DECLARE声明,要使用set声明
DELIMITER //
CREATE PROCEDURE InsertRandomStudentAndScoreData()
BEGIN
SET @i = 1;
SET @student_id = 0;
SET @rand_username = '';
SET @rand_phone = '';
SET @rand_age = 0;
SET @rand_sex = '';
SET @rand_introduce = '';
SET @rand_score_name = '';
SET @rand_result = 0.00;
-- 重置学生表的AUTO_INCREMENT值
ALTER TABLE `student` AUTO_INCREMENT = 6;
-- 插入10条学生信息
WHILE @i <= 10 DO
SET @rand_username = CONCAT('Student', FLOOR(RAND() * 10000)); -- 生成随机用户名
SET @rand_phone = CONCAT('1', FLOOR(RAND() * 900000000) + 100000000); -- 生成随机电话号码
SET @rand_age = FLOOR(RAND() * 30) + 1; -- 生成1到30的随机年龄
SET @rand_sex = IF(RAND() > 0.5, '男', '女'); -- 生成随机性别
SET @rand_introduce = CONCAT('This is a random introduction for ', @rand_username); -- 生成随机介绍
-- 插入学生信息
INSERT INTO `student` (`userName`, `phone`, `age`, `sex`, `introduce`)
VALUES (@rand_username, @rand_phone, @rand_age, @rand_sex, @rand_introduce);
-- 获取新插入学生的ID
SET @student_id = LAST_INSERT_ID();
-- 为该学生插入10条成绩信息
SET @j = 1;
WHILE @j <= 10 DO
SET @rand_score_name = CONCAT('Subject', FLOOR(RAND() * 10)); -- 生成随机科目名
SET @rand_result = FLOOR(RAND() * 100) + 1; -- 生成1到100的随机成绩
-- 插入成绩信息
INSERT INTO `score` (`scoreName`, `result`, `studentId`)
VALUES (@rand_score_name, @rand_result, @student_id);
SET @j = @j + 1;
END WHILE;
SET @i = @i + 1;
END WHILE;
END //
DELIMITER ;
CALL InsertRandomStudentAndScoreData();
问题四:触发器
帮我创建一个修改score表scoreName的触发器,当修改scoreName的时候触发,判断修改的scoreName是否是数学,如果是数学就改成(天书)。
并且给出测试示例。
-- 创建触发器
DELIMITER //
CREATE TRIGGER trg_update_scoreName
BEFORE UPDATE ON `score`
FOR EACH ROW
BEGIN
IF NEW.scoreName = '数学' THEN
SET NEW.scoreName = '天书';
END IF;
END;
//
DELIMITER ;
UPDATE `score` SET `scoreName` = '数学' WHERE id = 1;
问题五
创建一个两个表的综合【游标查询】,要求根据学生姓名进行查询,查询的结果返回两个表的综合信息。
DELIMITER //
CREATE PROCEDURE GetStudentAndScoreByName(IN p_userName VARCHAR(50))
BEGIN
-- 声明变量
DECLARE done INT DEFAULT FALSE;
DECLARE v_id INT;
DECLARE v_userName VARCHAR(50);
DECLARE v_phone VARCHAR(20);
DECLARE v_age INT;
DECLARE v_sex ENUM('男','女');
DECLARE v_introduce TEXT;
DECLARE v_scoreName VARCHAR(50);
DECLARE v_result DECIMAL(5,2);
-- 声明游标
DECLARE cur CURSOR FOR
SELECT
s.id,
s.userName,
s.phone,
s.age,
s.sex,
s.introduce,
sc.scoreName,
sc.result
FROM
student s
LEFT JOIN
score sc ON s.id = sc.studentId
WHERE
s.userName = p_userName;
-- 声明游标结束的处理程序
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
OPEN cur;
-- 读取游标数据
read_loop: LOOP
FETCH cur INTO v_id, v_userName, v_phone, v_age, v_sex, v_introduce, v_scoreName, v_result;
IF done THEN
LEAVE read_loop;
END IF;
-- 这里可以处理查询到的数据,比如打印出来或者返回给客户端
-- 例如,简单地将查询结果输出到控制台
SELECT
v_id AS student_id,
v_userName AS student_name,
v_phone AS student_phone,
v_age AS student_age,
v_sex AS student_sex,
v_introduce AS student_introduce,
v_scoreName AS score_name,
v_result AS score_result;
END LOOP;
-- 关闭游标
CLOSE cur;
END //
DELIMITER ;
CALL GetStudentAndScoreByName('目标学生姓名');