Linux--sqlite编程

1.数据库与表

 SQLite将每个数据库都保存成一个文件。

 数据库中的数据被组织成表的形式。

 表由若干拥有相同字段的记录组成。

 表可以为空，即拥有0条记录。

 字段指的是记录中的数据域，它有不同的类型，可以是一个数值，也可以是字符串。

 每个表中可以指定一个或多个字段为主键，表中所有记录的主键的值都不能重复。这种不重复是在插入记录时控制的，即如果向一个表中插入记录时，这个表中已有与插入记录具有相同主键的记录，则插入操作失败。

 

 数据库为student.db

 数据库中有两张表，名称分别为Student和Score

 表Student包含学号(ID)、姓名(Name)、性别(Sex)、年龄(Age)四个字段

 表Score包含学号(ID)、课程(Subject)、分数(Score)三个字段

 表Student中，学号是各不相同的，因此可以作为主键

 表Score中，一个学生有多个课程的成绩，因此ID字段不能再单独作为主键

 

2.创建表

create table student(

    ID          INTEGER,

    name     TEXT,

    sex         TEXT,

    age         INTEGER

    primary  key(ID)

);    

3.数据库中的数据类型

 NULL -- The value is a NULL value.

 INTEGER -- The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending  on the magnitude of the value.

 REAL -- The value is a floating point value, stored as an 8-byte IEEE floating point number.

 TEXT -- The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

 BLOB -- The value is a blob of data, stored exactly as it was input.

 

4.删除表

 用drop语句可以从数据库中删除指定的表

drop table student;

表被删除后，表中所存储的数据将全部丢弃。

 

5.插入记录

 用insert语句可以向表中插入一条新记录

insert into student values(1, 'Zhang', 'M', 18);

6.修改记录

 用update语句可以可以修改表中的记录

update student set Sex = 'F', Age = 20 where Name = 'Zhang';

 where及其后的部分表示一个过滤条件，如果省略where和它后面的部分，则语句的作用就是修改表student中的所有记录，将它们的Sex字段的值设为F，Age字段的值设为20。

7.删除记录

 用delete语句可以删除表中的记录

delete from student where ID = 2;

如果没有where子句，则表student中的所有记录均被删除，成为空表。

8.比较表达式

使用where子句时，需要一个比较表达式，满足这个条件的记录将被操作。常用的比较操作符如下

 

9.逻辑操作符

 

10.单表查询

查询是对数据库的最常用操作。查询的结果可以理解为得到了一个新表，只是这个表并没有保存在数据库里，而是以某种方式显示给查询者。

 用select语句可以对数据库进行查询

select ID, Name from student where Sex = 'F';

查询结果中出现表的所有字段(*)

select * from score where  score < 60;

 查询结果中没有重复的记录(distinct)

select distinct * from score;

 查询结果按某个字段的值进行排序(order by)

select * from score order by score asc;

select * from score order by score desc;

 

11多表联合查询

 查询数据库时，也可以将多个表的内容放在一起进行查询

select student.name from student, score where student.id = score.id and score.score < 60;

上述语句中，from关键字后出现了两个表名student和score，表示对这两个表进行联合查询。

12.使用别名进行多表联合查询

可以使用表的别名以避免多次输入较长的表名，使代码更清晰

select A.name from student A, score B where A.id = B.id and B.score < 60;

上述语句中，from关键字后的表名都指定了一个别名。

 

注意：别名只在当前语句中有效。

 

13.多表联合查询

可以把多表联合查询看作在一张由多个表的记录两两组合而成的大表中进行查询。

 

组合后的大表的记录数等于各个表的记录数之积。因此多表联合查询的效率较低。此外，语句的不同写法也能对查询效率产生显著的影响。

 

 

 

 

 

Sqlite3命令行工具

SQLite库包含一个简单的命令行工具：sqlite3，该工具使得用户可以对一个SQLite数据库手动输入并且执行SQL语句。

如下命令可以启动sqlite3工具：

sqlite3 student.dbcd

其中student.db是要操作的数据库文件，如果它不存在，则会自动创建一个新的空数据库。

.exit ,退出sqlite互动模式的命令

.help,列出命令的提示信息。

.tables显示数据库中所有表名

.schema  <table_name> 查看表的结构

.database 显示当前打开的数据库文件

 

 

Sqlite3编程接口

1.关键数据类型

