/*超市管理系統(tǒng)數(shù)據(jù)庫(kù)Market_Datebase*/

/*
建表
*/

--建立商品信息表
create table goods_table
( goods_no int primary key, --商品條形碼(編號(hào))
  goods_name char(20),
  goods_price char(10), 
  goods_num int  --存貨數(shù)量
)
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 --進(jìn)貨數(shù)量
)
select *from goods_sell_table

--建立員工信息表
create table employee_table
( employee_no int primary key, --員工編號(hào)
  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

/*
向表格插入數(shù)據(jù)
*/
--插入商品基本信息
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區(qū)');
insert into goods_position_table
values('西瓜','A區(qū)');
insert into goods_position_table
values('電池','B區(qū)XX欄');
insert into goods_position_table
values('牛奶','C區(qū)');
insert into goods_position_table
values('**洗衣粉','B區(qū)**欄');

--插入商品銷售情況
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','經(jīng)理','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

/*
存儲(chǔ)過程
*/
--進(jìn)貨存儲(chǔ)過程
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) --錄用時(shí)間
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



/**********

觸發(fā)器

**********/
--對(duì)刪除物品做限制

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

--對(duì)刪除人員限制

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