mysql查看表结构和表创建语命令

本文详细介绍了如何使用SQL查询数据库表的结构、创建表、以及通过特定条件筛选和获取表中信息的方法。同时,提供了从信息表中选择列的具体步骤,并通过实际案例展示了查询过程。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

1,desc tablename;

mysql> desc authors;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| email | varchar(100) | NO   | UNI | NULL    |                |
| name  | varchar(100) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set

2,show create table tablename; 

mysql> show create table authors;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                           |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| authors | CREATE TABLE `authors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(100) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

3,select * from columns where table_name='表名'


mysql> use information_schema;
Database changed
mysql> select * from columns where table_name='authors';  
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-----------------+--------------+------------+----------------+---------------------------------+----------------+
| 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           | zqad         | authors    | id          |                1 | NULL           | NO          | int       | NULL                     | NULL                   |                10 |             0 | NULL               | NULL            | int(11)      | PRI        | auto_increment | select,insert,update,references |                |
| def           | zqad         | authors    | email       |                2 | NULL           | NO          | varchar   |                      100 |                    300 | NULL              | NULL          | utf8               | utf8_general_ci | varchar(100) | UNI        |                | select,insert,update,references |                |
| def           | zqad         | authors    | name        |                3 | NULL           | NO          | varchar   |                      100 |                    300 | NULL              | NULL          | utf8               | utf8_general_ci | varchar(100) |            |                | select,insert,update,references |                |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-----------------+--------------+------------+----------------+---------------------------------+----------------+
3 rows in set

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值