mysql之单行函数

单行函数定义

• 操作数据对象
• 接受参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以嵌套
• 参数可以是一列或者一个值

字符串函数

1. 大小写转换

SELECT UPPER('yerennuo'),UCASE('hello'),
LOWER('YRENNUO'),LCASE('HELLO');

2. 字符串连接

SELECT CONCAT('yerennuo ',' is',' a',' student');

SELECT CONCAT(ename,' 的入职日期是 ',hiredate) FROM emp;

3. 字符串替换

SELECT REPLACE('yerennuo','nuo','诺'),
INSERT('yerennuo',1,2,'野'); # index从1开始不是从0

4. 长度

SELECT
	LENGTH( 'yerennuo' ),
	LENGTH( '野人诺' ), 	#字节大小
	CHAR_LENGTH('yerennuo'),
	CHAR_LENGTH('野人诺'); #字符个数

5. 字符串截取

SELECT
	SUBSTR( 'hello world', 2, 5 ),
	SUBSTR('hello world' FROM 3),
	SUBSTR('hello world',4);

SELECT LEFT('hello',3),RIGHT('hello',3),MID('hello',2,3);

6. ASCII(str)

SELECT ASCII('a'),CHAR(65);

7. 填充 RPAD(str,len,padstr)

SELECT
	RPAD( 'yerennuo', 10, '☆' ),
	LPAD( 'yerennuo', 10, '★' ),
	RPAD( 'yerennuo', 10, '★' );

8. 修剪 LTRIM(str) 去除左空格

SELECT
	LTRIM( ' I\'m yerennuo ' ),
	RTRIM( ' I\'m yerennuo ' ),#去除右空格
	TRIM(' I\'m yerennuo ');#前后去除
	
	#TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
SELECT
	TRIM(BOTH'-' FROM '-2024-01-30-');

9. 查找

-- INSTR(str,substr)
SELECT INSTR('thanks, yerennuo!','ank');# 返回3 返回查找到的索引
SELECT INSTR('thanks, yerennuo!','anki'); 
-- LOCATE(substr,str)
SELECT LOCATE('a','thanks, yerennuo!');# 返回3 返回查找到的索引
-- LOCATE(substr,str,pos)
SELECT LOCATE('a','thanks, yerennuo!',4);#返回0
-- POSITION(substr IN str)
SELECT POSITION('a' in 'thanks, yerennuo!');# 返回3

10. 其他

-- REPEAT(str,count)
SELECT REPEAT(5,10); #结果 5555555555

-- SPACE(N)
SELECT SPACE(3); 
SELECT CONCAT(ename,SPACE(5),hiredate)FROM emp;

-- STRCMP(expr1,expr2)
SELECT STRCMP('abc','def');# 结果 -1

-- ELT(N,str1,str2,str3,...)
SELECT ELT(2,'张三','李四','王五'); #结果 李四
-- FIELD(str,str1,str2,str3,...)
SELECT FIELD('张三','张三','李四','王五');# 结果 1

-- FIND_IN_SET(str,strlist)
SELECT FIND_IN_SET('李四','张三,李四,王五'); #结果 2

• 练习

-- 案例 1 把员工姓名首字母大写,其他小写
	SELECT
		CONCAT(
			UPPER(
			LEFT ( ename, 1 )),
			LOWER(
			SUBSTR( ename, 2 ))) AS 姓名 
	FROM
	emp;
	
	-- 案例2 查出姓名长度是5的所有员工信息
	SELECT * FROM emp
	WHERE CHAR_LENGTH(ename) = 5;

日期、时间

1. 获取

	SELECT CURRENT_DATE(),CURRENT_DATE,CURDATE(),
		CURTIME(),CURRENT_TIME(),CURRENT_TIME,
		NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),
		SYSDATE()

2. utc时间

SELECT UTC_DATE(),UTC_TIME();

3. 时间转换

	SELECT UNIX_TIMESTAMP(),
		UNIX_TIMESTAMP('2024/01/31'),
		FROM_UNIXTIME(1706630400);

4. 从日期中提取想要的信息

	SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),
			HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())

	SELECT MONTHNAME(NOW()); #结果 January
	
	SELECT MONTHNAME(NOW()),
	DAYNAME(NOW()),
	WEEKDAY(NOW())+1,#星期2是1
	QUARTER(NOW()),
	WEEK(NOW())+1,WEEKOFYEAR(NOW()); #一月之中第几周
	
	SELECT EXTRACT(SECOND FROM CURRENT_TIME), #秒
	EXTRACT(SECOND_MICROSECOND FROM CURRENT_TIME) #毫秒 

