公寓管理系统学生信息初次导入步骤

 1、从教务和研究生部要学生信息（学院dept、年级year、班级class、学号studentid、姓名name、性别sex、密码password）导入数据库成表student

 

2、从后管部门要学生住宿信息（字段：校区address、楼宇building、房间room、床位bed、班级class、姓名name、学号studentid、电话phone）导入数据库成表studentbed1

 

3、从校区address表、楼宇building表、房间room表、床位bed表导出床位信息表bedinfo（字段：校区address、楼宇building、房间room、床位bed、床位序号bedid）并导入数据库（sql语句：select buildingname,roomname,bedname,a3.id as bedid from building as a1,room as a2,bed as a3 where a1.id=a2.buildingid and a2.id=a3.roomid）

 

4、因从后管部门得到的学生信息信息通常有错误，因此根据student表中的学号、姓名找出正确的数据：从student表和studentbed1表和bedinfo导出表allneedinfo（班级class、学号studentid、姓名name、校区address、楼宇building、房间building、床位bed、床位序号bedid、电话phone）并导入数据库.
 （ sql语句：select distinct a2.id,a1.class,a1.studentid,a1.name,a2.building,a2.room,a2.bed,a2.phone,a3.bedid
from student as a1,studentbed1 as a2,bedinfo as a3
where a1.name=a2.name and a1.studentid=a2.studentid and a3.building=a2.building and a3.room =a2.room and a3.bed=a2.bed ）

 

5、导出不正确的数据（sql语句：select * from studentbed1 where id not in (select id from allneedinfo)），根据student表得出正确的数据并导入数据库得表studentbed2，重复步骤4。此过程可能需要多次才能得到正确的数据

 

6、将allneedinfo表的studentid写入bed表（sql语句：update bed b1,allneedinfo b2 set b1.studentid=b2.studentid where b1.id=b2.bedid）

 

常用sql语句:
1、select * from allneedinfo where building='8号楼' and room='507'
2、select * from bedinfo where building='2号楼' and room='223'
3、select * from allneedinfo where bedid='41157'

4、select s1.building,s1.room,s1.class,s1.bed,s1.name,s1.studentid from studentbed1 as s1 where s1.studentid not in (select studentid from allneedinfo ) or s1.name not in (select name from allneedinfo )

5、select s1.id,s1.building,s1.room,s1.class,s1.bed,s1.name,s1.studentid,s2.name,s1.phone from studentbed1 as s1,student as s2 where s1.studentid not in (select studentid from allneedinfo ) and s1.studentid=s2.studentid

6、select s1.id,s1.building,s1.room,s1.class,s1.bed,s1.name,s1.studentid from studentbed1 as s1,student as s2 where s1.studentid=s2.studentid and s1.name=s2.name

7、select * from studentbed5 where id not in (select id from allneedinfo)

 

细节：

1、将excel表导入数据库

    a、将excel表另存为dbf格式，当出现“可能含有与DBF4不兼容的功能，是否保持这种格式”的提示时，选择“是”，当关掉excel表，提示“是否保存更改”，选择“否”。

    b、使用“dbf2sql”软件将dbf表转换成sql语句的文本，用记事本打开此文本，将其转换为“utf-8”编码格式。否则导入数据库显示乱码。

    c、将sql语句的文本导入mysql数据库