In between the begin and end CFQUERY tags, write the SQL that you want the database to execute.
For example, to retrieve data from a database:
Tip | If you are using ColdFusion Studio, you can use the Query Builder to build SQL statements by graphically selecting the tables, and records within those tables you want to retrieve. See Using ColdFusion Studio for details. |
When the database processes the SQL, it creates a data set that is returned to ColdFusion Server. ColdFusion places the data set in memory and assigns it the name that you defined for the query in the begin CFQUERY tag.
You may reference that data set by name using the CFOUTPUT tag further down on the page.
The following sections present brief descriptions of the main SQL command elements.
These keywords identify commonly-used SQL commands:
Basic SQL Statements | |
---|---|
Keyword | Description |
SELECT | Retrieves the specified records |
INSERT | Adds a new row |
UPDATE | Changes values in the specified rows |
DELETE | Removes the specified rows |
These keywords are used to refine SQL statements:
Basic SQL Clauses | |
---|---|
Keyword | Description |
FROM | Names the data source 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. |
These specify conditions and perform logical and numeric functions:
Basic SQL Operators | |
---|---|
Operator | Description |
AND | Both conditions must be met, such as Paris AND Texas |
OR | At least one condition must be met, such as Smith OR Smyth |
NOT | Exclude the condition following, such as Paris NOT France |
= | Equal to |
<> | Not equal to |
< | Less than |
> | Greater than |
<= | Less than or equal to |
>= | Greater than or equal to |
+ | Addition |
- | Subtraction |
/ | Division |
* | Multiplication |
Keep the following in mind when writing SQL in ColdFusion: