SQL must-know note the 16th Chapter updates and inserts data

16.1 Updating data

In order to update (modify) The data in the table, you can use the UPDATE statement. You can use update in two ways:
(1) Updating specific rows in a table
(2) Update all lines marked red
Do not omit the WHERE clause: always be careful when using update. Because you are not careful, all rows in the table are updated.
The basic UPDATE statement consists of 3 parts:
(1) The table to update
(2) column names and their new values
(3) Determine which rows to update the filter criteria
Do not omit the WHERE clause
Update a single column

UPDATE CustomersSET cust_email = ‘[email protected]‘WHERE cust_id = ‘1000000005‘;

Update multiple columns

UPDATE CustomersSET cust_contact = ‘Sam Roberts‘,     cust_email = ‘[email protected]‘WHERE cust_id = ‘1000000006‘;

Use a subquery in an UPDATE statement: You can use a subquery in an UPDATE statement to update the column data with the data retrieved from the SELECT statement.
To remove a column's value, set it to null

UPDATE CustomersSET cust_email = NULLWHERE cust_id = ‘1000000005‘;

16.2 Deleting data

In order to delete data from a table, use the Delect statement. You can use delete in two ways:
(1) Delete a specific row from the table
(2) Remove all rows from the table
Delete a row

DELETE FROM CustomersWHERE cust_id = ‘1000000006‘;

Delete does not require a column name or wildcard character. Delete Deletes the entire row instead of deleting the column. In order to delete the specified column, use the UPDATE statement.
Delete the contents of a table instead of a table: The DELETE statement deletes rows from the table, or even deletes all rows in the table. However, delete does not delete the table itself.
Faster deletion: If you want to delete all rows from a table, do not use Delete, and use the TRUNCATE TABLE statement, he does the same work, but faster (because the data changes are not recorded).

16.3 Known principles of updates and deletions

Using update or delete is a habit to follow:
(1) Never use an UPDATE or DELETE statement without a WHERE clause unless you do intend to update and delete each row.
(2) Ensure that each table has a primary key, and use it as much as possible as a WHERE clause (you can specify a range of primary keys, multiple values, or values).
(3) before using the WHERE clause on an UPDATE or DELETE statement, you should test with select to ensure that it filters the correct records in case the WHERE clause being written is incorrect.
(4) Use a database that enforces referential integrity, so that the DBMS will not allow the deletion of rows that have data associated with other tables.
(5) Some DBMS allows the database administrator to impose constraints to prevent the execution of an update or delete without a WHERE clause.
Use caution: SQL does not have the Undo button, you should use update and delete very carefully, or you may find yourself updating or deleting the wrong data.

SQL must know note 16th update and insert data