《MySQL 必知必会》实验

MySQL 环境搭建

在 Ubuntu 虚拟机上使用 Docker 容器。docker-compose up -d 启动 MySQL 容器，设置密码和数据库并挂载文件夹，目前最新版本是 MySQL Server 8.0.22 ，挂载文件夹时为了之后使用 SQL 脚本。

version: "3"
services:
  db:
    image: mysql:latest
    container_name: mysql
    restart: always
    environment: 
      - MYSQL_ROOT_PASSWORD=password
      - MYSQL_DATABASE=test
    ports:
      - 3306:3306
    volumes:
      - ~/test/MySQL Crash Course/mysql_scripts:/home # 主机:容器

使用 root 用户登录 MySQL 容器，密码为 password ：

# 第一个 mysql 是容器名称（container_name）
docker exec -it mysql mysql -u root -p

使用 test 数据库，从 .sql 文件读取 SQL 语句并执行：

• Executing SQL Statements from a Text File

use test;
source /home/create.sql
source /home/populate.sql

查看一下执行结果：

-- 列出当前数据库（test）中所有的表
show tables;

-- 列出 customers 表的信息
describe customers;

-- 列出 customers 表中的前 3 行数据
select * from customers limit 3;

注意事项：

• MySQL 清屏：CTRL+l
• MySQL 必须使用 ; 来结束 SQL 语句
• SQL 语句不区分大小写
• SQL 语句可以分为多行

HELP

帮助语句，用于返回 MySQL 参考手册中的信息，例如查找 SHOW 语句用法（不区分大小写）：

mysql> help show;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW SLAVE HOSTS
SHOW SLAVE STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where: {
    LIKE 'pattern'
  | WHERE expr
}

If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL % and _ wildcard
characters. The pattern is useful for restricting statement output to
matching values.

Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
https://dev.mysql.com/doc/refman/8.0/en/extended-show.html.

URL: https://dev.mysql.com/doc/refman/8.0/en/show.html

USE

使用 USE 语句打开特定名称的数据库，作为后续语句的默认（当前）数据库：

mysql> use test;
Database changed
mysql> 

SHOW

SHOW DATABASES

使用 SHOW DATABASES 语句列出 MySQL 服务器主机上的数据库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

SHOW TABLES

使用 SHOW TABLES 语句列出给定数据库中的非临时表（non-TEMPORARY tables），默认列出当前数据库中的表：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| customers      |
| orderitems     |
| orders         |
| productnotes   |
| products       |
| vendors        |
+----------------+
6 rows in set (0.00 sec)

SHOW COLUMNS

使用 SHOW COLUMNS 列出给定表中有关列的信息，也适用于视图。对每个字段返回一行，每行显示字段名、数据类型、是否允许为 NULL、键信息、默认值以及其他信息。例如，列出 customers 表的信息：

mysql> show columns from customers;
+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int       | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50)  | NO   |     | NULL    |                |
| cust_address | char(50)  | YES  |     | NULL    |                |
| cust_city    | char(50)  | YES  |     | NULL    |                |
| cust_state   | char(5)   | YES  |     | NULL    |                |
| cust_zip     | char(10)  | YES  |     | NULL    |                |
| cust_country | char(50)  | YES  |     | NULL    |                |
| cust_contact | char(50)  | YES  |     | NULL    |                |
| cust_email   | char(255) | YES  |     | NULL    |                |
+--------------+-----------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

也可以使用 DESCRIBE 或者 EXPLAIN 语句查看表信息。查看 customers 表的信息，返回的结果和使用 SHOW COLUMNS 相同：

mysql> describe customers;
+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int       | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50)  | NO   |     | NULL    |                |
| cust_address | char(50)  | YES  |     | NULL    |                |
| cust_city    | char(50)  | YES  |     | NULL    |                |
| cust_state   | char(5)   | YES  |     | NULL    |                |
| cust_zip     | char(10)  | YES  |     | NULL    |                |
| cust_country | char(50)  | YES  |     | NULL    |                |
| cust_contact | char(50)  | YES  |     | NULL    |                |
| cust_email   | char(255) | YES  |     | NULL    |                |
+--------------+-----------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql> explain customers;
+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int       | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50)  | NO   |     | NULL    |                |
| cust_address | char(50)  | YES  |     | NULL    |                |
| cust_city    | char(50)  | YES  |     | NULL    |                |
| cust_state   | char(5)   | YES  |     | NULL    |                |
| cust_zip     | char(10)  | YES  |     | NULL    |                |
| cust_country | char(50)  | YES  |     | NULL    |                |
| cust_contact | char(50)  | YES  |     | NULL    |                |
| cust_email   | char(255) | YES  |     | NULL    |                |
+--------------+-----------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

SHOW STATUS

SHOW STATUS 用于显示服务器状态信息，使用 LIKE 查找模式匹配的变量，例如 LIKE 'ssl%' 表示匹配以 ssl 开头的变量名称，不区分大小写：

mysql> show status like 'ssl%';
+--------------------------------+--------------------------+
| Variable_name                  | Value                    |
+--------------------------------+--------------------------+
| Ssl_accept_renegotiates        | 0                        |
| Ssl_accepts                    | 0                        |
| Ssl_callback_cache_hits        | 0                        |
| Ssl_cipher                     |                          |
| Ssl_cipher_list                |                          |
| Ssl_client_connects            | 0                        |
| Ssl_connect_renegotiates       | 0                        |
| Ssl_ctx_verify_depth           | 18446744073709551615     |
| Ssl_ctx_verify_mode            | 5                        |
| Ssl_default_timeout            | 0                        |
| Ssl_finished_accepts           | 0                        |
| Ssl_finished_connects          | 0                        |
| Ssl_server_not_after           | Nov 19 07:19:37 2030 GMT |
| Ssl_server_not_before          | Nov 21 07:19:37 2020 GMT |
| Ssl_session_cache_hits         | 0                        |
| Ssl_session_cache_misses       | 0                        |
| Ssl_session_cache_mode         | SERVER                   |
| Ssl_session_cache_overflows    | 0                        |
| Ssl_session_cache_size         | 128                      |
| Ssl_session_cache_timeouts     | 0                        |
| Ssl_sessions_reused            | 0                        |
| Ssl_used_session_cache_entries | 0                        |
| Ssl_verify_depth               | 0                        |
| Ssl_verify_mode                | 0                        |
| Ssl_version                    |                          |
+--------------------------------+--------------------------+
25 rows in set (0.00 sec)

SHOW CREATE DATABASE

SHOW CREATE DATABASE test 显示创建 test 数据库时使用的 CREATE DATABASE 语句，该数据库在容器启动后自动创建。

mysql> show create database test;
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

SHOW CREATE TABLE

SHOW CREATE TABLE customers 显示创建 customers 表时使用的 CREATE TABLE 语句，该语句也适用于视图。

mysql> show create table customers;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customers | CREATE TABLE `customers` (
  `cust_id` int NOT NULL AUTO_INCREMENT,
  `cust_name` char(50) NOT NULL,
  `cust_address` char(50) DEFAULT NULL,
  `cust_city` char(50) DEFAULT NULL,
  `cust_state` char(5) DEFAULT NULL,
  `cust_zip` char(10) DEFAULT NULL,
  `cust_country` char(50) DEFAULT NULL,
  `cust_contact` char(50) DEFAULT NULL,
  `cust_email` char(255) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

SHOW GRANTS

以 GRANT 语句的形式显示用户的权限信息和角色信息，不加任何参数默认显示当前用户的信息。

mysql> show grants;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION                                                                                                                                               |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

SHOW ERRORS

SHOW ERRORS 是一个诊断语句，类似于 SHOW WARNINGS，但是只显示错误信息。

mysql> show errors;
Empty set (0.00 sec)

SHOW WARNINGS

SHOW WARNINGS 也是一个诊断语句，显示在当前会话中执行语句所产生的条件（错误、警告和注释）的信息。比 SHOW ERRORS 显示更多的信息，二者显示的信息是包含关系。

mysql> show warnings;
Empty set (0.00 sec)

SELECT

SELECT 语句用于从一个或多个表中检索行，可以包括联合语句（UNION）和子查询。

1. 检索单独的列

给出具体的表名和列名，例如，从 products 表中检索 prod_name 列：

mysql> select prod_name from products;
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Detonator      |
| Bird seed      |
| Carrots        |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.00 sec)

2. 检索多个列

指定表名，给出多个列名，用 , 分隔。例如，从 products 表中检索 prod_id,prod_name,prod_price 列：

mysql> select prod_id, prod_name, prod_price from products;
+---------+----------------+------------+
| prod_id | prod_name      | prod_price |
+---------+----------------+------------+
| ANV01   | .5 ton anvil   |       5.99 |
| ANV02   | 1 ton anvil    |       9.99 |
| ANV03   | 2 ton anvil    |      14.99 |
| DTNTR   | Detonator      |      13.00 |
| FB      | Bird seed      |      10.00 |
| FC      | Carrots        |       2.50 |
| FU1     | Fuses          |       3.42 |
| JP1000  | JetPack 1000   |      35.00 |
| JP2000  | JetPack 2000   |      55.00 |
| OL1     | Oil can        |       8.99 |
| SAFE    | Safe           |      50.00 |
| SLING   | Sling          |       4.49 |
| TNT1    | TNT (1 stick)  |       2.50 |
| TNT2    | TNT (5 sticks) |      10.00 |
+---------+----------------+------------+
14 rows in set (0.00 sec)

3. 检索所有列

使用 * 通配符匹配所有的列，给出表名即可。如果数据表有很多列，或者数据量很多，不推荐使用通配符；不过用通配符可以检索出名字未知的列。例如，检索 products 表中所有的列：

mysql> select * from products;
+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                                                      |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| ANV01   |    1001 | .5 ton anvil   |       5.99 | .5 ton anvil, black, complete with handy hook                  |
| ANV02   |    1001 | 1 ton anvil    |       9.99 | 1 ton anvil, black, complete with handy hook and carrying case |
| ANV03   |    1001 | 2 ton anvil    |      14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| DTNTR   |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included                |
| FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runners)                          |
| FC      |    1003 | Carrots        |       2.50 | Carrots (rabbit hunting season only)                           |
| FU1     |    1002 | Fuses          |       3.42 | 1 dozen, extra long                                            |
| JP1000  |    1005 | JetPack 1000   |      35.00 | JetPack 1000, intended for single use                          |
| JP2000  |    1005 | JetPack 2000   |      55.00 | JetPack 2000, multi-use                                        |
| OL1     |    1002 | Oil can        |       8.99 | Oil can, red                                                   |
| SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                                     |
| SLING   |    1003 | Sling          |       4.49 | Sling, one size fits all                                       |
| TNT1    |    1003 | TNT (1 stick)  |       2.50 | TNT, red, single stick                                         |
| TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                                    |
+---------+---------+----------------+------------+----------------------------------------------------------------+
14 rows in set (0.00 sec)

4. 检索不同的行

当检索出的列中有重复数据时，可以使用 DISTINCT 关键字在行上进行过滤。例如，检索 products 表的 vend_id 列，包含许多重复数据：

mysql> select vend_id from products;
+---------+
| vend_id |
+---------+
|    1001 |
|    1001 |
|    1001 |
|    1002 |
|    1002 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1005 |
|    1005 |
+---------+
14 rows in set (0.01 sec)

DISTINCT 关键字必须放在所有的列名之前，并且作用于所有的列。例如，检索 products 表，使用 DISTINCT 过滤重复列：

mysql> select distinct vend_id from products;
+---------+
| vend_id |
+---------+
|    1001 |
|    1002 |
|    1003 |
|    1005 |
+---------+
4 rows in set (0.00 sec)

5. 限制数量

在语句的末尾使用 LIMIT 关键字限制返回的行数。例如，从 products 表中检索 prod_name 列，限制返回 5 行数据：

mysql> select prod_name from products limit 5;
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
| Detonator    |
| Bird seed    |
+--------------+
5 rows in set (0.00 sec)

LIMIT 5,5 表示从第 5 行开始返回 5 行，如果行数不够则有多少返回多少。注意，MySQL 中的行从 0 开始计数。

• 第一个数字表示从第几行开始
• 第二个数字表示返回几行

例如，从 products 表中检索 prod_name 列，从第 5 行开始返回 5 行数据：

mysql> select prod_name from products limit 5,5;
+--------------+
| prod_name    |
+--------------+
| Carrots      |
| Fuses        |
| JetPack 1000 |
| JetPack 2000 |
| Oil can      |
+--------------+
5 rows in set (0.00 sec)

使用 OFFSET 关键字可以实现同样的效果，语义更加直观，表示从第 5 行（OFFSET）开始返回 5 行数据（LIMIT）。同样的语句可以改写为如下形式：

mysql> select prod_name from products limit 5 offset 5;
+--------------+
| prod_name    |
+--------------+
| Carrots      |
| Fuses        |
| JetPack 1000 |
| JetPack 2000 |
| Oil can      |
+--------------+
5 rows in set (0.00 sec)

6. 完全限定的表名

完全限定的表名是指同时使用数据库名和表名，完全限定的列名是指同时使用表名和列名，products.prod_name 表示指定 products 表中的 prod_name 列，当不的同表有相同的列名时，可以用来进行区分。

mysql> select products.prod_name from products;
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Detonator      |
| Bird seed      |
| Carrots        |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.00 sec)

WHERE

WHERE 子句用于指定检索的表。子句（Clause）通常由一个关键字和所提供的数据组成。

只检索所需数据要指定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）。

MySQL 支持很多操作符（Operators），其中有不少可以用在 WHERE 子句中。

比较操作符

以下为 MySQL 支持的比较操作符和比较函数，MySQL 进行匹配时默认不区分大小写：

Name	Description
>	Greater than operator
>=	Greater than or equal operator
<	Less than operator
<>, !=	Not equal operator
<=	Less than or equal operator
<=>	NULL-safe equal to operator
=	Equal operator
BETWEEN ... AND ...	Whether a value is within a range of values
COALESCE()	Return the first non-NULL argument
GREATEST()	Return the largest argument
IN()	Whether a value is within a set of values
INTERVAL()	Return the index of the argument that is less than the first argument
IS	Test a value against a boolean
IS NOT	Test a value against a boolean
IS NOT NULL	NOT NULL value test
IS NULL	NULL value test
ISNULL()	Test whether the argument is NULL
LEAST()	Return the smallest argument
LIKE	Simple pattern matching
NOT BETWEEN ... AND ...	Whether a value is not within a range of values
NOT IN()	Whether a value is not within a set of values
NOT LIKE	Negation of simple pattern matching
STRCMP()	Compare two strings

1. 检查单个值

使用 = 检索 prod_name 列中值为 fuses 的行：

• 使用单引号 ' 限定字符串

mysql> select prod_name, prod_price
    -> from products
    -> where prod_name = 'fuses';
+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| Fuses     |       3.42 |
+-----------+------------+
1 row in set (0.00 sec)

使用 < 检索 prod_price 列中值小于 10 的行：

mysql> select prod_name, prod_price
    -> from products
    -> where prod_price < 10;
+---------------+------------+
| prod_name     | prod_price |
+---------------+------------+
| .5 ton anvil  |       5.99 |
| 1 ton anvil   |       9.99 |
| Carrots       |       2.50 |
| Fuses         |       3.42 |
| Oil can       |       8.99 |
| Sling         |       4.49 |
| TNT (1 stick) |       2.50 |
+---------------+------------+
7 rows in set (0.00 sec)

2. 不匹配检查

使用 <> 检索 vend_id 列中值不为 1003 的行：

mysql> select vend_id, prod_name
    -> from products
    -> where vend_id <> 1003;
+---------+--------------+
| vend_id | prod_name    |
+---------+--------------+
|    1001 | .5 ton anvil |
|    1001 | 1 ton anvil  |
|    1001 | 2 ton anvil  |
|    1002 | Fuses        |
|    1002 | Oil can      |
|    1005 | JetPack 1000 |
|    1005 | JetPack 2000 |
+---------+--------------+
7 rows in set (0.00 sec)

3. 范围值检查

使用 between ... and ... 检索范围内的值，包括指定的开始值和终止值：

mysql> select prod_name, prod_price
    -> from products
    -> where prod_price between 5 and 10;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| .5 ton anvil   |       5.99 |
| 1 ton anvil    |       9.99 |
| Bird seed      |      10.00 |
| Oil can        |       8.99 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
5 rows in set (0.00 sec)

