本文共 8011 字,大约阅读时间需要 26 分钟。
sudo dpkg -i *.deb
输入命令sqlite3若出现sqlite版本等信息就证明安装成功。
必须能连接互联网
sudo apt-get install sqlite3
创建一个名为student的数据库
sqlite3 student.db
.help //帮助文档.quit //退出.exit //退出.table //查看数据库表.databases //查看打开的数据库
sqlite3 student.db
create table stu(id integetr, name char, score interger); //创建一个表,表已经存在则创建失败create table if not exists student(id integetr, name char, score interger); //创建一个表如果该表不存在的话
stu — 表的名字
id、name、score — 字段名字
interger、char — 字段的类型
insert into stu values(100, 'zhangsan', 80); //完整插入insert into stu (name, score)values("ares", 100); //特定字段插入
select * from stu; //查看表所有内容select name from stu; //查询部分字段的内容select * from stu where score=80; //查询score字段为80的内容
where后面为查找条件,还可以多个条件的逻辑与或逻辑或进行查询
delete from stu;//删除整个表delete from stu where name="zhangsan"; //删除特定的一条记录
update stu set name='cainiao' where id=1002;
alter table stu add column address char //向表stu插入一列字段address
sqlite3无法直接删除一列,所以需要一些特殊操作。
1、创建一张新的表
create table stdu1 as select id, name, score from stu; //创建新表stud1,并从stu中复制id,name,score字段插入到新表stu1中
2、删除旧的表
drop table stu; //stu表的名字 删除就的表
3、将新的表重命名位旧表的名字
alter table stu1 rename to stu; //将新表stu1命名位stu
代码中使用sqlite3要安装sqlite3函数库:
sudo apt-get install libsqlite3-dev
#include#include #include "stdint.h"#include "stdlib.h"#define ARR_SIZE(a) (sizeof(a)/sizeof(a[0]))#define DATBASE_PATH "student.db"#define TABLE_NAME "student"enum OpItem { ADD_INFO = 0x01, DEL_INFO = 0x02, SHOW_INFO = 0x03, MODIF_INFO = 0x04, DEL_TABLE = 0x05, EXIT_SYS = 0x06,};struct option { int op; void (*op_callback)(void);};struct student{ int id; int score; char name[32];};sqlite3 *db;int insert_student_tbl(char *tblname, int id, char *name, int score){ int ret; char **err_msg; char sql[512] = { 0}; sprintf(sql, "insert into %s values(%d, \"%s\", %d);", tblname, id, name, score); //执行sql语句 ret = sqlite3_exec(db, sql, NULL, 0, NULL); if (ret != SQLITE_OK) { fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db)); } else { fprintf(stdout, "insert successfully\n"); } return ret;}int delete_student_by_name(char *tblname, char *name){ int ret; char **err_msg; char sql[512] = { 0}; sprintf(sql, "delete from %s where name=\"%s\";", tblname, name); ret = sqlite3_exec(db, sql, NULL, 0, NULL); if (ret != SQLITE_OK) { fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db)); } else { fprintf(stdout, "delete successfully\n"); } return ret;}int delete_student_by_id(char *tblname, int id){ int ret; char **err_msg; char sql[512] = { 0}; sprintf(sql, "delete from %s where id=%d;", tblname, id); ret = sqlite3_exec(db, sql, NULL, 0, NULL); if (ret != SQLITE_OK) { fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db)); } else { fprintf(stdout, "delete successfully\n"); } return ret;}static int callback(void *NotUsed, int column_Count, char **column_Val, char **column_name){ int i; printf("argc %d\r\n", column_Count); for (i = 0; i < (column_Count); i++) { printf(" %s : %s ", column_name[i], column_Val[i] ? column_Val[i] : "NULL"); } printf("\n"); return 0;}int delete_student_tbl(char *tblname){ int ret; char sql[512] = { 0}; sprintf(sql, "delete from %s", tblname); ret = sqlite3_exec(db, sql, NULL, 0, NULL); if (ret != SQLITE_OK) { fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db)); } else { fprintf(stdout, "delete table successfully\n"); } return ret;}int get_op(void){ int op; scanf("%d", &op); return op;}int show_all_student(char *tblname){ int ret; char sql[512] = { 0}; sprintf(sql, "select * from %s", tblname); ret = sqlite3_exec(db, sql, callback, 0, NULL); if (ret != SQLITE_OK) { fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db)); } else { fprintf(stdout, "select successfully\n"); } return ret;}void add_student_info_cb(void){ struct student s; printf("请输入学生名字:"); scanf("%s", s.name); printf("请输入学生学号:"); scanf("%d", &s.id); printf("请输入学生成绩:"); scanf("%d", &s.score); insert_student_tbl(TABLE_NAME, s.id, s.name, s.score);}void del_menu_init(void){ printf("**************1、选择通过姓名删除*****************\n"); printf("**************2、选择通过学号删除*****************\n");}void del_student_info_cb(void){ char name[32]; int id; del_menu_init(); int op = get_op(); if (op > 2) { printf("无效操作\n"); return; } switch(op) { case 1 : { printf("输入删除学生的名字: \n"); scanf("%s", name); delete_student_by_name(TABLE_NAME, name); break; } case 2 : { printf("输入删除学生的学号:\n"); scanf("%s", name); id = atoi(name); delete_student_by_id(TABLE_NAME, id); break; } }}void show_student_info_cb(void){ show_all_student(TABLE_NAME);}void modify_student_info_cb(void){ }void exit_student_sys_cb(void){ exit(0); sqlite3_close(db);}void del_student_table_cb(void){ delete_student_tbl(TABLE_NAME); sqlite3_close(db); exit(0);}struct option options[] = { { .op = ADD_INFO, .op_callback = add_student_info_cb}, { .op = DEL_INFO, .op_callback = del_student_info_cb}, { .op = SHOW_INFO, .op_callback = show_student_info_cb}, { .op = MODIF_INFO, .op_callback = modify_student_info_cb}, { .op = DEL_TABLE, .op_callback = del_student_table_cb}, { .op = EXIT_SYS, .op_callback = exit_student_sys_cb}, };void optiong_handle(struct option *ops, uint32_t size, int op){ int index = 0; for (index = 0; index < size; index++) { if (ops[index].op == op) { if (ops[index].op_callback) { ops[index].op_callback(); } break; } }}/* * @brief: 创建表 * @param[in]: None * @retval[out]: None * @note: * @author: guangjieMVP * @github: https://github.com/guangjieMVP*/int creat_student_tbl(char *tblname){ int ret; char **err_msg; char sql[512] = { 0}; sprintf(sql, "create table if not exists %s(id integetr, name char, score interger, weigth interger);", tblname); ret = sqlite3_exec(db, sql, NULL, 0, NULL); if (ret != SQLITE_OK) { fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db)); } else { fprintf(stdout, "Table created successfully\n"); } return ret;}/* * @brief: 初始化数据库 * @param[in]: None * @retval[out]: None * @note: * @author: guangjieMVP * @github: https://github.com/guangjieMVP*/void student_database_init(){ int ret; ret = sqlite3_open(DATBASE_PATH, &db); //打开数据库,不存在则创建 if (ret) { fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); } else { fprintf(stderr, "Opened database successfully\n"); creat_student_tbl(TABLE_NAME); }}void memu_init(void){ printf("**************1、添加学生信息*****************\n"); printf("**************2、删除学生信息*****************\n"); printf("**************3、显示学生信息*****************\n"); printf("**************4、修改学生信息*****************\n"); printf("**************5、删除所有学生信息**************\n"); printf("**************6、退出系统*********************\n");}int main(int argc, char **argv){ student_database_init(); while (1) { memu_init(); int op = get_op(); optiong_handle(options, ARR_SIZE(options), op); } return 0;}
编译:
gcc student_manager.c -o student_manager -lsqlite3
转载地址:http://lozcz.baihongyu.com/