在我们使用数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)