SQLite C编程

1、案例一：获取SQLite的版本

 #include <sqlite3.h>
#include <stdio.h>
 
int main(void) 
{
    printf("%s\n", sqlite3_libversion()); 
    
    return 0;
}

在Linux环境下需要：

1、安装sqlite3: sudo apt -get install sqlite3

2、安装sqlite3的编译工具包： sudo apt -get install libsqlite3 -dev

3、创建test.c，编写上面代码，然后 gcc test.c -o test -lsqlite3

2、案例二：使用SQL的查询功能获取SQLite的版本

 #include <sqlite3.h>
#include <stdio.h>
 
int main(void) 
{
    sqlite3 *db;
    sqlite3_stmt *pStmt;
 
    // 打开数据库
    if (sqlite3_open(":memory:", &db) != SQLITE_OK) {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }
 
    // 编译 SQL 语句
    if (sqlite3_prepare_v2(db, "SELECT SQLITE_VERSION()", -1, &pStmt, 0) != SQLITE_OK) {
        fprintf(stderr, "Failed to fetch data: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }
 
    // 执行 SQL 语句
    if (sqlite3_step(pStmt) == SQLITE_ROW) 
        printf("%s\n", sqlite3_column_text(pStmt, 0));
    
    // 释放 SQL 语句句柄并关闭数据库
    sqlite3_finalize(pStmt);
    sqlite3_close(db);
    
    return 0;
}

xuanmiao@linux:~/Demo/SQLite$ gcc test1.c -o test1 -lsqlite3
xuanmiao@linux:~/Demo/SQLite$ ls
test1 test1.c
xuanmiao@linux:~/Demo/SQLite$ ./test1
3.31.1

3、案例三：创建表并插入数据

 #include <sqlite3.h>
#include <stdio.h>
 
int main(void) 
{
    
    sqlite3 *db;
    char *err_msg = 0;
    
    // 打开数据库
    if (sqlite3_open("test.db", &db) != SQLITE_OK) {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }
    
    // 设置 SQL 语句字符串
    char *sql = "DROP TABLE IF EXISTS Cars;" 
                "CREATE TABLE Cars(Id INT, Name TEXT, Price INT);" 
                "INSERT INTO Cars VALUES(1, 'Audi', 52642);" 
                "INSERT INTO Cars VALUES(2, 'Mercedes', 57127);" 
                "INSERT INTO Cars VALUES(3, 'Skoda', 9000);" 
                "INSERT INTO Cars VALUES(4, 'Volvo', 29000);" 
                "INSERT INTO Cars VALUES(5, 'Bentley', 350000);" 
                "INSERT INTO Cars VALUES(6, 'Citroen', 21000);" 
                "INSERT INTO Cars VALUES(7, 'Hummer', 41400);" 
                "INSERT INTO Cars VALUES(8, 'Volkswagen', 21600);";
 
    // 编译并执行 SQL 语句字符串
    if (sqlite3_exec(db, sql, 0, 0, &err_msg) != SQLITE_OK ) {
        fprintf(stderr, "SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);        
        sqlite3_close(db);
        return 1;
    }
    
    sqlite3_close(db);
 
    return 0;
}

xuanmiao@linux:~/Demo/SQLite $ls test1 test1.c xuanmiao@linux:~/Demo/SQLite$ ./test1
xuanmiao@linux:~/Demo/SQLite$ ls
test1 test1.c test.db

xuanmiao@linux:~/Demo/SQLite$ sqlite3 test.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite>
sqlite> .headers on
sqlite> .mode column
sqlite> .tables
Cars
sqlite> SELECT * FROM Cars;
Id Name Price
---------- ---------- ----------
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600