MySQL Delete Command


To remove data into a MySQL table, you would need to use the SQL Delete from command. You can delete a record(s) from the MySQL table by using the mysql> prompt or by using any script like Python
Syntax
Here is a generic SQL syntax of Delete from command to remove data from the MySQL table −
Delete command Syntax

 Delete from TableName
[ Where  ] 

Sample Database to Demonstarte DELETE From Command

mysql> select * from student;
+-------+---------+--------+-------+-------+-------------+--------+
| admno | name    | stream | marks | grade | phone       | gender |
+-------+---------+--------+-------+-------+-------------+--------+
| 101   | rakesh  | comm   | 89.00 | A     | 9871812332  | M      |
| 102   | udit    | sci    | 56.50 | B     | 0000000000  | M      |
| 103   | mohit   | bio    | 46.50 | C     | 0000000001  | M      |
| 104   | mannat  | human  | 67.50 | A     | 99887766551 | F      |
| 105   | unnati  | comm   | 77.50 | B     | 96887766551 | F      |
| 106   | nikunj  | sci    | 79.50 | A     | 96787766551 | M      |
| 107   | pushkar | comm   | 54.20 | C     | 96787466551 | M      |
| 108   | pratham | comm   | 84.80 | A     | 9678784651  | M      |
| 109   | mohit   | comm   | 64.80 | B     | 9673784651  | M      |
| 110   | urshavi | bio    | 96.80 | A     | 92673784651 | M      |
| 111   | punya   | comm   | 86.56 | B     | 0000000004  | F      |
+-------+---------+--------+-------+-------+-------------+--------+
11 rows in set (0.00 sec)

Delete Records from student Table from MySQL Prompt

Example

The following commands were used at MySQL prompt to remove records from the table student of school database

root@host# mysql -u root -p password;
Enter password:*******
mysql> use SCHOOL;
Database changed
mysql> delete from student 
       -> where name='rakesh';
Query OK, 2 rows affected (0.01 sec)

mysql> delete from student 
       -> where name like 'r%' and admno=120;
Query OK, 0 rows affected (0.00 sec)

mysql>delete from student 
         -> where admno >105 and admno<=106;
    Query OK, 1 row affected (0.00 sec)

NOTE: The condition in DELETE from command is optional. when the condition is missing from the delete command then the command is bound to remove all the records from that database table. So use this command very carefully.

The only way to recover such data is – insert the same data using MySQL insert command

Delete Record from the Table Using Python Script

Try the following Python script to remove record(s) from the MySQL Table

import mysql.connector
conn = mysql.connector.connect(host='localhost',user='root',password='',database="school")
cursor = conn.cursor()
cursor.execute("delete from student where admno=102;")
conn.close()
print('Record deleted successfully')

The output of the above program is 
Record deleted successfully
Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.