Using SQL

This section introduces SQL, describes basic SQL syntax, and contains examples of SQL statements. It provides enough information for you to begin using ColdFusion. However, this section does not contain an exhaustive description of the entire SQL programming language. For complete SQL information, see the SQL reference that ships with your database.

A query is a request to a database. The query can ask for information from the database, write new data to the database, update existing information in the database, or delete records from the database.

The Structured Query Language (SQL) is an ANSI/ISO standard programming language for writing database queries. All databases supported by ColdFusion support SQL and all ColdFusion tags that access a database allow you to pass SQL statements to the tag.

SQL example

The most commonly used SQL statement in ColdFusion is the SELECT statement. The SELECT statement reads data from a database and returns it to ColdFusion. For example, the following SQL statement reads all the records from the employees table:

SELECT * FROM employees

You interpret this statement as "Select all rows from the table employees" where the wildcard symbol * corresponds to all rows.

Tip: If you are using Dreamweaver MX, ColdFusion Studio, or HomeSite+ you can use the built-in query builder to build SQL statements graphically by selecting the tables and records to retrieve. For more information, see "Writing queries using an editor".

In many cases, you do not want all rows from a table, but only a subset of rows. The next example returns all rows from the employees table, where the value of the DeptID column for the row is 3:

SELECT * FROM employees WHERE DeptID=3

You interpret this statement as "Select all rows from the table employees where the DeptID is 3".

SQL also lets you specify the table columns to return. For example, instead of returning all columns in the table, you can return a subset of columns:

SELECT LastName, FirstName FROM employees WHERE DeptID=3

You interpret this statement as "Select the columns FirstName and LastName from the table employees where the DeptID is 3".

In addition to with reading data from a table, you can write data to a table using the SQL INSERT statement. The following statement adds a new row to the employees table:

INSERT INTO employees(EmpID, LastName, Firstname) 
VALUES(51, 'Doe', 'John')

Basic SQL syntax elements

The following sections briefly describes the main SQL command elements.

Statements

A SQL statement always begins with a SQL verb. The following keywords identify commonly used SQL verbs:

Keyword

Description

SELECT

Retrieves the specified records.

INSERT

Adds a new row.

UPDATE

Changes values in the specified rows.

DELETE

Removes the specified rows.

Keyword	Description
SELECT	Retrieves the specified records.
INSERT	Adds a new row.
UPDATE	Changes values in the specified rows.
DELETE	Removes the specified rows.

Statement clauses

Use the following keywords to refine SQL statements:

Keyword

Description

FROM

Names the data tables for the operation.

WHERE

Sets one or more conditions for the operation.

ORDER BY

Sorts the result set in the specified order.

GROUP BY

Groups the result set by the specified select list items.

Keyword	Description
FROM	Names the data tables for the operation.
WHERE	Sets one or more conditions for the operation.
ORDER BY	Sorts the result set in the specified order.
GROUP BY	Groups the result set by the specified select list items.

Operators

The following basic operators specify conditions and perform logical and numeric functions:

Operator

Description

AND

Both conditions must be met

OR

At least one condition must be met

NOT

Exclude the condition following

LIKE

Matches with a pattern

IN

Matches with a list of values

BETWEEN

Matches with a range of values

=

Equal to

<>

Not equal to

<

Less than

>

Greater than

<=

Less than or equal to

>=

Greater than or equal to

+

Addition

-

Subtraction

/

Division

*

Multiplication

Operator	Description
AND	Both conditions must be met
OR	At least one condition must be met
NOT	Exclude the condition following
LIKE	Matches with a pattern
IN	Matches with a list of values
BETWEEN	Matches with a range of values
=	Equal to
<>	Not equal to
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to
+	Addition
-	Subtraction
/	Division
*	Multiplication

Case sensitivity with databases

ColdFusion is a case-insensitive programming environment. Case insensitivity means the following statements are equivalent:

<CFSET FOO="BAR">
<CfSet FOO="bar">

However, many databases, especially UNIX databases, are case sensitive. Case sensitivity means that you must match exactly the case of all column and table names in SQL queries.

For example, the following queries are not equivalent on a case-sensitive database:

