sqlite 支持库

qqmmcc

SQLite 是轻量嵌入式数据库，发布程序只要带一个sqlite.dll即可。
在aardio中可以将该DLL用$操作符直接嵌入内存直接加载，发布时不需要dll文件。

SQLite 顾名思议是以SQL为基础的数据库软件，SQL是一套强大的数据库语言，主要概念是由「数据库」、「资料表」（table）、「查询指令」（queries）等单元组成的「关联性数据库」（进一步的概念可参考网络上各种关于SQL及关联性数据库的文件）。因为SQL的查询功能强大，语法一致而入门容易，因此成为现今主流数据库的标准语言（微软、Oracle等大厂的数据库软件都提供SQL语法的查询及操作）。

SQLite不同于其他大部分的SQL数据库引擎,因为它的首要设计目标就是简单化:

易于管理
易于使用
易于嵌入其他大型程序
易于维护和配置
支援大多数的SQL指令（下面会简单介绍）。
一个档案就是一个数据库。不需要安装数据库服务器软件。
完整的Unicode支援（因此没有跨语系的问题）。
速度很快。

许多人喜欢SQLite因为它的小巧和快速.
但是这些特性只是它的部分优点, 使用者还会发现SQLite是非常稳定的. 出色的稳定性源于它的简单, 越简单就越不容易出错. 除了上述的简单、小巧和稳定性外, 最重要的在于SQLite力争做到简单化.

简单化在一个数据库引擎中可以说是一个优点, 但也可能是个缺点, 主要决定于你想要做什么. 为了达到简单化, SQLite省略了一些人们认为比较有用的特性, 例如高并发性、 严格的存取控制、 丰富的内置功能、 存储过程、复杂的SQL语言特性、 XML以及Java的扩展, 超大的万亿级别的数据测量等等. 如果你需要使用上述的这些特性并且不介意它们的复杂性, 那么SQLite也许就不适合你了. SQLite没有打算作为一个企业级的数据库引擎, 也并不打算和Oracle或者PostgreSQL竞争.

仅凭经验来说SQLite适用于以下场合: 当你更看中简单的管理、使用和维护数据库, 而不是那些企业级数据库提供的不计其数的复杂功能的时候，使用SQLite是一个比较明智的选择. 事实也证明, 人们在许多情况下已经清楚的认识到简单就是最好的选择.

OS X自从10.4后把SQLite这套相当出名的数据库软件，放进了作业系统工具集里。OS X包装的是第三版的SQLite，又称SQLite3。这套软件有几个特色： 软件属于公共财（public domain），SQLite可说是某种「美德软件」（virtueware），作者本人放弃着作权，而给使用SQLite的人以下的「祝福」（blessing）：

May you do good and not evil. 愿你行善莫行恶
May you find forgiveness for yourself and forgive others. 愿你原谅自己宽恕他人
May you share freely, never taking more than you give. 愿你宽心与人分享，所取不多于你所施予

aardio在v0.55.3已提供sqlite支持库。
在aardio中声明的API已经加入了各自的名字空间,
例如原来的sqlite3_bind_double 在aardio中声明为 sqlite.bind.double

在aardio开发环境中如下图操作查看范例：



示例：

1. import sqlite
   
2. 
   
3. //打开控制台
   
4. io.open();
   
5. 
   
6. //打开数据库连接
   
7. var sqlConnection = sqlite("/db.db")  
   
8. if( not sqlConnection.existsTable("film") ){
   
9. 
   
10.         //创建表
    
11.         sqlConnection.exec( "create table film(
    
12.                 title,
    
13.                 length,
    
14.                 year,
    
15.                 starring
    
16.                 );"
    
17.         )
    
18.         
    
19.          //插入数据
    
20.         sqlConnection.exec( "insert into film values ('Silence of the Lambs, The', 11.8, datetime('now','localtime'),'Jodie Foster' );")
    
21.         sqlConnection.exec( "insert into film values ('Contact', 153, 1997, 'Jodie Foster');")
    
22.         sqlConnection.exec( "insert into film values ('Crouching Tiger, Hidden Dragon', 120, 2000, 'Yun-Fat Chow');")
    
23.         
    
24.         io.print("最后插入ID",sqlConnection.last_insert_rowid())
    
25. }
    
