查看mysql表結構和表建立語句的方法

標籤：mysql資料庫

首先展示一下建立資料庫時的語句：

mysql>create table worker(      id char(11) primary key,      name varchar(20),      work varchar(20),      part varchar(20),      sex enum(‘男‘,‘女‘) not null,      birth date      );

這是剛開始的時候建立的語句。以上為例介紹查看mysql中表結構：

1.desc 表名

例如：要查看worker資料表的表結構，先進入資料表所在的資料庫，然後執行下面語句：

mysql>desc worker;

得到的結果：

 +-------+-------------+------+-----+---------+-------+ | Field | Type        | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name  | varchar(20) | YES  |     | NULL    |       | | sex   | enum(‘      | NO   |     | NULL    |       | | work  | varchar(20) | YES  |     | NULL    |       | | part  | varchar(20) | YES  |     | NULL    |       | | id    | char(11)    | NO   | PRI | NULL    |       | | birth | date        | YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+

2.show create table 表名

例如：mysql>show create table worker;  #注意：這裡同樣要求進入到表所在的資料庫當中；

得到的結果：

+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table  | Create Table                                                                                                                                                                                                                                                                        |+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| worker | CREATE TABLE `worker` (  `name` varchar(20) DEFAULT NULL,  `sex` enum(‘男‘,‘女‘) NOT NULL,  `work` varchar(20) DEFAULT NULL,  `part` varchar(20) DEFAULT NULL,  `id` char(11) NOT NULL,  `birth` date DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8    |+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

可以看到跟輸入的是不一樣的，得到的更加詳細

3.show columns from 表名

例如：mysql>show columns from worker;  #注意：這裡同樣要求進入到表所在的資料庫當中；

mysql> show columns from worker;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| name  | varchar(20) | YES  |     | NULL    |       || sex   | enum(‘      | NO   |     | NULL    |       || work  | varchar(20) | YES  |     | NULL    |       || part  | varchar(20) | YES  |     | NULL    |       || id    | char(11)    | NO   | PRI | NULL    |       || birth | date        | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+

4.在information_schema庫中查詢

use information_schema;

select * from columns where table_name=‘表名‘;

如下： 

mysql> use information_schema;Database changedmysql> select * from columns where table_name=‘worker‘;+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-----------------+-------------+------------+-------+---------------------------------+----------------+| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION_NAME  | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES                      | COLUMN_COMMENT |+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-----------------+-------------+------------+-------+---------------------------------+----------------+| def           | demo         | worker     | name        |                1 | NULL           | YES         | varchar   |                       20 |                     60 |              NULL |          NULL | utf8               | utf8_general_ci | varchar(20) |            |       | select,insert,update,references |                || def           | demo         | worker     | sex         |                2 | NULL           | NO          | enum      |                        2 |                      6 |              NULL |          NULL | utf8               | utf8_general_ci | enum(‘      |            |       | select,insert,update,references |                || def           | demo         | worker     | work        |                3 | NULL           | YES         | varchar   |                       20 |                     60 |              NULL |          NULL | utf8               | utf8_general_ci | varchar(20) |            |       | select,insert,update,references |                || def           | demo         | worker     | part        |                4 | NULL           | YES         | varchar   |                       20 |                     60 |              NULL |          NULL | utf8               | utf8_general_ci | varchar(20) |            |       | select,insert,update,references |                || def           | demo         | worker     | id          |                5 | NULL           | NO          | char      |                       11 |                     33 |              NULL |          NULL | utf8               | utf8_general_ci | char(11)    | PRI        |       | select,insert,update,references |                || def           | demo         | worker     | birth       |                6 | NULL           | YES         | date      |                     NULL |                   NULL |              NULL |          NULL | NULL               | NULL            | date        |            |       | select,insert,update,references |                |+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-----------------+-------------+------------+-------+---------------------------------+----------------+

查看mysql表結構和表建立語句的方法