How to UPDATE query in MySQL database? UPDATE syntax/command/statement

MySQL databaseHow to UPDATE a query? UPDATE syntax/command/statement

MySQL UPDATE query

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

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 the same time.
• You can specify any condition in the WHERE clause.
• You can update data simultaneously in a single table.

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

Update data via command prompt

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

Examples

The following example will update the value of the chenweiliang_title field whose chenweiliang_id is 3 in the data table:

mysql> UPDATE chenweiliang_tbl SET chenweiliang_title='学习 C++' WHERE chenweiliang_id=3;
Query OK, 1 rows affected (0.01 sec)
 
mysql> SELECT * from chenweiliang_tbl WHERE chenweiliang_id=3;
+-----------+--------------+---------------+-----------------+
| chenweiliang_id | chenweiliang_title | chenweiliang_author | submission_date |
+-----------+--------------+---------------+-----------------+
| 3 | 学习 C++ | chenweiliang.com | 2016-05-06 |
+-----------+--------------+---------------+-----------------+
1 rows in set (0.01 sec)

From the result, the chenweiliang_title whose chenweiliang_id is 3 has been modified.

Update data using PHP script

PHP uses the function mysqli_query() to execute SQL statements. You can use or not use the WHERE clause in SQL UPDATE statements.

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 a SQL statement at the mysql> command prompt.

Examples

The following example will update the data of the chenweiliang_title field whose chenweiliang_id is 3.

<?
php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
 die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
$sql = 'UPDATE chenweiliang_tbl
 SET chenweiliang_title="学习 Python"
 WHERE chenweiliang_id=3';
 
mysqli_select_db( $conn, 'chenweiliang' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
 die('无法更新数据: ' . mysqli_error($conn));
}
echo '数据更新成功！';
mysqli_close($conn);
?>