C/C++ SQLite 資料庫教學與範例

本篇 ShengYu 介紹 C/C++ SQLite 資料庫教學與範例,資料庫的操作無非就是 CRUD,CRUD 分別為 Create 新建資料/增加資料、Read 讀取資料/查詢資料、Update 更新資料、Delete 刪除資料,

CRUD 對應到SQL語法會像是這樣,
Create - 新建資料/增加資料,SQL語法:CREATE/INSERT
Read - 讀取資料/查詢資料,SQL語法:SELECT
Update - 更新資料,SQL語法:UPDATE
Delete - 刪除資料,SQL語法:DELETE

以上幾種都會介紹到,接下來介紹的順序會是 CREATE / INSERT / SELECT / UPDATE / DELETE

以下 C/C++ SQLite 資料庫教學與範例大概分為以下幾部份,

  • C/C++ SQLite CREATE 用法與範例
  • C/C++ SQLite INSERT 用法與範例
  • C/C++ SQLite SELECT 用法與範例
  • C/C++ SQLite UPDATE 用法與範例
  • C/C++ SQLite DELETE 用法與範例

Ubuntu sqlite3 的執行安裝指令如下,安裝完 sqlite3 後便可在命列列下執行 sqlite3 來進行會話,

1
sudo apt install sqlite3

要用程式寫 sqlite3 開發 的話需要 sqlite3 的標頭檔,可以利用 Ubuntu 安裝 libsqlite3-dev 即可,

1
sudo apt install libsqlite3-dev

以下是 C/C++ SQLite 最基本的開啟 DB 資料庫與關閉 DB 資料庫的範例,
例如我要開啟 tutorial.db 這個 database,
要使用 SQLite 的話需要引用 #include "sqlite3.h" 標頭檔,

cpp-sqlite3-tutorial-open-db.cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
// g++ cpp-sqlite3-tutorial-open-db.cpp -o a.out `pkg-config --cflags --libs sqlite3`
// g++ cpp-sqlite3-tutorial-open-db.cpp -o a.out -I./sqlite -L./ -lsqlite3 -pthread -ldl
#include <stdio.h>
#include "sqlite3.h"

int main() {
int rc;
sqlite3 *db = NULL;

rc = sqlite3_open("tutorial.db", &db);
if (rc != SQLITE_OK) {
printf("sqlite3_open failed, %s\n", sqlite3_errmsg(db));
return 1; // EXIT_FAILURE
}

rc = sqlite3_close(db);
if (rc != SQLITE_OK) {
printf("sqlite3_close failed, %s\n", sqlite3_errmsg(db));
return 1; // EXIT_FAILURE
}

db = NULL;
return 0;
}

接著用 gcc 來編譯 c 原始碼跟執行,

1
2
$ gcc cpp-sqlite3-tutorial-open-db.c -o a.out `pkg-config --cflags --libs sqlite3`
$ ./a.out

或用 g++ 來編譯 c++ 原始碼跟執行,

1
2
$ g++ cpp-sqlite3-tutorial-open-db.cpp -o a.out `pkg-config --cflags --libs sqlite3`
$ ./a.out

在 Ubuntu 可以安裝 SqliteBrowser 來看看 database 的資料,

1
sudo apt install sqlitebrowser

C/C++ SQLite CREATE 用法與範例

以下示範 C/C++ SQLite CREATE 語法新建資料表,

cpp-sqlite3-tutorial-create.cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
// g++ cpp-sqlite3-tutorial-create.cpp -o a.out `pkg-config --cflags --libs sqlite3`
// g++ cpp-sqlite3-tutorial-create.cpp -o a.out -I./sqlite -L./ -lsqlite3 -pthread -ldl
#include <stdio.h>
#include "sqlite3.h"

int main() {
int rc;
sqlite3 *db = NULL;

rc = sqlite3_open("tutorial.db", &db);
if (rc != SQLITE_OK) {
printf("sqlite3_open failed, %s\n", sqlite3_errmsg(db));
return 1; // EXIT_FAILURE
}

char sql_cmd[1024] = "CREATE TABLE movie(title, year, score)";
rc = sqlite3_exec(db, sql_cmd, NULL, NULL, NULL);
if (rc != SQLITE_OK) {
printf("sqlite3_exec failed, %s\n", sqlite3_errmsg(db));
return false;
}

rc = sqlite3_close(db);
if (rc != SQLITE_OK) {
printf("sqlite3_close failed, %s\n", sqlite3_errmsg(db));
return 1; // EXIT_FAILURE
}

db = NULL;
return 0;
}

