SQL自学，mysql从入门到精通 --- 第 15天，数据导入、导出
数据的导入、导出
-- 查看当前设置的目录路径，限制从数据库服务器读取和写入文件的操作只能在指定的目录中进行,在安全性和文件操作限制方面具有重要意义。root@mysqldb 14:19: [(none)]> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------------------+
| Variable_name | Value |
+------------------+-------------------+
| secure_file_priv | /data/mysql/data/ |
+------------------+-------------------+
1 row in set (0.02 sec)#配置文件
[root@mrloam ~]# grep secure_file_priv /etc/my.cnf
secure_file_priv = /data/mysql/data1.数据导入
root@mysqldb 14:40: [(none)]> CREATE TABLE db3.user (-> name VARCHAR(30),-> password VARCHAR(8),-> uid INT,-> gid INT,-> comment VARCHAR(150),-> homedir CHAR(80),-> shell CHAR(60)-> );
Query OK, 0 rows affected (0.01 sec)root@mysqldb 14:40: [(none)]> DESC db3.user;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| name | varchar(30) | YES | | NULL | |
| password | varchar(8) | YES | | NULL | |
| uid | int(11) | YES | | NULL | |
| gid | int(11) | YES | | NULL | |
| comment | varchar(150) | YES | | NULL | |
| homedir | char(80) | YES | | NULL | |
| shell | char(60) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
7 rows in set (0.01 sec)root@mysqldb 14:40: [(none)]> SELECT * FROM db3.user;
Empty set (0.00 sec)-- 准备数据文件
root@mysqldb 14:41: [(none)]> system cp /etc/passwd /data/mysql/data/
root@mysqldb 14:42: [(none)]> system ls /data/mysql/data/
auto.cnf client-key.pem ibdata1 mysql public_key.pem sys
ca-key.pem d1 ib_logfile0 passwd server-cert.pem
ca.pem db3 ib_logfile1 performance_schema server-key.pem
client-cert.pem ib_buffer_pool ibtmp1 private_key.pem sql_self_learning_skills-- 导入数据
root@mysqldb 14:43: [(none)]> load data infile "/data/mysql/data/passwd" into table db3.user fields terminated by ":" lines terminated by "\n";
Query OK, 21 rows affected (0.00 sec)
Records: 21 Deleted: 0 Skipped: 0 Warnings: 0root@mysqldb 14:45: [(none)]> alter table db3.user add id int primary key auto_increment first;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0root@mysqldb 14:46: [(none)]> select * from db3.user ;
+----+-----------------+----------+------+------+----------------------------+--------------------+----------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+-----------------+----------+------+------+----------------------------+--------------------+----------------+
| 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 |
| 4 | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin |
| 5 | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin |
| 6 | sync | x | 5 | 0 | sync | /sbin | /bin/sync |
| 7 | shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown |
| 8 | halt | x | 7 | 0 | halt | /sbin | /sbin/halt |
| 9 | mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin |
| 10 | operator | x | 11 | 0 | operator | /root | /sbin/nologin |
| 11 | games | x | 12 | 100 | games | /usr/games | /sbin/nologin |
| 12 | ftp | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin |
| 13 | nobody | x | 99 | 99 | Nobody | / | /sbin/nologin |
| 14 | systemd-network | x | 192 | 192 | systemd Network Management | / | /sbin/nologin |
| 15 | dbus | x | 81 | 81 | System message bus | / | /sbin/nologin |
| 16 | polkitd | x | 999 | 998 | User for polkitd | / | /sbin/nologin |
| 17 | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin |
| 18 | postfix | x | 89 | 89 | | /var/spool/postfix | /sbin/nologin |
| 19 | chrony | x | 998 | 996 | | /var/lib/chrony | /sbin/nologin |
| 20 | mysql | x | 1000 | 1000 | | /home/mysql | /sbin/nologin |
| 21 | test | x | 1001 | 1001 | | /home/test | /bin/bash |
+----+-----------------+----------+------+------+----------------------------+--------------------+----------------+
21 rows in set (0.00 sec)2.数据导出
把表记录存储到系统文件里
批量处理数据
命令格式:
sql查询命令 into outfile “目录/文件名”
[ fields terminated by “列间隔符号” lines terminated by “行间隔符号”];
-- 数据导出
root@mysqldb 14:47: [(none)]> select * from db3.user where id <= 10 into outfile "/data/mysql/data/a.txt";
Query OK, 10 rows affected (0.00 sec)root@mysqldb 14:51: [(none)]> select name ,shell from db3.user into outfile "/data/mysql/data/b.txt" fields terminated by "###";
Query OK, 21 rows affected (0.00 sec)root@mysqldb 14:51: [(none)]> select name , shell , uid from db3.user where id <= 3-> into outfile "/data/mysql/data/c.txt"-> fields terminated by "????" lines terminated by "###" ;
Query OK, 3 rows affected (0.00 sec)#查看
[root@mrloam ~]# cat /data/mysql/data/a.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
4 adm x 3 4 adm /var/adm /sbin/nologin
5 lp x 4 7 lp /var/spool/lpd /sbin/nologin
6 sync x 5 0 sync /sbin /bin/sync
7 shutdown x 6 0 shutdown /sbin /sbin/shutdown
8 halt x 7 0 halt /sbin /sbin/halt
9 mail x 8 12 mail /var/spool/mail /sbin/nologin
10 operator x 11 0 operator /root /sbin/nologin[root@mrloam ~]# cat /data/mysql/data/b.txt
root###/bin/bash
bin###/sbin/nologin
daemon###/sbin/nologin
adm###/sbin/nologin
lp###/sbin/nologin
sync###/bin/sync
shutdown###/sbin/shutdown
halt###/sbin/halt
mail###/sbin/nologin
operator###/sbin/nologin
games###/sbin/nologin
ftp###/sbin/nologin
nobody###/sbin/nologin
systemd-network###/sbin/nologin
dbus###/sbin/nologin
polkitd###/sbin/nologin
sshd###/sbin/nologin
postfix###/sbin/nologin
chrony###/sbin/nologin
mysql###/sbin/nologin
test###/bin/bash[root@mrloam ~]# cat /data/mysql/data/c.txt
root????/bin/bash????0###bin????/sbin/nologin????1###daemon????/sbin/nologin????2###
3.管理表记录
root@mysqldb 15:06: [(none)]> insert into db3.user-> values-> (22,"tom","x",2001,2001,"test user","/home/tom","/bin/bash"), -> (23,"tom","x",2001