Mysql 简明教程
MySQL - Show Columns
MySQL Show Columns Statement
要检索表的全部信息，我们使用 DESCRIBE、DESC 或 SHOW COLUMNS 语句。
To retrieve entire information of a table, we use DESCRIBE, DESC or SHOW COLUMNS statements.
MySQL 的所有这些语句都可用于检索/显示表的所有列的描述信息，因为它们都检索相同的结果集。
All of these statements of MySQL can be used to retrieve/display the description of all the columns of a table, as they all retrieve the same result-sets.
获取列信息在多种情况下很有用，例如根据列数据类型向表中插入值、更新或删除列，或者只是简单地了解表的结构。
Obtaining column information can be useful in several situations like inserting values into a table (based on the column datatype), updating or dropping a column, or to just simply know a table’s structure.
在本章中，让我们详细了解如何使用 SHOW COLUMNS 语句。
In this chapter, let us understand how to use SHOW COLUMNS statement in detail.
Syntax
以下是 MySQL SHOW COLUMNS 语句的语法：
Following is the syntax of the MySQL SHOW COLUMNS Statement −
SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]Example
我们首先使用以下查询创建名为 TUTORIALS 的数据库：
Let us start with creating a database named TUTORIALS using the below query −
CREATE DATABASE TUTORIALS;执行以下语句以切换至 TUTORIALS 数据库：
Execute the following statement to change into TUTORIALS database −
USE TUTORIALS;在以下查询中，我们使用以下 CREATE TABLE 语句创建名为 CUSTOMERS 的表：
In the following query, we are creating a table named CUSTOMERS using the following CREATE TABLE statement −
CREATE TABLE CUSTOMERS (
ID INT AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);现在，我们使用 SHOW COLUMNS 语句检索有关 CUSTOMERS 表的列的信息：
Now, we are using the SHOW COLUMNS statement to retrieve the information about columns of the CUSTOMERS table −
SHOW COLUMNS FROM CUSTOMERS;Example
我们也可以使用 IN clause 来代替 FROM，如下所示 -
We can also use the IN clause instead of FROM as shown in the query below −
SHOW COLUMNS IN CUSTOMERS;Output
正如我们看到的输出，它与之前的输出完全相同。
As we can obeserve the output, it is exactly the same as the previous output.
Example
我们可以指定 the name of the database 以及表名，如下所示 -
We can specify the name of the database along with the table name as shown in the query below −
SHOW COLUMNS IN CUSTOMERS FROM TUTORIALS;Output
以下是 TUTORIALS 数据库中 CUSTOMERS 表中列的信息。
Following is the information of columns in CUSTOMERS table that is present in TUTORIALS database.
The LIKE clause
在 MySQL 中，使用 LIKE 子句，您可以指定一个模式来检索关于特定列的信息。
In MySQL, using the LIKE clause, you can specify a pattern to retrieve info about specific columns.
The WHERE clause
我们可以使用 SHOW COLUMNS 语句的 MySQL WHERE 子句来检索与指定条件匹配的列的信息。
We can use the MySQL WHERE clause of the SHOW COLUMNS statements to retrieve information about the columns which match the specified condition.
The FULL clause
通常，SHOW COLUMNS 语句提供的信息包含字段类型、是否可以为 null、键、默认值以及一些额外的详细信息。如果你使用全子句 details，将添加校对、权限和注释。
Usually, the information provided by the SHOW COLUMNS statements contains field type, can be null or not, key, default values and some extra details. If you use the full clause details like collation, privileges and comments will be added.
Example
在下面的示例中，我们使用带有 SHOW COLUMNS 的 FULL 子句来检索 CUSTOMERS 表的额外详细信息 -
In the following example, we are using the FULL clause with SHOW COLUMNS to retrieve extra details of the CUSTOMERS table −
SHOW FULL COLUMNS IN CUSTOMERS FROM tutorials;执行上述查询将产生以下输出：
Executing the query above will produce the following output −
Showing Columns of a table Using a Client Program
除了使用 MySQL 查询显示 MySQL 数据库中表的列之外，我们还可以使用客户端程序来执行 SHOW COLUMNS 操作。
Besides showing the columns of a table in a MySQL database with a MySQL query, we can also use a client program to perform the SHOW COLUMNS operation.