python-mysql期末实验：二、基于MySQL进行的数据库的搭建

本专栏目录：

一、实验综述

二、基于MySQL进行的数据库的搭建（即本篇）

三、程序登录、注册界面的制作

四、数据导入界面的制作

五、collage（大学）表的增删改查界面的制作

六、province（省份）表的增删改查界面的制作

七、student_num（考生人数）表的增删改查界面以及数据可视化的制作

八、min_score_line（最低分数线）表的增删改查界面以及数据可视化的制作

九、collage_level（学校等级）表的增删改查界面的制作

十、enrollment_type（招生类型）表的增删改查界面的制作

十一、enrollment（招生人数）表的增删改查界面以及数据可视化的制作

十二、school_type（学校类型）表的增删改查界面的制作

十三、collage_school_level（各高校等级）表的增删改查界面的制作

十四、collage_province_enrollment_type（各高校招生类型）表的增删改查界面的制作

如果需要整个代码文件，以及数据库中的数据，或者有较为复杂的问题，请添加QQ：3095631599（CSDN-山河之书），添加时请备注：CSDN。

本部分将叙述本程序的底层数据库的搭建过程，包含但不限于数据库搭建的代码与E-R图、函数依赖关系等。参考资料：王朗、萨师煊著《数据库系统概论（第5版）》，高等教育出版社2014年版。

首先，根据初步的信息统计，我所能找到的具有实际意义和可行性的相关属性如下：高校招生代码（高校编号）、高校名称、高校官网、省份、省教育招生考试院官网、考生人数、最低分数线、高校等级及等级介绍、高校招生类型及招生类型介绍、各高校每年在各省的招生人数、学校的类型以及类型的介绍。

属性之间的关系如下：一对一包括：高校招生代码与高校名称，高校名称与高校官网，高校招生代码与高校招生官网，省份与省教育招生考试院官网，高校等级与等级介绍，高校招生类型与招生类型介绍，学校的类型与类型的介绍等；一对多包括：省份与高校名称、高校招生代码等；多对多包括：高校招生代码与高校招生类型、高校等级等。

基于这些信息以及用户需求，我设计如下十一张表格：大学表、省份表、考生人数表、最低分数线表、用户表、学校等级表、招生类型表、招生人数表、学校类型表、各高校等级表、各高校招生类型表。

具体设计如下（双语对照）：

大学(collage)表(主码为collage_no)
高校编号(collage_no)
高校名称(collage_name)
官方URL(official_URL)
省份名(province_name)(FK)
学校类型(school_type_name)(FK)
省份(province)表(主码为province_name)
省份名(province name)
省教育招生考试院(PEAEI_URL)
考生人数(student_num)表(主码为province_name与year)
省份名(provience_name)(FK)
年份(year)
参加考试人数(student_in_total)
最低分数线(min_score_line)表(主码为collage_no与province_name)
高校编号(collage_no)(FK)
省份名(province_name)(FK)
最低分数线(min_score)
用户(user)表(主码为user_name)
用户名(user_name)
用户密码(user_password)
用户电子邮箱(user_e_mail)
省份名(province_name)(FK)
高考分数(score)
学校等级(school_level)表(主码为school_level)
学校等级名(school_level_name)
学校等级介绍(school_level_intro)
招生类型(enrollment_type)表(主码为enrollment_type_name)
招生类型名(enrollment_type_name)
招生类型介绍(enrollment_type_intro)
招生人数(enrollment)表(主码为collage_no与province_name与year)
高校编号(collage_no)(FK)
省份名(province_name)(FK)
年份(year)
招生人数(enrollment_in_total)
学校类型(school_type)表(主码为school_type_name)
高校类型名(school_type_name)
高校类型介绍(school_type_intro)
各高校等级(collage_school_level)表(主码为collage_no)
高校编号(collage_no)(FK)
高校等级名(school_level_name)(FK)
各高校招生类型(collage_province_enrollment_type)(主码为collage_no与enrollment_type_name与province_name)
高校编号(collage_no)(FK)
招生类型名(enrollment_type_name)(FK)
省份名(province_name)(FK)

 各属性所构成的E-R图如下：

根据设计结果在MySQL中进行搭建，全部SQL语句如下（未导入数据）：

