数据库进阶4、常见函数

常见函数

进阶四、常见函数（单行函数）

函数

类似于java中的“方法”，为了解决某个问题，将编写的一系列命令集合封装一起，仅暴露方法名，以供外部调用。

1. 自定义方法（函数）
2. 调用方法（函数）

常见函数：

• 字符函数
  • concat
  • substr
  • length
  • char_length
  • upper/lower
  • trim
  • left/right
  • lpad/rpad
  • instr
  • strcmp
• 数学函数
  • abs
  • ceil
  • mod
  • floor
  • round
  • truncate
• 日期函数
  • now
  • str_to_date
  • date_format
  • curdate
  • curtime
  • datediff
• 流程控制函数

一、字符函数（一）

1 CONCAT 拼接字符

SELECT CONCAT('HELLO',',',last_name) 备注 FROM employees;

2 LENGTH 获取字节长度

SELECT LENGTH('HELLO');
SELECT LENGTH('HELLO,张三');   #UTF-8中，一个汉字3个字节

3 CHAR_LENGTH 获取字符个数

SELECT CHAR_LENGTH('HELLO');
SELECT CHAR_LENGTH('HELLO,张三');

4 SUBSTRING 选取子串 可缩写为SUBSTR

/*
 SUBSTR(str，截取的字符起始索引，截取的字符长度)
 SQL中索引从1开始，与java中从0开始不同
 */
SELECT SUBSTR('法外狂徒张三',1,4); # 法外狂徒
SELECT SUBSTR('法外狂徒张三',5,2); # 张三
SELECT SUBSTR('法外狂徒张三',5);   # 张三SUBSTR(str，截取的字符起始索引) 不写长度，后面的全部截取

5 INSTR 获取字符第一次出现时的索引

SELECT INSTR('法外狂徒张三符合覅挖方喝完了凤凰网张三法外加油你个躺赢','张三'); #  5
SELECT INSTR('法外狂徒张三符合覅挖方喝完了凤凰网张三法外加油你个躺赢','凤凰'); #  15

6 TRIM 去除 字符前后 指定 的字符，默认是空格

SELECT TRIM('   张    三          ') AS A;           #   张    三
SELECT TRIM('X'FROM 'XXXXX张XXXXX三XXXXXXXX') AS A;  #   张XXXXX三

7 LPAD/RPAD 左填充/右填充

用指定字符1 填充 字符2 为要求的 字符长度

SELECT LPAD('张三',6,'你'); # 你你你你张三
SELECT RPAD('张三',6,'你'); # 张三你你你你

8 UPPER/LOWER 变大写/变小写

案例

查询员工姓名，将first_name首字母大写，其他小写，last_name全部大写，并起别名’OUTPUT’

SELECT CONCAT(UPPER (SUBSTR(first_name,1,1)),
LOWER (SUBSTR(first_name,2)),'_',UPPER (SUBSTR(last_name,1)))
AS 'OUTPUT'
FROM employees ;

9 STRCMP 比较两个字符的大小

SELECT STRCMP('ABC','AAA'); #前边比后边大，为 1
SELECT STRCMP('ABC','ACA'); #前边比后边小，为-1
SELECT STRCMP('ABC','ABC'); #前边后边相等，为0

10 LEFT/RIGHT 从左/右截取指定长度的子串

SELECT LEFT('法外狂徒张三',1); # 法
SELECT LEFT('法外狂徒张三',5); # 法外狂徒张
SELECT RIGHT('法外狂徒张三',5);# 外狂徒张三
SELECT RIGHT('法外狂徒张三',2);# 张三

二、数学函数

1 ABS 绝对值

SELECT ABS(-2.3);

2 CEIL 向上取整 返回大于等于该参数的最小整数

SELECT CEIL(1.28);  # 2
SELECT CEIL(-2.3);  # -2

3 FLOOR 向下取整 返回小于等于该参数的最小整数

SELECT FLOOR(1.2); # 1
SELECT FLOOR(-2.34); # -3

4 ROUND 四舍五入 可以选择保留几位小数

SELECT ROUND(1.239); # 1
SELECT ROUND(-3.5634235); # -3
SELECT ROUND(-3.5634235,3); # -3.563

5 TRUNCATE 截断 指定截断到小数点后几位

SELECT TRUNCATE(3.3424,2); # 3.34
SELECT TRUNCATE(3.3424,1); #3.3
SELECT TRUNCATE(3.3424,0); #3

6 MOD 取余

SELECT MOD(-15,4); # -3
SELECT -15%4;      # -3
SELECT -15%-4;     # -3
#  a%b = a-(INT)a/b*b
SELECT MOD(33,3);  # 0

三、日期函数

1 NOW 当前日期及时间

SELECT NOW();

2 CURDATE 当前日期

SELECT CURDATE();

3 CURTIME 当前时间

SELECT CURTIME();

4 DATEDIFF 日期差的天数 前边减去后边的天数

SELECT DATEDIFF('1999-09-09','2020-08-03');

5 DATE_FORMAT 按照字符串fmt格式 格式化日期datetime值

SELECT DATE_FORMAT('1999-09-09','%Y年%m月%d鈤 %H小时%i分钟%s秒') 出生日期;
SELECT DATE_FORMAT(hiredate,'%Y年%m月%d鈤 %H小时%i分钟%s秒') 入职日期
FROM employees;

6 STR_TO_DATE 按指定格式解析字符串为日期类型

SELECT STR_TO_DATE('3/8 1999','%m/%d %Y');# 1999-03-08
SELECT * FROM employees
WHERE hiredate<STR_TO_DATE('3/8 1999','%m/%d %Y');

四、流程控制函数

1 IF函数 类似java中的三元运算符

IF(表达式1，表达式2，表达式3); 如果表达式1成立，则表达式2，否则表达式3.

SELECT IF(101>70,'GOOD','BAD');SELECT IF(commission_pct IS NOT NULL ,salary*12*commission_pct,'0') 奖金 FROM employees;

2 CASE函数

情况1：类似switch语句，可以实现等值判断

CASE(表达式) WHEN 表达式为值1   THEN   结果1 WHEN 表达式为值2   THEN   结果2 ... ELSE 结果n END

案例: 显示员工部门编号 旧工资 新工资,如果部门编号为30，工资2倍；部门编号50，工资3倍；部门编号60，工资4倍；否则不变

SELECT department_id,salary,CASE(department_id)WHEN 30 THEN salary*2WHEN 50 THEN salary*3WHEN 60 THEN salary*4ELSE salaryEND 新工资 FROM employees;

情况2：类似于多重if语句，实现区间判断

CASE WHEN 条件1  THEN 结果1 WHEN 条件2  THEN 结果2 ... ELSE END

案例：工资级别大于20000为A,大于15000为B，大于10000为C，否则为D

SELECT salary,CASEWHEN salary>20000 THEN 'A'WHEN salary>15000 THEN 'B'WHEN salary>10000 THEN 'C'ELSE 'D'END 级别 FROM employees;