bookdb.sql

一个图书管理系统

use master
go

IF EXISTS (SELECT * 
	   FROM   master..sysdatabases 
	   WHERE  name = 'BookDB')
	DROP DATABASE BookDB
GO


--创建数据库
create database BookDB
on 
(
	name='Book_1',
	filename='c:\book_1.mdf',
	size=1,
	maxsize=10,
	filegrowth=1
),
filegroup filegroup1
(
	name='Book_2',
	filename='c:\book_2.mdf',
	size=1,
	maxsize=10,
	filegrowth=1
)
log on
(
	name='Book1_log',
	filename='c:\Book1_log.ldf',
	size=1,
	maxsize=unlimited,
	filegrowth=1
)
go
use BookDB
go

--创建用户表，登录用
create table T_User
(
	userName varchar(20) primary key not null, --用户名
	userPwd varchar(20) not null               --拥护密码
)

insert into T_User values('Admin','admin')
insert into T_User values('Tom','123456')
insert into T_User values('Jack','654321')
insert into T_User values('Water','watermima')

--出版社信息表publishInfo  
create table publishInfo
(
	pubID varchar(10) primary key,   --出版社ID
	pubName varchar(40),    --出版社名
	pubAdress varchar(50),           --出版社地址
	pubPhone varchar(20),            --出版社电话
	pubEmail varchar(20)             --出版社Email
)

insert into publishInfo values('pub-00001','电子工业出版社','四川成都','022-3657865','[e-mail]')
insert into publishInfo values('pub-00002','清华大学出版社','北京','010-36345865','[e-mail]')
insert into publishInfo values('pub-00003','人民大学出版社','北京','010-36655865','[e-mail]')

--创建图书信息表books
create table books
(
	bookId varchar(30) primary key,  --图书编号
	bookName varchar(50) not null,   --图书名
	bookType varchar(40) not null,   --图书类型
	bookPrice money,                 --图书单价
        bookSum int not null,            --图书总数量
	bookAuthor varchar(20) null,     --图书作者
	pubID varchar(10) foreign key references publishInfo(pubID),   --出版社ID
	bookTime datetime                --购书时间
)

insert into books values('HBWH00000001','Java 2核心技术(上)','计算机类',88.00,8,'TOM','pub-00001','2003-3-12')
insert into books values('HBWH00000002','Java 2核心技术(下)','计算机类',108.00,8,'TOM','pub-00001','2003-3-12')
insert into books values('HBWH00000003','Marketing','管理类',34.00,6,'Jack','pub-00002','2004-7-8')
insert into books values('HBWH00000004','钢铁是怎样炼成的','文学类',24.00,20,'Wsoton','pub-00002','2000-9-18')
insert into books values('HBWH00000005','临床医学','医学类',24.00,120,'海岩','pub-00001','2004-9-8')
insert into books values('HBWH00000006','管理学原理','管理类',54.00,120,'Rostion','pub-00003','2004-7-8')
insert into books values('HBWH00000007','会计学','医学类',36.00,210,'Jom Rice','pub-00003','2004-5-18')
insert into books values('HBWH00000008','新编英语教程','外语类',44.00,70,'Jary','pub-00003','2004-4-23')
insert into books values('HBWH00000009','C++','计算机类',74.00,170,'Hoston','pub-00002','2002-4-23')
insert into books values('HBWH00000010','ASP动态网页设计','计算机类',94.00,250,'Logoer','pub-00002','2001-4-23')

--创建顾客信息表Custormers
create table Custormers
(
	custID varchar(30) primary  key , --顾客ID
        custName varchar(30),             --顾客姓名
	custPhone varchar(20),            --顾客电话
	custEmail varchar(40),            --顾客Email	
	custSum   numeric(6,2)            --顾客总消费金额 
)

--创建消费信息表Cust_Books
create table Cust_Books
(
	custID varchar(30) foreign key references Custormers(custID), --顾客ID
	bookId varchar(30) foreign key references books(bookId),  --图书编号
	bookNumber int,             --购书数量
	buyTime datetime            --购书时间
)


--存储过程，用于插入用户登录信息数据
go
create procedure pro_Insert
@userName varchar(20),
@userPwd varchar(20)

as
	if exists(select * from T_User where userName=@userName)
		return 0
	else
		begin
			insert into T_User values(@userName,@userPwd)
			return 1
		end

go

--存储过程，用于插入消费信息数据 
create procedure pro_Cust_Books
@custID varchar(30),
@custName varchar(30),
@custPhone varchar(20),
@custEmail varchar(40),
@bookId varchar(30),
@bookNumber int,
@buyTime datetime
as
	declare @num int,@price money,@sum money
	select  @num=bookSum from books where bookId=@bookId
	select  @price=bookPrice from books where bookId=@bookId
	select  @sum=@price*@bookNumber
	if @num<@bookNumber
		return 0
        else 
           begin
		--begin tran
		insert into Custormers(custID,custName,custPhone,custEmail)  values(@custID,@custName,@custPhone,@custEmail)
		insert into Cust_Books values(@custID,@bookId,@bookNumber,@buyTime)
		update Custormers set custSum=@sum where custID=@custID
		return 1
		--commit tran
	   end

