The DROP statement in MySQL database is used to remove or delete an existing database object such as a table, index, view, or procedure. Whenever we use DROP statement with any of the database objects, like indexes, it will remove them permanently along with their associated data.

Therefore, we can drop any index from a database table using two different SQL DROP queries.

It is important to understand that dropping an index can have a significant impact on the performance of your database queries. Therefore, only try to remove an index if you are sure that it is no longer required.

The MySQL DROP INDEX Statement

The DROP INDEX statement in MySQL is used to delete an index from a table.

Syntax

Following is the syntax to drop an index using DROP INDEX statement −

DROP INDEX index_name ON table_name;

Example

In this example, we first create a new table CUSTOMERS and adding an index to one of its columns (AGE) using the following CREATE TABLE query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY(ID),
   INDEX(AGE)
);

Now, create another index on CUSTOMERS table. We are using CREATE INDEX statement here −

CREATE INDEX NAME_INDEX ON CUSTOMERS (Name);

DROP INDEX Query −

Then, use the following query to drop the index created above.

DROP INDEX NAME_INDEX ON CUSTOMERS;

Verification

To verify if the index has been dropped, display the table definition using DESC query below −

DESC CUSTOMERS;

As we can see in the following table, the index on NAME column is dropped.

The MySQL ALTER… DROP Statement

The ALTER DROP statement can also be used to drop an index in a MySQL table. This is just an alternative to the DROP INDEX statement, so it only works with the index that exists on a table.

Syntax

Following is the syntax of the DROP INDEX IF EXISTS in SQL −

ALTER TABLE table_name DROP INDEX index_name;

Example

Let us see another example to drop the index from the CUSTOMERS table using the ALTER… DROP command as shown below −

ALTER TABLE CUSTOMERS DROP INDEX AGE;

Verification

To verify if the index on AGE column has been dropped, display the table definition using DESC query below −

DESC CUSTOMERS;

As we can see in the following table, the index on NAME column is dropped.

Dropping PRIMARY KEY or UNIQUE Constraint

The DROP INDEX statement in MySQL does not usually drop indexes like PRIMARY KEY or UNIQUE constraints. To drop indexes associated with these constraints, we need to use the ALTER TABLE DROP command.

Syntax

Following is the syntax −

ALTER TABLE table_name DROP constraint_name;

Example

In this example, we are using the following query to drop the PRIMARY KEY constraint present on the ID column of CUSTOMERS table −

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

Verification

To verify whether the primary key constraint is dropped from the table, describe the ”temp” table using DESC command as follows −

DESC CUSTOMERS;

The PRIMARY KEY constraint is finally dropped! Look at the table below −

Dropping an Index Using a Client Program

We have seen how to drop an index from a MySQL database using SQL queries. In addition to it, we can also use other client programs to perform the drop index operation in the MySQL database.

Syntax

Following are the syntaxes to drop an index from a MySQL database using various programming languages −

$sql = "DROP INDEX index_name ON tbl_name";
$mysqli->query($sql);

sql= "DROP INDEX index_name ON tbl_name";
con.query(sql);

String sql = "DROP INDEX index_name ON table_name";
statement.executeQuery(sql);

drop_index_query = "DROP INDEX index_name ON tbl_name"
cursorObj.execute(drop_index_query);

Example

Following are the implementations of this operation in various programming languages −

$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.
'');

// CREATE INDEX
$sql = "DROP INDEX tid ON tutorials_table";
if ($mysqli->query($sql)) {
    printf("Index droped successfully!.
");
}
if ($mysqli->errno) {
    printf("Index could not be droped!.
", $mysqli->error);
}
$mysqli->close();

Index droped successfully!.

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  sql = "create database TUTORIALS"
  con.query(sql);

  sql = "USE TUTORIALS"
  con.query(sql);

  sql = "CREATE TABLE temp(ID INT, Name VARCHAR(255), age INT, Location VARCHAR(255));"
  con.query(sql);

  sql = "INSERT INTO temp values(1, ''Radha'', 29, ''Vishakhapatnam''),(2, ''Dev'', 30, ''Hyderabad'');"
  con.query(sql);

  //Creating Indexes
  sql = "CREATE INDEX sample_index ON temp (name) USING BTREE;"
  con.query(sql);

  sql = "CREATE INDEX composite_index on temp (ID, Name);"
  con.query(sql);

  //Displaying Indexes
  sql = "SHOW INDEXES FROM temp;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
    console.log("--------------------------");
  });

  //Dropping Indexes
  sql = "DROP INDEX sample_index ON temp;"
  con.query(sql);

  sql = "DROP INDEX composite_index ON temp;"
  con.query(sql);

  //Displaying Indexes after deleting
  sql = "SHOW INDEXES FROM temp;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Connected!
--------------------------
[
  {
    Table: ''temp'',
    Non_unique: 1,
    Key_name: ''sample_index'',
    Seq_in_index: 1,
    Column_name: ''Name'',
    Collation: ''A'',
    Cardinality: 2,
    Sub_part: null,
    Packed: null,
    Null: ''YES'',
    Index_type: ''BTREE'',
    Comment: '''',
    Index_comment: '''',
    Visible: ''YES'',
    Expression: null
  },
  {
    Table: ''temp'',
    Non_unique: 1,
    Key_name: ''composite_index'',
    Seq_in_index: 1,
    Column_name: ''ID'',
    Collation: ''A'',
    Cardinality: 2,
    Sub_part: null,
    Packed: null,
    Null: ''YES'',
    Index_type: ''BTREE'',
    Comment: '''',
    Index_comment: '''',
    Visible: ''YES'',
    Expression: null
  },
  {
    Table: ''temp'',
    Non_unique: 1,
    Key_name: ''composite_index'',
    Seq_in_index: 2,
    Column_name: ''Name'',
    Collation: ''A'',
    Cardinality: 2,
    Sub_part: null,
    Packed: null,
    Null: ''YES'',
    Index_type: ''BTREE'',
    Comment: '''',
    Index_comment: '''',
    Visible: ''YES'',
    Expression: null
  }
]
--------------------------
[]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class DropIndex {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Drop index;
         String sql = "DROP INDEX tid ON tutorials_tbl";
         statement.executeUpdate(sql);
         System.out.println("Index has been dropped Successfully...!");
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
Index has been dropped Successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
drop_index_query = "DROP INDEX idx_submission_date ON tutorials_tbl"
cursorObj.execute(drop_index_query)
connection.commit()
print("Index dropped successfully.")
cursorObj.close()
connection.close()

Index dropped successfully.

MySQL Constraints

The MySQL constraints can be used to set certain rules to the column(s) in a table. These constraints can restrict the type of data that can be inserted or updated in a particular column. This helps you to maintain the data accuracy and reliability in a table.

There are two types of MySQL constraints.

• Column level constraints: These type of constraints will only apply to a column in a table.
• Table level constraints: These constraints will apply to the complete table.

The commonly used constraints in MySQL are NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, CREATE INDEX, AUTO_INCREMENT, etc.

Syntax

Following is the basic syntax to add a constraint for the column(s) in a table −

CREATE TABLE table_name (
   Column_name1 datatype constraint,
   Column_name2 datatype constraint,
   Column_name3 datatype constraint,
   .........
);

MySQL NOT NULL Constraint

By default, a column in a MySQL table can contain NULL values. In some scenarios, we may want a particular column to not accept or contain NULL values. To do so, we can use the MySQL NOT NULL constraint.

This constraint enforces a specific field to always contain a value, which means that we cannot insert or update a record without adding a value to this field.

Example

In the following query, we are adding the NOT NULL constraint on the ID and NAME columns of the CUSTOMERS table. As a result, the ID and NAME columns will not accept NULL values at the time of record insertion.

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int
);

Let”s try inserting records into this table. The following statement will insert a record into the CUSTOMERS table −

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(1, ''Nikhil'', 18);

But, if we try to insert records with NULL values as ID as −

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(Null, ''Varun'', 26);

An error will be generated saying “Column ”ID” cannot be null”.

ERROR 1048 (23000): Column ''ID'' cannot be null

In the same way if we try to pass NULLs as values to the NAME column, similar error will be generated.

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(3, Null, 19);

This will generate the following error −

ERROR 1048 (23000): Column ''NAME'' cannot be null

As we can see in the above queries, the first record is successfully inserted because it does not have null values in the ID and Name columns. Whereas, the second and third records are not inserted because we are trying to insert NULL values in the columns which shouldn”t be NULL.

MySQL UNIQUE Constraint

The UNIQUE constraint in MySQL ensures that every value in a column must be distinct. This means the column with the UNIQUE constraint cannot have the same value repeated; each value must be unique.

