sqlite3应用

SQLite 是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 源代码不受版权限制。

官网：https://www.sqlite.org/index.html

1. 安装sqlite3

sudo apt-get install sqlite3

sudo apt-get install libsqlite3-dev

或源码交叉编译

#compile sqlite3
cd ${TOPDIR}/${BASEDIR}
MODULE_DIR=sqlite-autoconf-3340000
tar xvf ${MODULE_DIR}.tar.* -C ${BUILDDIR}
cd ${BUILDDIR}/${MODULE_DIR}
CC="/opt/fsl-imx-x11/4.1.15-2.1.0/sysroots/x86_64-pokysdk-linux/usr/bin/arm-poky-linux-gnueabi/arm-poky-linux-gnueabi-gcc  -march=armv7ve -mfpu=neon  -mfloat-abi=hard -mcpu=cortex-a7 --sysroot=/opt/fsl-imx-x11/4.1.15-2.1.0/sysroots/cortexa7hf-neon-poky-linux-gnueabi" \
    ./configure --prefix=${TOPDIR}/winstall \
    --host=arm-poky-linux-gnueabi \
    --with-sysroot=/opt/fsl-imx-x11/4.1.15-2.1.0/sysroots/cortexa7hf-neon-poky-linux-gnueabi
make
make install

2. sqlite常用命令

当前目录下建立或打开test.db数据库文件，并进入sqlite命令终端，以sqlite>前缀标识：
#sqlite3 test.db

查看数据库文件信息命令(注意命令前带字符'.')：
sqlite>.database

查看所有表的创建语句：
sqlite>.schema

查看指定表的创建语句：
sqlite>.schema table_name

以sql语句的形式列出表内容：
sqlite>.dump table_name

设置显示信息的分隔符：
sqlite>.separator symble
Example：设置显示信息以‘：'分隔
sqlite>.separator :

设置显示模式：
sqlite>.mode mode_name
Example:默认为list，设置为column，其他模式可通过.help查看mode相关内容
sqlite>.mode column

输出帮助信息：
sqlite>.help

设置每一列的显示宽度：
sqlite>.width width_value
Example:设置宽度为2
sqlite>.width 2

列出当前显示格式的配置：
sqlite>.show

退出sqlite终端命令：
sqlite>.quit
或
sqlite>.exit

3. sqlite3常用指令

