超市商品管理系统后台数据库sql实现
/*后台数据库“超市管理系统”的创建*/
create database 超市商品管理系统
on
primary (
name=chaoshi_data,
filename="D:\超市商品管理系统\后台数据库\chaoshi.mdf",
size=50mb)
log on (
name=chaoshi_log,
filename="D:\超市商品管理系统\后台数据库\chaoshi.ldf",
size=15mb)
go
use 超市商品管理系统
go
/*商品供应商信息表products的创建*/
create table products (
供应商编号 char(5) not null,
供应商名称 varchar(50) not null unique,
供应商电话 varchar(12) not null,
供应商地址 varchar(50) not null,
供应商备注 varchar(50),
constraint PK_products primary key (供应商编号)
)
/*超市营业员信息表employees的创建*/
create table employees (
职工编号 char(3) not null,
姓名 varchar(20) not null,
性别 char(4) not null,
联系电话 varchar(12) not null,
基本工资 money not null,
销售提成 float not null,
出生日期 datetime not null,
雇佣日期 datetime not null,
住址 varchar(50) not null,
constraint CK_employees check (性别='男' or 性别='女'),
constraint PK_employees primary key (职工编号),
)
alter table employees
add constraint moren1_employees default 0.1 for 销售提成
/*同类商品表“sameshangpin”的创建*/
create table sameshangpin (
同类商品编号 char(4) not null unique,
商品名称 varchar(20) not null,
供应商编号 char(5) not null,
售价 money not null,
库存数 int not null,
报警库存数 int not null,
constraint PK_sameshangpin primary key (商品名称),
constraint FK_sameshangpin foreign key (供应商编号) references products(供应商编号)
)
alter table sameshangpin
add constraint moren1_sameshangpin default 1 for 报警库存数
alter table sameshangpin
add constraint moren2_sanmeshangpin default 0 for 库存数
/*库存商品信息表“shangpin”的创建,需要将其中的字段“商品名称”创建一个对应与“sameshangpin”表的主键“商品名称”的外键约束*/
create table shangpin (
商品编号 char(8) not null,
商品名称 varchar(20) not null,
进价 money not null,
入库日期 datetime not null,
constraint PK_shangpin primary key (商品编号),
constraint FK_shangpin foreign key (商品名称) references sameshangpin(商品名称)
)
alter table shangpin
add constraint moren1_shangpin default getdate() for 入库日期
/*销售商品表“sales”的创建*/
create table sales (
商品编号 char(8) not null,
商品名称 varchar(20),
营业员编号 char(3) not null,
进价 money,
真实售价 money,
入库日期 datetime,
出库日期 datetime,
constraint PK_sales primary key (商品编号),
constraint FK_sales foreign key (营业员编号) references employees(职工编号),
)
alter table sales
add constraint moren1_sales default getdate() for 出库日期
/*销售业绩表"salesyeji"的创建*/
create table salesyeji (
同类商品编号 char(4) not n
ull,
出库日期 datetime not null,
商品名称 varchar(20) not null,
销售数量 int not null,
成本总额 money not null,
销售总额 money not null,
利润总额 as 销售总额-成本总额,
constraint PK_salesyeji primary key (出库日期,商品名称),
constraint FK_salesyeji foreign key (商品名称) references sameshangpin(商品名称)
)
/*个人销售业绩表“personyeji”的创建*/
create table personyeji (
职工编号 char(3) not null,
销售日期 datetime not null,
成本总额 money not null,
销售总额 money not null,
利润总额 as 销售总额-成本总额,
constraint PK_personyeji primary key (职工编号,销售日期),
constraint FK_personyeji foreign key (职工编号) references employees(职工编号)
)
/*创建触发器tr1，用与向库存shangpin表中加入新的商品时，同类商品表sameshangpin中相应的同类商品库存数加1*/
CREATE TRIGGER tr1
ON shangpin
FOR insert
AS
begin
update sameshangpin
set 库存数=库存数+1
where sameshangpin.商品名称 like (select 商品名称 from inserted)
end
GO
/*创建触发器tr2，用与向库存shangpin表中删除商品（即已经卖出）时，同类商品表sameshangpin中相应的同类商品库存数减1*/
CREATE TRIGGER tr2
ON shangpin
FOR delete
AS
begin
update sameshangpin
set 库存数=库存数-1
where sameshangpin.商品名称 like (select 商品名称 from deleted)
end
GO
/*创建触发器tr4，用与更新库存商品信息表shangpin的外键'商品名称'时，库存同类商品表sameshangpin的对应的库存数也自动更新*/
CREATE TRIGGER tr4
ON shangpin
FOR update
AS
begin
declare @mingchen1 varchar(20)
declare @mingchen2 varchar(20)
if update(商品名称)
begin
if exists (select * from deleted)
begin
select @mingchen1=商品名称 from deleted
select @mingchen2=商品名称 from inserted
update sameshangpin
set 库存数=库存数-1
where 商品名称=@mingchen1
update sameshangpin
set 库存数=库存数+1
where 商品名称=@mingchen2
end
end
end
go
/*创建触发器tr3，用与向sales表中添加商品（即已经卖出）时，商品表shangpin中相应的商品删除,删除的信息进入销售表sales*/
CREATE TRIGGER tr3
ON sales
FOR insert
AS
begin
declare @bianhao char(8)
declare @mingchen varchar(20)
declare @jinjia money
declare @shoujia money
declare @riqi datetime
select @bianhao=商品编号 from inserted
if not exists (select * from shangpin where 商品编号=@bianhao)
rollback transaction
else
begin
select @mingchen=商品名称 from shangpin where 商品编号=@bianhao
select @jinjia=进价 from shangpin where 商品编号=@bianhao
select @shoujia=售价 from sameshangpin where 商品名称=@mingchen
select @riqi=入库日期 from shangpin where 商品编号=@bianhao
update sales
set 商品名称=@mingchen
where 商品编号=@bianhao
update sales
set 进价=@jinjia
where 商品编号=@bianh
ao
update sales
set 真实售价=@shoujia
where 商品编号=@bianhao
update sales
set 入库日期=@riqi
where 商品编号=@bianhao
delete from shangpin where 商品编号=@bianhao
/*以下用与向sales表中添加商品（即已经卖出）后，销售业绩表salesyeji中相应的那1天的卖出商品卖出总额变动*/
declare @chukuriqi datetime
declare @tonglei char(4)
declare @nian int
declare @yue int
declare @ri int
select @chukuriqi=出库日期 from sales where 商品编号=@bianhao
select @tonglei=同类商品编号 from sameshangpin where 商品名称=@mingchen
set @nian=year(@chukuriqi)
set @yue=month(@chukuriqi)
set @ri=day(@chukuriqi)
if not exists(select * from salesyeji where 商品名称=@mingchen and year(出库日期)=@nian and month(出库日期)=@yue and day(出库日期)=@ri )
begin
insert into salesyeji (同类商品编号,出库日期,商品名称,销售数量,成本总额,销售总额)
values (@tonglei,@chukuriqi,@mingchen,1,@jinjia,@shoujia)
end
else
begin
update salesyeji
set 出库日期=@chukuriqi,
销售数量=销售数量+1,
成本总额=成本总额+@jinjia,
销售总额=销售总额+@shoujia
where 商品名称=@mingchen and year(出库日期)=@nian and month(出库日期)=@yue and day(出库日期)=@ri
end
/*以下用与向sales表中添加商品（即已经卖出）后,个人销售业绩表personyeji中相应的那1天的卖出商品卖出总额变动*/
declare @sno char(3)
select @sno=营业员编号 from sales where 商品编号=@bianhao
if not exists(select * from personyeji where 职工编号=@sno and year(销售日期)=@nian and month(销售日期)=@yue and day(销售日期)=@ri )
begin
insert into personyeji (职工编号,销售日期,成本总额,销售总额)
values (@sno,@chukuriqi,@jinjia,@shoujia)
end
else
begin
update personyeji
set 销售日期=@chukuriqi,
成本总额=成本总额+@jinjia,
销售总额=销售总额+@shoujia
where 职工编号=@sno and year(销售日期)=@nian and month(销售日期)=@yue and day(销售日期)=@ri
end
end
end
GO
/*创建用户登陆表*/
create table denglu (
登陆帐号 varchar(32) not null,
密码 varchar(7) not null,
constraint PK_denglu primary key (登陆帐号)
)