MySQL數(shù)據(jù)庫學(xué)習(xí)之排序與單行處理函數(shù)詳解

更新時間：2022年07月24日 14:04:29 作者：世界盡頭與你

這篇文章主要為大家詳細(xì)介紹一下MySQL數(shù)據(jù)庫中排序與單行處理函數(shù)的使用，文中的示例代碼講解詳細(xì)，對我們學(xué)習(xí)MySQL有一定幫助，需要的可以參考一下

1.排序

Mysql支持?jǐn)?shù)據(jù)排序操作，例如，現(xiàn)在我們按照工資從小到大進(jìn)行排序操作：

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：（默認(rèn)為升序排序，如果您進(jìn)行指定的話，指定為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)

更復(fù)雜的情況，為多字段排序：

比如我們想按照薪資升序排列，薪資一樣的情況下，按照名字降序排序：

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)

排序結(jié)合條件進(jìn)行查找：

要求找出薪資在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.單行處理函數(shù)

處理完一行再處理下一行：（一個輸入對應(yīng)一個輸出）

內(nèi)容轉(zhuǎn)小寫

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)

內(nèi)容轉(zhuǎn)大寫

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)

求長度

取出每個人名字的字符數(shù)：

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的詳細(xì)信息，不包含前后空白：

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位小數(shù)

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)

生成隨機(jī)數(shù)

生成0到1的隨機(jī)小數(shù)：

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)

空轉(zhuǎn)換

數(shù)據(jù)庫中對于NULL進(jìn)行運(yùn)算結(jié)果一定為NULL 于是就有了NULL處理函數(shù)

例如：計算每個員工的年收入（月薪+月獎金）：

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)

到此這篇關(guān)于MySQL數(shù)據(jù)庫學(xué)習(xí)之排序與單行處理函數(shù)詳解的文章就介紹到這了,更多相關(guān)MySQL排序單行處理函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: