Chapter 5: sorting and retrieval data; Chapter 5 sorting and Retrieval

Chapter 5: sorting and retrieval data; Chapter 5 sorting and Retrieval

Table Name: products

Field: product_id, product_name, product_price, vend_id (supplier)

According to the database design theory, if the sorting order is not clearly specified, the order of retrieved data should not be assumed to be meaningful.

(If the data has not been updated or deleted, the order of Data addition is displayed by default)

Clause: An SQL statement consists of clauses. Some clauses are necessary, while others are optional. For example, in the FROM clause, if we want to implement sorting, we need to useORDERClause.

1. Sort data:

SELECT product_name FROM products ORDER BY product_name

Note: This statement sorts product_name by product_name. You can also use non-display columns for sorting.

2. sort by multiple columns:

SELECT product_name FROM products ORDER BY product_id,product_price

3. Specify the sorting direction:

SELECT product_name FROM products ORDER BY product_price DESC

Note: by default, data is sorted in ascending order. The DESC keyword is used to sort data in descending order.

First, sort the prices in descending order, and then sort the product names.

SELECT product_name FROM products ORDER BY product_price DESC ,product_name

Note: The keyword of Ascending Order is ASC, but the default sorting is ascending. Case sensitivity and sorting order: the Case sensitivity is the same by default, but DBA can change this sorting method.

Name of the product with the highest price:

SELECT product_name FROM products ORDER BY product_price DESC LIMIT 1

Note: Position of order by: After FROM and before LIMIT.

Conclusion: order by is used for sorting. This clause must be the last clause of the SELECT statement. You can sort one or more columns of data as needed.