Now that we have learned how to create tables in MySQL and insert values into it in the previous tutorials, the next step is to check whether the values are recorded in this table or not. To do this, one must use the SELECT statement to retrieve and view the records in that specific table.”

MySQL Select Statement

The MySQL SELECT command is used to fetch data from the MySQL database in the form of a result table. These result tables are called result-sets.

Note − We can use this command at ”mysql>” prompt as well as in any script like PHP, Node.js, Java, python, etc.

Syntax

Here is generic SQL syntax of SELECT command to fetch data from the MySQL table −

SELECT field1, field2,...fieldN
FROM table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]

• You can use one or more tables separated by comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command.

• We can fetch one or more fields in a single SELECT command.

• We can specify star (*) in place of fields. In this case, SELECT will return all the fields.

• We can specify any condition using the WHERE clause.

• We can specify an offset using OFFSET from where SELECT will start returning records. By default, the offset starts at zero.

• We can limit the number of returns using the LIMIT attribute.

Fetching Data Using SELECT from Command Prompt

This will use SQL SELECT command to fetch data from an MySQL table.

Example

First of all, let us create a table named 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)
);

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 Retrieve All Fields

Now, to retrieve all the data present in a CUSTOMERS table, we use the following SELECT statement −

SELECT * from CUSTOMERS;

Following are the records present in the CUSTOMERS table −

To Retrieve Selective Fields

Here, we are retrieving only three columns/fields, ID, NAME, and ADDRESS present in the CUSTOMERS table, using the following query −

SELECT ID, NAME, ADDRESS FROM CUSTOMERS;

On executing the given query, the output is displayed as follows −

Computing using SELECT in Command Prompt

The SELECT statement is not only used to fetch data from tables but can also be used to get the results of mathematical computations in a tabular format. In these cases, you don”t have to mention a specific database table in the SELECT statement.

Following is the syntax to do so −

SELECT [math_computation];

Example

In the following example, let us solve a mathematical computation using the SELECT statement −

SELECT 46475*453;

Output

The output for the program query is produced as given below −

Aliasing a Column in SELECT Statement

MySQL database provides a method to alias column names into a more understandable and relative name when being displayed. This is done using the ”AS” keyword. This keyword is used in the SELECT statement as well.

Following is the syntax to do so −

SELECT column_name AS alias_name FROM table_name;

You can also use an alias to display select expressions with the same syntax; you should use a select expression instead of column_name in the syntax.

Example

In the example below, we are retrieving the ID column from the previously created CUSTOMERS table. We aliased the ID column as “Identity_Document” −

SELECT ID AS Identity_Document FROM CUSTOMERS;

As we can see the output, the alias name ”Identity_Document” has been used instead of ”ID”.

Select Query into MySQL Database Using a Client Program

Besides getting data from a table using the SELECT query, you can also use a client program to perform the SELECT operation on a table.

Syntax

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

$sql="SELECT COLUMN_NAME1, COLUMN_NAME2,... FROM TABLE_NAME";
$mysqli->query($sql);

sql="SELECT field1, field2,...fieldN FROM table_name";
con.query(sql);

String sql="SELECT COLUMN_NAME1, COLUMN_NAME2,... FROM TABLE_NAME";
statement.executeQuery(sql);

select_query = "SELECT COLUMN1, COLUMN2,.. FROM TABLE_NAME";
cursorObj.execute(select_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 = "SELECT * FROM tutorials_tbl";
if($result = $mysqli->query($sql)){
   printf("Select statement executed successfully..!  ");
   printf("Records are: ");
   while($row = mysqli_fetch_row($result)){
      print_r ($row);
   }
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Select statement executed successfully..!  Records are:
Array
(
    [0] => 1
    [1] => MySQL Tut
    [2] => unknown
    [3] => 2023-07-25
)
Array
(
    [0] => 2
    [1] => PHP Tut
    [2] => unknown2
    [3] => 2023-08-12
)

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

  //Creating a Database
  sql = "CREATE DATABASE IF NOT EXISTS TUTORIALS"
  con.query(sql);

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

  //Creating a Table
  sql = "CREATE TABLE IF NOT EXISTS tutorials_tbl(tutorial_id INT NOT NULL PRIMARY KEY, tutorial_title VARCHAR(100) NOT NULL, tutorial_author VARCHAR(40) NOT NULL, submission_date DATE)"
  con.query(sql);
  //Inserting records into table
  sql = "INSERT INTO tutorials_tbl(tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES(1, ''Learn PHP'', ''John Paul'', NOW()), (2, ''Learn MySQL'', ''Abdul S'', NOW()), (3, ''JAVA Tutorial'', ''Sanjay'', ''2007-05-06''), (4, ''Python Tutorial'', ''Sasha Lee'', ''2016-09-04''), (5, ''Hadoop Tutorial'', ''Chris Welsh'', NOW())"
  con.query(sql);

  //Selecting all the records from table
  sql = "SELECT * FROM tutorials_tbl"
  con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
  });
});