4. 空值检查

当一个列不包含值时，称其包含空值 NULL ，在使用匹配和不匹配过滤数据时，具有 NULL 值的行将被忽略，因此需要验证返回的数据中确实给出了被过滤列具有 NULL 值的行。

使用 IS NULL 操作符检索指定列中值为 NULL 的行。例如，从 products 表中检索 prod_name ，由于列 prod_price 没有空值，返回为空集。

mysql> select prod_name
    -> from products
    -> where prod_price is null;
Empty set (0.00 sec)

检索 customers 表的 cust_id 列，返回 cust_email 为空值的列。

mysql> select cust_id
    -> from customers
    -> where cust_email is null;
+---------+
| cust_id |
+---------+
|   10002 |
|   10005 |
+---------+
2 rows in set (0.00 sec)

5. IN

IN 用来指定条件范围，其中每个条件的关系是 or ，因此满足范围中任意条件即可。下列语句检索 vender_id 为 1002 或 1003 的数据：

• IN 通常比 OR 的运算速度更快
• IN 可以包含其他 SELECT 语句

mysql> select prod_name, prod_price
    -> from products
    -> where vend_id in (1002, 1003)
    -> order by prod_name;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Bird seed      |      10.00 |
| Carrots        |       2.50 |
| Detonator      |      13.00 |
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Safe           |      50.00 |
| Sling          |       4.49 |
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
9 rows in set (0.00 sec)

6. NOT

NOT 否定之后的任何条件，例如，检索 vender_id 为 1002 或 1003 之外的数据：

• NOT 可以对 IN 、BETWEEN、EXISTS 语句取反

mysql> select prod_name, prod_price
    -> from products
    -> where vend_id not in (1002, 1003)
    -> order by prod_name;
+--------------+------------+
| prod_name    | prod_price |
+--------------+------------+
| .5 ton anvil |       5.99 |
| 1 ton anvil  |       9.99 |
| 2 ton anvil  |      14.99 |
| JetPack 1000 |      35.00 |
| JetPack 2000 |      55.00 |
+--------------+------------+
5 rows in set (0.00 sec)

7. Like

利用通配符可创建比较特定数据的搜索模式

• 通配符（wildcard）：用来匹配值的一部分的特殊字符
• 搜索模式（search pattern）：由字面值、通配符或两者组合构成的搜索条件

为了在搜索子句中使用通配符，必须使用 LIKE 操作符。

通配符 % 表示任何字符出现任意次数，例如，检索 prod_name 列中值以 jet 开头的行：

• % 无法匹配 NULL

mysql> select prod_id, prod_name
    -> from products
    -> where prod_name like 'jet%';
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| JP1000  | JetPack 1000 |
| JP2000  | JetPack 2000 |
+---------+--------------+
2 rows in set (0.00 sec)

通配符可以在搜索模式中任意位置使用，并且可以使用多个通配符。例如，检索 prod_name 列中值包含 anvil 的行， anvil 可以出现在任意位置：

mysql> select prod_id, prod_name
    -> from products
    -> where prod_name like '%anvil%';
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| ANV01   | .5 ton anvil |
| ANV02   | 1 ton anvil  |
| ANV03   | 2 ton anvil  |
+---------+--------------+
3 rows in set (0.00 sec)

通配符 _ 表示匹配任意单个字符，例如，_ ton anvil 匹配任意单个字符开头的，后接 ton anvil ：

mysql> select prod_id, prod_name
    -> from products
    -> where prod_name like '_ ton anvil';
+---------+-------------+
| prod_id | prod_name   |
+---------+-------------+
| ANV02   | 1 ton anvil |
| ANV03   | 2 ton anvil |
+---------+-------------+
2 rows in set (0.00 sec)

逻辑操作符

MySQL 中的逻辑操作符有四个：与、或、非、异或，通常用于联结或改变 WHERE 子句。

Name	Description
AND, &&	Logical AND
NOT, !	Negates value
OR, ||	Logical OR
XOR	Logical XOR

1. AND

为 WHERE 子句附加条件，可以使用多个 and 附加条件，检索返回满足所有条件的行。例如，检索 products 表中 vender_id 为 1003 ，prod_price 小于等于 10 的数据：

mysql> select prod_id, prod_price, prod_name
    -> from products
    -> where vend_id = 1003 and prod_price <= 10;
+---------+------------+----------------+
| prod_id | prod_price | prod_name      |
+---------+------------+----------------+
| FB      |      10.00 | Bird seed      |
| FC      |       2.50 | Carrots        |
| SLING   |       4.49 | Sling          |
| TNT1    |       2.50 | TNT (1 stick)  |
| TNT2    |      10.00 | TNT (5 sticks) |
+---------+------------+----------------+
5 rows in set (0.00 sec)

2. OR

返回满足任意条件的行，可以使用多个 or 附加条件，检索返回满足任意一个条件的行。例如，检索 products 表中 vender_id 为 1003 或 1002 的数据：

mysql> select prod_id, prod_price, prod_name
    -> from products
    -> where vend_id = 1002 or vend_id = 1003;
+---------+------------+----------------+
| prod_id | prod_price | prod_name      |
+---------+------------+----------------+
| FU1     |       3.42 | Fuses          |
| OL1     |       8.99 | Oil can        |
| DTNTR   |      13.00 | Detonator      |
| FB      |      10.00 | Bird seed      |
| FC      |       2.50 | Carrots        |
| SAFE    |      50.00 | Safe           |
| SLING   |       4.49 | Sling          |
| TNT1    |       2.50 | TNT (1 stick)  |
| TNT2    |      10.00 | TNT (5 sticks) |
+---------+------------+----------------+
9 rows in set (0.00 sec)

3. 优先级

where 子句中可以使用任意多个 and 和 or ，而 and 运算符的优先级大于 or 运算符，条件多的时候使用括号（(、)）进行分组。例如，检索 vender_id 为 1002 或 1003 的数据，同时满足 proc_price 大于等于 10 ：

mysql> select prod_id, prod_price, prod_name
    -> from products
    -> where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;
+---------+------------+----------------+
| prod_id | prod_price | prod_name      |
+---------+------------+----------------+
| DTNTR   |      13.00 | Detonator      |
| FB      |      10.00 | Bird seed      |
| SAFE    |      50.00 | Safe           |
| TNT2    |      10.00 | TNT (5 sticks) |
+---------+------------+----------------+
4 rows in set (0.00 sec)

ORDER BY

ORDER BY 可以根据一个或多个列对检索返回的结果进行排序。例如，从 products 表中检索 prod_name 列，按照 prod_name 列进行排序：

• 可以指定没有被检索的列作为排序依据

mysql> select prod_name from products order by prod_name;
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Bird seed      |
| Carrots        |
| Detonator      |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.00 sec)

按多个列排序只需按顺序给出多个列名，ORDER BY 会按照指定的列的顺序比较，当前比较的列相同时才会去比较下一个列。例如，以下语句先按价格排序，然后再按名称排序：

mysql> select prod_id,prod_price,prod_name from products order by prod_price,prod_name;
+---------+------------+----------------+
| prod_id | prod_price | prod_name      |
+---------+------------+----------------+
| FC      |       2.50 | Carrots        |
| TNT1    |       2.50 | TNT (1 stick)  |
| FU1     |       3.42 | Fuses          |
| SLING   |       4.49 | Sling          |
| ANV01   |       5.99 | .5 ton anvil   |
| OL1     |       8.99 | Oil can        |
| ANV02   |       9.99 | 1 ton anvil    |
| FB      |      10.00 | Bird seed      |
| TNT2    |      10.00 | TNT (5 sticks) |
| DTNTR   |      13.00 | Detonator      |
| ANV03   |      14.99 | 2 ton anvil    |
| JP1000  |      35.00 | JetPack 1000   |
| SAFE    |      50.00 | Safe           |
| JP2000  |      55.00 | JetPack 2000   |
+---------+------------+----------------+
14 rows in set (0.00 sec)

默认的排序顺序是升序（ASC 关键字），使用 DESC 关键字指定降序排列。

mysql> select prod_name from products order by prod_name desc;
+----------------+
| prod_name      |
+----------------+
| TNT (5 sticks) |
| TNT (1 stick)  |
| Sling          |
| Safe           |
| Oil can        |
| JetPack 2000   |
| JetPack 1000   |
| Fuses          |
| Detonator      |
| Carrots        |
| Bird seed      |
| 2 ton anvil    |
| 1 ton anvil    |
| .5 ton anvil   |
+----------------+
14 rows in set (0.00 sec)

DESC 关键字只应用位于其前面的列，当指定多个列排序时，可以用 DESC 指定需要降序排列的列，其余列默认升序排列。例如，以下语句按价格降序，名称升序排列：

mysql> select prod_id,prod_price,prod_name from products order by prod_price desc,prod_name;
+---------+------------+----------------+
| prod_id | prod_price | prod_name      |
+---------+------------+----------------+
| JP2000  |      55.00 | JetPack 2000   |
| SAFE    |      50.00 | Safe           |
| JP1000  |      35.00 | JetPack 1000   |
| ANV03   |      14.99 | 2 ton anvil    |
| DTNTR   |      13.00 | Detonator      |
| FB      |      10.00 | Bird seed      |
| TNT2    |      10.00 | TNT (5 sticks) |
| ANV02   |       9.99 | 1 ton anvil    |
| OL1     |       8.99 | Oil can        |
| ANV01   |       5.99 | .5 ton anvil   |
| SLING   |       4.49 | Sling          |
| FU1     |       3.42 | Fuses          |
| FC      |       2.50 | Carrots        |
| TNT1    |       2.50 | TNT (1 stick)  |
+---------+------------+----------------+
14 rows in set (0.00 sec)

ORDER BY 只能位于 FROM 子句之后，而 LIMIT 必须在 ORDER BY 之后。举个例子：

mysql> select prod_price
    -> from products
    -> order by prod_price desc
    -> limit 5;
+------------+
| prod_price |
+------------+
|      55.00 |
|      50.00 |
|      35.00 |
|      14.99 |
|      13.00 |
+------------+
5 rows in set (0.00 sec)

Regular Expressions

正则表达式是用来匹配文本的特殊的串（字符集合），用正则表达式语言构建。

1. 基本字符匹配

REGEXP 后跟正则表达式，下列语句表示返回 prod_name 列中值匹配 1000 的数据，即值中任意位置包含 1000 就返回：

mysql> select prod_name
    -> from products
    -> where prod_name regexp '1000'
    -> order by prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
+--------------+
1 row in set (0.01 sec)

如果使用 LIKE 匹配，则执行的是完全匹配，即列中的值为 1000 ，而不是包含：

mysql> select prod_name
    -> from products
    -> where prod_name like '1000'
    -> order by prod_name;
Empty set (0.00 sec)

正则表达式中，. 表示匹配任意一个字符，因此 2000 和 3000 都匹配 .000 ：

mysql> select prod_name
    -> from products
    -> where prod_name regexp '.000'
    -> order by prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

2. OR

正则表达式中的或使用 | 表示，下列语句表示返回 prod_name 列中值包含 1000 或 2000 的数据：

mysql> select prod_name
    -> from products
    -> where prod_name regexp '1000|2000'
    -> order by prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

3. 匹配几个字符之一

[] 是另一种形式的 OR 语句，[123] 相当于 [1|2|3] ：

mysql> select prod_name
    -> from products
    -> where prod_name regexp '[123] Ton'
    -> order by prod_name;
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.00 sec)

| 默认应用于整个串，除非放置在集合中，1|2|3 Ton 表示匹配 1 或 2 或 3 Ton ：

mysql> select prod_name
    -> from products
    -> where prod_name regexp '1|2|3 Ton'
    -> order by prod_name;
+---------------+
| prod_name     |
+---------------+
| 1 ton anvil   |
| 2 ton anvil   |
| JetPack 1000  |
| JetPack 2000  |
| TNT (1 stick) |
+---------------+
5 rows in set (0.00 sec)

使用 NOT 可以获得相反的结果，即返回不匹配正则表达式的结果。例如，检索 products 表中 prod_name 不包含 1 或 2 或 3 的列：

mysql> select prod_name
    -> from products
    -> where prod_name not regexp '[123]'
    -> order by prod_name;
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| Bird seed      |
| Carrots        |
| Detonator      |
| Fuses          |
| Oil can        |
| Safe           |
| Sling          |
| TNT (5 sticks) |
+----------------+
9 rows in set (0.00 sec)

4. 匹配范围

使用 - 定义一个范围，[1-5] 表示匹配 1 ~ 5 共 5 个数字：

mysql> select prod_name
    -> from products
    -> where prod_name regexp '[1-5] Ton'
    -> order by prod_name;
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+
3 rows in set (0.00 sec)

5. 匹配特殊字符

正则表达式中的特殊字符必须使用 \\ 为前导进行匹配

• 这就是所谓的转义（escaping）
• \\ 也用来引用元字符（具有特殊含义的字符）
• 使用 \\\ 匹配反斜杠

空白元字符

元字符	说明
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

例如，匹配 . 的正则表达式为 \\. ：

mysql> select vend_name
    -> from vendors
    -> where vend_name regexp '\\.'
    -> order by vend_name;
+--------------+
| vend_name    |
+--------------+
| Furball Inc. |
+--------------+
1 row in set (0.01 sec)

6. 匹配字符类

预定义的字符集，称为字符类（character class）

Character Class Name	Meaning
alnum	Alphanumeric characters
alpha	Alphabetic characters
blank	Whitespace characters
cntrl	Control characters
digit	Digit characters
graph	Graphic characters
lower	Lowercase alphabetic characters
print	Graphic or space characters
punct	Punctuation characters
space	Space, tab, newline, and carriage return
upper	Uppercase alphabetic characters
xdigit	Hexadecimal digit characters

使用 [:alnum:] 匹配字母和数字：

mysql> SELECT REGEXP_LIKE('justalnums', '[[:alnum:]]+');
+-------------------------------------------+
| REGEXP_LIKE('justalnums', '[[:alnum:]]+') |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.01 sec)

7. 重复元字符

使用重复元字符匹配多次出现的字符，* 表示任意次匹配，也可以是 0 次；+ 匹配一次及以上；? 表示字符可选，0 次或 1 次匹配。直接影响前面的单个字符或几何，例如，以下语句匹配 stick 或 sticks

mysql> SELECT REGEXP_LIKE('Ban', '^Ba*n');
+-----------------------------+
| REGEXP_LIKE('Ban', '^Ba*n') |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_LIKE('Baaan', '^Ba*n'); 
+-------------------------------+
| REGEXP_LIKE('Baaan', '^Ba*n') |
+-------------------------------+
|                             1 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_LIKE('Ban', '^Ba+n');
+-----------------------------+
| REGEXP_LIKE('Ban', '^Ba+n') |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_LIKE('Bn', '^Ba+n');
+----------------------------+
| REGEXP_LIKE('Bn', '^Ba+n') |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select prod_name
    -> from products
    -> where prod_name regexp '\\([0-9] sticks?\\)'
    -> order by prod_name;
+----------------+
| prod_name      |
+----------------+
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
2 rows in set (0.00 sec)

[[:digit:]]{4} 表示匹配任意 4 位数字，等同于使用 [0-9][0-9][0-9][0-9] ：

mysql> select prod_name
    -> from products
    -> where prod_name regexp '[[:digit:]]{4}'
    -> order by prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

8. 定位符

使用定位符匹配特定位置的文本，^ 匹配串的开始，用在集合中表示否定（[]）；
$ 匹配串结尾。例如，匹配任意以数字或 . 开头的字符串：

mysql> select prod_name
    -> from products
    -> where prod_name regexp '^[0-9\\.]'
    -> order by prod_name;
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+
3 rows in set (0.00 sec)

9. SELECT 语句中测试

使用 SELECT 测试正则表达式，REGEXP 检查总是返回 0 或 1 ：

mysql> select 'hello' regexp '[a-h]';
+------------------------+
| 'hello' regexp '[a-h]' |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

计算字段

计算字段是运行时在 SELECT 语句内创建的。

拼接字段

拼接是指将值连接到一起构成单个值，MySQL 中可以使用 Concat() 函数来拼接两个列：

mysql> select Concat(vend_name, '(', vend_country, ')')
    -> from vendors
    -> order by vend_name;