go
--创建触发器Tri_user_books
create trigger Tri_Cust_Books
on Cust_Books
for insert
as
	declare @bookNumber int,@bookId varchar(30)
	select @bookNumber=bookNumber from inserted 
	select @bookId=bookId from inserted	
	update books set bookSum=bookSum-@bookNumber where bookId=@bookId

go
exec pro_Cust_Books 'CUS-00000001','wj','027-45672343','[e-mail]','HBWH00000001',3,'2003-5-7'

go 
--创建存储过程，自动产生出版社编号
create procedure pro_pubID
	@pubID varchar(10) output
as
        declare @sum int
        select @pubID=pubID from publishInfo
        if @pubID is null      --当无记录时起始单号
              select  @pubID='pub-1'
          else
              begin 
	        select @pubID = max(pubID) from publishInfo 
	        select @sum=len(@pubID) 
	        select @pubID=substring(@pubID,5,@sum)
		select @pubID=
		  case
			when @pubID>=0 and @pubID<9 then 'pub-0000'+convert(varchar,@pubID+1)
			when @pubID>=9 and @pubID<99 then 'pub-000'+convert(varchar,@pubID+1)
			when @pubID>=99 and @pubID<999 then 'pub-00'+convert(varchar,@pubID+1)
			when @pubID>=999 and @pubID<9999 then 'pub-0'+convert(varchar,@pubID+1)
			when @pubID>=9999 and @pubID<99999 then 'pub-'+convert(varchar,@pubID+1)
		  end
                end
go 
--declare @out varchar(10)
--exec pro_pubID @out output
--select @out
--创建存储过程，自动产生出图书编号
go
create procedure pro_bookID
	@bookId varchar(30) output
as
        declare @sum int
        select @bookId=bookId from books
        if @bookId is null      --当无记录时起始单号
              select  @bookId='HBWH1'
          else
              begin 
	        select @bookId = max(bookId) from books 
	        select @sum=len(@bookId) 
	        select @bookId=substring(@bookId,5,@sum)
		select @bookId=
		  case
			when @bookId>=0 and @bookId<9 then 'HBWU0000000'+convert(varchar,@bookId+1)
			when @bookId>=9 and @bookId<99 then 'HBWU000000'+convert(varchar,@bookId+1)
			when @bookId>=99 and @bookId<999 then 'HBWU00000'+convert(varchar,@bookId+1)
			when @bookId>=999 and @bookId<9999 then 'HBWU0000'+convert(varchar,@bookId+1)
			when @bookId>=9999 and @bookId<99999 then 'HBWU000'+convert(varchar,@bookId+1)
			when @bookId>=99999 and @bookId<999999 then 'HBWU00'+convert(varchar,@bookId+1)
			when @bookId>=999999 and @bookId<9999999 then 'HBWU0'+convert(varchar,@bookId+1)
			when @bookId>=9999999 and @bookId<99999999 then 'HBWU'+convert(varchar,@bookId+1)
		  end
                end
go 
--declare @out varchar(30)
--exec pro_bookID @out output
--select @out
go
--创建存储过程，自动产生出顾客编号
create procedure pro_custID
	@custID varchar(30) output
as
        declare @sum int
        select @custID=custID from Custormers
        if @custID is null      --当无记录时起始单号
              select  @custID='HBWH1'
          else
              begin 
	        select @custID = max(custID) from Custormers 
	        select @sum=len(@custID) 
	        select @custID=substring(@custID,5,@sum)
		select @custID=
		  case
			when @custID>=0 and @custID<9 then 'CUS-0000000'+convert(varchar,@custID+1)
			when @custID>=9 and @custID<99 then 'CUS-000000'+convert(varchar,@custID+1)
			when @custID>=99 and @custID<999 then 'CUS-00000'+convert(varchar,@custID+1)
			when @custID>=999 and @custID<9999 then 'CUS-0000'+convert(varchar,@custID+1)
			when @custID>=9999 and @custID<99999 then 'CUS-U000'+convert(varchar,@custID+1)
			when @custID>=99999 and @custID<999999 then 'CUS-00'+convert(varchar,@custID+1)
			when @custID>=999999 and @custID<9999999 then 'CUS-0'+convert(varchar,@custID+1)
			when @custID>=9999999 and @custID<99999999 then 'CUS-'+convert(varchar,@custID+1)
		  end
                end
go
--declare @out varchar(30)
--exec pro_custID @out output
--select @out

--创建视图
create view view_Books
as
select b.bookType,b.bookName,b.bookAuthor,b.bookPrice,p.pubName,b.bookSum from publishInfo p join books b on p.pubID=b.pubID

go