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 changed

mysql> 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表结构和表创建语句的方法

标签：mysql数据库

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://11906658.blog.51cto.com/11896658/1865107