第六章 处理函数

6.1数据处理函数（单行处理函数）

注：数据处理函数是该数据本身特有的，有些函数可能在其它数据库不起作用。

6.1.1 lower函数

举例：查询员工姓名，将员工姓名全部转换成小写

SQL语句：select lower(ename) from employee;

6.1.2 upper函数

举例：查询员工姓名，将员工姓名全部转换成大写

SQL语句：select upper(ename) from employee;

6.1.3 substr函数

举例：查询员工姓名中第二个字母为A的所有员工

SQL语句：select ename from employee where substr(ename,2,1)='A';

联想模糊查询：select ename from employeewhere ename like '_A%';

6.1.4 length函数

举例：取得员工姓名长度

SQL语句：select ename,length(ename)as LENGTH from employee;

6.1.5 trim函数

举例：查询所有开发人员的姓名

SQL语句：select ename,job from employee where job=trim(' 开发人员  ');

6.1.6 round函数

用法：round(要四舍五入的数字,四舍五入到哪一位)，默认保留整数位

举例1：保留整数位或不保留小数位

SQL语句：select round(123.45);或者 select round(1123.45);

举例2：保留一位小数位

SQL语句：select round(123.45,1);

举例2：保留两位小数位

SQL语句：select round(123.456,2);

举例2：个位数四舍五入

SQL语句：select round(127.4,-1);

6.1.7 rand函数

举例1：生成一个0到1之间的随机数

SQL语句：select rand();

举例2：生成一个0到100之间的随机整数

SQL语句：select round(rand()*100);

6.1.8 ifnull函数

用法：ifnull(字段名称,将要替换)

结论：在数据库中，有Null参与数学运算的结果一定为Null；为了防止计算结果出现Null，建议先使用ifnull函数预先处理。

举例：查询员工的奖金，如果为Null设置为0；

SQL语句：select ename,ifnull(permium,0)permium from employee;

6.1.9 case…when…then…else…end

举例：根据工作岗位涨工资，当为项目经理时，工资涨2000，当为项目组长时，工资涨1000，其它岗位工资不变。

SQL语句：select ename,sal,job,(

case job

when '项目组长' then sal+2000

when '开发人员' then sal+1000

else sal

end

)as newsal from employee;

6.1.10 str_to_date函数

作用：将‘日期字符串’转换为‘日期类型’数据

执行结果：DATE类型

用法：str_to_date(‘日期字符串’,‘日期格式’)

MySQL日期格式：

%Y：代表4位的年份

%y：代表2位的年份

%m：代表月，格式（01 … … 12）

%c：代表月，格式（1 … … 12）

%d：代表日

%H：代表小时，格式（00 … … 23）

%h：代表小时，格式（01 … … 12）

%i：代表分种，格式（00 … … 59）

%r：代表 时间,格式为12 小时(hh:mm:ss [AP]M)  

%T：代表 时间,格式为24 小时(hh:mm:ss) 

%S：代表 秒,格式为(00……59)  

%s：代表 秒,格式为(00……59) 

联想知识点：java中的日期格式

yyyy 年

MM 月

dd 日

HH 时

mm 分

ss 秒

SSS 毫秒

Java中将字符串转换为日期类型：

SimpleDateFormat sdf = new

SimpleDateFormat(“yyyy-MM-dd HH:mm:ss SSS”);

Date date = sdf.parse(“2008-08-08 20:00:00:000”);

举例1：查询出2012-08-25入职的员工

SQL语句：select ename,hiredate from employeewhere hiredate='2012-08-25';

执行成功原因：

输入的日期字符串格式与MySQL默认日期格式相同，MySQL默认日期格式：%y-%m-%d

举例2：查询出08-25-2012入职的员工

SQL语句：select ename,hiredate from employee where hiredate = '08-25-2012';（错误的）

错误原因：‘02-20-1981’是一个字符串varchar类型，与DATE类型不匹配

正确的SQL语句：select ename,hiredate from employeewhere hiredate = str_to_date('08-25-2012','%m-%d-%Y');

总结：

1、日期是数据库本身的特色，也是数据库本身机制中的一个重要内容，所以仍需掌握；

2、每一个数据库处理日期时采用的机制都不一样，都有自己的一套处理机制，所以在实际开发中将日期字段定义为DATE类型的情况很少；

3、如果使用日期类型，java程序将不能通用。实际开发中，一般会使用“日期字符串”来表示日期。

6.1.11 data_format函数

作用：将‘日期类型’转换为特定格式的‘日期字符串’类型

执行结果：字符串varchar类型（具有特定格式）

用法： date_format(日期类型数据,‘日期格式’)

联想知识点：Java中将日期类型转换为字符串类型

SimpleDateFormat sdf = new

SimpleDateFormat(“yyyy-MM-dd HH:mm:ss SSS”);

Date date = new Date();

String time = sdf.format(date);

举例1：查询员工的入职日期，以‘10-12-2015’的格式显示

到窗口中

SQL语句：select ename,date_format(hiredate,'%m-%d-%Y')

as hiredate from employee;

举例2：查询员工的入职日期，以‘10/12/2015’的格式显示

到窗口中

SQL语句：select ename,date_format(hiredate,'%m/%d/%Y')

as hiredate from employee;

MySQL日期默认格式示例

以下两个SQL语句执行结果相同

第一种：hiredate自动转换成varchar类型，默认采用%Y-%m-%d格式

第二种:

总结：data_format函数主要用在数据库查询操作中。实际工作中，客户需要日期以特定格式展示的时候，需要使用该函数。

6.2 多行处理函数

为什么区分多行处理函数和单行处理函数？

