订餐库
来源：互联网 发布：截面数据是什么 编辑：程序博客网 时间：2024/06/16 23:38
navicat导出订餐系统代码
/*Navicat MySQL Data TransferSource Server : LJNSource Server Version : 50556Source Host : localhost:3306Source Database : 订餐系统Target Server Type : MYSQLTarget Server Version : 50556File Encoding : 65001Date: 2017-10-22 20:27:02*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for t_customer-- ----------------------------DROP TABLE IF EXISTS `t_customer`;CREATE TABLE `t_customer` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(225) NOT NULL, `tel` varchar(225) NOT NULL, `address` varchar(225) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;-- ------------------------------ Records of t_customer-- ----------------------------INSERT INTO `t_customer` VALUES ('1', '陆佳南', '13702858114', '江苏省南通市XXX');INSERT INTO `t_customer` VALUES ('2', '陆佳楠', '15728594769', '江苏省南京市XXX');INSERT INTO `t_customer` VALUES ('3', 'MR.LU', '15392761852', '江苏省苏州市XXX');INSERT INTO `t_customer` VALUES ('4', 'LU佳南', '13286283423', '江苏省扬州市XXX');INSERT INTO `t_customer` VALUES ('5', '小王', '12312314', '江苏省扬州市XXX');INSERT INTO `t_customer` VALUES ('6', '小李', '231241', '江苏省扬州市XXX');INSERT INTO `t_customer` VALUES ('7', '晓东', '2141253462', '江苏省扬州市XXX');INSERT INTO `t_customer` VALUES ('8', '王丽', '234262341', '江苏省扬州市XXX');INSERT INTO `t_customer` VALUES ('9', '蒋欣', '31243214', '江苏省扬州市XXX');INSERT INTO `t_customer` VALUES ('10', '王老头', '321412412', '江苏省扬州市XXX');-- ------------------------------ Table structure for t_menu-- ----------------------------DROP TABLE IF EXISTS `t_menu`;CREATE TABLE `t_menu` ( `id` int(11) NOT NULL AUTO_INCREMENT, `m_id` int(11) NOT NULL, `dish` varchar(225) NOT NULL, `price` int(11) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;-- ------------------------------ Records of t_menu-- ----------------------------INSERT INTO `t_menu` VALUES ('1', '1', '麻辣小龙虾', '50');INSERT INTO `t_menu` VALUES ('2', '2', '大盘鸡', '20');INSERT INTO `t_menu` VALUES ('3', '3', '红烧猪蹄', '40');INSERT INTO `t_menu` VALUES ('4', '4', '美味酸菜鱼', '35');INSERT INTO `t_menu` VALUES ('5', '5', '北京烤鸭', '30');INSERT INTO `t_menu` VALUES ('6', '6', '孜然羊肉', '60');INSERT INTO `t_menu` VALUES ('7', '7', '油焖茄子', '20');INSERT INTO `t_menu` VALUES ('8', '8', '回锅肉', '30');INSERT INTO `t_menu` VALUES ('9', '9', '奶酪玉米', '30');INSERT INTO `t_menu` VALUES ('10', '10', '烤乳猪', '80');-- ------------------------------ Table structure for t_order-- ----------------------------DROP TABLE IF EXISTS `t_order`;CREATE TABLE `t_order` ( `id` int(11) NOT NULL AUTO_INCREMENT, `tab_number` varchar(225) NOT NULL, `order_number` int(11) NOT NULL, `order_id` int(11) NOT NULL, `customer_id` int(11) NOT NULL, `number` int(11) NOT NULL, `time` datetime NOT NULL, `restaurant_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `fk_restaurant` (`restaurant_id`), KEY `fk_order_content` (`order_id`), KEY `fk_customer` (`customer_id`), CONSTRAINT `fk_customer` FOREIGN KEY (`customer_id`) REFERENCES `t_customer` (`id`), CONSTRAINT `fk_order_content` FOREIGN KEY (`order_id`) REFERENCES `t_order_content` (`id`), CONSTRAINT `fk_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `t_restaurant` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;-- ------------------------------ Records of t_order-- ----------------------------INSERT INTO `t_order` VALUES ('1', 'A1', '2017101801', '1', '1', '4', '2017-10-18 17:17:58', '1');INSERT INTO `t_order` VALUES ('2', 'A1', '2017101801', '2', '1', '4', '2017-10-18 17:19:49', '1');INSERT INTO `t_order` VALUES ('3', 'A1', '2017101801', '3', '1', '4', '2017-10-18 17:20:43', '1');INSERT INTO `t_order` VALUES ('4', 'B1', '201111', '5', '2', '2', '2017-10-18 17:26:32', '2');INSERT INTO `t_order` VALUES ('5', 'B1', '201111', '6', '2', '2', '2017-10-18 17:27:11', '2');INSERT INTO `t_order` VALUES ('6', 'C1', '2323', '1', '3', '3', '2017-10-18 17:27:40', '3');INSERT INTO `t_order` VALUES ('7', 'C1', '2323', '3', '3', '3', '2017-10-18 17:28:20', '3');INSERT INTO `t_order` VALUES ('8', 'D1', '1313', '2', '4', '1', '2017-10-18 17:29:01', '3');INSERT INTO `t_order` VALUES ('9', 'D1', '1313', '6', '4', '1', '2017-10-18 17:31:28', '3');INSERT INTO `t_order` VALUES ('10', 'D1', '1313', '8', '4', '1', '2017-10-19 17:11:01', '3');INSERT INTO `t_order` VALUES ('11', 'E1', '1413', '5', '5', '1', '2017-10-19 17:18:42', '4');INSERT INTO `t_order` VALUES ('12', 'F1', '1513', '6', '6', '2', '2017-10-19 17:19:23', '5');INSERT INTO `t_order` VALUES ('13', 'G1', '1163', '7', '7', '1', '2017-10-19 17:19:56', '7');INSERT INTO `t_order` VALUES ('14', 'H1', '1653', '8', '8', '2', '2017-10-19 17:20:17', '8');INSERT INTO `t_order` VALUES ('15', 'H1', '1653', '9', '8', '2', '2017-10-19 17:20:39', '8');INSERT INTO `t_order` VALUES ('16', 'I1', '2234', '5', '9', '1', '2017-10-19 17:21:10', '9');INSERT INTO `t_order` VALUES ('17', 'I1', '2234', '7', '9', '1', '2017-10-19 17:21:35', '9');INSERT INTO `t_order` VALUES ('18', 'J1', '3344', '10', '10', '1', '2017-10-19 17:22:01', '10');-- ------------------------------ Table structure for t_order_content-- ----------------------------DROP TABLE IF EXISTS `t_order_content`;CREATE TABLE `t_order_content` ( `id` int(11) NOT NULL AUTO_INCREMENT, `m_id` int(11) NOT NULL, `piece` int(11) NOT NULL, `price` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `fk_menu` (`m_id`), CONSTRAINT `fk_menu` FOREIGN KEY (`m_id`) REFERENCES `t_menu` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;-- ------------------------------ Records of t_order_content-- ----------------------------INSERT INTO `t_order_content` VALUES ('1', '1', '2', '50');INSERT INTO `t_order_content` VALUES ('2', '2', '1', '30');INSERT INTO `t_order_content` VALUES ('3', '3', '1', '20');INSERT INTO `t_order_content` VALUES ('4', '4', '1', '60');INSERT INTO `t_order_content` VALUES ('5', '5', '2', '40');INSERT INTO `t_order_content` VALUES ('6', '6', '2', '50');INSERT INTO `t_order_content` VALUES ('7', '7', '1', '30');INSERT INTO `t_order_content` VALUES ('8', '8', '2', '60');INSERT INTO `t_order_content` VALUES ('9', '9', '3', '40');INSERT INTO `t_order_content` VALUES ('10', '10', '1', '50');-- ------------------------------ Table structure for t_restaurant-- ----------------------------DROP TABLE IF EXISTS `t_restaurant`;CREATE TABLE `t_restaurant` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(225) NOT NULL, `tel` varchar(225) NOT NULL, `address` varchar(225) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;-- ------------------------------ Records of t_restaurant-- ----------------------------INSERT INTO `t_restaurant` VALUES ('1', '川王饭店', '1399144388', '南大街');INSERT INTO `t_restaurant` VALUES ('2', '金陵大饭店', '1425623562', '世贸大厦');INSERT INTO `t_restaurant` VALUES ('3', '苗家山寨', '1355446279', '圆融广场');INSERT INTO `t_restaurant` VALUES ('4', '辛香汇', '1322212312', '鸿鸣摩尔');INSERT INTO `t_restaurant` VALUES ('5', '鱼胖子饭店', '1542312312', '五角大楼');INSERT INTO `t_restaurant` VALUES ('6', '傣妹火锅', '1742318532', '北大街');INSERT INTO `t_restaurant` VALUES ('7', '麻辣无情火锅店', '1231241231', '崇川');INSERT INTO `t_restaurant` VALUES ('8', '海底捞', '3123121211', '八佰伴');INSERT INTO `t_restaurant` VALUES ('9', '麦哲伦', '1231312312', '八佰伴');INSERT INTO `t_restaurant` VALUES ('10', '小王饭店', '1422114412', '街头小巷');sql查询语句
-- 查询某人在某餐厅所点的菜品总共花了多少钱SELECT t_customer.`name`AS '姓名',t_restaurant.`name` AS '消费饭店',GROUP_CONCAT(t_menu.dish) AS '所点菜单',SUM(t_order_content.price*t_order_content.piece) AS '总消费数'FROM t_customerLEFT JOIN t_order ON t_order.customer_id=t_customer.idLEFT JOIN t_restaurant ON t_order.restaurant_id=t_restaurant.idLEFT JOIN t_order_content ON t_order.order_id=t_order_content.idLEFT JOIN t_menu ON t_order_content.m_id=t_menu.idGROUP BY(t_customer.`name`);-- 查询姓名后面两个字是佳南的人SELECT * FROM t_customer WHERE name LIKE '%佳南';-- 查询菜单价格按照正序排列SELECT * FROM t_menu ORDER BY (price);-- 查找价格在40到60之间的菜单SELECT * FROM t_menu WHERE price BETWEEN 40 AND 60;-- 查询从菜单第三行开始，后面3个菜单SELECT * FROM t_menu LIMIT 2,3;-- 查询餐厅的数量SELECT COUNT(t_restaurant.`name`) AS '所有餐厅数量' FROM t_restaurant; 
