火锅店点餐数据库设计_第二次实验报告

最新推荐文章于 2024-06-17 14:41:35 发布

Damon Tong

最新推荐文章于 2024-06-17 14:41:35 发布

阅读量253

点赞数

分类专栏：火锅店点餐数据库设计文章标签：数据库 sql

本文链接：https://blog.csdn.net/qq_46658082/article/details/131188792

版权

火锅店点餐数据库设计专栏收录该内容

2 篇文章 0 订阅

订阅专栏

第二次数据库实验

设计游标更换策略

点单时，更新顾客信息，插入订单信息触发器修改

旧代码

--这部分触发器可以正常使用，当顾客点单的时候，需要判断是否是第一次出现在顾客表中，如果是，则需要先对顾客表进行插入操作
create trigger Dian on 订单 instead of insert as
begin
	declare @cus char(20),
	@ding varchar(20)
	declare gu cursor for select 顾客编号,订单编号 from inserted
	open gu
	fetch gu into @cus,@ding
	while(@@FETCH_STATUS=0)
	begin
		if(@cus not in (select 顾客编号 from 顾客))
		begin
			insert into 顾客(顾客编号,VIP等级,累计消费) values ((select 顾客编号 from inserted where 顾客编号 not in (select 顾客编号 from 顾客)),'0',0)
		end
		update 餐桌 set 餐桌状态='用餐中' where 桌号 in (select 桌号 from inserted)
		insert into 订单 select * from inserted where 订单编号=@ding
		fetch gu into @cus,@ding
	end
	close gu
	deallocate gu
end
go

新代码–其实旧代码采用的嵌套子查询的方法已经足够，无需添加游标。同时为了避免插入空的值导致报错，即到访得顾客都是回头客，使用if语句

create trigger Dian on 订单 instead of insert as
begin
	declare @cus char(20),
	@ding varchar(20)
	if exists(select 顾客编号 from inserted where 顾客编号 not in (select 顾客编号 from 顾客))--存在新客户
	begin
		insert into 顾客(顾客编号,VIP等级,累计消费) values ((select 顾客编号 from inserted where 顾客编号 not in (select 顾客编号 from 顾客)),'0',0)
	end
	update 餐桌 set 餐桌状态='用餐中' where 桌号 in (select 桌号 from inserted)
	insert into 订单 select * from inserted
end
go

测试数据

insert into 订单 (订单编号, 顾客编号, 桌号, 开始点餐时间, 提交订单时间, 总金额, 结账时间, 订单状态) values ('CAAAABRGM0JY536', 'AAXNCGFANLH4EWSGHWX', '29', '2022-10-2 14:29:33', null,null,null, '点单中')
insert into 订单 (订单编号, 顾客编号, 桌号, 开始点餐时间, 提交订单时间, 总金额, 结账时间, 订单状态) values ('DAAAABRGM0JY536', 'BBXNCGFANLH4EWSGHWX', '30', '2022-10-2 14:21:33', null,null,null, '点单中')
insert into 订单 (订单编号, 顾客编号, 桌号, 开始点餐时间, 提交订单时间, 总金额, 结账时间, 订单状态) values ('EAAAABRGM0JY536', 'CCXNCGFANLH4EWSGHWX', '31', '2022-10-2 14:23:33', null,null,null, '点单中')
insert into 订单 (订单编号, 顾客编号, 桌号, 开始点餐时间, 提交订单时间, 总金额, 结账时间, 订单状态) values ('FAAAABRGM0JY536', 'DDXNCGFANLH4EWSGHWX', '32', '2022-10-2 14:27:33', null,null,null, '点单中')

付款或者提交订单时，更新订单信息

旧代码

--速度可以，采用连接查询ok
create trigger Pay2 on 订单 after update as
begin
    declare @ding char(20),--订单编号
            @total float,--总金额
            @cai varchar(20),--菜品编号
            @numb int,--菜品数量
            @jia float(8), --菜品价格
            @status varchar(20), --订单状态
            @disc float(8),--折扣
            @cus char(20),--顾客编号
            @lei float(8)--顾客累计金额
    declare saa cursor for select 订单编号,订单状态,顾客编号 from inserted
    open saa
    fetch saa into @ding,@status,@cus
    while(@@fetch_status=0)
    begin
        set @total=0
        if(@status='已提交') 
          begin
           select @total=sum(a.餐品数量*b.菜品单价) from 订单餐品 as a left join 菜品 as b on a.菜品编号=b.菜品编号 where a.订单编号=@ding
		   select @total=@total*(select 折扣 from 折扣 where VIP等级=(select VIP等级 from 顾客 where 顾客编号=@cus))
		   update 订单 set 总金额=@total where 订单编号=@ding
		end
        else if(@status='已结账')
        begin
            select @total=总金额 from inserted where 订单编号=@ding
            select @lei=累计消费 from 顾客 where 顾客编号=@cus
            set @lei=@lei+@total--更新累计金额
            update 顾客 set 累计消费=@lei where 顾客编号=@cus
			update 餐桌 set 餐桌状态='已结账' where 桌号=(select 桌号 from 订单 where 订单编号=@ding)
        end
        fetch saa into @ding,@status,@cus
    end
    close saa
    deallocate saa
