Take a look at MySQL updating data and deleting data

(1)Update data
(2)Delete data

(Free learning recommendation: mysql video Tutorial)

(1), update data

Use the update statement in MySQL to update records in the table, you can update specific Rows or colleagues update all rows. The basic syntax is as follows:

update table_nameset column_name1 = value1,column_name2 = value2,.....,column_namen = valuenwhere (condition);

[Example 1] In the person table, update the record with an id value of 11, change the age field value to 15, and change the name field value to LimMing, the SQL statement is as follows;

mysql> update person    -> set age =15,name ='LiMing'
    -> where id =11;Query OK, 1 row affected (0.05 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from person where id =11;+----+--------+------+---------+| id | name   | age  | info    |+----+--------+------+---------+| 11 | LiMing |   15 | student |+----+--------+------+---------+1 row in set (0.00 sec)

• Ensure that update ends with a where clause, and specify the conditions that the updated records need to meet through the where clause. If the where clause is ignored, MySQL will update the table All lines.

[Example 2] In the person table, update the records whose age value is 19-22, and change the info field values ​​​​to student. The SQL statement is as follows:

mysql> select * from person where age between 19 and 22;+----+---------+------+------------+| id | name    | age  | info       |+----+---------+------+------------+|  1 | Green   |   21 | Lawyer     ||  2 | Suse    |   22 | dancer     ||  4 | Willam  |   20 | sports man ||  7 | Dale    |   22 | cook       ||  9 | Harry   |   21 | magician   || 10 | Harriet |   19 | pianist    |+----+---------+------+------------+6 rows in set (0.00 sec)mysql> update person 
set info='student' where age between 19 and 22;Query OK, 0 rows affected (0.00 sec)Rows matched: 0  Changed: 0  Warnings: 0mysql> select * from person where age between 19 and 22;+----+---------+------+---------+| id | name    | age  | info    |+----+---------+------+---------+|  1 | Green   |   21 | student ||  2 | Suse    |   22 | student ||  4 | Willam  |   20 | student ||  7 | Dale    |   22 | student ||  9 | Harry   |   21 | student || 10 | Harriet |   19 | student |+----+---------+------+---------+6 rows in set (0.00 sec)

(2), delete data

Delete data from the data table using the delete statement, allowing the use of where clause to specify deletion conditions. The basic syntax format of the delete statement is as follows;

delete from table_name [where < condition>]

• table_name specifies the table to be deleted.
• "where" is an optional parameter, specifying the deletion condition. If not, the delete statement will delete all records in the table.

[Example 1] In the person table, delete the record with id equal to 11.

mysql> select *
    -> from person    -> where id =11;+----+--------+------+---------+| id | name   | age  | info    |+----+--------+------+---------+| 11 | LiMing |   15 | student |+----+--------+------+---------+1 row in set (0.00 sec)mysql> delete from person    -> where id = 11;Query OK, 1 row affected (0.05 sec)mysql> select *
    -> from person    -> where id = 11;Empty set (0.00 sec)

[Example 2] In the person table, use the delete statement to delete multiple records at the same time. In the previous update statement, change the info field value of the record whose age field value is 19-22 to student, here To delete these records, the SQL statement is as follows:

mysql> select * from person where age between 19 and 22;+----+---------+------+---------+| id | name    | age  | info    |+----+---------+------+---------+|  1 | Green   |   21 | student ||  2 | Suse    |   22 | student ||  4 | Willam  |   20 | student ||  7 | Dale    |   22 | student ||  9 | Harry   |   21 | student || 10 | Harriet |   19 | student |+----+---------+------+---------+6 rows in set (0.00 sec)mysql> delete from person where age between 19 and 22;Query OK, 6 rows affected (0.05 sec)mysql> select * from person where age between 19 and 22;Empty set (0.00 sec)

[Example 3] Delete all records in the person table, the SQL statement is as follows:

mysql> select * from person;+----+---------+------+-----------+| id | name    | age  | info      |+----+---------+------+-----------+|  3 | Mary    |   24 | Musician  ||  5 | Laura   |   25 | NULL      ||  6 | Evans   |   27 | secretary ||  8 | Edison  |   28 | singer    || 12 | Beckham |   31 | police    |+----+---------+------+-----------+5 rows in set (0.00 sec)mysql> delete from person;Query OK, 5 rows affected (0.05 sec)mysql> select * from person;Empty set (0.00 sec)

• If you want to delete all records in the table, also You can use the truncate table statement. truncate will directly delete the original table and re-create a table. Its syntax format is truncate table table_name. truncate directly deletes the table instead of deleting records, so the execution speed is faster than delete.

Related free learning recommendations: mysql database(Video)

The above is the detailed content of Take a look at MySQL updating data and deleting data. For more information, please follow other related articles on the PHP Chinese website!