How to query the table structure in mysql

Mysql query table structure method: 1. Use the "DESCRIBE table name;" statement, the table structure will be displayed in the form of a table; 2. Use the "SHOW CREATE TABLE table name;" statement, the SQL statement will be displayed to display the table structure.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

After creating the data table, you often need to check the table structure (table information). In MySQL, you can use the DESCRIBE and SHOW CREATE TABLE commands to view the structure of a data table.

DESCRIBE: Display the table structure in the form of a table

The DESCRIBE/DESC statement will display the field information of the table in the form of a table, including Field name, field data type, whether it is a primary key, whether there is a default value, etc. The syntax format is as follows:

DESCRIBE 表名;

or abbreviated as:

DESC 表名;

[Example 1] Use DESCRIBE and DESC to view the table respectively The table structure, SQL statements and running results of tb_emp1 are as follows:

mysql> DESCRIBE tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.14 sec)

mysql> DESC tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.14 sec)

Among them, the meaning of each field is as follows:

• Null: Indicates whether the column can be stored NULL value.

• Key: Indicates whether the column is indexed. PRI means that the column is part of the table's primary key, UNI means that the column is part of a UNIQUE index, and MUL means that a given value is allowed to appear multiple times in the column.

• Default: Indicates whether the column has a default value, and if so, what is the value.

• Extra: Indicates additional information related to a given column that can be obtained, such as AUTO_INCREMENT, etc.

SHOW CREATE TABLE: Display the table structure in the form of SQL statements

SHOW CREATE TABLE command will display the table structure in the form of SQL statements to display table information. Compared with DESCRIBE, SHOW CREATE TABLE displays richer content. It can view the storage engine and character encoding of the table; in addition, you can also control the display format through the \g or \G parameters.

The syntax format of SHOW CREATE TABLE is as follows:

SHOW CREATE TABLE 表名;

Add the \g or \G parameter at the end of the SHOW CREATE TABLE statement (before the semicolon) to change the display form.

[Example 2] Use the SHOW CREATE TABLE statement to view the detailed information of table tb_emp1. Use the \g ending once and do not use it once:

mysql> SHOW CREATE TABLE tb_emp1;
+---------+------------------------------------------------+
| Table   | Create Table                                   |
+---------+------------------------------------------------+
| tb_emp1 | CREATE TABLE `tb_emp1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL,
  `salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+---------+------------------------------------------------+
1 row in set (0.01 sec)

mysql> SHOW CREATE TABLE tb_emp1 \g;
+---------+------------------------------------------------+
| Table   | Create Table                                   |
+---------+------------------------------------------------+
| tb_emp1 | CREATE TABLE `tb_emp1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL,
  `salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+---------+------------------------------------------------+
1 row in set (0.00 sec)

SHOW CREATE TABLE Use# The SQL statement and running results ending in ##\G are as follows:

mysql> SHOW CREATE TABLE tb_emp1\G
*************************** 1. row ***************************
       Table: tb_emp1
Create Table: CREATE TABLE `tb_emp1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.03 sec)

[Related recommendations:

mysql video tutorial]

The above is the detailed content of How to query the table structure in mysql. For more information, please follow other related articles on the PHP Chinese website!