MySQL :: MySQL 8.4 参考手册 :: 15.2.16 TABLE 语句

8.4 中文镜像

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

打开官方英文原文 ↗

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 15 条 SQL 语句 / 15.2 数据操作语句 / 15.2.16 TABLE 语句

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

15.2.16 表语句15.2.16 TABLE Statement

TABLE 是一个 DML 语句，用于返回命名表的行和列。TABLE is a DML statement which returns rows and columns of the named table.

TABLE table_name 
    [ORDER BY column_name] 
    [LIMIT number [OFFSET number]]
    [INTO OUTFILE 'file_name'
        [{FIELDS | COLUMNS}
            [TERMINATED BY 'string']
            [[OPTIONALLY] ENCLOSED BY 'char']
            [ESCAPED BY 'char']
        ]
        [LINES
            [STARTING BY 'string']
            [TERMINATED BY 'string']
        ]
    | INTO DUMPFILE 'file_name'
    | INTO var_name [, var_name] ...]

TABLE 语句在某些方面类似于 SELECT。鉴于存在一个名为 t，以下两个语句输出完全相同：The TABLE statement in some ways acts like SELECT. Given the existence of a table named t, the following two statements produce identical output:

TABLE t;

SELECT * FROM t;

你可以使用 TABLEORDER BY 和 ORDER BY。当这些子句与 SELECT（包括可选的 OFFSET 子句，带 LIMIT时，功能与相同子句相同，正如你在这里看到的：You can order and limit the number of rows produced by TABLE using ORDER BY and LIMIT clauses, respectively. These function identically to the same clauses when used with SELECT (including an optional OFFSET clause with LIMIT), as you can see here:

mysql> TABLE t;
+----+----+
| a  | b  |
+----+----+
|  1 |  2 |
|  6 |  7 |
|  9 |  5 |
| 10 | -4 |
| 11 | -1 |
| 13 |  3 |
| 14 |  6 |
+----+----+
7 rows in set (0.00 sec)

mysql> TABLE t ORDER BY b;
+----+----+
| a  | b  |
+----+----+
| 10 | -4 |
| 11 | -1 |
|  1 |  2 |
| 13 |  3 |
|  9 |  5 |
| 14 |  6 |
|  6 |  7 |
+----+----+
7 rows in set (0.00 sec)

mysql> TABLE t LIMIT 3;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
| 6 | 7 |
| 9 | 5 |
+---+---+
3 rows in set (0.00 sec)

mysql> TABLE t ORDER BY b LIMIT 3;
+----+----+
| a  | b  |
+----+----+
| 10 | -4 |
| 11 | -1 |
|  1 |  2 |
+----+----+
3 rows in set (0.00 sec)

mysql> TABLE t ORDER BY b LIMIT 3 OFFSET 2;
+----+----+
| a  | b  |
+----+----+
|  1 |  2 |
| 13 |  3 |
|  9 |  5 |
+----+----+
3 rows in set (0.00 sec)

TABLE 与 SELECT 在两个关键方面不同：TABLE differs from SELECT in two key respects:

TABLE 始终显示表的所有列。TABLE always displays all columns of the table.
Exception：TABLE 不包含可见列。参见第 15.1.20.10 节，“Invisible Columns”。Exception: The output of TABLE does not include invisible columns. See Section 15.1.20.10, “Invisible Columns”.
TABLE不允许任意过滤行;也就是说，TABLE不支持任何 WHERE子句。TABLE does not allow for any arbitrary filtering of rows; that is, TABLE does not support any WHERE clause.

For limiting which table columns are returned, filtering rows beyond what can be accomplished using ORDER BY以及LIMIT, or both, use SELECT.For limiting which table columns are returned, filtering rows beyond what can be accomplished using ORDER BY and LIMIT, or both, use SELECT.

TABLE 可以与临时表一起使用。TABLE can be used with temporary tables.

TABLE 也可以在许多其他构造中替代 SELECT，包括此处列出的构TABLE can also be used in place of SELECT in a number of other constructs, including those listed here:

