1.排序
mysql支持数据排序操作，例如，现在我们按照工资从小到大进行排序操作：
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：
阿里云-虚拟数字人
阿里云-虚拟数字人是什么？ …
2 查看详情 
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之排序与单行处理函数怎么使用的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/263759.html
微信扫一扫 
支付宝扫一扫 
