linux sqlite3编程

ubuntu配置SQLite3数据库
sudo apt-get install sqlite3
sudo apt-get install libsqlite3-dev 
sudo apt-get install sqlitebrowser

SQLite3数据格式
bit  0或1的整型数字
int 从-2^31(-2,147,483,648)到2^31(2,147,483,647)的整型数字
smallint 从-2^15(-32,768)到2^15(32,767)的整型数字
tinyint  从0到255的整型数字

float 从-1.79E+308到1.79E+308可变精度的数字
real  从-3.04E+38到3.04E+38可变精度的数字
char 定长非Unicode的字符型数据，最大长度为8000   最多带有8000个元素的字符串

varchar 变长非Unicode的字符型数据，最大长度为8000
text 变长非Unicode的字符型数据，最大长度为2^31-1(2G)

nchar 定长Unicode的字符型数据，最大长度为8000
nvarchar 变长Unicode的字符型数据，最大长度为8000
ntext 变长Unicode的字符型数据，最大长度为2^31-1(2G)

Sqlite3的默认数据编码是utf-8

Sqlite3的命令：
1）新建一个数据库  
sqlite3   数据名
例子
 ./sqlite3 xxx.db 

2）创建表
CREATE TABLE  表名 （ 记录名1 类型，记录名2 类型，记录名3  类型 ，。。。。。）；
 例子：
CREATE TABLE SensorData(
...> ID INTEGER PRIMARY KEY,
...> SensorID INTEGER,
...> SiteNum INTEGER,
...> Time VARCHAR(12),
...> SensorParameter REAL
...> ); 
注意：sqlite下除了特殊命令都要以分号 “;” 结尾，否则它将一直等待第一个分号的出现才判断这条命令结束ID INTEGER PRIMARY KEY AUTOINCREMENT、rowid

3）插入、删除值 
往表中插入数据
INSERT INTO 表名 VALUES（字段1，字段2，字段3。。。。 ）；
例子：INSERT INTO "SensorData" VALUES(NULL, 1, 0, '200605011206', 18.9); 

往表中删除数据中的记录
DELETE FROM 表名 WHERE  匹配内容 ；
例子：
DELETE FROM "SensorData" WHERE SensorID = 1 ;
DELETE FROM SensorData  WHERE SensorID = 1 ;


4）查看数据中的表信息
.tables 
5）查看具体表数据、内容
 SELECT  *  FROM  表名  ；

6)修改更新数据
UPDATE [数据库名称].表名 SET 字段1=字段1值,字段2=字段2值… where 条件表达式;

UPDATE tbl1 SET name1=“张三” where number1 =1
把tbl1表中number1=1项的name1改为“张三”

update student set name='wangwu' where name='zhaoliu';
把student 表中的名字为zhaoliu的修改成名字为wangwu，其它值不变


每一个记录中包含了多个字段
一个表格中有多个记录
一个数据库文件中可以有多个表格
修改数据表中的列（字段名以及默认值）
 添加列
　　语法1：alter table 数据表名称 add 列定义 first；
　　语法2：alter table 数据表名称 add 列定义 after 列名称；
　　语法3：alter table 数据表名称 add (列定义,...)；
　　示例：
　　sqlite3> alter table tb1 add age int first;
　　sqlite3> alter table tb1 add age2 int default 10 after id;
　　sqlite3> alter table tb1 add sex tiny int default 0;

修改表名
alter table tb1 rename to  new_name 

删除SQLite3中的表
sqlite> drop  table student;
sqlite> .tables 

复制表格：
create table 表的名字 as select 保留的字段 from 原来的表;
                                

创建表时候限制某些字段的内容
          create table test (name text,score float,check(score>0 and score<100));
                                 限制分数必须是0到100之间

显示的时候也显示表头
.explain ON|OFF           
Turn output mode suitable for EXPLAIN on or off.( 以合适的方式显示表头, 不带参数则为开启)

显示字段名以及字段类型
PRAGMA table_info(表名) 

 如何实现删除字段？
	复制： 从源表中取出对应的字段组成新的表
	 create table new_x  as select name,height from new1；
	删除源表drop table new1;
	将新表重命名 alter table new_x rename to  new1；

条件判断：
where    进行匹配 