end
go

新代码–采用表变量和连接、嵌套子查询代替

--速度可以，采用连接查询ok
create trigger Pay2 on 订单 after update as
begin

	--使用表变量来临时存储总价格和订单信息，以便对应
	declare @tb1 table(id char(20) primary key, price float)--存储已提交的信息
    insert into @tb1 select a.订单编号, sum(a.餐品数量*b.菜品单价) from 订单餐品 as a left join 菜品 as b on a.菜品编号=b.菜品编号 where a.订单编号 in (select 订单编号 from inserted where 订单状态='已提交') group by a.订单编号--插入订单编号和折前总价
	update @tb1 set price=price*(select 折扣 from 折扣 where VIP等级=(select VIP等级 from 顾客 where 顾客编号 in (select 顾客编号 from inserted where 订单编号=id)))
	update 订单 set 总金额=b.price from @tb1 b where 订单.订单编号=b.id --使用表变量进行连接查询时，需要使用别名，将总价进行更新

    update 顾客 set 累计消费=累计消费+总金额 from inserted where 顾客.顾客编号 in (select 顾客编号 from inserted where 订单状态='已结账')
	update 餐桌 set 餐桌状态='已结账' where 桌号=(select 桌号 from 订单 where 订单.订单编号 in (select 订单编号 from inserted))
end
go

测试数据–多次更新导致所有测试数据进行多次调用，但不影响系统正常使用

update 订单 set 提交订单时间='2022-2-20 12:30:33',订单状态='已提交' where 订单编号='V91MA6SVUSK2G2X'
update 订单 set 提交订单时间='2022-11-7 16:00:33',订单状态='已提交' where 订单编号='VVOGTKR84L0XRSV'

update 订单 set 提交订单时间='2022-6-5 13:30:33',订单状态='已提交' where 订单编号='Q2EPU5EXCGWP0C2'
update 订单 set 结账时间='2022-9-1 16:10:33',订单状态='已结账' where 订单编号='DAXS1BRGM0JY536'
update 订单 set 结账时间='2022-2-20 14:30:33',订单状态='已结账' where 订单编号='V91MA6SVUSK2G2X'
update 订单 set 结账时间='2022-11-7 18:00:33',订单状态='已结账' where 订单编号='VVOGTKR84L0XRSV'

修改点单时，对于菜品存量的触发器判断

旧代码

--这部分触发器运行速度还行
create trigger submit on 订单餐品 after insert as
begin
    declare @ding char(20),--订单编号
            @cai varchar(20),--菜品编号
            @numb int,--餐品数量
            @nam varchar(20)

    declare s cursor for select 订单编号,菜品编号,餐品数量 from inserted
    open s
    fetch s into @ding,@cai,@numb
    while(@@fetch_status=0)
    begin
		if(@numb>(select 菜品存量 from 菜品 where 菜品编号=@cai))
		begin
			select @nam=菜品名称 from 菜品 where 菜品编号=@cai
			print '抱歉，您点的'+@nam+'已超过存量'
			delete from 订单餐品 where 订单编号=@ding and 菜品编号=@cai
		end  
		else
		begin
			update 菜品 set 菜品存量=菜品存量-@numb where 菜品编号=@cai
		end
		fetch s into @ding,@cai,@numb
    end
	close s
	deallocate s
end
go

新代码–其实对于现实生活中，餐品是一个个加入的，而不会同时加入，因此无需使用游标

create trigger submit on 订单餐品 after insert as
begin
    declare @ding char(20),--订单编号
            @cai varchar(20),--菜品编号
            @numb int,--餐品数量
            @nam varchar(20)

    select @ding=订单编号,@cai=菜品编号,@numb=餐品数量 from inserted
	if(@numb>(select 菜品存量 from 菜品 where 菜品编号=@cai))
	begin
		select @nam=菜品名称 from 菜品 where 菜品编号=@cai
		print '抱歉，您点的'+@nam+'已超过存量'
		delete from 订单餐品 where 订单编号=@ding and 菜品编号=@cai
	end  
	else
	begin
		update 菜品 set 菜品存量=菜品存量-@numb where 菜品编号=@cai
	end
end
go

对于退单的trigger，我们也采用上述相同的规则，一次退一个餐品

