SQL函数

一、SQL函数介绍

函数是 SQL 的一个非常强有力的特性，函数能够用于下面的目的：

• 执行数据计算
• 修改单个数据项
• 操纵输出进行行分组
• 格式化显示的日期和数字
• 转换列数据类型

SQL 函数有输入参数，并且总有一个返回值。

二、SQL函数的分类

2.1 单行函数

单行函数对查询到的每一行都进行运算，并且每行返回一个结果。

常见的函数类型：

• 字符
• 数字
• 日期
• 转换

2.2 多行函数(聚合函数)

多行函数能够操纵成组的行，每个行组给出一个结果，这些函数也被称为组函数。

三、单行函数

3.1 字符函数

①大小写处理函数 

②字符处理函数 

3.2 数字函数

例1：

ROUND(column|expression, n)

函数 ROUND 函数四舍五入列、表达式或者 n 位小数的值。如果第二个参数是 0 或者不写，值被四舍五入为整数。如果第二个参数是 2 ，值被四舍五入为两位小数。如果第二个参数是–1，值被四舍五入到小数点左边两位。

例2：

TRUNCATE(column|expression,n)

函数 TRUNCATE 函数的作用类似于 ROUND 函数。如果第二个参数是 0 或者不写，值被截断为整数。如果第二个参数是 2，值被截断为两位小数。如果第二个参数是–1，值被截断到小数点左边两位。与 ROUND 最大的区别是不会进行四舍五入。

例3：

MOD(m,n)

函数 MOD 函数找出m 除以n的余数。

3.3 日期函数

在MySQL中允许直接使用字符串表示日期，但是要求字符串的日期格式必须为：

‘YYYY-MM-DD HH:MI:SS’ 或者‘YYYY/MM/DD HH:MI:SS’;

3.4 (数据类型)转换函数

①隐式数据类型转换

隐式数据类型转换是指MySQL服务器能够自动地进行类型转换。
如：可以将标准格式的字串日期自动转换为日期类型。

MySQL标准格式字符串日期格式为：‘YYYY-MM-DD HH:MI:SS’ 或 ‘YYYY/MM/DD HH:MI:SS’; 

②显示数据类型转换

对于不是标准格式的字符串，MySQL无法进行隐式转换，那么就需要转换函数进行显示转换。

显示数据类型转换是指需要依赖转换函数来完成相关类型的转换。

• DATE_FORMAT(date,format) 将日期转换成字符串;
• STR_TO_DATE(str,format) 将字符串转换成日期;

format是格式，因为每个人需要的时间格式不一样，但是有一点是共通的，你要告诉MySQL你写的格式是什么样的，那个是时间，那个是日期等......

例1：

向 employees 表中添加一条数据，雇员ID：400，名字：oldlu ， email：oldlu@sxt.cn ，入职时间：2049 年 5 月 5 日，工作部 门：‘IT_PROG’。

insert  into employees(EMPLOYEE_ID,last_name,email,HIRE_DA TE,JOB_ID) values(400,'oldlu','oldlu@sxt.cn', STR_TO_DATE('2049 年 5 月 5 日','%Y 年%m 月%d 日'),'IT_PROG');

告诉MySQL哪个是年，哪个是月，哪个是日

例2：

查询 employees 表中雇员名字为 King 的雇员的入职日期，要求显示格式为 yyyy 年 MM 月 dd 日。

select DATE_FORMAT(hire_date,'%Y 年%m 月%d 日') from employees where last_name = 'King';

3.5 通用函数 

例1：

查询部门编号是50或者80的员工信息，包含他们的名字、薪水、佣金。在income列中，如果有佣金则显示‘SAL+COMM’，无佣金则显 示'SAL'。 

SELECT last_name, salary, commission_pct,  IF(ISNULL(commission_pct),

'SAL','SAL+COMM') income FROM employees WHERE department_id IN (50, 80);

例2：

计算雇员的年报酬，你需要用 12 乘以月薪，再加上它的佣金 (等于年薪乘以佣金百分比)。

