MySQL关于查询语句使用的一些关键字

菠萝蜜blm

已于 2024-04-28 16:12:46 修改

阅读量1.7k

点赞数 25

分类专栏：一起成为数据库高手文章标签：数据库 mysql

于 2024-04-21 18:56:36 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_46984703/article/details/138039271

版权

一起成为数据库高手专栏收录该内容

32 篇文章

订阅专栏

1. MySQL基础

1.1. 查询语句

1.1.1. SELECT

SELECT 语法

SELECT columns_list
FROM table_name;

- SELECT 就是进行查询操作，后可以跟着一个或多个数据表的字段。

SELECT 语句的正确语义应是 FROM table_name SELECT columns_list，即：从某个表检索某几列数据。MySQL 解析 SELECT 语句的时候，会首先评估 FROM 子句，再评估 SELECT 子句。

在 MySQL 中，要查询的数据不存在于任何表中，这时可以省略 FROM 子句。语法如下：

SELECT expression_list

例如：

SELECT NOW();

// 输出结果
+---------------------+
| NOW()               |
+---------------------+
| 2021-09-07 22:45:33 |
+---------------------+
1 row in set (0.00 sec)

SELECT 1+2;

//输出结果
+-----+
| 1+2 |
+-----+
|   3 |
+-----+
1 row in set (0.00 sec)

没有 FROM 子句的情况，也可以添加一个虚拟表 dual。

SELECT NOW() FROM dual;
SELECT 1+2 FROM dual;

运算结果和上面的实例完全相同。

dual 表是一个虚拟表，完全可以忽略。它存在的目的只是让 SQL 看起来更加工整。

1.1.2. WHERE

where子句在进行select查询时可以用来指定搜索条件，UPDATE 和 DELETE 语句中，用来指定要更新或删除的行。也可以是使用 AND , OR 和 NOT 逻辑运算符一个或多个表达式的组合。

MySQL 提供了很多比较运算符，下表列出了可用于 WHERE 子句中的比较运算符。

比较运算符	说明	举例
=	等于	age = 18
<>	不等于	age <> 18
!=	不等于	age != 18
>	大于，通常用于比较数字或者日期	age > 18
>=	大于等于，通常用于比较数字或者日期	age >= 18
<	小于，通常用于比较数字或者日期	age < 18
<=	小于等于，通常用于比较数字或者日期	age <= 18
IN	判断值是否在一个集合中	age IN (18, 19)
NOT IN	判断值是否不在一个集合中	age NOT IN (18, 19)
BETWEEN	判断值是否介于两个数中间	age BETWEEN 16 AND 18
LIKE	模糊匹配	name LIKE 'A%'
IS NULL	是否为 NULL	name IS NULL
IS NOT NULL	是否不为 NULL	name IS NOT NULL

注意：在 SQL 中，比较两个值是否相等的运算符是 =，而不是 ==。

1.1.3. AND

AND 左右两个条件都成立，结果才返回真，否则返回假或者 NULL。MySQL 中没有布尔类型，AND 的运算结果是 1（true）, 0（false）, 或者 NULL。

AND 运算符的运算规则如下：

- 两个操作数都为true（非0），则 AND 运算的结果为 1。

SELECT 1 AND 1, 1 AND 2;
//运算结果
+---------+---------+
| 1 AND 1 | 1 AND 2 |
+---------+---------+
|       1 |       1 |
+---------+---------+

- 有一个操作数为 0 (FALSE)，则 AND 运算的结果为 0。

SELECT
    0 AND 0,
    0 AND 1,
    0 AND 2,
    0 AND NULL;
//结果
+---------+---------+---------+------------+
| 0 AND 0 | 0 AND 1 | 0 AND 2 | 0 AND NULL |
+---------+---------+---------+------------+
|       0 |       0 |       0 |          0 |
+---------+---------+---------+------------+

- 有一个为 NULL，就返回 NULL。

SELECT 1 AND NULL, 2 AND NULL, NULL AND NULL;
//结果
+------------+------------+---------------+
| 1 AND NULL | 2 AND NULL | NULL AND NULL |
+------------+------------+---------------+
|       NULL |       NULL |          NULL |
+------------+------------+---------------+

操作数的前后顺序不影响 AND 操作符的运算结果。

1.1.4. OR

OR ，两个操作数中的一个为1（true）时，结果就返回1（true），否则返回0（false）或者 NULL。