+-------------------------------------------+
| Concat(vend_name, '(', vend_country, ')') |
+-------------------------------------------+
| ACME(USA)                                 |
| Anvils R Us(USA)                          |
| Furball Inc.(USA)                         |
| Jet Set(England)                          |
| Jouets Et Ours(France)                    |
| LT Supplies(USA)                          |
+-------------------------------------------+
6 rows in set (0.00 sec)

删除右侧多余的空格使用 RTrim() 函数：

• LTrim() 去除右侧多余的空格
• Trim() 去除左右两侧的空格

mysql> select Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
    -> from vendors
    -> order by vend_name;
+---------------------------------------------------------+
| Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') |
+---------------------------------------------------------+
| ACME(USA)                                               |
| Anvils R Us(USA)                                        |
| Furball Inc.(USA)                                       |
| Jet Set(England)                                        |
| Jouets Et Ours(France)                                  |
| LT Supplies(USA)                                        |
+---------------------------------------------------------+
6 rows in set (0.00 sec)

使用别名

别名（alias）是一个字段或值的替换名，别名使用 AS 关键字指定：

• 当不同的表名容易混淆时，使用别名进行区分
• 别名也称为导出列（derived column）

mysql> select Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
    -> as vend_title
    -> from vendors
    -> order by vend_name;
+------------------------+
| vend_title             |
+------------------------+
| ACME(USA)              |
| Anvils R Us(USA)       |
| Furball Inc.(USA)      |
| Jet Set(England)       |
| Jouets Et Ours(France) |
| LT Supplies(USA)       |
+------------------------+
6 rows in set (0.00 sec)

执行算数计算

对检索出的数据进行算术计算，例如，以下语句汇总物品的价格，并起名为 expanded_price ，这就是一个计算字段：

mysql> select prod_id,
    -> quantity,
    -> item_price, 
    -> quantity*item_price as expanded_price
    -> from orderitems
    -> where order_num = 20005;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
+---------+----------+------------+----------------+
4 rows in set (0.01 sec)

MySQL 支持的基本算术操作符：

操作符	说明
+	加
-	减
*	乘
/	除

SELECT 语句中测试

可以省略 FROM 语句在 SELECT 中测试表达式：

# 算术运算
mysql> select 3*2;
+-----+
| 3*2 |
+-----+
|   6 |
+-----+
1 row in set (0.00 sec)


# 去除左右的空格
mysql> select Trim(' abc   ');
+-----------------+
| Trim(' abc   ') |
+-----------------+
| abc             |
+-----------------+
1 row in set (0.00 sec)


# 返回当前日期和时间
mysql> select Now();
+---------------------+
| Now()               |
+---------------------+
| 2020-11-22 12:43:14 |
+---------------------+
1 row in set (0.00 sec)

函数（Functions and Operators）

文本函数

Upper() 用于将文本转换为大写，例如：

mysql> select vend_name,
    -> Upper(vend_name) as vend_name_upcase
    -> from vendors
    -> order by vend_name;
+----------------+------------------+
| vend_name      | vend_name_upcase |
+----------------+------------------+
| ACME           | ACME             |
| Anvils R Us    | ANVILS R US      |
| Furball Inc.   | FURBALL INC.     |
| Jet Set        | JET SET          |
| Jouets Et Ours | JOUETS ET OURS   |
| LT Supplies    | LT SUPPLIES      |
+----------------+------------------+
6 rows in set (0.00 sec)

Soundex() 函数将文本串转换为描述其语音表示的字母数字模式，可以根据发音字符和音节比较字符串。例如，Y.Lee 和 Y.Lie 的发音相似，因此匹配成功：

mysql> select cust_name, cust_contact
    -> from customers
    -> where Soundex(cust_contact) = Soundex('Y Lie');
+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+
1 row in set (0.00 sec)

数学函数

Name	Description
ABS()	Return the absolute value
ACOS()	Return the arc cosine
ASIN()	Return the arc sine
ATAN()	Return the arc tangent
ATAN2(), ATAN()	Return the arc tangent of the two arguments
CEIL()	Return the smallest integer value not less than the argument
CEILING()	Return the smallest integer value not less than the argument
CONV()	Convert numbers between different number bases
COS()	Return the cosine
COT()	Return the cotangent
CRC32()	Compute a cyclic redundancy check value
DEGREES()	Convert radians to degrees
EXP()	Raise to the power of
FLOOR()	Return the largest integer value not greater than the argument
LN()	Return the natural logarithm of the argument
LOG()	Return the natural logarithm of the first argument
LOG10()	Return the base-10 logarithm of the argument
LOG2()	Return the base-2 logarithm of the argument
MOD()	Return the remainder
PI()	Return the value of pi
POW()	Return the argument raised to the specified power
POWER()	Return the argument raised to the specified power
RADIANS()	Return argument converted to radians
RAND()	Return a random floating-point value
ROUND()	Round the argument
SIGN()	Return the sign of the argument
SIN()	Return the sine of the argument
SQRT()	Return the square root of the argument
TAN()	Return the tangent of the argument
TRUNCATE()	Truncate to specified number of decimal places

数学函数主要用于代数或几何运算，可以直接在 SELECT 语句中进行测试，例如：

# 取整
mysql> SELECT CEILING(1.23);
+---------------+
| CEILING(1.23) |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)


# 取模
mysql> SELECT MOD(29,9);
+-----------+
| MOD(29,9) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)


# 取模
mysql> SELECT 29 MOD 9;
+----------+
| 29 MOD 9 |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

时间和日期函数

时间和日期采用相应的数据类型和特殊的格式存储，因此能够快速和有效地排序或过滤，并节省物理存储空间。

• 时间和日期类型

MySQL 中的日期格式必须为 yyyy-mm-dd ，基本的日期使用如下，但是这只能匹配 order_date 为 2005-09-01 的数据，而不能匹配 2005-09-01 11:30:05 ：

mysql> select cust_id, order_num
    -> from orders
    -> where order_date = '2005-09-01';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+
1 row in set (0.00 sec)

使用 Date() 函数可以提取日期部分，因此以下写法更为可靠：

mysql> select cust_id, order_num
    -> from orders
    -> where Date(order_date) = '2005-09-01';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+
1 row in set (0.00 sec)

结合 BETWEEN ... AND ... 可以检索时间范围（需要记住每个月的天数和闰年 2 月）：

mysql> select cust_id, order_num
    -> from orders
    -> where Date(order_date) between '2005-09-01' and '2005-09-30';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10003 |     20006 |
|   10004 |     20007 |
+---------+-----------+
3 rows in set (0.00 sec)

使用 Year() 和 Month() 函数可以指定年份和月份，这样就无需自己记住每个月的天数：

mysql> select cust_id, order_num
    -> from orders
    -> where Year(order_date) = 2005 and Month(order_date) = 9;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10003 |     20006 |
|   10004 |     20007 |
+---------+-----------+
3 rows in set (0.00 sec)

聚集函数

聚集函数（aggregate function）用于对一组值进行操作，通常与 GROUP BY 子句一起使用，将值分组为子集。

AVG()

AVG() 通过对表中的行数计数，并计算特定列值之和，以求得该列的平均值。AVG() 可以用来返回特定列的平均值，也可以返回所有列的平均值。

• 只能用来计算数值列
• 忽略列值为 NULL 的行

计算 products 表中 product_price 列的平均值：

mysql> select AVG(prod_price) AS avg_price
    -> FROM products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.00 sec)

确定特定列或行的平均值：

mysql> select AVG(prod_price) AS avg_price
    -> FROM products
    -> WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+
1 row in set (0.01 sec)

COUNT()

COUNT() 函数可以用来确定（符合条件的）行数。

• COUNT(*) 对表中行的数目计数，无论空值还是 NULL 值
• COUNT(cloumn) 对特定列中具有值的行计数，忽略 NULL 值

返回 customers 表中客户的总数：

mysql> select count(*) as num_cust
    -> from customers;
+----------+
| num_cust |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

返回 customers 表中具有电子邮件的客户的总数：

mysql> select count(cust_email) as num_cust
    -> from customers;
+----------+
| num_cust |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

MAX()

MAX() 函数返回指定列中的最大值，忽略列值为 NULL 的行。例如，检索 products 表中最贵的物品的价格：

mysql> select max(prod_price) as max_price
    -> from products;
+-----------+
| max_price |
+-----------+
|     55.00 |
+-----------+
1 row in set (0.00 sec)

MIN()

MIN() 函数返回指定列中的最小值，忽略列值为 NULL 的行。例如，检索 products 表中最便宜的物品的价格：

mysql> select min(prod_price) as max_price
    -> from products;
+-----------+
| max_price |
+-----------+
|      2.50 |
+-----------+
1 row in set (0.00 sec)

SUM()

SUM() 函数用来返回指定列值的总和，忽略列值为 NULL 的行。例如，检索订单号为 20005 的订单中所有物品数量之和：

mysql> select sum(quantity) as items_ordered
    -> from orderitems
    -> where order_num = 20005;
+---------------+
| items_ordered |
+---------------+
|            19 |
+---------------+
1 row in set (0.00 sec)

SUM() 也可以用来合计计算值，例如 item_price*quantity 表示统计总金额：

mysql> select sum(item_price*quantity) as total_price
    -> from orderitems
    -> where order_num = 20005;
+-------------+
| total_price |
+-------------+
|      149.87 |
+-------------+
1 row in set (0.00 sec)

ALL | DISTINCT

上一节中的 5 个聚集函数都可以结合 ALL 或 DISTINCT 使用。

• 默认对所有的行执行计算，ALL 参数可以不用显式指定
• 针对包含不同值的行计算，指定 DISTINCT 参数
  • 不能用于 COUNT(*) ，即不允许使用 COUNT(DISTINCT)
  • 必须指定列名，不能用于计算或表达式

例如，返回 products 表中 vend_id 为 1003 的供应商提供的产品的平均价格，只考虑不同价格的产品：

mysql> select avg(DISTINCT prod_price) as avg_price
    -> from products
    -> where vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
1 row in set (0.00 sec)

组合聚集函数

SELECT 语句可以包含多个聚集函数：

mysql> select count(*) as num_items,
    -> min(prod_price) as price_min,
    -> max(prod_price) as price_max,
    -> avg(prod_price) as price_avg
    -> from products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|        14 |      2.50 |     55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)

数据分组

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

GROUP BY

使用 GROUP BY 对数据进行分组，例如，针对 vend_id 进行分组并统计每组的数量：

• 可以包含任意数目的列
• 嵌套分组将在最后规定的分组上进行汇总
• 每个列必须都是检索列或有效的表达式
  • 如果在 SELECT 语句中使用表达式，则必须在 GROUP BY 中指定相同的表达式（不能使用别名）
• 除了聚集计算语句外，SELECT 语句中的每个列都必须在 GROUP BY 子句中给出
• 如果分组列中具有 NULL 值，则 NULL 作为一个分组返回
• GROUP BY 子句必须在 WHERE 子句之后，ORDER BY 子句之前

mysql> select vend_id, count(*) as num_prods
    -> from products
    -> group by vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+
4 rows in set (0.00 sec)

1. WITH ROLLUP

使用 WITH ROLLUP 关键字可以得到每个分组以及每个分组汇总的值，例如，显示产品数量的总值：

mysql> select vend_id, count(*) as num_prods
    -> from products
    -> group by vend_id with rollup;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
|    NULL |        14 |
+---------+-----------+
5 rows in set (0.00 sec)

2. HAVING

使用 HAVING 关键字对分组进行过滤，在数据分组后进行过滤。

• WHERE 关键字过滤行，在数据分组前进行过滤

例如，检索 orders 表中拥有 2 个及以上订单的用户：

mysql> select cust_id, count(*) as orders
    -> from orders
    -> group by cust_id
    -> having count(*) >= 2;
+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+
1 row in set (0.00 sec)

同时使用 WHERE 和 HAVING 检索包含 2 个及以上价格大于等于 10 的产品的供应商：

• 先检索价格大于等于 10 的产品
• 再分组聚集，根据条件返回

mysql> select vend_id, count(*) as num_prods
    -> from products
    -> where prod_price >= 10
    -> group by vend_id
    -> having count(*) >= 2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+
2 rows in set (0.00 sec)

3. ORDER BY

一般在使用 GROUP BY 子句时会结合 ORDER BY ，以保证数据正确排序。

ORDER BY	GROUP BY
排序产生的输出	分组行，输出可能不是分组的顺序
任意列都可以使用，没有选择的列也可以使用	只能使用选择列或表达式列，且必须使用每个选择列表达式
不一定需要使用	如果与聚集函数一起使用列（或表达式），则必须使用

例如，检索订单价格大于等于 50 的订单及其总价：

mysql> select order_num, sum(quantity*item_price) as ordertotal
    -> from orderitems
    -> group by order_num
    -> having sum(quantity*item_price) >= 50;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20005 |     149.87 |
|     20006 |      55.00 |
|     20007 |    1000.00 |
|     20008 |     125.00 |
+-----------+------------+
4 rows in set (0.00 sec)

使用 ORDER BY 按订单总价排序：

mysql> select order_num, sum(quantity*item_price) as ordertotal
    -> from orderitems
    -> group by order_num
    -> having sum(quantity*item_price) >= 50
    -> order by ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+
4 rows in set (0.00 sec)

SELECT 子句顺序

子句	说明	是否必须使用
select	要返回的列或表达式	是
from	从中检索的数据表	仅在从表中选择数据时使用
where	行级过滤	否
group by	分组说明	仅在按组计算聚集时使用
having	组级过滤	否
order by	输出排序顺序	否
limit	限制检索的行数	否

子查询

子查询（subquery）是指嵌套在其他语句中的查询语句。在 SELECT 语句中，子查询总是从内向外处理。

过滤

可以把一条 SELECT 语句返回的结果用在另一条 SELECT 语句的 WHERE 子句中。例如，根据订单号检索购买了 TNT2 的客户：

mysql> select cust_id
    -> from orders
    -> where order_num in(
    ->   select order_num
    ->   from orderitems
    ->   where prod_id = 'TNT2');
+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+
2 rows in set (0.00 sec)

嵌套多个子查询，根据订单号检索购买了 TNT2 的客户，再根据客户 ID 检索客户的姓名：

• 在 WHERE 子句中使用子查询，应该保证 SELECT 语句和 WHERE 子句具有相同数目的列

mysql> select cust_name, cust_contact
    -> from customers
    -> where cust_id in(
    ->   select cust_id
    ->   from orders
    ->   where order_num in(
    ->     select order_num
    ->     from orderitems
    ->     where prod_id = 'TNT2'));
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.00 sec)

作为计算字段

对 customers 表中每个客户返回 3 列：cust_name, cust_state, orders：

• orders 是由子查询建立的计算字段

mysql> select cust_name, 
    -> cust_state,
    -> (select count(*)
    -> from orders
    -> where orders.cust_id=customers.cust_id) as orders
    -> from customers
    -> order by cust_name;
+----------------+------------+--------+
| cust_name      | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc.    | MI         |      2 |
| E Fudd         | IL         |      1 |
| Mouse House    | OH         |      0 |
| Wascals        | IN         |      1 |
| Yosemite Place | AZ         |      1 |
+----------------+------------+--------+
5 rows in set (0.00 sec)

相关子查询（Correlated Subqueries）是涉及外部查询的子查询。当列名具有多义性时，必须使用完全限定的名称（以 . 分隔）。

连接（JOIN）

关系表的设计就是要保证把信息分解成多个表，一类数据一张表，每个表通过关系（relation）关联。通常，关系数据库的可伸缩性比非关系数据库要好。

• 主键（PRIMARY KEY），唯一标识一行
• 外键（FOREIGN KEY），某个表中包含另一个表的主键

等值连接（INNER JOIN）

等值连接基于两个表之间的相等测试，也称为内部连接，在 MySQL 中 JOIN ， CROSS JOIN ， INNER JOIN 在语法上是等价的，可以相互替换。使用 vend_id 匹配列，检索每个供应商生产的产品信息：

• 不使用 WHERE 子句将会执行笛卡尔积（cartesian product），因此必须保证在联结中使用 WHERE 子句

mysql> select vend_name, prod_name, prod_price
    -> from vendors, products
    -> where vendors.vend_id = products.vend_id
    -> order by vend_name, prod_name;
