MACROMEDIA COLDFUSION MX 61-GETTING STARTED BUILDING COLDFUSION..., Начало работы, Страница: 62 / 134

62 Chapter 6: Lesson 2: Writing Your First ColdFusion Application
The result of the preceding SQL statement contains the following data:
You can compose a WHERE clause with one or more conditions; these are called subclauses . You
join subclauses using the operators AND and OR.The AND operator displays a row if ALL
conditions listed are True. The OR operator displays a row if ANY of the conditions listed are
True. An example of a WHERE clause with multiple subclauses follows:
SELECT LastName FROM Clients Where City = 'Boston' AND FirstName = 'Anita'
The result of the preceding SQL statement contains the following data:
Note: The preceding SQL SELECT examples use single quotation marks around the value. SQL
uses single quotation marks around text values. Most database systems will also accept double
quotation marks. Do not enclose numeric values in quotation marks.
Sorting the results
You use the ORDER BY clause to sort the result rows. The following SQL statement returns an
alphabetic list of people sorted by last name then first name from the Clients table:
SELECT * FROM Clients Order By LastName, FirstName
The default is to return the results in ascending order (top to bottom). If you include the DESC
keyword in the ORDER BY clause, the rows are returned in descending order (bottom to top).
The following statement returns a reverse alphabetic list of the Clients table:
SELECT * FROM Clients Order By LastName, FirstName DESC
Note: The SQL SELECT statement is quite powerful. There are several other options for retrieving
data from a SQL database using the SELECT statement, which are not described in this book. For
more information, consult a SQL reference
.
Using SQL with cfquery to dynamically retrieve information
Relational database management systems process SQL instructions sent to them from various
applications. ColdFusion sends SQL statements to database managers to manipulate data.
ColdFusion needs a way to know which database manager to send a specific SQL string for
evaluation. In CFML, the
cfquery
tag serves this purpose. You will use the SQL SELECT
statement and the
cfquery
tag to create a dynamic version of the Trip List page presented earlier
in this lesson. In this example, you use
cfquery
to return all the trip names found in the
tripName column within the Compass Travel Trips table. To use the SQL SELECT statement to
dynamically retrieve this information, you must execute the SQL SELECT statement between
the
cfquery
start and end tags as follows:
<cfquery name="TripResult" datasource= "CompassTravel">
SELECT tripName FROM trips
</cfquery>
LastName FirstName
Jones Tom
Adams Anita
LastName
Adams