MySQL排序和处理函数

排序order by
order by后面写上要排序字段，排序字段可以有多个，多个采用逗号间隔，order by默认采用升序（asc）排序，可以手动设置为降序(desc).如果存在where子句，那么order by必须放到where语句后面。

单个字段排序
按照薪水由小到大排序:

select ename,sal from emp order by sal;

取得job为MANAGER的员工，按照薪水由小到大排序：

select ename,job,sal from emp where job = 'MANAGER' order by sal; 

手动指定按照薪水由小到大排序:

select ename,sal from emp order by sal asc;

手动指定按照薪水由大到小排序（降序关键字desc）:

select ename,sal from emp order by sal desc;

多个字段排序

按照 job 和薪水倒序排序:

select ename,job,sal from emp order by job desc,sal desc;

说明：采用多个字段排序时，如果根据第一个字段排序重复了，会根据第二个字段排序；

使用字段位置排序

按照薪水升序排序：

select * from emp order by 6;

不建议采用此方法，含义不明确，可读性不强，程序不健壮。

处理函数
MySQL提供了一些函数可以对查询出的结果进行处理，方便开发者使用，下面介绍一些常用的数据处理函数，下列的函数中有些是MySQL特有的，在其他数据库管理系统中可能不起作用。

lower()转换为小写

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

select lower(ename) as ename from emp;

upper()转换为大写

查询员工姓名，将员工姓名全部转换为大写：

select upper(ename) as ename from emp;

substr()截取子串

该函数接收3个参数：substr(被截取字段名称,起始下标,截取长度)，起始下标从1开始。

查询并显示所员工姓名的第二个字母：

select substr(ename,2,1) from emp;

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

select ename from emp where substr(ename,2,1) = 'A';

方法二：

select ename from emp where ename like '_A%'; 

length()获取字段长度

取得员工姓名长度

select ename,length(ename) as nameLength from emp;

ifnull()空值处理
该函数接收两个参数： ifnull(字段名,替换值)
在数据库中，有Null参与数学运算的结果一定为Null；为了防止计算结果出现Null，建议先使用ifnull函数预先处理。

查询员工姓名及补助，如果补助为Null设置为0；

select ename,ifnull(comm,0) from emp;

查询员工总薪水：

select sal + ifnull(comm,0) from emp; 

没有补助的员工，每月补助100，求员工的年薪 ：

select ename, (sal + ifnull(comm,100)) * 12 yearsal from emp;

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

在select语句中使用：

select ename, sal, job, 

    (case job
        when ‘MANAGER’  then sal * 1.1
        when ‘SALESMAN’then sal * 1.5
        else    sal
    end) as newsal

from emp;

trim()去除首尾空格

取得工作岗位为manager的所有员工：

select * from emp where job = trim('  manager  ');

注意：MySQL默认去除字段后面的空格，原因：MySQL语法松散。

round()四舍五入

该函数接收两个参数round(数字，保留的小数位数)

查看员工薪水保留1位小数：

select round(sal,1) from emp;

rand()生成随机数

会生成一个0~1之间的的随机数，包含0和1。

select rand();

生成多个随机数：

select rand(), sal from emp;

生成0~100之间的随机数：

select round(rand()*100), sal from emp;

str_to_date()字符串转日期

该函数接收两个参数,str_to_date(‘日期字符串’,’日期格式’)。
其中日期格式如下：

• %Y 代表四位的年份
• %y 代表两位的年份
• %m 代表月，格式（01 … 12）
• %c 代表月，格式（1 … 12）
• %d 代表日
• %H 代表24小时制
• %h 代表12小时制
• %i 代表分种，格式（00 … 59）
• %S或%s 代表秒 , 格式(00…59)

查询出1981-12-03入职的员工：

select ename,hiredate from emp where hiredate = '1981-12-03';

在MySQL中日期作为查询条件时，可以使用字符串为其赋值，常用格式有三种:

2016-06-15
2016/06/15
20160615

使用str_to_date()函数查询02-20-1981年入职的员工:

select ename,hiredate from emp where hiredate = str_to_date('02-20-1981','%m-%d-%Y'); 

date_format()格式化日期
该函数接收两个参数，date_format(日期类型数据,’日期格式’)

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

select ename,date_format(hiredate,'%m-%d-%Y') hiredate from emp ;