OR 运算符的运算规则如下：

- 两个操作数中有一个操作数为 1 (true)，则 OR 运算的结果为 1。

SELECT 1 OR 1, 1 OR 0, 1 OR NULL, 2 OR 0;
//结果
+--------+--------+-----------+--------+
| 1 OR 1 | 1 OR 0 | 1 OR NULL | 2 OR 0 |
+--------+--------+-----------+--------+
|      1 |      1 |         1 |      1 |
+--------+--------+-----------+--------+

- or连接的所有操作数都为 0 (false)，则 OR 运算的结果为 0。

SELECT 0 OR 0;
//结果
+--------+
| 0 OR 0 |
+--------+
|      0 |
+--------+

- or连接的操作数在没有1（true）的情况下，有null就返回null

SELECT NULL OR 0, NULL or NULL;
//结果
+-----------+--------------+
| NULL OR 0 | NULL or NULL |
+-----------+--------------+
|      NULL |         NULL |
+-----------+--------------+

操作数的前后顺序不影响 OR 操作符的运算结果

有1就是1，全0才是0，没1都是null

	1	0	NULL
1	1	1	1
0	1	0	NULL
NULL	1	NULL	NULL

1.1.5. IN

SQL 查询的时候，用IN来判断左侧的值是否存在与右侧，右侧可以是1个值也可以是多个值。如果包含返回 1，否则返回 0。

- IN 运算符是多个 OR 运算符组合的简化版本。比如下面的 IN 语句：

name IN ('Alice', 'Tim', 'Jack')
//相当于下面的 OR 语句：
name = 'Alice' OR name = 'Tim' OR name = 'Jack'

- 当左侧和右侧都不是 NULL 时，右侧值列表中包含左侧的值时返回 1，否则返回 0。

SELECT 1 IN (1, 2), 3 IN (1, 2), 'A' IN ('A', 'B'), 'C' IN ('A', 'B');
//结果
+-------------+-------------+-------------------+-------------------+
| 1 IN (1, 2) | 3 IN (1, 2) | 'A' IN ('A', 'B') | 'C' IN ('A', 'B') |
+-------------+-------------+-------------------+-------------------+
|           1 |           0 |                 1 |                 0 |
+-------------+-------------+-------------------+-------------------+

- 左侧操作数为 NULL，返回 NULL。

SELECT NULL IN (1, 2), NULL IN (1, 2, NULL);
//结果
+----------------+----------------------+
| NULL IN (1, 2) | NULL IN (1, 2, NULL) |
+----------------+----------------------+
|           NULL |                 NULL |
+----------------+----------------------+

- 当右侧值列表含有 NULL，但是包含了左侧的值，返回 1，否则返回 NULL

SELECT 1 IN (1, NULL), 2 IN (1, NULL);
//结果
+----------------+----------------+
| 1 IN (1, NULL) | 2 IN (1, NULL) |
+----------------+----------------+
|              1 |           NULL |
+----------------+----------------+

MySQL IN 实例

WHERE 子句中也可以使用 IN 运算符。IN 除了与值列表比较，还能与子查询进行比较。

- 使用 WHERE 子句查找姓氏为 ALLEN 或 DAVIS 的所有演员

SELECT * FROM actor WHERE last_name IN ('ALLEN', 'DAVIS');
//结果
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      118 | CUBA       | ALLEN     | 2006-02-15 04:34:33 |
|      145 | KIM        | ALLEN     | 2006-02-15 04:34:33 |
|      194 | MERYL      | ALLEN     | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS     | 2006-02-15 04:34:33 |
|      101 | SUSAN      | DAVIS     | 2006-02-15 04:34:33 |
|      110 | SUSAN      | DAVIS     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

1.1.6. NOT IN

NOT ，用来否定一个操作。NOT IN 是 IN 的否定操作。

NOT IN 运算符的运算规则：

- 右侧值列表不包含左侧的值时条件成立，返回 1，否则返回 0。

SELECT 1 NOT IN (1, 2), 3 NOT IN (1, 2), 'A' NOT IN ('A', 'B'), 'C' NOT IN ('A', 'B');
//结果
+-----------------+-----------------+-----------------------+-----------------------+
| 1 NOT IN (1, 2) | 3 NOT IN (1, 2) | 'A' NOT IN ('A', 'B') | 'C' NOT IN ('A', 'B') |
+-----------------+-----------------+-----------------------+-----------------------+
|               0 |               1 |                     0 |                     1 |
+-----------------+-----------------+-----------------------+-----------------------+

