MySQL 运算符 常用函数

运算符

算数运算符

运算符 作用
+ 加法
- 减法
* 乘法
/,DIV 除法,返回商
%,MOD 除法,返回余数
1
2
3
4
5
6
7
MariaDB [test]> select 1/0, 100%0, 1 div 0, MOD(100, 0), 3/2, 3 DIV 2, 3%2, mod(3, 2), 5.3/2, 5.3 DIV 2, 5.3%2, mod(5.3, 2), 5.3 mod 2;
+------+-------+---------+-------------+--------+---------+------+-----------+---------+-----------+-------+-------------+-----------+
| 1/0 | 100%0 | 1 div 0 | MOD(100, 0) | 3/2 | 3 DIV 2 | 3%2 | mod(3, 2) | 5.3/2 | 5.3 DIV 2 | 5.3%2 | mod(5.3, 2) | 5.3 mod 2 |
+------+-------+---------+-------------+--------+---------+------+-----------+---------+-----------+-------+-------------+-----------+
| NULL | NULL | NULL | NULL | 1.5000 | 1 | 1 | 1 | 2.65000 | 2 | 1.3 | 1.3 | 1.3 |
+------+-------+---------+-------------+--------+---------+------+-----------+---------+-----------+-------+-------------+-----------+
1 row in set (0.00 sec)

比较运算符

运算符 作用
= 等于
<>或!= 不等于
<=> NULL 安全的等于(NULL-safe)
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN 存在与指定范围
IN 存在于指定集合
IS NULL 为 NULL
IS NOT NULL 不为 NULL
LIKE 通配符匹配
REGEXP 或 RLIKE 正则表达式
  1. =、<>、!=不能用于NULL比较
  2. <=>安全的等于运算符,即使操作数为NULL也可以正确比较
  3. BETWEEN运算符的使用格式为”a BETWEEN min AND max”,当 a 大于等于 min 并且小于等于 max,则返回值为 1,否则返回 0;当操作数 a、min、max 类型相同时,此表达式等价于(a>=min and a<=max),当操作数类型不同时,比较时会遵循类型转换原则进行转换后,再进行比较运算。
  4. “IN”运算符的使用格式为”a IN (value1, value2, … …)”,当 a 的值存在于列表中时,则整个比较表达式返回的值为 1,否则返回 0。
  5. “IS NULL”运算符的使用格式为”a IS NULL”,当 a 的值为 NULL,则返回值为 1,否则返回 0。
  6. “IS NOT NULL”运算符的使用格式为”a IS NOT NULL”。和”IS NULL”相反,当 a 的值不为 NULL,则返回值为 1,否则返回 0。
  7. “LIKE”运算符的使用格式为”a LIKE %123%”,当 a 中含有字符串”123”时,则返回值为 1,否则返回 0。
  8. “REGEXP”运算符的使用格式为”str REGEXP str-pat”,当 str 字符串中含有 str-pat 相匹配的字符串时,则返回值为 1,否则返回 0。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
MariaDB [test]> select 1=0, 1=1, NULL=NULL, 1<>0, 1<>1, NULL<>NULL, 1<=>1, 2<=>0, NULL<=>NULL;
+-----+-----+-----------+------+------+------------+-------+-------+-------------+
| 1=0 | 1=1 | NULL=NULL | 1<>0 | 1<>1 | NULL<>NULL | 1<=>1 | 2<=>0 | NULL<=>NULL |
+-----+-----+-----------+------+------+------------+-------+-------+-------------+
| 0 | 1 | NULL | 1 | 0 | NULL | 1 | 0 | 1 |
+-----+-----+-----------+------+------+------------+-------+-------+-------------+
1 row in set (0.00 sec)

MariaDB [test]> select '123' like '%123%', '123456' like '%123%', '0123123' like '%123%', '01 23' like '%123%';
+--------------------+-----------------------+------------------------+----------------------+
| '123' like '%123%' | '123456' like '%123%' | '0123123' like '%123%' | '01 23' like '%123%' |
+--------------------+-----------------------+------------------------+----------------------+
| 1 | 1 | 1 | 0 |
+--------------------+-----------------------+------------------------+----------------------+
1 row in set (0.00 sec)

