官网 SQLite是一款轻型的数据库,是关系型数据库(RDBMS)管理系统,它包含在一个相对小的C库中。目前在很多嵌入式产品中使用了它,它占用资源非常的低,在嵌入式设备中,可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix/Android/IOS等等主流的操作系统,同时能够跟很多程序语言相结合,比如 Tcl、C#、PHP、Java等,还有ODBC接口,同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲,它的处理速度比他们都快。
Sqlite命令分类:
(DDL)数据定义语言:
CMD | Description |
---|---|
CREATE | 创建一个新的表,一个表的视图,或者数据库中的其他对象。 |
ALTER | 修改数据库中的某个已有的数据库对象,比如一个表。 |
DROP | 删除整个表,或者表的视图,或者数据库中的其他对象。 |
(DML)数据操作语言:
CMD | Description |
---|---|
INSERT | 创建一条记录。 |
UPDATE | 修改记录。 |
DELETE | 删除记录。 |
(DQL)数据查询语言:
CMD | Description |
---|---|
SELECT | 从一个或多个表中检索某些记录。 |
Sqlite点命令
先看一张Windows下的截图:
这是在Windows的cmd下运行sqlite3命令(如何安装和配置烦请自行google | baidu),然后依据提示运行.help的打印(只截图一部分)。
可以发现sqlite的help列出来了所有sqlite支持的点命令,也可以发现,点命令不需要已”;”结尾。
我们对上面的.help命令进行翻译大致如下:
CMD | Description |
---|---|
.backup ?DB? FILE | 备份DB数据库(默认是”main”)到FILE文件。 |
.bail ON/OFF | 发生错误后停止。默认为OFF。 |
.databases | 列出附加数据库的名称和文件。 |
.dump ?TABLE? | 以SQL文本格式转储数据库。如果指定了TABLE表,则只转储匹配LIKE模式的TABLE表。 |
.echo ON/OFF | 开启或关闭echo命令。 |
.exit | 退出SQLite提示符。 |
.explain ON/OFF | 开启或关闭适合于EXPLAIN的输出模式。如果没有带参数,则为EXPLAIN on,及开启EXPLAIN。 |
.header(s) ON/OFF | 开启或关闭头部显示。 |
.help | 显示消息。 |
.import FILE TABLE | 导入来自FILE文件的数据到TABLE表中。 |
.indices ?TABLE? | 显示所有索引的名称。如果指定了TABLE表,则只显示匹配LIKE模式的TABLE表的索引。 |
.load FILE ?ENTRY? | 加载一个扩展库。 |
.log FILE/off | 开启或关闭日志。FILE文件可以是stderr(标准错误)/stdout(标准输出)。 |
.mode MODE | 设置输出模式,MODE可以是下列之一:csv 逗号分隔的值;column 左对齐的列;html HTML的<table> 代码;insert TABLE表的SQL插入(insert)语句;line 每行一个值;list 由 .separator字符串分隔的值;tabs 由Tab分隔的值;tcl TCL列表元素。 |
.nullvalue STRING | 在NULL值的地方输出STRING字符串。 |
.output FILENAME | 发送输出到FILENAME文件。 |
.output stdout | 发送输出到屏幕。 |
.print STRING... | 逐字地输出STRING字符串。 |
.prompt MAIN CONTINUE | 替换标准提示符。 |
.quit | 退出SQLite提示符。 |
.read FILENAME | 执行FILENAME文件中的SQL。 |
.schema ?TABLE? | 显示CREATE语句。如果指定了TABLE表,则只显示匹配LIKE模式的TABLE表。 |
.separator STRING | 改变输出模式和.import所使用的分隔符。 |
.show | 显示各种设置的当前值。 |
.stats ON/OFF | 开启或关闭统计。 |
.tables ?PATTERN? | 列出匹配LIKE模式的表的名称。 |
.timeout MS | 尝试打开锁定的表MS微秒。 |
.width NUM NUM | 为”column”模式设置列宽度。 |
.timer ON/OFF | 开启或关闭CPU定时器测量。 |
Sqlite数据库的sqlite_master表
主表中保存数据库表的关键信息,并把它命名为sqlite_master。如要查看表概要,可如下操作:
sqlite>.schema sqlite_master
Sqlite语法规则
SQLite是遵循一套独特的称为语法的规则和准则。
SQLite是不区分大小写的,但也有一些命令是大小写敏感的,比如GLOB和glob在SQLite的语句中有不同的含义。
SQLite 注释是附加的注释,可以在 SQLite 代码中添加注释以增加其可读性,他们可以出现在任何空白处,包括在表达式内和其他 SQL 语句的中间,但它们不能嵌套。
SQL注释以两个连续的”-“字符开始,并扩展至下一个换行符或直到输入结束,以先到者为准。也可以以”/*"
开始,并扩展至下一个 “*/
” 字符对或直到输入结束,以先到者为准。SQLite的注释可以跨越多行。
SQLite语句以任何关键字开始,以”;”结束。
Sqlite数据类型
SQLite数据类型是一个用来指定任何对象的数据类型的属性。SQLite 中的每一列,每个变量和表达式都有相关的数据类型。您可以在创建表的同时使用这些数据类型。SQLite使用一个更普遍的动态类型系统。在SQLite中,值的数据类型与值本身是相关的,而不是与它的容器相关。
存储类
SQLite有5个原始的数据类型,被称为存储类。存储类这个词表明了一个值在磁盘上存储的格式,其实就是类型或数据类型的同义词。如下即是存储类:
存储类 | Description |
---|---|
NULL | 值是一个NULL值。 |
INTEGER | 值是一个带符号的整数,根据值的大小存储在1、2、3、4、6 或8字节中。 |
REAL | 值是一个浮点值,存储为8字节的IEEE浮点数字。 |
TEXT | 值是一个文本字符串,使用数据库编码(UTF-8、UTF-16BE或UTF-16LE)存储。 |
BLOB | 值是一个blob数据,完全根据它的输入存储。 |
SQLite通过值的表示法来判断其类型,下面就是SQLite的推理方法:
- SQL语句中用单引号或双引号括起来的文字被指派为TEXT。
- 如果文字是未用引号括起来的数据,并且没有小数点和指数,被指派为INTEGER。
- 如果文字是未用引号括起来的数据,并且带有小数点或指数,被指派为REAL。
- 用NULL说明的值被指派为NULL存储类。
- 如果一个值的格式为X’ABCD’,其中ABCD为16进制数字,则该值被指派为BLOB。X前缀大小写皆可。
如下就是验证结果:
SQLite单独的一个字段可能包含不同存储类的值。
如下就是验证结果:
喜欢钻牛角尖的这时候指定开始BB了,temp这一列数据类型不同,咋样比较大小?咋样排序等等?
经过查阅资料发现,具有不同存储类的值可以存储在同一个字段中。可以被排序,因为这些值可以相互比较。有完善定义的规则来做这件事。不同存储类的值可以通过它们各自类的“类值”进行排序,定义如下:
- NULL存储类具有最低的类值。一个具有NULL存储类的值比所有其它值都小(包括其它具有NULL存储类的值)。在NULL值之间,没有特别的可排序值。
- INTEGER或REAL存储类值高于NULL,它们的类值相等。INTEGER值和REAL值通过其数值进行比较。
- TEXT存储类的值比INTEGER和REAL高。数值永远比字符串的值低。当两个TEXT值进行比较时,其值大小由“排序法”决定。
- BLOB存储类具有最高的类值。具有BLOB类的值大于其它所有类的值。BLOB值之间在比较时使用C函数memcmp()。
所以,当SQLite对一个字段进行排序时,首先按存储类排序,然后再进行类内的排序 (NULL类内部各值不必排序) 。
弱类型(manifest typing)
首先有如下SQL语句:
- 1
- 2
这里的x、y和z这3个字段中存储的是INTEGER, TEXT和REAL类型。
再看下面例子:
- 1
- 2
这里的x、y和z这3个字段中存储的是TEXT、TEXT和TEXT类型。
再看下面例子:
- 1
- 2
这里的x、y和z这3个字段中存储的是INTEGER、REAL和BLOB类型。
通过上面几种写法你会发现,可以为SQLite的字段定义类型,但这不是必须的,你可以尽管违反类型定义。这是因为在任何情况下,SQLite都可以接受一个值并推断它的类型。
总之,SQLite的弱类型可表示为:
- 字段可以有类型。
- 类型可以通过值来推断。
类型亲和性介绍这两个规定如何相互关联。所谓类型亲和性就是在强类型(strict typing)和动态类型(dynamic typing)之间的平衡艺术。
类型亲和性(Type Affinity)
在SQLite中,字段没有类型或域。当给一个字段声明了类型,该字段实际上仅仅具有了该类型的亲和性。声明类型和类型亲和性是两回事。类型亲和性预定SQLite用什么存储类在字段中存储值。在存储一个给定的值时到底SQLite会在该字段中用什么存储类决定于值的存储类和字段亲和性的结合。
任何列可以存储任何类型的数据,但列的首选存储类是它的affinity。在SQLite3数据库中,每个表的列分配为以下类型的affinity之一:
Affinity | Description |
---|---|
TEXT | 该列使用存储类NULL、TEXT或BLOB存储所有数据。 |
NUMERIC | 该列可以包含使用所有五个存储类的值。 |
INTEGER | 与带有NUMERIC affinity的列相同,在CAST表达式中带有异常。 |
REAL | 与带有NUMERIC affinity的列相似,不同的是,它会强制把整数值转换为浮点表示。 |
NONE | 带有affinity NONE的列,不会优先使用哪个存储类,也不会尝试把数据从一个存储类强制转换为另一个存储类。 |
下表列出了当创建SQLite3表时可使用的各种数据类型名称,同时也显示了相应的应用Affinity:
数据类型 | Affinity |
---|---|
INT、NTEGER、TINYINT、SMALLINT、MEDIUMINT、BIGINT、UNSIGNED BIG INT、INT2、INT8 | INTEGER |
CHARACTER(20)、VARCHAR(255)、VARYING CHARACTER(255)、NCHAR(55)、NATIVE CHARACTER(70)、NVARCHAR(100)、TEXT、CLOB | TEXT |
BLOB、no datatype specified | NONE |
REAL、DOUBLE、DOUBLE PRECISION、FLOAT | REAL |
NUMERIC、DECIMAL(10,5)、BOOLEAN、DATE、DATETIME | NUMERIC |
Boolean数据类型
SQLite没有单独的Boolean存储类,布尔值被存储为整数 0(false)和 1(true)。
Date与Time数据类型
SQLite没有一个单独的用于存储日期和/或时间的存储类,但SQLite 能够把日期和时间存储为TEXT、REAL或 INTEGER值。您可以以任何上述格式来存储日期和时间,并且可以使用内置的日期和时间函数来自由转换不同格式。
存储类 | 日期格式 |
---|---|
TEXT | 格式为”YYYY-MM-DD HH:MM:SS.SSS”的日期。 |
REAL | 从公元前4714年11月24日格林尼治时间的正午开始算起的天数。 |
INTEGER | 从1970-01-01 00:00:00 UTC算起的秒数。 |
字段类型和亲和性
首先,每个字段都具有一种亲和性。共有五种亲和性:NUMERIC、INTEGER、REAL、TEXT和NONE。一个字段的亲和性由它预声明的类型决定。所以,当你为字段声明了类型,从根本上说是为字段指定了亲和性。SQLite按下面的规则为字段指派亲和性:
- 默认的,一个字段默认的亲和性是NUMERIC。如果一个字段不是INTEGER、TEXT、REAL或NONE的,那它自动地被指派为NUMERIC亲和性。
- 如果为字段声明的类型中包含了’INT’(无论大小写),该字段被指派为INTEGER亲和性。
- 如果为字段声明的类型中包含了’CHAR’、’CLOB’或’TEXT’(无论大小写),该字段被指派为TEXT亲和性。如’VARCHAR’包含了’CHAR’,所以被指派为TEXT亲和性。
- 如果为字段声明的类型中包含了’BLOB’(无论大小写),或者没有为该字段声明类型,该字段被指派为NONE亲和性。
注意:如果没有为字段声明类型,该字段的亲和性为NONE,在这种情况下,所有的值都将以它们本身的(或从它们的表示法中推断的)存储类存储。如果你暂时还不确定要往一个字段里放什么内容,或准备将来修改,用NONE亲和性是一个好的选择。但SQLite默认的亲和性是NUMERIC。例如,如果为一定字段声明了类型JUJYFRUIT,该字段的亲和性不是NONE,因为SQLite不认识这种类型,会给它指派默认的NUMERIC亲和性。所以,与其用一个不认识的类型最终得到NUMERIC亲和性,还不如不为它指定类型,从而使它得到NONE亲和性。
亲和性和存储
亲和性对值如何存储到字段有影响,规则如下:
- 一个NUMERIC字段可能包括所有5种存储类。一个NUMERIC字段具有数字存储类的偏好(INTEGER和REAL)。当一个TEXT值被插入到一个NUMERIC字段,将会试图将其转化为INTEGER存储类;如果转化失败,将会试图将其转化为REAL存储类;如果还是失败,将会用TEXT存储类来存储。
- 一个INTEGER字段的处理很像NUMERIC字段。一个INTEGER字段会将REAL值按REAL存储类存储。也就是说,如果这个REAL值没有小数部分,就会被转化为INTEGER存储类。INTEGER字段将会试着将TEXT值按REAL存储;如果转化失败,将会试图将其转化为INTEGER存储类;如果还是失败,将会用TEXT存储类来存储。
- 一个TEXT字段将会把所有的INTEGER或REAL值转化为TEXT。
- 一个NONE字段不试图做任何类型转化。所有值按它们本身的存储类存储。
- 没有字段试图向NULL或BLOB值转化——如无论用什么亲和性。NULL和BLOB值永远都按本来的方式存储在所有字段。
这些规则初看起来比较复杂,但总的设计目标很简单,如果你需要,SQLite会尽量模仿其它的关系型数据库。也就是说,如果你将SQLite看成是一个传统数据库,类型亲和性将会按你的期望来存储值。如果你声明了一个INTEGER字段,并向里面放一个整数,就会按整数来存储。如果你声明了一个具有TEXT, CHAR或VARCHAR类型的字段并向里放一个整数,整数将会转化为TEXT。可是,如果你不遵守这些规定,SQLite也会找到办法来存储你的值。
如下例子展示了亲和性是如何工作的:
存储类和类型转换
关于存储类,需要关注的另一件事是存储类有时会影响到值如何进行比较。特别是SQLite有时在进行比较之前,会将值在数字存储类(INTEGER和REAL)和TEXT之间进行转换。为进行二进制的比较,遵循如下规则:
- 当一个字段值与一个表达式的结果进行比较,字段的亲和性会在比较之前应用于表达式的结果。
- 当两个字段值进行比较,如果一个字段拥有INTEGER或NUMERIC亲和性而另一个没有,NUMERIC亲和性会应用于非NUMERIC字段的TEXT值。
- 当两个表达式进行比较,SQLite不做任何转换。如果两个表达式有相似的存储类,则直接按它们的值进行比较;否则按类值进行比较。
总结
这里主要介绍了Sqlite的一些基本概念和数据类型的特性。关于Sqlite其他内容接下来文章继续介绍。继续阅读下一篇《Sqlite全面学习(二)》。
【工匠若水 http://blog.csdn.net/yanbober】
首先,看到此篇文章的人,肯定都对sqlite有所了解了,那些开场白之类的废话就不多写了,下面就简单的介绍sqlite的一些基本知识及简单操作,可能会有错漏的地方,欢迎批评指正,我也会不断学习并完善这篇文章
1.特性
2.数据类型
3.支持的SQL
COMMIT TRANSACTION
CREATE TABLE
CREATE TRIGGER
CREATE VIEW
DETACH DATABASE
DROP TABLE
DROP TRIGGER
DROP VIEW
EXPLAIN
expression
INSERT
ON CONFLICT clause
PRAGMA
REPLACE
ROLLBACK TRANSACTION
SELECT
UPDATE
4.数据库的简单使用:
1)创建数据库
具体操作如下:
操作如下:
4)查询表中内容
我们再插入一个更长的试试
看起来第一列的长度完全不受限制
6)查询一个数据库中所有的表名
sqlite数据库中有一个系统建立的表,sqlite_master,上面第2)小节也有调用,查询这个表就可以得到数据库中所有的关于此表,官网FAQ定义如下:
CREATE TABLE sqlite_master(type TEXT, name TEXT, tal_name TEXT, rootpage INTEGER, sql TEXT);
7)sqlite的输出格式
默认的输出格式是“列表”。在列表模式下,每条查询结果记录被写在一行中并且每列之间以一个字符串分隔符隔开,默认的分隔符为管道符号"|"。sqlite支持的输出格式包括:csv column html insert line list tabs tcl
具体使用方法如下:
9)查看所有的表的创建语句
10)删除记录
11)数据库的导入与导出
即备份一个一模一样的数据库,首先看一下一个新的命令可以得到一个用来创建当前数据库的原子操作集合。我们可以利用这个集合来创建一个新的一模一样的数据库
以上也是从网上查出并加以总结,水平有限,欢迎交流。
下一篇SQLITE文章 sqlite之我见--C/C++ API接口介绍中,我会介绍一下一些常用的C/C++ API接口。
个人比较喜欢sqlite, 使用最方便,唯一的准备工作是下载250K的源;而且作者很热心,有问必答。
以下演示一下使用sqlite的步骤,先创建一个数据库,然后查询其中的内容。2个重要结构体和5个主要函数:
sqlite3 *pdb, 数据库句柄,跟文件句柄FILE很类似
sqlite3_stmt *stmt, 这个相当于ODBC的Command对象,用于保存编译好的SQL语句
sqlite3_open(), 打开数据库
sqlite3_exec(), 执行非查询的sql语句
sqlite3_prepare(), 准备sql语句,执行select语句或者要使用parameter bind时,用这个函数(封装了sqlite3_exec).
Sqlite3_step(), 在调用sqlite3_prepare后,使用这个函数在记录集中移动。
Sqlite3_close(), 关闭数据库文件
还有一系列的函数,用于从记录集字段中获取数据,如
sqlite3_column_text(), 取text类型的数据。
sqlite3_column_blob(),取blob类型的数据
sqlite3_column_int(), 取int类型的数据
…
2:sqlite数据类型介绍
在进行数据库Sql操作之前,首先有个问题需要说明,就是Sqlite的数据类型,和其他的数据库不同,Sqlite支持的数据类型有他自己的特色,这个特色有时会被认为是一个潜在的缺点,但是这个问题并不在我们的讨论范围之内。
大多数的数据库在数据类型上都有严格的限制,在建立表的时候,每一列都必须制定一个数据类型,只有符合该数据类型的数据可以被保存在这一列当中。而在Sqlite 2.X中,数据类型这个属性只属于数据本生,而不和数据被存在哪一列有关,也就是说数据的类型并不受数据列限制(有一个例外:INTEGER PRIMARY KEY,该列只能存整型数据)。
但是当Sqlite进入到3.0版本的时候,这个问题似乎又有了新的答案,Sqlite的开发者开始限制这种无类型的使用,在3.0版本当中,每一列开始拥有自己的类型,并且在数据存入该列的时候,数据库会试图把数据的类型向该类型转换,然后以转换之后的类型存储。当然,如果转换被认为是不可行的,Sqlite仍然会存储这个数据,就像他的前任版本一样。
举个例子,如果你企图向一个INTEGER类型的列中插入一个字符串,Sqlite会检查这个字符串是否有整型数据的特征, 如果有而且可以被数据库所识别,那么该字符串会被转换成整型再保存,如果不行,则还是作为整型存储。
总的来说,所有存在Sqlite 3.0版本当中的数据都拥有以下之一的数据类型:
空(NULL):该值为空
整型(INTEGEER):有符号整数,按大小被存储成1,2,3,4,6或8字节。
实数(REAL):浮点数,以8字节指数形式存储。
文本(TEXT):字符串,以数据库编码方式存储(UTF-8, UTF-16BE 或者 UTF-16-LE)。
BLOB:BLOB数据不做任何转换,以输入形式存储。
ps: 在关系数据库中,CLOB和BLOB类型被用来存放大对象。BOLB表示二进制大对象,这种数据类型通过用来保存图片,图象,视频等。CLOB表示字符大对象,能够存放大量基于字符的数据。
对应的,对于数据列,同样有以下的数据类型:
- TEXT
- NUMERIC
- INTEGER
- REAL
- NONE
数据列的属性的作用是确定对插入的数据的转换方向:
- TEXT 将数据向文本进行转换,对应的数据类型为NULL,TEXT 或 BLOB
- NUMERIC 将数据向数字进行转换,对应的数据类型可能为所有的五类数据,当试图存入文本时将执行向整型或浮点类型的转换(视具体的数值而定),转换若不可行,则保留文本类型存储,NULL或BLOB不做变化
- INTEGER 将数据向整型转换,类似于NUMERIC,不同的是没有浮点标志的浮点数将转换为整型保存
- REAL 将数据向浮点数类型转换,类似于NUMERIC,不同的是整数将转换为浮点数保存
- NULL 不做任何转换的数据列类型
实例代码如下,
附件工程可直接编译,例子使用了blob数据类型。
#include "sqlite3.h" //包含一个头文件就可以使用所以sqlite的接口了
#include "stdlib.h"
#include "stdio.h"
#include "string.h"
#pragma comment(lib, "sqlite.lib") //我把sqlite编译成了一个静态的lib文件。
void createdb();
void querydb();
int main()
{
createdb();
querydb();
return 0;
}
void createdb()
{
int ret;
sqlite3 *pdb = 0;
sqlite3_stmt *stmt = 0;
char *error = 0;
char *sql = "insert into table1 values('value11',:aaa)";
int index;
static void *value = "asdfadsfasdfjasdfjaksdfaskjdfakdsfaksfja";
ret = sqlite3_open("db1.sdb", &pdb); //打开数据库,跟打开文本文件一样
if( ret != SQLITE_OK )
return;
ret = sqlite3_exec(pdb, "create table table1(col1 char(20), col2 BLOB)", 0,0, &error );
if( ret != SQLITE_OK )
return;
ret = sqlite3_prepare(pdb, sql,strlen(sql), &stmt, &error);
if( ret != SQLITE_OK )
return;
index = sqlite3_bind_parameter_index(stmt, ":aaa");
ret = sqlite3_bind_blob(stmt, index, value, strlen(value), SQLITE_STATIC);
if( ret != SQLITE_OK )
return;
ret = sqlite3_step(stmt);
if( ret != SQLITE_DONE )
return;
sqlite3_close(pdb);
}
void querydb()
{
int ret;
sqlite3 *pdb = 0;
sqlite3_stmt *pstmt = 0;
char *error = 0;
char *sql = "select * from table1";
int len;
int i;
char *name;
void *value;
ret = sqlite3_open("db1.sdb", &pdb);
if( ret != SQLITE_OK )
return;
ret = sqlite3_prepare(pdb, sql, strlen(sql), &pstmt, &error);
if( ret != SQLITE_OK )
return;
while( 1 )
{
ret = sqlite3_step(pstmt);
if( ret != SQLITE_ROW )
break;
name = sqlite3_column_text(pstmt, 0);
value = sqlite3_column_blob(pstmt, 1);
len = sqlite3_column_bytes(pstmt,1 );
}
}
实例二:SQLite中如何用api操作blob类型的字段
在实际的编程开发当中我们经常要处理一些大容量二进制数据的存储,如图片或者音乐等等。对于这些二进制数据(blob字段)我们不能像处理普通的文本那样简单的插入或者查询,为此SQLite提供了一组函数来处理这种BLOB字段类型。下面的代码演示了如何使用这些API函数。
首先我们要建立一个数据库:
sqlite3_exec(db, "CREATE TABLE list (fliename varchar(128) UNIQUE, fzip blob);", 0, 0, &zErrMsg);
//由于mmmm.rar是一个二进制文件,所以要在使用insert语句时先用?号代替
sqlite3_prepare(db, "insert into list values ('mmmm.rar',?);", -1, &stat, 0);
FILE *fp;
long filesize = 0;
char * ffile;
fp = fopen("mmmm.rar", "rb");
if(fp != NULL)
{
//计算文件的大小
fseek(fp, 0, SEEK_END);
filesize = ftell(fp);
fseek(fp, 0, SEEK_SET);
//读取文件
ffile = new char[filesize+1];
size_t sz = fread(ffile, sizeof(char), filesize+1, fp);
fclose(fp);
}
//将文件数据绑定到insert语句中,替换“?”部分
sqlite3_bind_blob(stat, 1, ffile, filesize, NULL);
//执行绑定之后的SQL语句
sqlite3_step(stat);
这时数据库当中已经有了一条包含BLOB字段的数据。接下来我们要读取这条数据:
//选取该条数据
sqlite3_prepare(db, "select * from list;", -1, &stat, 0);
sqlite3_step(stat);
//得到纪录中的BLOB字段
const void * test = sqlite3_column_blob(stat, 1);
//得到字段中数据的长度
int size = sqlite3_column_bytes(stat, 1);
//拷贝该字段
sprintf(buffer2, "%s", test);
此时可以将buffer2写入到文件当中,至此BLOB数据处理完毕。
实例三:sqlite 中用blob存储图片和取出图片
#include<iostream>
#include<string>
#include<sqlite3.h>
using namespace std;
int main()
{
sqlite3 *db;
sqlite3_stmt *stat;
char *zErrMsg = 0;
char buffer2[1024]="0";
sqlite3_open("./MetaInfo.db", &db);
int result;
if(result)
{
cout<<"Open the database sqlite.db failed"<<endl;
}
else
cout<<"Open the database sqlite.db sucessfully"<<endl;
sqlite3_exec(db, "CREATE TABLE list (fliename varchar(128) UNIQUE, fzip blob);", 0, 0, &zErrMsg);
sqlite3_prepare(db, "insert into list values ('./data/2.bmp',?);", -1, &stat, 0);
FILE *fp;
long filesize = 0;
char * ffile;
fp = fopen("./data/2.bmp", "rb");
if(fp != NULL)
{
fseek(fp, 0, SEEK_END);
filesize = ftell(fp);
fseek(fp, 0, SEEK_SET);
ffile = new char[filesize+1];
size_t sz = fread(ffile, sizeof(char), filesize+1, fp);
fclose(fp);
}
sqlite3_bind_blob(stat, 1, ffile, filesize, NULL);
sqlite3_step(stat);
sqlite3_prepare(db, "select * from list;", -1, &stat, 0);
sqlite3_step(stat);
const void * test = sqlite3_column_blob(stat, 1);
int size = sqlite3_column_bytes(stat, 1);
sprintf(buffer2, "%s", test);
FILE *fp2;
fp2 = fopen("outfile.png", "wb");
if(fp2 != NULL)
{
size_t ret = fwrite(test, sizeof(char), size, fp2);
fclose(fp2);
}
delete(ffile);
sqlite3_finalize(stat);
sqlite3_close(db);
return 0;
}
一.建立数据库
sqlite3.exe test.db
二.双击sqlite-3_6_16目录下的程序sqlite3.exe,即可运行
三.退出
.exit
或者
.quit
四.SQLite支持如下5种数据类型
1.NULL:空值。
2.INTEGER:带符号的整型,具体取决有存入数字的范围大小。
3.REAL:浮点数字,存储为8-byte IEEE浮点数。
4.TEXT:字符串文本。
5.BLOB:二进制对象。
五.联系人表格结构如下
create table contact(id integer primary key autoincrement,
lastname varchar(20),firstname varchar(20),
mobile varchar(30), telephone varchar(20),
email varchar(30), company varchar(50),
department varchar(16),address varchar(80),
id1 interger,id2 integer, updatetime datetime);
六.查看数据库有哪些数据表
命令是:.tables
七.如何插入一条记录
insert into contact(lastname,firstname,mobile,telephone,updatetime) values('刘','畅','13910128132','010-81749136','2009-07-22');
八.查看数据表的结构
针对整个数据库
.schema
针对仅仅是contact联系人该表
.schema contact 注意没有分号
九.如何打开一个已经创建的数据库
sqlite3 test.db
十.如何解决如下问题
SQL error: near "sqlite3": syntax error
SQL指令都是以分号(;)结尾的。如果遇到两个减号(--)则代表注解,sqlite3会略过去
十一.如何建立索引
create index index_name on table_name(field_to_be_indexed);
十二.如何删除一张数据表
drop table contact;
十三.查看当前的数据库
.database
十四.如何删除一个数据表的数据
delete from contact;
十五.如何导入一个文件到某个表中
.import 文件路径 表名
注意这是非SQL语句,所以不加分号
十六.如何设置文件字段的分隔符
.separator “,”
.import e:/contact.txt contact
十七.如何查看当前sqllite字段的分隔符是什么?
.show
十八.如何将查询结果导出到一个文件
第一步:.output a.txt
第二步:执行要导出的SQL语句
第三步:.output stdout
十九.SQL查询语句
select * from film order by year limit 10;
select * from film order by year desc limit 10;
select count(*) from film;
select * from film where starring like 'Jodie%';
select * from film where starring='Jodie Foster';
select title, year from film order by year desc limit 10;
select columns from table_name where expression;
最常见的用法,当然是倒出所有数据库的内容:
select * from film;
如果资料太多了,我们或许会想限制笔数:
select * from film limit 10;
或是照着电影年份来排列:
select * from film order by year limit 10;
或是年份比较近的电影先列出来:
select * from film order by year desc limit 10;
或是我们只想看电影名称跟年份:
select title, year from film order by year desc limit 10;
查所有茱蒂佛斯特演过的电影:
select * from film where starring='Jodie Foster';
查所有演员名字开头叫茱蒂的电影('%' 符号便是 SQL 的万用字符):
select * from film where starring like 'Jodie%';
查所有演员名字以茱蒂开头、年份晚于1985年、年份晚的优先列出、最多十笔,只列出电影名称和年份:
select title, year from film where starring like 'Jodie%' and year >= 1985 order by year desc limit 10;
有时候我们只想知道数据库一共有多少笔资料:
select count(*) from film;
有时候我们只想知道1985年以后的电影有几部:
select count(*) from film where year >= 1985;
(进一步的各种组合,要去看SQL专书,不过你大概已经知道SQL为什么这么流行了:这种语言允许你将各种查询条件组合在一起──而我们还没提到「跨数据库的联合查询」呢!)
如何更改或删除资料
了解select的用法非常重要,因为要在sqlite更改或删除一笔资料,也是靠同样的语法。
例如有一笔资料的名字打错了:
update film set starring='Jodie Foster' where starring='Jodee Foster';
就会把主角字段里,被打成'Jodee Foster'的那笔(或多笔)资料,改回成Jodie Foster。
delete from film where year < 1970;
就会删除所有年代早于1970年(不含)的电影了。
其他sqlite的特别用法
sqlite可以在shell底下直接执行命令:
sqlite3 film.db "select * from film;"
输出 HTML 表格:
sqlite3 -html film.db "select * from film;"
将数据库「倒出来」:
sqlite3 film.db ".dump" > output.sql
利用输出的资料,建立一个一模一样的数据库(加上以上指令,就是标准的SQL数据库备份了):
sqlite3 film.db < output.sql
在大量插入资料时,你可能会需要先打这个指令:
begin;
插入完资料后要记得打这个指令,资料才会写进数据库中:
commit;
创建数据库文件:
>SQLite3 d:/test.db 回车
就生成了一个test.db在d盘。
这样同时也SQLite3挂上了这个test.db
2)
用.help可以看看有什么命令
>.help 回车即可
3)可以在这里直接输入SQL语句创建表格 用;结束 ,然后回车就可以看到了
4)看看有创建了多少表
>.tables
5)看表结构
>.schema 表名
6)看看目前的数据库
>.database
7)如果要把查询输出到文件
>.output 文件名
> 查询语句;
查询结果就输出到了文件c:/query.txt
把查询结果用屏幕输出
>.output stdout
8)把表结构输出,同时索引也会输出
.dump 表名
9)退出
>.exit 或者.quit
2。从http://sqlite.phxsoftware.com/ 下载Ado.net驱动。
下载了安装,在安装目录中存在System.Data.SQLite.dll
我们只需要拷贝这个文件到引用目录,并添加引用即可对SQLite数据库操作了
所有的Ado.net对象都是以SQLite开头的,比如SQLiteConnection
连接串只需要如下方式
Data Source=d:/test.db 或者DataSource=test.db--应用在和应用程序或者.net能够自动找到的目录
剩下的就很简单了~~
3。SQL语法
由于以前用SQLServer或者ISeries,所以DDL的语法很汗颜
1)创建一个单个Primary Key的table
CREATE TABLE [Admin] (
[UserName] [nvarchar] (20) PRIMARY KEY NOT NULL ,
[Password] [nvarchar] (50) NOT NULL ,
[Rank] [smallint] NOT NULL ,
[MailServer] [nvarchar] (50) NOT NULL ,
[MailUser] [nvarchar] (50) NOT NULL ,
[MailPassword] [nvarchar] (50) NOT NULL ,
[Mail] [nvarchar] (50) NOT NULL
) ;
2)创建一个多个Primary Key的table
CREATE TABLE [CodeDetail] (
[CdType] [nvarchar] (10) NOT NULL ,
[CdCode] [nvarchar] (20) NOT NULL ,
[CdString1] [ntext] NOT NULL ,
[CdString2] [ntext] NOT NULL ,
[CdString3] [ntext] NOT NULL,
PRIMARY KEY (CdType,CdCode)
) ;
3)创建索引
CREATE INDEX [IX_Account] ON [Account]([IsCheck], [UserName]);
还可以视图等等。
4.还有很有用的SQL
Select * from Sqlite_master
Select datetime('now')
Select date('now')
Select time('now')
SQLite 内建函数表
算术函数 | |
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() |
|
oh,还有就是看到有人说,好像成批插入的时候,启动事务,比不启动事务快n倍
还有就是尽量使用参数化的SQL,估计和商用DB一样能够自动Prepare.
===========
sqlite可以在shell/dos command底下直接执行命令:
sqlite3 film.db "select * from film;"
输出 HTML 表格:
sqlite3 -html film.db "select * from film;"
将数据库「倒出来」:
sqlite3 film.db ".dump" > output.sql
利用输出的资料,建立一个一模一样的数据库(加上以上指令,就是标准的SQL数据库备份了):
sqlite3 film.db < output.sql
在大量插入资料时,你可能会需要先打这个指令:
begin;
插入完资料后要记得打这个指令,资料才会写进数据库中:
commit;
SQLITE深入------常见问题
如何建立自动增长字段?
简短回答:声明为 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错误代码。
多个应用程序或一个应用程序的多个实例可以同时访问同一个数据库文件吗?
多个进程可同时打开同一个数据库。多个进程可以同时进行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 函数调整这一行为。
在SQLite数据库中如何列出所有的表和索引?
如果你运行 sqlite3 命令行来访问你的数据库,可以键入 “.tables”来获得所有表的列表。或者,你可以输入“.schema” 来看整个数据库模式,包括所有的表的索引。输入这些命令,后面跟一个LIKE模式匹配可以限制显示的表。
在一个 C/C++ 程序中(或者脚本语言使用 Tcl/Ruby/Perl/Python 等) 你可以在一个特殊的名叫 SQLITE_MASTER 上执行一个SELECT查询以获得所有 表的索引。每一个 SQLite 数据库都有一个叫 SQLITE_MASTER 的表, 它定义数据库的模式。 SQLITE_MASTER 表看起来如下:
CREATE TABLE sqlite_master (
type TEXT,
name TEXT,
tbl_name TEXT,
rootpage INTEGER,
sql TEXT
);
对于表来说,type 字段永远是 'table',name 字段永远是表的名字。所以,要获得数据库中所有表的列表,使用下列SELECT语句:
SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;
对于索引,type 等于 'index', name 则是索引的名字,tbl_name 是该索引所属的表的名字。不管是表还是索引,sql字段是原先用 CREATE TABLE 或 CREATE INDEX 语句创建它们时的命令文本。对于自动创建的索引(用来实现 PRIMARY KEY 或 UNIQUE 约束),sql字段为NULL。
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
(SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master)
WHERE type='table'
ORDER BY name
在SQLite中,VARCHAR字段最长是多少?
SQLite 不强制 VARCHAR 的长度。 你可以在 SQLITE 中声明一个 VARCHAR(10),SQLite还是可以很高兴地允许你放入500个字符。 并且这500个字符是原封不动的,它永远不会被截断。
SQLite支持二进制大对象吗?
SQLite 3.0 及以后版本允许你在任何列中存储 BLOB 数据。 即使该列被声明为其它类型也可以。
在SQLite中,如何在一个表上添加或删除一列?
SQLite 有有限地 ALTER TABLE 支持。你可以使用它来在表的末尾增加一列,可更改表的名称。 如果需要对表结构做更复杂的改变,则必须重新建表。重建时可以先将已存在的数据放到一个临时表中,删除原表, 创建新表,然后将数据从临时表中复制回来。
如,假设有一个 t1 表,其中有 "a", "b", "c" 三列, 如果要删除列 c ,以下过程描述如何做:
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;
在数据库中删除了很多数据,但数据库文件没有变小,是Bug吗?
不是。当你从SQLite数据库中删除数据时, 未用的磁盘空间将会加入一个内部的“自由列表”中。 当你下次插入数据时,这部分空间可以重用。磁盘空间不会丢失,但也不会返还给操作系统。
如果删除了大量数据,而又想缩小数据库文件占用的空间,执行 VACUUM 命令。 VACUUM 将会从头重新组织数据库。这将会使用数据库有一个空的“自由链表”, 数据库文件也会最小。但要注意的是,VACUUM 的执行会需要一些时间(在SQLite开发时,在Linux上,大约每M字节需要半秒种),并且, 执行过程中需要原数据库文件至多两倍的临时磁盘空间。
对于 SQLite 3.1版本,一个 auto-vacumm 模式可以替代 VACUUM 命令。 可以使用 auto_vacuum pragma 打开。
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.
数据库被 VACUUMed
一个用户存储过程定义被删除或改变。
一个 collation 序列定义被删除或改变。
认证函数被改变。
在所有情况下,解决方法是重新编译并执行该SQL语句。 因为一个已准备好的语句可以由于其它进程改变数据库模式而失效,所有使用 sqlite3_prepare()/sqlite3_step()/sqlite3_finalize() API 的代码都应准备处理 SQLITE_SCHEMA错误。下面给出一个例子:
int rc;
sqlite3_stmt *pStmt;
char zSql[] = "SELECT .....";
do {
/* Compile the statement from SQL. Assume success. */
sqlite3_prepare(pDb, zSql, -1, &pStmt, 0);
while( SQLITE_ROW==sqlite3_step(pStmt) ){
/* Do something with the row of available data */
}
/* Finalize the statement. If an SQLITE_SCHEMA error has
** occured, then the above call to sqlite3_step() will have
** returned SQLITE_ERROR. sqlite3_finalize() will return
** SQLITE_SCHEMA. In this case the loop will execute again.
*/
rc = sqlite3_finalize(pStmt);
} while( rc==SQLITE_SCHEMA );
如何在字符串中使用单引号(')?
SQL 标准规定,在字符串中,单引号需要使用逃逸字符,即在一行中使用两个单引号。在这方面 SQL 用起来类似Pascal 语言。 SQLite 尊循标准。如:
INSERT INTO xyz VALUES('5 O''clock');
Sqlite中如何返回本地化当前时间?
在做ClinicOS的时候遇到一个问题,在保存病历登记时间时,我使用了“CURRENT_TIMESTAMP”,但这有个问题,它返回的是UTC Time,这对我们中国人没啥用,一直希望能想办法将它转为localtime。今天刚好有空,所以去查了查Sqlite的Mail List,果然也有人遇到了这个问题,我从一篇名为《translate time comparison statement》(http://www.mail-archive.com/sqlite-users@sqlite.org /msg12350.html)中看到这样的回复:
二十.如何更新表中数据
update contact set lastname=’江南七怪’where id = 1028
update contact set lastname='江南七怪', mobile='13912345678' where id=1028;
二十一.如何一次插入多个数据
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);