MySQL advanced SELECT grammar
- 2020-05-06 11:50:09
- OfStack
The basic syntax of the SELECT statement in MySQL is
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY] [DISTINCT|DISTINCTROW|ALL] select_list [INTO {OUTFILE|DUMPFILE} 'file_name' export_options] [FROM table_references [WHERE where_definition] [GROUP BY col_name,...] [HAVING where_definition] [ORDER BY {unsighed_integer|col_name|formura} [ASC|DESC],...] [LIMIT [offset,] rows] [PROCEDURE procedure_name]]
STRAIGHT_JOIN, SQL_SMALL_RESULT, SQL_BIG_RESULT, HIGH_PRIORITY are MySQL extensions to ANSI SQL92. If the optimizer joins the table in a non-optimal order, STRAIGHT_JOIN can be used to speed up the query.
SQL_SMALL_RESULT and SQL_BIG_RESULT are a set of relative keywords. They must be used with GROUP BY, DISTINCT, or DISTINCTROW. SQL_SMALL_RESULT tells the optimizer that the result will be small, requiring MySQL to use temporary tables to store the final table instead of using a sort; On the other hand, SQL_BIG_RESULT tells the optimizer that the results will be small, requiring MySQL to use sorting instead of making temporary tables.
HIGH_PRIORITY will give SELECT a higher priority than a statement that updates the table, enabling it to make a quick, prioritized query.
The use of the above four keywords is indeed obscure. Fortunately, in most cases, we can choose not to use these four keywords in MySQL.
DISTINCT and DISTINCTROW provide a basic but useful filter for the result set returned by a query. That is, the result set contains only non-repeating rows. Note here that the null values are the same for the keywords DISTINCT and DISTINCTROW. No matter how many NULL values there are, select only one. The use of ALL is a gilding of the lily. It has no effect on the production of the result set.
INTO {OUTFILE|DUMPFILE} 'file_name' export_options, writes the result set to a file. The file is created on the server host and cannot already exist. The syntax of the export_options section of the statement is the same as that used in the FIELDS and LINES clauses of the LOAD DATAINFILE statement, which we will discuss in detail in the MySQL advanced _LOAD DATA. The difference between the OUTFILE and DUMPFILE keywords is that only one line is written to the file and no column or line ends.
select list: it may contain one or more of the following:
1. "*" means all columns in the order create table.
2. A list of column names in the order the user wants.
3. Column names can be replaced by aliases in the form column name as column_heading.
4. Expressions (column names, constants, functions, or any combination of column names, constants, and functions connected by arithmetic or bitwise operators).
5. Internal function or set function.
6. Any combination of the above.
FROM: determines which tables are used in the SELECT command. This is generally required unless select_list contains no column names (for example, just constants, arithmetic expressions, and so on). If there are more than one table in a table entry, separate it with a comma. The order of the tables following the keyword FROM does not affect the results.
Table names can be given related aliases for clarity. The syntax here is tbl_name [AS] alias_name. For example:
select t1 name, t2. salary from employee as t1, info as t2 where t1. name = t2. name and select t1. name, t2. salary from employee t1, info t2 where Es176en1.name = t2.name is completely equivalent.
All other references to the table, such as in the where clause and having clause, are aliased; aliases cannot begin with Numbers.
The where clause sets the search criteria, and it is applied exactly the same way in insert, update, delete statements as it is in select statements. The search terms follow the keyword where. If the user wants to use multiple search criteria in a statement, an and or or connection is available. The basic syntax for search criteria is [not] expression comparison_operator expression; [not] expression [not] like "match_string"; [not] expression is [not] null; [not] expression [not] between expression and expression; [not] column_name join_operator column_name; [not] boolean_expression.
and: used to join two conditions and return the result if both conditions are TRUE. When more than one logical operator is used in the same statement, the and operator always takes precedence, unless the user changes the order of operations with parentheses.
or: used to join two conditions and return the result if either condition is TRUE. When more than one logical operator is used in the same statement, the operator or usually operates after the operator and. Of course, users can use parentheses to change the order of operations.
between: keyword used to identify the lower end of the range. and is followed by the upper end of the range. The range where @val between x and y contains the first and last values. If the first value specified after between is greater than the second value, the query does not return any rows.
column_name: the column name used in the comparison. Be sure to specify the table name of the column when ambiguity arises.
comparison_operator: comparison operator. See the following table:
The symbol means
is equal to
> is greater than
< is less than
> = is greater than or equal to
< = is less than or equal to
! = does not equal
< > does not equal
When comparing char, varchar data, "< "Means closer to the head of the alphabet," > "Is closer to the end of the alphabet. Generally, lowercase letters are greater than uppercase letters, and uppercase letters are greater than Numbers, but this may depend on the operating system comparison order on the server.
The space at the end of the comparison is ignored. For example, "Dirk" equals "Dirk ".
When comparing dates, "< "Means earlier than," > "Later than" means later than.
When comparing character and datetime data using the comparison operator, use quotation marks to enclose all the data.
expression: may be column names, constants, functions, or any combination of column names or constants, and functions connected by arithmetic or bitwise operators. The arithmetic operators are shown in the following table:
The symbol means
+ plus
- minus
* times
/ division number
is null: used when searching for an NULL value.
like: keywords: char, varchar, datetime (excluding seconds and milliseconds) can be used for like, like in MySQL can also be used for numeric expressions.
When users are searching for datetime type data, it is best to use the keyword like, as the full datetime record contains various date components. For example, the user adds a value "9:20" to the column arrival_time, but the clause where arrival_time= "9:20" does not find it because MySQL converts the input data to "Jan 1,1900 9:20AM". However, the clause where arrival_time like "%9:20%" finds it.
boolean_expression: returns an expression for the value "true" or "false".
match_string: string of characters and wildcards, enclosed in single or double quotes, is the matching pattern. The wildcard is shown in the following table:
The symbol means
% 0 or more character string
any single character
not: negates any logical expression, or keyword, such as like, null, between, etc.
The group by and having clauses are used in the select statement to group a table and return a group that matches the having clause condition.
Syntax: select begins
group by [all] aggregate_free_expression [,aggregate_free_expression]*
[having search_conditions]
select end of statement
group by: specifies the groups into which the table will be divided, and if the collection function is included in the select table entry, a total value is calculated for each group. The results of these totals are shown in new columns, not new rows. In the having clause, users can refer to these new total columns. Set functions such as avg, count, max, min, and sum can be used in select_list before group by. Tables can be grouped by any combination of columns.
all: include the Transact-SQL extension of all group groups in the result, and all group groups here include even those excluded by the where clause. If the having clause is used at the same time, the meaning of all is negated.
aggregate_free_expression: expressions that do not contain collection functions. The Transact-SQL extension allows you to group by column names while using expressions that do not contain collection functions.
having: conditions group by clause, similar to the way where conditions select statements. The search criteria for having can include set function expressions. Other than that, it has the same search criteria as where.
order by: arrange the results by column. The column output to select can be referenced with the column name, column alias, or column position. Such as: select id as myid name myid as myid These three statements are completely equivalent. Of course, we disapprove of the third usage, which can have a bad effect on the readability of the program. To order in descending order, add the DESC keyword to the column name you want to sort in the order by clause. The default is ascending, which you can also specify using the ASC keyword.
limit clause: used to limit the number of rows returned by select statements. limit takes one or two numeric parameters, if given two parameters, the first specifies the offset for the first row to be returned, and the second specifies the maximum number of rows to be returned. The offset for the initial row is 0 (not 1). Given a parameter, it indicates the maximum number of returned rows with an offset of 0. So limit 5 is exactly the same as limit 0,5.
As for the meaning of procedure keyword, I have not made it clear, it seems that it supports stored procedures, while MySQL itself does not support stored procedures, so it seems that it is reserved for future expansion.
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY] [DISTINCT|DISTINCTROW|ALL] select_list [INTO {OUTFILE|DUMPFILE} 'file_name' export_options] [FROM table_references [WHERE where_definition] [GROUP BY col_name,...] [HAVING where_definition] [ORDER BY {unsighed_integer|col_name|formura} [ASC|DESC],...] [LIMIT [offset,] rows] [PROCEDURE procedure_name]]
STRAIGHT_JOIN, SQL_SMALL_RESULT, SQL_BIG_RESULT, HIGH_PRIORITY are MySQL extensions to ANSI SQL92. If the optimizer joins the table in a non-optimal order, STRAIGHT_JOIN can be used to speed up the query.
SQL_SMALL_RESULT and SQL_BIG_RESULT are a set of relative keywords. They must be used with GROUP BY, DISTINCT, or DISTINCTROW. SQL_SMALL_RESULT tells the optimizer that the result will be small, requiring MySQL to use temporary tables to store the final table instead of using a sort; On the other hand, SQL_BIG_RESULT tells the optimizer that the results will be small, requiring MySQL to use sorting instead of making temporary tables.
HIGH_PRIORITY will give SELECT a higher priority than a statement that updates the table, enabling it to make a quick, prioritized query.
The use of the above four keywords is indeed obscure. Fortunately, in most cases, we can choose not to use these four keywords in MySQL.
DISTINCT and DISTINCTROW provide a basic but useful filter for the result set returned by a query. That is, the result set contains only non-repeating rows. Note here that the null values are the same for the keywords DISTINCT and DISTINCTROW. No matter how many NULL values there are, select only one. The use of ALL is a gilding of the lily. It has no effect on the production of the result set.
INTO {OUTFILE|DUMPFILE} 'file_name' export_options, writes the result set to a file. The file is created on the server host and cannot already exist. The syntax of the export_options section of the statement is the same as that used in the FIELDS and LINES clauses of the LOAD DATAINFILE statement, which we will discuss in detail in the MySQL advanced _LOAD DATA. The difference between the OUTFILE and DUMPFILE keywords is that only one line is written to the file and no column or line ends.
select list: it may contain one or more of the following:
1. "*" means all columns in the order create table.
2. A list of column names in the order the user wants.
3. Column names can be replaced by aliases in the form column name as column_heading.
4. Expressions (column names, constants, functions, or any combination of column names, constants, and functions connected by arithmetic or bitwise operators).
5. Internal function or set function.
6. Any combination of the above.
FROM: determines which tables are used in the SELECT command. This is generally required unless select_list contains no column names (for example, just constants, arithmetic expressions, and so on). If there are more than one table in a table entry, separate it with a comma. The order of the tables following the keyword FROM does not affect the results.
Table names can be given related aliases for clarity. The syntax here is tbl_name [AS] alias_name. For example:
select t1 name, t2. salary from employee as t1, info as t2 where t1. name = t2. name and select t1. name, t2. salary from employee t1, info t2 where Es176en1.name = t2.name is completely equivalent.
All other references to the table, such as in the where clause and having clause, are aliased; aliases cannot begin with Numbers.
The where clause sets the search criteria, and it is applied exactly the same way in insert, update, delete statements as it is in select statements. The search terms follow the keyword where. If the user wants to use multiple search criteria in a statement, an and or or connection is available. The basic syntax for search criteria is [not] expression comparison_operator expression; [not] expression [not] like "match_string"; [not] expression is [not] null; [not] expression [not] between expression and expression; [not] column_name join_operator column_name; [not] boolean_expression.
and: used to join two conditions and return the result if both conditions are TRUE. When more than one logical operator is used in the same statement, the and operator always takes precedence, unless the user changes the order of operations with parentheses.
or: used to join two conditions and return the result if either condition is TRUE. When more than one logical operator is used in the same statement, the operator or usually operates after the operator and. Of course, users can use parentheses to change the order of operations.
between: keyword used to identify the lower end of the range. and is followed by the upper end of the range. The range where @val between x and y contains the first and last values. If the first value specified after between is greater than the second value, the query does not return any rows.
column_name: the column name used in the comparison. Be sure to specify the table name of the column when ambiguity arises.
comparison_operator: comparison operator. See the following table:
The symbol means
is equal to
> is greater than
< is less than
> = is greater than or equal to
< = is less than or equal to
! = does not equal
< > does not equal
When comparing char, varchar data, "< "Means closer to the head of the alphabet," > "Is closer to the end of the alphabet. Generally, lowercase letters are greater than uppercase letters, and uppercase letters are greater than Numbers, but this may depend on the operating system comparison order on the server.
The space at the end of the comparison is ignored. For example, "Dirk" equals "Dirk ".
When comparing dates, "< "Means earlier than," > "Later than" means later than.
When comparing character and datetime data using the comparison operator, use quotation marks to enclose all the data.
expression: may be column names, constants, functions, or any combination of column names or constants, and functions connected by arithmetic or bitwise operators. The arithmetic operators are shown in the following table:
The symbol means
+ plus
- minus
* times
/ division number
is null: used when searching for an NULL value.
like: keywords: char, varchar, datetime (excluding seconds and milliseconds) can be used for like, like in MySQL can also be used for numeric expressions.
When users are searching for datetime type data, it is best to use the keyword like, as the full datetime record contains various date components. For example, the user adds a value "9:20" to the column arrival_time, but the clause where arrival_time= "9:20" does not find it because MySQL converts the input data to "Jan 1,1900 9:20AM". However, the clause where arrival_time like "%9:20%" finds it.
boolean_expression: returns an expression for the value "true" or "false".
match_string: string of characters and wildcards, enclosed in single or double quotes, is the matching pattern. The wildcard is shown in the following table:
The symbol means
% 0 or more character string
any single character
not: negates any logical expression, or keyword, such as like, null, between, etc.
The group by and having clauses are used in the select statement to group a table and return a group that matches the having clause condition.
Syntax: select begins
group by [all] aggregate_free_expression [,aggregate_free_expression]*
[having search_conditions]
select end of statement
group by: specifies the groups into which the table will be divided, and if the collection function is included in the select table entry, a total value is calculated for each group. The results of these totals are shown in new columns, not new rows. In the having clause, users can refer to these new total columns. Set functions such as avg, count, max, min, and sum can be used in select_list before group by. Tables can be grouped by any combination of columns.
all: include the Transact-SQL extension of all group groups in the result, and all group groups here include even those excluded by the where clause. If the having clause is used at the same time, the meaning of all is negated.
aggregate_free_expression: expressions that do not contain collection functions. The Transact-SQL extension allows you to group by column names while using expressions that do not contain collection functions.
having: conditions group by clause, similar to the way where conditions select statements. The search criteria for having can include set function expressions. Other than that, it has the same search criteria as where.
order by: arrange the results by column. The column output to select can be referenced with the column name, column alias, or column position. Such as: select id as myid name myid as myid These three statements are completely equivalent. Of course, we disapprove of the third usage, which can have a bad effect on the readability of the program. To order in descending order, add the DESC keyword to the column name you want to sort in the order by clause. The default is ascending, which you can also specify using the ASC keyword.
limit clause: used to limit the number of rows returned by select statements. limit takes one or two numeric parameters, if given two parameters, the first specifies the offset for the first row to be returned, and the second specifies the maximum number of rows to be returned. The offset for the initial row is 0 (not 1). Given a parameter, it indicates the maximum number of returned rows with an offset of 0. So limit 5 is exactly the same as limit 0,5.
As for the meaning of procedure keyword, I have not made it clear, it seems that it supports stored procedures, while MySQL itself does not support stored procedures, so it seems that it is reserved for future expansion.