sqlite3数据库操作

目录

一、SQL命令

1.系统命令

2.普通指令 

（1）创建/删除表

（2）增/删/改/查记录

（3）添加/删除一列

（4）设置主键

二、常用api

1.函数

（1）打开数据库

（2）关闭数据库

（3）执行一条sql指令

（4）从数据库查询结果

（5）释放内存

2.注意

总结

---

一、SQL命令

        命令分为按系统命令（以 '.' 开头的命令，一般用于对当前数据库的操作）以及普通命令（以 ';' 结尾的命令，一般为对数据库中表进行操作）。

1.系统命令

        以下为sqlite3系统命令。 

.backup ?DB? FILE                将本数据库备份为数据库/文件格式
.bail ON|OFF                          遇到错误时停止（默认关闭）
.databases                             列出数据的信息（名字及路径）
.dump ?TABLE? ...                以文本格式下载数据库如果选择TABLE，将只下载对应的表）
.echo ON|OFF                       打开或关闭命令行回显
.exit                                        退出程序
.header(s) ON|OFF               打开或关闭表头
.help                                      显示帮助信息
.import FILE TABLE              将文件FILE导入到数据库表中
.indices ?TABLE?                 显示索引名（指定表名时，显示表的索引）
.mode MODE ?TABLE?       设置输出模式:
                            csv            逗号分隔
                            column      左对齐
                            html           HTML编码
                            insert         SQL插入表语句
                            line            每行一个值
                            list             设置字符串分隔符
                            tabs           设置表分隔符
                            tcl              TCL列表元素
.quit                                      退出程序
.restore ?DB? FILE              恢复数据库或FILE
.schema ?TABLE?               列出数据库所有表的结构（如果有表名，仅展示此表结构）
.show                                    显示当前输出模式设置的值
.tables ?TABLE?                  列出表名（如果有表名，仅查看表是否存在）
.timeout MS                         以毫秒为单位尝试控制表
.width NUM NUM ...            设置输出模式中column对应的列宽

2.普通指令 

         本小节主要讲述创建/删除表、增/删/改/查记录、添加/删除一列、设置主键。

（1）创建/删除表

1.创建一张表

        create table 表名(字段名称1 字段类型，字段名称2 字段类型， ....);

        例：create table TestTable(id int, name char, age int);

        注意：字符、字符串的类型指定为char或者text

2.删除一张表

        drop table 表名;

        例：drop table TestTable；

（2）增/删/改/查记录

1.增加记录

        insert into 表名 values(字段值1，字段值2，...);

        例：insert into TestTable values(1, 'Shimenglong', 25);

        注意：此方式必须按照表中字段名依次赋值，每个字段都不能省略

        例：insert into TestTable(id, name) values(2, 'Chen');     

        注意：此方式可以不指定某些字段值，未指定字段可以不赋值 

                

2.查找记录

        select <查找项> from 表名 <where 查找条件>；

        例：select * from TestTable;                                                         (查找所有的记录)

               select * from TestTable where id=1;                                      (查找符号条件的记录) 

               select name from TestTable where id=2 and name='Chen';  (字符串需要加引号)

               select id from TestTable where name='Shimenglong' or age=20;

               select id,name from TestTable ;                                             (只查询id和name )

        注意：可搭配.headers on（显示列表名） .mode column（按照列显示内容 ）查看记录

3.删除记录

        delete from 表名 where 查找条件;

        例：delete from TestTable where name='Shimenglong';   

4.更新记录

        update 表名 set 替换项 where 查找条件;

        update TestTable set age=25 where name='Chen';

（3）添加/删除一列

1.添加一列

        alter table 表名 add column 字段 字段类型;

        例：alter table TestTable add column Address text;

2.删除一列

        注意：sqlite3 不允许直接删除一列，需要先创建一张新表，删除原来的旧表，对新表重命名

        例：create table TestTableBak as select id , name from TestTable ;（创建新表）

               drop table TestTable ;                                                                （删除旧表）

               alter table TestTableBak rename to TestTable ;                        （新表改名为旧表）

（4）设置主键

数据库主键

        注意：设置为主键的字段不能重复，在创建数据库的时候设置主键

        create table 表名(字段 字段类型 primary key);

        例：create table TestTableBak (id int primary key);

二、常用api

1.函数

（1）打开数据库

        原型：int sqlite3_open( const char *pFilename, sqlite3 **pDb ); 

        参数：

                pFilename：数据库名字；

                pDb：操作数据库的指针；

        返回值：成功返回SQLITE_OK，失败重置error_code，可以通过sqlite3_errmsg获取错误信息；

        例：

1.  
   sqlite3 *db;
