Delphi中SQL

「爱情、让人受尽委屈。」 2022-12-06 15:45 157阅读 0赞

SQL (Structured Query Language) is a standardized language for defining and manipulating data in a relational database. In accordance with the relational model of data, the database is perceived as a set of tables, relationships are represented by values in tables, and data is retrieved by specifying a result table that can be derived from one or more base tables. Queries take the form of a command language that lets you *select, insert, update, find* out the location of data, and so forth.

SQL(结构化查询语言)是一种用于定义和处理关系数据库中数据的标准化语言。 根据数据的关系模型，数据库被视为一组表，关系由表中的值表示，并且通过指定可以从一个或多个基本表派生的结果表来检索数据。 查询采用命令语言的形式，可让您*选择，插入，更新，找出*数据的位置等。

## 在Delphi中：TQuery **(** In Delphi: TQuery **)** ##

If you are going to use SQL in your applications, you will become very familiar with the *TQuery* component. Delphi enables your applications to use SQL syntax directly though TQuery component to access data from Paradox and dBase tables (using local SQL - subset of ANSI standard SQL), Databases on the Local InterBase Server, and Databases on remote database servers. Delphi also supports heterogeneous queries against more than one server or table type (for example, data from an Oracle table and a Paradox table).TQuery has a property called *SQL*, which is used to store the SQL statement.

如果要在应用程序中使用SQL，您将非常熟悉*TQuery*组件。 Delphi使您的应用程序可以通过TQuery组件直接使用SQL语法来访问Paradox和dBase表中的数据(使用本地SQL-ANSI标准SQL的子集)，本地InterBase服务器上的数据库以及远程数据库服务器上的数据库。 Delphi还支持针对多个服务器或表类型的异构查询(例如，来自Oracle表和Paradox表的数据)。TQuery具有称为*SQL*的属性，该属性用于存储SQL语句。

TQuery encapsulates one or more SQL statements, executes them and provides methods by which we can manipulate the results. Queries can be divided into two categories: those that produce result sets (such as a *SELECT* statement), and those that don't (such as an *UPDATE*or *INSERT* statement). Use TQuery.Open to execute a query that produces a result set; use TQuery.ExecSQL to execute queries that do not produce result sets.

TQuery封装了一个或多个SQL语句，执行它们并提供了我们可以操纵结果的方法。 查询可以分为两类：产生结果集的查询(例如*SELECT*语句)和不产生结果集的查询(例如*UPDATE*或*INSERT*语句)。 使用TQuery.Open执行产生结果集的查询； 使用TQuery.ExecSQL执行不会产生结果集的查询。

The SQL statements can be either *static* or *dynamic*, that is, they can be set at design time or include parameters (*TQuery.Params*) that vary at run time. Using parameterized queries is very flexible because you can change a user's view of and access to data on the fly at run time.

SQL语句可以是*静态的* ，也可以是*动态的* ，即可以在设计时设置它们，也可以包含在运行时变化的参数( *TQuery.Params* )。 使用参数化查询非常灵活，因为您可以在运行时动态更改用户对数据的视图和访问。

All executable SQL statements must be prepared before they can be executed. The result of preparation is the executable or operational form of the statement. The method of preparing an SQL statement and the persistence of its operational form distinguish static SQL from dynamic SQL. At design time a query is prepared and executed automatically when you set the [query][] component's Active property to True. At run time, a query is prepared with a call to Prepare and executed when the application calls the component's Open or ExecSQL methods.

必须先准备所有可执行SQL语句，然后才能执行它们。 准备的结果是语句的可执行形式或操作形式。 准备SQL语句的方法及其操作形式的持久性将静态SQL与动态SQL区别开来。 在设计时，如果将[查询][query]组件的Active属性设置为True，则[查询][query]将自动准备并执行。 在运行时，通过调用Prepare准备查询，并在应用程序调用组件的Open或ExecSQL方法时执行查询。

A TQuery can return two kinds of result sets: "*live*" as with TTable component (users can edit data with data controls, and when a call to Post occurs changes are sent to the database), "*read-only*" for display purposes only. To request a live result set, set a query component's RequestLive property to True, and be aware that SQL statement must meet some specific requirements (no ORDER BY, SUM, AVG, etc.)

