Mysqli 简明教程

MySQLi - Delete Query

如果您想从任何 MySQL 表中删除记录,那么您可以使用 SQL 命令 DELETE FROM 。您可以在 mysql> 提示符处以及在 PHP 等任何脚本中使用此命令。

If you want to delete a record from any MySQL table, then you can use the SQL command DELETE FROM. You can use this command at the mysql> prompt as well as in any script like PHP.

Syntax

以下代码块是 DELETE 命令的通用 SQL 语法,用于从 MySQL 表中删除数据。

The following code block has a generic SQL syntax of the DELETE command to delete data from a MySQL table.

DELETE FROM table_name [WHERE Clause]
  1. If the WHERE clause is not specified, then all the records will be deleted from the given MySQL table.

  2. You can specify any condition using the WHERE clause.

  3. You can delete records in a single table at a time.

当你想删除表中的选定行时,WHERE 子句非常有用。

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

Deleting Data from the Command Prompt

这将使用带有 WHERE 子句的 SQL DELETE 命令将所选数据删除到 MySQL 表中 - tutorials_tbl

This will use the SQL DELETE command with the WHERE clause to delete selected data into the MySQL table – tutorials_tbl.

Example

以下示例将从教程 ID 为 3 的 tutorial_tbl 中删除一条记录。

The following example will delete a record from the tutorial_tbl whose tutorial_id is 3.

root@host# mysql -u root -p password;
Enter password:*******

mysql> use TUTORIALS;
Database changed

mysql> DELETE FROM tutorials_tbl WHERE tutorial_id=3;
Query OK, 1 row affected (0.23 sec)

mysql>

Deleting Data Using a PHP Script

PHP 使用 mysqli query()mysql_query() 函数来删除 MySQL 表中的记录。这个函数接收两个参数,并在成功时返回 TRUE,并在失败时返回 FALSE。

PHP uses mysqli query() or mysql_query() function to delete records in a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli→query($sql,$resultmode)

Sr.No.

Parameter & Description

1

$sql Required - SQL query to delete records in a MySQL table.

2

$resultmode Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

尝试使用以下示例来删除表中的记录 -

Try the following example to delete a record in a table −

将以下示例复制粘贴为 mysql_example.php:

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Deleting MySQL Table record</title>
   </head>
   <body>
      <?php
         $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();
      ?>
   </body>
</html>

Output

访问在 apache web 服务器上部署的 mysql_example.php,并验证输出。在运行选择脚本之前,我们在此表中输入了多条记录。

Access the mysql_example.php deployed on apache web server and verify the output. Here we’ve entered multiple records in the table before running the select script.

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