MySQL ORDER BY Clause

The MySQL ORDER BY clause is used to sort one or more columns of a table in provided order that can be either ascending or descending order. By default, it sorts the column(s) in ascending order if the sort order is not specified.

The sort is specified with two keywords; ASC for ascending order and DESC for descending order.

Using the ORDER BY clause, we can sort multiple columns of a table and provide different sort orders for each column. For instance, we can sort the result set first by one column, and then by another column to the first column, and so on.

Syntax

Following is the syntax of ORDER BY clause in MySQL −

SELECT column-list
FROM table_name
[ORDER BY column1, column2, ..., columnN] [ASC|DESC]

Here,

• column-list are the names of the columns that we want to retrieve from the table_name.

• column1, column2,…columnN are the column(s) that we want to order (sort).

• ASC will sort the columns in ascending order.

• DESC will sort the columns in descending order.

By default, the ORDER BY clause sorts the provided column in Ascending order.

Example

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

Execute the following query to verify whether the CUSTOMERS table is created or not −

Select * from CUSTOMERS;

The CUSTOMERS table has been created successfully −

Now, let us display all the columns from the CUSTOMERS table, sorted by the NAME column −

By default, the ORDER BY clause sorts the provided column in Ascending order.

SELECT * FROM CUSTOMERS
ORDER BY NAME;

Output

As we can see in the output below, the NAME column is sorted in Ascending order.

ORDER BY with DESC

We can sort a particular column of a table in descending order by using the ORDER BY clause along with the DESC keyword. Let us understand with the following example.

Example

In the following query, we are displaying all the columns from the CUSTOMERS table, sorted by the NAME column in descending order −

SELECT * FROM CUSTOMERS
ORDER BY NAME DESC;

Output

As we can see in the output below, the NAME column is sorted in descending order.

ORDER BY with Multiple Columns

We can also sort multiple columns of a MySQL table. To do so, we need to specify all the column names in the ORDER BY clause.

Example

Here, we are selecting all the columns from the CUSTOMERS table, sorted by the ADDRESS and NAME columns.

SELECT * FROM CUSTOMERS
ORDER BY ADDRESS, NAME;

Output

The above query first sorts the ADDRESS column in ascending order, and for any rows that have the same ADDRESS value, they will be sorted by the NAME column in ascending order.

This means, all the rows with the same ADDRESS value will be grouped together and sorted by NAME.

ORDER BY with ASC and DESC

In MySQL, we can order the columns with ASC and DESC in the same query. The column provided first with ASC will be sorted in Ascending order and the column provided second with DESC will be sorted in descending order.

Example

In this query, we are selecting all the columns from the CUSTOMERS table, sorted ascending by the AGE and descending by the SALARY column −

SELECT * FROM CUSTOMERS
ORDER BY AGE ASC, SALARY DESC;

Output

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

ORDER BY with LENGTH()

We can use the LENGTH() function with the ORDER BY clause in MySQL to sort the values present in a particular column based on the length.

Example

Using the following query, we are sorting the ADDRESS column based on the length −

SELECT * FROM CUSTOMERS
ORDER BY LENGTH(ADDRESS) ASC;

Output

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

Order By Clause Using a Client Program

Besides using MySQL ORDER BY clause to sort one or more columns of a 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 COLUMN1, COLUMN2, .. FROM TABLE_NAME
ORDER BY COLUMN1, COLUMN2, ... ASC|DESC";
$mysqli->query($sql);

sql= SELECT column-list FROM table_name
[ORDER BY column1, column2, ..., columnN] [ASC|DESC]
con.query(sql);

String sql = "SELECT column-list FROM table_name
[ORDER BY column1, column2, ..., columnN] [ASC|DESC]";
statement.executeQuery(sql);

