MySQL(SQL数据导入/导出)

使用SQL语句完成下列导出、导入操作：
1)将/etc/passwd文件导入userdb库userlist表并给每条记录加编号
2)将userdb库userlist表中UID小于100的前10条记录导出，存为/dbak/ulist.txt文件
实现此案例需要按照如下步骤进行。

步骤一：将/etc/passwd文件导入MySQL数据库

导入后的表结构取决于/etc/passwd配置文件。若一时记不住各字段的含义，也可以查看passwd配置文件的man手册页，找到格式描述相关的说明，比如：
[root@dbsvr1 ~]# man 5 passwd
.. ..
       There is one entry per line, and each line has the format:

              account:password:UID:GID:GECOS:directory:shell
    												//各字段的顺序、大致用途

The field descriptions are:  					//以下详细解释各字段的作用

              account   the name of the user on the  system.   It  should  not
                        contain capital letters.

              password  the  encrypted  user password, an asterisk (*), or the
                        letter 'x'.  (See  pwconv(8)  for  an  explanation  of
                        'x'.)

              UID       the numerical user ID.

              GID       the numerical primary group ID for this user.

              GECOS     This field is optional and only used for informational
                        purposes.  Usually, it  contains  the  full  username.
                        GECOS  means  General Electric Comprehensive Operating
                        System, which has been renamed to GCOS when GE’s large
                        systems   division  was  sold  to  Honeywell.   Dennis
                        Ritchie has reported: "Sometimes we sent printer  out-
                        put or batch jobs to the GCOS machine.  The gcos field
                        in the password file was a place to stash the informa-
                        tion for the $IDENTcard.  Not elegant."

              directory the user’s $HOME directory.

              shell     the  program  to run at login (if empty, use /bin/sh).
                        If set to a non-existing executable, the user will  be
                        unable to login through login(1).
                        put or batch jobs to the GCOS machine.  The gcos field
                        in the password file was a place to stash the informa-
                        tion for the $IDENTcard.  Not elegant."
.. ..

1）新建userdb库、userlist表

以数据库用户root登入MySQL服务：
[root@dbsvr1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
新建userdb库，切换到userdb库：
mysql> CREATE DATABASE userdb;
Query OK, 1 row affected (0.04 sec)

mysql> USE userdb;
Database changed
新建userlist表，字段设置及相关操作参考如下：
mysql> CREATE TABLE userlist(
    -> username varchar(24) NOT NULL,
    -> password varchar(48) DEFAULT 'x',
    -> uid int(5) NOT NULL,
    -> gid int(5) NOT NULL,
    -> fullname varchar(48),
    -> homedir varchar(64) NOT NULL,
    -> shell varchar(24) NOT NULL
    -> );
Query OK, 0 rows affected (0.17 sec)
确认userlist表的结构：
mysql> DESC userlist;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(24) | NO   |     | NULL    |       |
| password | varchar(48) | YES  |     | x       |       |
| uid      | int(5)      | NO   |     | NULL    |       |
| gid      | int(5)      | NO   |     | NULL    |       |
| fullname | varchar(48) | YES  |     | NULL    |       |
| homedir  | varchar(64) | NO   |     | NULL    |       |
| shell    | varchar(24) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.06 sec)

2）执行导入操作

读取/etc/passwd文件内容，以“:”为分隔，导入到userlist表中：
mysql> LOAD DATA INFILE '/etc/passwd'
    -> INTO TABLE userlist
    -> FIELDS TERMINATED BY ':';
Query OK, 35 rows affected (0.06 sec)
Records: 35  Deleted: 0  Skipped: 0  Warnings: 0
上述操作中省略了行分隔 LINES TERMINATED BY '\n'，因为这是默认的情况（每行一条原始记录），除非需要以其他字符分割行，才需要用到这个。比如，以下操作指定了行分隔为'\n'，将/etc/passwd文件的内容导入另一个表userlist2，最终userlist2表的内容与userlsit的内容是一样的：
mysql> CREATE TABLE userlist2  				//参照userlist表结构建立新表
    -> SELECT * FROM userlist WHERE FALSE;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM userlist2;  			//确认新表userlist2无任何记录