Note: We can have one or more UNIQUE constraints on a single table.

Example

The following query creates a UNIQUE constraint on the ID column of the CUSTOMERS table −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   UNIQUE (ID)
 );

Now, let us insert the following records into the above-created table −

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(1, ''Nikhil'', 18);
INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(1, ''Varun'', 26);

In the above code block, the second insert statement returned an error saying “Duplicate entry ”1” for key ”customers.ID” because the ID value we are inserting already exists in the table. Therefore, it is a duplicate and the query generates the following error −

ERROR 1062 (23000): Duplicate entry ''1'' for key ''customers.ID''

MySQL PRIMARY KEY Constraint

The PRIMARY KEY constraint in MySQL is used to uniquely identify each record in a table. This means that, if we define primary key on a particular column in a table, it must contain UNIQUE values, and cannot contain NULL values.

Note: We can have only a single primary key on a table.

Example

The following query creates a PRIMARY KEY on the ID column of the CUSTOMERS table −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   PRIMARY KEY (ID)
);

Once the table is created, insert the following record into the above-created table −

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES (1, ''Nikhil'', 18);
Query OK, 1 row affected (0.01 sec)

Since we added the PRIMARY KEY constraint on the ID column, if you try to insert a record with duplicate ID value or NULL value, it will generate an error.

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES (1, ''Varun'', 26);
ERROR 1062 (23000): Duplicate entry ''1'' for key ''customers.PRIMARY''

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES (NULL, ''Datta'', 19);
ERROR 1048 (23000): Column ''ID'' cannot be null

As we can see in the above queries, the first insert statement is successfully inserted into the table. Whereas the second and third statements returned an error because they contain a duplicate and a NULL value in the primary key column i.e. (ID).

MySQL FOREIGN KEY Constraint

The FOREIGN KEY constraint in MySQL is used to link a field or collection of fields in one table to the primary key of another table.

A table with the foreign key is called a child table and the table with the primary key is called the parent table or referenced table.

Example

The following query creates a FOREIGN KEY on the CUST_ID column when the ORDERS table is created −

Table: Customers

CREATE TABLE CUSTOMERS (
   CUST_ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   PRIMARY KEY (CUST_ID)
);

Table: Orders

CREATE TABLE ORDERS (
   ORDER_ID int NOT NULL,
   ORDER_NUMBER int NOT NULL,
   CUST_ID int,
   FOREIGN KEY (CUST_ID) REFERENCES CUSTOMERS (CUST_ID)
);

MySQL CHECK Constraint

The CHECK constraint in MySQL restricts the range of values that can be inserted into a column. This constraint ensures that the inserted value in a column must be satisfied with the provided condition.

Example

The following query creates a CHECK constraint on the AGE column of the CUSTOMERS table, where it ensures that the age of the student must be 18 or older −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   CHECK (AGE >= 18)
 );

Once the table is created, we can insert the records into the above created table as shown below −

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(1, ''Nikhil'', 18);

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(3, ''Datta'', 19);

Since we added the CHECK constraint on the AGE column such that the age of the student should be equal or greater than 18. If you try to insert a record with age value less than 18, an error will be generated.

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(2, ''Varun'', 16);
ERROR 3819 (HY000): Check constraint ''customers_chk_1'' is violated.

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(4, ''Karthik'', 15);
ERROR 3819 (HY000): Check constraint ''customers_chk_1'' is violated.

Example

Here, the following query creates a CHECK constraint on multiple columns (AGE and ADDRESS) −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   ADDRESS varchar(40),
   CONSTRAINT CHECK_AGE CHECK (AGE >= 18 AND ADDRESS = "Mumbai")
);

Now, let us insert the following records into the above-created table −

INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS) VALUES(1, ''Nikhil'', 18, ''Mumbai'');
Query OK, 1 row affected (0.01 sec)

INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS) VALUES(3, ''Datta'', 19, ''Delhi'');
ERROR 3819 (HY000): Check constraint ''CHECK_AGE_AND_ADDRESS'' is violated.

The second insert statement returned an error because it is violating the condition of the check constraint i.e. (AGE >= 18 AND ADDRESS = “Mumbai”).

MySQL DEFAULT Constraint

The DEFAULT constraint in MySQL is used to assign a default value to a specific column in a table. This default value gets applied to any new records in the DEFAULT specified column when no other value is provided during insertion.

Example

In the following query, we are defining the DEFAULT constraint on the ADDRESS column of the CUSTOMERS table. We assigned “Mumbai” as default value when no value is inserted. −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   ADDRESS varchar(40) DEFAULT "Mumbai"
);

Here, we are inserting the first two records without any value in the ADDRESS column. In the third record, we are inserting the ADDRESS value as ”Delhi”.

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(1, ''Nikhil'', 18);

INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(2, ''Varun'', 16);

INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS) VALUES(3, ''Datta'', 19, ''Delhi'');

Exeucte the following query to display the records inserted in the above-created table −

Select * from CUSTOMERS;

In the following output, we can see that the value in the ADDRESS column for the first two rows is by default “Mumbai”.

MySQL CREATE INDEX Constraint

The CREATE INDEX constraint in MySQL is used to create indexes for one more columns in a table.

The indexes are used to fetch the data from the database much quicker. However, the users cannot see the indexes in action, instead, they are just used to speed up the searches and queries.

Example

Here, we are creating a table named CUSTOMERS using the query below −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int,
   ADDRESS varchar(40),
   PRIMARY KEY (ID)
);

The following query creates an index named “index_address” on the ADDRESS column of the CUSTOMERS table −

CREATE INDEX index_address ON CUSTOMERS (ADDRESS);

MySQL AUTO_INCREMENT Constraint

When a AUTO_INCREMENT constraint is defined on a particular column of a table, it will automatically generate a unique number when a new record is inserted into that column.

By default, the starting value is 1, and it will automatically increment its value by 1 for each new record.

Example

The following query adds an AUTO_INCREMENT constraint on the ID column of the CUSTOMERS table −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL AUTO_INCREMENT,
   NAME varchar(20) NOT NULL,
   AGE int,
   PRIMARY KEY (ID)
);

In the insert statements below, we are not inserting ID values.

INSERT INTO STUDENTS(NAME, AGE) VALUES(''Nikhil'', 18);
INSERT INTO STUDENTS(NAME, AGE) VALUES(''Varun'', 16);
INSERT INTO STUDENTS(NAME, AGE) VALUES(''Datta'', 19);

Now, execute the following query to display the records of the above-created table −

Select * from CUSTOMERS;

As we can see in the STUDENTS table below, the values in the ID column are automatically incremented because of the AUTO_INCREMENT constraint on the ID column.

MySQL ALTER Command

The MySQL ALTER command is used to modify the structure of an existing table. It allows you to make various changes, such as adding, deleting, or modify columns within the table.

Additionally, the ALTER command is also used to add and drop different constraints associated with an existing table.

Since this command modifies the structure of a table, it is a part of Data Definition Language in SQL. This is also where the ALTER command differs from UPDATE command; while ALTER interacts with the structure of a table to modify it, UPDATE only interacts with the data present in the table without disturbing its structure.

Syntax

Following is the syntax of ALTER command in MySQL −

ALTER TABLE table_name [alter_option ...];

Example

Let us begin with the creation of a table named CUSTOMERS.

CREATE TABLE CUSTOMERS (
   ID INT,
   NAME VARCHAR(20)
);

Now, execute the following query to display information about the columns in CUSTOMERS table.

SHOW COLUMNS FROM CUSTOMERS;

Output

Following are the details of the columns of the CUSTOMERS table −

Dropping a Column

To drop a column in an existing table, we use the ALTER TABLE command with DROP clause.

Example

In the following example, we are dropping an existing column named ID from the above-created CUSTOMERS table −

ALTER TABLE CUSTOMERS DROP ID;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

To verify whether the ID column has been dropped from the CUSTOMERS table, execute the following query −

SHOW COLUMNS FROM CUSTOMERS;

As we can see in the output below, there is no ID column present. Hence it is dropped.

Note: A DROP clause will not work if the column is the only one left in the table.

Adding a Column

To add a new column into an existing table, we use ADD keyword with the ALTER TABLE command.

Example

In the following query, we are adding a column named ID into an existing table CUSTOMERS.

ALTER TABLE CUSTOMERS ADD ID INT;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now, the CUSTOMERS table will contain the same two columns that it had when you first created the table. But the newly added ID column will be added at the end of the table by default. In this case, it will add after the NAME column.

Verification

Let us verify using the following query −

SHOW COLUMNS FROM CUSTOMERS;

As we can see in the output below, the newly added ID column is inserted at the end of the table.

Repositioning a Column

