檔案管理系統(tǒng)數(shù)據(jù)庫代碼

1、/*題目：檔案管理系統(tǒng)數(shù)據(jù)庫的設(shè)計與實(shí)現(xiàn)描述：設(shè)計一個檔案管理系統(tǒng)，實(shí)現(xiàn)下列功能：1維護(hù)人員的檔案信息（輸入、修改和刪除）2根據(jù)給定條件查詢?nèi)藛T檔案信息；3檔案報表輸出；*/*創(chuàng)建檔案管理系統(tǒng)數(shù)據(jù)庫*/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)/*創(chuàng)建學(xué)生信息表*/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)/*創(chuàng)建學(xué)生獎懲榮譽(yù)情況表*/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)/*創(chuàng)建學(xué)生家庭信息表*/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)/*用戶授權(quán)*/exec sp_addlogin zp,1234,danganguanli,nullexec sp_adduser zpexec sp_addsrvrolemember zp,dbcreator/*創(chuàng)建檔案視圖*/create view dangan(學(xué)號,班級,姓名,班主任,身份證號,性別,年齡,政治面貌,本人電話,獎懲情況,家庭地址,父親,母親,家庭電話,家庭月收入)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/*創(chuàng)建存儲過程進(jìn)行學(xué)生檔案信息錄入*/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 學(xué)號=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/*創(chuàng)建存儲過程查詢學(xué)生檔案信息*/create proce

8、dure cx(number varchar(20)asselect * from danganwhere 學(xué)號=number/*創(chuàng)建存儲過程進(jìn)行學(xué)生檔案信息修改*/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=該學(xué)生不存在，不能進(jìn)行修改！ if exists (select * from dangan where 學(xué)號=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/*創(chuàng)建存儲過程刪除某學(xué)生檔案*/create procedure dele(number varchar(20)asbegin declare cant va

11、rchar(20) set cant=不存在該學(xué)生檔案信息，不能刪除！ begin if exists (select * from dangan where 學(xué)號=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/*創(chuàng)建觸發(fā)器顯示錄入后的結(jié)果*/create trigger displayiseron jiatingafter insertasselect * from dangan/*創(chuàng)建觸發(fā)器檢驗刪除是否成功，顯示刪除后的結(jié)果*/create trigger displayscon studentafter deleteasselect *from dangan/*創(chuàng)建觸發(fā)器顯示修改后的學(xué)生檔案信息*/create trigger displayupdon jiatingafter updateasselect * from dangan/*錄入檢驗*/exec luru 001,02,小花,張老師,3204011991233229,女,20,團(tuán)員,13524