MySql-学天生绩管理系统 - ToB企服应用市场:ToB评测及商务社交产业平台

-- 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 = '刘老师';

复制代码