昨天设计出来了数据的第一版，现在飞哥和根儿正在修改中，两位大神保佑找到问题不要太多吐槽，我要开始设计服务器模型了，请把每一个epoll用正确，这非常重要，采用什么模式呢，ET还是LT。读写的时候细节。UDP（内网负载发送消息）和TCP（工作过程）等，下午服务器交工第一版。
放出数据库总体图：
代码
DROP DATABASE if EXISTS TSHH;
CREATE DATABASE TSHH; /*创建数据库*/
USE TSHH;
DROP TABLE IF EXISTS ts; /*教师学生信息表*/
DROP TABLE IF EXISTS ts_passwd; /*教师学生密码表*/
DROP TABLE IF EXISTS teacher_plan; /*教学计划安排表*/
DROP TABLE IF EXISTS single_class; /*单个班级表*/
DROP TABLE IF EXISTS big_class; /*单个班级组成的大班*/
DROP TABLE IF EXISTS college; /*学院*/
DROP TABLE IF EXISTS dept; /*专业*/
DROP TABLE IF EXISTS course; /*班级*/
DROP TABLE IF EXISTS schedule_course; /*课表*/
DROP TABLE IF EXISTS datad; /*上传的资料*/
DROP TABLE IF EXISTS download; /*下载的资料*/
DROP TABLE IF EXISTS question; /*问题*/
DROP TABLE IF EXISTS answer; /*答案*/
DROP TABLE IF EXISTS message; /*消息*/
DROP TABLE IF EXISTS rollcall; /*点名记录*/
DROP TABLE IF EXISTS rollcall_nothere; /*点名不在的人*/
DROP TABLE IF EXISTS classroom; /*教室的地图*/
/*教师学生信息表*/
CREATE TABLE ts (
ts_id INT NOT NULL AUTO_INCREMENT, /*主键*/
ts_num VARCHAR(20), /*老师教工号/学生学号*/
ts_name VARCHAR(20), /*姓名*/
college_id INT, /*所在学院 外键*/
dept_id INT, /*所在系 外键*/
single_class_id INT, /*学生的所在班级 外键*/
ts_introduction VARCHAR(200), /*自我介绍*/
ts_head_image LONGBLOB, /*头像*/
PRIMARY KEY (ts_id)
);
/*教师学生密码表*/
CREATE TABLE ts_passwd (
ts_passwd_id INT NOT NULL AUTO_INCREMENT, /*主键*/
ts_id INT, /*老师学生ID 外键*/
ts_passwd_content VARCHAR(100), /*密码*/
PRIMARY KEY (ts_passwd_id)
);
/*教学计划安排表*/
CREATE TABLE teacher_plan (
teacher_plan_id INT NOT NULL AUTO_INCREMENT, /*主键*/
ts_id INT, /*老师ID 外键*/
teacher_plan_week INT, /*周*/
schedule_course_id INT, /*确定每一节课 外键*/
teacher_plan_content VARCHAR(200), /*安排的上课内容，预习内容*/
teacher_plan_type SMALLINT, /* 0:上课 1：实验 2：上机*/
PRIMARY KEY (teacher_plan_id)
);
/*单个班级表*/
CREATE TABLE single_class (
single_class_id INT NOT NULL AUTO_INCREMENT, /*主键*/
single_class_name VARCHAR(20), /*班级名称 eg:1404*/
dept_id INT, /*所在系 外键*/
college_id INT , /*所在学院 外键*/
PRIMARY KEY (single_class_id)
);
/*单个班级组成的大班*/
CREATE TABLE big_class (
big_class_id INT NOT NULL AUTO_INCREMENT, /*主键*/
big_class_num INT, /*大班的编号*/
big_class_name VARCHAR(40), /*大班名称*/
ts_id INT, /*老师ID 外键*/
single_class_id INT, /*单个班级ID* 外键*/
course_id INT, /*课程ID 外键*/
PRIMARY KEY (big_class_id)
);
/*学院*/
CREATE TABLE college (
college_id INT NOT NULL AUTO_INCREMENT, /*主键*/
college_name VARCHAR(20), /*学院名称*/
college_introduction VARCHAR(200) , /*学院介绍*/
PRIMARY KEY (college_id)
);
/*专业*/
CREATE TABLE dept (
dept_id INT NOT NULL AUTO_INCREMENT, /*主键*/
college_id INT, /*属于的学院 外键*/
dept_name VARCHAR(20), /*专业名称*/
dept_introduction VARCHAR(200) , /*专业介绍*/
PRIMARY KEY (dept_id)
);
/*班级*/
CREATE TABLE course (
course_id INT NOT NULL AUTO_INCREMENT, /*主键*/
dept_id INT, /*属于的系 外键*/
course_name VARCHAR(20), /*课程名*/
course_introduction VARCHAR(200) , /*课程介绍*/
PRIMARY KEY (course_id)
);
/*课表*/
CREATE TABLE schedule_course (
schedule_course_id INT NOT NULL AUTO_INCREMENT, /*主键*/
dept_id INT, /*属于的系 外键*/
schedule_course_day INT, /*1-7 分别表示周一到周天*/
schedule_course_course INT, /*1-4 分别标识每天1-4节课*/
schedule_course_content varchar(200) , /*具体的课表内容*/
PRIMARY KEY (schedule_course_id)
);
/*上传的资料*/
CREATE TABLE datad (
datad_id INT NOT NULL AUTO_INCREMENT, /*主键*/
ts_id INT, /*老师/学生ID 外键*/
datad_uploadtime DATETIME, /*资料上传时间*/
datad_size INT, /*资料大小*/
datad_content VARCHAR(200), /*资料的路径 */
datad_downloadtimes INT, /*下载次数*/
PRIMARY KEY (datad_id)
);
/*下载的资料*/
CREATE TABLE download (
download_id INT NOT NULL AUTO_INCREMENT, /*主键*/
datad_id INT, /* 资料序号 外键*/
download_time DATETIME , /*下载时间*/
PRIMARY KEY (download_id)
);
/*问题*/
CREATE TABLE question (
question_id INT NOT NULL AUTO_INCREMENT, /*主键*/
ts_id INT, /*提问者*/
question_ask_time DATETIME, /*问题的提出时间*/
question_content VARCHAR(200), /*问题的内容*/
question_answertimes INT, /*问题的回答个数*/
question_type SMALLINT, /*数字分别表示不同的 科目*/
big_class_id INT, /*问题提出的大班ID 外键*/
PRIMARY KEY (question_id)
);
/*答案*/
CREATE TABLE answer (
answer_id INT NOT NULL AUTO_INCREMENT, /*主键*/
question_id INT, /*问题ID 外键*/
answer_content VARCHAR(1000), /*问题的答案*/
answer_time DATETIME, /*问题回答时间*/
PRIMARY KEY (answer_id)
);
/*消息*/
CREATE TABLE message (
message_id INT NOT NULL AUTO_INCREMENT, /*主键*/
message_type SMALLINT, /*0：公告 1：回答问题 2:私信*/
ts_id_from INT, /*消息来源*/
ts_id_to INT, /*消息目的*/
message_content VARCHAR(200), /*消息内容*/
message_status SMALLINT , /*0：未读 1：已读*/
PRIMARY KEY (message_id)
);
/*点名记录*/
CREATE TABLE rollcall (
rollcall_id INT NOT NULL AUTO_INCREMENT, /*主键*/
schedule_week INT, /*周*/
schedule_course_id INT, /*确定哪一节课*/
big_class_id INT, /*大班的ID*/
ts_id INT, /*老师的ID*/
PRIMARY KEY (rollcall_id)
);
/*点名不在的人*/
CREATE TABLE rollcall_nothere (
rollcall_nothere_id INT NOT NULL AUTO_INCREMENT, /*主键*/
rollcall_id INT, /*点名ID 外键*/
ts_id INT, /*学生ID */
PRIMARY KEY (rollcall_nothere_id)
);
/*教室的地图*/
CREATE TABLE classroom (
classroom_id INT NOT NULL AUTO_INCREMENT, /*主键*/
classroom_name VARCHAR(20), /*教室名称*/
classroom_way VARCHAR(20) , /*教室路径*/
PRIMARY KEY (classroom_id)
);
alter table ts add constraint FK_ts_college foreign key (college_id)
references college (college_id) on delete restrict on update restrict;
alter table ts add constraint FK_ts_dept foreign key (dept_id)
references dept (dept_id) on delete restrict on update restrict;
alter table ts add constraint FK_ts_single_class foreign key (single_class_id)
references single_class (single_class_id) on delete restrict on update restrict;
alter table ts_passwd add constraint FK_ts_passwd_ts foreign key (ts_id)
references ts (ts_id) on delete restrict on update restrict;
alter table teacher_plan add constraint FK_teacher_plan_ts foreign key (ts_id)
references ts (ts_id) on delete restrict on update restrict;
alter table teacher_plan add constraint FK_teacher_plan_schedule foreign key (schedule_course_id)
references schedule_course (schedule_course_id) on delete restrict on update restrict;
alter table single_class add constraint FK_single_class_dept foreign key (dept_id)
references dept (dept_id) on delete restrict on update restrict;
alter table single_class add constraint FK_single_class_college foreign key (college_id)
references college (college_id) on delete restrict on update restrict;
alter table big_class add constraint FK_big_class_ts foreign key (ts_id)
references ts (ts_id) on delete restrict on update restrict;
alter table big_class add constraint FK_big_class_single_class foreign key (single_class_id)
references single_class (single_class_id) on delete restrict on update restrict;
alter table big_class add constraint FK_big_class_course foreign key (course_id)
references course (course_id) on delete restrict on update restrict;
alter table dept add constraint FK_dept_college foreign key (college_id)
references college (college_id) on delete restrict on update restrict;
alter table course add constraint FK_course_dept foreign key (dept_id)
references dept (dept_id) on delete restrict on update restrict;
alter table schedule_course add constraint FK_dept_schedule foreign key (dept_id)
references dept (dept_id) on delete restrict on update restrict;
alter table datad add constraint FK_datad_ts foreign key (ts_id)
references ts (ts_id) on delete restrict on update restrict;
alter table download add constraint FK_download_datad foreign key (datad_id)
references datad (datad_id) on delete restrict on update restrict;
alter table question add constraint FK_question_big_class foreign key (big_class_id)
references big_class (big_class_id) on delete restrict on update restrict;
alter table answer add constraint FK_answer_question foreign key (question_id)
references question (question_id) on delete restrict on update restrict;
alter table message add constraint FK_message_ts foreign key (ts_id_from)
references ts (ts_id) on delete restrict on update restrict;
alter table message add constraint FK_message_ts_to foreign key (ts_id_to)
references ts (ts_id) on delete restrict on update restrict;
alter table rollcall add constraint FK_rollcall_big_class foreign key (big_class_id)
references big_class (big_class_id) on delete restrict on update restrict;
alter table rollcall add constraint FK_rollcall_ts foreign key (ts_id)
references ts (ts_id) on delete restrict on update restrict;
alter table rollcall_nothere add constraint FK_rollcall_nothere_rollcall foreign key (rollcall_id)
references rollcall (rollcall_id) on delete restrict on update restrict;
alter table rollcall_nothere add constraint FK_rollcall_nothere_ts foreign key (ts_id)
references ts (ts_id) on delete restrict on update restrict;