SELECT初探

SELECT

第一种结构，只有SELECT，没有任何子句。

示例代码：

SELECT 1;

运行结果：

+-+
|1|
+-+
|1|
+-+

示例代码：

1	SELECT 9/2;

运行结果：

+------+
|9/2   |
+------+
|4.5000|
+------+

SELECT-FROM

语法格式

语法：

1 2	SELECT ... FROM ...

SELECT：标识选择哪些列。
FROM：标识从哪些表中选择。

选择全部列

选择全部列，用通配符，*，示例代码：

1	SELECT * FROM departments;

选择特定的列

选择特定的列，示例代码：

1	SELECT department_id, location_id FROM departments;

列的别名

格式为列名 AS "列的别名"。

其中AS可以省略，但建议保留。
如果别名中有空格等其他特殊字符，可以将别名用双引号""括起来。

示例代码：

1 2	SELECT last_name AS "Name", salary * 12 AS "Annual Salary" FROM employees;

运行结果：

+-----------+-------------+
|Name       |Annual Salary|
+-----------+-------------+
|King       |288000       |
|Kochhar    |204000       |
|De Haan    |204000       |

【部分运行结果略】

|Baer       |120000       |
|Higgins    |144000       |
|Gietz      |99600        |
+-----------+-------------+

去除重复行

使用关键字DISTINCT可以去除重复行。

用法

例如，未使用DISTINCT，示例代码：

1	SELECT department_id FROM employees;

运行结果：

+-------------+
|department_id|
+-------------+
|NULL         |
|10           |
|20           |
|20           |
|30           |
|30           |

【部分运行结果略】

|100          |
|100          |
|100          |
|110          |
|110          |
+-------------+

有很多重复的数据。

使用DISTINCT之后，没有重复数据，示例代码：

1	SELECT DISTINCT department_id FROM employees;

运行结果：

+-------------+
|department_id|
+-------------+
|NULL         |
|10           |
|20           |
|30           |
|40           |
|50           |
|60           |
|70           |
|80           |
|90           |
|100          |
|110          |
+-------------+

注意

需要注意的有两点。

DISTINCT，需要放到所有列名的前面。
DISTINCT，是对后面所有列名的组合进行去重。

例如，没有将DISTINCT，放在所有列名的前面，示例代码：

1 2	SELECT salary, DISTINCT department_id FROM employees

运行结果：

1	[42000][1064] 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 'DISTINCT department_id

将DISTINCT，放在所有列名的前面，组合去重，示例代码：

1 2	SELECT DISTINCT department_id, salary FROM employees;

运行结果：

+-------------+------+
|department_id|salary|
+-------------+------+
|90           |24000 |
|90           |17000 |
|60           |9000  |
|60           |6000  |
|60           |4800  |

【部分运行结果略】

|20           |6000  |
|40           |6500  |
|70           |10000 |
|110          |12000 |
|110          |8300  |
+-------------+------+

分布式中的性能问题

在分布式数据库中，去重的更好办法是用GROUP BY。
例如，对于SELECT DISTINCT department_id FROM employees;，SELECT department_id FROM employees GROUP BY department_id;的性能会更好。
我们在下文讨论GROUP BY的时候会解释原因。

着重号

表中字段、表名等，不能和保留字、常用方法名等相同，如果相同了，可以在SQL中使用一对着重号``引起来。

例如，ORDER是属于一个保留字，示例代码：

1	SELECT * FROM ORDER;

运行结果：

1	[42000][1064] 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 'ORDER' at line 1

示例代码：

1	SELECT * FROM `order`;

运行结果：

+--------+----------+
|order_id|order_name|
+--------+----------+
|1       |shkstart  |
|2       |tomcat    |
|3       |dubbo     |
+--------+----------+

查询常数

SELECT 常数，即对常数进行查询。

示例代码：

1 2	SELECT '少林寺驻武当山办事处' as corporation, last_name FROM employees;

运行结果：

+-----------+-----------+
|corporation|last_name  |
+-----------+-----------+
|少林寺驻武当山办事处 |King       |
|少林寺驻武当山办事处 |Kochhar    |
|少林寺驻武当山办事处 |De Haan    |

【部分运行结果略】

|少林寺驻武当山办事处 |Baer       |
|少林寺驻武当山办事处 |Higgins    |
|少林寺驻武当山办事处 |Gietz      |
+-----------+-----------+

SELECT-FROM-WHERE

格式如下：

1	SELECT 字段1,字段2 FROM 表名 WHERE 过滤条件

示例代码：

1
2
3

SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90;

运行结果：

+-----------+---------+-------+-------------+
|employee_id|last_name|job_id |department_id|
+-----------+---------+-------+-------------+
|100        |King     |AD_PRES|90           |
|101        |Kochhar  |AD_VP  |90           |
|102        |De Haan  |AD_VP  |90           |
+-----------+---------+-------+-------------+

运算符

MySQL中的运算符有四种：

算术运算符
比较运算符
逻辑运算符
位运算符

其中，位运算符，我们不讨论。在实际中，或许的确应用不多，主要讨论前三种。

这些运算符，还有一个共同的特点，几乎所有的运算符遇到NULL，运算的结果都为NULL。
注意，是几乎，有三种例外：

安全等于运算符<=>
专门为NULL设计的运算符，例如：IS NULL。
在AND中，0 AND NULL结果为0，1 AND NULL结果为NULL。

例如，12 * NULL，示例代码：

1 2	SELECT employee_id, salary, commission_pct, 12 * commission_pct AS "test" FROM employees;

运行结果：

+-----------+------+--------------+----+
|employee_id|salary|commission_pct|test|
+-----------+------+--------------+----+
|100        |24000 |NULL          |NULL|
|101        |17000 |NULL          |NULL|
|102        |17000 |NULL          |NULL|

【部分运行结果略】

|145        |14000 |0.4           |4.8 |
|146        |13500 |0.3           |3.6 |
|147        |12000 |0.3           |3.6 |

【部分运行结果略】

|204        |10000 |NULL          |NULL|
|205        |12000 |NULL          |NULL|
|206        |8300  |NULL          |NULL|
+-----------+------+--------------+----+

算术运算符

算术运算符由可以分为：

加和减
乘和除
求余(取模)

加和减

类型提升

在《基于Java的后端开发入门：1.基础语法》中，我们讨论过"算术表达式的类型自动提升"。在MySQL中，也有类型的自动提升。
规则如下：

整数和整数进行加减，结果是整数。
整数和浮点数进行加减，结果是浮点数。

字符串参与运算

在《基于Java的后端开发入门：1.基础语法》中，我们还讨论过，+的左右两边如果有字符串，表示字符串的拼接。
在MySQL中，+只表示数值相加。如果遇到非数值类型，会先尝试转成数值，如果转换失败，就按0计算。
(如果需要在MySQL中做字符串拼接，可以通过字符串函数CONCAT()实现。)

示例代码：

1	SELECT 100 + 0, 100 + 0.0, 100 + '1', 100 + 'One'

运行结果：

+-------+---------+---------+-----------+
|100 + 0|100 + 0.0|100 + '1'|100 + 'One'|
+-------+---------+---------+-----------+
|100    |100.0    |101      |100        |
+-------+---------+---------+-----------+

乘和除

类型提升

加和减的类型提升，Java和MySQL的规则是一样的。但是对于乘和除，存在区别。

在Java中，对于乘法和除法，如果两个数字都是整型，则结果也是整型，不会有类型的自动提升。
但是在MySQL中：
- 对于除法，无论是否能除尽，都会保留4位小数(四舍五入)。
- 对于乘法，会类型提升，并保留"乘数"中的最大位。

在Java中，示例代码：

package com.kakawanyifan;

public class Main {
    public static void main(String[] args) {
        int a = 9;
        int b = 5;
        System.out.println(a/b);
    }
}

运行结果：

在MySQL中，示例代码：

1	SELECT 9 / 5, 9 * 0.2, 9 * 1.0;

运行结果：

+------+-------+-------+
|9 / 5 |9 * 0.2|9 * 1.0|
+------+-------+-------+
|1.8000|1.8    |9.0    |
+------+-------+-------+

0作为被除数

在Java中，如果0作为了被除数，会报错。
在MySQL中，如果0作为了被除数，结果会为NULL。

在Java中，示例代码：

package com.kakawanyifan;

public class Main {
    public static void main(String[] args) {
        int a = 9;
        int b = 0;
        System.out.println(a/b);
    }
}

运行结果：

1 2	Exception in thread "main" java.lang.ArithmeticException: / by zero at com.kakawanyifan.Main.main(Main.java:7)

在MySQL中，示例代码：

1	SELECT 9 / 0;

运行结果：

+-----+
|9 / 0|
+-----+
|NULL |
+-----+

字符串参与运算

和在加减中一样，MySQL会自动将字符串转成数字，转换失败，作为0。

示例代码：

SELECT 100 * 1,
       100 * 1.0,
       100 * '1',
       100 * '1.0',
       100 * 'One',
       100 / 1,
       100 / 1.0,
       100 / '1',
       100 / '1.0',
       100 / 'One';

运行结果：

+-------+---------+---------+-----------+-----------+--------+---------+---------+-----------+-----------+
|100 * 1|100 * 1.0|100 * '1'|100 * '1.0'|100 * 'One'|100 / 1 |100 / 1.0|100 / '1'|100 / '1.0'|100 / 'One'|
+-------+---------+---------+-----------+-----------+--------+---------+---------+-----------+-----------+
|100    |100.0    |100      |100        |0          |100.0000|100.0000 |100      |100        |NULL       |
+-------+---------+---------+-----------+-----------+--------+---------+---------+-----------+-----------+

乘以数字和乘以字符串数字不一样。

例如，乘以数字1.1，与乘以字符串1.1，结果不一样，示例代码：

1	SELECT 1.1 = '1.1', 100 * 1.1, 100 * '1.1'

运行结果：

+-----------+---------+------------------+
|1.1 = '1.1'|100 * 1.1|100 * '1.1'       |
+-----------+---------+------------------+
|1          |110.0    |110.00000000000001|
+-----------+---------+------------------+

求模(求余)

%和MOD，求模(求余)。

示例代码：

1	SELECT 12 % 3, 12 MOD 5

