MySQL学习创建的数据库-校园管理库

[TOC]

## 1. 校园管理库
一个以学生管理系统为背景设计的数据库，数据库主要有`学生表`，`教师表`，`课程配置类型表`，`学生成绩单表`，`班级表`，`学生班级关联表`，`教师班级关联表`

__创建库语句：__`study`
```SQL
CREATE DATABASE `study` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
```

### 1.1. 学生表

表名：`my_student`
表字段：主键id、生日、名字、性别、身高、年龄、民族、密码

#### 1.1.1 表结构
__创建语句：__
```SQL
 CREATE TABLE `my_student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `birthday` date DEFAULT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `gender` varchar(2) CHARACTER SET utf8mb3 DEFAULT NULL,
  `height` decimal(18,2) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `nation` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `password` varchar(50) NOT NULL DEFAULT ('123456'),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
```
> 主键自增初始化设置为从2开始

__表字段可视：__

| Field    | Type          | Null | Key | Default | Extra          |
|----------|---------------|------|-----|---------|----------------|
| id       | int           | NO   | PRI | NULL    | auto_increment |
| birthday | date          | YES  |     | NULL    |                |
| name     | varchar(20)   | NO   |     | NULL    |                |
| gender   | varchar(2)    | YES  |     | NULL    |                |
| height   | decimal(18,2) | YES  |     | NULL    |                |
| age      | int           | YES  |     | NULL    |                |
| nation   | varchar(50)   | YES  |     | NULL    |    &nbsp;      |
| password | varchar(50)   | NO   |     | `_gbk\'123456\'` | DEFAULT_GENERATED |

#### 1.1.2 表数据
__创建语句：__

```SQL
insert into my_student (birthday,name,gender,height,age,nation) values('1989-10-22','刘备','男',1.72,58,'汉'),
('2021-11-12','刘禅','男',1.82,16,'汉'),('1990-11-25','关羽','男',1.85,52,'汉'),('1992-09-08','张飞','男',1.82,50,'汉'),('1993-10-22','甄宓','女',1.62,38,'汉'),('1991-11-12','小乔','女',1.56,26,'汉'),
('1990-01-25','大乔','女',1.65,27,'汉'),('1988-09-08','蔡文姬','女',1.82,30,'汉');
```

__表数据可视：__

| id | birthday | name | gender | height | age  | nation |
|----|----------|------|--------|--------|------|--------|
|  2 | 1989-10-22 | 刘备 | 男     |   1.72 |   58 | 汉     |
|  3 | 2021-11-12 | 刘禅 | 男     |   1.82 |   16 | 汉     |
|  4 | 1990-11-25 | 关羽 | 男     |   1.85 |   52 | 汉     |
|  5 | 1992-09-08 | 张飞 | 男     |   1.82 |   50 | 汉     |

### 1.2. 教师表

表名：`my_teacher`
表字段：主键id、名字、性别、科目【subject】、年龄、密码

#### 1.2.1 表结构
__创建语句：__
```SQL
 CREATE TABLE `my_teacher` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `gender` varchar(2) CHARACTER SET utf8mb3 DEFAULT NULL,
  `subject` int NOT NULL DEFAULT 0,
  `age` int DEFAULT NULL,
  `password` varchar(50) NOT NULL DEFAULT ('123456'),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
```
> 主键自增初始化设置为从2开始

__表字段可视：__

| Field   | Type        | Null | Key | Default | Extra          |
|---------|-------------|------|-----|---------|----------------|
| id      | int         | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | NO   |     | NULL    |                |
| gender  | varchar(2)  | YES  |     | NULL    |                |
| subject | int         | NO   |     | 0       |                |
| age     | int         | YES  |     | NULL    |      &nbsp;    |
| password | varchar(50)   | NO   |     | `_gbk\'123456\'` | DEFAULT_GENERATED |

#### 1.2.2 表数据

__创建语句：__

```SQL
insert into my_teacher (name,gender,subject,age) values('曹操','男',1,58),
('曹仁','男',2,56),
('曹洪','男',3,49),
('曹真','男',4,33);
```

__表数据可视：__

| id | name | gender | subject | age  |
|----|------|--------|---------|------|
|  2 | 曹操 | 男     |       1 |   58 |
|  3 | 曹仁 | 男     |       2 |   56 |
|  4 | 曹洪 | 男     |       3 |   49 |
|  5 | 曹真 | 男     |       4 |   33 |

### 1.3. 科目配置表

表名：`my_subjectSet`
表字段：主键id、名字

#### 1.3.1 表结构
__创建语句：__
```SQL
 CREATE TABLE `my_subjectSet` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
```
__表字段可视：__

#### 1.3.2 表数据

__创建语句：__

```SQL
insert into my_subjectSet (name) values('语文'),('数学'),('英语'),('体育'),('地理'),('物理'),('化学'),('生物'),('历史'),('政治'),('音乐');
```

__表数据可视：__

