Sqlite 简明教程

SQLite - INDEXED BY Clause

“INDEXED BY index-name”从句指定必须使用命名的索引才能在前面的表格上查找值。

The "INDEXED BY index-name" clause specifies that the named index must be used in order to look up values on the preceding table.

如果 index-name 不存在或无法用于查询，则 SQLite 语句的准备将失败。

If index-name does not exist or cannot be used for the query, then the preparation of the SQLite statement fails.

“NOT INDEXED”从句指定在访问前面的表格（包括 UNIQUE 和 PRIMARY KEY 约束创建的隐式索引）时不应使用任何索引。

The "NOT INDEXED" clause specifies that no index shall be used when accessing the preceding table, including implied indices created by UNIQUE and PRIMARY KEY constraints.

然而，即使指定了 “NOT INDEXED”，INTEGER PRIMARY KEY 仍可用于查找条目。

However, the INTEGER PRIMARY KEY can still be used to look up entries even when "NOT INDEXED" is specified.

Syntax

以下是 INDEXED BY 从句的语法，可用于 DELETE、UPDATE 或 SELECT 语句。

Following is the syntax for INDEXED BY clause and it can be used with DELETE, UPDATE or SELECT statement.

SELECT|DELETE|UPDATE column1, column2...
INDEXED BY (index_name)
table_name
WHERE (CONDITION);

Example

考虑表格 COMPANY ，我们将创建索引并使用它执行 INDEXED BY 操作。

Consider table COMPANY We will create an index and use it for performing INDEXED BY operation.

sqlite> CREATE INDEX salary_index ON COMPANY(salary);
sqlite>

现在，从表格 COMPANY 中选择数据，你可以按如下方式使用 INDEXED BY 从句 −

Now selecting the data from table COMPANY you can use INDEXED BY clause as follows −

sqlite> SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;

这将产生以下结果。

This will produce the following result.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
7           James       24          Houston     10000.0
2           Allen       25          Texas       15000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0