The keywords that Manipulate data and control transactions are called Data Manipulation Language or DML. With out the DML a database would be useless and a waste.
Inserting Data
Syntax
INSERT INTO tablename [(columnname, ...)]
VALUES (datavalue, ...);
When inserting data if you are inserting a full row of data, placing data in all columns then you can omit the column names as long as you follow the order of the columns in the table. If there are no constraints that require a value to be inserted into the column you can opt out those values but you must then name the columns you wish to place data in if you either omit a column or change the order of data. Another way of not naming the columns is to insert a NULL into the column.
When moving data from table to table, an easy way is to use a subquery on an INSERT statement.
Syntax
INSERT INTO [(columnname, ...)]
subquery;
In a subquery the values that you are inserting are going to be defined in the subquery. The subquery does not need to be encased in parentheses. When copying data from another table and the columns are the same, you must be sure that they are in the same order as they appear in the table that you are copying it to. If you are copying specific tables from another table then you must make sure they line up accordingly to the columns in the table that you are copying them to.
table1
| name | address | phone | birthday | income |
table2
| name | birthday | income |
To populate table2 with data from table1 using a subquery, you would issue the following to Oracle:
INSERT INTO table2
SELECT name, birthday, income
FROM table1;
To only insert only the name and income into table2 from table1:
INSERT INTO table2(name, income)
SELECT name, income
FROM table1;
Updating Data
As things change, you may need to change the data that's stored in the database. Using the UPDATE command accomplishes this.
Syntax
UPDATE tablename
SET columnname = value
[WHERE condition];
If the WHERE clause is omitted then Oracle will set ALL values in the columnname. After inserting hundreds of records you're told that something has changed, instead of issuing hundreds of update commands you can use a substitute variable. When using a substitute variable Oracle will ask for the value of each substitute variable from the user and then do it's magic.
UPDATE customers
SET region = '&Region'
WHERE state = '&State';
When the user enters the value 'N' for '&Region' and 'WI' for '&State' Oracle will find all rows where 'WI' is listed under the state column and set the 'region' column to 'N.'
Deleting Data
To delete data from the table the DELETE command can be issued.
Syntax
DELETE FROM tablename
[WHERE condition];
Be very careful when deleting data from a table. If you omit the WHERE clause all data in the row will be deleted.
Transaction Control Statements
Issuing any DML Commands (INSERT, UPDATE, and DELETE) is not permanently saved. You can see the changes but they don't affect the database as a whole until you have committed the changes. The group of commands used to alter a table or database is called a transaction. For the changes to be permanent you must issue the COMMIT. However, when using a Data Definition Language Command (DDL) the COMMIT is implicitly issued. Let's say that you inserted, deleted, and modified some rows. For this to take effect you must issue this command:
COMMIT;
If you do your modification and use a CREATE or ALTER TABLE then you will not need to issue the COMMIT command because it's issued right after the CREATE or ALTER commands. Until the COMMIT statement has been issued the changes can be rolled back by issuing a ROLLBACK command. You can create a save point at anytime during the creation of a transaction. The SAVEPOINT and ROLLBACK are very straight forward commands:
SAVEPOINT savepointname;
ROLLBACK TO savepointname;
Locking the Table
Oracle protects against data being modified by two users at the same time by locking the row. When a user has a row locked, Oracle makes it known that a row has been locked so another user doesn't override that lock with a table lock. During the time that a row is locked another user can view row but can not modify it. This is known as a shared lock. A user can explicitly lock a table in share mode by issuing the LOCK TABLE command.
LOCK TABLE tablename IN SHARE | EXCLUSIVE MODE;
During a DDL operation Oracle puts an exclusive lock on the table so no other user can add, modify, or delete data.