【SQLite快速入门】

注：SQL语句不分大小写

SQLite数据类型

一般数据采用固定的静态数据类型，而 SQLite 采用的是动态数据类型，会根据存入值自动判断。

SOLite 具有以下五种基本数据类型:

1. integer：带符号的整型(最多64位)。
2. real：8字节表示的浮点类型。
3. text：字符类型，支持多种编码(如UTF-8、UTF-16)，大小无限制。
4. blob：任意类型的数据，大小无限制。 BLOB(binary large object)二进制大对象，使用二进制保存数据
5. NULL：表示空值

对数据库文件SQL语句：

1. 创建、打开数据库：
   sqlite3 *.db
   提示：
   • 当*.db文件不存在时，sqlite会创建并打开数据库文件
   • 当*.db文件存在时，sqlite会打开数据库文件
2. 退出数据库命令：
   .quit 或 .exit

SQL的语句格式

所有的SQL语句都是以分号结尾的，SQL语句不区分大小写。两个减号“--”则代表注释。关系数据库的核心操作:

1. 创建、修改、理除表
2. 添加、修改、删除行
3. 查表

1 创建表：create语句

语法：
create table 表名称 (列名称1 数据类型， 列名称2 数据类型， 列名称3 数据类型, ...);

例：创建一表格该表包含3列，列名分别是：“id”、“name”、“addr”。

sqlite> create table persons (id integer, name text, addr text);
1

2 创建表：create语句 （设置主键）

在用sqlite设计表时，每个表都可以通过primary key手动设置主键,每个表只能有一个主键，设置为主键的列数据不可以重复。

语法：
create table 表名称 (列名称1 数据类型 primary key, 列名称2 数据类型, 列名称3 数据券型, ...);

例：创建一表格该表包含3列，列名分别是：“id”、“name”、“addr”，设置“id”为主键
sqlite> create table persons (id integer primary key, name text, addr text);
1
2

3 查看表

语法：
.table
查看数据表的结构
.schma[表名]

4 修改表：alter语句

4.1 添加

语法：
alter table 表名 add 列名 数据类型;

sqlite> alter table persons add sex text;
1

4.2 修改表名

语法：
alter table 表名 renam to 新表名;

sqlite> alter table persons rename to new_persons;
1

5 删除表：drop table语句

用于删除表（表的结构、属性以及表的索引也会被删除）

语句：
drop table 表的名称;

sqlite> drop table persons;
1

6 插入新行：insert into语句（全部赋值）

给一行中的所有列赋值

语法：
insert into 表名 values(列值1, 列值2, 列值3, 列值4, ...);

sqlite> insert into persons values(1, 'lucy', 'beijing');
1

注：当列值为字符串时要加上 ' ' 号

7 插入新行：insert into语句（部分赋值）

给一行中的所有列赋值

语法：
insert into 表名 values(列值1, 列值2, ...)values (列值1, 列值2, ...);

sqlite> insert into persons values(1, 'lucy', 'beijing')values (2, 'tom', 'hunan');
1

注：当列值为字符串时要加上 '' 号

8 修改表中的数据：update语句

使用where根据匹配条件，查找一行或多行，根据查找的结果修改表中相应行的列值（修改哪一列由列名指定）。

语法：
update 表名 set 列1=值1, 列2=值2,... 匹配条件;
匹配：where子句
where子句用于规定匹配的条件。
操作符：=、<>、>、<、>=、<=
匹配条件语法：（基础）
where 列名 操作符 列值

sqlite> update persons set id=2, addr='tianjin' where name='peter';
1

9 删除表中的数据：delete语句

使用where根据匹配条件，查找一行或多行，根据查找的结果删除表中的查找到的行。

语法：
delete from 表名 匹配条件；
注：当表中有多列、多行符合匹配条件时会删除相应的多行。

sqlite> delete from persons where name='peter';
1

10 查询：select语句（基础）

用于从表中选取数据，结果被存储在一个结果表中（称为结果集）。

语法：

1. select * from 表名 匹配条件；
2. select 列名1, 列名2, ... from 表名 匹配条件;

sqlite> select id from persons;  			 --只读表中的id
sqlite> select * from persons;   			 --读整个表
sqlite> select * from persons where id=103;  --读表中id=103的所有数据
1
2
3

11 匹配条件语法：（提高）

数据库提供了丰富的操作符配合where子句实现多样的匹配方法

• in 操作符
• and 操作符
• or 操作符
• between and 操作符
• like 操作符
• not 操作符

• in 允许我们在where子句中规定多个值
  匹配条件语法：where 列名 in （列值1, 列值2, ...）
  例：select * from 表名 where 列名 in （值1, 值2, …）

sqlite> select * from persons where id in (1, 2);    --打印id为1,2的内容
1

例：select 列名1, [, 列名2, …] from 表名 where 列名 in （列值1, 列值2, …）

sqlite> select name from persons where id in (1, 2);  --打印id为1,2的name
1

• and 可在where子语句中把两个或多个条件结合起来
  匹配条件语法：where 列1=值1 [and 列2=值2 and...]
  例：select * from 表名 where 列1=值1 [and 列2=值2 and …];

sqlite> select * from persons where id=1 and addr='beijing';
1

例：select 列名1, [,列名2, …] from 表名 where 列1=值1 [and 列2=值2 and …];

sqlite> select name from persons persons where id=1 and addr='beijing';
1