运行结果：

+------+--------+
|12 % 3|12 MOD 5|
+------+--------+
|0     |2       |
+------+--------+

特别的，如果我们对浮点数求余呢？
结果还是浮点数。
示例代码：

1	SELECT 12 % 3.0, 12 MOD 5.5

运行结果：

+--------+----------+
|12 % 3.0|12 MOD 5.5|
+--------+----------+
|0.0     |1.0       |
+--------+----------+

比较运算符

分类

比较运算符可以分为两类，符号类，非符号类。

符号类

运算符	作用
`=`	等于
`<=>`	安全等于
`<>` `!=`	不等于
`<`	小于
`<=`	小于等于
`>`	大于
`>=`	大于等于

非符号类

运算符	作用
`IS NULL`	空
`NOT`	非
`LEAST`	最小值
`GREATEST`	最大值
`BETWEEN-AND`	两个值之间
`IN`	属于
`LIKE`	模糊查询
`REGEXP` `RLIKE`	正则表达式

=，等于

=，判断等号两边的值、字符串或表达式是否相等，如果相等返回1，不相等返回0。
规则如下：

能转成数字的用数字
不能转成数字的用字母
需要转成数字的，但转换失败就是0
NULL需要特别注意

其实，不单单=运算符，所有的运算符都是这个规则。

示例代码：

SELECT 1 = 1,
       1 = 0,
       (5 + 3) = (2 + 6),
       'a' = 'a',
       'a' = 'b',
       1 = '1',
       1 = 'ONE',
       0 = 'ONE',
       1 = TRUE,
       2 = TRUE,
       0 = FALSE,
       -1 = FALSE,
       '' = NULL,
       NULL = NULL;

运行结果：

+-----+-----+-----------------+---------+---------+-------+---------+---------+--------+--------+---------+----------+---------+-----------+
|1 = 1|1 = 0|(5 + 3) = (2 + 6)|'a' = 'a'|'a' = 'b'|1 = '1'|1 = 'ONE'|0 = 'ONE'|1 = TRUE|2 = TRUE|0 = FALSE|-1 = FALSE|'' = NULL|NULL = NULL|
+-----+-----+-----------------+---------+---------+-------+---------+---------+--------+--------+---------+----------+---------+-----------+
|1    |0    |1                |1        |0        |1      |0        |1        |1       |0       |1        |0         |NULL     |NULL       |
+-----+-----+-----------------+---------+---------+-------+---------+---------+--------+--------+---------+----------+---------+-----------+

<=>，安全等于

安全等于运算符，<=>。该运算符与等于运算符=的作用是相似的，唯一的区别是<=>可以用来对NULL进行判断。

示例代码：

SELECT 1 <=> 1,
       1 <=> 0,
       (5 + 3) <=> (2 + 6),
       'a' <=> 'a',
       'a' <=> 'b',
       1 <=> '1',
       1 <=> 'ONE',
       0 <=> 'ONE',
       1 <=> TRUE,
       2 <=> TRUE,
       0 <=> FALSE,
       -1 <=> FALSE,
       '' <=> NULL,
       NULL <=> NULL;

运行结果：

+-------+-------+-------------------+-----------+-----------+---------+-----------+-----------+----------+----------+-----------+------------+-----------+-------------+
|1 <=> 1|1 <=> 0|(5 + 3) <=> (2 + 6)|'a' <=> 'a'|'a' <=> 'b'|1 <=> '1'|1 <=> 'ONE'|0 <=> 'ONE'|1 <=> TRUE|2 <=> TRUE|0 <=> FALSE|-1 <=> FALSE|'' <=> NULL|NULL <=> NULL|
+-------+-------+-------------------+-----------+-----------+---------+-----------+-----------+----------+----------+-----------+------------+-----------+-------------+
|1      |0      |1                  |1          |0          |1        |0          |1          |1         |0         |1          |0           |0          |1            |
+-------+-------+-------------------+-----------+-----------+---------+-----------+-----------+----------+----------+-----------+------------+-----------+-------------+

<>和!=，不等于

不等于运算符，<>和!=。
用于判断两边的数字、字符串或者表达式的值是否不相等，如果不相等则返回1，相等则返回0。

示例代码：

SELECT 1 <> 1,
       1 != 2,
       (3 + 4) <> (2 + 6),
       'a' != 'b',
       1 != 'ONE',
       0 != 'ONE',
       1 != TRUE,
       1 != FALSE,
       'a' != NULL,
       NULL <> NULL;

运行结果：

+------+------+------------------+----------+----------+----------+---------+----------+-----------+------------+
|1 <> 1|1 != 2|(3 + 4) <> (2 + 6)|'a' != 'b'|1 != 'ONE'|0 != 'ONE'|1 != TRUE|1 != FALSE|'a' != NULL|NULL <> NULL|
+------+------+------------------+----------+----------+----------+---------+----------+-----------+------------+
|0     |1     |1                 |1         |1         |0         |0        |1         |NULL       |NULL        |
+------+------+------------------+----------+----------+----------+---------+----------+-----------+------------+

IS NULL，是否为空

空运算符IS NULL，判断一个值是否为NULL，如果为NULL则返回1，否则返回0。

示例代码：

1	SELECT NULL IS NULL, 1 IS NULL;

运行结果：

+------------+---------+
|NULL IS NULL|1 IS NULL|
+------------+---------+
|1           |0        |
+------------+---------+

有一个与IS NULL作用相同的，ISNULL()，这是一个函数，不是运算符。

BETWEEN-AND，区间

BETWEEN和AND，两边均是闭区间，符合结果为1，不符合结果为0。

示例代码：

SELECT 1 BETWEEN 0 AND 1,
       2 BETWEEN 0 AND 1,
       1 BETWEEN FALSE AND 100,
       1 BETWEEN '0' AND '100',
       'b' BETWEEN 'a' AND 'c',
       NULL BETWEEN 0 AND 100;

运行结果：

+-----------------+-----------------+-----------------------+-----------------------+-----------------------+----------------------+
|1 BETWEEN 0 AND 1|2 BETWEEN 0 AND 1|1 BETWEEN FALSE AND 100|1 BETWEEN '0' AND '100'|'b' BETWEEN 'a' AND 'c'|NULL BETWEEN 0 AND 100|
+-----------------+-----------------+-----------------------+-----------------------+-----------------------+----------------------+
|1                |0                |1                      |1                      |1                      |NULL                  |
+-----------------+-----------------+-----------------------+-----------------------+-----------------------+----------------------+

IN，是否在列表中

IN，用于判断给定的值是否是IN列表中的值，如果是则返回1，否则返回0。

示例代码：

SELECT 'a' IN ('a', 'b', 'c'),
       1 IN (2, 3),
       '1' IN (1, 2, 3),
       FALSE IN (0, 1, 2),
       NULL IN ('a', 'b'),
       'a' IN ('a', NULL);

运行结果：

+----------------------+-----------+----------------+------------------+------------------+------------------+
|'a' IN ('a', 'b', 'c')|1 IN (2, 3)|'1' IN (1, 2, 3)|FALSE IN (0, 1, 2)|NULL IN ('a', 'b')|'a' IN ('a', NULL)|
+----------------------+-----------+----------------+------------------+------------------+------------------+
|1                     |0          |1               |1                 |NULL              |1                 |
+----------------------+-----------+----------------+------------------+------------------+------------------+

有部分资料说：如果给定的值为NULL，或者IN列表中存在NULL，则结果为NULL。
实际上，在IN列表中存在NULL，并不会导致结果为NULL。

LIKE，模糊匹配

LIKE，用于模糊匹配，如果满足条件则返回1，否则返回0。
通常与如下两个通配符配合使用：

%：匹配0个或多个字符。
_：能且只能匹配一个字符。

示例代码：

SELECT 'aabcc' LIKE '%b%',
       'aabcc' LIKE '_b_',
       'abc' LIKE '%b%',
       'abc' LIKE '_b_',
       'b' LIKE '%b%',
       'b' LIKE '_b_',
       NULL LIKE '_b_',
       'abc' LIKE NULL;

运行结果：

+------------------+------------------+----------------+----------------+--------------+--------------+---------------+---------------+
|'aabcc' LIKE '%b%'|'aabcc' LIKE '_b_'|'abc' LIKE '%b%'|'abc' LIKE '_b_'|'b' LIKE '%b%'|'b' LIKE '_b_'|NULL LIKE '_b_'|'abc' LIKE NULL|
+------------------+------------------+----------------+----------------+--------------+--------------+---------------+---------------+
|1                 |0                 |1               |1               |1             |0             |NULL           |NULL           |
+------------------+------------------+----------------+----------------+--------------+--------------+---------------+---------------+

如果，LIKE的关键词中有%或_，可以使用\，转义运算符。

示例代码：

1	SELECT 'aab_ccccc' LIKE '%b\_%';

运行结果：

+------------------------+
|'aab_ccccc' like '%b\_%'|
+------------------------+
|1                       |
+------------------------+

REGEXP，正则

REGEXP运算符用来匹配字符串，语法格式为：expr REGEXP 匹配条件，如果expr满足匹配条件，返回1；如果不满足，则返回0。

常见匹配条件有：

^【某个字符】，匹配以某个字符开头的字符串。
【某个字符】$，匹配以某个字符结尾的字符串。
.，匹配任何一个单字符。
[...]，匹配在方括号内的任何字符。例如，[abc]匹配a或b或c。[a-z]匹配任何字母，[0-9]匹配任何数字。
【某个字符】*，匹配零个或多个的某个字符。例如，x*匹配任何数量的x字符，[0-9]*匹配任何数量的数字，而.*匹配任何数量的任何字符。

关于正则表达式，可以参考：https://www.runoob.com/regexp/regexp-tutorial.html

示例代码：

SELECT 'shkstart' REGEXP '^s',
       'shkstart' REGEXP 't$',
       'shkstart' REGEXP 'hk',
       'guigu' REGEXP 'gu.gu',
       'guigu' REGEXP '[iam]',
       'ohhhhhh' REGEXP 'o*';

运行结果：