--该触发器正常使用
create trigger Tui on 订单餐品 after delete as
begin
    declare @ding char(20),--订单编号
            @cai varchar(20),--菜品编号
            @numb int,--餐品数量
            @sta varchar(20),--订单状态
            @jia float(8),--菜品单价
            @cus char(20),--顾客编号
            @mon float(8),--某一菜品需要的价格
            @zhe float(8)--折扣
	select @ding=订单编号,@cai=菜品编号,@numb=餐品数量 from deleted
    select @sta=订单状态,@cus=顾客编号 from 订单 where 订单编号=@ding
    if(@sta='已提交')
    begin
        update 菜品 set 菜品存量=菜品存量+@numb where 菜品编号=@cai--更新库存
        select @jia=菜品单价 from 菜品 where 菜品编号=@cai
        select @zhe=折扣 from 折扣 where VIP等级=(select VIP等级 from 顾客 where 顾客编号=@cus)
        set @mon=@numb*@jia*@zhe
        update 订单 set 总金额=总金额-@mon where 订单编号=@ding  
    end
    else if(@sta='点单中')--直接冲回即可
    begin
		update 菜品 set 菜品存量=菜品存量+@numb where 菜品编号=@cai--更新库存
    end
end
go

为什么不使用游标呢？

游标常比集合的方法慢2-3倍，大数据情况下，这个比例还会增加。

数据修改/插入

由于需要大量的数据用于分析，下面我们对数据进行进一步的完善。

订单餐品表

我们用excel随机组合的方式生成了订单餐品信息，并且插入新表中，之后通过筛选以防止键值重复

insert into 订单餐品 select a.订单编号,a.菜品编号,a.数量 from 临时表 as a where not exists(select 1 from 订单餐品 as b where a.订单编号=b.订单编号 and a.菜品编号=b.菜品编号)
go

以下是保存在临时表里的187条订单信息，通过随机的排列组合生成

订单全部提交

由于订单在设计更新状态的触发器时，采取的是一次对一条订单数据修改，因为在日常生活中，我认为顾客在提交订单、结账的时间是有先后顺序，同时按钮对应的是一条更新指令，因此不存在批量更新的情况。

我们先对之前随机插入的数据进行批量处理，以获取初步的可统计数据，采用游标进行批量更新，之所以不用批量更新是因为该表的更新涉及很多触发器，这些触发器并不支持批量更新。但是游标执行真的特别慢，因此在之后的数据更新中不采用游标的方式。短短几十条数据跑了一个半小时居然不成功，决定不使用游标的方式。

declare @id char(20),@st varchar(20)
declare a cursor for select 订单编号,订单状态 from 订单
open a
fetch a into @id,@st
while (@@FETCH_STATUS=0)
begin
	if(@st='点单中')
	begin
		update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号=@id
	end
	else if(@st='已提交')
	begin
		update 订单 set 结账时间=DATEADD(mi,60,提交订单时间),订单状态='已结账' where 订单编号=@id
	end
end
close a
deallocate a
go

采用一行一行处理的方式，不到1秒就全部更新完了

update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='JG42SE5LJADRA3N'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='SFP84FFTYK3R7HM'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='G0GVQ226DWUG9SH'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='OMXB4OQ3A1FSAJT'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='63KB3HLVDP03YY1'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='FUQSKSXXLQWS6SE'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='ITRJHW3MV6R1YT4'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='8MAQNAEXXSSX2S5'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='EMO57X73RYUDJ82'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='M0B0C2G3QJUKS5I'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='V4TTI14UIQO1B0Y'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='RHK3I229AJLO1L5'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='1NMYP1QQ6B7GTFU'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='DCYFQBS5OOHN74F'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='OFYBSDCO40MT8UI'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='FWE4PBQALAC6PMB'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='0SP7G7K3O5YCWAQ'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='CAAAABRGM0JY536'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='DAAAABRGM0JY536'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='EAAAABRGM0JY536'
update 订单 set 提交订单时间=DATEADD(mi,20,开始点餐时间),订单状态='已提交' where 订单编号='FAAAABRGM0JY536'

