MySQL数据库基础练习系列—

项目名称与项目简介

教务管理系统是一个旨在帮助学校或教育机构管理教务活动的软件系统。它涵盖了学生信息管理、教师信息管理、课程管理、成绩管理以及相关的报表生成等功能。通过该系统，学校可以更加高效地处理教务数据，提升教学质量和管理水平。

1.新建ER图表和数据库模型图。

Chen's 数据库表示法——ER图绘制使用形

Crow's Foot数据库表示法——数据库模型图使用形状

2.DDL和DML语句

DDL语句

CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    gender ENUM('男', '女') NOT NULL COMMENT '性别',
    email VARCHAR(100) UNIQUE COMMENT '邮箱',
		phone VARCHAR(20) NOT NULL COMMENT '手机号',
		address VARCHAR(200) NOT NULL COMMENT '地址'
);
 
CREATE TABLE Roles (
    role_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '角色ID',
    role_name VARCHAR(50) NOT NULL UNIQUE COMMENT '角色名称'
);
 
CREATE TABLE `userroles` (
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `role_id` int(11) NOT NULL COMMENT '角色ID',
  `userroles_id` int(8) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`userroles_id`) USING BTREE,
  KEY `role_id` (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
 
CREATE TABLE Courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '课程ID',
    course_name VARCHAR(100) NOT NULL UNIQUE COMMENT '课程名称',
    course_code VARCHAR(50) NOT NULL UNIQUE COMMENT '课程代码',
		teacher_id INT NOT NULL UNIQUE COMMENT '教师ID',
		credit DECIMAL(3,1) NOT NULL COMMENT '学分',
    description TEXT COMMENT '课程描述'
);
 
