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之排序與單行處理函數怎麼使用的詳細內容。更多資訊請關注PHP中文網其他相關文章！
