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)
After processing one line, process the next line: (one input corresponds to one output)
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)
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)
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)
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)
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)
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 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)
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!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