CREATE TABLE Teachers (
    teacher_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '教师ID',
    user_id INT NOT NULL COMMENT '关联的用户ID',
    subject VARCHAR(100) NOT NULL COMMENT '教学科目',
    qualification VARCHAR(255) COMMENT '教师资质',
		teacherPhone VARCHAR(20) NOT NULL COMMENT '教师手机号',
		gender ENUM('男', '女') NOT NULL COMMENT '性别',
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
 
CREATE TABLE Students (
    student_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '学生ID',
    user_id INT NOT NULL COMMENT '关联的用户ID',
    class VARCHAR(50) NOT NULL COMMENT '班级',
    year_of_entry YEAR NOT NULL COMMENT '入学年份',
		gender ENUM('男', '女') NOT NULL COMMENT '性别',
		phone VARCHAR(20) NOT NULL COMMENT '手机号',
		address VARCHAR(200) NOT NULL COMMENT '地址',
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
 
CREATE TABLE Grades (
    grade_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '成绩ID',
    student_id INT NOT NULL COMMENT '学生ID',
    course_id INT NOT NULL COMMENT '课程ID',
    grade DECIMAL(5, 2) NOT NULL COMMENT '成绩',
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

DML语句

-- Users 表
INSERT INTO Users (username, password, gender, email, phone, address)
VALUES
    ('甄嬛', '123', '女', 'zhenhuan@example.com', '1234567890', '河北'),
    ('雍正', '456', '男', 'yongzheng@example.com', '0987654321', '山东'),
    ('苏培盛', '789', '男', 'supershy@example.com', '1122334455', '河北'),
    ('年世兰', '121', '女', 'nianshilan@example.com', '5544332211', '四川'),
    ('允礼', '113', '男', 'yunli@example.com', '6677889900', '上海'),
    ('沈眉庄', '125', '女', 'shenmeizhuang@example.com', '0099887766', '贵州'),
    ('安陵容', '486', '女', 'anlingrong@example.com', '1357924680', '山东'),
    ('乌兰那拉·宜修', '女', '女', 'yixiu@example.com', '0246813579', '重庆'),
    ('温实初', '415', '男', 'win10chu@example.com', '9876543210', '河北'),
    ('浣碧', '785', '女', 'huanbi@example.com', '0123456789', '河北');
 
-- Roles 表
INSERT INTO roles VALUES(0,'学生1');
INSERT INTO roles VALUES(0,'教师2');
INSERT INTO roles VALUES(0,'管理员3');
 
-- UserRoles 表
INSERT INTO UserRoles (user_id, role_id) VALUES
(1, 1), -- 管理员
(2, 2), -- 教师
(3, 3); -- 学生
 
-- Courses 表
INSERT INTO Courses (course_name, course_code, teacher_id, credit, description)
VALUES
    ('数学', 'MAT101', 1, 2.5, '基础数学课程'),
    ('英语', 'ENG101', 2, 3.0, '基础英语课程'),
    ('物理', 'PHY101', 3, 4.0, '大学物理'),
    ('化学', 'CHE101', 4, 2.0, '普通化学'),
    ('计算机科学', 'CS101', 5, 4.5, '计算机入门'),
    ('生物学', 'BIO101', 6, 1.0, '生物学基础'),
    ('历史', 'HIS101', 7, 1.5, '世界历史'),
    ('经济学', 'ECO101', 8, 3.5, '微观经济学'),
    ('艺术史', 'ART101', 9, 5.0, '艺术发展历程'),
    ('心理学', 'PSY101', 10, 5.5, '心理学导论');
 
-- Teachers 表
INSERT INTO Teachers (user_id, subject, qualification, teacherPhone, gender)
VALUES
    (1, '数学', '博士', '1111111111', '男'),
    (2, '英语', '硕士', '2222222222', '女'),
    (3, '物理', '教授', '3333333333', '男'),
    (4, '化学', '副教授', '4444444444', '女'),
    (5, '计算机科学', '专家', '5555555555', '男'),
    (6, '生物学', '博士', '6666666666', '女'),
    (7, '历史', '教授', '7777777777', '男'),
    (8, '经济学', '硕士', '8888888888', '女'),
    (9, '艺术史', '专家', '9999999999', '男'),
    (10, '心理学', '博士', '0000000000', '女');
 
-- Students 表
INSERT INTO Students (user_id, class, year_of_entry, gender, phone, address)
VALUES
    (1, '一班', 2023, '男', '1122334455', 'address11'),
    (2, '二班', 2022, '女', '2233445566', 'address12'),
    (3, '三班', 2021, '男', '3344556677', 'address13'),
    (4, '四班', 2020, '女', '4455667788', 'address14'),
    (5, '五班', 2019, '男', '5566778899', 'address15'),
    (6, '六班', 2018, '女', '6677889900', 'address16'),
    (7, '七班', 2017, '男', '7788990011', 'address17'),
    (8, '八班', 2016, '女', '8899001122', 'address18'),
    (9, '九班', 2015, '男', '9900112233', 'address19'),
    (10, '十班', 2014, '女', '0011223344', 'address20');
 
-- Grades 表
INSERT INTO Grades (student_id, course_id, grade) VALUES
(1, 1, 90.00),
(1, 2, 85.00),
(2, 3, 88.00),
(2, 4, 87.00),
(7, 8, 99.00),
(8, 9, 88.00),
(9, 10,85.00),
(4, 6, 75.00),
(5, 2, 100.00),
(6, 9, 99.00);

3.简单查询与多表联合复杂查询。

-- 简单查询：查询所有用户的信息，仅显示用户的姓名，性别和手机号，用中文显示列名
SELECT username '用户姓名', gender '性别', phone '手机号' FROM users;
 
 
-- 复杂查询：查询2020 年入学的学生的成绩信息
SELECT s.student_id '学生id', s.class '班级', g.course_id '课程id', g.grade '成绩'
FROM Students s
JOIN Grades g ON s.student_id = g.student_id
WHERE s.year_of_entry = 2020;  -- 使用 WHERE 子句筛选出入学年份为 2020 年的学生
/*
上述查询首先使用 JOIN 操作将 Students 表和 Grades 表基于 student_id 进行关联。
然后通过 WHERE 子句指定条件，只选取入学年份为 2020 年的学生的相关记录。
最后选择要显示的列，包括学生 ID、所在班级、课程 ID 和成绩。
*/

运行结果

4.触发器

触发器1：当在Students表中插入新记录时，在Grades表中为新学生添加默认课程的成绩。

DELIMITER //
CREATE TRIGGER after_student_insert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
    INSERT INTO Grades (student_id, course_id, grade)
    VALUES (NEW.student_id, 1, 0.00);
END;
//
DELIMITER ;

测试语句1：在学生表中插入新学生时，自动在Grades表中为这些学生添加默认的course_id为1的课程的成绩（假设为0）。

-- 测试语句
INSERT INTO Students (user_id, class, year_of_entry, gender, phone, address)
VALUES (11, '十一班', 2024, '男', '12345678901', '测试地址');


-- 检查是否成功在Grades表中插入了对应学生的记录
SELECT * FROM Grades WHERE student_id = 11;

测试结果1：

触发器2：当尝试更新Grades表中的成绩时，如果成绩超过100或小于0，则阻止更新。

DELIMITER //
CREATE TRIGGER before_grade_update
BEFORE UPDATE ON Grades
FOR EACH ROW
BEGIN
    IF NEW.grade > 100 OR NEW.grade < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '成绩必须在0到100之间！';
    END IF;
END;
//
DELIMITER ;

测试语句2：当修改学生成绩时，如果成绩超过100或小于0，则阻止更新

-- 测试语句（尝试将成绩更新为101，应该失败）
UPDATE Grades SET grade = 101 WHERE student_id = 1 AND course_id = 1;

测试结果2

触发器3：确保插入Users表的用户具有唯一的用户名和邮箱

DELIMITER //
CREATE TRIGGER trg_check_user_unique
BEFORE INSERT ON Users
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM Users WHERE username = NEW.username OR email = NEW.email) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Username or email already exists';
    END IF;
END //
DELIMITER ;

测试语句3：尝试插入一个已存在的用户名或邮箱应该会导致错误。

INSERT INTO Users (username, password, gender, email, phone, address)
VALUES ('甄嬛', 'new_password', '女', 'zhenhuan@example.com', '12345678901', '新地址');  -- 这会失败，因为用户名已存在

测试结果3：

5.存储过程它用于向`Grades`表中插入一条新的成绩记录

DELIMITER //
CREATE PROCEDURE InsertGrade(
    IN p_student_id INT,
    IN p_course_id INT,
    IN p_grade DECIMAL(5, 2)
)
BEGIN
    -- 检查参数是否有效
    IF p_student_id IS NULL OR p_course_id IS NULL OR p_grade IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid input. All parameters must be provided.';
    END IF;

    -- 插入成绩
    INSERT INTO Grades (student_id, course_id, grade)
    VALUES (p_student_id, p_course_id, p_grade);

    -- 如果需要，可以在这里添加其他逻辑，比如检查是否已经存在相同的成绩记录等

    -- 返回结果
    SELECT 'Grade inserted successfully.' AS message;
END //
DELIMITER ;

测试语句：插入操作使用INSERT INTO ... VALUES语句。插入后，可以选择性地添加其他逻辑，比如检查是否已经存在相同的成绩记录。最后，它返回一个表示成功的消息。


-- 调用存储过程插入一个新的成绩记录
CALL InsertGrade(11, 1, 95.00);  -- 假设有一个学生ID为11的学生和课程ID为1的课程


-- 查询Grades表以验证是否插入成功
SELECT * FROM Grades WHERE student_id = 11 AND course_id = 1;

测试结果