sqlite3学习之索引（Index）的使用&Indexed By子句&Alter 命令

淡淡的烟草味﹌ 2022-03-27 01:19 1495阅读 1赞

索引（Index）是一种特殊的查找表，数据库搜索引擎用来加快数据检索。简单地说，索引是一个指向表中数据的指针。一个数据库中的索引与一本书后边的索引是非常相似的。例如，如果我们想在一本讨论某个话题的书中引用所有页面，我们首先需要指向索引，索引按字母顺序列出了所有主题，然后指向一个或多个特定的页码。索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。使用 CREATE INDEX 语句创建索引，它允许命名索引，指定表及要索引的一列或多列，并指示索引是升序排列还是降序排列。索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目，来看下创建索引的语法格式：

CREATE INDEX index_name ON table_name;

单列索引是一个只基于表的一个列上创建的索引，基本语法如下：

CREATE INDEX index_name ON table_name (column_name);

使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中，基本语法如下：

CREATE UNIQUE INDEX index_name on table_name (column_name);

组合索引是基于一个表的两个或多个列上创建的索引，基本语法如下：

CREATE INDEX index_name on table_name (column1, column2);

至于我们是否要创建一个单列索引还是组合索引，要考虑到我们在作为查询过滤条件的 WHERE 子句中使用非常频繁的列，如果值使用到一个列，则选择使用单列索引，如果在作为过滤的 WHERE 子句中有两个或多个列经常使用，则选择使用组合索引。

隐式索引是在创建对象时，由数据库服务器自动创建的索引，索引自动创建为主键约束和唯一约束。

接下来，我们在 COMPANY 表的 salary 列上创建一个索引：

CREATE INDEX salary_index ON COMPANY (salary);

之后，我们就可以使用 .indices 或 .indexes 命令列出 COMPANY 表上所有可用的索引，如下：

.indices COMPANY

结果如下，其中 *sqlite\_autoindex\_COMPANY\_1* 是创建表时创建的隐式索引：

salary_index
    sqlite_autoindex_COMPANY_1

我们来尝试列出数据库范围的所有索引，如下：

SELECT * FROM sqlite_master WHERE type = 'index';

一个索引可以使用 SQLite 的 **DROP** 命令删除。当删除索引时应特别注意，因为性能可能会下降或提高，基本语法如下：

DROP INDEX index_name;

虽然索引的目的在于提高数据库的性能，但这里有几个情况需要避免使用索引。使用索引时，应重新考虑下列准则：

*  索引不应该使用在较小的表上。
 *  索引不应该使用在有频繁的大批量的更新或插入操作的表上。
 *  索引不应该使用在含有大量的 NULL 值的列上。
 *  索引不应该使用在频繁操作的列上。

"INDEXED BY index-name" 子句规定必须需要命名的索引来查找前面表中值。如果索引名 index-name 不存在或不能用于查询，然后 SQLite 语句的准备失败。"NOT INDEXED" 子句规定当访问前面的表（包括由 UNIQUE 和 PRIMARY KEY 约束创建的隐式索引）时，没有使用索引。然而，即使指定了 "NOT INDEXED"，INTEGER PRIMARY KEY 仍然可以被用于查找条目。

我们来看下 INDEXED BY 子句的语法，它可以与 DELETE、UPDATE 或 SELECT 语句一起使用：

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

假设有表 COMPANY，我们将创建一个索引，并用它进行 INDEXED BY 操作，如下：

CREATE INDEX salary_index ON COMPANY(salary);

接下来，可以使用 INDEXED BY 子句从表 COMPANY 中选择数据，如下：

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

SQLite 的 **ALTER TABLE** 命令不通过执行一个完整的转储和数据的重载来修改已有的表。您可以使用 ALTER TABLE 语句重命名表，使用 ALTER TABLE 语句还可以在已有的表中添加额外的列。在 SQLite 中，除了重命名表和在已有的表中添加列，ALTER TABLE 命令不支持其他操作，我们来看下用来重命名已有的表的 **ALTER TABLE** 的基本语法：

ALTER TABLE database_name.table_name RENAME TO new_table_name;

用来在已有的表中添加一个新的列的 **ALTER TABLE** 的基本语法：

ALTER TABLE database_name.table_name ADD COLUMN column_def...;

我们来假设 COMPANY 表有如下记录：

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

我们来尝试使用 ALTER TABLE 语句重命名该表，如下：

ALTER TABLE COMPANY RENAME TO OLD_COMPANY;

我们再来尝试在 OLD\_COMPANY 表中添加一个新的列，如下：

ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);

这里提个醒，sqlite3 并不支持直接添加带有 unique 约束的列，来看下面的例子：

sqlite> alter table company add department text unique;
    Error: Cannot add a UNIQUE column

可以先直接添加一列数据，然后再添加 unique 索引。其实在建表的时候如果列有 unique 约束，通过查询系统表 SQLITE\_MASTER 可以看到，会自动创建相应的索引。SQLITE\_TEMP\_MASTER 跟 SQLITE\_MASTER 是 sqlite 的系统表，SQLITE\_TEMP\_MASTER 表存储临时表有关的所有内容。

好啦，本次记录就到这里了。

如果感觉不错的话，请多多点赞支持哦。。。