Keyword
Last updated on
Jan 13, 2022
|
Also applies to ColdFusion
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.
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:
|
|
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. |
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 |
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 in mind the following guidelines:
If you use a ColdFusion variable in your SQL expression, and the variable value is a string that contains single quotes, place the variable in a PreserveSingleQuotes function to prevent ColdFusion from interpreting the quotation marks. The following example shows this use:
<cfset List = "'Suisun', 'San Francisco', 'San Diego'"> <cfquery name = "GetCenters" datasource = "cfdocexamples"> SELECT Name, Address1, Address2, City, Phone FROM Centers WHERE City IN (#PreserveSingleQuotes(List)#) </cfquery>
- 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.
- To perform a successful query, the data source, columns, and tables that you reference must exist.
- 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 your database supports. 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 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.
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 a database table. Because 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 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
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 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 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. 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 recordset has the following structure:
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
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 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 that 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, 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-quotation marks. This is necessary because the table columns are defined to contain character strings. Numeric data does not require the quotation marks.
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.
Do 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 preceding examples 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, canceling any previous writes that occurred within the transaction. You can use the same technique for selects, updates, and deletes. The following example uses the cftransaction tag to wrap multiple SQL statements:
<cftransaction>
<cfquery name="qInsEmp" datasource="cfdocexamples">
INSERT INTO Employees (FirstName,LastName,EMail,Phone,Department)
VALUES ('Simon', 'Horwith', 'SHORWITH','(202)-797-6570','Research and Development')
</cfquery>
<cfquery name="qGetID" datasource="cfdocexamples">
SELECT MAX(Emp_ID) AS New_Employee
FROM Employees
</cfquery>
</cftransaction>