[ TOC ]

Structured Query Language

SQL, the Structured Query Language, is a moderately standardized language for communicating with databases. Unfortunately, most every database out there has their own dialect of SQL, so it is necessary to stick with a bare minimum of commands, so that it is moderately portable whatever database you want to use. There are 4 main SQL commands.

INSERT

INSERT into TABLE
(field1, field2, field3)
values
(numericalvalue, 'string value', 'string value')

Used to insert a new record into a table. Note that string values should be enclosed in quotes, and numerical values should not. Additionally, most databases have a variety of other data types, with their own particular syntax rules. For the most part, non-numerical fields are enclosed in quotes. Some data types may need to have a very particular format, such as date/time data types.
Note also that if your string value includes the single quote character ('), you will probably be required to escape that character in some fashion. For some databases, this is done with a backslash - \' - while in other databases, this is done by putting two quote characters - '' - consult the documentation for your particular database. It would be real nice if this were more standard, but that's life.

Example:

INSERT into employees
(name, office)
values
('Rich Bowen', 378)

SELECT

SELECT [* | field_list]
from table_name [, table2, ...]
[WHERE clause]
[ORDER BY clause]

Used to read one or more records out of one or more tables from one or more databases. The * character indicates that you want all fields. This can be indicated with the keyword ALL in some databases, such as MS SQL Server. Alternatively, you can list only those fields in which you are interested.

The SELECT statement has perhaps the most variations between databases, with some supporting enormously complicated syntax, and others only supporting the options presented above. You will probably need to use the syntax here, if you want your code to be reasonably portable. Or, consult your database's documentation for the options supported.

Example:

SELECT fname, lname, customerid, address
from customer
where lname = 'Winfield'
order by fname, address

UPDATE

UPDATE table_name
SET column_name=value, ... column_name_n = value
[WHERE column_name comparison_operator value]

Used to modify the values of an existing record. The WHERE clause is optional, so be careful, or you may end up modifying all of the records in a table unintentionally.

Example:

UPDATE employee
set lname='Underwood',
marital = 1
where ID=17

DELETE

DELETE from table_name
[WHERE clause]

Used to permanently remove a record from a table. Note that the WHERE clause is optional, so be careful, or you may end up removing all the records from a table unintentionally.

Example:

DELETE from customers
where delinquent_days > 90

[ TOC ]