MySQL导入现有的数据表、表的数据

一、方法1：source .sql文件完整路径;命令导入

.sql文件完整路径：D:\database\atguigudb.sql。

C:\Users\song>mysql -hlocalhost -P3306 -uroot -p	# 连接MySQL服务
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;	# 查看所有的数据库
+--------------------+
| Database           |
+--------------------+
| dbtest1            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> source D:\database\atguigudb.sql		# 导入D:\database\atguigudb.sql文件中的数据表、表的数据
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected, 1 warning (0.08 sec)

Database changed
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected, 2 warnings (1.17 sec)

Query OK, 25 rows affected (0.20 sec)
Records: 25  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.08 sec)

Query OK, 0 rows affected, 4 warnings (0.63 sec)

Query OK, 27 rows affected (0.06 sec)
Records: 27  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.13 sec)

Query OK, 0 rows affected, 6 warnings (0.90 sec)

Query OK, 107 rows affected (0.09 sec)
Records: 107  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected, 3 warnings (0.74 sec)

Query OK, 6 rows affected (0.09 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected, 3 warnings (0.66 sec)

Query OK, 10 rows affected (0.12 sec)
Records: 10  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected, 3 warnings (0.73 sec)

Query OK, 19 rows affected (0.25 sec)
Records: 19  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.10 sec)

Query OK, 0 rows affected, 2 warnings (0.74 sec)

Query OK, 23 rows affected (0.15 sec)
Records: 23  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected, 2 warnings (0.27 sec)

Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected, 2 warnings (0.66 sec)

Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected, 6 warnings (0.56 sec)

Query OK, 0 rows affected (0.43 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.15 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show databases;	# 查看所有的数据库
+--------------------+
| Database           |
+--------------------+
| atguigudb          |
| dbtest1            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.03 sec)

mysql> use atguigudb;	# 使用atguigudb数据库
Database changed
mysql> show tables;	# 查看所有的表
+---------------------+
| Tables_in_atguigudb |
+---------------------+
| countries           |
| departments         |
| emp_details_view    |
| employees           |
| job_grades          |
| job_history         |
| jobs                |
| locations           |
| order               |
| regions             |
+---------------------+
10 rows in set (0.02 sec)

mysql>

注意：路径名不要有中文，不以t等转义字符开头，不然会报错：

二、方法2：基于具体的图形化界面的工具可以导入数据

1、SQLyog中，选择工具（Tools）→执行SQL脚本（Execute SQL Script…）

2、选择.sql文件所在的路径，打开文件

3、点击Execute执行

4、点击是继续

5、点击Done完成

6、空白处右键Refresh Object Browser刷新

7、查看数据库及表信息、表数据导入成功

注意：如果SQLyog原来选择了要导入的库atguigu，命令行删除atguigu该库之后，再到SQLyog导入.sql文件会报错：