柚子快報邀請碼778899分享：數(shù)據(jù)庫 sqlite基本操作

http://yzkb.51969.com/

簡介

文章目錄

簡介1.數(shù)據(jù)庫的安裝2.數(shù)據(jù)庫命令：API，創(chuàng)建表單代碼

csprintf（）getchar和scanf（）

1.數(shù)據(jù)庫的安裝

sudo dpkg -i *.deb

這個報錯表明出現(xiàn)依賴問題 用這個命令后再試試sudo apt --fix-broken install

2.數(shù)據(jù)庫命令：

1）系統(tǒng)命令 ， 都以'.'開頭

.exit

.quit

.table 查看表

.schema 查看表的結構

sql語句， 都以‘;’結尾

1-- 創(chuàng)建一張表

create table stuinfo(id integer, name text, age integer, score float);

2-- 插入一條記錄

insert into stuinfo values(1001, 'zhangsan', 18, 80);

insert into stuinfo (id, name, score) values(1002, 'lisi', 90);

3-- 查看數(shù)據(jù)庫記錄

select * from stuinfo;

select * from stuinfo where score = 80;

select * from stuinfo where score = 80 and name= 'zhangsan';

select * from stuinfo where score = 80 or name='wangwu';

select name,score from stuinfo; 查詢指定的字段

select * from stuinfo where score >= 85 and score < 90;

4-- 刪除一條記錄

delete from stuinfo where id=1003 and name='zhangsan';

5-- 更新一條記錄

update stuinfo set age=20 where id=1003;

update stuinfo set age=30, score = 82 where id=1003;

6-- 刪除一張表

drop table stuinfo;

7-- 增加一列

alter table stuinfo add column sex char;

8-- 刪除一列

create table stu as select id, name, score from stuinfo;

drop table stuinfo;

alter table stu rename to stuinfo;

API，創(chuàng)建表單

代碼

用c來調(diào)用sqlite 功能指令明細

#include

#include

#include

#include

#define DATABASE "student.db"

#define N 128

//1、2、3、4、5具體操作的實現(xiàn)

int do_insert(sqlite3 *db)

{

int id;

char name[32] = {};

char sex;

int score;

char sql[N] = {};

char *errmsg;

printf("Input id:");

scanf("%d", &id);

printf("Input name:");

scanf("%s", name);

getchar();

printf("Input sex:");

scanf("%c", &sex);

printf("Input score:");

scanf("%d", &score);

sprintf(sql, "insert into stu values(%d, '%s', '%c', %d)", id, name, sex, score);

if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)

{

printf("%s\n", errmsg);

}

else

{

printf("Insert done.\n");

}

return 0;

}

int do_delete(sqlite3 *db)

{

int id;

char sql[N] = {};

char *errmsg;

printf("Input id:");

scanf("%d", &id);

sprintf(sql, "delete from stu where id = %d", id);

if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)

{

printf("%s\n", errmsg);

}

else

{

printf("Delete done.\n");

}

return 0;

}

int do_update(sqlite3 *db)

{

int id;

char sql[N] = {};

char name[32] = "zhangsan";

char *errmsg;

printf("Input id:");

scanf("%d", &id);

sprintf(sql, "update stu set name='%s' where id=%d", name,id);

if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)

{

printf("%s\n", errmsg);

}

else

{

printf("update done.\n");

}

return 0;

}

int callback(void *arg, int f_num, char ** f_value, char ** f_name)

{

int i = 0;

for(i = 0; i < f_num; i++)

{

// printf("%-8s %s", f_value[i], f_name[i]);

printf("%-8s", f_value[i]);

}

printf("++++++++++++++++++++++");

putchar(10);

return 0;

}

int do_query(sqlite3 *db)

{

char *errmsg;

char sql[N] = "select count(*) from stu where name='zhangsan';";

if(sqlite3_exec(db, sql, callback,NULL , &errmsg) != SQLITE_OK)

{

printf("%s", errmsg);

}

else

{

printf("select done.\n");

}

}

int do_query1(sqlite3 *db)

{

char *errmsg;

char ** resultp;

int nrow;

int ncolumn;

if(sqlite3_get_table(db, "select * from stu", &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK)

{

printf("%s\n", errmsg);

return -1;

}

else

{

printf("query done.\n");

}

int i = 0;

int j = 0;

int index = ncolumn;

for(j = 0; j < ncolumn; j++)

{

printf("%-10s ", resultp[j]);

}

putchar(10);

for(i = 0; i < nrow; i++)

{

for(j = 0; j < ncolumn; j++)

{

printf("%-10s ", resultp[index++]);

}

putchar(10);

}

return 0;

}

int main(int argc, const char *argv[])

{

//創(chuàng)建庫

sqlite3 *db;

char *errmsg;

int n;

//打開數(shù)據(jù)庫

if(sqlite3_open(DATABASE, &db) != SQLITE_OK)

{

printf("%s\n", sqlite3_errmsg(db));

return -1;

}

else

{

printf("open DATABASE success.\n");

}

//創(chuàng)建表單

if(sqlite3_exec(db, "create table if not exists stu(id int, name char , sex char , score int);",

NULL, NULL, &errmsg) != SQLITE_OK)

{

printf("%s\n", errmsg);

}

else

{

printf("Create or open table success.\n");

}

//1、2、3、4、5功能的目錄，while保持每次執(zhí)行完再選擇相應操作

while(1)

{

printf("********************************************\n");

printf("1: insert 2:query 3:delete 4:update 5:quit\n");

printf("********************************************\n");

printf("Please select:");

scanf("%d", &n);

switch(n)

{

case 1:

do_insert(db);

break;

case 2:

do_query(db);

// do_query1(db);

break;

case 3:

do_delete(db);

break;

case 4:

do_update(db);

break;

case 5:

printf("main exit.\n");

sqlite3_close(db);

exit(0);

break;

default :

printf("Invalid data n.\n");

}

}

return 0;

}

運行 gcc student.c -lsqlite ./a.out

c

sprintf（）

getchar和scanf（）

用戶回車\n 輸入的字符會暫時存在緩沖區(qū)，等待getchar和scanf拿走

scanf()遇到 \n退出，不拿走\n getchar()遇到\n退出，會拿走\n getchar（）和scanf（）

柚子快報邀請碼778899分享：數(shù)據(jù)庫 sqlite基本操作

http://yzkb.51969.com/

精彩鏈接