Introduction and Usage Tutorial of SQLite3 Database (Business Oriented Programming - Database)
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.
