建库及表.sql

超市数据库管理系统：建库、触发器、事件、视图

create database 超市管理系统
on
( name=pos_dat,
     filename='E:\database\超市管理系统\pos_dat.mdf',
     size=5,
     maxsize=20,
     filegrowth=1)
LOG on
( name=pos_log,
     filename='E:\database\超市管理系统\pos_log.ldf',
     size=5,
     maxsize=20,
     filegrowth=1)

create table 部门
(
   部门编号 char(8) not null
   constraint PK_bno primary key,
   部门名称 char(4) not null
)
go

create table 商品信息
(
   商品编号 char(8) not null
  constraint PK_cno primary key,
   商品名称 varchar(20) not null ,
   所属类别 char(8) not null,
   数量 int not null,
   价格 money not null
)
go


create table 供应商信息
(
    供应商编号 char(8) not null
    constraint PK_dno primary key,
    供应商名称 char(8) not null,
    地址 varchar(20) not null,
    邮政编码 char(8) not null,
    电话号码 varchar(15) not null,
    税号 varchar(3) not null,
    银行帐号 varchar(20) not null,
    开户银行 char(8) not null,
    联系人 char(8) not null,
    备注  text null
)
go


create table 会员信息
(
  会员编号   char(8) not null
   constraint PK_eno primary key,
 姓名       char(6)not null,
 性别       char(2)  not null,
 身份证号    varchar(20) not null,
 消费总金额 money not null,
 积分      int not null,
 constraint  CK_hy check (性别 in ('男','女'))
)
Go


create table 员工信息
(
 员工编号 char(8) not null
   constraint PK_fno primary key,
 姓名 char(8) not null,
 性别 char(2) not null,
 职务 char(8) not null,
 口令 varchar(20) not null,
 权限级别 char(4) not null,
 身份证号 varchar(18) not null,
 所属部门编号 char(8) not null
  constraint FK_ano foreign key references 部门(部门编号),
  constraint CK_yg check (性别 in ('男','女'))
)
go


create table 入库信息
(
 入库编号 char(8) not null
  constraint PK_gno primary key,
  销售价格 money not null,
  入库日期 datetime not null,
  商品编号 char(8) not null
   constraint FK_bno foreign key references 商品信息(商品编号),
  业务员编号 char(8) not null
   constraint FK_cno foreign key references 员工信息(员工编号),
  计量单位 char(2) not null,
  入库价格 money not null,
  供应商编号 char(8) not null
   constraint FK_dno foreign key references 供应商信息(供应商编号),
  总金额 money not null,
  数量 int not null
 )
 go



create table 销售出货单主信息
(
 销售日期 datetime not null,
 总金额 money not null,
 是否现金 char(2)  not null ,  
 是否会员 char(2) ,
 会员编号 char(8) not null
constraint FK_eno foreign key references 会员信息(会员编号),
 收银员编号 char(8) not null
constraint FK_fno foreign key references 员工信息(员工编号),

constraint CK_zhuxx_sfhy check (是否会员 in ('是','否')),
constraint CK_zhuxx_sfxj check (是否现金 in ('是','否'))
)
go



create table 销售出货单子信息
(  
  商品编号 char(8) not null
  constraint FK_gno foreign key references 商品信息(商品编号),
  数量 int not null,
  单价 money not null,
  折扣比例 char(10) not null,
  金额 money not null,
)
go


create table 库存信息
(
     库存信息编号 char(8) not null
     constraint PK_pno primary key,
     商品编号  char(8) not null
     constraint FK_ino foreign key references 商品信息(商品编号),
     库存量 int not null
     )
     go


sp_addrole 'superusr',dbo
go

grant create table,create view,backup database ,backup log
to superusr
go

grant select,update,insert
on dbo.部门
to superusr
with grant option
go
grant select,update,insert
on dbo.商品信息
to superusr
with grant option
go
grant select,update,insert
on dbo.供应商信息
to superusr
with grant option
go

grant select,update,insert
on dbo.会员信息
to superusr
with grant option
go

grant select,update,insert
on dbo.员工信息
to superusr
with grant option
go

grant select,update,insert
on dbo.入库信息
to superusr
with grant option
go

grant select,update,insert
on dbo.销售出货单主信息
to superusr
with grant option
go

grant select,update,insert
on dbo.销售出货单子信息
to superusr
with grant option
go

grant select,update,insert
on dbo.库存信息
to superusr
with grant option
go


sp_addrole 'jl',dbo
go


grant select,update,insert
on dbo.员工信息
to jl
go


sp_addrole 'yg',dbo
go

grant select,update(口令)
on dbo.员工信息
to yg
go

sp_addrole 'hy',dbo
go

grant select
on dbo.会员信息
to hy
go


CREATE TRIGGER triger_sl 
ON 入库信息 
after  insert 
AS
  update 库存信息
  set 库存量=库存量+(select 数量 from inserted)
  where 商品编号=(select 商品编号 from inserted)


CREATE TRIGGER triger_kc 
ON  销售出货单子信息
after  delete
AS
 update 库存信息
  set 库存量=库存量-(select 数量 from deleted)
  where 商品编号=(select 商品编号 from deleted)


create proc hy_jf
@hy_ID char(8),
@hy_name char(6),
@jf int 
as
declare @ID char(8)
set @ID=@hy_ID
begin
select 会员编号,姓名,积分
from 会员信息
where 会员编号=@hy_ID
end


create proc yg_kl
@yg_ID char(8),
@oldkl varchar(20),
@newkl varchar(20)
as
declare @ID char(8)
set @ID=@yg_ID
begin
if not exists
(
select 员工编号,口令
from 员工信息
where 员工编号=@yg_ID and 口令=@oldkl 
)
print 'Error'
else
begin 
update 员工信息
set 口令=@newkl
where 员工编号=@yg_ID
print '密码已修改'
end
end

create proc kcts
@sp_ID char(8),
@sp_name varchar(20),
@kcl int
as
if(select sum(库存量)
     from 库存信息,商品信息
       where 库存信息.商品编号=@sp_ID and 库存信息.商品编号=商品信息.商品编号 and 商品信息.商品编号=@sp_name )<100
print @sp_name +'库存不足，需及时进货！'
if(select sum(库存量)
     from 库存信息,商品信息
       where 库存信息.商品编号=@sp_ID and 库存信息.商品编号=商品信息.商品编号 and 商品信息.商品编号=@sp_name )>500
print @sp_name +'商品库存积压，需促销！'

/*该视图为员工所在部门的信息*/
create view yg_bm(姓名,员工编号,部门名称)
as
select 姓名,员工编号,部门名称
from 员工信息,部门
where 员工信息.所属部门编号=部门.部门编号


CREATE VIEW SHANG_JIA
AS
SELECT 商品编号,商品名称,价格
FROM 商品信息