update 订单 set 结账时间=DATEADD(mi,60,提交订单时间),订单状态='已结账' where 订单编号='JG42SE5LJADRA3N'
update 订单 set 结账时间=DATEADD(mi,70,提交订单时间),订单状态='已结账' where 订单编号='SFP84FFTYK3R7HM'
update 订单 set 结账时间=DATEADD(mi,80,提交订单时间),订单状态='已结账' where 订单编号='G0GVQ226DWUG9SH'
update 订单 set 结账时间=DATEADD(mi,50,提交订单时间),订单状态='已结账' where 订单编号='OMXB4OQ3A1FSAJT'
update 订单 set 结账时间=DATEADD(mi,90,提交订单时间),订单状态='已结账' where 订单编号='63KB3HLVDP03YY1'
update 订单 set 结账时间=DATEADD(mi,65,提交订单时间),订单状态='已结账' where 订单编号='FUQSKSXXLQWS6SE'
update 订单 set 结账时间=DATEADD(mi,75,提交订单时间),订单状态='已结账' where 订单编号='ITRJHW3MV6R1YT4'
update 订单 set 结账时间=DATEADD(mi,80,提交订单时间),订单状态='已结账' where 订单编号='8MAQNAEXXSSX2S5'
update 订单 set 结账时间=DATEADD(mi,65,提交订单时间),订单状态='已结账' where 订单编号='EMO57X73RYUDJ82'
update 订单 set 结账时间=DATEADD(mi,79,提交订单时间),订单状态='已结账' where 订单编号='M0B0C2G3QJUKS5I'
update 订单 set 结账时间=DATEADD(mi,79,提交订单时间),订单状态='已结账' where 订单编号='V4TTI14UIQO1B0Y'
update 订单 set 结账时间=DATEADD(mi,90,提交订单时间),订单状态='已结账' where 订单编号='RHK3I229AJLO1L5'
update 订单 set 结账时间=DATEADD(mi,83,提交订单时间),订单状态='已结账' where 订单编号='1NMYP1QQ6B7GTFU'
update 订单 set 结账时间=DATEADD(mi,64,提交订单时间),订单状态='已结账' where 订单编号='DCYFQBS5OOHN74F'
update 订单 set 结账时间=DATEADD(mi,78,提交订单时间),订单状态='已结账' where 订单编号='OFYBSDCO40MT8UI'
update 订单 set 结账时间=DATEADD(mi,58,提交订单时间),订单状态='已结账' where 订单编号='FWE4PBQALAC6PMB'
update 订单 set 结账时间=DATEADD(mi,89,提交订单时间),订单状态='已结账' where 订单编号='0SP7G7K3O5YCWAQ'
update 订单 set 结账时间=DATEADD(mi,60,提交订单时间),订单状态='已结账' where 订单编号='CAAAABRGM0JY536'
update 订单 set 结账时间=DATEADD(mi,60,提交订单时间),订单状态='已结账' where 订单编号='DAAAABRGM0JY536'
update 订单 set 结账时间=DATEADD(mi,60,提交订单时间),订单状态='已结账' where 订单编号='EAAAABRGM0JY536'
update 订单 set 结账时间=DATEADD(mi,60,提交订单时间),订单状态='已结账' where 订单编号='FAAAABRGM0JY536'
update 订单 set 结账时间=DATEADD(mi,60,提交订单时间),订单状态='已结账' where 订单编号='AAAAABRGM0JY536 '
update 订单 set 结账时间=DATEADD(mi,60,提交订单时间),订单状态='已结账' where 订单编号='BBBAABRGM0JY536'
update 订单 set 结账时间=DATEADD(mi,60,提交订单时间),订单状态='已结账' where 订单编号='Q2EPU5EXCGWP0C2'

在清理餐桌中插入数据，将餐桌状态全部变为清台状态

insert into 清理餐桌 values('1','194241','2022/9/1')
insert into 清理餐桌 values('1','194240','2022/3/14')
insert into 清理餐桌 values('2','194239','2022/6/5')
insert into 清理餐桌 values('3','194238','2023/12/18')
insert into 清理餐桌 values('4','194237','2023/6/23')
insert into 清理餐桌 values('5','194236','2022/10/1')
insert into 清理餐桌 values('5','194235','2022/10/14')
insert into 清理餐桌 values('6','194234','2022/9/23')
insert into 清理餐桌 values('9','194233','2022/2/20')
insert into 清理餐桌 values('10','194232','2022/10/1')
insert into 清理餐桌 values('10','194231','2022/7/23')
insert into 清理餐桌 values('10','194230','2022/10/1')
insert into 清理餐桌 values('11','194229','2023/3/26')
insert into 清理餐桌 values('12','194228','2022/1/1')
insert into 清理餐桌 values('13','194227','2022/11/7')
insert into 清理餐桌 values('14','194226','2023/10/7')
insert into 清理餐桌 values('16','194238','2023/8/16')
insert into 清理餐桌 values('17','194237','2022/8/17')
insert into 清理餐桌 values('19','194238','2023/2/21')
insert into 清理餐桌 values('22','194238','2023/11/17')
insert into 清理餐桌 values('23','194236','2023/1/14')
insert into 清理餐桌 values('24','194235','2023/5/6')
insert into 清理餐桌 values('25','194234','2022/4/14')
insert into 清理餐桌 values('29','194233','2022/10/2')
insert into 清理餐桌 values('30','194232','2022/10/2')
insert into 清理餐桌 values('31','194231','2022/10/2')
insert into 清理餐桌 values('32','194230','2022/10/2')
insert into 清理餐桌 values('40','194237','2022/12/21')