If we want a column to be placed at a specific position within the table, we can use FIRST to make it the first column or AFTER col_name to indicate that the new column should be positioned after the col_name.

Example

Consider the previously modified CUSTOMERS table, where the NAME is the first column and ID is the last column.

In the following query, we are removing the ID column from the table and then adding it back, positioning it as the first column in the table using FIRST keyword −

ALTER TABLE CUSTOMERS DROP ID;
ALTER TABLE CUSTOMERS ADD ID INT FIRST;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Now, let us verify the positions of the column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can see in the output below, the ID column is positioned first.

Example

Here, we are removing the ID column from the table and then adding it back, positioning it after the NAME column using the AFTER col_name keyword.

ALTER TABLE CUSTOMERS DROP ID;
ALTER TABLE CUSTOMERS ADD ID INT AFTER NAME;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Now, let us verify the positions of the column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can see in the output below, the ID column is positioned first.

Note: The FIRST and AFTER specifiers work only with the ADD clause. This means that if you want to reposition an existing column within a table, you first must DROP it and then ADD it at the new position.

Altering a Column Definition or a Name

In MySQL, to change a column”s definition, we use MODIFY or CHANGE clause in conjunction with the ALTER command.

Example

In the query below, we are changing the definition of column NAME from varchar(20) to INT using the MODIFY clause −

ALTER TABLE CUSTOMERS MODIFY NAME INT;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Now, let us verify the definition of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can observe, the definition for NAME column has been changed to INT.

Example

We can also change the column definition using CHANGE, but the syntax is a bit different from MODIFY. After the CHANGE keyword, we specify the name of the column (twice) that we want to change, then specify the new definition.

Here, we are changing the definition of column ID from INT to varchar(20) using the CHANGE clause −

ALTER TABLE CUSTOMERS MODIFY ID VARCHAR(20);

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Now, let us verify the definition of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can observe, the definition for NAME column has been changed to INT.

Altering a Column”s Default Value

In MySQL, we can change a default value for any column by using the DEFAULT constraint with ALTER command.

Example

In the following example, we are changing the default value of NAME column.

ALTER TABLE CUSTOMERS ALTER NAME SET DEFAULT 1000;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Now, let us verify the default value of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can observe, the default value for NAME column has been changed to 1000.

Example

We can remove the default constraint from any column by using DROP clause along with the ALTER command.

Here, we are removing the default constraint of NAME column.

ALTER TABLE CUSTOMERS ALTER NAME DROP DEFAULT;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Now, let us verify the default value of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can observe, the default value for NAME column has been changed to NULL.

Altering (Renaming) a Table

To rename a table, use the RENAME option of the ALTER TABLE statement.

Example

The following query renames the table named CUSTOMERS to BUYERS.

ALTER TABLE CUSTOMERS RENAME TO BUYERS;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.02 sec)

Verification

Now, let us verify the default value of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM BUYERS;

The table has been renamed to BUYERS, as we can see from the columns within it.

Altering Table Using a Client Program

Besides altering an existing table in a MySQL database with a MySQL query, we can also use a client program to perform the ALTER TABLE operation.

Syntax

Following are the syntaxes of Altering a table from MySQL Database in various programming languages −

$sql = "ALTER TABLE table_name";
$mysqli->query($sql);

sql = "ALTER TABLE table_name";
con.query(sql);

String sql = "ALTER TABLE table_name";
statement.execute(sql);

sql = "ALTER TABLE table_name";
cursorObj.execute(sql);

Example

Following are the programs −

$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 = "ALTER TABLE testalter_tbl  DROP i";
if ($mysqli->query($sql)) {
    printf("table altered successfully.
");
}
if ($mysqli->errno) {
    printf("table could not alter: %s
", $mysqli->error);
}
$mysqli->close();

table altered successfully.

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",    user: "root",
    password: "Nr5a0204@123"
});
  //Connecting to MySQL
  con.connect(function (err){
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");
  sql = "USE TUTORIALS"
  con.query(sql);

  //Altering a table
  sql = "ALTER TABLE testalter_tbl  DROP i";
    con.query(sql, function(err){
    if (err) throw err
    console.log("Altered table successfully...");
  });
});

Connected!
--------------------------
Altered table successfully...

import java.sql.*;
public class AlterTable {
    public static void main(String[] args){
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //Alter table statement...!
            String sql = "ALTER TABLE testalter_tbl  DROP i";
            statement.executeUpdate(sql);
            System.out.println("Table altered successfully...!");
            connection.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}

Connected successfully...!
Table altered successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''testalter_tbl''
# ALTER TABLE statement
alter_statement = ''testalter_tbl  DROP i''
#Creating a cursor object
cursorObj = connection.cursor()
cursorObj.execute(f"ALTER TABLE {table_name} {alter_statement}")
print(f"Table ''{table_name}'' is altered successfully.")
cursorObj.close()
connection.close()

Table ''testalter_tbl'' is altered successfully.

There can be a situation where both users and database administrators might want to change the name of a table in a relational database to make the table”s name more suitable for a specific situation.

MySQL provides two different ways to rename an MySQL table. We can use either the RENAME TABLE or ALTER TABLE statement. In this tutorial, we will understand them with suitable examples.

MySQL RENAME TABLE Statement

The MySQL RENAME TABLE statement is used to rename an existing table in a database with another name.

Syntax

Following is the basic syntax of the MySQL RENAME TABLE statement −

RENAME TABLE table_name TO new_name;

Where, table_name is the name of an existing table and new_name is the new name which you want to assign.

Example

Let us start by creating a table with name CUSTOMERS in MySQL database using CREATE statement as shown below −

CREATE TABLE CUSTOMERS (
	ID INT,
	NAME VARCHAR(20),
	AGE INT
);

Here, we are renaming the above-created CUSTOMERS table to BUYERS using the following query −

RENAME TABLE CUSTOMERS to BUYERS;

Output

The table has been renamed without any errors.

Query OK, 0 rows affected (0.01 sec)

Verification

Execute the following query to retrieve the description of the CUSTOMERS table −

DESC CUSTOMERS;

It display an error because, we have changed the CUSTOMERS table name to BUYERS and there is no CUSTOMERS table in our database.

ERROR 1146 (42S02): Table ''tutorials.customers'' doesn''t exist

Renaming Multiple Tables

Using the MySQL RENAME TABLE statement, we can also rename multiple tables in a single query.

Syntax

Following is the syntax for renaming multiple tables using MySQL RENAME TABLE statement −

RENAME TABLE old_table1 TO new_table1,
   old_table2 TO new_table2,
   old_table3 TO new_table3;

Example

In the following example, we are creating three different tables named Cust1, Cust2, and Cust3 −

CREATE TABLE Cust1(ID INT);
CREATE TABLE Cust2(ID INT);
CREATE TABLE Cust3(ID INT);

Here, we are verifying whether the above tables are created or not using the following query −

SHOW TABLES;

As we can see in the output below, the above tables have been successfully created.

Now, let us rename all the above-created tables using the following query −

RENAME TABLE Cust1 TO Buyer1, Cust2 TO Buyer2, Cust3 TO Buyer3;

Output

All three tables has been renamed without any errors.

Query OK, 0 rows affected (0.03 sec)

Verification

Let us verify the list of the tables again to find whether the table names have been changed or not −

SHOW TABLES;

As we can see the output below, all three tables have been successfully renamed.

Renaming a Table using ALTER TABLE statement

In MySQL, we can also use the RENAME with ALTER TABLE statement to modify the name of an existing table.

Syntax

Following is the syntax to rename a table with ALTER TABLE statement −

ALTER TABLE existing_table_name RENAME TO new_table_name

Example

In the following query, we are creating a table named PLAYERS.

CREATE TABLE PLAYERS (
	ID INT,
	NAME VARCHAR(20),
	AGE INT
);

Now, let us rename the above-created table with a new name TEAMS using the following query −

ALTER TABLE PLAYERS RENAME TO TEAMS;

Output

The table has been renamed without any errors.

Query OK, 0 rows affected (0.02 sec)

Verification

Execute the following query to retrieve the description of the PLAYERS table −

DESC PLAYERS;

It will display an error because, we have renamed the PLAYERS table to TEAMS and there is no PLAYERS table in our database.

ERROR 1146 (42S02): Table ''tutorials.players'' doesn''t exist

Renaming Table Using a Client Program

In addition to renaming a table in MySQL Database using MySQL query, we can also perform the RENAME TABLE operation on a table using a client program.

Syntax

Following are the syntaxes to rename table in MySQL database in various programming languages −

$sql = "RENAME TABLE old_table_name TO new_table_name";
$mysqli->query($sql);

sql = "RENAME TABLE table_name TO new_name";
con.query(sql);

String sql = "RENAME TABLE old_table_name TO new_table_name";
statement.executeUpdate(sql);

sql = "RENAME TABLE old_table_name TO new_table_name"
cursorObj.execute(sql);

Example

Following are the programs −

$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 = "RENAME TABLE tutorials_table TO tutorials_tbl ";
if ($mysqli->query($sql)) {
    printf("table renamed successfully.
");
}
if ($mysqli->errno) {
    printf("table could not rename: %s
", $mysqli->error);
}
$mysqli->close();

table renamed successfully.

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "CREATE DATABASE tutorials"
  con.query(sql);

  //Selecting a Database
  sql = "USE tutorials"
  con.query(sql);

  //Creating DEMO table
  sql = "CREATE TABLE Demo(ID INT, First_Name VARCHAR(255), Last_Name VARCHAR(255));"
  con.query(sql);

  //Inserting records
  sql = "INSERT INTO Demo VALUES(1, ''Shikhar'', ''Dhawan''),(2, ''Jonathan'', ''Trott''),(3, ''Kumara'', ''Sangakkara'');"
  con.query(sql);

  //Fetching the DEMO table
  sql = "SELECT * FROM Demo;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**Following is the DEMO table**");
    console.log(result);
    console.log("--------------------------");
  });

  //Renaming the DEMO table as PLAYERS
  sql = "RENAME TABLE Demo to Players;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**Renamed the DEMO table as Players**");
    console.log(result);
    console.log("--------------------------");
  });

  //Trying to Retrieve the DEMO table, Leads to an error.
  sql = "SELECT * FROM Demo;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("Trying to retrieve DEMO table");
    console.log(result);
  });
});

