文章目录
SQL中的常用函数
SQL中常用的函数可分为：
1. 字符串函数
| 函数 | 描述 |
|---|
| CONCAT(S1, S2,…,SN) | 连接多个字符串为一个字符串 |
| INSERT(str, x, y, instr) | 将字符串str从x位置开始，y个字符长的子串替换为字符串instr |
| LOWER(str) | 将字符串中的所有字符变成小写 |
| UPPER(str) | 将字符串中的所有字符变成大写 |
| LEFT(str, x) | 返回字符串最左边的x个字符 |
| RIGHT(str, x) | 返回字符串最右边的x个字符 |
| LPAD(str, n, pad) | 用字符串pad对str最左边进行填充，直到长度为n个字符长度 |
| RPAD(str, n, pad) | 用字符串pad对str最右边进行填充，直到长度为n个字符长度 |
| LTRIM(str) | 去掉字符串str左侧的空格 |
| RTRIM(str) | 去掉字符串str右侧的空格 |
| REPEAT(str) | 返回str重复x次的结果 |
| STRCMP(s1, s2) | 比较两个字符串ASCII码值的大小 |
| TRIM(str) | 去掉字符串首尾的空格 |
| SUBSTRING(str, x, y) | 返回从字符串str的x位置起y个字符长度的子串 |
mysql> SELECT CONCAT('a', 'b'), CONCAT('a', NULL);
+
| CONCAT('a', 'b') | CONCAT('a', NULL) |
+
| ab | NULL |
+
1 row in set (0.00 sec)
NOTE：任何字符串和NULL拼接得到的结果都是NULL
mysql> SELECT INSERT('aaaaa', 2, 2, 'b');
+
| INSERT('aaaaa', 2, 2, 'b') |
+
| abaa |
+
1 row in set (0.00 sec)
mysql> SELECT LOWER('MYSQL'), UPPER('mysql');
+
| LOWER('MYSQL') | UPPER('mysql') |
+
| mysql | MYSQL |
+
1 row in set (0.00 sec)
mysql> SELECT LEFT('mysql', 2), RIGHT('mysql', 2);
+
| LEFT('mysql', 2) | RIGHT('mysql', 2) |
+
| my | ql |
+
1 row in set (0.00 sec)
mysql> SELECT LEFT('mysql', 2), RIGHT('mysql', 2), LEFT('mysql', NULL), RIGHT('mysql', NULL);
+
| LEFT('mysql', 2) | RIGHT('mysql', 2) | LEFT('mysql', NULL) | RIGHT('mysql', NULL) |
+
| my | ql | NULL | NULL |
+
1 row in set (0.00 sec)
如果LEFT或是RIGHT指定长度为NULL时，这里同样返回NULL
mysql> SELECT LPAD('MYSQL', 13, 'LOVE'), RPAD('MYSQL', 13, 'LOVE');
+
| LPAD('MYSQL', 13, 'LOVE') | RPAD('MYSQL', 13, 'LOVE') |
+
| LOVELOVEMYSQL | MYSQLLOVELOVE |
+
1 row in set (0.00 sec)
mysql> SELECT LTRIM(' | MYSQL '), RTRIM(' MYSQL| ');
+
| LTRIM(' | MYSQL ') | RTRIM(' MYSQL| ') |
+
| | MYSQL | MYSQL| |
+
1 row in set (0.00 sec)
mysql> SELECT STRCMP('MYSQL', 'MYSQL'), STRCMP('MYSQL', 'ORACLE');
+
| STRCMP('MYSQL', 'MYSQL') | STRCMP('MYSQL', 'ORACLE') |
+
| 0 | -1 |
+
1 row in set (0.01 sec)
其他的根据函数描述就知道用法，这里就不做试验了。
2. 数值函数
| 函数 | 描述 |
|---|
| ABS(x) | 返回x的绝对值 |
| CEIL(x) | 向上取整 |
| FLOOR(x) | 向下取整 |
| MOD(x, y) | 返回 x / y 的模 |
| RAND() | 生成0~1之间的随机数 |
| ROUND(x, y) | 返回x的四舍五入的有y位小数的值 |
| TRUNCATE(x, y) | 返回x截断位y位小数的结果，仅仅截断不进行四舍五入 |
mysql> SELECT ROUND(1.2222, 3), ROUND(1.2, 3);
+
| ROUND(1.2222, 3) | ROUND(1.2, 3) |
+
| 1.222 | 1.200 |
+
1 row in set (0.00 sec)
mysql> SELECT TRUNCATE(1.222, 2);
+
| TRUNCATE(1.222, 2) |
+
| 1.22 |
+
1 row in set (0.00 sec)
3. 日期和时间函数
| 函数 | 描述 |
|---|
| 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, INTERVAL expr type) | 返回一个日期或事件值加上一个时间间隔的时间值 |
| DATEDIFF(expr, expr2) | 返回起始时间expr和结束时间expr2之间的天数 |
mysql> SELECT CURDATE(), CURTIME(), NOW();
+
| CURDATE() | CURTIME() | NOW() |
+
| 2020-04-16 | 17:44:54 | 2020-04-16 17:44:54 |
+
1 row in set (0.01 sec)
mysql> SELECT WEEK(NOW()), YEAR(NOW()), HOUR(CURTIME()), MINUTE(CURTIME()), MONTHNAME(NOW());
+
| WEEK(NOW()) | YEAR(NOW()) | HOUR(CURTIME()) | MINUTE(CURTIME()) | MONTHNAME(NOW()) |
+
| 15 | 2020 | 17 | 47 | April |
+
1 row in set (0.01 sec)
mysql> SELECT DATEDIFF('2008-08-08', NOW());
+
| DATEDIFF('2008-08-08', NOW()) |
+
| -4269 |
+
1 row in set (0.01 sec)
4. 流程函数
| 函数 | 描述 |
|---|
| IF(value,t, f) | 如果value为真，返回t,否则返回f |
| IFNULL(value1, value2) | 如果value不为空。返回value1，否则返回value2 |
| CASE WHEN [value1] THEN [results]…ELSE [default] END | 如果value为真，返回result1， 否则返回default |
| CASE [expr] WHEN [value] THEN [results] … ELSE[default] END | 如果expr等于value1，返回result1，否则返回default |
mysql> SELECT IF(points > 2500, 'high', 'low') FROM CUSTOMERS;
+
| IF(points > 2500, 'high', 'low') |
+
| low |
| low |
| high |
| low |
| high |
| high |
| low |
| low |
| low |
| low |
| low |
+
11 rows in set (0.00 sec)
mysql> SELECT IFNULL(CUSTOMERS, 0) FROM CUSTOMERS;
ERROR 1054 (42S22): Unknown column 'CUSTOMERS' in 'field list'
mysql> SELECT IFNULL(points, 0) FROM CUSTOMERS;
+
| IFNULL(points, 0) |
+
| 2273 |
| 947 |
| 2967 |
| 457 |
| 3675 |
| 3073 |
| 1672 |
| 205 |
| 1486 |
| 796 |
| 81 |
+
11 rows in set (0.00 sec)
mysql> SELECT CASE WHEN points > 2500 THEN 'high' ELSE 'low' END FROM CUSTOMERS;
+
| CASE WHEN points > 2500 THEN 'high' ELSE 'low' END |
+
| low |
| low |
| high |
| low |
| high |
| high |
| low |
| low |
| low |
| low |
| low |
+
11 rows in set (0.00 sec)
5. 其他常用函数
| 函数 | 描述 |
|---|
| DATABASE() | 返回当前数据库名 |
| VERSION() | 返回当前数据库版本 |
| USER() | 返回当前登录用户名 |
| INET_ATON(IP) | 返回IP地址的数字表示 |
| INET_NTOA(num) | 返回数字代表的IP地址 |
| PASSWORD(str) | 返回sr的加密版本，一个41位的字符串 |
| MD5(str) | 返回str的MD5值 |
