数据库.sql

北大青鸟教学管理系统是学习规范编程范本.功能非常完备,代码编写有章法,不可多得

drop database studymanage
go
create database StudyManage  --为项目创建数据库
on primary
(
	name=StudyManage_mdf,
	filename='e:\StudyManage.mdf',
	size=10,
	maxsize=50,
	filegrowth=10
)
log on
(
	name=StudyManage_log,
	Filename='e:\StudyManage_log.ldf',
	size=5,
	maxsize=30,
	filegrowth=5
)
go

use studymanage --使用创建的数据库
go

create table sysUser  --创建表sysUser，系统用户表
(
	UserName varchar(20) constraint UserName_pk primary key not null,
	Password varchar(32) default '123',
	RegisterDate datetime not null,
	LogoutDate datetime,
	Status char(4) constraint Status_ck check(Status='正常' or Status='注销')
		default '正常',
	Popedom varchar(8) constraint Pope_ck 
		check(popedom='超级用户' or popedom='一般用户') default '超级用户' not null,
	Remark varchar(1024) --备注
)
go

insert into sysUser (UserName,RegisterDate) values ('Administrator',getdate( ))
go
insert into sysUser (UserName,RegisterDate,popedom) values ('Guest',getdate( ),'一般用户')
go

create table classInfo  --班级信息
(
	classNo varchar(15) constraint classNo_pk primary key not null, --班级编号
	begDate datetime not null,  --开班日期
	endDate datetime, --毕业日期
	remark varchar(1024)
)
insert into classInfo values('T11','2005-08-01',null,null)
insert into classInfo values('T12','2005-9-01',null,null)
go

create table termInfo  --学期信息
(
	termNo varchar(15) constraint termNo_pk primary key not null,  --学期编号
	classNo varchar(15) constraint classNo_fk foreign key references classinfo not null,  --班级编号
	term char(2) constraint term_ck check (term='S1' or term='S2' or term='Y2') not null,		--学期
	begDate datetime not null,	--开学日期
	endDate datetime,		--毕业日期
	Director varchar(32) not null,	--班主任
	remark varchar(1024)		--备注
)
go
create table studentInfo  --学员基本信息表
(
	studentNo varchar(15)constraint studentNo_pk primary key not null,	--学号
	name Varchar(16) not null,						--姓名
	py Varchar(32) not null,						--姓名拼音
	sex Char(2) constraint sex_ck check(sex='男' or sex='女') not null,
	identityNo Varchar(18) constraint identityNo_ck check([identityNo] 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][0-9][0-9][0-9]' or 
		[identityNo] 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][0-9][0-9][0-9][0-9][0-9][0-9]') not null,		--身份证号
	classNo	Varchar(15) constraint classNo2_fk foreign key references classInfo not null,	--班级编号
	School Varchar(64),	--毕业学校
	degree Varchar(6)constraint degree_ck check(degree='高中' or degree='中专' or 
		degree='大专' or degree='本科' or degree='研究生' or degree='其它') not null,	--学历
	specialty Varchar(32),						--专业
	certificate Char(2) constraint certificate_ck check(certificate='S1' or 
		certificate='S2' or certificate='Y2' or certificate='无') not null,		--已持有证书
	jobStatus char(4) constraint jobStatus_ck check(jobStatus='在职' or 
		jobStatus='在读' or jobStatus='待业') not null,					--工用状态
	jobUnit	Varchar(64),
	jobType	Varchar(64),
	Address	Varchar(64) not null,
	maincode Char(6) not null,
	Tel Varchar(20)	 not null,
	toCity Varchar(64),		--就业意向城市
	toPost Varchar(64),		--就业意向岗位
	Status Char(4) constraint Status_ck2 check(Status='在读' or Status='休学' or 
		Status='退学' or Status='转学' or Status='结业' or Status='毕业') not null,	--状态
	remark Varchar(1024)	
)
insert into studentinfo values('0001','聂植方','Nie Zhifang','男','432503198308184059','T12','涟源四中','高中','计算机','无','在读',null,null,'湖南娄底','417000','110',null,null,'在读',null)
go


create table scoreInfo
(
	StudentNo varchar(15) constraint StudentNo_fk foreign key references StudentInfo not null,--学号
	courseName varchar(32) not null,--课程名
	teacher varchar(16) not null,	--任课老师
	examDate datetime not null,	--考试日期
	score int not null,		--成绩
	remark varchar(1024)
)
insert scoreInfo values('0001','C语言','谭亮','2005-10-20',90,'空')
insert into scoreinfo values('0001','SQL Server','曾','2005-11-10',85,null)
insert into scoreinfo values('0001','软件技术基础','杨','2005-9-10',92,null)
insert into scoreinfo values('0001','Visual Basic 6.0','谭亮','2005-12-20',90,null)
go