写在前面

参考书籍：《SQL必知必会》《Mysql必知必会》

实习将至，最近在复习 Mysql ，之前学习 Mysql 是通过视频和学校课程，这次想通过书籍《SQL必知必会》来进行一次系统的复习，再重新快速的刷一遍牛客网的 SQL 题。在复习的过程中我会将重要内容进行记录，其中难以理解的部分也会通过典型的案例进行展示，希望能够帮到大家。

本篇主要内容

“实践是检验真理的唯一标准”，我总认为动手做比只空想更好，那么为了之后的学习能够一步一个脚印的学懂，首先要把示例数据导入本地 Mysql 中。本篇主要介绍如何导入数据文件，同时展示各表的结构和数据。

数据准备

1. Navicat中新建数据库

数据文件：网盘链接
提取码：rtbg

创建 order_system 数据库 ---- 新建查询----将文件内容复制并执行。

2. Mysql中导入sql文件新建数据库

首先新建 order_system 数据库。

命令行导入 create.sql 文件

mysql -u root -p order_system < C:\Users\pc\Desktop\Mysql\create.sql

命令行导入 populate.sql 文件

mysql -u root -p order_system < C:\Users\pc\Desktop\Mysql\populate.sql

3. 各表结构

Customers 表

Orders 表

OrderItems 表

Products 表

Vendors 表

各表关系图

4. 各表数据

Customers 表

mysql> SELECT * FROM customers;
+------------+---------------+----------------------+-----------+------------+----------+--------------+
| cust_id    | cust_name     | cust_address         | cust_city | cust_state | cust_zip | cust_country | 
+------------+---------------+----------------------+-----------+------------+----------+--------------+
| 1000000001 | Village Toys  | 200 Maple Lane       | Detroit   | MI         | 44444    | USA          | 
| 1000000002 | Kids Place    | 333 South Lake Drive | Columbus  | OH         | 43333    | USA          | 
| 1000000003 | Fun4All       | 1 Sunny Place        | Muncie    | IN         | 42222    | USA          |
| 1000000004 | Fun4All       | 829 Riverside Drive  | Phoenix   | AZ         | 88888    | USA          |
| 1000000005 | The Toy Store | 4545 53rd Street     | Chicago   | IL         | 54545    | USA          |
+------------+---------------+----------------------+-----------+------------+----------+--------------+
--------------------+-----------------------+
 cust_contact       | cust_email            |
--------------------+-----------------------+
 John Smith         | sales@villagetoys.com |
 Michelle Green     | NULL                  |
 Jim Jones          | jjones@fun4all.com    |
 Denise L. Stephens | dstephens@fun4all.com |
 Kim Howard         | NULL                  |
--------------------+-----------------------+

Orderitems 表

mysql> SELECT * FROM orderitems;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
|     20005 |          1 | BR01    |      100 |       5.49 |
|     20005 |          2 | BR03    |      100 |      10.99 |
|     20006 |          1 | BR01    |       20 |       5.99 |
|     20006 |          2 | BR02    |       10 |       8.99 |
|     20006 |          3 | BR03    |       10 |      11.99 |
|     20007 |          1 | BR03    |       50 |      11.49 |
|     20007 |          2 | BNBG01  |      100 |       2.99 |
|     20007 |          3 | BNBG02  |      100 |       2.99 |
|     20007 |          4 | BNBG03  |      100 |       2.99 |
|     20007 |          5 | RGAN01  |       50 |       4.49 |
|     20008 |          1 | RGAN01  |        5 |       4.99 |
|     20008 |          2 | BR03    |        5 |      11.99 |
|     20008 |          3 | BNBG01  |       10 |       3.49 |
|     20008 |          4 | BNBG02  |       10 |       3.49 |
|     20008 |          5 | BNBG03  |       10 |       3.49 |
|     20009 |          1 | BNBG01  |      250 |       2.49 |
|     20009 |          2 | BNBG02  |      250 |       2.49 |
|     20009 |          3 | BNBG03  |      250 |       2.49 |
+-----------+------------+---------+----------+------------+

Orders 表

mysql> SELECT * FROM orders;
+-----------+---------------------+------------+
| order_num | order_date          | cust_id    |
+-----------+---------------------+------------+
|     20005 | 2012-05-01 00:00:00 | 1000000001 |
|     20006 | 2012-01-12 00:00:00 | 1000000003 |
|     20007 | 2012-01-30 00:00:00 | 1000000004 |
|     20008 | 2012-02-03 00:00:00 | 1000000005 |
|     20009 | 2012-02-08 00:00:00 | 1000000001 |
+-----------+---------------------+------------+

Products 表

mysql> SELECT * FROM products;
+---------+---------+---------------------+------------+
| prod_id | vend_id | prod_name           | prod_price |
+---------+---------+---------------------+------------+
| BNBG01  | DLL01   | Fish bean bag toy   |       3.49 |
| BNBG02  | DLL01   | Bird bean bag toy   |       3.49 |
| BNBG03  | DLL01   | Rabbit bean bag toy |       3.49 |
| BR01    | BRS01   | 8 inch teddy bear   |       5.99 |
| BR02    | BRS01   | 12 inch teddy bear  |       8.99 |
| BR03    | BRS01   | 18 inch teddy bear  |      11.99 |
| RGAN01  | DLL01   | Raggedy Ann         |       4.99 |
| RYL01   | FNG01   | King doll           |       9.49 |
| RYL02   | FNG01   | Queen doll          |       9.49 |
+---------+---------+---------------------+------------+
-----------------------------------------------------------------------+
 prod_desc                                                             |
-----------------------------------------------------------------------+
 Fish bean bag toy, complete with bean bag worms with which to feed it |
 Bird bean bag toy, eggs are not included                              |
 Rabbit bean bag toy, comes with bean bag carrots                      |
 8 inch teddy bear, comes with cap and jacket                          |
 12 inch teddy bear, comes with cap and jacket                         |
 18 inch teddy bear, comes with cap and jacket                         |
 18 inch Raggedy Ann doll                                              |
 12 inch king doll with royal garments and crown                       |
 12 inch queen doll with royal garments and crown                      |
-----------------------------------------------------------------------+

Vendors 表

mysql> SELECT * FROM vendors;
+---------+-----------------+-----------------+------------+------------+----------+--------------+
| vend_id | vend_name       | vend_address    | vend_city  | vend_state | vend_zip | vend_country |
+---------+-----------------+-----------------+------------+------------+----------+--------------+
| BRE02   | Bear Emporium   | 500 Park Street | Anytown    | OH         | 44333    | USA          |
| BRS01   | Bears R Us      | 123 Main Street | Bear Town  | MI         | 44444    | USA          |
| DLL01   | Doll House Inc. | 555 High Street | Dollsville | CA         | 99999    | USA          |
| FNG01   | Fun and Games   | 42 Galaxy Road  | London     | NULL       | N16 6PS  | England      |
| FRB01   | Furball Inc.    | 1000 5th Avenue | New York   | NY         | 11111    | USA          |
| JTS01   | Jouets et ours  | 1 Rue Amusement | Paris      | NULL       | 45678    | France       |
+---------+-----------------+-----------------+------------+------------+----------+--------------+

这就是本文所有的内容了，如果感觉还不错的话。❤ 点个赞再走吧！！！❤


后续会继续分享《Mysql从入门到入魔》系列文章，如果感兴趣的话可以点个关注不迷路哦~。