运算符
运算符	含义
=	等于
>	大于
<	小于
>=	大于或等于
<=	小于或等于
<>	不等于
!	非

通配符：
*

单表查询
 SELECT  *  FROM  表名  where  条件 ；
sqlite> select * from  new1 where height=4;         
sqlite> select name from  new1 where height=4;

多表联表查询(嵌套子查询) in
sqlite> select  birth  from  class  where  tree_name in (select name from new1 where height=4);
字段       tree_name 为任意一个 （另一个表new1 中的匹配项)

查询集合操作
exists  如果条件成立，表示存在对应的记录
select   显示的字段  from  表1  a  where exists ( select 匹配项 from 表2  b where  a.某字段 = b.某字段   )  
表1  a  表示使用表1当成a表， 起了个别名
表2  b  表示使用表2当成b表， 起了个别名

例子：把class表中有birth字段的树木，在new1表中的详细信息都显示出来
select  *  from new1  a where exists (select tree_name from class b where a.name=b.tree_name);

例子：把class表中没有birth字段的树木，在new1表中的详细信息都显示出来
select  *  from new1  a where not exists (select tree_name from class b where a.name=b.tree_name);

between ... and....  设置取值范围
select * from 表名 where  字段名 between 最小值  and  最大值 ；
例子：select * from t_score where  score between 60 and 80

order by   将查询后结果集重新排序 ，默认是升序排序
select * from 表名 order by 字段名
例子：select * from t_student order by sno

sqlite3编程
sqlite3_open 打开数据库文件.db
 int sqlite3_open(const char *,sqlite3 **db)
const char * ----- db文件名，含有路径信息的字符串
sqlite3 **db  --- 存放的是数据库信息，需要先定义同类型变量
功能：打开或创建数据库，并通过输出参数返回“连接”
参数：1、数据库文件【IN】
		2、sqlite3 数据指针【out】

使用说明：
	typedef  struct sqlite3   sqlite3;

sqlite3_prepare

sqlite3_exec
 int sqlite3_exec(sqlite3 *db,const char *sql, sqlite3_callback, void *, char **errmsg)
功能：执行多条或一条SQL语句，并将结果传递给回调函数
参数：1、数据库连接【IN】
	     2、要执行的sql语句【IN】
       3、回调函数 【IN】   
	     4、传递给回调函数的参数地址【IN】 
       5、返回的错误信息【out】 
返回值：错误代码，参见SQLite错误代码
使用说明：
typedef int (*sqlite3_callback)(void*,int,char**, char**);

sqlite3_close
int sqlite3_close(sqlite3 *db)
功能：关闭数据库，释放资源
参数：1、数据库文件【IN】
返回值：错误代码，参见SQLite错误代码
使用说明：
	在不与数据库进行交互时，关闭数据库“连接”，并释放db所指向的内存区.

