一篇文章带你掌握SQLite3基本用法_Sqlite

一、基本语法

１．常用指令

.open filename --打开文件
-- 注解
.show --显示sqlite 命令提示符的默认设置
.q --退出
.databases --显示数据库（注：显示打开的数据库）
.help  --帮助
.dump  --导入导出数据库
.tables  --查看表

2.数据类型

存储类型	描述
null	空值
int	整形
text	一个文本字符串
blob	一个blob数据
integer	一个带符号的整数，根据值的大小存储在１、２、３、４、６或８字节中
real	值是一个浮点值，存储为８字节的浮点数
......	......

３．创建数据库

.open test.db --没有就创建
sqlite3 databasename.db

上面的命令将在当前目录下创建一个文件 testdb.db。该文件将被 sqlite 引擎用作数据库。如果您已经注意到 sqlite3 命令在成功创建数据库文件之后，将提供一个 sqlite> 提示符。

.databases 命令用于检查它是否在数据库列表中。

.open 操作

4. 导入/导出数据库

sqlite3 test.db .dump　> filename --导出
sqlite3 test.db < filename --导入

上面的转换流整个 testdb.db 数据库的内容到 sqlite 的语句中，并将其转储到 ascii 文本文件 testdb.sql 中。您可以通过简单的方式从生成的 testdb.sql 恢复，如下所示我删掉testdb.db后：

5.创建表

