dm.sql

实现超市收银管理.能实现超市的正常收银情况........

create table Customer
( 	Cno char(9) primary key,
  	Cname char(20),
  	Csex  char(2) check(Csex in('男','女')),
  	Creg  date,
  	Cactive char(2) check(Cactive in('是','否')),
  	Cconsumption decimal(8,2)
);

insert into Customer values('200800001','张三','男',curdate(),'是',800);
insert into Customer values('200800002','李四','男',curdate(),'是',1000);
insert into Customer values('200800003','王五','女',curdate(),'是',900);

select * from Customer;

create table Goods
(	Gno char(9) primary key,
	Gname  char(20) not null,
	Gprice decimal(5,2),
	Gstorage smallint
);

insert into Goods values('000000001','方便面',2.00,200);
insert into Goods values('000000002','矿泉水',1.00,100);
insert into Goods values('000000003','衬衣',35.00,200);
insert into Goods values('000000004','洗发水',19.00,200);
insert into Goods values('000000005','电风扇',50.00,200);

select * from Goods;

create table Tradeinfo
(	Cno char(9),
	Gno char(9),
	Tquantity smallint,
	Ttime timestamp(0),
--	primary key( Cno, Gno),
	foreign key(Cno) references Customer(Cno),
	foreign key(Gno) references Goods(Gno) 
);

insert into Tradeinfo values('200800001','000000001',2,current_timestamp());
insert into Tradeinfo values('200800001','000000002',1,current_timestamp());
insert into Tradeinfo values('200800002','000000003',2,current_timestamp());
insert into Tradeinfo values('200800002','000000004',1,current_timestamp());
insert into Tradeinfo values('200800002','000000001',4,current_timestamp());
insert into Tradeinfo values('200800002','000000002',2,current_timestamp());

select * from Tradeinfo;

create table Cashier
(	Cno char(9) primary key,
	Cname char(20) not null,
	Csex char(2) check(Csex in('男','女')),
	Clogname char(20) unique,
	Clogpass char(20)  check(Clogpass like '______%')
);

insert into Cashier values('derp001','李平','女','liping','123456');
insert into Cashier values('derp002','赵婷','女','zhaoting','666666');
insert into Cashier values('derp003','李莫愁','女','limochou','88888888');

select * from Cashier;

create table Cashinfo
(	Cno char(9) not null,
	Crecv decimal(7,2),
	CtimeOn timestamp(0),
	CtimeOff timestamp(0),
	foreign key(Cno) references Cashier(Cno)
);

insert into Cashinfo(Cno,CtimeOn) values('derp001',current_timestamp());
update Cashinfo
set Crecv=638.00, CtimeOff=current_timestamp()
where Crecv is NULL and CtimeOff is NUll;

select * from Cashinfo;

create trigger Insert_Customer
before insert on Customer
for each row
begin
	:new.Creg := curdate();
end;

--drop trigger Insert_Customer ;

create trigger Insert_Trade
before insert on Tradeinfo
for each row
begin
	:new.Ttime := current_timestamp();
end;

--用户登录系统时插入新记录
create trigger Insert_CashinfoOn
before insert on Cashinfo
for each row
begin
	:new.CtimeOn := current_timestamp();
end;

--用户更新Crecv时（logout）
create trigger Insert_CashinfoOff
before update on Cashinfo
for each row
begin
	if :old.CtimeOff is null
	then :new.CtimeOff := current_timestamp();
	end if;
end;

--drop trigger Insert_CashinfoOff;

create view Query_Cashinfo(Qcno,Qcname,Qcrecv,Qctimeon,Qctimeoff)
as
select Cashinfo.Cno, Cname, Crecv, CtimeOn, CtimeOff
from Cashinfo,Cashier
where Cashinfo.Cno = Cashier.Cno;

--drop view Query_Cashinfo;
select * from Query_Cashinfo;

create view Query_Tradeinfo(Qcno,Qcname,Qgno,Qgname,Qttime,Qtquantity)
as
select Tradeinfo.Cno, Cname, Tradeinfo.Gno, Gname, Ttime, Tquantity
from Tradeinfo,Customer,Goods
where Tradeinfo.Cno = Customer.Cno and Tradeinfo.Gno = Goods.Gno;

--drop view Query_Tradeinfo;

select * from Query_Tradeinfo;
--drop table Cashinfo;
--drop table Cashier;
drop table Tradeinfo;
--drop table Goods;
--drop table Customer;