SELECT

Grammar Description

The SELECT statement is used to retrieve data from a table.

Grammar Structure

SELECT
    [ALL | DISTINCT ]
    select_expr [, select_expr] [[AS] alias] ...
    [INTO variable [, ...]]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC]] [ NULLS { FIRST | LAST } ]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [FOR {UPDATE}]

Syntax Explanation

The most commonly used clauses or conditions in the SELECT statement are as follows:

`all` & `distinct`

The all and distinct modifiers specify whether duplicate rows should be returned. all (default) specifies that all matching rows should be returned, including duplicate rows. distinct Specifies that duplicate rows are removed from the result set.

`select_expr`

Each select_expr expression represents the column you need to query and must have at least one select_expr.

The select_expr list contains columns indicating which columns to query the selected list. select_expr specifies columns, or use * to specify all query columns:

SELECT * FROM t1

tbl_name.* can be used to select all columns from the table:

SELECT t1.*, t2.* FROM t1

select_expr You can use AS to specify an alias for the table.

`table_references`

You can call the table in the default database tbl_name or db_name.tbl_name, mainly used to explicitly specify the database. You can refer to the column as col_name, tbl_name.col_name, or db_name.tbl_name.col_name. You do not need to specify tbl_name or db_name.tbl_name for the column, if you need to specify it explicitly, you can add tbl_name or db_name.tbl_name.
The table can be aliased using tbl_name AS alias_name or tbl_name alias_name.

`WHERE`

The WHERE clause (if given) indicates one or more conditions that must be met to select a row. where_condition expression, evaluates to true for each row to be selected. If there is no WHERE clause, the statement selects all rows.

`GROUP BY`

You can refer to selected columns in the ORDER BY and GROUP BY clauses using column names, column alias, or column positions.

Note

In the GROUP BY or HAVING clause, one alias is not allowed to define another alias.
In the GROUP BY or HAVING clause, first attempts to group or conditional filter using column names. If the corresponding column names are not found in these two clauses, they check if there is an alias matching it before using this alias.
When using alias in the GROUP BY or HAVING clause, you should avoid the situation where column references are vague, because when using alias in the GROUP BY or HAVING clause, it will look for columns that match the alias, and if multiple matching columns are found, an error will be generated.
The ORDER BY clause first tries to sort with an alias, and if the alias cannot be found, then try to sort with a column name.

`HAVING`

The HAVING clause is the same as the WHERE clause, specifying the selection criteria.

`ORDER BY`

ORDER BY defaults to ascending order; it can be specified explicitly using the ASC keyword. To sort in reverse order, add the (descending) keyword to the name of the column in the clause you sorted by DESC.

`LIMIT`

The LIMIT clause can be used to limit the number of rows returned by the SELECT statement.

`FOR UPDATE`

SELECT...FOR UPDATE is mainly used to lock a set of data rows in transaction processing to prevent modification by other concurrent transactions. This statement is most commonly used to handle the**read-change-write scenario, that is, when you need to read a set of data, make changes to it, and then write the result back to the database, and in the process you do not want other transactions to modify this set of data.

Use SELECT FOR UPDATE in a transaction to lock the selected row until the transaction ends (by commit or rollback). This way, other transactions that attempt to modify these rows will be blocked until the first transaction completes.

See the following example:

START TRANSACTION;

SELECT * FROM Orders
WHERE OrderID = 1
FOR UPDATE;

In the above transaction, the row with OrderID of 1 in the Orders table was selected using the SELECT FOR UPDATE statement and locked this row. This line cannot be modified by other transactions before the transaction ends. After you have completed the modification of this line, you can submit the transaction to release the lock:

UPDATE Orders
SET Quantity = Quantity - 1
WHERE OrderID = 1;

COMMIT;

The UPDATE statement above changes the Quantity value of the selected row, and then the COMMIT statement commits the transaction and releases the lock. At this time, other blocked transactions can continue to execute. If you decide not to make any changes, you can use the ROLLBACK statement to end the transaction and release the lock.

Example

create table t1 (spID int,userID int,score smallint);
insert into t1 values (1,1,1);
insert into t1 values (2,2,2);
insert into t1 values (2,1,4);
insert into t1 values (3,3,3);
insert into t1 values (1,1,5);
insert into t1 values (4,6,10);
insert into t1 values (5,11,99);
insert into t1 values (null,0,99);

mysql> SELECT spID FROM t1;
+------+
| spid |
+------+
| 1 |
| 2 |
| 2 |
| 3 |
| 1 |
| 4 |
| 5 |
| NULL |
+------+

mysql> SELECT DISTINCT spID FROM t1;
+------+
| spid |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| NULL |
+------+

mysql> SELECT * FROM t1 WHERE spID>2 AND userID <2 || userID >=2 OR userID <2 LIMIT 3;
+-------+--------+
| spid | userid | score |
+-------+--------+
| NULL | 0 | 99 |
| 1 | 1 | 1 |
| 2 | 2 | 2 |
+-------+--------+

mysql> SELECT userID,MAX(score) max_score FROM t1 WHERE userID <2 || userID > 3 GROUP BY userID ORDER BY max_score;
+--------------------------+
| userid | max_score |
+--------------------------+
| 1 | 5 |
| 6 | 10 |
| 0 | 99 |
| 11 | 99 |
+--------------------------+

mysql> select userID,count(score) from t1 group by userID having count(score)>1 order by userID;
+------------------------------+
| userid | count(score) |
+------------------------------+
| 1 | 3 |
+------------------------------+

mysql> select userID,count(score) from t1 where userID>2 group by userID having count(score)>1 order by userID;
Empty set (0.01 sec)s

mysql> select * from t1 order by spID asc nulls last;
+-------+--------+
| spid | userid | score |
+-------+--------+
| 1 | 1 | 1 |
| 1 | 1 | 5 |
| 2 | 2 | 2 |
| 2 | 1 | 4 |
| 3 | 3 | 3 |
| 4 | 6 | 10 |
| 5 | 11 | 99 |
| NULL | 0 | 99 |
+-------+--------+

limit

SELECT...FOR UPDATE Only single table queries are currently supported.
Partially supported INTO OUTFILE.
When the table name is DUAL, it is not supported to directly enter the corresponding database (USE DBNAME) and execute SELECT xx from DUAL. You can specify the database name to query the table DUAL using SELECT xx from DBNAME.DUAL.

SELECT

Grammar Description

Grammar Structure

Syntax Explanation

all & distinct

select_expr

table_references

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

FOR UPDATE