3.1 数据导入导出

案例分析：

vsftp+MySQL

DROP DATABASE IF EXISTS vsftpd;

--- 创建数据库
CREATE DATABASE vsftpd;

--- 创建用户设置密码并授予对 vsftpd 库查询权限
GRANT SELECT ON vsftpd.* TO 'vsftpd'@'127.0.0.1' IDENTIFIED BY '123456';

--- 创建数据表
USE vsftpd;
CREATE TABLE `vsftpusers` (
  `id` int AUTO_INCREMENT NOT NULL,
  `name` VARCHAR(50) COMMENT'姓名',
  `username` char(20) binary NOT NULL COMMENT '登录名',
  `password` char(48) binary NOT NULL COMMENT '登录密码',
  `phone` varchar(11) DEFAULT NULL COMMENT '电话号码',
  `explain` varchar(255) DEFAULT NULL COMMENT '备注',
  primary key(`id`)
)character set = utf8, COMMENT 'vsftpd用户认证表';

--- 插入数据
INSERT INTO vsftpusers(name,username,password) VALUES('张三','a1',password('123123'));
INSERT INTO vsftpusers(name,username,password) VALUES('李四','a2',password('123456'));
INSERT INTO vsftpusers(name,username,password) VALUES('王五','a3',password('456456'));
INSERT INTO vsftpusers(name,username,password) VALUES('赵六','a4',password('456456'));
SELECT * FROM vsftpd.vsftpusers;

导入数据：

 

 

查看数据导入路径

mysql> show variables like "secure_file_priv"\G
*************************** 1. row ***************************
Variable_name: secure_file_priv
        Value: /var/lib/mysql-files/
1 row in set (0.00 sec)

创建导入文件的表时，表内的字段，类型，约束条件等取悦于导入文件的内容来定

此实验以导入/etc/passwd文件为例

创建导入数据的表
mysql> create table user(
    -> name char(50),
    -> password char(1),
    -> uid smallint,
    -> gid smallint,
    -> describ char(200),
    -> homedir char(200),
    -> shell char(100),
    -> index(name)
    -> );
Query OK, 0 rows affected (0.01 sec)

数据导入用法
基本用法
    LOAD    DATA    INFILE    “目录名/文件名”
    INTO    TABLE    表名
    FIELDS    TERMINATED  BY      “分隔符”
    LINES    TERMINATED  BY      “\n”;
注意事项
    字段分隔符要与文件内的一致
    指定导入文件的绝对路径
    导入数据的表字段类型要与文件字段匹配
    禁用SELinux保护机制

导入数据
mysql> load data infile "/var/lib/mysql-files/passwd"
    -> into table user
    -> fields terminated by ":"
    -> lines terminated by "\n";
Query OK, 21 rows affected (0.00 sec)
Records: 21  Deleted: 0  Skipped: 0  Warnings: 0

导出数据
基本用法
    SQL查询       INTO   OUTFILE       “目录名/文件名”
    FIELDS      TERMINATED     BY   “分隔符”
    LINES         TERMINATED     BY    “\n”;
注意事项
    导出的内容由SQL查询语句决定
    导出的是表中的记录，不包括表字段名
    禁用SELinux保护机制
    

数据导出时，不可预先创建文件，在导出时指定文件名

mysql> select * from user into outfile "/root/user.txt"
    -> fields terminated by ":"
    -> lines terminated by "\n";
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> select * from user into outfile "/var/lib/mysql-files/user.txt"
    -> fields terminated by ":"
    -> lines terminated by "\n";
Query OK, 21 rows affected (0.00 sec)

 导出指定字段：

mysql> select uid,name,password from user
    -> into outfile "/var/lib/mysql-files/unp.txt"
    -> fields terminated by ":"
    -> lines terminated by "\n";
Query OK, 21 rows affected (0.00 sec)


mysql> select uid,name from user where uid <=500
    -> into outfile "/var/lib/mysql-files/un.txt"
    -> fields terminated by ":"
    -> lines terminated by "\n";
Query OK, 19 rows affected (0.00 sec)