Clever methods for understanding MySQL SELECT statements

The following articles mainly show you how to master MySQL SELECT statements. We all know that MySQL SELECT statements are becoming more and more popular with the wide application of MySQL databases, the following articles describe how to master the MySQL SELECT statement.

How to install and operate a MySQL database (the best combination with PHP)

How to back up and restore a MySQL database (the best combination with PHP)

VC Implementation and Application of MySQL (best combination with PHP) database interface (2)

How to back up a MySQL database (the best combination with PHP)

MySQL (the best combination with PHP): use Java to test the best combination of MySQL (and PHP) Databases

MySQL (best combination with PHP) database technology 01)

How to connect to MySQL (the best combination with PHP) database in ASP

The easiest way to back up a MySQL database (the best combination with PHP)

MySQL (best combination with PHP) database backup and recovery

MySQL (the best combination with PHP) has too many database connections.

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

The basic syntax of the SELECT statement in MySQL (the best combination with PHP) 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 operation supported by MySQL (the best combination with PHP). For example: SELECT 1 + 1, it returns 2. Second, you can also use it to assign values to variables. in PHP, using the SELECT statement function, you can freely use MySQL (the best combination with PHP) and assign values to variables.

In many cases, you will find that MySQL (the best combination with PHP) has many more powerful functions than PHP.

STRAIGHT_JOIN, SQL _SMALL_RESULT, SQL _BIG_RESULT, and HIGH_PRIORITY are extensions of ANSI SQL92 in MySQL (the best combination with PHP. 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 (the best combination with PHP) is required to use a temporary table to store the final table instead of using the sorting. Otherwise, SQL _BIG_RESULT tells the optimizer that the result will be very small, mySQL (the best combination with PHP) is required to use sorting instead of temporary tables.

HIGH_PRIORITY will give MySQL SELECT a higher priority than a statement for updating a 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 (the best combination with PHP.

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 in the export_options section of the statement is the same as that in the FIELDS and LINES clauses used in the load datainfile statement. We will use MySQL (the best combination with PHP) in the advanced _ load data section, we will discuss it in detail. 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 a search condition. Its Application Methods in insert, update, and delete statements are the same as those in MySQL select statements. 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

The above content is an introduction to quick understanding of MySQL SELECT statements.