订餐库脚本
/*
Navicat MySQL Data Transfer
Source Server : ntqn_96
Source Server Version : 50556
Source Host : localhost:3306
Source Database : restaurant
Target Server Type : MYSQL
Target Server Version : 50556
File Encoding : 65001
Date: 2017-10-20 13:52:11
*/
SET FOREIGN_KEY_CHECKS=0;
– Table structure for account
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`ID` int(11) NOT NULL,
`food_name` varchar(255) DEFAULT NULL,
`food_code` varchar(255) DEFAULT NULL,
`unit_price` varchar(255) DEFAULT NULL,
`amount` varchar(255) DEFAULT NULL,
`total_price` decimal(10,0) DEFAULT NULL,
`time` datetime DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
– Records of account
INSERT INTO `account` VALUES ('1', '红烧肉', '27', '15', '2', '30', '2017-10-18 17:51:50');
INSERT INTO `account` VALUES ('2', '蒜苗炒肉丝', '14', '10', '1', '10', '2017-10-18 17:51:50');
INSERT INTO `account` VALUES ('3', '清蒸鲫鱼', '31', '20', '1', '20', '2017-10-19 17:02:35');
INSERT INTO `account` VALUES ('4', '青椒土豆丝', '11', '10', '1', '11', '2017-10-19 21:04:32');
INSERT INTO `account` VALUES ('5', '红烧猪蹄', '23', '30', '2', '60', '2017-10-19 21:04:52');
INSERT INTO `account` VALUES ('6', '西红柿蛋汤', '05', '8', '1', '16', '2017-10-19 21:05:17');
INSERT INTO `account` VALUES ('7', '香菇肉丝', '21', '18', '1', '18', '2017-10-19 21:07:08');
INSERT INTO `account` VALUES ('8', '茶树菇炒肉片', '24', '20', '2', '20', '2017-10-19 21:07:24');
INSERT INTO `account` VALUES ('9', '红烧黑鱼', '32', '25', '1', '25', '2017-10-19 21:07:47');
INSERT INTO `account` VALUES ('10', '排骨汤', '09', '15', '1', '15', '2017-10-19 21:08:33');
– Table structure for menu
DROP TABLE IF EXISTS `menu`;
CREATE TABLE `menu` (
`ID` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`code` varchar(255) DEFAULT NULL,
`unit_price` decimal(10,0) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
– Records of menu
INSERT INTO menu
VALUES (‘1’, ‘青椒土豆丝’, ‘11’, ‘10’);
INSERT INTO menu
VALUES (‘2’, ‘红烧猪蹄’, ‘23’, ‘30’);
INSERT INTO menu
VALUES (‘3’, ‘西红柿蛋汤’, ‘05’, ‘8’);
INSERT INTO menu
VALUES (‘4’, ‘清蒸鲫鱼’, ‘31’, ‘20’);
INSERT INTO menu
VALUES (‘5’, ‘红烧黑鱼’, ‘32’, ‘25’);
INSERT INTO menu
VALUES (‘6’, ‘酸菜鱼’, ‘33’, ‘30’);
INSERT INTO menu
VALUES (‘7’, ‘香菇肉丝’, ‘21’, ‘18’);
INSERT INTO menu
VALUES (‘8’, ‘茶树菇炒肉片’, ‘24’, ‘20’);
INSERT INTO menu
VALUES (‘9’, ‘紫菜蛋汤’, ‘07’, ‘7’);
INSERT INTO menu
VALUES (‘10’, ‘排骨汤’, ‘09’, ‘15’);
INSERT INTO menu
VALUES (‘11’, ‘蒜苗炒肉丝’, ‘14’, ‘15’);—————————-
– Table structure for order_details
DROP TABLE IF EXISTS `order_details`;
CREATE TABLE `order_details` (
`ID` int(11) NOT NULL,
`code` varchar(255) DEFAULT NULL,
`food_name` varchar(255) DEFAULT NULL,
`food_code` varchar(255) DEFAULT NULL,
`amount` varchar(255) DEFAULT NULL,
`order_time` datetime DEFAULT NULL,
`table_code` varchar(255) DEFAULT NULL,
`table_amount` varchar(255) DEFAULT NULL,
`table_for_number` int(11) DEFAULT NULL,
`total_people` varchar(255) DEFAULT NULL,
`other_remarks` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
– Records of order_details
INSERT INTO `order_details` VALUES ('1', '201710181648', '红烧肉', '27', '2', '2017-10-18 00:00:00', '88', '1', '4', '4', '无');
INSERT INTO `order_details` VALUES ('2', '201710181648', '蒜苗炒肉丝', '33', '1', '2017-10-18 16:50:47', '88', '1', '4', '4', '无');
INSERT INTO `order_details` VALUES ('3', '201710181648', '红烧猪蹄', '23', '2', '2017-10-18 16:50:47', '88', '1', '4', '4', '无');
INSERT INTO `order_details` VALUES ('4', '201710181648', '西红柿蛋汤', '05', '1', '2017-10-18 16:50:47', '88', '1', '4', '4', '无');
INSERT INTO `order_details` VALUES ('5', '201710181648', '清蒸鲫鱼', '31', '1', '2017-10-18 16:50:47', '88', '1', '4', '4', '无');
INSERT INTO `order_details` VALUES ('6', '201710181648', '青椒土豆丝', '11', '1', '2017-10-18 16:50:47', '88', '1', '4', '4', '无');
INSERT INTO `order_details` VALUES ('7', '201710181648', '香菇肉丝', '21', '1', '2017-10-18 16:50:47', '88', '1', '4', '4', '无');
INSERT INTO `order_details` VALUES ('8', '201710181648', '茶树菇炒肉片', '24', '2', '2017-10-18 16:50:47', '88', '1', '4', '4', '无');
INSERT INTO `order_details` VALUES ('9', '201710181648', '红烧黑鱼', '32', '1', '2017-10-18 16:50:47', '88', '1', '4', '4', '无');
INSERT INTO `order_details` VALUES ('10', '201710181648', '排骨汤', '09', '1', '2017-10-18 16:50:47', '88', '1', '4', '4', '无');
– Table structure for restaurant
DROP TABLE IF EXISTS `restaurant`;
CREATE TABLE `restaurant` (
`ID` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`phone` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`menu_style` varchar(255) DEFAULT NULL,
`network_evaluation` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
– Records of restaurant
INSERT INTO `restaurant` VALUES ('1', 'XX家常菜馆', '182-xxxx-xxxx', '南通市崇川区', '家常菜', '米其林3星好评');
INSERT INTO `restaurant` VALUES ('2', 'XX家常菜馆', '188-xxxx-xxxx', '南通市港闸区', '家常菜', '米其林3星好评');
INSERT INTO `restaurant` VALUES ('3', 'XX家常菜馆', '137-xxxx-xxxx', '南通市通州区', '家常菜', '米其林3星好评');
INSERT INTO `restaurant` VALUES ('4', 'XX家常菜馆', '130-xxxx-xxxx', '南通市中南世纪城', '家常菜', '米其林3星好评');
INSERT INTO `restaurant` VALUES ('5', 'XX家常菜馆', '182-xxxx-xxxx', '南通市滨海园区', '家常菜', '米其林3星好评');
INSERT INTO `restaurant` VALUES ('6', 'XX家常菜馆', '133-xxxx-xxxx', '南通市启东市', '家常菜', '米其林3星好评');
INSERT INTO `restaurant` VALUES ('7', 'XX家常菜馆', '188-xxxx-xxxx', '南通市如皋市', '家常菜', '米其林3星好评');
INSERT INTO `restaurant` VALUES ('8', 'XX家常菜馆', '182-xxxx-xxxx', '南通市海门市', '家常菜', '米其林3星好评');
INSERT INTO `restaurant` VALUES ('9', 'XX家常菜馆', '180-xxxx-xxxx', '南通市海安县', '家常菜', '米其林3星好评');
INSERT INTO `restaurant` VALUES ('10', 'XX家常菜馆', '188-xxxx-xxxx', '南通市如东县', '家常菜', '米其林3星好评');
– Table structure for vip_system
DROP TABLE IF EXISTS `vip_system`;
CREATE TABLE `vip_system` (
`ID` int(11) NOT NULL,
`VIP_code` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`sex` varchar(50) DEFAULT NULL,
`VIP_level` varchar(255) DEFAULT NULL,
`phone` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`total_consumption` varchar(255) DEFAULT NULL,
`recent_consumption` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `VIP_code` (`VIP_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
– Records of vip_system
INSERT INTO `vip_system` VALUES ('1', '8888', '张三', '男', '白金', '130-xxxx-xxxx', '南通市', '9786', '888');
INSERT INTO `vip_system` VALUES ('2', '1231', '李四', '女', '普通', '137-xxxx-xxxx', '', '800', '50');
INSERT INTO `vip_system` VALUES ('3', '88888', '王二', '男', '钻石', '188-xxxx-8888', null, '92345', '9999');
INSERT INTO `vip_system` VALUES ('4', '2341', '龙五', '男', '普通', '130-xxxx-1234', '海安', '588', '63');
INSERT INTO `vip_system` VALUES ('5', '2344', '李云', '男', '普通', '130-xxxx-3334', '南通市崇川区', '678', '83');
INSERT INTO `vip_system` VALUES ('6', '6666', '赵四', '男', '黄金', '188-xxxx-6234', '南通市如皋市', '1888', '324');
INSERT INTO `vip_system` VALUES ('7', '2232', '龙四', '男', '白银', '137-xxxx-7734', '南通市如东县', '967', '142');
INSERT INTO `vip_system` VALUES ('8', '2555', '张六', '男', '白银', '130-xxxx-1234', '南通市启东市', '456', '63');
INSERT INTO `vip_system` VALUES ('9', '8346', '和二', '男', '钻石', '188-xxxx-8834', '南通市海门市', '99786', '3456');
INSERT INTO `vip_system` VALUES ('10', '8666', '万三', '男', '白金', '182-xxxx-5634', '南通市通州区', '8664', '3567');
SQL 语句
– 指定字段的数据记录查询
SELECT name FROM menu WHERE ID ORDER BY unit_price ASC
– 查询中指定表的别名,定义别名
SELECT
m.name AS '名称',
m.`unit_price` AS '单价'
FROM menu AS m
– 查询所有数据
SELECT * FROM menu WHERE ID = 2
– 避免重复数据的查询
SELECT DISTINCT name FROM menu
– 查询数据处理,进行字符串拼接
SELECT name,CONCAT('¥',unit_price) AS '人民币',CONCAT('$',unit_price/6) AS '美元' FROM menu
– 表vip_system插入数据
INSERT INTO vip_system(ID,VIP_code,name,sex,VIP_level,phone,address,total_consumption,recent_consumption)
VALUES('4','2341','龙五','男','普通','130-xxxx-1234','南通市海安县','588','63')
INSERT INTO vip_system(ID,VIP_code,name,sex,VIP_level,phone,address,total_consumption,recent_consumption)
VALUES('5','2344','李云','男','普通','130-xxxx-3334','南通市崇川区','678','83')
INSERT INTO vip_system(ID,VIP_code,name,sex,VIP_level,phone,address,total_consumption,recent_consumption)
VALUES('6','6666','赵四','男','黄金','188-xxxx-6234','南通市如皋市','1888','324')
INSERT INTO vip_system(ID,VIP_code,name,sex,VIP_level,phone,address,total_consumption,recent_consumption)
VALUES('7','2232','龙四','男','白银','137-xxxx-7734','南通市如东县','967','142')
INSERT INTO vip_system(ID,VIP_code,name,sex,VIP_level,phone,address,total_consumption,recent_consumption)
VALUES('8','2555','张六','男','白银','130-xxxx-1234','南通市启东市','456','63')
INSERT INTO vip_system(ID,VIP_code,name,sex,VIP_level,phone,address,total_consumption,recent_consumption)
VALUES('9','8346','和二','男','钻石','188-xxxx-8834','南通市海门市','99786','3456')
INSERT INTO vip_system(ID,VIP_code,name,sex,VIP_level,phone,address,total_consumption,recent_consumption)
VALUES('10','8666','万三','男','白金','182-xxxx-5634','南通市通州区','8664','3567')
– 表order_details插入数据
INSERT INTO order_details(ID,code,food_name,food_code,amount,order_time,table_code,table_amount,table_for_number,total_people,other_remarks)
VALUES('3','201710181648','红烧猪蹄','23','2','2017-10-18 16:50:47','88','1','4','4','无')
INSERT INTO order_details(ID,code,food_name,food_code,amount,order_time,table_code,table_amount,table_for_number,total_people,other_remarks)
VALUES('4','201710181648','西红柿蛋汤','05','1','2017-10-18 16:50:47','88','1','4','4','无')
INSERT INTO order_details(ID,code,food_name,food_code,amount,order_time,table_code,table_amount,table_for_number,total_people,other_remarks)
VALUES('5','201710181648','清蒸鲫鱼','31','1','2017-10-18 16:50:47','88','1','4','4','无')
INSERT INTO order_details(ID,code,food_name,food_code,amount,order_time,table_code,table_amount,table_for_number,total_people,other_remarks)
VALUES('6','201710181648','青椒土豆丝','11','1','2017-10-18 16:50:47','88','1','4','4','无')
INSERT INTO order_details(ID,code,food_name,food_code,amount,order_time,table_code,table_amount,table_for_number,total_people,other_remarks)
VALUES('7','201710181648','香菇肉丝','21','1','2017-10-18 16:50:47','88','1','4','4','无')
INSERT INTO order_details(ID,code,food_name,food_code,amount,order_time,table_code,table_amount,table_for_number,total_people,other_remarks)
VALUES('8','201710181648','茶树菇炒肉片','24','2','2017-10-18 16:50:47','88','1','4','4','无')
INSERT INTO order_details(ID,code,food_name,food_code,amount,order_time,table_code,table_amount,table_for_number,total_people,other_remarks)
VALUES('9','201710181648','红烧黑鱼','32','1','2017-10-18 16:50:47','88','1','4','4','无')
INSERT INTO order_details(ID,code,food_name,food_code,amount,order_time,table_code,table_amount,table_for_number,total_people,other_remarks)
VALUES('10','201710181648','排骨汤','09','1','2017-10-18 16:50:47','88','1','4','4','无')
– 表restaurant插入数据
INSERT INTO restaurant(ID,name,phone,address,menu_style,network_evaluation)
VALUES('2','XX家常菜馆','188-xxxx-xxxx','南通市港闸区','家常菜','米其林3星好评')
INSERT INTO restaurant(ID,name,phone,address,menu_style,network_evaluation)
VALUES('3','XX家常菜馆','137-xxxx-xxxx','南通市通州区','家常菜','米其林3星好评')
INSERT INTO restaurant(ID,name,phone,address,menu_style,network_evaluation)
VALUES('4','XX家常菜馆','130-xxxx-xxxx','南通市中南世纪城','家常菜','米其林3星好评')
INSERT INTO restaurant(ID,name,phone,address,menu_style,network_evaluation)
VALUES('5','XX家常菜馆','182-xxxx-xxxx','南通市滨海园区','家常菜','米其林3星好评')
INSERT INTO restaurant(ID,name,phone,address,menu_style,network_evaluation)
VALUES('6','XX家常菜馆','133-xxxx-xxxx','南通市启东市','家常菜','米其林3星好评')
INSERT INTO restaurant(ID,name,phone,address,menu_style,network_evaluation)
VALUES('7','XX家常菜馆','188-xxxx-xxxx','南通市如皋市','家常菜','米其林3星好评')
INSERT INTO restaurant(ID,name,phone,address,menu_style,network_evaluation)
VALUES('8','XX家常菜馆','182-xxxx-xxxx','南通市海门市','家常菜','米其林3星好评')
INSERT INTO restaurant(ID,name,phone,address,menu_style,network_evaluation)
VALUES('9','XX家常菜馆','180-xxxx-xxxx','南通市海安县','家常菜','米其林3星好评')
INSERT INTO restaurant(ID,name,phone,address,menu_style,network_evaluation)
VALUES('10','XX家常菜馆','188-xxxx-xxxx','南通市如东县','家常菜','米其林3星好评')
– BETWEEN ADN 区间判断
SELECT * FROM account WHERE food_code BETWEEN 20 AND 40
SELECT * FROM account WHERE NOT (food_code >= 20 AND food_code<= 40)
– IS NULL 判断数据结果集中非空元素
SELECT * FROM vip_system WHERE address IS NULL
SELECT * FROM vip_system WHERE address IS NOT NULL
– IN 离散的数据范围
SELECT * FROM account WHERE unit_price IN ('14','20')
SELECT * FROM account WHERE unit_price = '14' OR unit_price = '20'
– LIKE 模糊查询
SELECT *FROM menu WHERE name LIKE '青%'
SELECT *FROM menu WHERE name LIKE '%椒%'
SELECT *FROM menu WHERE name LIKE '%丝'
SELECT *FROM menu WHERE name LIKE '_红%'
– LIMIT 限制数据记录的查询数量
SELECT * FROM menu ORDER BY code ASC,unit_price DESC LIMIT 9,3
– 统计函数和分组查询
SELECT
MAX(amount) AS '最大数目',
MIN(unit_price) AS '最小单价'
FROM account
WHERE ID > 5
GROUP BY food_code >15