+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| ACME        | Bird seed      |      10.00 |
| ACME        | Carrots        |       2.50 |
| ACME        | Detonator      |      13.00 |
| ACME        | Safe           |      50.00 |
| ACME        | Sling          |       4.49 |
| ACME        | TNT (1 stick)  |       2.50 |
| ACME        | TNT (5 sticks) |      10.00 |
| Anvils R Us | .5 ton anvil   |       5.99 |
| Anvils R Us | 1 ton anvil    |       9.99 |
| Anvils R Us | 2 ton anvil    |      14.99 |
| Jet Set     | JetPack 1000   |      35.00 |
| Jet Set     | JetPack 2000   |      55.00 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
+-------------+----------------+------------+
14 rows in set (0.00 sec)

也可以使用 ON 指明连接条件：

mysql> select vend_name, prod_name, prod_price
    -> from vendors inner join products
    -> on vendors.vend_id = products.vend_id
    -> order by vend_name, prod_name;
+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| ACME        | Bird seed      |      10.00 |
| ACME        | Carrots        |       2.50 |
| ACME        | Detonator      |      13.00 |
| ACME        | Safe           |      50.00 |
| ACME        | Sling          |       4.49 |
| ACME        | TNT (1 stick)  |       2.50 |
| ACME        | TNT (5 sticks) |      10.00 |
| Anvils R Us | .5 ton anvil   |       5.99 |
| Anvils R Us | 1 ton anvil    |       9.99 |
| Anvils R Us | 2 ton anvil    |      14.99 |
| Jet Set     | JetPack 1000   |      35.00 |
| Jet Set     | JetPack 2000   |      55.00 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
+-------------+----------------+------------+
14 rows in set (0.00 sec)

连接的表的数量没有限制，但是连接的表越多，性能越差。例如，检索订单号为 20005 的订单中的物品，用 WHERE 子句匹配表中的列：

mysql> select prod_name, vend_name, prod_price, quantity
    -> from orderitems, products, vendors
    -> where products.vend_id = vendors.vend_id
    -> and orderitems.prod_id = products.prod_id
    -> and order_num = 20005;
+----------------+-------------+------------+----------+
| prod_name      | vend_name   | prod_price | quantity |
+----------------+-------------+------------+----------+
| .5 ton anvil   | Anvils R Us |       5.99 |       10 |
| 1 ton anvil    | Anvils R Us |       9.99 |        3 |
| TNT (5 sticks) | ACME        |      10.00 |        5 |
| Bird seed      | ACME        |      10.00 |        1 |
+----------------+-------------+------------+----------+
4 rows in set (0.00 sec)

将上一节中三层嵌套的子查询进行化简：

mysql> select cust_name, cust_contact
    -> from customers, orders, orderitems
    -> where customers.cust_id = orders.cust_id
    -> and orderitems.order_num = orders.order_num
    -> and prod_id = 'TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.00 sec)

自连接

SQL 允许对表起别名。

• 缩短 SQL 语句
• 允许在单条 SELECT 语句中多次使用相同的表

自连接指对相同的表进行连接，通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句，由于对表的引用具有二义性，因此需要使用别名。例如，检索制造物品 DTNTR 的厂商制造的其他物品：

mysql> select p1.prod_id, p1.prod_name
    -> from products as p1, products as p2
    -> where p1.vend_id = p2.vend_id
    -> and p2.prod_id = 'DTNTR';
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+
7 rows in set (0.00 sec)

自然连接（NATURAL JOIN）

自然连接排除表连接中多次出现的行，默认为左连接（NATURAL LEFT JOIN），即保留左边表中的列，丢弃右边表中的同名的列。例如，对 customers 表使用通配符 * 表示检索出该表的所有列，其他列明确给出，因此不会出现重复的列：

mysql> select c.*, o.order_num, o.order_date,
    -> oi.prod_id, oi.quantity, oi.item_price
    -> from customers as c, orders as o, orderitems as oi
    -> where c.cust_id = o.cust_id
    -> and oi.order_num = o.order_num
    -> and prod_id = 'FB';
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
| cust_id | cust_name   | cust_address   | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email      | order_num | order_date          | prod_id | quantity | item_price |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20005 | 2005-09-01 00:00:00 | FB      |        1 |      10.00 |
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20009 | 2005-10-08 00:00:00 | FB      |        1 |      10.00 |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
2 rows in set (0.00 sec)

外部连接（OUTER JOIN）

外部连接可以返回相关表中没有关联的行，必须使用 LEFT 或 RIGHT 指定包括其所有行的表。例如，以下语句使用内部连接检索所有客户及其订单：

mysql> select customers.cust_id, orders.order_num
    -> from customers INNER JOIN orders
    -> on customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
5 rows in set (0.00 sec)

外部连接可以检索所有客户，包括没有订单的客户：

• LEFT OUTER JOIN 保留左边表的所有列

mysql> select customers.cust_id, orders.order_num
    -> from customers LEFT OUTER JOIN orders
    -> on customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
6 rows in set (0.00 sec)

结合聚集函数

函数也可以与连接一起使用。例如，检索客户以及每个客户的订单数：

mysql> select customers.cust_name,
    -> customers.cust_id,
    -> count(orders.order_num) as num_ord
    -> from customers INNER JOIN orders
    -> on customers.cust_id = orders.cust_id
    -> group by customers.cust_id;
+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+
4 rows in set (0.00 sec)

使用外部连接包含所有客户，包括没有订单的客户：

mysql> select customers.cust_name,
    -> customers.cust_id,
    -> count(orders.order_num) as num_ord
    -> from customers LEFT OUTER JOIN orders
    -> on customers.cust_id = orders.cust_id
    -> group by customers.cust_id;
+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Mouse House    |   10002 |       0 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+
5 rows in set (0.00 sec)

组合查询

组合查询通常称为并（union）或复合查询（compound query），指执行多个查询并将其结果作为单个集合返回。以下两种基本情况需要使用组合查询：

• 在单个查询中从不同的表返回结构类似的数据
• 对单个表执行多个查询，按单个查询返回数据

任何具有多个 WHERE 子句的 SELECT 语句都可以作为一个组合查询给出。

可以使用 UNION 子句将多个 SELECT 语句的结果组合成一个结果集。

• 必须由两条或以上的 SELECT 语句组成
• 每个查询必须包含相同的列、表达式或聚集函数
• 列数据类型必须兼容（可以隐式转换）

例如，同时检索价格小于等于 5 的物品以及供应商 1001 和 1002 制造的物品：

