目录
1. 算术运算符
算术运算符主要用于数学运算,其可以连接运算符前后的两个数值或表达式,对数值或表达式进行加 (+)、减(-)、乘(*)、除(/)和取模(
%
)运算。
1.加法与减法运算符
由运算结果可以得出如下结论:
一个整数类型的值对整数进行加法和减法操作,结果还是一个整数;一个整数类型的值对浮点数进行加法和减法操作,结果是一个浮点数;加法和减法的优先级相同,进行先加后减操作与进行先减后加操作的结果是一样的;在 Java 中, + 的左右两边如果有字符串,那么表示字符串的拼接。但是在 MySQL 中 + 只表示数值相加。如果遇到非数值类型,先尝试转成数值,如果转失败,就按 0 计算。(补充: MySQL中字符串拼接要使用字符串函数 CONCAT() 实现)
2.乘法与除法运算符
# 计算出员工的年基本工资SELECT employee_id,salary,salary * 12 annual_salFROM employees;
由运算结果可以得出如下结论:
一个数乘以整数 1 和除以整数 1 后仍得原数;一个数乘以浮点数 1 和除以浮点数 1 后变成浮点数,数值与原数相等;一个数除以整数后,不管是否能除尽,结果都为一个浮点数;一个数除以另一个数,除不尽时,结果为一个浮点数,并保留到小数点后 4 位;乘法和除法的优先级相同,进行先乘后除操作与先除后乘操作,得出的结果相同。在数学运算中, 0 不能用作除数,在 MySQL 中,一个数除以 0 为 NULL 。
3.求模(求余)运算符
将t22表中的字段i对3和5进行求模(求余)运算。
# 筛选出 employee_id 是偶数的员工SELECT * FROM employeesWHERE employee_id MOD 2 = 0 ;
可以看到,12对3求模后的结果为0,对5求模后的结果为2
2. 比较运算符
比较运算符用来对表达式左边的操作数和右边的操作数进行比较,比较的结果为真则返回
1
,比较的结果为假则返回0
,其他情况则返回
NULL
。
比较运算符经常被用来作为
SELECT
查询语句的条件来使用,返回符合条件的结果记录。
1.等号运算符
等号运算符(=)判断等号两边的值、字符串或表达式是否相等,如果相等则返回
1
,不相等则返回 0。
在使用等号运算符时,遵循如下规则:
如果等号两边的值、字符串或表达式都为字符串,则 MySQL 会按照字符串进行比较,其比较的是每个字符串中字符的ANSI 编码是否相等。如果等号两边的值都是整数,则 MySQL 会按照整数来比较两个值的大小。如果等号两边的值一个是整数,另一个是字符串,则 MySQL 会将字符串转化为数字进行比较。如果等号两边的值、字符串或表达式中有一个为 NULL ,则比较结果为 NULL 。对比: SQL 中赋值符号使用 :=
1 row in set , 2 warnings ( 0.00 sec)# 查询 salary=10000 ,注意在 Java 中比较是 ==SELECT employee_id,salary FROM employees WHERE salary = 10000 ;
2.安全等于运算符
安全等于运算符(<=>)与等于运算符(=)的作用是相似的,
唯一的区别
是
‘<=>’
可 以用来对NULL
进行判断。在两个操作数均为
NULL
时,其返回值为
1
,而不为
NULL
;当一个操作数为
NULL时,其返回值为0
,而不为
NULL
。
# 查询 commission_pct 等于 0.40SELECT employee_id,commission_pct FROM employees WHERE commission_pct = 0.40 ;SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> 0.40 ;# 如果把 0.40 改成 NULL 呢?
可以看到,使用安全等于运算符时,两边的操作数的值都为
NULL
时,返回的结果为
1
而不是
NULL
,其他返回结果与等于运算符相同。
3.不等于运算符
不等于运算符(<>和
!=
)用于判断两边的数字、字符串或者表达式的值是否不相等, 如果不相等则返回1
,相等则返回
0
。不等于运算符不能判断
NULL
值。如果两边的值有任意一个为
NULL
,或两边都为NULL
,则结果为
NULL
。
SQL
语句示例如下:
此外,还有非符号类型的运算符:
4. 空运算符
空运算符(IS NULL或者
ISNULL
)判断一个值是否为
NULL
,如果为
NULL
则返回
1
,否则返回0。
SQL
语句示例如下:
# 查询 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 ;
SELECT last_name, manager_idFROM employeesWHERE manager_id IS NULL ;
5. 非空运算符
非空运算符(IS NOT NULL)判断一个值是否不为
NULL
,如果不为
NULL
则返回
1
,否则返回0
。
SQL
语句示例如下:
# 查询 commission_pct 不等于 NULLSELECT 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);
6. 最小值运算符
语法格式为:
LEAST(
值
1
,值
2
,
...
,值
n)
。其中,
“
值
n”
表示参数列表中有
n
个值。在有 两个或多个参数的情况下,返回最小值。
由结果可以看到,当参数是整数或者浮点数时,
LEAST
将返回其中最小的值;当参数为字符串时,返回字母表中顺序最靠前的字符;当比较值列表中有NULL
时,不能判断大小,返回值为
NULL
。
7. 最大值运算符
语法格式为:
GREATEST(
值
1
,值
2
,
...
,值
n)
。其中,
n
表示参数列表中有
n
个值。当有两个或多个参数时,返回值为最大值。假如任意一个自变量为NULL
,则
GREATEST()
的返回值为
NULL
。
由结果可以看到,当参数中是整数或者浮点数时,
GREATEST
将返回其中最大的值;当参数为字符串时,返回字母表中顺序最靠后的字符;当比较值列表中有NULL
时,不能判断大小,返回值为
NULL
。
8. BETWEEN AND运算符
BETWEEN
运算符使用的格式通常为
SELECT D FROM TABLE WHERE C BETWEEN A AND B,此时,当
C
大于或等于
A
,并且
C
小于或等于
B
时,结果为
1
,否则结果为
0
。
SELECT last_name, salaryFROM employeesWHERE salary BETWEEN 2500 AND 3500 ;
9. IN运算符
IN
运算符用于判断给定的值是否是
IN
列表中的一个值,如果是则返回
1
,否则返回
0
。如果给定的值为NULL
,或者
IN
列表中存在
NULL
,则结果为
NULL
。
SELECT employee_id, last_name, salary, manager_idFROM employeesWHERE manager_id IN ( 100 , 101 , 201 )
11. LIKE运算符
LIKE
运算符主要用来匹配字符串,通常用于模糊匹配,如果满足条件则返回
1
,否则返回 0。如果给定的值或者匹配条件为
NULL
,则返回结果为
NULL
。
LIKE
运算符通常使用如下通配符:
“%” :匹配 0 个或多个字符。“_” :只能匹配一个字符。
SQL语句示例如下
SELECT first_nameFROM employeesWHERE first_name LIKE 'S%' ;
SELECT last_nameFROM employeesWHERE last_name LIKE '_o%' ;
ESCAPE
回避特殊符号的:
使用转义符 : \
。例:将
[%]
转为
[$%]
、
[]
转为
[$]
,然后再加上
[ESCAPE‘$’]
即可
SELECT job_idFROM jobsWHERE job_id LIKE ‘IT\_%‘;
12. REGEXP运算符
REGEXP
运算符用来匹配字符串,语法格式为:
expr REGEXP
匹配条件
。如果
expr
满足匹配条件,返回1;如果不满足,则返回
0
。若
expr
或匹配条件任意一个为
NULL
,则结果为
NULL
。
REGEXP运算符在进行匹配时,常用的有下面几种通配符:
( 1 ) ‘^’ 匹配以该字符后面的字符开头的字符串。( 2 ) ‘$’ 匹配以该字符前面的字符结尾的字符串。( 3 ) ‘.’ 匹配任何一个单字符。( 4 ) “[...]” 匹配在方括号内的任何字符。例如, “[abc]” 匹配 “a” 或 “b” 或 “c” 。为了命名字符的范围,使用一个 ‘-’ 。 “[a-z]” 匹配任何字母,而 “[0-9]” 匹配任何数字。( 5 ) ‘*’ 匹配零个或多个在它前面的字符。例如, “x*” 匹配任何数量的 ‘x’ 字符, “[0-9]*” 匹配任何数量的数字,而 “*” 匹配任何数量的任何字符。
SQL语句示例如下:
3. 逻辑运算符
逻辑运算符主要用来判断表达式的真假,在
MySQL
中,逻辑运算符的返回结果为
1
、
0
或者
NULL
。
MySQL
中支持
4
种逻辑运算符如下:
1.逻辑非运算符
逻辑非(
NOT
或
!
)运算符表示当给定的值为
0
时返回
1
;当给定的值为非
0
值时返回
0
; 当给定的值为NULL
时,返回
NULL
。
SELECT last_name, job_idFROM employeesWHERE job_id NOT IN ( 'IT_PROG' , 'ST_CLERK' , 'SA_REP' );
2.逻辑与运算符
逻辑与(AND或
&&
)运算符是当给定的所有值均为非
0
值,并且都不为
NULL
时,返回1;当给定的一个值或者多个值为
0
时则返回
0
;否则返回
NULL
。
SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary >= 10000AND job_id LIKE '%MAN%' ;
3.逻辑或运算符
逻辑或(OR或
||
)运算符是当给定的值都不为
NULL
,并且任何一个值为非
0
值时,则返回1
,否则返回
0
;当一个值为
NULL
,并且另一个值为非
0
值时,返回
1
,否则返回
NULL
;当两个值都为 NULL时,返回
NULL
。
# 查询基本薪资不在 9000-12000 之间的员工编号和基本薪资SELECT employee_id,salary FROM employeesWHERE NOT (salary >= 9000 AND salary <= 12000 );SELECT employee_id,salary FROM employeesWHERE salary < 9000 OR salary > 12000 ;SELECT employee_id,salary FROM employeesWHERE salary NOT BETWEEN 9000 AND 12000 ;
SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary >= 10000OR job_id LIKE '%MAN%' ;
注意:OR 可以和 AND 一起使用,但是在使用时要注意两者的优先级,由于 AND 的优先级高于 OR ,因此先对AND 两边的操作数进行操作,再与 OR 中的操作数结合
4.逻辑异或运算符
逻辑异或(XOR)运算符是当给定的值中任意一个值为
NULL
时,则返回
NULL
;如果 两个非NULL
的值都是
0
或者都不等于
0
时,则返回
0
;如果一个值为
0
,另一个值不为
0
时,则返回
1
。
select last_name,department_id,salaryfrom employeeswhere department_id in ( 10 , 20 ) XOR salary > 8000 ;
4. 位运算符
位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数,然后进行位运算,最后将计算结果从二进制变回十进制数。 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
,否则返回
0
。
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
进行按位与操作,结果为
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
。
5. 运算符的优先级
拓展:使用正则表达式查询
正则表达式通常被用来检索或替换那些符合某个模式的文本内容,根据指定的匹配模式匹配文本中符合 要求的特殊字符串。例如,从一个文本文件中提取电话号码,查找一篇文章中重复的单词或者替换用户 输入的某些敏感词语等,这些地方都可以使用正则表达式。正则表达式强大而且灵活,可以应用于非常 复杂的查询。
MySQL
中使用
REGEXP
关键字指定正则表达式的字符匹配模式。下表列出了
REGEXP
操作符中常用字符匹配列表
1. 查询以特定字符或字符串开头的记录
字符‘^’匹配以特定字符或者字符串开头的文本。
在
fruits
表中,查询
f_name
字段以字母
‘b’
开头的记录,
SQL
语句如下:
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^b';
2. 查询以特定字符或字符串结尾的记录
字符‘$’匹配以特定字符或者字符串结尾的文本。
在
fruits
表中,查询
f_name
字段以字母
‘y’
结尾的记录,
SQL
语句如下:
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'y$' ;
3. 用符号"."来替代字符串中的任意一个字符
字符‘.’匹配任意一个字符。 在fruits表中,查询f_name字段值
包含字母
‘a’
与
‘g’
且两个字母之间只有一个字母的记录,
SQL
语句如下:
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'a.g';
4. 使用"*"和"+"来匹配多个字符
星号‘*’匹配前面的字符任意多次,包括0次。加号‘+’匹配前面的字符至少一次。
在
fruits
表中,查询
f_name
字段值以字母
‘b’
开头且
‘b’
后面出现字母
‘a’
的记录,
SQL
语句如下:
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba*' ;
在
fruits
表中,查询
f_name
字段值以字母
‘b’
开头且
‘b’
后面出现字母
‘a’
至少一次的记录,
SQL
语句如下:
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba+' ;
5. 匹配指定字符串
正则表达式可以匹配指定字符串,只要这个字符串在查询文本中即可,如要匹配多个字符串,多个字符串之间使用分隔符‘|’隔开。
在
fruits
表中,查询
f_name
字段值包含字符串
“on”
的记录,
SQL
语句如下:
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on' ;
在
fruits
表中,查询
f_name
字段值包含字符串
“on”
或者
“ap”
的记录,
SQL
语句如下:
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on|ap' ;
之前介绍过,
LIKE
运算符也可以匹配指定的字符串,但与
REGEXP
不同,
LIKE
匹配的字符串如果在文本中间出现,则找不到它,相应的行也不会返回。REGEXP
在文本内进行匹配,如果被匹配的字符串在文本中出现,REGEXP
将会找到它,相应的行也会被返回。对比结果如下所示。
在
fruits
表中,使用
LIKE
运算符查询
f_name
字段值为
“on”
的记录,
SQL
语句如下:
mysql> SELECT * FROM fruits WHERE f_name like 'on' ;Empty set ( 0.00 sec)
6. 匹配指定字符中的任意一个
方括号“[]”指定一个字符集合,只匹配其中任何一个字符,即为所查找的文本。
在
fruits
表中,查找
f_name
字段中包含字母
‘o’
或者
‘t’
的记录,
SQL
语句如下:
mysql> SELECT * FROM fruits WHERE f_name REGEXP '[ot]' ;
在
fruits
表中,查询
s_id
字段中包含
4
、
5
或者
6
的记录,
SQL
语句如下:
mysql> SELECT * FROM fruits WHERE s_id REGEXP '[456]' ;
7. 匹配指定字符以外的字符
“[^字符集合]” 匹配不在指定集合中的任何字符。
在
fruits
表中,查询
f_id
字段中包含字母
a~e
和数字
1~2
以外字符的记录,
SQL
语句如下:
mysql> 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
语句如下:
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}' ;
在
fruits
表中,查询
f_name
字段值出现字符串
“ba”
最少
1
次、最多
3
次的记录,
SQL
语句如下:
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}' ;