Empty set (0.00 sec)

mysql> LOAD DATA INFILE '/etc/passwd'  		//导入时指定行分隔
    -> INTO TABLE userlist2
    -> FIELDS TERMINATED BY ':'
    -> LINES TERMINATED BY '\n';
Query OK, 35 rows affected (0.03 sec)
Records: 35  Deleted: 0  Skipped: 0  Warnings: 0

3）确认导入结果

分别统计userlist、userlist2表内的记录个数：
mysql> SELECT COUNT(*) FROM userlist;
+----------+
| COUNT(*) |
+----------+
|       35 |  								//userlist表有35条记录
+----------+
1 row in set (0.05 sec)

mysql> SELECT COUNT(*) FROM userlist2;
+----------+
| COUNT(*) |
+----------+
|       35 |  								//userlist表也有35条记录
+----------+
1 row in set (0.00 sec)
查看userlist表的前10条记录，列出用户名、UID、GID、宿主目录、登录Shell：
mysql> SELECT username,uid,gid,homedir,shell
    -> FROM userlist LIMIT 10;
+---------------+-----+-----+------------------------+---------------+
| username      | uid | gid | homedir                | shell         |
+---------------+-----+-----+------------------------+---------------+
| abrt          | 173 | 173 | /etc/abrt              | /sbin/nologin |
| adm           |   3 |   4 | /var/adm               | /sbin/nologin |
| apache        |  48 |  48 | /var/www               | /sbin/nologin |
| avahi-autoipd | 170 | 170 | /var/lib/avahi-autoipd | /sbin/nologin |
| bin           |   1 |   1 | /bin                   | /sbin/nologin |
| daemon        |   2 |   2 | /sbin                  | /sbin/nologin |
| dbus          |  81 |  81 | /                      | /sbin/nologin |
| ftp           |  14 |  50 | /var/ftp               | /sbin/nologin |
| games         |  12 | 100 | /usr/games             | /sbin/nologin |
| gdm           |  42 |  42 | /var/lib/gdm           | /sbin/nologin |
+---------------+-----+-----+------------------------+---------------+
10 rows in set (0.00 sec)
查看userlist2表的前10条记录，同样列出用户名、UID、GID、宿主目录、登录Shell：
mysql> SELECT username,uid,gid,homedir,shell
    -> FROM userlist2 LIMIT 10;
+----------+-----+-----+-----------------+----------------+
| username | uid | gid | homedir         | shell          |
+----------+-----+-----+-----------------+----------------+
| root     |   0 |   0 | /root           | /bin/bash      |
| bin      |   1 |   1 | /bin            | /sbin/nologin  |
| daemon   |   2 |   2 | /sbin           | /sbin/nologin  |
| adm      |   3 |   4 | /var/adm        | /sbin/nologin  |
| lp       |   4 |   7 | /var/spool/lpd  | /sbin/nologin  |
| sync     |   5 |   0 | /sbin           | /bin/sync      |
| shutdown |   6 |   0 | /sbin           | /sbin/shutdown |
| halt     |   7 |   0 | /sbin           | /sbin/halt     |
| mail     |   8 |  12 | /var/spool/mail | /sbin/nologin  |
| uucp     |  10 |  14 | /var/spool/uucp | /sbin/nologin  |
+----------+-----+-----+-----------------+----------------+
10 rows in set (0.00 sec)

步骤二：为userlist表中的每条记录添加自动编号

这个只要修改userlist表结构，添加一个自增字段即可。
比如，添加一个名为sn的序号列，作为userlist表的第一个字段：
1）添加自增主键字段sn

mysql> ALTER TABLE userlist
    -> ADD sn int(4) AUTO_INCREMENT PRIMARY KEY FIRST;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

