> For the complete documentation index, see [llms.txt](https://hezhiqiang-book.gitbook.io/mysql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://hezhiqiang-book.gitbook.io/mysql/di-ba-zhang/lie-chu-biao.md).

# 列出表

在本教程中，您将学习如何使用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`语句列出指定数据库中的所有表。