-
Notifications
You must be signed in to change notification settings - Fork 0
/
书店零售销售系统.sql
292 lines (255 loc) · 12.2 KB
/
书店零售销售系统.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
CREATE DATABASE Loan_Management2
ON PRIMARY
(
NAME = 'bookstore_DATA',
FILENAME = 'C:\shujuku\bookstore_DATE.MDF', --主数据文件存储位置
SIZE = 3MB, --主数据文件的初始大小
FILEGROWTH = 3MB, --每次增容大小
MAXSIZE = UNLIMITED --无最大内存限制
)
LOG ON
(
NAME = 'bookstore',
FILENAME = 'C:\shujuku\bookstore_LOG.MDF', --日志文件存储位置
SIZE = 1MB, --日志文件的初始大小
FILEGROWTH = 10%, --每次增容大小
MAXSIZE = 10MB --最大内存限制
)
--创建图书信息表
create table sj1(
book_id CHAR(5) primary key, --书籍ID
book_name NVARCHAR(30) not null,--书名
author NVARCHAR(20) not null,--作者
ISBN CHAR(20) not null,--ISBN
publish NVARCHAR(20) not null,--出版社
shelf_num CHAR(20) not null,--货架号
shelf_time DATE not null,--上架时间
selling_price MONEY not null check(selling_price>=0),--售价
pricing MONEY not null check(pricing>=0),--定价
publish_time DATE not null,--出版时间
book_sign NVARCHAR(20) check(book_sign in('y','n')),--售出标志
typ_name NVARCHAR(20) not null,--书籍类型名称
)
--4.2.3创建会员（顾客）信息表
create table customer(
member_id CHAR(5) primary key,--会员ID
name NVARCHAR(30) not null,--姓名
sexuality NVARCHAR(10) check(sexuality in('男','女')),--性别
birth DATE not null,--出生年月
customer_telephone VARCHAR(13) check(len(customer_telephone)>=11),--电话
total_amount MONEY not null check(total_amount>=0),--累计消费金额
discount FLOAT not null check(discount>=0),--折扣
)
--4.2.4创建厂商信息表
create table firm(
firm_id CHAR(5) primary key,--厂商ID
firm_name NVARCHAR(20) not null,--厂商名称
firm_telephone VARCHAR(13) check(len(firm_telephone)>=0),--电话号码
head_name NVARCHAR(30) not null,--负责人姓名
firm_address NVARCHAR(50) not null,--厂商地址
)
--4.2.5创建管理员表
create table manager(
manager_id CHAR(5) primary key,--管理员ID
manager_name NVARCHAR(50) not null,--管理员姓名
password1 NVARCHAR(50) not null,---登录密码
manager_telephone VARCHAR(13) check(len(manager_telephone)>=0),--联系电话
title NVARCHAR(30) not null,--职称
)
--4.2.6创建图书类型表
create table typ(
typ_id CHAR(5)primary key,--书籍类型ID
typ_name NVARCHAR(30) not null,--书籍类型名称
)
--4.2.7创建进货信息表
create table book_firm(
firm_id CHAR(5) references firm(firm_id),--厂商ID
book_id CHAR(5) references sj1(book_id),--书籍ID
book_name NVARCHAR(30) not null,--书名
purchase_time DATE not null,--进货时间
amount FLOAT not null check(amount>=0),--数量
purchase_price MONEY not null check(purchase_price>=0),--进价
ISBN CHAR(5) not null,--ISBN
publish NVARCHAR(20) not null,--出版社
primary key(firm_id,book_id),
)
--4.2.8创建订单表
create table customer_book(
book_id CHAR(5) references sj1(book_id),--书籍ID
member_id CHAR(5) references customer(member_id),--会员ID
name NVARCHAR(30) not null,--姓名
purchase_amount FLOAT not null check(purchase_amount>=0),--购买数量
purchase_data DATE not null,--购买日期
selling_price MONEY not null check(selling_price>=0),--售价
discount FLOAT not null check(discount>=0),--折扣
book_name NVARCHAR(30) not null,--书名
primary key(book_id,member_id),
)
--4.3基本数据处理
--4.3.1 插入
insert into sj1 values('A0001','挪威的森林','树上春树','00001','清华大学出版社','b1-101','2021-1-1',45,30,'2019-3-2','n','文学类'),
('A0021','挪威的森林','树上春树','00021','北京联合出版社','b1-101','2021-1-1',45,30,'2019-3-2','y','文学类'),
('B0001','挪威的森林','树上春树','00001','清华大学出版社','b1-101','2021-1-1',45,30,'2019-3-2','n','文学类'),
('C0001','挪威的森林','树上春树','00001','清华大学出版社','b1-101','2021-1-1',45,30,'2019-3-2','y','文学类'),
('C0002','明朝那些事儿','李彪','00002','北京联合出版社','b2-201','2021-3-6',105,70,'2018-4-5','y','史学类'),
('A0002','明朝那些事儿','李彪','00002','北京联合出版社','b2-201','2021-3-6',105,70,'2018-4-5','y','史学类'),
('B0002','明朝那些事儿','李彪','00002','北京联合出版社','b2-201','2021-3-6',105,70,'2018-4-5','n','史学类'),
('A0022','明朝那些事儿','李彪','00022','清华大学出版社','b2-201','2021-3-6',105,70,'2018-4-5','y','史学类'),
('A0023','云边有个小卖部','许有飞','00023','人民出版社','b1-106','2021-1-1',37.5,25,'2019-4-3','n','小说类'),
('A0003','云边有个小卖部','许有飞','00003','清华大学出版社','b1-102','2021-1-1',37.5,25,'2019-4-3','y','小说类'),
('B0003','云边有个小卖部','许有飞','00003','清华大学出版社','b1-102','2021-1-1',37.5,25,'2019-4-3','n','小说类'),
('C0003','云边有个小卖部','许有飞','00003','清华大学出版社','b1-102','2021-1-1',37.5,25,'2019-4-3','y','小说类'),
('A0004','热眼旁观','程枝文','00004','中国言实出版社','a1-302','2020-2-1',40.5,27,'2018-2-7','n','文学类'),
('B0004','热眼旁观','程枝文','00009','天地出版社','a1-302','2020-2-1',40.5,27,'2018-2-7','y','文学类'),
('B0005','失落的卫星','刘子超','00010','中国言实出版社','b2-302','2019-4-23',38.25,25.5,'2016-5-12','n','科学类'),
('A0005','失落的卫星','刘子超','00005','天地出版社','b2-302','2019-4-23',38.25,25.5,'2016-5-12','n','科学类'),
('B0027','失落的卫星','刘子超','00010','中国言实出版社','b2-302','2019-4-23',38.25,25.5,'2016-5-12','y','科学类'),
('A0006','这个故宫很有趣','江上渔者','00006','天地出版社','B2-302','2020-6-7',30.6,20.4,'2018-4-5','n','史学类'),
('B0006','这个故宫很有趣','江上渔者','00011','人民出版社','B2-302','2020-6-7',30.6,20.4,'2018-4-5','y','史学类'),
('A0007','价值','张磊','00007','浙江教育出版社','c3-404','2020-5-4',38.31,25.54,'2019-5-13','y','哲学类'),
('A0008','民法总论','朱庆育','00008','北京大学出版社','C2-105','2021-3-13',27,18,'2015-6-19','n','法学类'),
('A0009','合同法总论','韩世远','00009','法律出版社','C2-512','2022-1-5',52.335,34.89,'2020-9-3','y','法学类'),
('A0010','恶之花','夏尔·波德莱尔','00010','江苏凤凰文艺出版社','a1-4-3','2022-5-4',51.75,34.5,'2019-4-9','y','文学类'),
('B0011','恶之花','夏尔·波德莱尔','00010','江苏凤凰文艺出版社','a1-4-3','2022-5-4',51.75,34.5,'2019-4-9','y','文学类'),
('C0010','恶之花','夏尔·波德莱尔','00010','江苏凤凰文艺出版社','a1-4-3','2022-5-4',51.75,34.5,'2019-4-9','n','文学类'),
('A0011','恶之花','夏尔·波德莱尔','00010','江苏凤凰文艺出版社','a1-4-3','2022-5-4',51.75,34.5,'2019-4-9','y','文学类'),
('A0012','轻轻的亲亲','赵十余','00012','江苏凤凰文艺出版社','c3-312','2019-5-7',31.5,21,'2012-9-12','n','儿童类'),
('B0024','国宴','王珩','00024','浙江文艺出版社','a3-204','2019-3-4',70.5,47,'2012-9-7','n','文化类'),
('B0025','国宴','王珩','00024','浙江文艺出版社','a3-204','2019-3-4',70.5,47,'2012-9-7','n','文化类'),
('B0026','国宴','王珩','00025','清华大学出版社','a3-204','2020-3-4',70.5,47,'2012-9-7','y','文化类')
insert into customer values('S0001','张三','男','1999-3-4','18937456283',210,0.8),
('S0002','李四','男','1989-4-8','16583930563',45,1),
('S0003','王五','男','2002-9-18','17394759603',89.835,1),
('S0004','江云','女','2000-1-1','18563728465',122.25,1),
('S0005','郑明','男','1999-4-6','14372893576',108.6,1),
('S0006','宋浩','男','1989-6-5','17483963974',90,1),
('S0007','刘工','男','1978-4-13','13597574936',96.75,1),
('S0008','贺云','男','1997-5-23','15496734692',31.5,1),
('S0009','王福','男','2003-4-29','19564967046',27,1)
insert into customer_book values('C0001','S0002','李四',1,'2022-2-2',45,1,'挪威的森林'),
('C0002','S0001','张三',1,'2022-4-5',105,1,'明朝那些事儿'),
('A0022','S0001','张三',1,'2022-4-5',105,1,'明朝那些事儿'),
('A0002','S0002','何种',1,'2021-5-9',105,1,'明朝那些事儿'),
('A0009','S0003','王五',1,'2021-4-4',52.335,1,'合同法总论'),
('C0003','S0003','王五',1,'2022-3-1',37.5,1,'云边有个小卖部'),
('A0011','S0004','江云',1,'2022-6-4',51.75,1,'恶之花'),
('B0026','S0004','江云',1,'2021-6-1',70.5,1,'国宴'),
('B0006','S0005','郑明',1,'2021-3-4',30.6,1,'这个故宫很有趣'),
('A0003','S0005','郑明',1,'2021-3-4',37.5,1,'云边有个小卖部'),
('B0004','S0005','郑明',1,'2021-3-4',40.5,1,'热眼旁观'),
('B0027','S0006','宋浩',1,'2022-5-6',38.25,1,'失落的卫星'),
('A0010','S0006','宋浩',1,'2022-5-6',51.75,1,'恶之花'),
('A0021','S0007','刘工',1,'2022-1-1',45,1,'挪威的森林'),
('B0011','S0007','刘工',1,'2021-7-6',51.75,1,'恶之花'),
('A0012','S0008','贺云',1,'2020-8-9',31.5,1,'轻轻的亲亲'),
('A0007','S0009','王福',1,'2022-5-4',27,1,'价值')
insert into firm values('F0001','世龙厂商','137849346373','马强','福建宁德'),
('F0002','原西厂商','14782936485','王杰','福建福州'),
('F0003','里洞厂商','137849346373','王三','福建厦门'),
('F0004','万力厂商','14782936485','秀豆','福建漳州'),
('F0005','龙宇厂商','17395739502','宋玉','山东德州'),
('F0006','新硕厂商','16594739502','刘珍妮','湖北武汉'),
('F0007','开双厂商','15759639502','王静','山东济南'),
('F0008','建益厂商','17604639502','赵丽','贵州贵阳')
insert into typ values('T0001','文学类'),
('T0002','史学类'),
('T0003','小说类'),
('T0004','科学类'),
('T0005','哲学类'),
('T0006','法学类'),
('T0007','儿童类')
insert into book_firm values('F0001','A0001','挪威的森林','2020-12-29',1,21,'00001','清华大学出版社'),
('F0001','A0021','挪威的森林','2020-12-29',1,21,'00021','北京联合出版社'),
('F0001','B0001','挪威的森林','2020-12-29',1,21,'00001','清华大学出版社'),
('F0001','C0001','挪威的森林','2020-12-29',1,21,'00001','清华大学出版社'),
('F0001','C0002','明朝那些事儿','2021-3-3',1,49,'00002','北京联合出版社'),
('F0001','A0002','明朝那些事儿','2021-3-3',1,49,'00002','北京联合出版社'),
('F0001','B0002','明朝那些事儿','2021-3-3',1,49,'00002','北京联合出版社'),
('F0001','A0022','明朝那些事儿','2021-3-3',1,49,'00022','清华大学出版社'),
('F0001','A0023','云边有个小卖部','2020-12-29',1,17.5,'00023','人民出版社'),
('F0001','A0003','云边有个小卖部','2020-12-29',1,17.5,'00003','清华大学出版社'),
('F0001','B0003','云边有个小卖部','2020-12-29',1,17.5,'00003','清华大学出版社'),
('F0001','C0003','云边有个小卖部','2020-12-29',1,17.5,'00003','清华大学出版社'),
('F0001','A0004','热眼旁观','2020-1-28',1,18.9,'00004','中国言实出版社'),
('F0001','B0004','热眼旁观','2020-1-28',1,18.9,'00009','天地出版社'),
('F0002','B0005','失落的卫星','2019-4-20',1,17.85,'00010','中国言实出版社'),
('F0002','A0005','失落的卫星','2019-4-20',1,17.85,'00005','天地出版社'),
('F0002','B0027','失落的卫星','2019-4-20',1,17.85,'00010','中国言实出版社'),
('F0002','A0006','这个故宫很有趣','2020-6-4',1,14.28,'00006','天地出版社'),
('F0002','B0006','这个故宫很有趣','2020-6-4',1,14.28,'00011','人民出版社'),
('F0002','A0007','价值','2020-5-1',1,17.878,'00007','浙江教育出版社'),
('F0002','A0008','民法总论','2021-3-10',1,12.6,'00008','北京大学出版社'),
('F0002','A0009','合同法总论','2022-1-2',1,24.423,'00009','法律出版社'),
('F0002','A0010','恶之花','2022-5-1',1,24.15,'00010','江苏凤凰文艺出版社'),
('F0002','B0011','恶之花','2022-5-1',1,24.15,'00010','江苏凤凰文艺出版社'),
('F0002','C0010','恶之花','2022-5-1',1,24.15,'00010','江苏凤凰文艺出版社'),
('F0002','A0011','恶之花','2022-5-1',1,24.15,'00010','江苏凤凰文艺出版社'),
('F0002','A0012','轻轻的亲亲','2019-5-3',1,14.7,'00012','江苏凤凰文艺出版社'),
('F0002','B0024','国宴','2019-3-1',1,32.9,'00024','浙江文艺出版社'),
('F0002','B0025','国宴','2019-3-1',1,32.9,'00024','浙江文艺出版社'),
('F0002','B0026','国宴','2020-3-1',1,32.9,'00025','清华大学出版社')
insert into manager values('M0001','万京','123456','17365945738','店长'),
('M0002','宋一','123456','16839570379','员工'),
('M0003','宋二','123456','15385936503','员工'),
('M0004','宋三','123456','17493750647','员工'),
('M0005','宋四','123456','17840673958','员工'),
('M0006','宋五','123456','17593596840','员工'),
('M0007','宋六','123456','15493460793','员工'),
('M0008','宋七','123456','13850476946','员工')
delete
from typ
where typ_id='T0001'
delete
from typ
where typ_name='儿童类'
--4.3.3 更新
update firm
set firm_name='建亿厂商'
where firm_name='建益厂商'
update customer
set name='江云云'
where member_id='S0003'
select *
from sj1
where typ_name = '文学类'
select name
from customer
where total_amount>=200
--4.3.5 统计
select COUNT(*) as Num
from customer_book
select
(select SUM(total_amount) from customer) - (select SUM(purchase_price)
from book_firm join sj1
on sj1.book_id=book_firm.book_id
where book_sign='y') as profit --统计利润
--4.3.6 视图
----特价书视图
CREATE VIEW tejiashu
AS
select * from sj1
where DATEDIFF(year, shelf_time,GETDATE())>1
WITH CHECK OPTION;
----热销书视图
CREATE VIEW rexiaoshu
AS
select top (3) book_name ,COUNT(*) as Num
from customer_book
group by book_name
order by Num desc
WITH CHECK OPTION;
-----在库书籍管理视图
CREATE VIEW onsalebook
AS
select book_id, book_name,shelf_num,shelf_time,selling_price,pricing
from sj1
where book_sign='n'
WITH CHECK OPTION;
--4.3.7 存储过程
-----查询具有优惠（折扣）的会员名单
CREATE PROC p_1
AS
SELECT member_id, name, total_amount,discount
FROM customer
WHERE discount != 1
EXEC p_1