进阶四：常见函数（单行函数使用）

目前阶段学习调用函数

字符函数

​ concat
​ substr
​ length
​ char_length
​ upper
​ lower
​ trim
​ left
​ right
​ lpad
​ rpad
​ instr
​ strcmp

#一、字符函数

1、CONCAT 拼接字符

SELECT CONCAT(‘hello,’,first_name,last_name) 备注 FROM employees;

2、LENGTH 获取字节长度

SELECT LENGTH(‘hello,郭襄’);

3、CHAR_LENGTH 获取字符个数

SELECT CHAR_LENGTH(‘hello,郭襄’);

4、SUBSTRING 截取子串

/*

注意：起始索引从1开始！！！
substr(str,起始索引，截取的字符长度)
substr(str,起始索引)
*/
SELECT SUBSTR(‘张三丰爱上了郭襄’,1,3);
SELECT SUBSTR(‘张三丰爱上了郭襄’,7);

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

SELECT INSTR(‘三打白骨精aaa白骨精bb白骨精’,‘白骨精’);

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

SELECT TRIM(’ 虚 竹 ') AS a;
SELECT TRIM(‘x’ FROM ‘xxxxxx虚xxx竹xxxxxxxxxxxxxxxxxx’) AS a;

7、LPAD/RPAD 左填充/右填充

SELECT LPAD(‘木婉清’,10,‘a’);
SELECT RPAD(‘木婉清’,10,‘a’);

8、UPPER/LOWER 变大写/变小写

#案例：查询员工表的姓名，要求格式：姓首字符大写，其他字符小写，名所有字符大写，且姓和名之间用_分割，最后起别名“OUTPUT”

SELECT UPPER(SUBSTR(first_name,1,1)),first_name FROM employees;
SELECT LOWER(SUBSTR(first_name,2)),first_name FROM employees;
SELECT UPPER(last_name) FROM employees;

SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)),’_’,UPPER(last_name)) “OUTPUT”
FROM employees;

9、STRCMP 比较两个字符大小

SELECT STRCMP(‘aec’,‘aec’);

10、LEFT/RIGHT 截取子串（从左从右边开始截取）

SELECT LEFT(‘鸠摩智’,1);
SELECT RIGHT(‘鸠摩智’,1);

数学函数

​ abs
​ ceil
​ floor
​ round
​ truncate
​ mod

1、ABS 绝对值

SELECT ABS(-2.4);

2、CEIL 向上取整 返回>=该参数的最小整数

SELECT CEIL(-1.09);
SELECT CEIL(0.09);
SELECT CEIL(1.00);

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

SELECT FLOOR(-1.09);
SELECT FLOOR(0.09);
SELECT FLOOR(1.00);

4、ROUND 四舍五入（参数，指定保留几位小数）

SELECT ROUND(1.8712345);
SELECT ROUND(1.8712345,2);

5、TRUNCATE 截断 （指定截断为即为小数）

SELECT TRUNCATE(1.8712345,1);

6、MOD 取余（结果的正负，取决于被除数的符号）

SELECT MOD(-10,3);
a%b = a-(INT)a/bb
-10%3 = -10 - (-10)/33 = -1

SELECT -10%3;
SELECT 10%3;
SELECT -10%-3;
SELECT 10%-3;

流程控制函数

1、IF函数（有点像三元表达式）

SELECT IF(100>9,‘好’,‘坏’);

#需求：如果有奖金，则显示最终奖金，如果没有，则显示0
SELECT IF(commission_pct IS NULL,0,salary12commission_pct) 奖金,commission_pct
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 salary2
WHEN 50 THEN salary3
WHEN 60 THEN salary*4
ELSE salary
END newSalary
FROM employees;

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

CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果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 a
FROM employees;