• or 可在where子语句中把两个或多个条件结合起来
  匹配条件语法：where 列1=值1 [or 列2=值2 or ...]
  例：select * from 表名 where 列1=值1 [or 列2=值2 or …]；

sqlite> select * from persons where addr='beijing' or addr='hebei';
1

• between A and B 会选取介于A、B之间的数据范围。这些值可以是数值、文本或者日期
  匹配条件语法：where 列名 between A and B
  注：匹配字符串时会以ascii顺序匹配
  例：select * from 表名 where 列名 between A and B

sqlite> select * from persons where id between 1 and 3;
1

例：select 列名1 [,列名2,…] from 表名 where 列名 between A and B;

sqlite> select * from persons where addr between 'a' and 'c';
1

• like 用于模糊查找
  匹配条件语法：where 列名 like 列值
  1. 若列值为数字
     相当于列名=列值
  2. 若列值为字符串
     可以用通配符“%”代表缺少的字符（一个或多个）

sqlite> select * from persons where id like 3;
1

sqlite> select * from persons where addr like '%jing%';
1

• not 可取出原结果的补集
  显示除了%jing%外所有

sqlite> select * from persons where addr not like '%jing%';
1

12 复制一张表

create table tbl2 as select * from tbl1;
1

13 复制一张表的部分内容

create table tbl2 as select * from tbl1 where id=104;
1

14 修改表的结构

1. 创建新表
2. 从原表导入数据(如果有主键，注意数据不要重复)
3. 删除原表
4. 修改新表为原表的名称

create table ntbl(id interger primary key,name text,addr text);

insert into ntbl(id, name, addr) select id,name,addr from tbl;

drop table tbl;

alter table ntbl rename to tbl;
1
2
3
4
5
6
7

SQLite数据库C接口

1 打开数据库

int sqlite3_open(char *db_name,sqlite3 **db);

参数:
	db_name：数据库文件名，若文件名包含ASCII吗表范围的之外的字符，则其必需是（UTF-8）编码。
	sqlite3：数据库标示，此结构体为数据库操作句柄。通过此句柄可对数据库文件进行相应操作。

返回值：
	成功返回SQLITE_OK，失败返回非SQLITE_OK。
1
2
3
4
5
6
7
8

2 关闭数据库、释放打开数据库时申请的资源

int sqlite3_close(sqlite3 *db)

参数：
	db：数据库的标示

返回值：
	成功：SQLITE_OK
	失败：非SQLITE_OK
1
2
3
4
5
6
7
8

注：sqlite3使用了两个库：pthread、dll，故链接时应加上 -lpthread 和 -ldl。

3 执行SQL语句（含回调）

执行sql指向的SQL语句，若结果集不为空，函数会调用函数指针callback所指向的函数

int sqlite3_exec(sqlite3 *db,
				 const char *sql,
				 exechandler_t callback,
				 void *arg,
				 char **errmsg);

参数：
	db：数据库的标识
	sql：SQL语句（一条或多条），以 ';'结尾。
	callback：是回调函数指针，当这条语句执行之后，sqlite3会去调用你提供的这个函数。
	arg：当执行sqlite3_exec的时候传递给回调函数的参数
	errmsg：存放错误信息的地址，执行失败后可以查阅这个指针。
			打印错误信息方式：print("%s\n",errmsg);
			
1
2
3
4
5
6
7
8
9
10
11
12
13
14

回调函数指针：此函数由用户定义，当sqlite3_exec函数执行sql语句后，结果集不为空时sqlite3_exec函数会自动调用此函数，每次调用此函数时会把结果集的一行信息传给次函数。

typedef int(*exechandler_t)(void *para, int n_column, char **column_value, char **column_name);

参数：
	para：sqlite3_exec传给此函数的参数，para为任意数据类型的地址
	n_column：结果集的列数
	column_value：指针数组的地址，其存放一行信息中各个列值的首地址
	column_name：指针数组的地址，其存放一行信息中各个列值对应列名的首地址

返回值：
	若为非0值，则通知sqlite3_exec终止回调
1
2
3
4
5
6
7
8
9
10

4 执行SQL语句（非回调）

执行sql指向的SQL语句，函数将结果集相关的数据的地址保存在函数的参数中

int sqlite3_get_table(sqlite3 *db, const char *sql, char ***resultp, int *nrow, int *ncolumn, char **errmsg);

参数：
	db：数据库的标识。
	sql：SQL语句（一条或多条），以';'结尾
	resultp：指针数组的地址，其记录了结果集的数据。内存布局：先依次存放各列的列名，然后是每一行各列的值
	nrow：结果集的行数（不包含列名）
	ncolumn：结果集的列数
	errmsg：错误信息
1
2
3
4
5
6
7
8
9

释放sqlite3_get_table分配的内存

void sqlite3_free_table(char **resultp);

参数：
	结果集数据的首地址
1
2
3
4

例

#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include "sqlite3.h"

int main(int argc, char **argv)
{
    sqlite3 *db = NULL;
    int ret;
    char *err = NULL;
    char *sql;
    
    ret = sqlite3_open("stu.db", &db);
    if(ret != SQLITE_OK){
        printf("Open error\n");
        return -1;
    }
    
    sql = "insert into info values(120, 'zz', 'ts', time('now'), date('now'))";
    sqlite3_exec(db, sql, NULL, NULL, &err);
    if(err){
        printf("err:%s\n",err);
    }
    sqlite3_close(db);
    return 0;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26