数据导入:一次性向表里存储很多数据
数据导出:一次性把表里的数据都取出来
1、检索目录:
数据导入或导出,存放数据的文件必须在mysql服务要求的目录下叫检索目录
1.1、查看检索目录
mysql> show variables like "%secure_file_priv%";
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)
mysql> exit
[root@mysql01 ~]# ls -ld /var/lib/mysql-files/
drwxr-x---. 2 mysql mysql 6 11月 29 2016 /var/lib/mysql-files/
[root@mysql01 ~]# ls -l /var/lib/mysql-files/
总用量 0
1.2、修改检索目录
[root@mysql01 ~]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/myload"
[root@mysql01 ~]# mkdir /myload/
[root@mysql01 ~]# chown mysql /myload/
[root@mysql01 ~]# ls -ld /myload/
drwxr-xr-x. 2 mysql root 6 5月 15 14:05 /myload/
[root@mysql01 ~]# systemctl restart mysqld
[root@mysql01 ~]# mysql -uroot -pjy1987...ZY2011
mysql> show variables like "secure_file_priv";
+------------------+----------+
| Variable_name | Value |
+------------------+----------+
| secure_file_priv | /myload/ |
+------------------+----------+
1 row in set (0.01 sec)
2、数据导入
把系统文件的内容存储到数据库服务的表里 文件的内容要规律
格式:
- load data infile "/检索目录/文件名" into table 库名.表名 fields terminated by "文件中列的间隔符号" lines terminated by "\n" ;
备注:
- fields terminated by 文件中列的间隔符号,不指定默认是一个 tab 键的宽度
- lines terminated by 文件中行的间隔符号,不指定默认一条记录就是文件中的1行
2.2、导入之前先建库、建表,建表根据导入文件的内容创建: 表头名、 表头个数、 表头数据类型、 根据文件内容定义)
mysql> create database if not exists jy1;
Query OK, 1 row affected (0.00 sec)
mysql> create table jy1.zy1(name char(30),password char(1),uid int ,gid int ,comment varchar(200), homedir varchar(60), shell varchar(30));
Query OK, 0 rows affected (0.01 sec)
mysql> desc jy1.zy1;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| name | char(30) | YES | | NULL | |
| password | char(1) | YES | | NULL | |
| uid | int(11) | YES | | NULL | |
| gid | int(11) | YES | | NULL | |
| comment | varchar(200) | YES | | NULL | |
| homedir | varchar(60) | YES | | NULL | |
| shell | varchar(30) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
7 rows in set (0.03 sec)
mysql> select * from jy1.zy1;
Empty set (0.00 sec)
mysql> exit
Bye
2.3、把导入文件拷贝到检索目录中,以/etc/passwd为例
[root@mysql01 ~]# mysql -uroot -pJY1987...zy2011
mysql> system cp /etc/passwd /myload/
mysql> system ls /myload/
passwd
2.4、数据库管理执行导入数据的命令
mysql> load data infile "/myload/passwd" into table jy1.zy1 fields terminated by ":" lines terminated by "\n";
Query OK, 20 rows affected (0.01 sec)
Records: 20 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from jy1.zy1;
+-----------------+----------+------+------+----------------------------+--------------------+----------------+
| name | password | uid | gid | comment | homedir | shell |
+-----------------+----------+------+------+----------------------------+--------------------+----------------+
| root | x | 0 | 0 | root | /root | /bin/bash |
| bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
| daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
3、数据导出
把数据库服务的表里数据保存到系统文件里,注意导出的数据不包括表头名 ,只有表里的行。存放导出数据的文件名不需要事先创建且具有唯一。
格式:
select 字段名列表 from 库.表 where 条件 into outfile "/检索目录名/文件名" [fields terminated by | lines terminated by "符号"];
- 备注:
fields terminated by:文件中的列的间隔符号,不指定默认是一个 tab 键的宽度
lines terminated by:文件中行间隔符号 不指定默认一条记录就是文件中的1行
- 3.1、数据导出
-
mysql> select * from jy1.zy1 where uid <=4 into outfile "/myload/1.txt"; #方式一 Query OK, 5 rows affected (0.00 sec) mysql> system ls /myload 1.txt passwd mysql> system cat /myload/1.txt; root x 0 0 root /root /bin/bash 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 mysql> select name , homedir , uid from jy1.zy1 where uid <= 10 into outfile "/myload/2.txt" fields terminated by ":"; #方式二 Query OK, 9 rows affected (0.00 sec) mysql> system cat /myload/2.txt root:/root:0 bin:/bin:1 daemon:/sbin:2 adm:/var/adm:3 lp:/var/spool/lpd:4 sync:/sbin:5 shutdown:/sbin:6 halt:/sbin:7 mail:/var/spool/mail:8 mysql> select name , homedir , uid from jy1.zy1 where uid <= 13 into outfile "/myload/3.txt" fields terminated by ":" lines terminated by "--"; #方式三 Query OK, 11 rows affected (0.00 sec) mysql> system cat /myload/3.txt; root:/root:0--bin:/bin:1--daemon:/sbin:2--adm:/var/adm:3--lp:/var/spool/lpd:4--sync:/sbin:5--shutdown:/sbin:6--halt:/sbin:7--mail:/var/spool/mail:8--operator:/root:11--games:/usr/games:12--mysql >