mysql数据导入导出

                                                    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)

 

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

4、导入完成后就可以查看导入的数据了。

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)

5、数据导出操作

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)

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

 

相关知识： 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) 

 

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)

 

 

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 "换行符";

fields terminated by ":"列使用的分隔符；

lines terminated by :\n";  行使用的换行符；