数据库|（四）SQL常见函数

1. 基础

1.1 概念

将一组逻辑语句封装在方法中，对外暴露方法名。

SQL 提供给了我们内置函数，同时，我们也可以自定义函数。

1.2 调用

SELECT 函数名(实参列表) [FROM 表];

1.3 分类

常见函数分为 单行函数 和 分组函数

单行函数	分组函数
字符函数	SUM
日期函数	AVG
数学函数	MAX
其他函数	MIN
流程控制函数	COUNT

2. 字符函数

1. LENGTH
   LENGTH() 获取参数的 字节 个数

2. CONCAT
   CONCAT() 拼接字符串

3. uppser、lower
   示例：姓变大写，名变小写，拼接

   SELECT CONCAT(UPPER(last_name),'_', LOWER(first_name)) AS 姓名 FROM employees;
   

4. SUBSTR、SUBSTRING
   语法：

   • SUBSTR(str, start, len)
   • SUBSTR(str, len)
     注意：索引从1开始

   SELECT SUBSTR('李莫愁爱上了陆展元', 7) AS output;
   SELECT SUBSTR('李莫愁爱上了陆展元', 1, 3) AS output;
   SELECT CONCAT(UPPER(SUBSTR(last_name, 1, 1)),'_', LOWER(SUBSTR(last_name, 2))) 
   AS 姓名 
   FROM employees;
   

5. insert
   返回子串第一次出现的位置，如果找不到则返回 0

   SELECT INSTR('杨殷六侠不悔爱上了殷六侠', '殷八侠') AS output;
   

6. TRIM

   SELECT LENGTH(TRIM(' 张翠山 ')) AS output;#9
   SELECT LENGTH(' 张翠山 ') AS output;#11
   SELECT TRIM('aa' FROM 'aaa张aaaaa翠aaa山aaaaaaaa') as output;
   

7. LPAD
   用指定字符左填充至指定长度

   SELECT LPAD('殷素素', 12, '*') AS output;
   

8. RPAD
   用指定字符右填充至指定长度

   SELECT RPAD('殷素素', 12, '*') AS output;
   

9. REPLACE

   SELECT REPLACE('张无忌爱上了周芷若', '周芷若','赵敏') AS output;
   

3. 数学函数

1. round 四舍五入

   SELECT ROUND(-1.5);
   重载：小数点后保留2位
   SELECT	ROUND(1.567, 2);
   

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

   SELECT CEIL(-1.2) AS 结果;
   

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

   SELECT FLOOR(-1.7) AS 结果;
   

4. TRUNCATE 截断

   SELECT TRUNCATE(1.65,1) AS 结果;
   

5. mod 取余

   SELECT MOD(19,3);
   

4. 日期函数

返回当前系统日期+时间

SELECT NOW();

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

SELECT CURDATE();

获取指定部分，年、月、日、小时、分钟、秒

SELECT YEAR(NOW()) AS 年;
SELECT YEAR(hiredate) FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;

5. 其他函数

SELECT VERSION(); --当前数据库服务器的版本database当前打开的数据库
SELECT USER();  --当前用户
SELECT PASSWORD('字符'); -- 返回该字符的密码形式md5(字符):返回该字符的md5
SELECT VERSION(); 
SELECT DATABASE();

6. 流程控制函数

1. IF 函数

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

2. 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倍
   其他部门，显示的工资为原工资

   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
   END AS 新工资
   FROM employees;
   

3. case 函数使用二：类似多重 if

   CASE 
   WHEN 条件1 THEN 要显示的值1或语句1
   WHEN 条件2 THEN 要显示的值2或语句2
   ...
   ELSE 要显示的值n或语句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;
   

7. 分组函数

7.1 功能

功能：用作统计使用，又称为聚合函数或统计函数或组函数。

函数	含义
SUM	求和
AVG	平均值
MAX	最大值
MIN	最小值
count	计算个数

7.2 简单使用

SELECT SUM(salary) AS 工资和 FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

SELECT SUM(salary) AS 和, ROUND(AVG(salary),2) AS 平均,MAX(salary) 最大, MIN(salary) 最低, COUNT(salary) 个数
FROM employees;

7.3 参数支持类型

1. sum、avg 只支持数值型
2. max、min、count 支持所有类型

# 不支持以下类型，不合法
SELECT SUM(last_name), AVG(last_name) FROM employees;
# max和min支持字符型和日期型
SELECT MAX(last_name), MIN(last_name), MIN(hiredate) FROM employees;
# count 支持所有
SELECT COUNT(commission_pct), COUNT(last_name) FROM employees;

7.4 忽略 null

这些分组函数都忽略null值：

SELECT SUM(commission_pct), AVG(commission_pct), SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;
SELECT MAX(commission_pct),MIN(commission_pct) FROM employees;

7.5 和 DISTINCT 搭配

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary), COUNT(salary) FROM employees;

7.6 count 函数详细介绍

统计行数：

SELECT COUNT(*) FROM employees;

参数为常量值，则也统计行数：

SELECT COUNT(1) FROM employees;

效率：

• MYISAM 存储引擎下，count(*) 的效率最高
• INNODB 存储引擎下，count(*) 和 count(字段) 的效率差不多，比 count(字段) 效率高

7.7 和分组函数一同查询的字段有限制

和分组函数一同查询的字段要求是 group by 后的字段

SELECT AVG(salary),employee_id FROM employees;