The following information introduces SQL, describes basic SQL syntax, and contains examples of SQL statements. so that you can begin to use ColdFusion. 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.

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 let you 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 columns.

If you are using Dreamweaver MX 2004, Adobe Dreamweaver CS3, 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 by 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 tables briefly describe the main SQL command elements.

Statements

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

Statement clauses

Use the following keywords to refine SQL statements:

Operators

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

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">
<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 in 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.