MariaDB [test]> select 'abcdef' regexp 'ab', 'abcdef' regexp 'k', 'a bcdef' regexp 'ab';
+----------------------+---------------------+-----------------------+
| 'abcdef' regexp 'ab' | 'abcdef' regexp 'k' | 'a bcdef' regexp 'ab' |
+----------------------+---------------------+-----------------------+
| 1 | 0 | 0 |
+----------------------+---------------------+-----------------------+
1 row in set (0.00 sec)

逻辑运算符

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

位运算符

运算符 作用
& 位与(位 AND)
| 位或 (位 OR )
^ 位异或(位 XOR)
~ 位取反
>> 位右移
<< 位左移

运算符优先级

优先级顺序 运算符
1 :=
2 || , OR, XOR
3 &&, AND
4 NOT
5 BETWEEN, CASE, WHEN, THEN, ELSE
6 =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
7 |
8 &
9 <<, >>
10 -, +
11 *, /, DIV, %, MOD
12 ^
13 - (一元减号), ~ (一元比特反转)
14 !

常用函数

字符串函数

函数 功能
CONCAT(S1,S2,…Sn) 连接S1,S2,…Sn为一个字符串
INSERT(str,x,y,instr) 将字符串str从第x位置开始,y个字符长的子串替换为字符串instr
LOWER(str) 将字符串str中所有字符变为小写
UPPER(str) 将字符串str中所有字符变为大写
LEFT(str,x) 返回字符串str最左边的x个字符
RIGHT(str,x) 返回字符串str最右边的x个字符
LPAD(str,n,pad) 用字符串pad对str最左边进行填充,直到长度为n个字符长度
RPAD(str,n,pad) 用字符串pad对str最右边进行填充,直到长度为n个字符长度
LTRIM(str) 去掉字符串str左侧的空格
RTRIM(str) 去掉字符串str行尾的空格
REPEAT(str,x) 返回str重复x次的结果
REPLACE(str,a,b) 用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2) 比较字符串s1和s2
TRIM(str) 去掉字符串行尾和行头的空格
SUBSTRING(str,x,y) 返回从字符串strx位置起y个字符长度的字串

数值函数

函数 功能
ABS(x) 返回x的绝对值
CEIL(x) 返回大于x的最大整数值
FLOOR(x) 返回小于x的最大整数值
MOD(x,y) 返回x/y的模
RAND() 返回0到1内的随机值
ROUND(x,y) 返回参数x的四舍五入的有y位小数的值
TRUNCATE(x,y) 返回数字x截断为y位小数的结果

日期和时间函数

函数 功能
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前的日期和时间
UNIX_TIMESTAMP(date) 返回日期date的UNIX时间戳
FROM_UNIXTIME 返回UNIX时间戳的日期值
WEEK(date) 返回日期date为一年中的第几周
YEAR(date) 返回日期date的年份
HOUR(time) 返回time的小时值
MINUTE(time) 返回time的分钟值
MONTHNAME(date) 返回date的月份名
DATE_FORMAT(date,fmt) 返回按字符串fmt格式化日期date值
DATE_ADD(date,INTERVALexprtype) 返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr,expr2) 返回起始时间expr和结束时间expr2之间的天数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
MariaDB [test]> select CURDATE(), CURTIME(), NOW(), UNIX_TIMESTAMP(now()), FROM_UNIXTIME(1521907408),
-> WEEK(now()), YEAR(now()), HOUR(CURTIME()), MINUTE(CURTIME()), MONTHNAME(now())\G
*************************** 1. row ***************************
CURDATE(): 2018-03-25
CURTIME(): 00:13:13
NOW(): 2018-03-25 00:13:13
UNIX_TIMESTAMP(now()): 1521907993
FROM_UNIXTIME(1521907408): 2018-03-25 00:03:28
WEEK(now()): 12
YEAR(now()): 2018
HOUR(CURTIME()): 0
MINUTE(CURTIME()): 13
MONTHNAME(now()): March
1 row in set (0.00 sec)

