mysql数据导入导出
将/etc/passwd文件的内容导入到数据库中
1、首先创建一个表，字段和/etc/passwd文件字段对应。
mysql> create table passwd(username char(30) not null,pass char(1) not null,uid int(5) not null , gid int(5) not null,common varchar(50), homedir char(50),shell char(50) not null,index(username));
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
2、将passwd文件拷贝到当前数据库的目录下，当前使用的数据库是zhaoyun，
位置在/var/lib/mysql/zhaoyun
[root@zhaoyun ~]# cat /etc/passwd > /var/lib/mysql/zhaoyun/passwd
3、现在准备好了，就可以导入数据了
mysql> load data infile 'passwd' into table passwd fields terminated by ":" lines terminated by "\n";
Query OK, 37 rows affected (0.02 sec)
Records: 37 Deleted: 0 Skipped: 0 Warnings: 0
Query OK, 37 rows affected (0.02 sec)
Records: 37 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from passwd where shell='/sbin/nologin' limit 5;
+----------+------+-----+-----+--------+-----------------+---------------+
| username | pass | uid | gid | common | homedir | shell |
+----------+------+-----+-----+--------+-----------------+---------------+
| bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
| daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
| adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin |
| lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin |
| mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin |
+----------+------+-----+-----+--------+-----------------+---------------+
5 rows in set (0.00 sec)
+----------+------+-----+-----+--------+-----------------+---------------+
| username | pass | uid | gid | common | homedir | shell |
+----------+------+-----+-----+--------+-----------------+---------------+
| bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
| daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
| adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin |
| lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin |
| mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin |
+----------+------+-----+-----+--------+-----------------+---------------+
5 rows in set (0.00 sec)
mysql> select username,uid,gid from passwd where shell='/sbin/nologin' limit 5
-> into outfile "zhaoyunpasswd" fields terminated by ":" lines terminated by
-> "\n";
Query OK, 5 rows affected (0.04 sec)
-> into outfile "zhaoyunpasswd" fields terminated by ":" lines terminated by
-> "\n";
Query OK, 5 rows affected (0.04 sec)
6、导出完成后，就可以查看这个文件，导出的位置也在当前数据库名的目录下
[root@zhaoyun zhaoyun]# pwd
/var/lib/mysql/zhaoyun
[root@zhaoyun zhaoyun]# ls
db.opt passwd passwd.frm passwd.MYD passwd.MYI test.frm test.MYD test.MYI zhaoyunpasswd
[root@zhaoyun zhaoyun]# cat zhaoyunpasswd
bin:1:1
daemon:2:2
adm:3:4
lp:4:7
mail:8:12
/var/lib/mysql/zhaoyun
[root@zhaoyun zhaoyun]# ls
db.opt passwd passwd.frm passwd.MYD passwd.MYI test.frm test.MYD test.MYI zhaoyunpasswd
[root@zhaoyun zhaoyun]# cat zhaoyunpasswd
bin:1:1
daemon:2:2
adm:3:4
lp:4:7
mail:8:12
相关知识： limit
mysql> select * from passwd limit 2 ;
+----------+------+-----+-----+--------+---------+---------------+
| username | pass | uid | gid | common | homedir | shell |
+----------+------+-----+-----+--------+---------+---------------+
| root | x | 0 | 0 | root | /root | /bin/bash |
| bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
+----------+------+-----+-----+--------+---------+---------------+
2 rows in set (0.00 sec)
+----------+------+-----+-----+--------+---------+---------------+
| username | pass | uid | gid | common | homedir | shell |
+----------+------+-----+-----+--------+---------+---------------+
| root | x | 0 | 0 | root | /root | /bin/bash |
| bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
+----------+------+-----+-----+--------+---------+---------------+
2 rows in set (0.00 sec)
mysql> select * from passwd limit 2,3;
+----------+------+-----+-----+--------+----------------+---------------+
| username | pass | uid | gid | common | homedir | shell |
+----------+------+-----+-----+--------+----------------+---------------+
| daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
| adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin |
| lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin |
+----------+------+-----+-----+--------+----------------+---------------+
3 rows in set (0.00 sec)
+----------+------+-----+-----+--------+----------------+---------------+
| username | pass | uid | gid | common | homedir | shell |
+----------+------+-----+-----+--------+----------------+---------------+
| daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
| adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin |
| lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin |
+----------+------+-----+-----+--------+----------------+---------------+
3 rows in set (0.00 sec)
limit 2 打印俩行
limit2,3 默认从0开始计数，那么就是从第三行开始打印，在打印出3行。
load data infile 'passwd' into table passwd fields terminated by ":" lines terminated by "\n";
load data infile '文件名' into table 表名 fields terminated by "分隔符" lines terminated by "换行符";
load data infile '文件名' into table 表名 fields terminated by "分隔符" lines terminated by "换行符";
fields terminated by ":"列使用的分隔符；
lines terminated by :\n"; 行使用的换行符；
本文转自zhaoyun00 51CTO博客，原文链接:http://blog.51cto.com/zhaoyun/727216
