MySQL常用表结构查询语句

在我们使用数据库进行查询或者建表时，经常需要查看表结构，下面以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)

巴特西

MySQL常用表结构查询语句

最新文章

热门文章