面向交易员的 MQL5 编程指南第七章高级语言工具SQLite 数据库SQL 基础

在一个文件中下载:

SQL 基础知识

在 SQLite 中执行的所有任务都假定存在一个或多个工作数据库，因此创建和打开数据库（类似于文件）是建立必要编程环境的强制性框架操作。SQLite 中没有以编程方式删除数据库的功能，因为它假定你只需从磁盘删除数据库文件。

在打开的数据库上下文中可用的操作可以大致分为以下主要组：

创建和删除表，以及修改其模式，即列描述，包括类型、名称和限制的标识。
在表中创建（添加）、读取、编辑和删除记录；这些操作通常用通用缩写 CRUD (Create, Read, Update, Delete) 表示。
根据复杂条件构建查询以从一个或多个表的组合中选择记录。
可通过下列方式来优化算法：在选定列上构建索引、使用视图 (view)、将批量操作包装在事务中、声明事件处理触发器以及其他高级工具

在 SQL 数据库中，所有这些操作都是使用保留的 SQL 命令（或语句）执行的。由于与 MQL5 集成的特殊性，某些操作由内置的 MQL5 函数执行。例如，打开、应用或取消事务是由 DatabaseTransaction函数执行的，尽管 SQL 标准（以及 SQLite 的公共实现）具有相应的 SQL 命令（BEGIN TRANSACTION、COMMIT 和 ROLLBACK）。

大多数 SQL 命令在 MQL 程序中也可用：它们作为 DatabaseExecute 或 DatabasePrepare 函数的字符串参数传递给 SQLite 执行引擎。这两种选项之间的区别在于几个细微之处。

DatabasePrepare 允许准备一个查询，以便后续在每次迭代中使用不同的参数值进行大规模循环执行（参数本身，即它们在查询中的名称，是相同的）。此外，这些准备好的查询提供了使用 DatabaseRead 和 DatabaseReadBind读取结果的机制。因此，可以将它们用于处理一组选定记录的操作。

相比之下，DatabaseExecute 函数单方面执行传递的单个查询：命令进入 SQLite 引擎内部，对数据执行某些操作，但不返回任何内容。这通常用于创建表或批量修改数据。

将来，我们将经常需要使用几个基本概念。我们一一介绍它们：

表 – 结构化的数据集，由行和列组成。每一行都是一条单独的数据记录，其字段（特性）通过相应列的名称和类型进行描述。所有数据库表都以物理方式存储在数据库文件中，并且可供读写（如果在打开数据库时未限制权限）。

视图 – 一种虚拟表，由 SQLite 引擎根据给定的 SQL 查询、其他表或视图计算得出。视图是只读的。不同于任何表（包括 SQL 允许在程序会话期间在内存中创建的临时表），每次访问视图时，都会动态重新计算视图。

索引 – 一种服务数据结构（平衡树，B-树），用于通过预定义字段（特性）的值或其组合快速搜索记录。

触发器 – 一个或多个 SQL 语句的子程序，被指定为在特定表中添加、更改或删除记录的事件（之前或之后）发生时自动运行。

以下是最常用的 SQL 语句及其执行的操作的简短列表：

CREATE – 创建数据库对象（表、视图、索引、触发器）；
ALTER – 更改对象（表）；
DROP – 删除对象（表、视图、索引、触发器）；
SELECT – 选择满足给定条件的记录或计算值；
INSERT – 添加新数据（一条或一组记录）；
UPDATE – 更改现有记录；
DELETE – 从表中删除记录；

该列表仅显示了启动相应 SQL 语言结构的关键字。更详细的语法将在下面显示。它们的实际应用将在以下示例中展示。

每个语句可以跨越多行（换行符和多余的空格会被忽略）。如果需要，可以一次向 SQLite 发送多个命令。在这种情况下，在每个命令之后，应该使用命令终止符 ';'（分号）。

系统分析命令中的文本时不区分大小写，但在 SQL 中习惯于用大写字母书写关键字。

创建表时，我们必须指定其名称，以及列的列表，列用括号括起来并以逗号分隔。每一列都被赋予一个名称、一个类型以及可选的约束。最简单的形式：

CREATE TABLE table_name
( column_name type [ constraints ] [, column_name type [ constraints ...] ...]);

我们将在下一节中了解 SQL 中的限制。与此同时，我们来看一个清晰的示例（包含不同类型和选项）：

CREATE TABLE IF NOT EXISTS example_table
   (id INTEGER PRIMARY KEY,
    name TEXT,
    timestamp INTEGER DEFAULT CURRENT_STAMP,
    income REAL,
    data BLOB);

创建索引的语法是：

CREATE [ UNIQUE ] INDEX index_name
ON table_name( column_name [, column_name ...]);

现有索引会在对相应列具有筛选条件的查询中自动使用。没有索引，处理过程会更慢。

删除表（如果已向其中写入数据，则连同数据一起删除）非常简单：

DROP TABLE table_name;

可以如下所示向表中插入数据：

INSERT INTO table_name [ ( column_name [, column_name ...] ) ]
VALUES( value [, value ...]);

括号中的第一个列表包含列名，是可选的（见下文解释）。第一个列表必须与第二个包含这些列值的列表相匹配。例如，

INSERT INTO example_table (name, income) VALUES ('Morning Flat Breakout', 1000);

请注意，在 SQL 中，字符串字面量用单引号括起来。

