最全的MySQL数据库函数：字符串函数、时间日期函数、数值函数详解

CREATE DATABASE fundb;
-- 数学成绩表
CREATE TABLE t_score_math (
  sname varchar(40) ,
  score double 
);

insert  into t_score_math values ('zs',100);
insert  into t_score_math values ('ls',69);
insert  into t_score_math values ('ww',78);
insert  into t_score_math values ('zl',97);
insert  into t_score_math values ('lq',58);

CREATE TABLE t_user (
  id int(11) NOT NULL AUTO_INCREMENT,
  uname varchar(40) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  sex int(11) DEFAULT NULL,
  PRIMARY KEY (id)
);

insert  into t_user values (null,'zs',18,1);
insert  into t_user values (null,'ls',20,0);
insert  into t_user values (null,'ww',23,1);
insert  into t_user values (null,'zl',24,1);
insert  into t_user values (null,'lq',15,0);
insert  into t_user values (null,'hh',12,0);
insert  into t_user values (null,'wzx',60,null);
insert  into t_user values (null,'      lb   ',null,null);

CASE 字段
WHEN 值1 THEN 显示值1
WHEN 值2 THEN 显示值2
ELSE 显示其它值 END [as 字段]

select uname, 
	case sex 
	when 1 then '男'
	when 0 then '女'
	else '其他'
	end
from t_user;

select uname, 
	case sex 
	when 1 then '男'
	when 0 then '女'
	else '其他'
	end as '性别'
from t_user;

CASE WHEN 条件1 THEN 显示值1
WHEN 条件1 THEN 显示值2
ELSE 其他显示值 END [as 字段]

select sname,
	case when score>=90 then '优秀'
	when score>=80 and score<=89 then '良好'
	when score>=70 and score<=79 then '一般'
	when score>=60 and score<=69 then '合格'
	else  '不合格'
	end
from t_score_math;

select sname,
	case when score>=90 then '优秀'
	when score>=80 and score<=89 then '良好'
	when score>=70 and score<=79 then '一般'
	when score>=60 and score<=69 then '合格'
	else  '不合格'
	end as 成绩等级
from t_score_math;

case 
when 条件1 then 值1
when 条件2 then 值2
else 其他值 
end [as 字段]

if(expr1,expr2,expr3)

SELECT uname, IF(sex, 1, 0) FROM t_user;

ifnull(expr1,expr2)

SELECT uname, IFNULL(sex, 1) FROM t_user;

nullif(expr1,expr2)

select uname, nullif(age, 60) from t_user;

if(exp1, exp2, exp3)

ifnull(exp1, exp2)

nullif(exp1, exp2)

SELECT CONCAT('你好,' , uname) FROM t_user;

SELECT CONCAT_WS(',', '你好', uname) FROM t_user;

SELECT UPPER('hello'); -- HELLO

SELECT LOWER('heLLo'); -- hello

SELECT BIN(12); -- 1100

SELECT FORMAT('2345.9767', 2); -- 2,345.98

SELECT LPAD(uname, 10, '0') FROM t_user WHERE uname = 'zs'; -- 00000000zs

-- 表中数据是：'      lb   ', 使用ltrim后是： 'lb   '
SELECT LTRIM(uname) FROM t_user  WHERE id = 8; 

-- 表中数据是：'      lb   ', 使用rtrim后是： '      lb'
SELECT RTRIM(uname) FROM t_user  WHERE id = 8; 

-- 表中数据是：'      lb   ', 使用trim后是： 'lb'
SELECT TRIM(uname) FROM t_user  WHERE id = 8; 

SELECT SUBSTR("hello,world", 2);  -- ello,world

SELECT SUBSTR("hello,world", 2, 4); -- ello

SELECT SUBSTRING_INDEX("hello,world. hello,boy.hello,girl!", ".", 1);  -- 得到第一个.分隔的前面的所有内容
SELECT SUBSTRING_INDEX("hello,world. hello,boy.hello,girl!", ".", 2);  -- 得到第2个.分隔的前面的所有内容
SELECT SUBSTRING_INDEX("hello,world. hello,boy.hello,girl!", ".", 3);  -- 个数超过则返回全部
SELECT SUBSTRING_INDEX("hello,world. hello,boy.hello,girl!", ".", -1); -- 得到从尾部开始计数第一个分隔符.后面的所有内容
SELECT SUBSTRING_INDEX("hello,world. hello,boy.hello,girl!", ".", -2); -- 得到从尾部开始计数第2个分隔符.后面的所有内容
SELECT SUBSTRING_INDEX("hello,world. hello,boy.hello,girl!", ".", -3); -- 个数超过则返回全部

select curdate();
select cruuent_date();

select current_time();
select current_time;

select now();
select sysdate();

select date_format(now(), '%Y-%m-%d %H:%i:%s');

select str_to_date('2019-10-10 14:10:20', '%Y-%m-%d %H:%i:%s');

select date_format(date_add(now(), interval 1 day),  '%Y-%m-%d %H:%i:%s');

select date_sub(curdate(), interval 1 day);

select last_day(now());

select datediff(now(), '1949-10-01');

select abs(-12);

select ceil(-11.2);

select floor(1.6);

select pow(2, 32);

select rand()*100;

select cast('123' as unsigned int);

select convert('123', unsigned int);

select now(), sleep(2), now();