MySQL基础篇——第04章运算符

MySQL基础篇——第04章运算符 1. 算数运算符

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-b9d3Bujj-1652660140801)(MySQL基础篇——第04章运算符.assets/image-20220419201824842.png)]

1.1 加法与减法

加：SELECt A + B；减：SELECT A - B
一个整数类型的值对整数进行加法和减法操作，结果还是一个整数
一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数
加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的
在Java中，+ 的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中 + 只表示数值相加。如果遇到非数值类型，先尝试转成数值（隐式转换），如果转失败，就按0计算。（MySQL中字符串拼接要使用字符串函数 CONCAt() 实现）
null 值参与运算，结果仍为null

mysql> SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5
    -> FROM DUAL;
+-----+---------+---------+----------+--------------+------------+------------+
| 100 | 100 + 0 | 100 - 0 | 100 + 50 | 100 + 50 -30 | 100 + 35.5 | 100 - 35.5 |
+-----+---------+---------+----------+--------------+------------+------------+
| 100 |     100 |     100 |      150 |          120 |      135.5 |       64.5 |
+-----+---------+---------+----------+--------------+------------+------------+
1 row in set (0.00 sec)

# 将 '1' 转换成了数值1，而 'a' 在MySQL中无法转换成数值，故按0计算
mysql> SELECt 100 + '1', 100 + 'a'
    -> FROM DUAL;
+-----------+-----------+
| 100 + '1' | 100 + 'a' |
+-----------+-----------+
|       101 |       100 |
+-----------+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECt 100 + null
    -> FROM DUAL;
+------------+
| 100 + null |
+------------+
|       NULL |
+------------+
1 row in set (0.00 sec)

1.2 乘法与除法

乘：SELECt A * B；除：SELECT A / B 或 SELECT A DIV B
一个数乘以整数1和除以整数1后仍得原数
一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等
一个数除以整数后，不管是否能除尽，结果都为一个浮点数
一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位
乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同
在数学运算中，0不能用作除数，在MySQL中，一个数除以0为 NULL

mysql> SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2,100 + 2 * 5 / 2,100 /3, 100
    -> DIV 0 FROM dual;
+-----+---------+-----------+-----------+---------+-----------------+---------+-----------+
| 100 | 100 * 1 | 100 * 1.0 | 100 / 1.0 | 100 / 2 | 100 + 2 * 5 / 2 | 100 /3  | 100
DIV 0 |
+-----+---------+-----------+-----------+---------+-----------------+---------+-----------+
| 100 |     100 |     100.0 |  100.0000 | 50.0000 |        105.0000 | 33.3333 |      NULL |
+-----+---------+-----------+-----------+---------+-----------------+---------+-----------+
1 row in set (0.00 sec)

1.3 求模(求余)

求模(求余)：SELECt A % B 或 SELECT A MOD B
取模运算的结果与被模数的符号相同
经常使用 % (MOD)来判断是否能被除尽的情况

mysql> SELECT 12 % 5, 12 % -5, -12 MOD 5, -12 MOD -5
    -> FROM DUAL;
+--------+---------+-----------+------------+
| 12 % 5 | 12 % -5 | -12 MOD 5 | -12 MOD -5 |
+--------+---------+-----------+------------+
|      2 |       2 |        -2 |         -2 |
+--------+---------+-----------+------------+
1 row in set (0.00 sec)

2. 比较运算符

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

2.1 符号类型的比较运算符

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-besNi54j-1652660140802)(MySQL基础篇——第04章运算符.assets/image-20220419150113689.png)]

2.1.1 等于运算符 =

= 判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0
在使用等号运算符时，遵循如下规则：
如果等号两边都为字符串时，则比较的是两个字符串中字符的ANSI编码是否相等
如果等号两边都是整数，则按照整数来比较两个值的大小
如果等号两边一个是整数，另一个是字符串，则会将字符串转化为数值(隐式转换)进行比较，若转换失败，则看做是0
等于运算符不能判断 NULL 值。如果两边的值、字符串或表达式中有一个为 NULL，则比较结果为 NULL

对比：SQL中赋值符号使用 :=

mysql> SELECT 1 = 1, 1 = 'a', 1 = 0, 'a' = 'b', (5 + 3) = (2 + 6), '' = NULL, NULL = NULL
    -> FROM DUAL;
+-------+---------+-------+-----------+-------------------+-----------+-------------+
| 1 = 1 | 1 = '1' | 1 = 0 | 'a' = 'b' | (5 + 3) = (2 + 6) | '' = NULL | NULL = NULL |
+-------+---------+-------+-----------+-------------------+-----------+-------------+
|     1 |       1 |     0 |         0 |                 1 |      NULL |        NULL |
+-------+---------+-------+-----------+-------------------+-----------+-------------+
1 row in set (0.00 sec)

2.1.2 安全等于运算符 <=>

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

mysql> SELECt 1 <=> 2, 1 <=> '1', 'a' <=> 'a', (5 + 3) <=> (2 + 6), '' <=> NULL, NULL <=> NULL
    -> FROM dual;
+---------+-----------+-------------+---------------------+-------------+---------------+
| 1 <=> 2 | 1 <=> '1' | 'a' <=> 'a' | (5 + 3) <=> (2 + 6) | '' <=> NULL | NULL <=> NULL |
+---------+-----------+-------------+---------------------+-------------+---------------+
|       0 |         1 |           1 |                   1 |           0 |             1 |
+---------+-----------+-------------+---------------------+-------------+---------------+
1 row in set (0.00 sec)

2.1.3不等于运算符 <> 或 !=

不等于运算符（<> 或 !=）用于判断两边的数字、字符串或者表达式的值是否不相等，如果不相等则返回1，相等则返回0
不等于运算符不能判断 NULL 值。如果两边的值、字符串或表达式中有一个为 NULL，则比较结果为 NULL

2.2 非符号类型的比较运算符

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-H0puIv2n-1652660140803)(MySQL基础篇——第04章运算符.assets/image-20220419150149637.png)]

2.2.1 IS NULL / ISNULL() / IS NOT NULL

空运算符（IS NULL 或者 ISNULL()）判断一个值是否为NULL，如果为NULL则返回1，否则返回0
非空运算符（IS NOT NULL）判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0

mysql> SELECt NULL IS NULL, ISNULL(NULL), ISNULL('a'), 1 IS NULL, NULL IS NOT NULL, 'a' IS NOT NULL
    -> FROM dual;
+--------------+--------------+-------------+-----------+------------------+-----------------+
| NULL IS NULL | ISNULL(NULL) | ISNULL('a') | 1 IS NULL | NULL IS NOT NULL | 'a' IS NOT NULL |
+--------------+--------------+-------------+-----------+------------------+-----------------+
|            1 |            1 |           0 |         0 |                0 |               1 |
+--------------+--------------+-------------+-----------+------------------+-----------------+
1 row in set (0.00 sec)

# 查询commission_pct等于NULL。比较如下的四种写法:
SELECt employee_id,commission_pct FROM employees WHERe commission_pct IS NULL; # 正确
SELECt employee_id,commission_pct FROM employees WHERe commission_pct <=> NULL; # 正确
SELECt employee_id,commission_pct FROM employees WHERe ISNULL(commission_pct); # 正确
SELECt employee_id,commission_pct FROM employees WHERe commission_pct = NULL; # 错误

# 查询commission_pct不等于NULL
SELECt employee_id,commission_pct FROM employees WHERe commission_pct IS NOT NULL; # 正确
SELECt employee_id,commission_pct FROM employees WHERe NOT commission_pct <=> NULL; # 正确
SELECt employee_id,commission_pct FROM employees WHERe NOT ISNULL(commission_pct); # 正确

2.2.2 最小值LEAST() 和最大值GREATEST()

最小值运算符语法格式为：LEAST(值1, 值2, ..., 值n)
当参数是整数或者浮点数时，LEAST() 将返回其中最小的值；当参数为字符串时，返回字母表中顺序最靠前的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL

最大值运算符语法格式为：GREATEST(值1, 值2, ..., 值n)
当参数中是整数或者浮点数时，GREATEST() 将返回其中最大的值；当参数为字符串时，返回字母表中顺序最靠后的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL

mysql> SELECt LEAST(1, 0, 2), LEAST('b', 'a', 'c'), LEAST(1, NULL, 2)
    -> FROM DUAL;
+--------------+--------------------+-----------------+
| LEAST(1,0,2) | LEAST('b','a','c') | LEAST(1,NULL,2) |
+--------------+--------------------+-----------------+
|            0 | a                  |            NULL |
+--------------+--------------------+-----------------+
1 row in set (0.00 sec)

mysql> SELECt GREATEST(1, 0, 2), GREATEST('b', 'a', 'c'), GREATEST(1, NULL, 2)
    -> FROM DUAL;
+-----------------+-----------------------+--------------------+
| GREATEST(1,0,2) | GREATEST('b','a','c') | GREATEST(1,NULL,2) |
+-----------------+-----------------------+--------------------+
|               2 | c                     |               NULL |
+-----------------+-----------------------+--------------------+
1 row in set (0.00 sec)

2.2.3 两值之间BETWEEN ... AND ...

使用格式：

SELECt D, E
FROM table
WHERe C BETWEEN A AND B

当C大于或等于A，并小于或等于B时（左闭右闭），结果为1，否则结果为0

# 查询工资在6000到8000的员工信息
SELECt employee_id, last_name, salary
FROM employees
WHERe salary BETWEEN 6000 AND 8000;

# 查询工资不在6000到8000的员工信息
SELECt employee_id, last_name, salary
FROM employees
WHERe salary NOT BETWEEN 6000 AND 8000;
# WHERe salary < 6000 OR salary > 8000;

2.2.4 属于IN (set) 和不属于NOT IN (set)

IN () 用于判断给定的值是否是 IN 列表中的一个值，如果是则返回1，否则返回0。如果给定的值为NULL，或者 IN 列表中存在NULL，则结果为NULL
NOT IN 运算符用于判断给定的值是否不是 IN 列表中的一个值，如果不是 IN 列表中的一个值，则返回1，否则返回0

# 查询部门为10, 20, 30的员工信息
# 方法一：
SELECt last_name, salary, department_id
FROM employees
WHERe department_id = 10 OR department_id = 20 OR department_id = 30;

# 方法二：
SELECt last_name, salary, department_id
FROM employees
WHERe department_id IN (10, 20, 30);

# 查询工资不是6000, 7000, 8000的员工信息
SELECt last_name, salary, department_id
FROM employees
WHERe salary NOT IN (6000, 7000, 8000);

2.2.5 模糊匹配 LIKE

LIKE 运算符主要用来匹配字符串，即判断一个字符串是否满足模糊匹配的规则。语法格式为：expr LIKE 模糊匹配条件
如果expr满足模糊匹配条件则返回1，否则返回0。如果给定的值或者匹配条件为NULL，则返回结果为NULL
LIKE 运算符通常使用如下通配符：
%：代表不确定个数(0个，1个或多个)的字符
_：代表1个字符

# 查询last_name中包含字符'a'的员工信息
SELECt last_name
FROM employees
WHERe last_name LIKE '%a%';

# 查询last_name以字符'a'开头的员工信息
SELECt last_name
FROM employees
WHERe last_name LIKE 'a%';

# 查询last_name以字符'e'结尾的员工信息
SELECt last_name
FROM employees
WHERe last_name LIKE '%e';

# 查询last_name中包含字符'a'且包含字符'e'的员工信息
# 写法一：
SELECt last_name
FROM employees
WHERe last_name LIKE '%a%' AND last_name LIKE '%e%';
# 写法二：
SELECt last_name
FROM employees
WHERe last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

# 查询last_name的第2个字符是'a'的员工信息
SELECt last_name
FROM employees
WHERe last_name LIKE '_a%';

# 查询last_name的第3个字符是'a'的员工信息
SELECt last_name
FROM employees
WHERe last_name LIKE '__a%';

