SSM框架整合进阶版MySql数据库操作（协同版

mysql所有数据库建表、插入数据操作，id均使用自增长，并且为int类型，实体类中创建为String去使用没有任何关系。

SHOW DATABASES;
CREATE DATABASE heima_ssm;
USE heima_ssm;
SHOW DATABASES;
SELECT DATABASE();
CREATE TABLE product
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    productNum VARCHAR(50) UNIQUE NOT NULL,
    productName VARCHAR(50),
    cityName VARCHAR(50),
    DepartureTime TIMESTAMP,
    productPrice DOUBLE,
    productDesc VARCHAR(500),
    productStatus INT
);
INSERT INTO PRODUCT (id, productnum, productname, cityname, departuretime, productprice,
                     productdesc, productstatus)
VALUES (NULL,'itcast-002', '北京三日游', '北京', '20181010101000', 1200, '不错的旅行', 1);
INSERT INTO PRODUCT (id, productnum, productname, cityname, departuretime, productprice,
                     productdesc, productstatus)
VALUES (NULL, 'itcast-003', '上海五日游', '上海', '20180425143000', 1800, '魔都我来了', 0);
INSERT INTO PRODUCT (id, productnum, productname, cityname, departuretime, productprice,
                     productdesc, productstatus)
VALUES (NULL, 'itcast-001', '北京三日游', '北京', '20181010101000', 1200, '不错的旅行', 1);

CREATE TABLE member(
       id INT PRIMARY KEY AUTO_INCREMENT,
       NAME VARCHAR(20),
       nickname VARCHAR(20),
       phoneNum VARCHAR(20),
       email VARCHAR(20) 
);
INSERT INTO MEMBER (id, NAME, nickname, phonenum, email)
VALUES (1, '张三', '小三', '18888888888', 'zs@163.com');-- 旅客

CREATE TABLE orders(
  id INT PRIMARY KEY AUTO_INCREMENT,
  orderNum VARCHAR(20) NOT NULL UNIQUE,
  orderTime TIMESTAMP,
  peopleCount INT,
  orderDesc VARCHAR(500),
  payType INT,
  orderStatus INT,
  productId INT,
  memberId INT,
  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 (1, '12345', '20181203120000', 2, '没什么', 0, 1, 1, 1);
INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid)
VALUES (2, '54321', '20181203120000', 2, '没什么', 0, 1, 1, 1);
INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid)
VALUES (3, '67890', '20181203120000', 2, '没什么', 0, 1, 2, 1);
INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid)
VALUES (4, '98765', '20181203120000', 2, '没什么', 0, 1, 2, 1);
INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid)
VALUES (5, '11111', '20181203120000', 2, '没什么', 0, 1, 2, 1);
INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid)
VALUES (6, '22222', '20181203120000', 2, '没什么', 0, 1, 2, 1);
INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid)
VALUES (7, '33333', '20181203120000', 2, '没什么', 0, 1, 3, 1);
INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid)
VALUES (8, '44444', '20181203120000', 2, '没什么', 0, 1, 3, 1);
INSERT INTO ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid)
VALUES (9, '55555', '20181203120000', 2, '没什么', 0, 1, 3, 1);


CREATE TABLE traveller(
  id INT PRIMARY KEY AUTO_INCREMENT,
  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 (1, '张龙', '男', '13333333333', 0, '123456789009876543', 0);
INSERT INTO TRAVELLER (id, NAME, sex, phonenum, credentialstype, credentialsnum, travellertype)
VALUES (2, '张小龙', '男', '15555555555', 0, '987654321123456789', 1);

CREATE TABLE order_traveller(
  orderId INT,
  travellerId INT,
  PRIMARY KEY (orderId,travellerId),
  FOREIGN KEY (orderId) REFERENCES orders(id),
  FOREIGN KEY (travellerId) REFERENCES traveller(id)
)
INSERT INTO ORDER_TRAVELLER (orderid, travellerid)
VALUES (1, 1);
INSERT INTO ORDER_TRAVELLER (orderid, travellerid)
VALUES (3, 1);
INSERT INTO ORDER_TRAVELLER (orderid, travellerid)
VALUES (9, 2);
INSERT INTO ORDER_TRAVELLER (orderid, travellerid)
VALUES (7, 2);
INSERT INTO ORDER_TRAVELLER (orderid, travellerid)
VALUES (2, 1);
INSERT INTO ORDER_TRAVELLER (orderid, travellerid)
VALUES (6, 2);
INSERT INTO ORDER_TRAVELLER (orderid, travellerid)
VALUES (4, 1);
INSERT INTO ORDER_TRAVELLER (orderid, travellerid)
VALUES (8, 2);
INSERT INTO ORDER_TRAVELLER (orderid, travellerid)
VALUES (5, 2);

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

CREATE TABLE role(
id INT PRIMARY KEY AUTO_INCREMENT,
roleName VARCHAR(50) ,
roleDesc VARCHAR(50)
)

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

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

CREATE TABLE role_permission(
permissionId INT,
roleId INT,
PRIMARY KEY(permissionId,roleId),
FOREIGN KEY (permissionId) REFERENCES permission(id),
FOREIGN KEY (roleId) REFERENCES role(id)
)