- 左侧操作数是 NULL，返回 NULL。

SELECT NULL NOT IN (1, 2), NULL NOT IN (1, 2, NULL);
//结果
+--------------------+--------------------------+
| NULL NOT IN (1, 2) | NULL NOT IN (1, 2, NULL) |
+--------------------+--------------------------+
|               NULL |                     NULL |
+--------------------+--------------------------+

- 右侧有null且条件不成立，返回null

SELECT 1 NOT IN (1, NULL), 2 NOT IN (1, NULL);
//结果
+--------------------+--------------------+
| 1 NOT IN (1, NULL) | 2 NOT IN (1, NULL) |
+--------------------+--------------------+
|                  0 |               NULL |
+--------------------+--------------------+

1.1.7. BETWEEN

BETWEEN：确定一个值是不是存在两个值的范围内。常用于判断一个值是不是在数字和日期之间。

MySQL BETWEEN 语法：

expression BETWEEN min AND max
expression NOT BETWEEN min AND max

使用说明：左侧是字段名，表达式都可以，右侧是范围。

MySQL BETWEEN 运算规则：

当字段的值或者给定的值在 min 和 max 两个值之间时，返回 1。否则，返回 0。

- 左侧的值在 min 和 max 之间时， BETWEEN 返回 1，否则返回 0。

SELECT
    1 BETWEEN 1 AND 3,
    2 BETWEEN 1 AND 3,
    3 BETWEEN 1 AND 3,
    4 BETWEEN 1 AND 3;
//结果
+-------------------+-------------------+-------------------+-------------------+
| 1 BETWEEN 1 AND 3 | 2 BETWEEN 1 AND 3 | 3 BETWEEN 1 AND 3 | 4 BETWEEN 1 AND 3 |
+-------------------+-------------------+-------------------+-------------------+
|                 1 |                 1 |                 1 |                 0 |
+-------------------+-------------------+-------------------+-------------------+

- 有一个 NULL 时， BETWEEN 运算符就返回 NULL。

SELECT
    NULL BETWEEN 1 AND 3,
    1 BETWEEN NULL AND 3,
    1 BETWEEN 1 AND NULL;
//结果
+----------------------+----------------------+----------------------+
| NULL BETWEEN 1 AND 3 | 1 BETWEEN NULL AND 3 | 1 BETWEEN 1 AND NULL |
+----------------------+----------------------+----------------------+
|                 NULL |                 NULL |                 NULL |
+----------------------+----------------------+----------------------+

1.1.8. LIKE

LIKE 运算符根据指定的模式过滤数据，一般用于模糊匹配字符数据。

expression LIKE pattern

说明：左侧是字段名，表达式都可以，右侧是一个字符串模式。支持两个通配符： % 和 _。

- - % 匹配0个或多个任意字符。
  - _ 匹配单个任意字符。
  - 如果匹配通配符，使用 \ 转义字符，如 \% 就表示%和 \_就表示_。
  - 通配符匹配文本时，不区分字母大小写。
  - 左侧和右侧匹配，LIKE 运算符返回 1，否则返回 0。

比如：

- - a% 匹配以字符 a 开头的任意长度的字符串。
  - %a 匹配以字符 a 结尾的任意长度的字符串。
  - %a% 匹配包含字符 a 的任意长度的字符串。
  - %a%b% 匹配同时包含字符 a 和 b 且 a 在 b 前面的任意长度的字符串。
  - a_ 匹配以字符 a 开头长度为 2 字符串。
  - _a 匹配以字符 a 结尾长度为 2 字符串。

MySQL LIKE 运算规则：

- 当 LIKE左侧值匹配右侧是，返回 1。否则，返回 0。

SELECT 'a' LIKE 'a', 'a' LIKE 'a%', 'ab' LIKE 'a%', 'ab' LIKE '%a';
//结果
+--------------+---------------+----------------+----------------+
| 'a' LIKE 'a' | 'a' LIKE 'a%' | 'ab' LIKE 'a%' | 'ab' LIKE '%a' |
+--------------+---------------+----------------+----------------+
|            1 |             1 |              1 |              0 |
+--------------+---------------+----------------+----------------+