C/C++ SQLite INSERT 用法與範例

以下示範 C/C++ SQLite INSERT 語法新增/插入資料,

cpp-sqlite3-tutorial-insert.cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
// g++ cpp-sqlite3-tutorial-insert.cpp -o a.out `pkg-config --cflags --libs sqlite3`
// g++ cpp-sqlite3-tutorial-insert.cpp -o a.out -I./sqlite -L./ -lsqlite3 -pthread -ldl
#include <stdio.h>
#include "sqlite3.h"

int main() {
int rc;
sqlite3 *db = NULL;

rc = sqlite3_open("tutorial.db", &db);
if (rc != SQLITE_OK) {
printf("sqlite3_open failed, %s\n", sqlite3_errmsg(db));
return 1; // EXIT_FAILURE
}

char sql_cmd[1024] = "INSERT INTO movie VALUES" \
"('Monty Python and the Holy Grail', 1975, 8.2)," \
"('And Now for Something Completely Different', 1971, 7.5)";
rc = sqlite3_exec(db, sql_cmd, NULL, NULL, NULL);
if (rc != SQLITE_OK) {
printf("sqlite3_exec failed, %s\n", sqlite3_errmsg(db));
return false;
}

rc = sqlite3_close(db);
if (rc != SQLITE_OK) {
printf("sqlite3_close failed, %s\n", sqlite3_errmsg(db));
return 1; // EXIT_FAILURE
}

db = NULL;
return 0;
}

以下示範 C/C++ SQLite INSERT 語法插入多筆資料,插入大量多筆資料可使用 sqlite3_prepare_v2,
在 sqlite3_prepare_v2 的 SQL 語句中可以用 ? 表示之後會帶入的資料變數,也可以用 ?NNN, :VVV, @VVV$VVV 的形式,詳細請看 sqlite 官網說明

cpp-sqlite3-tutorial-insert2.cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
// g++ cpp-sqlite3-tutorial-insert2.cpp -o a.out `pkg-config --cflags --libs sqlite3` -std=c++11
// g++ cpp-sqlite3-tutorial-insert2.cpp -o a.out -I./sqlite -L./ -lsqlite3 -pthread -ldl -std=c++11
#include <stdio.h>
#include <string>
#include <vector>
#include "sqlite3.h"

using namespace std;

struct movie {
string title;
int year;
double score;
};

int main() {
int rc;
sqlite3 *db = NULL;

rc = sqlite3_open("tutorial.db", &db);
if (rc != SQLITE_OK) {
printf("sqlite3_open failed, %s\n", sqlite3_errmsg(db));
return 1; // EXIT_FAILURE
}

vector<struct movie> data = {
{"Monty Python Live at the Hollywood Bowl", 1982, 7.9},
{"Monty Python's The Meaning of Life", 1983, 7.5},
{"Monty Python's Life of Brian", 1979, 8.0}
};

char sql_cmd[1024] = "INSERT INTO movie VALUES(?, ?, ?)";
//char sql_cmd[1024] = "INSERT INTO movie(title, year, score) VALUES(?, ?, ?)";
//char sql_cmd[1024] = "INSERT INTO movie(title, year, score) VALUES(@title, @year, @score)";
sqlite3_stmt *stmt = NULL;
rc = sqlite3_prepare_v2(db, sql_cmd, -1, &stmt, NULL);
if (rc != SQLITE_OK) {
printf("sqlite3_prepare_v2 failed, %s\n", sqlite3_errmsg(db));
return false;
}

for (int i = 0; i < data.size(); i++) {
sqlite3_bind_text(stmt, 1, data[i].title.c_str(), data[i].title.size(), NULL);
sqlite3_bind_int(stmt, 2, data[i].year);
sqlite3_bind_double(stmt, 3, data[i].score);

rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE) {
printf("sqlite3_step failed, %s\n", sqlite3_errmsg(db));
return false;
}

rc = sqlite3_reset(stmt);
if (rc != SQLITE_OK) {
printf("sqlite3_reset failed, %s\n", sqlite3_errmsg(db));
return false;
}
}

