Home > Database > Mysql Tutorial > body text

How to query the table structure in mysql

青灯夜游
Release: 2021-12-01 17:37:30
Original
81152 people have browsed it

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.

How to query the table structure in mysql

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 表名;
Copy after login

or abbreviated as:

DESC 表名;
Copy after login

[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)
Copy after login
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)
Copy after login

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 表名;
Copy after login

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)
Copy after login
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)
Copy after login

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)
Copy after login
[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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!