Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to update data and delete data in MySQL, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

(1), update data

Update statement is used 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 id value 11, change the age field value to 15, and change the name field value to LimMing,SQL statement as follows

Mysql > update person-> set age = 15 journal name = 'LiMing'-> where id = 11 row affected 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)

Make sure that the update ends with a where clause, specifying the conditions to be met by the updated record through the where clause, and if you omit the where clause, MySQL updates all rows in the table.

[example 2] in the person table, update the record with an age value of 19-22 and change the value of the info field to the student,SQL statement 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 statement is used to delete data from the data table, and the delete condition is allowed to be specified using the where clause. The basic syntax format of the delete statement is as follows

Delete from table_name [where

]

Table_name specifies the table on which to perform the delete operation.

"where" is an optional parameter that specifies the delete condition, and if not, the delete statement deletes 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.05sec) mysql > select *-> from person-> where id = 11 sec empty set

[example 2] in the person table, delete statement is used to delete multiple records at the same time. In the previous update statement, the info field value of the record whose age field value is 19-22 is changed to student. Delete these records here, and 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.05sec) mysql > select * from person where age between 19 and 22 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.05sec) mysql > select * from person;Empty set (0.00 sec)

If you want to delete all the records in the table, you can also use the truncate table statement, and truncate will directly delete the original table and recreate a table with a syntax format of truncate table table_name. Truncate deletes the table rather than the record directly, so it executes faster than delete.

Thank you for reading this article carefully. I hope the article "how to update and delete data in MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.