Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

Describe Tables

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

DESCRIBE Tables
Describing a MySQL table refers to retrieving its definition or structure. When we describe a table, it basically includes the fields present, their datatypes, and if any constraints defined on them.
We can get the information about the table structure using the following SQL statements 
DESCRIBE Statement
DESC Statement
SHOW COLUMNS Statement
EXPLAIN Statement
All these statements are used for the same purpose. Let us learn about them in detail, one by one, in this tutorial.
DESCRIBE Statement
The MySQL DESCRIBE statement is used to retrieve a table-related information, which consists of field names, field data types, and constraints (if any). This statement is a shortcut for the SHOW columns statement (they both retrieve the same information from a table).
Apart from retrieving a table's definition, this statement can be used to get the information of a particular field in a table.
Syntax
Following is the syntax of MySQL DESCRIBE statement 
DESCRIBE table_name [col_name | wild];
Example
In the following example, we are creating a table named CUSTOMERS using the CREATE TABLE statement 
CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
Now, execute the following query to get the information about columns of the CUSTOMERS table 
DESCRIBE CUSTOMERS;
Output
Following is the columns information of CUSTOMERS table 
Field
Type
Null
Key
Default
Extra
ID
int
NO
PRI
NULL
auto_increment
NAME
varchar(20)
NO
﻿
NULL
﻿
AGE
int
NO
﻿
NULL
﻿
ADDRESS
char(25)
YES
﻿
NULL
﻿
SALARY
decimal(18, 2)
YES
﻿
NULL
﻿
Describing a specific column
By default, the DESCRIBE statement provides information about all the columns in the specified table. But you can also retrieve information about a particular column of a table by specifying the name of that column.
For example, the following query displays information about NAME column of CUSTOMERS table, which we created in the previous example.
DESCRIBE CUSTOMERS NAME;
Output
Following is the description of NAME column in CUSTOMERS table
Field
Type
Null
Key
Default
Extra
NAME
varchar(20)
NO
﻿
NULL
﻿
DESC Statement
We can also retrieve the table information using the MySQL DESC statement instead of DESCRIBE. They both give the same results, so DESC is just a shortcut for DESCRIBE statement.
Syntax
Following is the syntax of the MySQL DESC statement 
DESC table_name [col_name | wild];
Example
In this example, we are trying to get the information of CUSTOMERS table using DESC statement.
DESC CUSTOMERS;
Following is the columns information of CUSTOMERS table 
Field
Type
Null
Key
Default
Extra
ID
int
NO
PRI
NULL
auto_increment
NAME
varchar(20)
NO
﻿
NULL
﻿
AGE
int
NO
﻿
NULL
﻿
ADDRESS
char(25)
YES
﻿
NULL
﻿
SALARY
decimal(18,2)
YES
﻿
NULL
﻿
Describing a specific column
We can also get the information of a specific column in a given table, similar to using DESCRIBE. Instead of DESCRIBE, we use DESC.
For example, the following query displays information about NAME column of CUSTOMERS table.
DESC CUSTOMERS NAME;
Output
Following is the description of NAME column in CUSTOMERS table 
Field
Type
Null
Key
Default
Extra
NAME
varchar(20)
NO
﻿
NULL
﻿
SHOW COLUMNS Statement
The MySQL SHOW COLUMNS Statement is used to display the information of all the columns present in a table. The DESCRIBE statement is a shortcut for this statement.
Note: This statement will not display information of a specific field.
Syntax
Following is the syntax of the SHOW COLUMNS statement 
SHOW COLUMNS FROM table_name;
Example
Here, we are retrieving column information of the same CUSTOMERS table using the SHOW COLUMNS statement.
SHOW COLUMNS FROM CUSTOMERS;
Following is the columns information of CUSTOMERS table 
Field
Type
Null
Key
Default
Extra
ID
int
NO
PRI
NULL
auto_increment
NAME
varchar(20)
NO
﻿
NULL
﻿
AGE
int
NO
﻿
NULL
﻿
ADDRESS
char(25)
YES
﻿
NULL
﻿
SALARY
decimal(18,2)
YES
﻿
NULL
﻿
EXPLAIN Statement
The MySQL EXPLAIN Statement is a synonym of DESCRIBE Statement which retrieves the information of a table's structure such as column names, column data types, and constraints (if any).
Syntax
Following is the syntax of the SHOW COLUMNS statement 
EXPLAIN table_name;
Example
In the following query, we are retrieving column information of the CUSTOMERS table using the EXPLAIN statement.
EXPLAIN CUSTOMERS;
Following is the columns information of CUSTOMERS table 
Field
Type
Null
Key
Default
Extra
ID
int
NO
PRI
NULL
auto_increment
NAME
varchar(20)
NO
﻿
NULL
﻿
AGE
int
NO
﻿
NULL
﻿
ADDRESS
char(25)
YES
﻿
NULL
﻿
SALARY
decimal(18,2)
YES
﻿
NULL
﻿
Describe Tables in Different Formats
You can retrieve the information in various formats using the explain_type option. The value to this option can be TRADITIONAL, JSON and, TREE.
Syntax
Following is the syntax to describe tables in different formats 
{EXPLAIN | DESCRIBE | DESC} 
explain_type: { FORMAT = format_name } select_statement
Example
In the following example, we are describing the CUSTOMERS table format as TRADITIONAL.
EXPLAIN FORMAT = TRADITIONAL SELECT * FROM CUSTOMERS;
Output
Executing the query above will produce the following output 
id
select_type
table
partitions
possible_keys
1
SIMPLE
CUSTOMERS
NULL
NULL
Example
Here, we are describing the CUSTOMERS table format as JSON.
EXPLAIN FORMAT = JSON SELECT * FROM CUSTOMERS;
Output
Executing the query above will produce the following output 
EXPLAIN
{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "0.95" }, "table": { "table_name": "CUSTOMERS", "access_type": "ALL", "rows_examined_per_scan": 7, "rows_produced_per_join": 7, "filtered": "100.00", "cost_info": { "read_cost": "0.25", "eval_cost": "0.70", "prefix_cost": "0.95", "data_read_per_join": "1K" }, "used_columns": [ "ID", "NAME", "AGE", "ADDRESS", "SALARY" ] } } }
Example
In the following example, we are describing the CUSTOMERS table format as TREE.
EXPLAIN FORMAT = TREE SELECT * FROM CUSTOMERS;
Output
Executing the query above will produce the following output 
EXPLAIN
Table scan on CUSTOMERS (cost=0.95 rows=7)
Describing Table Using a Client Program
In addition to describe a table from MySQL Database using the MySQL query, we can also perform the DESCRIBE TABLE operation on a table using a client program.
Syntax
Following are the syntaxes to describe a table from MySQL Database in various programming languages 
PHPNodeJSJavaPython
To describe a table from MySQL Database through a PHP program, we need to execute the Describe Table statement using the mysqli function query() as 
$sql="Describe Table_name";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
﻿
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.');
﻿
$sql = " DESCRIBE sales ";
if ($q = $mysqli->query($sql)) {
    printf(" Table described successfully.");
    while ($row = mysqli_fetch_array($q)) {
        echo "{$row['Field']} - {$row['Type']}\n";
    }
}
if ($mysqli->errno) {
    printf("table could not be described .", $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as follows 
Table described successfully.
ID - int
ProductName - varchar(255)
CustomerName - varchar(255)
DispatchDate - date
DeliveryTime - time
Price - int
Location - varchar(255)
﻿
﻿
﻿
﻿
﻿

Field	Type	Null	Key	Default	Extra
ID	int	NO	PRI	NULL	auto_increment
NAME	varchar(20)	NO		NULL
AGE	int	NO		NULL
ADDRESS	char(25)	YES		NULL
SALARY	decimal(18, 2)	YES		NULL

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

DESCRIBE Tables

DESCRIBE Statement

Syntax

Example

Output

Describing a specific column

Output

DESC Statement

Syntax

Example

Describing a specific column

Output

SHOW COLUMNS Statement

Syntax

Example

EXPLAIN Statement

Syntax

Example

Describe Tables in Different Formats

Syntax

Example

Output

Example

Output

Example

Output

Describing Table Using a Client Program

Syntax

Example

Output