SQLite学习手册

SQLite学习手册 by @_Nicky SQLite学习手册 内容收集自网络 整理: zhoushuangsheng@gmail.com 新浪微博:@_Nicky 开篇 一、简介： SQLite是目前最流行的开源嵌入式数据库，和很多其他嵌入式存储引擎相比(NoSQL)，如 BerkeleyDB、MemBASE等，SQLite可以很好的支持关系型数据库所具备的一些基本特征， 如标准SQL语法、事务、数据 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 和索引等。事实上，尽管SQLite拥有诸多关系型数据库的基 本特征，然而由于应用场景的不同，它们之间并没有更多的可比性。 下面我们将列举一下SQLite的主要特征： 1. 管理简单，甚至可以认为无需管理。 2. 操作方便，SQLite生成的数据库文件可以在各个平台无缝移植。 3. 可以非常方便的以多种形式嵌入到其他应用程序中，如静态库、动态库等。 4. 易于维护。 综上所述，SQLite的主要优势在于灵巧、快速和可靠性高。SQLite的 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 者们为了达到这一 目标，在功能上作出了很多关键性的取舍，与此同时，也失去了一些对RDBMS关键性功能的 支持，如高并发、细粒度访问控制(如行级锁)、丰富的内置函数、存储过程和复杂的SQL语句 等。正是因为这些功能的牺牲才换来了简单，而简单又换来了高效性和高可靠性。 二、SQLite的主要优点： 1. 一致性的文件格式： 在SQLite的官方文档中是这样解释的，我们不要将SQLite与Oracle或PostgreSQL去比较，而 是应该将它看做fopen和fwrite。与我们自定义格式的数据文件相比，SQLite不仅提供了很好的 移植性，如大端小端、32/64位等平台相关问题，而且还提供了数据访问的高效性，如基于某 些信息建立索引，从而提高访问或排序该类数据的性能，SQLite提供的事务功能，也是在操 作普通文件时无法有效保证的。 2. 在嵌入式或移动设备上的应用： 由于SQLite在运行时占用的资源较少，而且无需任何管理开销，因此对于PDA、智能手机等 移动设备来说，SQLite的优势毋庸置疑。 1 SQLite学习手册 by @_Nicky 3. 内部数据库： 在有些应用场景中，我们需要为插入到数据库服务器中的数据进行数据过滤或数据清理，以保 证最终插入到数据库服务器中的数据有效性。有的时候，数据是否有效，不能通过单一一条记 录来进行判断，而是需要和之前一小段时间的历史数据进行特殊的计算，再通过计算的结果判 断当前的数据是否合法。在这种应用中，我们可以用SQLite缓冲这部分历史数据。还有一种 简单的场景也适用于SQLite，即统计数据的预计算。比如我们正在运行数据实时采集的服务 程序，我们可能需要将每10秒的数据汇总后，形成每小时的统计数据，该统计数据可以极大 的减少用户查询时的数据量，从而大幅提高前端程序的查询效率。在这种应用中，我们可以将 1小时内的采集数据均缓存在SQLite中，在达到整点时，计算缓存数据后清空该数据。 4. 数据 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 ： 可以充分利用SQLite提供SQL特征，完成简单的数据统计分析的功能。这一点是CSV文件无 法比拟的。 5. 产品Demo和测试： 在需要给客户进行Demo时，可以使用SQLite作为我们的后台数据库，和其他关系型数据库相 比，使用SQLite减少了大量的系统部署时间。对于产品的功能性测试而言，SQLite也可以起 到相同的作用。 三、和RDBMS相比SQLite的一些劣势： 1. C/S应用： 如果你有多个客户端需要同时访问数据库中的数据，特别是他们之间的数据操作是需要通过网 络传输来完成的。在这种情况下，不应该选择SQLite。由于SQLite的数据管理 机制 综治信访维稳工作机制反恐怖工作机制企业员工晋升机制公司员工晋升机制员工晋升机制图 更多的依 赖于OS的文件系统，因此在这种操作下其效率较低。 2. 数据量较大： 受限于操作系统的文件系统，在处理大数据量时，其效率较低。对于超大数据量的存储，甚至 不能提供支持。 3. 高并发： 由于SQLite仅仅提供了粒度很粗的数据锁，如读写锁，因此在每次加锁操作中都会有大量的 数据被锁住，即使仅有极小部分的数据会被访问。换句话说，我们可以认为SQLite只是提供 了表级锁，没有提供行级锁。在这种同步机制下，并发性能很难高效。 四、个性化特征： 1. 零配置： SQLite本身并不需要任何初始化配置文件，也没有安装和卸载的过程。当然也不存在服务器 实例的启动和停止。在使用的过程中，也无需创建用户和划分权限。在系统出现灾难时，如电 源问题、主机问题等，对于SQLite而言，不需要做任何操作。 2. 没有独立的服务器： 和其他关系型数据库不同的是，SQLite没有单独的服务器进程，以供客户端程序访问并提供 相关的服务。SQLite作为一种嵌入式数据库，其运行环境与主程序位于同一进程空间，因此 它们之间的通信完全是进程内通信，而相比于进程间通信，其效率更高。然而需要特别指出的 是，该种结构在实际运行时确实存在保护性较差的问题，比如此时，应用程序出现问题导致进 程崩溃，由于SQLite与其所依赖的进程位于同一进程空间，那么此时SQLite也将随之退出。 但是对于独立的服务器进程，则不会有此问题，它们将在密闭性更好的环境下完成它们的工 2 SQLite学习手册 by @_Nicky 作。 3. 单一磁盘文件： SQLite的数据库被存放在文件系统的单一磁盘文件内，只要有权限便可随意访问和拷贝，这 样带来的主要好处是便于携带和共享。其他的数据库引擎，基本都会将数据库存放在一个磁盘 目录下，然后由该目录下的一组文件构成该数据库的数据文件。尽管我们可以直接访问这些文 件，但是我们的程序却无法操作它们，只有数据库实例进程才可以做到。这样的好处是带来了 更高的安全性和更好的性能，但是也付出了安装和维护复杂的代价。 4. 平台无关性： 这一点在前面已经解释过了。和SQLite相比，很多数据库引擎在备份数据时不能通过该方式 直接备份，只能通过数据库系统提供的各种dump和restore工具，将数据库中的数据先导出到 本地文件中，之后在load到目标数据库中。这种方式存在显而易见的效率问题，首先需要导出 到另外一个文件，如果数据量较大，导出的过程将会比较耗时。然而这只是该操作的一小部 分，因为数据导入往往需要更多的时间。数据在导入时需要很多的验证过程，在存储时，也并 非简简单单的顺序存储，而是需要按照一定的数据结构、算法和策略存放在不同的文件位置。 因此和直接拷贝数据库文件相比，其性能是非常拙劣的。 5. 弱类型： 和大多数支持静态类型的数据库不同的是，SQLite中的数据类型被视为数值的一个属性。因 此对于一个数据表列而言，即便在声明该表时给出了该列的类型，我们在插入数据时仍然可 以插入任意类型，比如Integer的列被存入字符串'hello'。针对该特征唯一的例外是整型的主键 列，对于此种情况，我们只能在该列中存储整型数据。 6. SQL语句编译成虚拟机代码： 很多数据库产品会将SQL语句解析成复杂的，相互嵌套的数据结构，之后再交予执行器遍历该 数据结构完成指定的操作。相比于此，SQLite会将SQL语句先编译成字节码，之后再交由其 自带的虚拟机去执行。该方式提供了更好的性能和更出色的调试能力。 C/C++接口简介 一、概述： 在SQLite提供的C/C++接口中，其中5个APIs属于核心接口。在这篇博客中我们将主 要介绍它们的用法，以及它们所涉及到的核心SQLite对象，如database_connection和 prepared_statement。相比于其它数据库引擎提供的APIs，如OCI、MySQL API等， SQLite提供的接口还是非常易于理解和掌握的。 二、核心对象和接口： 1. 核心对象： 在SQLite中最主要的两个对象是，database_connection和prepared_statement。 database_connection对象是由sqlite3_open()接口函数创建并返回的，在应用程序使用任何其 他SQLite接口函数之前，必须先调用该函数以便获得database_connnection对象，在随后的 其他APIs调用中，都需要该对象作为输入参数以完成相应的工作。至于prepare_statement， 我们可以简单的将它视为编译后的SQL语句，因此，所有和SQL语句执行相关的函数也都需要 该对象作为输入参数以完成指定的SQL操作。 3 SQLite学习手册 by @_Nicky 2. 核心接口： 1). sqlite3_open 上面已经提到过这个函数了，它是操作SQLite数据库的入口函数。该函数返回的 database_connection对象是很多其他SQLite APIs的句柄参数。注意，我们通过该函 数既可以打开已经存在的数据库文件，也可以创建新的数据库文件。对于该函数返回 的database_connection对象，我们可以在多个线程之间共享该对象的指针，以便完成 和数据库相关的任意操作。然而在多线程情况下，我们更为推荐的使用方式是，为每 个线程创建独立的database_connection对象。对于该函数还有一点也需要额外说明， 我们没有必要为了访问多个数据库而创建多个数据库连接对象，因为通过SQLite自带 的ATTACH命令可以在一个连接中方便的访问多个数据库。 2). sqlite3_prepare 该函数将SQL文本转换为prepared_statement对象，并在函数执行后返回该对象的指 针。事实上，该函数并不会评估参数指定SQL语句，它仅仅是将SQL文本初始化为待 执行的状态。最后需要指出的，对于新的应用程序我们可以使用sqlite3_prepare_v2接 口函数来替代该函数以完成相同的工作。 3). sqlite3_step 该函数用于评估sqlite3_prepare函数返回的prepared_statement对象，在执行完该函 数之后，prepared_statement对象的内部指针将指向其返回的结果集的第一行。如果 打算进一步迭代其后的数据行，就需要不断的调用该函数，直到所有的数据行都遍历 完毕。然而对于INSERT、UPDATE和DELETE等DML语句，该函数执行一次即可完 成。 4). sqlite3_column 该函数用于获取当前行指定列的数据，然而严格意义上讲，此函数在SQLite的接口函 数中并不存在，而是由一组相关的接口函数来完成该功能，其中每个函数都返回不同 类型的数据，如： sqlite3_column_blob sqlite3_column_bytes sqlite3_column_bytes16 sqlite3_column_double sqlite3_column_int sqlite3_column_int64 sqlite3_column_text sqlite3_column_text16 sqlite3_column_type sqlite3_column_value sqlite3_column_count 其中sqlite3_column_count函数用于获取当前结果集中的字段数据。下面是使用 sqlite3_step和sqlite3_column函数迭代结果集中每行数据的伪代码，注意这里作为示 例代码简化了对字段类型的判断： int fieldCount = sqlite3_column_count(...); while (sqlite3_step(...) <> EOF) { for (int i = 0; i < fieldCount; ++i) { int v = sqlite3_column_int(...,i); } } 4 SQLite学习手册 by @_Nicky 5). sqlite3_finalize 该函数用于销毁prepared statement对象，否则将会造成内存泄露。 6). sqlite3_close 该函数用于关闭之前打开的database_connection对象，其中所有和该对象相关的 prepared_statements对象都必须在此之前先被销毁。 三、参数绑定： 和大多数关系型数据库一样，SQLite的SQL文本也支持变量绑定，以便减少SQL语句被动态 解析的次数，从而提高数据查询和数据操作的效率。要完成该操作，我们需要使用SQLite提 供的另外两个接口APIs，sqlite3_reset和sqlite3_bind。 见如下示例： void test_parameter_binding() { //1. 不带参数绑定的情况下插入多条数据。 char strSQL[128]; for (int i = 0; i < MAX_ROWS; ++i) { sprintf(strSQL,"insert into testtable values(%d)",i); sqlite3_prepare_v2(..., strSQL); sqlite3_step(prepared_stmt); sqlite3_finalize(prepared_stmt); } //2. 参数绑定的情况下插入多条数据。 string strSQLWithParameter = "insert into testtable values(?)"; sqlite3_prepare_v2(..., strSQL); for (int i = 0; i < MAX_ROWS; ++i) { sqlite3_bind(...,i); sqlite3_step(prepared_stmt); sqlite3_reset(prepared_stmt); } sqlite3_finalize(prepared_stmt); } 这里首先需要说明的是，SQL语句"insert into testtable values(?)"中的问号(?)表示参数变量的 占位符，该规则在很多关系型数据库中都是一致的，因此这对于数据库移植操作还是比较方便 的。 通过上面的示例代码可以显而易见的看出，参数绑定写法的执行效率要高于每次生成不同的 SQL语句的写法，即2)在效率上要明显优于1)，下面是针对这两种写法的具体比较： 1. 单单从程序表面来看，前者在for循环中执行了更多的任务，比如字符串的填充、SQL语句 的prepare，以及prepared_statement对象的释放。 2. 在SQLite的官方文档中明确的指出，sqlite3_prepare_v2的执行效率往往要低于 sqlite3_step的效率。 3. 当插入的数据量较大时，后者带来的效率提升还是相当可观的。 数据表和视图 一、创建数据表: 5 SQLite学习手册 by @_Nicky 该命令的语法规则和使用方式与大多数关系型数据库基本相同，因此我们还是以示例的方式 来演示SQLite中创建表的各种规则。但是对于一些SQLite特有的规则，我们会给予额外的说 明。注：以下所有示例均是在sqlite自带命令行工具中完成的。 1. 最简单的数据表： sqlite> CREATE TABLE testtable (first_col integer); 这里需要说明的是，对于自定义数据表表名，如testtable，不能以sqlite_开头，因为以该前缀 定义的表名都用于sqlite内部。 2. 创建带有缺省值的数据表： sqlite> CREATE TABLE testtable (first_col integer DEFAULT 0, second_col varchar DEFAULT 'hello'); 3. 在指定数据库创建表： sqlite> ATTACH DATABASE 'd:/mydb.db' AS mydb; sqlite> CREATE TABLE mydb.testtable (first_col integer); 这里先通过ATTACH DATABASE命令将一个已经存在的数据库文件attach到当前的连接中， 之后再通过指定数据库名的方式在目标数据库中创建数据表，如mydb.testtable。关于该规则 还需要给出一些额外的说明，如果我们在创建数据表时没有指定数据库名，那么将会在当前连 接的main数据库中创建该表，在一个连接中只能有一个main数据库。如果需要创建临时表， 就无需指定数据库名，见如下示例： --创建两个表，一个临时表和普通表。 sqlite> CREATE TEMP TABLE temptable(first_col integer); sqlite> CREATE TABLE testtable (first_col integer); --将当前连接中的缓存数据导出到本地文件，同时退出当前连接。 sqlite> .backup d:/mydb.db sqlite> .exit --重新建立sqlite的连接，并将刚刚导出的数据库作为主库重新导入。 --查看该数据库中的表信息，通过结果可以看出临时表并没有被持久化到数据库文件中。 sqlite> .tables testtable 4. "IF NOT EXISTS"从句： 如果当前创建的数据表名已经存在，即与已经存在的表名、视图名和索引名冲突，那么本次创 建操作将失败并报错。然而如果在创建表时加上"IF NOT EXISTS"从句，那么本次创建操作将 不会有任何影响，即不会有错误抛出，除非当前的表名和某一索引名冲突。 sqlite> CREATE TABLE testtable (first_col integer); Error: table testtable already exists sqlite> CREATE TABLE IF NOT EXISTS testtable (first_col integer); 5. CREATE TABLE ... AS SELECT： 通过该方式创建的数据表将与SELECT查询返回的结果集具有相同的Schema信息，但是不包 含缺省值和主键等约束信息。然而新创建的表将会包含结果集返回的所有数据。 sqlite> CREATE TABLE testtable2 AS SELECT * FROM testtable; sqlite> .schema testtable2 CREATE TABLE testtable2(first_col INT); .schema命令是sqlite3命令行工具的内置命令，用于显示当前数据表的CREATE TABLE语 句。 6 SQLite学习手册 by @_Nicky 6. 主键约束： --直接在字段的定义上指定主键。 sqlite> CREATE TABLE testtable (first_col integer PRIMARY KEY ASC); --在所有字段已经定义完毕后，再定义表的数约束，这里定义的是基于first_col和 second_col的联合主键。 sqlite> CREATE TABLE testtable2 ( ...> first_col integer, ...> second_col integer, ...> PRIMARY KEY (first_col,second_col) ...> ); 和其他关系型数据库一样，主键必须是唯一的。 7. 唯一性约束： --直接在字段的定义上指定唯一性约束。 sqlite> CREATE TABLE testtable (first_col integer UNIQUE); --在所有字段已经定义完毕后，在定义表的唯一性约束，这里定义的是基于两个列的唯一性约 束。 sqlite> CREATE TABLE testtable2 ( ...> first_col integer, ...> second_col integer, ...> UNIQUE (first_col,second_col) ...> ); 在SQLite中，NULL值被视为和其他任何值都是不同的，这样包括和其他的NULL值，如下例 ： sqlite> DELETE FROM testtable; sqlite> SELECT count(*) FROM testtable; count(*) ---------- 0 sqlite> INSERT INTO testtable VALUES(NULL); sqlite> INSERT INTO testtable VALUES(NULL); sqlite> SELECT count(*) FROM testtable; count(*) ---------- 2 由此可见，两次插入的NULL值均插入成功。 8. 为空(NOT NULL)约束： sqlite> CREATE TABLE testtable(first_col integer NOT NULL); sqlite> INSERT INTO testtable VALUES(NULL); Error: testtable.first_col may not be NULL 从输出结果可以看出，first_col已经被定义了非空约束，因此不能在插入NULL值了。 9. 检查性约束： sqlite> CREATE TABLE testtable (first_col integer CHECK (first_col < 5)); sqlite> INSERT INTO testtable VALUES(4); sqlite> INSERT INTO testtable VALUES(20); -- 20违反了字段first_col的检查性约束(first_col < 5) Error: constraint failed --和之前的其它约束一样，检查性约束也是可以基于表中的多个列来定义的。 7 SQLite学习手册 by @_Nicky sqlite> CREATE TABLE testtable2 ( ...> first_col integer, ...> second_col integer, ...> CHECK (first_col > 0 AND second_col < 0) ...> ); 二、表的修改： SQLite对ALTER TABLE命令支持的非常有限，仅仅是修改表名和添加新字段。其它的功能， 如重命名字段、删除字段和添加删除约束等均为提供支持。 1. 修改表名： 需要先说明的是，SQLite中表名的修改只能在同一个数据库中，不能将其移动到Attached数据 库中。再有就是一旦表名被修改后，该表已存在的索引将不会受到影响，然而依赖该表的视图 和触发器将不得不重新修改其定义。 sqlite> CREATE TABLE testtable (first_col integer); sqlite> ALTER TABLE testtable RENAME TO testtable2; sqlite> .tables testtable2 通过.tables命令的输出可以看出，表testtable已经被修改为testtable2。 2. 新增字段： sqlite> CREATE TABLE testtable (first_col integer); sqlite> ALTER TABLE testtable ADD COLUMN second_col integer; sqlite> .schema testtable CREATE TABLE "testtable" (first_col integer, second_col integer); 通过.schema命令的输出可以看出，表testtable的定义中已经包含了新增字段。 关于ALTER TABLE最后需要说明的是，在SQLite中该命令的执行时间是不会受到当前表行数 的影响，也就是说，修改有一千万行数据的表和修改只有一条数据的表所需的时间几乎是相等 的。 三、表的删除： 在SQLite中如果某个表被删除了，那么与之相关的索引和触发器也会被随之删除。在很多其 他的关系型数据库中是不可以这样的，如果必须要删除相关对象，只能在删除表语句中加入 WITH CASCADE从句。见如下示例： sqlite> CREATE TABLE testtable (first_col integer); sqlite> DROP TABLE testtable; sqlite> DROP TABLE testtable; Error: no such table: testtable sqlite> DROP TABLE IF EXISTS testtable; 从上面的示例中可以看出，如果删除的表不存在，SQLite将会报错并输出错误信息。如果希 望在执行时不抛出异常，我们可以添加IF EXISTS从句，该从句的语义和CREATE TABLE中 的完全相同。 四、创建视图： 我们这里只是给出简单的SQL命令示例，具体的含义和技术细节可以参照上面的创建数据表部 分，如临时视图、"IF NOT EXISTS"从句等。 8 SQLite学习手册 by @_Nicky 1. 最简单的视图： sqlite> CREATE VIEW testview AS SELECT * FROM testtable WHERE first_col > 100; 2. 创建临时视图： sqlite> CREATE TEMP VIEW tempview AS SELECT * FROM testtable WHERE first_col > 100; 3. "IF NOT EXISTS"从句： sqlite> CREATE VIEW testview AS SELECT * FROM testtable WHERE first_col > 100; Error: table testview already exists sqlite> CREATE VIEW IF NOT EXISTS testview AS SELECT * FROM testtable WHERE first_col > 100; 五、删除视图： 该操作的语法和删除表基本相同，因此这里只是给出示例： sqlite> DROP VIEW testview; sqlite> DROP VIEW testview; Error: no such view: testview sqlite> DROP VIEW IF EXISTS testview; 内置函数 一、聚合函数： SQLite中支持的聚合函数在很多其他的关系型数据库中也同样支持，因此我们这里将只是给 出每个聚集函数的简要说明，而不在给出更多的示例了。这里还需要进一步说明的是，对于所 有聚合函数而言，distinct关键字可以作为函数参数字段的前置属性，以便在进行计算时忽略 到所有重复的字段值，如count(distinct x)。 函数 说明 avg(x) 该函数返回在同一组内参数字段的平均值。 对于不能转换为数字值的String和BLOB类型 的字段值，如'HELLO'，SQLite会将其视为 0。avg函数的结果总是浮点型，唯一的例外 是所有的字段值均为NULL，那样该函数的结 果也为NULL。 count(x|*) count(x)函数返回在同一组内，x字段中值不 等于NULL的行数。count(*)函数返回在同一 组内的数据行数。 9 SQLite学习手册 by @_Nicky group_concat(x[,y]) 该函数返回一个字符串，该字符串将会连接 所有非NULL的x值。该函数的y参数将作为每 个x值之间的分隔符，如果在调用时忽略该参 数，在连接时将使用缺省分隔符","。再有就 是各个字符串之间的连接顺序是不确定的。 max(x) 该函数返回同一组内的x字段的最大值，如果 该字段的所有值均为NULL，该函数也返回 NULL。 min(x) 该函数返回同一组内的x字段的最小值，如果 该字段的所有值均为NULL，该函数也返回 NULL。 sum(x) 该函数返回同一组内的x字段值的总和，如 果字段值均为NULL，该函数也返回NULL。 如果所有的x字段值均为整型或者NULL，该 函数返回整型值，否则就 返回浮点型数值。 最后需要指出的是，如果所有的数据值均为 整型，一旦结果超过上限时将会抛出"integer overflow"的异常。 total(x) 该函数不属于标准SQL，其功能和sum基本 相同，只是计算结果比sum更为合理。比如 当所有字段值均为NULL时，和sum不同的 是，该函数返回0.0。再有就是该函数始终 返回浮点型数值。该函数始终都不会抛出异 常。 二、核心函数： 以下函数均为SQLite缺省提供的内置函数，其声明和描述见如下列表： 10 SQLite学习手册 by @_Nicky 函数 说明 abs(X) 该函数返回数值参数X的绝对值，如果X为 NULL，则返回NULL，如果X为不能转换 成数值的字符串，则返回0，如果X值超出 Integer的上限，则抛出"Integer Overflow"的 异常。 changes() 该函数返回最近执行的INSERT、 UPDATE和DELETE语句所影响的数据 行数。我们也可以通过执行C/C++函数 sqlite3_changes()得到相同的结果。 coalesce(X,Y,...) 返回函数参数中第一个非NULL的参数，如果 参数都是NULL，则返回NULL。该函数至少 2个参数。 ifnull(X,Y) 该函数等同于两个参数的coalesce()函数， 即返回第一个不为NULL的函数参数，如果两 个均为NULL，则返回NULL。 length(X) 如果参数X为字符串，则返回字符的数量， 如果为数值，则返回该参数的字符串表示形 式的长度，如果为NULL，则返回NULL。 11 SQLite学习手册 by @_Nicky lower(X) 返回函数参数X的小写形式，缺省情况下， 该函数只能应用于ASCII字符。 ltrim(X[,Y]) 如果没有可选参数Y，该函数将移除参数X左 侧的所有空格符。如果有参数Y，则移除X左 侧的任意在Y中出现的字符。最后返回移除 后的字符串。 max(X,Y,...) 返回函数参数中的最大值，如果有任何一个 参数为NULL，则返回NULL。 min(X,Y,...) 返回函数参数中的最小值，如果有任何一个 参数为NULL，则返回NULL。 nullif(X,Y) 如果函数参数相同，返回NULL，否则返回第 一个参数。 random() 返回整型的伪随机数。 replace(X,Y,Z) 将字符串类型的函数参数X中所有子字符串 Y替换为字符串Z，最后返回替换后的字符 串，源字符串X保持不变。 round(X[,Y]) 返回数值参数X被四舍五入到Y刻度的值，如 果参数Y不存在，缺省参数值为0。 rtrim(X[,Y]) 如果没有可选参数Y，该函数将移除参数X右 侧的所有空格符。如果有参数Y，则移除X右 侧的任意在Y中出现的字符。最后返回移除 后的字符串。 12 SQLite学习手册 by @_Nicky substr(X,Y[,Z]) 返回函数参数X的子字符串，从第Y位开始 (X中的第一个字符位置为1)截取Z长度的字 符，如果忽略Z参数，则取第Y个字符后面的 所有字符。如果Z的值为负数，则从第Y位 开始，向左截取abs(Z)个字符。如果Y值为 负数，则从X字符串的尾部开始计数到第 abs(Y)的位置开始。 total_changes() 该函数返回自从该连接被打开时起， INSERT、UPDATE和DELETE语句总共影 响的行数。我们也可以通过C/C++接口函数 sqlite3_total_changes()得到相同的结果。 trim(x[,y]) 如果没有可选参数Y，该函数将移除参数X两 侧的所有空格符。如果有参数Y，则移除X两 侧的任意在Y中出现的字符。最后返回移除 后的字符串。 upper(X) 返回函数参数X的大写形式，缺省情况下， 该函数只能应用于ASCII字符。 typeof(X) 返回函数参数数据类型的字符串表示形式， 如"Integer、text、real、null"等。 三、日期和时间函数： SQLite主要支持以下四种与日期和时间相关的函数，如： 1. date(timestring, modifier, modifier, ...) 2. time(timestring, modifier, modifier, ...) 3. datetime(timestring, modifier, modifier, ...) 4. strftime(format, timestring, modifier, modifier, ...) 以上所有四个函数都接受一个时间字符串作为参数，其后再跟有0个或多个修改符。其中 strftime()函数还接受一个格式字符串作为其第一个参数。strftime()和C运行时库中的同名函数 完全相同。至于其他三个函数，date函数的缺省格式为："YYYY-MM-DD"，time函数的缺省格 式为："HH:MM:SS"，datetime函数的缺省格式为："YYYY-MM-DD HH:MM:SS"。 1. strftime函数的格式信息： 格式 说明 %d day of month: 00 %f fractional seconds: SS.SSS 13 SQLite学习手册 by @_Nicky %H hour: 00-24 %j day of year: 001-366 %J Julian day number %m month: 01-12 %M minute: 00-59 %s seconds since 1970-01-01 %S seconds: 00-59 %w day of week 0-6 with Sunday==0 %W week of year: 00-53 %Y year: 0000-9999 %% % 需要额外指出的是，其余三个时间函数均可用strftime来表示，如： date(...) strftime('%Y-%m-%d', ...) time(...) strftime('%H:%M:%S', ...) datetime(...) strftime('%Y-%m-%d %H:%M:%S', ...) 2. 时间字符串的格式： 见如下列表： 1). YYYY-MM-DD 2). YYYY-MM-DD HH:MM 3). YYYY-MM-DD HH:MM:SS 4). YYYY-MM-DD HH:MM:SS.SSS 5). HH:MM 6). HH:MM:SS 7). HH:MM:SS.SSS 8). now 5)到7)中只是包含了时间部分，SQLite将假设日期为2000-01-01。8)表示当前时间。 3. 修改符： 见如下列表： 1). NNN days 14 SQLite学习手册 by @_Nicky 2). NNN hours 3). NNN minutes 4). NNN.NNNN seconds 5). NNN months 6). NNN years 7). start of month 8). start of year 9). start of day 10).weekday N 1)到6)将只是简单的加减指定数量的日期或时间值，如果NNN的值为负数，则减，否则加。7) 到9)则将时间串中的指定日期部分设置到当前月、年或日的开始。10)则将日期前进到下一个 星期N，其中星期日为0。注：修改符的顺序极为重要，SQLite将会按照从左到右的顺序依次 执行修改符。 4. 示例： --返回当前日期。 sqlite> SELECT date('now'); 2012-01-15 --返回当前月的最后一天。 sqlite> SELECT date('now','start of month','1 month','-1 day'); 2012-01-31 --返回从1970-01-01 00:00:00到当前时间所流经的秒数。 sqlite> SELECT strftime('%s','now'); 1326641166 --返回当前年中10月份的第一个星期二是日期。 sqlite> SELECT date('now','start of year','+9 months','weekday 2'); 2012-10-02 索引和数据分析/清理 一、创建索引： 在SQLite中，创建索引的SQL语法和其他大多数关系型数据库基本相同，因为这里也仅仅是 给出示例用法： sqlite> CREATE TABLE testtable (first_col integer,second_col integer); --创建最简单的索引，该索引基于某个表的一个字段。 sqlite> CREATE INDEX testtable_idx ON testtable(first_col); --创建联合索引，该索引基于某个表的多个字段，同时可以指定每个字段的排序规则(升序/降 序)。 sqlite> CREATE INDEX testtable_idx2 ON testtable(first_col ASC,second_col DESC); --创建唯一性索引，该索引规则和数据表的唯一性约束的规则相同，即NULL和任何值都不 同，包括NULL本身。 sqlite> CREATE UNIQUE INDEX testtable_idx3 ON testtable(second_col DESC); sqlite> .indices testtable testtable_idx testtable_idx2 testtable_idx3 从.indices命令的输出可以看出，三个索引均已成功创建。 15 SQLite学习手册 by @_Nicky 二、删除索引： 索引的删除和视图的删除非常相似，含义也是如此，因此这里也只是给出示例： sqlite> DROP INDEX testtable_idx; --如果删除不存在的索引将会导致操作失败，如果在不确定的情况下又不希望错误被抛出，可 以使用"IF EXISTS"从句。 sqlite> DROP INDEX testtable_idx; Error: no such index: testtable_idx sqlite> DROP INDEX IF EXISTS testtable_idx; 三、重建索引： 重建索引用于删除已经存在的索引，同时基于其原有的规则重建该索引。这里需要说明的是， 如果在REINDEX语句后面没有给出数据库名，那么当前连接下所有Attached数据库中所有索 引都会被重建。如果指定了数据库名和表名，那么该表中的所有索引都会被重建，如果只是指 定索引名，那么当前数据库的指定索引被重建。 --当前连接attached所有数据库中的索引都被重建。 sqlite> REINDEX; --重建当前主数据库中testtable表的所有索引。 sqlite> REINDEX testtable; --重建当前主数据库中名称为testtable_idx2的索引。 sqlite> REINDEX testtable_idx2; 四、数据分析： 和PostgreSQL非常相似，SQLite中的ANALYZE命令也同样用于分析数据表和索引中的数 据，并将统计结果存放于SQLite的内部系统表中，以便于查询优化器可以根据分析后的统计 数据选择最优的查询执行路径，从而提高整个查询的效率。见如下示例： --如果在ANALYZE命令之后没有指定任何参数，则分析当前连接中所有Attached数据库中的 表和索引。 sqlite> ANALYZE; --如果指定数据库作为ANALYZE的参数，那么该数据库下的所有表和索引都将被分析并生成 统计数据。 sqlite> ANALYZE main; --如果指定了数据库中的某个表或索引为ANALYZE的参数，那么该表和其所有关联的索引都 将被分析。 sqlite> ANALYZE main.testtable; sqlite> ANALYZE main.testtable_idx2; 五、数据清理： 和PostgreSQL中的VACUUM命令相比，他们的功能以及实现方式非常相似，不同的是 PostgreSQL提供了更细的粒度，而SQLite只能将该命令作用于数据库，无法再精确到数据库 中指定的数据表或者索引，然而这一点恰恰是PostgreSQL可以做到的。 当某个数据库中的一个或多个数据表存在大量的插入、更新和删除等操作时，将会有大量的 磁盘空间被已删除的数据所占用，在没有执行VACUUM命令之前，SQLite并没有将它们归还 于操作系统。由于该类数据表中的数据存储非常分散，因此在查询时，无法得到更好的批量 IO读取效果，从而影响了查询效率。 在SQLite中，仅支持清理当前连接中的主数据库，而不能清理其它Attached数据库。 VACUUM命令在完成数据清理时采用了和PostgreSQL相同的策略，即创建一个和当前数据库 文件相同大小的新数据库文件，之后再将该数据库文件中的数据有组织的导入到新文件中，其 16 SQLite学习手册 by @_Nicky 中已经删除的数据块将不会被导入，在完成导入后，收缩新数据库文件的尺寸到适当的大小。 该命令的执行非常简单，如： sqlite> VACUUM; 数据库和事物 一、Attach数据库： ATTACH DATABASE语句添加另外一个数据库文件到当前的连接中，如果文件名为 ":memory:"，我们可以将其视为内存数据库，内存数据库无法持久化到磁盘文件上。如果操作 Attached数据库中的表，则需要在表名前加数据库名，如dbname.table_name。最后需要说 明的是，如果一个事务包含多个Attached数据库操作，那么该事务仍然是原子的。 见如下示例： sqlite> CREATE TABLE testtable (first_col integer); sqlite> INSERT INTO testtable VALUES(1); sqlite> .backup 'D:/mydb.db' --将当前连接中的主数据库备份到指定文件。 sqlite> .exit --重新登录sqlite命令行工具： sqlite> CREATE TABLE testtable (first_col integer); sqlite> INSERT INTO testtable VALUES(2); sqlite> INSERT INTO testtable VALUES(1); sqlite> ATTACH DATABASE 'D:/mydb.db' AS mydb; sqlite> .header on --查询结果将字段名作为标题输出。 sqlite> SELECT t1.first_col FROM testtable t1, mydb.testtable t2 WHERE t.first_col = t2.first_col; first_col ---------- 1 二、Detach数据库： 卸载将当前连接中的指定数据库，注意main和temp数据库无法被卸载。