MySQL处理函数

处理函数

一、数据处理函数（单行处理函数）

1、lower(字段名)函数: 转换为小写
用法： lower(要转换字段名称)
（1）、查询员工姓名，将员工姓名全部转换成小写
例如： select lower(ename) as ename from emp;

2、upper(字段名)函数：转换为大写
用法： upper(要转换字段名称)
（1）、查询员工姓名，将员工姓名全部转换为大写
例如： select upper(ename) as ename from emp;

3、substr(字段名,起始下标,截取长度)函数：取子串
用法： substr(被截取字段名称,起始下标,截取长度)
注意： 起始下标：从1开始
（1）、查询并显示所员工姓名的第二个字母
例如： select substr(ename,2,1) from emp;

（2）、查询员工姓名中第二个字母为A的所有员工
例如; select ename from emp where substr(enam,2,1) = “A”;

联想知识点，模糊查询：
select ename from emp where ename like ”_A%”;
4、 length(字段名)凼数：叏字段长度
用法： length(字段名称)
（1）、取得员工姓名长度
例如： select ename,length(ename) as nameLength from emp;

5、ifnull(字段名,替换值)函数：空值处理函数
用法： ifnull(字段名称,将要替换)
结论： 在数据库中，有Null参不数学运算的结果一定为Null；为了防止计算结果出现Null，建议先使用ifnull函数预先处理。
（1）、查询员工姓名及补助，如果补助为Null设置为0；
例如： select ename,ifnull(comm,0) from emp;

（2）、查询员工薪水与补助的和
错误写法：select sal+comm from emp;

正确写法： select sal + ifnull(comm,0) from emp;

（3）、没有补助的员工，将每月补助100，求员工的年薪
例如： select ename, (sal + ifnull(comm,100)) * 12 yearsal from emp;

6、case…when…then…else…end
用法： 匹配工作岗位，当为MANAGER时，薪水上调10%，当为SALESMAN时，薪水上调50%，其它岗位薪水不变
case job
when ‘MANAGER’then sal * 1.1
when ‘SALESMAN’then sal * 1.5
else sal
end
注意： 使用在DQL询句中；
（1）、匹配工作岗位，当为MANAGER时，薪水上调10%，当为SALESMAN时，薪水上调50%，其它岗位薪水不变

7、trim(‘字符串’)函数：去除首尾空格
作用：trim凼数去除首尾空格，不会去除中间空格
用法：trim(字符串)
（1）、取得工作岗位为manager的所有员工
例如： select * from emp where job = trim(”manager ”);
注意：MySQL默认去除字段后面的空格，原因：MySQL语法松散

8、round(数字,保留小数位数)函数：四舍五入
用法： round(要四舍五入的数字,四舍五入到哪一位)，默认保留整数位
（1）、保留整数位或不保留小数位：select round(125.18)；或者select round(125.18,0);

（2）、保留1位小数：select round(125.18,1);

（3）、保留2位小数：select round(125.18,2);

（4）、个数位四舍五入：select round(125.18,-1);

9、 rand()函数：生成随机数
（1）、生成一个 0≤ v ≤ 1.0的随机数;
例如： select rand();

（2）、生成一个0-100的随机数
例如： select round(rand()*100);

10、str_to_date函数：将字符串转换为日期
作用：将‘日期字符串’转换为‘日期类型’数据
执行结果：DATE类
（1）、用法：str_to_date(‘日期字符串’,‘日期格式’)
①日期字符串：日期格式的字符串
②日期格式：告知MySQL输入日期字符串的格式是什么
MySQL日期格式和Java日期格式对比

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

（2）、案例：
①、查询出1981-12-03入职的员工
执行成功：select ename,hiredate from emp where hiredate = ‘1981-12-03’;

原因：
1、在MySQL中日期作为查询条件时，可以使用字符串为其赋值，常用格式有三种:
1949-10-10
1949/10/10
19491010
2、输入的日期字符串格式与MySQL默认日期格式相同，MySQL默认日期格式：%y-%m-%d
（2）查询出02-20-1981入职的员工
错误执行：select ename,hiredate from emp where hiredate = ‟02-20-1981‟;

错误原因： ‘02-20-1981’是一个字符串varchar类型，与MySQL中DATE默认类型不匹配
正确写法： select ename,hiredate from emp where hiredate = str_to_date(‟02-20-1981‟,‘%m-%d-%Y’);

总结：
1、日期是数据库本身的特色，也是数据库本身机制中的一个重要内容，所以仍需掌握；
2、每一个数据库处理日期时采用的机制都不一样，都有自己的一套处理机制，所以在实际开发中将日期字段定义为DATE类型的情况很少；
3、如果使用日期类型，java程序将不能通用。实际开发中，一般会使用“日期字符串”来表示日期
（3）、创建 t_student 表，插入含有日期的数据

插入数据：1980-01-18

执行成功：‘1980-01-18’虽然是一个varchar类型，但是由亍格式和MySQL数据库默认的日期格式相同，所以存在自动类型转换。

执行失败：再次插入01-18-1980
A.‘01-18-1980’日期字符串的格式和MySQL默认的格式不同；
B．‘01-18-1980’是varchar类型，birth字段需要DATE类型，类型不匹配；
正确写法：

*结论： * str_to_date函数通常使用在插入操作中；字段DATA类型，不接收varchar类型，需要先通过该函数将varchar变成data再插入数据。
11、date_format凼数：将日期转换为特定格式字符串
作用： 将‘日期类型’转换为特定格式的‘日期字符串’类型
用法： date_format(日期类型数据,‘日期格式‟)
（1）、查询员工的入职日期，以‘10-12-1980’的格式显示到窗口中；

（2）查询员工的入职日期，以‘10/12/1980’的格式显示到窗口中；

（3）、MySQL日期默认格式示例
以下两个DQL询句执行结果相同
第一种：hiredate自动转换成varchar类型，默认采用%Y-%m-%d格式

第二种：通过date_format(hiredate,’%Y-%m-%d’)函数

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