+----------------------+----------------------+----------------------+----------------------+----------------------+---------------------+
|'shkstart' REGEXP '^s'|'shkstart' REGEXP 't$'|'shkstart' REGEXP 'hk'|'guigu' REGEXP 'gu.gu'|'guigu' REGEXP '[iam]'|'ohhhhhh' REGEXP 'o*'|
+----------------------+----------------------+----------------------+----------------------+----------------------+---------------------+
|1                     |1                     |1                     |1                     |1                     |1                    |
+----------------------+----------------------+----------------------+----------------------+----------------------+---------------------+

逻辑运算符

运算符	作用
`NOT` `!`	非
`AND` `&&`	与
`OR` `\|\|`	或
`XOR` `!`	异或

NOT，非

示例代码：

1	SELECT NOT 1, NOT 0, NOT(1+1), NOT !1, NOT NULL;

运行结果：

+-----+-----+--------+------+--------+
|NOT 1|NOT 0|NOT(1+1)|NOT !1|NOT NULL|
+-----+-----+--------+------+--------+
|0    |1    |0       |1     |NULL    |
+-----+-----+--------+------+--------+

示例代码：

1	SELECT NULL IS NOT NULL, NOT ISNULL(NULL), NOT ISNULL('a'), 1 IS NOT NULL;

运行结果：

+----------------+----------------+---------------+-------------+
|NULL IS NOT NULL|NOT ISNULL(NULL)|NOT ISNULL('a')|1 IS NOT NULL|
+----------------+----------------+---------------+-------------+
|0               |0               |1              |1            |
+----------------+----------------+---------------+-------------+

示例代码：

1	SELECT 1 NOT BETWEEN 0 AND 1, 2 NOT BETWEEN 0 AND 1, 'b' NOT BETWEEN 'a' AND 'c';

运行结果：

+---------------------+---------------------+---------------------------+
|1 NOT BETWEEN 0 AND 1|2 NOT BETWEEN 0 AND 1|'b' NOT BETWEEN 'a' AND 'c'|
+---------------------+---------------------+---------------------------+
|0                    |1                    |0                          |
+---------------------+---------------------+---------------------------+

示例代码：

1	SELECT 'a' NOT IN ('a', 'b', 'c'), 1 NOT IN (2, 3), NULL NOT IN ('a', 'b'), 'a' NOT IN ('a', NULL);

运行结果：

+--------------------------+----------------+----------------------+----------------------+
|'a' NOT IN ('a', 'b', 'c')|1  NOT IN (2, 3)|NULL NOT IN ('a', 'b')|'a' NOT IN ('a', NULL)|
+--------------------------+----------------+----------------------+----------------------+
|0                         |1               |NULL                  |0                     |
+--------------------------+----------------+----------------------+----------------------+

AND，与

示例代码：

1	SELECT 1 AND -1, 0 AND 1, 0 AND NULL, 1 AND NULL;

运行结果：

+--------+-------+----------+----------+
|1 AND -1|0 AND 1|0 AND NULL|1 AND NULL|
+--------+-------+----------+----------+
|1       |0      |0         |NULL      |
+--------+-------+----------+----------+

OR，或

示例代码：

1	SELECT 1 OR -1, 1 OR 0, 1 OR NULL, 0 \|\| NULL, NULL \|\| NULL;

运行结果：

+-------+------+---------+---------+------------+
|1 OR -1|1 OR 0|1 OR NULL|0 || NULL|NULL || NULL|
+-------+------+---------+---------+------------+
|1      |1     |1        |NULL     |NULL        |
+-------+------+---------+---------+------------+

XOR，异或

XOR，异或，主要用来判断两个值是否不同。

示例代码：

SELECT 1 XOR -1,
       1 XOR 0,
       0 XOR 0,
       1 XOR '1',
       1 XOR 'ONE',
       0 XOR 'ONE',
       1 XOR TRUE,
       0 XOR TRUE,
       1 XOR 1 XOR 1,
       0 XOR 0 XOR 0,
       1 XOR NULL;

运行结果：

+--------+-------+-------+---------+-----------+-----------+----------+----------+-------------+-------------+----------+
|1 XOR -1|1 XOR 0|0 XOR 0|1 XOR '1'|1 XOR 'ONE'|0 XOR 'ONE'|1 XOR TRUE|0 XOR TRUE|1 XOR 1 XOR 1|0 XOR 0 XOR 0|1 XOR NULL|
+--------+-------+-------+---------+-----------+-----------+----------+----------+-------------+-------------+----------+
|0       |1      |0      |0        |1          |0          |0         |1         |1            |0            |NULL      |
+--------+-------+-------+---------+-----------+-----------+----------+----------+-------------+-------------+----------+

运算符的优先级

优先级由低到高排列	运算符
1	`=`(赋值运算)、`:=`
2	`\|\|`、`OR`
3	`XOR`
4	`&&`、`AND`
5	`NOT`
6	`BETWEEN`、`CASE`、`WHEN`、`THEN`、`ELSE`
7	`=`、`<=>`、`>=`、`>`、`<=`、`<`、`<>`、`!=`、`IS`、`LIKE`、`REGEXP`、`IN`
8	`\|`
9	`&`
10	`<<`、`>>`
11	`-`(减号)、`+`
12	`*`、`/`、`%`
13	`^`
14	`-`(负号)、`~`(位反转）
15	`!`
16	`()`

特别注意：OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。

记不住怎么办？
不用记。
多敲几个括号()，耽误不了多大功夫。

排序

排序规则

使用ORDER BY子句排序。

ASC，ascend，升序(从小到大)，默认升序
DESC，descend，降序(从大到小)。

注意事项有：

ORDER BY子句在SELECT语句的结尾。
可以使用不在SELECT列表中的列排序。
可以使用别名进行排序。
NULL值最小。

单列排序

默认升序，示例代码：

1
2
3

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date;

运行结果：

+-----------+----------+-------------+----------+
|last_name  |job_id    |department_id|hire_date |
+-----------+----------+-------------+----------+
|King       |AD_PRES   |90           |1987-06-17|
|Whalen     |AD_ASST   |10           |1987-09-17|
|Kochhar    |AD_VP     |90           |1989-09-21|
|Hunold     |IT_PROG   |60           |1990-01-03|
|Ernst      |IT_PROG   |60           |1991-05-21|

【部分运行结果略】

|Lee        |SA_REP    |80           |2000-02-23|
|Markle     |ST_CLERK  |50           |2000-03-08|
|Ande       |SA_REP    |80           |2000-03-24|
|Banda      |SA_REP    |80           |2000-04-21|
|Kumar      |SA_REP    |80           |2000-04-21|
+-----------+----------+-------------+----------+

指定降序，示例代码：

1
2
3

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC;

运行结果：

+-----------+----------+-------------+----------+
|last_name  |job_id    |department_id|hire_date |
+-----------+----------+-------------+----------+
|Banda      |SA_REP    |80           |2000-04-21|
|Kumar      |SA_REP    |80           |2000-04-21|
|Ande       |SA_REP    |80           |2000-03-24|
|Markle     |ST_CLERK  |50           |2000-03-08|
|Lee        |SA_REP    |80           |2000-02-23|

【部分运行结果略】

|Ernst      |IT_PROG   |60           |1991-05-21|
|Hunold     |IT_PROG   |60           |1990-01-03|
|Kochhar    |AD_VP     |90           |1989-09-21|
|Whalen     |AD_ASST   |10           |1987-09-17|
|King       |AD_PRES   |90           |1987-06-17|
+-----------+----------+-------------+----------+

可以使用别名进行排序，示例代码：

1
2
3

SELECT employee_id, last_name, salary * 12 as "annsal"
FROM employees
ORDER BY annsal;

运行结果：

+-----------+-----------+------+
|employee_id|last_name  |annsal|
+-----------+-----------+------+
|132        |Olson      |25200 |
|128        |Markle     |26400 |
|136        |Philtanker |26400 |
|127        |Landry     |28800 |
|135        |Gee        |28800 |

【部分运行结果略】

|146        |Partners   |162000|
|145        |Russell    |168000|
|101        |Kochhar    |204000|
|102        |De Haan    |204000|
|100        |King       |288000|
+-----------+-----------+------+

NULL值最小，示例代码：

1
2
3

SELECT *
FROM departments
ORDER BY manager_id

运行结果：

+-------------+--------------------+----------+-----------+
|department_id|department_name     |manager_id|location_id|
+-------------+--------------------+----------+-----------+
|140          |Control And Credit  |NULL      |1700       |
|270          |Payroll             |NULL      |1700       |
|260          |Recruiting          |NULL      |1700       |
|250          |Retail Sales        |NULL      |1700       |
|240          |Government Sales    |NULL      |1700       |

【部分运行结果略】

|10           |Administration      |200       |1700       |
|20           |Marketing           |201       |1800       |
|40           |Human Resources     |203       |2400       |
|70           |Public Relations    |204       |2700       |
|110          |Accounting          |205       |1700       |
+-------------+--------------------+----------+-----------+

多列排序

在对多列进行排序的时候，首先对第一列进行排序，只有当第一列的有相同的列值时，才会对第二列进行排序。
(即，排序的稳定性。我们在《算法入门经典(Java与Python描述)：5.排序》中，讨论过。)

在对多列进行排序的时候，未指定的字段，依旧按照默认升序，指定的字段，按照按照指定的方式进行排列；示例代码：

1
2
3

SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;

运行结果：

+-----------+-------------+------+
|last_name  |department_id|salary|
+-----------+-------------+------+
|Grant      |NULL         |7000  |
|Whalen     |10           |4400  |
|Hartstein  |20           |13000 |
|Fay        |20           |6000  |
|Raphaely   |30           |11000 |

【部分运行结果略】

|Urman      |100          |7800  |
|Sciarra    |100          |7700  |
|Popp       |100          |6900  |
|Higgins    |110          |12000 |
|Gietz      |110          |8300  |
+-----------+-------------+------+

分页

MySQL中使用LIMIT实现分页。
格式：LIMIT [位置偏移量,] 行数

位置偏移量参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定位置偏移量，将会从表中的第一条记录开始(第一条记录的位置偏移量是0)。
第二个参数行数指示返回的记录条数。

$\text{开始的索引} = (\text{当前的页码} - 1) * \text{每页显示的条数}$

例如，每页10条。
第一页：SELECT * FROM 表名 LIMIT 0,10;、SELECT * FROM 表名 LIMIT 10;。
第二页：SELECT * FROM 表名 LIMIT 10,10;。
第三页：SELECT * FROM 表名 LIMIT 20,10;。

在MySQL 8.0中，还可以使用LIMIT 3 OFFSET 4，和LIMIT 4,3效果相同。

在实际业务中，通常还需要返回总页数：

$\text{总页数} = CEIL\bigg(\frac{\text{总条数}}{\text{每页显示的条数}}\bigg)$

$CEIL$ ：向上取整。

关联查询

关联查询，也称多表查询，指多张表一起完成查询操作。

笛卡尔积

我们先讨论笛卡尔积。因为只要多张表一起完成查询，就很有可能会有笛卡尔积。

例子

假设存在三张表如下：
employees：

Field	Type	Null	Key	Default
employee_id	int	NO	PRI	0
first_name	varchar(20)	YES		NULL
last_name	varchar(25)	NO		NULL
email	varchar(25)	NO	UNI	NULL
phone_number	varchar(20)	YES		NULL
hire_date	date	NO		NULL
job_id	varchar(10)	NO	MUL	NULL
salary	double(8,2)	YES		NULL
commission_pct	double(2,2)	YES		NULL
manager_id	int	YES	MUL	NULL
department_id	int	YES	MUL	NULL

departments：

Field	Type	Null	Key	Default
department_id	int	NO	PRI	0
department_name	varchar(30)	NO		NULL
manager_id	int	YES	MUL	NULL
location_id	int	YES	MUL	NULL

locations：

Field	Type	Null	Key	Default
location_id	int	NO	PRI	0
street_address	varchar(40)	YES		NULL
postal_code	varchar(12)	YES		NULL
city	varchar(30)	NO		NULL
state_province	varchar(25)	YES		NULL
country_id	char(2)	YES	MUL	NULL

现在，我们要查询员工的姓名及其部门名称。
员工的姓名在employees表中，部门名称在departments表中。
来吧！

示例代码：

1
2
3

SELECT last_name, department_name
FROM employees,
     departments;

运行结果：

+-----------+--------------------+
|last_name  |department_name     |
+-----------+--------------------+
|King       |Payroll             |
|King       |Recruiting          |
|King       |Retail Sales        |
|King       |Government Sales    |
|King       |IT Helpdesk         |

【部分运行结果略】

|Gietz      |Shipping            |
|Gietz      |Human Resources     |
|Gietz      |Purchasing          |
|Gietz      |Marketing           |
|Gietz      |Administration      |
+-----------+--------------------+

好像不太对，好像每个人，都属于任何一个部门。

特别的，我们还可以查查记录数，示例代码：

SELECT count(*)
FROM employees,
     departments
UNION ALL
SELECT count(*)
FROM departments
UNION ALL
SELECT count(*)
FROM employees;

运行结果：

+--------+
|count(*)|
+--------+
|2889    |
|27      |
|107     |
+--------+

$27 * 107$ 恰好等于 $2889$

这就是出现笛卡尔积了。

笛卡尔积

笛卡尔积，一种数学运算。

假设我有两个集合X和Y，那么X和Y的所有可能组合，就是就是X和Y的笛卡尔积，组合的个数即为两个集合中元素个数的乘积数。

例如：

在上文的例子中，employees.last_name和departments.department_name的所有可能组合，就是其笛卡尔积。

以下几种，也是笛卡尔积。
CROSS JOIN(没有连接条件)：

1
2
3

SELECT last_name, department_name
FROM employees
CROSS JOIN departments;

INNER JOIN(没有连接条件)：

1
2
3

SELECT last_name, department_name
FROM employees
INNER JOIN departments;

JOIN(没有连接条件)：

1
2
3

SELECT last_name, department_name
FROM employees
JOIN departments;

表的连接条件

那么，怎么解决问题呢？
笛卡尔积的"错误"出现的原因是缺少表的连接条件，所以，解决问题的关键点是添加表的连接条件。

例如：

SELECT last_name, department_name
FROM employees,
     departments
WHERE employees.department_id = departments.department_id;

1
2
3

SELECT last_name, department_name
FROM employees
         CROSS JOIN departments ON employees.department_id = departments.department_id;

1
2
3

SELECT last_name, department_name
FROM employees
         INNER JOIN departments ON employees.department_id = departments.department_id;

1
2
3

SELECT last_name, department_name
FROM employees
         JOIN departments ON employees.department_id = departments.department_id;

关联查询的分类

根据连接条件，可以分为：
1. 等值连接
2. 非等值连接
根据关联的表是否同一张，可以分为：
1. 自连接
2. 非自连接
根据关联方式，可以分为：
1. 内连接
2. 外连接

等值连接与非等值连接

等值连接

等值连接，是指关联条件是"相等"。

示例代码：

SELECT employees.employee_id,
       employees.last_name,
       employees.department_id,
       departments.department_id,
       departments.location_id
FROM employees,
     departments
WHERE employees.department_id = departments.department_id;

SELECT e.employee_id,
       e.last_name,
       e.department_id,
       d.department_id,
       d.location_id
FROM employees e,
     departments d
WHERE e.department_id = d.department_id;

解释说明：

多个表中有相同列时，必须在列名之前加上表名前缀。
可以使用表的别名。需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。

强烈建议指定表

在阿里巴巴的开发规范中，对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名(或表名)进行限定。
正例：

SELECT t1.name
FROM table_first AS t1,
     table_second AS t2
WHERE t1.id = t2.id;

反例：
在某业务中，由于多表关联查询语句没有加表的别名(或表名)的限制，正常运行两年后，最近在某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现1052异常：Column ‘name’ in field list is ambiguous。

非等值连接

非等值连接，顾名思义，连接条件不是"相等"。
示例代码：

SELECT e.last_name, e.salary, j.grade_level
FROM employees e,
     job_grades j
WHERE e.salary != j.lowest_sal;

SELECT e.last_name, e.salary, j.grade_level
FROM employees e,
     job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

自连接与非自连接

自连接： table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。反之，如果不是同一张表，则被称为非自连接。

示例代码：

SELECT CONCAT(worker.last_name, ' works for '
           , manager.last_name)
FROM employees worker,
     employees manager
WHERE worker.manager_id = manager.employee_id;

运行结果：

+----------------------------------------------------------------------+
|CONCAT(worker.last_name, ' works for '                                |
|, manager.last_name)                                                  |
+----------------------------------------------------------------------+
|Kochhar works for King                                                |
|De Haan works for King                                                |
|Hunold works for De Haan                                              |
|Ernst works for Hunold                                                |
|Austin works for Hunold                                               |

【部分运行结果略】

|Fay works for Hartstein                                               |
|Mavris works for Kochhar                                              |
|Baer works for Kochhar                                                |
|Higgins works for Kochhar                                             |
|Gietz works for Higgins                                               |
+----------------------------------------------------------------------+

内连接和外连接

内连接和外连接，是关联查询的分类的第三种分类方式，根据关联方式进行分类。

内连接

关键字有：JOIN、INNER JOIN、CROSS JOIN。

取交集。

语法：

1 2	SELECT 字段列表 FROM A表 INNER JOIN B表 ON 关联条件

示例代码：

SELECT e.employee_id,
       e.last_name,
       e.department_id,
       d.department_id,
       d.location_id
FROM employees e
         JOIN departments d
              ON e.department_id = d.department_id;

SELECT e.employee_id, l.city, d.department_name
FROM employees e
         JOIN departments d
              ON d.department_id = e.department_id
         JOIN locations l
              ON d.location_id = l.location_id;

外连接

外连接，OUTER JOIN，可以分为三种：

左外连接，LEFT OUTER JOIN
右外连接，RIGHT OUTER JOIN
满外连接，FULL OUTER JOIN

左外连接

LEFT JOIN，以左边的表为准，右边的表没有的数据补NULL。

语法：

1 2	SELECT 字段列表 FROM A表 LEFT JOIN B表 ON 关联条件

示例代码：

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
         LEFT JOIN departments d
                   ON e.department_id = d.department_id;

运行结果：

+-----------+-------------+----------------+
|last_name  |department_id|department_name |
+-----------+-------------+----------------+
|King       |90           |Executive       |
|Kochhar    |90           |Executive       |
|De Haan    |90           |Executive       |

【部分运行结果略】

|Grant      |NULL         |NULL            |

【部分运行结果略】

|Baer       |70           |Public Relations|
|Higgins    |110          |Accounting      |
|Gietz      |110          |Accounting      |
+-----------+-------------+----------------+

解释说明：有一行的department_id和department_name是NULL。

右外连接

RIGHT JOIN，以右边的表为准，左边的表没有的数据补NULL。

语法：

1 2	SELECT 字段列表 FROM A表 RIGHT JOIN B表 ON 关联条件

示例代码：

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
         RIGHT JOIN departments d
                    ON e.department_id = d.department_id;

运行结果：

+-----------+-------------+--------------------+
|last_name  |department_id|department_name     |
+-----------+-------------+--------------------+
|Whalen     |10           |Administration      |
|Hartstein  |20           |Marketing           |
|Fay        |20           |Marketing           |
|Raphaely   |30           |Purchasing          |
|Khoo       |30           |Purchasing          |

【部分运行结果略】

|NULL       |NULL         |IT Helpdesk         |
|NULL       |NULL         |Government Sales    |
|NULL       |NULL         |Retail Sales        |
|NULL       |NULL         |Recruiting          |
|NULL       |NULL         |Payroll             |
+-----------+-------------+--------------------+

解释说明：有若干行的last_name和department_id是NULL。

满外连接

FULL OUTER JOIN，满外连接。

$\text{满外连接的结果} = \text{左右表匹配的数据} + \text{左表没有匹配到的数据} + \text{右表没有匹配到的数据}$

需要注意的是，MySQL不支持FULL JOIN，但是可以用UNION的方式代替。

示例代码：

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
         LEFT JOIN departments d
                   ON e.department_id = d.department_id
UNION
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
         RIGHT JOIN departments d
                    ON e.department_id = d.department_id;

七种JOIN

查询以左边的表为准，右边没有的数据补NULL。
左连接。
示例代码：

1
2
3

SELECT employee_id, last_name, department_name
FROM employees e
         LEFT JOIN departments d ON e.`department_id` = d.`department_id`;

查询以右边的表为准，左边没有的数据补NULL。
右连接。
示例代码：

1
2
3

SELECT employee_id, last_name, department_name
FROM employees e
         RIGHT JOIN departments d ON e.`department_id` = d.`department_id`;

查询左边和右边都有数据的。
内连接。
示例代码：

1
2
3

SELECT employee_id, last_name, department_name
FROM employees e
         JOIN departments d ON e.`department_id` = d.`department_id`;

查询左边有，且右边没有的。
先做左连接，然后剔除掉右边为NULL的。
示例代码：

SELECT employee_id, last_name, department_name
FROM employees e
         LEFT JOIN departments d ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

查询右边有，且左边没有的。
先做右连接，然后剔除掉左边为NULL的。
示例代码：

SELECT employee_id, last_name, department_name
FROM employees e
         RIGHT JOIN departments d ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

查询左边和右边的全连接
用UNION

SELECT employee_id, last_name, department_name
FROM employees e
         LEFT JOIN departments d ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id, last_name, department_name
FROM employees e
         RIGHT JOIN departments d ON e.`department_id` = d.`department_id`;

查询左边有右边没有和右边有左边没有的

SELECT employee_id, last_name, department_name
FROM employees e
         LEFT JOIN departments d ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id, last_name, department_name
FROM employees e
         RIGHT JOIN departments d ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

UNION

UNION，格式如下：

1
2
3

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

其中：

UNION，去重，效率较低。
UNION ALL，不去重，效率较高。

如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。
(在《5.索引和优化》，我们还会讨论，UNION ALL效率更高的具体原因。)

规范

在阿里巴巴的开发规范中，超过三个表禁止JOIN。
需要JOIN的字段，数据类型保持绝对一致。
多表关联查询时，保证被关联的字段有索引。

(后两个规范要求，和效率有关，在《5.索引和优化》会讨论。)

单行函数

函数的分类

按照入参是一行还是多行，MySQL中的内置函数可以分为：

单行函数
入参只有一行，例如：数值函数、字符串函数、日期和时间函数、流程控制函数、MySQL信息函数。
多行函数
入参有多行，例如：聚合函数。

数值函数

函数	用法
`ABS(x)`	返回绝对值
`SIGN(x)`	返回符号，正数返回 $1$ ，负数返回 $-1$ ， $0$ 返回 $0$
`PI()`	返回圆周率
`CEIL(x)` `CEILING(x)`	返回大于或等于某个值的最小整数
`FLOOR(x)`	返回小于或等于某个值的最大整数
`LEAST(e1,e2,e3…)`	返回列表中的最小值
`GREATEST(e1,e2,e3…)`	返回列表中的最大值
`MOD(x,y)`	返回 $x$ 除以 $y$ 后的余数
`RAND()`	返回 $[0,1]$ 的随机值
`RAND(x)`	返回 $[0,1]$ 的随机值，其中 $x$ 的值用作随机数种子
`ROUND(x)`	对 $x$ 进行四舍五入，返回整数。
`ROUND(x,y)`	对 $x$ 进行四舍五入，并保留到小数点后面 $y$ 位
`TRUNCATE(x,y)`	返回数字 $x$ 截断为 $y$ 位小数的结果
`SQRT(x)`	返回 $x$ 的平方根。当 $x$ 的值为负数时，返回`NULL`
`FORMAT(value,n)`	返回对数字value进行格式化后的结果数据，n表示四舍五入后保留到小数点后n位，不足的补`0`

绝对值、符号、圆周率、向上取整、向下取整、余数，示例代码：

SELECT ABS(-123),
       ABS(32),
       SIGN(-23),
       SIGN(43),
       PI(),
       CEIL(32.32),
       CEILING(-43.23),
       FLOOR(32.32),
       FLOOR(-43.23),
       MOD(12, 5);

运行结果：

+---------+-------+---------+--------+--------+-----------+---------------+------------+-------------+----------+
|ABS(-123)|ABS(32)|SIGN(-23)|SIGN(43)|PI()    |CEIL(32.32)|CEILING(-43.23)|FLOOR(32.32)|FLOOR(-43.23)|MOD(12, 5)|
+---------+-------+---------+--------+--------+-----------+---------------+------------+-------------+----------+
|123      |32     |-1       |1       |3.141593|33         |-43            |32          |-44          |2         |
+---------+-------+---------+--------+--------+-----------+---------------+------------+-------------+----------+

随机数，示例代码：

1	SELECT RAND(), RAND(), RAND(10), RAND(10), RAND(-1), RAND(-1)

运行结果：

+-----------------+-------------------+------------------+------------------+------------------+------------------+
|RAND()           |RAND()             |RAND(10)          |RAND(10)          |RAND(-1)          |RAND(-1)          |
+-----------------+-------------------+------------------+------------------+------------------+------------------+
|0.504043546043377|0.31395704421583287|0.6570515219653505|0.6570515219653505|0.9050373219931845|0.9050373219931845|
+-----------------+-------------------+------------------+------------------+------------------+------------------+

四舍五入(ROUND)和截断(TRUNCATE)，示例代码：

1	SELECT ROUND(13.579), ROUND(13.579, 2), ROUND(13.579, -1), TRUNCATE(13.579, 2), TRUNCATE(13.579, -1);

运行结果：

+-------------+----------------+-----------------+-------------------+--------------------+
|ROUND(13.579)|ROUND(13.579, 2)|ROUND(13.579, -1)|TRUNCATE(13.579, 2)|TRUNCATE(13.579, -1)|
+-------------+----------------+-----------------+-------------------+--------------------+
|14           |13.58           |10               |13.57              |10                  |
+-------------+----------------+-----------------+-------------------+--------------------+

格式化，不足的补0，示例代码：

SELECT FORMAT(123.123, 2),
       FORMAT(123.523, 0),
       FORMAT(123.123, 0),
       FORMAT(123.123, -1),
       FORMAT(123.123, -2),
       FORMAT(123, 2);

运行结果：

+------------------+------------------+------------------+-------------------+-------------------+--------------+
|FORMAT(123.123, 2)|FORMAT(123.523, 0)|FORMAT(123.123, 0)|FORMAT(123.123, -1)|FORMAT(123.123, -2)|FORMAT(123, 2)|
+------------------+------------------+------------------+-------------------+-------------------+--------------+
|123.12            |124               |123               |123                |123                |123.00        |
+------------------+------------------+------------------+-------------------+-------------------+--------------+

解释说明：如果n的值小于或等于0，则只保留整数部分。

除了上述的，数值函数还有很多，例如：

角度与弧度函数：
- RADIANS(x)：将角度转化为弧度。
- DEGREES(x)：将弧度转化为角度。
三角函数：
- SIN(x)：返回正弦值，其中，参数 $x$ 为弧度值。
- COS(x)：返回余弦值，其中，参数 $x$ 为弧度值。
指数与对数：
- POW(x,y)，POWER(X,Y)：返回 $x$ 的 $y$ 次方。
- EXP(x)：返回 $e$ 的 $x$ 次方。
- LN(x)，LOG(x)：返回以 $e$ 为底的 $x$ 的对数，当 $x<=0$ 时，返回NULL。
- LOG10(x)：返回以 $10$ 为底的 $x$ 的对数，当 $x<=0$ 时，返回NULL。
- LOG2(x)：返回以 $2$ 为底的 $x$ 的对数，当 $x<=0$ 时，返回NULL。
进制间的转换：
- BIN(x)：返回二进制编码。
- HEX(x)：返回十六进制编码。
- OCT(x)：返回八进制编码。
- CONV(value,from,to)：将value的值进行不同进制之间的转换

示例代码：

SELECT SIN(RADIANS(30)),
       COS(RADIANS(30)),
       POW(2, 5),
       POWER(2, 4),
       EXP(2),
       LN(10),
       LOG10(10),
       LOG2(4),
       BIN(10),
       HEX(10),
       OCT(10),
       CONV(10, 2, 8),
       CONV(16, 10, 2),
       CONV(16, 10, 16),
       CONV(NULL, 10, 2);

运行结果：

+-------------------+------------------+---------+-----------+----------------+-----------------+---------+-------+-------+-------+-------+--------------+---------------+----------------+-----------------+
|SIN(RADIANS(30))   |COS(RADIANS(30))  |POW(2, 5)|POWER(2, 4)|EXP(2)          |LN(10)           |LOG10(10)|LOG2(4)|BIN(10)|HEX(10)|OCT(10)|CONV(10, 2, 8)|CONV(16, 10, 2)|CONV(16, 10, 16)|CONV(NULL, 10, 2)|
+-------------------+------------------+---------+-----------+----------------+-----------------+---------+-------+-------+-------+-------+--------------+---------------+----------------+-----------------+
|0.49999999999999994|0.8660254037844387|32       |16         |7.38905609893065|2.302585092994046|1        |2      |1010   |A      |12     |2             |10000          |10              |NULL             |
+-------------------+------------------+---------+-----------+----------------+-----------------+---------+-------+-------+-------+-------+--------------+---------------+----------------+-----------------+

字符串函数

函数	用法
`ASCII(s)`	返回字符串s中的第一个字符的ASCII码值
`CHAR_LENGTH(s)` `CHARACTER_LENGTH(s)`	返回字符串s的字符数
`LENGTH(s)`	返回字符串s的字节数，具体字节数和字符集有关
`CONCAT(s1,s2,...,sn)`	连接"s1,s2,…,sn"为一个字符串
`CONCAT_WS(x, s1,s2,...,sn)`	同`CONCAT(s1,s2,...,sn)`函数，但在"s1,s2,…,sn"之间要加上指定的字符`x`
`INSERT(str, idx, len, replacestr)`	将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
`REPLACE(str, a, b)`	将字符串str中所有出现的字符串a，替换成b
`UPPER(s)` `UCASE(s)`	将字符串s的所有字母转成大写字母
`LOWER(s)` `LCASE(s)`	将字符串s的所有字母转成小写字母
`LEFT(str,n)`	返回字符串str最左边的n个字符
`RIGHT(str,n)`	返回字符串str最右边的n个字符
`LPAD(str, len, pad)`	用字符串pad对str最左边进行填充，直到str的长度为len个字符
`RPAD(str ,len, pad)`	用字符串pad对str最右边进行填充，直到str的长度为len个字符
`LTRIM(s)`	去掉字符串s左侧的空格
`RTRIM(s)`	去掉字符串s右侧的空格
`TRIM(s)`	去掉字符串s开始与结尾的空格
`TRIM(s1 FROM s)`	去掉字符串s开始与结尾的s1
`TRIM(LEADING s1 FROM s)`	去掉字符串s开始处的s1
`TRIM(TRAILING s1 FROM s)`	去掉字符串s结尾处的s1
`REPEAT(str, n)`	str重复n次的结果
`SPACE(n)`	n个空格
`STRCMP(s1,s2)`	比较字符串s1,s2的ASCII码值的大小
`SUBSTR(s,index,len)` `SUBSTRING(s,n,len)` `MID(s,n,len)`	从字符串s的index位置其len个字符
`LOCATE(substr,str)` `POSITION(substr IN str)` `INSTR(str,substr)`	字符串substr在字符串str中首次出现的位置；未找到，返回0
`ELT(m,s1,s2,…,sn)`	返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，以此类推
`FIELD(s,s1,s2,…,sn)`	返回字符串s在字符串列表中第一次出现的位置
`FIND_IN_SET(s1,s2)`	返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串
`REVERSE(s)`	返回s反转后的字符串
`NULLIF(value1,value2)`	比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

注意！在MySQL中，字符串的位置是从1开始的。

查找某个字符串在另一个字符串中的位置，示例代码：

SELECT LOCATE('s', 'str'),
       POSITION('s' IN 'str'),
       INSTR('str', 's'),
       LOCATE('a', 'str'),
       POSITION('a' IN 'str'),
       INSTR('str', 'a');

运行结果：

+------------------+----------------------+-----------------+------------------+----------------------+-----------------+
|LOCATE('s', 'str')|POSITION('s' IN 'str')|INSTR('str', 's')|LOCATE('a', 'str')|POSITION('a' IN 'str')|INSTR('str', 'a')|
+------------------+----------------------+-----------------+------------------+----------------------+-----------------+
|1                 |1                     |1                |0                 |0                     |0                |
+------------------+----------------------+-----------------+------------------+----------------------+-----------------+

在Java，Python和JavaScript中，也都有类似的功能，如下：

示例代码：

System.out.println("str".contains("s"));
System.out.println("str".contains("a"));

System.out.println("str".indexOf("s"));
System.out.println("str".indexOf("a"));

运行结果：

true
false
0
-1

示例代码：

print('str'.find('s'))
print('str'.find('a'))

print('str'.index('s'))
print('str'.index('a'))

运行结果：

0
-1
0
ValueError: substring not found

示例代码：

1 2	console.log('str'.indexOf('s')) console.log('str'.indexOf('a'))

运行结果：

1
2

0
-1

返回字符串s在字符串列表中第一次出现的位置，示例代码：

1	SELECT FIELD('mm', 'hello', 'mm', 'amma'), FIND_IN_SET('mm', 'hello,mm,amma');

运行结果：

+----------------------------------+----------------------------------+
|FIELD('mm', 'hello', 'mm', 'amma')|FIND_IN_SET('mm', 'hello,mm,amma')|
+----------------------------------+----------------------------------+
|2                                 |2                                 |
+----------------------------------+----------------------------------+

比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1，示例代码：

1	SELECT NULLIF('mysql', 'mysql'), NULLIF('mysql', '');

运行结果：

+------------------------+-------------------+
|NULLIF('mysql', 'mysql')|NULLIF('mysql', '')|
+------------------------+-------------------+
|NULL                    |mysql              |
+------------------------+-------------------+

日期和时间函数

获取日期、时间

函数	用法
`CURDATE()` `CURRENT_DATE()`	返回当前日期，只包含年、月、日
`CURTIME()` `CURRENT_TIME()`	返回当前时间，只包含时、分、秒
`NOW()` `CURRENT_TIMESTAMP()` `LOCALTIME()` `LOCALTIMESTAMP()`	返回当前系统日期和时间(语句执行时刻)
`SYSDATE()`	返回当前系统日期和时间(动态)
`UTC_DATE()`	返回UTC(世界标准时间)日期
`UTC_TIME()`	返回UTC(世界标准时间)时间

当前日期、当前时间、当前日期和时间、UTC(世界标准时间)日期、UTC(世界标准时间)时间，示例代码：

SELECT CURDATE(),
       CURTIME(),
       NOW(),
       UTC_DATE(),
       UTC_TIME();

运行结果：

+----------+---------+-------------------+----------+----------+
|CURDATE() |CURTIME()|NOW()              |UTC_DATE()|UTC_TIME()|
+----------+---------+-------------------+----------+----------+
|2022-02-28|07:43:13 |2022-02-28 07:43:13|2022-02-27|23:43:13  |
+----------+---------+-------------------+----------+----------+

SYSDATE()和NOW()的区别，示例代码：

SELECT NOW(),
       SYSDATE(),
       CURRENT_TIMESTAMP(),
       LOCALTIME(),
       LOCALTIMESTAMP(),
       SLEEP(3),
       NOW(),
       SYSDATE(),
       CURRENT_TIMESTAMP(),
       LOCALTIME(),
       LOCALTIMESTAMP();

运行结果：

+-------------------+-------------------+-------------------+-------------------+-------------------+--------+-------------------+-------------------+-------------------+-------------------+-------------------+
|NOW()              |SYSDATE()          |CURRENT_TIMESTAMP()|LOCALTIME()        |LOCALTIMESTAMP()   |SLEEP(3)|NOW()              |SYSDATE()          |CURRENT_TIMESTAMP()|LOCALTIME()        |LOCALTIMESTAMP()   |
+-------------------+-------------------+-------------------+-------------------+-------------------+--------+-------------------+-------------------+-------------------+-------------------+-------------------+
|2022-02-28 07:43:53|2022-02-28 07:43:53|2022-02-28 07:43:53|2022-02-28 07:43:53|2022-02-28 07:43:53|0       |2022-02-28 07:43:53|2022-02-28 07:43:56|2022-02-28 07:43:53|2022-02-28 07:43:53|2022-02-28 07:43:53|
+-------------------+-------------------+-------------------+-------------------+-------------------+--------+-------------------+-------------------+-------------------+-------------------+-------------------+

解释说明：NOW()取的是语句开始执行的时间，在整个语句执行过程中都不会变化，SYSDATE()取的是动态的实时时间，在语句的不同执行时刻会存在变化。

日期与时间戳的转换

函数	用法
`UNIX_TIMESTAMP()`	以UNIX时间戳的形式返回当前时间
`UNIX_TIMESTAMP(date)`	将时间date以UNIX时间戳的形式返回
`FROM_UNIXTIME(timestamp)`	将UNIX时间戳的时间转换为普通格式的时间

日期与时间戳的转换，示例代码：

SELECT UNIX_TIMESTAMP(),
       UNIX_TIMESTAMP(NOW()),
       UNIX_TIMESTAMP(CURDATE()),
       UNIX_TIMESTAMP(CURTIME()),
       UNIX_TIMESTAMP('2022-02-15 11:00:00'),
       FROM_UNIXTIME(1666666666);

运行结果：

+----------------+---------------------+-------------------------+-------------------------+-------------------------------------+-------------------------+
|UNIX_TIMESTAMP()|UNIX_TIMESTAMP(NOW())|UNIX_TIMESTAMP(CURDATE())|UNIX_TIMESTAMP(CURTIME())|UNIX_TIMESTAMP('2022-02-15 11:00:00')|FROM_UNIXTIME(1666666666)|
+----------------+---------------------+-------------------------+-------------------------+-------------------------------------+-------------------------+
|1646005700      |1646005700           |1645977600               |1646005700               |1644894000                           |2022-10-25 10:57:46      |
+----------------+---------------------+-------------------------+-------------------------+-------------------------------------+-------------------------+

获取月份、星期、星期数、天数等

函数	用法
`YEAR(date)` `MONTH(date)` `DAY(date)`	返回具体的日期值：年、月、日
`HOUR(time)` `MINUTE(time)` `SECOND(time)`	返回具体的时间值：时、分、秒
`MONTHNAME(date)`	返回月份名：January、February等
`DAYNAME(date)`	返回星期名：Monday、Tuesday等
`WEEKDAY(date)`	返回星期数字：`0`代表周一、`1`代表周二、…、`6`代表周天
`DAYOFWEEK(date)`	返回一周的第几天：周天是`1`、周一是`2`、…、周六是`7`
`QUARTER(date)`	返回日期对应的季度，范围为 $[1,4]$
`WEEK(date)` `WEEKOFYEAR(date)`	返回一年中的第几周
`DAYOFYEAR(date)`	返回日期是一年中的第几天
`DAYOFMONTH(date)`	返回日期位于所在月份的第几天

WEEKDAY(date)和DAYOFWEEK(date)的计数方式不一样。

获取年、月、日、时、分、秒，示例代码：

SELECT YEAR(CURDATE()),
       MONTH(CURDATE()),
       DAY(CURDATE()),
       HOUR(CURTIME()),
       MINUTE(NOW()),
       SECOND(SYSDATE());

运行结果：

+---------------+----------------+--------------+---------------+-------------+-----------------+
|YEAR(CURDATE())|MONTH(CURDATE())|DAY(CURDATE())|HOUR(CURTIME())|MINUTE(NOW())|SECOND(SYSDATE())|
+---------------+----------------+--------------+---------------+-------------+-----------------+
|2022           |2               |15            |11             |7            |8                |
+---------------+----------------+--------------+---------------+-------------+-----------------+

获取月份名、星期名、星期数字、季度、一年中的第几周、一年中的第几天、第一月中的第几天、一周中的第几天，示例代码：

SELECT MONTHNAME('2022-02-15'),
       DAYNAME('2022-02-15'),
       WEEKDAY('2022-02-15'),
       QUARTER(CURDATE()),
       WEEK(CURDATE()),
       DAYOFYEAR(NOW()),
       DAYOFMONTH(NOW()),
       DAYOFWEEK(NOW());

运行结果：

+-----------------------+---------------------+---------------------+------------------+---------------+----------------+-----------------+----------------+
|MONTHNAME('2022-02-15')|DAYNAME('2022-02-15')|WEEKDAY('2022-02-15')|QUARTER(CURDATE())|WEEK(CURDATE())|DAYOFYEAR(NOW())|DAYOFMONTH(NOW())|DAYOFWEEK(NOW())|
+-----------------------+---------------------+---------------------+------------------+---------------+----------------+-----------------+----------------+
|February               |Tuesday              |1                    |1                 |7              |46              |15               |3               |
+-----------------------+---------------------+---------------------+------------------+---------------+----------------+-----------------+----------------+

WEEKDAY(date)，获取星期数字，0代表周一、1代表周二；DAYOFWEEK(date)，返回一周的第几天，周天是1、周一是2。
示例代码：

1	SELECT WEEKDAY(CURDATE()), DAYOFWEEK(CURDATE());

运行结果：

+------------------+--------------------+
|WEEKDAY(CURDATE())|DAYOFWEEK(CURDATE())|
+------------------+--------------------+
|1                 |3                   |
+------------------+--------------------+

时间和秒钟转换

函数	用法
`TIME_TO_SEC(time)`	将`time`转化为秒并返回结果值
`SEC_TO_TIME(seconds)`	将`seconds`描述转化为包含小时、分钟和秒的时间

时间和秒钟转换，示例代码：

1	SELECT TIME_TO_SEC(NOW()), SEC_TO_TIME(TIME_TO_SEC(NOW()));

运行结果：

+------------------+-------------------------------+
|TIME_TO_SEC(NOW())|SEC_TO_TIME(TIME_TO_SEC(NOW()))|
+------------------+-------------------------------+
|42556             |11:49:16                       |
+------------------+-------------------------------+

计算日期和时间的函数

函数	用法
`DATE_ADD(date, INTERVAL expr type)` `ADDDATE(date, INTERVAL expr type)`	在给定日期时间基础上，加上一定的日期时间
`DATE_SUB(date, INTERVAL expr type)` `SUBDATE(date, INTERVAL expr type)`	在给定日期时间基础上，减去一定的日期时间

type的取值为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND，以及其中的某些组合，YEAR_MONTH、DAY_HOUR、DAY_MINUTE、DAY_SECOND、HOUR_MINUTE、HOUR_SECOND、MINUTE_SECOND。

加1天、加1秒、加1秒、加1分1秒、加-1年、加1年1月，示例代码：

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY)                               AS col1,
       DATE_ADD('2022-02-15 14:08:57', INTERVAL 1 SECOND)            AS col2,
       ADDDATE('2022-02-15 14:08:57', INTERVAL 1 SECOND)             AS col3,
       DATE_ADD('2022-02-15 14:08:57', INTERVAL '1_1' MINUTE_SECOND) AS col4,
       DATE_ADD(NOW(), INTERVAL -1 YEAR)                             AS col5,
       DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH)                    AS col6;

