MySQL导入现有的数据表、表的数据_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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160

注意：路径名不要有中文，不以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文件会报错：