Sqlite使用手册
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. 愿你宽心与人分享,所取不多于你所施予
优点:
支援大多数的SQL指令。
一个档案就是一个数据库。不需要安装数据库服务器软件。
完整的Unicode支援(因此没有跨语系的问题)。
速度很快。
建立数据库档案
用sqlite3建立数据库的方法很简单,只要在shell下键入:
# sqlite3 foo.db如果目录下没有foo.db,sqlite3就会建立这个数据库。
使用.help可以取得求助,.quit则是离开(请注意:不是quit)
SQL的指令格式
所以的SQL指令都是以分号(;)结尾的。如果遇到两个减号(--)则代表注解,sqlite3会略过去。
建立资料表
假设我们要建一个名叫film的资料表,只要键入以下指令就可以了:
create table film(title, length, year, starring);这样我们就建立了一个名叫film的资料表,里面有name、length、year、starring四个字段。
这个create table指令的语法为:
create table table_name(field1, field2, field3, ...);table_name是资料表的名称,fieldx则是字段的名字。sqlite3与许多SQL数据库软件不同的是,它不在乎字段属于哪一种资
料型态:sqlite3的字段可以储存任何东西:文字、数字、大量文字(blub),它会在适时自动转换。
建立索引
如果资料表有相当多的资料,我们便会建立索引来加快速度。好比说:
create index film_title_index on film(title);意思是针对film资料表的name字段,建立一个名叫film_name_index的索引。这个指令的语法为
create index index_name on table_name(field_to_be_indexed);一旦建立了索引,sqlite3会在针对该字段作查询时,自动使用该索引。这一切的操作都是在幕后自动发生的,无须使用者特别指令。
加入一笔资料
接下来我们要加入资料了,加入的方法为使用insert into指令,语法为:
insert into table_name values(data1, data2, data3, ...);例如我们可以加入
insert into film values ('Silence of the Lambs, The', 118, 1991, 'Jodie Foster');insert into film values ('Contact', 153, 1997, 'Jodie Foster');insert into film values ('Crouching Tiger, Hidden Dragon', 120, 2000, 'Yun-Fat Chow');insert into film values ('Hours,
The', 114, 2002, 'Nicole Kidman');如果该字段没有资料,我们可以填NULL。
查询资料
讲到这里,我们终于要开始介绍SQL最强大的select指令了。我们首先简单介绍select的基本句型:
select columns from table_name where expression;最常见的用法,当然是倒出所有数据库的内容:
select * from film;如果资料太多了,我们或许会想限制笔数:
select * from film limit 10;或是照着电影年份来排列:
select * from film order by year limit 10;或是年份比较
C/C++中调用SQLITE3的基本步骤
sqlite3 *pDB = NULL;
char * errMsg = NULL;
//打开一个数据库,如果改数据库不存在,则创建一个名字为databaseName的数据库文件
int rc = sqlite3_open(databaseName, &pDB);
if(rc)
{
cout << " Open the database " << databaseName << " failed" << endl;
}
//如果创建成功,添加表
else
{
cout << "create the database successful!" << endl;
//creat the table
int i;
for(i=1; i
//函数参数:第一个为操作数据库的指针,第二句为SQL命令字符串
//第三个参数为callback函数,这里没有用,第四个参数为callback函数
//中的第一个参数,第五个为出错信息
rc = sqlite3_exec(pDB, "CREATE TABLE chn_to_eng(chinese QString,
english QString)", 0, 0, &errMsg);
if(rc == SQLITE_OK)
cout << "create the chn_to_eng table successful!" << endl;
else
cout << errMsg << endl;
//同上,插入另一个表
rc = sqlite3_exec(pDB, "CREATE TABLE eng_to_chn(english QString,
chinese QString)", 0, 0, &errMsg);
if(rc == SQLITE_OK)
cout << "create the eng_to_chn table successful!" << endl;
else
cout << errMsg << endl;
}
、、、、、、
//往表中添加数据
char chn[]="...";
char eng[]="...";
char value[500];
//定义一条参数SQL命令,其中chn,eng为需要插入的数据
sprintf(value, "INSERT INTO chn_to_eng(chinese, english) VALUES('%s', '%s')", chn, eng);
//use the SQLITE C/C++ API to create and adjust a database.
rc = sqlite3_exec(pDB,
value,
0, 0, &errMsg);
//查询一条记录
char value[500];
//定义一条查询语句,其中条件为当english为target时的中文记录
//print_result_cb为callback函数,在其中可以得到查询的结果,具体见下文
sprintf(value, "SELECT chinese FROM eng_to_chn where english='%s' ", target);
rc = sqlite3_exec(pDB,
value,
print_result_cb, 0, &errMsg);
if(rc == SQLITE_OK)
{
cout << "select the record successful!" << endl;
}
else
{
cout << errMsg << endl;
return false;
}
.......
}
//callback回调函数print_result_cb的编写,其中data为sqlite3_exec中的第四个参数,第二个参数是栏的数目,第三个是栏的名字,第四个为查询得到的值。这两个函数输出所有查询到的结果
int print_result_cb(void* data, int n_columns, char** column_values,
char** column_names)
{
static int column_names_printed = 0;
int i;
if (!column_names_printed) {
print_row(n_columns, column_names);
column_names_printed = 1;
}
print_row(n_columns, column_values);
return 0;
}
void
print_row(int n_values, char** values)
{
int i;
for (i = 0; i < n_values; ++i) {
if (i > 0) {
printf("\t");
}
printf("%s", values[i]);
}
printf("\n");
}
大致过程就是如此,具体可以参考SQLITE的API函数说明,见www.sqlite.org
===================================================
SQLITE数据类型
SQLite与其他常见的DBMS的最大不同是它对数据类型的支持。其他常见的DBMS通常支持强类型的数据,也就是每一列的类型都必须预先指定,但是SQLite采用的是弱类型的字段。实际上,其内部仅有下列五种存储类型:
NULL: 表示一个NULL值
INTEGER: 用来存储一个整数,根据大小可以使用1,2,3,4,6,8位来存储.
REAL: IEEE 浮点数
TEXT: 按照字符串来存储
BLOB: 按照二进制值存储,不做任何改变.
要注意,这些类型是值本身的属性,而不是列的属性.
但是为了和其他DBMS(以及SQL标准)兼容,在其create table语句中可以指定列的类型,为此,SQLite有个列相似性的概念(Column Affinity). 列相似性是列的属性,SQLite有以下几种列相似性:
TEXT: TEXT列使用NULL,TEXT或者BLOB存储任何插入到此列的数据,如果数据是数字,则转换为TEXT.
NUMERIC: NUMERIC列可以使用任何存储类型,它首先试图将插入的数据转换为REAL或INTEGER型的,如果成功则存储为REAL和INTEGER型,否则不加改变的存入.
INTEGER:和NUMERIC类似,只是它将可以转换为INTEGER值都转换为INTEGER,如果是REAL型,且没有小数部分,也转为INTEGER
REAL: 和NUMERIC类型 只是它将可以转换为REAL和INTEGER值都转换为REAL.
NONE:不做任何改变的尝试.
SQLite根据create table语句来决定每个列的列相似性.规则如下(大小写均忽略):
1. 如果数据类型中包括INT,则是INTEGER
2. 如果数据类型中包括CHAR,CLOB,TEXT则是TEXT
3. 如果数据类型中包括BLOB,或者没有指定数据类型,则是NONE
4. 如果数据类型中包括REAL,FLOA或者DOUB,则是REAL
5. 其余的情况都是NUMERIC
由上可知,对于sqlite来说 char,varchar,nchar,nvarchar等都是等价的,且后面最大长度也是没有意义的。但是对于其他DBMS却不是相同的。另外,列相似 性仅仅是向Sqlite提出了一个存储数据的建议,即使实际存储的数据类型和列相似性不一致,SQLite还是可以成功插入的,下面给出一个例子来说明下 以上论述,注意,这个例子需要在SQLite的命令行下运行,如果在SQLite Expert工具下执行,SQLite会进行一些额外的处理。
如下图,创建一个新表,两列的类型分别是int 和varchar,但是还是可以插入其他类型的数据,并且可以正确读出。
要注意SQLite的这种特性可能会给SQLite的ADO驱动造成一些麻烦,因为.NET都是强类型的语言,必须把数据库中的字段转换为合适的类型,所以在插入数据的时候,还是应该严格的按照create table中的定义插入数据。
(2)自增列
在SQL Server中,只需要指定identity(1,1)就可以设定自增列,但是在SQLite中不支持这样做。在SQLite中,任何一张表都有一个字段类型是Integer,且是自增的,这个列是作为B树的索引的,它的名字是ROWID,如下图所示:
test2表虽然只有一列,但是ROWID列还是存在的。在程序中对任何一张表都可以使用ROWID作为自增列。不过这样可能导致和其他数据库的不 兼容,SQLite中如果一个列的声明类型是Integer,并且是主键,那么这个列的名字就成为ROWID的别名。注意,声明类型必须是 Integer,而不能是int或bigint之类。例如:
注意上面例子的最后3条语句,它显示了SQLite默认的自增列算法是在当前表中最大的数再加1,这样可能导致的结果是ID被重复使用——当最后一条数据被删除的时候。这与SQL Server的Identity列的行为是不一致的,例如:
SQL Server会记住每一次插入的序号,哪怕它已经被删除了。要实现SQL Server 这样的效果,需要使用autoincrement关键字。如下例所示:
不过 autoincrement关键字不被SQL Server支持(我不知道SQL 92标准中是否有此关键字),同样SQL Server的 indentity关键字在SQLite中也无法使用,因为SQLite只要求声明类型必须是integer才可以启用自增列。所以,我想不出什么方法能 使建库的脚本能够不加修改的被两种数据库使用。
(3) 日期函数
Sqlite的日期函数比较有特色,它的使用本质上是调用C的库函数strftime,基本使用方法如下:
(4) 不被支持的特性
用户自定义函数,存储过程
外键的约束(不过可以通过自定义触发器来替代)
right out join , full out join
SQLite语法备忘录
结构定义 | |||||||||||||||
CREATE TABLE |
创建新表。
语法:
|
||||||||||||||
CREATE VIEW |
创建一个视图(虚拟表),该表以另一种方式表示一个或多个表中的数据。 语法:
例子:
|
||||||||||||||
CREATE TRIGGER |
创建触发器,触发器是一种特殊的存储过程,在用户试图对指定的表执行指定的数据修改语句时自动执行。 语法:
|