SELECT LastName FROM EMPLOYEES

SELECT LASTNAME FROM employees

In a case-sensitive database, employees and EMPLOYEES are two different tables.

For information on how your database handles case, see the product documentation.

SQL notes and considerations

When writing SQL in ColdFusion, keep the following guidelines in mind:

There is a lot more to SQL than what is covered here. It is a good idea to purchase one or several SQL guides for reference.
The data source, columns, and tables that you reference must exist in order to perform a successful query.
Some DBMS vendors use nonstandard SQL syntax (known as a dialect) in their products. ColdFusion does not validate the SQL; it is passed on to the database for validation, so you are free to use any syntax that is supported by your database. Check your DBMS documentation for nonstandard SQL usage.

Reading data from a database

You use the SQL SELECT statement to read data from a database. The SQL statement has the following general syntax:

SELECT column_names 
FROM table_names 
[ WHERE search_condition ] 
[ GROUP BY group_expression ] [HAVING condition]
[ ORDER BY order_condition [ ASC | DESC ] ]

The statements in square brackets are optional.

Note: There are additional options to SELECT depending on your database. For a complete syntax description for SELECT, see the product documentation.

This section describes options to the SELECT statement.

Results of a SELECT statement

When the database processes a SELECT statement, it returns a record set containing the requested data. The format of a record set is a table with rows and columns. For example, if you write the following query:

SELECT * FROM employees WHERE DeptID=3

The query returns the following table:

Results from the SELECT query

Since the data returned to ColdFusion by a SELECT statement is in the form of a database table, ColdFusion lets you write a SQL query on the returned results. This functionality is called query of queries. For more information on query of queries, see Chapter 20, "Accessing and Retrieving Data".

The next example uses a SELECT statement to return only a specific set of columns from a table:

SELECT LastName, FirstName FROM employees WHERE DeptID=3

The query returns the following table:

Results of a SELECT query returning selected table columns

Filtering results

The SELECT statement lets you filter the results of a query to return only those records that meet specific criteria. For example, if you want to access all database records for employees in department 3, you use the following query:

SELECT * FROM employees WHERE DeptID=3

You can combine multiple conditions using the WHERE clause. For example, the following example uses two conditions:

SELECT * FROM employees WHERE DeptID=3 AND Title='Engineer'

Sorting results

By default, a database does not sort the records returned from a SQL query. In fact, you cannot guarantee that the records returned from the same query are returned in the same order each time you run the query.

However, if you require records in a specific order, you can write your SQL statement to sort the records returned from the database. To do so, you include an ORDER BY clause in the SQL statement.

For example, the following SQL statement returns the records of the table ordered by the LastName column:

SELECT * FROM employees ORDER BY LastName

You can combine multiple fields in the ORDER BY clause to perform additional sorting:

SELECT * FROM employees ORDER BY DepartmentID, LastName

This statement returns row ordered by department, then by last name within the department.

Returning a subset of columns

You might want only a subset of columns returned from a database table, as in the following example, which returns only the FirstName, LastName, and Phone columns. This example is useful if you are building a web page that shows the phone numbers for all employees.

SELECT FirstName, LastName, Phone FROM employees

However, this query does not to return the table rows in alphabetical order. You can include an ORDER clause in the SQL, as follows:

SELECT the FirstName, LastName, Phone 
FROM employees 
ORDER BY LastName, FirstName

Using column aliases

You might have column names that you do not want to retain in the results of your SQL statement. For example, your database is set up with a column that uses a reserved word in ColdFusion, such as EQ. In this case, you can rename the column as part of the query, as follows:

SELECT EmpID, LastName, EQ as MyEQ FROM employees

The results returned by this query contains columns named EmpID, LastName, and MyEQ.

Accessing multiple tables

In a database, you can have multiple tables containing related information. You can extract information from multiple tables as part of a query. In this case, you specify multiple table names in the SELECT statement, as follows:

SELECT LastName, FirstName, Street, City, State, Zip 
FROM employees, addresses 
WHERE employees.EmpID = addresses.EmpID
ORDER BY LastName, FirstName

