Home > News list > Programming/Database >> Database Operation Tutorial

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

Database Operation Tutorial 2023-05-12 12:13:39 Source: Network

SQLite3-SQLite3SQLite3CMakemain.cppSQLSQLite3SQLCSQLitesqlite3_get_tableSQLitecppCMakeSQLite3-SQLite3SQLiteCSQLhttps://www

SQLite3-

SQLite3

SQLiteCSQL

https://www.sqlite.org/index.html

SQLite

SQLite3

SQLite3https://www.sqlite.org/download.html

sqlite-autoconf-3410200.tar.gz

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

teaTcl Extension Architecture

ch

lib/sqlite3

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

sqlite3.c8.3M

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

CMake

shell.c

https://www.sqlite.org/howtocompile.html

CMakeLists.txt

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)

-ldlhttps://stackoverflow.com/questions/20131138/cmake-add-ldl-at-end-of-link-stage-of-add-library

main.cpp

main.cpp

#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;}

Usage: %s DATABASE SQL-STATEMENTnSQL

sqlite3_open()sqlite3_exec()sqlite3_close()

SQLiteSQLite

SQLite

mkdir build && cd buildcmake .. && make

useSQLiteSQLite3-cli

useSQLitemain.cpp

SQLite3-cliSQL

UbuntuSQLite3

SQL

https://www.cnblogs.com/nbtech/p/use_sqlite_library.html

SQLiteSQLSQLiteSQL

SQLiteSQLSQLSQLiteSQL

SQL

SQLite3

UbuntuSQLite3

sudo apt install sqlite3

sqlite3 mydatabase.db

mydatabase.dbSQL

shell.cSQLite3-cli

SQLNoSQLSQLite

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

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

primary key

;SQLSQLite

idnameidinteger012

nametext"zhangsan" ""

SQLiteunique

name text name text unique

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

zhangsan1000

INSERT INTOmytablemytable

id1000name"zhangsan"

idid

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

SELECT * FROM mytable;

*FROMmytableSELECT

mytable

WHEREid1000

SELECT * FROM mytable WHERE id > 1000;

1000|zhangsan1001|lisi1002|wangwu

1001|lisi1002|wangwu

DELETE

DELETE FROM mytable WHERE id=1001; 

id1001WHEREDELETEWHERE

WHERE

DELETE FROM mytable;

AND

age35id1000

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

id1000"zhangsan""zhansang"

id1000id1000name"zhansang"

WHEREUPDATE

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

SETid<1000SETname="zhansang"id1000name"zhansang"

SQL

SQL

SQLSQL

SQLSQL

C

CMakeSQLiteC

main.cpp3Sqlite3sqlite3_opensqlite3_execsqlite3_close

sqlite3_openxxx.db

sqlite3_execSQL

sqlite3_close

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;}

mydatabase.dbdb

sqlite3_close(sqlite3* db);

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

SQLite

https://www.cnblogs.com/nbtech/p/use_sqlite_library.html

SQLitesqlite3_execC

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 */){

SQLiteSQL

//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;    }}

sqlSQLxCallbackpArgNULL

// 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;}

mytable

pArgvector

sqlite3_get_table

sqlite3_exec()SQLite3

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 */){

SQL

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

SQLitecpp

SQLiteC++C++

CC++C++

C

SQLiteC++SQLiteC++https://github.com/SRombauts/SQLiteCpp

SQLiteC++

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

examples/example2/srcmain.cppSQLiteC++useSQLiteCppmain.cpp

#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;}

1dbSQLite::Database db("test.db3", SQLite::OPEN_READWRITE|SQLite::OPEN_CREATE);

2execSQLdb.exec(const char* );

3SQLite::Statement query(db, "SELECT * FROM test");

4

main.cpp

CMake

CMakeSQLiteC++CMake

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)

SQLite3

SQLite3

SQLite3SQLCSQLite3

but, not yet

SQLite3

SQLite3-,SQLite3

Tag: Database Introduction and Usage Tutorial of SQLite3 Business Oriented


Disclaimer: The content of this article is sourced from the internet. The copyright of the text, images, and other materials belongs to the original author. The platform reprints the materials for the purpose of conveying more information. The content of the article is for reference and learning only, and should not be used for commercial purposes. If it infringes on your legitimate rights and interests, please contact us promptly and we will handle it as soon as possible! We respect copyright and are committed to protecting it. Thank you for sharing.

AdminSo

http://www.adminso.com

Copyright @ 2007~2024 All Rights Reserved.

Powered By AdminSo

Open your phone and scan the QR code on it to open the mobile version


Scan WeChat QR code

Follow us for more hot news

AdminSo Technical Support