一、导入数据
把存在系统文件中的数据,导入到数据库中
以默认分隔符导入
1.创建源文件
导入数据时默认的字段分隔符是\t,默认行分隔符是\n
[root@ULA ~]# vim /opt/test.txt
1root //以tab做分隔符
2bin
3adm
1000user
2.创建表
MariaDB [test]> create table test(
-> uid int,
->username char(20));
Query OK, 0 rows affected (0.04 sec)
3.导入数据 记录
语法:load data infile 文件名 into table 表名;
MariaDB [test]> load data infile "/opt/test.txt" into table test;
MariaDB [test]> select * from test;
+------+----------+
| uid | username |
+------+----------+
| 1 | root |
| 2 | bin |
| 3 | adm |
| 1000 | user |
+------+----------+
总结:
1)文件不要放在/tmp和/root家目录下,mysql读不到
2)mysql用户对要导入的文件至少要有读的权限
3)文件用双引号或单引号引起来
手动指定分隔符
1.创建源文件
# head /etc/passwd > /opt/pass.txt
2.创建表
MariaDB [test]> create table pass (
-> username char(20),
-> password char(1),
-> uid int(10),
-> gid int(10),
-> msinfo char(50),
-> home char(30),
-> shell char(30));
3.导入数据 记录
语法:load data infile 文件名 into table 表名 fields terminated by '分隔符';
MariaDB [test]> load data infile '/opt/pass.txt' into table pass fields terminated by ':';
MariaDB [test]> select * from pass;
二、导出数据
将数据库中的数据导出到系统文件中
MariaDB [test]> select * from pass into outfile "/opt/passout";
ERROR 1 (HY000): Can't create/write to file '/opt/passout' (Errcode: 13)
[root@ULA tmp]# chmod 777 /opt
MariaDB [test]> select * from pass into outfile "/opt/passout";
[root@ULA tmp]# cat /opt/passout
总结:
1)导出的文件一定是不存在的
2)mysql用户对目录要有读写权限
3)导出的文件默认以\t为字段分隔符,\n为行分隔符