net start mysql  # 启动数据库

mysql -u root -p  # 使用root登录

create database dbms_report  # 建立数据库（数据库默认字符集UTF-8，以下均默认）

create table province(  #建province表 province_name varchar(20) primary key,  # 设置主键 PEAEI_URL varchar(250));

create table school_level(  #建school_level表 school_level_name varchar(20) primary key,  # 设置主键 school_level_intro varchar(250));

create table enrollment_type( enrollment_type_name varchar(20) primary key,  # 设置主键 enrollment_type_intro varchar(250));

create table school_type( school_type_name varchar(20) primary key,  # 设置主键 school_type_intro varchar(250));

create table collage( collage_no int primary key,  # 设置主键 collage_name varchar(20), official_URL varchar(50), province_name varchar(20), school_type_name varchar(20), foreign key (province_name) references province(province_name),  # 设置外键，下同 foreign key (school_type_name) references school_type(school_type_name));

create table student_num( province_name varchar(20), year int, student_in_total varchar(20), primary key (province_name,year),  # 设置双属性主键 foreign key (province_name) references province(province_name));  # 设置外键

create table min_score_line( collage_no int, province_name varchar(20), min_score int, primary key (collage_no,province_name),  # 设置双属性主键 foreign key (collage_no) references collage(collage_no),  # 设置外键，下同 foreign key (province_name) references province(province_name));

create table user( user_name varchar(20) primary key,  # 设置主键 user_password varchar(20), user_e_mail varchar(50), province_name varchar(20), score int, foreign key (province_name) references province(province_name));  # 设置外键

create table enrollment( collage_no int, province_name varchar(20), year int, enrollment_in_total int, primary key (collage_no,province_name,year)  # 设置三属性主键 foreign key (collage_no) references collage(collage_no),  # 设置外键，下同 foreign key (province_name) references province(province_name));

create table collage_school_level( collage_no int, school_level_name varchar(20), primary key (collage_no,school_level_name),  # 设置双属性主键 foreign key (collage_no) references collage(collage_no),  # 设置外键，下同 foreign key (school_level_name) references school_level(school_level_name));

create table collage_province_enrollment_type( collage_no int, province_name varchar(20), enrollment_type_name varchar(20), primary key (collage_no,province_name,enrollment_type_name),  # 设置三属性主键 foreign key (collage_no) references collage(collage_no),  # 设置外键，下同 foreign key (province_name) references province(province_name), foreign key (enrollment_type_name) references enrollment_type(enrollment_type_name));

 对于数据的搜集，我采取的是第三方的数据爬取工具，辅以自己手动统计，将数据写入Excel表格中（不写表头）

完成数据的分表写入后，将原有的表格文件（xlsx）转换为UTF-8编码的文本文档（txt）（具体操作为：文件-另存为-选择存储路径-保存类型选择文本文档-保存-打开文本文档-文件-另存为-编码格式选择utf8-保存）。至此，已经完成了数据导入前的准备工作。

导入数据我选择的SQL语句为：load data infile ‘file_path_name.txt’ into table table_name;

注意，在导入数据时，由于xlsx文件转txt文件时会保留换行符\n与纵向制表符\v等，因此就需要进行数据清洗，去掉这些符号，以保证最后数据在数据库中的正常显示，此处参见：mysql的关系表在cmd窗口上显示不全或显示出现行列混乱可能存在的问题以及相应的解决方案_山河之书的博客-CSDN博客_mysql 显示不全

但是，问题在于由于外键限制，导致collage表插入数据的时候，最后一列需要先清洗再插入，然而只有插入才能清洗，形成了死循环，此处的解决方案是： 笑论mysql如何在你不需要的时候禁用参照完整性（禁用外键）从而方便你改数据_山河之书的博客-CSDN博客_mysql 禁止外键

这样就能正常插入数据（但我还是由衷的建议，尽量不要用txt文本作为基础导入数据，也不要把涉及外键的属性放在最后一列）

插入数据后，对每个数据都进行select，检查是否存在数据插入失败、中文乱码、表格混乱的问题，有的话及时排除。

至此我们已经完成了整个系统开发的第一部分（其实也是很困难的一部分），简单的数据库底层搭建