MySQL常用表结构查询语句

方法 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
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_ :
*************************** 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_ :

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)