This SELECT statement uses the EmpID field to connect the two tables. This query prefixes the EmpID column with the table name. This is necessary because each table has a column named EmpID. You must prefix a column name with its table name if the column name appears in multiple tables.

In this case, you extract LastName and FirstName information from the employees table and Street, City, State, and Zip information from the addresses table. You can use output such as this is to generate mailing addresses for an employee newsletter.

The results of a SELECT statement that references multiple tables is a single result table containing a join of the information from corresponding rows. A join means information from two or more rows is combined to form a single row of the result. In this case, the resultant record set has the following structure:

Results of a SELECT query that performs a join

What is interesting in this result is that even though you used the EmpID field to combine information from the two tables, you did not include that field in the output.

Modifying a database

You can use SQL to modify a database in the following ways:

Inserting data into a database
Updating data in a database
Deleting data from a database
Updating multiple tables

The following sections describe these modifications.

Inserting data into a database

You use SQL INSERT statement to write information to a database. A write adds a new row to a database table. The basic syntax of an INSERT statement is as follows:

INSERT INTO table_name(column_names) 
VALUES(value_list)

where:

column_names specifies a comma-separated list of columns.
value_list specifies a comma-separated list of values. The order of values has to correspond to the order that you specified column names.

Note: There are additional options to INSERT depending on your database. For a complete syntax description for INSERT, see the product documentation.

For example, the following SQL statement adds a new row to the employees table:

INSERT INTO employees(EmpID, LastName, Firstname) 
VALUES(51, 'Smith', 'John')

This statement creates a new row in the employees table and sets the values of the EmpID, LastName, and FirstName fields of the row. The remaining fields in the row are set to Null. Null means the field does not contain a value.

When you, or your database administrator, creates a table, you can set properties on the table and the columns of the table. One of the properties you can set for a column is whether the field supports Null values. If a field supports Nulls, you can omit the field from the INSERT statement. The database automatically sets the field to Null when you insert a new row.

However, if the field does not support Nulls, you must specify a value for the field as part of the INSERT statement; otherwise, the database issues an error.

The LastName and FirstName values in the query are contained within single quotes. This is necessary because the table columns are defined to contain character strings. Numeric data does not require the quotes.

Updating data in a database

Use the UPDATE statement in SQL to update the values of a table row. Update lets you update the fields of a specific row or all rows in the table. The UPDATE statement has the following syntax:

UPDATE table_name 
SET column_name1=value1, ... , column_nameN=valueN 
[ WHERE search_condition ]

Note: There are additional options to UPDATE depending on your database. For a complete syntax description for UPDATE, see the product documentation.

You should not attempt to update a record's primary key field. Your database typically enforces this restriction.

The UPDATE statement uses the optional WHERE clause, much like the SELECT statement, to determine which table rows to modify. The following UPDATE statement updates the e-mail address of John Smith:

UPDATE employees SET Email='jsmith@mycompany.com' WHERE EmpID = 51

Be careful using UPDATE. If you omit the WHERE clause to execute the following statement:

UPDATE employees SET Email = 'jsmith@mycompany.com'

you update the Email field for all rows in the table.

Deleting data from a database

The DELETE statement removes rows from a table. The DELETE statement has the following syntax:

DELETE FROM table_name
[ WHERE search_condition ]

Note: There are additional options to DELETE depending on your database. For a complete syntax description for DELETE, see the product documentation.

You can remove all rows from a table using a statement in the form:

DELETE FROM employees

Typically, you specify a WHERE clause to the DELETE statement to delete specific rows of the table. For example, the following statement deletes John Smith from the table:

DELETE FROM employees WHERE EmpID=51

Updating multiple tables

The examples in this section all describe how to modify a single database table. However, you might have a database that uses multiple tables to represent information.

One way to update multiple tables is to use one INSERT statement per table and to wrap all INSERT statements within a database transaction. A transaction contains one or more SQL statements that can be rolled back or committed as a unit. If any single statement in the transaction fails, you can roll back the entire transaction, cancelling any previous writes that occurred within the transaction. You can use the same technique for updates and deletes.

Developing ColdFusion MX Applications with CFML
Introduction to Databases and SQL