【Mysql】一、Mysql基础篇 --- 函数

一、字符函数

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

1. 字符集转换
   SELECT CONVERT(col_num USING utf8)
2. 类型转换
   CAST：CAST(value AS type);
   CONVERT：CONVERT(value, type);

可转换的类型

1. 二进制，同带binary前缀的效果 : BINARY
2. 字符型，可带参数 : CHAR()
3. 日期 : DATE
4. 时间: TIME
5. 日期时间型 : DATETIME
6. 浮点数 : DECIMAL
7. 整数 : SIGNED
8. 无符号整数 : 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()