(1)Update data
(2)Delete data
(Free learning recommendation: mysql video Tutorial)
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)
[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)
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>]
[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)
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!