delphi 7 ado_在ADO中使用Delphi查询
delphi 7 ado
The TADOQuery component provides Delphi developers the ability to fetch data from one or multiple tables from an ADO database using SQL.
TADOQuery组件使Delphi开发人员能够使用SQL从ADO数据库的一个或多个表中获取数据。
These SQL statements can either be DDL (Data Definition Language) statements such as CREATE TABLE, ALTER INDEX, and so forth, or they can be DML (Data Manipulation Language) statements, such as SELECT, UPDATE, and DELETE. The most common statement, however, is the SELECT statement, which produces a view similar to that available using a Table component.
这些SQL语句可以是DDL(数据定义语言)语句，例如CREATE TABLE，ALTER INDEX等，也可以是DML(数据操作语言)语句，例如SELECT，UPDATE和DELETE。 但是，最常见的语句是SELECT语句，它产生的视图类似于使用Table组件可获得的视图。
Note: even though executing commands using the ADOQuery component is possible, the ADOCommandcomponent is more appropriate for this purpose. It is most often used to execute DDL commands or to execute a stored procedure (even though you should use theTADOStoredProc for such tasks) that does not return a result set.
注意：尽管可以使用ADOQuery组件执行命令，但ADOCommand组件更适合于此目的。 它最常用于执行DDL命令或执行不返回结果集的存储过程(即使您应将TADOStoredProc用于此类任务)。
The SQL used in a ADOQuery component must be acceptable to the ADO driver in use. In other words you should be familiar with the SQL writing differences between, for example, MS Access and MS SQL.
ADOQuery组件中使用SQL必须对所使用的ADO驱动程序可接受。 换句话说，您应该熟悉MS Access和MS SQL之间SQL编写差异。
As when working with the ADOTable component, the data in a database is accessed using a data store connection established by the ADOQuery component using itsConnectionString property or through a separate ADOConnection component specified in the Connectionproperty.
如同ADOTable组件工作时，在一个数据库中的数据是使用通过使用其ConnectionString属性的ADOQuery组分或通过在连接属性中指定的单独的ADOConnection分量建立的数据存储连接访问。
To make a Delphi form capable of retrieving the data from an Access database with the ADOQuery component simply drop all the related data-access and data-aware components on it and make a link as described in the previous chapters of this course. The data-access components: DataSource, ADOConnection along with ADOQuery (instead of the ADOTable) and one data-aware component like DBGrid is all we need.As already explained, by using the Object Inspector set the link between those components as follows:
为了使Delphi表单能够使用ADOQuery组件从Access数据库中检索数据，只需将其上所有相关的数据访问和数据感知组件放下并建立一个链接，如本课程前面的章节所述。 我们只需要数据访问组件：DataSource，ADOConnection和ADOQuery(而不是ADOTable)以及一个数据感知组件(如DBGrid)，正如已经说明的那样，通过使用对象检查器设置这些组件之间的链接如下：
DBGrid1.DataSource = DataSource1
DataSource1.DataSet = ADOQuery1
ADOQuery1.Connection = ADOConnection1
//build the ConnectionString
ADOConnection1.ConnectionString = ...
ADOConnection1.LoginPrompt = False进行SQL查询 ( Doing a SQL query )
The TADOQuery component doesn't have a TableNameproperty as the TADOTable does. TADOQuery has a property (TStrings) called SQL which is used to store the SQL statement. You can set the SQL property's value with the Object Inspector at design time or through code at runtime.
TADOQuery组件不像TADOTable那样具有TableName属性。 TADOQuery具有一个称为SQL的属性(TStrings)，用于存储SQL语句。 您可以在设计时使用对象检查器或在运行时通过代码设置SQL属性的值。
At design-time, invoke the property editor for the SQL property by clicking the ellipsis button in the Object Inspector. Type the following SQL statement: "SELECT * FROM Authors".
在设计时，通过单击对象检查器中的省略号按钮来调用SQL属性的属性编辑器。 键入以下SQL语句：“ SELECT * FROM Authors”。
The SQL statement can be executed in one of two ways, depending on the type of the statement. The Data Definition Language statements are generally executed with the ExecSQL method. For example to delete a specific record from a specific table you could write a DELETE DDL statement and run the query with the ExecSQL method.The (ordinary) SQL statements are executed by setting the TADOQuery.Active property to True or by calling theOpen method (essentialy the same). This approach is similar to retrieving a table data with the TADOTable component.
取决于语句的类型，可以通过两种方式之一执行SQL语句。 数据定义语言语句通常使用ExecSQL方法执行。 例如，要从特定表中删除特定记录，您可以编写DELETE DDL语句并使用ExecSQL方法运行查询。(常规)SQL语句通过将TADOQuery.Active属性设置为True或调用Open方法来执行。 (本质上相同)。 这种方法类似于使用TADOTable组件检索表数据。
At run-time, the SQL statement in the SQL property can be used as any StringList object:
在运行时，SQL属性中SQL语句可以用作任何StringList对象：
with ADOQuery1 do begin Close;
SQL.Clear;
SQL.Add:='SELECT * FROM Authors ' SQL.Add:='ORDER BY authorname DESC' Open;
end;The above code, at run-time, closes the dataset, empties the SQL string in the SQL property, assigns a new SQL command and activates the dataset by calling the Open method.
上面的代码在运行时关闭数据集，清空SQL属性中SQL字符串，分配新SQL命令并通过调用Open方法激活数据集。
Note that obviously creating a persistent list of field objects for an ADOQuery component does not make sense. The next time you call the Open method the SQL can be so different that the whole set of filed names (and types) may change. Of course, this is not the case if we are using ADOQuery to fetch the rows from just one table with the constant set of fields - and the resulting set depends on the WHERE part of the SQL statement.
注意，显然，为ADOQuery组件创建字段对象的持久列表是没有意义的。 下次调用Open方法时，SQL可能会如此不同，以致整个文件名(和类型)的集合可能会更改。 当然，如果我们使用ADOQuery从具有恒定字段集的一个表中获取行，则情况并非如此-结果集取决于SQL语句的WHERE部分。
动态查询 ( Dynamic Queries )
One of the great properties of the TADOQuery components is the Params property. A parameterized query is one that permits flexible row/column selection using a parameter in the WHERE clause of a SQL statement. The Params property allows replacable parameters in the predefined SQL statement. A parameter is a placeholder for a value in the WHERE clause, defined just before the query is opened. To specify a parameter in a query, use a colon (:) preceding a parameter name.At design-time use the Object Inspector to set the SQL property as follows:
TADOQuery组件的出色属性之一是Params属性。 参数化查询是一种允许使用SQL语句的WHERE子句中的参数灵活选择行/列的查询。 Params属性允许在预定义SQL语句中替换参数。 参数是WHERE子句中值的占位符，该子句是在打开查询之前定义的。 要在查询中指定参数，请在参数名称前使用冒号(:)。在设计时，使用对象检查器设置SQL属性，如下所示：
ADOQuery1.SQL := ' SELECT * FROM Applications WHERE type = :apptype'When you close the SQL editor window open the Parameters window by clicking the ellipsis button in the Object Inspector.
当关闭SQL编辑器窗口时，通过单击对象检查器中的省略号按钮打开“参数”窗口。
The parameter in the preceding SQL statement is namedapptype. We can set the values of the parameters in the Params collection at design time via the Parameters dialog box, but most of the time we will be changing the parameters at runtime. The Parameters dialog can be used to specify the datatypes and default values of parameters used in a query.
前面SQL语句中的参数名为apptype 。 我们可以在设计时通过“参数”对话框设置Params集合中参数的值，但是大多数时候我们将在运行时更改参数。 “参数”对话框可用于指定查询中使用的参数的数据类型和默认值。
At run-time, the parameters can be changed and the query re-executed to refresh the data. In order to execute a parameterized query, it is necessary to supply a value for each parameter prior to the execution of the query. To modify the parameter value, we use either the Params property or ParamByName method. For example, given the SQL statement as above, at run-time we could use the following code:
在运行时，可以更改参数，并重新执行查询以刷新数据。 为了执行参数化查询，必须在执行查询之前为每个参数提供一个值。 要修改参数值，我们可以使用Params属性或ParamByName方法。 例如，给定上述SQL语句，在运行时我们可以使用以下代码：
with ADOQuery1 do begin
Close;
SQL.Clear;
SQL.Add('SELECT * FROM Applications WHERE type =:apptype');
ParamByName('apptype').Value:='multimedia';
Open;
end;As like when working with the ADOTable component the ADOQuery returns a set or records from a table (or two or more). Navigating through a dataset is done with the same set of methods as described in the "Behind data in datasets" chapter.
就像使用ADOTable组件时一样，ADOQuery从一个表(或两个或多个)中返回一个集合或记录。 使用“在数据集中的数据后面”一章中描述的相同方法集浏览数据集。
导航和编辑查询 ( Navigating and Editing the Query )
In general ADOQuery component should not be used when editing takes place. The SQL based queries are mostly used for reporting purposes. If your query returns a result set, it is sometimes possible to edit the returned dataset. The result set must contain records from a single table and it must not use any SQL aggregate functions. Editing of a dataset returned by the ADOQuery is the same as editing the ADOTAble's dataset.
通常，进行编辑时不应使用ADOQuery组件。 基于SQL的查询主要用于报告目的。 如果查询返回结果集，则有时可以编辑返回的数据集。 结果集必须包含单个表中的记录，并且不得使用任何SQL聚合函数。 ADOQuery返回的数据集的编辑与ADOTAble数据集的编辑相同。
例 ( Example )
To see some ADOQuery action we'll code a small example. Let's make a query that can be used to fetch the rows from various tables in a database. To show the list of all the tables in a database we can use the GetTableNamesmethod of the ADOConnection component. The GetTableNames in the OnCreate event of the form fills the ComboBox with the table names and the Button is used to close the query and to recreate it to retrieve the records from a picked table. The () event handlers should look like:
为了看到一些ADOQuery动作，我们将编写一个小示例。 让我们进行一个查询，该查询可用于从数据库中的各个表中获取行。 要显示数据库中所有表的列表，我们可以使用ADOConnection组件的GetTableNames方法。 表单的OnCreate事件中的GetTableNames将表名称填充到ComboBox中，并且Button用于关闭查询并重新创建它以从选取的表中检索记录。 ()事件处理程序应如下所示：
procedure TForm1.FormCreate(Sender: TObject);
begin
ADOConnection1.GetTableNames(ComboBox1.Items);
end;
procedure TForm1.Button1Click(Sender: TObject);
var tblname : string;
begin
if ComboBox1.ItemIndex then Exit;
tblname := ComboBox1.Items[ComboBox1.ItemIndex];
with ADOQuery1 do begin
Close;
SQL.Text := 'SELECT * FROM ' + tblname;
Open;
end;
end;Note that all this can be done by using the ADOTable and its TableName property.
请注意，所有这些都可以通过使用ADOTable及其TableName属性来完成。
翻译自: https://www.thoughtco.com/queries-with-ado-db-7-4092570
delphi 7 ado