如果 INSERT 语句中省略了列名，则假定 VALUES 关键字后跟的是表中所有列的值，并且严格按照它们在表中描述的顺序排列。

该运算符还有更复杂的形式，特别允许从其他表或查询结果中插入记录。

按条件选择记录，并可选地限制返回字段（列）的列表，由 SELECT 命令执行。

SELECT column_name [, column_name ...] FROM table_name [WHERE condition ];

如果你想完整地返回每个匹配的记录（所有列），请使用星号表示法：

SELECT *FROM table_name [WHERE condition ];

当条件不存在时，系统返回表中的所有记录。

作为条件，可以代入一个逻辑表达式，该表达式包含列名和各种比较运算符，以及内置的 SQL 函数和嵌套 SELECT 查询的结果（此类查询写在括号中）。比较运算符包括：

逻辑与
逻辑或
IN，列表中的某个值
NOT IN，列表外的某个值
BETWEEN，范围内的某个值
LIKE – 与带有特殊通配符（'%'、'_'）的模式在拼写上相似
EXISTS – 检查嵌套查询结果的非空性

例如，选择收入至少为 1000 且年龄不超过一年（预先四舍五入到最近的月份）的记录名称：

SELECT name FROM example_table
WHERE income >= 1000 AND timestamp > datetime('now', 'start of month', '-1 year');

此外，选择结果可以按升序或降序排序 (ORDER BY)，按特征分组 (GROUP BY)，并按组筛选 (HAVING)。我们还可以限制其中的记录数量（LIMIT、OFFSET）。对于每个组，可以返回任何聚合函数的值，特别是 COUNT、SUM、MIN、MAX 和 AVG，这些函数是在所有组成员记录上计算的。

SELECT [ DISTINCT ] column_name [, column_name...](i) FROM table_name
  [ WHERE condition ]
  [ORDER BY column_name [ ASC | DESC ]
     [ LIMIT quantity OFFSET start_offset ] ]
  [ GROUP BY column_name ⌠ HAVING condition ] ];

可选关键字 DISTINCT 可用于移除重复项（如果根据当前选择标准在结果中找到它们）。该关键字仅在没有分组的情况下才有意义。

只有在存在排序的情况下，LIMIT 才能给出可重现的结果。

如果需要，SELECT 选择可以不是从一个表而是从多个表进行，根据所需的字段组合将它们结合起来。为此使用关键字 JOIN。

SELECT [...] FROM table name_1
[ INNER | OUTER | CROSS ] JOIN table_name_2
ON boolean_condition

或

SELECT [...] FROM table name_1
[ INNER | OUTER | CROSS ] JOIN table_name_2
USING ( common_column_name [, common_column_name ...] )

SQLite 支持三种 JOIN：INNER JOIN、OUTER JOIN 和 CROSS JOIN。本书通过示例对其进行了概要介绍，你可以自行进一步探索细节。

例如，使用 JOIN，你可以构建一个表中的记录与另一个表中的记录的所有组合，或者根据匹配仓位标识符的原则比较交易表（我们称之为 "deals"）中的交易与同一表中的交易，但要使交易的方向（入市/出市）相反，从而产生一个虚拟的交易表。

SELECT // list the columns of the results table with aliases (after 'as')
  d1.time as time_in, d1.position_id as position, d1.type as type, // table d1
   d1.volume as volume, d1.symbol as symbol, d1.price as price_in,
  d2.time as time_out, d2.price as price_out,                      // table d2
   d2.swap as swap, d2.profit as profit,
  d1.commission + d2.commission as commission                      // combination
  FROM deals d1 INNER JOIN deals d2      // d1 and d2 - aliases of one table "deals"
  ON d1.position_id = d2.position_id     // merge condition by position
  WHERE d1.entry = 0 AND d2.entry = 1    // selection condition "entry/exit"

这是 MQL5 帮助中的一个 SQL 查询，在 DatabaseExecute 和 DatabasePrepare 函数的描述中可以找到 JOIN 的示例。

SELECT 的一个基本特性是它总是向调用程序返回结果，这与其他查询（如 CREATE、INSERT 等）不同。然而，从 SQLite 3.35 开始，INSERT、UPDATE 和 DELETE 语句也可视需要使用附加的 RETURNING 关键字返回值。例如，

INSERT INTO example_table (name, income) VALUES ('Morning Flat Breakout', 1000)
RETURNING id;

在任何情况下，MQL5 中的查询结果都是通过 DatabaseColumn函数、 DatabaseRead和 DatabaseReadBind访问的。

此外，SELECT 允许对表达式的结果求值，并按原样返回它们，或将它们与表中的结果组合起来。表达式可以包含我们从 MQL5 表达式中学到的大多数运算符，以及内置的 SQL 函数。请参阅 SQLite 文档以获取完整列表。例如，下面是如何在终端和编辑器实例中查找 SQLite 当前的构建版本，这对于了解哪些选项可用可能很重要。

SELECT sqlite_version();

这里，整个表达式由对 sqlite_version 函数的单个调用组成。与从表中选择多个列类似，你可以评估用逗号分隔的多个表达式。

还提供了一些常用的统计和数学函数。

应使用 UPDATE 语句编辑记录。

UPDATE table_name SET column_name = value [, column_name = value ...]
WHERE condition;

删除命令的语法如下：

DELETE FROM table_name WHERE condition;

MQL5 中的数据库操作原理

表结构：数据类型和限制