此时可以用于初始分析的数据已经全部处理完毕，之后我们可以对实际点单情况进行模拟

生成连续5天的流水记录，便于之后的时序分析

为了方便操作，我们对订单编号采用从1到n的序号编号，这样子可以使用while函数进行自动赋值。生成从2023-1-11到2023-1-16这六天的数据，共130条订单。

declare @id int,@date1 datetime,@date datetime,@day int,@tim int
set @id=1
set @date1='2023-01-10 20:35:36.000'
set @date=@date1
select @day=1,@tim=1
while @day<=6
begin
	set @date=dateadd(dd,@day,@date1)--加上一天
	while @tim<=20
	begin
		set @date=dateadd(mi,5,@date)
		insert into 订单 values(cast(@id as char(20)),cast(@id%30 as char(20)),@id%30+1,@date,null,null,null,'点单中')
		set @id=@id+1
		set @tim=@tim+1
	end
	set @day=@day+1
	set @tim=1
end
go

采用与之前相同的操作得到处理完毕的所有订单数据

declare @id int
set @id=1
while(@id<=130)
begin
	update 订单 set 提交订单时间=DATEADD(mi,cast(rand()*15+10 as int),开始点餐时间),订单状态='已提交' where 订单编号=cast(@id as char(20))
	set @id=@id+1
end
go

declare @id int
set @id=1
while(@id<=130)
begin
	update 订单 set 结账时间=DATEADD(mi,cast(rand()*60+30 as int),提交订单时间),订单状态='已结账' where 订单编号=cast(@id as char(20))
	set @id=@id+1
end
go

最后将餐桌状态全部变成清台，此时生成了大量的连续数据，可以进行之后的统计操作

在这里插入图片描述

函数/存储过程方法操作

顾客信息查询–使用存储过程

查询顾客信息，并且将与该顾客相关的订单信息拉出来

create procedure cus_search @cus char(20)
as
	select 顾客编号,VIP等级,累计消费 from 顾客 where 顾客编号=@cus
	select a.顾客编号,a.订单编号,a.订单状态 from 订单 a left join 顾客 b on a.顾客编号=b.顾客编号 where a.顾客编号=@cus
go

declare @cus char(20)
exec cus_search @cus='HVXNCGFANLH4EWSGHWX'
go

某一时间段最受欢迎商品前10查询–使用函数

根据订单信息表返回最受顾客喜欢的10个产品，构建临时表将名字与订单编号向对应，这样可以避免直接使用连接查询时的存储规模过大的问题。

考虑到有的产品销量可能相同，因此采用with ties的方法，将第十个最后相同的额外行都打印出来

create function top10(@start datetime,@end datetime)--输入起始时间
returns @top table(food_id varchar(20),
					food_name varchar(20),
					count int)
as
begin
	declare @tab table(fo_id varchar(20),number int)
	insert into @tab select 菜品编号,sum(餐品数量) from 订单餐品 where 订单编号 in (select 订单编号 from 订单 where 结账时间>=@start and 结账时间<=@end) group by 菜品编号
	insert into @top select top 10 with ties 菜品编号,菜品名称,a.number from @tab as a left join 菜品 as b on a.fo_id=b.菜品编号 order by a.number desc
return
end
go

select * from top10('2022-01-01','2023-01-01')
go

查询利润最高的前10个产品（函数）

注意使用over（partition by）方法可以对分区进行集函数使用，但是并不能聚类成结果，仍是每条数据单列出来

create function profit() 
returns @ta table(菜品名称 varchar(20),amount float)
as
begin
	declare @tt table(idt char(20),nam varchar(20),pp float)
	insert into @tt select 菜品编号,菜品名称,菜品单价-菜品成本 from 菜品
	insert into @ta select b.nam, round(sum(a.餐品数量*b.pp),2) as 总利润 from 订单餐品 as a,@tt as b where a.菜品编号=b.idt group by a.菜品编号,b.nam
return
end
go

select  top 10 with ties * from profit() order by amount desc
go

在这里插入图片描述

对每日订单平均利润进行统计，并且统计从开始到结束期间的累计利润聚合

对于平均利润统计需要注意的是，时间的处理，使用datepart提取天，使用distinct，防止重复计算平均数，导致均值到每个菜品的平均利润。

