在我們使用資料庫進行查詢或者建表時,經常需要查看表結構,下麵以employees資料庫中的departments表為例進行表結構查詢: 以上三種方法的查詢結果相同: 方法 4: 借用MySQL自身的information_schema資料庫,輸入如下指令: 查詢結果如下: 建表信息查詢 : show ...
在我們使用資料庫進行查詢或者建表時,經常需要查看表結構,下麵以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)