MySQL Learning notes-Supplement to data Logging query operations (single-table query)

Select ... from ... .... GROUP by ... the having ... order by ... limit ...;;. (Fixed sequentially)

1. Select Specifies the field (column) to be retrieved

Select to add the DISTINCT keyword, you can remove duplicate records, where duplicate records are records that all field values are the same

2, from the specified query data table, can be a table name, can also be an alias

Usually when an expression is difficult to read, it is aliased: the expression as alias;

3. Where pre-filter condition---Filter the table data off part

Operators that may appear in a conditional statement:

Relational operators:

equals = greater than > less than < greater than or equal to >= less than or equal to <= not equal to <>

String matching operators:

Like words

Like followed by a string and wildcard character, where a wildcard _ (underscore) can match only one character, and a wildcard% (percent) may match any number of characters

Determines whether a value is null:

IsNull (value)

Between A and B: value between [A, a]

In/not in (collection Element): value in/not between collection elements

Logical operators:

With: And && exists null, the result is null

Or: or | | Null with NULL or 0-phase or, still not NULL, with 1-phase or 1

Non: Not!

XOR: The result is null if there is a null

4. Group by groups the where filtered data

aggregate function sum () sums avg () averages max () Max min () to find minimum count () number of records for non-null fields

5. Having to add conditional filtering after grouping results

After group BY, if you need to add conditional statements, you can only use the HAVING clause, not the WHERE clause,

6. Order BY to sort the results of the search

The default is ascending ASC, which is set to desc if descending is required

Allow multi-field sorting: Sort by first field, and if not, use second field to sort, and so on

7. Limit the number of records obtained

Syntax: Limit (limit offset, record_count);

Where limit offset refers to the offset between the first record selected and the first record in the table, if the first record to be selected is the second record in the original table, the offset is 1, and so on

Record_count refers to the total number of records to select, and if the number is greater than the number of records remaining in the table, the remaining records are all displayed

Limit offset can be omitted, the default is 0

MySQL Learning notes-Supplement to data Logging query operations (single-table query)