# 列出表

在本教程中，您将学习如何使用MySQL `SHOW TABLES`命令查询特定数据库中的表。

要在MySQL数据库中列出所有表，请按照下列步骤操作：

1. 使用MySQL客户端(如`mysql`)登录到MySQL数据库服务器
2. 使用`USE`语句切换到特定的数据库。
3. 使用`SHOW TABLES`命令。

下面说明了MySQL `SHOW TABLES`命令的语法：

```sql
SHOW TABLES;
```

## MySQL SHOW TABLES示例

以下示例说明如何列出`yiibaidb`数据库中的所有表。

*步骤1* - 连接到MySQL数据库服务器：

```sql
C:\Users\Administrator>mysql -u root -p
```

*步骤2* -切换到`yiibaidb`数据库：

```sql
mysql> USE yiibaidb;
Database changed
```

*步骤3* - 显示`yiibaidb`数据库中的所有表：

```sql
mysql> show tables;
+--------------------+
| Tables_in_yiibaidb |
+--------------------+
| aboveavgproducts   |
| article_tags       |
| bigsalesorder      |
| contacts           |
| customerorders     |
| customers          |
| departments        |
| employees          |
| employees_audit    |
| officeinfo         |
| offices            |
| offices_bk         |
| offices_usa        |
| orderdetails       |
| orders             |
| organization       |
| payments           |
| price_logs         |
| productlines       |
| products           |
| saleperorder       |
| user_change_logs   |
| v_contacts         |
| vps                |
+--------------------+
24 rows in set
```

`SHOW TABLES`命令可显示表是基表还是视图。 要在结果中包含表类型，请使用`SHOW TABLES`语句，如下所示 -

```sql
SHOW FULL TABLES;
```

执行上面语句，如下所示 -

```sql
mysql> SHOW FULL TABLES;
+--------------------+------------+
| Tables_in_yiibaidb | Table_type |
+--------------------+------------+
| aboveavgproducts   | VIEW       |
| article_tags       | BASE TABLE |
| bigsalesorder      | VIEW       |
| contacts           | BASE TABLE |
| customerorders     | VIEW       |
| customers          | BASE TABLE |
| departments        | BASE TABLE |
| employees          | BASE TABLE |
| employees_audit    | BASE TABLE |
| officeinfo         | VIEW       |
| offices            | BASE TABLE |
| offices_bk         | BASE TABLE |
| offices_usa        | BASE TABLE |
| orderdetails       | BASE TABLE |
| orders             | BASE TABLE |
| organization       | VIEW       |
| payments           | BASE TABLE |
| price_logs         | BASE TABLE |
| productlines       | BASE TABLE |
| products           | BASE TABLE |
| saleperorder       | VIEW       |
| user_change_logs   | BASE TABLE |
| v_contacts         | VIEW       |
| vps                | VIEW       |
+--------------------+------------+
24 rows in set
```

我们在`yiibaidb`数据库中创建一个名为`view_contacts`的视图，其中包括来自`employees`和`customers`表的名字，姓氏和电话。

```sql
CREATE VIEW view_contacts 
AS 
SELECT lastName, firstName, extension as phone 
FROM employees 
UNION
SELECT contactFirstName, contactLastName, phone 
FROM customers;
```

现在，执行查询`SHOW FULL TABLES`命令：

```sql
mysql> SHOW FULL TABLES;
+--------------------+------------+
| Tables_in_yiibaidb | Table_type |
+--------------------+------------+
| aboveavgproducts   | VIEW       |
| article_tags       | BASE TABLE |
| bigsalesorder      | VIEW       |
| contacts           | BASE TABLE |
| customerorders     | VIEW       |
| customers          | BASE TABLE |
| departments        | BASE TABLE |
| employees          | BASE TABLE |
| employees_audit    | BASE TABLE |
| officeinfo         | VIEW       |
| offices            | BASE TABLE |
| offices_bk         | BASE TABLE |
| offices_usa        | BASE TABLE |
| orderdetails       | BASE TABLE |
| orders             | BASE TABLE |
| organization       | VIEW       |
| payments           | BASE TABLE |
| price_logs         | BASE TABLE |
| productlines       | BASE TABLE |
| products           | BASE TABLE |
| saleperorder       | VIEW       |
| user_change_logs   | BASE TABLE |
| v_contacts         | VIEW       |
| view_contacts      | VIEW       |
| vps                | VIEW       |
+--------------------+------------+
25 rows in set
```

您可以看到，`v_contacts`,`view_contacts`,`vps`等是视图(*VIEW*)，而其它表则都是基表(*BASE TABLE*)。

对于具有很多表的数据库，一次显示所有表可能不免直观。

幸运的是，`SHOW TABLES`命令提供了一个选项，允许使用[LIKE](http://www.yiibai.com/mysql/like.html)运算符或[WHERE](http://www.yiibai.com/mysql/where.html)子句中的表达式对返回的表进行过滤，如下所示：

```sql
SHOW TABLES LIKE pattern;

SHOW TABLES WHERE expression;
```

例如，要显示`yiibaidb`数据库中以字母`p`开头的所有表，请使用以下语句：

```sql
mysql> SHOW TABLES LIKE 'p%';
+-------------------------+
| Tables_in_yiibaidb (p%) |
+-------------------------+
| payments                |
| price_logs              |
| productlines            |
| products                |
+-------------------------+
4 rows in set
```

或者显示以’`es`‘字符串结尾的表，可使用以下语句：

```sql
mysql> SHOW TABLES LIKE '%es';
+--------------------------+
| Tables_in_yiibaidb (%es) |
+--------------------------+
| employees                |
| offices                  |
| productlines             |
+--------------------------+
3 rows in set
```

以下语句说明了如何在`SHOW TABLES`语句中使用`WHERE`子句列出`yiibai`数据库中的所有视图 -

```sql
mysql> SHOW FULL TABLES WHERE table_type = 'VIEW';
+--------------------+------------+
| Tables_in_yiibaidb | Table_type |
+--------------------+------------+
| aboveavgproducts   | VIEW       |
| bigsalesorder      | VIEW       |
| customerorders     | VIEW       |
| officeinfo         | VIEW       |
| organization       | VIEW       |
| saleperorder       | VIEW       |
| v_contacts         | VIEW       |
| view_contacts      | VIEW       |
| vps                | VIEW       |
+--------------------+------------+
9 rows in set
```

有时，希望看到非当前使用的数据库中的表。可以使用`SHOW TABLES`语句的`FROM`子句来指定要显示表的数据库。

以下示例演示如何显示以’`time`‘开头的表;

```sql
mysql> SHOW TABLES FROM mysql LIKE 'time%';
+---------------------------+
| Tables_in_mysql (time%)   |
+---------------------------+
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
+---------------------------+
5 rows in set
```

以下语句相当于上面的语句，但它使用`IN`而不是`FROM`子句，如下所示 -

```sql
mysql> SHOW TABLES IN mysql LIKE 'time%';
+---------------------------+
| Tables_in_mysql (time%)   |
+---------------------------+
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
+---------------------------+
5 rows in set
```

请注意，如果您没有基表或视图的权限，则它不会显示在`SHOW TABLES`命令的结果集中。

在本教程中，您已经学习了如何使用MySQL `SHOW TABLES`语句列出指定数据库中的所有表。


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://hezhiqiang-book.gitbook.io/mysql/di-ba-zhang/lie-chu-biao.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.