MySQL常见函数

概述

1. 概念：类似于java方法，将一组逻辑语句封装在方法体中，对外暴露方法名
2. 好处：
   • 隐藏了实现细节
   • 提高代码的重用性
3. 调用：select 函数名(实参列表)【from 表】
4. 分类：
   1. 单行函数
      如：concat、lenght、ifnull等、
   2. 分组函数
      功能：做统计使用

字符函数

1. length 获取参数值的字节个数

SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha');
// 英文字符占一个字节，中文字符占三个字节

2. concat 拼接字符串

SELECT CONCAT(`last_name`,'_',`first_name`) 姓名 FROM `employees`;

3. upper lower：大小转换

// 案例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(`last_name`), LOWER(`first_name`)) FROM `employees`;

4. substr、substring
   注：索引从1开始

   1. 截取从指定索引处后面的所有字符

      SELECT SUBSTR('一二三', 2) out_put;
      

   2. 截取从指定索引处指定字符长度的字符

      SELECT SUBSTR('一二三',1,2) out_put;
      

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

SELECT INSTR('一二三四五', '三') out_put;

6. trim：取出前后指定字符，默认为空格

SELECT LENGTH(TRIM('    一     ')) out_put;

SELECT TRIM('a' FROM 'aaaaaaa一aaa二aaa三aaaaaaaa') out_put;

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

SELECT LPAD('一二三',10,'*') out_put;

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

SELECT RPAD('一二三',10,'*') out_put;

9. replace 替换

SELECT REPLACE('一二三','一','四');

数学函数

1. round 四舍五入

SELECT ROUND(-4.56);
SELECT ROUND(1.567, 2); #保留小数点后两位

2. ceil向上取整,返回>=该参数的最小整数

SELECT CEIL(-1.002);

3. floor 向下取整，返回<=该参数的最大整数

SELECT FLOOR(9.99);

4. truncate 截断

// 截断到小数点后2位
SELECT TRUNCATE(1.6999, 2);  #输出1.69

5. mod取余

// mod(a,b): a-a/b*b
SELECT MOD(10, -3);
SELECT 10%3;

日期函数

1. now 返回当前系统日期+时间

SELECT NOW();

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

SELECT CURDATE();

3. 获取指定的部分：年、月、日、小时、分组、秒

SELECT YEAR(NOW()) 年;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月名;

4. str_to_date 将日期格式的字符转换成指定格式的日期
   

SELECT STR_TO_DATE('9-13-1999', '%m-%d-%Y');

5. date_format：将日期转化成字符

SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日');

其它函数

1. verson() 查看数据库的版本号

SELECT VERSION();

2. database() 查看当前的库

SELECT DATABASE();

3. user 代表当前用户

SELECT USER();

流程控制函数

1. if函数：if else 效果

SELECT IF(10>5, '大', '小');  // 大
SELECT `last_name`,`commission_pct`, IF(`commission_pct` IS NULL, '没奖金', '有奖金')
FROM `employees`;

3. case函数

   • 使用一：switch case 的效果

     • 语法：
       case 要判断的字段或表达式
       when 常量1 then 要显示的值1或语句1;
       when 常量2 then 要显示的值2或语句2;
       …
       else 要显示的值n或语句n;
       end

     • 案例：查询员工的工资，要求：
       部门号=30，显示工资为1.1倍
       部门号=40，显示工资为1.2倍
       部门号=50，显示工资为1.3倍
       其他部门，显示工资为1.4倍

       SELECT `salary` 原始工资,`department_id`,
       CASE`department_id`
       WHEN 30 THEN `salary`*1.1
       WHEN 40 THEN `salary`*1.2
       WHEN 50 THEN `salary`*1.3
       ELSE `salary`*1.4
       END AS 新工资
       FROM `employees`
       

   • 使用二：类似于 多重if

     • 语法：
       case
       when 条件1 then 要显示的值1或语句1
       when 条件2 then 要显示的值2或语句2
       …
       else 要显示的值n或语句
       end

     • 案例：
       如果工资>20000,显示A级别
       如果工资>15000,显示B级别
       如果工资>10000,显示C级别
       否则，显示D级别

       SELECT `salary`,
       CASE
       WHEN `salary`>20000 THEN 'A'
       WHEN `salary`>15000 THEN 'B'
       WHEN `salary`>10000 THEN 'C'
       ELSE 'D' 
       END AS 工资级别
       FROM `employees`