sql的指令格式：所有sql指令都是以分号(;)结尾，两个减号(--)则表示注释。
如：
sqlite>create studen_table(Stu_no interger PRIMARY KEY, Name text NOT NULL, Id interger UNIQUE, Age interger CHECK(Age>6), School text DEFAULT 'xx小学);
该语句创建一个记录学生信息的数据表。

CREATE TABLE IF NOT EXISTS MusicList (id integer primary key AutoIncrement,name varchar(20),path varchar(20));

3.1 sqlite3存储数据的类型
NULL：标识一个NULL值
INTERGER：整数类型
REAL：浮点数
TEXT：字符串
BLOB：二进制数

3.2 sqlite3存储数据的约束条件
Sqlite常用约束条件如下：
PRIMARY KEY - 主键：
1）主键的值必须唯一，用于标识每一条记录，如学生的学号
2）主键同时也是一个索引，通过主键查找记录速度较快
3）主键如果是整数类型，该列的值可以自动增长
NOT NULL - 非空：
约束列记录不能为空，否则报错
UNIQUE - 唯一：
除主键外，约束其他列的数据的值唯一
CHECK - 条件检查：
约束该列的值必须符合条件才可存入
DEFAULT - 默认值：
列数据中的值基本都是一样的，这样的字段列可设为默认值

3.3 sqlite3常用指令
1）建立数据表
create table table_name(field1 type1, field2 type1, ...);
table_name是要创建数据表名称，fieldx是数据表内字段名称，typex则是字段类型。
例，建立一个简单的学生信息表，它包含学号与姓名等学生信息：
create table student_info(stu_no interger primary key, name text);
2）添加数据记录
insert into table_name(field1, field2, ...) values(val1, val2, ...);
valx为需要存入字段的值。
例，往学生信息表添加数据：
Insert into student_info(stu_no, name) values(0001, alex);

3）修改数据记录
update table_name set field1=val1, field2=val2 where expression;
where是sql语句中用于条件判断的命令，expression为判断表达式
例，修改学生信息表学号为0001的数据记录：
update student_info set stu_no=0001, name=hence where stu_no=0001;

4）删除数据记录
delete from table_name [where expression];
不加判断条件则清空表所有数据记录。
例，删除学生信息表学号为0001的数据记录：
delete from student_info where stu_no=0001;

注：若table中所有记录都删除，再插入数据，id配置为primary key，则id又从1开始（默认id从1开始，没有0）。

5）查询数据记录
select指令基本格式：
select columns from table_name [where expression];
a查询输出所有数据记录
select * from table_name;
b限制输出数据记录数量
select * from table_name limit val;
c升序输出数据记录
select * from table_name order by field asc;
d降序输出数据记录
select * from table_name order by field desc;
e条件查询
select * from table_name where expression;
select * from table_name where field in ('val1', 'val2', 'val3');
select * from table_name where field between val1 and val2;
f查询记录数目
select count (*) from table_name;
g区分列数据
select distinct field from table_name;
有一些字段的值可能会重复出现，distinct去掉重复项，将列中各字段值单个列出。

6）建立索引
当说数据表存在大量记录，索引有助于加快查找数据表速度。
create index index_name on table_name(field);
例，针对学生表stu_no字段，建立一个索引：
create index student_index on student_table(stu_no);
建立完成后，sqlite3在对该字段查询时，会自动使用该索引。

7）删除数据表或索引
drop table table_name;
drop index index_name;

8）replace替换数据

replace init table_name(field1, field2) values (val1, val2);

假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。
注意，除非表有一个PRIMARY KEY或UNIQUE索引，否则，使用一个REPLACE语句没有意义。该语句会与INSERT相同，因为没有索引被用于确定是否新行复制了其它的行。

4. sqlite3函数

//当数据库文件不存在时，sqlite3_open不会报错，会创建一个空文件

//而sqlite3_open_v2会报错，并且不会产生空的数据库文件

int sqlite3_open(
const char *filename,   /* Database filename (UTF-8) */
sqlite3 **ppDb          /* OUT: SQLite db handle */
);

int sqlite3_open_v2(
const char *filename,   /* Database filename (UTF-8) */
sqlite3 **ppDb,         /* OUT: SQLite db handle */
int flags,              /* Flags */
const char *zVfs        /* Name of VFS module to use */
);

int sqlite3_close(sqlite3 *);

 //回调函数是检测到有一条记录就执行一次，有几条记录就执行几次

//p是&empty, argc是字段个数，argvv是字段名字， argv是字段值

//int rscallback(void *p, int argc, char *argv[], char *argvv[])
typedef int (*sqlite3_callback)(void*,int,char**, char**);

int sqlite3_exec(
sqlite3*,                                  /* An open database */
const char *sql,                           /* SQL to be evaluated */
int (*callback)(void*,int,char**,char**),  /* Callback function */
void *,                                    /* 1st argument to callback */
char **errmsg                              /* Error msg written here */
);

** The pointer arguments to [sqlite3_free()] and [sqlite3_realloc()]
** must be either NULL or else pointers obtained from a prior
** invocation of [sqlite3_malloc()] or [sqlite3_realloc()] that have
** not yet been released.

void *sqlite3_malloc(int);
void *sqlite3_realloc(void*, int);
void sqlite3_free(void*);

**The sqlite3_mprintf() and sqlite3_vmprintf() routines write their
** results into memory obtained from [sqlite3_malloc()].
** The strings returned by these two routines should be
** released by [sqlite3_free()].  ^Both routines return a
** NULL pointer if [sqlite3_malloc()] is unable to allocate enough
** memory to hold the resulting string.

SQLITE_API char *sqlite3_mprintf(const char*,...);
SQLITE_API char *sqlite3_vmprintf(const char*, va_list);
SQLITE_API char *sqlite3_snprintf(int,char*,const char*, ...);
SQLITE_API char *sqlite3_vsnprintf(int,char*,const char*, va_list);

返回结果：

#define SQLITE_OK           0   /* Successful result */

/* beginning-of-error-codes */
#define SQLITE_ERROR        1   /* SQL error or missing database */
#define SQLITE_INTERNAL     2   /* Internal logic error in SQLite */
#define SQLITE_PERM         3   /* Access permission denied */
#define SQLITE_ABORT        4   /* Callback routine requested an abort */
#define SQLITE_BUSY         5   /* The database file is locked */
#define SQLITE_LOCKED       6   /* A table in the database is locked */
#define SQLITE_NOMEM        7   /* A malloc() failed */
#define SQLITE_READONLY     8   /* Attempt to write a readonly database */
#define SQLITE_INTERRUPT    9   /* Operation terminated by sqlite3_interrupt()*/
#define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT     11   /* The database disk image is malformed */
#define SQLITE_NOTFOUND    12   /* Unknown opcode in sqlite3_file_control() */
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* Database lock protocol error */
#define SQLITE_EMPTY       16   /* Database is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* String or BLOB exceeds size limit */
#define SQLITE_CONSTRAINT  19   /* Abort due to constraint violation */
#define SQLITE_MISMATCH    20   /* Data type mismatch */
#define SQLITE_MISUSE      21   /* Library used incorrectly */
#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
#define SQLITE_AUTH        23   /* Authorization denied */
#define SQLITE_FORMAT      24   /* Auxiliary database format error */
#define SQLITE_RANGE       25   /* 2nd parameter to sqlite3_bind out of range */
#define SQLITE_NOTADB      26   /* File opened that is not a database file */
#define SQLITE_ROW         100  /* sqlite3_step() has another row ready */
#define SQLITE_DONE        101  /* sqlite3_step() has finished executing */
/* end-of-error-codes */

** ^The sqlite3_errcode() interface returns the numeric [result code] or
** [extended result code] for the most recent failed sqlite3_* API call
** associated with a [database connection]. If a prior API call failed
** but the most recent API call succeeded, the return value from
** sqlite3_errcode() is undefined.  ^The sqlite3_extended_errcode()
** interface is the same except that it always returns the
** [extended result code] even when extended result codes are
** disabled.
**
** ^The sqlite3_errmsg() and sqlite3_errmsg16() return English-language
** text that describes the error, as either UTF-8 or UTF-16 respectively.
** ^(Memory to hold the error message string is managed internally.
** The application does not need to worry about freeing the result.
** However, the error string might be overwritten or deallocated by
** subsequent calls to other SQLite interface functions.)^
**
** When the serialized [threading mode] is in use, it might be the
** case that a second error occurs on a separate thread in between
** the time of the first error and the call to these interfaces.
** When that happens, the second error will be reported since these
** interfaces always report the most recent result.  To avoid
** this, each thread can obtain exclusive use of the [database connection] D
** by invoking [sqlite3_mutex_enter]([sqlite3_db_mutex](D)) before beginning
** to use D and invoking [sqlite3_mutex_leave]([sqlite3_db_mutex](D)) after
** all calls to the interfaces listed here are completed.
**
** If an interface fails with SQLITE_MISUSE, that means the interface
** was invoked incorrectly by the application.  In that case, the
** error code and message may or may not be set.
*/

SQLITE_API int sqlite3_errcode(sqlite3 *db);
SQLITE_API int sqlite3_extended_errcode(sqlite3 *db);
SQLITE_API const char *sqlite3_errmsg(sqlite3*);
SQLITE_API const void *sqlite3_errmsg16(sqlite3*);

5. 举例

5.1  数据库插入数据

#include <sqlite3.h>
#include <stdio.h>

int main()
{
      int ret;
      sqlite3 * db;
      char *err = NULL;
      char *sql = NULL;
      char id[10];
      char name[20];
      char age[10];

      //当数据库文件不存在时，sqlite3_open不会报错，会创建一个空文件
      //而sqlite3_open_v2会报错，并且不会产生空的数据库文件
//    ret = sqlite3_open_v2("mydatabase1.dat", &db, SQLITE_OPEN_READWRITE, NULL);  
      ret = sqlite3_open("mydatabase1.dat", &db);
      if(ret != SQLITE_OK)
      {
           //printf("open database error!\n");
           fputs(sqlite3_errmsg(db), stderr);
           return -1;
      }

      ret = sqlite3_exec(db, "create table student(id integer primary key, name text, age integer);", NULL, NULL, &err);
      if(ret != SQLITE_OK)
      {
           fputs(err, stderr);
           return -1;
      }

      printf("please input student info:\n");
      printf("Id:\n");
      scanf("%s", id);
      printf("Name:\n");
      scanf("%s", name);
      printf("Age:\n");
      scanf("%s", age);
 
      sql = sqlite3_mprintf("insert into student values(%s, %Q, %s);",
                 id, name, age);
      ret = sqlite3_exec(db, sql, NULL, NULL, &err);

      /*
      ret = sqlite3_exec(db, "insert into student values(4, 'XiaoLi', 23);", NULL, NULL, &err);*/
      if(ret != SQLITE_OK)
      {
           fputs(err, stderr);
           return -1;
      }

      sqlite3_free(sql);
      sqlite3_close(db);
      return 0;
}

5.2  显示数据库内容

#include <sqlite3.h>
#include <stdio.h>

//select查询用到的回调函数
//回调函数是检测到有一条记录就执行一次，有几条记录就执行几次
//p是&empty, argc是字段个数，argvv是字段名字， argv是字段值
int rscallback(void *p, int argc, char *argv[], char *argvv[])
{
      int i;
      *(int *)p = 0; //有记录，我们就把empty改变成0
      for(i=0; i<argc; ++i)
           printf("%s=%s ", argvv[i], argv[i]?argv[i]:"null");
      printf("\n");

      return 0;
}

int main()
{
      int ret;
      sqlite3 * db;
      int empty=1; //为1表示查询结果为空，没有匹配的记录
      char *err = NULL;
 
      ret = sqlite3_open("mydatabase.dat", &db);
      if(ret != SQLITE_OK)
      {
           printf("open database error!\n");
           return -1;
      } 

      ret = sqlite3_exec(db, "select * from student;", rscallback, &empty, &err);

      if(ret != SQLITE_OK)
      {
           fputs(err, stderr);
           return -1;
      }

      if(empty)
      {
           printf("查询结果为空!\n");
      }

      sqlite3_close(db);

      return 0;
}

  

参考

1. https://www.runoob.com/sqlite/sqlite-tutorial.html 菜鸟

2. http://www.jb51.net/article/44545.htm  Linux sqlite3 基本命令

3. http://www.cnblogs.com/hnrainll/archive/2011/09/08/2170489.html   Linux下用到数据库sqlite3