【数据库设计】学生学籍信息和学期学年课程成绩信息表以及插入数据的python脚本生成设计

参考登陆用户信息设计

create table stu_users(stu_user_id BIGINT not null,\
stu_password varchar(32) COLLATE utf8_unicode_ci\
 DEFAULT NULL,last_login timestamp NOT NULL DEFAULT\
  current_timestamp() ON UPDATE current_timestamp(),\
  PRIMARY KEY (stu_user_id))DEFAULT CHARSET=utf8 \
  COLLATE=utf8_unicode_ci;

create table adm_users(adm_user_id int(6) not null,\
adm_password varchar(32) COLLATE utf8_unicode_ci DEFAULT\
 NULL,last_login timestamp NOT NULL DEFAULT \
 current_timestamp() ON UPDATE current_timestamp(),\
 PRIMARY KEY(adm_user_id))DEFAULT CHARSET=utf8\
  COLLATE=utf8_unicode_ci;

学籍信息设计

MySQL外键约束（FOREIGN KEY）

枚举几个我校的专业数据：

enum('土木工程','车辆工程','自动化','电子信息工程','计算机科学与技术','网络空间安全','材料科学与工程','电气工程及其自动化','数学与应用数学','工商管理')

枚举几个我校学院数据：

enum('海洋工程学院','汽车工程学院','信息科学与工程学院','计算机科学与技术学院','材料科学与工程学院','新能源学院','理学院','经济管理学院')

因此创建专业信息表

create table master_school(master enum('土木工程','车辆工程','自动化','电子信息工程','计算机科学与技术','网络空间安全','材料科学与工程','电气工程及其自动化','数学与应用数学','工商管理'),school enum('海洋工程学院','汽车工程学院','信息科学与工程学院','计算机科学与技术学院','材料科学与工程学院','理学院','经济管理学院'),PRIMARY KEY(master))DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

因为学生有学号，对应有班级，建立院系专业对应的班级表
根据如下学号信息建表：

create table class_info(class int(7),master enum('土木工程','车辆工程','自动化','电子信息工程','计算机科学与技术','网络空间安全','材料科学与工程','电气工程及其自动化','数学与应用数学','工商管理'),dept enum('海洋工程学院','汽车工程学院','信息科学与工程学院','计算机科学与技术学院','材料科学与工程学院','新能源学院','理学院','经济管理学院'),PRIMARY KEY(class),FOREIGN KEY(master) REFERENCES master_school(master))DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

学生信息表

create table stu_info(stu_id BIGINT not null, stu_name varchar(7) COLLATE utf8_unicode_ci DEFAULT NULL, stu_sex enum('男','女') COLLATE utf8_unicode_ci DEFAULT NULL, stu_year int(4) COLLATE utf8_unicode_ci DEFAULT NULL,stu_class int(7) COLLATE utf8_unicode_ci DEFAULT NULL,stu_master enum('土木工程','车辆工程','自动化','电子信息工程','计算机科学与技术','网络空间安全','材料科学与工程','电气工程及其自动化','数学与应用数学','工商管理') COLLATE utf8_unicode_ci DEFAULT NULL,stu_dept enum('海洋工程学院','汽车工程学院','信息科学与工程学院','计算机科学与技术学院','材料科学与工程学院','新能源学院','理学院','经济管理学院') COLLATE utf8_unicode_ci DEFAULT NULL,PRIMARY KEY (stu_id),FOREIGN KEY(stu_id) REFERENCES stu_users(stu_user_id),FOREIGN KEY(stu_class) REFERENCES class_info(class),FOREIGN KEY(stu_master) REFERENCES mater_school(master))DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

drop table stu_info;

课程成绩信息设计

MySQL 中的 CHECK 约束不起作用

MySQL常见建表选项及约束

鉴于mysql版本为5.5，不支持CHECK，采用触发器约束insert和update中关于learn_seme和learn_year的新值。
或者使用SET/ENUM进行约束。

鉴于课程需要，需要设计课程表：
课程号，课程名，开设学期，开设学年，开设学院

create table courses(cour_id varchar(7),cour_name varchar(30),learn_seme enum('春季','秋季') COLLATE utf8_unicode_ci DEFAULT NULL,learn_year enum('第一学年','第二学年','第三学年','第四学年') COLLATE utf8_unicode_ci DEFAULT NULL,dept enum('校部机关','思想政治理论教学部','体育教学部','海洋工程学院','汽车工程学院','信息科学与工程学院','计算机科学与技术学院','材料科学与工程学院','新能源学院','理学院','经济管理学院') COLLATE utf8_unicode_ci DEFAULT NULL,PRIMARY KEY(cour_id))DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