| id | name | id | name | id | name |
|----|------|----|------|----|------|
|  1 | 语文 |  5 | 地理 |  9 | 历史 |
|  2 | 数学 |  6 | 物理 | 10 | 政治 |
|  3 | 英语 |  7 | 化学 | 11 | 音乐 |
|  4 | 体育 |  8 | 生物 ||&nbsp;|

### 1.4. 班级表

表名：`my_classs`
表字段：主键id、名称、班级级别、创建日期、结束日期、结束原因

#### 1.4.1 表结构
__创建语句：__
```SQL
CREATE TABLE `my_classs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL comment '名称',
  `level` int NOT NULL DEFAULT 0 comment '班级级别',
  `createTime` date NOT NULL DEFAULT (CURRENT_DATE) comment '创建日期',
  `endTime` date NULL comment '结束日期',
  `endReason` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL comment '结束原因',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
```
__表字段可视：__

| Field      | Type          | Null | Key | Default   | Extra             |
|------------|---------------|------|-----|-----------|-------------------|
| id         | int           | NO   | PRI | NULL      | auto_increment    |
| name       | varchar(50)   | NO   |     | NULL      |                   |
| level      | int           | NO   |     | 0         |                   |
| createTime | date          | NO   |     | curdate() | DEFAULT_GENERATED |
| endTime    | date          | YES  |     | NULL      |                   |
| endReason  | varchar(2000) | YES  |     | NULL      |     &nbsp;        |

__班级表level【班级级别】字段可选项：__

|级别名称|值|级别名称|值|级别名称|值|
|--|--|--|--|--|--|
|小一|1|初一|7|高一|10|
|小二|2|初二|8|高二|11|
|小三|3|初三|9|高三|12|
|小四|4|||||
|小五|5|||||
|小六|6||||&nbsp;|

#### 1.4.2 表数据
__创建语句：__

```SQL
insert into my_classs (name,level) values('1班',7),('2班',7),('3班',7),('1班',10),('2班',10),('3班',10);
```
> 展示班级名称规则：level对应的名称 + name，例如（初一3班）

__表数据可视：__

| id | name | level | createTime | endTime | endReason |
|----|------|-------|------------|---------|-----------|
|  1 | 1班  |     7 | 2023-03-03 | NULL    | NULL      |
|  2 | 2班  |     7 | 2023-03-03 | NULL    | NULL      |
|  3 | 3班  |     7 | 2023-03-03 | NULL    | NULL      |
|  4 | 1班  |    10 | 2023-03-03 | NULL    | NULL      |
|  5 | 2班  |    10 | 2023-03-03 | NULL    | NULL      |
|  6 | 3班  |    10 | 2023-03-03 | NULL    | NULL      |

### 1.5. 教师班级关联表

表名：`my_teacherClasssRelation`
表字段：主键id、教师id、班级id、班级级别、开始时间、结束时间、结束原因

#### 1.5.1 表结构
__创建语句：__
```SQL
CREATE TABLE `my_teacherClasssRelation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `teacher` int NOT NULL DEFAULT 0 comment '教师id',
  `classs` int NOT NULL DEFAULT 0 comment '班级id',
  `level` int NOT NULL DEFAULT 0 comment '班级级别',
  `createTime` date NOT NULL DEFAULT (CURRENT_DATE) comment '创建日期',
  `endTime` date NULL comment '结束日期',
  `endReason` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL comment '结束原因',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
```
__表字段可视：__

| Field      | Type          | Null | Key | Default   | Extra             |
|------------|---------------|------|-----|-----------|-------------------|
| id         | int           | NO   | PRI | NULL      | auto_increment    |
| teacher    | int           | NO   |     | 0         |                   |
| classs     | int           | NO   |     | 0         |                   |
| level      | int           | NO   |     | 0         |                   |
| createTime | date          | NO   |     | curdate() | DEFAULT_GENERATED |
| endTime    | date          | YES  |     | NULL      |                   |
| endReason  | varchar(2000) | YES  |     | NULL      |     &nbsp;        |

#### 1.5.2 表数据
__创建语句：__
```SQL
-- 将教师分配到高一的每个班，每个老师叫整个级别
insert into my_teacherClasssRelation (teacher,classs,level)
select my_teacher.id, my_classs.id, my_classs.level
from my_teacher,my_classs where my_classs.level = 7;
```
__表数据可视：__

| id | teacher | classs | level | createTime | endTime | endReason |
|----|---------|--------|-------|------------|---------|-----------|
|  1 |       2 |      3 |     7 | 2023-03-03 | NULL    | NULL      |
|  2 |       2 |      2 |     7 | 2023-03-03 | NULL    | NULL      |
|  3 |       2 |      1 |     7 | 2023-03-03 | NULL    | NULL      |
|  4 |       3 |      3 |     7 | 2023-03-03 | NULL    | NULL      |
|  5 |       3 |      2 |     7 | 2023-03-03 | NULL    | NULL      |
|  6 |       3 |      1 |     7 | 2023-03-03 | NULL    | NULL      |
|  7 |       4 |      3 |     7 | 2023-03-03 | NULL    | NULL      |
|  8 |       4 |      2 |     7 | 2023-03-03 | NULL    | NULL      |
|  9 |       4 |      1 |     7 | 2023-03-03 | NULL    | NULL      |
| 10 |       5 |      3 |     7 | 2023-03-03 | NULL    | NULL      |
| 11 |       5 |      2 |     7 | 2023-03-03 | NULL    | NULL      |
| 12 |       5 |      1 |     7 | 2023-03-03 | NULL    | NULL      |

