MySQL Query statement collation

Single-Table Query

MySQL query data from the data table is the most basic statement is a SELECT statement, in the previous "additions and deletions" has been used: SELECT * from the table name, that is, to query all the data in the specified data table. The SELECT statement is described in more detail below.

Querying all fields

Syntax: SELECT field name 1, field Name 2, ...

From table name

Results:

Note: The field order can be changed and the result will be adjusted accordingly:

4.1.2 Use (' * ') wildcard characters in SELECT statements instead of all fields

Syntax: SELECT * from table name;

4.1.3 querying a specified part of a field

Syntax: SELECT field name 1, field Name 2, ... from table name;

4.2 Query by condition

4.2.1 Query with relational operators

Syntax: SELECT field name 1, field Name 2, ...

From table name

WHERE condition Expression

The following relational operators can be used in the WHERE clause:

Relational operators	Description
=	Equals
<>	Not equal to
!=	Not equal to
<	Less than
<=	Less than or equal
>	Greater than
>=	Greater than or equal

Example:

4.2.2 with in keyword query

The In keyword is used to determine whether a field's value is in the specified collection, and if so, the record in which the field is located will be queried.

Syntax: SELECT * | Field name 1, field Name 2, ...

From table name

WHERE field name [not] in (element 1, Element 2, ...) ）

Note: Instead of in and in, the query is a record that is not within the specified range.

4.2.3 query with between and keywords

Between and is used to determine whether a field's value is within the specified range, and if so, the record in which the field is located is queried, and vice versa.

Syntax: SELECT * | {Field name 1, field Name 2, ...}

From table name

WHERE field name [not] between value 1 and value 2;

Note: Not between and indicates that the query is outside the specified range of records.

4.2.4 Null value Query

Some values in the data table may be null (NULL), the null value differs from 0, and it is different from an empty string, and you need to use is NULL to determine whether the value of the field is a null value.

Syntax: SELECT * | Field name 1, field Name 2, ...

From table name

WHERE field name is [NOT] NULL

Note the IS-not-null keyword is used to query for records that have fields that are not null values.

4.2.5 with DISTINCT keyword query

There are duplicate values for some of the fields in many tables, and you can use the DISTINCT keyword to filter for duplicate values, leaving only one value.

Syntax: SELECT DISTINCT field name from table name;

Note: The DISTINCT keyword can also be used for multiple fields, and only if the values of multiple fields are identical will it be considered a duplicate record.

4.2.6 queries with the LIKE keyword

Syntax: SELECT * | Field name 1, field Name 2, ...

From table name

WHERE field name [not] like ' match string ';

Percent percent (%) wildcard character

Matches any length of string, including an empty string. For example, the string "c%" matches a string of any length starting with the character C, such as "CT", "cut", "current", and so on; the string "C%g" means a string beginning with the character C, ending with G; the string "%y%" represents a string containing the character "Y", whether "Y" in what position of the string.

(2) The underscore (_) wildcard character

The underscore wildcard matches only a single character, and multiple underscore wildcards are required to match multiple characters. For example, the string "Cu_" matches a character that starts with the string "CU" with a length of 3, such as "cut", "Cup", and the string "c__l" matches a string containing two characters between "C" and "L", such as "cool". It is important to note that there can be no spaces between consecutive "_", for example "M_ _ql" can only match "My SQL" and cannot match "MySQL".

Note: To query for a field value that contains "%" or "_", the command should be escaped with "\", if you want to query a string with a "%" in it, it should be changed to "%\%%".

4.2.7 Multi-criteria queries with and keywords

When querying data using the SELECT statement, the advantage is that in order to make the query result more accurate, you can use multiple query criteria, such as using the AND keyword to connect two or more query conditions.

Syntax: SELECT * | Field name 1, field Name 2, ...

From table name

WHERE condition expression 1 and conditional expression 2 [... An and conditional expression n];

4.2.8 Multi-criteria query with OR keyword

Unlike the and keyword, the OR keyword is queried whenever one of the conditions is met

Syntax: SELECT * | Field name 1, field Name 2, ...

From table name

A WHERE condition expression 1 OR conditional expression 2 [... An OR conditional expression n];

4.2.9 the use of or with and

When used with OR and and, the and has precedence over or, so when they are used together, the expressions on and both sides are first calculated, and then the expressions on OR both sides are calculated.

Advanced Query

Aggregation functions

Function name	Role
COUNT ()	Returns the number of rows in a column
SUM ()	Returns the value of a column and
AVG ()	Returns the average of a column
MAX ()	Returns the maximum value of a column
MIN ()	Returns the minimum value of a column

(1) Count () function: Count the number of records

Syntax: SELECT COUNT (*) from table name

(2) sum () function: Find out the sum of all the values in a field in a table

Syntax: SELECT SUM (field name) from table name;

(3) AVG () function: Calculate the average of all values in a field in a table

Syntax: SELECT AVG (field name) from table name;

(4) Max () function: Find the maximum value of all values in a field in a table

Syntax: SELECT MAX (field name) from table name;

(5) Min () function: Find the minimum value of all values in a field in a table

Syntax: SELECT MIN (field name) from table name;

4.3.2 to sort the results of a query

Syntax: SELECT field name 1, field Name 2, ...

From table name

ORDER by field name 1 [ASC | DESC], field name 2 [ASC | DESC] ...

The field names specified in the syntax are the basis for sorting the results of the query, ASC for ascending, DESC for descending, and by default in ascending order.

4.3.3 Group Query

When you count data in a table, you can use group by to group by a field or multiple fields, with the same values in a field, such as a group of boys, and a group of girls.

Syntax: SELECT field name 1, field Name 2, ...

From table name

GROUP by Field name 1, field Name 2, ... [having conditional expression];

(1) Grouping by using GROUP by alone

Use the group BY keyword alone to query for one record in each group

Note: If the "#1055" error occurs after typing a command in this 2 example, the group query cannot be completed because the MySQL version is upgraded to 5.7, and Baidu "MySQL 1055 error" refers to the solution.

(2) GROUP by and aggregate functions are used together

Group BY is used with aggregate functions to count the maximum, minimum, average, and so on for one or some of the fields in a group.

(2) GROUP by and having keywords used together

The HAVING keyword has the same effect as the WHERE keyword, except that the HAVING keyword can be followed by an aggregate function, while the WHERE keyword cannot. Usually having the keyword is used with group by to filter the results after grouping.

4.3.4 to limit the number of query results by using limit

Syntax: SELECT field name 2, field Name 2, ...

From table name

Limit [OFFSET,] number of records

In this syntax, the LIMIT can be followed by two parameters, the first parameter "offset" represents the offset, if the offset is 0, from the first record of the query results, the offset of 1 starts from the second record in the query results, and so on. Offset is an optional value, the default value is 0, and the second parameter, record count, indicates the number of bars that are specified to return a query record.

4.4 Aliases for tables and fields

4.4.1 Aliases for tables

In the case of query operations, if the table name is too long to use, you can take an alias for the table instead of the name of the table.

Syntax: SELECT * FROM table name [as] alias;

4.4.1 to alias a field

Syntax: SELECT field name [as] alias [, field name [as] alias, ...] from table name;

MySQL Query statement collation