SELECT 'a' LIKE 'a_', 'ab' LIKE 'a_', 'abc' LIKE 'a_';
//结果
+---------------+----------------+-----------------+
| 'a' LIKE 'a_' | 'ab' LIKE 'a_' | 'abc' LIKE 'a_' |
+---------------+----------------+-----------------+
|             0 |              1 |               0 |
+---------------+----------------+-----------------+

- LIKE 两侧有一个为null，返回 NULL。

SELECT NULL LIKE 'a%', 'a' LIKE NULL;
//结果
+----------------+---------------+
| NULL LIKE 'a%' | 'a' LIKE NULL |
+----------------+---------------+
|           NULL |          NULL |
+----------------+---------------+

MySQL LIKE 实例：

WHERE 子句中可以使用 LIKE 运算符。

使用 % 匹配开头

SELECT * FROM actor WHERE first_name LIKE 'P%';
//结果
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
|       46 | PARKER     | GOLDBERG  | 2006-02-15 04:34:33 |
|       54 | PENELOPE   | PINKETT   | 2006-02-15 04:34:33 |
|      104 | PENELOPE   | CRONYN    | 2006-02-15 04:34:33 |
|      120 | PENELOPE   | MONROE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

使用 % 匹配结尾

SELECT * FROM actor WHERE first_name LIKE '%ES';
//结果
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       48 | FRANCES    | DAY-LEWIS | 2006-02-15 04:34:33 |
|       84 | JAMES      | PITT      | 2006-02-15 04:34:33 |
|      126 | FRANCES    | TOMEI     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

使用 % 匹配包含字符

SELECT * FROM actor WHERE first_name LIKE '%AM%';
//结果
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       24 | CAMERON    | STREEP    | 2006-02-15 04:34:33 |
|       63 | CAMERON    | WRAY      | 2006-02-15 04:34:33 |
|       71 | ADAM       | GRANT     | 2006-02-15 04:34:33 |
|       84 | JAMES      | PITT      | 2006-02-15 04:34:33 |
|      111 | CAMERON    | ZELLWEGER | 2006-02-15 04:34:33 |
|      132 | ADAM       | HOPPER    | 2006-02-15 04:34:33 |
|      175 | WILLIAM    | HACKMAN   | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

使用 _匹配单个字符

SELECT * FROM actor WHERE first_name LIKE '_AY';
//结果
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       55 | FAY        | KILMER    | 2006-02-15 04:34:33 |
|       64 | RAY        | JOHANSSON | 2006-02-15 04:34:33 |
|      147 | FAY        | WINSLET   | 2006-02-15 04:34:33 |
|      156 | FAY        | WOOD      | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

NOT LIKE

SELECT * FROM category WHERE name NOT LIKE 'A%';
//结果
+-------------+-------------+---------------------+
| category_id | name        | last_update         |
+-------------+-------------+---------------------+
|           3 | Children    | 2006-02-15 04:46:27 |
|           4 | Classics    | 2006-02-15 04:46:27 |
|           5 | Comedy      | 2006-02-15 04:46:27 |
|           6 | Documentary | 2006-02-15 04:46:27 |
|           7 | Drama       | 2006-02-15 04:46:27 |
|           8 | Family      | 2006-02-15 04:46:27 |
|           9 | Foreign     | 2006-02-15 04:46:27 |
|          10 | Games       | 2006-02-15 04:46:27 |
|          11 | Horror      | 2006-02-15 04:46:27 |
|          12 | Music       | 2006-02-15 04:46:27 |
|          13 | New         | 2006-02-15 04:46:27 |
|          14 | Sci-Fi      | 2006-02-15 04:46:27 |
|          15 | Sports      | 2006-02-15 04:46:27 |
|          16 | Travel      | 2006-02-15 04:46:27 |
+-------------+-------------+---------------------+

1.1.9. REGEXP（正则表达式）

刚刚知道SQL也有正则表达式。在搜索字符串方面更强大，可搜索更复杂的模板。

REGEXP实例：

select * from customers where last_name like '%field%'
//等效于：
select * from customers where last_name regexp 'field'

正则表达式可以组合来表达更复杂的字符串模式

