Shulou(Shulou.com)06/01 Report--

The basic syntax of the SELECT statement in MySQL Advanced SELECT (turn) [@ more@] MySQL is as follows:

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 accomplish many of the functions you expect. First of all, you can use it to perform any operation supported by MySQL, such as: SELECT 1 operations 1, which will return 2. Second, you can also use it to assign values to variables, and in PHP, using this function of SELECT statements, you are free to 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 ANSI SQL92 to MySQL. 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 results will be small, requiring MySQL to use temporary tables to store the final table instead of sorting; conversely, SQL_BIG_RESULT tells the optimizer that the results will be small, requiring MySQL to use sorting instead of temporary tables.

HIGH_PRIORITY will give SELECT higher priority than a statement that updates the table, allowing it to make a priority and fast query.

The use of the above four keywords is indeed rather obscure. Fortunately, in the vast majority of 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-repeating rows. It should be noted here that for the keywords DISTINCT and DISTINCTROW, null values are equal, no matter how many null values there are, select only one. The use of ALL is suspected of being superfluous. It has no effect on the generation of the result set.

INTO {OUTFILE | DUMPFILE} 'file_name' export_options to write the result set to a file. The file is created on the server host and cannot already exist. 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 article. The difference between OUTFILE and DUMPFILE keywords is that only one line is written to the file before it, and there are no columns or lines ending.

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

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

2. A list of column names arranged in the order desired by the user.

3. You can replace the column name with an alias as follows: 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 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 select_list does not contain column names (for example, only constants, arithmetic expressions, and so on). If there are multiple tables in the table item, separate them with a comma. The order of the tables after the keyword FROM does not affect the result.

The table name can be given an alias to make the expression clear. The syntax here is tbl_name [AS] alias_name. For example:

Select t1.name from employee as T2 where t1.name=t2.name is completely equivalent to select t1.name from employee T2 where t1.name=t2.name and select t1.name from employee T1 where t1.name=t2.name.

All other references to the table, such as in the where clause and the having clause, use aliases, which cannot start with a number.

The where clause sets the search criteria, and it is applied in insert,update,delete statements in exactly the same way as in select statements. The search criteria follow the keyword where. If the user wants to use multiple search criteria in the statement, you can use and or or to connect. The basic syntax for 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 join two conditions and return a result when both conditions are TRUE. When using multiple logical operators in the same statement, the and operator always takes precedence unless the user changes the order of operations with parentheses.

Or: used to join two conditions and return a result when either of the 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: the keyword used to identify the lower limit of the range. And is 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 specify the name of the table where the column is located.

Comparison_operator: comparison operator. See the following table:

Symbolic meaning

= equal to

> greater than

= greater than or equal to

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.