博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
sqlite3笔记
阅读量:484 次
发布时间:2019-03-07

本文共 8011 字,大约阅读时间需要 26 分钟。

本地安装

sudo dpkg -i *.deb

测试是否安装成功

输入命令sqlite3若出现sqlite版本等信息就证明安装成功。

在线安装

必须能连接互联网

sudo apt-get install sqlite3

创建数据库

创建一个名为student的数据库

sqlite3 student.db

数据库基本命令

1、系统命令

.help //帮助文档.quit //退出.exit //退出.table //查看数据库表.databases //查看打开的数据库

2、sql命令

2.0 创建数据库

sqlite3 student.db

2.1创建一个表

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 — 字段的类型

2.2 向表插入数据

insert into stu values(100, 'zhangsan', 80); //完整插入insert into stu (name, score)values("ares", 100); //特定字段插入

2.3 查看表内容

select * from stu; //查看表所有内容select name from stu; //查询部分字段的内容select * from stu where score=80;  //查询score字段为80的内容

where后面为查找条件,还可以多个条件的逻辑与或逻辑或进行查询

2.4 删除记录

delete from stu;//删除整个表delete from stu where name="zhangsan"; //删除特定的一条记录

2.5 修改字段内容

update stu set name='cainiao' where id=1002;

2.6 插入一列

alter table stu add column address char //向表stu插入一列字段address

2.7 删除一列

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

基于sqlite3数据库的简单实例

#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/

你可能感兴趣的文章