//查询以mac开头 或者 以field结尾 或者 包含rose的数据
where last_name regexp '^mac|field$|rose'
//查找含ge/ie或ef/em/eq的
where last_name regexp '[gi]e|e[fmq]' 
// 查询e前面包含a-h的数据 或者 e后面包含c-j的数据
where last_name regexp '[a-h]e|e[c-j]'

REGEXP常用通配符：

- . ：匹配任意单个字符，除了换行符 \n 之外的任何字符。
- * ：匹配前面的字符零次或多次。
- + ：匹配前面的字符一次或多次。
- ? ：匹配前面的字符零次或一次。
- ^ ：匹配输入字符串的开头。
- $ ：匹配输入字符串的结尾。
- [...] ：字符类，匹配方括号中任意字符。例如，[abc] 匹配 "a"、"b" 或 "c" 中的任何一个字符。
- [^...] ：否定字符类，匹配除了方括号中的字符之外的任何字符。例如，[^abc] 匹配除了 "a"、"b"、"c" 之外的任何字符。
- | ：逻辑或运算符，匹配两个模式中的任意一个。
- \ ：转义字符，用于转义具有特殊含义的字符，使其成为普通字符。例如，\. 匹配实际的点而不是任意字符。

1.1.10. IS NULL

IS NULL 用来判断一个值是不是 NULL，如果是 NULL 返回 1，否则返回 0。

expression IS NULL
expression IS NOT NULL

说明：左侧是字段名，表达式都可以。

MySQL IS NULL 运算规则：

- IS NULL 左侧值是 NULL 时，返回 1，否则返回 0。

SELECT
    NULL IS NULL,
    0 IS NULL,
    1 IS NULL,
    (NULL IN (NULL)) IS NULL,
    (1+1) IS NULL;
//结果
+--------------+-----------+-----------+--------------------------+---------------+
| NULL IS NULL | 0 IS NULL | 1 IS NULL | (NULL IN (NULL)) IS NULL | (1+1) IS NULL |
+--------------+-----------+-----------+--------------------------+---------------+
|            1 |         0 |         0 |                        1 |             0 |
+--------------+-----------+-----------+--------------------------+---------------+

- IS NOT NULL 左侧值不是 NULL 时，返回 1，否则返回 0。

SELECT
    NULL IS NOT NULL,
    0 IS NOT NULL,
    1 IS NOT NULL;
//结果
+------------------+---------------+---------------+
| NULL IS NOT NULL | 0 IS NOT NULL | 1 IS NOT NULL |
+------------------+---------------+---------------+
|                0 |             1 |             1 |
+------------------+---------------+---------------+

MySQL IS NULL 实例：

- 以下 SQL 语句使用 IS NULL 查询 staff 表中没有设置密码的职员。

SELECT
    first_name, last_name, password
FROM
    staff
WHERE
    password IS NULL;
//结果
+------------+-----------+----------+
| first_name | last_name | password |
+------------+-----------+----------+
| Jon        | Stephens  | NULL     |
+------------+-----------+----------+

要查询密码不是null的职员，使用password IS NOT NULL

1.1.11. EXISTS

EXISTS 判断当前数据是否存在，会用在where子句中，在这个子句中执行一条查询语句，如果查询到了说明这个数据是存在的就会返回1（true），反之返回一个0（false）。

EXISTS 实例：

下面的实例查询 language 表的一些语种，该语种在 film 表中存在相关语种的影片。

下面这条查询语句会查询这张表的所有数据，说一下执行的流程，先执行最内层的查询语句，涉及到了两个表，拿film表的language_id去找language表的language_id如果两个值相等，EXISTS会返回true，外层的where判断是true，最外层查询就会输出这条语句，然后进行下一条语句的判断。

SELECT *
FROM language
WHERE EXISTS(
    SELECT *
    FROM film
    WHERE film.language_id = language.language_id
  );
//结果
+-------------+---------+---------------------+
| language_id | name    | last_update         |
+-------------+---------+---------------------+
|           1 | English | 2006-02-15 05:02:19 |
+-------------+---------+---------------------+
1 row in set (0.01 sec)

由EXISTS引出的子查询，目标列表达式通常都用 *，因为EXISTS的子查询只返回真值假值，给出列名无实际意义。

EXISTS 和 IN：

- 有时候 EXISTS 可以使用 IN 来实现。

//EXISTS
SELECT *
FROM language
WHERE EXISTS(
    SELECT *
    FROM film
    WHERE film.language_id = language.language_id
  );
