SQLite库包含一个名字叫做sqlite3的命令行,它可以让用户手工输入并执行面向SQLite数据库的SQL命令。本文档提供一个样使用sqlite3的简要说明. 一.qlite3一些常用Sql语句操作创建表: create table 表名(元素名 类型,…); 删除表: drop table 表名; 插入数据: insert into 表名 values(, , ,) ; 创建索引: create [unique] index 索引名on 表名(col….); 删除索引: drop index 索引名(索引是不可更改的,想更改必须删除重新建) 更新数据: update 表名 set 字段=’修改后的内容’ where 条件; 增加一个列: Alter table 表名 add column 字段 数据类型; 选择查询: select 字段(以”,”隔开) from 表名 where 条件; 日期和时间: Select datetime('now') 日期: select date('now'); 总数:select count(*) from table1; 排序:select 字段 from table1 order by 字段(desc或asc) ;(降序或升序) 分组:select 字段 from table1 group by 字段,字段… ; 限制输出:select 字段fromtable1 limit x offset y; = select 字段 from table1 limit y , x; (备注:跳过y行,取x行数据)
(操作仍待完善)… SQLite支持哪些数据类型些?NULL 值为NULL 但实际上,sqlite3也接受如下的数据类型: 如果将声明表的一列设置为 INTEGER PRIMARY KEY,则具有: 1.每当你在该列上插入一NULL值时, NULL自动被转换为一个比该列中最大值大1的一个整数; 2.如果表是空的, 将会是1; 算术函数abs(X)返回给定数字表达式的绝对值。 max(X,Y[,...])返回表达式的最大值。 min(X,Y[,...])返回表达式的最小值。 random(*)返回随机数。 round(X[,Y])返回数字表达式并四舍五入为指定的长度或精度。 字符处理函数length(X)返回给定字符串表达式的字符个数。 lower(X)将大写字符数据转换为小写字符数据后返回字符表达式。 upper(X)返回将小写字符数据转换为大写的字符表达式。 substr(X,Y,Z)返回表达式的一部分。 randstr() quote(A) like(A,B) 确定给定的字符串是否与指定的模式匹配。 glob(A,B) 条件判断函数coalesce(X,Y[,...]) ifnull(X,Y) nullif(X,Y) 集合函数avg(X)返回组中值的平均值。 count(X)返回组中项目的数量。 max(X)返回组中值的最大值。 min(X)返回组中值的最小值。 sum(X)返回表达式中所有值的和。 其他函数typeof(X)返回数据的类型。 last_insert_rowid()返回最后插入的数据的 ID 。 sqlite_version(*)返回 SQLite 的版本。 change_count()返回受上一语句影响的行数。 last_statement_change_count() 二.有关事务的操作(成批操作的时候,启动事务,比不启动事务快n倍) 开始事物处理 BEGIN TRANSACTION; ………….. 进行对数据库操作 ………….. 事物提交 COMMIT; 具体事例如下: 假设有一个 t1 表,其中有 "a", "b", "c" 三列, 如果要删除列 c ,以下过程描述如何做: BEGIN TRANSACTION; 三.启动sqlite3程序仅仅需要敲入带有SQLite数据库名字的"sqlite3"命令即可。如果文件不存在,则创建一个新的(数据库)文件。然后 sqlite3程序将提示你输入SQL。敲入SQL语句(以分号“;”结束),敲回车键之后,SQL语句就会执行。 四.题外话:查询SQLITE_MASTER表 SQLite数据库的框架被保存在一个名叫"sqlite_master"的特殊的表中。你可以像查询其它表一样通过执行“SELECT”查询这个特殊的表。例如: 五.sqlite3的特殊命令 大多数候,sqlite3读入输入行,并把它们传递到SQLite库中去运行。但是如果输入行以一个点(“.”)开始,那么这行将被sqlite3程序自
己截取并解释。这些“点命令”通常被用来改变查询输出的格式,或者执行鞭个预封包(预定义prepackaged)的查询语句。 .bail ON|OFFStop after hitting an error. Default OFF(遇到错误时不再继续, 默认为OFF) .databasesList names and files of attached databases(列出附加到数据库的数据库和文件) .dump ?TABLE? ...Dump the database in an SQL text format(保存表到SQL格式的文件中, 没有指表名, 则保存所有. 如果要保存到磁盘上需要结合 .output 命令.) .echo ON|OFFTurn command echo on or off(打开/关闭 命令行回显) .exitExit this program(退出该命令行) .explain ON|OFFTurn output mode suitable for EXPLAIN on or off.( 以合适的方式显示表头, 不带参数则为开启) .header(s) ON|OFFTurn display of headers on or off(是否显示表头, 和 .explain 差别不是很大) .helpShow this message(显示帮助信息) .import FILE TABLEImport data from FILE into TABLE(从文件中导入表) .indices TABLEShow names of all indices on TABLE(显示索引) .load FILE ?ENTRY?Load an extension library(加载一个扩展库) .mode MODE ?TABLE?Set output mode where MODE is one of:
(设置输出模式, 模式可以是以下几种): .nullvalue STRINGPrint STRING in place of NULL values(以 STRING 代替 NULL 值的输出) .output FILENAMESend output to FILENAME(输出到文件, 而不是显示在屏幕上) .output stdoutSend output to the screen(输出到屏幕上) .prompt MAIN CONTINUEReplace the standard prompts(替换默认的命令提示信息, 默认就是 sqlite>) .quitExit this program(退出命令行) .read FILENAMEExecute SQL in FILENAME(执行 FILENAME 中的 SQL语句) .schema ?TABLE?Show the CREATE statements(显示 CREATE 语句) .separator STRINGChange separator used by output mode and .import .showShow the current values for various settings(显示各种设置) .tables ?PATTERN?List names of tables matching a LIKE pattern(查看数据库的表列表) .timeout MSTry opening locked tables for MS milliseconds(在 MS 时间内尝试打开被锁定的表) .width NUM NUM ...Set column widths for "column" mode(设置 column 模式中的列的宽度)
.timer ON|OFF(显示CPU时间) .vfsname ?AUX?(显示 VFS 栈信息) .restore ?DB? FILE(从文件中还原数据到表, 默认表为 main) 六.改变输出格式(.mode) sqlite3程序可以以八种不同的格式显示一个查询的结果:"csv", "列",
"html", "插入", "行", "制表"和"tcl"。你可以用".mode"点命令在这些输出格式之间切换。 七.查询数据库结构 sqlite3程序提供几个有用的用于查询数据库结构的快捷命令。这些不是不可以用别的方式来实现。这些命令仅仅是一个快捷方式而已。 八.事实上你可以查看sqlite3的源代码(可以在源文件树的src/shell.c中),你可找到上面的具体的查询。 九.将整个数据库转换为ASCII文本文件".dump"命令成一个单一的ASCII文本文件。这个文件可以被用作管道传递给sqlite3命令来转换回数据库。 十.Sqlite3 表结构和数据的导出及导入数据库结构导出和导入: “.output” 默认情况下,sqlte3把结送到标准输出。你可以用“.output”命令改变它。只须把输出文件名做为.output命令的输出参数然后所有后续查询结果将被写到那个文件中。 用“.output stdout”再一次改为标准输出。例如: 全部导出 sqlite > .exit $cat dd //以下是数据库的结构 PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE long(h,m); INSERT INTO "long" VALUES('hhhhhh',2); INSERT INTO "long" VALUES('hello',2); INSERT INTO "long" VALUES('mall',5); INSERT INTO "long" VALUES('suinvzi',8); INSERT INTO "long" VALUES('meimei',5); CREATE TABLE bak(m varchar(10), n int); INSERT INTO "bak" VALUES('hhhhhh',2); INSERT INTO "bak" VALUES('hell',2); INSERT INTO "bak" VALUES('mall',5); INSERT INTO "bak" VALUES('suinvzi',8); INSERT INTO "bak" VALUES('meimei',5); COMMIT; [h2] 导出表结构 sqlite >.output dd sqlite >.schema sqlite > .exit $cat dd //以下是表的结构 CREATE TABLE bak(m varchar(10), n int); CREATE TABLE long(h,m); 全部导入 : (用.read命令,执行
FILENAME 中的 SQL语句) sqlite >.tables bak long //导入的是空表bak,long 数据的导出和导入: 导出:例如: $sqlite3 data.db hello|2 mall|5 suinvzi|8 meimei|5 备注:每次导出若为同一个文本,则正在导出的内容覆盖上次导出的内容 导入: 例如:(用.import 导入的文本 被导入的表 命令, 把文件中的数据导入表中) $sqlite3 mydb.db sqlite> .show echo: off explain: off headers: off mode: list nullvalue: "" output: stdout separator: "|" stats: off width: hello|2 mall|5 suinvzi|8 meimei|5 备注:文本的内容所对应的数据分布与所要导入的表一致,元素间的分割符要与”.show”中separator: "|"的字符一致如果不一致可直接修改, 比如: sqlite>.separator "," 将分隔符转为逗号,不然导入会出错. 十一.其它的点命令".explain"
命令可以被用来设置输出格式为“column”
并设置列宽为EXPLAIN命令看起来比较合理的宽度。EXPLAIN命令是SQLite特有的SQL扩展,它是对调试有用。如果任何常规的SQL被 EXPLAIN执行,那么SQL命令被分解并分析但并不执行。取而代之的是,虚拟机指令序列将被用于执行SQL命令并返回一个类似的查询结果。如: 十二.在命令和脚本中使用sqlite3一
个在脚本命令中使用sqlite3的方式是用“echo”或“cat”来产生一个命令序列在一个文件中,然后当从一个产生的命令行中重定向输入时调用 sqlite3。它有用并且适应许多环境。但作为一附加的便利是,sqlite3允许一个单一的SQL语句在命令行中作为数据库名后的第二个参数输入。当 sqlite3程序带着两个参数启动时,第二个参数被传递给SQLite库处理,查询以列表模式打印到标准输出,然后程序退出。这个机制被设计用于让
sqlite3容易于用于连接诸如"AWK"的程序。例如: 十三.结束命令行命令SQLite 命令通常以一个分号结束。在一个命令行中你也可以用“GO”单词(大小写敏感)或者一个“/”斜线在它所在好行结束一个命令。这常被SQL Server和Oracle使用。这些将不在sqlite3_exec()中有用,因为命令行在传递它们到函数之前把这些翻译为分号。 十四.参数化的SQL语句sqlite可以在shell/dos command底下直接执行命令: 对数据库进行SQL操作: 输出 HTML 表格: 将数据库「导出来」: 将数据库「导进去」: 十五.SQLITE深入------常见问题1.如何建立自动增长字段?简短回答:声明为 INTEGER PRIMARY KEY 的列将会自动增长 。 (如果是最大可能的主键 9223372036854775807,那个,将键值将是随机未使用的数。)如,有下列表: CREATE TABLE t1( INSERT INTO t1 VALUES(NULL,123); INSERT INTO t1 VALUES((SELECT max(a) FROM
t1)+1,123); 2.多个应用程序或一个应用程序的多个实例可以同时访问同一个数据库文件吗?多个进程可同时打开同一个数据库。多个进程可以同时进行SELECT 操作,但在任一时刻,只能有一个进程对数据库进行更改。 SQLite使用读、写锁控制对数据库的访问。(在Win95/98/ME等不支持读、写锁的系统下,使用一个概率性的模拟来代替。)但使用时要注意:如果数据库文件存放于一个NFS文件系统上,这种锁机制可能不能正常工作。 这是因为 fcntl() 文件锁在很多NFS上没有正确的实现。 在可能有多个进程同时访问数据库的时候,应该避免将数据库文件放到NFS上。在Windows上,Microsoft的文档中说:如果使用 FAT 文件系统而没有运行 share.exe 守护进程,那么锁可能是不能正常使用的。那些在Windows上有很多经验的人告诉我:对于网络文件,文件锁的实现有好多Bug,是靠不住的。如果他们说的是对的,那么在两台或多台Windows机器间共享数据库可能会引起不期望的问题。 我们意识到,没有其它嵌入式的 SQL 数据库引擎能象 SQLite 这样处理如此多的并发。SQLite允许多个进程同时打开一个数据库,同时读一个数据库。当有任何进程想要写时,它必须在更新过程中锁住数据库文件。但那通常只是几毫秒的时间。其它进程只需等待写进程干完活结束。典型地,其它嵌入式的SQL数据库引擎同时只允许一个进程连接到数据库。 但是,Client/Server数据库引擎(如 PostgreSQL, MySQL, 或 Oracle)通常支持更高级别的并发,并且允许多个进程同时写同一个数据库。这种机制在Client/Server结构的数据库上是可能的,因为总是有一个单一的服务器进程很好地控制、协调对数据库的访问。如果你的应用程序需要很多的并发,那么你应该考虑使用一个Client/Server 结构的数据库。但经验表明,很多应用程序需要的并发,往往比其设计者所想象的少得多。 当SQLite试图访问一个被其它进程锁住的文件时,缺省的行为是返回 SQLITE_BUSY。 可以在C代码中使用 sqlite3_busy_handler() 或 sqlite3_busy_timeout() API 函数调整这一行为。 3.在SQLite数据库中如何列出所有的表和索引?如果你运行 sqlite3 命令行来访问你的数据库,可以键入 “.tables”来获得所有表的列表。或者,你可以输入 “.schema” 来看整个数据库模式,包括所有的表的索引。输入这些命令,后面跟一个LIKE模式匹配可以限制显示的表。 在一个 C/C++ 程序中(或者脚本语言使用 Tcl/Ruby/Perl/Python 等) 你可以在一个特殊的名叫 SQLITE_MASTER 上执行一个SELECT查询以获得所有 表的索引。每一个 SQLite 数据库都有一个叫 SQLITE_MASTER 的表, 它定义数据库的模式。 SQLITE_MASTER 表看起来如下: CREATE TABLE sqlite_master ( SELECT name FROM sqlite_master SQLITE_MASTER 表是只读的。不能对它使用 UPDATE、INSERT 或 DELETE。 它会被 CREATE TABLE、CREATE INDEX、DROP TABLE 和 DROP INDEX 命令自动更新。 临时表不会出现在 SQLITE_MASTER 表中。临时表及其索引和触发器存放在另外一个叫 SQLITE_TEMP_MASTER 的表中。SQLITE_TEMP_MASTER 跟 SQLITE_MASTER 差不多,但它只是对于创建那些临时表的应用可见。如果要获得所有表的列表, 不管是永久的还是临时的,可以使用类似下面的命令: SELECT name FROM 4.在SQLite中,VARCHAR字段最长是多少?SQLite 不强制 VARCHAR 的长度。 你可以在 SQLITE 中声明一个 VARCHAR(10),SQLite还是可以很高兴地允许你放入500个字符。 并且这500个字符是原封不动的,它永远不会被截断。 5.SQLite支持二进制大对象吗?SQLite 3.0 及以后版本允许你在任何列中存储 BLOB 数据。即使该列被声明为其它类型也可以。 在SQLite中,如何在一个表上添加或删除一列? SQLite 有有限地 ALTER TABLE 支持。你可以使用它来在表的末尾增加一列,可更改表的名称。如果需要对表结构做更复杂的改变,则必须重新建表。重建时可以先将已存在的数据放到一个临时表中,删除原表, 创建新表,然后将数据从临时表中复制回来。 如,假设有一个 t1 表,其中有 "a", "b", "c" 三列, 如果要删除列 c ,以下过程描述如何做: BEGIN TRANSACTION; 6.在数据库中删除了很多数据,但数据库文件没有变小,是Bug吗?不是。当你从SQLite数据库中删除数据时, 未用的磁盘空间将会加入一个内部的“自由列表”中。 当你下次插入数据时,这部分空间可以重用。磁盘空间不会丢失,但也不会返还给操作系统。 如果删除了大量数据,而又想缩小数据库文件占用的空间,执行 VACUUM 命令。 VACUUM 将会从头重新组织数据库。这将会使用数据库有一个空的“自由链表”, 数据库文件也会最小。但要注意的是,VACUUM 的执行会需要一些时间(在SQLite开发时,在Linux上,大约每M字节需要半秒种),并且, 执行过程中需要原数据库文件至多两倍的临时磁盘空间。 对于 SQLite 3.1版本,一个 auto-vacumm 模式可以替代 VACUUM 命令。 可以使用 auto_vacuum pragma 打开。 7.SQLITE_SCHEMA error是什么错误?为什么会出现该错误?当一个准备好的(prepared)SQL语句不再有效或者无法执行时,将返回一个 SQLITE_SCHEMA 错误。发生该错误时,SQL语句必须使用 sqlite3_prepare() API来重新编译. 在 SQLite 3 中, 一个 SQLITE_SCHEMA 错误只会发生在用 sqlite3_prepare()/sqlite3_step()/sqlite3_finalize() API 执行 SQL 时。而不会发生在使用 sqlite3_exec()时。 在版本2中不是这样。 准备好的语句失效的最通常原因是:在语句准备好后, 数据库的模式又被修改了。另外的原因会发生在: 数据库离线:DETACHed. int rc; do { while(
SQLITE_ROW==sqlite3_step(pStmt) ){ /* Finalize
the statement. If an SQLITE_SCHEMA error has 8.如何一次插入多个数据Insert into SAMPLE(PRJNUM, PRJNAME, EMYNUM, EMYNAME, SALCATEGORY, SALPACKAGE) values(100001, 'TPMS', 200001, 'Johnson', 'A', 2000), (100001, 'TPMS', 200002, 'Christine', 'B', 3000), (100001, 'TPMS', 200003, 'Kevin', 'C', 4000), (100002, 'TCT', 200001, 'Johnson', 'A', 2000), (100002, 'TCT', 200004, 'Apple', 'B', 3000); 9.如何备份数据库先打开数据库test.db sqlite>sqlite3 test.db sqlite> .backup test.bak 10.如何恢复数据库先打开数据库test.db sqlite>sqlite3 test.db sqlite> .restore test.bak 11.先判断表如果不存在,则再创建表SQL可以如下写,供参考: create table if not exists student(id integer primary key autoincrement,/ age smallint ,anchor smallint); 12.如何查询SQLite3小工具的版本执行 select sqlite_version();命令即可 十六.Sqlite数据库中索引的使用、索引的优缺点要使用索引对数据库的数据操作进行优化,那必须明确几个问题: 1. 数据库索引简介
2. B-树与索引大多数的数据库都是以B-树或者B+树作为存储结构的,B树索引也是最常见的索引。先简单介绍下B-树,可以增强对索引的理解。
3. 索引的种类1)聚集索引:表中行的物理顺序与键值的逻辑(索引)顺序相同。因为数据的物理顺序只能有一种,所以一张表只能有一个聚集索引。如果一张表没有聚集索引,那么这张表就没有顺序的概念,所有的新行都会插入到表的末尾。对于聚集索引,叶节点即存储了数据行,不再有单独的数据页。就比如说我小时候查字典从来不看目录,我觉得字典本身就是一个目录,比如查裴字,只需要翻到p字母开头的,再按顺序找到e。通过这个方法我每次都能最快的查到老师说的那个字,得到老师的表扬。 2)非聚集索引:表中行的物理顺序与索引顺序无关。对于非聚集索引,叶节点存储了索引字段值以及指向相应数据页的指针。叶节点紧邻在数据之上,对数据页的每一行都有相应的索引行与之对应。有时候查字典,我并不知道这个字读什么,那我就不得不通过字典目录的“部首”来查找了。这时候我会发现,目录中的排序和实际正文的排序是不一样的,这对我来说很苦恼,因为我不能比别人快了,我需要先再目录中找到这个字,再根据页数去找到正文中的字。 4.索引与数据的查询,插入与删除1)查询。查询操作就和查字典是一样的。当我们去查找指定记录时,数据库会先查找根节点,将待查数据与根节点的数据进行比较,再通过根节点的指针查询下一个记录,直到找到这个记录。这是一个简单的平衡树的二分搜索的过程,我就不赘述了。在聚集索引中,找到页节点即找到了数据行,而在非聚集索引中,我们还需要再去读取数据页。 2)插入。聚集索引的插入操作比较复杂,最简单的情况,插入操作会找到对于的数据页,然后为新数据腾出空间,执行插入操作。如果该数据页已经没有空间,那就需要拆分数据页,这是一个非常耗费资源的操作。对于仅有非聚集索引的表,插入只需在表的末尾插入即可。如果也包含了聚集索引,那么也会执行聚集索引需要的插入操作。 3)删除。删除行后下方的数据会向上移动以填补空缺。如果删除的数据是该数据页的最后一行,那么这个数据页会被回收,它的前后一页的指针会被改变,被回收的数据页也会在特定的情况被重新使用。与此同时,对于聚集索引,如果索引页只剩一条记录,那么该记录可能会移动到邻近的索引表中,原来的索引页也会被回收。而非聚集索引没办法做到这一点,这就会导致出现多个数据页都只有少量数据的情况。 5. 索引的优缺点其实通过前面的介绍,索引的优缺点已经一目了然。 3)在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。 再说缺点: 3)当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度,这个是比较大的问题。 6. 索引的使用 根据上文的分析,我们大致对什么时候使用索引有了自己的想法(如果你没有,回头再看一遍。。。)。一般我们需要在这些列上建立索引: 4)在经常需要对范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的,同样,在经常需要排序的列上最好也创建索引。 6)在经常放到where子句中的列上面创建索引,加快条件的判断速度。要注意的是where字句中对列的任何操作(如计算表达式,函数)都需要对表进行整表搜索,而没有使用该列的索引。所以查询时尽量把操作移到等号右边。 对于以下的列我们不应该创建索引: 7. 在sqlite中使用索引1)Sqlite不支持聚集索引,android默认需要一个_id字段,这保证了你插入的数据会按“_id”的整数顺序插入,这个integer类型的主键就会扮演和聚集索引一样的角色。所以不要再在对于声明为:INTEGER PRIMARY KEY的主键上创建索引。 2)很多对索引不熟悉的朋友在表中创建了索引,却发现没有生效,其实这大多数和我接下来讲的有关。对于where子句中出现的列要想索引生效,会有一些限制,这就和前导列有关。所谓前导列,就是在创建复合索引语句的第一列或者连续的多列。比如通过:CREATE INDEX comp_ind ON table1(x, y, z)创建索引,那么x,xy,xyz都是前导列,而yz,y,z这样的就不是。下面讲的这些,对于其他数据库或许会有一些小的差别,这里以sqlite为标准。在where子句中,前导列必须使用等于或者in操作,最右边的列可以使用不等式,这样索引才可以完全生效。同时,where子句中的列不需要全建立了索引,但是必须保证建立索引的列之间没有间隙。举几个例子来看吧: 用如下语句创建索引: 索引将不会被使用,因为没有使用前导列,这个查询会是一个全表查询。 3)对于between,or,like,都无法使用索引。 其实除了索引,对查询性能的影响因素还有很多,比如表的连接,是否排序等。影响数据库操作的整体性能就需要考虑更多因素,使用更对的技巧,不得不说这是一个很大的学问。 最后在android上使用sqlite写一个简单的例子,看下索引对数据库操作的影响。 删除:delete from t1 where a = '1010' 数据如下(5次不同的操作取平均值): 可以看到显著提升了查询的速度,稍稍减慢了插入速度,还稍稍提升了更新数据和删除数据的速度。如果把更新和删除中的where子句中的列换成b,速度就和没有索引一样了,因为索引失效。所以索引能大幅度提升查询速度,对于删除和更新操作,如果where子句中的列使用了索引,即使需要重新build索引,有可能速度还是比不使用索引要快的。对与插入操作,索引显然是个负担。同时,索引让db的大小增加了2倍多。 还有个要吐槽的是,android中的rawQurey方法,执行完sql语句后返回一个cursor,其实并没有完成一个查询操作,我在rawquery之前和之后计算查询时间,永远是1ms...这让我无比苦闷。看了下源码,在对cursor调用moveToNext这些移动游标方法时,都会最终先调用getCount方法,而getCount方法才会调用native方法调用真正的查询操作。这种设计显然更加合理 |
|