sqlite 里最常用到的是 sqlite3 * 类型。从数据库打开开始，sqlite就要为这个类型准备好内存，直到数据库关闭，整个过程都需要用到这个类型。当数据库打开时开始，这个类型的变量就代表了你要操作的数据库。下面再详细介绍。（首先要定义一个sqlite3的对象，如：sqlite3 *database_;）

2.打开数据库

int sqlite3_open( 文件名, sqlite3 ** );
用这个函数开始数据库操作。
需要传入两个参数，一是数据库文件名，比如：database.db。
文件名不需要一定存在，如果此文件不存在，sqlite 会自动建立它。如果它存在，就尝试把它当数据库文件来打开。
sqlite3 ** 参数即前面提到的关键数据结构。这个结构底层细节如何，你不要管它。
函数返回值表示操作是否正确，如果是 SQLITE_OK 则表示操作正常。相关的返回值sqlite定义了一些宏。具体这些宏的含义可以参考 sqlite3.h 文件。里面有详细定义（顺便说一下，sqlite3 的代码注释率自称是非常高的，实际上也的确很高。只要你会看英文，sqlite 可以让你学到不少东西）。

 

3.关闭数据库

int sqlite3_close(sqlite3 *);
前面如果用 sqlite3_open 开启了一个数据库，结尾时不要忘了用这个函数关闭数据库。
下面给段简单的代码：
int main()
{
sqlite3 * db = NULL; //声明sqlite关键结构指针

 int result;
   result = sqlite3_open( “database.db”, &db );

 if( result != SQLITE_OK )
    {

return -1;
    }
sqlite3_close( db );
return 0;
}

 

3.执行sql语句

int sqlite3_exec(sqlite3*, const char *sql, sqlite3_callback, void *,  char **errmsg );
这就是执行一条 sql 语句的函数。
第1个参数不再说了，是前面open函数得到的指针。说了是关键数据结构。
第2个参数const char *sql 是一条 sql 语句，以/0结尾。
第3个参数sqlite3_callback 是回调，当这条语句执行之后，sqlite3会去调用你提供的这个函数。（什么是回调函数，自己找别的资料学习）

第4个参数void * 是你所提供的指针，你可以传递任何一个指针参数到这里，这个参数最终会传到回调函数里面，如果不需要传递指针给回调函数，可以填NULL。等下我们再看回调函数的写法，以及这个参数的使用

第5个参数char ** errmsg 是错误信息。注意是指针的指针。sqlite3里面有很多固定的错误信息。执行 sqlite3_exec 之后，执行失败时可以查阅这个指针（直接 printf(“%s/n”,errmsg)）得到一串字符串信息，这串信息告诉你错在什么地方。sqlite3_exec函数通过修改你传入的指针的指针，把你提供的指针指向错误提示信息，这样sqlite3_exec函数外面就可以通过这个 char*得到具体错误提示。
说明：通常，sqlite3_callback 和它后面的 void * 这两个位置都可以填 NULL。填NULL表示你不需要回调。比如你做insert 操作，做 delete 操作，就没有必要使用回调。而当你做 select 时，就要使用回调，因为 sqlite3 把数据查出来，得通过回调告诉你查出了什么数据。

 

4.exec的回调

typedef int (*sqlite3_callback)(void*,int, char**, char**);
你的回调函数必须定义成上面这个函数的类型。下面给个简单的例子：
//sqlite3的回调函数      
// sqlite 每查到一条记录，就调用一次这个回调

例1：sqlite1.c

 

5.不使用回调查询数据库

上面介绍的 sqlite3_exec 是使用回调来执行 select 操作。还有一个方法可以直接查询而不需要回调。但是，我个人感觉还是回调好，因为代码可以更加整齐，只不过用回调很麻烦，你得声明一个函数。

虽然回调显得代码整齐，但有时候你还是想要非回调的 select 查询。这可以通过 sqlite3_get_table 函数做到。
int sqlite3_get_table(sqlite3*, const char *sql, char ***resultp, int *nrow, int *ncolumn, char **errmsg );

第1个参数不再多说，看前面的例子。
第2个参数是 sql 语句，跟 sqlite3_exec 里的 sql 是一样的。是一个很普通的以/0结尾的char *字符串。
第3个参数是查询结果，它依然一维数组（不要以为是二维数组，更不要以为是三维数组）。它内存布局是：第一行是字段名称，后面是紧接着是每个字段的值。下面用例子来说事。

第4个参数是查询出多少条记录（即查出多少行）。
第5个参数是多少个字段（多少列）。
第6个参数是错误信息，跟前面一样，这里不多说了。

例2： sqlite2.c