Connected!
--------------------------
**Following is the DEMO table**
[
  { ID: 1, First_Name: ''Shikhar'', Last_Name: ''Dhawan'' },
  { ID: 2, First_Name: ''Jonathan'', Last_Name: ''Trott'' },
  { ID: 3, First_Name: ''Kumara'', Last_Name: ''Sangakkara'' }
]
--------------------------
**Renamed the DEMO table as Players**
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  info: '''',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}
--------------------------
C:UsersLenovodesktopJavaScriptconnectDB.js:52
    if (err) throw err
             ^

Error: Table ''tutorials.demo'' doesn''t exist
    at Packet.asError (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libpacketspacket.js:728:17)
    at Query.execute (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libcommandscommand.js:29:26)
    at Connection.handlePacket (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libconnection.js:478:34)
    at PacketParser.onPacket (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libconnection.js:97:12)
    at PacketParser.executeStart (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libpacket_parser.js:75:16)
    at Socket. (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libconnection.js:104:25)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10) {
  code: ''ER_NO_SUCH_TABLE'',
  errno: 1146,
  sqlState: ''42S02'',
  sqlMessage: "Table ''tutorials.demo'' doesn''t exist",
  sql: ''SELECT * FROM Demo;'',
  fatal: true
}

import java.sql.*;
public class RenameTable {
    public static void main(String[] args){
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //Rename tables...!
            String sql = "RENAME TABLE tutorials_tbl TO new_table";
            statement.executeUpdate(sql);
            System.out.println("Table renamed successfully successfully...!");
            connection.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}

Table renamed successfully successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
old_table_name = ''tutorials_tbl''
new_table_name = ''tutorials_table''
#Creating a cursor object
cursorObj = connection.cursor()
cursorObj.execute(f"RENAME TABLE {old_table_name} TO {new_table_name}")
print(f"Table ''{old_table_name}'' is renamed to ''{new_table_name}'' successfully.")
cursorObj.close()
connection.close()

Table ''tutorials_tbl'' is renamed to ''tutorials_table'' successfully.

MySQL Show Tables Statement

In MySQL, we use the SHOW TABLES command to retrieve the names of tables that are present in a specific database. This command is useful in various situations, such as:

• When we want to view names of tables present in a database to verify if a specific table exists or not.

• When we want to display additional information about each table present in a database, we use the SHOW TABLES command with the MySQL FULL modifier.

• Additionally, we can use the SHOW TABLES command with WILDCARDS to filter and display only the tables that match a specific pattern.

Syntax

Following is the syntax of MySQL SHOW TABLES command−

SHOW TABLES;

Before proceeding with the examples, assume that the following tables exist in two databases, testdb1 and testdb2:

Example

First of all, we are changing the database to testdb1 to perform the SHOW TABLES operation on it.

mysql> USE testdb1;
Database changed

Now, execute the following query to list down all the tables from testdb1 database.

SHOW TABLES;

Output

Following are the tables that are present in the testdb1 database −

SHOW TABLES with FULL modifier

In MySQL, we use the optional FULL modifier along with the SHOW TABLES command to display a second output column that contains additional information about the tables present in a database, such as their types: BASE TABLE for a table, VIEW for a view, or SYSTEM VIEW for an INFORMATION_SCHEMA table.

Example

In the following query, we are using the FULL modifier along with the SHOW TABLES command to list the tables and their types in the testdb1 database.

SHOW FULL TABLES;

Output

Following is the output of the above query −

SHOW TABLES in different Database

In MySQL, we can retrieve the list of tables present in another database. To do so, we need to use the IN operator or the FROM clause in conjunction with the SHOW TABLES statement.

Example

In the following query, we are fetching the list of tables that exist in another database testdb2, using the SHOW TABLES command with IN operator.

SHOW TABLES IN testdb2;

Output

Following are the names of the tables that are present in testdb2 database −

Example

We can also perform the above operation using the SHOW TABLES command with FROM clause.

SHOW TABLES FROM testdb2;

Output

As we can observe, both outputs are the same.

SHOW TABLES using Pattern Matching

In some scenarios where there are large amount of tables present in a database, and we want to retrieve only specific tables, we use the LIKE operator with WILDCARD characters such as ”%”. These wildcards will filter and display only the tables that match a specific pattern.”

Example

In the following query, we are using the LIKE operator with SHOW TABLES command to select all the tables (in testdb1 database) where the name starts with “stud”.

SHOW TABLES IN testdb1 LIKE "stud%";

Output

Following are the tables present in testdb1 database whose name starts with “stud” −

Example

Here, we are trying to retrieve the tables from testdb2 database where the name starts with “stud” −

SHOW TABLES IN testdb2 LIKE "stud%";

Output

This will produce following result −

Example

We are using the SHOW TABLES along with WHERE clause to check if there is a table named “employee_remarks” in testdb1 database −

SHOW TABLES FROM testdb1 WHERE Tables_in_testdb1 = "employee_remarks";

Output

This will produce following result −

Showing tables Using a Client Program

Besides showing the list of tables present in a MySQL database with a MySQL query, we can also use a client program to perform the SHOW TABLES operation.

Syntax

Following are the syntaxes to Show list of tables in MySQL Database in various programming languages −

$sql = "SHOW TABLES FROM DATABASE";
$mysqli->query($sql);

sql= "SHOW TABLES FROM DATABASE";
con.query(sql);

String sql = "SHOW TABLES FROM DATABASE";
statement.executeQuery(sql);

show_table_query = "SHOW TABLES FROM DATABASE"
cursorObj.execute(show_table_query);

Example

Following are the programs −

$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 = "SHOW TABLES FROM TUTORIALS";
if ($result = $mysqli->query($sql)) {
    printf("Show table executed successfully.
");
    while ($row = mysqli_fetch_array($result)) {
        print_r($row);
    }
}
if ($mysqli->errno) {
    printf("Could not show table: %s
", $mysqli->error);
}
$mysqli->close();

Show table executed successfully.
Array
(
    [0] => articles
    [Tables_in_tutorials] => articles
)
Array
(
    [0] => courses
    [Tables_in_tutorials] => courses
)
Array
(
    [0] => customers
    [Tables_in_tutorials] => customers
)
Array
(
    [0] => customerss
    [Tables_in_tutorials] => customerss
)
Array
(
    [0] => demo_table
    [Tables_in_tutorials] => demo_table
)
Array
(
    [0] => employee
    [Tables_in_tutorials] => employee
)
Array
(
    [0] => films
    [Tables_in_tutorials] => films
)
Array
(
    [0] => films_watched
    [Tables_in_tutorials] => films_watched
)
Array
(
    [0] => myplayers
    [Tables_in_tutorials] => myplayers
)
Array
(
    [0] => new_tutorial_tbl
    [Tables_in_tutorials] => new_tutorial_tbl
)
Array
(
    [0] => orders
    [Tables_in_tutorials] => orders
)
Array
(
    [0] => persons
    [Tables_in_tutorials] => persons
)
Array
(
    [0] => products
    [Tables_in_tutorials] => products
)
Array
(
    [0] => sales
    [Tables_in_tutorials] => sales
)
Array
(
    [0] => students
    [Tables_in_tutorials] => students
)

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  sql = "CREATE DATABASE TUTORIALS;"
  con.query(sql);

  sql = "USE TUTORIALS;"
  con.query(sql);

  sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL);"
  con.query(sql);

  sql = "CREATE TABLE ORDERS (OID INT NOT NULL, CUSTOMER_ID INT);"
  con.query(sql);

  sql = "SHOW TABLES;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Connected!
--------------------------
[
  { Tables_in_tutorials: ''customers'' },
  { Tables_in_tutorials: ''orders'' }
]

import java.sql.*;
public class JDBCconnection {
public static void main(String[] args){
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String username = "root";
    String password = "password";
    try{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(url, username, password);
        Statement statement = connection.createStatement();
        System.out.println("Connected successfully...!");

        //show table statement...!
        String sql = "SHOW TABLES FROM TUTORIALS";
        ResultSet resultSet = statement.executeQuery(sql);
        System.out.println("Tables in the current database: ");
        while(resultSet.next()) {
            System.out.print(resultSet.getString(1));
            System.out.println();
        }
        connection.close();
    }
    catch(Exception e){
        System.out.println(e);
    }
}
}

Connected successfully...!
Tables in the current database:
articles
blackpink
blog
courses
customer
customers
data

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
#Creating a cursor object
cursorObj = connection.cursor()
cursorObj.execute("SHOW TABLES")
tables = cursorObj.fetchall()
print("Tables in the database are:")
for table in tables:
    print(table[0])
cursorObj.close()
connection.close()

Tables in the database are:
books
novel
novels
tutorials_tbl

There may be a situation when you need an exact copy of a table with the same columns, attributes, indexes, default values and so forth. Instead of spending time on creating the exact same version of an existing table using the CREATE TABLE statement, you can clone the table without disturbing the original table.

Cloning operation in SQL allows the user to create the exact copy of an existing table along with its definition, that is completely independent from the original table. Thus, if any changes are made to the cloned table, they will not be reflected in the original table. This operation comes in handy during testing processes, where there is a need to perform sample testing using the existing database tables.

There are three types of cloning possible using SQL in MySQL RDBMS; they are listed below −

• Simple Cloning: Creates a new table containing same records of existing table but void of any constraints or indexes etc.

• Shallow Cloning: Creates a new empty table with the same table definition of an existing table.

• Deep Cloning: Creates a new table and copies the table structure and data of an existing table to the new table.

Cloning Tables in MySQL

You can handle this situation by following the steps given below −

• Use SHOW CREATE TABLE to get a CREATE TABLE statement that specifies the source table”s structure, indexes and all.

• Modify the statement to change the table name to that of the clone table and execute the statement. This way, you will have the exact clone table.

• Optionally, if you need the table contents copied as well, issue an INSERT INTO … SELECT statement, too.

Example

Try out the following example to create a clone table for CUSTOMERS.

Step 1 − First of all, create the 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)
);

Insert some records into it using the following INSERT INTO statement.

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Now, execute the following SHOW CREATE TABLE command to display the source table”s (CUSTOMERS) structure.

SHOW CREATE TABLE CUSTOMERS;

Following is the CUSTOMERS table structure.

Table: CUSTOMERS
Create Table: CREATE TABLE `customers` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) NOT NULL,
  `AGE` int NOT NULL,
  `ADDRESS` char(25) DEFAULT NULL,
  `SALARY` decimal(18,2) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Step 2 − Now, copy the above source table structure and just change the name to copyCUSTOMERS.

CREATE TABLE `copyCUSTOMERS` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) NOT NULL,
  `AGE` int NOT NULL,
  `ADDRESS` char(25) DEFAULT NULL,
  `SALARY` decimal(18,2) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Step 3 − After executing step 2, if we want to copy data from the source table then we can do it by using INSERT INTO… SELECT statement.

INSERT INTO copyCUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

SELECT ID,NAME,AGE,ADDRESS,SALARY FROM CUSTOMERS;

Let us verify whether the clone table copyCUSTOMERS has the same records as CUSTOMERS table or not using the following query −

SELECT * FROM copyCUSTOMERS;

As we observe the output, we have an exact clone table as CUSTOMERS.

But to make this process simpler, we can try to perform Simple Cloning, Shallow Cloning or Deep Cloning using CREATE TABLE and INSERT INTO statements. Let us discuss them one by one in detail with suitable examples further in this tutorial.

Simple Cloning in MySQL

Simple Cloning means making a new table that contains the same data as an existing one. First, a new table is created using the CREATE TABLE statement. Then, data from selected columns in the existing table is copied into the new table using a SELECT statement.

Syntax

Following is the basic syntax to perform simple cloning in MySQL RDBMS −

CREATE TABLE new_table SELECT * FROM original_table;

Example

To perform the simple cloning operation, let us first create a table named CUSTOMERS table using the below query −

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)
);

The following query inserts 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

To display the records of the above created table, execute the following query −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

Now, with the following query, we are creating a new table CUSTOMERScopy by cloning the CUSTOMERS table.

CREATE TABLE CUSTOMERScopy SELECT * FROM CUSTOMERS;

Output

Executing the query above will produce the following output −

Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

To verify whether the new table contains all the information from the existing table CUSTOMERS, we can use the following SELECT query −

SELECT * FROM CUSTOMERScopy;

Following is the CUSTOMERScopy table −

Shallow Cloning in MySQL

The Shallow Cloning operation only copies the structure of the existing table into the new table created, but it doesn”t copy any of the data. So, we end up with a new empty table that has the same structure as the existing table.

Syntax

Following is the basic syntax to perform shallow cloning in MySQL RDBMS −

CREATE TABLE testCUSTOMERS1 LIKE CUSTOMERS;

Example

In the following query, we are performing shallow cloning by creating a new table named CUSTOMERScopy1 by cloning the CUSTOMERS table.

CREATE TABLE CUSTOMERScopy1 LIKE CUSTOMERS;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.03 sec)

Verification

To verify whether the new table is created or not, we can use the following SELECT query −

SELECT * FROM CUSTOMERScopy1;

As we can see in the output, the new table CUSTOMERScopy1 has been created with no data copied into it.

Empty set (0.00 sec)

Deep Cloning in MySQL

Deep Cloning operation is a combination of simple cloning and shallow cloning. It not only copies the structure of the existing table but also its data into the newly created table. Hence, the new table will have all the attributes of the existing table and also its contents.

Since it is a combination of shallow and simple cloning, this type of cloning will have two different queries to be executed: one with CREATE TABLE statement and one with INSERT INTO statement. The CREATE TABLE statement will create the new table by including all the attributes of existing table; and INSERT INTO statement will insert the data from existing table into new table.

Syntax

Following is the basic syntax to perform deep cloning in MySQL RDBMS −

CREATE TABLE new_table LIKE original_table;
INSERT INTO new_table SELECT * FROM original_table;

Example

In the following query, we are creating a new table CUSTOMERScopy2 by cloning the CUSTOMERS table, i.e. perform shallow cloning first.

CREATE TABLE CUSTOMERScopy2 LIKE CUSTOMERS;

Now using the following query, we are inserting data from CUSTOMERS table into new table CUSTOMERScopy2, i.e. performing simple cloning.

INSERT INTO CUSTOMERScopy2 SELECT * FROM CUSTOMERS;

Output

Executing the query above will produce the following output −

Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

To verify whether the new table is created or not with all the data present in it, we can use the following SELECT query −

SELECT * FROM CUSTOMERScopy2;

As we observe the ouptut, the CUSTOMERScopy2 table has the same structure and data as CUSTOMERS table.

Cloning a table Using a Client Program

Besides using MySQL queries to clone an existing table, you can also perform the cloning operation on a table using a client program.

Syntax

Following are the syntaxes to clone a table various in programming languages −

$sql = "CREATE TABLE clone_table LIKE tut_tbl";
$mysqli->query($sql);

sql = "CREATE TABLE new_table SELECT * FROM original_table";
con.query(sql);

String sql = "CREATE TABLE clone_table LIKE tut_tbl";
statement.executeUpdate(sql);

sql = "CREATE TABLE new_table_name AS
   SELECT * FROM source_table_name";
cursorObj.execute(sql);

Example

Following are the programs −

$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.
'');

// clone table
// To inherit all table definitions, use the create table...like syntax
$sql = "CREATE TABLE clone_table LIKE tut_tbl";
if ($mysqli->query($sql)) {
    printf("Table cloned successfully.
");
}
if ($mysqli->errno) {
    printf("table could not be cloned: %s
", $mysqli->error);
}

// To copy the data, you''ll need INSER...SELECT
$cpy_data = "INSERT INTO clone_table SELECT * FROM tut_tbl";
if ($mysqli->query($cpy_data)) {
    printf("fully cloned including data.
");
}
if ($mysqli->errno) {
    printf("table could not be cloned fully: %s
", $mysqli->error);
}
$mysqli->close();

Connected successfully.
Table cloned successfully.
fully cloned including data.

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  sql = "CREATE DATABASE TUTORIALS;"
  con.query(sql);

  sql = "USE TUTORIALS;"
  con.query(sql);

  sql = "CREATE TABLE CUSTOMERS(ID int NOT NULL, NAME varchar(20) NOT NULL, AGE int NOT NULL, ADDRESS varchar(25), SALARY decimal(18, 2), PRIMARY KEY (ID) );"
  con.query(sql);

  sql = "INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ''Ramesh'',32, ''Ahmedabad'', 2000.00 ),(2, ''Khilan'',25, ''Delhi'', 1500.00 ),(3, ''kaushik'',23, ''Kota'', 2000.00),(4,''Chaitali'', 25, ''Mumbai'', 6500.00 ),(5, ''Hardik'',27, ''Bhopal'', 8500.00 ),(6, ''Komal'',22, ''MP'', 4500.00 ),(7, ''Muffy'',24, ''Indore'', 10000.00 );"
  con.query(sql);

  sql = "CREATE TABLE testCUSTOMERS SELECT * FROM CUSTOMERS;"
  con.query(sql);

  sql = "SELECT * FROM testCUSTOMERS;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**Records in Clone table**")
    console.log(result);
  });
});

Connected!
--------------------------
**Records in Clone table**
[
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Ahmedabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Kota'',
    SALARY: ''2000.00''
  },
  {
    ID: 4,
    NAME: ''Chaitali'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''6500.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Bhopal'',
    SALARY: ''8500.00''
  },
  { ID: 6, NAME: ''Komal'', AGE: 22, ADDRESS: ''MP'', SALARY: ''4500.00'' },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Indore'',
    SALARY: ''10000.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class CloneTable {
    public static void main(String[] args){
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //Creates clone tables...!
            String sql = "CREATE TABLE clone_tbl LIKE customers";
            statement.executeUpdate(sql);
            System.out.println("Clone table of customers has been created successfully...!");

            connection.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}

Connected successfully...!
Clone table of customers has been created successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
source_table_name = ''tutorials_tbl''
new_table_name = ''tutorials_tbl_cloned''
#Creating a cursor object
cursorObj = connection.cursor()
cursorObj.execute(f"CREATE TABLE {new_table_name} AS SELECT * FROM {source_table_name}")
print(f"Table ''{source_table_name}'' is cloned to ''{new_table_name}'' successfully.")
cursorObj.close()
connection.close()

Table ''tutorials_tbl'' is cloned to ''tutorials_tbl_cloned'' successfully.

MySQL TRUNCATE TABLE Statement

The MySQL TRUNCATE TABLE statement is used to delete only the data of an existing table, but not the table.

This command helps to TRUNCATE a table completely in one go instead of deleting table records one by one which will be very time consuming and hefty process.

You can delete a table using the DROP TABLE command, but be careful because it completely erases both data and the table”s structure from the database. If you want to store some data again, you would need to re-create this table once again.

Syntax

Following is the basic syntax of the TRUNCATE TABLE statement −

TRUNCATE TABLE table_name

Where, table_name is the name of the table you need to delete all the records from.

Example

First of all, let us create a table with name CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, we are inserting 7 records into the above-created table using the following INSERT statement −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Using the following query, we are displaying the records of CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following are the records of CUSTOMERS table −

In the following query, we are using the TRUNCATE TABLE command to remove all the records in the CUSTOMERS table −

TRUNCATE TABLE CUSTOMERS;

Output

The records have been truncated from the CUSTOMERS table without any error.

Query OK, 0 rows affected (0.02 sec)

Verification

To verify whether the records have been truncated, let us retrieve the records using the following query −

SELECT * FROM CUSTOMERS;

As we can see the output below, there are no records present in the CUSTOMERS table. Thus, the records have been truncated.

Empty set (0.00 sec)

TRUNCATE vs DELETE

Following are some major differences between the TRUNCATE and DELETE commands, even though they work similar logically:

TRUNCATE vs DROP

The TRUNCATE and DROP are two different commands. TRUNCATE just deletes the table”s records, whereas DROP command deletes the table entirely from the database.

However, there are still some differences between these commands, which are summarized in the following table −

Truncating Table Using a Client Program

Besides truncating a table in a MySQL database with a MySQL query, we can also use a client program to perform the TRUNCATE TABLE operation.

Syntax

Following are the syntaxes to truncate a table from MySQL Database in various programming languages −

$sql = "TRUNCATE TABLE table_name";
$mysqli->query($sql);

sql = "TRUNCATE TABLE table_name";
con.query(sql);

String sql = "TRUNCATE TABLE table_name";
statement.executeUpdate(sql);

sql = "TRUNCATE TABLE table_name";
cursorObj.execute(sql);

Example

Following are the programs −

$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 = " TRUNCATE TABLE clone_table ";
if ($mysqli->query($sql)) {
    printf("table truncated successfully.
");
}
if ($mysqli->errno) {
    printf("table could not be truncated: %s
", $mysqli->error);
}
$mysqli->close();

table truncated successfully.

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "CREATE DATABASE testdb"
  con.query(sql);

  //Selecting a Database
  sql = "USE testdb"
  con.query(sql);

  //Creating table
  sql = "CREATE TABLE MyPlayers(ID INT,First_Name VARCHAR(255),Last_Name VARCHAR(255),Date_Of_Birth date,Place_Of_Birth VARCHAR(255),Country VARCHAR(255),PRIMARY KEY (ID));"
  con.query(sql);

  sql = "insert into MyPlayers values(1, ''Shikhar'', ''Dhawan'', DATE(''1981-12-05''), ''Delhi'', ''India''),(2, ''Jonathan'', ''Trott'', DATE(''1981-04-22''), ''CapeTown'', ''SouthAfrica''),(3, ''Kumara'', ''Sangakkara'', DATE(''1977-10-27''), ''Matale'', ''Srilanka''),(4, ''Virat'', ''Kohli'', DATE(''1988-11-05''), ''Delhi'', ''India''),(5, ''Rohit'', ''Sharma'', DATE(''1987-04-30''), ''Nagpur'', ''India''),(6, ''Ravindra'', ''Jadeja'', DATE(''1988-12-06''), ''Nagpur'', ''India''),(7, ''James'', ''Anderson'', DATE(''1982-06-30''), ''Burnley'', ''England'');"
  con.query(sql);

  //List of tables
  sql = "select * from MyPlayers;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**MyPlayers Table:**")
    console.log(result);
    console.log("--------------------------");
  });

  //Truncating the records of Myplayers table
  sql = "TRUNCATE TABLE MyPlayers;"
  con.query(sql);

  //fetching the records of table after truncating
  sql = "select * from MyPlayers;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**Myplayers table after truncating:**");
    console.log(result);
  });
});

Connected!
--------------------------
**MyPlayers Table:**
[
  {
    ID: 1,
    First_Name: ''Shikhar'',
    Last_Name: ''Dhawan'',
    Date_Of_Birth: 1981-12-04T18:30:00.000Z,
    Place_Of_Birth: ''Delhi'',
    Country: ''India''
  },
  {
    ID: 2,
    First_Name: ''Jonathan'',
    Last_Name: ''Trott'',
    Date_Of_Birth: 1981-04-21T18:30:00.000Z,
    Place_Of_Birth: ''CapeTown'',
    Country: ''SouthAfrica''
  },
  {
    ID: 3,
    First_Name: ''Kumara'',
    Last_Name: ''Sangakkara'',
    Date_Of_Birth: 1977-10-26T18:30:00.000Z,
    Place_Of_Birth: ''Matale'',
    Country: ''Srilanka''
  },
  {
    ID: 4,
    First_Name: ''Virat'',
    Last_Name: ''Kohli'',
    Date_Of_Birth: 1988-11-04T18:30:00.000Z,
    Place_Of_Birth: ''Delhi'',
    Country: ''India''
  },
  {
    ID: 5,
    First_Name: ''Rohit'',
    Last_Name: ''Sharma'',
    Date_Of_Birth: 1987-04-29T18:30:00.000Z,
    Place_Of_Birth: ''Nagpur'',
    Country: ''India''
  },
  {
    ID: 6,
    First_Name: ''Ravindra'',
    Last_Name: ''Jadeja'',
    Date_Of_Birth: 1988-12-05T18:30:00.000Z,
    Place_Of_Birth: ''Nagpur'',
    Country: ''India''
  },
  {
    ID: 7,
    First_Name: ''James'',
    Last_Name: ''Anderson'',
    Date_Of_Birth: 1982-06-29T18:30:00.000Z,
    Place_Of_Birth: ''Burnley'',
    Country: ''England''
  }
]
--------------------------
**Myplayers table after truncating:**
[]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TruncateTable {
    public static void main(String[] args){
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //truncate tables...!
            String sql = "TRUNCATE TABLE clone_tbl";
            statement.executeUpdate(sql);
            System.out.println("Table Truncated successfully...!");

            ResultSet resultSet = statement.executeQuery("SELECT * FROM clone_tbl");
            while (resultSet.next()){
                System.out.println(resultSet.getInt(1)+" "+resultSet.getString(2)+" "+
                        resultSet.getInt(3)+" "+ resultSet.getString(4)+" "+resultSet.getFloat(5));
                System.out.println();
            }
            connection.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}

Connected successfully...!
Table Truncated successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''tutorials_tbl_cloned''
#Creating a cursor object
cursorObj = connection.cursor()
cursorObj.execute(f"TRUNCATE TABLE {table_name}")
print(f"Table ''{table_name}'' is truncated successfully.")
cursorObj.close()
connection.close()

Table ''tutorials_tbl_cloned'' is truncated successfully.

What are Temporary Tables?

The Temporary Tables are the tables that are created in a database to store data temporarily. These tables will be automatically deleted once the current client session is terminated or ends. In addition to that, these tables can be deleted explicitly if the users decide to drop them manually. You can perform various SQL operations on temporary tables, just like you would with permanent tables, including CREATE, UPDATE, DELETE, INSERT, JOIN, etc.

Temporary tables were introduced in MySQL version 3.23. If you”re using an older version of MySQL that”s older than 3.23, you won”t be able to use temporary tables, instead you can use the Heap Tables.

As stated earlier, temporary tables will only last as long as the session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQL database server through the MySQL client program, then the temporary table will exist until you close the client or manually destroy the table.

Creating Temporary Tables in MySQL

Creating a temporary table in MySQL is very similar to creating a regular database table. But, instead of using CREATE TABLE, we use CREATE TEMPORARY TABLE statement.

Syntax

Following is the syntax to create a temporary table in MySQL −

CREATE TEMPORARY TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Example

First of all, let us create a temporary table named CUSTOMERS using the below query −

CREATE TEMPORARY TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Similar to normal tables, we can insert records into a temporary table using the INSERT statement. Here, we are inserting three records into the above created temporary table −

INSERT INTO CUSTOMERS VALUES
   (1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
   (2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
   (3, ''kaushik'', 23, ''Kota'', 2000.00 );

Execute the following query to display all the records of the temporary table CUSTOMERS.

SELECT * FROM CUSTOMERS;

Following are the records of CUSTOMERS table −

When we issue a SHOW TABLES command, our temporary table won”t be displayed in the list of tables. To verify if the temporary table exists, we need to use the SELECT statement to retrieve its data. Temporary tables will be deleted when we ends our session, so if we log out of MySQL and then try to issue the SELECT command, we won”t find the temporary table in the database.

Dropping Temporary Tables in MySQL

Though MySQL automatically removes temporary tables when your database connection ends, we can still delete them ourselves by using the DROP TEMPORARY TABLE command if we want to.

Syntax

Following is the syntax for dropping a temporary table in MySQL −

DROP TEMPORARY TABLE table_name;

Example

In the following query, we are dropping the temporary table CUSTOMERS that was created in the previous example −

DROP TEMPORARY TABLE CUSTOMERS;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.00 sec)

Verification

Now, let us verify the temporary table CUSTOMERS by retrieving it”s records using the following query −

SELECT * FROM CUSTOMERS;

Since we removed the the temporary table CUSTOMERS, it will generate an error saying the table does not exist.

ERROR 1146: Table ''TUTORIALS.CUSTOMERS'' doesn''t exist

Creating Temporary table Using a Client Program

In addition to create a temporary table in MySQL Database using the MySQL query, we can also perform the “TEMPORARY TABLE” operation on a table using a client program.

Syntax

Following are the syntaxes to create a temporary table into MySQL Database in various programming languages −

$sql="CREATE temporary Table table_name(column_name, column_type, ...)";
$mysqli->query($sql);

sql="CREATE temporary Table table_name(column_name, column_type, ...)";
con.query(sql);

String sql="CREATE temporary Table table_name(column_name, column_type, ...)";
statement.executeQuery(sql);

sql="CREATE temporary Table table_name(column_name, column_type, ...)";
cursorObj.execute(sql);

Example

Following are the programs −

$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 = "CREATE TEMPORARY TABLE SalesSummary (" . " product_name VARCHAR(50) NOT NULL, " . " total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00, " . " avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00, " . " total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 )";
if ($mysqli->query($sql)) {
    printf("temporary table created successfully.
");
}
if ($mysqli->errno) {
    printf("temporary table could not be created: %s
", $mysqli->error);
}
$mysqli->close();

temporary table created successfully.

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
 });

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "CREATE DATABASE testdb"
  con.query(sql);

  //Selecting a Database
  sql = "USE testdb"
  con.query(sql);

  //Creating table
  sql = "CREATE TEMPORARY TABLE SalesSummary (product_name VARCHAR(50) NOT NULL, total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00, avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00, total_units_sold INT UNSIGNED NOT NULL DEFAULT 0);"
  con.query(sql);

  sql = "INSERT INTO SalesSummary(product_name, total_sales, avg_unit_price, total_units_sold)VALUES(''cucumber'', 100.25, 90, 2);"
  con.query(sql);

  sql = "SELECT * FROM SalesSummary;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**SalesSummary Table:**")
    console.log(result);
  });
});

Connected!
--------------------------
**SalesSummary Table:**
[
  {
    product_name: ''cucumber'',
    total_sales: ''100.25'',
    avg_unit_price: ''90.00'',
    total_units_sold: 2
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TemporaryTable {
    public static void main(String[] args){
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //Create temporary table...!
            String sql = "CREATE TEMPORARY TABLE SalesSummary (product_name VARCHAR(50) NOT NULL, total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00, avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00, total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 )";
            statement.executeUpdate(sql);
            System.out.println("Temporary table created successfully...!");

            ResultSet resultSet = statement.executeQuery("DESCRIBE SalesSummary");
            while(resultSet.next()) {
                System.out.print(resultSet.getNString(1));
                System.out.println();
            }
            connection.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}

Connected successfully...!
Temporary table created successfully...!
product_name
total_sales
avg_unit_price
total_units_sold

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''tutorials_tbl_temp''
#Creating a cursor object
cursorObj = connection.cursor()
create_table_query = f"CREATE TEMPORARY TABLE {table_name} (NAME VARCHAR(50), ID INT)"
cursorObj.execute(create_table_query)
print(f"Temporary table ''{table_name}'' is created successfully.")
cursorObj.close()
connection.close()

Temporary table ''tutorials_tbl_temp'' is created successfully.

MySQL Repair Table Statement

There can be scenarios where tables in databases can become corrupted due to various reasons such as hardware failures, software bugs, or unexpected server crashes. When this situation happens, we cannot be able to access or manipulate the data in those tables because of data inconsistencies or errors.

In such situations, to repair those currupted tables, we use the MySQL REPAIR TABLE statement. This statement works for only certain engines such as MyISAM, etc.

Syntax

Following is the syntax of MySQL REPAIR TABLE Statement −

REPAIR [NO_WRITE_TO_BINLOG | LOCAL]
   TABLE tbl_name [, tbl_name] ...
   [QUICK] [EXTENDED] [USE_FRM]

Example

Let us start by creating a table named CUSTOMERS using the following query −

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)
);

Here, we are inserting 7 records into the above created table using the below INSERT statement −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Assume the above created table is corrupted and we are using the REPAIR TABLE statement to repair it.

REPAIR TABLE CUSTOMERS;

The above query displays an error as: “The storage engine for the table doesn”t support repair” because the REPAIR TABLE statement won”t work with the default InnoDB engine.

To repair the table, We need to change the table”s engine to MyISAM because it supports the REPAIR TABLE statement.

ALTER TABLE CUSTOMERS ENGINE = MyISAM;

Now, to repair the CUSTOMERS table, execute the following query −

REPAIR TABLE CUSTOMERS;

Output

We can see in the output below, it says OK which indicates that the table CUSTOMERS is in good condition, and there are no issues or corruption.

Repairing multiple tables

In MySQL, we can also repair multiple tables and get the results using the REPAIR TABLE Statement. To do this, we just need to list the names of the tables we want to repair, separating them with commas.

Example

Let us create three different tables with the names Test1, Test2, and Test3 using the following CREATE TABLE statements −

CREATE TABLE Test1(ID INT, Name VARCHAR(255));
CREATE TABLE Test2(ID INT, Name VARCHAR(255));
CREATE TABLE Test3(ID INT, Name VARCHAR(255));

Assume the above three tables are corrupted. Change the engine of these tables to MyISAM to repair them with REPAIR TABLE statement −

ALTER TABLE Test1 ENGINE = MyISAM;
ALTER TABLE Test2 ENGINE = MyISAM;
ALTER TABLE Test3 ENGINE = MyISAM;

Now, to repair these tables, execute the following query −

REPAIR TABLE Test1, Test2, Test3;

As we can see in the output below, all three tables are in good condition, and there are no issues or corruption.

Repair Table Options

We have various optional clauses to use with REPAIR TABLE such as QUICK, EXTENDED, and, USE_FRM clause. Let us discuss them one by one with suitable examples.

QUICK Clause

The QUICK clause is the is the default and it is most commonly used with REPAIR TABLE. If you specify the QUICK clause, MySQL will repair the table without re-creating it. −

In the following example, we are using the QUICK clause with the REPAIR TABLE statement to repair the CUSTOMERS table.

REPAIR TABLE CUSTOMERS QUICK;

Executing the query above will produce the following output −

EXTENDED Clause

If we specify the EXTENDED clause, MySQL not only repairs the table but also rebuilds the index and optimizes the table structure.

Note: The EXTENDED clause is a more time-consuming compared to QUICK clause.

In the following example, we are using the EXTENDED clause with the REPAIR TABLE statement to repair the CUSTOMERS table.

REPAIR TABLE CUSTOMERS EXTENDED;

Executing the query above will produce the following output −

USE_FRM clause

We can use the USE_FRM clause, in case the MYI index file is missing. If you provide this clause the .NYI file will be recreated using information from the data dictionary −

Here, we are using the USE_FRM clause with the REPAIR TABLE statement to repair the CUSTOMERS table.

REPAIR TABLE CUSTOMERS USE_FRM;

Executing the query above will produce the following output −

Repairing table Using a Client Program

Besides repairing a table in a MySQL database with a MySQL query, we can also use a client program to perform the REPAIR TABLE operation.

Syntax

Following are the syntaxes to repair a table in various programming languages −

$sql="Repair TABLE table_names";
$mysqli->query($sql);

sql=" REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ...
   [QUICK] [EXTENDED] [USE_FRM]";
con.query(sql);

String sql="Repair TABLE table_names";
statement.executeUpdate(sql);

sql="REPAIR TABLE table_name";
cursorObj.execute(sql);

Example

Following are the programs −

$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 = " REPAIR TABLE SalesSummary ";
if ($mysqli->query($sql)) {
    printf(" Table repair successfully.
");
}
if ($mysqli->errno) {
    printf("table could not be repaired .
", $mysqli->error);
}
$mysqli->close();

Table repair successfully.

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  sql = "Create Database TUTORIALS"
  con.query(sql);

  sql = "USE TUTORIALS"
  con.query(sql);

  sql = "CREATE TABLE sales(ID INT, ProductName VARCHAR(255), CustomerName VARCHAR(255), DispatchDate date, DeliveryTime time, Price INT, Location VARCHAR(255));"
  con.query(sql);

  sql = "insert into sales values(1, ''Key-Board'', ''Raja'', DATE(''2019-09-01''), TIME(''11:00:00''), 7000, ''Hyderabad''),(2, ''Earphones'', ''Roja'', DATE(''2019-05-01''), TIME(''11:00:00''), 2000, ''Vishakhapatnam''),(3, ''Mouse'', ''Puja'', DATE(''2019-03-01''), TIME(''10:59:59''), 3000, ''Vijayawada''),(4, ''Mobile'', ''Vanaja'', DATE(''2019-03-01''), TIME(''10:10:52''), 9000, ''Chennai''),(5, ''Headset'', ''Jalaja'', DATE(''2019-04-06''), TIME(''11:08:59''), 6000, ''Goa'');"
  con.query(sql);

  sql = "ALTER TABLE Sales ENGINE = MyISAM;"
  con.query(sql);

  sql = "REPAIR TABLE Sales;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Connected!
--------------------------
[
  {
    Table: ''tutorials.sales'',
    Op: ''repair'',
    Msg_type: ''status'',
    Msg_text: ''OK''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class RepairTable{
        public static void main(String[] args){
            String url = "jdbc:mysql://localhost:3306/TUTORIALS";
            String username = "root";
            String password = "password";
            try{
                Class.forName("com.mysql.cj.jdbc.Driver");
                Connection connection = DriverManager.getConnection(url, username, password);
                Statement statement = connection.createStatement();
                System.out.println("Connected successfully...!");

                //Repair tables...!
                String sql = "REPAIR TABLE customers";
                statement.executeUpdate(sql);
                System.out.println("Table repaired successfully...!");

                connection.close();
            }
            catch(Exception e){
                System.out.println(e);
            }
        }
}

Connected successfully...!
Table repaired successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''tutorials_tbl_temp''
#Creating a cursor object
cursorObj = connection.cursor()
repair_table_query = f"REPAIR TABLE {table_name}"
cursorObj.execute(repair_table_query)
print(f"Table ''{table_name}'' is repaired successfully.")
# Fetch and consume any remaining results from the cursor
# ensuring that there are no unread results before closing the cursor.
for _ in cursorObj:
    pass
cursorObj.close()
connection.close()

Table ''tutorials_tbl_temp'' is repaired successfully.

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 −

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 −

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 −

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 −

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 −

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 −

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 −

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 −

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 −

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 −

$sql="Describe Table_name";
$mysqli->query($sql);

sql="Describe Table_name";
con.query(sql);

String sql="Describe Table_name";
statement.executeUpdate(sql);

sql="Describe Table_name";
cursorObj.execute(sql);

Example

Following are the programs −

$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();

Table described successfully.
ID - int
ProductName - varchar(255)
CustomerName - varchar(255)
DispatchDate - date
DeliveryTime - time
Price - int
Location - varchar(255)

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  sql = "USE TUTORIALS"
  con.query(sql);

  sql = "CREATE TABLE SALES(ID INT, ProductName VARCHAR(255), CustomerName VARCHAR(255), DispatchDate date, DeliveryTime time, Price INT, Location VARCHAR(255));"
  con.query(sql);

  sql = "DESCRIBE SALES;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Connected!
--------------------------
[
  {
    Field: ''ID'',
    Type: ''int'',
    Null: ''YES'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''ProductName'',
    Type: ''varchar(255)'',
    Null: ''YES'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''CustomerName'',
    Type: ''varchar(255)'',
    Null: ''YES'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''DispatchDate'',
    Type: ''date'',
    Null: ''YES'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''DeliveryTime'',
    Type: ''time'',
    Null: ''YES'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''Price'',
    Type: ''int'',
    Null: ''YES'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''Location'',
    Type: ''varchar(255)'',
    Null: ''YES'',
    Key: '''',
    Default: null,
    Extra: ''''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DescribeTable {
    public static void main(String[] args){
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //Describe table details...!
            ResultSet resultSet = statement.executeQuery("DESCRIBE customers");
            System.out.println("Following is the table description");
            while(resultSet.next()) {
                System.out.print(resultSet.getNString(1));
                System.out.println();
            }
            connection.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}

Connected successfully...!
Following is the table description
ID
NAME
AGE
ADDRESS
SALARY

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''tutorials_tbl''
#Creating a cursor object
cursorObj = connection.cursor()
describe_table_query = f"DESCRIBE {table_name}"
cursorObj.execute(describe_table_query)
columns_info = cursorObj.fetchall()
print(f"Description of table ''{table_name}'':")
for column in columns_info:
    print(f"Column Name: {column[0]}, Type: {column[1]}, Null: {column[2]}, Key: {column[3]}, Default: {column[4]}, Extra: {column[5]}")
cursorObj.close()
connection.close()

Description of table ''tutorials_tbl'':
Column Name: tutorial_id, Type: b''int'', Null: NO, Key: PRI, Default: None, Extra: auto_increment
Column Name: tutorial_title, Type: b''varchar(100)'', Null: NO, Key: , Default: None, Extra:
Column Name: tutorial_author, Type: b''varchar(40)'', Null: NO, Key: , Default: None, Extra:
Column Name: submission_date, Type: b''date'', Null: YES, Key: , Default: None, Extra:
Column Name: tutorial_name, Type: b''varchar(20)'', Null: YES, Key: , Default: None, Extra: