MySQL之排序与单行处理函数怎么使用

MySQL之排序与单行处理函数怎么使用

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：

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)登录后复制

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。