SQL（sqlite3）基础语句

一、命令行模式

1、数据类型

SQL拥有五种基本数据类型：

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

2、创建、打开、退出

创建、打开数据库文件：sqlite3 *.db
退出数据库命令：.quit 或 .exit

3、创建表：creat语句

create table 表名称 (列名称 1 数据类型, 列名称 2 数据类型, 列名称 3 数据类型, …);
例如： create table data(id integer,name text,addr text);

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

每个表只能有一个主键，设置为主键的列数据不可以重复。

create table 表名称 ( 列名称 1 数据类型 primary key, 列名称 2 数据类型, 列名称 3 数据类型, …)；
例如：
create table data(id integer primary key,name text,addr text);

5、查看表

查看表：.table
查看表结构：.schema[表名]

6、修改表：alter语句

在已有的表中添加或删除列以及修改表名。

alter table 表名 add 列名 数据类型;
例如：
alter table 表名
rename to 新表名;

7、删除表：drop table语句

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

drop table 表名称;

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

给一行中的所有列赋值。

insert into 表名 values (列值 1, 列值 2, 列值 3， 列值 4, …);
例如：
insert into data values (1, ‘kangkang’, ‘beijing’);

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

给一行中的部分列赋值。

insert into 表名 (列名 1, 列名 2, …) values (列值 1, 列值 2, …);
例如：
insert into data (id, name) values (1, ‘jane’);

10、修改表中的数据：update语句

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

update 表名 set 列 1 = 值 1 [, 列 2 = 值 2, …] [where 列名 操作符 列值];
这里的操作符可以有： =，<>，>，<，>=，<= （<>是不等于）
例如：
update data set id=2,addr=‘beijing’ where name=‘jane’;
如果有多个条件：
update data set id=2,addr=‘beijing’ where name=‘jane’ and name=‘kangkang’;

11、删除表中的数据：delete语句

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

delete from 表名 [where 列名 操作符 列值];

12、查询：select语句

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

1、 select * from 表名 [匹配条件]; // * 指的是选取所有列的通配符
2、 select 列名 1[,列名 2, …] from 表名 [匹配条件];

13、匹配语法：where

where除了提供=，<>，>，<，>=，<=   （<>是不等于）这几种操作符之外，还可以使用多种组合匹配方法。

1、in 允许我们规定多个值。 where 列名 in (列值 1, 列值 2, …)
2、and 多个条件同时搜索。 where 列 1 = 值 1 [and 列 2 = 值 2 and …]
3、or多个条件“或关系”搜索。 where 列 1 = 值 1 [or 列 2 = 值 2 or …]
4、between A and B会选取介于A和B之间数据范围，这些值可以是数值、文本或者日期。 where 列名 between A and B
5、like用于模糊查找。 where 列名 like 列值 //如果列值为字符串，可以用通配符“%”代表缺少的字符（一个或多个）
6、not取出原结果集的补集。 where 列名 not in 列值 、 where 列名 not like 列值 等多种操作符的组合
7、order by 根据指定的列对结果集进行排序。默认升序，desc关键字可进行降序。 select * from 表名 order by列名;
select * from 表名
order by 列名 desc;

二、编程模式

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

1、打开、关闭数据库函数

int sqlite3_open(char *db_name, sqlite3 **db);    //打开数据库
int sqlite3_close(sqlite3 *db);    //关闭数据库、 释放打开数据库时申请的资源

成功返回：SQLITE_OK，失败返回：非SQLITE_OK

2、执行SQL语句（回调）

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);
*/

回调函数指针：

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 终止回调。
*/

3、执行SQL语句（非回调）

int sqlite3_get_table(
  sqlite3 *db,          /* 数据库标识*/
  const char *zSql,     /* SQL语句，一条或多条，以;结尾*/
  char ***pazResult,    /* 指针数组的地址，其记录了结果集的数据。内存布局：先依次存放各列的列名，然后是一行各列的值*/
  int *pnRow,           /* 结果集的行数 */
  int *pnColumn,        /* 结果集的列数 */
  char **pzErrmsg       /* 错误信息 */
);

4、释放内存

执行SQL语句之后，记得释放内存。

void sqlite3_free_table(char **result);
/*
功能：释放sqlite3_get_table分配的内存。
参数：结果集数据的首地址。
*/