As can be seen from this basic syntax, the simplest
SELECT statement is SELECT select_list. In fact, with this simplest SELECT statement, you can also complete many of the functions you expect. First of all, you can use it to perform any operation supported by MySQL For example: SELECT 1+1, it will return 2; Second, you can also use it to assign values to variables, and in PHP, using this function of the SELECT statement, you can freely use MySQL functions for PHP programs. Operations, and assign values to variables. In many cases, you will find that MySQL has many more powerful functions than PHP.
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 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 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 small, requiring MySQL to use a temporary table to store the final table instead of sorting; conversely, SQL_BIG_RESULT tells the optimizer that the result will be 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, making it possible to perform a quick query with priority.
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 the query. That is, the result set contains only non-duplicate rows. It should be noted here that for the keywords DISTINCT and DISTINCTROW, the null values are all equal, no matter how many NULL values there are, only one is selected. The use of ALL is too much to add to the picture. It has no effect on the production of result sets.
INTO {OUTFILE|DUMPFILE}'file_name' export_options, write 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 the FIELDS and LINES clauses used in the LOAD DATAINFILE statement. We will discuss it in detail in the MySQL Advanced _LOAD DATA chapter. The difference between the keywords of OUTFILE and DUMPFILE is: only one line is written to the file after the end, and there is no column or line end.
select list: It can contain one or more of the following:
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 aliases to replace column names in the form of 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 functions or aggregate functions.
6. Any combination of the above.
FROM: Decide which tables to use in the SELECT command. This is generally required unless the column name is not included in select_list (for example, only constants, arithmetic expressions, etc.) If there are multiple tables in the table entry, separate them with commas. The order of the table after the keyword FROM does not affect the result.
Table names can be given related aliases in order to make the expression clear. The syntax here is tbl_name [AS] alias_name. E.g:
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 t1.name=t2.name are exactly the same Price.
All other references to the table, such as in the where clause and having clause, must use aliases, which cannot begin with a number.
The where clause sets the search condition, and its application method in insert, update, and delete statements is also the same as that in the select statement. The search condition immediately follows the keyword where. If the user wants to use multiple search conditions in the statement, they can be connected with 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 using multiple logical operators in the same statement, the and operator is always the highest priority unless the user changes the order of operations with parentheses.
or: used to connect two conditions, and returns the result when any one of the two conditions is TRUE. When multiple logical operators are 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 limit of the range, and followed by the value of 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, the query does not return any rows.
column_name: The column name used in the comparison. When there is ambiguity, be sure to indicate the table name where the column is located.
comparison_operator: comparison operator. See the table below:
Symbol meaning
= Equal to
> Greater than
<Less than
>= greater than or equal to
<= less than or equal to
!= not equal
<> not equal
When comparing char and varchar data, "<" means closer to the head of the alphabet, and ">" means closer to the end of the alphabet. In general, lowercase letters are larger than uppercase letters, and uppercase letters are larger than numbers, but this may depend on the comparison order of the operating systems on the server.
In the comparison, the space at the end is ignored. For example, "Dirk" is equal to "Dirk".
When comparing dates, "<" means earlier, and ">" means later.
When using comparison operators to compare character and datetime data, quote all the data.
expression: May be a column name, constant, function, or any combination of column names or constants, and functions connected by arithmetic operators or bitwise operators. The arithmetic operators are shown in the following table:
Symbol meaning
+ Plus sign
-Minus sign
* Multiplication sign
/ Divide
is null: used when searching for a NULL value.
like: keywords, you can use like for char, varchar, and datetime (not including seconds and milliseconds). In MySQL, like can also be used for numeric expressions.
When users are searching for datetime data, it is best to use the keyword like, because the complete datetime record contains a variety of date components. For example, the user adds a value "9:20" to the column arrival_time, but the clause where arrival_time="9:20" fails to find it, because MySQL converts the entered data to "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 composed of characters and wildcards, enclosed in single or double quotes, is a matching pattern. The wildcards are shown in the following table:
Symbol meaning
% 0 or more character strings
_ Any single character
not: Denies any logical expressions, or keywords, such as like, null, between, etc.
The group by and having clauses are used in select statements to divide the table into groups and return groups that match the conditions of the 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 group that the table will be divided into. If an aggregate function is included in the select table entry, a total value is calculated for each group. The results of these totals are displayed in new columns instead of new rows. The user can refer to these new total columns in the having clause. Aggregate functions such as avg, count, max, min, and sum can be used in the select_list before group by. Tables can be grouped by any combination of columns.
all: Includes Transact-SQL extensions for all groups in the results. All groups here even include 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: Expressions that do not include aggregate functions. The Transact-SQL extension allows grouping with expressions without aggregate functions while grouping by column name.
having: Set conditions for the group by clause, similar to the method for setting conditions for the select statement. The search condition of having may include an aggregate function expression. In addition, its search conditions are the same as where search conditions.
order by: Sort the results by column. The columns output by select can be referenced by column name, column alias, or column position. 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 are completely equivalent of. Of course, we do not approve of the third usage, which will have a bad influence on the readability of the program. To sort in descending order, add the DESC keyword before the column name you want to sort in the order by clause. The default is ascending order, you can also use the ASC keyword to specify explicitly.
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 a parameter is given, it indicates the maximum number of returned rows with offset 0. In other words, limit 5 and limit 0,5 are completely equivalent.
As for the meaning of the procedure keyword, I did not make it too clear, it seems to support stored procedures, and MySQL itself does not support stored procedures, it seems that it is reserved for future expansion needs.
