订餐库

最新推荐文章于 2024-06-08 19:39:09 发布

想学python的小菜鸟

最新推荐文章于 2024-06-08 19:39:09 发布

阅读量516

点赞数

分类专栏：课堂作业文章标签： mysql

本文链接：https://blog.csdn.net/lujiananjava/article/details/78313055

版权

课堂作业专栏收录该内容

17 篇文章 0 订阅

订阅专栏

navicat导出订餐系统代码

/*
Navicat MySQL Data Transfer

Source Server         : LJN
Source Server Version : 50556
Source Host           : localhost:3306
Source Database       : 订餐系统

Target Server Type    : MYSQL
Target Server Version : 50556
File Encoding         : 65001

Date: 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_customer
LEFT JOIN t_order ON t_order.customer_id=t_customer.id
LEFT JOIN t_restaurant ON t_order.restaurant_id=t_restaurant.id
LEFT JOIN t_order_content ON t_order.order_id=t_order_content.id
LEFT JOIN t_menu ON t_order_content.m_id=t_menu.id
GROUP 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;

想学python的小菜鸟

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
订餐库

订餐系统
复制链接

扫一扫

专栏目录

订餐库

“相关推荐”对你有帮助么？