//应用实例
#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: \n", (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()
{
    sqlite3* db = NULL;
    char *err = 0;
    int  ret;
    char *sql;
    const char* data = "callback function";

    int len = sqlite3_open("my.db", &db);
    if(len)
	{
        fprintf(stderr, "open fail: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 0;
    }

    sql = "create table student("   \
          "id 			int,"  		\
          "name 		text," 		\
          "age  		int,"  		\
          "address  	char(50),"  \
          "achievement  real);";

    ret = sqlite3_exec(db, sql, NULL, NULL, &err);
    if( ret != SQLITE_OK )
	{
		fprintf(stderr, "create error: %s\n", err);
        sqlite3_free(err);
    }

	//插入
     sql = 	"insert into student (id, name, age, address, achievement) "  	\
				"values (1, 'Nicholas', 18, 'china', 100 ); "  		 			\
			"insert into student (id, name, age, address, achievement) "  	\
				"values (2, 'Owen', 25, 'US', 99.0 ); "      					\
			"insert into student (id, name, age, address, achievement) "  	\
				"values (3, 'Kevin', 23, 'UK', 98.0 ); "     					\
			"insert into student (id, name, age, address, achievement) "  	\
				"values (4, 'Hellen', 25, 'Janpan', 97.0 ); ";
     ret = sqlite3_exec(db, sql, NULL, NULL, &err);
     if( ret != SQLITE_OK )
	 {
         fprintf(stderr, "insert error: %s\n", err);
         sqlite3_free(err);
     }
	 else
	 {		
         fprintf(stdout, "insert success\n\n");
     }
 
	//查询
     sql = "select * from student";
     ret = sqlite3_exec(db, sql, callback, (void*)data, &err);
     if( ret != SQLITE_OK )
	 {
         fprintf(stderr, "select error: %s\n", err);
         sqlite3_free(err);
     }
	 else
	 {
         fprintf(stdout, "select success\n\n");
     }

     //修改
     sql = "update student set name = 'Kim' where ID=1; " \
             "select * from student";
    ret = sqlite3_exec(db, sql, callback, (void*)data, &err);
    if( ret != SQLITE_OK )
	{
        fprintf(stderr, "update error: %s\n", err);
        sqlite3_free(err);
    }
	else
	{
        fprintf(stdout, "update success\n\n");
    }

    //删除
    sql = "delete from student where ID=2; " \
            "select * from student";
    ret = sqlite3_exec(db, sql, callback, (void*)data, &err);
    if( ret != SQLITE_OK )
	{
        fprintf(stderr, "delete error: %s\n", err);
        sqlite3_free(err);
    }
	else
	{
        fprintf(stdout, "delete success\n\n");
    }

	int i;
    int row=0;
    int column = 0;
    char **result=NULL;       
    sql="select * from student";
    sqlite3_get_table( db , sql , &result , &row , &column , &err );
    printf("row = %d column = %d\n", row, column);
    for(i = 0; i < (row + 1) * column; i++)
	{
		printf("[%d] = %s\n", i, result[i]);
		if( (i+1)%5 == 0)
			printf("****************************\n");
    }
	sqlite3_free_table(result);
	
	//删除表
	sql = "drop table student;";
	ret = sqlite3_exec(db, sql, NULL, NULL, &err);
    if( ret != SQLITE_OK )
	{
        fprintf(stderr, "drop error: %s\n", err);
        sqlite3_free(err);
    }
	else
	{
        fprintf(stdout, "drop success\n\n");
    }
	
    sqlite3_close(db);
    return 0;
}

//另一种写法
#include "sqlite3.h"
#include <stdio.h>

sqlite3 *db = NULL;

void insert_sql(char *name, char *passwd)
{
	char *err_msg = 0;
	char buf[1024] = {0};
	sprintf(buf, "INSERT INTO user (name, passwd) VALUES ('%s', '%s')", name, passwd);
	int rc = sqlite3_exec(db, buf, 0, 0, &err_msg);
    if (rc != SQLITE_OK) 
	{
        fprintf(stderr, "插入数据失败: %s\n", err_msg);
        sqlite3_free(err_msg);
        return ;
    } 
	else 
	{
        printf("数据插入成功！\n");
    }
}

void select_sql()
{
	sqlite3_stmt *stmt;
	const char *query = "SELECT * FROM user;";
    int rc = sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
    if (rc == SQLITE_OK)
	{
		while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) 
		{
			const char *name = sqlite3_column_text(stmt, 0);
			const char *passwd = sqlite3_column_text(stmt, 1);
			printf("name: %s, passwd: %s\n", name, passwd);
		}
		
		if (rc != SQLITE_DONE) 
		{
			fprintf(stderr, "无法获取结果集: %s\n", sqlite3_errmsg(db));
		}

		// 释放查询语句资源
		sqlite3_finalize(stmt);
	}
}

int main()
{
	char *err_msg = 0;
	int ret = sqlite3_open_v2("1.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE, NULL);
	if(ret != SQLITE_OK)
	{
		printf("open fail:%s\n", sqlite3_errmsg(db));
		return -1;
	}
	
	char *create_table_sql = "CREATE TABLE IF NOT EXISTS user (name TEXT, passwd TEXT)";
	ret = sqlite3_exec(db, create_table_sql, 0, 0, &err_msg);
    if (ret != SQLITE_OK) 
	{
        fprintf(stderr, "create fail: %s\n", err_msg);
        sqlite3_free(err_msg);
        return 1;
    }
	
	insert_sql("111", "aaa");
	insert_sql("222", "bbb");
	
	select_sql();
	
	// 关闭数据库连接
    sqlite3_close_v2(db);
	
	return 0;
}

觉得有帮助的话，打赏一下呗。。