2.  
   if(sqlite3_open("testDB.db", &db) != SQLITE_OK)
3.  
   {
4.  
   printf("错误：%s\n", sqlite3_errmsg(db));
5.  
   exit(1);
6.  
   }

（2）关闭数据库

        原型：int sqlite3_close(sqlite3* pDb);

        参数：

                pDb：操作数据库的指针；

        返回值：成功返回SQLITE_OK，失败重置error_code，可以通过sqlite3_errmsg获取错误信息；

        例：

sqlite3_close(db);

（3）执行一条sql指令

        原型：int sqlite3_exec(sqlite3* pDb,const char *pSql,int (*callback)(void*,int,char**,char**),                          void *pArg,  char **pzErrmsg );

        参数：

                pDb：数据库的指针

                pSql：将要被执行sql指令

                callback：回调函数, 只有在查询语句时，才给回调函数传参

        原型：int (*callback)(void* arg ,int ncolumn ,char** f_value,char** f_name) ；

        功能：获取查询结果。sqlite3_exec查询时，可以获取多条记录，sqlite3_exec会根据记录的条数多次调用回调函数，回调函数每次只获取sqlite3_exec查询到的一行记录的内容

        参数：

                arg：为回调函数传递的参数

                ncolumn：记录中包含的字段的列数 

                f_value：保存每个字段值的指针数组

                f_name：保存每个字段名称的指针数组

        返回值： 成功：0；失败：非0

                pArg：为callback 传参的

                pzErrmsg：错误信息的地址

        返回值：成功返回SQLITE_OK，失败重置error_code，可以通过sqlite3_errmsg获取错误信息；

        例：

                执行一条SQL指令

1.  
   sprintf(sql, "create table %s(id int, name char)", "test");
2.  
   if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
3.  
   printf("错误：创建%s表失败<%s>\n", "test", errmsg);
4.  
   else
5.  
   printf("成功：创建%s表成功\n", "test");

                执行回调函数查找数据

1.  
   int callback(void *arg, int ncolumn, char **text, char **name)
2.  
   {
3.  
   printf("查询结果: column = %d\n", ncolumn);
4.  
   printf("id = %s, name = %s\n", text[0], text[1]);
5.  
   return 0;
6.  
   }
7.  
    
8.  
   void get_message(sqlite3 *db)
9.  
   {
10.  
    char sql[128] = {0};
11.  
    char *errmsg;
12.  
     
13.  
    sprintf(sql, "select * from %s", "test");
14.  
    if(sqlite3_exec(db, sql, callback, NULL, &errmsg) != SQLITE_OK)
15.  
    printf("错误：查询%s表失败<%s>\n", "test", errmsg);
16.  
    else
17.  
    printf("成功：查询%s表成功\n", "test");
18.  
    }

（4）从数据库查询结果

        原型：int sqlite3_get_table( sqlite3 *pDb, const char *pSql, char ***pazResult,  int *pnRow, 

                         int *pnColumn, char **pzErrmsg );

        参数：

                pDb：数据库操作句柄

                pSql：数据库的sql语句

                pazResult：查询的结果（函数中已创建新的内存存储查询信息结果，此处传递char**                                 类型指针变量地址即可）

                pnRow：符合查询条件的行数 ，不包含字段名的行

                pnColumn：符合查询条件的列数

                pzErrmsg：错误消息

        返回值：成功返回0，失败重置error_code，可以通过sqlite3_errmsg获取错误信息；

        例：

1.  
   void get_table(sqlite3 *db)
2.  
   {
3.  
   char sql[128] = {0};
4.  
   char **ret;
5.  
   int nrow, ncolumn;
6.  
   char *errmsg;
7.  
    
8.  
   sprintf(sql, "select * from %s where id = 1", "test");
9.  
   if(sqlite3_get_table(db, sql, &ret, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
10.  
    printf("错误：查询%s表失败<%s>\n", "test", errmsg);
11.  
     
12.  
    printf("row= %d, colum = %d\n", nrow, ncolumn);
13.  
    int i, j, n = 0;
14.  
    for(i = 0; i < nrow 1; i )
15.  
    {
16.  
    for(j = 0; j < ncolumn; j )
17.  
    printf("%-15s", ret[n ]);
18.  
    putchar(10);
19.  
    }
20.  
    }

（5）释放内存

        原型：void sqlite3_free_table(char **pzResult);

        参数：

                pzResult：调用sqlite3_get_table函数时，申请查询信息的存储空间；

2.注意

        使用sqlite3的api时除了添加头文件外，编译时还需添加添加 “ -lsqlite3”连接动态库，否则编译报类似“collect2: error: ld returned 1 exit status”错误。

---

总结

这篇好文章是转载于：学新通技术网