rc = sqlite3_finalize(stmt);
if (rc != SQLITE_OK) {
printf("sqlite3_finalize failed, %s\n", sqlite3_errmsg(db));
return false;
}

rc = sqlite3_close(db);
if (rc != SQLITE_OK) {
printf("sqlite3_close failed, %s\n", sqlite3_errmsg(db));
return 1; // EXIT_FAILURE
}

db = NULL;
return 0;
}

C/C++ SQLite SELECT 用法與範例

以下示範 C/C++ SQLite SELECT 語法查詢資料,
C/C++ SQLite 如果要查詢 tutorial.db 資料庫裡有什麼資料表的話可以這樣寫,

cpp-sqlite3-tutorial-select.cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
// g++ cpp-sqlite3-tutorial-select.cpp -o a.out `pkg-config --cflags --libs sqlite3`
// g++ cpp-sqlite3-tutorial-select.cpp -o a.out -I./sqlite -L./ -lsqlite3 -pthread -ldl
#include <stdio.h>
#include "sqlite3.h"

int sqlite_callback(void *data, int ncols, char **values, char **attribute) {
printf("ncols: %d\n", ncols);
for (int i = 0; i < ncols; i++) {
printf("%s=%s\n", attribute[i], values[i]);
}
return 0;
}

int main() {
int rc;
sqlite3 *db = NULL;

rc = sqlite3_open("tutorial.db", &db);
if (rc != SQLITE_OK) {
printf("sqlite3_open failed, %s\n", sqlite3_errmsg(db));
return 1; // EXIT_FAILURE
}

const char *sql_cmd = "SELECT name FROM sqlite_master";
rc = sqlite3_exec(db, sql_cmd, sqlite_callback, NULL, NULL);
if (rc != SQLITE_OK) {
printf("sqlite3_exec failed, %s\n", sqlite3_errmsg(db));
return false;
}

rc = sqlite3_close(db);
if (rc != SQLITE_OK) {
printf("sqlite3_close failed, %s\n", sqlite3_errmsg(db));
return 1; // EXIT_FAILURE
}

db = NULL;
return 0;
}

執行結果如下,

1
2
ncols: 1
name=movie

C/C++ SQLite 如果要查詢 movie 資料表裡有什麼資料的話可以這樣寫,

cpp-sqlite3-tutorial-select2.cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
// g++ cpp-sqlite3-tutorial-select2.cpp -o a.out `pkg-config --cflags --libs sqlite3`
// g++ cpp-sqlite3-tutorial-select2.cpp -o a.out -I./sqlite -L./ -lsqlite3 -pthread -ldl
#include <stdio.h>
#include "sqlite3.h"

int sqlite_callback(void *data, int ncols, char **values, char **attribute) {
printf("sqlite_callback data=%s\n", (char *)data);
printf("ncols: %d\n", ncols);
for (int i = 0; i < ncols; i++) {
printf("%s=%s\n", attribute[i], values[i]);
}
return 0;
}

int main() {
int rc;
sqlite3 *db = NULL;

rc = sqlite3_open("tutorial.db", &db);
if (rc != SQLITE_OK) {
printf("sqlite3_open failed, %s\n", sqlite3_errmsg(db));
return 1; // EXIT_FAILURE
}

const char *sql_cmd = "SELECT * FROM movie";
const char* data = "hello world";
rc = sqlite3_exec(db, sql_cmd, sqlite_callback, (void *)data, NULL);
if (rc != SQLITE_OK) {
printf("sqlite3_exec failed, %s\n", sqlite3_errmsg(db));
return false;
}

rc = sqlite3_close(db);
if (rc != SQLITE_OK) {
printf("sqlite3_close failed, %s\n", sqlite3_errmsg(db));
return 1; // EXIT_FAILURE
}

db = NULL;
return 0;
}

執行結果如下,

1
2
3
4
5
6
7
8
9
10
sqlite_callback data=hello world
ncols: 3
title=Monty Python and the Holy Grail
year=1975
score=8.2
sqlite_callback data=hello world
ncols: 3
title=And Now for Something Completely Different
year=1971
score=7.5

C/C++ SQLite UPDATE 用法與範例

以下示範 C/C++ SQLite UPDATE 語法更新資料,
如果 movie 資料表找到 score 分數為 8.2 的資料時,更新它們的 title 與 year,

