SELECT
SELECT 语句用于从 TiDB 读取数据。
概述
- SelectStmt
- SelectStmtBasic
- SelectStmtFromDualTable
- SelectStmtFromTable
- SelectStmtOpts
- TableRefsClause
- TableRef
- TableFactor
- JoinTable
- JoinClause
- AsOfClause
- SelectStmtLimit
- SelectLockOpt
- TableList
- WhereClause
- GroupByClause
- OrderBy
- WindowClause
- TableSample
SelectStmt ::=
( SelectStmtBasic | SelectStmtFromDualTable | SelectStmtFromTable )
OrderBy? SelectStmtLimit? SelectLockOpt? SelectStmtIntoOption
SelectStmtBasic ::=
"SELECT" SelectStmtOpts Field ("," Field)* ( "HAVING" Expression)?
SelectStmtFromDualTable ::=
"SELECT" SelectStmtOpts Field ("," Field)* "FROM" "DUAL" WhereClause?
SelectStmtFromTable ::=
"SELECT" SelectStmtOpts Field ("," Field)* "FROM" TableRefsClause
WhereClause? GroupByClause? ( "HAVING" Expression)? WindowClause?
SelectStmtOpts ::=
TableOptimizerHints DefaultFalseDistictOpt PriorityOpt SelectStmtSQLSmallResult
SelectStmtSQLBigResult SelectStmtSQLBufferResult SelectStmtSQLCache SelectStmtCalcFoundRows
SelectStmtStraightJoin
TableRefsClause ::=
TableRef ( ',' TableRef )*
TableRef ::=
TableFactor
| JoinTable
TableFactor ::=
TableName ( "PARTITION" "(" Identifier ("," Identifier)* ")" )? ("AS" TableAlias)? AsOfClause? TableSample?
JoinTable ::=
TableRef
(
("INNER" | "CROSS")? "JOIN" TableRef JoinClause?
| "STRAIGHT_JOIN" TableRef "ON" Expression
| ("LEFT" | "RIGHT") "OUTER"? "JOIN" TableRef JoinClause
| "NATURAL" ("LEFT" | "RIGHT") "OUTER"? "JOIN" TableFactor
)
JoinClause ::=
("ON" Expression
| "USING" "(" ColumnNameList ")" )
AsOfClause ::=
'AS' 'OF' 'TIMESTAMP' Expression
SelectStmtLimit ::=
("LIMIT" LimitOption ( ("," | "OFFSET") LimitOption )?
| "FETCH" ("FIRST" | "NEXT") LimitOption? ("ROW" | "ROWS") "ONLY" )
SelectLockOpt ::=
( 'FOR' 'UPDATE' ( 'OF' TableList )? 'NOWAIT'?
| 'LOCK' 'IN' 'SHARE' 'MODE' )
TableList ::=
TableName ( ',' TableName )*
WhereClause ::=
"WHERE" Expression
GroupByClause ::=
"GROUP" "BY" Expression
OrderBy ::=
"ORDER" "BY" Expression
WindowClause ::=
"WINDOW" WindowDefinition ("," WindowDefinition)*
TableSample ::=
'TABLESAMPLE' 'REGIONS' '(' ')'
语法元素说明
示例
SELECT
mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO t1 (c1) VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t1;
+----+----+
| id | c1 |
+----+----+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+----+----+
5 rows in set (0.00 sec)
mysql> SELECT AVG(s_quantity), COUNT(s_quantity) FROM stock TABLESAMPLE REGIONS();
+-----------------+-------------------+
| AVG(s_quantity) | COUNT(s_quantity) |
+-----------------+-------------------+
| 59.5000 | 4 |
+-----------------+-------------------+
1 row in set (0.00 sec)
mysql> SELECT AVG(s_quantity), COUNT(s_quantity) FROM stock;
+-----------------+-------------------+
| AVG(s_quantity) | COUNT(s_quantity) |
+-----------------+-------------------+
| 54.9729 | 1000000 |
+-----------------+-------------------+
1 row in set (0.52 sec)
以上示例使用 tiup bench tpcc prepare 生成的数据。第一个查询展示了 TABLESAMPLE 的用法。
SELECT ... INTO OUTFILE
SELECT ... INTO OUTFILE 语句用于将查询结果写入文件。
在语句中，可以通过以下子句指定输出文件的格式：
FIELDS TERMINATED BY：指定字段分隔符。例如，可以设置为','输出逗号分隔值（CSV），或'\t'输出制表符分隔值（TSV）。FIELDS ENCLOSED BY：指定包裹每个字段的字符。LINES TERMINATED BY：指定行终止符，如果希望以特定字符结束一行。
假设有一个表 t，包含三列，定义如下：
mysql> CREATE TABLE t (a INT, b VARCHAR(10), c DECIMAL(10,2));
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO t VALUES (1, 'a', 1.1), (2, 'b', 2.2), (3, 'c', 3.3);
Query OK, 3 rows affected (0.01 sec)
以下示例展示如何使用 SELECT ... INTO OUTFILE 将查询结果写入文件。
示例 1：
mysql> SELECT * FROM t INTO OUTFILE '/tmp/tmp_file1';
Query OK, 3 rows affected (0.00 sec)
此时，查询结果存放在 /tmp/tmp_file1，内容如下：
1 a 1.10
2 b 2.20
3 c 3.30
示例 2：
mysql> SELECT * FROM t INTO OUTFILE '/tmp/tmp_file2' FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Query OK, 3 rows affected (0.00 sec)
此时，查询结果存放在 /tmp/tmp_file2，内容如下：
"1","a","1.10"
"2","b","2.20"
"3","c","3.30"
示例 3：
mysql> SELECT * FROM t INTO OUTFILE '/tmp/tmp_file3'
-> FIELDS TERMINATED BY ',' ENCLOSED BY '\'' LINES TERMINATED BY '<<<\n';
Query OK, 3 rows affected (0.00 sec)
此时，查询结果存放在 /tmp/tmp_file3，内容如下：
'1','a','1.10'<<<
'2','b','2.20'<<<
'3','c','3.30'<<<
MySQL 兼容性
- 不支持
SELECT ... INTO @variable语法。 - 不支持
SELECT ... INTO DUMPFILE语法。 SELECT .. GROUP BY expr不会像 MySQL 5.7 那样隐含GROUP BY expr ORDER BY expr，而是遵循 MySQL 8.0 的行为，不隐含默认排序。SELECT ... TABLESAMPLE ...是 TiDB 的扩展，旨在兼容其他数据库系统和 ISO/IEC 9075-2 标准，但目前不被 MySQL 支持。