MySQL函数解读
对MySQL其中一些函数进一步解读
一、字符串函数
1.1.instr()
INSTR(str,substr)一共有两个参数
str被查询字符，substr查询字符，查询时下标从1开始记，只查询第一次出现的地方，为查询到显示为0
一般用法
SELECT INSTR(str,substr) FROM dual;
-- dual亚元表，系统表，可以作为测试表使用
-- 查询'hahaheha'中是否有'eh',有返回6
SELECT INSTR('hahaheha','eh') FROM dual;
详细用法及特殊用例如下：
先创建表
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`xi` int NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
);
INSERT INTO `employee` VALUES (1001, '小红', 789123);
INSERT INTO `employee` VALUES (1002, '小白', 963258);
INSERT INTO `employee` VALUES (1003, '小绿', 987456);
INSERT INTO `employee` VALUES (1004, '小明', 753951);
示例
-- 查询xi中含有74的数据
SELECT * FROM employee WHERE INSTR(xi,'74')>0;
-- 相当于左右like查询
SELECT * FROM employee WHERE xi LIKE '%74%';
-- 相当于in的用法
SELECT * FROM employee WHERE INSTR('小白,小绿',name) > 0;
SELECT * FROM employee WHERE name IN('小白','小绿');
1.2.去除空格
LTRIM、RTRIM、TRIM去左空格，去有空格，去两边空格
SELECT LTRIM(字段) FROM DUAL;
SELECT LTRIM(' 芊嵛 ') FROM DUAL;
SELECT RTRIM(' 芊嵛 ') FROM DUAL;
SELECT TRIM(' 芊嵛 ') FROM DUAL;
结果：
-- 未了能看清空格我加了``
`芊嵛 `
` 芊嵛`
芊嵛
1.3拼接字符串
-- 将多个字段串成一个大的字符串
concat(字段1,字段2,字段3):
1.4转换大小写
-- 将字段的英文字符都转成小写 等价 LCASE()
lower(字段)
-- 将字段的英文字符都转成大写 等价 UCASE()
upper(字段)
1.5截取字符串
-- 从大的字符串截取一部分作为一个子串
substring(字段,起始位置,长度)
substr()同 substring
二、日期时间函数
2.1显示当前日期
-- 显示当前日期
SELECT CURRENT_DATE() FROM DUAL;
| CURRENT_DATE() |
|---|
| 2023-10-23 |
2.2显示当前时间
-- 显示当前时间
SELECT CURRENT_TIME() FROM DUAL;
| CURRENT_TIME() |
|---|
| 20:23:30 |
2.3显示当前日期和时间
-- 显示当前日期和时间
SELECT CURRENT_TIMESTAMP() FROM DUAL;
-- 显示当前日期和时间
SELECT NOW() FROM DUAL;
| CURRENT_TIMESTAMP() |
|---|
| 2023-10-23 20:23:30 |
| NOW() |
|---|
| 2023-10-23 20:29:13 |
2.4将已知日期和时间转换为日期
SELECT DATE('2023-10-23 20:30:15') FROM DUAL;
| DATE('2023-10-23 20:30:15') |
|---|
| 2023-10-23 |
2.5时间加/减时间
-- 加10分钟,后面的minute可以换成year那样就能加年了
SELECT DATE_ADD('2023-10-23 20:35:10' ,INTERVAL 10 MINUTE) FROM DUAL;
-- 减的话把DATE_ADD换成DATE_SUB
2.6相差多少天
-- 前减后
SELECT DATEDIFF('2005-11-11','2003-01-02') FROM DUAL;
-- 1044天
SELECT DATEDIFF('2005-11-11','2005-11-10') FROM DUAL;
-- 1天
2.7相差多少时间
-- 前减后
SELECT TIMEDIFF('20:10:03','19:11:10') FROM DUAL;
-- 00:58:53
2.8计算年龄
TIMESTAMPDIFF(YEAR, birth, NOW())
-- 能得出岁数
2.9返回1970-1-1到现在的毫秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;
2.10把UNIX_TIMESTAMP()函数转换成具体日期格式
SELECT FROM_UNIXTIME(1698065850,'%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1698065850,'%Y-%m-%d %H:%i%s') FROM DUAL;
| FROM_UNIXTIME(1698065850,'%Y-%m-%d') | FROM_UNIXTIME(1698065850,'%Y-%m-%d %H:%i%s') |
|---|---|
| 2023-10-23 | 2023-10-23 20:5730 |
三、加密函数和系统函数
3.1查询用户
-- USER()
SELECT USER() FROM DUAL;
-- 返回用户@IP地址，如上述语句运行得到 root@localhost
3.2查看当前所用的数据库
-- DATABASE();
SELECT DATABASE();
3.2为字符串算出一个MD5 32的字符串，常用(用户密码)加密
-- a8105204604a0b11e916f3879aae3b0b
SELECT MD5('密码') FROM DUAL;
-- 长度为32
SELECT LENGTH(MD5('密码')) FROM DUAL;
四、流程控制函数
4.1.IF函数
类似于三元运算符
-- expr1 为判断条件TRUE为真 false 为假
-- expr2 当expr1为真时选择
-- expr3 当expr1为假时选择
-- 固定有三个参数不能省略
IF(expr1,expr2,expr3)
-- 北京
SELECT IF(TRUE,'北京','上海') FROM DUAL;
-- 上海
SELECT IF(FALSE,'北京','上海') FROM DUAL;
4.2.IFNULL函数
用于将NULL转换为有值
-- 0.0
SELECT IFNULL(NULL,0.0) FROM DUAL;
-- 0.0
SELECT IFNULL(0.0,'芊嵛') FROM DUAL;
这个函数可以用上述IF函数来代替
IF(字段 IS NULL, 0.0, 字段)
4.3.CASE函数
相当于多分支语句
-- 当when_value1返回statement_list1
-- 当when_value2返回statement_list2
-- 否则返回statement_list3
CASE
WHEN when_value1 THEN statement_list1
WHEN when_value2 THEN statement_list2
ELSE
statement_list3
END
-- 返回第一条
SELECT CASE
WHEN TRUE THEN '第一条'
WHEN FALSE THEN '第二条'
ELSE '第二条'
END FROM DUAL;
五、数学相关函数
5.1绝对值
ABS(num)
5.2十进制转二进制
BIN(num)
5.3向上取整 CEILING(num) >= num
CEILING(num)
5.4向下取整 FLOOR(num) <= num
FLOOR(num)
5.5进制转换
CONV(num,from_base,to_base)
-- 例如 CONV(8,10,2) 数字8十进制转换成2进制 1000
5.6保留固定小数位
FORMAT(num,小数位数)
-- 10.33
SELECT FORMAT(10.333,2) FROM DUAL;
-- 10.00
SELECT FORMAT(10,2) FROM DUAL;
5.7转十六进制
HEX(num)
-- A
SELECT HEX(10);
5.8最小值
LEAST(value1,value2,...)
-- 7
SELECT LEAST(10,8,11,7,15);
5.9求余
SELECT MOD(N,M)
-- 1
SELECT MOD(10,3);
5.10随机数
-- 范围0<=v<=1.0
SELECT RAND()
六、统计函数
6.1计数函数
COUNT()
-- COUNT(*) 可以统计NULL值
-- COUNT(字段) 不会统计NULL值
6.2求和函数
SUM()
6.3最大/最小值
MAX()
MIN()
6.4求平均值
AVG()
-- 注意NULL判断
