1、学生基本信息数据库代码create database studjcuse studjccreate table adminUsers(adminID int IDentity(1,1) primary key,adminName varchar(20),adminPwd varchar(20)insert into adminUsers values(wyx,1234)insert into adminUsers values(sql,abcd)create table zhuanye(zhuanyeID int identity(1,1) primary key,zhuanyeName va
2、rchar(20)insert into zhuanye values(计算机应用技术)insert into zhuanye values(软件技术)insert into zhuanye values(计算机网络技术)create table class(classID int identity(1,1) primary key,zhuanyeID int foreign key references zhuanye(zhuanyeID),className varchar(20)insert into class values(1,计 A081)insert into class val
3、ues(1,计 B081)insert into class values(2,软件 A081)insert into class values(2,软件 A091)insert into class values(3,网络 A081)insert into class values(2,网络 A091)create table zhengzhimm(mmID int primary key,mmName char(4) )insert into zhengzhimm values(1,党员)insert into zhengzhimm values(2,团员)insert into zhen
4、gzhimm values(3,群众)create table studUsers(studNum char(10) primary key,studName varchar(20),studSex char(2),zhuanyeID int foreign key references zhuanye(zhuanyeID),classID int foreign key references class(classID),mmID int foreign key references zhengzhimm(mmID) )insert into studUsers values(2008010
5、101,张凤,女,1,1,2)insert into studUsers values(2008010102,李立芬,女,1,2,2)insert into studUsers values(2008010201,王一,男,2,3,1)alter table studUsers add check(studSex=男 or studSex=女)create table jangli(jlID int identity(1,1) primary key,jlName varchar(50),jlDanwei varchar(50),jlGrade char(10)alter table jang
6、li add unique(jlName,jlDanwei)insert into jangli values(三好学生,水利学院,院级)insert into jangli values(优秀团员,水利学院,院级)insert into jangli values(一等奖学金,水利学院,院级)create table chufa(cfID int identity(1,1) primary key,cfName varchar(50),cfDanwei varchar(50),cfGrade char(10)insert into chufa values(警告,水利学院,院级)insert
7、 into chufa values(留校察看,水利学院,院级)create table studjl(studjlID int identity(1,1) primary key,studNum char(10) foreign key references studUsers(studNum),jlID int foreign key references jangli(jlID),jlXueqi char(1) not null,jlTime dateTime,jlCailiao varchar(50) null,jlYouxiao char(1) default y, jlChexia
8、oTime datetime null)alter table studjl add unique(studNum,jlID,jlXueqi)insert into studjl(studNum,jlID,jlXueqi,jlTime) values(2008010101,1,1,2009-11-2)insert into studjl(studNum,jlID,jlXueqi,jlTime) values(2008010102,2,2,2009-11-2)insert into studjl(studNum,jlID,jlXueqi,jlTime) values(2008010201,1,2
9、,2009-11-2)create table studcf(studcfID int identity(1,1) primary key,studNum char(10) foreign key references studUsers(studNum),cfID int foreign key references chufa(cfID),cfXueqi char(1) not null,cfTime dateTime,cfCailiao char(50) null,cfYouxiao char(1) default y, cfChexiaoTime datetime null)alter
10、 table studcf add unique(studNum,cfID,cfXueqi)insert into studcf(studNum,cfID,cfXueqi,cfTime) values(2008010201,1,1,2009-11-2)insert into studcf(studNum,cfID,cfXueqi,cfTime) values(2008010101,2,2,2009-11-12)create table news(newsID int identity(1,1) primary key,title varchar(50),text varchar(400)ins
11、ert into news values(操作说明,1.学生使用系统说明;2.管理员使用系统说明.)创建视图 studxx，方便查询学生的信息create view studxxasSELECT dbo.studUsers.studNum, dbo.studUsers.studName, dbo.studUsers.studSex, dbo.zhuanye.zhuanyeName, dbo.class.className, dbo.zhengzhimm.mmNameFROM dbo.studUsers INNER JOINdbo.class ON dbo.studUsers.classID =
12、 dbo.class.classID INNER JOINdbo.zhengzhimm ON dbo.studUsers.mmID = dbo.zhengzhimm.mmID INNER JOINdbo.zhuanye ON dbo.studUsers.zhuanyeID = dbo.zhuanye.zhuanyeID AND dbo.class.zhuanyeID = dbo.zhuanye.zhuanyeID创建视图 studjlxx，方便查询学生的获奖信息create view studjlxxasSELECT dbo.studUsers.studNum AS 学号, dbo.studU
13、sers.studName AS 姓名, dbo.jangli.jlName AS 奖励名称, dbo.studjl.jlXueqi AS 奖励学期, dbo.studjl.jlYouxiao AS 有效性FROM dbo.studUsers INNER JOINdbo.studjl ON dbo.studUsers.studNum = dbo.studjl.studNum INNER JOINdbo.jangli ON dbo.studjl.jlID = dbo.jangli.jlID创建视图 studcfxx，方便查询学生的处罚信息create view studcfxxasSELECT
14、dbo.studUsers.studNum AS 学号, dbo.studUsers.studName AS 姓名, dbo.chufa.cfName AS 处罚名称, dbo.studcf.cfXueqi AS 处罚学期, dbo.studcf.cfYouxiao AS 有效性FROM dbo.studUsers INNER JOINdbo.studcf ON dbo.studUsers.studNum = dbo.studcf.studNum INNER JOINdbo.chufa ON dbo.studcf.cfID = dbo.chufa.cfID创建视图 jangli_view，方便查询奖励的基本信息create view jangli_viewasSELECT jlID AS 序号, jlName AS 奖励名称, jlDanwei AS 奖励单位, jlGrade AS 奖励级别FROM dbo.jangli创建视图 admin_view，方便管理的管理的基本信息create view admin_viewasSELECT adminID AS 编号,adminName AS 用户名, adminPwd AS 密码FROM dbo.adminUsers
