-- 	绝对值	abs

select abs (-1);
SELECT ABS (1);

-- 	向上取整	ceil和ceiling

select ceil (1.4);
select ceil(-1.4);
SELECT CEIL (1.6);
SELECT CEIL(-1.6);
select ceiling (1.4);
select Ceiling(-1.4);
SELECT CEILING (1.6);
SELECT CEILING(-1.6);

-- 	向下取整	floor

select floor (1.4);
select floor(-1.4);
SELECT FLOOR (1.6);
SELECT FLOOR(-1.6);

-- 	随机数	rand

select rand();
select rand(1);
select rand(1.4);
SELECT RAND(2);
SELECT RAND(2.4);
SELECT RAND(-2.4)

-- 	四舍五入	round

select round(1.4);
select round(-1.4);
SELECT ROUND(1.6);
SELECT ROUND(-1.6);

-- 长度		LENGTH   CHAR_LENGTH

select length (2);
select char_length('软件技术');
SELECT LENGTH(2.4);
SELECT CHAR_LENGTH('软件工程学院');

-- 字符串替换	 REPLACE('1','2','3')在1中，2被3替代  
-- INSERT	('1','2','3','4')在1中，1是在第几位被3替代，2是从几开始

select replace('121212','2','1');
select replace('校园','校','花');
SELECT REPLACE('121212','1','2');
SELECT REPLACE('校园','校','花');

select insert('123456','2','3','3');
SELECT INSERT('111111','4','1','3');

-- 截取		LEFT(从几开始)  RIGHT(最后几个数)  SUBSTRING(从第几个数开始数几个数)

select left('123456','2');
SELECT LEFT('111111','3');
select Right('123456','2');
SELECT RIGHT('111111','3');
select substring('123456'from 2 for 3);
SELECT SUBSTRING('ABCDEF'FROM 3 FOR 2);

-- 拼接		CONCAT

select concat('软','件','技','术');
SELECT CONCAT('1314','520');

-- 大小写		LOWER  UPPER

select lower('abcdEFG');
SELECT LOWER('ASDF');
select upper('China');
SELECT UPPER('abcd');

-- 逆序					REVERSE

select reverse('123456');
SELECT REVERSE('ABCDEFG');

-- 去空格					TRIM			LTRIM			RTRIM

select trim('   123   ');
SELECT TRIM('   ABC   ');
select ltrim('   123   ');
SELECT LTRIM('   ABC   ');
select rtrim('   123   ');
SELECT RTRIM('   ABC   ');

-- 获取当前时间	now()

select now();
SELECT NOW();

-- 	时间戳			FROM_UNIXTIME		UNIX_TIMESTAMP

select UNIX_TIMESTAMP(now());
select FROM_UNIXTIME(123456789);
	
-- 流程控制	IF	CASE	IFNULL

select *,if (money>=0,'正常','异常')资金状态 from user;

select *,(
	CASE
			when money>=0
			then '正常'
			else '异常'
	END
)资金状态 from user;