select statement

1. order by (Sort)

Usually one field is used as the reference standard for sorting.

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

tip: Proofreading rules determine the sorting relationship.

                                                                                                                                                          allowed multi-field sorting (sort by the first field first, and when indistinguishable, sort by the second field, and so on).

[Give me an example] For the following table, when you enter select * from tb_name;, the data in the table will be displayed in order according to the input order: When you need to sort the data in the table in descending order according to the field stu_score, add order by stu_score desc after the input statement. :

# This If you use multi -field sorting, eg. Press stu_score for sorting, if the score is the same, then sort according to the opening time

Date

_ Begin, the statement, the statement, the statement, the statement For:

select * from tb_name

order by stu_score desc, date_begin, asc;

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

If it is grouping, you should use the group by syntax to sort the grouping field.

2. limit (Limit the number of records obtained)

limit occurs after sorting, retrieval and other

actions

, so limit appears at the end. For the following data table, if only one of the data is displayed, the output result will be as shown on the right: In the order of initial data input, the first set of data is taken for output.

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

Syntax: limit the total number of offset records

；

Offset: starting point of interception. Total number of records: intercepted length.

If you do not write an offset, it will start from 0 by default.

3.

distinct

(remove duplicate records) (corresponding to

all

)

When in the table exam_stu, display all records in the field stu_score When reading the data, the result is as follows:

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

The standard for duplicate records is determined according to the fields of query. When the statement is select distinct *, only when all fields are the same, two records are considered Data duplication; When the statement is select distinct stu_score, when the stu_score of the two data are the same, they are considered to be duplicates;

When the statement is select distinct stu_score,stu_name, the scores must be sum The names are the same, so they are considered to be duplicates.

4.

union query

（

Union query

）

Merging multiple select statements together is called a joint operation.

Use the union keyword to combine two select statements.

[For example] To query the two teachers who have taught class php0228 and php0331 the most days in the table, the statement is

select teacher_name,days from teacher_class where class_name='php0228' order by days desc limit 1;

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

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

But this way of writing will repeat the record

to delete

, if you want to keep all the records, even repeated , You can use the format: (Specific 1) Union all (statement 2);

tip: In the case of using union, there are a few points to pay attention to sorting.

Ordering of sub-statements

: ① Wrap the sub-statement in sub-parentheses;

② The order by of the sub-statement can only be used with limit will take effect. The reason is: union will optimize (ignore) the order by of sentences without limit.

If you want to sort the results of relative union, use the following statement:

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

Note: ① Multiple select statements The number of retrieved fields must be consistent; more strictly, the

data type

should also be consistent (but mysql will do type conversion internally, to I hope the conversion can be successful).

The above is the detailed content of select statement. For more information, please follow other related articles on the PHP Chinese website!