MySQL: select statement, mysqlselect statement

MySQL: select statement, mysqlselect statement

1. order by (SORT)

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

Syntax: order by [field] asc | desc; (ascending or descending)

Tip: Check rules to determine the sorting relationship.

Multi-field sorting is allowed (first sort by the first field, and when there is no distinction, sort by the second field, and so on ).

[Example] for the following table, when you input select * from tb_name;, the data in the table is displayed in order of input:

To sort data in the table in descending order according to the stu_score field, add order by stu_score desc after the input statement.

If the ascending or descending order is not specified, the ascending order is used by default:

If multi-field sorting is used, for example, sort by stu_score first. If the scores are the same, sort by date_begin according to the course class time. The statement is:

Select * from tb_name order by stu_score desc, date_begin, asc;

Tip: order by sorts the retrieved information. Therefore, it must be written after the where statement.

If it is a group, you should use the group by syntax for sorting grouping fields.

2. limit (limit the number of records obtained)

Limit occurs after sorting, retrieval, and other actions, so limit appears at the end.

If only one of the following data tables is displayed, the output result is shown on the right:

The first group of data is output according to the initial data input order.

You can also specify only the first three output names after sorting the rows:

Syntax: Total number of limit offset records;

Offset: the start point of the truncation. Total number of records: The Truncation length.

If no offset is specified, the default value starts from 0.

3. distinct (removing duplicate records) (corresponding to all)

When all data in the stu_score field is displayed in the exam_stu table, the result is as follows:

If you want to remove the duplicate records, you can use the following statement, so there is only one remaining for the two 95 points:

The repeat record criteria are determined by the queried fields. When the statement is select distinct *, the two data records are considered to be duplicated only when all fields are the same;

When the statement is select distinct stu_score, when the stu_score of the two data is the same, the two are considered to be duplicate;

When the statement is select distinct stu_score and stu_name, the score and name must be the same before they are considered repeated.

4. union query (Joint query)

Merging multiple select statements is called a union operation.

Use the union keyword to join two select statements.

[Example] The statement is as follows:

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 results, but what if you want to perform a joint query?

Statement: (statement 1) union (Statement 2 );

However, this write method will delete repeated records. If you want to keep all records, even if they are repeated, you can use the following format:

(Statement 1) union all (Statement 2 );

Tip: note the following points when using union.

Substatement sorting:

① Wrap the sub-statement in the sub-brackets;

② The substatement order by takes effect only when used with limit. The reason is: union will optimize (ignore) order by for words without limit ).

If the union result is sorted, use the following statement:

(Statement 1) union (Statement 2) order by days;

Note: ① The number of fields retrieved by multiple select statements must be consistent. more strictly, the data types should also be consistent (but mysql will perform type conversion internally, if yes, the conversion is successful ).