溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
今天小編給大家分享一下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)
處理完一行再處理下一行：（一個輸入對應一個輸出）
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之排序與單行處理函數怎么使用”這篇文章的所有內容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會為大家更新不同的知識，如果還想學習更多的知識，請關注億速云行業資訊頻道。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
