SQLite参考资料
SQLite参考资料
简介
SQLite 是一个开源的、内嵌式的关系型数据库。它最初发布于 2000 年,在便携性、易用性、紧凑性、有效性和可靠性方面有突出的表现. SQLite实现了多数的SQL-92标准,包括事务、触发器和多数的复杂查询。且体积只有300KB左右无需安装,最大支持2TB。适合在终端或小型网站建站使用。SQLite 被应用在 Solaris 10操作系统、Mac OS 操作系统、iPhone、Skype和Android 中。
下载安装
相关资源网站:http://www.sqlite.org
命令行版本
命令行版本是一个可执行文件,可在cmd.exe中运行
Windows 版命令工具的下载地址为:sqlite-shell-win32-x86-3070701.zip
动态链接库
动态链接库是一个.Dll文件,应用程序调用操作数据库
Windows 动态链接库的下载地址为:sqlite-dll-win32-x86-3070701.zip
命令使用
sqlite程序通过查找分号(;)来决定一个SQL语句的结束,所以要执行SQL语句请确保在SQL语句后有分号(;).如果你省略分号,sqlite3将给你一个连续的命令提示符(...> )并等你给当前的SQL命令添加更多的文字。这个特点让你输入多行的多个SQL语句.但点号(.)命令不要分号(;).ctrl+c可以结束当前命令。
启用SQLITE3
在dos窗口输入sqlite3 test.db,然后回车,结果如下:
F:\>sqlite3 test.db
SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
此时我们并没有在F盘下看到 test.db,需要在输入分号(;)然后回车如下:
F:\>sqlite3 test.db
SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> ;
sqlite>
我们现在就可以看到在F盘下有test.db文件了。
附:此命令如果没有test.db数据库则创建,如果有则打开,实例见下文。
建立表(websites)
命令
CREATE TABLE [websites] (
[WebID] INTEGER NOT NULL PRIMARY KEY,
[WebName] VARCHAR(20) NULL
);
注意;不可少";"号不可缺少,否则不执行。
插入数据
insert into [websites] ([WebName])values('CTOChina.net');
insert into [websites] ([WebName])values('搜狐');
insert into [websites] ([WebName])values('雅虎');
查看数据
sqlite> select * from websites;
1|CTOChina.net
2|搜狐
3|雅虎
sqlite>
.help 显示帮助信息
可以接着上一步操作,直接向DOS窗口输入.help命令来查看SQLite的所有命令行及解释如下:
F:\>sqlite3
SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .help
也可以ctrl+c结束命令,想DOS窗口 输入sqlite3执行后再输入.help命令如下:
F:\>sqlite3
SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .help
显示如下:
.backup ?DB? FILE |
Backup DB (default "main") to FILE |
.bail ON|OFF |
Stop after hitting an error. Default OFF |
.databases |
List names and files of attached databases |
.dump ?TABLE? ... |
Dump the database in an SQL text format |
.echo ON|OFF |
Turn command echo on or off |
.exit |
Exit this program |
.explain ON|OFF |
Turn output mode suitable for EXPLAIN on or off. |
.genfkey ?OPTIONS? |
Options are: |
.header(s) ON|OFF |
Turn display of headers on or off |
.help |
Show this message |
.import FILE TABLE |
Import data from FILE into TABLE |
.indices TABLE |
Show 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 STRING |
Print STRING in place of NULL values |
.output FILENAME |
Send output to FILENAME |
.output stdout |
Send output to the screen |
.prompt MAIN CONTINUE |
Replace the standard prompts |
.quit |
Exit this program |
.read FILENAME |
Execute SQL in FILENAME |
.restore ?DB? FILE |
Restore content of DB (default "main") from FILE |
.schema ?TABLE? |
Show the CREATE statements |
.separator STRING |
Change separator used by output mode and .import |
.show |
Show the current values for various settings |
.tables ?PATTERN? |
List names of tables matching a LIKE pattern |
.timeout MS |
Try opening locked tables for MS milliseconds |
.width NUM NUM ... |
Set column widths for "column" mode |
注解:?表示支持正则表达式
.Backup 备份
backup ?DB? FILE 备份数据库到文件,默认备份的是main数据库,如果不附加数据库,test.db文件中默认的只有main数据库。 命令操作举例:
sqlite> .backup mydb.bak
sqlite>
此时可以再F盘看到mydb.bak文件,用记事本打开虽然是乱码,但可窥见一斑。
.Restore还原
.restore ?DB? FILE 从备份文件还原数据和.backup相对应. 接上一步,我们可以ctrl+c结束命令,我们再建立一个新的库,来用mydb.bak文件还原,命令如下:
sqlite> ^C
F:\>sqlite3 test0.db
SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> ;
sqlite> .restore mydb.bak
sqlite>
验证数据如下:
sqlite> select * from websites;
1|CTOChina.net
2|搜狐
3|雅虎
sqlite>
为了下边的顺利进行,我们重新打开test.db数据:
sqlite> ^C
F:\>sqlite3 test.db
SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
终止语句
8、.bail ON|OFF 遇到错误是继续还是终止选项,默认是终止.
显示所有库
9、.databases 显示文件中数据库列表,如果没有附加数据库的情况下,文件中只有main数据库。如:
sqlite> .databases
seq name file
--- --------------- ----------------------------------------------
0 main F:\test.db
sqlite>
显示表结构
.dump ?TABLE? ... 以SQL语句方式显示表的结构。如:
sqlite> .dump websties
BEGIN TRANSACTION;
COMMIT;
sqlite> .dump websites
BEGIN TRANSACTION;
CREATE TABLE [websites] (
[WebID] INTEGER NOT NULL PRIMARY KEY,
[WebName] VARCHAR(20) NULL
);
INSERT INTO "websites" VALUES(1,'CTOChina.net');
INSERT INTO "websites" VALUES(2,'搜狐');
INSERT INTO "websites" VALUES(3,'雅虎');
COMMIT;
sqlite>
隐藏命令
11、.echo ON|OFF 是输出还是隐藏操作命令的命令,默认是隐藏, 显示命令:
sqlite> .echo on
sqlite> select * from websites;
select * from websites;
1|CTOChina.net
2|搜狐
3|雅虎
sqlite>
可以看出"select * from websites;"在和查询结果一起显示在屏幕上。
隐藏命令如:
sqlite> .echo off
sqlite> select * from websites;
1|CTOChina.net
2|搜狐
3|雅虎
sqlite>
退出程序
12、.exit 退出SQLite程序
输出格式
13、.explain ON|OFF
".explain"命令可以被用来设置输出格式为“column” 并设置列宽为EXPLAIN命令看起来比较合理的宽度。EXPLAIN命令是SQLite特有的SQL扩展,它是对调试有用。如果任何常规的SQL被EXPLAIN执行,那么SQL命令被分解并分析但并不执行。取而代之的是,虚拟机指令序列将被用于执行SQL命令并返回一个类似的查询结果。可结合.width命令查看效果如:
sqlite> .explain
sqlite> select * from websites;
WebI WebName
---- -------------
1 CTOChina.net
2 搜狐
3 雅虎
sqlite>
用.width设置宽度再看效果
sqlite> .width 10 20
sqlite> select * from websites;
WebID WebName
---------- --------------------
1 CTOChina.net
2 搜狐
3 雅虎
sqlite>
14、.header(s) ON|OFF 输出|隐藏字段名选项,默认是隐藏,如:
sqlite> select * from websites;
1|CTOChina.net
2|搜狐
3|雅虎
sqlite>
我们用.header命令把字段名显示出来,如:
sqlite> .header on
sqlite> select * from websites;
WebID|WebName
1|CTOChina.net
2|搜狐
3|雅虎
sqlite>
数据操作
15、 .import FILE TABLE 把文件中的数据导入到表中,各字段用separator(默认是"|")的值为分隔符,下面我们举个例子。 我们在F盘下建一个data.txt文件,内容如下:
4|开源
5|技术
16、.import命令操作如下:
sqlite> .import data.txt websites
sqlite>
查看结果如下:
sqlite> select * from websites;
WebID|WebName
1|CTOChina.net
2|搜狐
3|雅虎
4|开源
5|技术
sqlite>
我们已经看到
4|开源
5|技术
数据,已经导入websites表中了.....
17、.indices TABLE
“.indices”命令作用类似的方式是列出特定表的所有的索引。“.indices”命令须一个参数即所要索引表的表名。在此提醒下“.schema”命令,如果".schema"不带任何参数,“.schema”命令显示原始的用于创建当前数据库的CREATE TABLE和CREATE INDEX语句。如果你给".schema"命令一个表名,它显示原始的创建该表和它所有索引的CREATE语句。为了举例,我们先建个索引以作铺垫。在表websites的webID字段上建立一个名为mytestindex的索引,如下:
sqlite> create index mytestindex on websites([webID]);
sqlite>
18、.indices命令如下:
sqlite> .indices websites
mytestindex
sqlite>
19、.schema命令如下:
sqlite> .schema
CREATE TABLE [websites] (
[WebID] INTEGER NOT NULL PRIMARY KEY,
[WebName] VARCHAR(20) NULL
);
CREATE INDEX mytestindex on websites([webID]);
sqlite>
20、.load FILE ?ENTRY? 请补充?
21、.mode MODE ?TABLE? 输出格式选项
22、csv Comma-separated values 各字段以逗号为分隔符输出 如:
sqlite> .mode csv
sqlite> select * from websites;
WebID,WebName
1,CTOChina.net
2,"搜狐"
3,"雅虎"
4,"开源"
5,"技术"
sqlite>
23、column Left-aligned columns. 以.width设置的宽度显示各字段 如:
sqlite> .mode column
sqlite> select * from websites;
WebID WebName
---------- ------------
1 CTOChina.net
2 搜狐
3 雅虎
4 开源
5 技术
sqlite>
24、html HTML <table> code html 表格格式输出 如:
sqlite> .mode html
sqlite> select * from websites;
<TR><TH>WebID</TH><TH>WebName</TH></TR>
<TR><TD>1</TD>
<TD>CTOChina.net</TD>
</TR>
<TR><TD>2</TD>
<TD>搜狐</TD>
</TR>
<TR><TD>3</TD>
<TD>雅虎</TD>
</TR>
<TR><TD>4</TD>
<TD>开源</TD>
</TR>
<TR><TD>5</TD>
<TD>技术</TD>
</TR>
sqlite>
25、insert SQL insert statements for TABLE 以insert SQL语句形式输出 如:
sqlite> .mode insert
sqlite> select * from websites;
INSERT INTO sql VALUES(1,'CTOChina.net');
INSERT INTO sql VALUES(2,'搜狐');
INSERT INTO sql VALUES(3,'雅虎');
INSERT INTO sql VALUES(4,'开源');
INSERT INTO sql VALUES(5,'技术');
sqlite>
26、line One value per line field = value的形式逐行输出 如:
sqlite> .mode line
sqlite> select * from websites;
WebID = 1
WebName = CTOChina.net
WebID = 2
WebName = 搜狐
WebID = 3
WebName = 雅虎
WebID = 4
WebName = 开源
WebID = 5
WebName = 技术
sqlite>
27、list Values delimited by .separator string 各字段以separator的值为分隔符输出 如:
sqlite> .mode list
sqlite> select * from websites;
WebID,WebName
1,CTOChina.net
2,搜狐
3,雅虎
4,开源
5,技术
sqlite>
28、tabs Tab-separated values 如:
sqlite> .mode tabs
sqlite> select * from websites;
WebID WebName
1 CTOChina.net
2 搜狐
3 雅虎
4 开源
5 技术
sqlite>
29、tcl TCL list elements 如:
sqlite> .mode tcl
sqlite> select * from websites;
"WebID" "WebName"
"1" "CTOChina.net"
"2" "\313\321\272\374"
"3" "\321\305\273\242"
"4" "\277\252\324\264"
"5" "\274\274\312\365"
sqlite>
30、.nullvalue STRING 用STRING代替null值显示,不难理解,就不再累述了。
31、.output FILENAME 设置把查询输出到文件,后面的输出结果都保存到文件中,如:
sqlite> .mode list
sqlite> .output websites.txt
sqlite> select * from websites;
sqlite>
可以在F盘下发现建立了websites.txt文件,其内容如下:
1|CTOChina.net
2|搜狐
3|雅虎
4|开源
5|技术
33、.output stdout 设置把查询结果输出到屏幕,这是默认输出方式。如:
sqlite> .output stdout
sqlite> select * from websites;
1|CTOChina.net
2|搜狐
3|雅虎
4|开源
5|技术
sqlite>
34、.prompt MAIN CONTINUE Replace the standard prompts(修改提示符)请补充?
35、.quit 退出SQLite程序,同.exit命令
36、.read FILENAME Execute SQL in FILENAME 执行文件中的SQL语句,文件中的语句一定要带上分号(;).
我们在F盘下建sqldata.txt文件,其内容为:
insert into websites(WebName) values('测试插入数据0');
insert into websites(WebName) values('测试插入数据1');
select * from websites;
我们执行.read命令如下:
sqlite> .read sqldata.txt
1|CTOChina.net
2|搜狐
3|雅虎
4|开源
5|技术
6|测试插入数据0
7|测试插入数据1
sqlite>
37、.schema ?TABLE? Show the Create statements 以SQL格式输出表结构,如:
sqlite> .schema websites
CREATE TABLE [websites] (
[WebID] INTEGER NOT NULL PRIMARY KEY,
[WebName] VARCHAR(20) NULL
);
CREATE INDEX mytestindex on websites([webID]);
sqlite>
38、.separator STRING Change separator used by output mode and .import 修改分隔符,如:
sqlite> select * from websites limit 4;
1|CTOChina.net
2|搜狐
3|雅虎
4|开源
sqlite> .separator /
sqlite> select * from websites limit 4;
1/CTOChina.net
2/搜狐
3/雅虎
4/开源
sqlite>
配置信息
39. show Show the current values for various settings 显示配置信息,如:
sqlite> .show
echo: off
explain: off
headers: off
mode: list
nullvalue: ""
output: stdout
separator: "/"
width:
sqlite>
40、.tables ?PATTERN? List names of tables matching a LIKE pattern 显示库中表的列表。
41、.timeout MS Try opening locked tables for MS milliseconds 超时时间,单位:毫秒
42、.width NUM NUM ... Set column widths for "column" mode 设置列宽,举例见上文.explain命令。
脚本命令
SQLite的SQL语法
SQLite SQL语法请参考<<SQLIte中文文档.rar>>
SQLite库可以解析大部分标准SQL语言。但它也省去了一些特性 并且加入了一些自己的新特性。这篇文档就是试图描述那些SQLite支持/不支持的SQL语法的。 查看关键字列表。
如下语法表格中,纯文本用蓝色粗体显示。非终极符号为斜体红色。作为语法一部分的运算符用黑色Roman字体表示。
这篇文档只是对SQLite实现的SQL语法的综述,有所忽略。想要得到更详细的信息,参考源代码和语法文件 "parse.y".
SQLite执行如下的语法
- ALTER TABLE
- ANALYZE
- ATTACH DATABASE
- BEGIN TRANSACTION
- 注释
- COMMIT TRANSACTION
- COPY
- CREATE INDEX
- CREATE TABLE
- CREATE TRIGGER
- CREATE VIEW
- DELETE
- DETACH DATABASE
- DROP INDEX
- DROP TABLE
- DROP TRIGGER
- DROP VIEW
- END TRANSACTION
- EXPLAIN
- 表达式
- INSERT
- ON CONFLICT子句
- PRAGMA
- REINDEX
- REPLACE
- ROLLBACK TRANSACTION
- SELECT
- UPDATE
- VACUUM
ALTER TABLE
sql-statement ::= |
ALTER TABLE [database-name .] table-name alteration |
alteration ::= |
RENAME TO new-table-name |
alteration ::= |
ADD [COLUMN] column-def |
SQLite版本的的ALTER TABLE命令允许用户重命名或添加新的字段到已有表中,不能从表中删除字段。
RENAME TO语法用于重命名表名 [database-name.]table-name到new-table-name。这一命令不能用于在附加数据库之间移动表,只能在同一个数据库中对表进行重命名。
若需要重命名的表有触发器或索引,在重命名后它们依然属于该表。但若定义了视图,或触发器执行的语句中有提到表的名字,则它们不会被自动改为使用新的表名。若要进行这一类的修改,则需手工撤销并使用新的表名重建触发器或视图。
ADD [COLUMN]语法用于在已有表中添加新的字段。新字段总是添加到已有字段列表的末尾。 Column-def可以是CREATE TABLE中允许出现的任何形式,且须符合如下限制:
- 字段不能有主键或唯一约束。
- 字段不能有这些缺省值:CURRENT_TIME, CURRENT_DATE 或CURRENT_TIMESTAMP
- 若定义了NOT NULL约束,则字段必须有一个非空的缺省值。
ALTER TABLE语句的执行时间与表中的数据量无关,它在操作一个有一千万行的表时的运行时间与操作仅有一行的表时是一样的。
在对数据库运行ADD COLUMN之后,该数据库将无法由SQLite 3.1.3及更早版本读取,除非运行VACUUM命令。
ANALYZE
sql-statement ::= |
ANALYZE |
sql-statement ::= |
ANALYZE database-name |
sql-statement ::= |
ANALYZE [database-name .] table-name |
ANALYZE命令令集合关于索引的统计信息并将它们储存在数据库的一个特殊表中,查询优化器可以用该表来制作更好的索引选择。若不给出参数,所有附加数据库中的所有索引被分析。若参数给出数据库名,该数据库中的所有索引被分析。若给出表名作参数,则只有关联该表的索引被分析。
最初的实现将所有的统计信息储存在一个名叫sqlite_stat1的表中。未来的加强版本中可能会创建名字类似的其它表,只是把"1"改为其它数字。sqlite_stat1表不能够被撤销,但其中的所有内容可以被删除,这是与撤销该表等效的行为。
ATTACH DATABASE
ATTACH DATABASE语句将一个已存在的数据库添加到当前数据库连接。若文件名含标点符号,则应用引号引起来。数据库名’main’和’temp’代表主数据库和用于存放临时表的数据库,它们不能被拆分。拆分数据库使用DETACH DATABASE语句。
你可以读写附加数据库,或改变其结构。这是SQLite3.0提供的新特性。在SQLite 2.8中,改变附加数据库的结构是不允许的。
在附加数据库中添加一个与已有表同名的表是不允许的。但你可以附加带有与主数据库中的表同名的表的数据库。也可以多次附加同一数据库。
使用database-name.table-name来引用附加数据库中的表。若附加数据库中的表与主数据库的表不重名,则不需加数据库名作为前缀。当数据库被附加时,它的所有不重名的表成为该名字指向的缺省表。之后附加的任意与之同名的表需要加前缀。若“缺省”表被拆分,则最后附加的同名表变为“缺省”表。
若主数据库不是":memory:",多附加数据库的事务是原子的。若主数据库是":memory:"则事务在每个独立文件中依然是原子的。但若主机在改变两个或更多数据库的COMMIT语句进行时崩溃,则可能一部分文件被改变而其他的保持原样。附加数据库的原子性的提交是SQLite 3.0的新特性。在SQLite 2.8中,所有附加数据库的提交类似于主数据库是":memory:"时的情况。
对附加数据库的数目有编译时的限制,最多10个附加数据库。
BEGIN TRANSACTION
sql-statement ::= |
BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] [TRANSACTION [name]] |
sql-statement ::= |
END [TRANSACTION [name]] |
sql-statement ::= |
COMMIT [TRANSACTION [name]] |
sql-statement ::= |
ROLLBACK [TRANSACTION [name]] |
从2.0版开始,SQLite支持带有回退和原子性的提交的事务处理。
可选的事务名称会被忽略。SQLite目前不允许嵌套事务。
在事务之外,不能对数据库进行更改。如果当前没有有效的事务,任何修改数据库的命令(基本上除了SELECT以外的所有SQL命令)会自动启动一个事务。命令结束时,自动启动的事务会被提交。
可以使用BEGIN命令手动启动事务。这样启动的事务会在下一条COMMIT或ROLLBACK命令之前一直有效。但若数据库关闭或出现错误且选用ROLLBACK冲突判定算法时,数据库也会ROLLBACK。查看ON CONFLICT子句 获取更多关于ROLLBACK冲突判定算法的信息。
在SQLite 3.0.8或更高版本中,事务可以是延迟的,即时的或者独占的。“延迟的”即是说在数据库第一次被访问之前不获得锁。这样就会延迟事务,BEGIN语句本身不做任何事情。直到初次读取或访问数据库时才获取锁。对数据库的初次读取创建一个SHARED锁,初次写入创建一个RESERVED锁。由于锁的获取被延迟到第一次需要时,别的线程或进程可以在当前线程执行BEGIN语句之后创建另外的事务写入数据库。若事务是即时的,则执行BEGIN命令后立即获取RESERVED锁,而不等数据库被使用。在执行BEGIN IMMEDIATE之后, 你可以确保其它的线程或进程不能写入数据库或执行BEGIN IMMEDIATE或BEGIN EXCLUSIVE. 但其它进程可以读取数据库。 独占事务在所有的数据库获取EXCLUSIVE锁,在执行BEGIN EXCLUSIVE之后,你可以确保在当前事务结束前没有任何其它线程或进程 能够读写数据库。
有关SHARED, RESERVED, 和EXCLUSIVE锁可以参见这里。
SQLite 3.0.8的默认行为是创建延迟事务。SQLite 3.0.0 到3.0.7中延迟事务是唯一可用的事务类型。SQLite 2.8或更早版本中,所有的事务 都是独占的。
COMMIT命令在所有SQL命令完成之前并不作实际的提交工作。这样若两个或更多个SELECT语句在进程中间而执行COMMIT时,只有全部SELECT 语句结束才进行提交。
执行COMMIT可能会返回SQLITE_BUSY错误代码。这就是说有另外一个线程或进程获取了数据库的读取锁,并阻止数据库被改变。当COMMIT获得该错误代码时,事务依然是活动的,并且在COMMIT可以在当前读取的线程读取结束后再次试图读取数据库。
END TRANSACTION
sql-statement ::= |
BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] [TRANSACTION [name]] |
sql-statement ::= |
END [TRANSACTION [name]] |
sql-statement ::= |
COMMIT [TRANSACTION [name]] |
sql-statement ::= |
ROLLBACK [TRANSACTION [name]] |
从2.0版开始,SQLite支持带有回退和原子性的提交的事务处理。
可选的事务名称会被忽略。SQLite目前不允许嵌套事务。
在事务之外,不能对数据库进行更改。如果当前没有有效的事务,任何修改数据库的命令(基本上除了SELECT以外的所有SQL命令)会自动启动一个事务。命令结束时,自动启动的事务会被提交。
可以使用BEGIN命令手动启动事务。这样启动的事务会在下一条COMMIT或ROLLBACK命令之前一直有效。但若数据库关闭或出现错误且选用ROLLBACK冲突判定算法时,数据库也会ROLLBACK。查看ON CONFLICT子句获取更多关于ROLLBACK冲突判定算法的信息。
在SQLite 3.0.8或更高版本中,事务可以是延迟的,即时的或者独占的。“延迟的”即是说在数据库第一次被访问之前不获得锁。这样就会延迟事务,BEGIN语句本身不做任何事情。直到初次读取或访问数据库时才获取锁。对数据库的初次读取创建一个SHARED锁,初次写入创建一个RESERVED锁。由于锁的获取被延迟到第一次需要时,别的线程或进程可以在当前线程执行BEGIN语句之后创建另外的事务写入数据库。若事务是即时的,则执行BEGIN命令后立即获取RESERVED锁,而不等数据库被使用。在执行BEGIN IMMEDIATE之后,你可以确保其它的线程或进程不能写入数据库或执行BEGIN IMMEDIATE或BEGIN EXCLUSIVE. 但其它进程可以读取数据库。独占事务在所有的数据库获取EXCLUSIVE锁,在执行BEGIN EXCLUSIVE之后,你可以确保在当前事务结束前没有任何其它线程或进程能够读写数据库。
有关SHARED, RESERVED, 和EXCLUSIVE锁可以参见这里。
SQLite 3.0.8的默认行为是创建延迟事务。SQLite 3.0.0 到3.0.7中延迟事务是唯一可用的事务类型。SQLite 2.8或更早版本中,所有的事务都是独占的。
COMMIT命令在所有SQL命令完成之前并不作实际的提交工作。这样若两个或更多个SELECT语句在进程中间而执行COMMIT时,只有全部SELECT 语句结束才进行提交。
执行COMMIT可能会返回SQLITE_BUSY错误代码。这就是说有另外一个线程或进程获取了数据库的读取锁,并阻止数据库被改变。当COMMIT获得该错误代码时,事务依然是活动的,并且在COMMIT可以在当前读取的线程读取结束后再次试图读取数据库。
注释
comment ::= |
SQL-comment | C-comment |
SQL-comment ::= |
-- single-line |
C-comment ::= |
/* multiple-lines [*/] |
注释不是SQL命令,但会出现在SQL查询中。它们被解释器处理为空白部分。它们可以在任何空白可能存在的地方开始,即使是在跨越多行的表达式中。
SQL风格的注释仅对当前行有效。
C风格的注释可以跨越多行。若没有结束符号,注释的范围将一直延伸到输入末尾,且不会引起报错。新的SQL语句可以从多行注释结束的地方开始。C风格注释可以嵌入任何空白可以出现的地方,包括表达式内,或其他SQL语句中间,并且C风格的注释不互相嵌套。SQL风格的注释出现在C风格注释中时将被忽略。
COPY
sql-statement ::= |
COPY [ OR conflict-algorithm ] [database-name .] table-name FROM filename |
COPY命令在SQLite 2.8及更早的版本中可用。SQLite 3.0 删除了这一命令,因为在混合的UTF-8/16环境中对它进行支持是很复杂的。 在3.0版本中,命令行解释器包含新的.import命令,用以替代COPY。
COPY命令是用于将大量数据插入表的一个插件。它模仿PostgreSQL中的相同命令而来。事实上,SQLite的COPY 命令就是为了能够读取PostgreSQL的备份工具pg_dump的输出从而能够将PostgreSQL的数据轻松转换到SQLite中而设计的。
table-name是将要导入数据的一个已存在的表的名字。filename是一个字符串或标识符,用于说明作为数据来源的文件。 filename可以使用STDIN从标准输入流中获取数据。
输入文件的每一行被转换成一条单独的记录导入表中。字段用制表符分开。若某个字段的数据中出现制表符,则前面被添加反斜线""符号。数据中的反斜线则被替换为两条反斜线。可选的USING DELIMITERS子句可给出一个与制表符不同的分界符。
若字段由"\N"组成,则被赋以空值NULL.
使用这一命令时,利用可选的ON CONFLICT子句可以定义替代的约束冲突判定算法。更多信息,参见 ON CONFLICT。
当输入数据源是STDIN,输入将终止于一行仅包含一个反斜线和一个点的输入: "\.".
CREATE INDEX
sql-statement ::= |
CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database-name .] index-name |
column-name ::= |
name [ COLLATE collation-name] [ ASC | DESC ] |
CREATE INDEX命令由"CREATE INDEX"关键字后跟新索引的名字,关键字"ON",待索引表的名字,以及括弧内的用于索引键的字段列表构成。每个字段名可以跟随"ASC"或"DESC"关键字说明排序法则,但在当前版本中排序法则被忽略。排序总是按照上升序。
每个字段名后跟COLLATE子句定义文本记录的比较顺序。缺省的比较顺序是由CREATE TABLE语句说明的比较顺序。若不定义比较顺序,则使用内建的二进制比较顺序。
附加到单个表上的索引数目没有限制,索引中的字段数也没有限制。
若UNIQUE关键字出现在CREATE和INDEX之间,则不允许重名的索引记录。试图插入重名记录将会导致错误。
每条CREATE INDEX语句的文本储存于sqlite_master或sqlite_temp_master表中,取决于被索引的表是否临时表。每次打开数据库时,所有的CREATE INDEX语句从sqlite_master表中读出,产生SQLite的索引样式的内部结构。
若使用可选的IF NOT EXISTS子句,且存在同名索引,则该命令无效。
使用DROP INDEX命令删除索引。
CREATE TABLE
sql-command ::= |
CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] table-name ( |
sql-command ::= |
CREATE [TEMP | TEMPORARY] TABLE [database-name.] table-name AS select-statement |
column-def ::= |
name [type] [[CONSTRAINT name] column-constraint]* |
type ::= |
typename | |
column-constraint::= |
NOT NULL [ conflict-clause ] | |
constraint ::= |
PRIMARY KEY ( column-list ) [ conflict-clause ] | |
conflict-clause ::= |
ON CONFLICT conflict-algorithm |
CREATE TABLE语句基本上就是"CREATE TABLE"关键字后跟一个新的表名以及括号内的一堆 定义和约束。表名可以是字符串或者标识符。以"sqlite_"开头的表名是留给数据库引擎使用的。
每个字段的定义是字段名后跟字段的数据类型,接着是一个或多个的字段约束。字段的 数据类型并不限制字段中可以存放的数据。可以查看SQLite3的数据类型获取 更多信息。 UNIQUE约束为指定的字段创建索引,该索引须含有唯一键。COLLATE子句说明在比较字段的 文字记录时所使用的排序函数。缺省使用内嵌的BINARY排序函数。
DEFAULT约束说明在使用INSERT插入字段时所使用的缺省值。该值可以是NULL,字符串常量或一个数。从3.1.0版开始,缺省值也可以是以下特殊的与事件无关的关键字CURRENT_TIME, CURRENT_DATE或CURRENT_TIMESTAMP.若缺省值为NULL,字符串常量或数,在执行未指明字段值的INSERT语句的时候它被插入字段。若缺省值是CURRENT_TIME, CURRENT_DATE或CURRENT_TIMESTAMP,则当前UTC日期和/或时间被插入字段。CURRENT_TIME的格式为HH:MM:SS,CURRENT_DATE为YYYY-MM-DD,而CURRENT_TIMESTAMP是"YYYY-MM-DD HH:MM:SS".
正常情况下定义PRIMARY KEY只是在相应字段上创建一个UNIQUE索引。然而,若主键定义在单一的INTEGER类型的字段上,则该字段在内部被用作表的B-Tree键。这即是说字段仅能容纳唯一整数值。(在除此之外的其它情况下,SQLite忽略数据类型的说明,允许任何类型的数据放入字段中,不管该字段被声明为什么数据类型。) 若一个表中不含一个INTEGER PRIMARY KEY字段,则B-Tree键为自动产生的整数。一行的B-Tree键可以通过如下特殊的名字"ROWID", "OID", 或 "_ROWID_"进行访问,不论是否有INTEGER PRIMARY KEY存在。INTEGER PRIMARY KEY字段可以使用关键字AUTOINCREMENT声明。AUTOINCREMENT关键字修改了B-Tree键自动产生的方式。B-Tree键的生成的其它信息可以在这里找到。
若"TEMP"或"TEMPORARY"关键字出现在"CREATE"和"TABLE"之间,则所建立的表仅在当前数据库连接可见,并在断开连接时自动被删除。在临时表上建立的任何索引也是临时的。临时表和索引单独存储在与主数据库文件不同的文件中。
若说明了,则表在该数据库中被创建。同时声明和TEMP关键字会出错,除非是"temp".若不声明数据库名,也不使用TEMP关键字,则表创建于主数据库中。
在每个约束后跟可选的ON CONFLICT子句可以定义替代的约束冲突判定算法。缺省为ABORT。同一个表中的不同约束可以使用不同的缺省冲突判定算法。若一条COPY, INSERT, 或 UPDATE 命令指定了不同的冲突判定算法,则该算法将替代CREATE TABLE语句中说明的缺省算法。 更多信息,参见ON CONFLICT.
3.3.0版支持CHECK约束。在3.3.0之前,CHECK约束被解析但不执行。
表中的字段数或约束数没有任何限制。在2.8版中,单行数据的总数被限制为小于1 megabytes。而在3.0中则消除了限制。
CREATE TABLE AS形式定义表为一个查询的结果集。表的字段名字即是结果中的字段名字。
每条CREATE TABLE语句的文本都储存在sqlite_master表中。每当数据库被打开,所有的CREATE TABLE语句从sqlite_master表中读出,构成表结构的SQLite内部实现。若原始命令为CREATE TABLE AS则合成出等效的 CREATE TABLE语句并储存于sqlite_master表中代替原命令。CREATE TEMPORARY TABLE语句文本储存于sqlite_temp_master表中。
若在命令中使用可选的IF NOT EXISTS子句且存在同名的另一个表,则当前的命令无效。
删除表可以使用DROP TABLE语句。
CREATE TRIGGER
sql-statement ::= |
CREATE [TEMP | TEMPORARY] TRIGGER trigger-name [ BEFORE | AFTER ] |
sql-statement ::= |
CREATE [TEMP | TEMPORARY] TRIGGER trigger-name INSTEAD OF |
database-event ::= |
DELETE | |
trigger-action ::= |
[ FOR EACH ROW | FOR EACH STATEMENT ] [ WHEN expression ] |
trigger-step ::= |
update-statement | insert-statement | |
CREATE TRIGGER语句用于向数据库schema中添加触发器。触发器是一些在特定的数据库事件(database-event) 发生时自动进行的数据库操作(trigger-action).
触发器可由在特殊表上执行的DELETE, INSERT, UPDATE等语句触发,或UPDATE表中特定的字段时触发。
现在SQLite仅支持FOR EACH ROW触发器,不支持FOR EACH STATEMENT触发。因此可以不用明确说明FOR EACH ROW .FOR EACH ROW的意思是由trigger-steps说明的SQL语句可能在(由WHEN子句决定的)数据库插入,更改或删除的每一行触发trigger.
WHEN子句和trigger-steps可以使用"NEW.column-name"和"OLD.column-name"的引用形式访问正在被插入,更改或删除的行的元素,column-name是触发器关联的表中的字段名。OLD 和 NEW 引用只在触发器与之相关的trigger-event处可用,例如:
INSERT |
NEW可用 |
UPDATE |
NEW和OLD均可用 |
DELETE |
OLD可用 |
当使用WHEN子句,trigger-steps只在WHEN子句为真的行执行。不使用WHEN时则在所有行执行。
trigger-time决定了trigger-steps执行的时间,它是相对于关联行的插入,删除和修改而言的。
作为的一部分trigger-step的UPDATE 或 INSERT可以使用ON CONFLICT子句。但若触发trigger的语句使用了ON CONFLICT子句,则覆盖前述的ON CONFLICT子句所定义的冲突处理方法。
关联表被撤销时触发器被自动删除。
不仅在表上,在视图上一样可以创建触发器,在CREATE TRIGGER语句中使用INSTEAD OF即可。若视图上定义了一个或多个ON INSERT, ON DELETE, ON UPDATE触发器,则相应地对视图执行INSERT,DELETE 或UPDATE语句不会出错,而会触发关联的触发器。视图关联的表不会被修改。(除了由触发器进行的修改操作)。
Example:
假设"customers"表存储了客户信息,"orders"表存储了订单信息,下面的触发器确保当用户改变地址时所有的关联订单地址均进行相应改变:
CREATE TRIGGER update_customer_address UPDATE OF address ON customers BEGIN UPDATE orders SET address = new.address WHERE customer_name = old.name; END;
定义了该触发器后执行如下语句:
UPDATE customers SET address = ’1 Main St.’ WHERE name = ’Jack Jones’;
会使下面的语句自动执行:
UPDATE orders SET address = ’1 Main St.’ WHERE customer_name = ’Jack Jones’;
注意,目前在有INTEGER PRIMARY KEY域的表上触发器可能工作不正常。若BEFORE触发器修改了一行的 INTEGER PRIMARY KEY域,而该域将由触发该触发器的语句进行修改,则可能根本不会修改该域。 可以用PRIMARY KEY字段代替INTEGER PRIMARY KEY字段来解决上述问题。
一个特殊的SQL函数RAISE()可用于触发器程序,使用如下语法:
raise-function ::= |
RAISE ( ABORT, error-message ) | |
当触发器程序执行中调用了上述前三个之一的形式时,则执行指定的ON CONFLICT进程(ABORT, FAIL或者ROLLBACK) 且终止当前查询,返回一个SQLITE_CONSTRAINT错误并说明错误信息。
当调用RAISE(IGNORE),当前触发器程序的余下部分,触发该触发器的语句和任何之后的触发器程序被忽略并且不恢复对数据库的已有改变。 若触发触发器的语句是一个触发器程序本身的一部分,则原触发器程序从下一步起继续执行。
使用DROP TRIGGER删除触发器。
CREATE VIEW
sql-command ::= |
CREATE [TEMP | TEMPORARY] VIEW [database-name.] view-name AS select-statement |
CREATE VIEW命令为一个包装好的SELECT语句命名。当创建了一个视图,它可以用于其他SELECT的FROM字句中代替表名。
若"TEMP"或"TEMPORARY"关键字出现在"CREATE"和"VIEW"之间,则创建的视图仅对打开数据库的进程可见,且在数据库关闭时自动删除。
若指定了则视图在指定的数据库中创建。同时使用和TEMP关键字会导致错误,除非是 "temp".若不声明数据库名,也不使用TEMP关键字,则视图创建于主数据库中。
你不能对视图使用COPY, DELETE, INSERT 或 UPDATE,视图在SQLite中是只读的。多数情况下你可以在视图上创建TRIGGER来达到相同目的。用DROP VIEW 命令来删除视图。
DELETE
sql-statement ::= |
DELETE FROM [database-name .] table-name [WHERE expr] |
DELETE命令用于从表中删除记录。命令包含"DELETE FROM"关键字以及需要删除的记录所在的表名。
若不使用WHERE子句,表中的所有行将全部被删除。否则仅删除符合条件的行。
DETACH DATABASE
sql-command ::= |
DETACH [DATABASE] database-name |
该语句拆分一个之前使用ATTACH DATABASE语句附加的数据库连接。可以使用不同的名字多次附加同一数据库,并且拆分一个连接不会影响其他连接。
若SQLite在事务进行中,该语句不起作用。
DROP INDEX
sql-command ::= |
DROP INDEX [IF EXISTS] [database-name .] index-name |
DROP INDEX语句删除由CREATE INDEX 语句创建的索引。索引将从数据库结构和磁盘文件中完全删除,唯一的恢复方法是重新输入相应的CREATE INDEX命令。
DROP TABLE语句在缺省模式下不减小数据库文件的大小。空间会留给后来的INSERT语句使用。要释放删除产生的空间,可以使用VACUUM命令。若AUTOVACUUM模式开启,则空间会自动被DROP INDEX释放。
DROP TABLE
sql-command ::= |
DROP TABLE [IF EXISTS] [database-name.] table-name |
DROP TABLE语句删除由 CREATE TABLE语句创建的表。表将从数据库结构和磁盘文件中完全删除,且不能恢复。该表的所有索引也同时被删除。
DROP TABLE语句在缺省模式下不减小数据库文件的大小。空间会留给后来的INSERT语句使用。要释放删除产生的空间,可以使用 VACUUM 命令。若AUTOVACUUM模式开启,则空间会自动被DROP TABLE释放。
若使用可选的IF EXISTS子句,在删除的表不存在时就不会报错。
DROP TRIGGER
sql-statement ::= |
DROP TRIGGER [database-name .] trigger-name |
DROP TRIGGER语句删除由 CREATE TRIGGER创建的触发器。触发器从数据库的schema中删除。注意当关联的表被撤消时触发器自动被删除。
DROP VIEW
sql-command ::= |
DROP VIEW view-name |
DROP VIEW语句删除由CREATE VIEW 创建的视图。视图从数据库的schema中删除,表中的数据不会被更改。
EXPLAIN
sql-statement ::= |
EXPLAIN sql-statement |
EXPLAIN命令修饰语是一个非标准的扩展功能,灵感来自PostgreSQL中的相同命令,但操作完全不同。
若EXPLAIN关键字出现在任何SQLite SQL命令之前,则SQLite库返回不加EXPLAIN时执行该命令所需要使用的虚拟机指令序列,而不是真正执行该命令。关于虚拟机指令的更多信息参见系统结构描述或关于虚拟机的可用代码。
表达式
expr ::= |
expr binary-op expr | |
like-op ::= |
LIKE | GLOB | REGEXP |
这一节与其它的各节有所不同。我们讨论的不是一个单一的SQL命令,而是作为其他大部分命令的一部分的表达式。
SQLite支持如下的二元运算符,按优先级由高至低排列:
|| * / % + - << >> & | < <= > >= = == != <> IN AND OR
所支持的一元运算符:
- + ! ~
注意等号和“不等”号的两个变种。等号可以是 =或==. “不等”号可以是!=或<>. ||为“连接符”——它将两个字符串连接起来。 %输出左边部分以右边部分为模取模得到的余数。
二元运算符的结果均为数字,除了||连接符,它给出字符串结果。
文本值(literal value)是一个整数或浮点数。可以使用科学计数法。"."符号总是被当作小数点即使本地设定中用","来表示小数点 ——用","表示小数点会造成歧义。字符串常量由字符串加单引号(')构成。字符串内部的单引号可像Pascal 中一样用两个单引号来表示。C风格的加反斜线的表示法由于不是标准SQL而不被支持。 BLOB文本是以"x"或"X"开头的含有十六进制文本信息的文本值 For example:
X'53514697465'
文本值同样可以为"NULL".
表达式中插入文本值占位符的参数可以使用 sqlite3_bind API函数在运行时插入。参数可以是如下几种形式:
?NNN |
问号跟随数字NNN为第NNN个参数占位。NNN需介于1和999之间。 |
|
? |
不加数字的问号为下一个参数占位。 |
|
:AAAA |
冒号带一个标识符名称为一个名为AAAA的参数占位。命名的参数同样可以使用序号占位,被赋予的参数序号为下一个尚未被使用的序号。建议不要混合使用命名代表的参数和序号代表的参数以免引起混淆。 |
|
$AAAA |
$符号带一个标识符名称也可以为一个名为AAAA的参数占位。在这一情况下标识符名称可以包括一个或更多的"::" 以及包含任何文本的"(...)"后缀。该语法是Tcl编程语言中的一个可变形式。 |
不使用sqlite3_bind赋值的参数被视为NULL.
LIKE运算符进行模式匹配比较。运算符右边为进行匹配的模式而左边为需进行匹配的字符串。 模式中的百分号%匹配结果中的零或任意多个字符。下划线 _匹配任意单个字符。其他的任意字符匹配本身或等同的大/小写字符。 (即不区分大小写的匹配)。 (一个bug: SQLite仅对7-bit拉丁字符支持不区分大小写匹配。这是由于 LIKE运算符对8-bit iso8859字符或UTF-8字符是大小写敏感的。例如,表达式'a' LIKE 'A' 的值为真而'æ' LIKE 'Æ'为假)。
如果使用可选的ESCAPE子句,则跟随ESCAPE关键字的必须是一个有一个字符的字符串。这一字符(逃逸字符)可用于LIKE模式中,以代替百分号或下划线。逃逸字符后跟百分号,下划线或它本身代表字符串中的百分号,下划线或逃逸字符。插入的LIKE运算符功能通过调用用户函数 like(X,Y)来实现。
当使用可选的ESCAPE子句,它对函数给出第三个参数,LIKE的功能可以通过重载SQL函数like()进行改变。
GLOB运算符与LIKE相似,但它使用Unix文件 globbing 语法作为通配符。还有一点不同是GLOB对大小写敏感。 GLOB和LIKE都可以前缀NOT关键字构成相反的意思。插入的GLOB运算符功能通过调用用户函数 glob(X,Y) 可以通过重载函数改变GLOB的功能。
REGEXP运算符是用户函数regexp()的一个特殊的代表符号。缺省情况下regexp()函数不被定义,所以使用REGEXP运算符会报错。当运行时存在用户定义的"regexp"函数的定义,则调用该函数以实现REGEXP运算符功能。
字段名可以是CREATE TABLE语句定义的任何名字或如下几个特殊标识符之一"ROWID", "OID", 以及"_ROWID_".这些特殊标识符均代表每个表每一行关联的那个唯一随机整数键("row key")。仅仅在CREATE TABLE语句没有对这些特殊标识符的真实字段予以定义的情况下,它们才代表"row key"。它们与只读字段类似,可以像任何正常字段一样使用,除了在UPDATE或INSERT语句中(即是说你不能添加或更改row key)。 "SELECT * ..."不返回row key.
SELECT语句可以在表达式中出现,作为IN运算符的右边运算量,作为一个纯量,或作为EXISTS运算符的运算量。当作纯量或IN的运算量时,SELECT语句的结果仅允许有一个字段,可以使用复合的SELECT(用UNION或 EXCEPT等关键字连接)。作为EXISTS运算符的运算量时,SELECT结果中的字段被忽略,在结果为空时表达式为假,反之为真。若SELECT表达式代表的查询中不含有引用值的部分,则它将在处理其它事务之前被计算,并且结果在必要时会被重复使用。若SELECT表达式含从其它查询中得到的变量,在每一次使用时该表达式均被重新计算。
当SELECT作为IN运算符的右运算量,在左边的运算量是SELECT产生的任意一个值时,表达式返回TRUE。IN运算符前可以加NOT 构成相反的意思。
当SELECT与表达式一同出现且不在IN的右边,则SELECT结果的第一行作为表达式中使用的值。SELECT返回的结果在第一行以后的部分被忽略。返回结果为空时SELECT语句的值为NULL.
CAST表达式将的数据类型改为声明的类型。 可以是CREATE TABLE语句字段定义部分定义的对该字段有效的任意非空数据类型。
表达式支持简单函数和聚集函数。简单函数直接从输入获得结果,可用于任何表达式中。聚集函数使用结果集中的所有行计算结果,仅用于SELECT语句中。
T下面这些函数是缺省可用的。可以使用C语言写出其它的函数然后使用sqlite3_create_function() API函数添加到数据库引擎中。
abs(X) |
返回参数X的绝对值。 |
coalesce(X,Y,...) |
返回第一个非空参数的副本。若所有的参数均为NULL,返回NULL。至少2个参数。 |
glob(X,Y) |
用于实现SQLite的 "X GLOB Y"语法。可使用 sqlite3_create_function() 重载该函数从而改变GLOB运算符的功能。 |
ifnull(X,Y) |
返回第一个非空参数的副本。 若两个参数均为NULL,返回NULL。与上面的 coalesce()类似。 |
last_insert_rowid() |
返回当前数据库连接最后插入行的ROWID。sqlite_last_insert_rowid() API函数同样可用于得到该值。 |
length(X) |
返回X的长度,以字符计。如果SQLite被配置为支持UTF-8,则返回UTF-8字符数而不是字节数。 |
like(X,Y [,Z]) |
用于实现SQL语法"X LIKE Y [ESCAPE Z]".若使用可选的ESCAPE子句,则函数被赋予三个参数,否则只有两个。可使用sqlite3_create_function() 重载该函数从而改变LIKE运算符的功能。 |
lower(X) |
返回X字符串的所有字符小写化版本。这一转换使用C语言库的tolower()函数,对UTF-8字符不能提供好的支持。 |
max(X,Y,...) |
返回最大值。参数可以不仅仅为数字,可以为字符串。大小顺序由常用的排序法则决定。注意,max()在有2个或更多参数时为简单函数,但当仅给出一个参数时它变为聚集函数。 |
min(X,Y,...) |
返回最小值。与max()类似。 |
nullif(X,Y) |
当两参数不同时返回X,否则返回NULL. |
quote(X) |
返回参数的适于插入其它SQL语句中的值。字符串会被添加单引号,在内部的引号前会加入逃逸符号。 BLOB被编码为十六进制文本。当前的VACUUM使用这一函数实现。在使用触发器实现撤销/重做功能时这一函数也很有用。 |
random(*) |
返回介于-2147483648和 +2147483647之间的随机整数。 |
round(X) |
将X四舍五入,保留小数点后Y位。若忽略Y参数,则默认其为0。 |
soundex(X) |
计算字符串X的soundex编码。参数为NULL时返回字符串"?000".缺省的SQLite是不支持该函数的,当编译时选项 -DSQLITE_SOUNDEX=1 时该函数才可用。 |
sqlite_version(*) |
返回所运行的SQLite库的版本号字符串。如 "2.8.0"。 |
substr(X,Y,Z) |
返回输入字符串X中以第Y个字符开始,Z个字符长的子串。 X最左端的字符序号为1。若Y为负,则从右至左数起。若SQLite配置支持UTF-8,则“字符”代表的是UTF-8字符而非字节。 |
typeof(X) |
返回表达式X的类型。返回值可能为"null", "integer", "real", "text", 以及 "blob". SQLite的类型处理参见SQLite3的数据类型. |
upper(X) |
返回X字符串的所有字符大写化版本。这一转换使用C语言库的toupper()函数,对UTF-8字符不能提供好的支持。 |
以下是缺省可用的聚集函数列表。可以使用C语言写出其它的聚集函数然后使用sqlite3_create_function() API函数添加到数据库引擎中。
在单参数聚集函数中,参数可以加前缀DISTINCT。这时重复参数会被过滤掉,然后才穿入到函数中。例如,函数"count(distinct X)"返回字段X的不重复非空值的个数,而不是字段X的全部非空值。
avg(X) |
返回一组中非空的X的平均值。非数字值作0处理。avg()的结果总是一个浮点数,即使所有的输入变量都是整数。
|
count(X) |
返回一组中X是非空值的次数的第一种形式。第二种形式(不带参数)返回该组中的行数。 |
max(X) |
返回一组中的最大值。大小由常用排序法决定。 |
min(X) |
返回一组中最小的非空值。大小由常用排序法决定。仅在所有值为空时返回NULL。 |
sum(X) |
返回一组中所有非空值的数字和。若没有非空行,sum()返回NULL而total()返回0.0. NULL通常情况下并不是对于“没有行”的和的一个有意义的结果,但SQL标准如此要求,且大部分其它SQL数据库引擎这样定义sum(),所以SQLite 也如此定义以保证兼容。我们提供非标准的total()函数作为解决该SQL语言设计问题的一个简易方法。
total()的返回值式中为浮点数。sum()可以为整数,当所有非空输入均为整数时,和是精确的。若sum()的任意一个输入既非整数也非NULL或计算中产生整数类型的溢出时,sum()返回接近真和的浮点数。 |
INSERT
sql-statement::= |
INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)]VALUES(value-list) | |
INSERT语句有两种基本形式。一种带有"VALUES"关键字,在已有表中插入一个新的行。若不定义字段列表,那么值的数目将与表中的字段数目相同。否则值的数目须与字段列表中的字段数目相同。不在字段列表中的字段被赋予缺省值或NULL(当未定义缺省值)。
INSERT的第二种形式从SELECT语句中获取数据。若未定义字段列表,则从SELECT得到的字段的数目必须与表中的字段数目相同,否则应与定义的字段列表中的字段数目相同。SELECT的每一行结果在表中插入一个新的条目。SELECT可以是简单的或者复合的。如果SELECT语句带有ORDER BY子句,ORDER BY会被忽略。
在使用这一命令时,利用可选的ON CONFLICT子句可以定义替代的约束冲突判定算法。更多信息,参见 ON CONFLICT 。为了兼容MySQL,可以使用REPLACE代替"INSERT OR REPLACE".
ON CONFLICT子句
conflict-clause ::= |
ON CONFLICT conflict-algorithm |
conflict-algorithm ::= |
ROLLBACK | ABORT | FAIL | IGNORE | REPLACE |
ON CONFLICT子句不是独立的SQL命令。这是一条可以出现在许多其他SQL命令中的非标准的子句。由于它并不是标准的SQL语言,这里单独介绍它。
ON CONFLICT子句的语法在如上的CREATE TABLE命令中示出。对于INSERT和UPDATE,关键词"ON CONFLICT"由"OR"替代,这样语法显得自然。例如,不用写"INSERT ON CONFLICT IGNORE"而是"INSERT OR IGNORE".二者表示相同的意思。
ON CONFLICT子句定义了解决约束冲突的算法。有五个选择:ROLLBACK, ABORT, FAIL, IGNORE, 和REPLACE.缺省方案是ABORT.选项含义如下:
ROLLBACK
当发生约束冲突,立即ROLLBACK,即结束当前事务处理,命令中止并返回SQLITE_CONSTRAINT代码。若当前无活动事务(除了每一条命令创建的默认事务以外),则该算法与ABORT相同。
ABORT
当发生约束冲突,命令收回已经引起的改变并中止返回SQLITE_CONSTRAINT。但由于不执行ROLLBACK,所以前面的命令产生的改变将予以保留。缺省采用这一行为。
FAIL
当发生约束冲突,命令中止返回SQLITE_CONSTRAINT。但遇到冲突之前的所有改变将被保留。例如,若一条UPDATE语句在100行遇到冲突100th,前99行的改变将被保留,而对100行或以后的改变将不会发生。
IGNORE
当发生约束冲突,发生冲突的行将不会被插入或改变。但命令将照常执行。在冲突行之前或之后的行将被正常的插入和改变,且不返回错误信息。
REPLACE
当发生UNIQUE约束冲突,先存在的,导致冲突的行在更改或插入发生冲突的行之前被删除。这样,更改和插入总是被执行。命令照常执行且不返回错误信息。当发生NOT NULL约束冲突,导致冲突的NULL值会被字段缺省值取代。若字段无缺省值,执行ABORT算法。
当冲突应对策略为满足约束而删除行时,它不会调用删除触发器。但在新版中这一特性可能被改变。
INSERT或UPDATE的OR子句定义的算法会覆盖CREATE TABLE所定义的。ABORT算法将在没有定义任何算法时缺省使用。
SQLite支持的编译指令(pragma)
PRAGMA命令是用于修改SQlite库或查询SQLite库内部数据(non-table)的特殊命令。PRAGMA 命令使用与其它SQLite命令(e.g. SELECT, INSERT)相同的接口,但在如下重要方面与其它命令不同:
- 在未来的SQLite版本中部分pragma可能被删除或添加,小心使用。
- 当使用未知的pragma语句时不产生报错。未知的pragma仅仅会被忽略,即是说若是打错了pragma语句SQLite不会提示用户。
- 一些pragma在SQL编译阶段生效而非执行阶段。即是说若使用C语言的sqlite3_compile(), sqlite3_step(), sqlite3_finalize() API (或类似的封装接口中),pragma可能在调用sqlite3_compile()期间起作用。
- pragma命令不与其它SQL引擎兼容。
可用的pragma命令有如下四个基本类型:
- 用于察看当前数据库的模式。
- 用于修改SQLite库的操作或查询当前的操作模式。
- 用于查询或修改两个数据库的版本号,schema-version和user-version.
- 用于调试库和校验数据库文件。
PRAGMA命令语法
sql-statement ::= |
PRAGMA name [= value] | |
使用整数值value的pragma也可以使用符号表示,字符串"on", "true",和 "yes" 等同于1,"off", "false",和 "no"等同于0. 这些字符串大小写不敏感且无须进行引用。无法识别的字符串被当作1且不会报错。value返回时是整数。
用于修改SQLite库的操作的Pragma
- PRAGMA auto_vacuum;
PRAGMA auto_vacuum = 0 | 1;
查询或设置数据库的auto-vacuum标记。
正常情况下,当提交一个从数据库中删除数据的事务时,数据库文件不改变大小。未使用的文件页被标记并在以后的添加操作中再次使用。这种情况下使用VACUUM命令释放删除得到的空间。
当开启auto-vacuum,当提交一个从数据库中删除数据的事务时,数据库文件自动收缩, (VACUUM命令在auto-vacuum开启的数据库中不起作用)。数据库会在内部存储一些信息以便支持这一功能,这使得数据库文件比不开启该选项时稍微大一些。
只有在数据库中未建任何表时才能改变auto-vacuum标记。试图在已有表的情况下修改不会导致报错。
- PRAGMA cache_size;
PRAGMA cache_size = Number-of-pages;
查询或修改SQLite一次存储在内存中的数据库文件页数。每页使用约1.5K内存,缺省的缓存大小是2000. 若需要使用改变大量多行的UPDATE或DELETE命令,并且不介意SQLite使用更多的内存的话,可以增大缓存以提高性能。
当使用cache_size pragma改变缓存大小时,改变仅对当前对话有效,当数据库关闭重新打开时缓存大小恢复到缺省大小。要想永久改变缓存大小,使用default_cache_size pragma.
- PRAGMA case_sensitive_like;
PRAGMA case_sensitive_like = 0 | 1;
LIKE运算符的缺省行为是忽略latin1字符的大小写。因此在缺省情况下'a' LIKE 'A'的值为真。可以通过打开 case_sensitive_like pragma来改变这一缺省行为。当启用case_sensitive_like,'a' LIKE 'A'为假而 'a' LIKE 'a'依然为真。
- PRAGMA count_changes;
PRAGMA count_changes = 0 | 1;
查询或更改count-changes标记。正常情况下INSERT, UPDATE和DELETE语句不返回数据。当开启count-changes,以上语句返回一行含一个整数值的数据——该语句插入,修改或删除的行数。返回的行数不包括由触发器产生的插入,修改或删除等改变的行数。
- PRAGMA default_cache_size;
PRAGMA default_cache_size = Number-of-pages;
查询或修改SQLite一次存储在内存中的数据库文件页数。每页使用约1.5K内存,它与 cache_sizepragma类似,只是它永久性地改变缓存大小。利用该pragma,你可以设定一次缓存大小,并且每次重新打开数据库时都继续使用该值。
- PRAGMA default_synchronous;
该语句在2.8版本中可用,但在3.0版中被去掉了。这条pragma很危险且不推荐使用,安全起见在该文档中不涉及此pragma的用法。
- PRAGMA empty_result_callbacks;
PRAGMA empty_result_callbacks = 0 | 1;
查询或更改empty-result-callbacks标记。
empty-result-callbacks标记仅仅影响sqlite3_exec API函数。正常情况下,empty-result-callbacks标记清空,则对返回0行数据的命令不调用sqlite3_exec()的回叫函数,当设置了empty-result-callbacks,则调用回叫函数一次,置第三个参数为0 (NULL).这使得使用sqlite3_exec() API的程序即使在一条查询不返回数据时依然检索字段名。
- PRAGMA encoding;
PRAGMA encoding = "UTF-8";
PRAGMA encoding = "UTF-16";
PRAGMA encoding = "UTF-16le";
PRAGMA encoding = "UTF-16be";
在第一种形式中,若主数据库已创建,这条pragma返回主数据库使用得文本编码格式,为 "UTF-8", "UTF-16le" (little-endian UTF-16 encoding) 或者"UTF-16be" (big-endian UTF-16 encoding)中的一种。 若主数据库未创建,返回值为当前会话创建的主数据库将要使用的文本编码格式。
第二种及以后几种形式只在主数据库未创建时有效。这时该pragma设置当前会话创建的主数据库将要使用的文本编码格式。 "UTF-16"表示"使用本机字节顺序的UTF-16编码"。若这些形式在主数据库创建后使用,将被忽略且不产生任何效果。
数据库的编码格式设置后不能够被改变。
ATTACH命令创建的数据库使用与主数据库相同的编码格式。
- PRAGMA full_column_names;
PRAGMA full_column_names = 0 | 1;
查询或更改the full-column-names标记。该标记影响SQLite命名SELECT语句(当字段表达式为表-字段或通配符"*"时) 返回的字段名的方式。正常情况下,当SELECT语句将两个或多个表连接时, 这类结果字段的返回名为,当SELECT语句查询一个单独的表时, 返回字段名为。当设置了full-column-names标记,返回的字段名将统一为不管是否对表进行了连接。
若short-column-names和full-column-names标记同时被设置,则使用full-column-names方式。
- PRAGMA fullfsync
PRAGMA fullfsync = 0 | 1;
查询或更改fullfsync标记。该标记决定是否在支持的系统上使用F_FULLFSYNC同步模式。缺省值为off.截至目前(2006-02-10) 只有Mac OS X 系统支持F_FULLFSYNC.
- PRAGMA page_size;
PRAGMA page_size = bytes;
查询或设置page-size值。只有在未创建数据库时才能设置page-size。页面大小必须是2的整数倍且大于等于512小于等于8192。上限可以通过在编译时修改宏定义SQLITE_MAX_PAGE_SIZE的值来改变。上限的上限是32768.
- PRAGMA read_uncommitted;
PRAGMA read_uncommitted = 0 | 1;
查询,设置或清除READ UNCOMMITTED isolation(读取未授权的分隔符).缺省的SQLite分隔符等级是SERIALIZABLE. 任何线程或进程可选用READ UNCOMMITTED isolation,但除了共享公共页和schema缓存的连接之间以外的地方也会使用SERIALIZABLE.缓存共享通过 sqlite3_enable_shared_cache() API开启,且只在运行同一线程的连接间有效。缺省情况下缓存共享是关闭的。
- PRAGMA short_column_names;
PRAGMA short_column_names = 0 | 1;
查询或更改the short-column-names标记。该标记影响SQLite命名SELECT语句(当字段表达式为表-字段或通配符"*"时) 返回的字段名的方式。正常情况下,当SELECT语句将两个或多个表连接时, 这类结果字段的返回名为,当SELECT语句查询一个单独的表时, 返回字段名为。当设置了full-column-names标记,返回的字段名将统一为不管是否对表进行了连接。
若short-column-names和full-column-names标记同时被设置,则使用full-column-names方式。
- PRAGMA synchronous;
PRAGMA synchronous = FULL; (2)
PRAGMA synchronous = NORMAL; (1)
PRAGMA synchronous = OFF; (0)
查询或更改"synchronous"标记的设定。第一种形式(查询)返回整数值。 当synchronous设置为FULL (2), SQLite数据库引擎在紧急时刻会暂停以确定数据已经写入磁盘。 这使系统崩溃或电源出问题时能确保数据库在重起后不会损坏。FULL synchronous很安全但很慢。 当synchronous设置为NORMAL, SQLite数据库引擎在大部分紧急时刻会暂停,但不像FULL模式下那么频繁。 NORMAL模式下有很小的几率(但不是不存在)发生电源故障导致数据库损坏的情况。但实际上,在这种情况下很可能你的硬盘已经不能使用,或者发生了其他的不可恢复的硬件错误。 设置为synchronous OFF (0)时,SQLite在传递数据给系统以后直接继续而不暂停。若运行SQLite的应用程序崩溃,数据不会损伤,但在系统崩溃或写入数据时意外断电的情况下数据库可能会损坏。另一方面,在synchronous OFF时一些操作可能会快50倍甚至更多。
在SQLite 2中,缺省值为NORMAL.而在3中修改为FULL.
- PRAGMA temp_store;
PRAGMA temp_store = DEFAULT; (0)
PRAGMA temp_store = FILE; (1)
PRAGMA temp_store = MEMORY; (2)
查询或更改"temp_store"参数的设置。当temp_store设置为DEFAULT (0),使用编译时的C预处理宏 TEMP_STORE来定义储存临时表和临时索引的位置。当设置为MEMORY (2)临时表和索引存放于内存中。 当设置为FILE (1)则存放于文件中。temp_store_directory pragma 可用于指定存放该文件的目录。当改变temp_store设置,所有已存在的临时表,索引,触发器及视图将被立即删除。
库中的编译时C预处理标志TEMP_STORE可以覆盖该pragma设置。下面的表给出TEMP_STORE预处理宏和 temp_store pragma交互作用的总结:
TEMP_STORE |
PRAGMA |
临时表和索引 |
0 |
any |
文件 |
1 |
0 |
文件 |
1 |
1 |
文件 |
1 |
2 |
内存 |
2 |
0 |
内存 |
2 |
1 |
文件 |
2 |
2 |
内存 |
3 |
any |
内存 |
- PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory-name';
查询或更改"temp_store_directory"设置——存储临时表和索引的文件所在的目录。仅在当前连接有效,在建立新连接时重置为缺省值。
当改变了temp_store_directory设置,所有已有的临时表,索引,触发器,视图会被直接删除。建议在数据库一打开时就设置好temp_store_directory.
directory-name需用单引号引起来。要想恢复缺省目录,把directory-name设为空字符串。例如 PRAGMA temp_store_directory = ''.若directory-name未找到或不可写会引发错误。
临时文件的缺省目录与主机的系统有关,使用Unix/Linux/OSX系统的主机,缺省目录是如下序列之中第一个可写的 /var/tmp, /usr/tmp, /tmp,current-directory.对于Windows NT,缺省目录由Windows决定,一般为 C:\Documents and Settings\user-name\Local Settings\Temp\. SQLite创建的临时文件在使用完毕时就被unlink,所以操作系统可以在SQLite进程进行中自动删除临时文件。于是,正常情况下不能通过ls 或 dir命令看到临时文件。
用于查询数据库的schema的Pragma
- PRAGMA database_list;
对每个打开的数据库,使用该数据库的信息调用一次回叫函数。使用包括附加的数据库名和索引名在内的参数。第一行用于主数据库,第二行用于存放临时表的临时数据库。
- PRAGMA foreign_key_list(table-name);
对于参数表中每个涉及到字段的外键,使用该外键的信息调用一次回叫函数。每个外键中的每个字段都将调用一次回叫函数。
- PRAGMA index_info(index-name);
对该索引涉及到的每个字段,使用字段信息(字段名,字段号)调用一次回叫函数。
- PRAGMA index_list(table-name);
对表中的每个索引,使用索引信息调用回叫函数。参数包括索引名和一个指示索引是否唯一的标志。
- PRAGMA table_info(table-name);
对于表中的每个字段,使用字段信息(字段名,数据类型,可否为空,缺省值)调用回叫函数。
用于查询/更改版本信息的Pragma
- PRAGMA [database.]schema_version;
PRAGMA [database.]schema_version = integer ;
PRAGMA [database.]user_version;
PRAGMA [database.]user_version = integer ;
这两条pragma分别用于设置schema-version和user-version的值。schema-version 和user-version均为32位有符号整数,存放于数据库头中。
schema-version通常只由SQLite内部操作。每当数据库的schema改变时(创建或撤消表或索引),SQLite 将这个值增大。schema版本在每一次query被执行时被SQLite所使用,以确定编译SQL query时内部cache的schema与编译后的query实际执行时数据库的schema相匹配。使用"PRAGMA schema_version"更改schema-version会破坏这一机制,有导致程序崩溃或数据库损坏的潜在危险。请小心使用!
user-version不在SQLite内部使用,任何程序可以用它来做任何事。
用于库debug的Pragma
- PRAGMA integrity_check;
该命令对整个数据库进行完整性检查,查找次序颠倒的记录,丢失的页,残缺的记录以及损坏的索引。若发现任何问题则返回一形容问题所在的字符串,若一切正常返回"ok".
- PRAGMA parser_trace = ON; (1)
PRAGMA parser_trace = OFF; (0)
打开或关闭SQLite库中的SQL语法分析追踪,用于debug.只有当SQLite不使用NDEBUG宏进行编译时该pragma才可用。
- PRAGMA vdbe_trace = ON; (1)
PRAGMA vdbe_trace = OFF; (0)
打开或关闭SQLite库中的虚拟数据库引擎追踪,用于debug.更多信息,察看 VDBE文档。
- PRAGMA vdbe_listing = ON; (1)
PRAGMA vdbe_listing = OFF; (0)
打开或关闭虚拟机程序列表,当开启列表功能,整个程序的内容在执行前被打印出来,就像在每条语句之前自动执行EXPLAIN. 语句在打印列表之后正常执行。用于debug.更多信息,察看 VDBE文档。
REINDEX
sql-statement ::= |
REINDEX collation name |
sql-statement ::= |
REINDEX [database-name .] table/index-name |
REINDEX命令用于删除并从草稿重建索引。当比较顺序改变时该命令显得很有效。
在第一种形式中,所有附加数据库中使用该比较顺序的索引均被重建。在第二种形式中, [database-name.]table/index-name标识出一个表,所有关联该表的索引被重建。若标识出索引,则仅仅该索引被删除并重建。
若不指定database-name而指定表/索引名以及比较顺序,只有关联该比较顺序的索引被重建。在重建索引时总是指定database-name可以消除这一歧义。
REPLACE
sql-statement ::= |
REPLACE INTO [database-name .] table-name [( column-list )] VALUES ( value-list ) | |
REPLACE命令用于替代INSERT的"INSERT OR REPLACE"变体,以更好的兼容MySQL。查看 INSERT命令文档获取更多信息。
SELECT
sql-statement ::= |
SELECT [ALL | DISTINCT] result [FROM table-list] |
result ::= |
result-column [, result-column]* |
result-column ::= |
* | table-name . * | expr [ [AS] string ] |
table-list ::= |
table [join-op table join-args]* |
table ::= |
table-name [AS alias] | |
join-op ::= |
, | [NATURAL] [LEFT | RIGHT | FULL] [OUTER | INNER | CROSS] JOIN |
join-args ::= |
[ON expr] [USING ( id-list )] |
sort-expr-list ::= |
expr [sort-order] [, expr [sort-order]]* |
sort-order ::= |
[ COLLATE collation-name ] [ ASC | DESC ] |
compound_op ::= |
UNION | UNION ALL | INTERSECT | EXCEPT |
SELECT语句用于查询数据库。一条SELECT命令的返回结果是零或多行每行有固定字段数的数据。字段的数目由在SELECT和FROM之间的表达式列表定义。任意的表达式都可以被用作结果。若表达式是 *则表示所有表的所有字段。若表达式是表的名字后接.*则结果为该表中的所有字段。
DISTINCT关键字的使用会使返回的结果是原结果的一个不含相同行的子集。NULL值被认为是相同的。缺省行为是返回所有的行,为清楚起见可以使用关键字ALL。
查询对FROM之后定义的一个或多个表进行。若多个表用逗号连接,则查询针对它们的交叉连接。所有的SQL-92连接语法均可以用于定义连接。圆括号中的副查询可能被FROM子句中的任意表名替代。当结果中仅有一行包含表达式列表中的结果的行时,整个的FROM子句会被忽略。
WHERE子句可以限定查询操作的行数目。
GROUP BY子句将一行或多行结果合成单行输出。当结果有聚集函数时这将尤其有用。GROUP BY子句的表达式不须是出现在结果中的表达式。HAVING子句与WHERE相似,只是HAVING用于过滤分组创建的行。HAVING子句可能包含值,甚至是不出现在结果中的聚集函数。
ORDER BY子句对所得结果根据表达式排序。表达式无须是简单SELECT的结果,但在复合SELECT中每个表达式必须精确对应一个结果字段。每个表达式可能跟随一个可选的COLLATE关键字以及用于排序文本的比较函数名称和/或关键字ASC或DESC,用于说明排序规则。
LIMIT子句限定行数的最大值。负的LIMIT表示无上限。后跟可选的OFFSET说明跳过结果集中的前多少行。在一个复合查询中,LIMIT子句只允许出现在最终SELECT语句中。限定对于所有的查询均适用,而不仅仅是添加了LIMIT子句的那一行。注意OFFSET关键字用于LIMIT子句中,则限制值是第一个数字,而偏移量(offset)是第二个数字。若用逗号替代OFFSET关键字,则偏移量是第一个数字而限制值是第二个数字。这是为了加强对遗留的SQL数据库的兼容而有意造成的矛盾。
复合的SELECT由两个或更多简单SELECT经由UNION, UNION ALL, INTERSECT, EXCEPT中的一个运算符连接而成。在一个复合SELECT中,各个SELECT需指定相同个数的结果字段。仅允许一个ORDER BY子句出现在SELECT的末尾。UNION和UNION ALL运算符从左至右将所有SELECT的结果合成一个大的表。二者的区别在于UNION的所有结果行是不相同的而 UNION ALL允许重复行。INTERSECT运算符取左右两个SELECT结果的交。EXCEPT从左边SELECT的结果中除掉右边SELECT的结果。三个或更多SELECT复合时,它们从左至右结合。
UPDATE
sql-statement ::= |
UPDATE [ OR conflict-algorithm ] [database-name .] table-name |
assignment ::= |
column-name = expr |
UPDATE语句用于改变表中所选行的字段值。每个UPDATE的赋值的等号左边为字段名而右边为任意表达式。表达式可以使用其它字段的值。所有的表达式将在赋值之前求出结果。可以使用WHERE子句限定需要改变的行。
在使用这一命令时,利用可选的ON CONFLICT子句可以定义替代的约束冲突判定算法。更多信息,参见 ON CONFLICT。
VACUUM
sql-statement ::= |
VACUUM [index-or-table-name] |
VACUUM命令是SQLite的一个扩展功能,模仿PostgreSQL中的相同命令而来。若调用VACUUM带一个表名或索引名,则将整理该表或索引。在SQLite 1.0中,VACUUM命令调用 gdbm_reorganize()整理后端数据库文件。
SQLITE 2.0.0中去掉了GDBM后端,VACUUM无效。在2.8.1版中,VACUUM被重新实现。现在索引名或表名被忽略。
当数据库中的一个对象(表,索引或触发器)被撤销,会留下空白的空间。它使数据库比需要的大小更大,但能加快插入速度。实时的插入和删除会使得数据库文件结构混乱,减慢对数据库内容访问的速度。 VACUUM命令复制主数据库文件到临时数据库并从临时数据库重新载入主数据库,以整理数据库文件。这将除去空白页,使表数据彼此相邻排列,并整理数据库文件结构。不能对附加数据库文件进行以上操作。
若当前有活动事务,该命令无法起作用。对于in-memory数据库,该命令无效。
SQLite3.1中,可以通过使用auto-vacuum模式取代VACUUM命令,使用 auto_vacuum pragma开启该模式。
SQLite与SQL Server语法差异
1 TOP
这是一个大家经常问到的问题,例如在SQLSERVER中可以使用如下语句来取得记录集中的前十条记录:
1. SELECT TOP 10 * FROM [index] ORDER BY indexid DESC;
但是这条SQL语句在SQLite中是无法执行的,应该改为:
1. SELECT * FROM [index] ORDER BY indexid DESC limit 0,10;
其中limit 0,10表示从第0条记录开始,往后一共读取10条
2 创建视图(Create View)
SQLite在创建多表视图的时候有一个BUG,问题如下:
1. CREATE VIEW watch_single AS SELECT DISTINCTwatch_item.[watchid],watch_item.[itemid] FROM watch_item;
上面这条SQL语句执行后会显示成功,但是实际上除了
1. SELECT COUNT(*) FROM [watch_single ] WHERE watch_ single.watchid = 1;
能执行之外是无法执行其他任何语句的。其原因在于建立视图的时候指定了字段所在的表名,而SQLite并不能正确地识别它。所以上面的创建语句要改为:
1. CREATE VIEW watch_single AS SELECT DISTINCT [watchid],[itemid] FROM watch_item;
但是随之而来的问题是如果是多表的视图,且表间有重名字段的时候该怎么办?
3 COUNT(DISTINCT column)
SQLite在执行如下语句的时候会报错:
1. SELECT COUNT(DISTINCT watchid) FROM [watch_item] WHERE watch_item.watchid = 1;
其原因是SQLite的所有内置函数都不支持DISTINCT限定,所以如果要统计不重复的记录数的时候会出现一些麻烦。比较可行的做法是先建立一个不重复的记录表的视图,然后再对该视图进行计数。
4 外连接
虽然SQLite官方已经声称LEFT OUTER JOIN 已经实现,但还没有 RIGHT OUTER JOIN 和 FULL OUTER JOIN。但是实际测试表明似乎并不能够正常的工作。以下三条语句在执行的时候均会报错:
1. SELECT tags.[tagid] FROM [tags],[tag_rss] WHERE tags.[tagid] = tag_rss.[tagid](*);
2. SELECT tags.[tagid] FROM [tags],[tag_rss] WHERE LEFT OUTER JOIN tag_rss.[tagid] = tags.[tagid];
3. SELECT tags.[tagid] FROM [tags],[tag_rss] WHERE LEFT JOIN tag_rss.[tagid] = tags.[tagid];
此外经过测试用+号代替*号也是不可行的。
5 返回最后插入的标识值
返回最后插入的标识值sql server用@@IDENTITY
sqlite用标量函数LAST_INSERT_ROWID()
返回通过当前的 SQLConnection 插入到数据库的最后一行的行标识符(生成的主键)。此值与 SQLConnection.lastInsertRowID 属性返回的值相同。
6 top n
在sql server中返回前2行可以这样:
1. select top 2 * from aa
2. order by ids desc
sqlite中用LIMIT,语句如下:
1. select * from aa
2. order by ids desc
3. LIMIT 2
7 GETDATE ( )
在sql server中GETDATE ( )返回当前系统日期和时间,sqlite中没有
8 EXISTS语句
sql server中判断插入(不存在ids=5的就插入)
1. IF NOT EXISTS (select * from aa where ids=5)
2. BEGIN
3. insert into aa(nickname)
4. select 't'
5. END
在sqlite中可以这样
1. insert into aa(nickname)
2. select 't'
3. where not exists(select * from aa where ids=5)
9嵌套事务
sqlite仅允许单个活动的事务
10 RIGHT 和 FULL OUTER JOIN
sqlite不支持 RIGHT OUTER JOIN 或 FULL OUTER JOIN
11可更新的视图
sqlite视图是只读的。不能对视图执行 DELETE、INSERT 或 UPDATE 语句,sql server是可以对视图 DELETE、INSERT 或 UPDATE
12关于日期时间类型列的默认设置
对"列"的设置包括三个字段:Name、Type、Default
Name:logTime(随意命名);
Type:Date类型,得到的值形如"2011-04-23",
DATETIME类型,得到的值形如"2011-04-23 11:49:04.000";
Default:datetime('now','localtime') 我采用的是两个参数,后面的localtime不要丢弃,不然时间不准确。
Delphi中调用SQLite
ASQLite3
asqlite3下载安装后,有以下控件
1.让SQLite支持中文路径
Sqlite是一款不错的嵌入式数据库,在Delphi中使用时一个比较好的选择是ASqlite控件。但是,遗憾地是你会发现当路径中含有汉字时,Sqlite不工作!!实际上这都是编码惹的祸――Sqlite在处理时采用的是Utf-8编码,但是,Delphi传递给他的都是Ansi格式,ASqlite并没有处理编码转换问题,因此造成Sqlite无法正常工作。解决方案其实很简单:
在给ASqlite的DefaultDir,以及Database属性赋值时,调用一个编码转换函数(AnsiToUtf8)即可。例如:
path := GetCurrentDir;
DB1.DefaultDir := AnsiToUtf8(path);
//DB1.Database := 'aa.sqb';
DB1.Database := AnsiToUtf8('测试汉字.sqb');// 'aa.sqb';
这样就不会有问题了!
2.让SQLite的SQL语句中可以包含中文字符
这里主要是一个使用习惯的问题,以前我都是直接在SQL语句里进行操作,每次都是直接把SQL语句写好,但在SQLite中,如果你的SQL语句中包含了中文字符比如说查询的条件里有中文你就会发现虽然执行不报错但查询的结果是不正确的!要解决这个问题就必须使用SQL语句的参数查询(这种说法不知道正确不正确,我姑且先叫SQL语句的参数查询吧)
具体说明如下:
在Delphi中,要灵活地使用SQL查询语句,还需要使用TQuery构件的Params特性,
即在查询语句中使用参数。Delphi中的SQL语句如需参数,则在参数名称前使用一个
冒号':'来作为标识。例如:'select * from mytable where id_no=:p'一句,其中
p为参数,可以在程序运行过程进行在赋值。在程序运行期动态地创建带参数的SQL
查询语句较为复杂,其过程如下:
Query1.close;
{先关闭以前查询的连接}
Query1.SQL.Clear;
{清除以前的查询语句}
Query1.Params.Clear;
{清除以前查询语句的参数}
Query1.Params.CreateParam(ftString, 'p',ptInput);
{创建新的查询语句的参数p}
Query1.SQL.Add('select * from title1 where NO_GLOBE=:p');
{增加新的查询语句内容为select * from title1 where NO_GLOBE=:p}
{其中p为新建的参数}
Query1.Params.ParamByName('p').asstring:= '1';
{给新的参数赋值为'1'}
Query1.open;
{建立新的查询语句的数据库连接}
以上两个示例中,SQL查询语句都是SELECT语句,而当SQL语句是UPDATE或INSERT、DELETE时,其中的Query1.Open需要改为Query1.ExecSQL。
ZEOSDBO
待续…