//IN
SELECT *
FROM language
WHERE language_id IN (
    SELECT DISTINCT language_id
    FROM film
  );

大多数情况下，使用 EXISTS 的语句的性能比对应的使用 IN 的语句要好。

在子查询中使用 TABLE 语句：

在 MySQL 8.0.19 和以后的版本中，可以直接在 EXISTS 或 NOT EXISTS 的子查询中使用 TABLE 语句。就像下面的一样：

//只有当表 t2 不为空时，才会选择表 t1 中的数据。
SELECT column1 FROM t1 WHERE EXISTS (TABLE t2);
//例如
SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

1.1.12. ORDER BY

SELECT 语句返回的结果集默认是升序排序的。如果想改变结果的输出顺序，可以使用 ORDER BY 子句指定排序的字段以及升序排序还是降序排序。

MySQL ORDER BY 语法：

ORDER BY 子句中，可以指定一个或多个排序的字段。 ORDER BY 子句的语法如下：

SELECT
   column1, column2, ...
FROM
   table_name
[WHERE clause]
ORDER BY
   column1 [ASC|DESC],
   column2 [ASC|DESC],
   ...;

说明：

- [ASC|DESC] 代表排序是升序还是降序，这是可选的，只能选一个，不能由升序又降序。
- ASC 代表升序，DESC 代表降序。

MySQL ORDER BY 排序规则说明：

- ORDER BY column1, column2;

此 ORDER BY 子句主排序按 column1 字段升序排序，在主排序的基础上，column1 字段如果有多个相同的行，再按 column2 字段升序排序。默认是升序排序，也可指定为降序排序DESC

MySQL ORDER BY 实例：

按多字段排序：

以下 SQL 语句使用 ORDER BY 子句先按演员姓氏升序排序，再按演员名字升序排序。

SELECT
    actor_id, first_name, last_name
FROM
    actor
ORDER BY last_name, first_name;
//结果
+----------+-------------+--------------+
| actor_id | first_name  | last_name    |
+----------+-------------+--------------+
|       58 | CHRISTIAN   | AKROYD       |
|      182 | DEBBIE      | AKROYD       |
|       92 | KIRSTEN     | AKROYD       |
|      118 | CUBA        | ALLEN        |
|      145 | KIM         | ALLEN        |
|      194 | MERYL       | ALLEN        |
....

按自定义顺序排序：

有时候按照字段的值排序不能满足要求，可以自定义排序。比如，需要按照电影分级 'G', 'PG', 'PG-13', 'R', 'NC-17' 的顺序对影片进行排序。

可以理解为按照列表中元素的索引位置进行排序。分别使用 CASE 子句或 FIELD() 函数实现它。

根据影片的等级按照的 'G', 'PG', 'PG-13', 'R', 'NC-17' 顺序对影片进行排序。

SELECT
    film_id, title, rating
FROM
    film
ORDER BY CASE rating
    WHEN 'G' THEN 1
    WHEN 'PG' THEN 2
    WHEN 'PG-13' THEN 3
    WHEN 'R' THEN 4
    WHEN 'NC-17' THEN 5
END;
//结果
+---------+-----------------------------+--------+
| film_id | title                       | rating |
+---------+-----------------------------+--------+
|       2 | ACE GOLDFINGER              | G      |
|       4 | AFFAIR PREJUDICE            | G      |
...
|       1 | ACADEMY DINOSAUR            | PG     |
|       6 | AGENT TRUMAN                | PG     |
...
|       7 | AIRPLANE SIERRA             | PG-13  |
|       9 | ALABAMA DEVIL               | PG-13  |
...
|       8 | AIRPORT POLLOCK             | R      |
|      17 | ALONE TRIP                  | R      |
...
|       3 | ADAPTATION HOLES            | NC-17  |
|      10 | ALADDIN CALENDAR            | NC-17  |
...
1000 rows in set (0.00 sec)

使用 CASE 将电影的等级转换为一个索引数字。然后使用 ORDER BY 按照这个数字进行排序。

CASE 子句写起来很复杂，特别是列表值很多的时候。可以使用如下的 FIELD() 函数。

SELECT * FROM film ORDER BY FIELD(rating, 'G', 'PG', 'PG-13', 'R', 'NC-17');

ORDER BY 和 NULL：

