MySQL database update statement DELETE and TRUNCATETABLE

We have previously introduced the INSERT and REPLACE statements in MySQL database UPDATE statements. Today we will introduce the UPDATE, DELETE, and TRUNCATETABLE statements, and introduces the practical application functions of these statements. UPDATEUPDATE is used to update data in a table. This syntax is similar to the second INSERT syntax.

We have previously introduced the INSERT and REPLACE statements in MySQL database UPDATE statements. Today we will introduce the UPDATE, DELETE, and truncate table statements, and introduces the practical application functions of these statements. The UPDATE function is to UPDATE data in a table. This syntax is similar to the second INSERT syntax.

We have previously introduced the INSERT and REPLACE statements in MySQL database UPDATE statements. Today we will introduce the UPDATE, DELETE, and truncate table statements, and introduces the practical application functions of these statements.

UPDATE

UPDATE is used to UPDATE data in a table. This syntax is similar to the second INSERT syntax. The table name and SET expression must be provided. You can add the WHERE clause to limit the range of updated records.

 
 

  
  
UPDATE table_anem SET column_name1 = value1, column_name2 = value2, ... 
  
  
WHERE ... ; 
 
 

The following statement changes the age of the record whose id is 123 in the users table to 24.

 
 

  
  
UPDATE users SET age = 24 WHERE id = 123; 
 
 

Similarly, you can use UPDATE to UPDATE the values of multiple fields: UPDATE users SET age = 24, name = 'Mike 'WHERE id = 123;

The above UPDATE statement specifies a condition through WHERE. Otherwise, UPDATE updates the values of all records in the table.

When updating a record using UPDATE, if the type of the updated field does not match the assigned value, MySQL database (the best combination with PHP) convert the value to a value of the corresponding type. If this field is of the numeric type and the value is assigned beyond the maximum range of the Data Type, MySQL (the best combination with PHP) convert the value to the maximum or minimum value of the range. If the string is too long, MySQL (the best combination with PHP) will cut off the extra strings.

If you set a non-empty field to null, set this field to their default value. The default value of the number is 0, and the default value of the string is a null string (not null, it is "").
　
In either case, UPDATE does not affect the data in the table.

1. When the WHERE condition does not match the record in the table.

2. When we assign the same value to a field, for example, assign the field abc to '123', and the original value of abc is '123 '.

Like INSERT and REPLACE, UPDATE also returns the number of updated records. However, these records do not include records that meet the WHERE condition but are not updated. The following UPDATE statement does not UPDATE any records.

 
 

  
  
UPDATE users SET age = 30 WHERE id = 12; 
  
  
Query OK, 0 rows affected (0.00 sec) 
 
 

Note that if the type of a field is TIMESTAMP, this field is automatically updated when other fields are updated.

In some cases, we need to get the number of rows selected for UPDATE, rather than the number of rows to be updated. We can achieve this through some APIs. For example, the c api provided by MySQL database (the best combination with PHP) provides an option to get the number of records you want. The default number of records obtained by the JDBC driver of MySQL (the best combination with PHP) is also the number of matching records.

UPDATE and REPLACE are similar, but there are two differences between them.

1. UPDATE does not do anything when there is no matching record, while REPLACE is updated when there is a duplicate record and inserted when there is no duplicate record.

2. UPDATE can selectively UPDATE some fields of a record. REPLACE deletes this record and inserts a new record when it finds a duplicate record. That is, all fields are updated.

DELETE and TRUNCATE TABLE

There are two methods to DELETE data in MySQL (the best combination with PHP): one is the DELETE statement and the other is the truncate table statement. You can use the WHERE clause to select the record to be deleted. However, using truncate table will delete all records in the TABLE. Therefore, the DELETE statement is more flexible.

To clear all records in the table, you can use the following two methods:

 
 

  
  
DELETE FROM table1 
  
  
TRUNCATE TABLE table1 
 
 

The TABLE in the second record is optional.

To DELETE some records in a table, you can only use the DELETE statement.

 
 

  
  
DELETE FROM table1 WHERE ...; 
 
 

If DELETE does not add a WHERE clause, it is the same as the truncate table, but they are a little different. That is, DELETE can return the number of deleted records, while the truncate table returns 0.

If an auto-increment field exists in a TABLE, after deleting all records using truncate table and DELETE without WHERE clause, the auto-increment field restores the starting value to 1. if you do not want to do this, you can add the permanent WHERE statement in the DELETE statement, such as WHERE 1 or WHERE true.

 
 

  
  
DELETE FROM table1 WHERE 1; 
 
 

The preceding statement scans each record during execution. But it is not compared, because the WHERE condition is always true. In this way, although the maximum auto-increment value can be maintained, because it scans all records, the execution cost is much greater than the DELETE without the WHERE clause.

The biggest difference between DELETE and truncate table is that DELETE can use the WHERE statement to select the record to be deleted. However, the execution speed is not fast. You can also return the number of deleted records. The truncate table cannot delete the specified record, and the deleted record cannot be returned. But it runs very fast.

Unlike standard SQL statements, DELETE supports the ORDER BY and LIMIT clauses, which allow us to better control the records to be deleted. For example, if you only want to delete part of the records filtered out BY the WHERE clause, you can use LIMIB. If you want to delete the last few records, you can use order by and LIMIT together. Suppose we want to delete the first six records whose name is "Mike" in the users table. You can use the following DELETE statement:

 
 

  
  
DELETE FROM users WHERE name = 'Mike' LIMIT 6; 
 
 

In general, MySQL databases (the best combination with PHP) do not determine which six records are deleted. To be more secure, we can use order by to sort records.

 
 

  
  
DELETE FROM users WHERE name = 'Mike' ORDER BY id DESC LIMIT 6; 
 
 

The above content is an introduction to the update statements of the MySQL database. I hope you will gain some benefits.