# 列出表的列

在本教程中，您将学习如何使用`DESCRIBE`语句和MySQL `SHOW COLUMNS`命令来显示表的列。

## 使用DESCRIBE语句

要显示表的所有列，请使用以下步骤：

1. 登录到MySQL数据库服务器
2. 切换到特定数据库(使用`USE`语句)
3. 使用`DESCRIBE`语句

以下示例演示如何在`yiibaidb`数据库中显示`orders`表的所有列。

*步骤1* - 登录到MySQL数据库。

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

*步骤2* - 发出`USE`命令将数据库切换到`yiibaidb`数据库：

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

*步骤3* - 使用`DESCRIBE`语句，得到以下结果 -

```sql
mysql> DESCRIBE orders;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber    | int(11)     | NO   | PRI | NULL    |       |
| orderDate      | date        | NO   |     | NULL    |       |
| requiredDate   | date        | NO   |     | NULL    |       |
| shippedDate    | date        | YES  |     | NULL    |       |
| status         | varchar(15) | NO   |     | NULL    |       |
| comments       | text        | YES  |     | NULL    |       |
| customerNumber | int(11)     | NO   | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
7 rows in set
```

实际上，一般使用`DESC`语句，它是`DESCRIBE`语句的缩写。 例如，以下语句等同于上面的`DESCRIBE`，如下所示 -

```sql
mysql> DESC orders;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber    | int(11)     | NO   | PRI | NULL    |       |
| orderDate      | date        | NO   |     | NULL    |       |
| requiredDate   | date        | NO   |     | NULL    |       |
| shippedDate    | date        | YES  |     | NULL    |       |
| status         | varchar(15) | NO   |     | NULL    |       |
| comments       | text        | YES  |     | NULL    |       |
| customerNumber | int(11)     | NO   | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
7 rows in set
```

## MySQL SHOW COLUMNS命令

获取表中列的更灵活的方法是使用MySQL `SHOW COLUMNS`命令。

```sql
SHOW COLUMNS FROM table_name;
```

要显示表的列，请在`SHOW COLUMNS`语句的`FROM`子句中指定表名。要显示指定数据库中不是当前数据库中的表的列定义信息，请使用以下形式：

```sql
SHOW COLUMNS FROM database_name.table_name;
-- 例如
SHOW COLUMNS FROM mysql.user;
```

或者 -

```sql
SHOW COLUMNS FROM table_name IN database_name;
-- 例如
SHOW COLUMNS FROM user IN mysql;
```

例如，要获取`orders`表的列，请使用`SHOW COLUMNS`语句，如下所示：

```sql
SHOW COLUMNS FROM orders;
```

您可以看到这个`SHOW COLUMNS`命令的结果与`DESC`语句的结果相同。

要获取有关列的更多信息，请将`FULL`关键字添加到`SHOW COLUMNS`命令中，如下所示：

```sql
SHOW FULL COLUMNS FROM table_name;
```

例如，以下语句列出了`yiibaidb`数据库中的`payments`表的所有列。

```sql
mysql> SHOW FULL COLUMNS FROM payments \G;
*************************** 1. row ***************************
     Field: customerNumber
      Type: int(11)
 Collation: NULL
      Null: NO
       Key: PRI
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 2. row ***************************
     Field: checkNumber
      Type: varchar(50)
 Collation: utf8_general_ci
      Null: NO
       Key: PRI
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 3. row ***************************
     Field: paymentDate
      Type: date
 Collation: NULL
      Null: NO
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 4. row ***************************
     Field: amount
      Type: decimal(10,2)
 Collation: NULL
      Null: NO
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:
4 rows in set (0.02 sec)

ERROR:
No query specified

mysql>
```

如您所见，`SHOW FULL COLUMNS`命令将排序规则，权限和注释列添加到结果集中。

`SHOW COLUMNS`命令允许使用[LIKE](http://www.yiibai.com/mysql/like.html)运算符或[WHERE](http://www.yiibai.com/mysql/where.html)子句来过滤表的列：

```sql
SHOW COLUMNS FROM table_name LIKE pattern;

SHOW COLUMNS FROM table_name WHERE expression;
```

例如，要显示`payments`表中以字母`c`开头的列，请使用`LIKE`运算符，如下所示：

```sql
mysql> SHOW COLUMNS FROM payments LIKE 'c%';
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| customerNumber | int(11)     | NO   | PRI | NULL    |       |
| checkNumber    | varchar(50) | NO   | PRI | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
```

在本教程中，您已经学会了如何使用MySQL `SHOW COLUMNS`命令和`DESC`语句来显示表的列。


---

# 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-de-lie.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.