26. else {
    
27.         sqlConnection.exec(  "UPDATE film SET year=2010, length=112 WHERE year=1991")
    
28.         if( ! sqlConnection.changes() ){
    
29.                 //更新语句影响的行为为0,下面插入新的
    
30.                 sqlConnection.exec( "insert into film values ('Silence of the Lambs, The', 112, 2010, 'Jodie Foster');")
    
31.         }
    
32.         
    
33.         io.print("最后插入ID",sqlConnection.last_insert_rowid())
    
34. }
    
35. 
    
36. //sql里的问号表示参数
    
37. var command = sqlConnection.prepare("insert into film values (?,123, 1991, 'Jodie Foster');" )
    
38. //绑定一个参数,其实在aardio里所有字符串都是blob,所以非常简单
    
39. command.bind.blob( "用string.load读入二进制字符串" )//可以使用第二个参数指定是第几个问号
    
40. command.step() //执行
    
41. command.finalize() //释放命令对象
    
42. 
    
43. //迭代方式查询数据
    
44. for title, length, year, starring in sqlConnection.each("select * from film") {
    
45.         io.print( title, length, year, starring  )
    
46.         break;
    
47. }
    
48. io.print("------------迭代数据 ok")
    
49. 
    
50. //枚举方式 注意枚举返回的数据都是文本类型.
    
51. sqlConnection.enum(
    
52.         "select * from film",
    
53.         function(tName,tValue){
    
54.                 for(col=1;#tName;1){
    
55.                         io.print(tName[col],tValue[col])
    
56.                 }
    
57.                 //return false; //返回false表示停止枚举
    
58.         }
    
59. )        
    
60. io.print("------------枚举数据 ok")
    
61. 
    
62. dbTable = sqlConnection.getTable("select * from film")
    
63. //遍列所有行记录
    
64. for(row=1;#dbTable ){
    
65. 
    
66.         //打印所有列名和值
    
67.         for( k,v in dbTable[row] ){
    
68.                 io.print(k,v);
    
69.         }
    
70. }
    
71. io.print("------------数据列表 ok")
    
72. 
    
73. //删除表
    
74. sqlConnection.exec("drop table film")
    
75. sqlConnection.close()
    
76. 
    
77. execute("pause") //按任意键继续
    
78. io.close();//关闭控制台
    

复制代码

qqmmcc

收集SQLite与Sql Server的语法差异

---

1.返回最后插入的标识值
返回最后插入的标识值sql server用@@IDENTITY
sqlite用标量函数LAST_INSERT_ROWID()

返回通过当前的 SQLConnection 插入到数据库的最后一行的行标识符（生成的主键）。
此值与 SQLConnection.lastInsertRowID 属性返回的值相同。

2.top n
在sql server中返回前2行可以这样：
select top 2 * from aa order by ids desc

Sqlite中提供的方法和Mysql的一样，也是通过关键字limit限制，语句如下
select * from aa order by ids desc LIMIT 2

3.GETDATE ( )
在sql server中GETDATE ( )返回当前系统日期和时间
sqlite中没有

4.EXISTS语句

sql server中判断插入（不存在ids=5的就插入）
IF NOT EXISTS (select * from aa where ids=5)
BEGIN
insert into aa(nickname)
select 't'
END


在sqlite中可以这样
insert into aa(nickname) select 't'  where not exists(select * from aa where ids=5)

5.嵌套事务
sqlite仅允许单个活动的事务

6.RIGHT 和 FULL OUTER JOIN
sqlite不支持 RIGHT OUTER JOIN 或 FULL OUTER JOIN

7.可更新的视图
sqlite视图是只读的。不能对视图执行 DELETE、INSERT 或 UPDATE 语句，sql server是可以对视图 DELETE、INSERT 或 UPDATE



SQLite不支持的SQL语法总结

---

1 TOP
这是一个大家经常问到的问题，例如在SQLSERVER中可以使用如下语句来取得记录集中的前十条记录：
SELECT TOP 10 * FROM [index] ORDER BY indexid DESC;

但是这条SQL语句在SQLite中是无法执行的，应该改为：
SELECT * FROM [index] ORDER BY indexid DESC limit 0,10;
其中limit 0,10表示从第0条记录开始，往后一共读取10条