cpp-sqlite3-tutorial-update.cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
// g++ cpp-sqlite3-tutorial-update.cpp -o a.out `pkg-config --cflags --libs sqlite3`
// g++ cpp-sqlite3-tutorial-update.cpp -o a.out -I./sqlite -L./ -lsqlite3 -pthread -ldl
#include <stdio.h>
#include "sqlite3.h"

int main() {
int rc;
sqlite3 *db = NULL;

rc = sqlite3_open("tutorial.db", &db);
if (rc != SQLITE_OK) {
printf("sqlite3_open failed, %s\n", sqlite3_errmsg(db));
return 1; // EXIT_FAILURE
}

char sql_cmd[1024] = "UPDATE movie SET title='12345', year=2000 WHERE score=8.2";
rc = sqlite3_exec(db, sql_cmd, NULL, NULL, NULL);
if (rc != SQLITE_OK) {
printf("sqlite3_exec failed, %s\n", sqlite3_errmsg(db));
return false;
}

rc = sqlite3_close(db);
if (rc != SQLITE_OK) {
printf("sqlite3_close failed, %s\n", sqlite3_errmsg(db));
return 1; // EXIT_FAILURE
}

db = NULL;
return 0;
}

如果有多筆符合的話會更新多筆。

C/C++ SQLite DELETE 用法與範例

以下示範 C/C++ SQLite DELETE 語法刪除資料表,
C/C++ SQLite 如果要刪除 movie 資料表裡的 score 分數為 8.2 的資料可以這樣寫,

cpp-sqlite3-tutorial-delete.cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
// g++ cpp-sqlite3-tutorial-delete.cpp -o a.out `pkg-config --cflags --libs sqlite3`
// g++ cpp-sqlite3-tutorial-delete.cpp -o a.out -I./sqlite -L./ -lsqlite3 -pthread -ldl
#include <stdio.h>
#include "sqlite3.h"

int main() {
int rc;
sqlite3 *db = NULL;

rc = sqlite3_open("tutorial.db", &db);
if (rc != SQLITE_OK) {
printf("sqlite3_open failed, %s\n", sqlite3_errmsg(db));
return 1; // EXIT_FAILURE
}

char sql_cmd[1024] = "DELETE FROM movie WHERE score=8.2";
rc = sqlite3_exec(db, sql_cmd, NULL, NULL, NULL);
if (rc != SQLITE_OK) {
printf("sqlite3_exec failed, %s\n", sqlite3_errmsg(db));
return false;
}

rc = sqlite3_close(db);
if (rc != SQLITE_OK) {
printf("sqlite3_close failed, %s\n", sqlite3_errmsg(db));
return 1; // EXIT_FAILURE
}

db = NULL;
return 0;
}

C/C++ SQLite 如果要刪除 movie 資料表裡的所有資料可以這樣寫,

cpp-sqlite3-tutorial-delete2.cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
// g++ cpp-sqlite3-tutorial-delete2.cpp -o a.out `pkg-config --cflags --libs sqlite3`
// g++ cpp-sqlite3-tutorial-delete2.cpp -o a.out -I./sqlite -L./ -lsqlite3 -pthread -ldl
#include <stdio.h>
#include "sqlite3.h"

int main() {
int rc;
sqlite3 *db = NULL;

rc = sqlite3_open("tutorial.db", &db);
if (rc != SQLITE_OK) {
printf("sqlite3_open failed, %s\n", sqlite3_errmsg(db));
return 1; // EXIT_FAILURE
}

char sql_cmd[1024] = "DELETE FROM movie";
rc = sqlite3_exec(db, sql_cmd, NULL, NULL, NULL);
if (rc != SQLITE_OK) {
printf("sqlite3_exec failed, %s\n", sqlite3_errmsg(db));
return false;
}

rc = sqlite3_close(db);
if (rc != SQLITE_OK) {
printf("sqlite3_close failed, %s\n", sqlite3_errmsg(db));
return 1; // EXIT_FAILURE
}

db = NULL;
return 0;
}

以上就是 C/C++ SQLite 資料庫教學與範例 資料庫教學與範例介紹,
如果你覺得我的文章寫得不錯、對你有幫助的話記得 Facebook 按讚支持一下!

其它參考
SQLite - C/C++
https://www.tutorialspoint.com/sqlite/sqlite_c_cpp.htm

其它相關文章推薦
如果你想學習 C++ 相關技術,可以參考看看下面的文章,
C/C++ 新手入門教學懶人包