SQL+DELPHI 图书管理系统 数据库

create database tushu go use tushu go CREATE TABLE People ( 借书证号 CHAR(15) UNIQUE, 姓名 CHAR(6), 性别 CHAR(2), 职业 char(10), 身份证号 CHAR(20) UNIQUE, 住址 char(10), 联系电话 char(20) ) go CREATE TABLE Book ( 图书编号 CHAR(10) UNIQUE, 书名 CHAR(20), 作者 CHAR(10), 类别 char(10), 出版社 char(20), 出版日期 char(10), 定价 real, 总数 int, 在管数 int, 出借数 int ) go CREATE TABLE kind (类别代号 char(2), 类别名称 char(10), ) go CREATE TABLE JS (借书证号 CHAR(15), 姓名 char(15), 图书编号 CHAR(10), 出借时间 smalldatetime, 已借数 int, 已借天数 int, 付款 real ) go insert into people values('0306102201','张三','男','厨师','330326198811113415','福州','13107615082') insert into people values('0306102202','李四','女','军人','330326198712113415','温州','0577-63683090') insert into people values('0306102203','王二','男','学生','330326198603113415','平阳','0591-83960187') insert into people values('0306102204','毛一','女','教师','330326198504113415','绍兴','0577-63626647') insert into people values('0306102205','陈五','女','司机','330326198405113415','北京','15908244879') insert into people values('0306102206','关羽','男','警察','330326192507093415','四川','13056845124') insert into people values('0306102207','张飞','男','警察','3303192505083215','四川','0577-63681510') insert into people values('0306102208','刘备','女','商人','3303192506033315','四川','15908256412') go insert into book values('G001','计算机','唐浩强','教育','北京大学出版社','2001-12-2',20.9,3,1,2) insert into book values('B001','大学生心理','张向荣','哲学','福建工程学院出版社','2002-6-5',12.3,2,2,0) insert into book values('A001','马克思','毛泽东','马克思主义','人民教育出版社','1999-9-9',40.5,1,1,0) insert into book values('G002','C语言设计','唐浩强','G','北京大学出版社','2005-9-6',32.3,4,2,2) insert into book values('O001','高等数学','华罗庚','数学','四川大学出版社','2007-8-5',22.2,4,3,1) insert into book values('H001','大学英语','李阳','语言','人民出版社','2003-3-2',15.6,2,2,0) insert into book values('U001','汽车修理','张三','交通','新世纪出版社','1996-8-9',66.6,1,1,0) insert into book values('Z001','美食大全','李四','综合性图书','新华出版社','2008-7-8',77.8,1,0,1) insert into book values('I0011','西游记（上）','吴承恩','文学','文学出版社','2006-3-6',45.2,2,1,1) insert into book values('I0012','西游记（下）','吴承恩','文学','文学出版社','2006-3-6',45.2,2,0,2) go insert into js values ('0306102201','张三','G001','2008-6-1',2,0,0) insert into js values('0306102202','李四','G002','2008-5-9',1,0,0) insert into js values('0306102205','陈五','I0012','2008-4-23',2,0,0) insert into js values('0306102201','张三','U001','2008-3-9',3,0,0) insert into js values('0306102201','张三','H001','2008-3-4',1,0,0) go insert into kind values('A','马克思主义') insert into kind values('B','哲学') insert into kind values('G','教育') insert into kind values('H','语言') insert into kind values('I','文学') insert into kind values('O','数学') insert into kind values('U','交通') insert into kind values('Z','综合性图书') create procedure search2 @no char(15) as select *from book where 图书编号=@no go create procedure insert2 @no char(10),@name char(20),@zuozhe char(10),@kind char(10),@time char(15),@chu char(20),@price real,@num int as insert into book values (@no,@name ,@zuozhe ,@kind,@time,@chu ,@price,@num,@num,0) go create procedure update2 @no char(10),@name char(20),@zuozhe char(10),@kind char(10),@chu char(20),@time char(15),@price real,@all int,@num1 int,@num2 int as update book set 书名=@name, 作者=@zuozhe, 类别=@kind,出版社=@chu,出版日期=@time,定价=@price,总数=@all ,在管数=@num1, 出借数=@num2 where 图书编号=@no go select *from book create procedure dele1 @no char(10) as delete from book where 图书编号=@no go create procedure dele2 @no char(5) as delete from kind where 类别代号=@no select *from kind create procedure update3 @no char(5),@name char(10) as update kind set 类别代号=@no,类别名称=@name where 类别代号=@no or 类别名称=@name create procedure insert3 @no char(4),@name char(10) as insert into kind values(@no,@name) create procedure panduan @bno char(10) as select *from book where 在管数<0 and 图书编号=@bno exec panduan 'G001' create procedure jieshu @pno char(15),@bno char(10),@num int,@name char(10) as set @name=(select 姓名 from people where 借书证号=@pno) insert into js values(@pno,@name,@bno, GetDate(),@num,0,0) update book set 在管数=在管数-@num,出借数=出借数+@num where 图书编号=@bno create procedure chuli @no char(10),@num int as delete from js where 图书编号=@no update book set 在管数=在管数+@num,出借数=出借数-@num where 图书编号=@no create procedure huanshu1 @pno char(15) as select book.图书编号 ,book.书名,book.作者, book.类别,book.出版日期 ,book.出版社 ,book.定价,js.已借数 from book,js where js.借书证号=@pno and book.图书编号=js.图书编号 create procedure dele4 @bno char(10) as delete from js where 图书编号=@bno and 已借数=1 create procedure update5 @no char(15),@num int as update js set 已借数=已借数-@num where 图书编号=@no create procedure search3 @no char(15) as select *from book where 书名=@no go create procedure search4 @no char(15) as select *from book where 作者=@no go create procedure search5 @no char(15) as select *from book where 类别=@no go create procedure search6 @no char(15) as select *from book where 出版社=@no go create procedure search7 @no char(15) as select *from people where 借书证号=@no go create procedure search8 @no char(15) as select *from people where 姓名=@no create procedure insert1 @no char(15),@name char(6),@sex char(2),@zhiye char(10),@shenfeng char(20),@addrise char(10),@phone char(20) as insert into people values(@no,@name,@sex,@zhiye,@shenfeng,@addrise,@phone) go create procedure dele5 @bno char(10) as delete from people where 借书证号=@bno create procedure search01 @no char(15) as update js set 已借天数=datediff(day,出借时间,getdate())，付款=1.5*已借数*datediff(day,出借时间,getdate()) where 借书证号=@no select *from js where 借书证号=@no create procedure search02 @no char(15) as update js set 已借天数=datediff(day,出借时间,getdate())，付款=1.5*已借数*datediff(day,出借时间,getdate()) where 姓名=@no select *from js where 姓名=@no