SQLite3数据库的介绍和使用教程(面向业务编程-数据库)

目录

• SQLite3数据库的介绍和使用（面向业务编程-数据库）
• SQLite3介绍
• 获取SQLite3源码
• 编写CMake工程
• main.cpp
• 编译
• 使用SQL
• 安装一个SQLite3
• 建表
• 插入行
• 查找数据
• 删除一项数据
• 更新字段
• SQL总结
• C语言编程
• 打开和关闭一个数据库文件
• 打开一个数据库文件
• 关闭一个数据库句柄
• 执行SQLite语句
• 不带回调
• 带回调
• sqlite3_get_table
• SQLitecpp
• 源文件
• 编写CMake工程
• 总结

wget https://www.sqlite.org/2023/sqlite-autoconf-3410200.tar.gztar -zxvf sqlite-autoconf-3410200

cp sqlite-autoconf-3410200/*.c lib/sqlite3/cp sqlite-autoconf-3410200/*.h lib/sqlite3/rm -r sqlite-autoconf-3410200

├── build├── CMakeLists.txt├── main.cpp└── lib    └── sqlite3        ├── shell.c        ├── sqlite3.c        ├── sqlite3ext.h        ├── sqlite3.h        └── sqlite3rc.h

cmake_minimum_required(VERSION 3.0 FATAL_ERROR)project(useSQLite LANGUAGES C CXX)set(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS} -std=c++11 -pthread")add_executable(useSQLite main.cpp)# lib sqlite3include_directories(lib/sqlite3)add_library(sqlite3    STATIC    lib/sqlite3/sqlite3.c)target_link_libraries(sqlite3 dl)#target_link_libraries(useSQLite sqlite3)# sqlite3-cliadd_executable(SQLite3-cli lib/sqlite3/shell.c)target_link_libraries(SQLite3-cli sqlite3)

#include <iostream>#include <stdio.h>#include <sqlite3.h>static int callback(void *NotUsed, int argc, char **argv, char **azColName) {    int i;    for (i = 0; i < argc; i++) {        printf("%s = %sn", azColName[i], argv[i] ? argv[i] : "NULL");    }    printf("n");    return 0;}int main(int argc, char **argv) {    sqlite3 *db;    char *zErrMsg = 0;    int rc;    if (argc != 3) {        fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENTn", argv[0]);        return(1);    }    rc = sqlite3_open(argv[1], &db);    if (rc) {        fprintf(stderr, "Can't open database: %sn", sqlite3_errmsg(db));        sqlite3_close(db);        return(1);    }    rc = sqlite3_exec(db, argv[2], callback, 0, &zErrMsg);    if (rc!=SQLITE_OK) {        fprintf(stderr, "SQL error: %sn", zErrMsg);        sqlite3_free(zErrMsg);    }    sqlite3_close(db);    return 0;}

mkdir build && cd buildcmake .. && make

sudo apt install sqlite3

sqlite3 mydatabase.db

CREATE TABLE IF NOT EXISTS mytable (id integer primary key,name text);

CREATE TABLE yourtable (id integer primary key, name text, age integer);

INSERT INTO mytable (id,name) values (1000, "zhangsan");

INSERT INTO mytable (id, name) values (1000, "lisi");Runtime error: UNIQUE constraint failed: mytable.id (19)

SELECT * FROM mytable;

SELECT * FROM mytable WHERE id > 1000;

1000|zhangsan1001|lisi1002|wangwu

1001|lisi1002|wangwu

DELETE FROM mytable WHERE id=1001; 

DELETE FROM mytable;

DELETE FROM yourtable WHERE id<1000 AND age>35;

UPDATE mytable SET name="zhansang" WHERE name="zhangsan";

int opendatabase(sqlite3** db, const char* dbfilename) {    char *zErrMsg = 0;    int rc;    rc = sqlite3_open(dbfilename, db);    if(SQLITE_OK != rc) {        fprintf(stderr, "Can't open database: %sn", sqlite3_errmsg(*db));        sqlite3_close(*db);        return -1;    }    return 0;}

int clsoedb(sqlite3** db) {    if(NULL != *db) {        sqlite3_close(*db);        return 0;    }    return -1;}

SQLITE_API int sqlite3_exec(  sqlite3 *db,                /* The database on which the SQL executes */  const char *zSql,           /* The SQL to be executed */  sqlite3_callback xCallback, /* Invoke this callback routine */  void *pArg,                 /* First argument to xCallback() */  char **pzErrMsg             /* Write error messages here */){

//创建表int create_table(sqlite3 *pdb) {    char *sql = NULL;    char *errmsg = NULL;    int ret;    sql = (char*)"create table if not exists mytable (id integer primary key,name text);";    ret = sqlite3_exec(pdb, sql, NULL, NULL, &errmsg);    if(SQLITE_OK != ret) {        printf("create table error! %sn", errmsg);        return -1;    } else {        return 0;    }}

// callbackint show_row(void *return_, int column, char* result[], char** column_name) {    for(int i = 0; i < column; i++) {        printf("%st", result[i]);    }    printf("n");    return 0;}// 查询和显示所有mytable的数据int query_all_and_show(sqlite3* pdb) {    char sql[24] = {0};    char *errmsg = NULL;    int ret;    strncpy(sql, "select * from mytable;", 22);    // 数据库，语句字符串，回调函数，用户输入的参数，最终传给回调函数使用，错误信息    ret = sqlite3_exec(pdb, sql, show_row, NULL, &errmsg);    if(SQLITE_OK != ret) {        printf("select exec error: %sn", errmsg);        return -1;    }    return 0;}

SQLITE_API int sqlite3_get_table(  sqlite3 *db,                /* The database on which the SQL executes */  const char *zSql,           /* The SQL to be executed */  char ***pazResult,          /* Write the result table here */  int *pnRow,                 /* Write the number of rows in the result here */  int *pnColumn,              /* Write the number of columns of result here */  char **pzErrMsg             /* Write error messages here */){

for(int i = 0; i < Col; i++) {    for(int j = 0; j < Row; j++) {       printf("%st", azResult[i*Row+j]);    }    printf("n");}

mkdir useSQLiteCpp && cd useSQLiteCppgit clone https://github.com/SRombauts/SQLiteCpp.gitcd SQLiteCppgit submodule initgit submodule update

#include <iostream>#include "SQLiteCpp/SQLiteCpp.h"// https://www.cnblogs.com/nbtech/p/use_sqlite_library.htmlint main() {    try    {        // Open a database file in create/write mode(用写模式打开一个数据库文件)        SQLite::Database    db("test.db3", SQLite::OPEN_READWRITE|SQLite::OPEN_CREATE);        std::cout << "SQLite database file '" << db.getFilename().c_str() << "' opened successfullyn";        // Create a new table with an explicit "id" column aliasing the underlying rowid（创建一个表，id设置为主键）        db.exec("DROP TABLE IF EXISTS test");        db.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)");        // first row（插入一行，id是NULL就是不指定，不指定会从1开始分配，value是test）        int nb = db.exec("INSERT INTO test VALUES (NULL, "test")");        std::cout << "INSERT INTO test VALUES (NULL, "test")", returned " << nb << std::endl;        // second row（插入第二行，id是2，根据上一条记录加一，value是second）        nb = db.exec("INSERT INTO test VALUES (NULL, "second")");        std::cout << "INSERT INTO test VALUES (NULL, "second")", returned " << nb << std::endl;        // update the second row（将id为2的行的value值更新为second-updated）        nb = db.exec("UPDATE test SET value="second-updated" WHERE id='2'");        std::cout << "UPDATE test SET value="second-updated" WHERE id='2', returned " << nb << std::endl;        // Check the results : expect two row of result（读取结果，应该会有两行数据。其实就是查表）        SQLite::Statement   query(db, "SELECT * FROM test");        std::cout << "SELECT * FROM test :n";        while (query.executeStep())        {            std::cout << "row (" << query.getColumn(0) << ", "" << query.getColumn(1) << "")n";        }        db.exec("DROP TABLE test"); // 删除test这个表    }    catch (std::exception& e)    { // 异常处理        std::cout << "SQLite exception: " << e.what() << std::endl;        return EXIT_FAILURE; // unexpected error : exit the example program    }    // remove("test.db3"); // 删除文件    return 0;}

cmake_minimum_required(VERSION 3.0 FATAL_ERROR)project(useSQLiteCpp LANGUAGES CXX)add_executable(useSQLiteCpp main.cpp)# SQLiteCppinclude_directories(SQLiteCpp/include)option(SQLITECPP_RUN_CPPLINT "Not Run cpplint.py tool for Google C++ StyleGuide." OFF)add_subdirectory(SQLiteCpp lib)target_link_libraries(useSQLiteCpp SQLiteCpp)