SELECT last_name, salary, IFNULL((commission_pct, 0), (salary*12) + (salary*12*IFNULL(commission_pct, 0)) AN_SAL FROM employees;

例3：

查询员工表，显示他们的名字、名字的长度该列名为expr1，姓氏、姓氏的长度该列名为expr2。在result列中，如果名字与姓氏的长度相同则显示空，如果不相同则显示名字长度。

SELECT first_name, LENGTH(first_name) "expr1",  last_name, LENGTH(last_name) "expr2",     NULLIF(LENGTH(first_name), LENGTH(last_name)) result FROM employees; 

例4： 

查询员工表，显示他们的名字，如果 COMMISSION_PCT 值是非空，显示它。如果COMMISSION_PCT 值是空，则显示 SALARY 。 如果 COMMISSION_PCT 和SALARY 值都是空，那么显示 10。在结果中对佣金列升序排序。

SELECT last_name, COALESCE(commission_pct, salary, 10) comm FROM employees ORDER BY commission_pct;

例5： 

查询员工表，如果 JOB_ID 是 IT_PROG，薪水增加 10%；如果 JOB_ID 是 ST_CLERK，薪水增加 15%；如果 JOB_ID 是 SA_REP， 薪水增加 20%。对于所有其他的工作角色，不增加薪水。

SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY" FROM employees;

四、聚合函数(多行函数)

聚合函数也称之为多行函数，组函数或分组函数。聚合函数不象单行函数，聚合函数对行的分组进行操作，对每组给出一个结果。如果在查询中没有指定分组，那么聚合函数则将查询到的结果集视为一组。

使用聚合函数的细节

• DISTINCT 使得函数只考虑不重复的值；
• 所有聚合函数忽略空值。为了用一个值代替空值，用 IFNULL 或 COALESCE 函数。 

---

4.1 AVG(arg)函数

对分组数据做平均值运算。

arg:参数类型只能是数字类型。 

4.2 SUM(arg)函数

对分组数据求和。

arg:参数类型只能是数字类型。

4.3 MIN(arg)函数

求分组中最小数据。

arg:参数类型可以是字符、数字、 日期。

4.4 MAX(arg)函数

求分组中最大数据。

arg:参数类型可以是字符、数字、 日期。

4.5 COUNT( )函数

返回分组中的总行数。

• COUNT(*)：返回表中满足 SELECT 语句的所有列的行数，包括重复行，包括有空值列的行。
• COUNT(expr)：返回在列中的由 expr 指定的非空值的数。
• COUNT(DISTINCT expr)：返回在列中的由 expr 指定的唯一的非空值的数

五、数据分组(GROUP BY)

与聚合函数搭配使用

对查询到的数据进行分组

在没有进行数据分组之前，所有聚合函数是将结果集作为一个大的信息组进行处理。但是，有时，则需要将表的信息划分为较小的组，可以用 GROUP BY 子句实现。

4.1 GROUP BY 子句语法

原则 

• 如果有WHERE子句，那么GROUP BY 子句必须在WHERE的子句后面。
• 在 GROUP BY 子句中必须包含列。

4.2 在分组中使用分组

可以列出多个 GROUP BY 列返回组和子组的摘要结果。可以用 GROUP BY子句中的列的顺序确定结果的默认排序顺序。

下面是图片中的 SELECT 语句中包含一个 GROUP BY 子句时的求值过程：

SELECT 子句指定被返回的列：
− 部门号在 EMPLOYEES 表中
− Job ID 在 EMPLOYEES 表中
− 在 GROUP BY 子句中指定的组中所有薪水的合计
FROM 子句指定数据库必须访问的表：EMPLOYEES 表。

GROUP BY 子句指定你怎样分组行：
− 首先，用部门号分组行。
− 第二，在部门号的分组中再用 job ID 分组行。

如此 SUM 函数被用于每个部门号分组中的所有 job ID 的 salary 列。

4.3 约束分组结果

HAVING 子句

HAVING 子句是对查询出结果集分组后的结果进行过滤。

用 WHERE 子句约束选择的行，用 HAVING 子句约束组。

为了找到每个部门中的最高薪水，而且只显示最高薪水大于 $10,000 的那些部门，可以像下面这样做：