Quickly grasp the SELECT statement in MySQL database

This paper is a fast and precise grasp of the SELECT statement in MySQL database.

The basic syntax for a SELECT statement in MySQL is:

The following is a reference fragment:

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]]

As you can see from this basic syntax, the simplest SELECT statement is the select select_list, and you can actually use the simplest SELECT statement to do many of the things you expect, and you can use it for any operation that MySQL supports, for example: SELECT 1+1, it will return 2; second, you can also use it to assign values to variables, and in PHP, using the function of the SELECT statement, you are free to use MySQL's functions to perform various operations on the PHP program and assign values to variables. In many cases, you will find that MySQL has many functions that are more powerful than PHP.

Straight_join, Sql_small_result, Sql_big_result, high_priority are MySQL extensions to the ANSI SQL92. If the optimizer joins tables in a non optimal order, using Straight_join can speed up the query.

Sql_small_result and Sql_big_result are a set of relative keywords. They must be used in conjunction with group BY, distinct, or distinctrow. Sql_small_result told the optimizer that the results would be small, requiring MySQL to use temporary tables to store the final tables instead of using the sort; instead, Sql_big_result told the optimizer that the results would be small, requiring MySQL to use sort instead of temporary tables.

High_priority will give the select a higher priority than a statement that updates the table, making it possible to make a quick query first.

The use of the above four keywords is indeed more obscure. Fortunately, in most cases, we can choose not to use these four keywords in mysql.

DISTINCT, Distinctrow provides a basic but useful filter for the result set returned by the query. That is, the result set contains only distinct rows. Note here that for the keyword distinct, distinctrow, null values are equal, no matter how many null values, select only one. And all the use of the superfluous suspicion. It has no effect on the production of the result set.

into {outfile| DumpFile} ' file_name ' export_options to write a result set to a file. The file is created on the server host and cannot be already present. The syntax for the export_options part of the statement is the same as in the fields and lines clauses used in the LOAD datainfile statement, which we will discuss in detail in the MySQL Advanced _load data section. The difference between outfile and DumpFile is that you write only one line to the file, and no columns or rows end.

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

1, "*", representing all columns arranged in the order of the CREATE table.

2. List of column names in the order of the users.

3, you can use aliases to replace the column name, in the form of: column name as column_heading.

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

5, internal function or aggregate function.

6. Any combination of the above items.

From: Determines which tables are used in the Select command. This is generally required unless the column name is not included in the select_list (for example, only constants, arithmetic expressions, and so on). If there are multiple tables in the table entry, separate them with commas. The order of the tables after the keyword from does not affect the result.

The table name can be given a related alias so that the expression is clear. 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 with select T1.name,t2.salary from employee t1,in The FO t2 where t1.name=t2.name is completely equivalent.

All other references to the table, such as in the WHERE clause and the HAVING clause, are aliased, and the alias cannot begin with a number.

The WHERE clause sets the search condition, and its application in the Insert,update,delete statement is exactly the same as the method applied in the SELECT statement. The search condition is immediately following the keyword where. If a user wants to use more than one search condition in a statement, it can be connected with and OR OR. The basic syntax for search conditions is [not] expression Comparison_operator expression; [NOT] expression (not) like "match_string"; [NOT] expression are [not] null; [NOT] expression [not] between expression and expression; [NOT] column_name join_operator column_name; [NOT] boolean_expression.

And: To join two conditions and return the result when all two conditions are true. When multiple logical operators are used in the same statement, the AND operator is always the highest priority, unless the user changes the order of operations with parentheses.

Or: is used to join two conditions and return the result when any of the two conditions are true. When more than one logical operator is used in the same statement, the operator or is usually performed after the operator and. Of course, users can use parentheses to change the order of operations.

Between: A keyword used to identify the lower bound of a range, followed by a value with a range cap. The range where @val between x and y contain the end value. If the first value specified after between is greater than the second value, the query does not return any rows.

