书店销售管理系统—-数据库原理及应用综合实验
枯木逢春犹再发，人无两度再少年🍂
系统主要模块如下：
（1） 书店销售管理系统设计与实现—图书入库管理及查询统计
图书入库管理：维护入库图书信息（如图书编号、书名、作者、价格、图书分类、出版社等）。自动计算库存。
图书查询统计：按图书分类，出版社、书名、作者等条件查询图书的详细信息。支持模糊查询。
（2） 书店销售管理系统设计与实现—销售管理
销售管理：销售过的图书都记录在销售列表中，方便统计收入。图书销售后，实时记录图书库存，按每天统计销售额、按每个月或季度统计销售额并生成报表，并能根据销售数量统计生成畅销书名单。
（3） 书店销售管理系统设计与实现—书店会员管理
书店会员管理：提供会员信息的维护功能，可设置会员等级，不同级别的会员享受不同的折扣，可以变更折扣额度。
目录
一、数据字典
1.图书实体表
2.会员实体表
3. 会员类型表
4. 销售实体表
5. 销售明细实体表
二、概念模型设计
1. 图书基本信息E-R图
2. 会员实体E-R图
3.会员类型E-R图
4.销售实体E-R图
5.销售明细E-R图
6.总体E-R图
三、逻辑结构设计
四、物理结构设计
五、SQL查询
1. 分组统计、模糊查询
2. 天销售额
3. 天销售榜前三
4.月销售额
5. 月销售榜前三
6.自动计算库存，支付金额（触发器）
一、数据字典
1. 图书实体表
| 字段名 | 别名 | 数据类型 | 长度 | 约束 |
|---|---|---|---|---|
| 图书编号 | bookIsbn | 字符型 | 20位 | 主码 |
| 图书名称 | bookName | 字符型 | 20位 | not null |
| 作者 | bookAuthor | 字符型 | 20位 | not null |
| 图书类别 | bookType | 字符型 | 20位 | not null |
| 价格 | bookPrice | 浮点型 | not null | |
| 出版社 | bookPublisher | 字符型 | 20位 | not null |
| 库存 | bookCount | 整型 | not null |
2.会员实体表
| 字段名 | 别名 | 数据类型 | 长度 | 约束 |
|---|---|---|---|---|
| 会员id | vipId | 字符型 | 20位 | 主码 |
| 会员等级 | vipLevel | 整型 | 外键 | |
| 会员名字 | vipName | 字符型 | 20位 | not null |
| 会员性别 | vipSex | 字符型 | 20位 | not null |
| 会员年龄 | vipAge | 字符型 | 20位 | not null |
| 会员电话 | vipTel | 字符型 | 20位 | not null |
3. 会员类型表
| 字段名 | 别名 | 数据类型 | 长度 | 约束 |
|---|---|---|---|---|
| 会员等级 | vipId | 字符型 | 20位 | 主键 |
| 会员等级名 | vipName | 字符型 | 20位 | not null |
| 会员等级折扣 | vipLevelDisCount | float | not null |
4. 销售实体表
| 字段名 | 别名 | 数据类型 | 长度 | 约束 |
|---|---|---|---|---|
| 销售单号 | saleId | 字符型 | 20位 | 主键 |
| 会员ID | vipId | 字符型 | 20位 | 外键 |
| 销售日期 | saleDate | 日期型 | not null |
5. 销售明细实体表
| 字段名 | 别名 | 数据类型 | 长度 | 约束 |
|---|---|---|---|---|
| 销售明细id | saleDetailId | 字符型 | 20位 | 主键 |
| 销售单号 | saleId | 字符型 | 20位 | 外键 |
| 图书编号 | bookIsbn | 字符型 | 20位 | 外键 |
| 销售数量 | bookSaleCount | 整型 | not null |
二、概念模型设计
1. 图书基本信息E-R图
2. 会员实体E-R图
3.会员类型E-R图
4.销售实体E-R图
5.销售明细E-R图
6.总体E-R图
三、逻辑结构设计
E-R图向关系模型的转换（主键用下划线标出）
图书（图书编号，图书名称，作者，图书类别，价格，出版社，库存）
会员（会员id，会员等级，会员名字，会员性别，会员年龄，会员电话）
会员类型（会员等级，会员等级名，会员等级折扣）
销售（销售单号，会员ID，销售日期）
销售明细（销售明细id，销售单号，图书编号，销售数量）
逻辑模型图
四、物理结构设计
设计与实现
1. 创建book表
/*==============================================================*/
/* Table: book */
/*==============================================================*/
create table book (
bookIsbn char(20) not null,
bookName char(20) null,
bookAuthor char(20) null,
bookType char(20) null,
bookPrice float null,
bookPublisher char(20) null,
bookCount int null,
constraint PK_BOOK primary key nonclustered (bookIsbn)
)
2.创建会员表
/*==============================================================*/
/* Table: vip */
/*==============================================================*/
create table vip (
vipId char(20) not null,
vipLevel int null,
vipName char(20) null,
vipSex char(20) null,
vipAge char(20) null,
vipTel char(20) null,
constraint PK_VIP primary key nonclustered (vipId)
)
3.创建会员类型表
/*==============================================================*/
/* Table: vipType */
/*==============================================================*/
create table vipType (
vipLevel int not null,
vipLevelName char(20) null,
vipLevelDisCount float null,
constraint PK_VIPTYPE primary key nonclustered (vipLevel)
)
4.创建销售实体表
/*==============================================================*/
/* Table: sale */
/*==============================================================*/
create table sale (
saleId char(20) not null,
vipId char(20) null,
saleDate datetime null,
constraint PK_SALE primary key nonclustered (saleId)
)
5.创建销售明细表
/*==============================================================*/
/* Table: saleDetail */
/*==============================================================*/
create table saleDetail (
saleDetailId char(20) not null,
saleId char(20) null,
bookIsbn char(20) null,
bookSaleCount int null,
constraint PK_SALEDETAIL primary key nonclustered (saleDetailId)
)
6.创建表整体脚本如下：
/*==============================================================*/
/* DBMS name: Microsoft SQL Server 2012 */
/* Created on: 2022/11/18 8:12:35 */
/*==============================================================*/
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('sale') and o.name = 'FK_SALE_BUY_VIP')
alter table sale
drop constraint FK_SALE_BUY_VIP
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('saleDetail') and o.name = 'FK_SALEDETA_RELATIONS_SALE')
alter table saleDetail
drop constraint FK_SALEDETA_RELATIONS_SALE
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('saleDetail') and o.name = 'FK_SALEDETA_RELATIONS_BOOK')
alter table saleDetail
drop constraint FK_SALEDETA_RELATIONS_BOOK
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('vip') and o.name = 'FK_VIP_参照_VIPTYPE')
alter table vip
drop constraint FK_VIP_参照_VIPTYPE
go
if exists (select 1
from sysobjects
where id = object_id('SysManageUser')
and type = 'U')
drop table SysManageUser
go
if exists (select 1
from sysobjects
where id = object_id('book')
and type = 'U')
drop table book
go
if exists (select 1
from sysindexes
where id = object_id('sale')
and name = 'buy_FK'
and indid > 0
and indid < 255)
drop index sale.buy_FK
go
if exists (select 1
from sysobjects
where id = object_id('sale')
and type = 'U')
drop table sale
go
if exists (select 1
from sysindexes
where id = object_id('saleDetail')
and name = 'Relationship_4_FK'
and indid > 0
and indid < 255)
drop index saleDetail.Relationship_4_FK
go
if exists (select 1
from sysindexes
where id = object_id('saleDetail')
and name = 'Relationship_6_FK'
and indid > 0
and indid < 255)
drop index saleDetail.Relationship_6_FK
go
if exists (select 1
from sysobjects
where id = object_id('saleDetail')
and type = 'U')
drop table saleDetail
go
if exists (select 1
from sysindexes
where id = object_id('vip')
and name = '参照_FK'
and indid > 0
and indid < 255)
drop index vip.参照_FK
go
if exists (select 1
from sysobjects
where id = object_id('vip')
and type = 'U')
drop table vip
go
if exists (select 1
from sysobjects
where id = object_id('vipType')
and type = 'U')
drop table vipType
go
/*==============================================================*/
/* Table: SysManageUser */
/*==============================================================*/
create table SysManageUser (
SMUId char(20) not null,
SMUName char(20) null,
SMUPassword char(20) null,
SMUType char(20) null,
constraint PK_SYSMANAGEUSER primary key nonclustered (SMUId)
)
go
/*==============================================================*/
/* Table: book */
/*==============================================================*/
create table book (
bookIsbn char(20) not null,
bookName char(20) null,
bookAuthor char(20) null,
bookType char(20) null,
bookPrice float null,
bookPublisher char(20) null,
bookCount int null,
constraint PK_BOOK primary key nonclustered (bookIsbn)
)
go
/*==============================================================*/
/* Table: sale */
/*==============================================================*/
create table sale (
saleId char(20) not null,
vipId char(20) null,
saleDate datetime null,
constraint PK_SALE primary key nonclustered (saleId)
)
go
/*==============================================================*/
/* Index: buy_FK */
/*==============================================================*/
create index buy_FK on sale (
vipId ASC
)
go
/*==============================================================*/
/* Table: saleDetail */
/*==============================================================*/
create table saleDetail (
saleDetailId char(20) not null,
saleId char(20) null,
bookIsbn char(20) null,
bookSaleCount int null,
constraint PK_SALEDETAIL primary key nonclustered (saleDetailId)
)
go
/*==============================================================*/
/* Index: Relationship_6_FK */
/*==============================================================*/
create index Relationship_6_FK on saleDetail (
bookIsbn ASC
)
go
/*==============================================================*/
/* Index: Relationship_4_FK */
/*==============================================================*/
create index Relationship_4_FK on saleDetail (
saleId ASC
)
go
/*==============================================================*/
/* Table: vip */
/*==============================================================*/
create table vip (
vipId char(20) not null,
vipLevel int null,
vipName char(20) null,
vipSex char(20) null,
vipAge char(20) null,
vipTel char(20) null,
constraint PK_VIP primary key nonclustered (vipId)
)
go
/*==============================================================*/
/* Index: 参照_FK */
/*==============================================================*/
create index 参照_FK on vip (
vipLevel ASC
)
go
/*==============================================================*/
/* Table: vipType */
/*==============================================================*/
create table vipType (
vipLevel int not null,
vipLevelName char(20) null,
vipLevelDisCount float null,
constraint PK_VIPTYPE primary key nonclustered (vipLevel)
)
go
alter table sale
add constraint FK_SALE_BUY_VIP foreign key (vipId)
references vip (vipId)
go
alter table saleDetail
add constraint FK_SALEDETA_RELATIONS_SALE foreign key (saleId)
references sale (saleId)
go
alter table saleDetail
add constraint FK_SALEDETA_RELATIONS_BOOK foreign key (bookIsbn)
references book (bookIsbn)
go
alter table vip
add constraint FK_VIP_参照_VIPTYPE foreign key (vipLevel)
references vipType (vipLevel)
go
向book表中插入数据：
INSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103001 ', N'恋人拍卖行 ', N'Tom ', N'社会科学 ', N'12', N'商务印书馆 ', N'15')
GO
INSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103002 ', N'俄狄浦斯王 ', N'Kit ', N'工具书 ', N'3', N'人民出版社 ', N'11')
GO
INSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103003 ', N'至尊女王爷 ', N'xiaoming ', N'工具书 ', N'7', N'中华书局 ', N'16')
GO
INSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103004 ', N'天是红尘岸 ', N'zhaoyun ', N'专业书 ', N'14', N'商务印书馆 ', N'7')
GO
INSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103005 ', N'逐日追风剑 ', N'huatuo ', N'小说 ', N'1', N'商务印书馆 ', N'2')
GO
INSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103006 ', N'雪中悍刀行 ', N'machao ', N'儿童读物 ', N'4', N'商务印书馆 ', N'3')
GO
INSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103007 ', N'神级大魔头 ', N'sunbin ', N'小说 ', N'6', N'商务印书馆 ', N'14')
GO
INSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103008 ', N'宿主请留步 ', N'shangguan ', N'工具书 ', N'13', N'人民出版社 ', N'93')
GO
INSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103009 ', N'巴黎圣母院 ', N'zhouyu ', N'工具书 ', N'11', N'人民出版社 ', N'3')
GO
INSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103010 ', N'徐霞客游记 ', N'xiaoqiao ', N'社会科学 ', N'19', N'人民出版社 ', N'12')
GO
INSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103011 ', N'喧哗与骚动 ', N'baili ', N'社会科学 ', N'10', N'人民出版社 ', N'18')
GO
INSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103012 ', N'苏菲的世界 ', N'make ', N'社会科学 ', N'8', N'人民出版社 ', N'5')
GO
向vipType表中插入数据
INSERT INTO [dbo].[vipType] ([vipLevel], [vipLevelName], [vipLevelDisCount]) VALUES (N'0', N'普通会员 ', N'0.95')
GO
INSERT INTO [dbo].[vipType] ([vipLevel], [vipLevelName], [vipLevelDisCount]) VALUES (N'1', N'一级会员 ', N'0.9')
GO
INSERT INTO [dbo].[vipType] ([vipLevel], [vipLevelName], [vipLevelDisCount]) VALUES (N'2', N'二级会员 ', N'0.85')
GO
INSERT INTO [dbo].[vipType] ([vipLevel], [vipLevelName], [vipLevelDisCount]) VALUES (N'3', N'三级会员 ', N'0.8')
GO
INSERT INTO [dbo].[vipType] ([vipLevel], [vipLevelName], [vipLevelDisCount]) VALUES (N'4', N'四级会员 ', N'0.75')
GO
INSERT INTO [dbo].[vipType] ([vipLevel], [vipLevelName], [vipLevelDisCount]) VALUES (N'5', N'五级会员 ', N'0.7')
GO
INSERT INTO [dbo].[vipType] ([vipLevel], [vipLevelName], [vipLevelDisCount]) VALUES (N'6', N'六级会员 ', N'0.65')
GO
INSERT INTO [dbo].[vipType] ([vipLevel], [vipLevelName], [vipLevelDisCount]) VALUES (N'7', N'七级会员 ', N'0.6')
GO
向vip表中插入数据
INSERT INTO [dbo].[vip] ([vipId], [vipLevel], [vipName], [vipSex], [vipAge], [vipTel]) VALUES (N'v001 ', N'7', N'张三丰 ', N'男 ', N'21 ', N'19907078888 ')
GO
INSERT INTO [dbo].[vip] ([vipId], [vipLevel], [vipName], [vipSex], [vipAge], [vipTel]) VALUES (N'v002 ', N'6', N'杨暖昕 ', N'女 ', N'22 ', N'19803038888 ')
GO
INSERT INTO [dbo].[vip] ([vipId], [vipLevel], [vipName], [vipSex], [vipAge], [vipTel]) VALUES (N'v003 ', N'0', N'凌秋子 ', N'男 ', N'36 ', N'17806069999 ')
GO
INSERT INTO [dbo].[vip] ([vipId], [vipLevel], [vipName], [vipSex], [vipAge], [vipTel]) VALUES (N'v004 ', N'4', N'李楠 ', N'男 ', N'18 ', N'17806068888 ')
GO
INSERT INTO [dbo].[vip] ([vipId], [vipLevel], [vipName], [vipSex], [vipAge], [vipTel]) VALUES (N'v005 ', N'6', N'文春雪 ', N'男 ', N'36 ', N'18808089999 ')
GO
INSERT INTO [dbo].[vip] ([vipId], [vipLevel], [vipName], [vipSex], [vipAge], [vipTel]) VALUES (N'v006 ', N'3', N'陆亦思 ', N'男 ', N'46 ', N'17801010000 ')
GO
INSERT INTO [dbo].[vip] ([vipId], [vipLevel], [vipName], [vipSex], [vipAge], [vipTel]) VALUES (N'v007 ', N'1', N'代迎海 ', N'男 ', N'18 ', N'13109787777 ')
GO
INSERT INTO [dbo].[vip] ([vipId], [vipLevel], [vipName], [vipSex], [vipAge], [vipTel]) VALUES (N'v008 ', N'2', N'白紫玉 ', N'男 ', N'17 ', N'13120200897 ')
GO
INSERT INTO [dbo].[vip] ([vipId], [vipLevel], [vipName], [vipSex], [vipAge], [vipTel]) VALUES (N'v009 ', N'6', N'韩若初 ', N'女 ', N'26 ', N'17809271234 ')
GO
向sale表中插入数据
INSERT INTO [dbo].[sale] ([saleId], [vipId], [saleDate]) VALUES (N'saleid010 ', N'v001 ', N'2020-12-01 04:06:21.000')
GO
INSERT INTO [dbo].[sale] ([saleId], [vipId], [saleDate]) VALUES (N'saleid009 ', N'v002 ', N'2020-01-03 08:15:12.000')
GO
INSERT INTO [dbo].[sale] ([saleId], [vipId], [saleDate]) VALUES (N'saleid001 ', N'v001 ', N'2020-12-01 04:06:21.000')
GO
INSERT INTO [dbo].[sale] ([saleId], [vipId], [saleDate]) VALUES (N'saleid002 ', N'v002 ', N'2020-01-03 08:15:12.000')
GO
INSERT INTO [dbo].[sale] ([saleId], [vipId], [saleDate]) VALUES (N'saleid003 ', N'v005 ', N'2020-01-05 02:45:00.000')
GO
INSERT INTO [dbo].[sale] ([saleId], [vipId], [saleDate]) VALUES (N'saleid004 ', N'v009 ', N'2020-01-06 02:45:00.000')
GO
INSERT INTO [dbo].[sale] ([saleId], [vipId], [saleDate]) VALUES (N'saleid005 ', N'v007 ', N'2020-01-06 02:45:00.000')
GO
INSERT INTO [dbo].[sale] ([saleId], [vipId], [saleDate]) VALUES (N'saleid006 ', N'v003 ', N'2020-01-06 02:45:00.000')
GO
INSERT INTO [dbo].[sale] ([saleId], [vipId], [saleDate]) VALUES (N'saleid007 ', N'v004 ', N'2020-02-27 00:00:00.000')
GO
INSERT INTO [dbo].[sale] ([saleId], [vipId], [saleDate]) VALUES (N'saleid008 ', N'v001 ', N'2020-03-11 00:00:00.000')
GO
向saleDetail表中插入数据
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'HAWGPIXSQBPRW6IA1TD4', N'saleid001 ', N'isbn2103001 ', N'19')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'52SU70BURCAMS50F5QFD', N'saleid001 ', N'isbn2103004 ', N'9')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'7FVQ0GKT5G9QB0P6TUA4', N'saleid001 ', N'isbn2103003 ', N'2')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'C6VNTSXDUCOQKE37ER14', N'saleid001 ', N'isbn2103002 ', N'1')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'UKA4710SMLJKWSQO389H', N'saleid002 ', N'isbn2103009 ', N'1')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'970CM5NUN54WDHXNUYQ8', N'saleid002 ', N'isbn2103001 ', N'2')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'P6G5AHGVYCGJBHD8NU9L', N'saleid002 ', N'isbn2103006 ', N'2')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'WX1RIJJMQYACA4Y3FL69', N'saleid001 ', N'isbn2103004 ', N'15')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'YF9G7JP86C3OVSE1AQR4', N'saleid003 ', N'isbn2103007 ', N'3')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'UPH2K75GBCQI68W3NLXC', N'saleid006 ', N'isbn2103006 ', N'11')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'V7OLOUXQ5WM9AIF96NSS', N'saleid005 ', N'isbn2103003 ', N'17')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'5Q6MB5S4HA3Y0TNC HO ', N'saleid004 ', N'isbn2103002 ', N'8')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'VWLWUACJQ36G0N7SVSCN', N'saleid002 ', N'isbn2103005 ', N'8')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'8MFJ3SREX7OJ9D0GC69U', N'saleid006 ', N'isbn2103001 ', N'2')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'ELK8E2O5E0W4Q4YHA0QK', N'saleid004 ', N'isbn2103007 ', N'7')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'1 ', N'saleid001 ', N'isbn2103008 ', N'1')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'2 ', N'saleid001 ', N'isbn2103008 ', N'6')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'ELK8E2O5t68UQ4YHA0QK', N'saleid005 ', N'isbn2103007 ', N'2')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'PA31CORBOML5W9MPLP88', N'saleid007 ', N'isbn2103001 ', N'2')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'D16V7L1PD 19JI7GGDJN', N'saleid007 ', N'isbn2103002 ', N'1')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'CPQKT SXWO LEL10 KFW', N'saleid008 ', N'isbn2103011 ', N'1')
GO
INSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'28N9LUIDU37HAWMCFC3 ', N'saleid008 ', N'isbn2103012 ', N'1')
GO
SQL查询
1.图书查询统计：按图书分类，出版社、书名、作者等条件查询图书的详细信息。支持模糊查询。
select bookType '图书分类' from book group by bookType;
select bookpublisher '出版社' from book group by bookpublisher;
select * from book where booktype like'%工具%';
select * from book where bookPublisher like '%商务印书馆%'
select * from book where bookname like '%王%'
2.天销售额（这里的销售额我算的是销售数量，也有人说是算销售金额）
select
book.bookname,
sum(booksalecount) day_sale_count
from
(
select
bookisbn,
booksalecount
from
saledetail
where
saleid in(
select
saleid
from
sale
where
datepart(year, saledate) = 2020
and datepart(month, saledate) = 1
and datepart(day, saledate) = 6
)
) t1
left join book on book.bookIsbn = t1.bookisbn
group by
book.bookname;
3.天销售榜前三
select
top 3 book.bookname,
sum(booksalecount) sale_count
from
(
select
bookisbn,
booksalecount
from
saledetail
where
saleid in(
select
saleid
from
sale
where
datepart(year, saledate) = 2020
and datepart(month, saledate) = 1
and datepart(day, saledate) = 6
)
) t1
left join book on book.bookIsbn = t1.bookisbn
group by
book.bookname
order by
sale_count desc;
4.月销售额
select
book.bookname,
sum(booksalecount) book_count
from
(
select
bookisbn,
booksalecount
from
saledetail
where
saleid in (
select
saleid
from
sale
where
datepart(year, saledate) = 2020
and datepart(month, saledate) = 1
)
) t1
left join book on book.bookisbn = t1.bookisbn
group by
book.bookname;
5.月销售榜前三
select
top 3 book.bookname,
sum(booksalecount) book_count
from
(
select
bookisbn,
booksalecount
from
saledetail
where
saleid in (
select
saleid
from
sale
where
datepart(year, saledate) = 2020
and datepart(month, saledate) = 1
)
) t1
left join book on book.bookisbn = t1.bookisbn
group by
book.bookname
order by
book_count desc;
6.自动计算库存，支付金额（触发器）
create trigger auto_update_bookcount_money on saleDetail
after insert
as
begin
declare @booksalecount int;
declare @bookisbn char(20);
declare @bookcount int;
select @bookisbn=bookisbn from inserted;
select @booksalecount=bookSaleCount from inserted;
select @bookcount=bookcount from book where bookisbn=@bookisbn;
if(@booksalecount>@bookcount)
begin
print('购买数量：'+convert(varchar,@booksalecount)+'， 库存量：'+convert(varchar,@bookcount)+'。 库存不足，订单支付失败！')
rollback transaction;
end
else
begin
declare @newCount int
update book set bookcount=(bookcount-@booksalecount) where bookisbn=@bookisbn;
select @newCount=bookcount from book where bookisbn=@bookisbn;
declare @saleId char(20);
select @saleId=saleId,@bookIsbn=bookIsbn,@bookSaleCount=bookSaleCount from inserted;
declare @bookPrice float;
declare @bookName char(20);
select @bookPrice=bookPrice,@bookName=bookName from book where bookIsbn=@bookIsbn;
declare @vipId char(20);
select @vipId=vipId from sale where saleId=@saleId;
declare @vipName char(20);
declare @vipLevel int;
select @vipName=vipName,@vipLevel=vipLevel from vip where vipId=@vipId
declare @vipLevelDiscount float;
select @vipLevelDiscount=vipLevelDisCount from vipType where vipLevel=@vipLevel
print(convert(varchar(6),@vipName)+'购买《'+convert(varchar(10),@bookName)+'》的消费金额为：'+convert(varchar,@bookSaleCount*@bookPrice*@vipLevelDiscount)+'， 购买数量：'+convert(varchar,@bookSaleCount)+'， 单价：'+convert(varchar,@bookPrice)+'， 会员等级：'+convert(varchar,@vipLevel)+'， 会员折扣：'+convert(varchar,@vipLevelDiscount)+'， 更新后的库存量为：'+convert(varchar,@newCount))
end
end
向销售明细表中插入一条数据测试，库存量和金额是否正确。
insert into saledetail values('3','saleid001','isbn2103002',1);
说明：销售明细id为3，这条销售明细属于saleid001销售id，售出的书id是isbn2103002，数量是1。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
THE END
二维码