# **SELECT**

## **Grammar Description**

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

## **Grammar Structure**

``` sql
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:

```sql
SELECT * FROM t1
```

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

```sql
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:

```sql
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:

```sql
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**

```text
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`.