mysql> select ename,sal from emp order by sal;+--------+---------+| ename | sal |+--------+---------+| smith | 800.00 || james | 950.00 || adams | 1100.00 || ward | 1250.00 || martin | 1250.00 || miller | 1300.00 || turner | 1500.00 || allen | 1600.00 || clark | 2450.00 || blake | 2850.00 || jones | 2975.00 || scott | 3000.00 || ford | 3000.00 || king | 5000.00 |+--------+---------+14 rows in set (0.00 sec)
如果需要降序排序的话，需要指定desc：（默认为升序排序，如果您进行指定的话，指定为asc即可）
mysql> select ename,sal from emp order by sal desc;+--------+---------+| ename | sal |+--------+---------+| king | 5000.00 || scott | 3000.00 || ford | 3000.00 || jones | 2975.00 || blake | 2850.00 || clark | 2450.00 || allen | 1600.00 || turner | 1500.00 || miller | 1300.00 || ward | 1250.00 || martin | 1250.00 || adams | 1100.00 || james | 950.00 || smith | 800.00 |+--------+---------+14 rows in set (0.00 sec)
更复杂的情况，为多字段排序：
比如我们想按照薪资升序排列，薪资一样的情况下，按照名字降序排序：
mysql> select ename,sal from emp order by sal,ename desc;+--------+---------+| ename | sal |+--------+---------+| smith | 800.00 || james | 950.00 || adams | 1100.00 || ward | 1250.00 || martin | 1250.00 || miller | 1300.00 || turner | 1500.00 || allen | 1600.00 || clark | 2450.00 || blake | 2850.00 || jones | 2975.00 || scott | 3000.00 || ford | 3000.00 || king | 5000.00 |+--------+---------+14 rows in set (0.00 sec)
排序结合条件进行查找：
要求找出薪资在1250到3500之间，按照薪资降序排序：
mysql> select ename,sal from emp where sal between 1250 and 3500 order by sal desc;+--------+---------+| ename | sal |+--------+---------+| scott | 3000.00 || ford | 3000.00 || jones | 2975.00 || blake | 2850.00 || clark | 2450.00 || allen | 1600.00 || turner | 1500.00 || miller | 1300.00 || ward | 1250.00 || martin | 1250.00 |+--------+---------+10 rows in set (0.00 sec)
2.单行处理函数处理完一行再处理下一行：（一个输入对应一个输出）
内容转小写mysql> select lower(ename) from emp;+--------------+| lower(ename) |+--------------+| smith || allen || ward || jones || martin || blake || clark || scott || king || turner || adams || james || ford || miller |+--------------+14 rows in set (0.00 sec)
内容转大写mysql> select upper(ename) from emp;+--------------+| upper(ename) |+--------------+| smith || allen || ward || jones || martin || blake || clark || scott || king || turner || adams || james || ford || miller |+--------------+14 rows in set (0.00 sec)
取子串例如：我们想要取到每个名字的第一个字母：
mysql> select substr(ename,1,1) from emp;+-------------------+| substr(ename,1,1) |+-------------------+| s || a || w || j || m || b || c || s || k || t || a || j || f || m |+-------------------+14 rows in set (0.00 sec)
字符串拼接拼接每个人的empno和ename：
mysql> select concat(empno,ename) from emp;+---------------------+| concat(empno,ename) |+---------------------+| 7369smith || 7499allen || 7521ward || 7566jones || 7654martin || 7698blake || 7782clark || 7788scott || 7839king || 7844turner || 7876adams || 7900james || 7902ford || 7934miller |+---------------------+14 rows in set (0.00 sec)
求长度取出每个人名字的字符数：
mysql> select length(ename) from emp;+---------------+| length(ename) |+---------------+| 5 || 5 || 4 || 5 || 6 || 5 || 5 || 5 || 4 || 6 || 5 || 5 || 4 || 6 |+---------------+14 rows in set (0.00 sec)
去除前后空白查询名字为king的详细信息，不包含前后空白：
mysql> select * from emp where ename = trim('king ');+-------+-------+-----------+------+------------+---------+------+--------+| empno | ename | job | mgr | hiredate | sal | comm | deptno |+-------+-------+-----------+------+------------+---------+------+--------+| 7839 | king | president | null | 1981-11-17 | 5000.00 | null | 10 |+-------+-------+-----------+------+------------+---------+------+--------+1 row in set (0.00 sec)
四舍五入对123.456保留0位小数
mysql> select round(123.456,0) from emp;+------------------+| round(123.456,0) |+------------------+| 123 || 123 || 123 || 123 || 123 || 123 || 123 || 123 || 123 || 123 || 123 || 123 || 123 || 123 |+------------------+14 rows in set (0.00 sec)
生成随机数生成0到1的随机小数：
mysql> select rand() from emp;+---------------------+| rand() |+---------------------+| 0.06316715857309024 || 0.5963954959031152 || 0.7924760345299505 || 0.17319371567405176 || 0.48854050551405226 || 0.923121411281751 || 0.1499855706002429 || 0.9805636498896066 || 0.4528615683809496 || 0.3226169229695731 || 0.25449994043866164 || 0.304648964018234 || 0.75974502950883 || 0.8847782862230933 |+---------------------+14 rows in set (0.00 sec)
空转换数据库中对于null进行运算结果一定为null 于是就有了null处理函数
例如：计算每个员工的年收入（月薪+月奖金）：
mysql> select ename,job,sal, -> (case job when 'manager' then sal*1.1 when 'salesman' then sal*1.5 else sal*1.2 end) as newsal -> from emp;+--------+-----------+---------+---------+| ename | job | sal | newsal |+--------+-----------+---------+---------+| smith | clerk | 800.00 | 960.00 || allen | salesman | 1600.00 | 2400.00 || ward | salesman | 1250.00 | 1875.00 || jones | manager | 2975.00 | 3272.50 || martin | salesman | 1250.00 | 1875.00 || blake | manager | 2850.00 | 3135.00 || clark | manager | 2450.00 | 2695.00 || scott | analyst | 3000.00 | 3600.00 || king | president | 5000.00 | 6000.00 || turner | salesman | 1500.00 | 2250.00 || adams | clerk | 1100.00 | 1320.00 || james | clerk | 950.00 | 1140.00 || ford | analyst | 3000.00 | 3600.00 || miller | clerk | 1300.00 | 1560.00 |+--------+-----------+---------+---------+14 rows in set (0.00 sec)
以上就是mysql之排序与单行处理函数怎么使用的详细内容。