ssm订单管理系统1-数据库表结构和mysql语句

java通关整理汇总-Java基础、计算机网络、数据库、设计模式、框架、算法模板、笔试

---

ssm订单管理系统1-数据库表结构和mysql语句

ssm订单管理系统2-订单详情操作

ssm订单管理系统3-用户管理操作

ssm订单管理系统4-角色管理操作和资源权限操作

---

# -------------------------------------------------------订单操作相关表-------------------------------------------------
CREATE DATABASE Users
USE Users

SELECT * FROM users
# product产品表

EXPLAIN SELECT * FROM permission WHERE id IN (SELECT permissionId FROM role_permission WHERE roleId='1111')

`role_permission`

CREATE TABLE product(  
id VARCHAR(32) PRIMARY KEY,  
productNum VARCHAR(50) NOT NULL,  
productName VARCHAR(50),  
cityName VARCHAR(50),  
DepartureTime TIMESTAMP,
productPrice DOUBLE,  
productDesc VARCHAR(500),  
productStatus INT,  
CONSTRAINT product UNIQUE (id, productNum) 

)ENGINE=INNODB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8


 
INSERT INTO product (id, productnum, productname, cityname, departuretime, productprice, productdesc, productstatus) 
VALUES ('676C5BD1D35E429A8C2E114939C5685A', 'itcast-002', '北京三日游', '北京', '2018-10-10 10:10:00.000000', 1200, '不错的旅行', 1); 
INSERT INTO product (id, productnum, productname, cityname, departuretime, productprice, productdesc, productstatus) 
VALUES ('12B7ABF2A4C544568B0A7C69F36BF8B7', 'itcast-003', '上海五日游', '上海', '2018-04-25 14:30:00.000000', 1800, '魔都我来了', 0); 
INSERT INTO product (id, productnum, productname, cityname, departuretime, productprice, productdesc, productstatus) 
VALUES ('9F71F01CB448476DAFB309AA6DF9497F', 'itcast-001', '北京三日游', '北京', '2018-10-10 10:10:00.000000', 1200, '不错的旅行', 1);

# Member
CREATE TABLE member(       
id VARCHAR(32) PRIMARY KEY,       
NAME VARCHAR(20),
nickname VARCHAR(20),
phoneNum VARCHAR(20),
email VARCHAR(20)   
)

INSERT INTO MEMBER (id, NAME, nickname, phonenum, email) 
VALUES ('E61D65F673D54F68B0861025C69773DB', '张三', '小三', '18888888888', 'zs@163.com');


# Traveller
CREATE TABLE traveller(    
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(20),
sex VARCHAR(20),
phoneNum VARCHAR(20),
credentialsType INT,
credentialsNum VARCHAR(50),
travellerType INT 
)

INSERT INTO TRAVELLER (id, NAME, sex, phonenum, credentialstype, credentialsnum, travellertype) 
VALUES ('3FE27DF2A4E44A6DBC5D0FE4651D3D3E', '张龙', '男', '13333333333', 0, '123456789009876543', 0); 
INSERT INTO TRAVELLER (id, NAME, sex, phonenum, credentialstype, credentialsnum, travellertype) 
VALUES ('EE7A71FB6945483FBF91543DBE851960', '张小龙', '男', '15555555555', 0, '987654321123456789', 1);


# orders表

USE Users
 CREATE TABLE orders(  
 id VARCHAR(32) PRIMARY KEY,  
 orderNum VARCHAR(20) NOT NULL,  
 orderTime TIMESTAMP,  
 peopleCount INT,  
 orderDesc VARCHAR(500),  
 payType INT,  
 orderStatus INT,  
 productId VARCHAR(32),  
 memberId VARCHAR(32),  
 FOREIGN KEY (productId) REFERENCES product(id),  
 FOREIGN KEY (memberId) REFERENCES member(id) 
 )
 
 INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid)
 VALUES ('0E7231DC797C486290E8713CA3C6ECCC', '12345', '2018-03-02 12:00:00.000000', 2, '没什么', 0, 1, '676C5BD1D35E429A8C2E114939C5685A', 'E61D65F673D54F68B0861025C69773DB'); 
 INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid) 
 VALUES ('5DC6A48DD4E94592AE904930EA866AFA', '54321', '2018-03-02 12:00:00.000000', 2, '没什么', 0, 1, '676C5BD1D35E429A8C2E114939C5685A', 'E61D65F673D54F68B0861025C69773DB'); 
 INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid) 
 VALUES ('2FF351C4AC744E2092DCF08CFD314420', '67890', '2018-03-02 12:00:00.000000', 2, '没什么', 0, 1, '12B7ABF2A4C544568B0A7C69F36BF8B7', 'E61D65F673D54F68B0861025C69773DB'); 
 INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid) 
 VALUES ('A0657832D93E4B10AE88A2D4B70B1A28', '98765', '2018-03-02 12:00:00.000000', 2, '没什么', 0, 1, '12B7ABF2A4C544568B0A7C69F36BF8B7', 'E61D65F673D54F68B0861025C69773DB'); 
 INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid) 
 VALUES ('E4DD4C45EED84870ABA83574A801083E', '11111', '2018-03-02 12:00:00.000000', 2, '没什么', 0, 1, '12B7ABF2A4C544568B0A7C69F36BF8B7', 'E61D65F673D54F68B0861025C69773DB'); 
 INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid) 
 VALUES ('96CC8BD43C734CC2ACBFF09501B4DD5D', '22222', '2018-03-02 12:00:00.000000', 2, '没什么', 0, 1, '12B7ABF2A4C544568B0A7C69F36BF8B7', 'E61D65F673D54F68B0861025C69773DB'); 
 INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid) 
 VALUES ('55F9AF582D5A4DB28FB4EC3199385762', '33333', '2018-03-02 12:00:00.000000', 2, '没什么', 0, 1, '9F71F01CB448476DAFB309AA6DF9497F', 'E61D65F673D54F68B0861025C69773DB'); 
 INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid) 
 VALUES ('CA005CF1BE3C4EF68F88ABC7DF30E976', '44444', '2018-03-02 12:00:00.000000', 2, '没什么', 0, 1, '9F71F01CB448476DAFB309AA6DF9497F', 'E61D65F673D54F68B0861025C69773DB'); 
 INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid) 
 VALUES ('3081770BC3984EF092D9E99760FDABDE', '55555', '2018-03-02 12:00:00.000000', 2, '没什么', 0, 1, '9F71F01CB448476DAFB309AA6DF9497F', 'E61D65F673D54F68B0861025C69773DB');



