sql语句：
1.select count(*) FROM student//查询表中数据行数
2.SELECT sex,student_number FROM student where name=?//查询名字为《要查的名字》的性别，学号
查询demon

//查找表中所有学生人数
#define SELECT_STUDENT_MSG "select count(*) FROM student"//查找某个学生的信息
#define SELECT_STUDENT_NAME "SELECT sex,student_number FROM student where name=?;"
/***************************
**1.1查询表中所数据个数
*****************************/
int select_students_number(sqlite3 *db)
{int res=-1;int ret=-1;sqlite3_stmt *stmt;//1.执行sqlite语句res=sqlite3_prepare_v2(db,SELECT_STUDENT_MSG,-1,&stmt,NULL);if (SQLITE_OK != res) {goto out;}//2.遍历select返回的结果res=sqlite3_step(stmt);//SQLITE_ROW--->下一行准备if (SQLITE_ROW == res) {ret = sqlite3_column_int(stmt, 0);//返回当前语句结果}out_free:sqlite3_finalize(stmt);return ret;
out:if (res < 0) {printf("%s", sqlite3_errmsg(db));return ret;}
}/***************************
**1.2查询表中相应学生信息
*****************************/
student_t *selec_student_msg(sqlite3 *db,char *name)
{int res=-1;int err=-1;struct student *msg;sqlite3_stmt *stmt;//1.执行sqlit语句res=sqlite3_prepare_v2(db,SELECT_STUDENT_NAME,-1,&stmt,NULL);if (SQLITE_OK != res) {err = -1;goto out_free;}res = sqlite3_bind_text(stmt,1,name,-1,SQLITE_STATIC); if (res != SQLITE_OK) goto out;//2遍历返回结果while(SQLITE_ROW == sqlite3_step(stmt)){msg = calloc(1, sizeof(struct student));if (NULL != sqlite3_column_text(stmt, 0)) {strcpy(msg->student_sex,sqlite3_column_text(stmt, 0));}msg->student_number = sqlite3_column_int(stmt, 1);strcpy(msg->student_name, name);}err=0;
out_free:sqlite3_finalize(stmt);
out:if (err < 0) {printf("%s\n", sqlite3_errmsg(db));}return msg;
}

完整可运行demon

