MACROMEDIA COLDFUSION MX 61-DEVELOPING COLDFUSION MX, Develop Manual

Page: 415 / 860

Using SQL 415
Database design guidelines
From this basic description, the following database design rules emerge:
•
Each record should contain a unique identifier as the primary key such as an employee ID, a
part number, or a customer number. The primary key is typically the column used to maintain
each record's unique identity among the tables in a relational database. Databases allow you to
use multiple columns for the primary key.
•
When you define a column, you define a SQL data type for the column, such as allowing only
numeric values to be entered in the salary column.
•
Assessing user needs and incorporating those needs in the database design is essential to a
successful implementation. A well-designed database accommodates the changing data needs
within an organization.
The best way to familiarize yourself with the capabilities of your database product or database
management system (DBMS) is to review the product documentation.
Using SQL
This section introduces SQL, describes basic SQL syntax, and contains examples of SQL
statements. It provides enough information for you to begin using ColdFusion MX. However,
this section does not contain an exhaustive description of the entire SQL programming language.
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 allow you to 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.
Tip: If you are using Dreamweaver MX or H, 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 using an editor” on page 422 .
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