How to sort data in MySQL

1. Basic use of sorting

1. When querying data, if no sorting operation is used, by default SQL will arrange the query results in the order in which tuples are added.

2. To perform sorting operations in SQL, you can use the keyword ORDER BY.... After this keyword, you can add the keyword ASC (ascend) to indicate ascending order (from small to large), and DESC (descend) to indicate descending order (from large to small).

3. If ASC or DESC is not added after the ORDER BY... keyword to indicate ascending or descending order, SQL will default to ascending ASC order. As shown in the following code:

SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary;

Query results:

##[Example 1] Query employee table

employees The employee's employee_id, last_name, salary and department_id information. And sort by salary from largest to smallest (descending order).

SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC;

Query results:

2. Use column aliases to sort

In SQL, you can use fields (columns) Alias ​​to sort. Because sometimes we need to calculate fields that are not in the table (such as annual salary

annual_salary), or some field names are really too long, using short aliases can improve the efficiency of our programming. As shown in the following example:

[Example] Query the

employee_id, last_name, and monthly salarysalary# of the employees in the employee table employees ## and annual salaryannual_salary information. And sorted by annual_salary from largest to smallest (descending order). <pre class="brush:sql;">SELECT employee_id, last_name, salary, salary * (1 + IFNULL(commission_pct, 0) * 12) AS &quot;annual_salary&quot; FROM employees ORDER BY annual_salary DESC;</pre> Query results:

[Note]

Format requirements,
• WHERE

  Must be declared after FROM.

The alias of the column can only be used in
• ORDER BY...

  , not in WHERE. **If you use column aliases in WHERE, SQL will report an error. So why can’t column aliases in MySQL be used in WHERE? The reasons are as follows:

[Cause]

SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id IN(50, 60, 70)
ORDER BY department_id DESC;

In SQL, the execution order of query statements does not follow the above code from line 1 to line 1 4 lines in order from top to bottom. Instead, first execute line 2 FROM... and first find the table to be queried.
Execute line 3 WHERE... to filter tuples that meet the conditions.
Then execute line 1 SELECT... to display the fields you want to query. Because the column alias is generated only in this step, the WHERE statement it provides cannot be used in the previous step. But it can be used in the next step of ORDER BY.
Finally, the 4th row is ORDER BY... , sorted by a certain column. At this time, you can use column aliases for sorting.
Query results:

3. Second-level sorting

In real life, sometimes we need Sort by one field and then by another field. In SQL we can use secondary sorting to achieve this. Please see the following example for usage:

[Example 1] Query the employee ID

employee_id

, name last_name, of the employees in the employee table employees, Monthly salary salary and department ID department_id. And first sort by department ID department_id in descending order, and then sort by salary from large to small (descending order). <pre class="brush:sql;">SELECT employee_id, last_name, salary, department_id FROM employees ORDER BY department_id DESC, salary DESC;</pre> Query results:

## and so on. You can add a comma after ORDER BY

to achieve level three and level four&hellip ; Sort.

The above is the detailed content of How to sort data in MySQL. For more information, please follow other related articles on the PHP Chinese website!