·创建数据库:
strFilename = App.Path & "\test.db"
Set cn = New LITEXLib.LiteConnection
cn.Path = strFilename
cn.Open
·创建表:创建名为testtable的表,含testCol01,testCol02,testCol03三个字段,字段的属性为text
tableName = "testtable"
segstring = "testCol01 TEXT,testCol02 TEXT,testCol03 TEXT"
str_SQLString = "Create table if not exists " & tableName & "(" & segstring & ")"
cn.Execute str_SQLString
·向表里面添加数据,批量添加(添加30×1440条数据)
firstDate = Now
cn.Execute "BEGIN;"'批量插入操作开始
For count1 = 1 To 30
DoEvents
For count2 = 1 To 1440
nextDate = DateAdd("n", count2, firstDate)
datastring = "datetime('" & Format(nextDate, "yyyy-mm-dd hh:nn:ss") & "'),"'添加日期内容
For count3 = 1 To 22'添加的数据内容
myValue = CStr(Int((20 * Rnd) + 1) + 890)
datastring = datastring & myValue & ","
Next
If Right(datastring, 1) = "," Then datastring = Left(datastring, Len(datastring) - 1)
str_SQLString = "insert into " & tableName & " values(" & datastring & ")" '相关的数据进行插入操作
cn.Execute str_SQLString
Next
firstDate = DateAdd("d", 1, firstDate)
Me.Caption = CStr(count1)
Next
cn.Execute "COMMIT;"'批量插入操作结束
·删除一条记录
str_SQLString="delete from testtable where testcol01=datetime('2012-12-12 12:12:12')"'字符串用单引号
·修改一条记录
str_SQLString="update testtable set testcol01=datetime('2012-09-13 12:12:12'),testcol02='hello' where testcol01=datetime('2012-12-13 12:12:12')"'字符串用单引号
·删除一个表
str_SQLString="drop testtable"
'以上表的操作为 cn.Execute str_SQLString
——————————————————————————————————————————————
·表的查询
str_SQLString="select * from testtable [where testcol02='hello'] [limit 1440 offset 2880]"
where 后面是条件
limit 后面是限制显示1440条
offset 后面是从开始的2880后开始的1440条
'由于查询数据需要返回数据应该使用 Set rs = cn.Prepare(str_SQLString)
'关于查询的记录集的使用
MSHFlexGrid1.Rows = rs.RowCount
For count1 = 0 To MSHFlexGrid1.Rows - 1
If rs.Step() Then Exit For'查看是否是记录集的结尾
For count2 = 0 To 22
MSHFlexGrid1.TextMatrix(count1, count2) = rs.ColumnValue(count2)'指定字段的数据
Next
Next
rs.Close
Set rs = Nothing '关闭记录集并设置其为空
本次使用的SQLITE版本为LITEXLib sqlite