数据库原理第二次实验报告
目录
写在前面
因为任务四说的实在是太说得道理了如果没有欧内的手很难做这个尊尼获加的哈利路大旋风，于是就去找了老师：
另外本次实验报告的生成用了 Keldos-Li_typora-latex-theme_ 将Typora伪装成LaTeX的中文样式主题，本科生轻量级课程论文撰写的好帮手。This is a theme disguising Typora into Chinese LaTeX style，超级好用，强推！
Update on 2024.6.11：
- 任务一-1、任务三-2：添加外键。
- 完善了任务二-3 的设计。
- 修正了 任务三-1 的设计思路。
实验报告要求
- 列出所有的SQL语句和源代码；
- PL/SQL程序可以有适当的注释。
- 实验报告中需给出代码、结果截图和对分析结果的文字描述。
任务一（数据库CAP）
初始化 CAP
按照课件所示进行表的建立：
Use CAP;
CREATE TABLE MONTH
(
Rank smallint,
Month char(8) PRIMARY KEY NOT NULL
);
CREATE TABLE CUSTOMERS
(
Cid char(8) PRIMARY KEY NOT NULL,
Cname varchar(8) NOT NULL,
City varchar(8),
Discnt numeric(10,2)
);
CREATE TABLE AGENTS
(
Aid char(8) PRIMARY KEY NOT NULL,
Aname varchar(8) NOT NULL,
City varchar(8),
AgentsPercent int
);
CREATE TABLE PRODUCTS
(
Pid char(8) PRIMARY KEY NOT NULL,
Pname varchar(8) NOT NULL,
City varchar(8),
Quantity int,
Price numeric(10,2)
);
CREATE TABLE ORDERS
(
Ordno char(8) PRIMARY KEY NOT NULL,
Month char(8),
cid char(8) NOT NULL,
aid char(8) NOT NULL,
pid char(8) NOT NULL,
Qty int,
Dollars numeric(10, 2)
);
alter table ORDERS
add constraint FK_ORDERS_REFERENCE_MONTH foreign key (Month)
references MONTH (Month)
alter table ORDERS
add constraint FK_ORDERS_REFERENCE_CUSTOMERS foreign key (cid)
references CUSTOMERS (Cid)
alter table ORDERS
add constraint FK_ORDERS_REFERENCE_AGENTS foreign key (aid)
references AGENTS (Aid)
alter table ORDERS
add constraint FK_ORDERS_REFERENCE_PRODUCTS foreign key (pid)
references PRODUCTS (Pid)
插入一些数据，并对 MONTH 进行初始化：
Use master;
Use CAP;
INSERT INTO CUSTOMERS VALUES('C001','TipTip','Duluth', 10.00);
INSERT INTO AGENTS VALUES('A01','smith','New York', 6);
INSERT INTO PRODUCTS VALUES('P01','comb','Dallas', 111400, 0.50);
INSERT INTO MONTH VALUES(1, 'Jan.');
INSERT INTO MONTH VALUES(2, 'Feb.');
INSERT INTO MONTH VALUES(3, 'Mar.');
INSERT INTO MONTH VALUES(4, 'Apr.');
INSERT INTO MONTH VALUES(5, 'May.');
INSERT INTO MONTH VALUES(6, 'Jun.');
INSERT INTO MONTH VALUES(7, 'Jul.');
INSERT INTO MONTH VALUES(8, 'Aug.');
INSERT INTO MONTH VALUES(9, 'Sept.');
INSERT INTO MONTH VALUES(10, 'Oct.');
INSERT INTO MONTH VALUES(11, 'Nov.');
INSERT INTO MONTH VALUES(12, 'Dec.');
1
写一段程序，向表Orders中增加50000条记录，要求订单尽可能均匀地分布在12个月中。
需要保证数据的合法性，考虑首先从 CUSTOMERS、AGENTS、PRODUCTS、MONTH 中各选出一行数据作为插入订单的参数，实现方法为将上述各表随机排序后取第一行。
然后按照数字递增地构造 50000 条数据的编号 00001 ~ 50000，再随机地构造其他参数后插入。
declare @i int
set @i = 1
while @i < 5000
begin
declare @cid char(8)
select top 1 @cid = Cid
from CUSTOMERS
order by newid()
declare @aid char(8)
select top 1 @aid = Aid
from AGENTS
order by newid()
declare @pid char(8)
select top 1 @pid = Pid
from PRODUCTS
order by newid()
declare @mon char(8)
select top 1 @mon = Month
from MONTH
order by newid()
declare @oid char(8)
set @oid = RIGHT('00000000' + cast(@i as varchar), 6)
-- print @oid
insert into ORDERS values(@oid, @mon, @cid, @aid, @pid, rand() * 10, rand() * 10)
set @i = @i + 1
end
执行结果：
2
假设在表Orders上经常要执行的一个查询是“根据给定的月份，查询该月订单的总金额”。写出该查询的SQL语句，尝试通过创建索引提高查询的速度，并对比创建索引前后执行查询所消耗的时间。
查询语句：
select sum(Dollars) sum
from ORDERS
where Month = 'Jan.'
建立索引语句：
create index OrdersIndexOnMonth
on ORDERS(Month)
在（1）建立的表上的执行结果前后时间差异：
[2024-05-22 10:48:22] 在 71 ms (execution: 37 ms, fetching: 34 ms) 内检索到从 1 开始的 1 行 \(\rightarrow\) [2024-05-22 10:47:58] 在 60 ms (execution: 11 ms, fetching: 49 ms) 内检索到从 1 开始的 1 行。
任务二：视图（数据库GlobalToyz）
1
假设GlobalToyz数据库的用户经常需要查询某个购物者(shopper)的所有已收货订单的编号和待收货订单的编号。请通过定义视图解决这个问题。
视图中需要包含 ShopperId，OrderNo，cDeliveryStatus。
create view ViewShopper(cShopperId, cDeliveryStatus, cOrderNo)
as select Shopper.cShopperId, cDeliveryStatus, Orders.cOrderNo
from Shopper
left join Orders on Shopper.cShopperId = Orders.cShopperId
left join Shipment on Orders.cOrderNo = Shipment.cOrderNo
测试语句：
select cDeliveryStatus, cOrderNo
from ViewShopper
where cShopperId = '000002'
执行结果：
2
基于（1）中定义的视图，根据给定的购物者Id查询该购物者所有待收货玩具的Id,名称和描述。
订单的具体信息位于 OrderDetail 中，玩具的具体信息位于 Toys 中。
假设给定购物者 cShopperId = '000008'：
select VS.cOrderNo, T.vToyName, T.vToyDescription
from ViewShopper VS
left join OrderDetail OD
on VS.cOrderNo = OD.cOrderNo
left join Toys T
on OD.cToyId = T.cToyId
where VS.cShopperId = '000008' and VS.cDeliveryStatus = 's'
执行结果：
3
假设当系统中新增一个购物者（Shopper）的时候，需要为该购物者创建一个账号，并通过授权机制限制该购物者不能访问其他购物者的私有信息，例如其他购物者的订单、购物者的姓名、地址、邮件等个人信息。请给出解决的方案。
考虑在系统新增购物者的同时，为每个购物者单独建立查询视图，其中仅包含该购物者的信息。每次购物者访问数据库时，仅允许在该视图中进行查询。
具体实现时可以在对应数据库中新建该购物者对应的角色，该对应的角色仅有查看购物者对应的视图权限。当对应用户连接到服务器且访问该数据库时，仅允许其以该用户的权限访问即可。
以对购物者cShopperId = '000002' 为例：
create view ShopperView_000002
as select *
from Shopper
where Shopper.cShopperId = '000002'
create view OrdersView_000002
as select *
from Orders
where cShopperId = '000002'
新建服务器主体 shopper_00002，然后在数据库中新建用户 user_000002 并对其进行权限分配：
use master go
create login [shopper_000002] with password ='123qwe!@#', default_database = GlobalToyz
use GlobalToyz go
create user [user_000002] for login [shopper_000002]
grant select on OrdersView_000002 to [user_000002]
grant select on ShopperView_000002 to [user_000002]
任务三：触发器（数据库GlobalToyz）
1
PickofMonth 这张表是一张统计表，按年月统计某个玩具的销售总量。当用户下订单的时候，需要自动维护 PickofMonth 这张表。请利用触发器实现这个功能。
订单的详细信息存储于 OrderDetail 中，考虑使用游标枚举 inserted 表中与对应订单编号相同的所有 OrderDetail 中的数据，根据其 cToyId 与 siQty，然后参考 dOrderDate 修改 PickofMonth 中的数据，若对应的年份月份不存在则新建一行。
create trigger updatePickOfMonth on Orders
after insert as
declare @orderno char(6)
declare @year int
declare @month int
select @orderno = cOrderNo, @year = year(inserted.dOrderDate), @month = month(inserted.dOrderDate)
from inserted
if not exists(select * from OrderDetail where cOrderNo = @orderno) begin
return
end
select cToyId, siQty into T
from OrderDetail
where cOrderNo = @orderno
-- select *
-- from T
declare cursor_toy cursor for
select cToyId, siQty from T
open cursor_toy;
declare @toy char(6)
declare @qty smallint
fetch next from cursor_toy into @toy, @qty;
while @@fetch_status = 0 begin
-- PRINT 'toyid：' + @toy + '，qty：' + CAST(@qty AS varchar(10));
if exists(select * from PickOfMonth
where @year = PickOfMonth.iYear and
@month = PickOfMonth.siMonth and
@toy = PickOfMonth.cToyId) begin
update PickOfMonth
set iTotalSold = iTotalSold + @qty
where cToyId = @toy and iYear = @year and siMonth = @month
end else begin
insert into PickOfMonth values(@toy, @month, @year, @qty)
end
fetch next from cursor_toy into @toy, @qty;
end
close cursor_toy;
deallocate cursor_toy;
drop table T
go
2
Orders表是GlobalToyz数据库里的一张核心的表，对这张表上做的任何更新动作（增、删、改）都需要记录下来，这是数据库审计（Audit）的基本思想。要求设计一张表存储对Orders表的更新操作，包括操作者、操作时间、操作类型、更新前的数据、更新后的数据。请通过设计触发器实现对Orders表的审计。
为了记录更新操作与历史对象，首先建立如下两张表：
create table HistoryOrders
(
biHistoryOrderNo bigint not null primary key identity(1, 1),
cOrderNo char(6) not null,
dOrderDate datetime not null,
cCartId char(6) not null,
cShopperId char(6) not null,
cShippingModeId char(2),
mShippingCharges money,
mGiftWrapCharges money,
cOrderProcessed char,
mTotalCost money,
dExpDelDate datetime
)
-- 操作者、操作时间、操作类型、更新前的数据、更新后的数据
create table AuditOrders
(
biAuditOrdersNo bigint PRIMARY KEY NOT NULL identity(1, 1),
vOperator varchar(16) NOT NULL,
dDatetime datetime,
vType varchar(16),
biBeforeHistoryOrderNo bigint,
biAfterHistoryOrderNo bigint
)
alter table AuditOrders
add constraint FK_AUDITORDERS_REFERENCE_HISTORYORDERS_1 foreign key (biBeforeHistoryOrderNo)
references HistoryOrders (biHistoryOrderNo)
alter table AuditOrders
add constraint FK_AUDITORDERS_REFERENCE_HISTORYORDERS_2 foreign key (biAfterHistoryOrderNo)
references HistoryOrders (biHistoryOrderNo)
然后建立如下三个触发器，分别用于处理增删改：
InsertOrders：将新增数据插入HistoryOrders，然后将其在HistoryOrders中的编号作为参数，将insert操作插入AuditOrders。DeleteOrders：将被删除数据插入HistoryOrders，然后将其在HistoryOrders中的编号作为参数，将delete操作插入AuditOrders。UpdateOrders：将被删除数均插入HistoryOrders，然后将它们在HistoryOrders中的编号作为参数，将update操作插入AuditOrders。
特别地，若操作非法，没有对数据库造成影响，仍会记录一次空操作。
-- 增、删、改
create trigger InsertOrders on Orders
after insert as
declare @after bigint
if (select count(*) from inserted) > 0 begin
insert into HistoryOrders select * from inserted
select @after = max(biHistoryOrderNo) from HistoryOrders
end
insert into AuditOrders(vOperator, dDatetime, vType, biBeforeHistoryOrderNo, biAfterHistoryOrderNo)
values (user, getdate(), 'insert', null, @after)
go
create trigger DeleteOrders on Orders
after delete as
declare @before bigint
if (select count(*) from deleted) > 0 begin
insert into HistoryOrders select * from deleted
select @before = max(biHistoryOrderNo)from HistoryOrders
end
insert into AuditOrders(vOperator, dDatetime, vType, biBeforeHistoryOrderNo, biAfterHistoryOrderNo)
values (user, getdate(), 'delete', @before, null)
go
create trigger UpdateOrders on Orders
after update
as
declare @before bigint
declare @after bigint
if (select count(*) from deleted) > 0 begin
insert into HistoryOrders select * from deleted
select @before = max(biHistoryOrderNo) from HistoryOrders
insert into HistoryOrders select * from inserted
select @after = max(biHistoryOrderNo) from HistoryOrders
end
insert into AuditOrders(vOperator, dDatetime, vType, biBeforeHistoryOrderNo, biAfterHistoryOrderNo)
values (user, getdate(), 'update', @before, @after)
go
测试
为了测试上述四个触发器的正确性，需要对应的 Orders 的数据在 OrderDetail 里有对应编号的数据存在，仅仅简单地通过在 Orders 中插入或删除一条数据进行测试实际上是非法的，由于约束的存在也无法进行。
上述触发器的测试结果见任务四中定义的向 Orders 中插入数据的存储过程。
任务四：存储过程与事务（数据库GlobalToyz）
1
当用户确认了一笔订单的时候，需要对数据库进行一系列的操作，例如向表Orders、表OrderDetail中添加记录、对表Toys中玩具的库存数量的修改等，请首先画出相应的处理流程，然后将处理流程定义为一个事务，通过一个存储过程来实现，存储过程以购物车ID（cCartId）和购物者ID(cShopperId)为参数。（这道题请充分考虑确认订单的各个环节）
最折磨的一集。
只使用购物车 ID 和购物者 ID 为参数显然不太充分，所以额外地随机生成了如下参数：
- 运输种类
- 对于每个物品：
- 是否进行礼物包装
- 礼物包装种类
- 礼物消息
以下是一些用于生成随机数据的函数：
create view v_rand as
select rand() as val;
go
create or alter function getRandNum(@n int)
returns int
as begin
select @n = @n * val from v_rand
return floor(@n)
end;
go
create or alter function getRandomString(
@num int,
@chars varchar(1024) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
) returns varchar(1024)
as begin
declare @res_str VARCHAR(1024) = ''
declare @i int=0
while (@i < @num) begin
set @res_str = @res_str + substring(@chars, dbo.getRandNum(len(@chars))+ 1, 1)
set @i = @i + 1
end
return @res_str
end
go
然后考虑添加一笔订单后的过程：
- 枚举每个物品，在
OrderDetail插入数据：- 随机参数（是否包装，包装种类，礼物消息）。
- 计算礼物包装费用、根据重量计算运输费用、计算总花费。
- 将所有花费累计。
- 在
Orders中插入数据：- 在
PickOfMonth更新销量（续用任务三的触发器）。 - 在
Toys更新现有量Qoh（quantity on hand）（触发器实现）
- 在
- Recipient 插入数据（触发器实现）
- Shipment 插入数据（触发器实现）
- 仅插入包含
OrderNo的空行。
- 仅插入包含
create or alter function getShippingCost(
@shopperId char(6),
@modeId char(2),
@pound smallint
) returns money
as begin
declare @countryId char(3)
declare @ratePerPound money
select @countryId = cCountryID
from Shopper
where cShopperId = @shopperId
select @ratePerPound = mRatePerPound
from ShippingRate
where cCountryID = @countryId and cModeId = @modeId
return convert(money, @ratePerPound * @pound)
end
go
create procedure AddOrder
(
@shopperId char(6),
@cartId char(6)
)
as begin
declare @maxNo int
select @maxNo = max(cOrderNo) + 1 from Orders
declare @orderNo char(6)
declare @shippingModeId char(2)
declare @shippingCharges money = 0
declare @giftWrapCharges money = 0
declare @totalToyCost money = 0
declare @OrderDate datetime = getdate()
declare @expDelDate datetime
set @orderNo = RIGHT('000000' + CAST(@maxNo AS VARCHAR), 6);
select top 1 @shippingModeId = cModeId,
@expDelDate = dateadd(day, iMaxDelDays, @OrderDate)
from ShippingMode
order by newid()
alter table OrderDetail
nocheck constraint FK__OrderDeta__cOrde__534D60F1
declare toy_cursor cursor for
select ShoppingCart.cToyId as cToyId,
ShoppingCart.siQty as siQty,
Toys.mToyRate as mToyRate,
Toys.siToyWeight as siToyWeight
from ShoppingCart
left join Toys
on ShoppingCart.cToyId = Toys.cToyId
where cCartId = @cartId
open toy_cursor
declare @toy char(6)
declare @qty smallint
declare @rate money
declare @weight smallint
fetch next from toy_cursor into @toy, @qty, @rate, @weight
while @@fetch_status = 0 begin
declare @giftWrap char(1) = 'N'
declare @wrapId char(3)
declare @message char(256)
declare @toyCost money = @qty * @rate
declare @toyWrapCost money = 0
declare @toyShippingCost money = 0
if (rand() < 0.5) begin
select top 1 @wrapId = cWrapperId, @toyWrapCost = mWrapperRate
from Wrapper
order by newid()
set @giftWrap = 'Y'
set @toyWrapCost = @toyWrapCost * @qty
set @message = dbo.getRandomString(10,DEFAULT)
end
set @toyShippingCost = dbo.getShippingCost(@shopperId,
@shippingModeId, @qty * @weight)
print convert(char(6), @orderNo) + ' ' + @toy + ' ' + convert(char(6), @qty) + ' ' +
@giftWrap + ' ' + @wrapId + ' ' + @message + ' '
print @toyShippingCost
set @shippingCharges = @shippingCharges + @toyShippingCost
set @giftWrapCharges = @giftWrapCharges + @toyWrapCost
set @totalToyCost = @totalToyCost + @toyCost
insert into OrderDetail(cOrderNo, cToyId, siQty, cGiftWrap, cWrapperId,
vMessage, mToyCost)
values (@orderNo, @toy, @qty, @giftWrap, @wrapId,
@message, @toyCost)
fetch next from toy_cursor into @toy, @qty, @rate, @weight
end
close toy_cursor
deallocate toy_cursor
alter table OrderDetail
check constraint FK__OrderDeta__cOrde__534D60F1
insert into Orders(cOrderNo, dOrderDate, cCartId, cShopperId, cShippingModeId,
mShippingCharges, mGiftWrapCharges, cOrderProcessed,
mTotalCost, dExpDelDate)
values (@orderNo, @OrderDate, @cartId, @shopperId, @shippingModeId,
@shippingCharges, @giftWrapCharges, 'Y',
@totalToyCost + @shippingCharges + @giftWrapCharges, @expDelDate)
end;
go
create trigger updateToysQoh on OrderDetail
after insert as
declare @toyId char(6)
declare @toyQty smallint
select @toyId = cToyId, @toyQty = siQty
from inserted
if exists(select * from Toys where Toys.cToyId = @toyId and
Toys.siToyQoh >= @toyQty) begin
update Toys
set siToyQoh = siToyQoh - @toyQty
where cToyId = @toyId
end
go
create trigger insertRecipient on Orders after insert as
insert into Recipient
select cOrderNo, vFirstName, vLastName, vAddress, cCity, cState, cCountryId, cZipCode, cPhone
from inserted
left join Shopper
on inserted.cShopperId = Shopper.cShopperId
go
create trigger insertShipment on Orders after insert as
insert into Shipment
select cOrderNo, null, null, null
from inserted
go
测试
exec AddOrder @shopperId = '000002', @cartId = '000001'
执行结果：
表 Orders 中的变化：
表 OrderDetail 中的变化：
表 Recipient 中的变化：
表 Shippment 中的变化：
表 PickofMonth 中的变化：
表 HistoryOrders 中的变化：
表 AuditOrders 中的变化：
任务五：游标与SQL（数据库GlobalToyz）
1
基于表Orders和Shopper，以下列格式生成报表：（要求用游标实现）
购货人ID XXX 购货人姓名 XXX 购货人地址 XXXXXX 定单号XXX 定单时间XXX 定单金额XXX 定单号XXX 定单时间XXX 定单金额XXX
使用两层循环，枚举所有购物者，再枚举 Orders 中该购物者的所有订单，输出要求的信息即可。也可以先将 Shopper 与 Orders 表 join 后得到一张临时表，再对该临时表进行枚举。以下代码使用了两重循环。
注意若某购物者没有下过订单，则不输出。
declare shopper_cursor cursor for
select cShopperId, vFirstName, vLastName, vAddress from Shopper
open shopper_cursor
declare @shopper char(6)
declare @firstname varchar(20)
declare @lastname varchar(20)
declare @address varchar(40)
fetch next from shopper_cursor into @shopper, @firstname, @lastname, @address
while @@fetch_status = 0 begin
select cOrderNo, dOrderDate, mTotalCost into TempOrders
from Orders
where @shopper = Orders.cShopperId
declare order_cursor cursor for
select cOrderNo, dOrderDate, mTotalCost from TempOrders
open order_cursor
declare @no char(6)
declare @date datetime
declare @cost money
fetch next from order_cursor into @no, @date, @cost
if @@fetch_status = 0 begin
print 'Shopper Id: ' + @shopper + ' Shopper Name: ' + @firstname + ' ' + @lastname
print 'Shopper Address: ' + @address
while @@fetch_status = 0 begin
print 'Order Id: ' + @no + ' Order Datetime: ' + convert(varchar, @date) + ' Order Cost: ' + convert(varchar, @cost)
fetch next from order_cursor into @no, @date, @cost
end
print ''
print ''
end
close order_cursor
deallocate order_cursor
drop table TempOrders
fetch next from shopper_cursor INTO @shopper, @firstname, @lastname, @address
end
close shopper_cursor
deallocate shopper_cursor
执行结果：
Shopper Id: 000002 Shopper Name: Barbara Johnson
Shopper Address: 227 Beach Ave.
Order Id: 000001 Order Datetime: 05 20 2021 12:00AM Order Cost: 62.22
Order Id: 000005 Order Datetime: 05 21 2021 12:00AM Order Cost: 231.68
Order Id: 000011 Order Datetime: 05 23 2024 8:27PM Order Cost: 53.97
Shopper Id: 000003 Shopper Name: Betty Williams
Shopper Address: 1 Tread Road
Order Id: 000010 Order Datetime: 05 22 2021 12:00AM Order Cost: 67.97
Shopper Id: 000005 Shopper Name: Catherine Roberts
Shopper Address: 5508 Aquiline Court
Order Id: 000002 Order Datetime: 05 20 2021 12:00AM Order Cost: 96.50
Shopper Id: 000006 Shopper Name: Charles Brown
Shopper Address: 7822 S. Glitzy Avenue
Order Id: 000004 Order Datetime: 05 20 2021 12:00AM Order Cost: 40.99
Shopper Id: 000007 Shopper Name: Christopher Davis
Shopper Address: 4896 11th ST
Order Id: 000003 Order Datetime: 05 20 2021 12:00AM Order Cost: 83.97
Shopper Id: 000008 Shopper Name: Cynthia Miller
Shopper Address: 98066 Weary Storm Street
Order Id: 000007 Order Datetime: 05 22 2021 12:00AM Order Cost: 16.99
Shopper Id: 000009 Shopper Name: Daniel Wilson
Shopper Address: 4642 Peripheral Drive
Order Id: 000008 Order Datetime: 05 22 2021 12:00AM Order Cost: 53.98
Shopper Id: 000010 Shopper Name: David Moore
Shopper Address: 8808 Joviality Drive
Order Id: 000009 Order Datetime: 05 22 2021 12:00AM Order Cost: 26.99
Shopper Id: 000012 Shopper Name: Donna Anderson
Shopper Address: 7930 Orange St.
Order Id: 000006 Order Datetime: 05 21 2021 12:00AM Order Cost: 97.97
2
编写代码，分析购物者、玩具和地域的关系，例如哪个城市的购买者对哪一种、哪一类或哪一个品牌的玩具更有兴趣。这道题是个开放的题目，同学们可以按照自己的理解从不同的角度进行分析。
探究玩具品牌与购买者所在州的关系。
考虑分析每种品牌在各个州的销售额在总销售额的占比，并按照降序排序：
-- 计算每种品牌玩具销售量总和
select Toys.cBrandId, convert(money, sum(siQty)) sum into T
from Toys
left join OrderDetail OD on Toys.cToyId = OD.cToyId
group by Toys.cBrandId
-- 求销售额在各州占比
select Toys.cBrandId, Shopper.cState,
convert(money, sum(siQty)) / (select sum from T where Toys.cBrandId = T.cBrandId) sum
from OrderDetail OD
left join Orders O on OD.cOrderNo = O.cOrderNo
left join Toys on Toys.cToyId = OD.cToyId
left join Shopper on O.cShopperId = Shopper.cShopperId
group by Shopper.cState, Toys.cBrandId
order by Toys.cBrandId, sum desc
drop table T
执行结果：
发现每种玩具均在加州的销售额的占比最高。你们加州可真是人才辈出啊（赞美之心
不行了提到加州就想到睦月跳那个铸币 California Girls 我草
参考
主要参考：数据库原理及应用教程 第4版︱微课版 （陈志泊 许福 韩慧 崔晓晖 路贺俊 阮豫红编著）
语句参考：
- SQL server 自定义函数FUNCTION的使用_sqlserver function-CSDN博客
- SQL 如何建立索引来加快数据库的查询_sql创建索引加速查询-CSDN博客
- SQL SERVER中使用print如何一起输出数值和字符串_sql server print-CSDN博客
- 变量 (Transact-SQL) - SQL Server _ Microsoft Learn
- SQL INSERT INTO 语句 _ 菜鸟教程
- SQL UPDATE 语句 _ 菜鸟教程
- SQL Server 临时禁用和启用所有外键约束_sqlserver 临时禁用表订阅-CSDN博客
- SQLServer禁用、启用外键约束 - 振乾 - 博客园
- 在 INSERT 和 UPDATE 语句中禁用外键约束 - SQL Server _ Microsoft Learn
- 玩转SQL语句之group by 多字段分组查询与having子句，一篇解决你的疑惑！_sql group by-CSDN博客
- SQL ORDER BY 子句【排序】升降序_sql排序语句order by升序-CSDN博客
- sql server生成自动加1序号_mob649e8166179a的技术博客_51CTO博客
- SQL Server Year()函数 - SQL Server教程
- SQL Server YEAR() 函数使用指南
- CREATE ROLE
- mysql 角色(role)管理功能实现、创建role，role权限赋值、激活（基于mysql8.0）_mysql create role-CSDN博客
- SQL Server 用角色(Role)管理数据库权限 - MSSQL123 - 博客园
- 数据库级别的角色 - SQL Server _ Microsoft Learn
- SQL Server Roles
- 数据库MySQL用户创建、角色创建、权限授予_创建一个角色dba1,将查询和更新teacher表的权限授予该角色,-CSDN博客
写法参考：
-
Sql Server 对数据表循环插入多条数据（使用循环方法给数据表插入数据）_给sql server数据库持续写入数据的方法-CSDN博客
-
Microsoft SQL Server 生成随机数字、字符串、日期、验证码以及 UUID_sql server 验证码-CSDN博客
-
MySQL 使用触发器记录用户的操作日志_在emp表中的数据被删除时,就会把执行这个动作的用户和时间,以及被删除的人名记录-CSDN博客
捉虫参考：
- SQL Server错误443：在函数内对带副作用的运算符 'rand' 的使用无效 - HandsomeFa - 博客园
- 解决为'_________' 的游标已存在问题_名为 的游标已存在-CSDN博客
- sql语句报错：“不是可以识别的内置函数名称”_sql不是可以识别的内置函数名称-CSDN博客
实验报告格式：