create table stu_year_grades(stu_id BIGINT not null, stu_name varchar(7) COLLATE utf8_unicode_ci DEFAULT NULL,cour_id varchar(7),cour_name varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,learn_seme enum('春季','秋季') COLLATE utf8_unicode_ci DEFAULT NULL,learn_year enum('第一学年','第二学年','第三学年','第四学年') COLLATE utf8_unicode_ci DEFAULT NULL,grades int(3),FOREIGN KEY(stu_id) REFERENCES stu_users(stu_user_id),FOREIGN KEY(cour_id) REFERENCES courses(cour_id))DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

其他表的补充

专业-学院对照表

CREATE TABLE `master_school` (
  `master` enum('土木工程','车辆工程','自动化','电子信息工程','计算机科学与技术','网络空间安全','材料科学与工程','电气工程及其自动化','数学与应用数学','工商管理') COLLATE utf8_unicode_ci NOT NULL,
  `school` enum('海洋工程学院','汽车工程学院','信息科学与工程学院','计算机科学与技术学院','材料科学与工程学院','新能源学院','理学院','经济管理学院') COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`master`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

班级信息表

CREATE TABLE `class_info` (
  `class` int(7) NOT NULL,
  `master` enum('土木工程','车辆工程','自动化','电子信息工程','计算机科学与技术','网络空间安全','材料科学与工程','电气工程及其自动化','数学与应用数学','工商管理') COLLATE utf8_unicode_ci DEFAULT NULL,
  `dept` enum('海洋工程学院','汽车工程学院','信息科学与工程学院','计算机科学与技术学院','材料科学与工程学院','新能源学院','理学院','经济管理学院') COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`class`),
  KEY `master` (`master`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

课程表

CREATE TABLE `courses` (
  `cour_id` varchar(7) COLLATE utf8_unicode_ci NOT NULL,
  `cour_name` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `learn_seme` enum('春季','秋季') COLLATE utf8_unicode_ci DEFAULT NULL,
  `learn_year` enum('第一学年','第二学年','第三学年','第四学年') COLLATE utf8_unicode_ci DEFAULT NULL,
  `dept` enum('校部机关','体育教学部','思想政治理论教学部','海洋工程学院','汽车工程学院','信息科学与工程学院','计算机科学与技术学院','材料科学与工程学院','新能源学院','理学院','语言文学学院','经济管理学院') COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`cour_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

插入数据脚本

因为外键依赖，我的学生信息和课程成绩的用户名都是依赖于stu_users表中的stu_user_id，将10个user_id以及数个课程信息，按python列表存储，遍历生成插入数据：

import random
stu_ids=["(2190000000, '某某木',","(219000011,'材联动',","(2190000102, '陈处理时',",\
"(2190400103, '李好看',","(180400101, '大法师',","(180400104, '费腾', ",\
"(170400105, '德华从',","(170400106, '发如雪',","(160400107, '欧格斯',",\
"(160400108, '防晒剂',"]
courses_ids=["'AD15001','军训及军事理论','秋季','第一学年',",\
"'CS14001','大学计算机','秋季','第一学年',",\
"'MA21001','微积分','秋季','第一学年',",\
"'LL12002','通用英语A','春季','第一学年',",\
"'PH21001','大学物理A','春季','第一学年',",\
"'DP11023','马克思主义基本原理概论','秋季','第二学年',",\
"'PE13003','体育','秋季','第二学年',",\
"'DP11024','毛泽东思想和中国特色社会主义理论体系概论','春季','第二学年',",\
"'CS33310','编译原理','秋季','第三学年',",\
"'CS33316','信息内容安全','春季','第三学年',",\
"'CS34307','毕业实习','秋季','第四学年',",\
"'DP11028','形势与政策','秋季','第四学年',",\
"'CS34308','毕业设计','春季','第四学年',"]
file=open("C:/Users/Lenovo/Desktop/软设Ⅱ/courses.txt",mode='w',encoding='utf-8')
for a in stu_ids:
	for b in courses_ids:
				s="INSERT INTO `stu_year_grades` VALUES "+str(a)+str(b)+str(random.randint(70,100))+');\n'
				file.write(s)

然后根据学生大致学年，删除那些因为遍历而实际上不会上的课程，最终效果如下：

错误提示

学院与专业对应信息中，学院不能作为KEY，专业可以作为KEY，因为一个专业只能对应一个学院，但是一个学院可以对应多个专业。