--注意，在打开数据库时才能操作
create table database_name.table_name(
   column1 datatype  primary key(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnn datatype,
);

create table 是告诉数据库系统创建一个新表的关键字。create table 语句后跟着表的唯一的名称或标识。您也可以选择指定带有 table_name 的 database_name。

如上图所示，我们创建了company department两个表。其中id 作为主键，not null 的约束表示在表中创建纪录时这些字段不能为 null。

６．查看表的详细信息

.schema --注意：打开数据库时才能操作

７．删除表

drop table database_name.table_name;

如上，删除了名为department的表

８.插入数据

insert into table_name [(column1, column2, column3,...columnn)]  
values (value1, value2, value3,...valuen);

在这里，column1, column2,...columnn 是要插入数据的表中的列的名称。

如果要为表中的所有列添加值，您也可以不需要在 sqlite 查询中指定列名称。但要确保值的顺序与列在表中的顺序一致。sqlite 的 insert into 语法如下：

insert into table_name values (value1,value2,value3,...valuen);

现在，我已经创建了company表，如下

create table company(
   id int primary key     not null,
   name           text    not null,
   age            int     not null,
   address        char(50),
   salary         real
);

现在，下面的语句将在 company 表中创建六个记录：

insert into company (id,name,age,address,salary)
values (1, 'paul', 32, 'california', 20000.00 );
 
insert into company (id,name,age,address,salary)
values (2, 'allen', 25, 'texas', 15000.00 );
 
insert into company (id,name,age,address,salary)
values (3, 'teddy', 23, 'norway', 20000.00 );
 
insert into company (id,name,age,address,salary)
values (4, 'mark', 25, 'rich-mond ', 65000.00 );
 
insert into company (id,name,age,address,salary)
values (5, 'david', 27, 'texas', 85000.00 );
 
insert into company (id,name,age,address,salary)
values (6, 'kim', 22, 'south-hall', 45000.00 );

输出结果如下：

我们也可以使用第二种语法在company 表中创建一个记录，如下所示：

insert into company values (7, 'james', 24, 'houston', 10000.00 );

输出结果如下：

９.格式化输出

.header on
.mode column
.timer on　--开启ｃｐｕ定时器
select * from table_name;　--显示表table_name

非格式化输出

格式化输出

１０．输出表

完整输出

.header on
.mode column
select * from company;

输出指定列

.header on
.mode column
select id, name, salary from company;　--只输出id, name和salary三列

设置输出列的宽度

.width num1,num1,num3....
select * from company;

下面 .width 命令设置第一列的宽度为 10，第二列的宽度为 20，第三列的宽度为 10。输出结果如下：

１１．运算符

sqlite运算符主要用于 sqlite 语句的 where 子句中执行操作，如比较和算术运算。

运算符用于指定 sqlite 语句中的条件，并在语句中连接多个条件。

算术运算符：

运算符描述实例+加法 - 把运算符两边的值相加a + b 将得到 30-减法 - 左操作数减去右操作数a - b 将得到 -10*乘法 - 把运算符两边的值相乘a * b 将得到 200/除法 - 左操作数除以右操作数b / a 将得到 2%取模 - 左操作数除以右操作数后得到的余数b % a will give 0

比较运算符

运算符	描述	实例
+	加法 - 把运算符两边的值相加	a + b 将得到 30
-	减法 - 左操作数减去右操作数	a - b 将得到 -10
*	乘法 - 把运算符两边的值相乘	a * b 将得到 200
/	除法 - 左操作数除以右操作数	b / a 将得到 2
%	取模 - 左操作数除以右操作数后得到的余数	b % a will give 0

逻辑运算符

运算符	描述	实例
==	检查两个操作数的值是否相等，如果相等则条件为真。	(a == b) 不为真。
=	检查两个操作数的值是否相等，如果相等则条件为真。	(a = b) 不为真。
!=	检查两个操作数的值是否相等，如果不相等则条件为真。	(a != b) 为真。
<>	检查两个操作数的值是否相等，如果不相等则条件为真。	(a <> b) 为真。
>	检查左操作数的值是否大于右操作数的值，如果是则条件为真。	(a > b) 不为真。
<	检查左操作数的值是否小于右操作数的值，如果是则条件为真。	(a < b) 为真。
>=	检查左操作数的值是否大于等于右操作数的值，如果是则条件为真。	(a >= b) 不为真。
<=	检查左操作数的值是否小于等于右操作数的值，如果是则条件为真。	(a <= b) 为真。
!<	检查左操作数的值是否不小于右操作数的值，如果是则条件为真。	(a !< b) 为假。
!>	检查左操作数的值是否不大于右操作数的值，如果是则条件为真。	(a !> b) 为真。

位运算符

p	q	p & q	p \| q
0	0	0	0
0	1	0	1
1	1	1	1
1	0	0	1

下面直接上例子

我有company 表如下：

id          name        age         address     salary
----------  ----------  ----------  ----------  ----------
1           paul        32          california  20000.0
2           allen       25          texas       15000.0
3           teddy       23          norway      20000.0
4           mark        25          rich-mond   65000.0
5           david       27          texas       85000.0
6           kim         22          south-hall  45000.0
7           james       24          houston     10000.0

用 select列出salary 大于 50,000.00 的所有记录：

select * from company where salary > 50000;

输出结果如下：

用 select列出salary 等于的所有记录：

select * from company where salary = 20000;

输出结果如下：

用 select列出age 大于等于 25 且salary大于等于 65000.00的所有记录：

select * from company where age >= 25 and salary >= 65000;

输出结果如下：

12.where子句

sqlite的 where 子句用于指定从一个表或多个表中获取数据的条件。如果满足给定的条件，即为真（true）时，则从表中返回特定的值。您可以使用 where 子句来过滤记录，只获取需要的记录。where 子句不仅可用在 select 语句中，它也可用在 update、delete 语句中，等等。用例参考运算符。

13.删除记录表中的数据

sqlite中，删除记录表数据为delete语句，我们可以使用带有 where 子句的 delete。

语法如下：

delete from table_name　where [condition];

我们有以下记录表：

删除id为７的列：

delete from company where id = 7;

再次输出结果：

14.update语句

sqlite 的 update 查询用于修改表中已有的记录。可以使用带有 where 子句的 update 查询来更新选定行，否则所有的行都会被更新。

语法：

update table_name　
set column1 = value1, column2 = value2...., columnn = valuen
where [condition];

注：这三行实为同一行。

现在我有数据表如下：

把company表中id 为 6 的客户地址改为texas：

update company set address = 'texas' where id = 6;

修改结果：

如果您想修改 company 表中 address 和 salary 列的所有值，则不需要使用 where 子句，update 查询如下：

update company set address = 'texas', salary = 20000.00;

修改结果：

二、ｃ/c++操作

1.接口api

下面接口能满足我们的基本需求，需要学习更多的操作，我们可以参考官方文档。

序号	api & 描述
1	sqlite3_open(const char filename, sqlite3 *ppdb) 该例程打开一个指向 sqlite 数据库文件的连接，返回一个用于其他 sqlite 程序的数据库连接对象。如果 filename 参数是 null 或 ':memory:'，那么 sqlite3_open() 将会在 ram 中创建一个内存数据库，这只会在 session 的有效时间内持续。如果文件名 filename 不为 null，那么 sqlite3_open() 将使用这个参数值尝试打开数据库文件。如果该名称的文件不存在，sqlite3_open() 将创建一个新的命名为该名称的数据库文件并打开。
2	sqlite3_exec(sqlite3, const char sql, sqlite_callback, void data, char *errmsg) 该例程提供了一个执行 sql 命令的快捷方式，sql 命令由 sql 参数提供，可以由多个 sql 命令组成。在这里，第一个参数 sqlite3 是打开的数据库对象，sqlite_callback 是一个回调，data 作为其第一个参数，errmsg 将被返回用来获取程序生成的任何错误。 sqlite3_exec() 程序解析并执行由 sql 参数所给的每个命令，直到字符串结束或者遇到错误为止。
3	sqlite3_close(sqlite3*) 该例程关闭之前调用 sqlite3_open() 打开的数据库连接。所有与连接相关的语句都应在连接关闭之前完成。如果还有查询没有完成，sqlite3_close() 将返回 sqlite_busy 禁止关闭的错误消息。

序号

api & 描述

sqlite3_open(const char *filename, sqlite3 **ppdb)

该例程打开一个指向 sqlite 数据库文件的连接，返回一个用于其他 sqlite 程序的数据库连接对象。

如果 filename 参数是 null 或 ':memory:'，那么 sqlite3_open() 将会在 ram 中创建一个内存数据库，这只会在 session 的有效时间内持续。

如果文件名 filename 不为 null，那么 sqlite3_open() 将使用这个参数值尝试打开数据库文件。如果该名称的文件不存在，sqlite3_open() 将创建一个新的命名为该名称的数据库文件并打开。

sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char **errmsg)

