Introduction and Usage Tutorial of SQLite3 Database (Business Oriented Programming - Database)

• 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;}// mytableint 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 rowidid        db.exec("DROP TABLE IF EXISTS test");        db.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)");        // first rowidNULL1valuetest        int nb = db.exec("INSERT INTO test VALUES (NULL, "test")");        std::cout << "INSERT INTO test VALUES (NULL, "test")", returned " << nb << std::endl;        // second rowid2valuesecond        nb = db.exec("INSERT INTO test VALUES (NULL, "second")");        std::cout << "INSERT INTO test VALUES (NULL, "second")", returned " << nb << std::endl;        // update the second rowid2valuesecond-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)