首页 > 代码库 > SQLite数据库使用
SQLite数据库使用
一、安装SQLite3方法
1、字符界面
sudo apt-get install sqlite3
2、图形界面
sudo apt-get install sqliteman
二、SQLite数据类型
SQLite具有以下五种基本数据类型：
1、integer：带符号的整型（最多64位）。
2、real：8字节表示的浮点类型。
3、text：字符类型，支持多种编码（如UTF-8、UTF-16），大小无限制。
4、blob：任意类型的数据，大小无限制。
5、null：表示空值。
三、SQLite命令使用
1、创建、打开数据库:
sqlite3 *.db
提示：当*.db文件不存在时，sqlite会创建并打开数据库文件。当*.db文件存在时，sqlite会打开数据库文件。
2、退出数据库命令：
.quit 或 .exit
注意：SQL的语句格式：所有的SQL语句都是以分号结尾的，SQL语句不区分大小写。两个减号“--”则代表注释。
3、创建表：create 语句语法：
create table 表名称 (列名称1 数据类型, 列名称2 数据类型, 列名称3 数据类型, ...);创建一表格该表包含3列，列名分别是：“id”、“name”、“addr”。
在终端下输入：create table persons (id integer, name text, addr text);
4、创建表：create 语句 (设置主键)
在用sqlite设计表时，每个表都可以通过primary key手动设置主键(整型)，设置为主键的列数据不可以重复。语法：
create table 表名称 (列名称1 数据类型 primary key, 列名称2 数据类型, 列名称3 数据类型, ...)；
在终端下输入：create table persons (id integer primary key, name text, addr text);
5、查看表： .tables 查看数据表的结构： .schema
6、修改表：alter语句
在已有的表中添加或删除列。语法:(添加、删除-sqlite3暂不支持)
alter table 表名 add 列名 数据类型;
在终端下输入：alter table persons add sex text ;
7、删除表：drop table语句
用于删除表（表的结构、属性以及表的索引也会被删除）语法：drop table 表名称;
在终端输入：drop table persons ;
8、插入新行：inser into 语句(全部赋值)给一行中的所有列赋值。语法：insert into 表名 values (列值1, 列值2,列值3，列值4, ...);
注意：当列值为字符串时要加上‘’号。
在终端下输入：create table persons (id integer, name text, addr text);
insert into persons values (1, ‘lucy‘, ‘beijing‘);
9、插入新行：inser into 语句(部分赋值) 给一行中的部分列赋值。语法：insert into 表名 (列名1, 列名2, ...) values(列值1, 列值2, ...);
在终端下输入：insert into persons (id, name) values (1, ‘peter‘);
10、修改表中的数据：update 语句
使用where根据匹配条件，查找一行或多行，根据查找的结果修改表中相应行的列值(修改哪一列由列名指定)。
语法：update 表名 set 列1 = 值1 [, 列2 = 值2, ...] [匹配条件];
注意：当表中有多列、多行符合匹配条件时会修改相应的多行。当匹配条件为空时则匹配所有。
11、匹配：where 子句
where 子句用于规定匹配的条件：等于（=）、不等于（<>）、大于（>）、小于（<）、大于等于（>=）、小于等于（<=）。
匹配条件语法：（基础） where 列名 操作符 列值
在终端输入：update persons set id=2, addr=‘tianjing‘ where name=‘peter‘;
12、删除表中的数据：delete 语句
使用where根据匹配条件，查找一行或多行，根据查找的结果删除表中的查找到的行。语法：delete from 表名 [匹配条件];
注意：当表中有多列、多行符合匹配条件时会删除相应的多行。
在终端输入：delete from persons where name=‘peter‘;
13、查询：select 语句（基础）
于从表中选取数据，结果被存储在一个结果表中（称为结果集）。语法：
1、select * from 表名 [匹配条件];
2、select 列名1[, 列名2, ...] from 表名 [匹配条件];
提示：星号（*）是选取所有列的通配符。
在终端输入：insert into persons values (1, ‘peter‘, ‘tianjing‘);
insert into persons values (3, ‘bob‘, ‘hebei‘);
select * from persons;
select * from persons where id=1;
select name from persons;
select name from persons where id=1;
14、in 允许我们在 where 子句中规定多个值。匹配条件语法：where 列名 in (列值1, 列值2, ...)例：
1、select * from 表名 where 列名 in (值1, 值2, ...);
2、select 列名1[,列名2,...] from 表名 where列名 in (列值1, 列值2, ...)
在终端输入：select * from persons where id in (1, 2);
select name from persons where id in (1, 2);
15、and 可在 where 子语句中把两个或多个条件结合起来（多个条件之间是与的关系）。匹配条件语法：where 列1 = 值1 [and 列2 = 值2 and ...]例：
1、select * from 表名 where 列1 = 值1 [and列2 = 值2 and ...];
2、select 列名1[, 列名2, ...] from 表名where 列1 = 值1 [and 列2 = 值2 and ...];
在终端输入：select * from persons where id=1 and addr=‘beijing‘;
select name from persons where id=1 and addr=‘beijing‘;
16、or 可在 where 子语句中把两个或多个条件结合起来（多个条件之间是或的关系）。匹配条件语法：where 列1 = 值1 [or 列2 = 值2 or ...]例：
1、select * from 表名 where 列1 = 值1 [or 列2 = 值2 or ...];
2、select 列名1[,列名2,...] from 表名 列1 = 值1 [or 列2 = 值2 or ...];
在终端输入：select * from persons where addr=‘beijing‘ or addr=‘hebei‘;
17、操作符 between A and B 会选取介于A、B之间的数据范围。这些值可以是数值、文本或者日期。匹配条件语法：where 列名 between A and B例：
1、select * from 表名 where 列名 between A and B;
2、select 列名1[,列名2,...] from 表名 where列名 between A and B;
注：匹配字符串时会以ascii顺序匹配。
在终端输入：select * from persons where id between 1 and 3;
select * from persons where addr between ‘a‘ and ‘c‘;
18、like 用于模糊查找。匹配条件语法：where 列名 like 列值
1、若列值为数字相当于列名＝列值。
2、若列值为字符串可以用通配符“%”代表缺少的字符（一个或多个）。
在终端输入：select * from persons where id like 3;
select * from persons where addr like ‘%jing%‘;
19、not 可取出原结果集的补集。匹配条件语法：例：
1、where 列名 not in (列值1, 列值2, ...)
2、 where not (列1 = 值1 [and 列2 = 值2 and...])
3、where not (列1 = 值1 [or 列2 = 值2 or ...])
4、where 列名 not between A and B
5、where 列名 not like 列值
在终端输入：select * from persons where id not in ( 1 );
select * from persons where addr not like ‘%jing%‘;
20、order by 语句根据指定的列对结果集进行排序。默认按照升序对结果集进行排序，可使用 desc 关键字按照降序对结果集进行排序。例：
升序 ： select * from 表名 order by 列名;
降序 ： select * from 表名 order by 列名 desc;
在终端输入：select * from persons order by name;
select * from persons order by id;
select * from persons order by id desc;
四、SQLite C语言编程
1、int sqlite3_open(char *db_name,sqlite3 **db);
功能：打开数据库。
参数：db_name：数据库文件名，若文件名包含ASCII码表范围的之外的字符，则其必需是(UTF-8)编码。
sqlite3：数据库标识，此结构体为数据库操作句柄。通过此句柄可对数据库文件进行相应操作。
返回值：成功返回SQLITE_OK，失败返回非 SQLITE_OK。
2、int sqlite3_close(sqlite3 *db);
功能：关闭数据库、释放打开数据库时申请的资源。
参数：db：数据库的标识。
返回值：成功返回 SQLITE_OK。失败返回非 SQLITE_OK。
注意：sqlite3使用了两个库：pthread、dl，故链接时应加上 -lpthread和 -ldl。
3、sqlite3_exec函数:int sqlite3_exec(sqlite3 *db, const char *sql,exechandler_t callback,void *arg, char **errmsg);
功能：执行sql指向的SQL语句，若结果集不为空，函数会调用函数指针callback所指向的函数。
参数：db：数据库的标识。
sql：SQL语句（一条或多条），以’;’结尾。
callback：是回调函数指针，当这条语句执行之后，sqlite3会去调用你提供的这个函数。
arg：当执行sqlite3_exec的时候传递给回调函数的参数。
errmsg：存放错误信息的地址，执行失败后可以查阅这个指针。
打印错误信息方法：printf("%s\n", errmsg);
4、回调函数指针：typedef int (*exechandler_t)(void *para, int n_column, char **column_value,char **column_name);
功能：此函数由用户定义，当sqlite3_exec函数执行sql语句后，结果集不为空时sqlite3_exec函数会自动调用此函数，每次调用此函数时会把结果集的一行信息传给此函数。
参数：para：sqlite3_exec传给此函数的参数，para为任意 数据类型的地址。
n_column：结果集的列数。
column_value：指针数组的地址，其存放一行信息中 各个列值的首地址。
column_name：指针数组的地址，其存放一行信息中各 个列值对应列名的首地址。
返回值：若为非0值，则通知sqlite3_exec终止回调。
5、sqlite3_get_table函数:int sqlite3_get_table(sqlite3 *db, const char *sql,char ***resultp, int *nrow,int *ncolumn,char **errmsg);
功能：执行sql指向的SQL语句，函数将结果集相关的数据的地址保存在函数的参数中。
参数：db：数据库的标识。
sql：SQL语句（一条或多条），以’;’结尾。
resultp：指针数组的地址，其记录了结果集的数据。内存布局：先依次存放各列的列名，然后是每一行各列的值。
nrow：结果集的行数（不包含列名）。
ncolumn：结果集的列数。
errmsg：错误信息。
6、sqlite3_free_table函数:void sqlite3_free_table(char **resultp);
功能：释放sqlite3_get_table分配的内存。
参数：结果集数据的首地址。
SQLite数据库使用