一、字符函数
1、<>与!=有什么区别?
<>
是标准语法.可以移植到其他任何平台.
!=
是非标准语法.可移植性差.
但都是表示不等于的意思,应用中尽量用<>
2、安全等于:<=>
SELECT * FROM table WHERE column <=> null
=>等价于
SELECT * FROM table WHERE column is null
3、合并字段值:CONCAT
mysql:
SELECT CONCAT(last_name, " ", first_name) from contacts
oracle:
需要注意oracle中的concat只可以拼接两个字段!!!
//方法1:
SELECT CONCAT(CONCAT(last_name, ' '), first_name) FROM contacts
//方法2:
SELECT last_name ||' -- '||first_name||'&' AS name FROM contacts
4、判断为空字段:IFNULL(item1, item2)
如果item1不为null则返回item1,反之则返回item2
SELECT IFNULL(item1, item2) FROM table
5、大小写转换:UPPER、LOWER
UPPER转大写,OWER转小写
SELECT UPPER(last_name), LOWER(first_name) FROM contacts
6、截取字符串:SUBSTR
截取某列从第2位开始到第5位 需要注意字符索引丛1开始
SELECT SUBSTR(col, 2, 5) FROM table
7、截取字串位置:INSTR
截取的字符串在源字符串中的位置
SELECT INSTR("aaabbbccc", 'ccc') FROM table
8、去除前后空格:TRIM
SELECT TRIM(col) FROM tab
去除字段中前后指定的字符
SELECT TRIM('a' from col) FROM tab
SELECT TRIM('a' from 'aaaaaaTTTTTTaaaaaaTTTTTaaaaa') FROM tab
输出=>
'TTTTTTaaaaaaTTTTT'
9、左填充:LPAD
LPAD(字段, 总字符长度, 填充字符) 如果总字符长度小于字段长度则会substr字段
SELECT LPAD(col, 10, '*') FROM tab
同理右填充rpad
10、替换指定字符:REPLACE
SELECT REPLACE(col, strFront, strAfter) FROM tab
SELECT REPLACE("testHHHHHHH", 'test', 'world') FROM tab
输出=>
'worldHHHHHHH'
11、类型转化函数:CONVERT、CAST
- 字符集转换
SELECT CONVERT(col_num USING utf8)
- 类型转换
CAST:CAST(value AS type);
CONVERT:CONVERT(value, type);
可转换的类型
- 二进制,同带binary前缀的效果 : BINARY
- 字符型,可带参数 : CHAR()
- 日期 : DATE
- 时间: TIME
- 日期时间型 : DATETIME
- 浮点数 : DECIMAL
- 整数 : SIGNED
- 无符号整数 : UNSIGNED
-- 字符转换成整数类型
-- CAST 转换
mysql> SELECT CAST('1' AS SIGNED);
+---------------------+
| CAST('1' AS SIGNED) |
+---------------------+
| 1 |
+---------------------+
-- CONVERT 转换
mysql> SELECT CONVERT('23',SIGNED);
+----------------------+
| CONVERT('23',SIGNED) |
+----------------------+
| 23 |
+----------------------+
二、数学函数
1、四舍五入:ROUND
SELECT ROUND(col) from tab
// 案例1
SELECT ROUND(1.67) from tab
输出=>
2
//案例2 参数二表示保留小数位数
SELECT ROUND(1.677, 2) from tab
输出=>
1.68
2、向上取整:CEIL
返回 >= 该参数的最小整数
SELECT CEIL(1.02) FROM tab
输出=>
2
SELECT CEIL(-1.02) FROM tab
输出=>
-1
3、向下取整:FLOOR
返回 <= 该参数的最大整数
SELECT FLOOR(1.9) FROM tab
输出=>
1
SELECT FLOOR(-1.9) FROM tab
输出=>
-2
4、TRUNCATE:截断小数点后
保留小数点后几位
SELECT TRUNCATE(1.67, 1) FROM tab
输出=>
1.6
三、日期函数
1、当前日期和时间(%Y-%m-%d %H:%M:%S):NOW
SELECT NOW()
输出=>
2020-12-25 16:05:23
2、当前日期(%Y-%m-%d):CURDATE
SELECT CURDATE()
输出=>
2020-12-25
3、当前时间(%H:%M:%S):CURTIME
SELECT CURTIME()
输出=>
16:10:10
4、年月日:YEAR / MONTH / DAY
SELECT YEAR(NOW()) AS '年', MONTH(NOW()) AS '月', DAY(NOW()) AS '日';
输出=>
+------+------+------+
| 年 | 月 | 日 |
+------+------+------+
| 2021 | 2 | 13 |
+------+------+------+
5、日期格式化:STR_TO_DATE
转化成指定格式
SELECT STR_TO_DATE('2020-5-5 20:08:54',"%Y-%m-%d")
输出=>
2020-05-05
SELECT STR_TO_DATE('2020-5-5 20:08:54',"%Y")
输出=>
NULL 需要注意格式化最少需要三个字符
案例:
SELECT * FROM users WHERE created_time = STR_TO_DATE('12-12 2020','%m-%d %Y')
输出=>
查询成功
SELECT * FROM users WHERE created_time = '12-12 2020'
输出=>
查询成功
SELECT * FROM users WHERE created_time = '12-12 2020'
输出=>
1525-时间戳值不正确:“12-12 2020”,时间:0.000000s
6、日期格式化:DATE_FORMAT
转化成指定字符 STR_TO_DATE 只可以匹配标准的格式
SELECT DATE_FORMAT(created_time,"%Y年") from users
7、两个日期间的相差天数:DATEDIFF
SELECT DATEDIFF('2020-1-2','2020-1-1') AS 相差天数
输出=>
1
四、流程函数
1、IF函数
SELECT IF(条件表达式,true返回,false返回)
SELECT IF(1 > 0,'大','小')
输出=>
大
SELECT IF(created_time > '2022-12-23','这个时间大','这个时间小') FROM users WHERE id = 3
输出=>
这个时间小
SELECT IF(created_time IS NULL ,'没时间','有时间') FROM users
输出=>
没时间
没时间
有时间
-- IF函数结合分组函数
SELECT COUNT(IF(age > 20,TRUE,NULL)) FROM users;
输出=>
+-------------------------------+
| COUNT(IF(age > 20,TRUE,NULL)) |
+-------------------------------+
| 3 |
+-------------------------------+
-- 不使用IF函数统计全表有5行
SELECT COUNT(age) FROM users;
+------------+
| COUNT(age) |
+------------+
| 5 |
+------------+
2、CASE函数
案例1:(switch case)
SELECT dep,
CASE dep
WHEN 10 THEN '测试部门'
WHEN 20 THEN '产品部门'
ELSE '特殊部门'
END AS '部门'
FROM users
输出=>
dep 部门
10 测试部门
20 产品部门
30 特殊部门
案例2:(if else)
SELECT created_time,
CASE
WHEN created_time < '2020-1-1' THEN '以过期'
WHEN created_time > '2020-1-1' THEN '未过期'
ELSE '刚好是2020-1-1'
END AS '部门'
FROM users
输出=>
日期 是否过期
2020-01-01 00:00:00 刚好是2020-1-1
2019-10-11 00:00:00 以过期
2020-12-12 00:00:00 未过期
五、分组函数
1、SUM AVG MAX MIN COUNT
SUM AVG 用于处理数值型字段(忽略null值)
MAX MIN COUNT 可以处理任何类型
SELECT SUM(num) 和,AVG(num) 平均, MAX(num) 最大, MIN(num) 最小, COUNT(num) 总数 FROM users
输出=>
六、数据库有关的函数
// 查看版本
SELECT VERSION()
// 查看当前使用的库
SELECT DATABASE()
// 查看当前用户
SELECT USER()