Mysql query the table structure under the database?

Method: 1. Use the DESCRIBE command to display the table structure in the form of a table, the syntax "DESCRIBE table name;"; 2. Use the "SHOW CREATE TABLE" command to display the table structure in the form of a SQL statement, the syntax "SHOW CREATE TABLE table name;".

(Recommended tutorial: mysql video tutorial)

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] respectively Use DESCRIBE and DESC to view the table structure of table tb_emp1. The SQL statement and running results 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: represents the column Whether NULL values ​​can be stored.

• 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, once using the \g ending and once not using it:

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 \G ending SQL statement and The running results 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)

The above is the detailed content of Mysql query the table structure under the database?. For more information, please follow other related articles on the PHP Chinese website!