mysql

Khóa học miễn phí MySQL – Delete Query nhận dự án làm có lương

Posted on by user

MySQL – Delete Query

Table of content

MySQL DELETE Statement

If we want to delete a record from any MySQL table, then we can use the SQL command DELETE FROM. This statement is a part of Data Manipulation Language in SQL as it interacts with the data in a MySQL table rather than the structure.

DELETE statement can be used to delete multiple rows of a single table and records across multiple tables. However, in order to filter the records to be deleted, we can use the WHERE clause along with the DELETE statement.

We can use this command at the mysql> prompt as well as in any script like PHP, Node.js, Java, and Python.

Syntax

Following is the basic SQL syntax of the DELETE command to delete data from a MySQL table −

DELETE FROM table_name [WHERE Clause]

  • If the WHERE clause is not specified, then all the records will be deleted from the given MySQL table.

  • We can specify any condition using the WHERE clause.

  • We can delete records in a single table at a time.

The WHERE clause is very useful when you want to delete selected rows in a table.

Deleting Data from a MySQL Table

We use the MySQL DELETE query to delete records from a database table. However, we can delete single, multiple rows at once or even delete all records from a table in a single query. Let us discuss them one by one futher in this tutorial with appropriate examples.

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

Execute the below SELECT statement to retrieve all the records present in the CUSTOMERS table −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

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

Now, let us delete a single record (ID = 1) from the CUSTOMERS table using the DELETE statement as follows −

DELETE FROM CUSTOMERS WHERE ID = 1;

Output

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

Query OK, 1 row affected (0.00 sec)

Verification

Execute the following query to verify whether the above record have been deleted or not −

Select * From CUSTOMERS;

As we can see in the output, the row with ID=1 has been deleted −

ID NAME AGE ADDRESS SALARY
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

Deleting Multiple Rows

We can also delete multiple rows using the DELETE statement. For this, we just have to specify multiple conditions in the WHERE clause that are satisfied by all the records that are supposed to be deleted.

Example

Here, we are deleting records from previously created CUSTOMERS table whose ID is 2 and 3 −

DELETE FROM CUSTOMERS WHERE ID = 2 OR ID = 3;

Output

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

Query OK, 2 rows affected (0.01 sec)

Verification

Execute the following query to verify whether the above records have been deleted or not −

Select * From CUSTOMERS;

As we can see in the output, the row with ID values 2 and 3 are deleted −

ID NAME AGE ADDRESS SALARY
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Deleting All Rows

If we want to delete all records from a MySQL table, simply execute the DELETE statement without using the WHERE clause. Following is the syntax −

DELETE FROM table_name;

Example

The following query will delete all records from the CUSTOMERS table −

DELETE FROM CUSTOMERS;

Output

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

Query OK, 4 rows affected (0.01 sec)

Verification

Execute the following query to verify whether all the records from CUSTOMERS table have been deleted or not −

Select * From CUSTOMERS;

As we can see the output below, empty set has been returned i.e all the records have been deleted.

Empty set (0.00 sec)

Delete Query in MySQL Using a Client Program

Besides deleting records of a database table with a MySQL query, we can also use a client program to perform the DELETE operation.

Syntax

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

To delete data in a database table through PHP program, we need to execute the DELETE statement using the mysqli function query() as follows −

$sql="DELETE FROM table_name [WHERE Clause]";
$mysqli->query($sql);

To delete data in a database table through Node.js program, we need to execute the DELETE statement using the query() function of the mysql2 library as follows −

sql = "DELETE FROM table_name [WHERE Clause]";
VALUES (value1, value2, value3,...valueN)"
con.query(sql);

To delete data in a database table through Java program, we need to execute the DELETE statement using the JDBC function executeUpdate() as follows −

String sql="DELETE FROM table_name [WHERE Clause]";
statement.executeUpdate(sql);

To delete data in a database table through Python program, we need to execute the DELETE statement using the execute() function of the MySQL Connector/Python as follows −

delete_query = "DELETE FROM table_name [WHERE Clause]"
cursorObj.execute(delete_query);

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(''DELETE FROM tutorials_tbl where tutorial_id = 4'')) {
   printf("Table tutorials_tbl record deleted successfully.<br />");
}
if ($mysqli->errno) {
   printf("Could not delete record from table: %s<br />", $mysqli->error);
}
$sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date 
   FROM tutorials_tbl";

$result = $mysqli->query($sql);
  
if ($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) {
      printf("Id: %s, Title: %s, Author: %s, Date: %d <br />", 
         $row["tutorial_id"], 
         $row["tutorial_title"], 
         $row["tutorial_author"],
         $row["submission_date"]);               
   }
} else {
   printf(''No record found.<br />'');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Connected successfully.
Table tutorials_tbl record deleted successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021


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

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

  //Updating a single record in the table
  sql = "DELETE FROM tutorials_tbl WHERE tutorial_id = 5;"
  con.query(sql);

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

Output

The output produced is as follows −

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: 2021-03-27T18:30:00.000Z
  },
  {
    tutorial_id: 3,
    tutorial_title: ''Learning Java'',
    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
  }
]


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class DeleteQuery {
	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 = "DELETE FROM tutorials_tbl WHERE tutorial_id = 6";
            st.executeUpdate(sql);
            System.out.println("Delete query executed successfully..!");
            String sql1 = "SELECT * FROM tutorials_tbl";
            rs = st.executeQuery(sql1);
            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();
		}
	}
}

Output

The output obtained is as shown below −

Delete query executed successfully..!
Table records: 
Id: 1, Title: Learn PHP, Author: John Paul, Submission_date: 2023-08-08
Id: 2, Title: Angular Java, Author: Abdul S, Submission_date: 2023-08-08
Id: 3, Title: Learning Java, Author: Sanjay, Submission_date: 2007-05-06
Id: 4, Title: Python Tutorial, Author: Sasha Lee, Submission_date: 2016-09-04
Id: 5, Title: Hadoop Tutorial, Author: Chris Welsh, 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''
)
#Creating a cursor object 
cursorObj = connection.cursor()
delete_query = "DELETE FROM tutorials_tbl WHERE tutorial_id = 6"
cursorObj.execute(delete_query)
connection.commit()
print("Row deleted successfully.")
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

Row deleted successfully.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

user