订餐系统

/*
Navicat Premium Data Transfer

Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 50558
Source Host : localhost:3306
Source Schema : testtotest

Target Server Type : MySQL
Target Server Version : 50558
File Encoding : 65001

Date: 22/10/2017 16:21:44
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

---

– Table structure for order_number

---

DROP TABLE IF EXISTS order_number;
CREATE TABLE order_number (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
code varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX FKA(name) USING BTREE,
INDEX order number(code) USING BTREE,
INDEX id(id, code) USING BTREE,
CONSTRAINT FKA FOREIGN KEY (name) REFERENCES restaurant_menu (code) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 25 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

---

– Records of order_number

---

INSERT INTO order_number VALUES (1, ‘M1’, ‘A001’);
INSERT INTO order_number VALUES (2, ‘M2’, ‘A002’);
INSERT INTO order_number VALUES (3, ‘M3’, ‘A003’);
INSERT INTO order_number VALUES (4, ‘M4’, ‘A004’);
INSERT INTO order_number VALUES (5, ‘M5’, ‘B001’);
INSERT INTO order_number VALUES (6, ‘M6’, ‘B002’);
INSERT INTO order_number VALUES (7, ‘M7’, ‘B003’);
INSERT INTO order_number VALUES (8, ‘M8’, ‘B004’);
INSERT INTO order_number VALUES (9, ‘M9’, ‘B005’);
INSERT INTO order_number VALUES (10, ‘M10’, ‘C001’);
INSERT INTO order_number VALUES (11, ‘M11’, ‘C002’);
INSERT INTO order_number VALUES (12, ‘M12’, ‘C003’);
INSERT INTO order_number VALUES (13, ‘M13’, ‘C004’);
INSERT INTO order_number VALUES (14, ‘M14’, ‘D001’);
INSERT INTO order_number VALUES (15, ‘M15’, ‘D002’);
INSERT INTO order_number VALUES (16, ‘M16’, ‘D003’);
INSERT INTO order_number VALUES (17, ‘M17’, ‘D004’);
INSERT INTO order_number VALUES (18, ‘M18’, ‘D005’);
INSERT INTO order_number VALUES (19, ‘M19’, ‘E001’);
INSERT INTO order_number VALUES (20, ‘M20’, ‘E002’);
INSERT INTO order_number VALUES (21, ‘M21’, ‘E003’);
INSERT INTO order_number VALUES (22, ‘M22’, ‘E004’);
INSERT INTO order_number VALUES (23, ‘M23’, ‘E005’);
INSERT INTO order_number VALUES (24, ‘M24’, ‘E006’);

---

– Table structure for p_t_m

---

DROP TABLE IF EXISTS p_t_m;
CREATE TABLE p_t_m (
id int(11) NOT NULL AUTO_INCREMENT,
code varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
p_to varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
mean varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX FK_a(p_to) USING BTREE,
INDEX FK_b(mean) USING BTREE,
INDEX code(code) USING BTREE,
CONSTRAINT FK_a FOREIGN KEY (p_to) REFERENCES person_to (code) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT FK_b FOREIGN KEY (mean) REFERENCES restaurant_menu (code) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

---

– Records of p_t_m

---

INSERT INTO p_t_m VALUES (1, ‘E1’, ‘W1’, ‘M1’);
INSERT INTO p_t_m VALUES (2, ‘E2’, ‘W1’, ‘M2’);
INSERT INTO p_t_m VALUES (3, ‘E3’, ‘W2’, ‘M1’);
INSERT INTO p_t_m VALUES (4, ‘E4’, ‘W2’, ‘M2’);
INSERT INTO p_t_m VALUES (5, ‘E5’, ‘W3’, ‘M5’);
INSERT INTO p_t_m VALUES (6, ‘E6’, ‘W3’, ‘M8’);
INSERT INTO p_t_m VALUES (7, ‘E7’, ‘W4’, ‘M6’);
INSERT INTO p_t_m VALUES (8, ‘E8’, ‘W4’, ‘M8’);
INSERT INTO p_t_m VALUES (9, ‘E9’, ‘W5’, ‘M11’);
INSERT INTO p_t_m VALUES (10, ‘E10’, ‘W6’, ‘M14’);
INSERT INTO p_t_m VALUES (11, ‘E11’, ‘W6’, ‘M15’);
INSERT INTO p_t_m VALUES (12, ‘E12’, ‘W7’, ‘M15’);
INSERT INTO p_t_m VALUES (13, ‘E13’, ‘W7’, ‘M17’);
INSERT INTO p_t_m VALUES (14, ‘E14’, ‘W8’, ‘M22’);
INSERT INTO p_t_m VALUES (15, ‘E15’, ‘W8’, ‘M23’);

---

– Table structure for person_info

---

DROP TABLE IF EXISTS person_info;
CREATE TABLE person_info (
id int(11) NOT NULL AUTO_INCREMENT,
code varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
age int(11) NOT NULL,
telephone varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX code(code) USING BTREE,
INDEX name(name) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

---

– Records of person_info

---

INSERT INTO person_info VALUES (1, ‘P1’, ‘陶晓聪’, 16, ‘165456465’);
INSERT INTO person_info VALUES (2, ‘P2’, ‘谢晨晖’, 23, ‘5454464654’);
INSERT INTO person_info VALUES (3, ‘P3’, ‘王涛’, 25, ‘545646546’);
INSERT INTO person_info VALUES (4, ‘P4’, ‘罗潇’, 30, ‘546465465’);
INSERT INTO person_info VALUES (5, ‘P5’, ‘郑爽’, 23, ‘5456464655’);
INSERT INTO person_info VALUES (6, ‘P6’, ‘崔老师’, 30, ‘512522116’);
INSERT INTO person_info VALUES (7, ‘P7’, ‘陆老师’, 28, ‘5462546468’);
INSERT INTO person_info VALUES (8, ‘P8’, ‘邱老师’, 30, ‘54658226’);

---

– Table structure for person_order

---

DROP TABLE IF EXISTS person_order;
CREATE TABLE person_order (
id int(11) NOT NULL,
name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
order_1 varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
order_2 varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
order_3 varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
date date NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX fk_per(name) USING BTREE,
INDEX fk_or1(order_1) USING BTREE,
INDEX fk_or2(order_2) USING BTREE,
INDEX fk_or3(order_3) USING BTREE,
CONSTRAINT fk_per FOREIGN KEY (name) REFERENCES person_info (code) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT fk_or1 FOREIGN KEY (order_1) REFERENCES order_number (code) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT fk_or2 FOREIGN KEY (order_2) REFERENCES order_number (code) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT fk_or3 FOREIGN KEY (order_3) REFERENCES order_number (code) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

---

– Records of person_order

---

INSERT INTO person_order VALUES (1, ‘P1’, ‘A001’, ‘A002’, NULL, ‘2017-10-22’);
INSERT INTO person_order VALUES (2, ‘P2’, ‘A002’, ‘A003’, NULL, ‘2017-10-27’);
INSERT INTO person_order VALUES (3, ‘P3’, ‘B001’, ‘B004’, NULL, ‘2017-10-18’);
INSERT INTO person_order VALUES (4, ‘P4’, ‘B002’, ‘B004’, NULL, ‘2017-10-19’);
INSERT INTO person_order VALUES (5, ‘P5’, ‘C002’, NULL, NULL, ‘2017-10-22’);
INSERT INTO person_order VALUES (6, ‘P6’, ‘D001’, ‘D002’, NULL, ‘2017-10-22’);
INSERT INTO person_order VALUES (7, ‘P7’, ‘D002’, ‘D004’, NULL, ‘2017-10-22’);
INSERT INTO person_order VALUES (8, ‘P8’, ‘E004’, ‘E005’, NULL, ‘2017-10-22’);

---

– Table structure for person_to

---

DROP TABLE IF EXISTS person_to;
CREATE TABLE person_to (
id int(11) NOT NULL AUTO_INCREMENT,
code varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
person varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
restaurant varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX FK_3(person) USING BTREE,
INDEX FK_4(restaurant) USING BTREE,
INDEX code(code) USING BTREE,
CONSTRAINT FK_3 FOREIGN KEY (person) REFERENCES person_info (code) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT FK_4 FOREIGN KEY (restaurant) REFERENCES restaurant (code) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

---

– Records of person_to

---

INSERT INTO person_to VALUES (1, ‘W1’, ‘P1’, ‘R1’);
INSERT INTO person_to VALUES (2, ‘W2’, ‘P2’, ‘R1’);
INSERT INTO person_to VALUES (3, ‘W3’, ‘P3’, ‘R2’);
INSERT INTO person_to VALUES (4, ‘W4’, ‘P4’, ‘R2’);
INSERT INTO person_to VALUES (5, ‘W5’, ‘P5’, ‘R3’);
INSERT INTO person_to VALUES (6, ‘W6’, ‘P6’, ‘R4’);
INSERT INTO person_to VALUES (7, ‘W7’, ‘P7’, ‘R4’);
INSERT INTO person_to VALUES (8, ‘W8’, ‘P8’, ‘R5’);

---

– Table structure for r_m

---

DROP TABLE IF EXISTS r_m;
CREATE TABLE r_m (
id int(11) NOT NULL AUTO_INCREMENT,
code varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
r_name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
m_name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX FK_1(r_name) USING BTREE,
INDEX FK_2(m_name) USING BTREE,
CONSTRAINT FK_1 FOREIGN KEY (r_name) REFERENCES restaurant (code) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT FK_2 FOREIGN KEY (m_name) REFERENCES restaurant_menu (code) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 25 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

---

– Records of r_m

---

INSERT INTO r_m VALUES (1, ‘RM1’, ‘R1’, ‘M1’);
INSERT INTO r_m VALUES (2, ‘RM2’, ‘R1’, ‘M2’);
INSERT INTO r_m VALUES (3, ‘RM3’, ‘R1’, ‘M3’);
INSERT INTO r_m VALUES (4, ‘RM4’, ‘R1’, ‘M4’);
INSERT INTO r_m VALUES (5, ‘RM5’, ‘R2’, ‘M5’);
INSERT INTO r_m VALUES (6, ‘RM6’, ‘R2’, ‘M6’);
INSERT INTO r_m VALUES (7, ‘RM7’, ‘R2’, ‘M7’);
INSERT INTO r_m VALUES (8, ‘RM8’, ‘R2’, ‘M8’);
INSERT INTO r_m VALUES (9, ‘RM9’, ‘R2’, ‘M9’);
INSERT INTO r_m VALUES (10, ‘RM10’, ‘R3’, ‘M10’);
INSERT INTO r_m VALUES (11, ‘RM11’, ‘R3’, ‘M11’);
INSERT INTO r_m VALUES (12, ‘RM12’, ‘R3’, ‘M12’);
INSERT INTO r_m VALUES (13, ‘RM13’, ‘R3’, ‘M13’);
INSERT INTO r_m VALUES (14, ‘RM14’, ‘R4’, ‘M14’);
INSERT INTO r_m VALUES (15, ‘RM15’, ‘R4’, ‘M15’);
INSERT INTO r_m VALUES (16, ‘RM16’, ‘R4’, ‘M16’);
INSERT INTO r_m VALUES (17, ‘RM17’, ‘R4’, ‘M17’);
INSERT INTO r_m VALUES (18, ‘RM18’, ‘R4’, ‘M18’);
INSERT INTO r_m VALUES (19, ‘RM19’, ‘R5’, ‘M19’);
INSERT INTO r_m VALUES (20, ‘RM20’, ‘R5’, ‘M20’);
INSERT INTO r_m VALUES (21, ‘RM21’, ‘R5’, ‘M21’);
INSERT INTO r_m VALUES (22, ‘RM22’, ‘R5’, ‘M22’);
INSERT INTO r_m VALUES (23, ‘RM23’, ‘R5’, ‘M23’);
INSERT INTO r_m VALUES (24, ‘RM24’, ‘R5’, ‘M24’);

---

– Table structure for restaurant

---

DROP TABLE IF EXISTS restaurant;
CREATE TABLE restaurant (
id int(11) NOT NULL AUTO_INCREMENT,
code varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
address varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX code(code) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

---

– Records of restaurant

---

INSERT INTO restaurant VALUES (1, ‘R1’, ‘兰州拉面’, ‘跃龙南路一号’);
INSERT INTO restaurant VALUES (2, ‘R2’, ‘沙县小吃’, ‘跃龙南路二号’);
INSERT INTO restaurant VALUES (3, ‘R3’, ‘龙虾馆’, ‘跃龙南路三号’);
INSERT INTO restaurant VALUES (4, ‘R4’, ‘小聪饭店’, ‘跃龙南路四号’);
INSERT INTO restaurant VALUES (5, ‘R5’, ‘烧烤店’, ‘跃龙南路五号’);

---

– Table structure for restaurant_menu

---

DROP TABLE IF EXISTS restaurant_menu;
CREATE TABLE restaurant_menu (
id int(11) NOT NULL AUTO_INCREMENT,
code varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
price decimal(10, 2) NOT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX code(code) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 25 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

---

– Records of restaurant_menu

---

INSERT INTO restaurant_menu VALUES (1, ‘M1’, ‘牛肉拉面’, 15.00);
INSERT INTO restaurant_menu VALUES (2, ‘M2’, ‘刀削面’, 8.00);
INSERT INTO restaurant_menu VALUES (3, ‘M3’, ‘牛肉炒饭’, 10.00);
INSERT INTO restaurant_menu VALUES (4, ‘M4’, ‘孜然牛肉炒饭’, 12.00);
INSERT INTO restaurant_menu VALUES (5, ‘M5’, ‘大排饭’, 12.00);
INSERT INTO restaurant_menu VALUES (6, ‘M6’, ‘卤肉饭’, 13.00);
INSERT INTO restaurant_menu VALUES (7, ‘M7’, ‘鸡腿饭’, 14.00);
INSERT INTO restaurant_menu VALUES (8, ‘M8’, ‘烤肠’, 1.00);
INSERT INTO restaurant_menu VALUES (9, ‘M9’, ‘鸡蛋’, 2.50);
INSERT INTO restaurant_menu VALUES (10, ‘M10’, ‘麻辣龙虾’, 40.00);
INSERT INTO restaurant_menu VALUES (11, ‘M11’, ‘孜然龙虾’, 45.00);
INSERT INTO restaurant_menu VALUES (12, ‘M12’, ‘蒜蓉龙虾’, 50.00);
INSERT INTO restaurant_menu VALUES (13, ‘M13’, ‘普通口味龙虾’, 30.00);
INSERT INTO restaurant_menu VALUES (14, ‘M14’, ‘鱼香肉丝’, 20.00);
INSERT INTO restaurant_menu VALUES (15, ‘M15’, ‘木须肉’, 15.00);
INSERT INTO restaurant_menu VALUES (16, ‘M16’, ‘水煮鱼’, 20.00);
INSERT INTO restaurant_menu VALUES (17, ‘M17’, ‘宫爆鸡丁’, 23.00);
INSERT INTO restaurant_menu VALUES (18, ‘M18’, ‘海米冬瓜’, 28.00);
INSERT INTO restaurant_menu VALUES (19, ‘M19’, ‘烤羊肉串’, 2.00);
INSERT INTO restaurant_menu VALUES (20, ‘M20’, ‘烤金针菇’, 1.00);
INSERT INTO restaurant_menu VALUES (21, ‘M21’, ‘烤牛板筋’, 1.50);
INSERT INTO restaurant_menu VALUES (22, ‘M22’, ‘烤韭菜’, 1.00);
INSERT INTO restaurant_menu VALUES (23, ‘M23’, ‘烤鸡翅’, 4.00);
INSERT INTO restaurant_menu VALUES (24, ‘M24’, ‘骨肉相连’, 5.00);

SET FOREIGN_KEY_CHECKS = 1;