新建
CREATE TABLE [dbo].[books](
[ids] [int] not null primary key identity ,—关键字， 自增
[totols] [int] , –总数
[price] [numeric](18, 0) , –价
[locat] varchar (200) ,–位置
[stat] [int] NULL,—状态
[kind] varchar(200),—种类
[bkname] varchar (200) ,—书名字
[note] varchar (200) ,–备注
[due] [date] —日期
);
插入一些数据
insert into books (totals,price,locat,stat,kind,bkname,note,due) values (3,28,’一层1′,1,’文学’,’三体’,”,’2021-10-15′)，
(2,20,’一层1′,1,’生活’,’川菜谱’,”,’2021-10-17′),
(5,20,’二层1′,1,’文学’,’三言’,”,’2021-10-17′);
等等
更改字段名
exec sp_rename ‘books.totols’,’totals’;
建一个数量明细表
CREATE TABLE bookqty(
ids int IDENTITY(1,1) NOT NULL,
totals int ,
[bkname] varchar(200)
)
触发器
想要书本数量更新为0状态自动变为0。更新books数量bookqty数量也同时更新.
插入books表新资料也同时插入bookqty
一般
update books set stat=0 where totols=0; 更新状态
insert into bookqty (totals,bkname) select totals,bkname from books 来更新bookqty表
在books加一个update触发器来
create trigger [dbo].[upstat]
on [dbo].[books]
after update
as
if update(totals) –有更新到totals字段
begin
update books set stat=0 where totals=0
update books set stat=1 where totals<>0
update bookqty
set totals=books.totals
from bookqty,books
where books.bkname=bookqty.bkname
end
go
当更新books数量 bookqty 就会同步更新以上两个内容。
在books加一个insert触发器
create trigger [dbo].[upqty]
on [dbo].[books]
after insert
as
begin
insert into bookqty(totals,bkname) select totals,bkname from books where bkname not in(select bkname from bookqty) –插入books内有但bookqty表内没有的
end
go
当插入books新资料bookqty就会同步更新内容。
视图
新建一个定义状态表
CREATE TABLE [dbo].[bstat](
[ids] [int] IDENTITY(1,1) NOT NULL,
[stat] [int] ,
[name] varchar(200)
)
添加数据 ,0为禁借，1为可借。
insert into bstat ( stat , name ) values (0,’禁借’)，
( 1,’可借’ );
创建视图，查询是否可借
create view qtyinv as
select bstat.name,books.bkname,books.totals from books
left join bstat on books.stat=bstat.stat;
select * from qtyinv
查每层总数。创建视图。
create view qtylocat as
select sum(totals) as total,LEFT(locat,2) as locat from books group by LEFT(locat,2)
go
类似，可以创建查每类的总数，不做说明。
函数
在书‘三言’记录的备注插入一个备注
update books set note=’全名：三言两拍，位置：一层1′ where bkname=’三言’;
写个函数可以取全名或位置的。
create function note_arg
(
@note nvarchar(max),— 参数 某字段内容
@k NVARCHAR(30) –某字符
)
returns nvarchar(max)
as
begin
declare @f bit=1
if CHARINDEX(@k,@note)<=0 return null –没有某字符返回null
while @f=1
begin
if CHARINDEX(@k,@note,2)>1–有就截取字符后面的内容
set @note=SUBSTRING(@note,CHARINDEX(@k,@note,2),LEN(@note))
else
set @f=0
end
while CHARINDEX(‘ ‘,@note)>0 –后面有两空格替换为一个空格
begin
set @note=REPLACE(@note,’ ‘,’ ‘)
end
set @note=REPLACE(@note,’：’,’:’) —替换后面的符号为,
set @note=REPLACE(@note,’，’,’,’)
set @note=REPLACE(@note,’ ,’,’,’)
set @note=REPLACE(@note,’;’,’,’)
set @note=REPLACE(@note,’ ;’,’,’)
set @note=REPLACE(@note,’|’,’,’)
set @note=REPLACE(@note,’ ‘,’,’)
if CHARINDEX(‘,’,@note)>1 —截取,前面的
set @note=SUBSTRING(@note,1,CHARINDEX(‘,’,@note)-1)
return REPLACE(@note,@k,”)
end
函数如上，可以截取某字符后和某符 号前的内容。
存储过程
写两个存储过程，实现加减书籍数量、
增加
CREATE proc addbook
@name nvarchar(30),–参数 书名
@i int —增加多少
as
declare @n int
if exists(select * from books where bkname=@name) –是否存在记录
begin
select @n=totals from books where bkname=@name
set @n=@n+@i
if @n<=20 –不能超过存放上限
begin
update books set totals=@n where bkname=@name
end
else
begin
RAISERROR(‘大于可存量!’,10,1)
end
end
else
begin
RAISERROR(‘不存在!’,10,1)
end
GO
减少，类似
CREATE proc reducebook
@name nvarchar(30),
@i int
as
declare @n int
if exists(select * from books where bkname=@name)
begin
select @n=totals from books where bkname=@name
if @n>=@i –不能超过库存
begin
set @n=@n-@i
update books set totals=@n where bkname=@name
end
else
begin
RAISERROR(‘大于库存!’,10,1)
end
end
else
begin
RAISERROR(‘不存在!’,10,1)
end
GO
