-- 绝对值 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;