How to quickly master SELECT statements in MySQL database _ MySQL

This article provides a quick and detailed understanding of the SELECT statements in the MySQL database. The basic syntax of the SELECT statement in MySQL is as follows: SELECT [STRAIGHT_JOIN] [SQL _SMALL_RESULT] [SQL _BIG_RESULT] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL] select_list [INTO {OUTFILE | DUMPFILE} file_namee MySQL statement

This article provides a quick and detailed understanding of the SELECT statements in the MySQL database.

　　The basic syntax of the SELECT statement in MySQL is:

The following is a reference clip: 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]

From this basic syntax, we can see that the simplest SELECT statement is SELECT select_list. In fact, you can use this simplest SELECT statement to complete many functions you want, first, you can use it to perform any operations supported by MySQL. for example, SELECT 1 + 1 returns 2. second, you can also use it to assign values to variables. in PHP, with this feature of SELECT statements, you can freely use MySQL functions to perform various operations for PHP programs and assign values to variables. In many cases, you will find that MySQL has many more powerful functions than PHP.

STRAIGHT_JOIN, SQL _SMALL_RESULT, SQL _BIG_RESULT, and HIGH_PRIORITY are extensions of MySQL to ANSI SQL92. If the optimizer joins tables in a non-optimal order, use STRAIGHT_JOIN 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 very small. MySQL is required to use a temporary table to store the final table instead of using sorting. On the contrary, SQL _BIG_RESULT tells the optimizer that the result will be very small, and MySQL is required to use sorting instead of temporary.

HIGH_PRIORITY will give the SELECT statement a higher priority than the statement for updating the table, so that it can perform a quick query with priority.

The usage 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 returned result set. That is, only non-duplicate rows are included in the result set. Note that for keywords DISTINCT and DISTINCTROW, NULL values are equal, no matter how many NULL values there are, select only one. The use of "ALL" is too confusing. It has no effect on the generation of result sets.

INTO {OUTFILE | DUMPFILE} 'File _ name' export_options to write the result set to a file. Files are created on the server host and cannot exist. The syntax of the export_options section in the statement is the same as that in the FIELDS and LINES clauses used in the load datainfile statement. we will discuss it in detail in the MySQL advanced _ load data section. The keyword difference between OUTFILE and DUMPFILE is that only one row is written to the file before, and no column or row ends.

Select list: it can contain one or more of the following:

1. "*" indicates all columns in the order of create table.

2. list of column names in the user's order.

3. you can replace the column name with an alias in the following format: column name as column_heading.

4. expression (column name, constant, function, or any combination of column names, constants, and functions connected by arithmetic or bitwise operators ).

5. internal functions or collection functions.

6. any combination of the above items.

FROM: determines which tables are used in the SELECT command. This option is generally required unless select_list does not contain column names (for example, only constants, arithmetic expressions, etc ). If multiple tables exist in the table, separate them with commas. The sequence of the table after the keyword FROM does not affect the result.

Table names can be associated with 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 is equivalent to select t1.name, t2.salary from employee t1, info t2 where t1.name = t2.name.

All other references to the table. for example, aliases must be used in the where clause and having clause. aliases cannot start with numbers.

The where clause sets the search condition. the method used in the insert, update, and delete statements is the same as that used in the select statement. The search condition follows the where keyword. If you want to use multiple search conditions in a statement, use and or to connect. The basic syntax of the search condition 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 results when both conditions are TRUE. When multiple logical operators are used in the same statement, the and operator always gives priority to the operator unless the operator order is changed with parentheses.

Or: used to join two conditions. if either of the two conditions is TRUE, the result is returned. When multiple logical operators are used in the same statement, or operations are usually performed after and. Of course, you can use parentheses to change the operation sequence.

Between: A keyword used to identify the lower limit of the range, and the value following the upper limit 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, no row is returned for this query.

Column_name: name of the column used for comparison. When ambiguity occurs, you must specify the name of the table where the column is located.

Comparison_operator: Comparison operator. See the table below:

The following is a reference clip: Symbolic meaning = Equal > Greater <Less > = Greater than or equal <= Less than or equal ! = Not equal <> Not equal

When comparing char and varchar data, "<" means closer to the alphabet header, and ">" means closer to the end of the alphabet. Generally, lower-case letters are greater than upper-case letters and upper-case letters are greater than numbers, but this may depend on the comparison sequence of the operating system on the server.

During comparison, spaces at the end are ignored. For example, "Dirk" is equal to "Dirk ".

When comparing a date, "<" indicates that it is earlier than, and ">" indicates that it is later.

When comparing character and datetime data using comparison operators, all data must be enclosed in quotation marks.

Expression: it may be a column name, constant, function, or any combination of column name or constant, and a function connected by arithmetic operators or bitwise operators. Arithmetic operators are shown in the following table:

The following is a reference clip: Symbolic meaning + Plus sign -Minus sign * Multiplication number /Division number

Is null: used to search for a NULL value.

Like: Keyword. you can use like for char, varchar, and datetime (excluding seconds and milliseconds). in MySQL, like can also be used for numeric expressions.

When you search for datetime data, it is best to use the keyword like, because the complete datetime record contains a variety of date components. For example, if you add a value "" to the arrival_time column, but the clause where arrival_time = "" does not find it, because MySQL converts the input data to Jan AM ". However, the clause where arrival_time like "% 9:20%" can be found.

Boolean_expression: expression that returns the "true" or "false" value.

Match_string: a string composed of characters and wildcard characters. it is a matching mode and is caused by single or double quotation marks. The following table lists the wildcards:

The following is a reference clip: Symbolic meaning % 0 or multiple character strings _ Any single character Not: denies any logical expressions or keywords, Such as like, null, and. The group by and having clauses are used in select statements, You can divide tables into groups and return groups that match the having clause conditions. Syntax: start with a select statement Group by [all] aggregate_free_expression [, aggregate_free_expression] * [Having search_conditions]

　　End of select statement

Group by: group to which the table is divided. if the select table item contains a set function, a total value is calculated for each group. The results of these total values are displayed in new columns instead of new rows. You can reference these new total columns in the having clause. You can use a set of functions such as avg, count, max, min, and sum in select_list before group. A table can be grouped by any combination of columns.

All: The results contain the Transact-SQL extension of all group Groups. 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: expression that does not contain a set function. the Transact-SQL extension allows grouping by column name without a set function.

Having: set conditions for the group by clause, similar to the where method for setting conditions for select statements. Having search conditions can include set function expressions. In addition, the search condition is the same as the where search condition.

Order by: Results are arranged by columns. Columns output by select can be referenced by column names, column aliases, or column positions. For example, select id as myid, name as myname from mytable group by id, select id as myid, name as myname from mytable group by myid, select id as myid, the three statements name as myname from mytable group by 1 are completely equivalent. Of course, we do not agree with the third method, which will affect the readability of the program. 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. you can also specify it using the ASC keyword.

Limit clause: used to limit the number of rows returned by a 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 ). If a parameter is specified, it indicates the maximum number of returned rows whose offset is 0. That is to say, limit 5 is equivalent to limit.

As for the meaning of the procedure keyword, I have not made it very clear that it seems to support stored procedures, while MySQL itself does not support stored procedures, it seems that it is reserved for future expansion.