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 ;