超市管理系统.sql

使用MFC界面连SQL2000实现管理操作

/*超市管理系统数据库Market_Datebase*/

/*
建表
*/

--建立商品信息表
create table goods_table
( goods_no int primary key, --商品条形码(编号)
  goods_name char(20),
  goods_price char(10), 
  goods_num int  --存货数量
)
select * from goods_table

--建立商品存放位置表
create table goods_position_table
( goods_name char(20),
  goods_position char(20)
)
select * from goods_position_table

--商品销售情况表
create table goods_sell_table
( goods_no int, 
  goods_import int --进货数量
)
select *from goods_sell_table

--建立员工信息表
create table employee_table
( employee_no int primary key, --员工编号
  employee_name char(10),
  employee_age int,
  employee_workage int
)
select * from employee_table

--建立员工职位信息表
create table employee_job_table
( employee_no int,
  employee_position char(20),  --职位
  employee_pay  int --薪水
)
select * from employee_job_table

/*
建视图
*/
--商品基本信息视图
create view goods_view
as
select  goods_no,goods_name,goods_price,goods_num
from goods_table

select * from goods_view

--商品位置视图
create view goods_position_view
as
select goods_name,goods_position
from goods_position_table

select *from goods_position_view

--建立商品销售情况视图
create view goods_sell_view
as
select goods_name,goods_import
from goods_table,goods_sell_table
where goods_table.goods_no=goods_sell_table.goods_no

select * from goods_sell_view

--建立员工信息视图
create view employee_view
as
select employee_no,employee_name,employee_age,employee_workage
from employee_table
select *from employee_view

--建立员工职位信息视图
create view employee_job_view
as
select employee_no,employee_position,employee_pay
from employee_job_table

select *from employee_job_view

/*
向表格插入数据
*/
--插入商品基本信息
insert into goods_table
values('0','苹果','5.8','1000');
insert into goods_table
values('1','西瓜','1.8','2000');
insert into goods_table
values('2','电池','2','1200');
insert into goods_table
values('3','牛奶','48','500');
insert into goods_table
values('4','**洗衣粉','15','957');

--插入商品摆放位置
insert into goods_position_table
values('苹果','A区');
insert into goods_position_table
values('西瓜','A区');
insert into goods_position_table
values('电池','B区XX栏');
insert into goods_position_table
values('牛奶','C区');
insert into goods_position_table
values('**洗衣粉','B区**栏');

--插入商品销售情况
insert into goods_sell_table
values('1','1500');
insert into goods_sell_table
values('2','3000');
insert into goods_sell_table
values('3','1500');
insert into goods_sell_table
values('4','1500');

--插入员工基本信息
insert into employee_table
values('1001','张三','28','6');
insert into employee_table
values('1002','李四','35','12');
insert into employee_table
values('1003','王五','21','1');
insert into employee_table
values('1004','徐七','28','2');

--插入员工职位信息
insert into employee_job_table
values('1001','经理','5000');
insert into employee_job_table
values('1002','主管','4000');
insert into employee_job_table
values('1003','员工','2500');
insert into employee_job_table
values('1004','员工','2500');

select * from goods_table
select * from goods_position_table
select * from goods_sell_table
select * from employee_table
select * from employee_job_table

/*
存储过程
*/
--进货存储过程
create proc add_goods

@no int,
@import int

as
if @no is null or @import is null 
begin
raiserror('null values are not allowed',14,1)
return
end


update goods_table
set goods_num=goods_num+@import
where goods_no=@no

--增加员工过程
create proc add_employee

@no int ,
@name char(20),
@age int,
@workage int

as
if  @no is null or @name is null or @age is null or @workage is null 
begin
raiserror('null values are not allowed',14,1)
return
end
insert into employee_table
values(@no,@name,@age,@workage)
update employee_table
set employee_no=@no,employee_name=@name,employee_age=@age,
employee_workage=@workage
 

--修改商品信息
create proc alter_goods
@no int,
@name char(20),
@price char(10),
@num int

as

if @no is null or @name is null or @price is null or @num is null
begin
  raiserror('null values are not allowed',14,1)
  return
end
update goods_table
set goods_name=@name,goods_price=@price,goods_num=@num
where goods_no=@no

--修改员工信息

create proc alter_employee
@no int,
@name char(20),
@age int,
@workage int

as

if @no is null or @name is null or @age is null or @workage is null
begin
  raiserror('null values are not allowed',14,1)
  return
end
update employee_table
set employee_name=@name,employee_age=@age,employee_workage=@workage
where employee_no=@no
drop proc alter_employee
select * from employee_table

--添加商品信息
create proc add_goods_infor
@produce_position char(20),
@produce_day char(20)
as
if @produce_position is null or @produce_day is null
begin
  raiserror('null values are not allowed',14,1)
  return
end 
insert into goods_position_table()
values(@produce_position,@produce_day)

exec  add_goods_infor 'xxx','ccc'
select * from goods_position_table
drop proc add_goods_infor

--添加人员信息
create proc add_employee_infor
@employee_mark char(10),
@employee_admitday char(10) --录用时间
as
if @employee_mark is null or @employee_admitday is null
begin
  raiserror('null values are not allowed',14,1)
  return
end
insert into employee_table('employee_mark','employee_admitday')
values(@employee_mark ,@employee_admitday)

--删除商品
create proc del_goods_infor

@name  char(20)
as
if  @name is null
begin
  raiserror('null values are not allowed',14,1)
  return
end
delete from goods_table 
where goods_name=@name
select *from goods_table
drop proc del_goods_infor

--删除人员信息

create proc del_employee_infor

@name char(20)
as
if @name is null
begin
  raiserror('null values are not allowed',14,1)
  return
end
delete from employee_table 
where  employee_name=@name
select *from employee_table

drop proc del_employee_infor
--清除所有商品
create proc del_all_goods
@del_goods char(20)
as
if @del_goods='clear_goods'


--EXECUTE sp_msforeachtable 'delete from goods_table'
--EXECUTE sp_msforeachtable 'delete from goods_position_table'

--EXECUTE sp_msforeachtable 'goods_sell_table'
delete from goods_table
delete from goods_position_table
delete from goods_sell_table

select *from  goods_table
drop  proc del_all_goods
--清除所有员工
create proc del_all_employee
@del_employee char(20)
as
if @del_employee='clear_employee'

drop table employee_table
drop table employee_job_table

select *from employee_table
drop proc del_all_employee
--查询物品
create proc lookup_goods

@name char(20)
as

if @name is null
begin
raiserror('null values are not allowed',14,1)
return
end
select * from goods_table
where goods_name=@name



/**********

触发器

**********/
--对删除物品做限制

create trigger limit_goods_del on goods_table

for delete
as
if(select count(*) from deleted)>1
begin
  raiserror('You cannot delete more than one student at a time.',16,1)
  rollback
end

--对删除人员限制

create trigger limit_employee_del on employee_table

for delete
as
if(select count(*) from deleted)>1
begin
  raiserror('You cannot delete more than one student at a time.',16,1)
  rollback
end