Mysql:select statements

1. Order by (sort)

A field is typically used as the reference standard for sorting.

Syntax: ORDER BY "Field" ASC|DESC; (ascending, descending)

Tip: Proofing rules determine the sort relationship.

Allow multi-field sorting (Sort by first field, sort by second field when indistinguishable occurs, and so on).

"Give me a chestnut." For the following table, enter select * from Tb_name; , the data in the table is displayed in the order in which they are entered:

When you need to sort the data in a table in descending order by field Stu_score, add the order by stu_score DESC after the input statement.

If you do not specify ascending, descending order, the default is to use ascending:

If you use multi-field sorting, eg. Sort by Stu_score first, if the score is the same, then according to the start time date_begin to order, the statement is:

SELECT * FROM Tb_name ORDER by stu_score Desc, Date_begin, ASC;

Tip:order by is the sort of information retrieved, so it needs to be written after the where statement.

If you are grouping, you should use the group by syntax for sorting grouped fields.

2. limit (limits on the number of records obtained)

Limit occurs after sorting, retrieving, and so on, so limit appears at the end.

For the following data table, if you limit the display to only 1 of the data, the output is the right image:

The first set of data is output according to the order in which the data was originally entered.

You can also limit the output to only the top 3 after you have finished sorting:

Syntax:limit offset total number of records ;

Offset: The start point of the Intercept. Total number of records: Length of interception.

If the offset is not written, the default starts at 0.

3. distinct(remove duplicate records) (corresponds to all)

When you display all the data in the field stu_score in table Exam_stu, the result is as follows:

If you want to remove the duplicate records, you can use the statement, so two of 95 points left only one:

The standard for repeating records is determined by the field of the query, when the statement is select DISTINCT *, the two data are considered duplicates only when all fields are the same;

When the statement is a SELECT distinct Stu_score, when the stu_score of two data is the same, the two are considered to be repeated;

When a statement is a SELECT distinct stu_score,stu_name, it must be the same as the name of the score to be repeated.

4. Union query (union query)

Merging multiple SELECT statements together is called a union operation.

With the Union keyword, you can combine two SELECT statements.

"Give me a chestnut." To query the table for the php0228 class and php0331 class days the most two teachers, the statement is

Select Teacher_name,days from Teacher_class where class_name= ' php0228 ' ORDER BY days DESC LIMIT 1;

Select Teacher_name,days from Teacher_class where class_name= ' php0331 ' ORDER BY days DESC LIMIT 1;

These two separate statements can indeed find the desired result, but what about a joint query?

The statement is: (Statement 1) Union (statement 2);

But this kind of writing will delete duplicate records, if you want to keep all the records, even if you repeat, you can use the format:

(statement 1) UNION ALL (statement 2);

Tip: In the case of Union, there are a few things to note about sorting.

ordering of child statements :

① wraps the child statement inside the parentheses;

The order by of the ② child statement takes effect only if it is used with limit. The reason is that union will optimize (ignore) the order by of the words without limit.

To sort the results of a union, use the following statement:

(Statement 1) Union (statement 2) Order by days;

Note: The number of fields retrieved for ① multiple SELECT statements must be the same, and more strictly, the data types should also be consistent (but MySQL internally does type conversion processing, which requires the ability to convert successfully).

Mysql:select statements