一. Data Manipulation Language(DML) Statements
1. Insert
- Insert one row to one table with one statement
INSERT INTO table [(column [, column...])] VALUES (value [,value...]);
- Insert multiple rows to one table with on statement
INSERT INTO table [column [, column ...] ]subquery;
- Insert rows into several tables with one statement
2. Update
All the rows updated will be in one table, it is not possible for a single update command to affect rows in multiple tables.
UPDATE table
SET column=[subquery] [,column=subquery...]
WHERE column = (subquery) [AND column=subquery...] ;
3. Delete
A deletion is all or nothing, applies to the whole row.
DELETE FROM table [WHERE condition];
4. Truncate
Truncate is a DDL command, insert/update/delete are DML commands.
Difference: DML --- Made permanent or reversed by users.
DDL --- Immediately permanent, and never be reversed. Faster than DML. DDL commands, such as TRUNCATE, will fail if there is any DML command
active on the table.
Difference: DELETE --- Leaves the high water mark where it is.
TRUNCATE --- Resets the high water mark.
TRUNCATE TABLE table;
二. DML Statement Failures
- Syntax errors
- References to nonexistent objects or columns
- Access permissions
- Constraint violations
- Space issues
三. Control Transactions
1. Database Transactions
ACID test:
- A (Atomicity)
Anything goes wrong before the transaction is complete, the database itself must guarantee that any parts that did go through are reversed.
- C(Cnsistency)
The result of a query must be consistent with the state of the database at the time the query started.
- I (Isolation)
An incomplete transacation must be invisible to the rest of the world.
- D (Durability)
Once a transaction completes, it must be impossible for the database to lose it.
2. Execute SQL statement
- Select Statement
Check whether the bolcks containing the data required are already in memory, if not copy it into database buffer cache.
For any DML statements, it is necessary to work on both data blocks and undo blocks, and also to generate redo
- Update Statement
Check data blocks in memory.
An empty block of an undo segement is needed.
Locks must be placed.
Redo is generated to table block changes and undo block changes. New value to table block, old value to undo block
- Insert and Delete Statement
A crucial difference between INSERT and DELETE is in the amount of undo generated. Insert : write the new rowid to undo.Delete: write the whole row to undo.
3. Start and End of a Transaction
-
The SQL standard does not allow user to start new transaction before old one terminated.
-
The explicit transaction control statements are COMMIT, ROLLBACK, SAVEPOINT
-
The implicit terminating :
Issuing a DDL or DCL statement
Exiting from the user tool
If the client session dies
If the system crashes
4. Transaction Control
- COMMIT
- ROLLBACK
- SAVEPOINT
- SELECT FOR UPDATE