代码

create datebase fs
on(name=flowershop,size=3,filename=’d:\flo.mbf’)
log on(name=flowershop_log,size=1, filename=’d:\flo_log.ldf’)

create table flower
(
name char(20) primary key,
price float ,
flowerlanguage char(20));

create table flowermarket
(
marketno char(10) primary key,
marketname char(20) ,
marketadd char(50) );

create table flowershop
(
shopno char(10) primary key,
shopname char(20),
shoptell char(20),
shopadd char(50));

create table salesman
(
salesmanno char(10) primary key,
salesmanname char(20),
wage float,
shopno char(10),
foreign key(shopno) references flowershop(shopno));

create table huadianxiaoshou
(
shopno char(10),
name char(20),
xiaoshoue int,
primary key(shopno,name),
foreign key(shopno) references flowershop(shopno),
foreign key(name) references flower(name));

create table huadiancaigou
(
marketno char(10),
shopno char(10),
primary key (marketno,shopno),
foreign key(marketno) references flowermarket(marketno),
foreign key(shopno) references flowershop(shopno));


视图：
create view salesa
as
SELECT dbo.salesman.salesmanno AS 店员编号, dbo.salesman.salesmanname AS 店员名, dbo.salesman.wage AS 工资, dbo.flowershop.shopname AS 所属花店
FROM dbo.flowershop,dbo.salesman ON dbo.flowershop.shopno = dbo.salesman.shopno
WHERE (dbo.salesman.shopno = 'HB09')


create view xiaoshou
as
SELECT dbo.huadianxiaoshou.shopno, dbo.flowershop.shopname, https://www.360docs.net/doc/3a14587050.html, AS flowername, dbo.huadianxiaoshou.xiaoshoue
FROM dbo.flowershop,dbo.huadianxiaoshou ON dbo.flowershop.shopno = dbo.huadianxiaoshou.shopno
WHERE (dbo.huadianxiaoshou.shopno = 'HB06')

create view xiaoshoua
as
SELECT dbo.huadianxiaoshou.shopno, dbo.flowershop.shopname, https://www.360docs.net/doc/3a14587050.html, AS flowername, dbo.huadianxiaoshou.xiaoshoue
FROM dbo.flowershop,dbo.huadianxiaoshou ON dbo.flowershop.shopno = dbo.huadianxiaoshou.shopno
WHERE (dbo.huadianxiaoshou.shopno = 'HB05')

create view xiaoshoub
as
SELECT dbo.huadianxiaoshou.shopno, dbo.flowershop.shopname, https://www.360docs.net/doc/3a14587050.html, AS flowername, dbo.huadianxiaoshou.xiaoshoue
FROM dbo.flowershop,dbo.huadianxiaoshou ON dbo.flowershop.shopno = dbo.huadianxiaoshou.shopno
WHERE (dbo.huadianxiaoshou.shopno = 'HB09')


索引：
CREATE index 工资高于2500的店员
on dbo.salesman(wage asc)
where wage>=2500;

create index 编号为HB09的花店销售信息
on dbo.huadianxiaoshou(xiaoshoue asc)
where shopno='HB09';


存储过程：
create procedure 计算花店销量
@zonge float output
as
select @zonge=sum(xiaoshoue) from dbo.huadianxiaoshou where shopno='HB05'
go
declare @zonge float
execute 计算花店销量 @zonge output
select @zonge as '编号为HB05的花店的当前销售总量'
go

create procedure 计算鲜花销量
@zonge float output
as
select @zonge=sum(xiaoshoue) from dbo.huadianxiaoshou where name='水仙'
go
decl

are @zonge float
execute 计算鲜花销量 @zonge output
select @zonge as '花名为水仙的花的销售总量'
go

create procedure 计算单价超过10元的鲜花数
@zonge float output
as
select @zonge=count(*) from dbo.flower where price>=10
go
declare @zonge float
execute 计算单价超过10元的鲜花数 @zonge output
select @zonge as '单价超过10元的鲜花数'
go


触发器：
create trigger insert_鲜花
on dbo.flower
for insert
as
if exists(select price from dbo.flower where price<0)
begin
delete from dbo.flower where price<0
print '新插入的记录不符合要求已被删除'
end
测试语句：insert into dbo.flower(name,price,flowerlanguage)
values('兰花',-3,'dyt') 结果见图1

create trigger message1 on dbo.flowermarket
for insert
as
raiserror('你更新了数据',10,1)
测试语句：insert into dbo.flowermarket(marketno,marketname,marketadd)
values ('HS06','啦啦花市','西陵区7号') 结果见图2

create trigger 删除店员
on dbo.salesman
for delete
as
if exists(select salesmanname from dbo.salesman where salesmanname='李阳')
begin
delete from dbo.salesman where salesmanname='李阳'
print '名为李阳的店员信息已被删除'
end
else
begin
print '没有名为李阳的店员信息'
end
测试语句：delete from dbo.salesman
where salesmanname='李阳'; 结果见图3