 Create a simple Table | Structured Query Language Handle Data | Example Database Structure |
As shown in the previous page, we now have an empty table namedperson. 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 essential operations, which concerns data in tables:
For each of these four operations, there is a SQL command. It starts with a keyword and runs up to a terminating semicolon. 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.
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 column names and the values. All this is embedded within some keywords so that the SQL compiler can recognize the tokens and their meaning. In general, the syntax for a simple INSERT is
INSERTINTO<tablename>(<list_of_columnnames>)VALUES(<list_of_values>);
Here is an example
-- put one rowINSERTINTOperson(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 commandCOMMIT;
When the DBMS recognizes 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.)
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 column names if the list of values correlates precisely with the number, order, and data type of the columns used in the original CREATE TABLE statement.
-- put four rowsINSERTINTOpersonVALUES(2,'Tom','Burton',date'1980-01-22','Birmingham','078-05-1121',75);INSERTINTOpersonVALUES(3,'Lisa','Hamilton',date'1975-12-30','Mumbai','078-05-1122',56);INSERTINTOpersonVALUES(4,'Debora','Patterson',date'2011-06-01','Shanghai','078-05-1123',11);INSERTINTOpersonVALUES(5,'James','de Winter',date'1975-12-23','San Francisco','078-05-1124',75);COMMIT;
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>ORDERBY<order_by_clause>;
As with the INSERT command, you may omit some parts. The simplest example is
SELECT*FROMperson;
The asterisk 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 currently missing clauses of the general syntax - one after the other.
Add a list of some or all columnnames
SELECTfirstname,lastnameFROMperson;
The DBMS should deliver the two columnsfirstname andlastname of all five rows.
Add a search condition
SELECTid,firstname,lastnameFROMpersonWHEREid>2;
The DBMS should deliver the three columnsid,firstname andlastname of three rows.
Add a sort instruction
SELECTid,firstname,lastname,date_of_birthFROMpersonWHEREid>2ORDERBYdate_of_birth;
The DBMS should deliver the four columnsid,firstname,lastname anddate_of_birth of three rows in the ascending order ofdate_of_birth.
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
UPDATEpersonSETfirstname='James Walker'WHEREid=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
UPDATEpersonSETfirstname='Unknown'WHEREdate_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 different operators - they solely have to match the data type of the column.
In this example, we change thefirstname 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 commaUPDATEpersonSETfirstname='Jimmy Walker',lastname='de la Crux'WHEREid=5;COMMIT;
The two values are changed with one single command.
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.
DELETEFROM<tablename>WHERE<search_condition>;
Delete one row
DELETEFROMpersonWHEREid=5;COMMIT;
The row of James de Winter is removed from the table.
Delete many rows
DELETEFROMperson;COMMIT;
All remained rows are deleted as we have omitted the <search_condition>. The table is empty, but it still exists.
No rows affected
DELETEFROMpersonWHEREid=99;COMMIT;
This command will remove no row as there is no row withid 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 entire 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>.
| Create a simple Table | Structured Query Language Handle Data | Example Database Structure |