枯木逢春犹再发，人无两度再少年
系统主要模块如下：
（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, bookPricefloatnull, bookPublisherchar(20) null, bookCountintnull, constraint PK_BOOK primary key nonclustered (bookIsbn))2.创建会员表
/*==============================================================*//* Table: vip *//*==============================================================*/create table vip ( vipIdchar(20) not null, vipLevel intnull, vipNamechar(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 intnot null, vipLevelName char(20) null, vipLevelDisCount floatnull, constraint PK_VIPTYPE primary key nonclustered (vipLevel))4.创建销售实体表
/*==============================================================*//* Table: sale*//*==============================================================*/create table sale ( saleId char(20) not null, vipIdchar(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, bookSaleCountintnull, 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_VIPgoif 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_SALEgoif 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_BOOKgoif 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_参照_VIPTYPEgoif exists (select 1fromsysobjects whereid = object_id('SysManageUser')and type = 'U') drop table SysManageUsergoif exists (select 1fromsysobjects whereid = object_id('book')and type = 'U') drop table bookgoif exists (select 1fromsysindexes whereid= object_id('sale')and name= 'buy_FK'and indid > 0and indid < 255) drop index sale.buy_FKgoif exists (select 1fromsysobjects whereid = object_id('sale')and type = 'U') drop table salegoif exists (select 1fromsysindexes whereid= object_id('saleDetail')and name= 'Relationship_4_FK'and indid > 0and indid < 255) drop index saleDetail.Relationship_4_FKgoif exists (select 1fromsysindexes whereid= object_id('saleDetail')and name= 'Relationship_6_FK'and indid > 0and indid < 255) drop index saleDetail.Relationship_6_FKgoif exists (select 1fromsysobjects whereid = object_id('saleDetail')and type = 'U') drop table saleDetailgoif exists (select 1fromsysindexes whereid= object_id('vip')and name= '参照_FK'and indid > 0and indid < 255) drop index vip.参照_FKgoif exists (select 1fromsysobjects whereid = object_id('vip')and type = 'U') drop table vipgoif exists (select 1fromsysobjects whereid = object_id('vipType')and type = 'U') drop table vipTypego/*==============================================================*//* Table: SysManageUser *//*==============================================================*/create table SysManageUser ( SMUIdchar(20) not null, SMUNamechar(20) null, SMUPasswordchar(20) null, SMUTypechar(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, bookPricefloatnull, bookPublisherchar(20) null, bookCountintnull, constraint PK_BOOK primary key nonclustered (bookIsbn))go/*==============================================================*//* Table: sale*//*==============================================================*/create table sale ( saleId char(20) not null, vipIdchar(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, bookSaleCountintnull, 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 ( vipIdchar(20) not null, vipLevel intnull, vipNamechar(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 intnot null, vipLevelName char(20) null, vipLevelDisCount floatnull, constraint PK_VIPTYPE primary key nonclustered (vipLevel))goalter table sale add constraint FK_SALE_BUY_VIP foreign key (vipId)references vip (vipId)goalter table saleDetail add constraint FK_SALEDETA_RELATIONS_SALE foreign key (saleId)references sale (saleId)goalter table saleDetail add constraint FK_SALEDETA_RELATIONS_BOOK foreign key (bookIsbn)references book (bookIsbn)goalter 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')GOINSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103002 ', N'俄狄浦斯王', N'Kit ', N'工具书', N'3', N'人民出版社', N'11')GOINSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103003 ', N'至尊女王爷', N'xiaoming', N'工具书', N'7', N'中华书局', N'16')GOINSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103004 ', N'天是红尘岸', N'zhaoyun ', N'专业书', N'14', N'商务印书馆', N'7')GOINSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103005 ', N'逐日追风剑', N'huatuo', N'小说', N'1', N'商务印书馆', N'2')GOINSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103006 ', N'雪中悍刀行', N'machao', N'儿童读物', N'4', N'商务印书馆', N'3')GOINSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103007 ', N'神级大魔头', N'sunbin', N'小说', N'6', N'商务印书馆', N'14')GOINSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103008 ', N'宿主请留步', N'shangguan ', N'工具书', N'13', N'人民出版社', N'93')GOINSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103009 ', N'巴黎圣母院', N'zhouyu', N'工具书', N'11', N'人民出版社', N'3')GOINSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103010 ', N'徐霞客游记', N'xiaoqiao', N'社会科学', N'19', N'人民出版社', N'12')GOINSERT INTO [dbo].[book] ([bookIsbn], [bookName], [bookAuthor], [bookType], [bookPrice], [bookPublisher], [bookCount]) VALUES (N'isbn2103011 ', N'喧哗与骚动', N'baili ', N'社会科学', N'10', N'人民出版社', N'18')GOINSERT 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')GOINSERT INTO [dbo].[vipType] ([vipLevel], [vipLevelName], [vipLevelDisCount]) VALUES (N'1', N'一级会员', N'0.9')GOINSERT INTO [dbo].[vipType] ([vipLevel], [vipLevelName], [vipLevelDisCount]) VALUES (N'2', N'二级会员', N'0.85')GOINSERT INTO [dbo].[vipType] ([vipLevel], [vipLevelName], [vipLevelDisCount]) VALUES (N'3', N'三级会员', N'0.8')GOINSERT INTO [dbo].[vipType] ([vipLevel], [vipLevelName], [vipLevelDisCount]) VALUES (N'4', N'四级会员', N'0.75')GOINSERT INTO [dbo].[vipType] ([vipLevel], [vipLevelName], [vipLevelDisCount]) VALUES (N'5', N'五级会员', N'0.7')GOINSERT INTO [dbo].[vipType] ([vipLevel], [vipLevelName], [vipLevelDisCount]) VALUES (N'6', N'六级会员', N'0.65')GOINSERT 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 ')GOINSERT INTO [dbo].[vip] ([vipId], [vipLevel], [vipName], [vipSex], [vipAge], [vipTel]) VALUES (N'v002', N'6', N'杨暖昕', N'女', N'22', N'19803038888 ')GOINSERT INTO [dbo].[vip] ([vipId], [vipLevel], [vipName], [vipSex], [vipAge], [vipTel]) VALUES (N'v003', N'0', N'凌秋子', N'男', N'36', N'17806069999 ')GOINSERT INTO [dbo].[vip] ([vipId], [vipLevel], [vipName], [vipSex], [vipAge], [vipTel]) VALUES (N'v004', N'4', N'李楠', N'男', N'18', N'17806068888 ')GOINSERT INTO [dbo].[vip] ([vipId], [vipLevel], [vipName], [vipSex], [vipAge], [vipTel]) VALUES (N'v005', N'6', N'文春雪', N'男', N'36', N'18808089999 ')GOINSERT INTO [dbo].[vip] ([vipId], [vipLevel], [vipName], [vipSex], [vipAge], [vipTel]) VALUES (N'v006', N'3', N'陆亦思', N'男', N'46', N'17801010000 ')GOINSERT INTO [dbo].[vip] ([vipId], [vipLevel], [vipName], [vipSex], [vipAge], [vipTel]) VALUES (N'v007', N'1', N'代迎海', N'男', N'18', N'13109787777 ')GOINSERT INTO [dbo].[vip] ([vipId], [vipLevel], [vipName], [vipSex], [vipAge], [vipTel]) VALUES (N'v008', N'2', N'白紫玉', N'男', N'17', N'13120200897 ')GOINSERT 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')GOINSERT INTO [dbo].[sale] ([saleId], [vipId], [saleDate]) VALUES (N'saleid009 ', N'v002', N'2020-01-03 08:15:12.000')GOINSERT INTO [dbo].[sale] ([saleId], [vipId], [saleDate]) VALUES (N'saleid001 ', N'v001', N'2020-12-01 04:06:21.000')GOINSERT INTO [dbo].[sale] ([saleId], [vipId], [saleDate]) VALUES (N'saleid002 ', N'v002', N'2020-01-03 08:15:12.000')GOINSERT INTO [dbo].[sale] ([saleId], [vipId], [saleDate]) VALUES (N'saleid003 ', N'v005', N'2020-01-05 02:45:00.000')GOINSERT INTO [dbo].[sale] ([saleId], [vipId], [saleDate]) VALUES (N'saleid004 ', N'v009', N'2020-01-06 02:45:00.000')GOINSERT INTO [dbo].[sale] ([saleId], [vipId], [saleDate]) VALUES (N'saleid005 ', N'v007', N'2020-01-06 02:45:00.000')GOINSERT INTO [dbo].[sale] ([saleId], [vipId], [saleDate]) VALUES (N'saleid006 ', N'v003', N'2020-01-06 02:45:00.000')GOINSERT INTO [dbo].[sale] ([saleId], [vipId], [saleDate]) VALUES (N'saleid007 ', N'v004', N'2020-02-27 00:00:00.000')GOINSERT 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')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'52SU70BURCAMS50F5QFD', N'saleid001 ', N'isbn2103004 ', N'9')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'7FVQ0GKT5G9QB0P6TUA4', N'saleid001 ', N'isbn2103003 ', N'2')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'C6VNTSXDUCOQKE37ER14', N'saleid001 ', N'isbn2103002 ', N'1')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'UKA4710SMLJKWSQO389H', N'saleid002 ', N'isbn2103009 ', N'1')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'970CM5NUN54WDHXNUYQ8', N'saleid002 ', N'isbn2103001 ', N'2')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'P6G5AHGVYCGJBHD8NU9L', N'saleid002 ', N'isbn2103006 ', N'2')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'WX1RIJJMQYACA4Y3FL69', N'saleid001 ', N'isbn2103004 ', N'15')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'YF9G7JP86C3OVSE1AQR4', N'saleid003 ', N'isbn2103007 ', N'3')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'UPH2K75GBCQI68W3NLXC', N'saleid006 ', N'isbn2103006 ', N'11')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'V7OLOUXQ5WM9AIF96NSS', N'saleid005 ', N'isbn2103003 ', N'17')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'5Q6MB5S4HA3Y0TNC HO ', N'saleid004 ', N'isbn2103002 ', N'8')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'VWLWUACJQ36G0N7SVSCN', N'saleid002 ', N'isbn2103005 ', N'8')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'8MFJ3SREX7OJ9D0GC69U', N'saleid006 ', N'isbn2103001 ', N'2')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'ELK8E2O5E0W4Q4YHA0QK', N'saleid004 ', N'isbn2103007 ', N'7')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'1 ', N'saleid001 ', N'isbn2103008 ', N'1')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'2 ', N'saleid001 ', N'isbn2103008 ', N'6')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'ELK8E2O5t68UQ4YHA0QK', N'saleid005 ', N'isbn2103007 ', N'2')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'PA31CORBOML5W9MPLP88', N'saleid007 ', N'isbn2103001 ', N'2')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'D16V7L1PD 19JI7GGDJN', N'saleid007 ', N'isbn2103002 ', N'1')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'CPQKT SXWO LEL10 KFW', N'saleid008 ', N'isbn2103011 ', N'1')GOINSERT INTO [dbo].[saleDetail] ([saleDetailId], [saleId], [bookIsbn], [bookSaleCount]) VALUES (N'28N9LUIDU37HAWMCFC3 ', N'saleid008 ', N'isbn2103012 ', N'1')GOSQL查询
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.天销售额（这里的销售额我算的是销售数量，也有人说是算销售金额）
selectbook.bookname,sum(booksalecount) day_sale_countfrom(selectbookisbn,booksalecountfromsaledetailwheresaleid in(selectsaleidfromsalewheredatepart(year, saledate) = 2020and datepart(month, saledate) = 1and datepart(day, saledate) = 6)) t1left join book on book.bookIsbn = t1.bookisbngroup bybook.bookname;
3.天销售榜前三
selecttop 3 book.bookname,sum(booksalecount) sale_countfrom(selectbookisbn,booksalecountfromsaledetailwheresaleid in(selectsaleidfromsalewheredatepart(year, saledate) = 2020and datepart(month, saledate) = 1and datepart(day, saledate) = 6)) t1left join book on book.bookIsbn = t1.bookisbngroup bybook.booknameorder bysale_count desc;
4.月销售额
selectbook.bookname,sum(booksalecount) book_countfrom(selectbookisbn,booksalecountfromsaledetailwheresaleid in (selectsaleidfromsalewheredatepart(year, saledate) = 2020and datepart(month, saledate) = 1)) t1left join book on book.bookisbn = t1.bookisbngroup bybook.bookname;
5.月销售榜前三
selecttop 3 book.bookname,sum(booksalecount) book_countfrom(selectbookisbn,booksalecountfromsaledetailwheresaleid in (selectsaleidfromsalewheredatepart(year, saledate) = 2020and datepart(month, saledate) = 1)) t1left join book on book.bookisbn = t1.bookisbngroup bybook.booknameorder bybook_count desc;
6.自动计算库存，支付金额（触发器）
create trigger auto_update_bookcount_money on saleDetailafter insertasbegindeclare @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)beginprint('购买数量：'+convert(varchar,@booksalecount)+'， 库存量：'+convert(varchar,@bookcount)+'。 库存不足，订单支付失败！')rollback transaction;endelsebegindeclare @newCount intupdate 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=@vipIddeclare @vipLevelDiscount float;select @vipLevelDiscount=vipLevelDisCount from vipType where vipLevel=@vipLevelprint(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))endend向销售明细表中插入一条数据测试，库存量和金额是否正确。
insert into saledetail values('3','saleid001','isbn2103002',1);说明：销售明细id为3，这条销售明细属于saleid001销售id，售出的书id是isbn2103002，数量是1。