# 旅客与订单之间是多对多关系，所以我们需要一张中间表（order_traveller）来描述。

`order_traveller`
 
INSERT INTO ORDER_TRAVELLER (orderid, travellerid) 
VALUES ('0E7231DC797C486290E8713CA3C6ECCC', '3FE27DF2A4E44A6DBC5D0FE4651D3D3E'); 
INSERT INTO ORDER_TRAVELLER (orderid, travellerid) 
VALUES ('2FF351C4AC744E2092DCF08CFD314420', '3FE27DF2A4E44A6DBC5D0FE4651D3D3E'); 
INSERT INTO ORDER_TRAVELLER (orderid, travellerid) 
VALUES ('3081770BC3984EF092D9E99760FDABDE', 'EE7A71FB6945483FBF91543DBE851960'); 
INSERT INTO ORDER_TRAVELLER (orderid, travellerid) 
VALUES ('55F9AF582D5A4DB28FB4EC3199385762', 'EE7A71FB6945483FBF91543DBE851960'); 
INSERT INTO ORDER_TRAVELLER (orderid, travellerid) 
VALUES ('5DC6A48DD4E94592AE904930EA866AFA', '3FE27DF2A4E44A6DBC5D0FE4651D3D3E'); 
INSERT INTO ORDER_TRAVELLER (orderid, travellerid) 
VALUES ('96CC8BD43C734CC2ACBFF09501B4DD5D', 'EE7A71FB6945483FBF91543DBE851960'); 
INSERT INTO ORDER_TRAVELLER (orderid, travellerid) 
VALUES ('A0657832D93E4B10AE88A2D4B70B1A28', '3FE27DF2A4E44A6DBC5D0FE4651D3D3E'); 
INSERT INTO ORDER_TRAVELLER (orderid, travellerid) 
VALUES ('CA005CF1BE3C4EF68F88ABC7DF30E976', 'EE7A71FB6945483FBF91543DBE851960'); 
INSERT INTO ORDER_TRAVELLER (orderid, travellerid) 
VALUES ('E4DD4C45EED84870ABA83574A801083E', 'EE7A71FB6945483FBF91543DBE851960');


# ---------------------------------------------------------------------------登录操作相关表-----------------------------------------------------------
USE Users

CREATE TABLE users(    
id VARCHAR(32) PRIMARY KEY,    
email VARCHAR(50) UNIQUE NOT NULL,    
username VARCHAR(50),    
PASSWORD VARCHAR(50),    
phoneNum VARCHAR(20),    
STATUS INT 

)

INSERT INTO users(id,email,username,PASSWORD,phoneNum,STATUS)
VALUES('E4DD44A801', 'cn@.com', 'liu','123', '13333333', 1)


# ---中间表   - 用户、角色多对多关系------------
CREATE TABLE role(    
id VARCHAR(32) PRIMARY KEY,    
roleName VARCHAR(50) ,    
roleDesc VARCHAR(50) 
)

INSERT INTO role(id,roleName,roleDesc)
VALUES('1111','ADMIN','vip');
INSERT INTO role(id,roleName,roleDesc)
VALUES('222','USER','vip')

CREATE TABLE users_role(    
userId VARCHAR(32),    
roleId VARCHAR(32),    
PRIMARY KEY(userId,roleId),    
FOREIGN KEY (userId) REFERENCES users(id),    
FOREIGN KEY (roleId) REFERENCES role(id)
)

INSERT INTO users_role(userId,roleId)
VALUES('111-222','1111');
INSERT INTO users_role(userId,roleId)
VALUES('111-222','222')
`users`


CREATE TABLE permission(    
id VARCHAR(32) PRIMARY KEY,    
permissionName VARCHAR(50) ,    
url VARCHAR(50) 
)

# ---中间表   - 权限、角色表多对多关系------------
CREATE TABLE role_permission(    
permissionId VARCHAR(32),    
roleId VARCHAR(32),    
PRIMARY KEY(permissionId,roleId),    
FOREIGN KEY (permissionId) REFERENCES permission(id),    
FOREIGN KEY (roleId) REFERENCES role(id) 
)