共计 2141 个字符，预计需要花费 6 分钟才能阅读完成。
在我们使用数 MySQL 据库进行查询或者建表时，经常需要查看表结构，下面以 employees 数据库中的 departments 表为例进行表结构查询：
方法 1：DESC departments;
方法 2：DESCRIBE departments;
方法 3：SHOW COLUMNS FROM departments;
以上三种方法的查询结果相同：
Field Type Null Key Default Extra
dept_no char(4) NO PRI
dept_name varchar(40) NO UNI
方法 4：借用 MySQL 自身的 information_schema 数据库，输入如下指令：
— 方法 4
SELECT *
FROM information_schema.COLUMNS
WHERE table_schema = ’employees’ AND table_name = ‘departments’;
— 方法 4 简化版（需要处于 information_schema 数据库内）
SELECT * FROM COLUMNS
WHERE table_name = ‘departments’;
查询结果如下：
mysql> SELECT *
-> FROM information_schema.COLUMNS
-> WHERE table_schema = ’employees’ AND table_name = ‘departments’\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: employees
TABLE_NAME: departments
COLUMN_NAME: dept_no
ORDINAL_POSITION: 1
COLUMN_DEFAULT: NULL
IS_NULLABLE: NO
DATA_TYPE: char
CHARACTER_MAXIMUM_LENGTH: 4
CHARACTER_OCTET_LENGTH: 12
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8
COLLATION_NAME: utf8_general_ci
COLUMN_TYPE: char(4)
COLUMN_KEY: PRI
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: employees
TABLE_NAME: departments
COLUMN_NAME: dept_name
ORDINAL_POSITION: 2
COLUMN_DEFAULT: NULL
IS_NULLABLE: NO
DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 40
CHARACTER_OCTET_LENGTH: 120
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8
COLLATION_NAME: utf8_general_ci
COLUMN_TYPE: varchar(40)
COLUMN_KEY: UNI
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
建表信息查询：show create table departments\G
mysql> show create table departments\G
*************************** 1. row ***************************
Table: departments
Create Table: CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`),
UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
：
