Detailed parsing of MySQL SELECT syntax

The following articles mainly describe the detailed parsing of MySQL SELECT syntax. We all know that MySQL databases are frequently used by all of us, and their related applications are also of great concern, the following articles mainly explain MySQL SELECT syntax in detail.

 
 

  
  
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY]  
  
  
[DISTINCT | DISTINCTROW | ALL]  
  
  
select_expression,...  
  
  
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]  
  
  
[FROM table_references  
  
  
[WHERE where_definition]  
  
  
[GROUP BY col_name,...]  
  
  
[HAVING where_definition]  
  
  
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]  
  
  
[LIMIT [offset,] rows]  
  
  
[PROCEDURE procedure_name] ] 
 
 

MySQL SELECT is used to retrieve selected rows from one or more tables. Select_expression indicates the column you want to retrieve. SELECT can also be used to retrieve computing rows that do not reference any table. For example:

 
 

  
  
MySQL> SELECT 1 + 1;  
  
  
-> 2 
 
 

All the keywords used must be given exactly in the above sequence. For example, a HAVING clause must be followed BY the group by clause and before the order by clause.

A select expression can use an AS to give an alias. the alias is used AS the column name of the expression and can be used in the order by or HAVING clause. For example:

 
 

  
  
MySQL> select concat(last_name,', ',first_name) AS full_name  
  
  
from mytable ORDER BY full_name; 
 
 

The FROM table_references clause specifies the table FROM which the row is retrieved. If you name multiple tables, you are executing a join operation ). For the syntaxes of JOIN, see 7.13 JOIN syntax.

You can reference a column as col_name, tbl_name.col_name, or db_name.tbl_name.col_name. You do not have to specify a tbl_name or db_name.tbl_name in a MySQL SELECT statement as the prefix of a column reference unless the reference is ambiguous. See 7.1.5 database, table, index, column, and alias naming. For the case of ambiguity, a more explicit column reference format is required.

You can use tbl_name [AS] alias_name to create an alias for a table reference.

 
 

  
  
MySQL> select t1.name, t2.salary from employee AS t1, info AS t2  
  
  
where t1.name = t2.name;  
  
  
MySQL> select t1.name, t2.salary from employee t1, info t2  
  
  
where t1.name = t2.name; 
 
 

The selected output columns can be referenced BY the column name, column alias, or column position in the order by and group by clauses. The column position starts from 1.

 
 

  
  
MySQL> select college, region, seed from tournament  
  
  
ORDER BY region, seed;  
  
  
MySQL> select college, region AS r, seed AS s from tournament  
  
  
ORDER BY r, s;  
  
  
MySQL> select college, region, seed from tournament  
  
  
ORDER BY 2, 3; 
 
 

To sort in descending ORDER, add the DESC keyword to the column name you want to sort in the order by clause. The default value is ascending, which can be explicitly specified by the ASC keyword.

The HAVING clause can reference any column or alias named in select_expression. It is used at last and is not optimized until the project is delivered to the customer. Do not use HAVING for projects in the WHERE clause. For example, it cannot be written as follows:

 
 

  
  
MySQL> select col_name from tbl_name HAVING col_name > 0; 
 
 

The opposite is written as follows:

 
 

  
  
MySQL> select col_name from tbl_name WHERE col_name > 0; 
 
 

In MySQL 3.22.5 or later, you can write the query as follows:

 
 

  
  
MySQL> select user,max(salary) from users group by user HAVING max(salary)>10; 
 
 

In the older MySQL version, you can write as follows:

 
 

  
  
MySQL> select user,max(salary) AS sum from users  
  
  
group by user HAVING sum>10; 
 
 

SQL _SMALL_RESULT, SQL _BIG_RESULT, STRAIGHT_JOIN, and HIGH_PRIORITY are extensions of MySQL to ANSI SQL92.

The STRAIGHT_JOIN force optimizer joins a table in the order of its columns in the FROM clause. If the optimizer joins tables in an unoptimal order, you can use it to accelerate queries. See 7.22 EXPLAIN syntax (obtain information about MySQL SELECT ).

SQL _SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set will be small. In this case, MySQL uses a quick temporary table to store the final table instead of sorting. SQL _SMALL_RESULT is a MySQL extension.

SQL _BIG_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that there will be many rows in the result set. In this case, MySQL uses a temporary disk-based table if needed. In this case, MySQL uses the key values on the group by unit for sorting instead of making a temporary table.

HIGH_PRIORITY will give the SELECT statement a higher priority than the statement for updating a table. You should use it only for queries that are very fast and must be completed at a time. If the table is locked for read or even has an update statement waiting for the table to be released, a SELECT HIGH_PRIORITY will run.

The LIMIT clause can be used to LIMIT the number of rows returned by the MySQL SELECT statement. LIMIT takes one or two numeric parameters. If two parameters are specified, the first parameter specifies the offset of the first row to be returned and the second parameter specifies the maximum number of rows to be returned. The offset of the initial row is 0 (not 1 ).

 
 

  
  
MySQL> select * from table LIMIT 5,10; # Retrieve rows 6-15 
 
 

If a parameter is specified, it indicates the maximum number of returned rows.

 
 

  
  
MySQL> select * from table LIMIT 5; # Retrieve first 5 rows 
 
 

In other words, LIMIT n is equivalent to LIMIT 0, n.

SELECT... into outfile 'file _ name' SELECT statement writes the selected row INTO a file. Files are created on the server host and cannot already exist (no matter what it is, this can prevent database tables and files such as "/etc/passwd" from being damaged ). On the server host, you must have the file permission to use this SELECT statement. MySQL SELECT... into outfile is the inverse operation of load data infile. The export_options syntax of the statement is the same as that of the FIELDS and LINES clauses used in the load data infile statement. See the 7.16 load data infile syntax. In the final text file, only the following characters are escaped by the escaped by character:

Escaped by character

The first character in FIELDS TERMINATED

The first character in LINES TERMINATED

In addition, ASCII 0 is converted to escaped by followed BY 0 (ASCII 48 ). The reason is that you must escape any fields terminated by, escaped by, or lines terminated by characters so that you can read the file back reliably. ASCII 0 is escaped to make it easier to watch with the browser. Because the final file does not have to follow the SQL syntax, nothing else needs to be escaped.

If you use into dumpfile instead of into outfile, MySQL writes only one row to the file without any columns or rows ending with no escape. This is useful if you want to store a blob in a file.