#include <stdio.h>
#include <sqlite3.h>
#include <string.h>
#include <stdlib.h>#define DB_PATH "./student.db" //数据库创建路径char name[10]="\0";
char sex[10]="\0";typedef struct student{char student_name[10];char student_sex[10];int student_number;
}student_t;/***************************sql 语句定义 start**********************************/
#define CREATE_TABLE               \"CREATE TABLE IF NOT EXISTS student( " \"student_number	INTEGER DEFAULT 1,"   \"name		TEXT DEFAULT NULL,"  \"sex		TEXT DEFAULT NULL"  \");"//清空学生表
#define EMPTY_STUDENT "DELETE FROM student"#define INSERT_MSG "INSERT or IGNORE INTO student (student_number,name,sex) VALUES (?,?,?)"//更新学生信息
#define UPDATE_STUDENT_MSG "update student set student_number=?,sex=? where name=?;"//查找表中所有学生人数
#define SELECT_STUDENT_MSG "select count(*) FROM student"//查找某个学生的信息
#define SELECT_STUDENT_NAME "SELECT sex,student_number FROM student where name=?;"/***************************sql 语句定义 end***********************************/static sqlite3 *db;//初始化表
void init_tab(sqlite3 *db)
{int res,err=-1;sqlite3_stmt *stmt = NULL;if (NULL == db) {printf("param error\n");}//1.执行sql select语句res = sqlite3_prepare_v2(db, CREATE_TABLE, -1, &stmt, NULL);if (res != SQLITE_OK) {goto out_err;}//2.遍历select执行的返回结果res = sqlite3_step(stmt);//SQLITE_DONE == res | sqlite3_step() has finished executingif (SQLITE_DONE == res) {err = 0;}out_err://3.销毁前面被sqlite3_prepare创建的准备语句//【每个准备语句都必须使用这个函数去销毁以防止内存泄露】sqlite3_finalize(stmt);if (err < 0) {printf("%s\n", sqlite3_errmsg(db));//打印错误信息}return ;
}/*************************
*1.初始化数据库
*参数：数据库的路径
*return 0成功
**************************/
int sqlite_init(const char *path)
{int res;if(NULL == path)//防止传入参数为空{printf("param error\n");return -1;}res = sqlite3_initialize();//初始化，成功返回SQLITE_OKif (res != SQLITE_OK) {printf("sqlite3_initialize error\n");return -1;}//打开数据库，（存在打开，不存在创建）成功返回1res = sqlite3_open(path, &db);if (res) {printf("cannot open database error\n");return -1;}//初始化表init_tab(db);return 0;
}/*************************
*2.数据库插入数据
*参数：要插入的数据
**************************/void database_insert(int number,const char *name, const char *sex)
{int res, err = -1;sqlite3_stmt *stmt;//1.执行sql select语句res = sqlite3_prepare_v2(db, INSERT_MSG, -1, &stmt, NULL);if (SQLITE_OK != res) {goto out;}//2.绑定数据 int类型res = sqlite3_bind_int(stmt, 1, number);if (res != SQLITE_OK) {goto out_free;   }//2.绑定数据 字符型//SQLITE_STATIC->传递给该字符串的指针将有效,直到执行查询为止res = sqlite3_bind_text(stmt, 2, name, -1, SQLITE_STATIC); if (res != SQLITE_OK) {goto out_free;}res = sqlite3_bind_text(stmt, 3, sex, -1, SQLITE_STATIC); if (res != SQLITE_OK) {goto out_free;}//3.遍历select执行的返回结果res = sqlite3_step(stmt);if (SQLITE_DONE == res) {err = 0;}
out_free://4.销毁前面被sqlite3_prepare创建的准备语句//【每个准备语句都必须使用这个函数去销毁以防止内存泄露】sqlite3_finalize(stmt);
out:if (err < 0) {printf("%s\n", sqlite3_errmsg(db));printf( "res = %d\n", res);}
}/*****************
**3.数据库更新数据
**参数：要更新的数据
*******************/
void update_student_msg(int student_number,char *sex,char *name)
{int res, err = -1;sqlite3_stmt *stmt = NULL;//1.执行sql select语句res = sqlite3_prepare_v2(db, UPDATE_STUDENT_MSG, -1, &stmt, NULL);if (SQLITE_OK != res) {goto out_err;}//2.绑定数据 整型//SQLITE_STATIC->传递给该字符串的指针将有效,直到执行查询为止res = sqlite3_bind_int(stmt, 1, student_number);if (res != SQLITE_OK) goto out_err;res = sqlite3_bind_text(stmt,2,sex,-1,SQLITE_STATIC); if (res != SQLITE_OK) goto out_err;res = sqlite3_bind_text(stmt,3,name,-1,SQLITE_STATIC); if (res != SQLITE_OK) goto out_err;//3.遍历select执行的返回结果res = sqlite3_step(stmt);if (SQLITE_DONE == res) {err = 0;}out_free:sqlite3_finalize(stmt);
out_err:if (err < 0) {printf("%s", sqlite3_errmsg(db));}
}/*************************
*4.数据库删除数据
*参数：要删除的数据
**************************/
//清空数据库
void database_empty_student(sqlite3 *db)
{int res, err = -1;sqlite3_stmt *stmt;//1.执行sql语句res = sqlite3_prepare_v2(db, EMPTY_STUDENT, -1, &stmt, NULL);if (SQLITE_OK != res) {goto out;}//2.遍历执行的返回结果res = sqlite3_step(stmt);if (SQLITE_DONE == res) {err = 0;}out_free:sqlite3_finalize(stmt);
out:if (err < 0) {printf("%s", sqlite3_errmsg(db));}
}
/***************************
**5.1查询表中所数据个数
*****************************/
int select_students_number(sqlite3 *db)
{int res=-1;int ret=-1;sqlite3_stmt *stmt;//1.执行sqlite语句res=sqlite3_prepare_v2(db,SELECT_STUDENT_MSG,-1,&stmt,NULL);if (SQLITE_OK != res) {goto out;}//2.遍历select返回的结果res=sqlite3_step(stmt);//SQLITE_ROW--->下一行准备if (SQLITE_ROW == res) {ret = sqlite3_column_int(stmt, 0);//返回当前语句结果}out_free:sqlite3_finalize(stmt);return ret;
out:if (res < 0) {printf("%s", sqlite3_errmsg(db));return ret;}
}/***************************
**5.2查询表中相应学生信息
*****************************/
student_t *selec_student_msg(sqlite3 *db,char *name)
{int res=-1;int err=-1;struct student *msg;sqlite3_stmt *stmt;//1.执行sqlit语句res=sqlite3_prepare_v2(db,SELECT_STUDENT_NAME,-1,&stmt,NULL);if (SQLITE_OK != res) {err = -1;goto out_free;}res = sqlite3_bind_text(stmt,1,name,-1,SQLITE_STATIC); if (res != SQLITE_OK) goto out;//2遍历返回结果while(SQLITE_ROW == sqlite3_step(stmt)){msg = calloc(1, sizeof(struct student));if (NULL != sqlite3_column_text(stmt, 0)) {strcpy(msg->student_sex,sqlite3_column_text(stmt, 0));}msg->student_number = sqlite3_column_int(stmt, 1);strcpy(msg->student_name, name);}err=0;
out_free:sqlite3_finalize(stmt);
out:if (err < 0) {printf("%s\n", sqlite3_errmsg(db));}return msg;
}void init_msg(const char *name_msg,const char *sex_msg)
{memset(name, '\0', sizeof(name));memset(name, '\0', sizeof(sex));sprintf(name,"%s",name_msg);sprintf(sex,"%s",sex_msg);	
}int main()
{	int student_count=0;struct student *msg;sqlite_init(DB_PATH);database_empty_student(db);//1.插入init_msg("小明","男");	database_insert(1,name,sex);init_msg("小龙","男");	database_insert(2,name,sex);init_msg("小红","女");	database_insert(3,name,sex);//2.更新数据,将小明的学号更改为3，小红改为1init_msg("小明","男");	update_student_msg(3,sex,name);init_msg("小红","女");	update_student_msg(1,sex,name);//查询操作student_count=select_students_number(db);printf("====学生数量是：%d\n",student_count);msg=selec_student_msg(db,"小龙");printf("=====姓名：%s | 学号：%d       | 性别：%s\n",msg->student_name,msg->student_number,msg->student_sex);msg=selec_student_msg(db,"小明");printf("=====姓名：%s | 学号：%d       | 性别：%s\n",msg->student_name,msg->student_number,msg->student_sex);msg=selec_student_msg(db,"小红");printf("=====姓名：%s | 学号：%d       | 性别：%s\n",msg->student_name,msg->student_number,msg->student_sex);return 0;
}

运行结果：