MySQL必知必会-官方数据库表及SQL脚本导入生成

时间:2022-03-12 07:49:30

最近在复习SQL语句,看的是MySQL必知必会这本书,但是发现附录中只有表设计,没有表的具体数据。所以在学习相应的语句中体验不是很好,去网上查了数据库的内容,自己慢慢导入到了数据库中。把表放出来作为参照,SQL脚本语句放在最后,可以直接导到自己的数据库。

customer表

cust_id cust_name cust_address cust_city cust_state cust_zip cust_country cust_contact cust_email
10001 Coyote Inc. 200 Maple Lane Detroit MI 44444 USA Y Lee [email protected]
10002 Mouse House 333 Fromage Lane Columbus OH 43333 USA Jerry Mouse Null
10003 Wascals 1 Sunny Place Muncie IN 42222 USA Jim Jones [email protected]
10004 Yosemite Place 829 Riverside Drive Phoenix AZ 88888 USA Y Sam [email protected]
10005 E Fudd 4545 53rd Street Chicago IL 54545 USA E Fudd Null

orderitems表

order_num order_item prod_id quantity item_price
20005 1 ANV01 10 5.99
20005 2 ANV02 3 9.99
20005 3 TNT2 5 10
20005 4 FB 1 10
20006 1 JP2000 1 55
20007 1 TNT2 100 10
20008 1 FC 50 2.5
20009 1 FB 1 10
20009 2 OL1 1 8.99
20009 3 SLING 1 4.49
20009 4 ANV03 1 14.99

orders表

order_num order_date cust_id
20005 2005-09-01 00:00:00 10001
20006 2005-09-12 00:00:00 10003
20007 2005-09-30 00:00:00 10004
20008 2005-10-03 00:00:00 10005
20009 2005-10-08 00:00:00 10001

productnotes表

note_id prod_id note_date note_text
101 TNT2 2005-08-17 00:00:00 Customer complaint:Sticks not individually wrapped, too easy to mistakenly detonate all at once.Recommend individual wrapping.
102 OL1 2005-08-18 00:00:00 Can shipped full, refills not available.Need to order new can if refill needed.
103 SAFE 2005-08-18 00:00:00 Safe is combination locked, combination not provided with safe. This is rarely a problem as safes are typically blown up or dropped by customers.
104 FC 2005-08-19 00:00:00 Quantity varies, sold by the sack load.All guaranteed to be bright and orange, and suitable for use as rabbit bait.
105 TNT2 2005-08-20 00:00:00 Included fuses are short and have been known to detonate too quickly for some customers.Longer fuses are available (item FU1) and should be recommended.
106 TNT2 2005-08-22 00:00:00 Matches not included, recommend purchase of matches or detonator (item DTNTR).
107 SAFE 2005-08-23 00:00:00 Please note that no returns will be accepted if safe opened using explosives.
108 ANV01 2005-08-25 00:00:00 Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.
109 ANV03 2005-09-01 00:00:00 Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.
110 FC 2005-09-01 00:00:00 Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
111 SLING 2005-09-02 00:00:00 Shipped unassembled, requires common tools (including oversized hammer).
112 SAFE 2005-09-02 00:00:00 Customer complaint:Circular hole in safe floor can apparently be easily cut with handsaw.
113 ANV01 2005-09-05 00:00:00 Customer complaint:Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.
114 SAFE 2005-09-07 00:00:00 Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.Comment forwarded to vendor.

products表

prod_id vend_id prod_name prod_price prod_desc
ANV01 1001 .5 ton anvil 5.99 .5 ton anvil, black, complete with handy hook
ANV02 1001 1 ton anvil 9.99 1 ton anvil, black, complete with handy hook and carrying case
ANV03 1001 2 ton anvil 14.99 2 ton anvil, black, complete with handy hook and carrying case
DTNTR 1003 Detonator 13 Detonator (plunger powered), fuses not included
FB 1003 Bird seed 10 Large bag (suitable for road runners)
FC 1003 Carrots 2.5 Carrots (rabbit hunting season only)
FU1 1002 Fuses 3.42 1 dozen, extra long
JP1000 1005 JetPack 1000 35 JetPack 1000, intended for single use
JP2000 1005 JetPack 2000 55 JetPack 2000, multi-use
OL1 1002 Oil can 8.99 Oil can, red
SAFE 1003 Safe 50 Safe with combination lock
SLING 1003 Sling 4.49 Sling, one size fits all
TNT1 1003 TNT (1 stick) 2.5 TNT, red, single stick
TNT2 1003 TNT (5 sticks) 10 TNT, red, pack of 10 sticks

vendors表

vend_id vend_name vend_address vend_city vend_state vend_zip vend_county
1001 Anvils R Us 123 Main Street Southfield MI 48075 USA
1002 LT Supplies 500 Park Street Anytown OH 44333 USA
1003 ACME 555 High Street Los Angeles CA 90046 USA
1004 Furball Inc. 1000 5th Avenue New York NY 11111 USA
1005 Jet Set 42 Galaxy Road London Null N16 6PS England
1006 Jouets Et Ours 1 Rue Amusement Paris Null 45678 France

数据库SQL脚本语句

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 50612
 Source Host           : localhost:3306
 Source Schema         : zlx_mysql

 Target Server Type    : MySQL
 Target Server Version : 50612
 File Encoding         : 65001

 Date: 19/12/2019 23:03:46
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for customers
-- ----------------------------
DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers`  (
  `cust_id` int(11) NOT NULL AUTO_INCREMENT,
  `cust_name` char(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `cust_address` char(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `cust_city` char(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `cust_state` char(5) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `cust_zip` char(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `cust_country` char(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `cust_contact` char(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `cust_email` char(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`cust_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10006 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of customers
-- ----------------------------
INSERT INTO `customers` VALUES (10001, Coyote Inc., 200 Maple Lane, Detroit, MI, 44444, USA, Y Lee, [email protected]);
INSERT INTO `customers` VALUES (10002, Mouse House, 333 Fromage Lane, Columbus, OH, 43333, USA, Jerry Mouse, NULL);
INSERT INTO `customers` VALUES (10003, Wascals, 1 Sunny Place, Muncie, IN, 42222, USA, Jim Jones, [email protected]);
INSERT INTO `customers` VALUES (10004, Yosemite Place, 829 Riverside Drive, Phoenix, AZ, 88888, USA, Y Sam, [email protected]);
INSERT INTO `customers` VALUES (10005, E Fudd, 4545 53rd Street, Chicago, IL, 54545, USA, E Fudd, NULL);

