Programming ColdFusion MX, 2nd Edition

A Quick SQL Primer

Before we go any further, a quick primer on SQL is in order. If you are already an SQL guru, feel free to skip this section. If, however, you are new to SQL, this section quickly covers the basic elements that go into creating an SQL statement. This primer is by no means a substitute for a thorough lesson on SQL. You may want to consult additional SQL references before proceeding, as a good understanding of SQL is an essential element in ColdFusion application design. One of the surest ways to bottleneck your applications is with poorly written SQL. Additionally, SQL is implemented in slightly different ways across various RDBMS platforms. For this reason, it is important to consult the documentation specific to your database to understand these differences.

With the disclaimer out of the way, let’s move on and look at the elements that go into creating an SQL statement for use in a cfquery tag. If you don’t completely understand everything we are about to cover, don’t worry. Every aspect (and more) of the SQL we cover in the primer is covered in more detail throughout this and the next chapter.

Most database transactions in a web application can be grouped into one of four categories: selecting, inserting, updating, and deleting data. Not surprisingly, there are four commands in SQL that handle theses tasks. They are SELECT, INSERT, UPDATE, and DELETE, respectively:

SELECT

Retrieves data from a data source

INSERT

Inserts new data in a data source

UPDATE

Updates existing data in a data source

DELETE

Deletes data from a data source

Once you have determined the type of operation you want to perform, the next step is to refine the SQL statement by adding various clauses and operators. Depending on the action you want to perform, the syntax of the SQL statement varies. Here are some common SQL clauses:

FROM

The table name or names you want to perform the SELECT or DELETE action against

INTO

Specifies the table name and column names you want to INSERT data into

VALUES

The values to add to the columns specified in INTO when adding data with an INSERT

SET

Specifies the column names you wish to UPDATE with new values

WHERE

Specifies one or more conditions governing what data is returned by a SELECT, what data is changed by a UPDATE, or what data is deleted by a DELETE

ORDER BY

Determines the sort order for records returned by a SELECT

group BY

Groups related data in a SELECT. Frequently used along with aggregate functions (discussed later in the chapter)

HAVING

Generally used in place of a WHERE clause when using the group BY clause

JOIN

Used along with a SELECT statement to retrieve data from two or more related tables

UNION

Combines the results of two record sets returned by a SELECT statement into a single record set, provided both record sets have the same number of columns, and those columns are of compatible or convertible datatypes

SQL provides a number of operators, such as AND, =, and OR, that can be used to construct compound, conditional, and comparison statements. Some of the more popular operators are shown in Table 4-1.

Table 4-1. Common SQL operators

Operator	Description
=	Equal to
<>	Not equal to
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to
+	Plus (addition)
-	Minus (subtraction)
/	Divided by (division)
*	Multiplied by (multiplication)
AND	Both conditions must be True
OR	One or the other condition must be True
NOT	Ignores a condition
IS [NOT] NULL	Value is [not] null
IN	Value is in a list of values
BETWEEN	Value is in the range between one value and another
LIKE	Value is like a wildcarded value; wildcards are % (string) and _ (character)
EXISTS	Used only with subqueries; tests for a nonempty record set

Now we can look at some SQL examples. Here’s how to select all the fields from a database table:

SELECT *
FROM TableName

Here’s how to select specific fields from a table where a certain field must meet a specific condition:

SELECT Field1, Field2
FROM TableName
WHERE Field1 = value

This example shows how to select fields from two different tables:

SELECT TableName1.Field1, TableName2.Field1
FROM TableName1, TableName2

This example selects two fields from a table and orders the result set by the value of Field1 in ascending order:

SELECT Field1, Field2
FROM TableName
WHERE Field1 = Value1 AND Field2 = Value2
ORDER BY Field1 ASC

To perform the same query but have the result set ordered in descending order use this code:

SELECT Field1, Field2
FROM TableName
WHERE Field1 = Value1 OR Field1 = Value2
ORDER BY Field1 DESC

To insert a record into a table, use the INSERT clause with the INTO and VALUES operators:

INSERT INTO TableName(Field1, Field2, Field3)
VALUES('value1', value2, 'value3')

If you want to update an existing row, you can do so on a field-by-field basis using the UPDATE clause along with the SET and WHERE operators:

UPDATE TableName
SET Field1 = 'value1',
    Field2 = value2, 
            Field3 = 'value3'
WHERE Fieldx = valuex

This code deletes a single row of data from a table:

DELETE FROM TableName
WHERE Field = value

If you want to delete multiple rows in one operation, use the IN operator like this:

DELETE FROM TableName
WHERE field IN (field1,field2,fieldx)

String values passed using the IN operator must be surrounded by single quotes. Additionally, many databases have a limit of 1000 items in a single IN list.

Now you should be primed and ready to jump into embedding SQL statements within the cfquery tag. The next section looks at using the cfquery tag in conjunction with SQL to retrieve data from a database and display the results in the browser.