2017/10/22订单信息

/*
Navicat MySQL Data Transfer

Source Server : 作业专区
Source Server Version : 50556
Source Host : localhost:3306
Source Database : 2017-10-17

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

Date: 2017-10-22 16:11:11
*/

SET FOREIGN_KEY_CHECKS=0;

---

– Table structure for menu

---

DROP TABLE IF EXISTS menu;
CREATE TABLE menu (
id int(11) NOT NULL AUTO_INCREMENT,
caidan_name varchar(255) DEFAULT NULL,
money int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY caidan_name (caidan_name)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

---

– Records of menu

---

INSERT INTO menu VALUES (‘1’, ‘海参’, ‘20’);
INSERT INTO menu VALUES (‘2’, ‘海带’, ‘15’);
INSERT INTO menu VALUES (‘3’, ‘饭团’, ‘22’);
INSERT INTO menu VALUES (‘4’, ‘鱼香肉丝’, ‘10’);
INSERT INTO menu VALUES (‘5’, ‘鲍鱼’, ‘50’);
INSERT INTO menu VALUES (‘6’, ‘龙虾’, ‘60’);
INSERT INTO menu VALUES (‘7’, ‘青菜’, ‘10’);
INSERT INTO menu VALUES (‘8’, ‘川菜’, ‘10’);
INSERT INTO menu VALUES (‘9’, ‘辣椒酱’, ‘5’);
INSERT INTO menu VALUES (‘10’, ‘牛肉’, ‘55’);

---

– Table structure for people

---

DROP TABLE IF EXISTS people;
CREATE TABLE people (
id int(10) NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
PRIMARY KEY (id),
KEY name (name)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

---

– Records of people

---

INSERT INTO people VALUES (‘1’, ‘张三’);
INSERT INTO people VALUES (‘10’, ‘张八’);
INSERT INTO people VALUES (‘9’, ‘张六’);
INSERT INTO people VALUES (‘2’, ‘李四’);
INSERT INTO people VALUES (‘7’, ‘王三’);
INSERT INTO people VALUES (‘3’, ‘王二’);
INSERT INTO people VALUES (‘4’, ‘王五’);
INSERT INTO people VALUES (‘8’, ‘王思’);
INSERT INTO people VALUES (‘5’, ‘赵六’);
INSERT INTO people VALUES (‘6’, ‘麻子’);

---

– Table structure for selected_menu

---

DROP TABLE IF EXISTS selected_menu;
CREATE TABLE selected_menu (
id int(11) NOT NULL AUTO_INCREMENT,
p_code int(10) DEFAULT NULL,
m_code int(10) DEFAULT NULL,
PRIMARY KEY (id),
KEY FK01 (p_code),
KEY FK02 (m_code),
CONSTRAINT FK02 FOREIGN KEY (m_code) REFERENCES menu (id),
CONSTRAINT FK01 FOREIGN KEY (p_code) REFERENCES people (id)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

---

– Records of selected_menu

---

INSERT INTO selected_menu VALUES (‘1’, ‘1’, ‘1’);
INSERT INTO selected_menu VALUES (‘2’, ‘1’, ‘3’);
INSERT INTO selected_menu VALUES (‘3’, ‘2’, ‘2’);
INSERT INTO selected_menu VALUES (‘4’, ‘2’, ‘5’);
INSERT INTO selected_menu VALUES (‘5’, ‘3’, ‘4’);
INSERT INTO selected_menu VALUES (‘6’, ‘3’, ‘6’);
INSERT INTO selected_menu VALUES (‘7’, ‘4’, ‘10’);
INSERT INTO selected_menu VALUES (‘8’, ‘4’, ‘9’);
INSERT INTO selected_menu VALUES (‘9’, ‘5’, ‘6’);
INSERT INTO selected_menu VALUES (‘10’, ‘5’, ‘8’);
INSERT INTO selected_menu VALUES (‘11’, ‘6’, ‘7’);
INSERT INTO selected_menu VALUES (‘12’, ‘6’, ‘4’);
INSERT INTO selected_menu VALUES (‘13’, ‘7’, ‘2’);
INSERT INTO selected_menu VALUES (‘14’, ‘7’, ‘1’);
INSERT INTO selected_menu VALUES (‘15’, ‘8’, ‘7’);
INSERT INTO selected_menu VALUES (‘16’, ‘8’, ‘9’);
INSERT INTO selected_menu VALUES (‘17’, ‘9’, ‘10’);
INSERT INTO selected_menu VALUES (‘18’, ‘9’, ‘2’);
INSERT INTO selected_menu VALUES (‘19’, ‘10’, ‘5’);
INSERT INTO selected_menu VALUES (‘20’, ‘10’, ‘6’);

SELECT name AS’姓名’,caidan_name AS’菜名’,money AS’价格’ FROM selected_menu
LEFT JOIN people ON people.id=selected_menu.p_code
LEFT JOIN menu ON selected_menu.m_code=menu.id

SELECT DISTINCT name FROM people

SELECT caidan_name AS’菜名’,money AS’价格’,money/6 AS’美元’ FROM menu

SELECT caidan_name AS’菜名’,CONCAT(‘¥’,money) AS’价格’,CONCAT(‘$’,money/6) AS’美元’ FROM menu

SELECT * FROM menu WHERE money>20

SELECT * FROM menu WHERE money BETWEEN 15 AND 50

SELECT * FROM menu WHERE NOT(money BETWEEN 15 AND 50)

SELECT * FROM menu WHERE money IN (‘15’,’55’)

SELECT * FROM people WHERE name LIKE ‘王%’

SELECT * FROM people WHERE name LIKE ‘%王%’

SELECT * FROM menu ORDER BY money ASC

SELECT * FROM menu ORDER BY money DESC

SELECT * FROM menu ORDER BY money ASC LIMIT 2,8