因为单行函数都是一行输入对应一行输出，而多行处理函数都是多行输入对应一行输出。

注意：

分组函数自动忽略空值，不需要手动增加where条件排除空值；

分组函数不能直接使用在where关键字后面。

6.2.1 sum函数

举例1：查询所有员工的工资总和

SQL语句：select sum(sal) from employee;

举例2：查询所有员工的奖金总和

SQL语句：select sum(permium) from employee;

注意：求某一列的和，null会自动被忽略。

举例2：查询所有员工的工资与奖金总和

SQL语句：select sum(sal+permium) from employee;

以上结果计算错误，原因：

permium字段有 null 值，只要有 null 参与的数学运算结果都为 null ，而sum函数会自动忽略掉 null 值，正确的做法是将permium的 null 值转换为 0 ，如：ifnull(permium,0)

正确的语句，如下：

select sum(sal+ifnull(permium,0)) from employee;

6.2.2 avg函数

作用：求某一列的平均值

用法：avg(字段名称)

举例1：查询所有员工的平均工资

SQL语句：select avg(sal) as 平均工资 from employee;

举例2：查询所有员工工资高于平均工资的员工姓名和工资

SQL语句：

select ename,sal from employee where sal > avg(sal);

错误原因：分组函数无法使用在where关键字后面。

正确的SQL语句：select ename,sal from employee where sal>(select avg(sal) from employee);

6.2.3 max函数

作用：取得某一列的最大值

用法：max(字段名称)

举例1：查询员工的最高工资

SQL语句：select max(sal) from employee;

举例2：查询员工入职最晚的日期

SQL语句：select max(hiredate) from employee;

6.2.4 min函数

作用：取得某一列的最小值

用法：min(字段名称)

举例1：查询员工的最低工资

SQL语句：select min(sal) from employee;

举例2：查询员工入职最早的日期

SQL语句：select min(hiredate) from employee;

6.2.5 count函数

作用：取得某字段值不为null的记录总数

用法：count(字段名称) 或 count(*)

注意：count(*)表示取得所有记录，忽略 null ，为 null 的值也会取

count(字段名称)，不会统计为 null 的记录

举例1：查询员工的总人数

SQL语句：select count(*) from employee;

举例2：查询有奖金的员工总人数

SQL语句：select count(permium) from employee;

举例2：查询没有奖金的员工总人数

SQL语句：

select count(*) from employee where permium is null;

6.2.6 多个函数同时使用

可以将多行处理函数（如：sum、avg、max、min、count）都放到 select 中一起使用

select count(*),sum(sal),avg(sal),min(sal),max(sal)

from employee;

6.3 distinct 函数

作用：将查询结果中某一字段的的重复记录去除掉

用法：distinct 字段名称或 distinct 字段名称, 字段名称 … …

注意：distinct只能出现在所有字段最前面，后面如果有多个字段及为多字段联合去重

举例1：查询公司有哪些工作岗位

SQL语句：select distinct job from employee;

举例2：查询公司有多少个工作岗位

SQL语句：select count(distinct job) from employee;

举例2：去除部门编号和工作岗位重复的记录

SQL语句：select distinct deptno,job from employee;

6.4 分组查询

6.4.1 group by

作用：通过哪个或哪些字段进行分组

用法：group by 字段名称

举例1：找出每个工作岗位的最高薪水

思路分析：按照工作岗位分组，然后对每一组求最大值。SQL语句中肯定包含 group by、max(sal)

SQL语句：select max(sal)from employee group by job;

重点结论：有 group by 的DQL语句中，select 语句后面只能跟 分组函数+ 参与分组的字段

举例2：查询每个工作岗位的最高薪水，并且按照由低到高进行排序

思路分析：先按照工作岗位 job 进行分组，然后对每一组薪水求最大值max，最后再根据每一组薪水的最大值进行由低到高排序

SQL语句：select job,max(sal)as maxsal from employee group by job order by maxsal;

注意：如果使用了 order by ，order by 必须放到 group by后面。

举例3：计算每个部门的平均薪水

SQL语句：select deptno,avg(sal)from employee group by deptno;

举例4：查询出不同部门不同岗位的最高薪水

SQL语句：select deptno,job,avg(sal)from employee group by deptno,job;

举例5：查询每个工作岗位的最高薪水，除销售专员之外

SQL语句：select job,max(sal)from employee where job!='销售专员' group by job;

6.4.2 having

作用：如果想对分组的数据再进行过滤，需要使用having子句；

where 与 having 区别：

1、where 和 having 都是为了完成数据的过滤，它们后面都是添加条件；

2、where 是在 group by之前完成过滤；

3、having 是在 group by 之后完成过滤；

举例：查询每个工作岗位的平均工资大于5000的；

SQL语句：select job,avg(sal) from employee where avg(sal) > 5000 group by job;（错误的）

错误原因：

where关键字后面不能直接使用分组函数，这与SQL语句的执行顺序有关系，它会先执行from employee ，然后再进行 where 条件过滤，where条件过滤结束之后再执行 group by 分组，之后才会显示出查询结果。

正确的SQL语句：select job,avg(sal) from employee group by job having avg(sal) > 4650;

注意：能够在where在过滤的数据不要放到having中进行过滤，否则影响SQL语句的执行效率。

6.5 select语句总结

一个完成的DQL语句如下：

select

xxxx

from

xxxx

where 

xxxx

group by

xxxx

having

xxxx

order by

xxxx

以上关键字的顺序不能变，严格遵守

以上语句的执行顺序：

from 从某张表中检索数据

where 经过条件进行过滤

group by 然后分组

having 分组后对数据不满意再过滤

select 查询出来

order by 排序输出