CREATE DATABASE IF NOT EXISTS student_db ;
-- 1. 清空已有表和对象（避免冲突，首次执行可跳过）
DROP TABLE IF EXISTS score_log;
DROP TABLE IF EXISTS score;
DROP TABLE IF EXISTS course;
DROP TABLE IF EXISTS student;
DROP PROCEDURE IF EXISTS pro_query_student_score;
DROP FUNCTION IF EXISTS func_calculate_valid_credit;
DROP TRIGGER IF EXISTS trig_score_update_log;
-- 2. 创建基础数据表
-- 学生表（存储学生基本信息和总学分）
CREATE TABLE student (
sno CHAR(6) PRIMARY KEY COMMENT '学号（6位）',
sname VARCHAR(20) NOT NULL COMMENT '学生姓名',
totalcredit INT DEFAULT 0 COMMENT '总学分（初始为0）'
) COMMENT '学生信息表';
-- 课程表（存储课程信息和学分）
CREATE TABLE course (
cno CHAR(3) PRIMARY KEY COMMENT '课程号（3位）',
cname VARCHAR(50) NOT NULL COMMENT '课程名称',
credit INT NOT NULL COMMENT '课程学分'
) COMMENT '课程信息表';
-- 成绩表（存储学生-课程-成绩关联关系）
CREATE TABLE score (
sno CHAR(6) COMMENT '学号',
cno CHAR(3) COMMENT '课程号',
grade INT COMMENT '成绩（0-100）',
PRIMARY KEY (sno, cno), -- 复合主键：一个学生一门课只有一个成绩
FOREIGN KEY (sno) REFERENCES student(sno) ON DELETE CASCADE, -- 学生删除时同步删除成绩
FOREIGN KEY (cno) REFERENCES course(cno) ON DELETE CASCADE -- 课程删除时同步删除成绩
) COMMENT '学生成绩表';
-- 成绩操作日志表（记录成绩更新操作）
CREATE TABLE score_log (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '日志ID',
operate_date DATETIME NOT NULL COMMENT '操作时间',
operate_user VARCHAR(50) NOT NULL COMMENT '操作用户（MySQL用户名）',
log_text VARCHAR(200) NOT NULL COMMENT '日志内容（成绩变更信息）'
) COMMENT '成绩更新日志表';
-- 3. 插入测试数据（用于验证功能）
INSERT INTO student (sno, sname) VALUES
('001101', '张三'),
('001102', '林晓'),
('001103', '李四'),
('001104', '林思');
INSERT INTO course (cno, cname, credit) VALUES
('101', '高等数学', 5),
('102', '大学英语', 4),
('208', '数据结构', 4),
('301', '数据库原理', 3);
INSERT INTO score (sno, cno, grade) VALUES
('001101', '101', 88),
('001101', '102', 65),
('001102', '101', 85),
('001102', '102', 78),
('001102', '208', 69),
('001103', '101', 92),
('001104', '301', 75);
-- 4. 创建存储过程：根据姓名模糊查询学生信息及已修课程成绩
DELIMITER //
CREATE PROCEDURE pro_query_student_score(IN v_name VARCHAR(20))
BEGIN
-- 关联3张表，查询姓名含v_name的学生的学号、姓名、课程名、成绩
SELECT
s.sno,
s.sname,
c.cname,
sc.grade,
c.credit
FROM student s
LEFT JOIN score sc ON s.sno = sc.sno
LEFT JOIN course c ON sc.cno = c.cno
WHERE s.sname LIKE CONCAT('%', v_name, '%') -- 模糊匹配姓名
ORDER BY s.sno, c.cno; -- 按学号、课程号排序
END //
DELIMITER ;
-- 5. 创建存储函数：计算指定学生的有效总学分（成绩≥70分的课程学分总和）
DELIMITER //
CREATE FUNCTION func_calculate_valid_credit(v_sno CHAR(6))
RETURNS INT -- 返回有效总学分（整数）
DETERMINISTIC -- 相同输入返回相同输出（确定函数）
READS SQL DATA -- 仅读取数据，不修改数据
BEGIN
DECLARE total_credit INT DEFAULT 0; -- 存储有效总学分
-- 累加成绩≥70分的课程学分
SELECT SUM(c.credit) INTO total_credit
FROM score sc
JOIN course c ON sc.cno = c.cno
WHERE sc.sno = v_sno AND sc.grade >= 70; -- 成绩≥70分才计入
-- 若没有有效学分（如无成绩或成绩均<70），返回0
RETURN IFNULL(total_credit, 0);
END //
DELIMITER ;
-- 6. 创建触发器：成绩表更新时自动记录日志
DELIMITER //
CREATE TRIGGER trig_score_update_log
AFTER UPDATE ON score -- 成绩表更新后触发
FOR EACH ROW -- 行级触发器：每更新一行就触发一次
BEGIN
-- 插入日志：包含操作时间、操作用户、成绩变更内容
INSERT INTO score_log (operate_date, operate_user, log_text)
VALUES (
NOW(), -- 当前时间
USER(), -- 当前MySQL用户名（如root@localhost）
CONCAT(
'学号:', OLD.sno,
',姓名:', (SELECT sname FROM student WHERE sno = OLD.sno),
',课程:', (SELECT cname FROM course WHERE cno = OLD.cno),
',原成绩:', OLD.grade,
',新成绩:', NEW.grade
)
);
END //
DELIMITER ;
最新发布
本文档提供了在错误地执行了DROP USER CASCADE命令后，如何保护数据库现场并避免进一步损失的具体步骤。强调了禁止对受影响数据库进行任何写入操作的重要性。
扫一扫
1533
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