COLUMN_NAME: The name of the column used in the comparison. When ambiguity arises, be sure to indicate the name of the table in which the column is located.

Comparison_operator: comparison operator. See table below:

The following is a reference fragment:

Symbolic meaning

= equals

> Greater than

< less than

>= is greater than or equal to

<= less than or equal

!= is not equal to

<> Not equal to

When comparing Char,varchar data, "<" means closer to the head of the alphabet, and ">" represents closer to the end of the alphabet. In general, lowercase letters are larger than uppercase letters, uppercase letters are larger than numbers, but this may depend on the comparison order of the operating systems on the server.

At the time of comparison, the trailing spaces are ignored. For example, "Dirk" equals "Dirk".

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

When using comparison operators to compare character and datetime data, enclose all data in quotes.

Expression: May be a column name, constant, function, or any combination of column names or constants, as well as functions connected by arithmetic or bitwise operators. The arithmetic operators are shown in the following table:

The following is a reference fragment:

Symbolic meaning

+ Plus

-Minus sign

* Multiplication

/Division

Is null: used when searching for a null value.

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

When a user searches for datetime data, it is best to use the keyword like, because the full datetime record contains a variety of date components. For example, the user adds a value of "9:20" to the column arrival_time, and the clause where arrival_time= "9:20" does not find it, because MySQL converts the input data into a "1,1900 9:20am". However, the clause where arrival_time like "%9:20%" can be found.

Boolean_expression: An expression that returns a value of "true" or "false."

Match_string: A string consisting of characters and wildcards, enclosed in single or double quotes, is a matching pattern. The wildcard characters are shown in the following table:

The following is a reference fragment:

Symbolic meaning

String of% 0 or more characters

_ Any single character

Not: Negation of any logical expression, or keyword,

such as Like,null,between and so on.

The GROUP BY and having clauses are used in the SELECT statement,

You can divide a table into groups and return a group that matches the conditions of a HAVING clause.

Syntax: Beginning of SELECT statement

GROUP BY [all] aggregate_free_expression [, aggregate_free_expression]*

[Having search_conditions]

End of SELECT statement

GROUP BY: Specifies the groups that the table will divide, and if you include aggregate functions in a Select table entry, calculate a total for each group. The results of these total values are displayed in a new column, not a new row. In the HAVING clause, the user can refer to these new total columns. You can use aggregate functions such as AVG, COUNT, Max, Min, and sum in the select_list before group by. A table can be grouped by a combination of any columns.

All: A Transact-SQL extension of all groups is included in the result, and all groups here even include groups that are 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 aggregate functions, Transact-SQL extensions allow grouping of expressions with no aggregate function while grouping with column names.

Having: sets a condition for the GROUP BY clause, similar to the way where a condition is set for a SELECT statement. The having lookup condition can include an aggregate function expression. In addition, its lookup conditions are the same as where lookup conditions.

ORDER BY: Arranges results by column. Columns that are output to select can be referenced with 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, s The three sentences elect ID as myid,name as myname from MyTable Group by 1 are completely equivalent. Of course, we disagree with the third use, which will have a bad effect on the readability of the program. To sort in descending order, add the DESC keyword in front of the column name you want to order in the ORDER BY clause. The default is ascending, and you can specify it explicitly with the ASC keyword.

Limit clause: Used to limit the number of rows returned by a SELECT statement. Limit takes 1 or 2 numeric arguments and, given 2 arguments, the first specifies the offset of the first row to be returned, and the second specifies the maximum number of rows to return. The initial line offset is 0 (not 1). If given a parameter, it indicates the maximum number of rows returned with an offset of 0. That is to say, limit 5 and limit 0,5 are completely equivalent.

As for the meaning of procedure keywords, I did not make it too clear, as if support for stored procedures, and MySQL itself does not support stored procedures, it seems to be for future expansion of the need to retain it.