在我们使用数据库进行查询或者建表时，经常需要查看表结构，下面以employees数据库中的departments表为例进行表结构查询：
departments表：（2列9行）
+---------+--------------------+
| dept_no | dept_name |
+---------+--------------------+
| d009 | Customer Service |
| d005 | Development |
| d002 | Finance |
| d003 | Human Resources |
| d001 | Marketing |
| d004 | Production |
| d006 | Quality Management |
| d008 | Research |
| d007 | Sales |
+---------+--------------------+
方法 1：DESC departments; 方法 2：DESCRIBE departments; 方法 3：SHOW COLUMNS FROM departments;
以上三种方法的查询结果相同：
mysql> DESC departments;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_no | char() | NO | PRI | NULL | |
| dept_name | varchar() | NO | UNI | NULL | |
+-----------+-------------+------+-----+---------+-------+
方法 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:
方法 5： 表信息查询，输入如下指令：
SHOW TABLE STATUS LIKE '%departments%';
查询结果：
*************************** 1. row ***************************
Name: departments
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 9
Avg_row_length: 1820
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: NULL
Create_time: 2018-12-05 20:37:56
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
方法 6：建表信息查询 ，输入如下指令：
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)