SQL题库：sqllearning3​—【未明学院】SQL课程作业

出处

【MySQL教程】SQL零基础教程，带你掌握最受企业欢迎的数据库语言！_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1PJ411m7Lz

准备工作

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for cust_info
-- ----------------------------
DROP TABLE IF EXISTS `cust_info`;
CREATE TABLE `cust_info`  (
  `cust_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `cust_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `tel` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of cust_info
-- ----------------------------
INSERT INTO `cust_info` VALUES ('C00100101', '赵一', '13377770001', 'zhaoyi@gmail.com');
INSERT INTO `cust_info` VALUES ('C00100102', '赵二', '13377770002', 'zhaoer@gmail.com');
INSERT INTO `cust_info` VALUES ('C00100103', '赵三', '13377770003', 'zhaosan@gmail.com');
INSERT INTO `cust_info` VALUES ('C00100104', '钱大', '13377770004', 'qianda@gmail.com');
INSERT INTO `cust_info` VALUES ('C00100105', '钱二', '13377770005', 'qianer@gmail.com');
INSERT INTO `cust_info` VALUES ('C00100106', '钱三', '13377770006', 'qiansan@gmail.com');
INSERT INTO `cust_info` VALUES ('C00100107', '孙大', '13377770007', 'sunda@gmail.com');
INSERT INTO `cust_info` VALUES ('C00100108', '孙二', '13377770008', 'suner@gmail.com');
INSERT INTO `cust_info` VALUES ('C00100109', '孙三', '13377770009', 'sunsan@gmail.com');
INSERT INTO `cust_info` VALUES ('C00100110', '李一', '13377770010', 'liyi@gmail.com');
INSERT INTO `cust_info` VALUES ('C00100111', '李二', '13377770011', 'lier@gmail.com');
INSERT INTO `cust_info` VALUES ('C00100112', '李三', '13377770012', 'lisan@gmail.com');
INSERT INTO `cust_info` VALUES ('C00100113', '吴大', '13377770013', 'wuda@gmail.com');
INSERT INTO `cust_info` VALUES ('C00100114', '吴二', '13377770014', 'wuer@gmail.com');
INSERT INTO `cust_info` VALUES ('C00100115', '吴二', '13377770015', 'wuer@126.com');

-- ----------------------------
-- Table structure for milk_tea
-- ----------------------------
DROP TABLE IF EXISTS `milk_tea`;
CREATE TABLE `milk_tea`  (
  `prod_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `prod_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `net_w` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pro_date` date NULL DEFAULT NULL,
  `valid_month` int(0) NULL DEFAULT NULL,
  `in_price` decimal(10, 2) NULL DEFAULT NULL,
  `sale_price` decimal(10, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`prod_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of milk_tea
-- ----------------------------
INSERT INTO `milk_tea` VALUES ('1', '奶茶', '150g', '2018-09-11', 12, 10.80, 15.00);
INSERT INTO `milk_tea` VALUES ('2', '奶糖', '150g', '2019-05-13', 18, 12.30, 20.00);
INSERT INTO `milk_tea` VALUES ('3', '棒棒糖', '15g', '2019-04-29', 18, 2.10, 2.50);
INSERT INTO `milk_tea` VALUES ('4', '饼干', '200g', NULL, 12, 16.10, 23.00);
INSERT INTO `milk_tea` VALUES ('5', '薯片', '100g', '2018-08-27', 12, 9.30, 15.00);
INSERT INTO `milk_tea` VALUES ('6', '薯条', '100g', '2018-08-31', 12, 8.80, 15.00);
INSERT INTO `milk_tea` VALUES ('7', '火腿肠', '550g', '2019-02-04', 12, 15.50, NULL);
INSERT INTO `milk_tea` VALUES ('8', '方便面', '100g', '2018-12-09', 18, 3.60, 4.00);

-- ----------------------------
-- Table structure for order_list
-- ----------------------------
DROP TABLE IF EXISTS `order_list`;
CREATE TABLE `order_list`  (
  `serial_num` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `order_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `date` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `prod_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `prodname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `cust_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `cust_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `quantity` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of order_list
-- ----------------------------
INSERT INTO `order_list` VALUES ('00000001', '20190401001', '2019-04-01', '10001', '抽纸', 'C00100101', '赵一', '1');
INSERT INTO `order_list` VALUES ('00000002', '20190401001', '2019-04-01', '10002', '抽纸', 'C00100101', '赵一', '1');
INSERT INTO `order_list` VALUES ('00000003', '20190401002', '2019-04-01', '10003', '抽纸', 'C00100107', '孙大', '2');
INSERT INTO `order_list` VALUES ('00000004', '20190402001', '2019-04-02', '10004', '洗衣液', 'C00100114', '吴二', '1');
INSERT INTO `order_list` VALUES ('00000005', '20190402002&