Java Reference
In-Depth Information
and column names (to make the statement more readable) are helpful but not required. In fact, the command could be
entered as a continuous character string on one line. Note that SQL, like Java, ignores the extra spaces.
Defining tables (and schemas) in an application is very unusual. These DB objects are usually defined once by
a database analyst, not every time an application is run. Please remember that a table must be created in a schema
before an application can access the table.
Manipulating Data in a Table
SQL has many commands to modify and format data in a table. We will concentrate on the four basic commands of
insert into
,
select
,
update
, and
delete
.
Use the SQL
insert into
command to enter a row of data into a table. The command is followed by:
The name of the schema and table
The
values
keyword
The values to be inserted enclosed in parentheses with a comma separating each value
Additionally, character values must be enclosed in single quotes. As an example, the following statement would
insert an employee row for Mary Worker:
INSERT INTO tntdb.employee
VALUES('111', 'Mary Worker', '1 Main St.',
'Enid', 'OK', '77777', 17.50, 3)
Remember to be careful about spaces, commas, parentheses, and quotes.
This example inserted data for every field. You can insert data for only some fields, but you must specify which
fields (and the database has to be defined to allow partial records).
The following is an example of inserting a row with only four columns of data specified.
INSERT INTO tntdb.employee (empnum,empname,payrate,exempts)
VALUES('222', 'Joe Programmer', 17.50, 2)
Notice that the column names are specified in parentheses, separated by a comma, and at least one space after
the table name. There must also be at least one space separating the closing parenthesis and the
values
keyword.
The
select
command retrieves data from a table. The programmer can identify specific rows and or columns to
be retrieved. The syntax of the
select
statement requires at least the
select
keyword followed by the column names to
be retrieved (separated by commas, if individually specified), the
from
keyword, and the table name.
The following statement returns all rows and columns of the employee table:
SELECT * FROM tntdb.employee
The returned result set would look like the following:
111 Mary Worker 1 Main St. Enid OK 77777 17.5 3
222 Joe Programmer 17.5 2
Notice that an asterisk was specified after the select keyword. This means that all columns should be retrieved. If
the statement had only specified four fields as in the following:
SELECT empnum, empname, payrate, exempts
FROM tntdb.employee