MariaDB [test]> select DATE_FORMAT(now(),'%M,%D,%Y'),
-> now() current,
-> date_add(now(),INTERVAL 31 day) after31days,
-> date_add(now(),INTERVAL '1_2' year_month) after_oneyear_twomonth,
-> date_add(now(),INTERVAL -31 day) before31days,
-> date_add(now(),INTERVAL '-1_-2' year_month) before_oneyear_twomonth\G
*************************** 1. row ***************************
DATE_FORMAT(now(),'%M,%D,%Y'): March,25th,2018
current: 2018-03-25 00:36:15
after31days: 2018-04-25 00:36:15
after_oneyear_twomonth: 2019-05-25 00:36:15
before31days: 2018-02-22 00:36:15
before_oneyear_twomonth: 2017-01-25 00:36:15
1 row in set (0.00 sec)

DATE_FORMAT

DATE_FORMAT(date,fmt)函数:按字符串fmt格式化日期date值,此函数能够按指定的格式显示日期

格式符 格式说明
%S,%s 两位数字形式的秒(00,01,…,59)
%i 两位数字形式的分(00,01,…,59)
%H 两位数字形式的小时,24 小时(00,01,…,23)
%h,%I 两位数字形式的小时,12 小时(01,02,…,12)
%k 数字形式的小时,24 小时(0,1,…,23)
%l 数字形式的小时,12 小时(1,2,…,12)
%T 24 小时的时间形式(hh:mm:ss)
%r 12 小时的时间形式(hh:mm:ssAM 或 hh:mm:ssPM)
%p AM 或 PM
%W 一周中每一天的名称(Sunday,Monday,…,Saturday)
%a 一周中每一天名称的缩写(Sun,Mon,…,Sat)
%d 两位数字表示月中的天数(00,01,…,31)
%e 数字形式表示月中的天数(1,2,…,31)
%D 英文后缀表示月中的天数(1st,2nd,3rd,…)
%w 以数字形式表示周中的天数(0=Sunday,1=Monday,…,6=Saturday)
%j 以 3 位数字表示年中的天数(001,002,…,366)
%U 周(0,1,52),其中 Sunday 为周中的第一天
%u 周(0,1,52),其中 Monday 为周中的第一天
%M 月名(January,February,…,December)
%b 缩写的月名(January,February,…,December)
%m 两位数字表示的月份(01,02,…,12)
%c 数字表示的月份(1,2,…,12)
%Y 4 位数字表示的年份
%y 两位数字表示的年份
%% 直接值“%

DATE_ADD

DATE_ADD(date,INTERVAL expr type)函数:返回与所给日期date相差INTERVAL时间段的日期
其中INTERVAL是间隔类型关键字,expr是一个表达式,这个表达式对应后面的类型,type是间隔类型

表达式类型 描述 格式
HOUR 小时 hh
MINUTE mm
SECOND ss
YEAR YY
MONTH MM
DAY DD
YEAR_MONTH 年和月 YY-MM
DAY_HOUR 日和小时 DD hh
DAY_MINUTE 日和分钟 DD hh:mm
DAY_SECOND 日和秒 DD hh:mm:ss
HOUR_MINUTE 小时和分 hh:mm
HOUR_SECOND 小时和秒 hh:ss
MINUTE_SECOND 分钟和秒 mm:ss

流程函数

函数 功能
IF(value,t f) 如果 value 是真,返回 t;否则返回 f
IFNULL(value1,value2) 如果 value1 不为空返回 value1,否则返回 value2
CASE WHEN [value1] THEN[result1]…ELSE[default]END 如果 value1 是真,返回 result1,否则返回 default
CASE [expr] WHEN [value1] THEN[result1]…ELSE[default]END 如果 expr 等于 value1,返回 result1,否则返回 default

其他常用函数

函数 功能
DATABASE() 返回当前数据库名
VERSION() 返回当前数据库版本
USER() 返回当前登录用户名
INET_ATON(IP) 返回IP地址的数字表示
INET_NTOA(num) 返回数字代表的IP地址
PASSWORD(str) 返回字符串str的加密版本
MD5() 返回字符串str的MD5值