MySQL DELETE statement


MySQL DELETE statement

You can use the SQL DELETE FROM command to delete records in the MySQL data table.

You can execute this command in the mysql> command prompt or PHP script.

Syntax

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

DELETE FROM table_name [WHERE Clause]

  • If not Specifying the WHERE clause, 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 in one go.

#The WHERE clause is very useful when you want to delete specified records in the data table.

Delete data from the command line

Here we will use the WHERE clause in the SQL DELETE command to delete the MySQL data table selected by user The data.

Example

The following example will delete the record with user_id 3 in the user table: 

mysql> use demo;
Database changed
mysql> DELETE FROM user WHERE user_id=3;
Query OK, 1 row affected (0.23 sec)

Use PHP script to delete data

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

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

Example

The following PHP example will delete the record with user_id 3 in the user table:

<?php
$dbhost = ' localhost'; // mysql server host address
$dbuser = 'root'; , $dbuser, $dbpass);
if(! $conn )
{
die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding , to prevent Chinese garbled characters
mysqli_query($conn , "set names utf8");

$sql = 'DELETE FROM user
WHERE user_id=3';

mysqli_select_db( $ conn, 'demo' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('Unable to delete data: ' . mysqli_error($ conn));
}
echo 'Data deletion successful! ';
mysqli_close($conn);

?>

Related video tutorial recommendations:

MySQL Authoritative Development Guide (Tutorial)

←
→