jimmy.txt

学生类：Student 教师类：Teacher 主任：AdminTeacher 班主任：MasterTeacher 授课老师： PrelectTeacher 班级类：classTeam 班

                             以下代码一次性完成，建立数据库



use master
-------------------创建数据库------------------------------------------------------------------
-------------------务必修改创建目录------------------------------------------------------
if exists(select * from sysdatabases where name = 'APTECH')
	drop database APTECH
go
Create database APTECH
on
(
	name = APTECH_dat,
	filename = 'E:\APTECH\Database\APTECH_Data.mdf',
	size = 20,
	maxsize = 300,
	filegrowth = 15%
)
log on
(
	name = 'APTECH_log',
	filename = 'E:\APTECH\Database\APTECH_Log.ldf',
	size = 10,
	maxsize = 150,
	filegrowth = 10%
)
go

use APTECH
go




---------------------------创建教师基本信息表--------------------
if exists(select * from sysobjects where name = 'TeacherInfo')
	drop table TeacherInfo
go
Create table TeacherInfo
(
	teaLimit varchar(2) not null			--教师权限 	T：授课教师
constraint Check_teaLimit check(teaLimit in('A','M','T')), 	--	M：班主任教师
							--		A：主管教师
	teaName varchar(20) not null,			--姓名
	teaSex bit not null,				--性别，0：男  1：女
	teaOrigin varchar(20) default '',		--籍贯
	teaNation varchar(20) default '',		--民族
	teaTelephone varchar(20) default ''
constraint Check_teaTelephone check(teaTelephone like
'[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or teaTelephone like
'[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or teaTelephone like
'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or teaTelephone like
'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),--联系电话
	teaAddress text default '',			--地址
	teaPostcode varchar(10)
constraint Check_teaPostcode check(teaPostcode like
'[0-9][0-9][0-9][0-9][0-9][0-9]') default '000000',	--邮政编码
	teaPicture varchar(200) default 'beidaqingniao.jpg',	--照片目录
	teaIn_Service bit not null default 1,		--是否在职 0:离职  1：在职（默认）
	teaRemark text default '',			--备注
	teaID int identity(10000,1) primary key		--教师编号
)
go



-------------------输入教师基本信息表数据-----------------------------
--分别插入一条公共教师信息，其本身并不存在，作为必要时的默认值是用
insert TeacherInfo values('A','北大青鸟',0,'北京','中华民族','13800000000',
'北京市西城区复兴门','100000',default,1,'模拟教师')
--实际教师
insert TeacherInfo values('A','邓斌',0,'浙江','汉族','13811702727',
'北京市回龙观区21楼501室','100076','E:\APTECH\Picture\dengbin.jpg',1,'部门经理')
insert TeacherInfo values('A','王冬强',0,'辽宁','汉族','13910012727',
'北京市学院路6号楼302室','100082','E:\APTECH\Picture\wangdongqiang.jpg',1,'部门经理')
insert TeacherInfo values('M','孙贵苓',1,'北京','汉族','13311722787',
'北京市展春园15号楼201室','100066','E:\APTECH\Picture\sunguiling.jpg',1,'班主任')
insert TeacherInfo values('M','穆振东',0,'北京','汉族','87765895',
'北京市展览路3号楼301室','100039','E:\APTECH\Picture\muzhendong.jpg',1,'班主任')
insert TeacherInfo values('M','洪丽',1,'北京','汉族','13911613727',
'北京市复外大街21号楼102室','100037','E:\APTECH\Picture\hongli.jpg',1,'班主任')
insert TeacherInfo values('T','戴炜',0,'湖南','汉族','83762090',
'北京市新街口大街15楼202室','100030',default,1,'高级讲师')
insert TeacherInfo values('T','曹雪松',0,'北京','汉族','13922102808',
'北京市护国寺大街6号楼502室','100021','E:\APTECH\Picture\caoxuesong.jpg',1,'高级讲师')
insert TeacherInfo values('T','徐健',0,'广州','回族','13322301368',
'北京市白石桥大街3号楼101室','100070','E:\APTECH\Picture\xujian.jpg',1,'高级讲师')
insert TeacherInfo values('T','杨波',0,'上海','汉族','13510102626',
'北京市方庄小区16号楼601室','100011',default,1,'高级讲师')
insert TeacherInfo values('T','王东石',0,'北京','汉族','13911132816',
'北京市西四大街1号楼201室','100050','E:\APTECH\Picture\wangdongshi.jpg',1,'高级讲师')
go


-------------------------------创建教师密码表--------------------------------
if exists(select * from sysobjects where name = 'TeacherPassWordInfo')
	drop table TeacherPassWordInfo
Create table TeacherPassWordInfo
(
	teaID int primary key constraint FK_TeacherPassWordInfo_teaID
foreign key(teaID) references TeacherInfo(teaID),     --教师编号		     
	teaPassWord varchar(30) default '',	      --密码
)


----------------------输入教师密码表的基本信息-------------------------------
declare @teaID int
select @teaID = teaID from TeacherInfo where teaName = '邓斌'
insert TeacherPassWordInfo values(@teaID,'dengbin')

select @teaID = teaID from TeacherInfo where teaName = '王冬强'
insert TeacherPassWordInfo values(@teaID,'wangdongqiang')

select @teaID = teaID from TeacherInfo where teaName = '孙贵苓'
insert TeacherPassWordInfo values(@teaID,default)

select @teaID = teaID from TeacherInfo where teaName = '穆振东'
insert TeacherPassWordInfo values(@teaID,'muzhengdong')

select @teaID = teaID from TeacherInfo where teaName = '洪丽'
insert TeacherPassWordInfo values(@teaID,'hongli')

select @teaID = teaID from TeacherInfo where teaName = '戴炜'
insert TeacherPassWordInfo values(@teaID,'daiwei')

select @teaID = teaID from TeacherInfo where teaName = '曹雪松'
insert TeacherPassWordInfo values(@teaID,'caoxuesong')

select @teaID = teaID from TeacherInfo where teaName = '徐健'
insert TeacherPassWordInfo values(@teaID,'xujian')

select @teaID = teaID from TeacherInfo where teaName = '杨波'
insert TeacherPassWordInfo values(@teaID,'yangbo')

select @teaID = teaID from TeacherInfo where teaName = '王东石'
insert TeacherPassWordInfo values(@teaID,'wangdongshi')



-----------------------------创建教师缺勤信息表--------------------------------
if exists(select * from sysobjects where name = 'TeacherAFDInfo')
	drop table TeacherAFDInfo
go
Create table TeacherAFDInfo      	-- AFD: Absence From Duty 缺勤
(
	teaID int not null constraint FK_TeacherAFDInfo_teaID
foreign key(teaID) references TeacherInfo(teaID),--教师编号
	afdDate smallDatetime not null,		--缺勤日期
	Type int not null,			--缺勤类型
						--0：迟到 1：早退 2：旷工 3：请假
	Hours int not null default 0,		--旷工或者请假的工时
	Remark text default '',			--备注
	afdID int identity(1,1) primary key	--编号
)
go


-----------------------------输入教师缺勤信息表数据------------------------------------
insert TeacherAFDInfo values(10001,'2006-8-10',0,default,default)
insert TeacherAFDInfo values(10002,'2006-8-16',1,default,default)
insert TeacherAFDInfo values(10003,'2006-8-23',3,2,'事假')
insert TeacherAFDInfo values(10004,'2006-8-27',3,8,'病假')
insert TeacherAFDInfo values(10005,'2006-9-5',2,1,'堵车')
insert TeacherAFDInfo values(10006,'2006-9-13',0,default,default)
insert TeacherAFDInfo values(10007,'2006-9-17',0,default,default)
insert TeacherAFDInfo values(10008,'2006-9-19',1,default,default)
insert TeacherAFDInfo values(10009,'2006-9-25',1,default,default)
insert TeacherAFDInfo values(10010,'2006-9-29',3,24,'探亲')
insert TeacherAFDInfo values(10010,'2006-10-10',0,default,default)
insert TeacherAFDInfo values(10001,'2006-10-12',1,default,default)
insert TeacherAFDInfo values(10002,'2006-10-15',3,2,'事假')
insert TeacherAFDInfo values(10003,'2006-10-17',3,8,'病假')
insert TeacherAFDInfo values(10004,'2006-10-23',2,1,'堵车')
insert TeacherAFDInfo values(10005,'2006-10-25',0,default,default)
insert TeacherAFDInfo values(10006,'2006-10-26',0,default,default)
insert TeacherAFDInfo values(10007,'2006-10-27',1,default,default)
insert TeacherAFDInfo values(10008,'2006-10-28',1,default,default)
insert TeacherAFDInfo values(10009,'2006-10-29',3,24,'探亲')
go



----------------------------创建班级基本信息表-----------------------
if exists(select * from sysobjects where name = 'ClassInfo')
	drop table ClassInfo
Create table ClassInfo
(
	claID varchar(10) primary key,			--班级编号