> 教师表【my_teacher】数据和班级表【my_classs】中满足条件的数据的笛卡尔积

### 1.6. 学生班级关联表

表名：`my_studentClasssRelation`
表字段：主键id、学生id、班级id、班级级别、开始时间、结束时间、结束原因

#### 1.6.1 表结构
__创建语句：__
```SQL
CREATE TABLE `my_studentClasssRelation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student` int NOT NULL DEFAULT 0 comment '学生id',
  `classs` int NOT NULL DEFAULT 0 comment '班级id',
  `level` int NOT NULL DEFAULT 0 comment '班级级别',
  `createTime` date NOT NULL DEFAULT (CURRENT_DATE) comment '创建日期',
  `endTime` date NULL comment '结束日期',
  `endReason` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL comment '结束原因',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
```
__表字段可视：__

| Field      | Type          | Null | Key | Default   | Extra             |
|------------|---------------|------|-----|-----------|-------------------|
| id         | int           | NO   | PRI | NULL      | auto_increment    |
| student    | int           | NO   |     | 0         |                   |
| classs     | int           | NO   |     | 0         |                   |
| level      | int           | NO   |     | 0         |                   |
| createTime | date          | NO   |     | curdate() | DEFAULT_GENERATED |
| endTime    | date          | YES  |     | NULL      |                   |
| endReason  | varchar(2000) | YES  |     | NULL      |     &nbsp;        |

#### 1.6.2 表数据
__创建语句：__
```SQL
-- 将学生id和班级id相同记录更新到学生班级关联表中
insert into my_studentClasssRelation (student,classs,level)
select my_student.id, my_classs.id, my_classs.level
from my_student,my_classs where my_student.id = my_classs.id;
```
__表数据可视：__

| id | student | classs | level | createTime | endTime | endReason |
|----|---------|--------|-------|------------|---------|-----------|
|  1 |       2 |      2 |     7 | 2023-03-03 | NULL    | NULL      |
|  2 |       3 |      3 |     7 | 2023-03-03 | NULL    | NULL      |
|  3 |       4 |      4 |    10 | 2023-03-03 | NULL    | NULL      |
|  4 |       5 |      5 |    10 | 2023-03-03 | NULL    | NULL      |

### 1.7. 学生成绩表

表名：`my_schoolReport`
表字段：主键id、学生id、课程id、班级id、班级级别、成绩日期、成绩分数、名称

#### 1.7.1 表结构
__创建语句：__
```SQL
CREATE TABLE `my_schoolReport` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student` int NOT NULL DEFAULT 0 comment '学生id',
  `subject` int NOT NULL DEFAULT 0 comment '课程id',
  `classs` int NOT NULL DEFAULT 0 comment '班级id',
  `level` int NOT NULL DEFAULT 0 comment '班级级别',
  `reportTime` date NOT NULL DEFAULT (CURRENT_DATE) comment '成绩日期',
  `score` decimal(18,2) NOT NULL DEFAULT 0 comment '成绩分数',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL comment '名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
```
__表字段可视：__

| Field      | Type          | Null | Key | Default | Extra            |
|------------|---------------|------|-----|---------|------------------|
| id         | int           | NO   | PRI | NULL    | auto_increment   |
| student    | int           | NO   |     | 0       |                  |
| subject    | int           | NO   |     | 0       |                  |
| classs     | int           | NO   |     | 0       |                  |
| level      | int           | NO   |     | 0       |                  |
| reportTime | date          | NO   |     |curdate()|DEFAULT_GENERATED |
| score      | decimal(18,2) | NO   |     | 0.00    |                  |
| name       | varchar(20)   | NO   |     | NULL    |      &nbsp;      |

#### 1.7.2 表数据

__创建语句：__

```SQL
-- 将每个学生的语文成绩录入到成绩单中，分数是个随机数
insert into my_schoolReport (student,subject,classs,level,score,name)
select s.id,1, r.classs, r.level,TRUNCATE(RAND() * 100,2),'期中考试'
from my_student s,my_studentClasssRelation r where s.id = r.student and r.endTime is null;
```

__表数据可视：__

| id | student | subject | classs | level | reportTime | score | name     |
|----|---------|---------|--------|-------|------------|-------|----------|
|  1 |       2 |       1 |      2 |     7 | 2023-03-03 | 75.98 | 期中考试 |
|  2 |       3 |       1 |      3 |     7 | 2023-03-03 | 85.99 | 期中考试 |
|  3 |       4 |       1 |      4 |    10 | 2023-03-03 |  1.99 | 期中考试 |
|  4 |       5 |       1 |      5 |    10 | 2023-03-03 | 51.99 | 期中考试 |