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

吾爱主题阅读：147 2024-04-01 23:20:12 评论：0

1.排序

Mysql支持数据排序操作，例如，现在我们按照工资从小到大进行排序操作：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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即可）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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)

更复杂的情况，为多字段排序：

比如我们想按照薪资升序排列，薪资一样的情况下，按照名字降序排序：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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之间，按照薪资降序排序：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 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.单行处理函数

处理完一行再处理下一行：（一个输入对应一个输出）

内容转小写

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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)

内容转大写

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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)

取子串

例如：我们想要取到每个名字的第一个字母：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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)

求长度

取出每个人名字的字符数：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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的详细信息，不包含前后空白：

1 2 3 4 5 6 7 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位小数

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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的随机小数：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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处理函数

例如：计算每个员工的年收入（月薪+月奖金）：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 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)