5. 时间日期计算

 SELECT DATE_ADD(NOW(),INTERVAL 1 DAY),# 2024-02-01 16:17:04
 ADDDATE(NOW(),INTERVAL 1 YEAR),# 2025-01-31 16:17:04
 DATE_ADD(NOW(),INTERVAL '1,2' YEAR_MONTH);# 2025-03-31 16:17:04
 
 SELECT NOW(),ADDTIME(NOW(),10000);# 100表示1分钟 10000表示1小时
 
 
 SELECT DATEDIFF(NOW(),'2024-1-29'), # 2
 TIMEDIFF(CURRENT_TIME,'16:26:33'); # 00:02:57
 
 -- TO_DAYS(date) 返回日期date距离0000年1月1日的天数
SELECT TO_DAYS(NOW())	,
FROM_DAYS(739281); # 0000年1月1日,过了date天的日期

-- PERIOD_ADD(P,N) P年月，N月数
SELECT PERIOD_ADD(201702,50); # 2017年2月加50个月 结果 202104

-- MAKEDATE(year,dayofyear)
SELECT MAKEDATE(2024,365); # 2024-12-30

6. 格式化与解析

SELECT DATE_FORMAT(NOW(),'日期%y/%m/%d时间%h:%i:%s'); #输出结果 日期24/01/31时间04:45:05

格式符	说明	格式符	说明
%Y	四位数字表示年份	%y	两位数字表示年份
%M	月名表示月份(January…)	%m	两位数字表示月份(01,02,03…)
%b	缩写的月名(Jan.)	%c	数字表示月份
%D	英文后缀表示月中的天数(1st,2nd…)	%d	两位数字表示月中天数(01,02,03…)
%e	数字形式表示月中的天数（1，2，3…）
%H	两位数字表示小时，24小时制（01,02,…）	%h	两位数字表示小时，12小时制(01,02,03…)
%k	数字形式小时，24小时制（1，2，3…）	%l	数字表示小时，12小时制(1,2,3…)
%i	两位数字表示分钟(00,01,02)	%S和%s	两位数字表示秒(00,01,02…)
%W	一周中的星期名称(Sunday,…)	%a	一周中星期的缩写(Sun,Mon…)
%w	以数字表示周中的天数(0=Sunday,1=Monday,…)
%j	以3位数字表示年中的天数(001,002,…)
%u	以数字表示月中的第几周，Monday是周中第一天	%U	以数字表示年中的第几周,(1,2,3…)Sunday是周中的第一天
%T	24小时制	%r	12小时制
%p	AM或PM	%%	表示%

• 练习，查找出入职日期大于1981年6月的员工

SELECT *FROM emp
WHERE DATE_FORMAT(hiredate,'%Y-%m')> '1981-06';

7. 获取格式 GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})

SELECT GET_FORMAT(DATE, 'INTERNAL'), # %Y%m%d
GET_FORMAT(DATE,'USA'); # %m.%d.%Y

8. 按照格式把字符串解析为日期 STR_TO_DATE(str,format)

SELECT STR_TO_DATE('2024,1-31','%Y,%m-%d');

数值函数

-- ROUND(X,D) 保留D位小数，四舍五入
SELECT ROUND(251.55,1)；
-- TRUNCATE(X,D) 保留D位小数，后面截断
SELECT TRUNCATE(251.55,1);
-- CEIL(X),CEILING(X) 对小数向上取整
SELECT CEIL(3.14),CEILING(3.14); #结果都是4
-- FLOOR(X)对小数向下取整
SELECT FLOOR(3.14);#3
-- RAND()随机数(0到1之间的数，包括小数）
SELECT RAND();

条件判断函数

1. IF

SELECT IF(TRUE,'is true','is false');

• 练习，把奖金为null的换成0

SELECT IF(comm is NULL,0,comm) 奖金 FROM emp;
-- 判断为空 IFNULL(expr1,expr2)
SELECT IFNULL(comm,0) 奖金 FROM emp;

2. CASE

	SELECT
	CASE
			521
			WHEN 1 THEN '1' 
			WHEN 520 THEN
			'520' ELSE '521' 
		END ;

SELECT ename,DATE_FORMAT(hiredate,'%m') AS 'm',
	CASE  MONTH(hiredate)
	WHEN   1 THEN '1月'
	WHEN   2 THEN '2月'
	WHEN   5 THEN '5月'
	ELSE '其他月'
	END AS 入职月份
FROM emp
ORDER BY m;