数据导入与导出

## 数据存放检索路径
### 查看
查看默认数据存放的文件检索路径
```asp
show variables like "%file%";
```
```asp
show variables like "%secure_file_priv%";
```
```asp
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |	 #默认路径（默认是空的）
+------------------+-----------------------+
```
使用system可以在mysql里执行系统命令
```asp
system ls /var/lib/mysql-files/
```
### 修改
修改数据检索路径的位置
```asp
vim /etc/my.cnf
```
添加自定义路径
```asp
secure_file_priv="/myload"
```
再次查看
```asp
show variables like "%secure_file_priv%";
```
```asp
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |	#路径已经修改成指定的
+------------------+-----------------------+    
```
## 数据导入
将passwd表放入导入检索的文件下
```asp
cp /etc/passwd /mylod/
```
创建表基于passwd结构的
```asp
create table db3.user(username char(50),
password char(1),
uid int,
gid int,
comment varchar(150),
homedir char(150),
shell char(50)
); 
```
将pass表导入到数据库
```asp
load data infile "/myload/passwd" into table db3.user fields terminated by ":" lines terminated by "\n";
```
添加一行行号设置为主键并自增长
```asp
alter table db3.user add id int primary key auto_increment first;
```
单独添加一行数据
```asp
insert into db3.user values(27,"done","x",1002,1002,"","/home/done","bin/bash");
```
## 数据导出
单独一条数据导出
```asp
select * from db3.user where id=1 into outfile "/myload/user1.txt";
```
查看导出内容
```asp
system cat /myload/user1.txt
```
按照字段条件导出
```asp
select username,homedir,shell from db3.user where id<=2
    ->into outfile "/myload/user4.txt"	#导出路径
    ->fields terminated by ":"			#指定分隔符
    ->lines terminated by "\n";   		#指定换行符
```
```asp
system cat /myload/user4.txt  			#查看导出内容
```
```asp
root#/root#/bin/bash!!!bin#/bin#/sbin/nologin!!!
```