2）验证自动编号结果

查看userlist表的前10条记录，列出序号、用户名、UID、GID、宿主目录：
mysql> SELECT sn,username,uid,gid,homedir
    -> FROM userlist LIMIT 10;
+----+---------------+-----+-----+------------------------+
| sn | username      | uid | gid | homedir                |
+----+---------------+-----+-----+------------------------+
|  1 | abrt          | 173 | 173 | /etc/abrt              |
|  2 | adm           |   3 |   4 | /var/adm               |
|  3 | apache        |  48 |  48 | /var/www               |
|  4 | avahi-autoipd | 170 | 170 | /var/lib/avahi-autoipd |
|  5 | bin           |   1 |   1 | /bin                   |
|  6 | daemon        |   2 |   2 | /sbin                  |
|  7 | dbus          |  81 |  81 | /                      |
|  8 | ftp           |  14 |  50 | /var/ftp               |
|  9 | games         |  12 | 100 | /usr/games             |
| 10 | gdm           |  42 |  42 | /var/lib/gdm           |
+----+---------------+-----+-----+------------------------+
10 rows in set (0.00 sec)

步骤三：从MySQL数据库中导出查询结果

以将userdb库userlist表中UID小于100的前10条记录导出为/dbak/ulist.txt文件为例，首先要确保目标文件夹存在，且msyql用户有权限写入（否则导出会失败）。
1）确认存放导出数据的文件夹

[root@dbsvr1 ~]# mkdir /dbbak  					//若没有此文件夹，可新建
[root@dbsvr1 ~]# chown mysql /dbbak  			//确保mysql有权限写入
[root@dbsvr1 ~]# ls -ld /dbbak/  				//确认权限
drwxr-xr-x. 2 mysql root 4096 1月  10 17:46 /dbbak/

2）导出userlsit表中UID小于100的前10条记录

如果以默认的'\n' 为行分隔，导出操作同样可不指定LINES TERMINATED BY：
mysql> SELECT * FROM userdb.userlist WHERE uid<100
    -> INTO OUTFILE '/dbbak/ulist.txt'
    -> FIELDS TERMINATED BY ':';
Query OK, 26 rows affected (0.08 sec)

3）确认导出结果

返回到Shell命令行，查看/dbbak/ulist.txt文件的行数：
[root@dbsvr1 ~]# wc -l /dbbak/ulist.txt
26 /dbbak/ulist.txt
查看/dbbak/ulist.txt文件的最后10行内容：
[root@dbsvr1 ~]# tail /dbbak/ulist.txt
22:postfix:x:89:89::/var/spool/postfix:/sbin/nologin
24:root:x:0:0:root:/root:/bin/bash
25:rpc:x:32:32:Rpcbind Daemon:/var/cache/rpcbind:/sbin/nologin
26:rpcuser:x:29:29:RPC Service User:/var/lib/nfs:/sbin/nologin
29:shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
30:sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
31:sync:x:5:0:sync:/sbin:/bin/sync
32:tcpdump:x:72:72::/:/sbin/nologin
34:uucp:x:10:14:uucp:/var/spool/uucp:/sbin/nologin
35:vcsa:x:69:69:virtual console memory owner:/dev:/sbin/nologin

4）验证两种会导出失败的情况

目标文件夹不存在时：
mysql> SELECT * FROM userdb.userlist
    -> INTO OUTFILE '/databackup/ulist.txt'
    -> FIELDS TERMINATED BY ':';
ERROR 1 (HY000): Can't create/write to file '/databackup/ulist.txt' (Errcode: 2 - No such file or directory)
目标文件夹存在，但是mysql没有写入权限时：
mysql> SELECT * FROM userdb.userlist
    -> INTO OUTFILE '/opt/ulist.txt'
    -> FIELDS TERMINATED BY ':';
ERROR 1 (HY000): Can't create/write to file '/opt/ulist.txt' (Errcode: 13 - Permission denied)