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

方法 1DESC departments; 方法 2:DESCRIBE departments; 方法 3:SHOW COLUMNS FROM departments;

以上三种方法的查询结果相同:

SRE实战 互联网时代守护先锋,助力企业售后服务体系运筹帷幄!一键直达领取阿里云限量特价优惠。
mysql> DESC departments;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_no   | char(4)     | NO   | PRI | NULL    |       |
| dept_name | varchar(40) | 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)

 

扫码关注我们
微信号:SRE实战
拒绝背锅 运筹帷幄