database.sql

该系统的最终用户是访问本系统的工作人员

create database stucheckbase
create table hygiene_check
(日期 datetime,
 栋号 char(4),
 寝室号 char(10) default '0',
 评分1 float(8) default '0',
 评分2 float(8) default '0',
 评分3 float(8) default '0',
 总分 float(8) default '0',
 平均分 decimal(9),
 primary key(日期,栋号,寝室号)
)
create table reward_punish
(栋号 char(4),
 寝室号 char(10),
 年级 char(10) default '无',
 学院 char(30) default '无',
 专业 char(20) default '无',
 寝室成员 text default '无',
 奖励情况 char(200) default '无',
 违纪情况 char(200) default '无',
 电话 char(12) default '无',
primary key (栋号,寝室号) 
)
create table hygiene_staff
(编号 char(10) primary key,
 栋号 char(4) default '无',
 寝室号 char(10) default '无',
 姓名 char(10) default '无',
 学院 char(30) default '无',
 年龄 int default '0',
 性别 char(8) default '无',
 职位 char(20) default '无',
 电话 char(12) default '无', 
 备注 varchar(50) default '无'
)
create table user_If
(user_ID char(10),
 user_PWD char(10),
 user_Des char(10)
)
insert into hygiene_check values ('2005-3-25','1','N212','90','90','90','270','90')
insert into hygiene_check values ('2005-3-26','2','N213','90','90','90','270','90')
insert into hygiene_check values ('2005-3-27','3','S214','90','90','90','270','90')
insert into hygiene_check values ('2005-3-25','4','N215','80','80','80','240','80')
insert into hygiene_check values ('2005-3-25','5','N216','90','90','90','270','90')
insert into hygiene_check values ('2005-3-30','6','S217','90','90','90','270','90')
insert into hygiene_check values ('2005-4-18','2','N218','90','90','90','270','90')

insert into reward_punish values ('1','N212','03','软件学院','软件工程','王冲，欧阳红，刘华，李名','无','无','7105778')
insert into reward_punish values ('2','N213','03','音乐学院','音乐','王力红，欧万有，刘名，朱美','无','无','7105779')
insert into reward_punish values ('3','S214','03','数信学院','数学','张青，欧阳红，刘华，李四','无','无','7105786')
insert into reward_punish values ('4','N215','03','教育学院', '教育学 ', '张学有，苏有朋，刘德华，李红','无','无','7105778')
insert into reward_punish values ('5','N216','03','软件学院','软件工程','供杜，罗雪娟，刘华，李四光','无','无','8121778')
insert into reward_punish values ('6','S217','03','体育学院','体育','刘翔，任贤齐，毛阿美，张波','无','无','8121768')
insert into reward_punish values ('2','N218','03','科技学院','软件工程','宋祖英，黎明，小刚，涂红刚','无','无','7105567')
 
insert into hygiene_staff values ('001','2','S218','赵节','计算机学院','20','男','栋长','7106753','无')
insert into hygiene_staff values ('002','3','N220','吴宾','科技学院','19','女','监督员','7106763','无')
insert into hygiene_staff values ('003','4','N321','王露','音乐学院','21','女','栋长','7106753','无')
insert into hygiene_staff values ('004','5','N418','杨西','外语学院','20','女','部长','8121743','无')
insert into hygiene_staff values ('005','6','S518','龙大里','体育学院','22','男','栋长','8121694','无')
insert into hygiene_staff values ('006','7','N228','彭丽','财金学院','20','女','层长','8122753','无')
insert into hygiene_staff values ('007','8','N208','赵万','美术学院','21','男','副部长','8122753','无')

insert into user_If values('user','123','123') 
insert into user_If values('sa','sa','sa')