六、数据导入导出（批量操作数据）

1.1 数据导入的命令格式及数据导入时的注意事项
导入数据的命令格式：

数据导入：把系统文件的内容存储到数据库服务器的表里。
把系统已有用户的信息保存到db3库下的usertab表里

创建存储数据表
create database db3;
create table db3.usertab(
username char(50),
password char(1),
uid int(2),
gid int(2),
comment char(100),
homedir char(100),
shell char(50),
index(username)
);
desc db3.usertab;
select * from db3.usertab;

导入数据
]# cp /etc/passwd /var/lib/mysql-files/

mysql>
load data infile "/var/lib/mysql-files/passwd"
into table db3.usertab
fields terminated by ":"
lines terminated by "\n";

mysql> alter table db3.usertab
add
id int(2) primary key auto_increment first;

mysql> select * from db3.usertab;
mysql> select * from db3.usertab where id=20;

 load data infile "/mysqldata/passwd"

into table db3.usertab
fields terminated by ":"  #字段分割符号为：
lines terminated by "\n";  #结尾换行

1.2 数据导出的命令格式及数据导出时的注意事项

mysql>select username,uid from db3.usertab into outfile "/mysqldata/user1.txt";

mysql>select * from db3.usertab into outfile "/mysqldata/user2.txt";

mysql>select username,uid from db3.usertab into outfile "/mysqldata/user3.txt" fields terminated by "###";

]# cat /mysqldata/user1.txt
]# cat /mysqldata/user2.txt
]# cat /mysqldata/user3.txt