数据库检索目录的更改以及数据的导入和导出

检索路径–搜索路径

默认路径:

安装mysql时 系统自动创建 目录 /var/lib/mysql-files
查询检索路径方法:show variables like “secure_file_priv”;

修改检索路径

创建目录 修改配置文件 重启服务
我们把默认路径修改为/myload

[root@mysql50 ~]# mkdir /myload
[root@mysql50 ~]# vim /etc/my.cnf
        [mysqld]
        secure_file_priv="/myload”   //在配置文件中添加
[root@mysql50 ~]# chown  mysql  /myload
[root@mysql50 ~]# systemctl restart mysqld

数据导入

命令格式:

load date infile “目录/文件”
into table 库名.表名
fields terminated by “分隔符”
lines terminated by “\n” ;

只有root用户能导入权限
字段分隔符要与文件分隔符一致
表字段类型和字段个数要与文件匹配

导入步骤:建库–>建表–>拷贝文件到检索目录下–>导入数据–>查看表记录

1.建库 , 建表

mysql> create database db3;
Query OK, 1 row affected (0.00 sec)

mysql> create table db3.user(
    -> name   char(50),
    -> passwdrd  char(1),
    -> uid   int,
    -> gid   int,
    -> comment  varchar(150),
    -> hostdir  char(50),
    -> shell   char(50));
Query OK, 0 rows affected (0.64 sec)

mysql> desc db3.user
    -> ;

2.拷贝文件到检索目录下
利用system 可以在数据库里执行系统命令

mysql> system cp /etc/passwd  /myload/
mysql> system ls /myload
passwd

3.导入数据

mysql> load data infile "/myload/passwd"  into  table db3.user fields  terminated by ":" lines   terminated by "\n";

4.查看表数据

mysql> select * from db3.user;

数据导出

有三种方法导出数据

命令格式1:select into outfile “目录/文件”;
命令格式2:select into outfile “目录/文件”
fields terminated by “分隔符”;
命令格式3:select into outfile “目录/文件”
fields terminated by “分隔符”
lines terminated by “\n”;

导出数据有sql查询决定
导出的是表记录,不包括字段名
自动创建存储的文件
存储的文件不能重名,具有唯一性
默认分隔符是"\n".

1. 把表前两行导出到/mysql/user1.txt

mysql> select * from db3.user where id<=2 into  outfile "/myload/user1.txt";
Query OK, 2 rows affected (0.00 sec)

mysql> system cat /myload/user1.txt
1	root	x	0	0	root	/root	/bin/bash
2	bin	x	1	1	bin	/bin	/sbin/nologin

2.导出前两行命令到/myload/user2.txt 指定分隔符为 :

mysql> select * from db3.user where  id <= 2 into outfile "/myload/user2.txt"
    -> fields  terminated by ":";
Query OK, 2 rows affected (0.00 sec)

mysql> system cat /myload/user2.txt
1:root:x:0:0:root:/root:/bin/bash
2:bin:x:1:1:bin:/bin:/sbin/nologin

3.导出前三行到/myload/user3.txt 指定分隔符为# 指定换行符为@@@

mysql> select * from db3.user where id <= 3 into outfile "/myload/user3.txt"
    -> fields terminated by "#"
    -> lines  terminated by "@@@";
Query OK, 3 rows affected (0.00 sec)

mysql> system cat /myload/user3.txt
1#root#x#0#0#root#/root#/bin/bash@@@2#bin#x#1#1#bin#/bin#/sbin/nologin@@@3#daemon#x#2#2#daemon#/sbin#/sbin/nologin@@@mysql> 

4.导出name行uid行gid行到/myload/user4.txt 指定分隔符为 :

mysql> select name,uid,gid from db3.user  into outfile "/myload/user4.txt"
    -> fields terminated by ":";
Query OK, 21 rows affected (0.00 sec)

mysql> system cat /myload/user4.txt
root:0:0
bin:1:1
daemon:2:2
adm:3:4
lp:4:7
sync:5:0
shutdown:6:0

注意事项:
确认/myload目录有写入权限
确认重启mysql数据库