sql.sql

实现图书信息管理

USE XSBOOK
--建表
if exists(select name from sysobjects
          where name='XS' and type='U')
drop table XS
GO
create table XS
(借书证号 char(8) primary key not null,
 姓名 nvarchar(20) not null,
 专业名 nvarchar(20) not null,
 性别 char(2) check(性别 in('男','女')) not null,
 出生时间 datetime not null,
 借书量 int not null default 0
)

if exists(select name from sysobjects
          where name='BOOK' and type='U')
drop table BOOK
GO
create table BOOK
(ISBN varchar(16) primary key not null, 
 书名 nvarchar(50) not null,
 作者 nvarchar(20) not null,
 出版社 nvarchar(20) not null,
 价格 float not null,
 复本量 int not null,
 库存量 int not null
 )

if exists(select name from sysobjects
          where name='JY' and type='U')
drop table JY
GO
create table JY
(借书证号 char(8) not null,
 ISBN varchar(16) not null,
 索书号 varchar(10) primary key not null,
 借书时间 datetime not null
)

if exists(select name from sysobjects
          where name='JYLS' and type='U')
drop table JYLS
GO
create table JYLS
(借书证号 char(8) not null,
 ISBN varchar(16) not null,
 索书号 varchar(10) not null,
 借书时间 datetime primary key not null,
 还书时间 datetime not null
)

if exists(select name from sysobjects
          where name='Users' and type='U')
drop table Users
GO
create table Users
(用户名 nvarchar(20) not null,
 密码 varchar(20) not null
)
--建表完成


--存储过程
if exists(select name from sysobjects
          where name='xs_insert' and type='p')
drop proc xs_insert
GO
create proc xs_insert  --向XS表中插入一条记录
       @jszh char(8),@name nvarchar(8),@major nvarchar(20),@sex char(2),@birth datetime,@num int
as
  insert into XS(借书证号,姓名,专业名,性别,出生时间,借书量)
  values(@jszh,@name,@major,@sex,@birth,@num)
go

if exists(select name from sysobjects
          where name='xs_update' and type='p')
drop proc xs_update
GO
create procedure xs_update   --在XS表中修改一条记录
       @jszh char(8),@name nvarchar(8),@major nvarchar(20),@sex char(2),@birth datetime,@num int
as
  update XS set 姓名=@name,专业名=@major,性别=@sex,出生时间=@birth,借书量=@num
  where 借书证号=@jszh
go

if exists(select name from sysobjects
          where name='xs_delete' and type='p')
drop proc xs_delete
go
create procedure xs_delete   --在XS表中删除一条记录
       @jszh char(8)
as
  delete 
  from XS
  where 借书证号=@jszh
go

if exists(select name from sysobjects
          where name='book_insert' and type='p')
drop proc book_insert
go
create procedure book_insert   --向BOOK表中插入一条记录
       @isbn varchar(16),@name nvarchar(50),@writer nvarchar(20),@cbs nvarchar(20),@price float,@fbl int ,@kcl int
as
  insert into BOOK(ISBN,书名,作者,出版社,价格,复本量,库存量)
  values(@isbn,@name,@writer,@cbs,@price,@fbl,@kcl)
go

if exists(select name from sysobjects
          where name='book_update' and type='p')
drop proc book_update
go
create procedure book_update   --向BOOK表中修改一条记录
       @isbn varchar(16),@name nvarchar(50),@writer nvarchar(20),@cbs nvarchar(20),@price float,@fbl int ,@kcl int
as
  update BOOK
  set 书名=@name,作者=@writer,出版社=@cbs,价格=@price,复本量=@fbl,库存量=@kcl
  where ISBN=@isbn
go


if exists(select name from sysobjects
          where name='book_delete' and type='p')
drop proc book_delete
go
create procedure book_delete   --向BOOK表中删除一条记录
       @isbn varchar(16)
as
  delete
  from BOOK
  where ISBN=@isbn
go

if exists(select name from sysobjects
          where name='jy_insert' and type='p')
drop proc jy_insert
go
create procedure jy_insert   --向JY表中插入一条记录
       @jszh char(8),@isbn varchar(16),@ssh varchar(10)
as
  declare @jssj datetime
  set @jssj=getdate()
  insert into jy(借书证号,ISBN,索书号,借书时间)
  values(@jszh,@isbn,@ssh,@jssj)
go

if exists(select name from sysobjects
          where name='jy_delete' and type='p')
drop proc jy_delete
go
create procedure jy_delete   --向JY表中删除一条记录，同时向JYLS表插入一条记录
       @ssh varchar(10)
as
  declare @jszh char(8),@isbn varchar(16),@hssj datetime,@jssj datetime
  if exists(select * from JY where 索书号=@ssh)
  begin
       select @jszh=借书证号,@isbn=ISBN,@jssj=借书时间 from JY where 索书号=@ssh
       set @hssj=getdate()
       insert into JYLS
              values(@jszh,@isbn,@ssh,@jssj,@hssj)
       delete from JY
              where 索书号=@ssh 
  end
go


if exists(select name from sysobjects
          where name='user_insert' and type='p')
drop proc user_insert
go
create procedure user_insert    --向users表插入一条记录
       @name nvarchar(20),@pwd varchar(20)
as
  insert into users(用户名,密码)
  values(@name,@pwd)
go


if exists(select name from sysobjects
          where name='user_update' and type='p')
drop proc user_update
go
create procedure user_update   --向users表修改密码
       @pwd varchar(20)
as
  update users
  set 密码=@pwd
go
--存储过程完成



--触发器
if exists(select name from sysobjects
          where name='tjy_insert' and type='tr')
drop trigger tjy_insert
go
create trigger tjy_insert on JY   --对JY表定义的INSERT触发器
     for insert as
     begin
          update XS
          set 借书量=借书量+1
          where XS.借书证号 in (select inserted.借书证号 from inserted)
          update BOOK
          set 库存量=库存量-1
          where BOOK.ISBN in (select inserted.ISBN from inserted)
     end
go

if exists(select name from sysobjects
          where name='tjy_delete' and type='tr')
drop trigger tjy_delete
go
create trigger tjy_delete on JY    --对JY表定义的DELETE触发器
     for delete as
     begin
          update XS
          set 借书量=借书量-1
          where XS.借书证号 in (select deleted.借书证号 from deleted)
          update BOOK
          set 库存量=库存量+1
          where BOOK.ISBN in (select deleted.ISBN from deleted)
     end
go
--触发器完成