sqlite3数据库
系统命令
以.
开头的命令是系统命令
- .help 帮助
- .exit 退出
- .quit 退出
- .schema 查看表的结构图
- .databases 查看打开的数据库
- .table 查看该数据库拥有的表格
sql命令
创建表命令
create table stu(id Integer,name char,score Integer);
- create 创建命令
- table 创建表格
- stu 表格的名字
- id Integer stu表格成员属性 id为元素 Integer为整型
- name char stu表格成员属性 name为元素 char为字符(串)类型
- score Integer stu表格成员属性 score为元素 Integer为字符(串)类型
基本的sql命令不以.开头但是必须以分号结尾
插入命令
整体插入
insert into stu values(1001,"zhangsan",80);
insert into stu values(1001,'lisi',80);
部分插入
insert into stu (name,score)values(1003,"wangwu");
查看命令
查看全部字段
select * from stu;
查看部分字段
select name,socre from stu;
按指定条件查看
select * from stu where score=80;
select * from stu where score=80 and name="zhansan";
select * from stu where score=80 or name="lisi";
删除命令
delete from stu where name="wangwu";
`sqlite不支持删除一例列
-
创建一张新的表
-
create table stu1 as select id,name,score from stu;
-
删除原有的表
-
drop table stu;
-
将新的表名字改为原有的旧表的名字
-
alter table stu1 rename to stu;
更新记录
update stu set name="wangwu" where id=1001;
update stu set name="wangwu",score=88 where id=1001;
插入一例
alter table stu add columu address char;
删除表
drop table stu;
修改表格的名字
alter table stu1 rename to stu;
数据库API操作
打开sqlite数据库
int sqlite3_open(char *path,sqlite3 **db);
参数:
- path 数据库路径
- db 指向sqlite句柄的指针(二级指针)
- 返回值 成功返回0 失败返回错误码(非零值)
关闭数据库
int sqlite3_close(sqlite3 *db);
返回值
- 成功返回0
- 失败返回错误码
返回错误信息
const char * sqlite3_errmg(sqlite3 * db);
返回值
- 错误信息的首地址
执行一条sql语句
int sqlite3_exec(
sqlite3*db, /* An open database */
const char *sql, /* SQL to be evaluated */
int (*callback)(void*,int,char**,char**),/* Callback function */
void * arg, /* 1st argument to callback */
char **errmsg /* Error msg written here */
);
参数:
- db 数据库操作句柄
- sql 一条sql语句
- callback 回调函数,只有sql为查询语句的时候,才会执行此语句
- arg 表示的是给回调函数传递参数
- errmsg 错误消息
返回值
- SQLITE_OK
使用回调函数执行SQL语句
int (*callback)(void * para,int f_num,char** f_value,char** f_name)
- 功能:每找到一条记录自动执行一次回调函数
- para:传递给回调函数的参数
- f_num:记录中包含的字段数目
- f_value:包含每个字段值的指针数组
- f_name:包含每个字段名称的指针数组
- 返回值:成功返回0,失败返回-1
不使用回调函数执行SQL语句
int sqlite3_get_table(sqlite3 * db, const char *sql, char ***resultp, int * nrow, int * ncolumn, char ** errmsg);
- 功能:执行SQL操作
- db:数据库句柄
- sql:SQL语句
- resultp:用来指向sql执行结果的指针
- nrow:满足条件的记录的数目
- ncolumn:每条记录包含的字段数目
- errmsg:错误信息指针的地址
- 返回值:成功返回0,失败返回错误码
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
#define DATABASE "student.db"
#define N 128
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[])
{
sqlite3 *db;
char *errmsg;
int n;
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 , sex char , score int);",
NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
}
else
{
printf("Create or open table success.\n");
}
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;
}
项目:学生管理系统
#include<stdio.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
#include <time.h>
#define DATABASE "fruit.db"
#define N 128
int insert(sqlite3 *db,char *buf);
int update_weight(sqlite3 *db,char *buf);
int update_price(sqlite3 *db);
int query(sqlite3 *db);
int callback(void *arg, int f_num, char ** f_value, char ** f_name);
int main(int argc, const char *argv[])
{
sqlite3 *db;
char *errmsg;
int n;
char buf[128] = {};
time_t ctime;
struct tm *t_time;
if(sqlite3_open(DATABASE, &db) != SQLITE_OK)
{
printf("%s\n", sqlite3_errmsg(db));
return -1;
}
else
{
printf("open success.\n");
}
if(sqlite3_exec(db,"create table if not exists fruit (id integer primary key autoincrement,name char,weight float,price float,time char);",NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
}
else
{
printf("Create or open table success.\n");
}
ctime = time(NULL);
t_time = localtime(&ctime);
sprintf(buf,"%s\n",asctime(t_time));
printf("%s",buf);
while(1)
{
puts("-------------------------预览列表-------------------------");
query(db);
puts("----------------------------------------------------------");
puts("");
printf("***************功能选项********************\n");
printf("1: insert 2:query 3:trade 4:update 5:quit\n");
printf("********************************************\n");
printf("Please select:");
scanf("%d", &n);
switch(n)
{
case 1:
insert(db,buf);//进货
break;
case 2:
query(db);//查询
break;
case 3:
update_weight(db,buf);//更新重量
break;
case 4:
update_price(db);//更新价格
break;
case 5:
printf("quit!\n");
sqlite3_close(db);
exit(0);
break;
default :
printf("Invalid.\n");
}
system("clear");
}
return 0;
}
int insert(sqlite3 *db,char *buf)
{
char name[30] = {};
float weight;
float price;
char sql[N] = {};
char *errmsg;
printf("Input fruit name:");
scanf("%s", name);
getchar();
printf("Input weight:");
scanf("%f", &weight);
printf("Input price:");
scanf("%f", &price);
sprintf(sql, "insert into fruit (name,weight,price,time)values('%s', '%.3f', '%.3f','%s')", name, weight, price,buf);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
}
else
{
printf("Insert done.\n");
}
return 0;
}
int update_weight(sqlite3 *db,char *buf)
{
int id;
char sql[N] = {};
char *errmsg;
float weight;
printf("Input id:");
scanf("%d", &id);
printf("Input weight:");
scanf("%f", &weight);
sprintf(sql, "update fruit set weight='%f',time='%s' where id=%d",weight,buf,id);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
}
else
{
printf("Delete done.\n");
}
return 0;
}
int update_price(sqlite3 *db)
{
float price;
int id;
char sql[N] = {};
char *errmsg;
printf("Input id:");
scanf("%d", &id);
printf("Input alter price:");
scanf("%f", &price);
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 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", f_value[i]);
}
return 0;
}
int query(sqlite3 *db)
{
char *errmsg;
if(sqlite3_exec(db,"select * from fruit", callback,NULL , &errmsg) != SQLITE_OK)
{
printf("%s", errmsg);
}
else
{
printf("select done.\n");
}
return 0;
}