mysql常用函数总结(完整实用)

• 表结构

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `s_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `s_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `s_birth` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `s_sex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci;

• 表数据

INSERT INTO `student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('01', '赵雷', '1990-01-01', '男');
INSERT INTO `student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('02', '钱电', '1990-12-21', '男');
INSERT INTO `student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('03', '孙风', '1990-05-20', '男');
INSERT INTO `student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('04', '李云', '1990-08-06', '男');
INSERT INTO `student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('05', '周梅', '1991-12-01', '女');
INSERT INTO `student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('06', '吴兰', '1992-03-01', '女');
INSERT INTO `student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('07', '郑竹', '1989-07-01', '女');
INSERT INTO `student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('08', '王菊', '1990-01-20', '女');

字符串函数

1. length 获取参数值的字节个数（utf-8一个汉字代表3个字节,gbk为2个字节）

select  length(s_name) as 字节个数 from student t where s_id='01';
-- 返回键结果 6

2. concat 拼接字符串

select concat(s_name,'_',s_birth) from student t where s_id='01';

3. UPPER、LOWER大小写转换

SELECT UPPER('zou') 大写转换;
-- 返回结果 ZOU
SELECT LOWER('Zou') 小写转换;
-- 返回结果 zou

4. substr、substring字符串截取

-- 一个参数，索引从1开始，截取索引到索引后所有字符串
select substr(s_birth,2) from student t where s_id='01';
-- 两个参数 1.指定所谓，2截取长度
select substr(s_birth,2,3) from student t where s_id='01';

5. instr 返回子串第一次出现的索引，如果找不到返回0

select instr(s_name,'雷') from student where s_id='01';
-- 返回结果 2
select instr(s_name,'魏') 第一次出现的索引 from student  where s_id='01';
-- 返回结果 0

6. trim 删除指定首位字符或去空格

--去除首尾空格
SELECT TRIM('  haha   ');
--  返回结果： haha
SELECT TRIM(LEADING 'z' FROM 'zzwhlovezz');   --删除指定的首字符 z
-- 返回结果 whlovezz
SELECT TRIM(BOTH 'z' FROM 'zzwhlovezz');      --删除指定的首尾字符 z
-- 返回结果 whlove
SELECT TRIM(TRAILING 'z' FROM 'zzwhlovezz');  --删除指定的尾字符 z
-- 返回结果 zzwhlove

7. lpad 用指定的字符实现左填充指定长度

SELECT LPAD(s_name,5,'*') from student t where s_id='01';
-- 返回结果 ***赵雷

8. rpad 用指定的字符实现右填充指定长度

SELECT RPAD(s_name,5,'*') from student t where s_id='01';
-- 返回结果  赵雷***

9. replace 替换指定字符串

select REPLACE(s_name,'赵雷','阿离') from student t where s_id='01';
--返回结果 阿离

数字函数

1. round 四舍五入

SELECT ROUND(-1.67);
-- 返回结果 -2
SELECT ROUND(1.675,2);
-- 返回结果 1.58

2. CEIL 向上取整

SELECT CEIL(-1.23);
-- 返回结果 -1
SELECT CEIL(1.23);
-- 返回结果 2

3. FLOOR 向下取整

SELECT FLOOR (-1.23);
-- 返回结果 -2
SELECT FLOOR (1.23);
-- 返回结果 1

4. TRUNCATE 保留几位小数点

SELECT TRUNCATE (1.23,1);
-- 返回结果 1.2

5. mod 或 % 取余

SELECT MOD(10,3);
SELECT 10%3;
-- 返回结果 1

日期函数

1. NOW() 返回当前系统日期+时间

SELECT NOW();
-- 返回结果 2019-09-28 22:48:59

2. curdate 返回当前系统日期，不包含时间

SELECT CURDATE();
-- 返回结果 2019-09-28

3. curtime 返回当前时间，不包含日期

SELECT CURTIME();
-- 返回结果 22:50:11

4. 获取年、月、日

SELECT YEAR(NOW()) 年;
-- -- 返回结果 2019
SELECT MONTH(NOW()) 月;
-- 返回结果 9
SELECT MONTHNAME(NOW()) 月;
-- 返回结果 September
SELECT DAY(NOW()) 日;
-- 返回结果 28

5. str_to_date 字符串转日期

SELECT STR_TO_DATE('2019-9-29','%Y-%m-%d') AS 日期;
-- 返回结果 2019-09-29

6. date_format 日期转字符串

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS 字符串;
-- SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS 字符串;
-- 返回结果 2019年09月28日

7. DATEDIFF 计算两个时间相差多少天

SELECT DATEDIFF('2020-01-25','2019-09-28');    
-- 返回结果 119

8. TIMESTAMPDIFF 就算两个时间相差多少天或小时月等

-- 
-- 第一个参数可以是FRAC_SECOND、SECOND、 MINUTE、 HOUR、 DAY、 WEEK、 MONTH、 QUARTER或 YEAR
SELECT TIMESTAMPDIFF(DAY,'2019-09-28','2020-01-25'); 
-- 返回结果 119

流程控制函数

1. if

格式 if(条件，满足显示，不满足显示)

select if(s_sex='男','男人好难','女人也难') 谁难 from student

2. case when

select s_name, case s_sex
when '男' then '男人好难' 
when '女' then '女人好难'
else '不男不女肯定不难'
end as 谁难
from student
-- 两种写法大同小异
select s_name,case
when s_sex='男' then  '男人好难' 
when s_sex='女' then  '女人好难'
else '不男不女肯定不难'
end as 谁难
from student

其他函数

1. 查看数据库版本

SELECT VERSION();

2. 查看当前使用数据库名

SELECT DATABASE();

3. 查看某个表的表结构

desc 表名;

4. 查看表的索引

show index from  表名

5. 查看系统当前隔离级别

select @@global.transaction_isolation;