TQuery可以返回两种结果集：与TTable组件一样，“ *实时* ”(用户可以使用数据控件编辑数据，并且在发生对Post的调用时将更改发送到数据库)，“ *只读* ” *仅*用于显示目的。 若要请求实时结果集，请将查询组件的RequestLive属性设置为True，并请注意SQL语句必须满足某些特定要求(没有ORDER BY，SUM，AVG等)。

A query behaves in many ways very much like a table filter, and in some ways, a query is even more powerful than a filter because it lets you access:

查询的行为在很多方面都非常类似于表过滤器，并且在某些方面，查询比过滤器功能更强大，因为它可以访问：

*  more than one table at a time ("join" in SQL)
    
    一次一个以上的表(SQL中的“ join”)
 *  a specified subset of rows and columns from its underlying table(s), rather than always returning all of them
    
    来自其基础表的行和列的指定子集，而不是总是返回所有子集

## 简单的例子 **(** Simple Example **)** ##

Now let's see some SQL in action. Although we could use the Database Form Wizard to create some SQL examples for this example we will do it manually, step by step:

现在，让我们来看一些实际SQL。 尽管我们可以使用数据库表单向导为该示例创建一些SQL示例，但我们将逐步手动进行操作：

1. Place a TQuery, TDataSource, TDBGrid, TEdit, and a TButton component on the main form. 2. Set TDataSource component's DataSet property to Query1. 3. Set TDBGrid component's DataSource property to DataSource1. 4. Set TQuery component's DatabaseName property to DBDEMOS. 5. Double-click on SQL property of a TQuery to assign the SQL statement to it.6. To make the grid display data at design time, change TQuery component's Active property to True.The grid displays data from Employee.db table in three columns (FirstName, LastName, Salary) even if Employee.db has 7 fields, and the result set is restricted to those records where the FirstName begins with 'R'.

1.在主窗体上放置一个TQuery，TDataSource，TDBGrid，TEdit和TButton组件。 2.将TDataSource组件的DataSet属性设置为Query1。 3.将TDBGrid组件的DataSource属性设置为DataSource1。 4.将TQuery组件的DatabaseName属性设置为DBDEMOS。 5.双击TQuerySQL属性以为其分配SQL语句。6。 若要使网格在设计时显示数据，请将TQuery组件的Active属性更改为True。即使Employee.db具有7个字段，网格也会在三列(FirstName，LastName，Salary)中显示Employee.db表中的数据，并显示结果集仅限于名字以“ R”开头的记录。

7. Now assign the following code to the OnClick event of the Button1.

7.现在，将以下代码分配给Button1的OnClick事件。

procedure TForm1.Button1Click(Sender: TObject);
    begin
    Query1.Close;{close the query}
    //assign new SQL expression
    Query1.SQL.Clear;
    Query1.SQL.Add ('Select EmpNo, FirstName, LastName');
    Query1.SQL.Add ('FROM Employee.db');
    Query1.SQL.Add ('WHERE Salary > ' + Edit1.Text);
    Query1.RequestLive := true;
    Query1.Open; {open query + display data}
    end;

8. Run your application. When you click on the Button (as long as Edit 1 has a valid currency value in it), the grid will display the EmpNo, FirstName and LastName fields for all records where Salary is greater than the specified currency value.

8.运行您的应用程序。 当您单击按钮时(只要Edit 1中具有有效的货币值)，网格将为Salary大于指定货币值的所有记录显示EmpNo，FirstName和LastName字段。

In this example, we created a simple static SQL statement with live result set (we haven't changed any of displayed records) just for displaying purposes.

在此示例中，我们创建了一个简单的带有活动结果集的静态SQL语句(我们没有更改任何显示的记录)，仅用于显示目的。

> 翻译自: [https://www.thoughtco.com/sql-in-delphi-4092571][https_www.thoughtco.com_sql-in-delphi-4092571]

[query]: https://www.lifewire.com/query-definition-1019180
[https_www.thoughtco.com_sql-in-delphi-4092571]: https://www.thoughtco.com/sql-in-delphi-4092571