-- ----------------------------
-- Table structure for orderitems
-- ----------------------------
DROP TABLE IF EXISTS `orderitems`;
CREATE TABLE `orderitems`  (
  `order_num` int(11) NOT NULL,
  `order_item` int(11) NOT NULL,
  `prod_id` char(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `quantity` int(11) NOT NULL,
  `item_price` decimal(8, 2) NOT NULL,
  PRIMARY KEY (`order_num`, `order_item`) USING BTREE,
  INDEX `fk_orderitems_products`(`prod_id`) USING BTREE,
  CONSTRAINT `fk_orderitems_products` FOREIGN KEY (`prod_id`) REFERENCES `products` (`prod_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_orderitems_orders` FOREIGN KEY (`order_num`) REFERENCES `orders` (`order_num`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of orderitems
-- ----------------------------
INSERT INTO `orderitems` VALUES (20005, 1, ANV01, 10, 5.99);
INSERT INTO `orderitems` VALUES (20005, 2, ANV02, 3, 9.99);
INSERT INTO `orderitems` VALUES (20005, 3, TNT2, 5, 10.00);
INSERT INTO `orderitems` VALUES (20005, 4, FB, 1, 10.00);
INSERT INTO `orderitems` VALUES (20006, 1, JP2000, 1, 55.00);
INSERT INTO `orderitems` VALUES (20007, 1, TNT2, 100, 10.00);
INSERT INTO `orderitems` VALUES (20008, 1, FC, 50, 2.50);
INSERT INTO `orderitems` VALUES (20009, 1, FB, 1, 10.00);
INSERT INTO `orderitems` VALUES (20009, 2, OL1, 1, 8.99);
INSERT INTO `orderitems` VALUES (20009, 3, SLING, 1, 4.49);
INSERT INTO `orderitems` VALUES (20009, 4, ANV03, 1, 14.99);

-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders`  (
  `order_num` int(11) NOT NULL AUTO_INCREMENT,
  `order_date` datetime(0) NOT NULL,
  `cust_id` int(11) NOT NULL,
  PRIMARY KEY (`order_num`) USING BTREE,
  INDEX `fk_orders_customers`(`cust_id`) USING BTREE,
  CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 20010 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES (20005, 2005-09-01 00:00:00, 10001);
INSERT INTO `orders` VALUES (20006, 2005-09-12 00:00:00, 10003);
INSERT INTO `orders` VALUES (20007, 2005-09-30 00:00:00, 10004);
INSERT INTO `orders` VALUES (20008, 2005-10-03 00:00:00, 10005);
INSERT INTO `orders` VALUES (20009, 2005-10-08 00:00:00, 10001);

-- ----------------------------
-- Table structure for productnotes
-- ----------------------------
DROP TABLE IF EXISTS `productnotes`;
CREATE TABLE `productnotes`  (
  `note_id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_id` char(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `note_date` datetime(0) NOT NULL,
  `note_text` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL,
  PRIMARY KEY (`note_id`) USING BTREE,
  FULLTEXT INDEX `note_text`(`note_text`)
) ENGINE = MyISAM AUTO_INCREMENT = 115 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of productnotes
-- ----------------------------
INSERT INTO `productnotes` VALUES (101, TNT2, 2005-08-17 00:00:00, Customer complaint:rnSticks not individually wrapped, too easy to mistakenly detonate all at once.rnRecommend individual wrapping.);
INSERT INTO `productnotes` VALUES (102, OL1, 2005-08-18 00:00:00, Can shipped full, refills not available.rnNeed to order new can if refill needed.);
INSERT INTO `productnotes` VALUES (103, SAFE, 2005-08-18 00:00:00, Safe is combination locked, combination not provided with safe.rnThis is rarely a problem as safes are typically blown up or dropped by customers.);
INSERT INTO `productnotes` VALUES (104, FC, 2005-08-19 00:00:00, Quantity varies, sold by the sack load.rnAll guaranteed to be bright and orange, and suitable for use as rabbit bait.);
INSERT INTO `productnotes` VALUES (105, TNT2, 2005-08-20 00:00:00, Included fuses are short and have been known to detonate too quickly for some customers.rnLonger fuses are available (item FU1) and should be recommended.);
INSERT INTO `productnotes` VALUES (106, TNT2, 2005-08-22 00:00:00, Matches not included, recommend purchase of matches or detonator (item DTNTR).);
INSERT INTO `productnotes` VALUES (107, SAFE, 2005-08-23 00:00:00, Please note that no returns will be accepted if safe opened using explosives.);
INSERT INTO `productnotes` VALUES (108, ANV01, 2005-08-25 00:00:00, Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.);
INSERT INTO `productnotes` VALUES (109, ANV03, 2005-09-01 00:00:00, Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.);
INSERT INTO `productnotes` VALUES (110, FC, 2005-09-01 00:00:00, Customer complaint: rabbit has been able to detect trap, food apparently less effective now.);
INSERT INTO `productnotes` VALUES (111, SLING, 2005-09-02 00:00:00, Shipped unassembled, requires common tools (including oversized hammer).);
INSERT INTO `productnotes` VALUES (112, SAFE, 2005-09-02 00:00:00, Customer complaint:rnCircular hole in safe floor can apparently be easily cut with handsaw.);
INSERT INTO `productnotes` VALUES (113, ANV01, 2005-09-05 00:00:00, Customer complaint:rnNot heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.);
INSERT INTO `productnotes` VALUES (114, SAFE, 2005-09-07 00:00:00, Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.rnComment forwarded to vendor.);

-- ----------------------------
-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products`  (
  `prod_id` char(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `vend_id` int(11) NOT NULL,
  `prod_name` char(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `prod_price` decimal(8, 2) NOT NULL,
  `prod_desc` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL,
  PRIMARY KEY (`prod_id`) USING BTREE,
  INDEX `fk_products_vendors`(`vend_id`) USING BTREE,
  CONSTRAINT `fk_products_vendors` FOREIGN KEY (`vend_id`) REFERENCES `vendors` (`vend_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES (ANV01, 1001, .5 ton anvil, 5.99, .5 ton anvil, black, complete with handy hook);
INSERT INTO `products` VALUES (ANV02, 1001, 1 ton anvil, 9.99, 1 ton anvil, black, complete with handy hook and carrying case);
INSERT INTO `products` VALUES (ANV03, 1001, 2 ton anvil, 14.99, 2 ton anvil, black, complete with handy hook and carrying case);
INSERT INTO `products` VALUES (DTNTR, 1003, Detonator, 13.00, Detonator (plunger powered), fuses not included);
INSERT INTO `products` VALUES (FB, 1003, Bird seed, 10.00, Large bag (suitable for road runners));
INSERT INTO `products` VALUES (FC, 1003, Carrots, 2.50, Carrots (rabbit hunting season only));
INSERT INTO `products` VALUES (FU1, 1002, Fuses, 3.42, 1 dozen, extra long);
INSERT INTO `products` VALUES (JP1000, 1005, JetPack 1000, 35.00, JetPack 1000, intended for single use);
INSERT INTO `products` VALUES (JP2000, 1005, JetPack 2000, 55.00, JetPack 2000, multi-use);
INSERT INTO `products` VALUES (OL1, 1002, Oil can, 8.99, Oil can, red);
INSERT INTO `products` VALUES (SAFE, 1003, Safe, 50.00, Safe with combination lock);
INSERT INTO `products` VALUES (SLING, 1003, Sling, 4.49, Sling, one size fits all);
INSERT INTO `products` VALUES (TNT1, 1003, TNT (1 stick), 2.50, TNT, red, single stick);
INSERT INTO `products` VALUES (TNT2, 1003, TNT (5 sticks), 10.00, TNT, red, pack of 10 sticks);

-- ----------------------------
-- Table structure for vendors
-- ----------------------------
DROP TABLE IF EXISTS `vendors`;
CREATE TABLE `vendors`  (
  `vend_id` int(11) NOT NULL AUTO_INCREMENT,
  `vend_name` char(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `vend_address` char(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `vend_city` char(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `vend_state` char(5) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `vend_zip` char(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `vend_country` char(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`vend_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1007 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of vendors
-- ----------------------------
INSERT INTO `vendors` VALUES (1001, Anvils R Us, 123 Main Street, Southfield, MI, 48075, USA);
INSERT INTO `vendors` VALUES (1002, LT Supplies, 500 Park Street, Anytown, OH, 44333, USA);
INSERT INTO `vendors` VALUES (1003, ACME, 555 High Street, Los Angeles, CA, 90046, USA);
INSERT INTO `vendors` VALUES (1004, Furball Inc., 1000 5th Avenue, New York, NY, 11111, USA);
INSERT INTO `vendors` VALUES (1005, Jet Set, 42 Galaxy Road, London, NULL, N16 6PS, England);
INSERT INTO `vendors` VALUES (1006, Jouets Et Ours, 1 Rue Amusement, Paris, NULL, 45678, France);

SET FOREIGN_KEY_CHECKS = 1;