sqlite數據庫——select查詢
2020-08-14 19:09:35
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 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;
}
執行結果：
