MySQL存储引擎、数据导入导出、管理表记录

工作原理

MySQL存储引擎

• 作为可插拔式的组件提供

1. MySQL服务软件自带的功能程序，处理表的处理器
2. 不同的存储引擎有不同的功能饥和数据存储方式

• 默认的存储引擎

1. MySQL 5.0/5.1 —> MyISAM
2. MySQL 5.5/5.6 —> InnoDB

列出可用的存储引擎

• SHOW ENGINES;或SHOW ENGINES\G

修改表的存储引擎

• 建表的时候手动指定

1. 未指定时，使用默认的存储引擎
2. show create table 表名\G;可确认

• 设置默认的存储引擎

1. 修改/etc/my.cnf配置文件
2. default-storage-engine=存储引擎的名称

存储引擎的特点

myisam 存储引擎

• 主要特点

1. 支持表级锁
2. 不支持事务、事务回滚、外键

• 相关的表文件

1. 表名.frm //存储的是表结构
2. 表名.MYI //存储的是表的索引
3. 表名.MYD //存储的是表数据

InnoDB存储引擎

• 主要特点

1. 支持行级锁定
2. 支持事务、事务回滚、外键

• 相关的表文件

1. 表名.frm //存储的表结构
2. 表名.ibd //存储的是表数据+索引
3. ibdata1 //事务日志文件
4. ib_logfile0
5. ib_logfile1

查询访问多的表使用myisam存储引擎，节省系统资源,写操作多的表使用innodb存储引擎，并发访问量大

MySQL锁机制

• 锁粒度

1. 表级锁：一次直接对整张表进行加锁(myisam)
2. 行级锁：只锁定某一行(innodb)
3. 页级锁：对整个页面(MySQL管理数据的基本存储单位)进行加锁

• 锁类型

1. 读锁（共享锁）：支持并发读
2. 写锁（互斥锁、排他锁）：是独占锁，上锁期间其他线程不能读表或写表

事务回滚：在执行事务时，任意一步失败，会恢复所有操作

事务特性（ACID）

• Atomic:原子性
  事务的整个操作是一个整体，不可分隔，要么全部成功，要么全部失败
• Consistency：一致性
  事务操作的前后，表中的记录没有变化
• Isolation：隔离性
  事务操作是相互隔离不受影响的
• Durability：持久性
  数据一旦提交，不可改变，永久改变表数据

show  variables like "autocommit";  //查看提交状态
set autocommit=off;  //关闭自动提交
commit;//提交数据
rollback;//数据回滚

一：查看存储引擎信息

登入MySQL服务器，查看当前支持哪些存储引擎。
使用mysql命令连接，以root用户登入：

[root@dbsvr1 ~]# mysql -u root –p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> 

执行SHOW ENGINES\G指令可列表查看，MySQL 5.6可用的存储引擎有9种（除最后的FEDERATED以外，其他8种都支持），其中默认采用的存储引擎为InnoDB：

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT                              //此存储引擎为默认
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO                             //此引擎不被支持
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.01 sec)

二：查看默认存储类型

查看系统变量default_storage_engine 的值，确认默认采用的存储引擎是InnoDB：

mysql> SHOW VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)

三：修改默认存储引擎

在 mysql> 环境中，可以直接通过SET指令更改默认的存储引擎（只在本次连接会话过程中有效，退出重进即失效） 。比如临时修改为MyISAM，可执行下列操作：

mysql> SET default_storage_engine=MyISAM;              //改用MyISAM引擎
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'default_storage_engine';          //确认结果
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.00 sec)

若希望直接修改MySQL服务程序所采用的默认存储引擎，应将相关设置写入配置文件/etc/my.cnf，并重启服务后生效。比如：

[root@dbsvr1 ~]# vim /etc/my.cnf
[mysqld]
.. ..
default_storage_engine=MEMORY                              //改用MEMORY引擎
[root@dbsvr1 ~]# systemctl  restart mysqld.service           //重启服务

重新登入 mysql> 确认修改结果：

