How to use MySQL sorting and single row processing functions

1. Sorting

Mysql supports data sorting operations. For example, now we sort the salary from small to large:

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)

If you need to sort in descending order, you need to specify desc: (default Sort in ascending order, if you specify it, just specify 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)

For more complex situations, sort multiple fields:

For example, we want to sort by salary in ascending order, and the salary is the same In this case, sort by name in descending order:

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)

Sort and search with conditions:

It is required to find the salary between 1250 and 3500, sort by salary in descending order:

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. Single line processing function

After processing one line, process the next line: (one input corresponds to one output)

Convert the content to lowercase

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)

Convert the content to uppercase

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)

Get substring

For example: we want to get the first letter of each name:

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)

String splicing

Splicing each person’s empno and 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)

Find the length

Get the number of characters of each person’s name:

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)

Remove the leading and trailing blanks

Query the detailed information of the name KING, excluding Blanks before and after:

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)

Rounding

Retain 0 decimal places for 123.456

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)

Generate random numbers

Generate random decimals from 0 to 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 conversion

The result of NULL operation in the database must be NULL, so there is a NULL processing function

For example: Calculate the annual income (monthly salary and monthly bonus) of each employee:

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)

The above is the detailed content of How to use MySQL sorting and single row processing functions. For more information, please follow other related articles on the PHP Chinese website!