运行结果：

+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+
|col1               |col2               |col3               |col4               |col5               |col6               |
+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+
|2022-02-16 14:13:06|2022-02-15 14:08:58|2022-02-15 14:08:58|2022-02-15 14:09:58|2021-02-15 14:13:06|2023-03-15 14:13:06|
+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+

减31天、减31天、减-1天、减1天1小时，示例代码：

SELECT DATE_SUB('2022-02-15', INTERVAL 31 DAY)                  AS col1,
       SUBDATE('2022-02-15', INTERVAL 31 DAY)                   AS col2,
       SUBDATE('2022-02-15', INTERVAL -1 DAY)                   AS col3,
       DATE_SUB('2022-02-15 14:08:57', INTERVAL '1_1' DAY_HOUR) AS col4;

运行结果：

+----------+----------+----------+-------------------+
|col1      |col2      |col2      |col3               |
+----------+----------+----------+-------------------+
|2022-01-15|2022-01-15|2022-02-16|2022-02-14 13:08:57|
+----------+----------+----------+-------------------+

解释说明：可以加上或减去一个负的时间单位。

除了上述，还有其他更灵活的。

函数	用法
`ADDTIME(time1,time2)`	返回`time1`加上`time2`的时间；当`time2`为一个数字时，代表的是秒，可以为负数。
`SUBTIME(time1,time2)`	返回`time1`减去`time2`后的时间；当`time2`为一个数字时，代表的是秒，可以为负数。
`DATEDIFF(date1,date2)`	返回`date1-date2`的日期间隔天数。
`TIMEDIFF(time1,time2)`	返回`time1-time2`的时间间隔。
`FROM_DAYS(N)`	返回从0000年1月1日起，N天以后的日期
`TO_DAYS(date)`	返回日期date距离0000年1月1日的天数
`LAST_DAY(date)`	返回date所在月份的最后一天的日期
`MAKEDATE(year,n)`	针对给定年份与所在年份中的天数返回一个日期
`MAKETIME(hour,minute,second)`	将给定的小时、分钟和秒组合成时间并返回
`PERIOD_ADD(time,n)`	返回time加上n后的时间

