Shulou(Shulou.com)06/01 Report--

MySQL UPDATE query

If we need to modify or update the data in MySQL, we can use the SQL UPDATE command to do so. .

Grammar

The following is the general SQL syntax for the UPDATE command to modify MySQL data table data:

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

You can update one or more fields at once.

You can specify any condition in the WHERE clause.

You can update the data at the same time in a single table.

The WHERE clause is very useful when you need to update the data of a specified row in a data table.

Update data from the command prompt

Below we will use the WHERE clause in the SQL UPDATE command to update the data specified in the runoob_tbl table:

Example

The following example updates the value of the runoob_title field with runoob_id 3 in the data table:

SQL UPDATE statement:

Mysql > UPDATE runoob_tbl

SET runoob_title=' learns Clearing skills'

WHERE runoob_id=3;Query OK, 1

Rows affected (0.01sec)

Mysql > SELECT * from runoob_tbl

WHERE runoob_id=3 +-+ | runoob_id | runoob_title | runoob_author | submission_date | +- -- +-+ | 3 | Learning C++ | RUNOOB.COM | 2016-05-06 | +-+ 1

Rows in set (0.01sec)

As a result, the runoob_title with a runoob_id of 3 has been modified.

Use PHP scripts to update data

The function mysqli_query () is used in PHP to execute the SQL statement. You can use the WHERE clause in the SQL UPDATE statement or not.

Note: do not use the WHERE clause to update all the data in the data table, so be careful.

This function has the same effect as executing the SQL statement at the mysql > command prompt.

Example

The following example updates the data for the runoob_title field with runoob_id 3.

MySQL UPDATE statement test:

MySQL WHERE clause

MySQL DELETE statement

Note list

The update statement can be used to modify the data in a table. To put it simply, the basic form of use is:

Update table name set column name = new value where update condition

The following is an example in the table students:

Change the mobile phone number with id 5 to the default -: update students settel=default where id=5

Increase the age of all by 1: update students set age=age+1

Change the name of the mobile phone number 13288097888 to "Xiaoming", and the age to 19: update students setname= "Xiaoming", age=19 wheretel= "13288097888"

UPDATE replaces a character in a field

When we need to bulk modify a specific string in a field to another string, we have used the following actions:

UPDATE table_name SET field=REPLACE (field, 'old-string',' new-string') [WHERE Clause]

Example:

The following example replaces "C++" with "Python" for the value of the runoob_title field updated with runoob_id 3:

UPDATE runoob_tbl SET runoob_title = REPLACE (runoob_title, 'Python') where runoob_id = 3

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.