Java Reference
In-Depth Information
Common SQL Statements
SQL Statements are constructed as strings and passed to JDBC. The syntax and examples
for the
CREATE TABLE,INSERT,UPDATE
, and
SELECT
statements follow.
SYNTAX
CREATE TABLE
Create a new table named
newtable
with fields
field1
,
field2
, etc. Data types are
similar to Java and include:
int, bigint, float,
double
, and
var(size)
which
is equivalent to a
String
of
maximum length
size
.
CREATE TABLE newtable
(field1 datatype, field2
datatype, ...)
INSERT
Insert a new row into the table
tableName
where
field1
has the value
field1Value
,
field2
has the value
field2Value
, etc. The data
types for the values must match
those for the corresponding
fields when the table was
created. String values should be
enclosed in single quotes.
INSERT INTO tableName
VALUES (field1Value,
field2Value, ...)
UPDATE
UPDATE tableNameSET field1 =
newValue, field2 = newValue,
...WHERE fieldName Op
someValue
Change the specified fields to
the new values for any rows that
match the
WHERE
clause.
Op
is a
comparison operator such as
=
,
<>
(not equal to),
<
,
>
, etc.
SELECT
SELECT field1, field2 FROM
tableName WHERE fieldname Op
someValue
Retrieve the specified fields
for the rows that match the
WHERE
clause. The * may be
used to retrieve all fields. Omit
the
WHERE
clause to retrieve all
rows from the table.
EXAMPLE
CREATE TABLE names(author varchar(50), author_id int, url
varchar(80))
INSERT INTO names VALUES ('Adams, Douglas', 1, 'http://
www.douglasadams.com')
UPDATE names SET url = 'http://www.douglasadams.com/dna/bio.html'
WHERE author_id = 1
SELECT author, author_id, url FROM names
SELECT author, author_id, url FROM names WHERE author_id > 1
