最近在复习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;