命令导入导出
mysql> desc salaryClass;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| grade | tinyint(4) | YES | | NULL | |
| lowsalary | int(11) | YES | | NULL | |
| highsalary | int(11) | YES | | NULL | |
+------------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
导出命令:
select * from salaryClass into outfile '/SE/log/salary'
fields terminated by ',' enclosed by '"'
lines terminated by '\r\n';
说明:
fields terminated by '列之间的分隔符'
enclosed by '列值包裹符'
lines terminated by '行之间分割符'
导出结果:
cat /SE/log/salary
"1","100","200"
"2","200","400"
数据导入
load data infile '/SE/log/salary' into table salaryClass
fields terminated by ','
optionally enclosed by '"'
lines terminated by '\r\n';
mysql> select * from salaryClass;
+-------+-----------+------------+
| grade | lowsalary | highsalary |
+-------+-----------+------------+
| 1 | 100 | 200 |
| 2 | 200 | 400 |
| 1 | 100 | 200 |
| 2 | 200 | 400 |
+-------+-----------+------------+
该方法多用于将一张表的数据导入另一张表,表之间的数据类型是一致的。
mysqldump工具
导出:
mysqldump -u root -p --all-databases > database_dump.txt
导入两个方法:
(1) mysql -uroot -pxxx 数据库名称 < database_dump.txt
(2) 在mysql终端里执行:source database_dump.txt
注意:在导入之前必须手动创建数据库名称