mysql> select vend_id, prod_id ,prod_price
    -> from products
    -> where prod_price <= 5
    -> union
    -> select vend_id, prod_id ,prod_price
    -> from products
    -> where vend_id in (1001, 1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+
8 rows in set (0.00 sec)

也可以使用 WHERE 子句实现，更为简洁：

mysql> select vend_id, prod_id ,prod_price
    -> from products
    -> where prod_price <= 5
    -> or vend_id in (1001, 1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
+---------+---------+------------+
8 rows in set (0.00 sec)

UNION 子句从结果集中自动去除了重复的行，即与在单条 SELECT 语句中使用多个 WHERE 子句条件一样。如果想返回匹配的所有行，可以使用 UNION ALL ，UNION ALL 不排除重复的行：

mysql> select vend_id, prod_id ,prod_price
    -> from products
    -> where prod_price <= 5
    -> union all
    -> select vend_id, prod_id ,prod_price
    -> from products
    -> where vend_id in (1001, 1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+
9 rows in set (0.00 sec)

使用 UNION 组合查询时可以使用 ORDER BY ，但只能用一条，且必须放在最后一条 SELECT 语句之后。MySQL 会按照 ORDER BY 指定的列对结果集进行排序。

mysql> select vend_id, prod_id ,prod_price
    -> from products
    -> where prod_price <= 5
    -> union
    -> select vend_id, prod_id ,prod_price
    -> from products
    -> where vend_id in (1001, 1002)
    -> order by vend_id, prod_price;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
|    1003 | FC      |       2.50 |
|    1003 | TNT1    |       2.50 |
|    1003 | SLING   |       4.49 |
+---------+---------+------------+
8 rows in set (0.00 sec)

全文本搜索

为了执行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断重新索引。索引之后 SELECT 语句可以和 MATCH() 和 AGAINST() 一起使用以执行搜索。

一般在创建表时启用全文本搜索，指定被索引列以及使用的存储引擎。FULLTEXT() 用于指定被索引的列，可以包含多个列，定义之后 MySQL 自动维护索引。

• MySQL 默认使用 InnoDB 引擎，也有其他的 可选引擎
• 索引时短词被忽略，默认为 3 个或以下字符的词
• MySQL 内置停用词表（stopword），索引时忽略这些词
• 50% 规则：如果一个词出现在 50% 以上的行中，则将其作为停用词忽略
  • 不用于布尔全文本检索
• 如果行数少于 3 行，则全文本搜索不返回结果
• 忽略词中的单引号
• 不具有分隔符的语言（日语、汉语等）需要处理，默认处理不能恰当返回搜索结果

MATCH() 指定被搜索的列，必须和 FULLTEXT() 定义中的相同，如果指定多个列还必须按次序列出；AGAINST() 指定要使用的搜索表达式。全文本搜索默认不区分大小写。

例如，检索 note_text 列值包含 rabbit 的行，并且先返回 rabbit 出现位置靠前的行：

mysql> select note_text
    -> from productnotes
    -> where MATCH(note_text) AGAINST('rabbit');
+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                         |
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

上面的语句也可以使用 LIKE 完成：

mysql> select note_text
    -> from productnotes
    -> where note_text LIKE '%rabbit%';
+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                         |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

全文本搜索对结果进行排序，先返回具有较高等级的行。等级由 MySQL 根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该次的行数计算得出。因此不包含 rabbit 的行的等级为 0 ，包含 rabbit 的行具有非零等级：

• 如果指定多个搜索项，则包含多数匹配词的行将具有更高的等级

mysql> select note_text, MATCH (note_text) AGAINST('rabbit')
    -> from productnotes;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+
| note_text                                                                                                                                                 | MATCH (note_text) AGAINST('rabbit') |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+
| Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.                          |                                   0 |
| Can shipped full, refills not available.
Need to order new can if refill needed.                                                                          |                                   0 |
| Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.         |                                   0 |
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.                                      |                  1.5905543565750122 |
| Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended. |                                   0 |
| Matches not included, recommend purchase of matches or detonator (item DTNTR).                                                                            |                                   0 |
| Please note that no returns will be accepted if safe opened using explosives.                                                                             |                                   0 |
| Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.  |                                   0 |
| Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.                                       |                                   0 |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                                                              |                  1.6408053636550903 |
| Shipped unassembled, requires common tools (including oversized hammer).                                                                                  |                                   0 |
| Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.                                                                |                                   0 |
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.   |                                   0 |
| Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.                            |                                   0 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+
14 rows in set (0.00 sec)

WITH QUERY EXPANSION

查询扩展（query expansion）用来放宽所返回的全文本搜索结果的范围。在使用查询扩展时，MySQL 对数据和索引进行两遍扫描来完成搜索：

• 全文本搜索，找出与搜索条件匹配的所有行
• 检查匹配并选择有用的词
• 全文本搜索，找出与有用的词匹配的所有行

例如，一个简单的全文本搜索：

mysql> select note_text
    -> from productnotes
    -> where MATCH(note_text) AGAINST('anvils');
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

使用 WITH QUERY EXPANSION 放宽搜索范围：

mysql> select note_text
    -> from productnotes
    -> where MATCH(note_text) AGAINST('anvils' WITH QUERY EXPANSION);
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. |
| Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.                         |
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.  |
| Please note that no returns will be accepted if safe opened using explosives.                                                                            |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                                                             |
| Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.                                                               |
| Matches not included, recommend purchase of matches or detonator (item DTNTR).                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

布尔全文本搜索（Boolean Full-Text Searches）

没有使用 FULLTEXT() 定义索引也可以使用布尔全文本搜索，但是相比建立索引的搜索性能较差。使用 IN BOOLEAN MODE 指定布尔全文本搜索，- 表示排除文本，+ 表示必须包含文本，布尔全文本搜索排列但不排序。

下列语句表示检索包含 heavy 但不包含以 rope 开始的词的行：

mysql> select note_text
    -> from productnotes
    -> where MATCH(note_text) AGAINST('heavy -rope*' IN BOOLEAN MODE);
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

1. 匹配词 rabbit 和 bait ：

mysql> select note_text
    -> from productnotes
    -> where MATCH(note_text) AGAINST('+rabbit +bait' IN BOOLEAN MODE);
+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2. 匹配词 rabbit 或 bait ：

mysql> select note_text
    -> from productnotes
    -> where MATCH(note_text) AGAINST('rabbit bait' IN BOOLEAN MODE);
+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                         |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

3. 匹配短语 rabbit bait ：

mysql> select note_text
    -> from productnotes
    -> where MATCH(note_text) AGAINST('"rabbit bait"' IN BOOLEAN MODE);
+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4. 增加前者的等级，降低后者的等级：

mysql> select note_text
    -> from productnotes
    -> where MATCH(note_text) AGAINST('>rabbit <carrot' IN BOOLEAN MODE);
+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                         |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

5. 匹配词 safe 和 combination，降低后者的等级：

mysql> select note_text
    -> from productnotes
    -> where MATCH(note_text) AGAINST('+safe +(<combination>)' IN BOOLEAN MODE);
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                         |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers. |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

INSERT

INSERT 用来插入行到数据库表中，可以用以下几种方式使用：

插入单行数据

要求指定表名和被插入到新行中的值，如果不显式给出列名，必须给出所有列的值，且次序为表定义中的顺序。例如，往 customers 表中插入数据，第一个值为 MySQL 自动增量，因此指定为 NULL ：

mysql> insert into customers
    -> values(
    -> NULL,
    -> 'Pep E. LaPew',
    -> '100 Main Street',
    -> 'Los Angles',
    -> 'CA',
    -> '90046',
    -> 'USA',
    -> NULL,
    -> NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select * from customers
    -> where cust_name like '%Pep%';
+---------+--------------+-----------------+------------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name    | cust_address    | cust_city  | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+--------------+-----------------+------------+------------+----------+--------------+--------------+------------+
|   10006 | Pep E. LaPew | 100 Main Street | Los Angles | CA         | 90046    | USA          | NULL         | NULL       |
+---------+--------------+-----------------+------------+------------+----------+--------------+--------------+------------+
1 row in set (0.00 sec)

上面的写法很不安全，而且依赖于表定义，显式给出列名更好（还不用考虑次序），即使表结构改变，只要列还存在就不会出错：

• 省略的列必须满足以下条件之一
  • 允许 NULL 值
  • 设置了默认值

mysql> delete from customers where cust_name like '%Pep%';
Query OK, 1 row affected (0.02 sec)

mysql> insert into customers(
    -> cust_name,
    -> cust_address,
    -> cust_city,
    -> cust_state,
    -> cust_zip,
    -> cust_country,
    -> cust_contact,
    -> cust_email
    -> )
    -> values(
    -> 'Pep E. LaPew',
    -> '100 Main Street',
    -> 'Los Angles',
    -> 'CA',
    -> '90046',
    -> 'USA',
    -> NULL,
    -> NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select * from customers where cust_name like '%Pep%';
+---------+--------------+-----------------+------------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name    | cust_address    | cust_city  | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+--------------+-----------------+------------+------------+----------+--------------+--------------+------------+
|   10007 | Pep E. LaPew | 100 Main Street | Los Angles | CA         | 90046    | USA          | NULL         | NULL       |
+---------+--------------+-----------------+------------+------------+----------+--------------+--------------+------------+
1 row in set (0.00 sec)

可以使用 LOW_PRIORITY 延迟插入的执行，直到没有其他客户端从表中读取数据时才会执行，该修饰符也可以用于 UPDATE 和 DELETE 语句。

插入多行数据

可以在一条 INSERT 语句中插入多行数据，每组值中列的次序相同，使用圆括号括起来，并用逗号分隔。

mysql> delete from customers where cust_name like '%Pep%';
Query OK, 1 row affected (0.02 sec)

mysql> insert into customers(
    ->   cust_name,
    ->   cust_address,
    ->   cust_city,
    ->   cust_state,
    ->   cust_zip,
    ->   cust_country
    -> )
    -> values(
    ->   'Pep E. LaPew',
    ->   '100 Main Street',
    ->   'Los Angles',
    ->   'CA',
    ->   '90046',
    ->   'USA'),
    ->   (
    ->     'M. Martian',
    ->     '42 Galaxy Way',
    ->     'New York',
    ->     'NY',
    ->     '11213',
    ->     'USA'
    ->   );
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from customers where cust_country = 'USA';
+---------+----------------+---------------------+------------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name      | cust_address        | cust_city  | cust_state | cust_zip | cust_country | cust_contact | cust_email          |
+---------+----------------+---------------------+------------+------------+----------+--------------+--------------+---------------------+
|   10001 | Coyote Inc.    | 200 Maple Lane      | Detroit    | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com     |
|   10002 | Mouse House    | 333 Fromage Lane    | Columbus   | OH         | 43333    | USA          | Jerry Mouse  | NULL                |
|   10003 | Wascals        | 1 Sunny Place       | Muncie     | IN         | 42222    | USA          | Jim Jones    | rabbit@wascally.com |
|   10004 | Yosemite Place | 829 Riverside Drive | Phoenix    | AZ         | 88888    | USA          | Y Sam        | sam@yosemite.com    |
|   10005 | E Fudd         | 4545 53rd Street    | Chicago    | IL         | 54545    | USA          | E Fudd       | NULL                |
|   10008 | Pep E. LaPew   | 100 Main Street     | Los Angles | CA         | 90046    | USA          | NULL         | NULL                |
|   10009 | M. Martian     | 42 Galaxy Way       | New York   | NY         | 11213    | USA          | NULL         | NULL                |
+---------+----------------+---------------------+------------+------------+----------+--------------+--------------+---------------------+
7 rows in set (0.00 sec)

插入检索出的数据

INSERT … SELECT 语句用于将 SELECT 语句的结果插入表中。

检索 customers 表中的数据并插入 custnew 表，custnew 表的结构与 customers 表相同，省略自增列 cust_id ，MySQL 会自动生成。

mysql> CREATE TABLE custnew
    -> (
    ->   cust_id      int       NOT NULL AUTO_INCREMENT,
    ->   cust_name    char(50)  NOT NULL ,
    ->   cust_address char(50)  NULL ,
    ->   cust_city    char(50)  NULL ,
    ->   cust_state   char(5)   NULL ,
    ->   cust_zip     char(10)  NULL ,
    ->   cust_country char(50)  NULL ,
    ->   cust_contact char(50)  NULL ,
    ->   cust_email   char(255) NULL ,
    ->   PRIMARY KEY (cust_id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into custnew
    -> select *
    -> from customers;
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from custnew;
+---------+----------------+---------------------+------------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name      | cust_address        | cust_city  | cust_state | cust_zip | cust_country | cust_contact | cust_email          |
+---------+----------------+---------------------+------------+------------+----------+--------------+--------------+---------------------+
|   10001 | Coyote Inc.    | 200 Maple Lane      | Detroit    | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com     |
|   10002 | Mouse House    | 333 Fromage Lane    | Columbus   | OH         | 43333    | USA          | Jerry Mouse  | NULL                |
|   10003 | Wascals        | 1 Sunny Place       | Muncie     | IN         | 42222    | USA          | Jim Jones    | rabbit@wascally.com |
|   10004 | Yosemite Place | 829 Riverside Drive | Phoenix    | AZ         | 88888    | USA          | Y Sam        | sam@yosemite.com    |
|   10005 | E Fudd         | 4545 53rd Street    | Chicago    | IL         | 54545    | USA          | E Fudd       | NULL                |
|   10008 | Pep E. LaPew   | 100 Main Street     | Los Angles | CA         | 90046    | USA          | NULL         | NULL                |
|   10009 | M. Martian     | 42 Galaxy Way       | New York   | NY         | 11213    | USA          | NULL         | NULL                |
+---------+----------------+---------------------+------------+------------+----------+--------------+--------------+---------------------+
7 rows in set (0.00 sec)

UPDATE

可以使用两种方式使用 UPDATE ：

• 更新表中特定行（WHERE 子句、子查询）
• 更新表中所有行

SET 用来将新值赋给被更新的列。例如，更新 10005 客户的电子邮件地址：

mysql> UPDATE customers
    -> SET cust_email = 'elmer@fudd.com'
    -> WHERE cust_id = 10005;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

更新多个列时，使用逗号分隔：

mysql> UPDATE customers
    -> SET cust_name = 'The Fudds',
    -> cust_email = 'elmer@fudd.com'
    -> WHERE cust_id = 10005;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

使用 IGNORE 修饰符，即使在更新过程中出现错误，UPDATE 语句也不会中止。例如，UPDATE IGNORE customers 。

为了删除某个列的值，可以将其设置为 NULL （如果表定义允许 NULL 值）：

mysql> UPDATE customers
    -> SET cust_email = NULL
    -> WHERE cust_id = 10005;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

DELETE

和 UPDATE 相同，可以使用两种方式使用 DELETE ：

• 删除表中特定行（WHERE 子句、子查询）
• 删除表中所有行

DELETE 删除整行数据，用 FROM 指定待删除数据所在的表：

mysql> DELETE FROM customers
    -> WHERE cust_id = 10006;
Query OK, 1 row affected (0.00 sec)

删除表中所有行可以使用 TRUNCATE TABLE 语句，删除原来的表并重新创建；而 DELETE 语句执行逐行删除。

CREATE TABLE

使用 CREATE TABLE 创建表，必须给出表名和列（名字和定义）。例如，customers 表的创建：

• 表名紧跟在 CREATE TABLE 关键字后
• 表定义（所有列）都在圆括号中
• 每个列之间用逗号分隔
• 每列的定义以列名开始，后跟数据类型
  • 列名唯一
• 使用 PRIMARY KEY 指定关键字
• AUTO_INCREMENT 表示自动增量
• ENGINE 表示选择使用的引擎

CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

NULL

NULL 表示没有值或缺值，允许 NULL 值的列允许在插入行时不给出该列的值，不允许 NULL 值的列在插入或更新时必须有值。

orders 表的每个列都必须有值，因此每个列都使用关键字 NOT NULL ：

CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;

vendors 表拥有 NULL 和 NOT NULL 的列：

• NULL 为默认设置

CREATE TABLE vendors
(
  vend_id      int      NOT NULL AUTO_INCREMENT,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;

PRIMARY KEY

一个表只能有一个主键，主键的名称始终是 PRIMARY KEY ，且必须定义为 NOT NULL 如果没有将主键显式声明为 NOT NULL 则 MySQL 将自动进行设置。

• 如果主键使用单个列，则它的值必须唯一；如果使用多个列，则组合必须唯一。

orderitems 表，每个订单中的每种物品只会出现一次（数量不同），因此可以组合作为主键：

CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

AUTO_INCREMENT

AUTO_INCREMENT 指示当该列增加一行时自动增量。每个表只允许一个自增列，而且必须被索引。

如果手动插入自增列的值，则后续增量将使用手动插入的值。

使用 LAST_INSERT_ID() 可以查询最后一个增量值：

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|            10008 |
+------------------+
1 row in set (0.00 sec)

DEFAULT

创建表时可以使用 DEFAULT 指定默认值。

例如，orderitems 表设置在未给出 quantity 列值时使用默认值 1 ：

CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL DEFAULT 1,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

ENGINE

使用 ENGINE 指定表的存储引擎，默认使用 InnoDB 引擎，目前 MySQL 支持下列引擎：

• InnoDB
• MyISAM
• MEMORY
• CSV
• ARCHIVE
• EXAMPLE
• FEDERATED
• HEAP
• MERGE
• NDB

数据库中的表可以使用不同的存储引擎，需要注意的是外键不能跨引擎，即使用一个引擎的表不能引用使用不同引擎的表的外键。

ALTER TABLE

可以使用 ALTER TABLE 语句更新表，常用于定义外键。

使用 ADD 为 vendors 表添加 vend_phone 列：

mysql> ALTER TABLE vendors
    -> ADD vend_phone CHAR(20);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用 DROP COLUMN 删除 vendors 表中 vend_phone 列：

mysql> ALTER TABLE vendors
    -> DROP COLUMN vend_phone;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

ADD CONSTRAINT ... FOREIGN KEY ... REFERENCES ... 用于定义外键：

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

RENAME TABLE

使用 RENAME TABLE 可以重命名一个表：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| custnew        |
| customers      |
| orderitems     |
| orders         |
| productnotes   |
| products       |
| vendors        |
+----------------+
7 rows in set (0.00 sec)

mysql> RENAME TABLE custnew TO customers2;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| customers      |
| customers2     |
| orderitems     |
| orders         |
| productnotes   |
| products       |
| vendors        |
+----------------+
7 rows in set (0.00 sec)

可以在一条语句中对多个表重命名：

RENAME TABLE backup_customers TO customers,
backup_vendors TO vendors,
backup_products TO products

DROP TABLE

删除表使用 DROP TABLE ：

mysql> DROP TABLE customers2;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| customers      |
| orderitems     |
| orders         |
| productnotes   |
| products       |
| vendors        |
+----------------+
6 rows in set (0.00 sec)

视图（View）

视图是虚拟的表，只包含使用时动态检索数据的查询。视图不包含表中的数据，只是一个 SQL 查询。

视图的常见应用

• 重用 SQL 语句
• 简化复杂的 SQL 操作
• 使用表的组成部分（而不是整个表）
• 保护数据
• 更改数据格式和表示

使用视图的规则与限制

• 视图名必须唯一
• 视图数量没有限制
• 需要具有一定的访问权限才可以创建视图
• 视图可以嵌套
• 视图检索数据的 SELECT 语句的 ORDER BY 将会覆盖视图的 ORDER BY
• 视图不能索引，也不能有关联的触发器或默认值
• 视图可以和表一起使用
• 检索视图使用的 WHERE 子句和创建视图使用的 WHERE 子句会自动合并

相关语句

• 创建视图：CREATE VIEW
• 查看创建语句：SHOW CREATE VIEW viewname
• 更新视图：
  • 先 DROP 再 CREATE
  • CREATE OR REPLACE VIEW

简化复杂的 SQL操作

创建一个名为 productcustomers 的视图，连接三个表，返回订购了任意产品的客户：

mysql> CREATE VIEW productcustomers AS
    -> SELECT cust_name, cust_contact, prod_id
    -> FROM customers, orders, orderitems
    -> WHERE customers.cust_id = orders.cust_id
    -> AND orderitems.order_num = orders.order_num;
Query OK, 0 rows affected (0.01 sec)

用视图检索购买了 TNT2 的客户：

mysql> select cust_name, cust_contact
    -> from productcustomers
    -> where prod_id = 'TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.00 sec)

格式化数据

检索供应商和位置的格式化数据：

mysql> CREATE VIEW vendorlocation AS
    -> SELECT CONCAT(RTRIM(vend_name), '(', RTrim(vend_country), ')')
    -> AS vend_title
    -> FROM vendors
    -> ORDER BY vend_name;
Query OK, 0 rows affected (0.00 sec)

从视图中检索格式化的数据：

mysql> select * from vendorlocation;
+------------------------+
| vend_title             |
+------------------------+
| ACME(USA)              |
| Anvils R Us(USA)       |
| Furball Inc.(USA)      |
| Jet Set(England)       |
| Jouets Et Ours(France) |
| LT Supplies(USA)       |
+------------------------+
6 rows in set (0.00 sec)

过滤数据

创建过滤没有邮件地址的客户的视图：

mysql> CREATE VIEW customeremaillist AS
    -> SELECT cust_id, cust_name, cust_email
    -> FROM customers
    -> WHERE cust_email IS NOT NULL;
Query OK, 0 rows affected (0.01 sec)

从视图中检索拥有邮件地址的客户：

mysql> select * from customeremaillist;
+---------+----------------+---------------------+
| cust_id | cust_name      | cust_email          |
+---------+----------------+---------------------+
|   10001 | Coyote Inc.    | ylee@coyote.com     |
|   10003 | Wascals        | rabbit@wascally.com |
|   10004 | Yosemite Place | sam@yosemite.com    |
+---------+----------------+---------------------+
3 rows in set (0.00 sec)

计算字段

创建视图计算每种物品的总和：

mysql> CREATE VIEW orderitemsexpanded AS
    -> SELECT order_num,
    -> prod_id,
    -> quantity,
    -> item_price,
    -> quantity*item_price AS expanded_price
    -> FROM orderitems;
Query OK, 0 rows affected (0.01 sec)

直接检索视图就可以获得总和：

mysql> SELECT * FROM orderitemsexpanded
    -> WHERE order_num = 20005;
+-----------+---------+----------+------------+----------------+
| order_num | prod_id | quantity | item_price | expanded_price |
+-----------+---------+----------+------------+----------------+
|     20005 | ANV01   |       10 |       5.99 |          59.90 |
|     20005 | ANV02   |        3 |       9.99 |          29.97 |
|     20005 | TNT2    |        5 |      10.00 |          50.00 |
|     20005 | FB      |        1 |      10.00 |          10.00 |
+-----------+---------+----------+------------+----------------+
4 rows in set (0.00 sec)

更新视图

通常，视图是可以更新的，更新一个视图将更新其基表。但如果视图定义中有以下操作，则不能更新视图：

• 分组（GROUP BY、HAVING）
• 连接
• 子查询
• 并
• 聚集函数
• DISTINCT
• 计算列

存储过程（stored procedure）

使用存储过程的理由：简单、安全、高性能

• 简化复杂的操作
• 保证数据的完整性
  • 防止错误，保证数据的一致性
• 简化对变动的管理
  • 安全性
• 提高性能
• 功能更强更灵活

缺陷

• 复杂
• 权限
  • 编写权限和执行权限分离，不能编写但可以执行

CREATE PROCEDURE

使用 CREATE PROCEDIRE 创建一个返回平均值 priceaverage 的存储过程，不接受参数。BEGIN 和 END 用来限定存储过程体。

CREATE PROCEDURE productpricing()
BEGIN
  SELECT AVG(prod_price) AS priceaverage
  FROM products;
END

使用 DELIMITER 临时更改语句分隔符，使用 // 作为新的语句结束分隔符：

• 除了 \ 之外，任何字符都可以用作语句分隔符

mysql> DELIMITER //
mysql> CREATE PROCEDURE productpricing()
    -> BEGIN
    ->   SELECT AVG(prod_price) AS priceaverage
    ->   FROM products;
    -> END//
Query OK, 0 rows affected (0.01 sec)

一般，存储过程将结果返回给指定变量。每个参数必须指定数据类型，OUT 表示用于从存储过程传出的值，IN 表示用户传入存储过程的值。

• 存储过程的参数允许的数据类型与表中使用的数据类型相同
• 无法通过一个参数返回多个行和列

mysql> DROP PROCEDURE productpricing//
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE PROCEDURE productpricing(
    -> OUT pl DECIMAL(8,2),
    -> OUT ph DECIMAL(8,2),
    -> OUT pa DECIMAL(8,2)
    -> )
    -> BEGIN
    ->   SELECT MIN(prod_price)
    ->   INTO pl
    ->   FROM products;
    ->   SELECT MAX(prod_price)
    ->   INTO ph
    ->   FROM products;
    ->   SELECT AVG(prod_price)
    ->   INTO pa
    ->   FROM products;
    -> END//
Query OK, 0 rows affected (0.00 sec)

接受订单号并返回该订单的合计：

mysql> CREATE PROCEDURE ordertotal(
    -> IN onumber INT,
    -> OUT ototal DECIMAL(8,2)
    -> )
    -> BEGIN
    ->   SELECT SUM(item_price*quantity)
    ->   FROM orderitems
    ->   WHERE order_num = onumber
    ->   INTO ototal;
    -> END//
Query OK, 0 rows affected (0.01 sec)

CALL

MySQL 称存储过程的执行为调用，因此使用 CALL 调用存储过程，接受存储过程的名字以及参数。调用上一小节创建的第一个存储过程：

mysql> CALL productpricing();
+--------------+
| priceaverage |
+--------------+
|    16.133571 |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

调用需要传递参数的存储过程：

• 用户变量（User-Defined Variables） 以 @ 开头，由字母和 ._$ 构成。

mysql> DELIMITER ;
mysql> CALL productpricing(@pricelow,
    ->                     @pricehigh,
    ->                     @priceaverage);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select @pricelow, @pricehigh, @priceaverage;
+-----------+------------+---------------+
| @pricelow | @pricehigh | @priceaverage |
+-----------+------------+---------------+
|      2.50 |      55.00 |         16.13 |
+-----------+------------+---------------+
1 row in set (0.00 sec)

调用具有输入值的函数：

mysql> CALL ordertotal(20005, @total);
Query OK, 1 row affected (0.00 sec)

mysql> select @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.00 sec)

DROP PROCEDURE

删除存储过程使用 DROP PROCEDURE，给出存储过程名即可，例如：

• 如果存储过程不存在，则会产生一个错误
• 仅当存在时删除使用 DROP PROCEDURE IF EXISTS

mysql> DROP PROCEDURE productpricing;
Query OK, 0 rows affected (0.01 sec)

SHOW CREATE PROCEDURE

显示创建存储过程的 CREATE 语句：

mysql> SHOW CREATE PROCEDURE ordertotal;
+------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure  | sql_mode                                                                                                              | Create Procedure                                                                                                                                                                                             | character_set_client | collation_connection | Database Collation |
+------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| ordertotal | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `ordertotal`(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGIN
  SELECT SUM(item_price*quantity)
  FROM orderitems
  WHERE order_num = onumber
  INTO ototal;
END | latin1               | latin1_swedish_ci    | utf8mb4_0900_ai_ci |
+------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

SHOW PROCEDURE STATUS

SHOW PROCEDURE STATUS 显示所有存储过程的详细信息，可以使用 LIKE 过滤

mysql> SHOW PROCEDURE STATUS LIKE 'ordertotal';
+------+------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name       | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | ordertotal | PROCEDURE | root@localhost | 2020-11-22 13:29:10 | 2020-11-22 13:29:10 | DEFINER       |         | latin1               | latin1_swedish_ci    | utf8mb4_0900_ai_ci |
+------+------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)

智能存储过程

• 获得合计
• 添加营业税
• 返回合计（带税或不带税）

其中，-- 表示注释，DECLARE 定义局部变量（及其数据类型，支持默认值），COMMENT 值将在 SHOW PROCEDURE STATUS 的结果中显示。

-- Name: ordertotal
-- Parameters: onumber = order number
--             taxable = 0 if not taxable, 1 if taxable
--             ototal = order total variable


CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'

BEGIN
  
  -- Declare variable for total
  DECLARE total DECIMAL(8,2);
  -- Declare tax percentage
  DECLARE taxrate INT DEFAULT 6;

  -- Get te order total
  SELECT SUM(item_price*quantity)
  FROM orderitems
  WHERE order_num = onumber
  INTO total;

  -- Is this taxable?
  IF taxable THEN
    -- Yes, so add taxrate to the total
    SELECT total+(total/100*taxrate) INTO total;
  END IF;

  -- And finally, save to out variable
  SELECT total INTO ototal;

END;

先删除再创建 ordertotal 存储过程，注意设置分隔符。不添加营业税：

mysql> CALL ordertotal(20005, 0, @total);
Query OK, 1 row affected (0.00 sec)

mysql> select @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.00 sec)

添加营业税：

mysql> CALL ordertotal(20005, 1, @total);
Query OK, 1 row affected (0.00 sec)

mysql> select @total;
+--------+
| @total |
+--------+
| 158.86 |
+--------+
1 row in set (0.00 sec)

游标

MySQL 检索返回一组称为结果集的行，使用游标（Cursor）可以在检索出的行中前进或后退。游标是存储在 MySQL 服务器上的数据库查询，在存储了游标之后就可以根据需要滚动浏览其中的数据。

MySQL 的游标只能用于存储过程和函数。

创建

使用 DECLARE 命名游标，并定义相应的 SELECT 语句，存储过程处理完成后，游标就消失（仅限于存储过程）。

CREATE PROCEDURE processorders()
BEGIN
  DECLARE ordernumbers CURSOR
  FOR
  SELECT order_num FROM orders;
END;

打开和关闭

使用 OPEN 打开游标：

OPEN ordernumbers;

游标处理完毕后，使用 CLOSE 关闭游标：

• 释放游标使用的所有内部内存和资源
• MySQL 会在存储过程到达 END 语句时自动关闭游标

CLOSE ordernumbers;

使用游标

使用 FETCH 指定检索的数据和存储的位置。

DECLARE 语句的次序

• 局部变量
• 游标
• 句柄

使用游标循环检索数据，指定另一个存储过程计算每个订单的带税合计，并存储到 ordertotals 表中

• CONTINUE HANDLER 是满足条件时被执行的代码
• '02000' 是一个未找到条件，当 REPEAT 由于没有更多的行循环而抛出
• UNTIL done END REPEAT 规定循环结束条件

CREATE PROCEDURE processorders()
BEGIN

  -- Declare local variables
  DECLARE done BOOLEAN DEFAULT 0;
  DECLARE o INT;
  DECLARE t DECIMAL(8,2);
  
  -- Declare the cursor
  DECLARE ordernumbers CURSOR
  FOR
  SELECT order_num FROM orders;

  -- Declare continue handler
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

  -- Open the cursor
  OPEN ordernumbers;

  -- Loop through all rows
  REPEAT

    -- Get order number
    FETCH ordernumbers INTO o;

    -- Get the total for this order
    CALL ordertotal(o, 1, t);

    -- Insert order and total into ordertotals
    INSERT INTO ordertotals(order_num, total)
    VALUES(o, t);

  -- End of loop
  UNTIL done END REPEAT;

  -- Close the cursor
  CLOSE ordernumbers;

END;

创建 ordertotals 表，执行存储过程并查看结果：

mysql> CREATE TABLE ordertotals(
    ->   id int NOT NULL AUTO_INCREMENT,
    ->   order_num int NOT NULL,
    ->   total int NOT NULL,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CALL processorders();
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM ordertotals;
+----+-----------+-------+
| id | order_num | total |
+----+-----------+-------+
|  1 |     20005 |   159 |
|  2 |     20009 |    41 |
|  3 |     20006 |    58 |
|  4 |     20007 |  1060 |
|  5 |     20008 |   133 |
|  6 |     20008 |   133 |
+----+-----------+-------+
6 rows in set (0.00 sec)

触发器（Triggers）

触发器是 MySQL 响应以下任意语句而自动执行的一条或一组 MySQL 语句：

• INSERT
• UPDATE
• DELETE

只有表支持触发器，视图和临时表都不支持。触发器按每个表每个事件每次定义，相同事件次序的触发器默认按照创建时间顺序执行。

使用触发器保证数据的一致性（大小写、格式等），可以用于创建审计跟踪。

创建

创建触发器时，需要给出 4 条信息：

• 唯一的触发器名
  • 每个表中唯一
• 关联的表
• 响应的活动
• 何时执行（活动前或活动后）

使用 CREATE TRIGGER 创建触发器。例如，对 products 表创建一个响应插入的触发器，FOR EACH ROW 表示对每个插入执行：

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added' INTO @tmp;

删除

使用 DROP TRIGGER 删除触发器：

• 触发器不能更新或覆盖，为了修改，只能删除后重新创建

DROP TRIGGER newproduct;

使用

INSERT

• 在 INSERT 触发器内，可以引用一个名为 NEW 的虚拟表，访问被插入的行
• 在 BEFORE INSERT 出发其中，NEW 中的值也可以被更新（允许更改被插入的值）
  • 通常用于数据验证和净化
• 对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行前包含 0 ，在 INSERT 执行后包含新的自动生成值

获得自增量 order_num ：

mysql> CREATE TRIGGER neworder AFTER INSERT ON orders
    -> FOR EACH ROW SELECT NEW.order_num INTO @tmp;
Query OK, 0 rows affected (0.02 sec)

插入数据进行测试：

mysql> INSERT INTO orders(order_date, cust_id)
    -> VALUES(NOW(), 10001);
Query OK, 1 row affected (0.01 sec)

mysql> select @tmp;
+-------+
| @tmp  |
+-------+
| 20010 |
+-------+
1 row in set (0.00 sec)

DELETE

• 在 DELETE 触发器内，可以引用一个名为 OLD 的虚拟表，访问被删除的行
• OLD 中的值全部是只读的

将被删除的订单保存到存档表 archive_orders 中，使用 DEFORE DELETE 保证订单无法存档时，就不执行 DELETE 操作：

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
  INSERT INTO archive_orders(order_num, order_date, cust_id)
  VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END

UPDATE

• 在 UPDATE 触发器内，可以引用一个名为 OLD 的虚拟表，访问旧值，引用一个名为 NEW 的虚拟表，访问新值
• 在 BEFORE UPDATE 出发其中，NEW 中的值也可以被更新（允许更改被更新的值）
• OLD 中的值全部是只读的

保证州名缩写总是大写：

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

事务（TRANSACTION）

事务处理（transaction processing）可以用来维护数据库的完整性，保证成批的 MySQL 语句要么全部执行，要么完全不执行，数据库不会包含不完整的操作结果。事务用来管理 INSERT、UPDATE、 DELETE 语句

相关术语

• 事务（transaction）：一组 SQL 语句
• 回退（rollback）：撤销指定 SQL 语句的过程
• 提交（commit）：将未存储的 SQL 语句结果写入数据库表
• 保留点（savepoint）：指事务处理中设置的临时占位符（placeholder），可以对其发布回退

默认的 MySQL 行为是自动提交所有更改，SET autocommit=0 设置不自动提交更改，针对每个连接（而不是服务器）。

控制事务处理

管理事务处理的关键在于将 SQL 语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。

ROLLBACK

使用 START TRANSACTION 标识事务的开始，ROLLBACK 用来回退（撤销） MySQL 语句

• ROLLBACK 只能在一个事务处理内使用，即执行一条 START TRANSACTION 语句之后
• 不能回退 SELECT、CREATE、 DROP

删除所有行，然后回退：

mysql> SELECT * FROM ordertotals;
+----+-----------+-------+
| id | order_num | total |
+----+-----------+-------+
|  1 |     20005 |   159 |
|  2 |     20009 |    41 |
|  3 |     20006 |    58 |
|  4 |     20007 |  1060 |
|  5 |     20008 |   133 |
|  6 |     20008 |   133 |
+----+-----------+-------+
6 rows in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM ordertotals;
Query OK, 6 rows affected (0.00 sec)

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

mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM ordertotals;
+----+-----------+-------+
| id | order_num | total |
+----+-----------+-------+
|  1 |     20005 |   159 |
|  2 |     20009 |    41 |
|  3 |     20006 |    58 |
|  4 |     20007 |  1060 |
|  5 |     20008 |   133 |
|  6 |     20008 |   133 |
+----+-----------+-------+
6 rows in set (0.00 sec)

COMMIT

隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。在事务处理块中，必须使用 COMMIT 语句显式提交。

隐含事务关闭，当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭

从数据库中完全删除订单 20010 ，涉及两张表，如果有一条 DELETE 语句失败则不会删除：

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM orderitems WHERE order_num= 20010;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM orders WHERE order_num= 20010;
Query OK, 1 row affected (0.01 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.07 sec)

SAVEPOINT

保留点用于支持事务的部分回退，使用 SAVEPOINT 创建占位符（保留点），并且名字唯一：

• 保留点在事务处理完成（执行一条 ROLLBACK 或 COMMIT）后自动释放
• 可使用 RELEASE SAVEPOINT 显式释放

SAVEPOINT delete1

回退到保留点：

ROLLBACK TO delete1

字符集和校对

相关术语

• 字符集：字母和符号的集合
• 编码：某个字符集成员的内部表示
• 校对：规定字符如何比较

MySQL 按照以下顺序使用字符集合校对

• 指定 CHARACTER SET 和 COLLATION
• 只指定 CHARACTER SET ，使用默认 COLLATION
• 二者都没指定，使用数据库默认值

SHOW CHARACTER SET 显示所有可用的字符集以及每个字符集的描述和默认校对：

mysql> SHOW CHARACTER_SET;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER_SET' at line 1
mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

SHOW COLLATION 显示所支持校对的完整列表，显示所有可用的校对，以及适用的字符集：

• 有的字符集不止一种校对

mysql> SHOW COLLATION;
+----------------------------+----------+-----+---------+----------+---------+---------------+
| Collation                  | Charset  | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+----------+-----+---------+----------+---------+---------------+
| armscii8_bin               | armscii8 |  64 |         | Yes      |       1 | PAD SPACE     |
| armscii8_general_ci        | armscii8 |  32 | Yes     | Yes      |       1 | PAD SPACE     |
| ascii_bin                  | ascii    |  65 |         | Yes      |       1 | PAD SPACE     |
| ascii_general_ci           | ascii    |  11 | Yes     | Yes      |       1 | PAD SPACE     |
| big5_bin                   | big5     |  84 |         | Yes      |       1 | PAD SPACE     |
| big5_chinese_ci            | big5     |   1 | Yes     | Yes      |       1 | PAD SPACE     |
| binary                     | binary   |  63 | Yes     | Yes      |       1 | NO PAD        |
| cp1250_bin                 | cp1250   |  66 |         | Yes      |       1 | PAD SPACE     |
| cp1250_croatian_ci         | cp1250   |  44 |         | Yes      |       1 | PAD SPACE     |
| cp1250_czech_cs            | cp1250   |  34 |         | Yes      |       2 | PAD SPACE     |
| cp1250_general_ci          | cp1250   |  26 | Yes     | Yes      |       1 | PAD SPACE     |
| cp1250_polish_ci           | cp1250   |  99 |         | Yes      |       1 | PAD SPACE     |
| cp1251_bin                 | cp1251   |  50 |         | Yes      |       1 | PAD SPACE     |
| cp1251_bulgarian_ci        | cp1251   |  14 |         | Yes      |       1 | PAD SPACE     |
| cp1251_general_ci          | cp1251   |  51 | Yes     | Yes      |       1 | PAD SPACE     |
| cp1251_general_cs          | cp1251   |  52 |         | Yes      |       1 | PAD SPACE     |
| cp1251_ukrainian_ci        | cp1251   |  23 |         | Yes      |       1 | PAD SPACE     |
| cp1256_bin                 | cp1256   |  67 |         | Yes      |       1 | PAD SPACE     |
| cp1256_general_ci          | cp1256   |  57 | Yes     | Yes      |       1 | PAD SPACE     |
| cp1257_bin                 | cp1257   |  58 |         | Yes      |       1 | PAD SPACE     |
| cp1257_general_ci          | cp1257   |  59 | Yes     | Yes      |       1 | PAD SPACE     |
| cp1257_lithuanian_ci       | cp1257   |  29 |         | Yes      |       1 | PAD SPACE     |
| cp850_bin                  | cp850    |  80 |         | Yes      |       1 | PAD SPACE     |
| cp850_general_ci           | cp850    |   4 | Yes     | Yes      |       1 | PAD SPACE     |
| cp852_bin                  | cp852    |  81 |         | Yes      |       1 | PAD SPACE     |
| cp852_general_ci           | cp852    |  40 | Yes     | Yes      |       1 | PAD SPACE     |
| cp866_bin                  | cp866    |  68 |         | Yes      |       1 | PAD SPACE     |
| cp866_general_ci           | cp866    |  36 | Yes     | Yes      |       1 | PAD SPACE     |
| cp932_bin                  | cp932    |  96 |         | Yes      |       1 | PAD SPACE     |
| cp932_japanese_ci          | cp932    |  95 | Yes     | Yes      |       1 | PAD SPACE     |
| dec8_bin                   | dec8     |  69 |         | Yes      |       1 | PAD SPACE     |
| dec8_swedish_ci            | dec8     |   3 | Yes     | Yes      |       1 | PAD SPACE     |
| eucjpms_bin                | eucjpms  |  98 |         | Yes      |       1 | PAD SPACE     |
| eucjpms_japanese_ci        | eucjpms  |  97 | Yes     | Yes      |       1 | PAD SPACE     |
| euckr_bin                  | euckr    |  85 |         | Yes      |       1 | PAD SPACE     |
| euckr_korean_ci            | euckr    |  19 | Yes     | Yes      |       1 | PAD SPACE     |
| gb18030_bin                | gb18030  | 249 |         | Yes      |       1 | PAD SPACE     |
| gb18030_chinese_ci         | gb18030  | 248 | Yes     | Yes      |       2 | PAD SPACE     |
| gb18030_unicode_520_ci     | gb18030  | 250 |         | Yes      |       8 | PAD SPACE     |
| gb2312_bin                 | gb2312   |  86 |         | Yes      |       1 | PAD SPACE     |
| gb2312_chinese_ci          | gb2312   |  24 | Yes     | Yes      |       1 | PAD SPACE     |
| gbk_bin                    | gbk      |  87 |         | Yes      |       1 | PAD SPACE     |
| gbk_chinese_ci             | gbk      |  28 | Yes     | Yes      |       1 | PAD SPACE     |
| geostd8_bin                | geostd8  |  93 |         | Yes      |       1 | PAD SPACE     |
| geostd8_general_ci         | geostd8  |  92 | Yes     | Yes      |       1 | PAD SPACE     |
| greek_bin                  | greek    |  70 |         | Yes      |       1 | PAD SPACE     |
| greek_general_ci           | greek    |  25 | Yes     | Yes      |       1 | PAD SPACE     |
| hebrew_bin                 | hebrew   |  71 |         | Yes      |       1 | PAD SPACE     |
| hebrew_general_ci          | hebrew   |  16 | Yes     | Yes      |       1 | PAD SPACE     |
| hp8_bin                    | hp8      |  72 |         | Yes      |       1 | PAD SPACE     |
| hp8_english_ci             | hp8      |   6 | Yes     | Yes      |       1 | PAD SPACE     |
| keybcs2_bin                | keybcs2  |  73 |         | Yes      |       1 | PAD SPACE     |
| keybcs2_general_ci         | keybcs2  |  37 | Yes     | Yes      |       1 | PAD SPACE     |
| koi8r_bin                  | koi8r    |  74 |         | Yes      |       1 | PAD SPACE     |
| koi8r_general_ci           | koi8r    |   7 | Yes     | Yes      |       1 | PAD SPACE     |
| koi8u_bin                  | koi8u    |  75 |         | Yes      |       1 | PAD SPACE     |
| koi8u_general_ci           | koi8u    |  22 | Yes     | Yes      |       1 | PAD SPACE     |
| latin1_bin                 | latin1   |  47 |         | Yes      |       1 | PAD SPACE     |
| latin1_danish_ci           | latin1   |  15 |         | Yes      |       1 | PAD SPACE     |
| latin1_general_ci          | latin1   |  48 |         | Yes      |       1 | PAD SPACE     |
| latin1_general_cs          | latin1   |  49 |         | Yes      |       1 | PAD SPACE     |
| latin1_german1_ci          | latin1   |   5 |         | Yes      |       1 | PAD SPACE     |
| latin1_german2_ci          | latin1   |  31 |         | Yes      |       2 | PAD SPACE     |
| latin1_spanish_ci          | latin1   |  94 |         | Yes      |       1 | PAD SPACE     |
| latin1_swedish_ci          | latin1   |   8 | Yes     | Yes      |       1 | PAD SPACE     |
| latin2_bin                 | latin2   |  77 |         | Yes      |       1 | PAD SPACE     |
| latin2_croatian_ci         | latin2   |  27 |         | Yes      |       1 | PAD SPACE     |
| latin2_czech_cs            | latin2   |   2 |         | Yes      |       4 | PAD SPACE     |
| latin2_general_ci          | latin2   |   9 | Yes     | Yes      |       1 | PAD SPACE     |
| latin2_hungarian_ci        | latin2   |  21 |         | Yes      |       1 | PAD SPACE     |
| latin5_bin                 | latin5   |  78 |         | Yes      |       1 | PAD SPACE     |
| latin5_turkish_ci          | latin5   |  30 | Yes     | Yes      |       1 | PAD SPACE     |
| latin7_bin                 | latin7   |  79 |         | Yes      |       1 | PAD SPACE     |
| latin7_estonian_cs         | latin7   |  20 |         | Yes      |       1 | PAD SPACE     |
| latin7_general_ci          | latin7   |  41 | Yes     | Yes      |       1 | PAD SPACE     |
| latin7_general_cs          | latin7   |  42 |         | Yes      |       1 | PAD SPACE     |
| macce_bin                  | macce    |  43 |         | Yes      |       1 | PAD SPACE     |
| macce_general_ci           | macce    |  38 | Yes     | Yes      |       1 | PAD SPACE     |
| macroman_bin               | macroman |  53 |         | Yes      |       1 | PAD SPACE     |
| macroman_general_ci        | macroman |  39 | Yes     | Yes      |       1 | PAD SPACE     |
| sjis_bin                   | sjis     |  88 |         | Yes      |       1 | PAD SPACE     |
| sjis_japanese_ci           | sjis     |  13 | Yes     | Yes      |       1 | PAD SPACE     |
| swe7_bin                   | swe7     |  82 |         | Yes      |       1 | PAD SPACE     |
| swe7_swedish_ci            | swe7     |  10 | Yes     | Yes      |       1 | PAD SPACE     |
| tis620_bin                 | tis620   |  89 |         | Yes      |       1 | PAD SPACE     |
| tis620_thai_ci             | tis620   |  18 | Yes     | Yes      |       4 | PAD SPACE     |
| ucs2_bin                   | ucs2     |  90 |         | Yes      |       1 | PAD SPACE     |
| ucs2_croatian_ci           | ucs2     | 149 |         | Yes      |       8 | PAD SPACE     |
| ucs2_czech_ci              | ucs2     | 138 |         | Yes      |       8 | PAD SPACE     |
| ucs2_danish_ci             | ucs2     | 139 |         | Yes      |       8 | PAD SPACE     |
| ucs2_esperanto_ci          | ucs2     | 145 |         | Yes      |       8 | PAD SPACE     |
| ucs2_estonian_ci           | ucs2     | 134 |         | Yes      |       8 | PAD SPACE     |
| ucs2_general_ci            | ucs2     |  35 | Yes     | Yes      |       1 | PAD SPACE     |
| ucs2_general_mysql500_ci   | ucs2     | 159 |         | Yes      |       1 | PAD SPACE     |
| ucs2_german2_ci            | ucs2     | 148 |         | Yes      |       8 | PAD SPACE     |
| ucs2_hungarian_ci          | ucs2     | 146 |         | Yes      |       8 | PAD SPACE     |
| ucs2_icelandic_ci          | ucs2     | 129 |         | Yes      |       8 | PAD SPACE     |
| ucs2_latvian_ci            | ucs2     | 130 |         | Yes      |       8 | PAD SPACE     |
| ucs2_lithuanian_ci         | ucs2     | 140 |         | Yes      |       8 | PAD SPACE     |
| ucs2_persian_ci            | ucs2     | 144 |         | Yes      |       8 | PAD SPACE     |
| ucs2_polish_ci             | ucs2     | 133 |         | Yes      |       8 | PAD SPACE     |
| ucs2_romanian_ci           | ucs2     | 131 |         | Yes      |       8 | PAD SPACE     |
| ucs2_roman_ci              | ucs2     | 143 |         | Yes      |       8 | PAD SPACE     |
| ucs2_sinhala_ci            | ucs2     | 147 |         | Yes      |       8 | PAD SPACE     |
| ucs2_slovak_ci             | ucs2     | 141 |         | Yes      |       8 | PAD SPACE     |
| ucs2_slovenian_ci          | ucs2     | 132 |         | Yes      |       8 | PAD SPACE     |
| ucs2_spanish2_ci           | ucs2     | 142 |         | Yes      |       8 | PAD SPACE     |
| ucs2_spanish_ci            | ucs2     | 135 |         | Yes      |       8 | PAD SPACE     |
| ucs2_swedish_ci            | ucs2     | 136 |         | Yes      |       8 | PAD SPACE     |
| ucs2_turkish_ci            | ucs2     | 137 |         | Yes      |       8 | PAD SPACE     |
| ucs2_unicode_520_ci        | ucs2     | 150 |         | Yes      |       8 | PAD SPACE     |
| ucs2_unicode_ci            | ucs2     | 128 |         | Yes      |       8 | PAD SPACE     |
| ucs2_vietnamese_ci         | ucs2     | 151 |         | Yes      |       8 | PAD SPACE     |
| ujis_bin                   | ujis     |  91 |         | Yes      |       1 | PAD SPACE     |
| ujis_japanese_ci           | ujis     |  12 | Yes     | Yes      |       1 | PAD SPACE     |
| utf16le_bin                | utf16le  |  62 |         | Yes      |       1 | PAD SPACE     |
| utf16le_general_ci         | utf16le  |  56 | Yes     | Yes      |       1 | PAD SPACE     |
| utf16_bin                  | utf16    |  55 |         | Yes      |       1 | PAD SPACE     |
| utf16_croatian_ci          | utf16    | 122 |         | Yes      |       8 | PAD SPACE     |
| utf16_czech_ci             | utf16    | 111 |         | Yes      |       8 | PAD SPACE     |
| utf16_danish_ci            | utf16    | 112 |         | Yes      |       8 | PAD SPACE     |
| utf16_esperanto_ci         | utf16    | 118 |         | Yes      |       8 | PAD SPACE     |
| utf16_estonian_ci          | utf16    | 107 |         | Yes      |       8 | PAD SPACE     |
| utf16_general_ci           | utf16    |  54 | Yes     | Yes      |       1 | PAD SPACE     |
| utf16_german2_ci           | utf16    | 121 |         | Yes      |       8 | PAD SPACE     |
| utf16_hungarian_ci         | utf16    | 119 |         | Yes      |       8 | PAD SPACE     |
| utf16_icelandic_ci         | utf16    | 102 |         | Yes      |       8 | PAD SPACE     |
| utf16_latvian_ci           | utf16    | 103 |         | Yes      |       8 | PAD SPACE     |
| utf16_lithuanian_ci        | utf16    | 113 |         | Yes      |       8 | PAD SPACE     |
| utf16_persian_ci           | utf16    | 117 |         | Yes      |       8 | PAD SPACE     |
| utf16_polish_ci            | utf16    | 106 |         | Yes      |       8 | PAD SPACE     |
| utf16_romanian_ci          | utf16    | 104 |         | Yes      |       8 | PAD SPACE     |
| utf16_roman_ci             | utf16    | 116 |         | Yes      |       8 | PAD SPACE     |
| utf16_sinhala_ci           | utf16    | 120 |         | Yes      |       8 | PAD SPACE     |
| utf16_slovak_ci            | utf16    | 114 |         | Yes      |       8 | PAD SPACE     |
| utf16_slovenian_ci         | utf16    | 105 |         | Yes      |       8 | PAD SPACE     |
| utf16_spanish2_ci          | utf16    | 115 |         | Yes      |       8 | PAD SPACE     |
| utf16_spanish_ci           | utf16    | 108 |         | Yes      |       8 | PAD SPACE     |
| utf16_swedish_ci           | utf16    | 109 |         | Yes      |       8 | PAD SPACE     |
| utf16_turkish_ci           | utf16    | 110 |         | Yes      |       8 | PAD SPACE     |
| utf16_unicode_520_ci       | utf16    | 123 |         | Yes      |       8 | PAD SPACE     |
| utf16_unicode_ci           | utf16    | 101 |         | Yes      |       8 | PAD SPACE     |
| utf16_vietnamese_ci        | utf16    | 124 |         | Yes      |       8 | PAD SPACE     |
| utf32_bin                  | utf32    |  61 |         | Yes      |       1 | PAD SPACE     |
| utf32_croatian_ci          | utf32    | 181 |         | Yes      |       8 | PAD SPACE     |
| utf32_czech_ci             | utf32    | 170 |         | Yes      |       8 | PAD SPACE     |
| utf32_danish_ci            | utf32    | 171 |         | Yes      |       8 | PAD SPACE     |
| utf32_esperanto_ci         | utf32    | 177 |         | Yes      |       8 | PAD SPACE     |
| utf32_estonian_ci          | utf32    | 166 |         | Yes      |       8 | PAD SPACE     |
| utf32_general_ci           | utf32    |  60 | Yes     | Yes      |       1 | PAD SPACE     |
| utf32_german2_ci           | utf32    | 180 |         | Yes      |       8 | PAD SPACE     |
| utf32_hungarian_ci         | utf32    | 178 |         | Yes      |       8 | PAD SPACE     |
| utf32_icelandic_ci         | utf32    | 161 |         | Yes      |       8 | PAD SPACE     |
| utf32_latvian_ci           | utf32    | 162 |         | Yes      |       8 | PAD SPACE     |
| utf32_lithuanian_ci        | utf32    | 172 |         | Yes      |       8 | PAD SPACE     |
| utf32_persian_ci           | utf32    | 176 |         | Yes      |       8 | PAD SPACE     |
| utf32_polish_ci            | utf32    | 165 |         | Yes      |       8 | PAD SPACE     |
| utf32_romanian_ci          | utf32    | 163 |         | Yes      |       8 | PAD SPACE     |
| utf32_roman_ci             | utf32    | 175 |         | Yes      |       8 | PAD SPACE     |
| utf32_sinhala_ci           | utf32    | 179 |         | Yes      |       8 | PAD SPACE     |
| utf32_slovak_ci            | utf32    | 173 |         | Yes      |       8 | PAD SPACE     |
| utf32_slovenian_ci         | utf32    | 164 |         | Yes      |       8 | PAD SPACE     |
| utf32_spanish2_ci          | utf32    | 174 |         | Yes      |       8 | PAD SPACE     |
| utf32_spanish_ci           | utf32    | 167 |         | Yes      |       8 | PAD SPACE     |
| utf32_swedish_ci           | utf32    | 168 |         | Yes      |       8 | PAD SPACE     |
| utf32_turkish_ci           | utf32    | 169 |         | Yes      |       8 | PAD SPACE     |
| utf32_unicode_520_ci       | utf32    | 182 |         | Yes      |       8 | PAD SPACE     |
| utf32_unicode_ci           | utf32    | 160 |         | Yes      |       8 | PAD SPACE     |
| utf32_vietnamese_ci        | utf32    | 183 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_0900_ai_ci         | utf8mb4  | 255 | Yes     | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_ci         | utf8mb4  | 305 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_cs         | utf8mb4  | 278 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_bin           | utf8mb4  | 309 |         | Yes      |       1 | NO PAD        |
| utf8mb4_bin                | utf8mb4  |  46 |         | Yes      |       1 | PAD SPACE     |
| utf8mb4_croatian_ci        | utf8mb4  | 245 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_cs_0900_ai_ci      | utf8mb4  | 266 |         | Yes      |       0 | NO PAD        |
| utf8mb4_cs_0900_as_cs      | utf8mb4  | 289 |         | Yes      |       0 | NO PAD        |
| utf8mb4_czech_ci           | utf8mb4  | 234 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_danish_ci          | utf8mb4  | 235 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_da_0900_ai_ci      | utf8mb4  | 267 |         | Yes      |       0 | NO PAD        |
| utf8mb4_da_0900_as_cs      | utf8mb4  | 290 |         | Yes      |       0 | NO PAD        |
| utf8mb4_de_pb_0900_ai_ci   | utf8mb4  | 256 |         | Yes      |       0 | NO PAD        |
| utf8mb4_de_pb_0900_as_cs   | utf8mb4  | 279 |         | Yes      |       0 | NO PAD        |
| utf8mb4_eo_0900_ai_ci      | utf8mb4  | 273 |         | Yes      |       0 | NO PAD        |
| utf8mb4_eo_0900_as_cs      | utf8mb4  | 296 |         | Yes      |       0 | NO PAD        |
| utf8mb4_esperanto_ci       | utf8mb4  | 241 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_estonian_ci        | utf8mb4  | 230 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_es_0900_ai_ci      | utf8mb4  | 263 |         | Yes      |       0 | NO PAD        |
| utf8mb4_es_0900_as_cs      | utf8mb4  | 286 |         | Yes      |       0 | NO PAD        |
| utf8mb4_es_trad_0900_ai_ci | utf8mb4  | 270 |         | Yes      |       0 | NO PAD        |
| utf8mb4_es_trad_0900_as_cs | utf8mb4  | 293 |         | Yes      |       0 | NO PAD        |
| utf8mb4_et_0900_ai_ci      | utf8mb4  | 262 |         | Yes      |       0 | NO PAD        |
| utf8mb4_et_0900_as_cs      | utf8mb4  | 285 |         | Yes      |       0 | NO PAD        |
| utf8mb4_general_ci         | utf8mb4  |  45 |         | Yes      |       1 | PAD SPACE     |
| utf8mb4_german2_ci         | utf8mb4  | 244 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_hr_0900_ai_ci      | utf8mb4  | 275 |         | Yes      |       0 | NO PAD        |
| utf8mb4_hr_0900_as_cs      | utf8mb4  | 298 |         | Yes      |       0 | NO PAD        |
| utf8mb4_hungarian_ci       | utf8mb4  | 242 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_hu_0900_ai_ci      | utf8mb4  | 274 |         | Yes      |       0 | NO PAD        |
| utf8mb4_hu_0900_as_cs      | utf8mb4  | 297 |         | Yes      |       0 | NO PAD        |
| utf8mb4_icelandic_ci       | utf8mb4  | 225 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_is_0900_ai_ci      | utf8mb4  | 257 |         | Yes      |       0 | NO PAD        |
| utf8mb4_is_0900_as_cs      | utf8mb4  | 280 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ja_0900_as_cs      | utf8mb4  | 303 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ja_0900_as_cs_ks   | utf8mb4  | 304 |         | Yes      |      24 | NO PAD        |
| utf8mb4_latvian_ci         | utf8mb4  | 226 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_la_0900_ai_ci      | utf8mb4  | 271 |         | Yes      |       0 | NO PAD        |
| utf8mb4_la_0900_as_cs      | utf8mb4  | 294 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lithuanian_ci      | utf8mb4  | 236 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_lt_0900_ai_ci      | utf8mb4  | 268 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lt_0900_as_cs      | utf8mb4  | 291 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lv_0900_ai_ci      | utf8mb4  | 258 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lv_0900_as_cs      | utf8mb4  | 281 |         | Yes      |       0 | NO PAD        |
| utf8mb4_persian_ci         | utf8mb4  | 240 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_pl_0900_ai_ci      | utf8mb4  | 261 |         | Yes      |       0 | NO PAD        |
| utf8mb4_pl_0900_as_cs      | utf8mb4  | 284 |         | Yes      |       0 | NO PAD        |
| utf8mb4_polish_ci          | utf8mb4  | 229 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_romanian_ci        | utf8mb4  | 227 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_roman_ci           | utf8mb4  | 239 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_ro_0900_ai_ci      | utf8mb4  | 259 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ro_0900_as_cs      | utf8mb4  | 282 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ru_0900_ai_ci      | utf8mb4  | 306 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ru_0900_as_cs      | utf8mb4  | 307 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sinhala_ci         | utf8mb4  | 243 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_sk_0900_ai_ci      | utf8mb4  | 269 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sk_0900_as_cs      | utf8mb4  | 292 |         | Yes      |       0 | NO PAD        |
| utf8mb4_slovak_ci          | utf8mb4  | 237 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_slovenian_ci       | utf8mb4  | 228 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_sl_0900_ai_ci      | utf8mb4  | 260 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sl_0900_as_cs      | utf8mb4  | 283 |         | Yes      |       0 | NO PAD        |
| utf8mb4_spanish2_ci        | utf8mb4  | 238 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_spanish_ci         | utf8mb4  | 231 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_sv_0900_ai_ci      | utf8mb4  | 264 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sv_0900_as_cs      | utf8mb4  | 287 |         | Yes      |       0 | NO PAD        |
| utf8mb4_swedish_ci         | utf8mb4  | 232 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_tr_0900_ai_ci      | utf8mb4  | 265 |         | Yes      |       0 | NO PAD        |
| utf8mb4_tr_0900_as_cs      | utf8mb4  | 288 |         | Yes      |       0 | NO PAD        |
| utf8mb4_turkish_ci         | utf8mb4  | 233 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_unicode_520_ci     | utf8mb4  | 246 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_unicode_ci         | utf8mb4  | 224 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_vietnamese_ci      | utf8mb4  | 247 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_vi_0900_ai_ci      | utf8mb4  | 277 |         | Yes      |       0 | NO PAD        |
| utf8mb4_vi_0900_as_cs      | utf8mb4  | 300 |         | Yes      |       0 | NO PAD        |
| utf8mb4_zh_0900_as_cs      | utf8mb4  | 308 |         | Yes      |       0 | NO PAD        |
| utf8_bin                   | utf8     |  83 |         | Yes      |       1 | PAD SPACE     |
| utf8_croatian_ci           | utf8     | 213 |         | Yes      |       8 | PAD SPACE     |
| utf8_czech_ci              | utf8     | 202 |         | Yes      |       8 | PAD SPACE     |
| utf8_danish_ci             | utf8     | 203 |         | Yes      |       8 | PAD SPACE     |
| utf8_esperanto_ci          | utf8     | 209 |         | Yes      |       8 | PAD SPACE     |
| utf8_estonian_ci           | utf8     | 198 |         | Yes      |       8 | PAD SPACE     |
| utf8_general_ci            | utf8     |  33 | Yes     | Yes      |       1 | PAD SPACE     |
| utf8_general_mysql500_ci   | utf8     | 223 |         | Yes      |       1 | PAD SPACE     |
| utf8_german2_ci            | utf8     | 212 |         | Yes      |       8 | PAD SPACE     |
| utf8_hungarian_ci          | utf8     | 210 |         | Yes      |       8 | PAD SPACE     |
| utf8_icelandic_ci          | utf8     | 193 |         | Yes      |       8 | PAD SPACE     |
| utf8_latvian_ci            | utf8     | 194 |         | Yes      |       8 | PAD SPACE     |
| utf8_lithuanian_ci         | utf8     | 204 |         | Yes      |       8 | PAD SPACE     |
| utf8_persian_ci            | utf8     | 208 |         | Yes      |       8 | PAD SPACE     |
| utf8_polish_ci             | utf8     | 197 |         | Yes      |       8 | PAD SPACE     |
| utf8_romanian_ci           | utf8     | 195 |         | Yes      |       8 | PAD SPACE     |
| utf8_roman_ci              | utf8     | 207 |         | Yes      |       8 | PAD SPACE     |
| utf8_sinhala_ci            | utf8     | 211 |         | Yes      |       8 | PAD SPACE     |
| utf8_slovak_ci             | utf8     | 205 |         | Yes      |       8 | PAD SPACE     |
| utf8_slovenian_ci          | utf8     | 196 |         | Yes      |       8 | PAD SPACE     |
| utf8_spanish2_ci           | utf8     | 206 |         | Yes      |       8 | PAD SPACE     |
| utf8_spanish_ci            | utf8     | 199 |         | Yes      |       8 | PAD SPACE     |
| utf8_swedish_ci            | utf8     | 200 |         | Yes      |       8 | PAD SPACE     |
| utf8_tolower_ci            | utf8     |  76 |         | Yes      |       1 | PAD SPACE     |
| utf8_turkish_ci            | utf8     | 201 |         | Yes      |       8 | PAD SPACE     |
| utf8_unicode_520_ci        | utf8     | 214 |         | Yes      |       8 | PAD SPACE     |
| utf8_unicode_ci            | utf8     | 192 |         | Yes      |       8 | PAD SPACE     |
| utf8_vietnamese_ci         | utf8     | 215 |         | Yes      |       8 | PAD SPACE     |
+----------------------------+----------+-----+---------+----------+---------+---------------+
272 rows in set (0.01 sec)

查看使用的字符集合校对：

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | latin1                         |
| character_set_connection | latin1                         |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | latin1                         |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8                           |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | latin1_swedish_ci  |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

给表指定字符集和校对：

mysql> CREATE TABLE mytable
    -> (
    ->   column1 INT,
    ->   column2 VARCHAR(10)
    -> ) DEFAULT CHARACTER SET hebrew
    ->   COLLATE hebrew_general_ci;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE mytable;
+---------+------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                       |
+---------+------------------------------------------------------------------------------------------------------------------------------------+
| mytable | CREATE TABLE `mytable` (
  `column1` int DEFAULT NULL,
  `column2` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=hebrew |
+---------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

给列指定字符集和校对：

mysql> CREATE TABLE mytable2(
    ->   column1 INT,
    ->   column2 VARCHAR(10),
    ->   column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
    -> ) DEFAULT CHARACTER SET hebrew
    ->   COLLATE hebrew_general_ci;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE mytable2;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                             |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mytable2 | CREATE TABLE `mytable2` (
  `column1` int DEFAULT NULL,
  `column2` varchar(10) DEFAULT NULL,
  `column3` varchar(10) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=hebrew |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

使用 COLLATE 指定一个备用的校对顺序，临时区分大小写：

mysql> SELECT * FROM customers
    -> ORDER BY cust_name, cust_address COLLATE utf8mb4_general_ci;
+---------+----------------+---------------------+------------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name      | cust_address        | cust_city  | cust_state | cust_zip | cust_country | cust_contact | cust_email          |
+---------+----------------+---------------------+------------+------------+----------+--------------+--------------+---------------------+
|   10001 | Coyote Inc.    | 200 Maple Lane      | Detroit    | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com     |
|   10009 | M. Martian     | 42 Galaxy Way       | New York   | NY         | 11213    | USA          | NULL         | NULL                |
|   10002 | Mouse House    | 333 Fromage Lane    | Columbus   | OH         | 43333    | USA          | Jerry Mouse  | NULL                |
|   10007 | Pep E. LaPew   | 100 Main Street     | Los Angles | CA         | 90046    | USA          | NULL         | NULL                |
|   10008 | Pep E. LaPew   | 100 Main Street     | Los Angles | CA         | 90046    | USA          | NULL         | NULL                |
|   10005 | The Fudds      | 4545 53rd Street    | Chicago    | IL         | 54545    | USA          | E Fudd       | NULL                |
|   10003 | Wascals        | 1 Sunny Place       | Muncie     | IN         | 42222    | USA          | Jim Jones    | rabbit@wascally.com |
|   10004 | Yosemite Place | 829 Riverside Drive | Phoenix    | AZ         | 88888    | USA          | Y Sam        | sam@yosemite.com    |
+---------+----------------+---------------------+------------+------------+----------+--------------+--------------+---------------------+
8 rows in set (0.00 sec)

访问控制（Access Control）

MySQL 服务器的安全基础是：用户应该对他们需要的数据具有适当的访问权，既不能多也不能少。

• 最小权限原则

MySQL 用户账号和信息存储在名为 mysql 的数据库中，其中有一个名为 user 的表，包含所有用户账号：

mysql> USE mysql;
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> SELECT user FROM user;
+------------------+
| user             |
+------------------+
| root             |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
5 rows in set (0.00 sec)

用户账号

使用 CREATE USER 语句创建用户，并用 IDENTIFIED 设置密码：

• 密码以散列值的形式存储在数据库中

mysql> CREATE USER ben IDENTIFIED BY 'p@$$w0rd';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT user FROM mysql.user;
+------------------+
| user             |
+------------------+
| ben              |
| root             |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
6 rows in set (0.00 sec)

使用 RENAME USER 重命名用户：

mysql> RENAME USER ben TO cat;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT user FROM mysql.user;
+------------------+
| user             |
+------------------+
| cat              |
| root             |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
6 rows in set (0.00 sec)

使用 SET PASSWORD 更改口令，mysql 数据库 user 表的 authentication_string 是密码经过哈希处理的结果：

• 不指定用户名时，默认更新当前登录用户的口令：SET PASSWORD = 'n3w p@$$w0rd';

mysql> select user,authentication_string from mysql.user where user LIKE 'cat';
+------+------------------------------------------------------------------------+
| user | authentication_string                                                  |
+------+------------------------------------------------------------------------+
| cat  | $A$005$g1*ZeB!vr)F:2`E;dUfKlOQ.F3Q8469sRKIvvfAccTP8SlR627FYnTStLj.n2 |
+------+------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET PASSWORD FOR cat = 'n3w p@$$w0rd';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,authentication_string from mysql.user where user LIKE 'cat';
+------+------------------------------------------------------------------------+
| user | authentication_string                                                  |
+------+------------------------------------------------------------------------+
| cat  | $A$005$.'r;
                    Go^j0wm+wf6rpiUsskXasKT3FpbPDJcrJmF28N21dj1u2JfVZok8 |
+------+------------------------------------------------------------------------+
1 row in set (0.00 sec)

使用 DROP USER 删除用户账号（以及相关的权限）：

mysql> DROP USER cat;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT user FROM mysql.user;
+------------------+
| user             |
+------------------+
| root             |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
5 rows in set (0.00 sec)

访问权限

使用 SHOW GRANTS FOR 查看授予用户的权限：

• USAGE 表示没有对任意数据库和数据表都没有权限
• MySQL 的权限用用户名和主机名结合定义，如果不指定主机名，则使用默认的主机名 %

mysql> SHOW GRANTS FOR cat;
+---------------------------------+
| Grants for cat@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `cat`@`%` |
+---------------------------------+
1 row in set (0.00 sec)

使用 GRANT 语句设置权限，授予 cat 用户对 crashcourse 数据库中所有数据的只读权限，再次查看权限：

mysql> GRANT SELECT ON crashcourse.* TO cat;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR cat;
+----------------------------------------------+
| Grants for cat@%                             |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `cat`@`%`              |
| GRANT SELECT ON `crashcourse`.* TO `cat`@`%` |
+----------------------------------------------+
2 rows in set (0.00 sec)

REVOKE 用于撤销特定的权限，撤销刚才授予 cat 用户的权限：

mysql> REVOKE SELECT ON crashcourse.* FROM cat;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR cat;
+---------------------------------+
| Grants for cat@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `cat`@`%` |
+---------------------------------+
1 row in set (0.00 sec)

GRANT 和 REVOKE 可以在几个层次上控制访问权限

• 服务器（server），使用 GRANT ALL 和 REVOKE ALL
• 数据库（database），使用 ON database.*
• 表（table），使用 ON database.table
• 列（column）
• 存储过程（procedure）

MySQL 提供的权限

数据库维护

备份与恢复（Backup and Recovery）

1. 使用 mysqldump 转储所有数据库内容到某个外部文件
2. 使用 mysqlhotcopy 从一个数据库复制所有数据
3. 使用 MySQL 的 BACKUP TABLE 或 SELECT INTO OUTFILE 转储所有数据到某个外部文件

为了保证所有数据被写到磁盘（包括索引数据），可能需要在进行备份前使用 FLUSH TABLES 语句。

维护

ANALYZE TABLE 用来检查表键是否正确：

mysql> ANALYZE TABLE orders;
+--------------+---------+----------+------------------------------------+
| Table        | Op      | Msg_type | Msg_text                           |
+--------------+---------+----------+------------------------------------+
| mysql.orders | analyze | Error    | Table 'mysql.orders' doesn't exist |
| mysql.orders | analyze | status   | Operation failed                   |
+--------------+---------+----------+------------------------------------+
2 rows in set (0.00 sec)

CHECK TABLE 检查一个或多个表的错误，还可以检查视图是否存在问题，比如视图定义中引用的表不再存在：

mysql> CHECK TABLE orders, orderitems;
+------------------+-------+----------+----------------------------------------+
| Table            | Op    | Msg_type | Msg_text                               |
+------------------+-------+----------+----------------------------------------+
| mysql.orders     | check | Error    | Table 'mysql.orders' doesn't exist     |
| mysql.orders     | check | status   | Operation failed                       |
| mysql.orderitems | check | Error    | Table 'mysql.orderitems' doesn't exist |
| mysql.orderitems | check | status   | Operation failed                       |
+------------------+-------+----------+----------------------------------------+
4 rows in set (0.00 sec)

REPAIR TABLE 针对某些存储引擎修复可能损坏的表。

• MyISAM 表访问产生不正确和不一致的结果

OPTIMIZE TABLE 重新组织表数据和相关索引数据的物理存储，以减少存储空间并提高访问表时的 I/O 效率。对每个表所做的确切更改取决于该表所使用的存储引擎。

• 从一个表中删除大量数据，用以回收空间，从而优化表的性能

诊断启动问题

服务器启动问题通常在对 MySQL 配置或服务器本身进行更改时出现，MySQL 服务器自身通过在命令行上执行 mysqld 启动，具体选项使用 --help 参数查看即可。

日志文件

MySQL 服务器日志 主要有以下几种：错误日志、查询日志、二进制日志、缓慢查询日志。

使用日志时，可以使用 FLUSH LOGS 语句刷新和重新开始所有日志文件。

改善性能

1. 硬件建议
2. 专用服务器
3. 修改默认设置
4. 多用户多线程 DBMS ，可以使用 SHOW PROCESSLIST 显示所有活动进程
5. SELECT 语句有多种写法
6. 使用 EXPLAIN 语句解释 SELECT 语句的执行过程
7. 通常，存储过程比一条一条执行 MySQL 语句快
8. 正确的数据类型
9. 绝不检索比需求更多的数据
10. DELAYED 关键字
11. 导入数据时，关闭自动提交
12. 索引数据库表以改善检索性能
13. 索引影响插入删除更新的性能
14. 一般来说，最好使用 FULLTEXT 而不是 LIKE
15. 数据库是动态实体，需要动态调优
16. 每条规则在某些条件下都会被打破