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数据库