order_by_clause_query = SELECT column-list FROM table_name
[ORDER BY column1, column2, ..., columnN] [ASC|DESC]
cursorObj.execute(order_by_clause_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 ORDER BY tutorial_author ASC
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records based on ''tutorial_author'' in ascending order: n");
   while($row = $result->fetch_assoc()) {
      printf("ID %d, Title: %s, Author: %s ",
         $row["tutorial_id"],
         $row["tutorial_title"],
         $row["tutorial_author"]);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Table records based on ''tutorial_author'' in ascending order:
ID 5, Title: Learn MySQL, Author: Abdul S
ID 4, Title: Learn PHP, Author: John Poul
ID 2, Title: PHP Tut, Author: New Author
ID 1, Title: Java Tutorial, Author: new_author
ID 3, Title: JAVA Tutorial, Author: Sanjay

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

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating table
  sql = "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));"
  con.query(sql);

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

  //Using ORDER BY Clause
  sql = "SELECT * FROM CUSTOMERS ORDER BY NAME;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Connected!
--------------------------
[
  {
    ID: 4,
    NAME: ''Chaital'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''6500.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''8500.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Hyderabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 6,
    NAME: ''Komal'',
    AGE: 22,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''4500.00''
  },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Indore'',
    SALARY: ''10000.00''
  },
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Hyderabad'',
    SALARY: ''2000.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class OrderByClause {
  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 CUSTOMERS ORDER BY NAME DESC";
            rs = st.executeQuery(sql);
            System.out.println("Table records(in DESC order): ");
            while(rs.next()){
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String age = rs.getString("Age");
              String address = rs.getString("Address");
              String salary = rs.getString("Salary");
              System.out.println("Id: " + id +", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Table records(in DESC order):
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00
Id: 6, Name: Komal, Age: 22, Address: MP, Salary: 4500.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00

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()
order_by_clause_query = """
SELECT *
FROM customers
ORDER BY AGE ASC
"""
cursorObj.execute(order_by_clause_query)
# Fetch all the ordered rows
ordered_rows = cursorObj.fetchall()
# Printing the ordered rows
for row in ordered_rows:
    print(row)
cursorObj.close()
connection.close()

(3, ''kaushik'', 23, ''Hyderabad'', Decimal(''11000.00''))
(7, ''Muffy'', 24, ''Delhi'', Decimal(''10000.00''))
(2, ''Khilan'', 25, ''Kerala'', Decimal(''8000.00''))
(4, ''Chaital'', 25, ''Mumbai'', Decimal(''1200.00''))
(5, ''Hardik'', 27, ''Vishakapatnam'', Decimal(''10000.00''))
(6, ''Komal'', 29, ''Vishakapatnam'', Decimal(''7000.00''))
(1, ''Ramesh'', 32, ''Hyderabad'', Decimal(''4000.00''))

MySQL DISTINCT clause

The DISTINCT clause in MySQL is used with a SELECT statement to return the distinct values (unique values) from a single or multiple of columns in a table. It ignores all the duplicates values present in the particular column(s) and returns only the distinct values.

We can use this clause in various scenarios, such as identifying unique customer names, unique customer id”s, etc. It can be combined with other clauses such as WHERE, ORDER BY, and GROUP BY to filter the data further.

Syntax

Following is the syntax of the DISTINCT clause in MySQL −

SELECT DISTINCT column1, column2, ..., columnN
FROM table_name
WHERE conditions // optional

Where,

• (column1, column2,…,columnN) are the columns from which we want the distinct (unique) values.

• table_name is the name of the table from which we want to select data.

• WHERE conditions is optional. These are used to filter the data.

Example

Firstly, let us create a create a table named CUSTOMERS using the following INSERT 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 INSERT INTO statement adds 7 records into the above-created table −

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

Execute the below query to display all the inserted records in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

Now, let us retrieve the ADDRESS column from CUSTOMERS table without using the DISTINCT clause.

SELECT ADDRESS FROM CUSTOMERS;

Duplicate values are not ignored in the ADDRESS column.

Here, we are using the DISTINCT clause on the ADDRESS column −

SELECT DISTINCT ADDRESS FROM CUSTOMERS;

Output

As we can see in the output below, duplicate values are ignored in the ADDRESS column.

DISTINCT Clause with COUNT() Function

The MySQL count() function allows us to count the number of distinct values present in one or more columns of a table. Let us understand with the example below

Example

In the following query, we are using the MySQL COUNT() function to count the DISTINCT records in ADDRESS column of CUSTOMERS table −

SELECT COUNT(DISTINCT ADDRESS) FROM CUSTOMERS;

Output

There are 5 distinct records present in the ADDRESS column.

Example

In this query, we are retrieving unique SALARY records from the CUSTOMERS table where the ADDRESS is “Hyderabad”.

SELECT DISTINCT SALARY FROM CUSTOMERS WHERE ADDRESS = "HYDERABAD";

Output

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

DISTINCT on Multiple Columns

We can use the MySQL DISTINCT keyword on multiple columns of a table to return all the unique combinations of values across those columns, i.e. removing redundant records in a table.

Example

In the following query, we are retrieving the distinct combinations of ADDRESS and SALARY columns from the CUSTOMERS table and orders the result set by the ADDRESS column in ascending order.

SELECT DISTINCT ADDRESS, SALARY FROM CUSTOMERS ORDER BY ADDRESS;

Output

As we can see in the output below, the duplicate values “Hyderabad” and “Vishakapatnam” appears twice in the result set because each combination of Hyderabad and Vishakapatnam with SALARY is unique.

DISTINCT with NULL values

If there are NULL values present in a specific column, the MySQL DISTINCT will treat them as unique values and includes them in the result set.

Example

Here, we are returning the distinct salary of the customers using the following query −

SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;

Output

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

Distinct Clause Using a Client Program

In addition to fetch distinct records from a table with a MySQL query, you can also use a client program to perform the DISTINCT operation.

Syntax

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

$sql = "SELECT DISTINCT EXPRESSION FROM TABLE_NAME [WHERE CONDITION]";
$mysqli->query($sql);

sql = "SELECT DISTINCT column1, column2, ..., columnN FROM table_name";
con.query(sql);

String sql = "SELECT DISTINCT column1, column2, ..., columnN
FROM table_name WHERE conditions // optional";
statement.executeQuery(sql);

distinct_clause_query = "SELECT DISTINCT column1, column2, ...FROM table_name"
cursorObj.execute(distinct_clause_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 DISTINCT tutorial_title FROM tutorials_tbl where tutorial_id > 2
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) {
      printf("Title: %s ",
         $row["tutorial_title"], "n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Title: JAVA Tutorial Title: Learn PHP Title: Learn MySQL

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

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating table
  sql = "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));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1,''Ramesh'', 32, ''Hyderabad'', NULL),(2,''Khilan'', 25, ''Delhi'', 1500.00),(3,''kaushik'', 23, ''Hyderabad'', 2000.00),(4,''Chaital'', 25, ''Mumbai'', NULL),(5,''Hardik'', 27, ''Vishakapatnam'', 8500.00),(6, ''Komal'',22, ''Vishakapatnam'', 4500.00),(7, ''Muffy'',24, ''Indore'', 10000.00);"
  con.query(sql);

  //Using DISTINCT operator
  sql = "SELECT DISTINCT ADDRESS FROM CUSTOMERS;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Connected!
--------------------------
[
  { ADDRESS: ''Hyderabad'' },
  { ADDRESS: ''Delhi'' },
  { ADDRESS: ''Mumbai'' },
  { ADDRESS: ''Vishakapatnam'' },
  { ADDRESS: ''Indore'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class DistinctClause {
  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 DISTINCT ADDRESS FROM CUSTOMERS";
            rs = st.executeQuery(sql);
            System.out.println("Distinct records: ");
            while(rs.next()){
              String Address = rs.getString("Address");
              System.out.println("Address: " + Address);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Distinct records:
Address: Ahmedabad
Address: Delhi
Address: Kota
Address: Mumbai
Address: Bhopal
Address: MP
Address: Indore

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
distinct_clause_query = """
SELECT DISTINCT ADDRESS
FROM customers
"""
cursorObj.execute(distinct_clause_query)
# Fetch all the distinct addresses
distinct_addresses = cursorObj.fetchall()
# Printing the distinct addresses
for address in distinct_addresses:
    print(f"Address: {address[0]}")
cursorObj.close()
connection.close()

Address: Hyderabad
Address: Kerala
Address: Mumbai
Address: Vishakapatnam
Address: Delhi

MySQL Having Clause

The MySQL HAVING Clause is used to filter grouped rows in a table based on conditions.

This clause is used with the GROUP BY clause to group the rows based on one or more columns and then filter them based on the conditions specified in the HAVING clause. So, the HAVING clause must always be followed by the GROUP BY clause.

The HAVING clause was added to MySQL because the WHERE keyword cannot be used with aggregate functions such as COUNT(), SUM(), AVG(), etc.

This clause is similar to the MySQL WHERE clause. The difference between both of them is that the WHERE clause filters individual rows in a table, whereas the HAVING clause filters grouped rows based on conditions.

Syntax

Following is the basic syntax of the HAVING clause in MySQL −

SELECT column1, column2, aggregate_function(column)
FROM table_name
GROUP BY column1, column2, ...
HAVING condition
ORDER BY column1, column2, ...;

Example

Let us begin with creating 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 INSERT statement 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 );

Using the following query, we can verify whether the CUSTOMERS table is created or not −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

HAVING clause with ORDER BY clause

In MySQL, the HAVING clause filters the groups, and the ORDER BY clause sorts the results. When we used both of them together, HAVING is executed first, then the result set is sorted according to the ORDER BY criteria.

Example

In the following query, we are retrieving all the records from the CUSTOMERS table where the sum of their SALARY is less than 4540, ordered by their name in ascending order −

SELECT NAME, SUM(SALARY) as total_salary
FROM CUSTOMERS
GROUP BY NAME
HAVING SUM(SALARY) < 4540
ORDER BY NAME;

Output

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

HAVING clause with COUNT() function

We can use the MySQL HAVING clause in conjunction with the COUNT() function to filter the groups based on the number of rows they contain.

Example

In this query, we are fetching a record where the count of similar age is greater than or equal to 2.

SELECT AGE
FROM CUSTOMERS
GROUP BY age
HAVING COUNT(age) >= 2;

Output

There are two records in CUSTOMERS table with age 25, thus the output is 25 −

HAVING clause with AVG() function

The MySQL HAVING clause can also be used with the AVG() function to filter groups based on the average value of a specified column.

Example

In the following query, we are trying to return the names of the customers whose salary is greater than 3000 −

SELECT NAME, AVG(salary) as avg_salary
FROM customers
GROUP BY NAME
HAVING AVG(salary) > 3000;

Output

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

HAVING clause with MAX() function

In MySQL, we can also use the HAVING clause with MAX() function to filter groups based on the maximum value of a specified column.

Example

In this query, we are retrieving the customer names whose maximum SALARY is less than 4000 −

SELECT NAME, MAX(salary) as max_salary
FROM customers
GROUP BY NAME
HAVING MAX(salary) < 4000;

Output

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

Having Clause Using a Client Program

Besides using MySQL HAVING clause to filter grouped rows in a table based on conditions, 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 EXPRESSION1, EXPRESSION2, ...EXPRESSION_N,
AGGREGATE_FUNCTION(EXPRESSION) FROM TABLE_NAME
[WHERE CONDITION] GROUP BY EXPRESSION1,
EXPRESSION2.. EXPRESSION_N HAVING CONDITION";
$mysqli->query($sql);

sql= " SELECT column1, column2, aggregate_function(column)
FROM table_name GROUP BY column1, column2, ...
HAVING condition ORDER BY column1, column2, ...";
con.query(sql);

String sql = "SELECT column1, column2, aggregate_function(column)
FROM table_name GROUP BY column1, column2, ...
HAVING condition ORDER BY column1, column2, ...";
statement.executeQuery(sql);

having_clause_query = "SELECT column1, column2, aggregate_function(column)
FROM table_name GROUP BY column1, column2 HAVING condition"
cursorObj.execute(having_clause_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 tutorial_title, count(tutorial_id) AS tot_count FROM tutorials_tbl WHERE tutorial_id > 1 GROUP BY tutorial_title HAVING count(tutorial_id) > 1
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("Title: %s, Count: %d",
         $row["tutorial_title"],
         $row["tot_count"]);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Table records:
Title: Learn MySQL, Count: 2

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

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating table
  sql = "CREATE TABLE CUSTOMERS(ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,GENDER VARCHAR (25),SALARY DECIMAL (18, 2),PRIMARY KEY (ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CUSTOMERS VALUES(1, ''Ramesh'', 25, ''Male'', 2000.00),(2, ''Ramesh'', 25, ''Male'', 1500.00),(3, ''kaushik'', 25, ''Female'', 2000.00),(4, ''kaushik'', 20, ''Male'', 6500.00),(5, ''Hardik'', 25, ''Male'', 8500.00),(6, ''Komal'', 20, ''Female'', 4500.00),(7, ''Muffy'', 25, ''Male'', 10000.00);"
  con.query(sql);

  //Using HAVING Clause
  sql = "SELECT NAME, SUM(SALARY) as total_salary FROM CUSTOMERS GROUP BY NAME HAVING SUM(SALARY)
   
Output
   
The output produced is as follows −
Connected!
--------------------------
[
  { NAME: ''Komal'', total_salary: ''4500.00'' },
  { NAME: ''Ramesh'', total_salary: ''3500.00'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class HavingClause {
  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 NAME, SUM(SALARY) as total_salary FROM CUSTOMERS GROUP BY NAME HAVING SUM(SALARY)
   
Output
   
The output obtained is as shown below −
Table records:
Name: Komal, Total_Salary: 4500.00
Name: Ramesh, Total_Salary: 3500.00

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()
having_clause_query = """SELECT ADDRESS, SUM(SALARY) as total_salary FROM CUSTOMERS
GROUP BY ADDRESS HAVING SUM(SALARY)
Output
Following is the output of the above code −
(''Mumbai'', Decimal(''1200.00''))

MySQL GROUP BY Clause

The GROUP BY clause in MySQL is used to arrange identical data in a table into groups.

For example, let us suppose you have a table of sales data of an organization consisting of date, product, and sales amount. To calculate the total sales in a particular year, the GROUP BY clause can be used to group the sales of products made in that year. Similarly, you can group the data by date to calculate the total sales for each day, or by a combination of product and date to calculate the total sales for each product on each day.

This GROUP BY clause follows the WHERE clause in an SQL statement and precedes the ORDER BY or HAVING clause (if they exist). You can use GROUP BY to group values from a column, and, if you wish, perform calculations on that column. You can use COUNT, SUM, AVG, etc., functions on the grouped column.

Syntax

Following is the basic syntax to use GROUP BY with SELECT statement −

SELECT column_name(s) FROM table_name
GROUP BY [condition | column_name(s)];

Example

This example demonstrates how to use aggregate functions with GROUP BY clause.

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

Now, insert the following records into the CUSTOMERS table using the following INSERT statement −

INSERT INTO CUSTOMERS 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);

The table is created as follows −

Now, use the following GROUP BY query to group the customers based on their age −

SELECT AGE, COUNT(Name) FROM CUSTOMERS GROUP BY AGE;

Output

Following is the result produced −

MySQL GROUP BY on Single Column

When we use the GROUP BY clause on a single column, all common values in that column will be added together making it a single record.

Example

In this example, let us group the customers by their age and calculate the average salary for each age using the following query −

SELECT AGE, AVG(SALARY) AS AVG_SALARY
FROM CUSTOMERS
GROUP BY AGE;

Output

This would produce the following result −

SELECT CONCAT(AGE, '' - '', SALARY) AS SALARY_AGEWISE
FROM CUSTOMERS
GROUP BY AGE, SALARY;

SELECT column1, column2, ..., aggregate_function(columnX) AS alias
FROM table
GROUP BY column1, column2, ...
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

SELECT AGE, MAX(salary) AS MAX_SALARY
FROM CUSTOMERS
GROUP BY AGE
ORDER BY MAX(salary) DESC;

SELECT column1, column2, aggregate_function(column)
FROM table_name
GROUP BY column1, column2
HAVING condition;

SELECT AGE, AVG(SALARY) AS AVG_SALARY
FROM CUSTOMERS
GROUP BY AGE HAVING AVG(salary) > 8000;

$sql = "SELECT EXPRESSION1, EXPRESSION2, ... EXPRESSION_N,
   AGGREGATE_FUNCTION(EXPRESSION) FROM TABLE_NAME
   [WHERE CONDITION] GROUP BY EXPRESSION1, EXPRESSION2..";
$mysqli->query($sql,$resultmode)

sql= " SELECT column_name(s) FROM table_name
   GROUP BY [condition | column_name(s)];"
Con.query(sql);

String sql = "SELECT column_name(s) FROM table_name
   GROUP BY [condition | column_name(s)]";
statement.executeQuery(sql);

group_by_clause_query = "SELECT column_name(s) aggregate_function(column)
   FROM table_name GROUP BY column_name(s)"
cursorObj.execute(group_by_clause_query)

$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 tutorial_title, count(tutorial_id) AS tot_count FROM tutorials_tbl WHERE tutorial_id > 2 GROUP BY tutorial_title
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("Title: %s, Count: %d",
         $row["tutorial_title"],
         $row["tot_count"]);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Table records:
Title: JAVA Tutorial, Count: 1
Title: Learn PHP, Count: 1
Title: Learn MySQL, Count: 2

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

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating table
  sql = "CREATE TABLE IF NOT EXISTS employee_tbl(id INT NOT NULL, name VARCHAR(100) NOT NULL, work_date DATE, daily_typing_pages INT);"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO employee_tbl(id, name, work_date, daily_typing_pages) VALUES(1, ''John'', ''2007-01-24'', 250), (2, ''Ram'', ''2007-05-27'', 220), (3, ''Jack'', ''2007-05-06'', 170), (3, ''Jack'', ''2007-04-06'', 100), (4, ''Jill'', ''2007-04-06'', 220),(5, ''Zara'', ''2007-06-06'', 300),(5, ''Zara'', ''2007-02-06'', 350);"
  con.query(sql);

  //Using GROUP BY Clause
  sql = "SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Connected!
--------------------------
[
  { name: ''John'', ''COUNT(*)'': 1 },
  { name: ''Ram'', ''COUNT(*)'': 1 },
  { name: ''Jack'', ''COUNT(*)'': 2 },
  { name: ''Jill'', ''COUNT(*)'': 1 },
  { name: ''Zara'', ''COUNT(*)'': 2 }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class GroupByClause {
	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 AGE, AVG(SALARY) as avg_salary FROM CUSTOMERS GROUP BY age";
            rs = st.executeQuery(sql);
            System.out.println("Table records(gruop by age): ");
            while(rs.next()){
            	String age = rs.getString("Age");
            	String avg_salary = rs.getString("avg_salary");
            	System.out.println("Age: " + age + ", Salary: " + avg_salary);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

Table records(gruop by age):
Age: 32, Salary: 2000.000000
Age: 25, Salary: 4000.000000
Age: 23, Salary: 2000.000000
Age: 27, Salary: 8500.000000
Age: 22, Salary: 4500.000000
Age: 24, Salary: 10000.000000

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()
group_by_clause_query = """
SELECT ADDRESS, AVG(SALARY) AS average_salary
FROM customers
GROUP BY ADDRESS
"""
cursorObj.execute(group_by_clause_query)
# Fetching all the grouped rows
grouped_rows = cursorObj.fetchall()
for row in grouped_rows:
    address, average_salary = row
    print(f"Address: {address}, Average Salary: {average_salary}")
cursorObj.close()
connection.close()

Address: Hyderabad, Average Salary: 7500.000000
Address: Kerala, Average Salary: 8000.000000
Address: Mumbai, Average Salary: 1200.000000
Address: Vishakapatnam, Average Salary: 8500.000000
Address: Delhi, Average Salary: 10000.000000

An index is a data structure that improves the speed of operations on a database table. They are a special type of lookup tables pointing to the data. Indexes can be created on one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

Indexes, however, reduce the efficiency of INSERT and UPDATE operations on a table. This is because when we insert or update a data value in a table, indexes also need to be modified accordingly. So, they are not always appropriate to use.

Users cannot see the indexes, they are just used to speed up queries and will be used by the Database Search Engine to locate records very fast.

Types of MySQL Indexes

Indexes can be defined on single or multiple columns of a MySQL table. The decision to add indexes on a table column(s) depends on the type of data that needs to be searched. MySQL provides the following types of indexes −

• Simple Index

• Unique Index

• Primary Key Index

• Fulltext Index

• Descending Index

Simple Index

A simple index is a basic type of index where the values inserted into the column, containing this index, are searched easily. In such case, the column can contain duplicate values or NULL.

Unique Index

A Unique index does not allow any duplicate values to be inserted into a table column (where the index is defined on).It can be added to single or multiple columns of a table. If it is added to a single column, the values of that column must be unique. But if it is added to multiple columns, the combination of values in these columns must be unique.

Primary Key Index

Primary Key Index is an extension of unique index, as the primary key column must always contain unique values and these values must not be NULL. Primary key can be set to a single column of a database table, or multiple columns as well (which is not recommended).

Fulltext Index

In a database, sometimes you would have to search for a blob of text instead of a record. You can use fulltext index for it. As its name suggests, it is used to make the text searches in a table easier.

Descending Index

The descending index is only available in MySQL versions after 8.0. It is simple index used to store data in a reverse order. Using this index, it is easy to search for the latest values inserted into the database table.

Renaming Views in MySQL

The MySQL RENAME TABLE statement in MySQL is generally used to rename the name of a table. But this statement can also be used to rename views because views are typically virtual tables created by a query.

Before renaming a view, we need to ensure that no active transactions are being performed on the view using its old name. It is, however, recommended to delete the existing view and re-create it with a new name instead of renaming it.

Syntax

Following is the basic syntax of the RENAME TABLE query to rename a view in MySQL −

RENAME TABLE original_view_name
TO new_view_name;

Example

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

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

Here, we are inserting some records into the above-created table using the query below −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', ''32'', ''Ahmedabad'', 2000),
(2, ''Khilan'', ''25'', ''Delhi'', 1500),
(3, ''Kaushik'', ''23'', ''Kota'', 2500),
(4, ''Chaitali'', ''26'', ''Mumbai'', 6500),
(5, ''Hardik'',''27'', ''Bhopal'', 8500),
(6, ''Komal'', ''22'', ''MP'', 9000),
(7, ''Muffy'', ''24'', ''Indore'', 5500);

Creating a view −

Now, let us create a view based on the above created table using the following query −

CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS;

The view will be created as follows −

Renaming the view −

Now, we know that we are having an existing view in our database named “CUSTOMERS_VIEW”. So, we are going to rename this view to VIEW_CUSTOMERS using the below query −

RENAME TABLE CUSTOMERS_VIEW TO VIEW_CUSTOMERS;

Verification

Using the following SELECT statement, we can verify whether the view is renamed or not −

SELECT * FROM VIEW_CUSTOMERS;

The “VIEW_CUSTOMERS” view displayed is as follows −

Rules to be followed while Renaming Views

There are some rules and practices to ensure that the renaming process goes smoothly and one should follow them while renaming a view in MySQL. They are listed below:

• Avoid renaming system views: In MySQL, the system views are views that contain all the information about the database management system. It is recommended not to rename these views because it can cause issues with the functioning of the database.

• Update all references to the view: After renaming a view in MySQL, any stored procedures, triggers, or other database objects that reference the view will need to be updated to use the new name of the view. If we failed to update these references results in errors or issues with the functioning of the database system.

• Test thoroughly: It is important to test the renaming process thoroughly in the development or testing environment to make sure that all references to the view have been updated correctly.

• Use a consistent naming convention: While working with views in MySQL, it”s recommended to use a consistent naming convention. If you need to rename a view, follow the same naming convention you”ve used for other views in the database.

• Backup the database: Before renaming a view, it is recommended to have a backup of the database to make sure that you have a restore point.

Renaming a View Using a Client Program

Until now, we used an SQL statement to rename a view directly in the MySQL database. But, we can also perform the same rename operation on a view using another client program.

Syntax

$sql = "RENAME TABLE first_view To tutorial_view";
$mysqli->query($sql);

sql = "RENAME TABLE CUSTOMERS_VIEW TO VIEW_CUSTOMERS";
con.query(sql);

String sql = "RENAME TABLE first_view TO tutorial_view";
st.executeQuery(sql);

rename_view_query = "RENAME TABLE tutorial_view TO new_tutorial_view"
cursorObj.execute(rename_view_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.
'');
// A view can be renamed by using the RENAME TABLE old_view_name TO new_view_name
$sql = "RENAME TABLE first_view To tutorial_view";
if ($mysqli->query($sql)) {
    printf("View renamed successfully!.
");
}
if ($mysqli->errno) {
    printf("View could not be renamed!.
", $mysqli->error);
}
$mysqli->close();

View 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 = "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 CHAR (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);

  //Creating View
  sql = "Create view CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS";
  con.query(sql);

  //Renaming View
  sql = "RENAME TABLE CUSTOMERS_VIEW TO VIEW_CUSTOMERS;"
  con.query(sql);

  //Displaying records from view
  sql = "SELECT * FROM VIEW_CUSTOMERS;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**Views after deleting:**");
    console.log(result);
  });

});

Connected!
--------------------------
[
  {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 RenameView {
   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 Created View.....
         String sql = "RENAME TABLE first_view TO tutorial_view";
         statement.executeUpdate(sql);
         System.out.println("Renamed view Successfully...!");
         ResultSet resultSet = statement.executeQuery("SELECT * FROM tutorial_view");
         while (resultSet.next()) {
            System.out.print(resultSet.getInt(1)+" "+ resultSet.getString(2)+ " "+ resultSet.getString(3));
            System.out.println();
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
Renamed view Successfully...!
1 Learn PHP John Paul

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
rename_view_query = "RENAME TABLE tutorial_view TO new_tutorial_view"
cursorObj.execute(rename_view_query)
connection.commit()
print("View renamed successfully.")
cursorObj.close()
connection.close()

View renamed successfully.

A MySQL View is a virtual table which is generated from a predefined SQL query. It contains (all or selective) records from one or more database tables.

Views are not stored in a database physically, but they can still be dropped whenever not necessary. Even though they are used to see and modify the data in a database table, the data in that table remains unchanged when views are dropped.

The MySQL DROP VIEW Statement

The DROP VIEW statement in MySQL is used to delete an existing view, along with its definition and other information. Once the view is dropped, all the permissions for it will also be removed. We can also use this statement to drop indexed views.

Suppose a table is dropped using the DROP TABLE command and it has a view associated to it, this view must also be dropped explicitly using the DROP VIEW command.

NOTE −

• While trying to perform queries, the database engine checks all the objects referenced in that statement are valid and exist. So, if a view does not exist in the database, the DROP VIEW statement will throw an error.

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

Syntax

Following is the syntax of the DROP VIEW Statement −

DROP VIEW view_name;

Where, view_name is the name of the view to be deleted.

Example

Suppose we have created a table named CUSTOMERS using the following CREATE TABLE query −

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

Let us insert records in the above created table using the following INSERT query −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', ''32'', ''Ahmedabad'', 2000),
(2, ''Khilan'', ''25'', ''Delhi'', 1500),
(3, ''Kaushik'', ''23'', ''Kota'', 2500),
(4, ''Chaitali'', ''26'', ''Mumbai'', 6500),
(5, ''Hardik'',''27'', ''Bhopal'', 8500),
(6, ''Komal'', ''22'', ''MP'', 9000),
(7, ''Muffy'', ''24'', ''Indore'', 5500);

Creating a View −

Now, let us create a view on this table using the CREATE VIEW statement as shown below −

CREATE VIEW testView AS SELECT * FROM CUSTOMERS;

You can verify the list of all the views using the following query −

SHOW FULL TABLES WHERE table_type = ''VIEW

The view will be created as follows −

Dropping a View −

Following query drops the view created above −

DROP VIEW testView;

Verification

To verify if we have deleted the view or not, display the list of views using the query below −

SHOW FULL TABLES WHERE table_type = ''VIEW

As the view is dropped, an empty set is returned.

Empty set (0.12 sec)

The IF EXISTS clause

If you try to drop a view that doesn”t exist, an error will be generated. Let us see an example where we are dropping a view named NEW using the following query −

DROP VIEW NEW;

The following error is displayed (where ”tutorialspoint” is the database name) −

ERROR 1051 (42S02): Unknown table ''tutorialspoint.new''

However, if you use the IF EXISTS clause along with the DROP VIEW statement as shown below, the query will be ignored even if a VIEW with the given name does not exist.

DROP VIEW IF EXISTS NEW;

Deleting Rows from a View

Instead of removing an entire view, we can also drop selected rows of a view using the DELETE statement with a WHERE clause.

Syntax

Following is the syntax of the DELETE statement −

DELETE FROM view_name WHERE condition;

Example

In this example, let us first create a testView on the CUSTOMERS table using the following query −

CREATE VIEW testView AS SELECT * FROM CUSTOMERS;

Now, using the following query, you can delete a record from the testView created on the CUSTOMERS table. The changes made to the data in view will finally be reflected in the base table CUSTOMERS.

DELETE FROM testView WHERE Location = ''Indore

The associated table CUSTOMERS will have the following records −

Dropping View Using Client Program

In addition to drop a view from the MySQL database using the MySQL query, we can also perform the another operation on a table using a client program.

Syntax

Following are the syntaxes of the Drop View from MySQL in various programming languages −

$sql="DROP VIEW view_name";
$mysqli->query($sql);

sql="DROP VIEW view_name";
con.query(sql);

String sql = "DROP VIEW view_name";
statement.execute(sql);

drop_view_query = "DROP VIEW view_name"
cursorObj.execute(drop_view_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.
'');

// drop a view;
$sql = "DROP VIEW first_view";
if ($mysqli->query($sql)) {
    printf("View dropped successfully!.
");
}
if ($mysqli->errno) {
    printf("View could not be dropped!.
", $mysqli->error);
}
$mysqli->close();

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

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

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

  sql = "CREATE TABLE dispatches_data(ProductName VARCHAR(255),CustomerName VARCHAR(255),DispatchTimeStamp timestamp,Price INT,Location VARCHAR(255));"
  con.query(sql);

  sql = "insert into dispatches_data values(''Key-Board'', ''Raja'', TIMESTAMP(''2019-05-04'', ''15:02:45''), 7000, ''Hyderabad''),(''Earphones'', ''Roja'', TIMESTAMP(''2019-06-26'', ''14:13:12''), 2000, ''Vishakhapatnam''),(''Mouse'', ''Puja'', TIMESTAMP(''2019-12-07'', ''07:50:37''), 3000, ''Vijayawada''),(''Mobile'', ''Vanaja'' , TIMESTAMP (''2018-03-21'', ''16:00:45''), 9000, ''Chennai''),(''Headset'', ''Jalaja'' , TIMESTAMP(''2018-12-30'', ''10:49:27''), 6000, ''Goa'');"
  con.query(sql);

  //Creating Views
  sql = "CREATE VIEW testView AS SELECT * FROM dispatches_data;"
  con.query(sql);

  sql = "CREATE VIEW sample AS SELECT ProductName, Price FROM dispatches_data;"
  con.query(sql);

  sql = "CREATE VIEW demo AS SELECT * FROM dispatches_data WHERE Price>3000;"
  con.query(sql);

  //Displaying list of all views
  sql = "SHOW FULL TABLES WHERE table_type = ''VIEW"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**Views before deleting:**");
    console.log(result);
    console.log("--------------------------");
  });

  //Dropping views
  sql = "DROP VIEW demo;"
  con.query(sql);

  sql = "DROP VIEW sample;"
  con.query(sql);

  sql = "DROP VIEW testview;"
  con.query(sql);

  //retrieve the list of views
  sql = "SHOW FULL TABLES WHERE table_type = ''VIEW"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**Views after deleting:**");
    console.log(result);
  });
});

Connected!
--------------------------
**Views before deleting:**
[
  { Tables_in_tutorials: ''demo'', Table_type: ''VIEW'' },
  { Tables_in_tutorials: ''sample'', Table_type: ''VIEW'' },
  { Tables_in_tutorials: ''testview'', Table_type: ''VIEW'' }
]
--------------------------
**Views after deleting:**
[]

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

public class DropView {
   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 View.....
         String sql = "DROP VIEW tutorial_view";
         statement.executeUpdate(sql);
         System.out.println("View dropped Successfully...!");
         ResultSet resultSet = statement.executeQuery("SELECT * FROM tutorial_view");
         while (resultSet.next()) {
            System.out.print(resultSet.getString(1));
            System.out.println();
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
View dropped Successfully...!
java.sql.SQLSyntaxErrorException: Table ''tutorials.tutorial_view'' doesn''t exist

Python Program
import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
drop_view_query = "DROP VIEW tutorial_view"
cursorObj.execute(drop_view_query)
connection.commit()
print("View dropped successfully.")
cursorObj.close()
connection.close()

View dropped successfully.

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 |

A database index improves the speed of operations in a database table. They can be created on one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

Practically, indexes are a special type of lookup tables, that hold a pointer to each record into the actual table.

We can create indexes on a MySQL table in two scenarios: while creating a new table and on an existing table.

Creating Indexes on New Table

If we want to define an index on a new table, we use the CREATE TABLE statement.

Syntax

Following is the syntax to create an index on a new table −

CREATE TABLE(
 column1 datatype PRIMARY KEY,
 column2 datatype,
 column3 datatype,
 ...
 INDEX(column_name)
);

Example

In this example, we are create a new table CUSTOMERS and adding an index to one of its columns 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),
   INDEX(ID)
);

To verify whether the index has been defined or not, we check the table definition using the following DESC statement.

DESC CUSTOMERS;

Output

The table structure displayed will contain a MUL index on the ID column as shown −

Creating Indexes on Existing Table

To create an index on existing table, we use the following SQL statements −

• With CREATE INDEX Statement
• With ALTER Command

CREATE INDEX Statement

The basic syntax of the CREATE INDEX statement is as follows −

CREATE INDEX index_name ON table_name;

In the following example, let us create an index on CUSTOMERS table. We are using CREATE INDEX statement here −

CREATE INDEX NAME_INDEX ON CUSTOMERS (Name);

To check if the index is created on the table or not, let us display the table structure using DESC statement as shown below −

DESC CUSTOMERS;

Output

As we can see in the table below, a composite index is created on the ”NAME” column of CUSTOMERS table.

ALTER… ADD Command

Following is the basic syntax of ALTER statement −

ALTER TABLE tbl_name ADD INDEX index_name (column_list);

Let us use ALTER TABLE… ADD INDEX statement in the following example to add an index to the CUSTOMERS table −

ALTER TABLE CUSTOMERS ADD INDEX AGE_INDEX (AGE);

Output

As we can see in the table below, another composite index is created on the ”AGE” column of CUSTOMERS table.

Simple and Unique Index

A unique index is the one which cannot be created on two rows at once. Following is the syntax to create a unique index −

CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);

Example

Following example creates a unique index on the table temp −

CREATE UNIQUE INDEX UNIQUE_INDEX ON CUSTOMERS (Name);

Composite Indexes

We can also create an index on more than one column and it is called a composite index the basic syntax to create a composite index is as follows −

CREATE INDEX index_name
on table_name (column1, column2);

Example

Following query creates a composite index on the ID and Name columns of the above created table −

CREATE INDEX composite_index on CUSTOMERS (ID, Name);

Creating an Index Using Client Program

In addition to using SQL queries, we can also create an index on a table in a MySQL database using a client program.

Syntax

Following are the syntaxes to create an index in a MySQL database using various programming languages −

$sql=" CREATE INDEX index_name
   ON table_name (column_name)";
$mysqli->query($sql);

sql = "CREATE INDEX index_name
   ON table_name (column1, column2, ...)";
con.query(sql);

String sql = " CREATE INDEX index_name
   ON table_name (column_name)";
statement.executeUpdate(sql);

create_index_query = CREATE INDEX index_name
   ON table_name (column_name [ASC|DESC], ...);
cursorObj.execute(create_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 = "CREATE INDEX tid
        ON tutorials_table (tutorial_id)";
if ($mysqli->query($sql)) {
    printf("Index created successfully!.
");
}
if ($mysqli->errno) {
    printf("Index could not be created!.
", $mysqli->error);
}
$mysqli->close();

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

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

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

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

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

  //Creating an Index
  sql = "CREATE INDEX sample_index ON temp (name);"
  con.query(sql);

  //Describing the table
  sql = "DESC temp;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Connected!
--------------------------
[
  {Field: ''Name'',Type: ''varchar(255)'',Null: ''YES'',Key: ''MUL'',Default: null,Extra: ''''},
  {Field: ''age'',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 CreateIndex {
   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 an index on the tutorials_tbl...!;
         String sql = "CREATE INDEX tid ON tutorials_tbl (tutorial_id)";
         statement.executeUpdate(sql);
         System.out.println("Index created Successfully...!");
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
Index created Successfully...!

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

Index created successfully.

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;