[root@dbsvr1 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> SHOW VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MEMORY |                  //默认引擎已修改
+------------------------+--------+
1 row in set (0.00 sec)
mysql> exit
Bye

四：设置数据库服务默认使用的存储引擎

为了避免后续实验障碍，测试完后记得恢复原状——移除默认引擎设置，或者将其修改为InnoDB即可：

[root@dbsvr1 ~]# vim /etc/my.cnf
[mysqld]
.. ..
default_storage_engine=InnoDB
[root@dbsvr1 ~]# systemctl  restart mysqld.service
确认恢复结果（选项 -e 可调用指定的SQL操作后返回Shell命令行）：
[root@dbsvr1 ~]# mysql -u root -p -e "SHOW VARIABLES LIKE 'default_storage_engine';"
Enter password:
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+

数据导入/导出

数据导入

• 基本用法
  LOAD DATA INFILE “目录名/文件名”
  INTO TABLE 表名
  FIELDS TERMINATED BY “分隔符”
  LINES TERMINATED BY “\n”;
• 注意事项

1. 字段分隔符要与文件内的一致
2. 指定导入文件的绝对路径
3. 导入数据的表字段类型要与文件字段匹配
4. 禁用Selinux保护机制

数据导出

• 基本用法
  SQL查询 into outfile “目录名/文件名”
  FIELDS TERMINATED BY “分隔符”
  LINES TERMINATED BY “\n”;
• 注意事项

1. 导出的内容由SQL查询语句决定
2. 导出的是表中的记录，不包括字段名
3. 禁用Selinux保护机制

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

导入后的表结构取决于/etc/passwd配置文件。若一时记不住各字段的含义，也可以查看passwd配置文件的man手册页，找到格式描述相关的说明，比如：

[root@dbsvr1 ~]# man 5 passwd
.. ..
       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库、user表
以数据库用户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 5
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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.00 sec)
mysql> USE userdb;
Database changed

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

mysql> CREATE TABLE user(
    -> 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.70 sec)

确认user表的结构：

mysql> DESC user;
+----------+-------------+------+-----+---------+-------+
| 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.01 sec)

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

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

（3）执行导入操作
将/etc/passwd文件复制到/var/lib/mysql-files/目录下，
读取/var/lib/mysql-files/passwd文件内容，以“:”为分隔，导入到user表中：

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

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

mysql> load data "/var/lib/mysql-files/passwd" into table user2 fields terminated by ":" lines terminated by "\n";

（4）确认导入结果
分别统计user、user2表内的记录个数：

mysql> SELECT COUNT(*) FROM user;
+----------+
| COUNT(*) |
+----------+
|       39 |                          //user表有39条记录
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM user2;
+----------+
| COUNT(*) |
+----------+
|       39 |                                  //user2表也有39条记录
+----------+
1 row in set (0.00 sec)

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

mysql> SELECT username,uid,gid,homedir,shell
    -> FROM user 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  |
| operator |  11 |   0 | /root           | /sbin/nologin  |
+----------+-----+-----+-----------------+----------------+
10 rows in set (0.00 sec)

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

mysql> SELECT username,uid,gid,homedir,shell
    -> FROM user2 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  |
| operator |  11 |   0 | /root           | /sbin/nologin  |
+----------+-----+-----+-----------------+----------------+
10 rows in set (0.00 sec)

注意：
如果load data infile的时候报错，Can't get stat of "路径" (Errcode:13),处理方法是将LOAD DATA INFILE 换成 LOAD DATA LOCAL INFILE

原因是：
出于安全原因，当读取位于服务器中的文本文件时， 文件必须位于数据库目录中，或者是全体可读的。另外，要对服务器文件使用LOAD DATA INFILE，您必须拥有FILE权限。
如果指定了LOCAL，则文件会被客户主机上的客户端读取，并被发送到服务器。文件会被给予一个完整的路径名称，以指定确切的位置。

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

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

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

（2）验证自动编号结果
查看user表的前10条记录，列出序号、用户名、UID、GID、宿主目录：

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

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

以将userdb库user表中UID小于100的前10条记录导出为/myload/user2.txt文件为例。
（1）确认存放导出数据的文件夹

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

（2）修改目录及查看修改结果

[root@dbsvr1 ~]# mkdir  /myload  ;  chown  mysql  /myload 
[root@dbsvr1 ~]# vim  /etc/my.cnf 
[mysqld]
secure_file_priv="/myload"
[root@dbsvr1 ~]# systemctl  restart mysqld 
mysql> show variables like "secure_file_priv";
+------------------+----------+
| Variable_name    | Value       |
+------------------+----------+
| secure_file_priv   | /myload/ |

（3）导出user表中UID小于100的前10条记录
如果以默认的’\n’ 为行分隔，导出操作同样可不指定LINES TERMINATED BY：

mysql> SELECT * FROM userdb.user WHERE uid<100
    -> INTO OUTFILE '/myload/user2.txt'
    -> FIELDS TERMINATED BY ':';
Query OK, 24 rows affected (0.00 sec)

（3）确认导出结果
返回到Shell命令行，查看/myload/user2.txt文件的行数：

[root@dbsvr1 ~]# wc -l /myload/user2.txt 
24 /myload/user2.txt

查看/myload/user2.txt文件的最后10行内容：

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

操作表记录

增加表记录

语法格式

• 格式1：添加1条记录，给所有字段赋值
  insert into 表名 values (字段值列表);

• 格式2：添加N条记录,给所有字段赋值
  insert into 表名
  values
  (字段值列表),
  (字段值列表),
  (字段值类表);

• 格式3：添加1条记录，给指定字段赋值
  insert into 表名(字段名列表) values(字段值列表)

• 格式4：添加N条记录，给指定字段赋值
  insert into 表名(字段值列表)
  values
  (字段值列表),
  (字段值列表),
  (字段值类表);

• 注意事项
  字段值要与字段类型相匹配
  对于字符类型的字段，要用双或者单引号括起来
  依次给所有的字段赋值时，字段名可以省略
  只给一部分字段赋值时，必须明确写出对应的字段名称

查询表记录

语法格式

• 格式1
  SELECT 字段1,… …,字段N FROM 表名;
• 格式2
  SELECT 字段1,… …,字段N FROM 表名 WHERE 条件表达式;
• 注意事项
  使用*可匹配所有字段
  指定表名时，可采用 库名.表名 的形式

更新表记录

• 格式1，更新表内的所有记录
  UPDATE 表名 SET 字段1=字段1的值,字段2=字段2的值,字段N=字段N的值;
• 格式2，只更新符合条件的部分记录
  UPDATE 表名 SET 字段1=字段1的值,字段2=字段2的值,字段N=字段N的值 WHERE 条件表达式;
• 注意事项
  字段值要与字段类型匹配
  对于字符类型的字段，要用双引或单引括起来
  若不使用WHERE限定条件，会更新所有记录
  限定条件时，只更新匹配条件的记录

删除表记录

• 格式1，仅删除符合条件的记录
  DELETE FROM 表名 WHERE 条件表达式;
• 格式2，删除所有的表记录
  DELETE FROM 表名;

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

在userdb库中创建stu_info表：

[root@dbsvr1 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> use userdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE TABLE stu_info(
    -> name varchar(12) NOT NULL,
    -> gender enum('boy','girl') DEFAULT 'boy',
    -> age int(3) NOT NULL
    -> );
Query OK, 0 rows affected (0.23 sec)

删除stu_info表的所有记录：

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

确认删除结果：

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

二：练习表记录的操作

（1）插入记录时，指定记录的每一个字段的值
这种情况下，不需要明确指出字段，但每条记录的值的顺序、类型都必须与表格结构向一致，否则可能无法正确插入记录。
比如，以下操作将向stu_info表插入3条表记录：

mysql> INSERT stu_info VALUES
    -> ('Jim','girl',24),
    -> ('Tom','boy',21),
    -> ('Lily','girl',20);
Query OK, 3 rows affected (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 0

完成插入后确认表记录：

mysql> SELECT * FROM stu_info;
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim  | girl   |  24 |
| Tom  | boy    |  21 |
| Lily | girl   |  20 |
+------+--------+-----+
3 rows in set (0.00 sec)

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

这种情况下，必须指出各项值所对应的字段；而且，未赋值的字段应设置有默认值或者有自增填充属性或者允许为空，否则插入操作将会失败。
比如，向stu_info表插入Jerry的年龄信息，性别为默认的“boy”，自动编号，相关操作如下：

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

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

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

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

mysql> SELECT * FROM stu_info;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jim   | girl   |  24 |
| Tom   | boy    |  21 |
| Lily  | girl   |  20 |
| Jerry | boy    |  27 |
| Mike  | boy    |  21 |
+-------+--------+-----+
5 rows in set (0.00 sec)

（3）更新表记录时，若未限制条件，则适用于所有记录
将stu_info表中所有记录的age设置为10：

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

确认更新结果：

mysql> SELECT * FROM stu_info;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jim   | girl   |  10 |
| Tom   | boy    |  10 |
| Lily  | girl   |  10 |
| Jerry | boy    |  10 |
| Mike  | boy    |  10 |
+-------+--------+-----+
5 rows in set (0.00 sec)

（4）更新表记录时，可以限制条件，只对符合条件的记录有效
将stu_info表中所有性别为“boy”的记录的age设置为20：

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

确认更新结果：

mysql> SELECT * FROM stu_info;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jim   | girl   |  10 |
| Tom   | boy    |  20 |
| Lily  | girl   |  10 |
| Jerry | boy    |  20 |
| Mike  | boy    |  20 |
+-------+--------+-----+
5 rows in set (0.00 sec)

（5）删除表记录时，可以限制条件，只删除符合条件的记录
删除stu_info表中年龄小于18的记录：

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

确认删除结果：

mysql> SELECT * FROM stu_info;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Tom   | boy    |  20 |
| Jerry | boy    |  20 |
| Mike  | boy    |  20 |
+-------+--------+-----+
3 rows in set (0.00 sec)

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

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

确认删除结果：

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

查询及匹配条件

基本匹配条件

（1）数值比较

类型	用途
=	等于
>、>=	大于、大于或等于
<、<=	小于、小于或等于
!=	不等于

（2）字符比较/匹配空/非空
字符比较时，字段类型必须是字符类型

类型	用途
=	相等
!=	不相等
IS NULL	匹配空
IS NOT NULL	非空

例：

mysql> select id,name,shell from user where shell is null;

mysql> select id,name,shell from user where shell is not null;

（3）逻辑匹配

多个判断条件时使用

类型	用途
OR	逻辑或
AND	逻辑与
!	逻辑非
()	提高优先级

（5）范围内匹配/去重显示
匹配范围内的任意一个值即可

类型	用途
in（值列表）	在…里…
not in(值列表)	不在…里…
between数字1and数字2	在…之间…
distinct 字段名	去重显示

例：

select id,name,shell from user where name in ("root","nfsnobody","mysql"); //查找name字段值为括号内要求的值列表
select id,name,shell from user where shell not in ("/bin/bash","/sbin/nologin"); //查找shell字段的值不在括号之列的值列表
select id,name,shell from user where id between 5 and 10;//查找id在5～10之间的结果
select distinct shell from user where uid > 100; //查找uid大于100的结果，并对shell去重显示

高级匹配条件

模糊查询

• 基本用法
  where 字段名 like ‘通配符’
  _匹配单个字符、%匹配0～N个字符
• 示例
  列出name值,以"J"开头或者以"y"结尾的记录

select * from user where name like 'J%' or name like '%y';
select name from user where name like '%m%';
select name from user where name like '---'; //匹配有三个字母的name
select name from user where name like '--%--';

正则表达式

• 基本用法
  where 字段名 regexp ‘正则表达式’
  正则元字符 ^ $ . [] * |
• 示例
  列出name值,以"J"或者"Y"结尾的记录

select * from user where name regexp '^J|Y$';
select name from user where name regexp '^a.*t$'; //查找name以a开头并以t结尾的数据
select name from user where name regexp '[0-9]';  //查找name字段中包含数字的数据
select id,name,shell from user where id regexp '[1-2]'; //查找id中包含数字1或2的

四则运算

• 运算操作
  字段必须是数值类型

类型	用途
+	加法
-	减法
*	乘法
/	除法
%	求模

例:

select name,uid+gid from user;  //将uid与gid求和
select name,uid-gid from user;  //将uid与gid求差
select name,uid+gid he from user; //将uid与gid求和，并显示的标题改为he
update user set uid=uid+1;  //将uid加1显示
update user set uid=uid+1 where shell="/bin/bash"; //将shell为/bin/bash的数据的uid加1显示

聚集函数
MySQL内置数据统计函数

• avg(字段名) //统计字段平均值
• sum(字段名) //统计字段之和
• min(字段名) //统计字段最小值
• max(字段名) //统计字段最大值
• count(字段名) //统计字段值个数

例:

select avg(uid) from user where shell="/bin/bash";  //对符合条件的uid求平均值
select sum(id) from user; //求id的总和
select min(uid) from user where shell="/sbin/nologin"; //对符合条件的数据，求uid最小的
select max(uid) from user;  //求最大uid值
select count(*) from user;  //统计有多少条数据
select count(uid) from user; //统计uid字段的个数

查询结果

• 基本用法
  SQL查询 order by 字段名 [asc|desc]; //通常是数值类型的字段

例:

select name,gid from user order by gid desc limit 10;  //对gid降序排列，显示前十个
select name,gid from user order by gid limit 10;  //默认是升序排列（asc）,显示前10个
select name,gid from user where id<=20 order by gid desc; //查找id<=20的数据，然后根据gid进行降序排列

查询结果分组

• 基本用法
  SQL查询 group by 字段名; //通常是字符类型的字段

例:

select shell from user where id<=20 group by shell;  //等同于select distinct shell from user where id<=20;

查询结果过滤

• 基本用法
  SQL查询 having 条件表达式; //相比于and的用法，节省资源
  SQL查询 where 条件 having 条件表达式;
  SQL查询 group by 字段名 having 条件表达式;

例：

select id,name,shell from user where id<=20 having name like "a%";  //查找id<=20的结果中name字段以a开头的数据
select shell from user where id<=200 group by shell having shell="/bin/bash";

注：使用了group by分组的时候，前面的select字段只能显示分组字段，不能写其他的，后面的having条件字段也只能是分组字段

限制查询结果显示行数

• 基本用法
  SQL查询 limit N; //显示查询结果前N条记录
  SQL查询 limit N,M; //显示指定范围内的查询记录N+1~N+1+M行记录
  SQL查询 where 条件查询 limit 3; //显示查询结果前3条记录
  SQL查询 where 条件查询 LIMIT 3,3; //从第4条开始，共显示3条

例:

select id,name,shell from user limit 3,10; //从第4条开始，显示10行

select id,name,shell from user where uid <100 limit 3,3; //显示uid<100的结果内，从4开始往后三行显示

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

（1）在userdb库中创建stu_info表
以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 5
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>

打开test库：

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

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

mysql> CREATE TABLE stu_info(
    -> name varchar(12) NOT NULL,
    -> gender enum('boy','girl') DEFAULT 'boy',
    -> age int(3) NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)

确认表结构：

mysql> DESC stu_info;
+--------+--------------------+------+-----+---------+-------+
| Field  | Type               | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| name   | varchar(12)        | NO   |     | NULL    |       |
| gender | enum('boy','girl') | YES  |     | boy     |       |
| age    | int(3)             | NO   |     | NULL    |       |
+--------+--------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

（2）准备测试表格
向建立的stu_info表插入几条测试记录

mysql> INSERT INTO stu_info VALUES
    -> ('Jim','girl',24),
    -> ('Tom','boy',21),
    -> ('Lily','girl',20),
    -> ('Jerry','boy',27),
    -> ('Mike','boy',21)
    -> ;
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

确认stu_info表的所有记录内容：

mysql> SELECT * FROM stu_info;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jim   | girl   |  24 |
| Tom   | boy    |  21 |
| Lily  | girl   |  20 |
| Jerry | boy    |  27 |
| Mike  | boy    |  21 |
+-------+--------+-----+
5 rows in set (0.00 sec)

二：常见SQL查询及条件设置

（1）常用的表记录统计函数
查询stu_info表一共有多少条记录（本例中为5条）：

mysql> SELECT count(*) FROM stu_info;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

计算stu_info表中各学员的平均年龄、最大年龄、最小年龄：

mysql> SELECT avg(age),max(age),min(age) FROM stu_info;
+----------+----------+----------+
| avg(age) | max(age) | min(age) |
+----------+----------+----------+
|  22.6000 |       27 |       20 |
+----------+----------+----------+
1 row in set (0.00 sec)

计算stu_info表中男学员的个数：

mysql> SELECT count(gender) FROM stu_info WHERE gender='boy';
+---------------+
| count(gender) |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

（2）字段值的数值比较
列出stu_info表中年龄为21岁的学员记录：

mysql> SELECT * FROM stu_info WHERE age=21;
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Tom  | boy    |  21 |
| Mike | boy    |  21 |
+------+--------+-----+
2 rows in set (0.00 sec)

列出stu_info表中年龄超过21岁的学员记录：

mysql> SELECT * FROM stu_info WHERE age>21;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jim   | girl   |  24 |
| Jerry | boy    |  27 |
+-------+--------+-----+
2 rows in set (0.00 sec)

列出stu_info表中年龄大于或等于21岁的学员记录：

mysql> SELECT * FROM stu_info WHERE age>=21;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jim   | girl   |  24 |
| Tom   | boy    |  21 |
| Jerry | boy    |  27 |
| Mike  | boy    |  21 |
+-------+--------+-----+
4 rows in set (0.00 sec)

列出stu_info表中年龄在20岁和24岁之间的学员记录：

mysql> SELECT * FROM stu_info WHERE age BETWEEN 20 and 24;
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim  | girl   |  24 |
| Tom  | boy    |  21 |
| Lily | girl   |  20 |
| Mike | boy    |  21 |
+------+--------+-----+
4 rows in set (0.00 sec)

（3）多个条件的组合
列出stu_info表中年龄小于23岁的女学员记录：

mysql> SELECT * FROM stu_info WHERE age < 23 AND gender='girl';
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Lily | girl   |  20 |
+------+--------+-----+
1 row in set (0.00 sec)

列出stu_info表中年龄小于23岁的学员，或者女学员的记录：

mysql> SELECT * FROM stu_info WHERE age < 23 OR gender='girl';
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim  | girl   |  24 |
| Tom  | boy    |  21 |
| Lily | girl   |  20 |
| Mike | boy    |  21 |
+------+--------+-----+
4 rows in set (0.00 sec)

如果某个记录的姓名属于指定范围内的一个，则将其列出：

mysql> SELECT * FROM stu_info WHERE name IN
    -> ('Jim','Tom','Mickey','Minnie');
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim  | girl   |  24 |
| Tom  | boy    |  21 |
+------+--------+-----+
2 rows in set (0.00 sec)

（4）使用SELECT做数学计算
计算1234与5678的和：

mysql> SELECT 1234+5678;
+-----------+
| 1234+5678 |
+-----------+
|      6912 |
+-----------+
1 row in set (0.00 sec)

计算1234与5678的乘积：

mysql> SELECT 1234*5678;
+-----------+
| 1234*5678 |
+-----------+
|   7006652 |
+-----------+
1 row in set (0.00 sec)

计算1.23456789除以3的结果：

mysql> SELECT 1.23456789/3;
+----------------+
| 1.23456789/3   |
+----------------+
| 0.411522630000 |
+----------------+
1 row in set (0.00 sec)

输出stu_info表各学员的姓名、15年后的年龄：

mysql> SELECT name,age+15 FROM stu_info;
+-------+--------+
| name  | age+15 |
+-------+--------+
| Jim   |     39 |
| Tom   |     36 |
| Lily  |     35 |
| Jerry |     42 |
| Mike  |     36 |
+-------+--------+
5 rows in set (0.00 sec)

（5）使用模糊查询，LIKE引领
以下划线 _ 匹配单个字符，% 可匹配任意多个字符。
列出stu_info表中姓名以“J”开头的学员记录：

mysql> SELECT * FROM stu_info WHERE name LIKE 'J%';
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jim   | girl   |  24 |
| Jerry | boy    |  27 |
+-------+--------+-----+
2 rows in set (0.00 sec)

列出stu_info表中姓名以“J”开头且只有3个字母的学员记录：

mysql> SELECT * FROM stu_info WHERE name LIKE 'J__';
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim  | girl   |  24 |
+------+--------+-----+
1 row in set (0.00 sec)

（6）使用正则表达式，REGEXP引领
列出stu_info表中姓名以“J”开头且以“y”结尾的学员记录：

mysql> SELECT * FROM stu_info WHERE name REGEXP '^J.*y$';
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jerry | boy    |  27 |
+-------+--------+-----+
1 row in set (0.00 sec)

效果等同于：

mysql> SELECT * FROM stu_info WHERE name Like 'J%y';
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jerry | boy    |  27 |
+-------+--------+-----+
1 row in set (0.00 sec)

列出stu_info表中姓名以“J”开头或者以“y”结尾的学员记录：

mysql> SELECT * FROM stu_info WHERE name REGEXP '^J|y$';
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jim   | girl   |  24 |
| Lily  | girl   |  20 |
| Jerry | boy    |  27 |
+-------+--------+-----+
3 rows in set (0.00 sec)

效果等同于：

mysql> SELECT * FROM stu_info WHERE name Like 'J%' OR name Like '%y';
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jim   | girl   |  24 |
| Lily  | girl   |  20 |
| Jerry | boy    |  27 |
+-------+--------+-----+
3 rows in set (0.00 sec)

（7）按指定的字段排序，ORDER BY
列出stu_info表的所有记录，按年龄排序：

mysql> SELECT * FROM stu_info GROUP BY age;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Lily  | girl   |  20 |
| Tom   | boy    |  21 |
| Jim   | girl   |  24 |
| Jerry | boy    |  27 |
+-------+--------+-----+
4 rows in set (0.00 sec)

因默认为升序（Ascend）排列，所以上述操作等效于：

mysql> SELECT * FROM stu_info GROUP BY age ASC;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Lily  | girl   |  20 |
| Tom   | boy    |  21 |
| Jim   | girl   |  24 |
| Jerry | boy    |  27 |
+-------+--------+-----+
4 rows in set (0.00 sec)

若要按降序（Descend）排列，则将ASC改为DESC即可：

mysql> SELECT * FROM stu_info GROUP BY age DESC;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jerry | boy    |  27 |
| Jim   | girl   |  24 |
| Tom   | boy    |  21 |
| Lily  | girl   |  20 |
+-------+--------+-----+
4 rows in set (0.00 sec)

（8）限制查询结果的输出条数，LIMIT
查询stu_info表的所有记录，只列出前3条：

mysql> SELECT * FROM stu_info LIMIT 3;
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim  | girl   |  24 |
| Tom  | boy    |  21 |
| Lily | girl   |  20 |
+------+--------+-----+
3 rows in set (0.00 sec)

列出stu_info表中年龄最大的3条学员记录：

mysql> SELECT * FROM stu_info GROUP BY age DESC LIMIT 3;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jerry | boy    |  27 |
| Jim   | girl   |  24 |
| Tom   | boy    |  21 |
+-------+--------+-----+
3 rows in set (0.00 sec)

（9）分组查询结果，GROUP BY
针对stu_info表，按性别分组，分别统计出男、女学员的人数：

mysql> SELECT gender,count(gender) FROM stu_info GROUP BY gender;
+--------+---------------+
| gender | count(gender) |
+--------+---------------+
| boy    |             3 |
| girl   |             2 |
+--------+---------------+
2 rows in set (0.00 sec)

列出查询字段时，可以通过AS关键字来指定显示别名，比如上述操作可改为：

mysql> SELECT gender AS '性别',count(gender) AS '人数'
    -> FROM stu_info GROUP BY gender;
+--------+--------+
| 性别   | 人数   |
+--------+--------+
| boy    |      3 |
| girl   |      2 |
+--------+--------+
2 rows in set (0.00 sec)