一、SQlite数据库安装
环境:ubuntu14.04
1、本地安装
下载安装包 执行命令 sudo dpkg -i *.deb
2、在线安装
sudo apt-get install sqlite3
二、SQlite基本命令
在终端下运行sqlite3 <.db>,出现如下提示符
<.db> 是要打开的数据库文件。若该文件不存在,则自动创建
linux@linux:~/Desktop/$ sqlite3 stu.db
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
1、基本命令
以’.'开头的命令
动作 | 命令 |
---|---|
显示所有命令 | .help |
退出sqlite3 | .quit |
显示当前打开的数据库文件 | .database |
显示数据库中所有表名 | .tables |
查看表的结构 | .schema <table_name> |
2、sql命令
不以’.‘开头,以’;'结尾
- 创建一张数据表
sqlite>create table <table_name> (f1 type1, f2 type2,…);
- 插入一条记录
sqlite>insert into <table_name> values (value1, value2,…);
insert into stu values(1001, 'zhangsan',80); -- 所有字段均插入
insert into stu (name, score)values(1003,"wangwu"); -- 部分字段插入
- 查询记录
* 查询所有字段的结果
sqlite>select * from <table_name>;
* 查询数据库中部分字段的内容
sqlite>select name from stu;
* 查询特定的内容
sqlite>select * from <table_name> where <expression>;
- 按指定条件删除表中记录
sqlite>delete from <table_name> where <expression>
不加where删除整张表;//delete from stu;
- 更新一条记录
sqlite>update <table_name> set <f1=value1>, <f2=value2>… where <expression>;
- 插入一列
sqlite>alter table <table> add column <field> <type> default …;
- 删除一列
sqlite3不支持直接删除一列
1、创建一张新的表
create table stu1 as select id, name, score from stu;
2、删除原有的表
drop table stu;
3、将新表的名字改成原有旧表的名字
alter table stu1 rename to stu;
三、SQLite编程接口
int sqlite3_open(const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);
功能:打开数据库
参数:filename 数据库名称
ppdb 代表数据库操作句柄(指针)
返回值:成功为0 SQLITE_OK ,出错 错误码
int sqlite3_close(sqlite3* db);
功能:关闭数据库
参数:db
返回值:成功为0 SQLITE_OK ,出错 错误码
const char *sqlite3_errmsg(sqlite3*db);
功能:得到错误信息的描述
int sqlite3_exec(sqlite3* db, /* An open database */ const char *sql, /* SQL to be evaluated */ int (*callback)(void*arg,int,char**,char**), /* Callback function */ void * arg, /* 1st argument to callback */ char **errmsg /* Error msg written here */ );
功能:执行一条sql语句
参数:db 数据库句柄
sql sql语句
callback 回调函数,只有在查询时,才传参
arg 为回调函数传递参数
errmsg 错误消息
返回值:成功 SQLITE_OK 失败返回错误码
查询回调函数:
int (*callback)(void* arg,int ncolumns ,char** f_value,char** f_name), /* Callback function */
功能:查询语句执行之后,会回调此函数
参数:arg 接收sqlite3_exec 传递来的参数
ncolumns 列数
f_value 列的值得地址
f_name 列的名称
返回值:0,
不使用回调函数执行SQL语句
int sqlite3_get_table(
sqlite3 *db, /* An open database */
const char *zSql, /* SQL to be evaluated */
char ***pazResult, /* Results of the query */
int *pnRow, /* Number of result rows written here */
int *pnColumn, /* Number of result columns written here */
char **pzErrmsg /* Error msg written here */
);
void sqlite3_free_table(char **result);
功能:执行SQL操作
db:数据库句柄
sql:SQL语句
resultp:用来指向sql执行结果的指针
nrow:满足条件的记录的数目
ncolumn:每条记录包含的字段数目
errmsg:错误信息指针的地址
返回值:成功返回0,失败返回错误码
四、学生成绩管理系统
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#define DATABASE "stu.db"
int do_insert(sqlite3 *db)
{
int id;
char name[32] = {};
int score;
char sql[128] = {};
char *errmsg;
printf("输入 id:");
scanf("%d",&id);
getchar();
printf("输入 name:");
scanf("%s",name);
getchar();
printf("输入 score:");
scanf("%d",&score);
getchar();
sprintf(sql,"insert into stu values(%d, '%s', %d);", id, name, 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[128] = {};
char *errmsg;
printf("输入 id:");
scanf("%d",&id);
getchar();
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[128] = {};
int score;
char *errmsg;
printf("输入 id:");
scanf("%d",&id);
getchar();
printf("更新 score:");
scanf("%d",&score);
getchar();
sprintf(sql,"update stu set score = %d where id = %d", score, 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 *socre, int f_num, char **f_value, char **f_name)
{
int i = 0;
for(i = 0; i<f_num; i++)
{
printf("%-11s", f_value[i]);
}
putchar(10);
return 0;
}
/*按指定条件查询*/
int do_query(sqlite3 *db)
{
char sql[128] = {};
char *errmsg;
int score;
printf("输入要查询的成绩\n>>>");
scanf("%d", &score);
getchar();
sprintf(sql, "select * from stu where score=%d", score);
if(sqlite3_exec(db,sql,callback,&score,&errmsg) != SQLITE_OK)
{
printf("%s\n",errmsg);
}
else
{
printf("query done.\n");
}
return 0;
}
/*查询全部*/
int do_query1(sqlite3 *db)
{
char sql[128] = {};
char *errmsg;
char ** resultp;
int nrow;
int ncloumn;
int i, j;
sprintf(sql, "select * from stu;");
if(sqlite3_get_table(db, sql,&resultp,&nrow,&ncloumn,&errmsg) != SQLITE_OK)
{
printf("%s\n",errmsg);
return -1;
}
else
{
printf("query done.\n");
}
int index = ncloumn;
for (j = 0;j < ncloumn;j++)
{
printf("%-10s",resultp[j]);
}
putchar(10);
for(i = 0; i < nrow; i++)
{
for(j = 0; j < ncloumn; j++)
{
printf("%-10s",resultp[index++]);
}
putchar(10);
}
return 0;
}
int main(int argc, const char *argv[])
{
sqlite3 *db;
char *errmsg;
int cmd;
if(sqlite3_open(DATABASE,&db) != SQLITE_OK)
{
printf("%s\n", sqlite3_errmsg(db));
return -1;
}
else
{
printf("Open DATABASE success!\n");
}
//创建一张数据库表格
if(sqlite3_exec(db, "create table if not exists stu (id int, name char ,score int)", NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n",errmsg);
}else
{
printf("create table sucess.\n");
}
while(1)
{
printf("*************************************\n");
printf("1:插入 2:删除 3:查询 4:更新 5:退出 \n");
printf("*************************************\n");
printf(">>>");
scanf("%d",&cmd);
getchar();
switch(cmd)
{
case 1:
do_insert(db);
break;
case 2:
do_delete(db);
break;
case 3:
printf("选择查询方式:1.按成绩查询 2.查询全部\n>>>");
scanf("%d",&cmd);
getchar();
switch(cmd){
case 1:
do_query(db);
break;
case 2:
do_query1(db);
break;
}
break;
case 4:
do_update(db);
break;
case 5:
sqlite3_close(db);
exit(0);
defalut:
printf("error cmd.\n");
}
}
return 0;
}
五、水果店管理系统
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <time.h>
#define DATABASE "fruit.db"
int do_insert(sqlite3 *db); //增加
int do_delete(sqlite3 *db); //删除
int do_update_pri(sqlite3 *db); //更新价格
int do_update_inv(sqlite3 *db); //更新库存
int callback(void *para, int f_num, char **f_value, char **f_name);
int do_query1(sqlite3 *db); //查询具体水果
int do_query(sqlite3 *db); //查询全部
int main(int argc, const char *argv[])
{
sqlite3 *db;
char *errmsg;
int cmd;
if(sqlite3_open (DATABASE, &db) != SQLITE_OK)
{
printf("%s\n", sqlite3_errmsg(db));
return -1;
}
else
{
printf("open DATABASE success!\n");
}
if(sqlite3_exec(db, "create table if not exists fruit (id int, kind char, price float, inventory int, time char)", NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n",errmsg);
}
else
{
printf("create table success.\n");
}
char buf[128] = {};
time_t t;
struct tm *tp;
time(&t);
tp = localtime(&t);
sprintf(buf,"%d-%02d-%02d %02d:%02d:%02d\n",
tp->tm_year+1900, tp->tm_mon+1,tp->tm_mday, tp->tm_hour, tp->tm_min, tp->tm_sec);
printf("%s",buf);
while(1)
{
// printf("==================================\n");
// do_query(db);
// printf("==================================\n");
// putchar(10);
printf("-------------------------------------------------\n");
printf("1:插入 2:删除 3:更新价格 4:更新库存 5:查询 6:退出\n");
printf("-------------------------------------------------\n");
printf("input cmd:");
scanf("%d",&cmd);
getchar();
switch(cmd)
{
case 1:
do_insert(db);
break;
case 2:
do_delete(db);
break;
case 3:
do_update_pri(db);//改价格
break;
case 4:
do_update_inv(db);//改库存
break;
case 5:
printf("查询方式选择:1.查询指定水果 2.查询全部水果\n>>>");
scanf("%d", &cmd);
getchar();
switch(cmd){
case 1:
do_query1(db);
break;
case 2:
do_query(db);
break;
}
break;
case 6:
sqlite3_close(db);
exit(0);
defalut:
printf("please input again!\n");
}
}
return 0;
}
int do_insert(sqlite3 *db)
{
int id;
char kind[32] = {};
float price;
int inventory;
char sql[128] = {};
char *errmsg;
char buf[128] = {};
time_t t;
struct tm *tp;
time(&t);
tp = localtime(&t);
sprintf(buf,"%d-%02d-%02d %02d:%02d:%02d\n",
tp->tm_year+1900, tp->tm_mon+1,tp->tm_mday, tp->tm_hour, tp->tm_min, tp->tm_sec);
printf("input id:");
scanf("%d",&id);
getchar();
printf("input kind:");
scanf("%s",kind);
getchar();
printf("input price:");
scanf("%f",&price);
getchar();
printf("input inventory:");
scanf("%d",&inventory);
getchar();
sprintf(sql, "insert into fruit values(%d,'%s', %f, %d, '%s');",id, kind, price, inventory, buf);
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[128] = {};
char *errmsg;
printf("input id:");
scanf("%d",&id);
getchar();
sprintf(sql, "delete from fruit 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_pri(sqlite3 *db)
{
int id;
float price;
char sql[128] = {};
char *errmsg;
printf("input id:");
scanf("%d",&id);
getchar();
printf("input price:");
scanf("%f",&price);
getchar();
sprintf(sql, "update fruit set price = %f where id = %d;",price, id);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
}
else
{
printf("update_pri done.\n");
}
return 0;
}
int do_update_inv(sqlite3 *db)
{
int id;
int inventory;
char sql[128] = {};
char *errmsg;
printf("input id:");
scanf("%d",&id);
getchar();
printf("input inventory:");
scanf("%d",&inventory);
getchar();
sprintf(sql, "update fruit set inventory = %d where id = %d;",inventory, id);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
}
else
{
printf("update_inv done.\n");
}
return 0;
}
int callback(void *kind, int f_num, char **f_value, char **f_name)
{
int i = 0;
for(i = 0; i<f_num; i++)
{
printf("%-11s", f_value[i]);
}
putchar(10);
return 0;
}
int do_query1(sqlite3 *db)
{
char sql[128] = {};
char *errmsg;
char kind[32] = {};
printf("输入查询的水果名字:");
scanf("%s", kind);
getchar();
sprintf(sql, "select * from fruit where kind = '%s';", kind);
if(sqlite3_exec(db,sql,callback,kind,&errmsg) != SQLITE_OK)
{
printf("%s\n",errmsg);
}
else
{
printf("query done.\n");
}
return 0;
}
int do_query(sqlite3 *db)
{
char sql[128] = {};
char *errmsg;
char ** resultp;
int nrow;
int ncloumn;
int i, j;
sprintf(sql, "select * from fruit;");
if(sqlite3_get_table(db, sql, &resultp, &nrow, &ncloumn, &errmsg) != SQLITE_OK)
{
printf("%s\n",errmsg);
return -1;
}
else
{
printf("query done.\n");
}
int index = ncloumn;
for(j = 0; j < ncloumn; j++)
{
printf("%-10s",resultp[j]);
}
putchar(10);
for(i = 0; i < nrow; i++)
{
for(j = 0; j < ncloumn; j++)
{
printf("%-10s",resultp[index++]);
}
putchar(10);
}
return 0;
}