日期的格式化与解析

函数	用法
`DATE_FORMAT(date,fmt)`	按照字符串fmt格式化日期date值
`TIME_FORMAT(time,fmt)`	按照字符串fmt格式化时间time值
`STR_TO_DATE(str,fmt)`	按照字符串fmt对str进行解析，解析为一个日期

fmt参数常用的格式符：

年：
%Y：四位数字表示年份
%y：表示两位数字表示年份
月：
%M：月名表示月份(January)
%b：缩写的月名(Jan,Feb)
%m：两位数字表示月份(01,02,03)
%c：数字表示月份(1,2,3)
天：
%D：英文后缀表示月中的天数(1st,2nd,3rd)
%d：两位数字表示月中的天数(01,02,03)
%e：数字形式表示月中的天数(1,2,3)
时：
%H：两位数字表示小数，24小时制(01,02,03)
%h、%I：两位数字表示小时，12小时制(01,02,03)
%k：数字形式的小时，24小时制(1,2,3)
%l：数字形式表示小时，12小时制(1,2,3)
分：
%i：两位数字表示分钟(00,01,02)
秒：
%S、%s：两位数字表示秒(00,01,02)
星期：
%W：一周中的星期名称(Sunday)
%a：一周中的星期缩写(Sun,Mon,Tue)
%w：以数字表示周中的天数(0:Sunday,1:Monday)
天数：
%j：以3位数字表示年中的天数(001,002,003)
周数：
%U：以数字表示年中的第几周(1,2,3)，周天为一周的第一天
%u：以数字表示年中的第几周(1,2,3)，周一为一周的第一天
时制：
%T：24小时制
%r：12小时制
上下午：
%p：AM或PM
转义：
%%：表示%

示例代码：

SELECT DATE_FORMAT(NOW(), '%H:%i:%s'),
       STR_TO_DATE('09/01/2009', '%m/%d/%Y'),
       STR_TO_DATE('20140422154706', '%Y%m%d%H%i%s'),
       STR_TO_DATE('2014-04-22 15:47:06', '%Y-%m-%d %H:%i:%s');

运行结果：

+------------------------------+-------------------------------------+---------------------------------------------+-------------------------------------------------------+
|DATE_FORMAT(NOW(), '%H:%i:%s')|STR_TO_DATE('09/01/2009', '%m/%d/%Y')|STR_TO_DATE('20140422154706', '%Y%m%d%H%i%s')|STR_TO_DATE('2014-04-22 15:47:06', '%Y-%m-%d %H:%i:%s')|
+------------------------------+-------------------------------------+---------------------------------------------+-------------------------------------------------------+
|15:43:57                      |2009-09-01                           |2014-04-22 15:47:06                          |2014-04-22 15:47:06                                    |
+------------------------------+-------------------------------------+---------------------------------------------+-------------------------------------------------------+

这种格式符，我们还在一个地方会用到，《机器学习实战方法(Python)：特征工程-1.特征预处理》的"时间字段的处理"。

流程控制函数

MySQL中的流程处理函数主要包括：

IF(value,value1,value2)：
如果value的值为TRUE，返回value1，否则返回value2。
IFNULL(value1, value2)：
如果value1不为NULL，返回value1，否则返回value2`。
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END：
相当于Java的if...else if...else if...else...
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END：
相当于Java的switch...case...break...default

IF(value,value1,value2)，示例代码：

1	SELECT IF(1 > 0, '正确', '错误');

运行结果：

+---------------------+
|IF(1 > 0, '正确', '错误')|
+---------------------+
|正确                   |
+---------------------+

IFNULL(value1, value2)，示例代码：

1	SELECT IFNULL(null, 'Hello Word');

运行结果：

+--------------------------+
|IFNULL(null, 'Hello Word')|
+--------------------------+
|Hello Word                |
+--------------------------+

解释说明：第一个参数为NULL，所以返回第二个参数。

CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END，示例代码：

1	SELECT CASE WHEN 1 > 0 THEN '1 > 0' WHEN 2 > 0 THEN '2 > 0' ELSE '3 > 0' END;

运行结果：

+---------------------------------------------------------------------+
|CASE WHEN 1 > 0 THEN '1 > 0' WHEN 2 > 0 THEN '2 > 0' ELSE '3 > 0' END|
+---------------------------------------------------------------------+
|1 > 0                                                                |
+---------------------------------------------------------------------+

解释说明：相当于Java的if...else if...else if...else...，所以当第一个判断为真的时候，不会进入第二个判断，所以输出且只输出1 > 0

CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END，示例代码：

1	SELECT CASE 1 WHEN 1 THEN '我是1' WHEN 1 THEN '我也是1' WHEN 2 THEN '我是2' ELSE '你是谁' END;

运行结果：

+----------------------------------------------------------------------------+
|CASE 1 WHEN 1 THEN '我是1' WHEN 1 THEN '我也是1' WHEN 2 THEN '我是2' ELSE '你是谁' END|
+----------------------------------------------------------------------------+
|我是1                                                                         |
+----------------------------------------------------------------------------+

解释说明：相当于Java的switch...case...break...default，所以当第一个条件符合的时候，会break掉，所以不会进入后面的条件。

MySQL信息函数

函数	用法
`VERSION()`	返回当前MySQL的版本号
`CONNECTION_ID()`	返回当前MySQL服务器的连接ID
`DATABASE()` `SCHEMA()`	返回MySQL命令行当前所在的数据库
`USER()` `CURRENT_USER()` `SYSTEM_USER()` `SESSION_USER()`	返回当前连接MySQL的用户名，返回结果格式为"主机名@用户名"
`CHARSET(value)`	返回字符串value自变量的字符集
`COLLATION(value)`	返回字符串value的比较规则

MySQL信息函数，示例代码：

SELECT VERSION(),
       CONNECTION_ID(),
       DATABASE(),
       SCHEMA(),
       USER(),
       CURRENT_USER(),
       SYSTEM_USER(),
       SESSION_USER(),
       CHARSET('value'),
       COLLATION('value');

运行结果：

+---------+---------------+----------+---------+------------------+--------------+------------------+------------------+----------------+------------------+
|VERSION()|CONNECTION_ID()|DATABASE()|SCHEMA() |USER()            |CURRENT_USER()|SYSTEM_USER()     |SESSION_USER()    |CHARSET('value')|COLLATION('value')|
+---------+---------------+----------+---------+------------------+--------------+------------------+------------------+----------------+------------------+
|8.0.28   |1162           |kakadb |kakadb|root@124.74.24.138|root@%        |root@124.74.24.138|root@124.74.24.138|utf8mb4         |utf8mb4_0900_ai_ci|
+---------+---------------+----------+---------+------------------+--------------+------------------+------------------+----------------+------------------+

聚合函数

五种聚合函数

聚合函数，入参有多行，对一组数据进行聚合，只返回一个值。
常见的五种聚合函数有：

AVG()：数值型数据的平均值
SUM()：数值型数据的总和
MAX()：任意数据类型的数据的最大值
MIN()：任意数据类型的数据的最小值
COUNT()：个数

注意：聚合函数不能嵌套调用。比如不能出现类似AVG(SUM(字段名称))形式的调用。

COUNT

三种用法

在5种聚合函数中，需要专门讨论的是COUNT()。
COUNT()中，常见的三种用法为：

COUNT(*)
COUNT(1)
COUNT(字段名)

在计数规则方面

COUNT(*)和COUNT(1)，会去统计表中的行数，如果某一行有一个或多个字段为NULL，依旧会被视作一行。
COUNT(字段名)，会统计该字段名的列中，不为NULL的数据个数，即不统计NULL。

也就是说，COUNT(*)和COUNT(1)，统计的是表的行数，COUNT(字段名)统计的是某一列的个数。

在查询效率方面

对于InnoDB引擎

对于COUNT(1)，InnoDB引擎会去找到一个最小的索引树去遍历(不一定是主键索引)，但是不会读取数据，而是读到一个叶子节点，就返回1，最后将结果累加。
对于COUNT(字段名)
1. 如果该字段是主键索引，性能OK。
2. 如果该字段不是主键索引，InnoDB引擎会遍历整张表做全表扫描； 所以，不要这么用！
对于COUNT(*)，InnoDB引擎会自动进行优化。

对于MyISAM引擎

对于MyISAM引擎，且没有其他WHERE条件，或者GROUP BY条件，三种方法的效率是一样的。
即，如下三个SQL的效率是一样的。

1
2
3

SELECT COUNT(*) FROM 表;
SELECT COUNT(1) FROM 表;
SELECT COUNT(字段名) FROM 表;

这是因为MyISAM把表中的行数直接存在磁盘中了，需要的时候直接读取出来就行了，所以非常快。

GROUP BY

基本使用

可以使用GROUP BY子句将表中的数据分成若干组。

用法：

SELECT column, group_function(column) 
FROM table 
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

注意：

在SELECT列表中所有未包含在聚合函数中的列都应该被包含在GROUP BY子句中，即在上述例子中的所有的column，都应该被包含在group_by_expression。
包含在GROUP BY子句中的列不必包含在SELECT列表中。
可以使用多个列分组。

示例代码：

1
2
3

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

1
2
3

SELECT AVG(salary)
FROM employees
GROUP BY department_id;

1
2
3

SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id;

WITH ROLLUP

WITH ROLLUP，可以理解成"汇总"。

例如，所有的和。
示例代码：

SELECT department_id, SUM(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id
WITH ROLLUP;

运行结果：

+-------------+-----------+
|department_id|SUM(salary)|
+-------------+-----------+
|90           |58000      |
|100          |51600      |
|110          |20300      |
|NULL         |129900     |
+-------------+-----------+

又如，所有的平均。
示例代码：

SELECT department_id, AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id
WITH ROLLUP;

运行结果：

+-------------+------------+
|department_id|AVG(salary) |
+-------------+------------+
|90           |19333.333333|
|100          |8600        |
|110          |10150       |
|NULL         |11809.090909|
+-------------+------------+

注意，是对结果的汇总，是上述三行结果的平均，不是对所有的表中的数据求平均。

GROUP_CONCAT

GROUP_CONCAT()中的字段，以逗号进行拼接。

假设存在表如下：

deptno	dname	loc
1	信息技术	上海
2	信息技术	北京
3	市场	纽约
4	市场	新加坡

示例代码：

1
2
3

SELECT dname, group_concat(loc)
FROM dept
GROUP BY dname;

运行结果：

+-----+-----------------+
|dname|group_concat(loc)|
+-----+-----------------+
|信息技术 |上海,北京            |
|市场   |纽约,新加坡           |
+-----+-----------------+

替换DISTINCT

正如我们上文所说，在分布式数据库中，去重的更好办法是用GROUP BY。
对于SELECT DISTINCT department_id FROM employees;，SELECT department_id FROM employees GROUP BY department_id;的性能会更好。

因为在分布式数据库中，DISTINCT会将所有节点的数据进行汇总到主节点，然后进行运算。但是GROUP是，每个节点先分别计算，最后汇总到主节点再进行一次运算。

HAVING

用法

HAVING：过滤分组，满足HAVING子句中条件的分组将被显示。

例如：查询部门平均工资比10000高的部门。
首先，查询部门平均工资，这个很简单。

1
2
3

SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id;

那么，怎么找到平均工资比10000高的部门呢？
加上HAVING AVG(salary)>10000;。

SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id
HAVING   AVG(salary)>10000;

HAVING子句使用的前提

已经被分组(所以HAVING不能单独使用，必须要和GROUP BY一起使用。)
使用了聚合函数。

特别的，HAVING中的聚合函数，不必和SELECT中的聚合函数一致。
示例代码：

SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id
HAVING   MAX(salary)>10000;

运行结果：

+-------------+------------+
|department_id|AVG(salary) |
+-------------+------------+
|20           |9500        |
|30           |4150        |
|80           |8955.882353 |
|90           |19333.333333|
|100          |8600        |
|110          |10150       |
+-------------+------------+

HAVING和WHERE的区别

在查询语法结构上

WHERE在GROUP BY之前，所以无法对分组结果进行筛选，被WHERE排除的记录不再包括在分组中。
HAVING在GROUP BY之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选。

在筛选条件上

WHERE可以使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件。
HAVING可以使用分组计算的函数作为筛选条件，也可以表中的字段作为筛选条件。

HAVING使用表中的字段作为筛选条件，示例代码：

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000
   AND department_id = '100';

运行结果：

+-------------+-----------+
|department_id|AVG(salary)|
+-------------+-----------+
|100          |8600       |
+-------------+-----------+

在关联查询效率上

WHERE是先筛选后连接。
HAVING是先连接后筛选。

所以，在关联查询中，WHERE比HAVING更高效。

那么，到底是选择WHERE还是HAVING呢？
一般，包含分组统计函数的条件用HAVING，普通条件用WHERE。

子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。

例子

例如，我们要查询薪资比Abel高的员工。

我们有多种方式：

用两个SQL，先查到Abel的工资具体是多少，然后找到工资比那个数字大的员工。

1
2
3

SELECT salary
FROM employees
WHERE last_name = 'Abel';

1
2
3

SELECT last_name, salary
FROM employees
WHERE salary > 11000;

自连接，构造Abel的薪资表，两张表进行关联，设置条件"工资大于Abel工资"。

SELECT e2.last_name, e2.salary
FROM employees e1,
     employees e2
WHERE e1.last_name = 'Abel'
AND e2.`salary` > e1.`salary`

子查询，先查到Abel的工资具体是多少，然后找到工资比那个数字大的员工

SELECT last_name, salary
FROM employees
WHERE salary > (
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'
);

基本使用

子查询的基本语法结构：

注意：

子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询(子查询的结果返回一条)
多行操作符对应多行子查询(子查询的结果返回多条)

接下来，我们分别讨论单行子查询和多行子查询。

单行子查询

基础操作

例如，查询job_id与141号员工相同，salary比143号员工多的员工姓名、job_id和工资。

SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141)
  AND salary > (SELECT salary FROM employees WHERE employee_id = 143);

查询公司工资最少的员工的last_name、job_id和salary。

1
2
3

SELECT last_name, job_id, salary
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);

成对比较法

例如，查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id、manager_id和department_id。

分别比较法(不成对比较)：
即，manager_id和department_id，分别进行比较，用AND进行关联。

SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN (SELECT manager_id FROM employees WHERE employee_id IN (174, 141))
  AND department_id IN (SELECT department_id FROM employees WHERE employee_id IN (174, 141))
  AND employee_id NOT IN (174, 141);

成对比较法：
即，manager_id和department_id，同时进行比较。

SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN (SELECT manager_id, department_id FROM employees WHERE employee_id IN (141, 174))
  AND employee_id NOT IN (141, 174);

HAVING中的子查询

HAVING中的子查询，首先执行子查询，然后向主查询中的HAVING子句返回结果。

例如，查询最低工资大于50号部门最低工资的部门id和其最低工资。

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
       (SELECT MIN(salary)
        FROM employees
        WHERE department_id = 50);

CASE中的子查询

在CASE中使用单列子查询：

例如，查询员工的employee_id,last_name和location。若该department_id对应的location_id为1800，则location为’Canada’，否则为’USA’。

SELECT employee_id,
       last_name,
       (CASE department_id
            WHEN
                (SELECT department_id
                 FROM departments
                 WHERE location_id = 1800)
                THEN 'Canada'
            ELSE 'USA' END) location
FROM employees;