2 创建视图（Create View）
SQLite在创建多表视图的时候有一个BUG，问题如下：
CREATE VIEW watch_single AS SELECT DISTINCT watch_item.[watchid],watch_item.[itemid] FROM watch_item;
上面这条SQL语句执行后会显示成功，但是实际上除了
SELECT COUNT(*) FROM [watch_single ] WHERE watch_ single.watchid = 1;
能执行之外是无法执行其他任何语句的。其原因在于建立视图的时候指定了字段所在的表名!

而SQLite并不能正确地识别它。所以上面的创建语句要改为：
CREATE VIEW watch_single AS SELECT DISTINCT [watchid],[itemid] FROM watch_item;
但是随之而来的问题是如果是多表的视图，且表间有重名字段的时候该怎么办？

3 COUNT（DISTINCT column）
SQLite在执行如下语句的时候会报错：
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。

qqmmcc

sqlite基础知识
一、导入sqlite库
必须先调用代码

1. 
   
2. import sqlite;
   

复制代码

导入sqlite支持库

二、sqlite基本对象

sqlite *pdb 数据库句柄，在aardio里声明为指针内型。
例如

1. 
   
2. var sqlConn = sqlite("/db.db")
   

复制代码

打开一个连接，那么

1. 
   
2. sqlConn.db
   

复制代码

就是数据库句柄

sqlite_stmt      *stmt, 这个相当于ODBC的Command对象，用于保存编译好的SQL语句，一般用 sqlite.prepare来创建sqlite_stmt，在aardio中也是一个pointer指针类型

在Ｃ里面你可能晕头转向，但是在aardio里一律不管他，都是pointer(指针)

三、基本的API函数

sqlite.open() 打开数据库，可以指定不存在的数据库文件。
sqlite.exec()  执行非查询的sql语句
sqlite.prepare() 准备sql语句，执行select语句或者要使用parameter bind时，用这个函数（封装了sqlite3_exec）.
sqlite.step() 在调用sqlite3_prepare后，使用这个函数在记录集中移动。这个很有一点象mssql里常用的Rs.MoveNext()
sqlite.close() 关闭数据库连接，一般关闭数据库时会自动保存数据库文件。


四、关于建库

初接触sqlite可能对建库会郁闷一下，
sqlite是隐式自动建库的，而在命令行操作中对相对路径的支持又不太好。

实际上sqlite可以直接连接不存在的文件名。如果库不存在就会自动创建。
然后在调用sqlite.close()关闭数据库时自动保存在硬盘上。在aardio中，sqlite.open()会自动调用io.fullpath转换为绝对路径，所以不必顾虑此问题

五、字段类型

sqlite的字段类型要求不是很严格，很有一点象aardio里的动态类型。
这在C之类的静态语言里是个麻烦，不过在aardio里就非常好用了，是以一般情况下大可以都把他们都当字符串来处理，不必理会是什么类型了。

空（null）：该值为空
整型（int）：有符号整数，按大小被存储成1,2,3,4,6或8字节。
实数（double）：浮点数，以8字节指数形式存储，aardio里的数值就是浮点数。
文本（text）：字符串，以数据库编码方式存储（UTF-8, UTF-16BE 或者 UTF-16-LE）。
blob：BLOB数据不做任何转换，以输入形式存储。

sqlite里的int64在aardio里称之为long类型，即64位整数。
而text就是aardio里的string类型了.因为aardio里字符串也可以包含二进制，所以blob在aardio里也是string类型

不过这里需要注意一下，你可以在aardio把blob类型当字符串的使用。
但是sqlite却要注意分别，因为静态语言，对于字符串，他是寻找\0的终结符来获取一个字符串的长度，而象blob这样的二进制数据，他是不会自动知道长度的，所以操作blob有独立的API，而且通常会多一个参数，用以指定字符串的长度。

可以参考aardio静态类型手册，以了解更多静态类型有关的知识：
http://bbs.aardio.com/doc/reference/libraries/kernel/raw/api.html

qqmmcc

