MySQL数据库基础练习系列——教务管理系统-CSDN博客

本文链接：https://blog.csdn.net/2401_83963238/article/details/139963335

一、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);

三、简单查询和复杂查询

-- 简单查询：查询所有用户的信息，仅显示用户的姓名，性别和手机号，用中文显示列名
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 和成绩。
*/