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 DATE NOT NULL,
userName VARCHAR(50) NOT NULL,
phone VARCHAR(20) 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 INT NOT NULL,
studentId INT,
FOREIGN KEY (studentId) REFERENCES student(id) ON DELETE CASCADE
);
2.问题:帮我创建一个多表联合查询以student为主表,查看学生的所有科目成绩总分。
SELECT
s.id AS student_id,
s.userName,
SUM(sc.result) AS total_score
FROM
student s
JOIN
score sc ON s.id = sc.studentId
GROUP BY
s.id, s.userName;
3.问题:帮我根据两个DDL创建一个插入信息的存储过程,先插入10条学生表信息,再根据插入的学生表信息插入100条对应studentId的score成绩,
所有信息随机生成,学生表的自增序号从6开始。
DELIMITER //
CREATE PROCEDURE InsertStudentsAndScores()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE j INT DEFAULT 1;
DECLARE random_date DATE;
DECLARE random_name VARCHAR(50);
DECLARE random_phone VARCHAR(20);
DECLARE random_age INT;
DECLARE random_sex ENUM('男', '女');
DECLARE random_introduce TEXT;
DECLARE random_score_name VARCHAR(50);
DECLARE random_result INT;
-- 设置学生表的自增序号从6开始
ALTER TABLE student AUTO_INCREMENT = 6;
-- 插入10条学生表信息
WHILE i <= 10 DO
-- 生成随机数据
SET random_date = DATE_ADD(CURDATE(), INTERVAL FLOOR(RAND() * 365) DAY);
SET random_name = CONCAT('Student', FLOOR(RAND() * 1000));
SET random_phone = CONCAT('1', FLOOR(RAND() * 900000000) + 10000000);
SET random_age = FLOOR(RAND() * 30) + 5; -- 假设年龄范围是5到34岁
SET random_sex = IF(RAND() > 0.5, '男', '女');
SET random_introduce = CONCAT('This is a random introduction for ', random_name);
-- 插入学生信息
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES (random_date, random_name, random_phone, random_age, random_sex, random_introduce);
SET i = i + 1;
END WHILE;
-- 为每个学生插入10条成绩记录
SET j = 1;
WHILE j <= 10 DO
-- 获取学生的id
SET @studentId = (SELECT id FROM student ORDER BY id ASC LIMIT 1 OFFSET (j - 1));
-- 为该学生插入10条成绩记录
WHILE i <= 10 DO
-- 生成随机数据
SET random_score_name = CONCAT('Subject', FLOOR(RAND() * 10));
SET random_result = FLOOR(RAND() * 100); -- 假设成绩范围是0到99
-- 插入成绩信息
INSERT INTO score (scoreName, result, studentId)
VALUES (random_score_name, random_result, @studentId);
SET i = i + 1;
END WHILE;
-- 重置i为1以便下一个学生使用
SET i = 1;
SET j = j + 1;
END WHILE;
END //
DELIMITER ;
CALL InsertStudentsAndScores();
4.问题:帮我创建一个修改score表scoreName的触发器,当修改scoreName的时候触发,判断修改的scoreName是否是数学,如果是数学就改成(天书)。
并且给出测试示例。
DELIMITER //
CREATE TRIGGER tr_score_name_change
BEFORE UPDATE ON score
FOR EACH ROW
BEGIN
IF NEW.scoreName = '数学' THEN
SET NEW.scoreName = '天书';
END IF;
END;
//
DELIMITER ;
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 ('物理', 78, 2);
UPDATE score SET scoreName = '数学' WHERE id = 1; -- 假设第一条记录的id是1
SELECT * FROM score WHERE id = 1;
5.问题:创建一个两个表的综合【游标查询】,要求根据学生姓名进行查询,查询的结果返回两个表的综合信息。
DELIMITER //
CREATE PROCEDURE GetStudentScoresByName(IN p_userName VARCHAR(50))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_studentId INT;
DECLARE v_userName VARCHAR(50);
DECLARE v_age INT;
DECLARE v_sex ENUM('男', '女');
DECLARE v_scoreName VARCHAR(50);
DECLARE v_result INT;
-- 声明游标
DECLARE cur CURSOR FOR
SELECT
s.id AS studentId,
s.userName,
s.age,
s.sex,
sc.scoreName,
sc.result
FROM
student s
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_studentId, v_userName, v_age, v_sex, v_scoreName, v_result;
IF done THEN
LEAVE read_loop;
END IF;
-- 在这里处理每一行的数据,例如输出到控制台或返回给客户端
-- 这里只是一个示例,你可以根据需求进行其他操作
SELECT
v_userName AS '学生姓名',
v_age AS '年龄',
v_sex AS '性别',
v_scoreName AS '成绩名称',
v_result AS '成绩';
END LOOP;
-- 关闭游标
CLOSE cur;
END //
DELIMITER ;
CALL GetStudentScoresByName('张三');