造：通过集合运算符如 UNION，如此处所示：With set operators such as UNION, as shown here:
```
mysql> TABLE t1;
+---+----+
| a | b  |
+---+----+
| 2 | 10 |
| 5 |  3 |
| 7 |  8 |
+---+----+
3 rows in set (0.00 sec)

mysql> TABLE t2;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
| 3 | 4 |
| 6 | 7 |
+---+---+
3 rows in set (0.00 sec)

mysql> TABLE t1 UNION TABLE t2;
+---+----+
| a | b  |
+---+----+
| 2 | 10 |
| 5 |  3 |
| 7 |  8 |
| 1 |  2 |
| 3 |  4 |
| 6 |  7 |
+---+----+
6 rows in set (0.00 sec)
```
The UNION等价于 statement：The UNION just shown is equivalent to the following statement:
```
mysql> SELECT * FROM t1 UNION SELECT * FROM t2;
+---+----+
| a | b  |
+---+----+
| 2 | 10 |
| 5 |  3 |
| 7 |  8 |
| 1 |  2 |
| 3 |  4 |
| 6 |  7 |
+---+----+
6 rows in set (0.00 sec)
```
TABLE 也可以与 SELECT 语句、VALUES 语句，或两者结合使用。更多信息和示例请参见 Section 15.2.18， “UNION Clause”，第 15.2.4 节， “EXCEPT Clause”， and 第 15.2.8 节， “INTERSECT Clause”，另见第 15.2.14 节，“Set Operations with UNION， INTERSECT 和 EXCEPT”。TABLE can also be used together in set operations with SELECT statements, VALUES statements, or both. See Section 15.2.18, “UNION Clause”, Section 15.2.4, “EXCEPT Clause”, and Section 15.2.8, “INTERSECT Clause”, for more information and examples. See also Section 15.2.14, “Set Operations with UNION, INTERSECT, and EXCEPT”.
使用 INTO 来填充用户变量，INTO OUTFILE 或 INTO DUMPFILE，用于将表数据写入文件。参见第 15.2.13.1 节，“SELECT ...INTO 语句”，以获取更具体的信息和示例。With INTO to populate user variables, and with INTO OUTFILE or INTO DUMPFILE to write table data to a file. See Section 15.2.13.1, “SELECT ... INTO Statement”, for more specific information and examples.
在许多情况下，你可以使用子查询。给定任何表 t1，列名为 a，以及另一个表 t2，且只有一列，可以出现如下语句：In many cases where you can employ subqueries. Given any table t1 with a column named a, and a second table t2 having a single column, statements such as the following are possible:
```
SELECT * FROM t1 WHERE a IN (TABLE t2);
```
假设表 t1命名为 x，则前述与此处显示的语句等价（且无论哪种情况结果完全相同）：Assuming that the single column of table t1 is named x, the preceding is equivalent to each of the statements shown here (and produces exactly the same result in either case):
```
SELECT * FROM t1 WHERE a IN (SELECT x FROM t2);

SELECT * FROM t1 WHERE a IN (SELECT * FROM t2);
```
详见第 15.2.15 节，“子查询”。See Section 15.2.15, “Subqueries”, for more information.
使用 INSERT 和 REPLACE 语句，否则你会使用 SELECT *。参见第 15.2.7.1 节，“INSERT ...SELECT 语句”，了解更多信息和示例。With INSERT and REPLACE statements, where you would otherwise use SELECT *. See Section 15.2.7.1, “INSERT ... SELECT Statement”, for more information and examples.
TABLE“在许多情况下也可以用来替代 SELECT in CREATE TABLE ... SELECT 或 CREATE VIEW ... SELECT.请参阅这些语句的描述以获取更多信息和示例。TABLE can also be used in many cases in place of the SELECT in CREATE TABLE ... SELECT or CREATE VIEW ... SELECT. See the descriptions of these statements for more information and examples.

首页上一级下一节