在 MySQL 中使用 ORDER BY 子句升序 ASC 排序时， NULL 值会出现在非 NULL 值之前。

在 MySQL 中使用 ORDER BY 子句降序DESC排序时， NULL 值会出现在非 NULL 值之后

1.1.13. LIMIT

LIMIT 子句可以设置 SELECT 语句返回固定行的数据，接收一个或两个非负数作为参数。

LIMIT和ORDER BY子句：

在 SELECT 语句中， LIMIT 子句经常和 ORDER BY子句结合使用。比如在下面的场景中：

- ERP 中显示销售额最高的 5 个销售员
- 网站上的点击率最高的 10 个文章
- 论坛中一个月内最活跃的 10 个会员
- 博客网站的文章分页列表

这些场景中，先按照某个规则排序，然后使用取出指定数量的记录行。

MySQL LIMIT 实例：

查询片长最长的10部影片：

SELECT 
    film_id, title, length
FROM
    film
WHERE
    reting = 'G'
ORDER BY length DESC
LIMIT 10;
//查询结果
+---------+--------------------+--------+
| film_id | title              | length |
+---------+--------------------+--------+
|     212 | DARN FORRESTER     |    185 |
|     182 | CONTROL ANTHEM     |    185 |
|     609 | MUSCLE BRIGHT      |    185 |
|     597 | MOONWALKER FOOL    |    184 |
|     128 | CATCH AMISTAD      |    183 |
|     996 | YOUNG LANGUAGE     |    183 |
|      50 | BAKED CLEOPATRA    |    182 |
|     467 | INTRIGUE WORST     |    181 |
|     510 | LAWLESS VISION     |    181 |
|     612 | MUSSOLINI SPOILERS |    180 |
+---------+--------------------+--------+

使用 LIMIT 子句进行分页：

对于一些大型的数据表来说，分页查询能很好的减少数据库的消耗和提高用户体验。如果一个表中有1万条数据，不进行分页查询所有数据都显示在页面上，会发生以下问题：

- 增加数据库的性能消耗
- 传输过程中的数据量增加
- 用户的体验不好，太多的数据对让用户眼花缭乱。

引入分页后，每页显示 10 行数据， 1万行数据需要 1000 (10000 / 10) 页就能显示完全。

要查询第二页数据需要先跳过第一页的 10 行数据并且最多行数为 10 行数据，整个 SQL 如下：

SELECT film_id, title FROM film LIMIT 10, 10;

//结果
+---------+------------------+
| film_id | title            |
+---------+------------------+
|       1 | ACADEMY DINOSAUR |
|       2 | ACE GOLDFINGER   |
|       3 | ADAPTATION HOLES |
|       4 | AFFAIR PREJUDICE |
|       5 | AFRICAN EGG      |
|       6 | AGENT TRUMAN     |
|       7 | AIRPLANE SIERRA  |
|       8 | AIRPORT POLLOCK  |
|       9 | ALABAMA DEVIL    |
|      10 | ALADDIN CALENDAR |
+---------+------------------+

1.1.14. DISTINCT

使用 SELECT 查询数据时，会有一些重复的行。比如演员表中有很多重复的姓氏。如果想得到一个唯一的、没有重复记录的结果集，就需要在SELECT后面加上 DISTINCT 关键字就能返回一个没有重复记录行的结果集。

MySQL DISTINCT 实例：

DISTINCT 可以消除多个字段的重复值。当指定多个字段值时， DISTINCT 使用多个字段组合确定记录行的唯一性。检索 actor 表中的名字和姓氏：

SELECT DISTINCT last_name, first_name FROM actor;
//结果
+--------------+-------------+
| last_name    | first_name  |
+--------------+-------------+
| GUINESS      | PENELOPE    |
| WAHLBERG     | NICK        |
| CHASE        | ED          |
| DAVIS        | JENNIFER    |
| LOLLOBRIGIDA | JOHNNY      |
...
| TEMPLE       | THORA       |
+--------------+-------------+
199 rows in set (0.01 sec)

注意：DISTINCT 关键字确保了查询结果中的每个值都是唯一的，但它并不会删除表中的任何数据，仅在结果集中消除了重复的行。

DISTINCT 与 NULL：

当 DISTINCT 遇到 NULL 值时，只保留一个 NULL 值。因为 DISTINCT 认为所有的 NULL 值都是相同的，这与字段的类型无关。