# 查询last_name的第2个字符是'_'且第3个字符是'a'的员工信息
SELECt last_name
FROM employees
WHERe last_name LIKE '__a%'; # 使用转义字符''

2.2.6 ESCAPE

回避特殊符号：使用转义符
通常使用表示转义，此时省略 ESCAPE ；若想使用别的符号充当转义符，则需要显示加上 ESCAPE

# 查询job_id以'IT_'开头的员工信息

# 使用转义符''，省略ESCAPE
SELECt job_id
FROM jobs
WHERe job_id LIKE 'IT_%';

# 使用'$'充当转义符，则需要显示加上ESCAPE
SELECt job_id
FROM jobs
WHERe job_id LIKE 'IT$_%' ESCAPE '$';

2.2.7 正则表达式 REGEXP / RLIKE

REGEXP 运算符用来匹配字符串，即判断一个字符串是否满足正则表达式的规则。语法格式为：expr REGEXP 正则表达式
如果expr满足正则表达式，返回1；如果不满足，则返回0。若expr或正则表达式任意一个为NULL，则结果为NULL
REGEXP 运算符在进行匹配时，常用的有下面几种通配符：
^ 匹配以该字符后面的字符开头的字符串
$ 匹配以该字符前面的字符结尾的字符串
. 匹配任何一个单字符
[...] 匹配在方括号内的任何字符。例如，[abc] 匹配 a 或 b 或 c。为了命名字符的范围，使用一个 -，如 [a-z] 匹配任何字母，而 [0-9] 匹配任何数字
* 匹配0个或多个在它前面的字符。例如，x* 匹配任何数量的 x 字符，[0-9]* 匹配任何数量的数字，而 * 匹配任何数量的任何字符

# '^s'判断该字符串是否以's'开头；'t$'判断是否以't'结尾；'hk'判断是否包含'hk'
mysql> SELECt 'shkstart' REGEXP '^s', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk'
    -> FROM DUAL;
+------------------------+------------------------+------------------------+
| 'shkstart' REGEXP '^s' | 'shkstart' REGEXP 't$' | 'shkstart' REGEXP 'hk' |
+------------------------+------------------------+------------------------+
|                      1 |                      1 |                      1 |
+------------------------+------------------------+------------------------+
1 row in set (0.01 sec)

# 'gu.gu'中'.'可以表示任何一个单字符，判断该字符串是否包含'gu.gu'这样的结构
# '[ab]'判断该字符串是否包含'a'或'b'
mysql> SELECt 'atguigu' REGEXP 'gu.gu', 'atguigu' REGEXP '[ab]'
    -> FROM DUAL;
+--------------------------+-------------------------+
| 'atguigu' REGEXP 'gu.gu' | 'atguigu' REGEXP '[ab]' |
+--------------------------+-------------------------+
|                        1 |                       1 |
+--------------------------+-------------------------+
1 row in set (0.00 sec)

3. 逻辑运算符

逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-m0advZLa-1652660140803)(MySQL基础篇——第04章运算符.assets/image-20220419201939559.png)]

**逻辑非(NOT 或 !)：**当给定的值为0时返回1；当给定的值为非0值时返回0；当给定的值为NULL时，返回NULL

# 逻辑非：NOT 或 !
mysql> SELECt NOT 1, NOT 0, NOT(1+1), NOT !1, NOT NULL
    -> FROM DUAL;
+-------+-------+----------+--------+----------+
| NOT 1 | NOT 0 | NOT(1+1) | NOT !1 | NOT NULL |
+-------+-------+----------+--------+----------+
|     0 |     1 |        0 |      1 |     NULL |
+-------+-------+----------+--------+----------+
1 row in set, 1 warning (0.00 sec)

**逻辑与(AND 或 &&)：**当给定的所有值均为非0值，且都不为NULL时，返回1；当给定的一个值或者多个值都为0则返回0；当给定的值中至少有一个NULL，且其他均为非0值，则返回NULL