Connected!
[
  {
    tutorial_id: 1,
    tutorial_title: ''Learn PHP'',
    tutorial_author: ''John Paul'',
    submission_date: 2023-07-25T18:30:00.000Z
  },
  {
    tutorial_id: 2,
    tutorial_title: ''Learn MySQL'',
    tutorial_author: ''Abdul S'',
    submission_date: 2023-07-25T18:30:00.000Z
  },
  {
    tutorial_id: 3,
    tutorial_title: ''JAVA Tutorial'',
    tutorial_author: ''Sanjay'',
    submission_date: 2007-05-05T18:30:00.000Z
  },
  {
    tutorial_id: 4,
    tutorial_title: ''Python Tutorial'',
    tutorial_author: ''Sasha Lee'',
    submission_date: 2016-09-03T18:30:00.000Z
  },
  {
    tutorial_id: 5,
    tutorial_title: ''Hadoop Tutorial'',
    tutorial_author: ''Chris Welsh'',
    submission_date: 2023-07-25T18:30:00.000Z
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class SelectQuery {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		ResultSet rs;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * FROM tutorials_tbl";
            rs = st.executeQuery(sql);
            System.out.println("Select query executed successfully..!");
            System.out.println("Table records: ");
            while(rs.next()) {
            	String tutorial_id = rs.getString("tutorial_id");
            	String tutorial_title = rs.getString("tutorial_title");
            	String tutorial_author = rs.getString("tutorial_author");
            	String submission_date = rs.getString("submission_date");
            	System.out.println("Id: " + tutorial_id + ", Title: " + tutorial_title + ", Author: " +  tutorial_author + ", Submission_date: " + submission_date);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

Select query executed successfully..!
Table records:
Id: 1, Title: Learn PHP, Author: John Paul, Submission_date: 2023-08-08

import mysql.connector
import datetime
#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()
select_query = f"SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM {table_name}"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Tutorial Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

Derived Table Result:
Tutorial Table Data:
(1, ''Learn PHP'', ''John Paul'', datetime.date(2023, 3, 28))
(2, ''Learn MySQL'', ''Abdul S'', datetime.date(2023, 3, 28))
(3, ''JAVA Tutorial'', ''Sanjay'', datetime.date(2007, 5, 6))
(4, ''Python Tutorial'', ''Sasha Lee'', datetime.date(2016, 9, 4))
(5, ''Hadoop Tutorial'', ''Chris Welsh'', datetime.date(2023, 3, 28))
(6, ''R Tutorial'', ''Vaishnav'', datetime.date(2011, 11, 4))

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 is an open-source relational management system (RDBMS) that allows us to store and manage data or information. The queries in MySQL are commands that are used to retrieve or manipulate the data from a database table.

Following are the commonly used commands in MySQL: SELECT, UPDATE, DELETE, INSERT INTO, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE DATABASE, ALTER DATABASE, CREATE INDEX, DROP INDEX, etc.

Note: These keywords are not case-sensitive. For instance, create table is the same as CREATE TABLE.

MySQL Create Database

The create database query in MySQL can be used to create a database in the MySQL server.

Syntax

Following is the syntax for the query −

CREATE DATABASE databasename;

Example

In the following query, we are creating a database named tutorials.

CREATE DATABASE tutorials;

MySQL Use Database

The MySQL use database query is used to select a database to perform operations such as creating, inserting, updating tables or views, etc.

Syntax

Following is the syntax for the query −

USE database_name;

Example

The following query selects a database named tutorials −

USE tutorials;

MySQL Create Query

The MySQL create query can be used to create databases, tables, indexes, views, etc.

Syntax

Following is the syntax for the query −

CREATE [table table_name |index index_name | view view_name];

Example

Here, we are creating a table named STUDENTS using the following CREATE query −

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

MySQL Insert Query

The MySQL insert query can be used to insert records within a specified table.

Syntax

Following is the syntax for the query −

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Example

In the following query, we are inserting some records into a table named CUSTOMERS −

INSERT INTO CUSTOMERS (ID, NAME, AGE) VALUES (1, "Nikhilesh", 28);
INSERT INTO STUDENTS (ID, NAME, AGE) VALUES (2, "Akhil", 23);
INSERT INTO STUDENTS (ID, NAME, AGE) VALUES (3, "Sushil", 35);

MySQL Update Query

The MySQL update query can be used to modify the existing records in a specified table.

Syntax

Following is the syntax for the query −

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example

UPDATE CUSTOMERS SET NAME = "Nikhil" WHERE ID = 1;

MySQL Alter Query

The ALTER query in MySQL can be used to add, delete, or modify columns in an existing table.

Syntax

Following is the syntax for the query −

ALTER TABLE table_name
[ADD|DROP] column_name datatype;

Example

Here, we are trying to add a column named ADDRESS to the existing CUSTOMERS table.

ALTER TABLE CUSTOMERS
ADD COLUMN ADDRESS varchar(50);

MySQL Delete Query

The Delete query in MySQL can be used to delete existing records in a specified table.

Syntax

Following is the syntax for the query −

DELETE FROM table_name WHERE condition;

Example

In the following query, we are deleting a record from CUSTOMERS table where the ID is equal to 3.

DELETE FROM CUSTOMERS WHERE ID = 3;

MySQL Truncate Table Query

The MySQL truncate table query can be used to remove all the records but not the table itself.

Syntax

Following is the syntax for the query −

TRUNCATE [TABLE] table_name;

Example

In the following query, we are removing all the records from the CUSTOMERS table using the truncate table query −

TRUNCATE TABLE CUSTOMERS;

MySQL Drop Query

The MySQL drop query is used to delete an existing table in a database.

Syntax

Following is the syntax for the query −

DROP TABLE table_name;

Example

Here, we are trying to delete the table named CUSTOMERS using the drop table query.

DROP TABLE CUSTOMERS;

MySQL Derived Tables

The Derived tables are pretty much what their name describes: they are the tables that are derived from another MySQL database table (main table). In other words, the derived table is a virtual result-set obtained from a SELECT statement given as a subquery to another SELECT statement of the main table.

This table is similar to a temporary table. But unlike temporary tables, you need not create a derived table separately; the records in it are retrieved from the main table using a subquery. Therefore, similar to the actual database table, a derived table can also be displayed as a result-set of computations, aggregate functions, etc.

Syntax

Following is the basic syntax to display a derived table in MySQL −

SELECT column_name(s) FROM (subquery) AS derived_table_name;

Example

Let us see a simple example demonstrating how derived table is displayed in MySQL. In the following query, we are creating a new table CUSTOMERS −

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

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 retrieve the records of the CUSTOMERS table, execute the following query −

SELECT * FROM CUSTOMERS;

Following are the records present in CUSTOMERS table −

Now, we are retrieving a derived table from this CUSTOMERS table using the following query −

SELECT ID, NAME, SALARY FROM (SELECT * FROM CUSTOMERS) AS DERIVED_CUSTOMERS;

The derived table DERIVED_CUSTOMERS is hence obtained with ID, NAME and SALARY as its attributes.

Using WHERE Clause

We can also use the WHERE clause to filter records (or rows) from the derived table. Following is the syntax for it −

SELECT column_name(s) FROM (subquery) AS derived_table_name WHERE [condition];

Example

In the following query, we are retrieving a derived table from the CUSTOMERS table created initially. We are doing this by filtering rows from it using the WHERE clause −

SELECT ID, NAME, SALARY FROM (SELECT * FROM CUSTOMERS) AS DERIVED_CUSTOMERS
WHERE DERIVED_CUSTOMERS.SALARY > 5000.00;

Executing the query above will produce the following output −

Aliasing a Column in Derived Table

In derived tables, not only the table name, but we can also alias a column name while displaying the contents. Following is the syntax −

SELECT column_name(s) AS alias_name(s) FROM (subquery) AS derived_table_name;

Example

In the example below, we are displaying the derived table from the CUSTOMERS table with the aliased columns using the following query −

SELECT ID AS DERIVED_ID, NAME AS DERIVED_NAME, SALARY AS DERIVED_SALARY
FROM (SELECT * FROM CUSTOMERS) AS DERIVED_CUSTOMERS;

Output

Executing the query above will produce the following output −

Displaying Aggregate Functions as Derived Tables

We can also show the result of an aggregate function or calculations performed on the main table”s records as a derived table.

Following is the syntax to display aggregate functions as a derived table −

SELECT function_name() FROM (subquery) AS derived_table_name;

Example

In the following query, we are using the aggregate SUM() function to calculate the total salary from the CUSTOMERS table −

SELECT SUM(SALARY) FROM (SELECT SALARY FROM CUSTOMERS) AS DERIVED_CUSTOMERS;

Output

Executing the query above will produce the following output −

Example

In the following query, we use the aggregate AVG() function to calculate the average salary of customers from the CUSTOMERS table.

SELECT AVG(DERIVED_SUM) AS AVERAGE_SALARY
FROM (SELECT SUM(SALARY) AS DERIVED_SUM FROM CUSTOMERS) AS DERIVED_CUSTOMERS;

Output

Executing the query above will produce the following output −

Deriving Table Using a Client Program

Besides using MySQL queries to derive a table from another database table (main table), we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

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

$sql="SELECT col_1, col_2 FROM table_name WHERE col_name IN (SELECT col_name FROM table_name)";
$mysqli->query($sql);

sql ="SELECT column_name(s) FROM (subquery) AS derived_table_name";
con.query(sql);

String sql="SELECT col_1, col_2 FROM table_name WHERE col_name IN (SELECT col_name FROM table_name)";
statement.executeQuery(sql);

sql="SELECT col_1, col_2 FROM table_name WHERE col_name IN (SELECT col_name FROM 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.
'');

//derived table (sub query)
$sql = "SELECT tutorial_title, tutorial_author FROM tutorials_table WHERE tutorial_id IN (SELECT tutorial_id FROM tutorials_table);";
if ($result = $mysqli->query("$sql")) {
    printf("Derived table query worked successfully!.
");
    while ($res = mysqli_fetch_array($result)) {
        print_r($res);
    }
}
if ($mysqli->errno) {
    printf("Derived table could not be worked!.
", $mysqli->error);
}

$mysqli->close();

Derived table query worked successfully!
Array
(
    [0] => MySQL
    [tutorial_title] => MySQL
    [1] => Aman kumar
    [tutorial_author] => Aman kumar
)
Array
(
    [0] => Python
    [tutorial_title] => Python
    [1] => Sarika Singh
    [tutorial_author] => Sarika Singh
)

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 SAMPLE(ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(25),SALARY DECIMAL(18, 2),PRIMARY KEY (ID));"
  con.query(sql);

  sql = "INSERT INTO SAMPLE 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);

  //retrieving a derived table
  sql = "SELECT ID, NAME, SALARY FROM (SELECT * FROM SAMPLE) AS DERIVED_SAMPLE;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Connected!
--------------------------
[
  { ID: 1, NAME: ''Ramesh'', SALARY: ''2000.00'' },
  { ID: 2, NAME: ''Khilan'', SALARY: ''1500.00'' },
  { ID: 3, NAME: ''kaushik'', SALARY: ''2000.00'' },
  { ID: 4, NAME: ''Chaitali'', SALARY: ''6500.00'' },
  { ID: 5, NAME: ''Hardik'', SALARY: ''8500.00'' },
  { ID: 6, NAME: ''Komal'', SALARY: ''4500.00'' },
  { ID: 7, NAME: ''Muffy'', SALARY: ''10000.00'' }
]

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

public class DerivedTable {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...!");

        //Lock table....
        String sql = "SELECT tutorial_title, tutorial_author FROM tutorials_tbl WHERE tutorial_id IN (SELECT tutorial_id FROM tutorials_tbl)";
        ResultSet resultSet = statement.executeQuery(sql);
        System.out.println("Table derived successfully...!");
        while (resultSet.next()) {
            System.out.print(resultSet.getString(1)+ " " +resultSet.getString(2));
            System.out.println();
        }

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

Connected successfully...!
Table derived successfully...!
Learn PHP John Paul
Learn MySQL Abdul S
JAVA Tutorial Sanjay
Python Tutorial Sasha Lee
Hadoop Tutorial Chris Welsh

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''tutorials_tbl''
# Main query with a derived table (subquery)
main_query = """
SELECT d.tutorial_id, d.tutorial_title, d.tutorial_author, d.submission_date
FROM (
    SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
    FROM tutorials_tbl
    WHERE submission_date >= ''2023-01-01''
) AS d
WHERE d.tutorial_author LIKE ''%Paul%''
"""
cursorObj = connection.cursor()
cursorObj.execute(main_query)
result = cursorObj.fetchall()
print("Derived Table Result:")
for row in result:
    print(f"| {row[0]:<11} | {row[1]:<15} | {row[2]:<15} | {row[3]:<15} |")
cursorObj.close()
connection.close()

Derived Table Result:
| 1           | Learn PHP       | John Paul       | <15 |

MySQL database provides a multi-user environment, that allows multiple clients to access the database at the same time. To run this environment smoothly, MySQL introduced the concept of locks.

A client in a session can lock a certain table they are working on, in order to prevent other clients from using the same table. This process will avoid any data losses that might occur when multiple users work on the same table simultaneously.

A client can lock a table and unlock it whenever needed. However, if a table is already locked by a client session, it cannot be accessed by other client sessions until it is released.

Locking Tables in MySQL

You can restrict the access to records of the tables in MYSQL by locking them. These locks are used to keep other sessions away from modifying the tables in the current session.

MySQL sessions can acquire or release locks on the table only for itself. To lock a table using the MySQL LOCK TABLES Statement you need have the TABLE LOCK and SELECT privileges.

These locks are used to solve the concurrency problems. There are two kinds of MYSQL table locks −

• READ LOCK − If you apply this lock on a table the write operations on it are restricted. i.e., only the sessions that holds the lock can write into this table.

• WRITE LOCK − This lock allows restricts the sessions (that does not possess the lock) from performing the read and write operations on a table.

Syntax

Following is the syntax of the MySQL LOCK TABLES Statement −

LOCK TABLES table_name [READ | WRITE];

Unlocking Tables in MySQL

Once the client session is done using/accessing a MySQL table, they must unlock the table for other client sessions to use it. To do so, you can use the MySQL UNLOCK TABLE statement. This will release the table until other sessions lock it again.

Syntax

Following is the syntax of the MySQL UNLOCK TABLES Statement −

UNLOCK TABLES;

Example

Let us start with creating a table named CUSTOMERS that contains the details as shown below −

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, let”s insert 2 records into the above created table using the INSERT statement as −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
  (1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
  (2, ''Khilan'', 25, ''Delhi'', 1500.00 );

Create another table named BUYERS using the following query −

CREATE TABLE BUYERS (
   B_ID INT AUTO_INCREMENT,
   B_NAME VARCHAR(20) NOT NULL,
   B_AGE INT NOT NULL,
   B_ADDRESS CHAR (25),
   B_SALARY DECIMAL (18, 2),
   PRIMARY KEY (B_ID)
);

Following queries inserts records into the BUYERS table using the INSERT INTO SELECT statement. Here, we are trying to insert records from the CUSTOMERS table to BUYERS table.

Locking and Unlocking:

Here before the transfer, we are acquiring the write lock on the BUYERS table to which we are inserting records and acquiring read lock on the CUSTOMERS table from which we are inserting records. Finally, after the transfer we are releasing the records.

LOCK TABLES CUSTOMERS READ, BUYERS WRITE;

INSERT INTO BUYERS (B_ID, B_NAME, B_AGE, B_ADDRESS, B_SALARY)
   SELECT
      ID, NAME, AGE, ADDRESS, SALARY
   FROM
      CUSTOMERS
   WHERE
      ID = 1 AND NAME = ''Ramesh
INSERT INTO BUYERS (B_ID, B_NAME, B_AGE, B_ADDRESS, B_SALARY)
   SELECT
      ID, NAME, AGE, ADDRESS, SALARY
   FROM
      CUSTOMERS
   WHERE
      ID = 2 AND NAME = ''Khilan

UNLOCK TABLES;

Verification

We can verify the contents of the BUYERS table using the below query −

SELECT * FROM BUYERS;

As we can see in the BUYERS table, the records has been transferred.

Table Locking Using a Client Program

Besides locking a table in a MySQL database with a MySQL query, we can also use a client program to perform the LOCK TABLES operation.

Syntax

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

$sql="LOCK TABLES table_name [READ | WRITE]";
$mysqli->query($sql);

sql = "LOCK TABLES table_name [READ | WRITE]";
con.query(sql);

String sql="LOCK TABLES table_name [READ | WRITE]";
statement.executeUpdate(sql);

sql="LOCK TABLES table_name [READ | WRITE]";
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.
'');

// Here we are locking two table;
$sql = "LOCK TABLES tut_tbl READ, clone_table WRITE";
if ($mysqli->query($sql)) {
    printf("Table locked successfully!.
");
}
if ($mysqli->errno) {
    printf("Table could not be locked!.
", $mysqli->error);
}
$mysqli->close();

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

  sql = "CREATE TABLE SalesDetails (ID INT, ProductName VARCHAR(255), CustomerName VARCHAR(255), DispatchDate date, DeliveryTime time, Price INT, Location VARCHAR(255), CustomerAge INT, CustomrtPhone BIGINT, DispatchAddress VARCHAR(255), Email VARCHAR(50));"
  con.query(sql);

  sql = "insert into SalesDetails values(1, ''Key-Board'', ''Raja'', DATE(''2019-09-01''), TIME(''11:00:00''), 7000, ''Hyderabad'', 25, ''9000012345'', ''Hyderabad - Madhapur'', ''pujasharma@gmail.com'');"
  con.query(sql);
  sql = "insert into SalesDetails values(2, ''Mobile'', ''Vanaja'', DATE(''2019-03-01''), TIME(''10:10:52''), 9000, ''Chennai'', 30, ''90000123654'', ''Chennai- TNagar'', ''vanajarani@gmail.com'');"
  con.query(sql);

  sql = "CREATE TABLE CustContactDetails (ID INT,Name VARCHAR(255), Age INT,Phone BIGINT, Address VARCHAR(255), Email VARCHAR(50));"
  con.query(sql);

  sql = "LOCK TABLES SalesDetails READ, CustContactDetails WRITE;"
  con.query(sql);

  sql = "INSERT INTO CustContactDetails (ID, Name, Age, Phone, Address, Email) SELECT ID, CustomerName, CustomerAge, CustomrtPhone, DispatchAddress, Email FROM SalesDetails  WHERE  ID = 1 AND CustomerName = ''Raja"
  con.query(sql);

  sql = "INSERT INTO CustContactDetails (ID, Name, Age, Phone, Address, Email) SELECT ID, CustomerName, CustomerAge, CustomrtPhone, DispatchAddress, Email FROM  SalesDetails WHERE ID = 2 AND CustomerName = ''Vanaja"
  con.query(sql);

  sql = "UNLOCK TABLES;"
  con.query(sql);

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

Connected!
--------------------------
[
  {
    ID: 1,
    Name: ''Raja'',
    Age: 25,
    Phone: 9000012345,
    Address: ''Hyderabad - Madhapur'',
    Email: ''pujasharma@gmail.com''
  },
  {
    ID: 2,
    Name: ''Vanaja'',
    Age: 30,
    Phone: 90000123654,
    Address: ''Chennai- TNagar'',
    Email: ''vanajarani@gmail.com''
  }
]

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

public class TableLock {
    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...!");

            //Lock table....
            String sql = "LOCK TABLES tutorials_tbl READ, clone_tbl WRITE";
            statement.executeUpdate(sql);
            System.out.println("Table Locked successfully...!");

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

Connected successfully...!
Table Locked successfully...!

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()
lock_table_query = f"LOCK TABLES {table_name} WRITE"
cursorObj.execute(lock_table_query)
print(f"Table ''{table_name}'' is locked successfully.")
cursorObj.close()
connection.close()

Table ''tutorials_tbl'' is locked successfully.

The MySQL DROP TABLE statement

The MySQL DROP TABLE statement is a Data Definition Language (DDL) command that is used to remove a table”s definition, and its data, indexes, triggers, constraints and permission specifications (if any). In simple terms, this statement will delete the entire table from the database.

However, while using DROP TABLE command, we need make note of the following −

• You should be very careful while using this command because once a table is deleted then all the information available in that table will also be lost forever.

• To drop a table in a database, one must require ALTER permission on the specified table and CONTROL permissions on the table schema.

• Even though it is a data definition language command, it is different from TRUNCATE TABLE statement as the DROP statement completely removes the table from the database.

Syntax

Following is the syntax of MySQL DROP TABLE statement −

DROP TABLE table_name ;

Dropping Tables from a database

To drop tables from a database, we need to use the MySQL DROP TABLE command. But we must make sure that the table exists in the database, before dropping it. If we try to drop a table that does not exist in the database, an error is raised.

Example

Let us start by creating a database TUTORIALS by executing below statement −

CREATE DATABASE TUTORIALS;

Using the following query, change the database to TUTORIALS −

USE TUTORIALS;

Create a a table CUSTOMERS using the following CREATE TABLE statement −

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

To verify whether the above created is created in the TUTORIALS database or not, execute the following SHOW TABLES command −

SHOW TABLES IN TUTORIALS;

As we can see in the output below, the CUSTOMERS table is created in the TUTORIALS database.

Now, let us use the MySQL DROP TABLE statement to delete the above created CUSTOMERS table −

DROP TABLE CUSTOMERS;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.02 sec)

Verification

Since we have removed the table CUSTOMERS, if we try to verify the list of tables again, using the “SHOW TABLES” query it will display an Empty Set as shown below −

Empty set (0.00 sec)

The IF EXISTS clause

Instead of constantly checking whether a table exists or not in a database before deleting it, we can use the IF EXISTS clause along with the DROP TABLE statement.

When we specify this clause in the DROP TABLE query, it will automatically verify if the table exists in the current database. If it exists, it will then delete the table. If the table doesn”t exist, the query will be ignored.

Syntax

Following is the basic syntax of DROP TABLE IF EXISTS statement −

DROP TABLE [IF EXISTS] table_name;

Example

Here, we are using just DROP TABLE statement to drop the CUSTOMERS table which has been deleted already in the previous example.

DROP TABLE CUSTOMERS;

Output

Since, we are not using IF EXISTS with the DROP TABLE command, it will display an error as follows −

ERROR 1051 (42S02): Unknown table ''tutorials.customers''

Example

If we try to drop CUSTOMERS that does not exist in the database, using the IF EXISTS clause, the query will be ignored without issuing any error −

DROP TABLE IF EXISTS CUSTOMERS;

Executing the query above will produce the following output −

Query OK, 0 rows affected, 1 warning (0.01 sec)

Dropping Table Using a Client Program

In addition to dropping a table from MySQL Database using the MySQL query, we can also perform the DROP TABLE operation on a table using a client program.

Syntax

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

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

sql = "DROP TABLE Table_name";
con.query(sql);

String sql="DROP TABLE Table_name";
statement.execute(sql);

sql="DROP TABLE Table_name";
cursorObj.execute(sql);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''root@123
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if($mysqli->connect_errno ) {
   printf("Connect failed: %s<br />", $mysqli->connect_error);
   exit();
}
printf(''Connected successfully.<br />'');

if ($mysqli->query("Drop Table tutorials_tbl")) {
   printf("Table tutorials_tbl dropped successfully.<br />");
}
if ($mysqli->errno) {
   printf("Could not drop table: %s<br />", $mysqli->error);
}

$mysqli->close();

Connected successfully.
Table tutorials_tbl dropped 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("--------------------------");

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

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

Connected!
--------------------------
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  info: '''',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}

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

public class DropTable {
    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 a table....
          String sql = "DROP TABLE customer";
          statement.execute(sql);
          System.out.println("Table Dropped successfully...!");

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

Connected successfully...!
Table Dropped 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()
drop_table_query = f"DROP TABLE {table_name}"
cursorObj.execute(drop_table_query)
print(f"Table ''{table_name}'' is dropped successfully.")
cursorObj.close()
connection.close()

Table ''tutorials_tbl_cloned'' is dropped successfully.

The ALTER TABLE statement in MySQL can be used to change the structure of a table. For instance, we can add, delete, or rename the columns, etc. using this statement.

Following are the two options that can be used with the ALTER TABLE statement to rename a column(s) of a table:

• RENAME COLUMN
• CHANGE COLUMN

Note: Renaming a column(s) of table requires ALTER and DROP privilages.

Using the RENAME COLUMN

In MySQL, we can change the name of one or multiple columns of a specified table using the ALTER TABLE RENAME COLUMN command.

Syntax

Following is the syntax to rename a column in MySQL table −

ALTER TABLE table_name
RENAME COLUMN old_column1_name TO new_column1_name,
RENAME COLUMN old_column2_name TO new_column2_name,
...;

Example

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

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

Here, we are using the DESCRIBE command to display the information about the above created table structure −

DESCRIBE CUSTOMERS;

As we can see in the table below, we have three columns present in CUSTOMERS table −

Now, we are renaming the column named ID to cust_id using following query −

ALTER TABLE CUSTOMERS
	RENAME COLUMN ID TO cust_id;

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

Let us retrive the CUSTOMERS table description to verify whether the column ID is renamed to stud_id or not −

DESCRIBE CUSTOMERS;

As we observe in the output table, the ID column is renamed to stud_id successfully.

Example

Now, we are renaming the other two columns in CUSTOMERS table named NAME and AGE to cust_name and cust_age −

ALTER TABLE CUSTOMERS
	RENAME COLUMN NAME TO cust_name,
	RENAME COLUMN AGE TO cust_age;

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

To verify whether column names have been renamed or not, execute the following query −

DESCRIBE CUSTOMERS;

As we observe in the output table, the above mentioned columns are successfully renamed.

Using CHANGE COLUMN

In MySQL, we can change the name of one or more columns along with their datatypes using the ALTER TABLE … CHANGE COLUMN command.

Syntax

Following is the syntax of the ALTER TABLE … CHANGE commnad in MySQL −

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name Data Type;

Example

Consider the previously updated CUSTOMERS table and, let us change the name and the datatype of cust_id column −

ALTER TABLE CUSTOMERS
	CHANGE COLUMN cust_id ID varchar(10);

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

Using the following query, we can verify whether the column cust_id has changed its name and datatype or not −

DESCRIBE CUSTOMERS;

The name of the column and datatype has been changed successfully.

Example

Here, we are changing the names and datatypes of multiple columns (cust_name and cust_age) in the CUSTOMERS table −

ALTER TABLE CUSTOMERS
	CHANGE COLUMN cust_name NAME DECIMAL(18,2),
	CHANGE COLUMN cust_age AGE VARCHAR(20);

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

Let us retrive the CUSTOMERS table description to verify whether the columns name and datatype are changed or not −

DESCRIBE STUDENTS;

As we observe in the output table, the names and datatypes of above mentioned columns are successfully changed.

Renaming a Column of a Table Using a Client Program

In addition to rename a column of a table in MySQL Database using MySQL query, we can also perform the ALTER TABLE operation on a table using a client program.

Syntax

Following are the syntaxes to rename a column of a MySQL table in various programming languages −

$sql = "ALTER TABLE table_name
RENAME COLUMN old_column1_name TO new_column1_name";
$mysqli->query($sql);

sql= "ALTER TABLE table_name
RENAME COLUMN old_column1_name TO new_column1_name";
con.query(sql);

String sql = "ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name";
statement.executeUpdate(sql);

sql = "ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_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.
'');

// rename column;
$sql = "ALTER TABLE tut_tbl
        RENAME COLUMN tutorial_id TO tutorial_IDs";
if ($mysqli->query($sql)) {
    printf("Column renamed successfully!.
");
}
if ($mysqli->errno) {
    printf("Columns could be renamed!.
", $mysqli->error);
}
$mysqli->close();

Column 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("--------------------------");

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

  sql = "CREATE TABLE STUDENTS (ID INT NOT NULL, NAME VARCHAR(40) NOT NULL, AGE INT);"
  con.query(sql);

  //Records before renaming
  sql = "DESCRIBE STUDENTS;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
    console.log("--------------------------");
  });

  //Renaming column ID to "stud_id"
  sql = "ALTER TABLE STUDENTS RENAME COLUMN ID TO stud_id;"
  con.query(sql);

  //Records after renaming
  sql = "DESCRIBE STUDENTS;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Connected!
--------------------------
[
  {
    Field: ''ID'',
    Type: ''int'',
    Null: ''NO'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''NAME'',
    Type: ''varchar(40)'',
    Null: ''NO'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''AGE'',
    Type: ''int'',
    Null: ''YES'',
    Key: '''',
    Default: null,
    Extra: ''''
  }
]
--------------------------
[
  {
    Field: ''stud_id'',
    Type: ''int'',
    Null: ''NO'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''NAME'',
    Type: ''varchar(40)'',
    Null: ''NO'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''AGE'',
    Type: ''int'',
    Null: ''YES'',
    Key: '''',
    Default: null,
    Extra: ''''
  }
]

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

public class RenameColumn {
    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 column name
            String sql = "ALTER TABLE tutorials_tbl RENAME COLUMN ID TO tutorial_Id";
            statement.executeUpdate(sql);
            System.out.println("Name renamed successfully...!");

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

Connected successfully...!
Name renamed successfully...!
tutorial_Id int
tutorial_title varchar(50)
tutorial_author varchar(30)

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''NOVELS''
old_column_name = ''PAGES''
new_column_name = ''PAGES_COUNT''
#Creating a cursor object
cursorObj = connection.cursor()
rename_column_query = f"ALTER TABLE {table_name} CHANGE {old_column_name} {new_column_name} INT"
cursorObj.execute(rename_column_query)
print(f"Column ''{old_column_name}'' is renamed to ''{new_column_name}'' in table ''{table_name}'' successfully.")
cursorObj.close()
connection.close()

Column ''tutorial_price'' is renamed to ''tutorial_amount'' in table ''tutorials_tbl_cloned'' successfully.

MySQL Show Columns Statement

To retrieve entire information of a table, we use DESCRIBE, DESC or SHOW COLUMNS statements.

All of these statements of MySQL can be used to retrieve/display the description of all the columns of a table, as they all retrieve the same result-sets.

Obtaining column information can be useful in several situations like inserting values into a table (based on the column datatype), updating or dropping a column, or to just simply know a table”s structure.

In this chapter, let us understand how to use SHOW COLUMNS statement in detail.

Syntax

Following is the syntax of the MySQL SHOW COLUMNS Statement −

SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}
   {FROM | IN} tbl_name
   [{FROM | IN} db_name]
   [LIKE ''pattern'' | WHERE expr]

Example

Let us start with creating a database named TUTORIALS using the below query −

CREATE DATABASE TUTORIALS;

Execute the following statement to change into TUTORIALS database −

USE TUTORIALS;

In the following query, we are creating a table named CUSTOMERS using the following 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, we are using the SHOW COLUMNS statement to retrieve the information about columns of the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

Output

Following is the information of columns in CUSTOMERS table −

Example

We can also use the IN clause instead of FROM as shown in the query below −

SHOW COLUMNS IN CUSTOMERS;

Output

As we can obeserve the output, it is exactly the same as the previous output.

Example

We can specify the name of the database along with the table name as shown in the query below −

SHOW COLUMNS IN CUSTOMERS FROM TUTORIALS;

Output

Following is the information of columns in CUSTOMERS table that is present in TUTORIALS database.

Example

We can replace the COLUMNS clause with FIELDS and get the same results −

SHOW FIELDS IN CUSTOMERS;

Output

As we see the output, we got the same results as COLUMNS clause.

The LIKE clause

In MySQL, using the LIKE clause, you can specify a pattern to retrieve info about specific columns.

Example

Following query retrieves the column names starting with the letter “P” from CUSTOMERS table.

SHOW COLUMNS FROM CUSTOMERS LIKE ''N%

Output

Executing the query above will produce the following output −

The WHERE clause

We can use the MySQL WHERE clause of the SHOW COLUMNS statements to retrieve information about the columns which match the specified condition.

Example

In the following example, we are using the WHERE clause to retrieve the columns where there type is int.

SHOW COLUMNS FROM CUSTOMERS WHERE Type= ''int

Output

Executing the query above will produce the following output −

The FULL clause

Usually, the information provided by the SHOW COLUMNS statements contains field type, can be null or not, key, default values and some extra details. If you use the full clause details like collation, privileges and comments will be added.

Example

In the following example, we are using the FULL clause with SHOW COLUMNS to retrieve extra details of the CUSTOMERS table −

SHOW FULL COLUMNS IN CUSTOMERS FROM tutorials;

Executing the query above will produce the following output −

Showing Columns of a table Using a Client Program

Besides showing the columns of a table in a MySQL database with a MySQL query, we can also use a client program to perform the SHOW COLUMNS operation.

Syntax

Following are the syntaxes to show columns of a MySQL table in various programming languages −

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

sql="SHOW COLUMNS FROM table_name";
con.query(sql);

String sql="SHOW COLUMNS FROM table_name FROM database";
statement.executeQuery(sql);

sql="SHOW COLUMNS FROM table_name FROM database";
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.
'');

//column can be shown by the following queries
// $sql = "SHOW COLUMNS FROM tut_tbl";
$sql = "SHOW COLUMNS FROM sales FROM tutorials";
if ($show_clmn = $mysqli->query($sql)) {
    printf("show column executed successfully!.
");
    while ($col = mysqli_fetch_array($show_clmn)) {
        echo "n{$col[''Field'']}";
    }
}
if ($mysqli->errno) {
    printf("Columns could be shown by the above query!.
", $mysqli->error);
}
$mysqli->close();

show column executed successfully!.
ID
ProductName
CustomerName
DispatchDate
DeliveryTime
Price
Location

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 demo"
  con.query(sql);

  sql = "USE demo"
  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);

  //Displaying all the columns from the Sales table
  sql = "SHOW COLUMNS FROM 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 ShowColumn {
    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 column
            String sql = "SHOW COLUMNS FROM tutorials_tbl FROM TUTORIALS";
            ResultSet resultSet = statement.executeQuery(sql);
            System.out.println("Column has been shown successfully...!");
            while (resultSet.next()) {
                System.out.print(resultSet.getString(1));
                System.out.println();
            }
            connection.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

Connected successfully...!
Column has been shown successfully...!
ID
tutorial_title
tutorial_author

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''NOVELS''
#Creating a cursor object
cursorObj = connection.cursor()
show_columns_query = f"SHOW COLUMNS FROM {table_name}"
cursorObj.execute(show_columns_query)
columns_info = cursorObj.fetchall()
print(f"Columns 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]}")
cursorObj.close()
connection.close()

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

A column in a table is a series of vertical cells that are used to store different types of data such as text, numbers, images, etc. Every column can contain one or more rows, where each row can store a single value.

Adding Columns to a MySQL table

In MySQL, we can add one or multiple columns in a table using the ALTER TABLE ADD statement. Adding columns to a table can be useful when we need to add new data.

Syntax

Following is the syntax to add a column in a MySQL table −

ALTER TABLE table_name
ADD [COLUMN] column_1_definition [FIRST|AFTER existing_column],
ADD [COLUMN] column_2_definition [FIRST|AFTER existing_column],
...;

Where,

• The FIRST keyword is used to add a specific column at the beginning of the table.
• The AFTER keyword is used to add a column after a particular existing column in the table.

Example

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

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

Execute the following query to retrieve the columns list in above created table −

DESCRIBE CUSTOMERS;

Following are the columns that are present in the CUSTOMERS table at the moment −

Now, we are adding a column named AGE to the CUSTOMERS table using the below query −

ALTER TABLE	CUSTOMERS
	ADD COLUMN AGE INT NOT NULL;

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

After adding the AGE column to the CUSTOMERS table, we can check to confirm if the AGE column has been added or not, using the following query −

DESCRIBE CUSTOMERS;

As we can see in the colums list of CUSTOMERS table, the column AGE is added successfully.

Example

In the following query, we are using the FIRST keyword to add the S_NO column at the beginning of the previosly created CUSTOMERS table −

ALTER TABLE CUSTOMERS
	ADD COLUMN S_NO INT NOT NULL FIRST;

Output

On executing the given query, the output is displayed as follows −

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

Verification

Now, let us verify whether the S_NO column is added first or not by executing the below query −

DESCRIBE CUSTOMERS;

As we can see in the output table, the S_NO column is added successfully at the beginning of the table.

Example

At the moment, the CUSTOMERS table has 4 columns in it. Now, we are using the AFTER keyword to add a new column GENDER after the column named ID −

ALTER TABLE CUSTOMERS
	ADD COLUMN GENDER VARCHAR(10) AFTER ID;

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

Using the following DESCRIBE statement, we can verify whether the column GENDER is added after the ID column or not −

DESCRIBE CUSTOMERS;

The GENDER column is successfully added after the ID column.

Adding Multiple Columns

We can add multiple columns into a specified table using the ALTER TABLE…ADD command. To do this, we just need to specify the new columns that we want to add, separating them with commas.

Example

In the below query, we are adding multiple columns (ADDRESS and CONTACT) to the CUSTOMERS table with a single ALTER statement −

ALTER TABLE CUSTOMERS
ADD COLUMN ADDRESS CHAR (25),
ADD COLUMN CONTACT INT;

Output

The output for the program above is produced as given below −

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

We can verify whether the columns MARKS and GRADES are added or not using the following query −

DESCRIBE CUSTOMERS;

The following output show that the MARKS and GRADES columns are added into CUSTOMERS table −

Deleting Columns from a MySQL table

In MySQL, we can delete single or multiple columns from a table using the ALTER TABLE DROP COLUMN statement. We generally delete the columns when there is specific data that is no longer needed.

Syntax

Following is the syntax of ATLER TABLE DROP COLUMN in MySQL −

ALTER TABLE table_name
DROP COLUMN column_name;

Example

At the moment, we have 7 columns in the CUSTOMERS table. Now, we are deleting the existing column S_NO from the CUSTOMERS table −

ALTER TABLE CUSTOMERS
	DROP COLUMN S_NO;

Output

When we execute the program above, the output is obtained as follows −

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

Verification

We can verify whether the column named S_NO is deleted or not using the following query −

DESCRIBE CUSTOMERS;

As we can see the newly updated columns list of CUSTOMERS table, the S_NO column has deleted.

Example

Here, we are trying to delete multiple columns (GENDER, ADDRESS, and CONTACT) using a single ALTER statement −

ALTER TABLE CUSTOMERS
DROP COLUMN AGE,
DROP COLUMN GENDER;

Output

On executing the given program, the output is displayed as follows −

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

Verification

Using the following query, we can verify whether the GENDER, ADDRESS and CONTACT columns are deleted or not −

DESCRIBE CUSTOMERS;

Following is the list of columns in CUSTOMERS after deleting the above mentioned columns −

Adding/Deleting column in a table Using a Client Program

Besides adding/deleting a column in a table in MySQL database with a MySQL query, we can also use a client program to perform the ALTER TABLE ADD/DROP operation.

Syntax

Following are the syntaxes to Add/Delete a column in MySQL Database in various programming languages −

//following is the syntax for add column in existing table.
$sql = "ALTER TABLE table_name ADD COLUMN column_name datatype NOT NULL AFTER existing_column_name";
//following is the syntax for delete column in existing table.
$sql = "ALTER TABLE table_name DROP COLUMN column_name";
$mysqli->query($sql);

//following is the syntax for add column in existing table.
sql = "ALTER TABLE table_name ADD COLUMN column_name datatype NOT NULL AFTER existing_column_name";
//following is the syntax for delete column in existing table.
sql = "ALTER TABLE table_name DROP COLUMN column_name";
con.query(sql);

//following is the syntax for add column in existing table.
String sql = "ALTER TABLE table_name ADD COLUMN column_name datatype NOT NULL AFTER existing_column_name";
//following is the syntax for delete column in existing table.
String sql = "ALTER TABLE table_name DROP COLUMN column_name";
statement.executeUpdate(sql);

//following is the syntax for add column in existing table.
sql = "ALTER TABLE table_name ADD COLUMN column_name datatype NOT NULL AFTER existing_column_name"
//following is the syntax for delete column in existing table.
sql = "ALTER TABLE table_name DROP COLUMN column_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.
'');

// Query to add column name in table...
$sql = "ALTER TABLE tutorials_tbl ADD COLUMN
tutorial_name VARCHAR(30) NOT NULL AFTER tutorial_id";

if ($mysqli->query($sql)) {
    printf(" Coulumn added seccessfully in existing table.
");
}

//Query to Delete column of a table...
$sql = "ALTER TABLE tutorials_tbl
DROP COLUMN tutorial_name";
if ($mysqli->query($sql)) {
    printf(" Coulumn Deleted seccessfully in existing table.
");
}
if ($mysqli->errno) {
    printf("we''r getting an error.
", $mysqli->error);
}
$mysqli->close();

Coulumn added seccessfully in existing table.
Coulumn Deleted seccessfully in existing table.

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 STUDENTS (ID INT NOT NULL, NAME VARCHAR(40) NOT NULL);"
  con.query(sql);

  //Adding column named "AGE"
  sql = "ALTER TABLE STUDENTS ADD COLUMN AGE INT NOT NULL;"
  con.query(sql);

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

  //Deleting column named "AGE"
  sql = "ALTER TABLE STUDENTS DROP COLUMN AGE;"
  con.query(sql);

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

Connected!
--------------------------
[
  {
    Field: ''ID'',
    Type: ''int'',
    Null: ''NO'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''NAME'',
    Type: ''varchar(40)'',
    Null: ''NO'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''AGE'',
    Type: ''int'',
    Null: ''NO'',
    Key: '''',
    Default: null,
    Extra: ''''
  }
]
--------------------------
[
  {
    Field: ''ID'',
    Type: ''int'',
    Null: ''NO'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''NAME'',
    Type: ''varchar(40)'',
    Null: ''NO'',
    Key: '''',
    Default: null,
    Extra: ''''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class AddDelColumn{
   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...!");
         //Adding One Column extra into the tutorials_tbl
         String sql = "ALTER TABLE tutorials_tbl ADD COLUMN tutorial_name VARCHAR(30) NOT NULL AFTER tutorial_id";
         statement.executeUpdate(sql);
         System.out.println("Column added into the tutorials table successfully...!");
         //Deleting One Column from the tutorials_tbl
         String sql1 = "ALTER TABLE tutorials_tbl DROP COLUMN tutorial_name";
         statement.executeUpdate(sql1);
         System.out.println("Column deleted successfully from the tutorials table ...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE tutorials_tbl");
         while (resultSet.next()) {
            System.out.print(resultSet.getNString(1));
            System.out.println();
         }
         connection.close();
      }catch(Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
Column added into the tutorials table successfully...!
Column deleted successfully from the tutorials table ...!
tutorial_id
tutorial_title
tutorial_author
submission_date

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
   host=''localhost'',
   user=''root'',
   password=''password'',
   database=''tut''
)
table_name = ''tutorials_tbl_cloned''
column_to_add = ''tutorial_price''
column_to_delete = ''tutorial_id''
# Adding a new column
cursorObj = connection.cursor()
add_column_query = f"ALTER TABLE {table_name} ADD COLUMN {column_to_add} INT"
cursorObj.execute(add_column_query)
print(f"Column ''{column_to_add}'' is added to table ''{table_name}'' successfully.")
# Deleting a column
delete_column_query = f"ALTER TABLE {table_name} DROP COLUMN {column_to_delete}"
cursorObj.execute(delete_column_query)
print(f"Column ''{column_to_delete}'' is deleted from table ''{table_name}'' successfully.")
cursorObj.close()
connection.close()

Column ''tutorial_price'' is added to table ''tutorials_tbl_cloned'' successfully.
Column ''tutorial_id'' is deleted from table ''tutorials_tbl_cloned'' 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: