create table vip
(
vno varchar(20) primary key,
vname varchar(20) NOT NULL,
vphone varchar(11) unique not null,
vtype varchar(10) check(vtype in('初级会员','中级会员','高级会员'))
);
CREATE PROCEDURE vinsert
@vno varchar(20)
,@vname varchar(20)
,@vphone varchar(11)
,@vtype varchar(10)
AS
INSERT INTO vip(
vno,vname,vphone,vtype
)
VALUES(
@vno,@vname,@vphone,@vtype
)
GO
EXECUTE vinsert '0106','项兆坤','18638007673','中级会员'
CREATE PROCEDURE vupdate
@vno varchar(20)
,@vname varchar(20)
,@vphone varchar(11)
,@vtype varchar(10)
AS
UPDATE vip SET
vname=@vname,vphone=@vphone,vtype=@vtype
WHERE
vno=@vno
GO
EXECUTE vupdate '0106','项坤','18638007673','中级会员'
CREATE PROCEDURE vselect
@vno VARCHAR(20)
AS
SELECT * FROM vip WHERE vno= @vno
GO
EXECUTE vselect '0106'
CREATE PROCEDURE vdelete
@vno VARCHAR(20)
AS
DELETE vip
WHERE
vno= @vno
GO
EXECUTE vdelete '0106'
CREATE PROCEDURE vvague
@message varchar(20)
AS
select * from vip where vno like '%'+@message+'%' or
vname like '%'+@message+'%' or vphone like '%'+@message+'%' or vtype like '%'+@message+'%'
GO
EXECUTE vvague '98'
create table commodity
(
cno varchar(20) primary key,
cname varchar(20) NOT NULL,
cprice money NOT NULL,
cstock int NOT NULL,
ccount float ,
cfirm varchar(20)
);
ALTER TABLE commodity ADD CONSTRAINT 折扣 check(ccount in(1,0.95,0.9,0.85,0.8,0.75))
CREATE PROCEDURE cinsert
@cno varchar(20)
,@cname varchar(20)
,@cprice money
,@cstock float
,@ccount float
,@cfirm varchar(20)
AS
INSERT INTO commodity (
cno,cname,cprice,cstock,ccount,cfirm
)
VALUES(
@cno,@cname,@cprice,@cstock,@ccount,@cfirm
)
GO
EXECUTE cinsert '001','小鸡腿',2.00,20,1,'AAA'
CREATE PROCEDURE cupdate
@cno varchar(20)
,@cname varchar(20)
,@cprice money
,@cstock float
,@ccount float
,@cfirm varchar(20)
AS
UPDATE commodity SET
cname=@cname,cprice=@cprice,cstock=@cstock,ccount=@ccount,cfirm=@cfirm
WHERE
cno=@cno
GO
EXECUTE cupdate '001','小鸡腿',2,16,1,'AAA'
CREATE PROCEDURE cselect
@cno VARCHAR(20)
AS
SELECT * FROM commodity WHERE cno= @cno
GO
EXECUTE cselect '005'
CREATE PROCEDURE cdelete
@cno VARCHAR(20)
AS
DELETE commodity
WHERE
cno= @cno
GO
EXECUTE cdelete '001'
CREATE PROCEDURE cvague
@message varchar(20)
AS
select * from commodity where cno like '%'+@message+'%' or
cname like '%'+@message+'%' or cprice like '%'+@message+'%' or cstock like '%'+@message+'%'
or cfirm like '%'+@message+'%'
GO
EXECUTE cvague '2'
create procedure cselectall
AS
select * from commodity
Go
create procedure cselectbyname
@cname varchar(20)
AS
select * from commodity where cname=@cname
Go
EXECUTE cselectbyname '卫龙'
create table shoppoing
(id int identity(1,1) primary key not null,
sno varchar(20),
cno varchar(20),
cname varchar(20),
cnumber int,
cprice float,
ccount float,
cdate datetime
)
create procedure insertshop
@sno varchar(20),
@cno varchar(20),
@cname varchar(20),
@cnumber int,
@cprice float,
@ccount float,
@cdate datetime
AS
insert into shoppoing values(@sno,@cno,@cname,@cnumber,@cprice,@ccount,@cdate)
GO
create procedure chakanshop
@no varchar(20)
AS
select * from shoppoing where sno=@no
Go
create procedure cselectno
as
select MAX(sno) from shoppoing
go
create procedure checkvip
@phone varchar(11)
AS
select vtype from vip where vphone=@phone
GO
DECLARE @TYPE1 varchar(10)
EXECUTE checkvip '13574750929',@TYPE1 output
SELECT @TYPE1 类型
create table orders
(
ono varchar(20),
cno varchar(20),
cname varchar(20),
cnumber int,
cprice float,
ccount float,
odate datetime
)
create procedure insertorder
@ono varchar(20),
@cno varchar(20),
@cname varchar(20),
@cnumber int,
@cprice float,
@ccount float,
@odate datetime
AS
insert into orders values(@ono,@cno,@cname,@cnumber,@cprice,@ccount,@odate)
GO
create procedure getstock
@cno float
AS
select cstock from commodity where cno=@cno
GO
create trigger updatestock1 on orders
after insert
as
declare @cno varchar(20),@cnumber varchar(20)
select @cno=cno,@cnumber=cnumber from inserted
update commodity set cstock=cstock-@cnumber where cno=@cno
go
create procedure shaohuo
AS
select * from commodity where cstock>0 and cstock<=5
GO
create procedure quehuo
AS
select * from commodity where cstock=0
GO
create procedure guosheng
AS
select * from commodity where cstock>=20
GO
create procedure jinhuojihua
AS
select * from commodity where cstock between 0 and 5
GO
create table jinhuo
(
jno varchar(20) not null,
jname varchar(20) not null,
jnumber int not null,
jdate datetime
)
create procedure querenjinhuo
@jno varchar(20) ,
@jname varchar(20) ,
@jnumber int,
@jdate datetime
AS
insert into jinhuo values(@jno,@jname,@jnumber,@jdate)
GO
create trigger updatestock2 on jinhuo
after insert
as
declare @jno varchar(20),@jnumber varchar(20)
select @jno=jno,@jnumber=jnumber from inserted
update commodity set cstock=cstock+@jnumber where cno=@jno
go
create procedure ccountcommodity
AS
select * from commodity where ccount<1
Go
execute ccountcommodity
create procedure sell
AS
select cno,cname,SUM(cnumber) 销量 from orders
group by cno,cname
order by 销量 desc
GO
execute sell
CREATE PROCEDURE dayaccount
@ddate datetime
AS
SELECT cno,cname,SUM(cnumber) 销量 FROM orders WHERE replace(convert(char(10),odate,120),'-0','-') = replace(convert(char(10),@ddate,120),'-0','-')
group by cno,cname
order by cno
GO
execute dayaccount'2018-01-01'
/*
create procedure monthaccount
@mmdate varchar(12)
select year()
*/
