coldfusion_ColdFusion教程第三部分

coldfusion

介绍： (Introduction:)

Welcome to the final chapter of this article. If you have come this far you should have a firm grasp of how to use ColdFusion to make a dynamic site. In this part I will cover code you would find inside the <CFQUERY> tags, and more advanced SQL.

欢迎来到本文的最后一章。 如果您走了这么远，那么您应该对如何使用ColdFusion制作动态站点有一定的了解。 在这一部分中，我将介绍<CFQUERY>标记内的代码以及更高级SQL。

1.添加数据 (1. Adding Data)

To add data to your database you will use the INSERT SQL statement.

要将数据添加到数据库，您将使用INSERT SQL语句。

Figure 1. The INSERT Statement

INSERT INTO Friends(Name, Sex, FavMovie)

VALUES (Chris, Male, Braveheart)

How it works is you specify the table you wish to insert into, in our case "Friends", and then the columns in parenthesis. On the next line you specify the values in much the same way. The order the values are listed must match with the order the columns are listed. You can also make this insert statement dynamic.

它的工作方式是指定要插入的表(在本例中为“ Friends”)，然后指定括号中的列。 在下一行中，您将以几乎相同的方式指定值。 列出值的顺序必须与列出列的顺序相匹配。 您还可以使此插入语句动态化。

Figure 2. A Dynamic INSERT Statement

INSERT INTO Friends(Name, Sex, FavMovie)

