Quick understanding of SELECT statements in MySQL Databases

Source: Internet
Author: User
Tags mysql functions
This article provides a quick and detailed understanding of the SELECT statements in the MySQL database. The basic syntax of the SELECT statement in MySQL is as follows: SELECT [STRAIGHT_JOIN] [SQL _SMALL_RESULT] [SQL _BIG_RESULT] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL] select_list [INTO {OUTFILE | DUMPFILE} fi

This article provides a quick and detailed understanding of the SELECT statements in the MySQL database. The basic syntax of the SELECT statement in MySQL is as follows: SELECT [STRAIGHT_JOIN] [SQL _SMALL_RESULT] [SQL _BIG_RESULT] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL] select_list [INTO {OUTFILE | DUMPFILE} 'fi

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

The basic syntax of the SELECT statement in MySQL 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 operations supported by MySQL. For example, SELECT 1 + 1 returns 2. Second, you can also use it to assign values to variables. in PHP, with this feature of SELECT statements, you can freely 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 MySQL to ANSI SQL92. 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 is required to use a temporary table to store the final table instead of using sorting. On the contrary, SQL _BIG_RESULT tells the optimizer that the result will be very small and MySQL is required to use sorting instead of creating.

HIGH_PRIORITY will give the SELECT statement a higher priority than the statement for updating the 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.

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 of the export_options section in the statement is the same as that in the FIELDS and LINES clauses used in the load datainfile statement. We will discuss it in detail in the MySQL advanced _ load data section. 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 the search condition. The method used in the insert, update, and delete statements is the same as that used in the select statement. 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, 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

Is null: used to search 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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.