当前位置：Gxlcms > 数据库问题 > SQL数据导入/导出，操作表记录，查询及匹配条件

SQL数据导入/导出，操作表记录，查询及匹配条件

时间：2021-07-01 10:21:17 帮助过：20人阅读

  • .. ..
  • Each line of the file describes a single user, and contains seven colon-sep‐
  • arated fields:
  • name:password:UID:GID:GECOS:directory:shell                                                 //各字段的顺序、大致用途
  • The field are as follows: //以下详细解释各字段的作用
  • name This is the user‘s login name. It should not contain capital
  • letters.
  • password This is either the encrypted user password, an asterisk (*), or
  • the letter ‘x‘. (See pwconv(8) for an explanation of ‘x‘.)
  • UID The privileged root login account (superuser) has the user ID 0.
  • GID This is the numeric primary group ID for this user. (Additional
  • groups for the user are defined in the system group file; see
  • group(5)).
  • GECOS stands for "General Electric Comprehensive Operating Sys‐
  • tem", which was renamed to GCOS when GE‘s large systems division
  • was sold to Honeywell. Dennis Ritchie has reported: "Sometimes
  • we sent printer output or batch jobs to the GCOS machine. The
  • gcos field in the password file was a place to stash the infor‐
  • mation for the $IDENTcard. Not elegant."
  • directory This is the user‘s home directory: the initial directory where
  • the user is placed after logging in. The value in this field is
  • used to set the HOME environment variable.
  • shell This is the program to run at login (if empty, use /bin/sh). If
  • set to a nonexistent executable, the user will be unable to
  • login through login(1). The value in this field is used to set
  • the SHELL environment variable.
  • .. ..

    • 1）新建userdb库、userlist表

    以数据库用户root登入MySQL服务：

    1. [root@dbsvr1 ~]# mysql -u root -p
    2. Enter password:
    3. Welcome to the MySQL monitor. Commands end with ; or \g.
    4. Your MySQL connection id is 5
    5. Server version: 5.7.17 MySQL Community Server (GPL)
    7. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
    9. Oracle is a registered trademark of Oracle Corporation and/or its
    10. affiliates. Other names may be trademarks of their respective
    11. owners.
    13. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
    15. mysql>

    新建userdb库，切换到userdb库：

    1. mysql> CREATE DATABASE userdb;
    2. Query OK, 1 row affected (0.00 sec)
    4. mysql> USE userdb;
    5. Database changed

    新建userlist表，字段设置及相关操作参考如下：

    1. mysql> CREATE TABLE userlist(
    2. -> username varchar(24) NOT NULL,
    3. -> password varchar(48) DEFAULT ‘x‘,
    4. -> uid int(5) NOT NULL,
    5. -> gid int(5) NOT NULL,
    6. -> fullname varchar(48),
    7. -> homedir varchar(64) NOT NULL,
    8. -> shell varchar(24) NOT NULL
    9. -> );
    10. Query OK, 0 rows affected (0.70 sec)

    确认userlist表的结构：

    1. mysql> DESC userlist;
    2. +----------+-------------+------+-----+---------+-------+
    3. | Field | Type | Null | Key | Default | Extra |
    4. +----------+-------------+------+-----+---------+-------+
    5. | username | varchar(24) | NO | | NULL | |
    6. | password | varchar(48) | YES | | x | |
    7. | uid | int(5) | NO | | NULL | |
    8. | gid | int(5) | NO | | NULL | |
    9. | fullname | varchar(48) | YES | | NULL | |
    10. | homedir | varchar(64) | NO | | NULL | |
    11. | shell | varchar(24) | NO | | NULL | |
    12. +----------+-------------+------+-----+---------+-------+
    13. 7 rows in set (0.01 sec)

    2）如果直接导入会报错。在MySQL 5.7.6版本之后，导入文件只能在secure_file_priv指定的文件夹下。执行show variables like ‘%secure%‘命令显示文件目录：

    1. mysql> LOAD DATA INFILE ‘/etc/passwd‘ INTO TABLE userlist FIELDS TERMINATED BY ‘:‘;
    2. ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
    3. mysql> show variables like ‘%secure%‘;
    4. +--------------------------+-----------------------+
    5. | Variable_name | Value |
    6. +--------------------------+-----------------------+
    7. | require_secure_transport | OFF |
    8. | secure_auth | ON |
    9. | secure_file_priv | /var/lib/mysql-files/ |
    10. +--------------------------+-----------------------+
    11. 3 rows in set (0.00 sec)

    3）执行导入操作

    将/etc/passwd文件复制到/var/lib/mysql-files/目录下，

    读取/var/lib/mysql-files/passwd文件内容，以“:”为分隔，导入到userlist表中：

    1. [root@dbsvr1 ~]#cp /etc/passwd /var/lib/mysql-files/
    2. mysql> LOAD DATA INFILE ‘/var/lib/mysql-files/passwd‘
    3. -> INTO TABLE userlist
    4. -> FIELDS TERMINATED BY ‘:‘;
    5. Query OK, 39 rows affected (0.11 sec)
    6. Records: 39 Deleted: 0 Skipped: 0 Warnings: 0

    上述操作中省略了行分隔 LINES TERMINATED BY ‘\n‘，因为这是默认的情况（每行一条原始记录），除非需要以其他字符分割行，才需要用到这个。比如，以下操作指定了行分隔为‘\n‘，将/var /lib/mysql-files/passwd文件的内容导入另一个表userlist2，最终userlist2表的内容与userlsit的内容是 一样的：

    代码

    4）确认导入结果

    分别统计userlist、userlist2表内的记录个数：

    1. mysql> SELECT COUNT(*) FROM userlist;
    2. +----------+
    3. | COUNT(*) |
    4. +----------+
    5. | 39 | //userlist表有39条记录
    6. +----------+
    7. 1 row in set (0.00 sec)
    9. mysql> SELECT COUNT(*) FROM userlist2;
    10. +----------+
    11. | COUNT(*) |
    12. +----------+
    13. | 39 |                                 //userlist表也有39条记录
    14. +----------+
    15. 1 row in set (0.00 sec)

    查看userlist表的前10条记录，列出用户名、UID、GID、宿主目录、登录Shell：

    1. mysql> SELECT username,uid,gid,homedir,shell
    2. -> FROM userlist LIMIT 10;
    3. +----------+-----+-----+-----------------+----------------+
    4. | username | uid | gid | homedir | shell |
    5. +----------+-----+-----+-----------------+----------------+
    6. | root | 0 | 0 | /root | /bin/bash |
    7. | bin | 1 | 1 | /bin | /sbin/nologin |
    8. | daemon | 2 | 2 | /sbin | /sbin/nologin |
    9. | adm | 3 | 4 | /var/adm | /sbin/nologin |
    10. | lp | 4 | 7 | /var/spool/lpd | /sbin/nologin |
    11. | sync | 5 | 0 | /sbin | /bin/sync |
    12. | shutdown | 6 | 0 | /sbin | /sbin/shutdown |
    13. | halt | 7 | 0 | /sbin | /sbin/halt |
    14. | mail | 8 | 12 | /var/spool/mail | /sbin/nologin |
    15. | operator | 11 | 0 | /root | /sbin/nologin |
    16. +----------+-----+-----+-----------------+----------------+
    17. 10 rows in set (0.00 sec)

    查看userlist2表的前10条记录，同样列出用户名、UID、GID、宿主目录、登录Shell：

    1. mysql> SELECT username,uid,gid,homedir,shell
    2. -> FROM userlist2 LIMIT 10;
    3. +----------+-----+-----+-----------------+----------------+
    4. | username | uid | gid | homedir | shell |
    5. +----------+-----+-----+-----------------+----------------+
    6. | root | 0 | 0 | /root | /bin/bash |
    7. | bin | 1 | 1 | /bin | /sbin/nologin |
    8. | daemon | 2 | 2 | /sbin | /sbin/nologin |
    9. | adm | 3 | 4 | /var/adm | /sbin/nologin |
    10. | lp | 4 | 7 | /var/spool/lpd | /sbin/nologin |
    11. | sync | 5 | 0 | /sbin | /bin/sync |
    12. | shutdown | 6 | 0 | /sbin | /sbin/shutdown |
    13. | halt | 7 | 0 | /sbin | /sbin/halt |
    14. | mail | 8 | 12 | /var/spool/mail | /sbin/nologin |
    15. | operator | 11 | 0 | /root | /sbin/nologin |
    16. +----------+-----+-----+-----------------+----------------+
    17. 10 rows in set (0.00 sec)

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

    这个只要修改userlist表结构，添加一个自增字段即可。

    比如，添加一个名为sn的序号列，作为userlist表的第一个字段：

    1）添加自增主键字段sn

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

    2）验证自动编号结果

    查看userlist表的前10条记录，列出序号、用户名、UID、GID、宿主目录：

    1. mysql> SELECT sn,username,uid,gid,homedir
    2. -> FROM userlist LIMIT 10;
    3. +----+----------+-----+-----+-----------------+
    4. | sn | username | uid | gid | homedir |
    5. +----+----------+-----+-----+-----------------+
    6. | 1 | root | 0 | 0 | /root |
    7. | 2 | bin | 1 | 1 | /bin |
    8. | 3 | daemon | 2 | 2 | /sbin |
    9. | 4 | adm | 3 | 4 | /var/adm |
    10. | 5 | lp | 4 | 7 | /var/spool/lpd |
    11. | 6 | sync | 5 | 0 | /sbin |
    12. | 7 | shutdown | 6 | 0 | /sbin |
    13. | 8 | halt | 7 | 0 | /sbin |
    14. | 9 | mail | 8 | 12 | /var/spool/mail |
    15. | 10 | operator | 11 | 0 | /root |
    16. +----+----------+-----+-----+-----------------+
    17. 10 rows in set (0.00 sec)

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

    以将userdb库userlist表中UID小于100的前10条记录导出为/var/lib/mysql-files/ulist.txt文件为例。

    1）确认存放导出数据的文件夹

    1. [root@dbsvr1 ~]# ls -ld /var/lib/mysql-files/                 
    2. drwxr-x---. 2 mysql mysql 19 4月 7 11:15 /var/lib/mysql-files/

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

    如果以默认的‘\n‘ 为行分隔，导出操作同样可不指定LINES TERMINATED BY：

    1. mysql> SELECT * FROM userdb.userlist WHERE uid<100
    2. -> INTO OUTFILE ‘/var/lib/mysql-files/ulist.txt‘
    3. -> FIELDS TERMINATED BY ‘:‘;
    4. Query OK, 24 rows affected (0.00 sec)

    3）确认导出结果

    返回到Shell命令行，查看/var/lib/mysql-files/ulist.txt文件的行数：

    1. [root@dbsvr1 ~]# wc -l /var/lib/mysql-files/ulist.txt
    2. 24 /var/lib/mysql-files/ulist.txt

    查看/var/lib/mysql-files/ulist.txt文件的最后10行内容：

    1. [root@dbsvr1 ~]# tail /var/lib/mysql-files/ulist.txt
    2. 19:avahi:x:70:70:Avahi mDNS/DNS-SD Stack:/var/run/avahi-daemon:/sbin/nologin
    3. 24:rpc:x:32:32:Rpcbind Daemon:/var/lib/rpcbind:/sbin/nologin
    4. 25:rpcuser:x:29:29:RPC Service User:/var/lib/nfs:/sbin/nologin
    5. 28:radvd:x:75:75:radvd user:/:/sbin/nologin
    6. 29:ntp:x:38:38::/etc/ntp:/sbin/nologin
    7. 33:gdm:x:42:42::/var/lib/gdm:/sbin/nologin
    8. 35:postfix:x:89:89::/var/spool/postfix:/sbin/nologin
    9. 36:sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
    10. 37:tcpdump:x:72:72::/:/sbin/nologin
    11. 39:mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/false

    2 操作表记录

    2.1 问题

    练习表记录的操作

    1. 表记录的插入
    2. 表记录的更新
    3. 表记录的查询
    4. 表记录的删除

    2.2 步骤

    实现此案例需要按照如下步骤进行。

    步骤一：创建stu_info表，并确保stu_info表记录为空。

    在userdb库中创建stu_info表：

    1. [root@dbsvr1 ~]# mysql -uroot -p
    2. Enter password:
    3. Welcome to the MySQL monitor. Commands end with ; or \g.
    4. Your MySQL connection id is 19
    5. Server version: 5.7.17 MySQL Community Server (GPL)
    7. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
    9. Oracle is a registered trademark of Oracle Corporation and/or its
    10. affiliates. Other names may be trademarks of their respective
    11. owners.
    13. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
    15. mysql> use userdb;
    16. Reading table information for completion of table and column names
    17. You can turn off this feature to get a quicker startup with -A
    19. Database changed
    20. mysql> CREATE TABLE stu_info(
    21. -> name varchar(12) NOT NULL,
    22. -> gender enum(‘boy‘,‘girl‘) DEFAULT ‘boy‘,
    23. -> age int(3) NOT NULL
    24. -> );
    25. Query OK, 0 rows affected (0.23 sec)

    删除stu_info表的所有记录：

    1. mysql> DELETE FROM stu_info;
    2. Query OK, 0 rows affected (0.00 sec) //stu_info表刚建立 删除零条记录

    确认删除结果：

    1. mysql> SELECT * FROM stu_info;
    2. Empty set (0.00 sec)

    步骤二：练习表记录的操作

    1）插入记录时，指定记录的每一个字段的值

    这种情况下，不需要明确指出字段，但每条记录的值的顺序、类型都必须与表格结构向一致，否则可能无法正确插入记录。

    比如，以下操作将向stu_info表插入3条表记录：

    1. mysql> INSERT stu_info VALUES
    2. -> (‘Jim‘,‘girl‘,24),
    3. -> (‘Tom‘,‘boy‘,21),
    4. -> (‘Lily‘,‘girl‘,20);
    5. Query OK, 3 rows affected (0.15 sec)
    6. Records: 3 Duplicates: 0 Warnings: 0

    完成插入后确认表记录：

    1. mysql> SELECT * FROM stu_info;
    2. +------+--------+-----+
    3. | name | gender | age |
    4. +------+--------+-----+
    5. | Jim | girl | 24 |
    6. | Tom | boy | 21 |
    7. | Lily | girl | 20 |
    8. +------+--------+-----+
    9. 3 rows in set (0.00 sec)

    2）插入记录时，只指定记录的部分字段的值

    这种情况下，必须指出各项值所对应的字段；而且，未赋值的字段应设置有默认值或者有自增填充属性或者允许为空，否则插入操作将会失败。

    比如，向stu_info表插入Jerry的年龄信息，性别为默认的“boy”，自动编号，相关操作如下：

    1. mysql> INSERT INTO stu_info(name,age)
    2. -> VALUES(‘Jerry‘,27);
    3. Query OK, 1 row affected (0.04 sec)

    类似的，再插入用户Mike的年龄信息：

    1. mysql> INSERT INTO stu_info(name,age)
    2. -> VALUES(‘Mike‘,21);
    3. Query OK, 1 row affected (0.05 sec)

    确认目前stu_info表的所有记录：

    1. mysql> SELECT * FROM stu_info;
    2. +-------+--------+-----+
    3. | name | gender | age |
    4. +-------+--------+-----+
    5. | Jim | girl | 24 |
    6. | Tom | boy | 21 |
    7. | Lily | girl | 20 |
    8. | Jerry | boy | 27 |
    9. | Mike | boy | 21 |
    10. +-------+--------+-----+
    11. 5 rows in set (0.00 sec)

    3）更新表记录时，若未限制条件，则适用于所有记录

    将stu_info表中所有记录的age设置为10：

    1. mysql> UPDATE stu_info SET age=10;
    2. Query OK, 5 rows affected (0.04 sec)
    3. Rows matched: 5 Changed: 5 Warnings: 0

    确认更新结果：

    1. mysql> SELECT * FROM stu_info;
    2. +-------+--------+-----+
    3. | name | gender | age |
    4. +-------+--------+-----+
    5. | Jim | girl | 10 |
    6. | Tom | boy | 10 |
    7. | Lily | girl | 10 |
    8. | Jerry | boy | 10 |
    9. | Mike | boy | 10 |
    10. +-------+--------+-----+
    11. 5 rows in set (0.00 sec)

    4）更新表记录时，可以限制条件，只对符合条件的记录有效

    将stu_info表中所有性别为“boy”的记录的age设置为20：

    1. mysql> UPDATE stu_info SET age=20
    2. -> WHERE gender=‘boy‘;
    3. Query OK, 3 rows affected (0.04 sec)
    4. Rows matched: 3 Changed: 3 Warnings: 0

    确认更新结果：

    1. mysql> SELECT * FROM stu_info;
    2. +-------+--------+-----+
    3. | name | gender | age |
    4. +-------+--------+-----+
    5. | Jim | girl | 10 |
    6. | Tom | boy | 20 |
    7. | Lily | girl | 10 |
    8. | Jerry | boy | 20 |
    9. | Mike | boy | 20 |
    10. +-------+--------+-----+
    11. 5 rows in set (0.00 sec)

    5）删除表记录时，可以限制条件，只删除符合条件的记录

    删除stu_info表中年龄小于18的记录：

    1. mysql> DELETE FROM stu_info WHERE age < 18;
    2. Query OK, 2 rows affected (0.03 sec)

    确认删除结果：

    1. mysql> SELECT * FROM stu_info;
    2. +-------+--------+-----+
    3. | name | gender | age |
    4. +-------+--------+-----+
    5. | Tom | boy | 20 |
    6. | Jerry | boy | 20 |
    7. | Mike | boy | 20 |
    8. +-------+--------+-----+
    9. 3 rows in set (0.00 sec)

    6）删除表记录时，如果未限制条件，则会删除所有的表记录

    删除stu_info表的所有记录：

    1. mysql> DELETE FROM stu_info;
    2. Query OK, 3 rows affected (0.00 sec)

    确认删除结果：

    1. mysql> SELECT * FROM stu_info;
    2. Empty set (0.00 sec)

    3 查询及匹配条件

    3.1 问题

    练习常见的SQL查询及条件设置

    1. 创建stu_info表，并插入数据
    2. 练习常见SQL查询及条件设置

    3.2 步骤

    实现此案例需要按照如下步骤进行。

    步骤一：根据任务要求建立员工档案表stu_info（如上个实验已创建，可将上个实验stu_info表中记录清除后继续使用）

    1）在userdb库中创建stu_info表

    以root用户登入MySQL服务器：

    1. [root@dbsvr1 ~]# mysql -u root -p
    2. Enter password:
    3. Welcome to the MySQL monitor. Commands end with ; or \g.
    4. Your MySQL connection id is 5
    5. Server version: 5.6.15 MySQL Community Server (GPL)
    7. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
    9. Oracle is a registered trademark of Oracle Corporation and/or its
    10. affiliates. Other names may be trademarks of their respective
    11. owners.
    13. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
    15. mysql>

    打开test库：

    1. mysql> USE userdb;
    2. Reading table information for completion of table and column names
    3. You can turn off this feature to get a quicker startup with -A
    5. Database changed

    创建stu_info表，包括name、gender、age三个字段：

    1. mysql> CREATE TABLE stu_info(
    2. -> name varchar(12) NOT NULL,

    人气教程排行

    本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！