织梦淘客网站天堂网

1、字符串函数

-- concat
select concat('hello', 'MySQL');-- lower
select lower('Hello');-- upper
select upper('Hello');-- lpad
select lpad('hhh', 5, '-');-- rpad
select rpad('hhh', 5, '-');-- trim
select trim('  Hello MySQL!  ');-- substring
select substring('Hello MySQL', 1, 5);

2、数值函数

-- ceil
select ceil(1.1);-- floor
select floor(1.1);-- mod
select mod(7, 4);-- rand
select rand();-- round
select round(1.254, 1);

3、日期函数

-- curdate
select curdate();-- curtime
select curtime();-- now
select now();-- YEAR
select YEAR(now());-- MONTH
select MONTH(now());-- DAY
select DAY(now());-- DATE_ADD
select DATE_ADD(now(), INTERVAL 10 DAY);-- datediff
select datediff('2021-10-01', '2021-06-01');

4、流程函数

-- if(value, t, f)
select if(true, 'True', 'False');
select if(false, 'True', 'False');-- ifnull(value1, value2)
select ifnull('OK', 'Default');
select ifnull('', 'Default');
select ifnull(null, 'Default');-- case when [val1] then [res1] when [val2] then [res2] ... else [default] end
select id, name,(case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end) as math,(case when english >= 85 then '优秀' when english >= 60 then '及格' else '不及格' end) as english,(case when chinese >= 85 then '优秀' when chinese >= 60 then '及格' else '不及格' end) as chinese
from score;-- case [expr] when [val1] then [res1] when [val2] then [res2] ... else [default] end
selectid,name,(case address when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as address
from tb_emp;