This article aims at quickly and accurately mastering the SELECT statement in the MySQL database.
The basic syntax of the SELECT statement in MySQL is:
The following is a quoted 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 can be seen from this basic syntax, the simplest SELECT statement is SELECT select_list. In fact, using this simplest SELECT statement, you can also complete many functions you expect. First, you can use it to perform any operation supported by MySQL. For example: SELECT 1+1, it will return 2; secondly, you can also use it to assign values to variables. In PHP, using this function of the SELECT statement, you can freely use MySQL functions to perform various tasks for the PHP program. operations 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 MySQL extensions to 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 with GROUP BY, DISTINCT or DISTINCTROW. SQL_SMALL_RESULT tells the optimizer that the result will be very small, requiring MySQL to use a temporary table to store the final table instead of using sorting; conversely, SQL_BIG_RESULT tells the optimizer that the result will be very small, requiring MySQL to use sorting instead of making a temporary table.
HIGH_PRIORITY will give SELECT a higher priority than a statement that updates the table, allowing it to perform a prioritized and fast query.
The usage of the above four keywords is indeed rather obscure. Fortunately, in most cases, we can choose not to use these four keywords in MySQL.
DISTINCT and DISTINCTROW provide the most basic but useful filtering for the result set returned by the query. That is, the result set contains only distinct rows. What should be noted here is that for the keywords DISTINCT and DISTINCTROW, null values are equal. No matter how many NULL values there are, only one is selected. The usage of ALL is superfluous. It has no effect on the generation 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 part of the statement is the same as that used in the FIELDS and LINES clauses of the LOAD DATAINFILE statement. We will discuss it in detail in the MySQL Advanced_LOAD DATA article. The difference between the keywords OUTFILE and DUMPFILE is that only one line is written to the file, without any column or line ending.
select list: It can contain one or more of the following contents:
1. "*" means all columns arranged in the order of create table.
2. A list of column names arranged in the order required by the user.
3. You can use an alias to replace the column name, in the following form: column name as column_heading.
4. Expressions (column names, constants, functions, or any combination of column names, constants, and functions connected with arithmetic or bitwise operators).
5. Internal functions or aggregate functions.
6. Any combination of the above items.
FROM: Determines which tables are used in the SELECT command. This item is generally required unless the select_list does not contain column names (for example, only constants, arithmetic expressions, etc.). If there are multiple tables in the table entry, separate them with commas. The order of the tables following the FROM keyword does not affect the results.
Table names can be given related aliases to make expressions 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 is the same as select t1.name,t2.salary from employee t1,info t2 where t1.name=t2.name Completely equivalent.
All other references to the table, such as in where clauses and having clauses, must use aliases, and aliases cannot begin with a number.
The where clause sets the search conditions, and its application method in insert, update, and delete statements is exactly the same as its application method in select statements. The search terms follow the keyword where. If the user wants to use multiple search conditions in a statement, they can be connected by and or or. The basic syntax of search conditions 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 connect two conditions and return the result when both conditions are TRUE. When multiple logical operators are used in the same statement, the and operator always takes precedence, unless the user uses parentheses to change the order of operations.
or: Used to connect two conditions and return the result when either condition is TRUE. When multiple logical operators are used in the same statement, operator or usually operates after operator and. Of course the user can use parentheses to change the order of operations.
between: Keyword used to identify the lower limit of the range, and followed by the value of the upper limit of the range. The range where @val between x and y includes 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. When ambiguity arises, be sure to specify the table name where the column is located.
comparison_operator: comparison operator. See the table below:
The following are quotation fragments:
symbol meaning
= equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to
!= is not equal to
<> is not equal to
When comparing char and varchar type data, "<" means closer to the head of the alphabet, and ">" means closer to the end of the alphabet. Generally speaking, lowercase letters are greater than uppercase letters, and uppercase letters are greater than numbers, but this may depend on the comparison order of the operating system on the server.
Trailing spaces are ignored when comparing. For example, "Dirk" equals "Dirk".
When comparing dates, "<" means earlier than and ">" means later than.
When using comparison operators to compare character and datetime data, all data must be enclosed in quotation marks.
expression: may be a column name, a constant, a function, or any combination of column names or constants, and a function connected with arithmetic operators or bitwise operators. The arithmetic operators are shown in the following table:
Here is the quoted snippet:
symbol meaning
+ plus sign
- minus sign
* multiplication sign
/ The division sign
is null: used when searching 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 users search for datetime data, it is best to use the keyword like, because a complete datetime record contains a variety of date components. For example, the user adds a value "9:20" to the column arrival_time, but does not find it in the clause where arrival_time="9:20" because MySQL converts the entered data into "Jan 1,1900 9:20AM". However the clause where arrival_time like "%9:20%" can find it.
boolean_expression: An expression that returns a "true" or "false" value.
match_string: A string consisting of characters and wildcards, enclosed in single quotes or double quotes, is a matching pattern. Wildcards are shown in the following table:
The following is a quoted fragment:
symbol meaning
% A string of 0 or more characters
_ any single character
not: negate any logical expression or keyword,
Such as like, null, between, etc.
The group by and having clauses are used in the select statement,
It is possible to divide a table into groups and return the groups matching the having clause condition.
Syntax: Beginning of select statement
group by [all] aggregate_free_expression [,aggregate_free_expression]*
[having search_conditions]
at the end of the select statement
: specifies the group into which the table will be divided. If an aggregate function is included in the select table item, a total value is calculated for each group. The results of these totals are displayed in new columns instead of new rows. Users can reference these new total columns in the having clause. Aggregation 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: Transact-SQL extension that includes all groups in the results, even those excluded by the where clause. If the having clause is used at the same time, the meaning of all will be negated.
aggregate_free_expression: An expression that does not contain an aggregate function. The Transact-SQL extension allows grouping by an expression without an aggregate function while grouping by column name.
having: Set conditions for the group by clause, similar to where where sets conditions for the select statement. The search conditions for having can include aggregate function expressions. Other than that, its search criteria are the same as where search criteria.
order by: Arrange the results by column. The 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, name as myname from mytable group by 1. These three sentences are completely equivalent. of. Of course, we do not agree with the third usage, which will have a bad impact on the readability of the program. To sort in descending order, add the DESC keyword to the order by clause before the column name you want to sort. The default is ascending order, but you can specify it explicitly using the ASC keyword.
limit clause: used to limit the number of rows returned by the select statement. limit takes 1 or 2 numeric parameters. If 2 parameters are given, the first specifies the offset of the first row to be returned, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1). If an argument is given, it indicates the maximum number of rows returned at offset 0. In other words, limit 5 and limit 0,5 are completely equivalent.
As for the meaning of the procedure keyword, I am not very clear about it. It seems to support stored procedures, and MySQL itself does not support stored procedures. It seems to be reserved for future expansion needs.