档案管理系统数据库代码

1、/*题目：档案管理系统数据库的设计与实现描述：设计一个档案管理系统，实现下列功能：1维护人员的档案信息（输入、修改和删除）2根据给定条件查询人员档案信息；3档案报表输出；*/*创建档案管理系统数据库*/create database danganguanlion(name=danganguanli_mdf, filename=d:danganguanli.mdf, size=10, maxsize=100, filegrowth=5)log on(name=danganguanli_ldf, filename=d:danganguanli.ldf, size=10, maxsize=100,

2、filegrowth=5)/*创建学生信息表*/create table student(number varchar(20) primary key, class varchar(20) not null, sname varchar(10) not null, teacher varchar(20) not null, idnum char(20) unique, sex char(2) default男, age int , policy char(10), phone varchar(12) unique)/*创建学生奖惩荣誉情况表*/create table jiangcheng(n

3、umber varchar(20) not null constraint jc_for foreign key references student(number), rongyu char(50), constraint jc_pri primary key(number)/*创建学生家庭信息表*/create table jiating(number varchar(20) primary key constraint jt_for foreign key references student(number), homeadress char(20), father varchar(10

4、), mather varchar(10), call varchar(12), monthincome int)/*用户授权*/exec sp_addlogin zp,1234,danganguanli,nullexec sp_adduser zpexec sp_addsrvrolemember zp,dbcreator/*创建档案视图*/create view dangan(学号,班级,姓名,班主任,身份证号,性别,年龄,政治面貌,本人电话,奖惩情况,家庭地址,父亲,母亲,家庭电话,家庭月收入)asselect student.number,class,sname,teacher,idnu

5、m,student.sex,student.age,policy,phone,rongyu,homeadress,father,mather,call,monthincomefrom student,jiangcheng,jiatingwhere student.number=jiangcheng.number and student.number=jiating.number/*创建存储过程进行学生档案信息录入*/create procedure luru(number varchar(20), class varchar(20), sname varchar(10), teacher va

6、rchar(20), idnum char(20), sex char(2), age int , policy char(10), phone varchar(12), rongyu char(50), homeadress char(20), father varchar(10), mather varchar(10), call varchar(12), monthincome int)asbegindeclare cant varchar(20)declare set varchar(20)set cant=信息已存在，不能录入set set=录入if exists (select*f

7、rom dangan where 学号=number)print cant else begin print set insert into student values(number,class,sname, teacher,idnum,sex,age,policy,phone) insert into jiangcheng values(number,rongyu) insert into jiating values(number,homeadress,father,mather,call,monthincome) endend/*创建存储过程查询学生档案信息*/create proce

8、dure cx(number varchar(20)asselect * from danganwhere 学号=number/*创建存储过程进行学生档案信息修改*/create procedure xiugai(number varchar(20), class varchar(20), sname varchar(10), teacher varchar(10), idnum char(20), sex char(2), age int , policy char(10), phone varchar(12), rongyu char(50), homeadress char(20), f

9、ather varchar(10), mather varchar(10), call varchar(12), monthincome int)asbegin declare cant varchar(20) set cant=该学生不存在，不能进行修改！ if exists (select * from dangan where 学号=number) begin update student set class=class,sname=sname,teacher= teacher,idnum=idnum,sex=sex,age=age,policy=policy,phone=phone w

10、here number=number update jiangcheng set rongyu=rongyu where number=number update jiating set homeadress=homeadress,father=father,mather=mather,call=call,monthincome=monthincome where number=number end else print cantend/*创建存储过程删除某学生档案*/create procedure dele(number varchar(20)asbegin declare cant va

11、rchar(20) set cant=不存在该学生档案信息，不能删除！ begin if exists (select * from dangan where 学号=number) begin delete from jiangcheng where number=number delete from jiating where number=number delete from student where number=number end else print cant endend/*创建触发器显示录入后的结果*/create trigger displayiseron jiatingafter insertasselect * from dangan/*创建触发器检验删除是否成功，显示删除后的结果*/create trigger displayscon studentafter deleteasselect *from dangan/*创建触发器显示修改后的学生档案信息*/create trigger displayupdon jiatingafter updateasselect * from dangan/*录入检验*/exec luru 001,02,小花,张老师,3204011991233229,女,20,团员,13524