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

wget https://www.sqlite.org/2023/sqlite-autoconf-3410200.tar.gz
tar -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 sqlite3
include_directories(lib/sqlite3)
add_library(sqlite3
    STATIC
    lib/sqlite3/sqlite3.c)
target_link_libraries(sqlite3 dl)
#
target_link_libraries(useSQLite sqlite3)
# sqlite3-cli
add_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 = %s\n", 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-STATEMENT\n", argv[0]);
        return(1);
    }
    rc = sqlite3_open(argv[1], &db);
    if (rc) {
        fprintf(stderr, "Can't open database: %s\n", 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: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    sqlite3_close(db);
    return 0;
}

mkdir build && cd build
cmake .. && 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|zhangsan
1001|lisi
1002|wangwu

1001|lisi
1002|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: %s\n", 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! %s\n", errmsg);
        return -1;
    } else {
        return 0;
    }
}

// callback
int show_row(void *return_, int column, char* result[], char** column_name) {
    for(int i = 0; i < column; i++) {
        printf("%s\t", 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: %s\n", 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("%s\t", azResult[i*Row+j]);
    }
    printf("\n");
}

mkdir useSQLiteCpp && cd useSQLiteCpp
git clone https://github.com/SRombauts/SQLiteCpp.git
cd SQLiteCpp
git submodule init
git submodule update

#include <iostream>
#include "SQLiteCpp/SQLiteCpp.h"
// https://www.cnblogs.com/nbtech/p/use_sqlite_library.html
int 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 successfully\n";
        // 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)
# SQLiteCpp
include_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)