作业

/* 
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