# 逻辑与：AND 或 &&
mysql> SELECt 1 AND -1, 0 AND 1, 0 AND NULL, 1 AND NULL
    -> FROM DUAL;
+----------+---------+------------+------------+
| 1 AND -1 | 0 AND 1 | 0 AND NULL | 1 AND NULL |
+----------+---------+------------+------------+
|        1 |       0 |          0 |       NULL |
+----------+---------+------------+------------+
1 row in set (0.00 sec)

**逻辑或(OR 或 ||)：**当给定的值都不为NULL，且至少有一个值为非0值时，返回1；当给定的值全为0，则返回0；当一个值为NULL，且另一个值为非0值时，返回1；当一个值为NULL，且另一个值为0，返回NULL；当两个值都为NULL时，返回NULL

# 逻辑或：OR 或 ||
mysql> SELECt 1 OR -1, 1 OR 0, 1 OR NULL, 0 || NULL, NULL || NULL
    -> FROM DUAL;
+---------+--------+-----------+-----------+--------------+
| 1 OR -1 | 1 OR 0 | 1 OR NULL | 0 || NULL | NULL || NULL |
+---------+--------+-----------+-----------+--------------+
|       1 |      1 |         1 |      NULL |         NULL |
+---------+--------+-----------+-----------+--------------+
1 row in set, 2 warnings (0.00 sec)

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

**逻辑异或(XOR)：**当给定的值中任意一个值为NULL时，则返回NULL；如果两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1

# 逻辑异或：XOR
mysql> SELECt 1 XOR -1, 1 XOR 0, 0 XOR 0, 1 XOR NULL, 1 XOR 1 XOR 1, 0 XOR 0 XOR 0
    -> FROM DUAL;
+----------+---------+---------+------------+---------------+---------------+
| 1 XOR -1 | 1 XOR 0 | 0 XOR 0 | 1 XOR NULL | 1 XOR 1 XOR 1 | 0 XOR 0 XOR 0 |
+----------+---------+---------+------------+---------------+---------------+
|        0 |       1 |       0 |       NULL |             1 |             0 |
+----------+---------+---------+------------+---------------+---------------+
1 row in set (0.00 sec)

4. 位运算符

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wXxZfCZg-1652660140804)(MySQL基础篇——第04章运算符.assets/image-20220419220819467.png)]

 # 按位与(&) / 按位或(|) / 按位异或(^) / 按位取反(~)
 mysql> SELECt 12 & 5, 12 | 5,12 ^ 5, 10 & ~1
     -> FROM DUAL;
 +--------+--------+--------+---------+
 | 12 & 5 | 12 | 5 | 12 ^ 5 | 10 & ~1 |
 +--------+--------+--------+---------+
 |      4 |     13 |      9 |      10 |
 +--------+--------+--------+---------+
 1 row in set (0.00 sec)

# 按位右移(>>) / 按位左移(<<)
mysql> SELECt 4 << 1, 8 >> 1
    -> FROM DUAL;
+--------+--------+
| 4 << 1 | 8 >> 1 |
+--------+--------+
|      8 |      4 |
+--------+--------+
1 row in set (0.00 sec)

在一定范围内满足：二进制数每向左移动一位，相当于十进制数乘以2；二进制数每向右移动一位，相当于十进制数除以2

5 | 12 ^ 5 | 10 & ~1 |
±-------±-------±-------±--------+
| 4 | 13 | 9 | 10 |
±-------±-------±-------±--------+
1 row in set (0.00 sec)

```shell
# 按位右移(>>) / 按位左移(<<)
mysql> SELECt 4 << 1, 8 >> 1
    -> FROM DUAL;
+--------+--------+
| 4 << 1 | 8 >> 1 |
+--------+--------+
|      8 |      4 |
+--------+--------+
1 row in set (0.00 sec)

在一定范围内满足：二进制数每向左移动一位，相当于十进制数乘以2；二进制数每向右移动一位，相当于十进制数除以2

MySQL基础篇——第04章 运算符

Java相关栏目本月热门文章

MySQL基础篇——第04章运算符