运算符

运算符是保留字或主要用于 SQL 语句的 WHERE 子句 中的字符，用于执行操作，例如：比较和算术运算。 这些运算符用于指定 SQL 语句中的条件，并用作语句中多个条件的连词。
常见运算符有以下几种：

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

算术运算符

算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加 （+）、减（-）、乘（*）、除（/）和取模（%）运算。

加减运算符：

• 一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；
• 一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；
• 加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的；
• 在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数 值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（补充：MySQL 中字符串拼接要使用字符串函数CONCAT()实现）
  乘除运算符：
• 一个数乘以整数1和除以整数1后仍得原数；
• 一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；
• 一个数除以整数后，不管是否能除尽，结果都为一个浮点数；
• 一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；
• 乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。
• 在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。

比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果 为假则返回0，其他情况则返回NULL。
比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。

<=> 安全运算符：与等于运算符（=）的作用是相似的， 唯一的区别 是‘<=>’可 以用来对NULL进行判断。在两个操作数均为NULL时，其返回值为1，而不为NULL；当一个操作数为NULL 时，其返回值为0，而不为NULL

逻辑运算符

这是在 SQL 所有的逻辑运算符的列表。

位运算符

位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算， 最后将计算结果从二进制变回十进制数。
MySQL支持的位运算符如下：

1. 按位与运算符 按位与（&）运算符将给定值对应的二进制数逐位进行逻辑与运算。当给定值对应的二 进制位的数值都为1时，则该位返回1，否则返回0。
   

1的二进制数为0001，10的二进制数为1010，所以1 & 10的结果为0000，对应的十进制数为0。20的二进制 数为10100，30的二进制数为11110，所以20 & 30的结果为10100，对应的十进制数为20。

2. 按位或运算符 按位或（|）运算符将给定的值对应的二进制数逐位进行逻辑或运算。当给定值对应的 二进制位的数值有一个或两个为1时，则该位返回1，否则返回0。
   

1的二进制数为0001，10的二进制数为1010，所以1 | 10的结果为1011，对应的十进制数为11。20的二进 制数为10100，30的二进制数为11110，所以20 | 30的结果为11110，对应的十进制数为30。

3. 按位异或运算符 按位异或（^）运算符将给定的值对应的二进制数逐位进行逻辑异或运算。当给定值
   

1的二进制数为0001，10的二进制数为1010，所以1 ^ 10的结果为1011，对应的十进制数为11。20的二进 制数为10100，30的二进制数为11110，所以20 ^ 30的结果为01010，对应的十进制数为10。

4. 按位取反运算符 按位取反（~）运算符将给定的值的二进制数逐位进行取反操作，即将1变为0，将0变 为1。
   

由于按位取反（~）运算符的优先级高于按位与（&）运算符的优先级，所以10 & ~1，首先，对数字1进 行按位取反操作，结果除了最低位为0，其他位都为1，然后与10进行按位

5. 按位右移运算符 按位右移（>>）运算符将给定的值的二进制数的所有位右移指定的位数。右移指定的 位数后，右边低位的数值被移出并丢弃，左边高位空出的位置用0补齐。
   

1的二进制数为0000 0001，右移2位为0000 0000，对应的十进制数为0。4的二进制数为0000 0100，右移2 位为0000 0001，对应的十进制数为1。

6. 按位左移运算符 按位左移（<<）运算符将给定的值的二进制数的所有位左移指定的位数。左移指定的 位数后，左边高位的数值被移出并丢弃，右边低位空出的位置用0补齐。
   

1的二进制数为0000 0001，左移两位为0000 0100，对应的十进制数为4。4的二进制数为0000 0100，左移 两位为0001 0000，对应的十进制数为16。

运算符的优先级

数字编号越大，优先级越高，优先级高的运算符先进行计算。

拓展：使用正则表达式查询