对于累计利润聚合使用聚合函数+over()的方法，需要注意的是over()里的列名需要在主体语句 order by 中，partition by指的是统计的分区，如下语句，指的是统计2023-1-11的累计利润，聚合函数加over的方法主要是对partition部分进行操作，而不是外部主体的group by（因为一开始这么用不通过）

create procedure prof @start datetime, @end datetime
as
	select 订单编号,结账时间 into #t1 from 订单 where 结账时间<=@end and 结账时间>=@start--为了减少数据连接查询规模，通过临时表存储某时间段数据
	select datepart(dd,t1.结账时间) as 日期,sum(餐品数量*(菜品单价-菜品成本))/count(distinct t1.订单编号) as 平均订单利润 from #t1 as t1,订单餐品 as t2,菜品 as t3 where t1.订单编号=t2.订单编号 and t2.菜品编号=t3.菜品编号 group by datepart(dd,t1.结账时间)
	select t1.结账时间 as 日期,t1.订单编号,sum(餐品数量*(菜品单价-菜品成本)) as 订单利润 into #t2 from #t1 as t1,订单餐品 as t2,菜品 as t3 where t1.订单编号=t2.订单编号 and t2.菜品编号=t3.菜品编号 group by t1.订单编号,t1.结账时间
	select 日期,订单利润,sum(订单利润) over(partition by datepart(dd,日期) order by 日期 rows between unbounded preceding and current row) as 累计利润
	from #t2 as t2 order by 日期,订单编号--partition by限制统计分区
go

exec prof '2023-1-11','2023-1-16'
go

库存补货

使用case方法进行分类补货，分为酒水饮料（非果汁、牛奶）、调味料、锅底、食材这四类进行补货

对于易于保存的酒水饮料可以补货的多一些，对于调味料和锅底这类补货数量也不同，食材因为要保证新鲜，因此补货数量最少。

create procedure 补货
as
begin
	update 菜品 set 菜品存量=case 
	when 菜品名称 like '%锅底'  then 300
	when 菜品名称 in ('可乐','雪碧') then 400
	when 菜品名称='调味料' then 900
	else 200 end
end
go

exec 补货
go

全文检索

生成评论

生成一张新表用来保存评论

对订单表插入ID和评论列，ID采用自增变量用来对每一行数据进行唯一标识，这样子便于对表进行评论的批量插入操作。

alter table 订单 add ID int identity(1,1)
go

alter table 订单 add 评论 varchar(max)
go

declare @id int
set @id=1
while(@id<=31)
begin
	update 订单 set 订单.评论=b.评论 from 订单,临时表4 as b where b.ID=@id and 订单.ID=@id
	set @id=@id+1
end
go

注意多表多条件更新的方法，无需使用select

生成一个保存火锅店地图的数据库

我们对保存火锅店地图的数据库指定了文件组进行保存，同时尝试了文件读取，和二进制形式保存文件

create table 地图 (
ID int identity(1,1) primary key,
map varbinary(max)) textimage_on FG2
go

insert into 地图(map) select * from openrowset(BULK 'D:\大三下\大型数据库实验\实验\hotpot.jpg',single_blob) as pic1
insert into 地图(map) select * from openrowset(BULK 'D:\大三下\大型数据库实验\实验\2.jpg',single_blob) as pic2
insert into 地图(map) select * from openrowset(BULK 'D:\大三下\大型数据库实验\实验\3.jpg',single_blob) as pic3
go

对订单进行全文检索

在此之前，我们下载了语言语义统计库，不然无法建立全文目录

对数据库建立全文目录和对表建立全文索引

对订单的评论建立全文索引
对评论进行检索

停用词检索

alter fulltext index on 订单 set stoplist off
select ID,评论 from 订单 where contains(评论,'好吃')
go

alter fulltext index on 订单 set stoplist system
select ID,评论 from 订单 where contains(评论,'好吃')
go

我们使用了系统停用词发现两个方法的结果都为上图，因此系统停用词并没有对评论起到过滤的作用，因此我采用自设停用词表的方法

CREATE FULLTEXT STOPLIST stop ;--生成一个非索引停用词表
GO  

--添加停用词
alter fulltext stoplist stop add '好吃' language 'Simplified Chinese';
alter fulltext stoplist stop add '绝绝子' language 'Simplified Chinese';
alter fulltext stoplist stop add '美味' language 'Simplified Chinese';
alter fulltext stoplist stop add '开心' language 'Simplified Chinese';
alter fulltext stoplist stop add '服务好' language 'Simplified Chinese';
alter fulltext stoplist stop add '速度快' language 'Simplified Chinese';
go

alter fulltext index on 订单 set stoplist stop
select ID,评论 from 订单 where contains(评论,'好吃')
go

通过自用停用词，我们不会返回好的评论，需要注意的是，对于全文非索引字表需要以分号结尾

freetext明显慢于contains方法

alter fulltext index on 订单 set stoplist stop
select ID,评论 from 订单 where freetext(评论,'服务态度好') 
go

查询结果如下：

3、父上大人大寿来鸟今天回来老家附近摆酒，父上大人七十大寿门店虽不大，但摆设给人感觉不错，门口正可以现场烤乳猪等，服务态度很好，每桌都能及时服务到位，上菜速度很快，出品不错，味道与摆设都很大排，价格也可以，下次聚餐还来。

可以发现模糊匹配到了服务态度很好，这也是为什么该方法开销更大的原因

构造存储过程，进行关键词搜索

create procedure 检索 @string varchar(100)
as
	declare @tt table(ID int,评论 varchar(max))
	declare @ss varchar(100)
	set @ss='select t1.ID,t1.评论 from 订单 as t1, containstable(订单,评论,''isabout(' + @string + ')'',10) as t2 where t1.ID=t2.[key]'
	insert into @tt exec(@ss)
	select * from @tt
go

exec 检索 '"好吃" weight(1)'
go

XML数据访问

数据插入

首先我生成一个XML表，用来存储XML格式，一部分是直接输入的XML数据，一部分是从数据表中提取的数据转为XML传入该表，之后我将对该XML表进行相应的XML数据操作。

生成xml1表用来保存任意的非关系型数据，只需要根据数据类型即可判断是什么数据，用于进一步的处理

create table xml1 (id int identity(1,1) primary key,xml_type char(20),xml_content xml null)
go

下面我们输入几条员工信息的xml数据

员工数据格式如下

<?xml version="1.0" encoding="UTF-8"?>
<note>
    <id age='20'>192217</id>
    <type>前台员工</type>
    <occupy>大堂经理</occupy>
</note>