Frequently Asked Questions
(1) 如何建立自动增长字段?
简短回答：声明为 INTEGER PRIMARY KEY 的列将会自动增长。
长一点的答案： 如果你声明表的一列为 INTEGER PRIMARY KEY，那么， 每当你在该列上插入一NULL值时， NULL自动被转换为一个比该列中最大值大1的一个整数，如果表是空的， 将会是1。 (如果是最大可能的主键 9223372036854775807，那个，将键值将是随机未使用的数。） 如，有下列表：
CREATE TABLE t1(  a INTEGER PRIMARY KEY,  b INTEGER);在该表上，下列语句
INSERT INTO t1 VALUES(NULL,123);在逻辑上等价于：
INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);有一个新的API叫做 sqlite3_last_insert_rowid()， 它将返回最近插入的整数值。
注意该整数会比表中该列上的插入之前的最大值大1。 该键值在当前的表中是唯一的。但有可能与已从表中删除的值重叠。 要想建立在整个表的生命周期中唯一的键值，需要在 INTEGER PRIMARY KEY 上增加AUTOINCREMENT声明。那么，新的键值将会比该表中曾能存在过的最大值大1。 如果最大可能的整数值在数据表中曾经存在过，INSERT将会失败， 并返回SQLITE_FULL错误代码。
(2)SQLite支持何种数据类型？
参见 http://www.sqlite.org/datatype3.html.
(3)SQLite允许向一个integer型字段中插入字符串！
这是一个特性，而不是一个bug。SQLite不强制数据类型约束。 任何数据都可以插入任何列。你可以向一个整型列中插入任意长度的字符串， 向布尔型列中插入浮点数，或者向字符型列中插入日期型值。 在 CREATE TABLE 中所指定的数据类型不会限制在该列中插入任何数据。 任何列均可接受任意长度的字符串（只有一种情况除外： 标志为INTEGER PRIMARY KEY的列只能存储64位整数， 当向这种列中插数据除整数以外的数据时，将会产生错误。
但SQLite确实使用声明的列类型来指示你所期望的格式。所以， 例如你向一个整型列中插入字符串时，SQLite会试图将该字符串转换成一个整数。 如果可以转换，它将插入该整数；否则，将插入字符串。 这种特性有时被称为 类型或列亲和性(type or column affinity).
(4)为什么SQLite不允许在同一个表不同的两行上使用0和0.0作主键？
主键必须是数值类型，将主键改为TEXT型将不起作用。
每一行必须有一个唯一的主键。对于一个数值型列， SQLite认为 '0' 和 '0.0' 是相同的， 因为他们在作为整数比较时是相等的(参见上一问题)。 所以，这样值就不唯一了。
(5)多个应用程序或一个应用程序的多个实例可以同时访问同一个数据库文件吗？
多个进程可同时打开同一个数据库。多个进程可以同时进行SELECT 操作，但在任一时刻，只能有一个进程对数据库进行更改。
SQLite使用读、写锁控制对数据库的访问。（在Win95/98/ME等不支持读、 写锁的系统下，使用一个概率性的模拟来代替。）但使用时要注意： 如果数据库文件存放于一个NFS文件系统上，这种锁机制可能不能正常工作。 这是因为 fcntl() 文件锁在很多NFS上没有正确的实现。 在可能有多个进程同时访问数据库的时候，应该避免将数据库文件放到NFS上。 在Windows上，Microsoft的文档中说：如果使用 FAT 文件系统而没有运行 share.exe 守护进程，那么锁可能是不能正常使用的。那些在Windows上有很多经验的人告诉我： 对于网络文件，文件锁的实现有好多Bug，是靠不住的。如果他们说的是对的， 那么在两台或多台Windows机器间共享数据库可能会引起不期望的问题。
我们意识到，没有其它[ i ]嵌入式的 SQL 数据库引擎能象 SQLite 这样处理如此多的并发。SQLite允许多个进程同时打开一个数据库， 同时读一个数据库。当有任何进程想要写时，它必须在更新过程中锁住数据库文件。 但那通常只是几毫秒的时间。其它进程只需等待写进程干完活结束。 典型地，其它嵌入式的SQL数据库引擎同时只允许一个进程连接到数据库。
但是，Client/Server数据库引擎（如 PostgreSQL, MySQL, 或 Oracle） 通常支持更高级别的并发，并且允许多个进程同时写同一个数据库。 这种机制在Client/Server结构的数据库上是可能的， 因为总是有一个单一的服务器进程很好地控制、协调对数据库的访问。 如果你的应用程序需要很多的并发，那么你应该考虑使用一个Client/Server 结构的数据库。但经验表明，很多应用程序需要的并发，往往比其设计者所想象的少得多。
当SQLite试图访问一个被其它进程锁住的文件时，缺省的行为是返回 SQLITE_BUSY。 可以在C代码中使用 sqlite3_busy_handler() 或 sqlite3_busy_timeout() API 函数调整这一行为。


SQLite自增ID自段
使用方法为 INTEGER PRIMARY KEY AUTOINCREMENT
如:

1. 
   
2. CREATE TABLE 21andy ( id INTEGER PRIMARY KEY AUTOINCREMENT, 21andy VARCHAR(100) NOT NULL, date DATE );
   

复制代码

注意是AUTOINCREMENT, 和MySQL的写法不一样

SQLite清空表
SQLite没有TRUNCATE清空表命令,所以只能这样

1. 
   
2. DELETE FROM 21andy;
   

复制代码

SQLite 删除记录后, 自增ID置0
只能这样

1. 
   
2. DELETE FROM sqlite_sequence;
   

复制代码

另外, 这个sqlite_sequence可以CRUD

SQLite 删除记录后, 不会释放空间
必须像这样

1. 
   
2. VACUUM
   

复制代码

SQLite 分页查询
有两种写法:

1. 
   
2. Select * From Account Limit 9 Offset 10;
   

复制代码

1. 
   
2. SELECT * FROM Account LIMIT 10, 9
   

复制代码

他们两个的效果都是一样的,其中第一种写法比较清晰明了,即跳过10行,读取其后的9行数据.

SQLite批量插入数据
很不幸的事情是貌似SQLite只能一条一条的执行插入,但是这是非常非常慢的行为,执行一条就是执行一次写入磁盘的操作,这实在是太可怕了.在SQLite里面执行批量插入,只能将插入操作放入到事务当中去.示例如下:

1. 
   
2. BEGIN;
   
3. CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));
   
4. INSERT INTO t2 VALUES(1,59672,'fifty nine thousand six hundred seventy two');
   
5. INSERT INTO t2 VALUES(24999,89569,'eighty nine thousand five hundred sixty nine');
   
6. INSERT INTO t2 VALUES(25000,94666,'ninety four thousand six hundred sixty six');
   
7. COMMIT;
   

复制代码

在SQLite当中一系列要进行多次写入操作的时候,建议放入到事务当中去,这个优化的性能提升是可以很明显感觉到的.用与不用的差别是非常大的.

如果数据不存在时执行INSERT操作,否则执行UPDATE操作
在SQLite里面有一个关键字REPLACE,可以使用它达到上面的目的:

1. 
   
2. REPLACE INTO [table] (row1, row2) VALUES (2, 3);
   

复制代码

如果该表有一个主键,并且已经存在主键值相等的数据则执行更新操作,否则执行插入操作添加数据,但是如果表没有主键,那么它就执行的永远都是插入操作了.

look

支持系列教程！

wudijushi

收藏收藏，辛苦楼主了，谢谢。

低调点

不错......

落叶人生

不错，正是我要找的

ghl781258

很好的教程

gangz

这个要看

tisiem

收藏！为什么图片不出来呢？

cnhope

很好的教程，先收藏，慢慢学，还有就是图片不显示哦。

rimren

May you do good and not evil. 愿你行善莫行恶
May you find forgiveness for yourself and forgive others. 愿你原谅自己宽恕他人
May you share freely, never taking more than you give. 愿你宽心与人分享，所取不多于你所施予

感恩分享！

wynn_li

qqmmcc 发表于 2011-11-5 13:41
sqlite基础知识
一、导入sqlite库
必须先调用代码导入sqlite支持库

在论坛找了一晚上怎么建sqlite数据库的教程，我就纳闷了所以的案例都是直接连接数据库，没有一个讲怎么建立数据库的，看到您的回复，第四条~突然觉得释然了.....

wynn_li

版主你的标题不科学，不易搜索啊！应该多包含些关键字，这么好的帖子访问量才8000，版主你的标题不科学.....

fen

"如果该表有一个主键,那么当主键值相等的时候,该行数据不存在执行插入,存在则执行更新操作."
"主键值相等,该行数据不存",这种情况有可能发生吗？

usen

好奇怪，图片显示不了