站点图标 AI技术聚合

书店销售管理系统—-数据库原理及应用综合实验

社会演员多

枯木逢春犹再发，人无两度再少年🍂

系统主要模块如下：
（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.会员实体表
字段名别名数据类型长度约束
会员idvipId字符型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
会员等级折扣vipLevelDisCountfloat\not null
4. 销售实体表
字段名别名数据类型长度约束
销售单号saleId字符型20位主键
会员IDvipId字符型20位外键
销售日期saleDate日期型\not null
5. 销售明细实体表
字段名别名数据类型长度约束
销售明细idsaleDetailId字符型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。



文章出处登录后可见！

已经登录？立即刷新
退出移动版