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.

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')