该例程提供了一个执行 sql 命令的快捷方式，sql 命令由 sql 参数提供，可以由多个 sql 命令组成。

在这里，第一个参数 sqlite3 是打开的数据库对象，sqlite_callback 是一个回调，data 作为其第一个参数，errmsg 将被返回用来获取程序生成的任何错误。

sqlite3_exec() 程序解析并执行由 sql 参数所给的每个命令，直到字符串结束或者遇到错误为止。

sqlite3_close(sqlite3*)

该例程关闭之前调用 sqlite3_open() 打开的数据库连接。所有与连接相关的语句都应在连接关闭之前完成。

如果还有查询没有完成，sqlite3_close() 将返回 sqlite_busy 禁止关闭的错误消息。

2.连接数据库

下面的 c 代码段显示了如何连接到一个现有的数据库。如果数据库不存在，那么它就会被创建，最后将返回一个数据库对象。

#include <stdio.h>
#include <sqlite3.h>
 
int main(int argc, char* argv[])
{
   sqlite3 *db;
   char *zerrmsg = 0;
   int rc;
 
   rc = sqlite3_open("test.db", &db);
 
   if( rc ){
      fprintf(stderr, "can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "opened database successfully\n");
   }
   sqlite3_close(db);
}

编译命令

gcc lianjie.c -l sqlite3

运行结果

终端输入ls -l命令发现多了个test.db文件，如图：

３．创建表

ｃ语言创建表与终端创建操作差不多，只不过命令由sqlite3_exec()函数的sql参数传入。格式如下：

sql = "create table company("  \
         "id int primary key     not null," \
         "name           text    not null," \
         "age            int     not null," \
         "address        char(50)," \
         "salary         real );";

示例代码：

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 
 
static int callback(void *notused, int argc, char **argv, char **azcolname){
   int i;
   for(i=0; i<argc; i++){
      printf("%s = %s\n", azcolname[i], argv[i] ? argv[i] : "null");
   }
   printf("\n");
   return 0;
}
 
int main(int argc, char* argv[])
{
   sqlite3 *db;
   char *zerrmsg = 0;
   int  rc;
   char *sql;
 
   /* open database */
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stdout, "opened database successfully\n");
   }
 
   /* create sql statement */
   sql = "create table company("  \
         "id int primary key     not null," \
         "name           text    not null," \
         "age            int     not null," \
         "address        char(50)," \
         "salary         real );";
 
   /* execute sql statement */
   rc = sqlite3_exec(db, sql, callback, 0, &zerrmsg);
   if( rc != sqlite_ok ){
   fprintf(stderr, "sql error: %s\n", zerrmsg);
      sqlite3_free(zerrmsg);
   }else{
      fprintf(stdout, "table created successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

输出结果：

再次ls -l:

我们可以看到，test.db文件大小明显变大了。

4.插入数据

与创建表类似，sql参数设为：

  sql = "insert into company (id,name,age,address,salary) "  \
         "values (1, 'paul', 32, 'california', 20000.00 ); " \
         "insert into company (id,name,age,address,salary) "  \
         "values (2, 'allen', 25, 'texas', 15000.00 ); "     \
         "insert into company (id,name,age,address,salary)" \
         "values (3, 'teddy', 23, 'norway', 20000.00 );" \
         "insert into company (id,name,age,address,salary)" \
         "values (4, 'mark', 25, 'rich-mond ', 65000.00 );";

示例代码：

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
 
static int callback(void *notused, int argc, char **argv, char **azcolname){
   int i;
   for(i=0; i<argc; i++){
      printf("%s = %s\n", azcolname[i], argv[i] ? argv[i] : "null");
   }
   printf("\n");
   return 0;
}
 
int main(int argc, char* argv[])
{
   sqlite3 *db;
   char *zerrmsg = 0;
   int rc;
   char *sql;
 
   /* open database */
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "opened database successfully\n");
   }
 
   /* create sql statement */
   sql = "insert into company (id,name,age,address,salary) "  \
         "values (1, 'paul', 32, 'california', 20000.00 ); " \
         "insert into company (id,name,age,address,salary) "  \
         "values (2, 'allen', 25, 'texas', 15000.00 ); "     \
         "insert into company (id,name,age,address,salary)" \
         "values (3, 'teddy', 23, 'norway', 20000.00 );" \
         "insert into company (id,name,age,address,salary)" \
         "values (4, 'mark', 25, 'rich-mond ', 65000.00 );";
 
   /* execute sql statement */
   rc = sqlite3_exec(db, sql, callback, 0, &zerrmsg);
   if( rc != sqlite_ok ){
      fprintf(stderr, "sql error: %s\n", zerrmsg);
      sqlite3_free(zerrmsg);
   }else{
      fprintf(stdout, "records created successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

输出结果：

５．查表操作

sqlite3_exec()给我们提供了一个回调函数，其声明如下：

typedef int (*sqlite3_callback)(
void*,    /* data provided in the 4th argument of sqlite3_exec() */
int,      /* the number of columns in row */
char**,   /* an array of strings representing fields in the row */
char**    /* an array of strings representing column names */
);

第一个参数：即第四个参数传入的数据

第二个参数：行中的列数

第三个参数：表示行中字段的字符串数组，即各行中的数据

第四个参数：表示列名的字符串数组，创建链表时设置的

执行流程：查表，是否还有符合条件数据。有，执行sqlite3_callback()函数；没有，退出

用法讲完了，下面看例子：

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
 
static int callback(void *data, int argc, char **argv, char **azcolname){
   int i;
   fprintf(stderr, "%s: ", (const char*)data);
   for(i=0; i<argc; i++){
      printf("%s = %s\n", azcolname[i], argv[i] ? argv[i] : "null");
   }
   printf("\n");
   return 0;
}
 
int main(int argc, char* argv[])
{
   sqlite3 *db;
   char *zerrmsg = 0;
   int rc;
   char *sql;
   const char* data = "callback function called";
 
   /* open database */
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "opened database successfully\n");
   }
 
   /* create sql statement */
   sql = "select * from company";
 
   /* execute sql statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zerrmsg);
   if( rc != sqlite_ok ){
      fprintf(stderr, "sql error: %s\n", zerrmsg);
      sqlite3_free(zerrmsg);
   }else{
      fprintf(stdout, "operation done successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

上面程序显示了如何从前面创建的 company 表中获取并显示记录，输出结果如下：

这里我没有特殊指明查询条件，表示查询全部。

６．数据删除操作

sql参数设置：

sql = "delete from company where id=2; " \\删除ｉｄ等于２的行
         "select * from company";        \\显示表

这里跟上面不同的是多了一个命令，表面sql参数可由多个命令组成。

示例代码：

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 
 
static int callback(void *data, int argc, char **argv, char **azcolname){
   int i;
   fprintf(stderr, "%s: ", (const char*)data);
   for(i=0; i<argc; i++){
      printf("%s = %s\n", azcolname[i], argv[i] ? argv[i] : "null");
   }
   printf("\n");
   return 0;
}
 
int main(int argc, char* argv[])
{
   sqlite3 *db;
   char *zerrmsg = 0;
   int rc;
   char *sql;
   const char* data = "callback function called";
 
   /* open database */
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "opened database successfully\n");
   }
 
   /* create merged sql statement */
   sql = "delete from company where id=2; " \
         "select * from company";
 
   /* execute sql statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zerrmsg);
   if( rc != sqlite_ok ){
      fprintf(stderr, "sql error: %s\n", zerrmsg);
      sqlite3_free(zerrmsg);
   }else{
      fprintf(stdout, "operation done successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

输出：

７．update 操作

操作示例：

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 
 
static int callback(void *data, int argc, char **argv, char **azcolname){
   int i;
   fprintf(stderr, "%s: ", (const char*)data);
   for(i=0; i<argc; i++){
      printf("%s = %s\n", azcolname[i], argv[i] ? argv[i] : "null");
   }
   printf("\n");
   return 0;
}
 
int main(int argc, char* argv[])
{
   sqlite3 *db;
   char *zerrmsg = 0;
   int rc;
   char *sql;
   const char* data = "callback function called";
 
   /* open database */
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "opened database successfully\n");
   }
 
   /* create merged sql statement */
   sql = "update company set salary = 25000.00 where id=1; " \
         "select * from company";
 
   /* execute sql statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zerrmsg);
   if( rc != sqlite_ok ){
      fprintf(stderr, "sql error: %s\n", zerrmsg);
      sqlite3_free(zerrmsg);
   }else{
      fprintf(stdout, "operation done successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

输出结果：