前言
暑假的时候学习过这个 MySql 数据库，然后学校这个学期居然开了这门课程，那就做一个学生成绩管理系统来练一练手吧。在这里做一个简单的练习记录。
需求分析
SQL 编写
新建数据库
为该数据库建表
INSERT INTO 数据
创建索引
创建视图
创建触发器
创建存储过程
SQL 查询进行调试
结语
完整代码如下
-- 1. 新建数据库 drop database studentManage; CREATE DATABASE studentManage; use studentManage; -- 2. 创建表 -- 创建班级表 CREATE TABLE classes ( class_id INT PRIMARY KEY, -- 班级ID，作为主键 class_name VARCHAR(50) -- 班级名称，最大长度为50个字符 ); -- 插入班级数据 INSERT INTO classes (class_id, class_name) VALUES (0, '尖子班'); INSERT INTO classes (class_id, class_name) VALUES (1, '垃圾班'); INSERT INTO classes (class_id, class_name) VALUES (2, '垃圾班'); INSERT INTO classes (class_id, class_name) VALUES (3, '废物班'); INSERT INTO classes (class_id, class_name) VALUES (4, '废物班'); INSERT INTO classes (class_id, class_name) VALUES (5, '脑残班'); -- 创建学生表 CREATE TABLE students ( student_id INT PRIMARY KEY NOT NULL, -- 学生ID，作为主键，不允许为空 name VARCHAR(50), -- 姓名，最大长度为50个字符 class_id INT, -- 班级ID age INT, -- 年龄 FOREIGN KEY (class_id) REFERENCES classes(class_id) -- 外键关联到班级表的班级ID ); -- 插入学生数据 INSERT INTO students (student_id, name, class_id, age) VALUES (1, '王八', 5, 19); INSERT INTO students (student_id, name, class_id, age) VALUES (2, '王一', 1, 20); INSERT INTO students (student_id, name, class_id, age) VALUES (3, '小六', 0, 18); INSERT INTO students (student_id, name, class_id, age) VALUES (4, '王五', 2, 19); INSERT INTO students (student_id, name, class_id, age) VALUES (5, '赵六', 3, 20); INSERT INTO students (student_id, name, class_id, age) VALUES (6, '小七', 4, 18); -- 创建教师表 CREATE TABLE teachers ( teacher_id INT PRIMARY KEY NOT NULL, -- 教师ID，作为主键，不允许为空 teacher_name VARCHAR(50) -- 教师姓名，最大长度为50个字符 ); -- 插入教师数据 INSERT INTO teachers (teacher_id, teacher_name) VALUES (1, '陈老师'); INSERT INTO teachers (teacher_id, teacher_name) VALUES (2, '杨老师'); INSERT INTO teachers (teacher_id, teacher_name) VALUES (4, '何老师'); INSERT INTO teachers (teacher_id, teacher_name) VALUES (4, '刘老师'); INSERT INTO teachers (teacher_id, teacher_name) VALUES (5, '陈老师'); INSERT INTO teachers (teacher_id, teacher_name) VALUES (6, '周老师'); -- 创建课程表 CREATE TABLE courses ( course_id INT PRIMARY KEY NOT NULL, -- 课程ID，作为主键，不允许为空 course_name VARCHAR(50), -- 课程名称，最大长度为50个字符 teacher_id INT, -- 教师ID FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id) -- 外键关联到教师表的教师ID ); -- 插入课程数据 INSERT INTO courses (course_id, course_name, teacher_id) VALUES (100, '英语', 1); INSERT INTO courses (course_id, course_name, teacher_id) VALUES (101, '语文', 2); INSERT INTO courses (course_id, course_name, teacher_id) VALUES (102, '数学', 3); INSERT INTO courses (course_id, course_name, teacher_id) VALUES (103, '生物', 4); INSERT INTO courses (course_id, course_name, teacher_id) VALUES (104, '物理', 5); INSERT INTO courses (course_id, course_name, teacher_id) VALUES (105, '化学', 6); -- 创建成绩表 CREATE TABLE scores ( student_id INT, -- 学生ID course_id INT, -- 课程ID score INT, -- 分数 FOREIGN KEY (student_id) REFERENCES students(student_id), -- 外键关联到学生表的学生ID FOREIGN KEY (course_id) REFERENCES courses(course_id) -- 外键关联到课程表的课程ID ); -- 插入成绩数据 INSERT INTO scores (student_id, course_id, score) VALUES (1, 1, 92); INSERT INTO scores (student_id, course_id, score) VALUES (2, 1, 8); INSERT INTO scores (student_id, course_id, score) VALUES (3, 1, 5); INSERT INTO scores (student_id, course_id, score) VALUES (4, 1, 0); INSERT INTO scores (student_id, course_id, score) VALUES (5, 1, 8); INSERT INTO scores (student_id, course_id, score) VALUES (6, 1, 7); INSERT INTO scores (student_id, course_id, score) VALUES (1, 2, 100); INSERT INTO scores (student_id, course_id, score) VALUES (2, 2, 8); INSERT INTO scores (student_id, course_id, score) VALUES (3, 2, 5); INSERT INTO scores (student_id, course_id, score) VALUES (4, 2, 2); INSERT INTO scores (student_id, course_id, score) VALUES (5, 2, 8); INSERT INTO scores (student_id, course_id, score) VALUES (6, 2, 7); INSERT INTO scores (student_id, course_id, score) VALUES (1, 1, 100); INSERT INTO scores (student_id, course_id, score) VALUES (2, 2, 1); INSERT INTO scores (student_id, course_id, score) VALUES (3, 3, 7); INSERT INTO scores (student_id, course_id, score) VALUES (4, 4, 10); INSERT INTO scores (student_id, course_id, score) VALUES (5, 5, 10); INSERT INTO scores (student_id, course_id, score) VALUES (6, 6, 60); INSERT INTO scores (student_id, course_id, score) VALUES (1, 1, 100); INSERT INTO scores (student_id, course_id, score) VALUES (2, 2, 22); INSERT INTO scores (student_id, course_id, score) VALUES (3, 3, 71); INSERT INTO scores (student_id, course_id, score) VALUES (4, 4, 20); INSERT INTO scores (student_id, course_id, score) VALUES (5, 5, 30); INSERT INTO scores (student_id, course_id, score) VALUES (6, 6, 40); INSERT INTO scores (student_id, course_id, score) VALUES (1, 1, 100); INSERT INTO scores (student_id, course_id, score) VALUES (2, 2, 14); INSERT INTO scores (student_id, course_id, score) VALUES (3, 3, 72); INSERT INTO scores (student_id, course_id, score) VALUES (4, 4, 11); INSERT INTO scores (student_id, course_id, score) VALUES (5, 5, 11); INSERT INTO scores (student_id, course_id, score) VALUES (6, 6, 62); INSERT INTO scores (student_id, course_id, score) VALUES (1, 1, 100); INSERT INTO scores (student_id, course_id, score) VALUES (2, 2, 12); INSERT INTO scores (student_id, course_id, score) VALUES (3, 3, 73); INSERT INTO scores (student_id, course_id, score) VALUES (4, 4, 14); INSERT INTO scores (student_id, course_id, score) VALUES (5, 5, 15); INSERT INTO scores (student_id, course_id, score) VALUES (6, 6, 66); -- 3. 创建索引 -- 为学生表的学生ID创建索引 CREATE INDEX idx_student_id ON students (student_id); -- 为课程表的课程ID创建索引 CREATE INDEX idx_course_id ON courses (course_id); -- 为成绩表的学生ID和课程ID创建联合索引 CREATE INDEX idx_student_course ON scores (student_id, course_id); -- 4. 创建视图 CREATE VIEW class_scores AS SELECT c.class_id, c.class_name, COUNT(s.student_id) AS student_count, AVG(sc.score) AS average_score FROM classes c JOIN students s ON c.class_id = s.class_id JOIN scores sc ON s.student_id = sc.student_id GROUP BY c.class_id, c.class_name; CREATE VIEW teacher_courses AS SELECT t.teacher_id, t.teacher_name, COUNT(c.course_id) AS course_count FROM teachers t JOIN courses c ON t.teacher_id = c.teacher_id GROUP BY t.teacher_id, t.teacher_name; -- 5. 创建触发器 DELIMITER // CREATE TRIGGER check_student_class_id_after_insert AFTER INSERT ON students FOR EACH ROW BEGIN IF NEW.class_id IS NULL OR NEW.class_id = 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '必须为新学生分配班级ID'; END IF; END; // DELIMITER ; DELIMITER // CREATE TRIGGER check_course_name_length_before_update BEFORE UPDATE ON courses FOR EACH ROW BEGIN IF NEW.course_name IS NOT NULL AND LENGTH(NEW.course_name) > 50 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '课程名称长度不能超过50个字符'; END IF; END; // DELIMITER ; DELIMITER // CREATE TRIGGER check_student_scores_after_delete AFTER DELETE ON students FOR EACH ROW BEGIN DECLARE score_count INT; SELECT COUNT(*) INTO score_count FROM scores WHERE student_id = OLD.student_id; IF score_count > 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '不能删除有分数记录的学生'; END IF; END; // DELIMITER ; -- 6. 创建存储函数 -- 存储过程1 - 添加学生 DELIMITER // CREATE PROCEDURE add_student ( IN p_student_id INT, IN p_name VARCHAR(50), IN p_class_id INT, IN p_age INT ) BEGIN INSERT INTO students (student_id, name, class_id, age) VALUES (p_student_id, p_name, p_class_id, p_age); END // DELIMITER ; -- 存储过程2 - 添加课程 DELIMITER // CREATE PROCEDURE add_course ( IN p_course_id INT, IN p_course_name VARCHAR(50), IN p_teacher_id INT ) BEGIN INSERT INTO courses (course_id, course_name, teacher_id) VALUES (p_course_id, p_course_name, p_teacher_id); END // DELIMITER ; -- 存储过程3 - 添加成绩 DELIMITER // CREATE PROCEDURE add_score ( IN p_student_id INT, IN p_course_id INT, IN p_score INT ) BEGIN INSERT INTO scores (student_id, course_id, score) VALUES (p_student_id, p_course_id, p_score); END // DELIMITER ; -- 数据更新 -- 更新学生的姓名 UPDATE students SET name = '新名字' WHERE student_id = 1; -- 更新学生的班级 UPDATE students SET class_id = 1 WHERE student_id = 2; -- 更新学生的年龄 UPDATE students SET age = 21 WHERE student_id = 3; -- 更新课程的名称 UPDATE courses SET course_name = '历史' WHERE course_id = 100; -- 更新课程的教师 UPDATE courses SET teacher_id = 2 WHERE course_id = 101; -- 更新成绩 UPDATE scores SET score = 90 WHERE student_id = 1 AND course_id = 100; -- 查询特定学生的信息： SELECT * FROM students WHERE student_id = 1; -- 查询特定课程的信息： SELECT * FROM courses WHERE course_id = 5; -- 查询特定学生在特定课程上的成绩： SELECT scores.score FROM scores JOIN students ON scores.student_id = students.student_id JOIN courses ON scores.course_id = courses.course_id WHERE students.student_id = 1 AND courses.course_id = 5; -- 查询某个班级的所有学生信息： SELECT * FROM students WHERE class_id = 1; -- 查询某个教师的所有课程信息： SELECT courses.* FROM courses JOIN teachers ON courses.teacher_id = teachers.teacher_id WHERE teachers.teacher_name = '刘老师';
