MySQL数据库学习之排序与单行处理函数详解

示例表内容见此篇文章

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)

到此这篇关于MySQL数据库学习之排序与单行处理函数详解的文章就介绍到这了,更多相关MySQL排序 单行处理函数内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

我想要获取技术服务或软件
服务范围：MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式：远程服务、电话支持、现场服务，沟通指定方式服务
技术标签：数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题

本站部分文章参考或来源于网络，如有侵权请联系站长。
数据库远程运维 MySQL数据库学习之排序与单行处理函数详解