正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合 要求的特殊字符串。例如，从一个文本文件中提取电话号码，查找一篇文章中重复的单词或者替换用户 输入的某些敏感词语等，这些地方都可以使用正则表达式。正则表达式强大而且灵活，可以应用于非常 复杂的查询。

MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式。下表列出了REGEXP操作符中常用字符匹配 列表。

# 1.查询以特定字符或字符串开头的记录 字符‘^’匹配以特定字符或者字符串开头的文本。
# 在fruits表中，查询f_name字段以字母‘b’开头的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name REGEXP '^b';

# 2.查询以特定字符或字符串结尾的记录 字符‘$’匹配以特定字符或者字符串结尾的文本。
# 在fruits表中，查询f_name字段以字母‘y’结尾的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name REGEXP 'y$';

# 3.用符号"."来替代字符串中的任意一个字符 字符‘.’匹配任意一个字符。
# 在fruits表中，查询f_name字段值包含字母‘a’与‘g’且两个字母之间只有一个字母的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name REGEXP 'a.g';

# 4.使用""和"+"来匹配多个字符 星号‘’匹配前面的字符任意多次，包括0次。加号‘+’匹配前面的字符至少一次。
# 在fruits表中，查询f_name字段值以字母‘b’开头且‘b’后面出现字母‘a’的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name REGEXP '^ba*'

# 5.匹配指定字符串 正则表达式可以匹配指定字符串，只要这个字符串在查询文本中即可，如要匹配多个字符串，多个字符串之间使用分隔符‘|’隔开。
# 在fruits表中，查询f_name字段值包含字符串“on”的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name REGEXP 'on';
# 在fruits表中，查询f_name字段值包含字符串“on”或者“ap”的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';

# 之前介绍过，LIKE运算符也可以匹配指定的字符串，但与REGEXP不同，LIKE匹配的字符串如果在文本中间出现，则找不到它，相应的行也不会返回。
# REGEXP在文本内进行匹配，如果被匹配的字符串在文本中出现，REGEXP将会找到它，相应的行也会被返回。对比结果如下所示。
# 在fruits表中，使用LIKE运算符查询f_name字段值为“on”的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name like 'on'; Empty set(0.00 sec)

# 6.匹配指定字符中的任意一个 方括号“[]”指定一个字符集合，只匹配其中任何一个字符，即为所查找的
文本。
# 在fruits表中，查找f_name字段中包含字母‘o’或者‘t’的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name REGEXP '[ot]';
# 在fruits表中，查询s_id字段中包含4、5或者6的记录，SQL语句如下：
SELECT * FROM fruits WHERE s_id REGEXP '[456]';

# 7.匹配指定字符以外的字符 “[^字符集合]” 匹配不在指定集合中的任何字符。
# 在fruits表中，查询f_id字段中包含字母a~e和数字1~2以外字符的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';

# 8.使用{n,}或者{n,m}来指定字符串连续出现的次数 “字符串{n,}”表示至少匹配n次前面的字符；“字符串{n,m}”表示匹配前面的字符串不少于n次，不多于m次。例如，a{2,}表示字母a连续出现至少2次，也可以大于2次；a{2,4}表示字母a连续出现最少2次，最多不能超过4次。
# 在fruits表中，查询f_name字段值出现字母‘x’至少2次的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';
# 在fruits表中，查询f_name字段值出现字符串“ba”最少1次、最多3次的记录，SQL语句如下：
SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}' ;

排序与分页

排序数据

排序规则

• 使用 ORDER BY 子句排序
  • ASC（ascend）: 升序
  • DESC（descend）:降序
• ORDER BY 子句在SELECT语句的结尾。

单列排序

# 根据 hire_date 升序排序排列：
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;
# 根据 hire_date 排序降序排列
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC;

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

多列排序

• 可以使用不在SELECT列表中的列排序。
• 在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第 一列数据中所有值都是唯一的，将不再对第二列进行排序。

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

分页

为什么要分页？
原因：查询返回的记录太多了，查看起来很不方便，想自定义返回行数

