sql语句:
1.select count(*) FROM student//查询表中数据行数
2.SELECT sex,student_number FROM student where name=?//查询名字为《要查的名字》的性别,学号
3.select * from table1 left/right join table2 on table1.id=table2.id //外连接查询,结合俩个表中数据去查询
查询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 executing
if (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_OK
if (res != SQLITE_OK) {
printf("sqlite3_initialize error\n");
return -1;
}
//打开数据库,(存在打开,不存在创建)成功返回1
res = 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,小红改为1
init_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;
}
运行结果: