grammar

The following is the general syntax for the SQL DELETE statement to DELETE data from a MySQL table:

DELETE FROM table_name [WHERE Clause]
Copy the code
  • If the WHERE clause is not specified, all records in the MySQL table will be deleted.
  • You can specify any condition in the WHERE clause
  • You can delete records in a single table at once.

The WHERE clause is useful when you want to delete a specified record in a table.

Deletes data from the command line

Here we will use the WHERE clause in the SQL DELETE command to DELETE the selected data from the MySQL table runoob_tbl.

The instance

Select * from runoob_tbl where runoob_id = 3;

The DELETE statement:

mysql> use RUNOOB; Database changed mysql> DELETE FROM runoob_tbl WHERE runoob_id=3; Query OK, 1 row affected (0.23 sec)

Use PHP scripts to delete data

PHP uses the mysqli_query() function to execute SQL statements. You can use or not use the WHERE clause in SQL DELETE commands.

This function has the same effect as the mysql> command to execute SQL commands.

The instance

Select * from runoob_tbl where runoob__id = 3;

MySQL DELETE clause test:

<? php $dbhost = ‘localhost’; $dbuser = ‘root’; $dbpass = ‘123456’; $conn = mysqli_connect($dbuser, $dbpass); if(! $conn) {die(‘ connection failed: ‘. Mysqli_error ($conn)); Mysqli_query ($conn, “set names utf8”); $sql = ‘DELETE FROM runoob_tbl WHERE runoob_id=3’; mysqli_select_db( $conn, ‘RUNOOB’ ); $retval = mysqli_query( $conn, $sql ); if(! $retval) {die(‘ cannot delete data: ‘.mysqli_error ($conn)); } echo ‘delete data successfully! ‘; mysqli_close($conn); ? >