实现规则

• 分页原理：所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。
• MySQL中使用 LIMIT 实现分页
• 格式：LIMIT [位置偏移量] 行数

第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移 量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是 1，以此类推）；第二个参数“行数”指示返回的记录条数。

#例子
--前10条记录：
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;

--第11至20条记录：
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录：
SELECT * FROM 表名 LIMIT 20,10;

MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;”返回的结果相同。

• 分页显式公式：（当前页数-1）*每页条数，每页条数

SELECT * FROM table LIMIT(PageNo - 1)*PageSize,PageSize;

• 注意：LIMIT 子句必须放在整个SELECT语句的最后！
• 使用 LIMIT 的好处

约束返回结果的数量可以 减少数据表的网络传输量 ，也可以 提升查询效率 。如果我们知道返回结果只有 1 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需 要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

拓展
在不同的 DBMS 中使用的关键字可能不同。在 MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 关 键字，而且需要放到 SELECT 语句的最后面。

• 如果是 SQL Server 和 Access，需要使用 TOP 关键字，比如：

SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC

• 如果是 DB2，使用 FETCH FIRST 5 ROWS ONLY 这样的关键字：

SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY

• 如果是 Oracle，你需要基于 ROWNUM 来统计行数：

SELECT rownum,last_name,salary FROM employees WHERE rownum < 5 ORDER BY salary DESC;

需要说明的是，这条语句是先取出来前 5 条数据行，然后再按照 hp_max 从高到低的顺序进行排序。但 这样产生的结果和上述方法的并不一样。我会在后面讲到子查询，你可以使用

SELECT rownum, last_name,salary
FROM (
  SELECT last_name,salary
  FROM employees
  ORDER BY salary DESC)
WHERE rownum < 10;

得到与上述方法一致的结果。

函数

MySQL的内置函数及分类

MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、字符串函数、日期和时间函数、流程控制 函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，我将这些丰富的内置函数再分为两类： 单行函数、聚合函数（或分组函数） 。

单行函数

• 操作数据对象
• 接受参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以嵌套
• 参数可以是一列或一个值

数值函数

基本函数

角度与弧度互换函数

三角函数

指数与对数

进制间的转换

字符串函数

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

日期和时间函数

获取日期、时间

日期与时间戳的转换

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

日期的操作函数

EXTRACT(type FROM date)函数中type的取值与含义：

时间和秒钟转换的函数

计算日期和时间的函数

日期的格式化与解析

上述 非GET_FORMAT 函数中fmt参数常用的格式符：

GET_FORMAT函数中date_type和format_type参数取值如下：

流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。 MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

加密与解密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在 保证数据库安全时非常有用。

可以看到，ENCODE(value,password_seed)函数与DECODE(value,password_seed)函数互为反函数。

MySQL信息函数

MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地 对数据库进行维护工作。

其他函数

MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视 的。

聚合函数

聚合函数作用于一组数据，并对一组数据返回一个值。

聚合函数类型

• AVG()
• SUM()
• MAX()
• MIN()
• COUNT()
  聚合函数语法
  

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

常用五大聚合函数

AVG和SUM函数

AVG：求平均值
SUM：求和
可以对数值型数据使用AVG 和 SUM 函数。

SELECT AVG(salary),SUM(salary)
FROM employees

COUNT函数

• COUNT(*)返回表中记录总数，适用于任意数据类型。

SELECT COUNT(*)
FROM employees
WHERE department_id = 50;

COUNT(expr) 返回expr不为空的记录总数。

SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;

• 问题：用count()，count(1)，count(列名)谁好呢?
  其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。 Innodb引擎的表用count(),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好 于具体的count(列名)。

• 问题：能不能使用count(列名)替换count()?
  不要使用 count(列名)来替代 count() ， count() 是 SQL92 定义的标准统计行数的语法，跟数 据库无关，跟 NULL 和非 NULL 无关。 说明：count()会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。