As shown in the previous page we now have an empty table named person. What can we do with such a table? Just use it like a bag! Store things in it, look into it to check the existence of things, modify things in it or throw things out of it. These are the four natural operations, which concerns data in tables:
- INSERT: put some data into the table
- SELECT: retrieve data from the table
- UPDATE: modify data, which exists in the table
- DELETE: remove data from the table.
Each of these four operations are expressed by their own SQL command. They start with a keyword and runs up to a semicolon at the end. This rule applies to all SQL commands: They are introduced by a keyword and terminated by a semicolon. In the middle there may be more keywords as well as object names and values.
Store new Data with INSERT CommandEdit
When storing new data in rows of a table we must name all affected objects and values: the table name (there may be a lot of tables within the database), the columnnames and the values. All this is embedded within some keywords so that the SQL compiler can recognise the tokens and their meaning. In general the syntax for a simple INSERT is
INSERT INTO <tablename> (<list_of_columnnames>) VALUES (<list_of_values>);
Here is an example
-- put one row INSERT INTO person (id, firstname, lastname, date_of_birth, place_of_birth, ssn, weight) VALUES (1, 'Larry', 'Goldstein', date'1970-11-20', 'Dallas', '078-05-1120', 95); -- confirm the INSERT command COMMIT;
When the DBMS recognises the keywords INSERT INTO and VALUES it knows what to do: it creates a new row in the table and puts the given values into the named columns. In the above example the command is followed by a second one: COMMIT confirms the INSERT operation as well as the other writing operations UPDATE and DELETE. (We will learn much more about COMMIT and its counterpart ROLLBACK in a later chapter.)
- A short comment about the format of the value for date_of_birth: There is no unique format for dates honored all over the world. Peoples use different formats depending on their cultural habit. For our purpose we decide to represent dates in the hierarchical format defined in ISO 8601. It may be possible that your local database installation use a different format so that you are forced to either modify our examples or to modify the default date format of your database installation.
Now we will put some more rows into our table. To do so we use a variation of the above syntax. It is possible to omit the list of columnnames if the list of values correlates exactly with the number, order and data type of the columns used in the original CREATE TABLE statement.
- Hint: The practice of omitting the list of columnnames is not recommended for real applications! Table structures change over time, e.g. someone may add new columns to the table. In this case unexpected side effects may occur in applications.
-- put four rows INSERT INTO person VALUES (2, 'Tom', 'Burton', date'1980-01-22', 'Birmingham', '078-05-1121', 75); INSERT INTO person VALUES (3, 'Lisa', 'Hamilton', date'1975-12-30', 'Mumbai', '078-05-1122', 56); INSERT INTO person VALUES (4, 'Debora', 'Patterson', date'2011-06-01', 'Shanghai', '078-05-1123', 11); INSERT INTO person VALUES (5, 'James', 'de Winter', date'1975-12-23', 'San Francisco', '078-05-1124', 75); COMMIT;
Retrieve Data with SELECT CommandEdit
Now our table should contain five rows. Can we be sure about that? How can we check whether everything worked well and the rows and values exist really? To do so, we need a command which shows us the actual content of the table. It is the SELECT command with the following general syntax
SELECT <list_of_columnnames> FROM <tablename> WHERE <search_condition> ORDER BY <order_by_clause>;
As with the INSERT command you may omit some parts. The simplest example is
SELECT * FROM person;
The asterik character '*' indicates 'all columns'. In the result, the DBMS should deliver all five rows each with the seven values we used previously with the INSERT command.
In the following examples we add the actually missing clauses of the general syntax - one after the other.
Add a list of some or all columnnames
SELECT firstname, lastname FROM person;
The DBMS should deliver the two columns firstname and lastname of all five rows.
Add a search condition
SELECT id, firstname, lastname FROM person WHERE id > 2;
The DBMS should deliver the three columns id, firstname and lastname of three rows.
Add a sort instruction
SELECT id, firstname, lastname, date_of_birth FROM person WHERE id > 2 ORDER BY date_of_birth;
The DBMS should deliver the four columns id, firstname, lastname and date_of_birth of three rows in the ascending order of date_of_birth.
Modify Data with UPDATE CommandEdit
If we want to change the values of some columns in some rows we can do so by using the UPDATE command. The general syntax for a simple UPDATE is:
UPDATE <tablename> SET <columnname> = <value>, <columnname> = <value>, ... WHERE <search_condition>;
Values are assigned to the named columns. Unmentioned columns keep unchanged. The search_condition acts in the same way as in the SELECT command. It restricts the coverage of the command to rows, which satisfy the criteria. If the WHERE keyword and the search_condition are omitted, all rows of the table are affected. It is possible to specify search_conditions, which hit no rows. In this case no rows are updated - and no error or exception occurs.
Change one column of one row
UPDATE person SET firstname = 'James Walker' WHERE id = 5; COMMIT;
The first name of Mr. de Winter changes to James Walker whereas all his other values keep unchanged. Also all other rows keep unchanged. Please verify this with a SELECT command.
Change one column of multiple rows
UPDATE person SET firstname = 'Unknown' WHERE date_of_birth < date'2000-01-01'; COMMIT;
The <search_condition> isn't restricted to the Primary Key column. We can specify any other column. And the comparison operator isn't restricted to the equal sign. We can use other operators - they solely have to match the data type of the column.
In this example we change the firstname of four rows with a single command. If there is a table with millions of rows we can change all of them using one single command.
Change two columns of one row
-- Please note the additional comma UPDATE person SET firstname = 'Jimmy Walker', lastname = 'de la Crux' WHERE id = 5; COMMIT;
The two values are changed with one single command.
Remove data with DELETE CommandEdit
The DELETE command removes complete rows from the table. As the rows are removed as a whole there is no need to specify any columnname. The semantics of the <search_condition> is the same as with SELECT and UPDATE.
DELETE FROM <tablename> WHERE <search_condition>;
Delete one row
DELETE FROM person WHERE id = 5; COMMIT;
The row of James de Winter is removed from the table.
Delete many rows
DELETE FROM person; COMMIT;
All remained rows are deleted as we have omitted the <search_condition>. The table is empty, but it still exists.
No rows affected
DELETE FROM person WHERE id = 99; COMMIT;
This command will remove no row as there is no row with id equals to 99. But the syntax and the execution within the DBMS are still perfect. No exception is thrown. The command terminates without any error message or error code.
The INSERT and DELETE commands affect rows in their entirety. INSERT puts a complete new row into a table (unmentioned columns remain empty) and DELETE removes complete rows. In contrast, SELECT and UPDATE affect only those columns that are mentioned in the command; unmentioned columns are unaffected.
The INSERT command (in the simple version of this page) has no <search_condition> and therefore handles exactly one row. The three other commands may affect zero, one or more rows depending on the evaluation of their <search_condition>.