forked from inkss/hotelbook-JavaWeb
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathsql.sql
More file actions
170 lines (158 loc) · 6.92 KB
/
sql.sql
File metadata and controls
170 lines (158 loc) · 6.92 KB
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
-- 建立hotelbook数据库
CREATE SCHEMA `hotelbook` DEFAULT CHARACTER SET utf8 ;
-- 建立登录表
CREATE TABLE `hotelbook`.`login` (
`loginId` INT NOT NULL AUTO_INCREMENT COMMENT '登录内部标示，主键、自动增长。',
`loginName` VARCHAR(45) NOT NULL COMMENT '登录用户名，前台唯一性的标识。不能重复。',
`loginPwd` VARCHAR(45) NOT NULL COMMENT '登录用户的密码，不能为空。',
`loginNickName` VARCHAR(45) NULL COMMENT '登录用户的昵称' ,
`loginAdmin` INT NULL COMMENT '权限，默认最高权限是0',
PRIMARY KEY (`loginId`),
UNIQUE INDEX `loginName_UNIQUE` (`loginName` ASC));
-- 为login表插入默认的管理员用户
INSERT INTO `hotelbook`.`login` (`loginName`,`loginPwd`,`loginNickName`,`loginAdmin`) VALUE ("root","eySvyLyA5UjWbE5/9yFxxQ==","管理员",0);
-- 建立日志表
CREATE TABLE `hotelbook`.`logInfo` (
`logId` INT NOT NULL AUTO_INCREMENT COMMENT '日志，主键、自动增长。',
`logName` VARCHAR(45) NOT NULL COMMENT '日志项目',
`loginId` INT NOT NULL COMMENT '用户 外键 login表字段值',
`loginName` VARCHAR(45) NULL COMMENT '用户名称',
`logDate` VARCHAR(45) NULL COMMENT '日志时间' ,
PRIMARY KEY (`logId`),
CONSTRAINT `fk_logInfo_1`
FOREIGN KEY (`loginId`)
REFERENCES `hotelbook`.`login` (`loginId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- 创建主体数据表
-- 核心数据库内容
-- 将不使用存储过程，仅仅用到外键/外联
-- 数据维护主要依靠后端维护
-- 楼层信息
CREATE TABLE `hotelbook`.`floorInfo` (
`floorId` INT NOT NULL AUTO_INCREMENT COMMENT '楼层编号',
`floorName` VARCHAR(45) NULL COMMENT '楼层名称',
PRIMARY KEY (`floorId`));
-- 客房类型
CREATE TABLE `hotelbook`.`roomType` (
`typeId` VARCHAR(45) NOT NULL COMMENT '类型编号',
`typeName` VARCHAR(45) NULL COMMENT '类型名称',
`price` VARCHAR(20) NULL COMMENT '价格',
`splicPrice` VARCHAR(20) NULL COMMENT '拼房价格',
`exceedance` INT NULL COMMENT '可超预定数',
`isSplice` VARCHAR(10) NULL COMMENT '是否可拼房',
PRIMARY KEY (`typeId`));
-- 客房信息
CREATE TABLE `hotelbook`.`roomInfo` (
`roomId` VARCHAR(45) NOT NULL COMMENT '客房编号',
`typeId` VARCHAR(45) NOT NULL COMMENT '类型编号',
`floorId` INT NOT NULL COMMENT '楼层编号',
`ratedNum` INT NULL COMMENT '额定人数',
`bedNum` INT NULL COMMENT '床数',
`roomDescription` VARCHAR(45) NULL COMMENT '客房描述',
`remark` VARCHAR(100) NULL COMMENT '备注',
`status` VARCHAR(10) NULL COMMENT '状态',
`isSplice` VARCHAR(10) NULL COMMENT '是否可拼房',
PRIMARY KEY (`roomId`),
INDEX `fk_roomInfo_1_idx` (`typeId` ASC),
INDEX `fk_roomInfo_2_idx` (`floorId` ASC),
CONSTRAINT `fk_roomInfo_1`
FOREIGN KEY (`typeId`)
REFERENCES `hotelbook`.`roomType` (`typeId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_roomInfo_2`
FOREIGN KEY (`floorId`)
REFERENCES `hotelbook`.`floorInfo` (`floorId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- 预订单
CREATE TABLE `hotelbook`.`orderInfo` (
`orderId` VARCHAR(45) NOT NULL COMMENT '预定单号',
`orderName` VARCHAR(45) NULL COMMENT '预定人',
`orderPhone` VARCHAR(45) NULL COMMENT '联系电话',
`orderIDcard` VARCHAR(18) NULL COMMENT '身份证号',
`typeId` VARCHAR(45) NOT NULL COMMENT '客房类型',
`arrireDate` VARCHAR(45) NULL COMMENT '抵店时间',
`leaveDate` VARCHAR(45) NULL COMMENT '离店时间',
`orderState` VARCHAR(20) NULL COMMENT '单据状态',
`checkNum` VARCHAR(45) NULL COMMENT '入住人数',
`roomId` VARCHAR(45) NULL COMMENT '客房编号',
`price` VARCHAR(20) NULL COMMENT '客房价格',
`checkPrice` VARCHAR(20) NULL COMMENT '入住价格',
`discount` INT NULL COMMENT '折扣',
`discountReason` VARCHAR(60) NULL COMMENT '折扣原因',
`addBed` VARCHAR(10) NULL COMMENT '是否加床',
`addBedPrice` VARCHAR(20) NULL COMMENT '加床价格',
`orderMoney` VARCHAR(20) NULL COMMENT '预收款',
`remark` VARCHAR(500) NULL COMMENT '备注',
`operatorId` VARCHAR(45) NULL COMMENT '操作员',
PRIMARY KEY (`orderId`),
INDEX `fk_orderInfo_1_idx` (`typeId` ASC),
CONSTRAINT `fk_orderInfo_1`
FOREIGN KEY (`typeId`)
REFERENCES `hotelbook`.`roomType` (`typeId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- 入住单
CREATE TABLE `hotelbook`.`checkInInfo` (
`checkId` VARCHAR(45) NOT NULL COMMENT '入住单号',
`orderId` VARCHAR(45) NULL COMMENT '预定单号',
`checkName` VARCHAR(45) NULL COMMENT '入住人',
`checkPhone` VARCHAR(45) NULL COMMENT '联系电话',
`checkIDcard` VARCHAR(18) NULL COMMENT '身份证号',
`typeId` VARCHAR(45) NOT NULL COMMENT '客房类型',
`arrireTime` VARCHAR(45) NULL COMMENT '抵店时间',
`leaveTime` VARCHAR(45) NULL COMMENT '离店时间',
`checkState` VARCHAR(20) NULL COMMENT '单据状态',
`checkNum` INT NULL COMMENT '入住人数',
`roomId` VARCHAR(45) NOT NULL COMMENT '客房编号',
`price` VARCHAR(20) NULL COMMENT '客房价格',
`checkPrice` VARCHAR(20) NULL COMMENT '入住价格',
`discount` INT NULL COMMENT '折扣',
`discountReason` VARCHAR(60) NULL COMMENT '折扣原因',
`addBed` VARCHAR(10) NULL COMMENT '是否加床',
`addBedPrice` VARCHAR(20) NULL COMMENT '加床价格',
`orderMoney` VARCHAR(20) NULL COMMENT '预收款',
`money` VARCHAR(20) NULL COMMENT '应收账款',
`isCheck` VARCHAR(10) NULL COMMENT '是否结账',
`checkMoney` VARCHAR(20) NULL COMMENT '结账金额',
`checkDate` VARCHAR(45) NULL COMMENT '结账日期',
`remark` VARCHAR(500) NULL COMMENT '备注',
`operatorId` VARCHAR(45) NULL COMMENT '操作员',
PRIMARY KEY (`checkId`),
INDEX `fk_checkInfo_1_idx` (`typeId` ASC),
INDEX `fk_checkInfo_2_idx` (`roomId` ASC),
CONSTRAINT `fk_checkInfo_1`
FOREIGN KEY (`typeId`)
REFERENCES `hotelbook`.`roomType` (`typeId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_checkInfo_2`
FOREIGN KEY (`roomId`)
REFERENCES `hotelbook`.`roomInfo` (`roomId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- 账单明细
CREATE TABLE `hotelbook`.`billInfo` (
`billId` INT NOT NULL COMMENT '账单编号',
`checkId` VARCHAR(45) NOT NULL COMMENT '入住单号',
`costMoney` VARCHAR(20) NULL COMMENT '消费金额',
`costDate` VARCHAR(45) NULL COMMENT '消费时间',
`remark` VARCHAR(100) NULL COMMENT '备注',
PRIMARY KEY (`billId`),
INDEX `fk_billInfo_1_idx` (`checkId` ASC),
CONSTRAINT `fk_billInfo_1`
FOREIGN KEY (`checkId`)
REFERENCES `hotelbook`.`checkInInfo` (`checkId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- 权限管理
CREATE TABLE `hotelbook`.`authInfo` (
`authId` INT NOT NULL AUTO_INCREMENT COMMENT '权限编号',
`authItem` VARCHAR(45) NULL COMMENT '权限项',
`isRead` VARCHAR(45) NULL COMMENT '可读',
`isWrite` VARCHAR(45) NULL COMMENT '可写',
`isChange` VARCHAR(45) NULL COMMENT '可改',
`isDelete` VARCHAR(45) NULL COMMENT '可删',
PRIMARY KEY (`authId`));