/*输入三条员工的xml数据到xml1表中，之后可以执行搜索和插入到员工表*/
insert into xml1(xml_type,xml_content) values('员工','<note>
    <id age="30">192217</id>
    <type>前台员工</type>
    <occupy>大堂经理</occupy>
</note>'),
('员工','<note>
    <id age="25">192220</id>
    <type>前台员工</type>
    <occupy>服务员</occupy>
</note>'),
('员工','<note>
    <id age="22">192223</id>
    <type>后台员工</type>
    <occupy>切菜员</occupy>
</note>')
go

点开链接可以查询到详细数据

之后利用xml文档将新增的菜品数据进行插入保存

xml文件内容如下

insert into xml1(xml_type,xml_content)
select '菜品',* from openrowset(BULK N'D:\大三下\大型数据库实验\实验\new_food.xml',single_blob) as xml_content
go

QUERY方法查询员工结点信息

select id,xml_content.query('/note/type') from xml1 where xml_type='员工'
go

注意query要小写，不然无效

value方法查询值或属性

select id,xml_content.value('(/note/id)[1]','varchar(20)') from xml1 where xml_type='员工'--搜索员工号的元素内容
go
select id,xml_content.value('(/note/id)[1]','varchar(20)') from xml1 where xml_type='员工'--搜索员工号的元素内容
go

在这里插入图片描述

xml数据更新 modify

我们在菜品信息一栏添加新的菜品信息结点

update xml1 set xml_content.modify('insert   <food>
    <num>DDDDD</num>
    <name>韩国年糕</name>
    <quan>300</quan>
    <price>15</price>
    <cost>5</cost>
  </food> after (/menu)[1]')
  go--提取错误，menu仅仅是提取到menu这一个部分，仍然包含menu元素，应注意查询到输入的最后一个元素部分，包含

因此我们需要对该节点进行删除

  update xml1 set xml_content.modify('delete(/food)[1]') where id=4
  go

成功删除

在这里插入图片描述

我发现鱼子蛋的价格偏高，售卖效果不好，因此我对数据进行了我更新，将鱼子蛋的价格降到了20

  update xml1 set xml_content.modify('replace value of (/menu/food/price/text())[3] with "20"') where id=4
  go
  --需要使用text()进行修改，否则会显示格式错误

修改成功

在这里插入图片描述

关系数据转换为xml数据

for xml auto方法

采用连接查询的方式将订单与订单菜品表的数据联系起来进行输出（rtrim 返回一个字符串，截取到字符串除空格外最后一位）

  select rtrim(订单.订单编号) as 编号,订单.结账时间 as 结束时间,
		菜品名称,餐品数量
  from 订单,订单餐品 ,菜品
  where 订单.订单编号=订单餐品.订单编号 and 订单餐品.菜品编号=菜品.菜品编号
  for xml auto
  go

在这里插入图片描述

for xml raw

查询跟2023-1-11同一周的订单

select rtrim(订单编号) as 订单编号,rtrim(顾客编号) as 顾客编号,开始点餐时间,评论 from 订单 where datediff(week,结账时间,'2023-1-11')=0 for xml raw ('订单')
go--对跟2023-1-11同一周的数据进行查询，输出xml文件

在这里插入图片描述

for xml path

查询跟2023-1-11同一周的订单信息，将xml格式处理为订单号，订单时间，订单的菜单的格式

实现的难点在于，逻辑梳理。

首先我们需要知道查询的顺序，我们搜索了个2023-1-11同一周的订单行（使用datediff函数）然后我希望生成一个xml文件的格式如下，订单为最外层元素标签，包含订单编号信息，由for xml path(‘订单’)规定标签名称， ‘@订单编号规定元素属性’，然后试订单的一些基础信息，as后表示元素内容或元素属性的定位。

其中最难的是对订单菜单的处理。

使用cast将筛选出来的订单菜单进行格式规定，需要cast为xml格式，否则在xml文档里显示的是16进制格式的字符。
采用连接查询的方式，选出该订单的菜单，即可将查询视作for语句，最外层为一行数据，根据订单编号筛选出来相应的订单餐品行，然后将菜品表与订单餐品连接，获取相应菜品编号的菜品名，生成一个连接查询表，然后对该连接查询表采用相同的for xml path方法生成xml数据然后保存在相应的订单标签内的菜单子标签内。

select rtrim(t1.订单编号) as '@订单编号',
t1.开始点餐时间 as '基础信息/开始时间',
t1.结账时间 as  '基础信息/结束时间',
t1.评论 as '基础信息/评论',
cast((select t2.菜品编号 as '餐品/@餐品编号',
t3.菜品名称 as '餐品/@餐品名称',
t2.餐品数量 as '餐品/@餐品数量' from 订单餐品 as t2,菜品 as t3 where t1.订单编号=t2.订单编号 and t2.菜品编号=t3.菜品编号 for xml path) as xml) as '基础信息/菜单'
from 订单 as t1 where datediff(week,结账时间,'2023-1-11')=0
group by t1.订单编号,t1.开始点餐时间,t1.结账时间,t1.评论
for xml path('订单')
go

在这里插入图片描述

xml数据转入关系数据

经过测试发现，对于xml转关系数据，必须要使得元素名称和列对应，否则无法顺利解析，返回空值。

declare @xx xml--生成一个xml变量对菜单信息进行保存
select @xx=xml_content from xml1 where xml_type='菜品'
declare @docHandle int--生成xml文档的句柄
exec sp_xml_preparedocument @docHandle output,@xx
select * into 测试2 from openxml(@docHandle,'menu/food',2)--2表示仅保存元素值
with(num varchar(20),name varchar(20),quan float,price float,cost float)
go

select * from 测试2
go

drop table 测试2
go

由于xml文件的元素名不与表列名对应，因此需要制定位置，否则不能正常插入，openxml操作可以视为创建了一个临时的表格式。成功插入。

declare @xx xml--生成一个xml变量对菜单信息进行保存
select @xx=xml_content from xml1 where xml_type='菜品'
declare @docHandle int--生成xml文档的句柄
exec sp_xml_preparedocument @docHandle output,@xx
insert into 菜品(菜品编号,菜品名称,菜品存量,菜品单价,菜品成本) select * from openxml(@docHandle,'menu/food',2)--2表示仅保存元素值
with(num varchar(20),name varchar(20),quan float,price float,cost float)
go

经过测试发现，对于xml转关系数据，必须要使得元素名称和列对应，否则无法顺利解析，返回空值。

declare @xx xml--生成一个xml变量对菜单信息进行保存
select @xx=xml_content from xml1 where xml_type='菜品'
declare @docHandle int--生成xml文档的句柄
exec sp_xml_preparedocument @docHandle output,@xx
select * into 测试2 from openxml(@docHandle,'menu/food',2)--2表示仅保存元素值
with(num varchar(20),name varchar(20),quan float,price float,cost float)
go

select * from 测试2
go

drop table 测试2
go

由于xml文件的元素名不与表列名对应，因此需要制定位置，否则不能正常插入，openxml操作可以视为创建了一个临时的表格式。成功插入。

declare @xx xml--生成一个xml变量对菜单信息进行保存
select @xx=xml_content from xml1 where xml_type='菜品'
declare @docHandle int--生成xml文档的句柄
exec sp_xml_preparedocument @docHandle output,@xx
insert into 菜品(菜品编号,菜品名称,菜品存量,菜品单价,菜品成本) select * from openxml(@docHandle,'menu/food',2)--2表示仅保存元素值
with(num varchar(20),name varchar(20),quan float,price float,cost float)
go