文章目录
一、算数运算符
算术运算符主要用于数学运算,其可以连接运算符前后的两个数值或表达式,对数值或表达式进行加(+)、减(-)、乘(*)、除(/)和取模(%)运算。
1.1 加法与减法运算符
mysql> SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5 FROM dual;
由运算结果可以得出如下结论:
- 一个整数类型的值对整数进行加法和减法操作,结果还是一个整数;
- 一个整数类型的值对浮点数进行加法和减法操作,结果是一个浮点数;
- 加法和减法的优先级相同,进行先加后减操作与进行先减后加操作的结果是一样的;
- 在Java中,+的左右两边如果有字符串,那么表示字符串的拼接。但是在MySQL中+只表示数值相加。如果遇到非数值类型,先尝试转成数值,如果转失败,就按0计算。(补充:MySQL中字符串拼接要使用字符串函数CONCAT()实现)
1.2 乘法与除法运算符
SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2,100 + 2 * 5 / 2,100 /3, 100 DIV 0 FROM dual;
结果如下:
#计算员工的年基本工资
SELECT employee_id,salary,salary * 12 annual_sal FROM employees;
由运算结果可以得出如下结论:
- 一个数乘以整数1和除以整数1后仍得原数;
- 一个数乘以浮点数1和除以浮点数1后变成浮点数,数值与原数相等;
- 一个数除以整数后,不管是否能除尽,结果都为一个浮点数;
- 一个数除以另一个数,除不尽时,结果为一个浮点数,并保留到小数点后4位;
- 乘法和除法的优先级相同,进行先乘后除操作与先除后乘操作,得出的结果相同。
- 在数学运算中,0不能用作除数,在MySQL中,一个数除以0为NULL。
1.3 求模(求余)运算符
SELECT 12 % 3, 12 MOD 5 FROM dual;
结果如下:
示例:
#筛选出employee_id是偶数的员工
SELECT * FROM employees WHERE employee_id MOD 2 = 0;
二、比较运算符
比较运算符用来对表达式左边的操作数和右边的操作数进行比较,比较的结果为真则返回1,比较的结果为假则返回0,其他情况则返回NULL。
比较运算符经常被用来作为SELECT查询语句的条件来使用,返回符合条件的结果记录。
2.1 等号运算符
- 等号运算符
(=)
断等号两边的值、字符串或表达式是否相等,如果相等则返回1
,不相等则返回0
- 在使用等号运算符时,遵循如下规则:
- 如果等号两边的值、字符串或表达式都为字符串,则
MySQL
会按照字符串进行比较,其比较的是每个字符串中字符的ANSI
编码是否相等。 - 如果等号两边的值都是整数,则
MySQL
会按照整数来比较两个值的大小 - 如果等号两边的值一个是整数,另一个是字符串,则
MySQL
会将字符串转化为数字进行比较 - 如果等号两边的值、字符串或表达式中有一个为
NULL
,则比较结果为NULL
- 如果等号两边的值、字符串或表达式都为字符串,则
- 对比:
SQL
中赋值符号使用:=
- 在
SQL
中,+
没有连接的作用,就表示加法运算,会将字符串隐式转换为数值 ,‘a’
为作为0
处理
示例一:
mysql> SELECT 1 = 1, 1 = '1', 1 = 0, 'a' = 'a', (5 + 3) = (2 + 6), '' = NULL , NULL = NULL;
+-------+---------+-------+-----------+-------------------+-----------+-------------+
| 1 = 1 | 1 = '1' | 1 = 0 | 'a' = 'a' | (5 + 3) = (2 + 6) | '' = NULL | NULL = NULL |
+-------+---------+-------+-----------+-------------------+-----------+-------------+
| 1 | 1 | 0 | 1 | 1 | NULL | NULL |
+-------+---------+-------+-----------+-------------------+-----------+-------------+
1 row in set (0.02 sec)
示例二:
mysql> SELECT 1 = 2, 0 = 'abc', 1 = 'abc' FROM dual;
+-------+-----------+-----------+
| 1 = 2 | 0 = 'abc' | 1 = 'abc' |
+-------+-----------+-----------+
| 0 | 1 | 0 |
+-------+-----------+-----------+
1 row in set (0.00 sec)
示例三:
mysql> SELECT 100 + '1', 100 + 'a', 100 + NULL FROM DUAL;
+-----------+-----------+------------+
| 100 + '1' | 100 + 'a' | 100 + NULL |
+-----------+-----------+------------+
| 101 | 100 | NULL |
+-----------+-----------+------------+
1 row in set (0.00 sec)
2.2 安全等于运算符
- 安全等于运算符
(<=>)
与等于运算符(=)
的作用是相似的, 唯一的区别 是‘<=>’
可以用来对NULL
进行判断 - 在两个操作数均为
NULL
时,其返回值为1
,而不为NULL
;当一个操作数为NULL
时,其返回值为0
,而不为NULL
示例一:
mysql> SELECT 1 <=> '1', 1 <=> 0, 'a' <=> 'a', (5 + 3) <=> (2 + 6), '' <=> NULL,NULL <=> NULL FROM dual;
+-----------+---------+-------------+---------------------+-------------+---------------+
| 1 <=> '1' | 1 <=> 0 | 'a' <=> 'a' | (5 + 3) <=> (2 + 6) | '' <=> NULL | NULL <=> NULL |
+-----------+---------+-------------+---------------------+-------------+---------------+
| 1 | 0 | 1 | 1 | 0 | 1 |
+-----------+---------+-------------+---------------------+-------------+---------------+
1 row in set (0.00 sec)
示例二:
#查询表中commission_pct为null的数据有哪些
SELECT employee_id,last_name,commission_pct FROM employees WHERE commission_pct <=> NULL;
2.3 不等于运算符
- 不等于运算符
(<>和!=)
用于判断两边的数字、字符串或者表达式的值是否不相等,
如果不相等则返回1
,相等则返回0
- 不等于运算符不能判断
NULL
值。如果两边的值有任意一个为NULL
,或两边都为NULL
,则结果为NULL
示例:
mysql> SELECT 1 <> 1, 1 != 2, 'a' != 'b', (3+4) <> (2+6), 'a' != NULL, NULL <> NULL;
+--------+--------+------------+----------------+-------------+--------------+
| 1 <> 1 | 1 != 2 | 'a' != 'b' | (3+4) <> (2+6) | 'a' != NULL | NULL <> NULL |
+--------+--------+------------+----------------+-------------+--------------+
| 0 | 1 | 1 | 1 | NULL | NULL |
+--------+--------+------------+----------------+-------------+--------------+
1 row in set (0.00 sec)
2.4 空运算符
空运算符(IS NULL或者ISNULL)
判断一个值是否为NULL
,如果为NULL
则返回1,否则返回0
。
示例一:
mysql> SELECT NULL IS NULL, ISNULL(NULL), ISNULL('a'), 1 IS NULL;
+--------------+--------------+-------------+-----------+
| NULL IS NULL | ISNULL(NULL) | ISNULL('a') | 1 IS NULL |
+--------------+--------------+-------------+-----------+
| 1 | 1 | 0 | 0 |
+--------------+--------------+-------------+-----------+
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;
2.5 非空运算符
非空运算符(IS NOT NULL)
判断一个值是否不为NULL
,如果不为NULL
则返回1,否则返
回0。
示例一:
mysql> SELECT NULL IS NOT NULL, 'a' IS NOT NULL, 1 IS NOT NULL;
+------------------+-----------------+---------------+
| NULL IS NOT NULL | 'a' IS NOT NULL | 1 IS NOT NULL |
+------------------+-----------------+---------------+
| 0 | 1 | 1 |
+------------------+-----------------+---------------+
1 row in set (0.00 sec)
示例二:
#查询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.6 最小值运算符
语法格式为:LEAST(值1,值2,...,值n)
其中,值n
表示参数列表中有n
个值。在有两个或多个参数的情况下,返回最小值
示例:
mysql> SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2);
+---------------+--------------------+-----------------+
| LEAST (1,0,2) | LEAST('b','a','c') | LEAST(1,NULL,2) |
+---------------+--------------------+-----------------+
| 0 | a | NULL |
+---------------+--------------------+-----------------+
1 row in set (0.00 sec)
由结果可以看到:
- 当参数是整数或者浮点数时,LEAST将返回其中最小的值
- 当参数为字符串时,返回字母表中顺序最靠前的字符
- 当比较值列表中有NULL时,不能判断大小,返回值为NULL
3.7 最大值运算符
语法格式为:GREATEST(值1,值2,...,值n)
其中,n
表示参数列表中有n
个值。当有两个或多个参数时,返回值为最大值。假如任意一个自变量为NULL
,则GREATEST()
的返回值为NULL
示例:
mysql> SELECT GREATEST(1,0,2), GREATEST('b','a','c'), GREATEST(1,NULL,2);
+-----------------+-----------------------+--------------------+
| GREATEST(1,0,2) | GREATEST('b','a','c') | GREATEST(1,NULL,2) |
+-----------------+-----------------------+--------------------+
| 2 | c | NULL |
+-----------------+-----------------------+--------------------+
1 row in set (0.00 sec)
由结果可以看到:
- 当参数中是整数或者浮点数时,GREATEST将返回其中最大的值
- 当参数为字符串时,返回字母表中顺序最靠后的字符
- 当比较值列表中有NULL时,不能判断大小,返回值为NULL
3.8 BETWEEN AND运算符
BETWEEN
运算符使用的格式通常为SELECT D FROM TABLE WHERE C BETWEEN A AND B
,此时,当C
大于或等于A
,并且C
小于或等于B
时,结果为1
,否则结果为0
。
示例一:
mysql> SELECT 1 BETWEEN 0 AND 1, 10 BETWEEN 11 AND 12, 'b' BETWEEN 'a' AND 'c';
+-------------------+----------------------+-------------------------+
| 1 BETWEEN 0 AND 1 | 10 BETWEEN 11 AND 12 | 'b' BETWEEN 'a' AND 'c' |
+-------------------+----------------------+-------------------------+
| 1 | 0 | 1 |
+-------------------+----------------------+-------------------------+
1 row in set (0.00 sec)
示例二:
mysql> SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500;
+-------------+---------+
| last_name | salary |
+-------------+---------+
| Khoo | 3100.00 |
| Baida | 2900.00 |
| Tobias | 2800.00 |
| Himuro | 2600.00 |
...
3.9 IN运算符
IN
运算符用于判断给定的值是否是IN
列表中的一个值,如果是则返回1
,否则返回0
。如果给定的值为NULL
,或者IN
列表中存在NULL
,则结果为NULL
。
示例一:
mysql> SELECT 'a' IN ('a','b','c'), 1 IN (2,3), NULL IN ('a','b'), 'a' IN ('a', NULL);
+----------------------+------------+-------------------+--------------------+
| 'a' IN ('a','b','c') | 1 IN (2,3) | NULL IN ('a','b') | 'a' IN ('a', NULL) |
+----------------------+------------+-------------------+--------------------+
| 1 | 0 | NULL | 1 |
+----------------------+------------+-------------------+--------------------+
1 row in set (0.00 sec)
示例二:
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201);
3.10 NOT IN运算符
NOT IN
运算符用于判断给定的值是否不是IN
列表中的一个值,如果不是IN
列表中的一
个值,则返回1
,否则返回0
。
示例:
mysql> SELECT 'a' NOT IN ('a','b','c'), 1 NOT IN (2,3);
+--------------------------+----------------+
| 'a' NOT IN ('a','b','c') | 1 NOT IN (2,3) |
+--------------------------+----------------+
| 0 | 1 |
+--------------------------+----------------+
1 row in set (0.00 sec)
3.11 LIKE运算符
LIKE
运算符主要用来匹配字符串,通常用于模糊匹配
- 如果满足条件则返回
1
,否则返回0
- 如果给定的值或者匹配条件为
NULL
,则返回结果为NULL
LIKE
运算符通常使用如下通配符:
%
:匹配0
个或多个字符_
:只能匹配一个字符
示例一:
mysql> SELECT NULL LIKE 'abc', 'abc' LIKE NULL;
+-----------------+-----------------+
| NULL LIKE 'abc' | 'abc' LIKE NULL |
+-----------------+-----------------+
| NULL | NULL |
+-----------------+-----------------+
1 row in set (0.00 sec)
示例二:
SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';
示例三:
SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';
ESCAPE
- 回避特殊符号的:使用转义符
- 例如:将[
%
]转为[$%
]、[]转为[$
],然后再加上[ESCAPE‘$’
]即可
示例:
mysql> SELECT job_id FROM jobs WHERE job_id LIKE 'IT\_%';
+---------+
| job_id |
+---------+
| IT_PROG |
+---------+
1 row in set (0.00 sec)
- 如果使用
\
表示转义,要省略ESCAPE
。如果不是\
,则要加上ESCAPE
关键字
示例:
mysql> SELECT job_id FROM jobs WHERE job_id LIKE 'IT$_%' escape '$';
+---------+
| job_id |
+---------+
| IT_PROG |
+---------+
1 row in set (0.00 sec)
3.12 REGEXP运算符
REGEXP
运算符用来匹配字符串,语法格式为:expr REGEXP 匹配条件
。如果expr
满足匹配条件,返回1
;如果不满足,则返回0
。
若expr或匹配条件任意一个为NULL
,则结果为NULL
。
REGEXP
运算符在进行匹配时,常用的有下面几种通配符:
^
匹配以该字符后面的字符开头的字符串$
匹配以该字符前面的字符结尾的字符串.
匹配任何一个单字符[...]
匹配在方括号内的任何字符- 例如,
[abc]
匹配a
或b
或c
。 - 为了命名字符的范围,使用一 个
-
。[a-z]
匹配任何字母,而[0-9]
匹配任何数字
- 例如,
*
匹配零个或多个在它前面的字符- 例如,
x*
匹配任何数量的x
字符,[0-9]*
匹配任何数量的数字, 而*
匹配任何数量的任何字符
- 例如,
示例一:
mysql> SELECT 'shkstart' REGEXP '^s', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk';
+------------------------+------------------------+------------------------+
| 'shkstart' REGEXP '^s' | 'shkstart' REGEXP 't$' | 'shkstart' REGEXP 'hk' |
+------------------------+------------------------+------------------------+
| 1 | 1 | 1 |
+------------------------+------------------------+------------------------+
1 row in set (0.02 sec)
示例二:
mysql> SELECT 'xiaoyangren' REGEXP 'ya.g', 'xiaoyangren' REGEXP '[cai]';
+-----------------------------+------------------------------+
| 'xiaoyangren' REGEXP 'ya.g' | 'xiaoyangren' REGEXP '[cai]' |
+-----------------------------+------------------------------+
| 1 | 1 |
+-----------------------------+------------------------------+
1 row in set (0.00 sec)