VALUES (#name#, #sex#, #movie#)

As was shown in the first part of this article, any aspect of the SQL Statement can be replaced with form input, this holds for the INSERT statement. Most database driven websites have an admin section using dynamic statements like this to add new content.

如本文第一部分所示，SQL语句的任何方面都可以由表单输入替换，这适用于INSERT语句。 大多数数据库驱动的网站都有一个admin部分，使用这样的动态语句来添加新内容。

2.更改数据 (2. Changing Data)

To modify existing data you must use the UPDATE statement.

要修改现有数据，必须使用UPDATE语句。

Figure 3. The UPDATE Statement

UPDATE Friends

SET Age = '21'

WHERE Name like "Chris"

How this works is you first specify the table which you want to update, in our case "Friends", Then you pick the value using the SET keyword. If you wanted to update multiple values you would separate each by a comma, for instance SET Age = ’21’, FavMovie = ‘The Patriot’. Finally we include the where clause, without it all rows would be updated. The UPDATE statement is very useful, for instance in our friends database we would use it to change someone’s age when they have a birthday.

工作原理是，首先指定要更新的表，在我们的示例中为“ Friends”，然后使用SET关键字选择值。 如果要更新多个值，则可以用逗号分隔每个值，例如SET Age ='21'，FavMovie ='The Patriot'。 最后，我们包含where子句，如果没有子句，则所有行都会被更新。 UPDATE语句非常有用，例如，在我们的朋友数据库中，我们将使用它来更改某人的生日时的年龄。

3.删除数据 (3. Deleting Data)

You should always be very careful when deleting data. A slip of the mind or a typo and you could have an empty database.

删除数据时应始终非常小心。 疏忽大意或打错字，您可能会有一个空的数据库。

Figure 4. The DELETE Statement

DELETE FROM Friends

WHERE Name = "Tim"

Lets say you had a falling out with a friend. You could delete their name from your friends database by using the above statement. Again the first thing specified is the table name followed by an optional WHERE statement. However remember that though the where statement is optional, without it everything in the table would be erased.

可以说您和一位朋友闹翻了。 您可以使用上面的语句从朋友数据库中删除他们的名字。 再次指定的第一件事是表名，后跟可选的WHERE语句。 但是请记住，尽管where语句是可选的，但如果没有它，表中的所有内容都会被删除。

4.不同的关键字 (4. The Distinct Keyword)

Sometimes when working with a database you do not want to return duplicate values. For instance when working with a Used Car database I wanted to dynamically generate a select form for vehicle make on the website. If I hadn’t used the distinct keyword the select list would have been very long, it would have had 1 option for every car in the database. Using the distinct keyword in my SELECT statement limited the output to every distinct make within the database.

有时，在使用数据库时，您不想返回重复的值。 例如，当使用“二手车”数据库时，我想动态生成网站上车辆制造商的选择表。 如果我没有使用“ distinct”关键字，那么选择列表将非常长，它将为数据库中的每辆车提供1个选项。 在我的SELECT语句中使用distinct关键字将输出限制为数据库中的每个独特make。

Figure 5. The DISTINCT Keyword

Select DISTINCT Make

FROM Used

As you can see it looks just like a regular SELECT statement, just with the DISTINCT keyword thrown in to eliminate duplicate values.

如您所见，它看起来就像常规的SELECT语句，只是加上DISTINCT关键字以消除重复的值。

5.列别名 (5. Column Aliases)

There may come a time when you wish to use aliases when referring to your columns. An alias is just a name you assign to a column to make it easier to refer to, it in no way changes your database, it only changes how the CF interpreter sees the column.

有时您希望在引用列时使用别名。 别名只是您分配给列以使其易于引用的名称，它绝不会更改数据库，只会更改CF解释器查看列的方式。

Figure 6. Aliases

<CFQUERY DATASOURCE = "Friends" name = "Query1">

Select DISTINCT FavMovie as Movie

From Friends

</CFQUERY>

.

.

.

<CFOUTPUT QUERY = "Query1">

#Movie#

</CFOUTPUT>

What the above would do is return a distinct list of your friends favorite movies. The only difference it has with a normal select statement is that within the page we can refer to the FavMovie column as Movie since we established the alias.

上面的操作将返回您朋友最喜欢的电影的不同列表。 它与普通的select语句唯一的区别在于，自从我们建立别名以来，在页面内我们可以将FavMovie列称为Movie。

6.汇总功能 (6. Aggregate Functions)

Aggregate Functions are used to summarise the results from your queries. The most common use for this is to display the number of results returned from a search. To do this you must use the COUNT function.

聚合函数用于汇总查询结果。 最常见的用途是显示搜索返回的结果数。 为此，您必须使用COUNT函数。

Figure 7. Count Function

Select COUNT(*) as Friends

From Friends 

The above will return the number of friends you have. Notice the asterisk in the SQL, this is used because we want to simply count the total number of records. The asterisk can be replaced with a column name to just count the number of records in a particular column.

以上将返回您拥有的朋友数量。 注意SQL中的星号，之所以使用，是因为我们只想计算记录的总数。 星号可以替换为列名，以仅计算特定列中的记录数。

There are more aggregate functions of course, they are all used in the same way, and are listed below:

当然，还有更多的聚合函数，它们都以相同的方式使用，并在下面列出：

Count() – Counts the number of results. Sum() – Calculates the total of values returned. Avg() – Calculates the average of values returned. Min() – Calculates the smallest value, earliest date, or first entry alphabetically. Max() – Calculates the largest value, latest date, or last entry alphabetically.

Count()–计算结果数。 Sum()–计算返回的总值。 Avg()–计算返回值的平均值。 Min()–按字母顺序计算最小值，最早的日期或第一次输入。 Max()–按字母顺序计算最大值，最新日期或最后输入。

7.排序结果 (7. Sorting Results)

When you run a select statement the results will be returned in the order they were entered into the table. This isn’t always what you want, sometimes you may want to order the results by a set of criteria. To do this you use the Order by clause.

当您运行select语句时，结果将按照输入表的顺序返回。 这并不总是您想要的，有时您可能希望按照一组条件对结果进行排序。 为此，您可以使用Order by子句。

Figure 8. Ordering Results

Select Firstname, Lastname

From Friends

Order by Lastname

What the above will do is return a list of your friends name’s ordered by their last name alphabetically. If you wanted to start with the Z’s and work your way to the A’s you would change it to read Order by Lastname DESC. DESC is short for descending and will reverse the order in which the records are listed.

上面的操作是返回您的朋友姓名的列表，按其姓氏的字母顺序排列。 如果要以Z开头并以A开头的方式，则将其更改为“按姓氏顺序排序”。 DESC是降序的缩写，它将颠倒记录的列出顺序。

But what if you want to order by more than one column? Lets assume you have a friend named John Smith and a friend named Peter Smith. They will be listed in the right place by their last name but whether or not John is above Peter is dependent on the order you entered them into the database.

但是，如果您要订购多于一列的商品怎么办？ 假设您有一个名为John Smith的朋友和一个名为Peter Smith的朋友。 它们的姓氏将在正确的位置列出，但是John是否高于Peter取决于您将其输入数据库的顺序。

To fix this you simply pass two column names in the Order clause.

要解决此问题，您只需在Order子句中传递两个列名。

Figure 9. Ordering by Two Columns

Select Firstname, Lastname

From Friends

Order by Lastname, Firstname

8.极限结果 (8. Limiting Results)

Often times it is desirable to only display 10 records at a time, for instance most search engines work this way. To do this you add a few arguments to your query tag.

通常，希望一次只显示10条记录，例如大多数搜索引擎都以此方式工作。 为此，您可以在查询标记中添加一些参数。

Figure 10. Limiting Results

<CFQUERY DATASOURCE = "Friends" Name = "Query1" Maxrows = "10" Startrow = "0">

..

..

..

</CFQUERY>

Note that the starting row is set at 0. Remember that computers start numbering at 0, not 1. So the first record in the database is record number 0.

请注意，起始行设置为0。请记住，计算机的起始编号为0，而不是1。因此，数据库中的第一条记录是记录号0。

结论： (Conclusion:)

If you’ve made it this far then you should be able to make a database driven website using ColdFusion quite easily. I have covered what is needed to achieve the most commonly desired results of a database driven website, but I have only scratched the surface. There is a lot more to ColdFusion and relational databases that I couldn’t cover in a short tutorial. If you would like to learn more I suggest ColdFusion Web Application Construction Kit 4.0 by Ben Forta. It is without a doubt the most comprehensive book I own and it should be able to assist you in all your ColdFusion endeavors.

如果到目前为止，您应该可以很容易地使用ColdFusion创建数据库驱动的网站。 我已经介绍了实现数据库驱动的网站的最常见期望结果所需要的内容，但是我只是从头开始。 ColdFusion和关系数据库还有很多我在简短的教程中无法涵盖的内容。 如果您想了解更多信息，建议使用Ben Forta的ColdFusion Web应用程序构建工具包4.0。 毫无疑问，这是我拥有的最全面的书，它应该能够帮助您进行ColdFusion的所有工作。

翻译自: https://www.sitepoint.com/cold-fusion-tutorial-iii/

coldfusion