一篇文章帶你掌握SQLite3基本用法

2022-06-13 18:04:08

一、基本語法

１．常用指令

.open filename --開啟檔案
-- 註解
.show --顯示SQLite 命令提示字元的預設設定
.q --退出
.databases --顯示資料庫（注：顯示開啟的資料庫）
.help  --幫助
.dump  --匯入匯出資料庫
.tables  --檢視表

2.資料型別

儲存型別	描述
NULL	空值
int	整形
text	一個文字字串
blob	一個blob資料
integer	一個帶符號的整數，根據值的大小儲存在１、２、３、４、６或８位元組中
real	值是一個浮點值，儲存為８位元組的浮點數
......	......

３．建立資料庫

.open test.db --沒有就建立
sqlite3 DatabaseName.db

上面的命令將在當前目錄下建立一個檔案 testDB.db。該檔案將被 SQLite 引擎用作資料庫。如果您已經注意到 sqlite3 命令在成功建立資料庫檔案之後，將提供一個 sqlite> 提示符。

.databases 命令用於檢查它是否在資料庫列表中。

.open 操作

4. 匯入/匯出資料庫

sqlite3 test.db .dump　> filename --匯出
sqlite3 test.db < filename --匯入

上面的轉換流整個 testDB.db 資料庫的內容到 SQLite 的語句中，並將其轉儲到 ASCII 文字檔案 testDB.sql 中。您可以通過簡單的方式從生成的 testDB.sql 恢復，如下所示我刪掉testDB.db後：

5.建立表

--注意，在開啟資料庫時才能操作
CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

CREATE TABLE 是告訴資料庫系統建立一個新表的關鍵字。CREATE TABLE 語句後跟著表的唯一的名稱或標識。您也可以選擇指定帶有 table_name 的 database_name。

如上圖所示，我們建立了COMPANY DEPARTMENT兩個表。其中ID 作為主鍵，NOT NULL 的約束表示在表中建立紀錄時這些欄位不能為 NULL。

６．檢視表的詳細資訊

.schema --注意：開啟資料庫時才能操作

７．刪除表

DROP TABLE database_name.table_name;

如上，刪除了名為DEPARTMENT的表

８.插入資料

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);

在這裡，column1, column2,...columnN 是要插入資料的表中的列的名稱。

如果要為表中的所有列新增值，您也可以不需要在 SQLite 查詢中指定列名稱。但要確保值的順序與列在表中的順序一致。SQLite 的 INSERT INTO 語法如下：

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

現在，我已經建立了COMPANY表，如下

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

現在，下面的語句將在 COMPANY 表中建立六個記錄：

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
 
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
 
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
 
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
 
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );
 
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );

輸出結果如下：

我們也可以使用第二種語法在COMPANY 表中建立一個記錄，如下所示：

INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

輸出結果如下：

９.格式化輸出

.header on
.mode column
.timer on　--開啟ＣＰＵ定時器
SELECT * FROM table_name;　--顯示錶table_name

非格式化輸出

格式化輸出

１０．輸出表

完整輸出

.header on
.mode column
SELECT * FROM COMPANY;

輸出指定列

.header on
.mode column
SELECT ID, NAME, SALARY FROM COMPANY;　--只輸出ID, NAME和SALARY三列

設定輸出列的寬度

.width num1,num1,num3....
SELECT * FROM COMPANY;

下面 .width 命令設定第一列的寬度為 10，第二列的寬度為 20，第三列的寬度為 10。輸出結果如下：

１１．運運算元

sqlite運運算元主要用於 SQLite 語句的 WHERE 子句中執行操作，如比較和算術運算。

運運算元用於指定 SQLite 語句中的條件，並在語句中連線多個條件。

算術運運算元：

運運算元描述範例+加法 - 把運運算元兩邊的值相加a + b 將得到 30-減法 - 左運算元減去右運算元a - b 將得到 -10*乘法 - 把運運算元兩邊的值相乘a * b 將得到 200/除法 - 左運算元除以右運算元b / a 將得到 2%取模 - 左運算元除以右運算元后得到的餘數b % a will give 0

比較運運算元

運運算元	描述	範例
+	加法 - 把運運算元兩邊的值相加	a + b 將得到 30
-	減法 - 左運算元減去右運算元	a - b 將得到 -10
*	乘法 - 把運運算元兩邊的值相乘	a * b 將得到 200
/	除法 - 左運算元除以右運算元	b / a 將得到 2
%	取模 - 左運算元除以右運算元后得到的餘數	b % a will give 0

邏輯運運算元

運運算元	描述	範例
==	檢查兩個運算元的值是否相等，如果相等則條件為真。	(a == b) 不為真。
=	檢查兩個運算元的值是否相等，如果相等則條件為真。	(a = b) 不為真。
!=	檢查兩個運算元的值是否相等，如果不相等則條件為真。	(a != b) 為真。
<>	檢查兩個運算元的值是否相等，如果不相等則條件為真。	(a <> b) 為真。
>	檢查左運算元的值是否大於右運算元的值，如果是則條件為真。	(a > b) 不為真。
<	檢查左運算元的值是否小於右運算元的值，如果是則條件為真。	(a < b) 為真。
>=	檢查左運算元的值是否大於等於右運算元的值，如果是則條件為真。	(a >= b) 不為真。
<=	檢查左運算元的值是否小於等於右運算元的值，如果是則條件為真。	(a <= b) 為真。
!<	檢查左運算元的值是否不小於右運算元的值，如果是則條件為真。	(a !< b) 為假。
!>	檢查左運算元的值是否不大於右運算元的值，如果是則條件為真。	(a !> b) 為真。

位運運算元

p	q	p & q	p \| q
0	0	0	0
0	1	0	1
1	1	1	1
1	0	0	1

下面直接上例子

我有COMPANY 表如下：

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

用 SELECT列出SALARY 大於 50,000.00 的所有記錄：

SELECT * FROM COMPANY WHERE SALARY > 50000;

輸出結果如下：

用 SELECT列出SALARY 等於的所有記錄：

SELECT * FROM COMPANY WHERE SALARY = 20000;

輸出結果如下：

用 SELECT列出AGE 大於等於 25 且SALARY大於等於 65000.00的所有記錄：

SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

輸出結果如下：

12.where子句

SQLite的 WHERE 子句用於指定從一個表或多個表中獲取資料的條件。如果滿足給定的條件，即為真（true）時，則從表中返回特定的值。您可以使用 WHERE 子句來過濾記錄，只獲取需要的記錄。WHERE 子句不僅可用在 SELECT 語句中，它也可用在 UPDATE、DELETE 語句中，等等。用例參考運運算元。

13.刪除記錄表中的資料

SQLite中，刪除記錄表資料為DELETE語句，我們可以使用帶有 WHERE 子句的 DELETE。

語法如下：

DELETE FROM table_name　WHERE [condition];

我們有以下記錄表：

刪除ID為７的列：

DELETE FROM COMPANY WHERE ID = 7;

再次輸出結果：

14.update語句

SQLite 的 UPDATE 查詢用於修改表中已有的記錄。可以使用帶有 WHERE 子句的 UPDATE 查詢來更新選定行，否則所有的行都會被更新。

語法：

UPDATE table_name　
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

注：這三行實為同一行。

現在我有資料表如下：

把COMPANY表中ID 為 6 的客戶地址改為Texas：

UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;

修改結果：

如果您想修改 COMPANY 表中 ADDRESS 和 SALARY 列的所有值，則不需要使用 WHERE 子句，UPDATE 查詢如下：

UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00;

修改結果：

二、Ｃ/C++操作

1.介面API

下面介面能滿足我們的基本需求，需要學習更多的操作，我們可以參考官方檔案。

序號	API & 描述
1	sqlite3_open(const char filename, sqlite3 *ppDb) 該例程開啟一個指向 SQLite 資料庫檔案的連線，返回一個用於其他 SQLite 程式的資料庫連線物件。如果 filename 引數是 NULL 或 ':memory:'，那麼 sqlite3_open() 將會在 RAM 中建立一個記憶體資料庫，這隻會在 session 的有效時間內持續。如果檔名 filename 不為 NULL，那麼 sqlite3_open() 將使用這個引數值嘗試開啟資料庫檔案。如果該名稱的檔案不存在，sqlite3_open() 將建立一個新的命名為該名稱的資料庫檔案並開啟。
2	sqlite3_exec(sqlite3, const char sql, sqlite_callback, void data, char *errmsg) 該例程提供了一個執行 SQL 命令的快捷方式，SQL 命令由 sql 引數提供，可以由多個 SQL 命令組成。在這裡，第一個引數 sqlite3 是開啟的資料庫物件，sqlite_callback 是一個回撥，data 作為其第一個引數，errmsg 將被返回用來獲取程式生成的任何錯誤。 sqlite3_exec() 程式解析並執行由 sql 引數所給的每個命令，直到字串結束或者遇到錯誤為止。
3	sqlite3_close(sqlite3*) 該例程關閉之前呼叫 sqlite3_open() 開啟的資料庫連線。所有與連線相關的語句都應在連線關閉之前完成。如果還有查詢沒有完成，sqlite3_close() 將返回 SQLITE_BUSY 禁止關閉的錯誤訊息。

序號

API & 描述

sqlite3_open(const char *filename, sqlite3 **ppDb)

該例程開啟一個指向 SQLite 資料庫檔案的連線，返回一個用於其他 SQLite 程式的資料庫連線物件。

如果 filename 引數是 NULL 或 ':memory:'，那麼 sqlite3_open() 將會在 RAM 中建立一個記憶體資料庫，這隻會在 session 的有效時間內持續。

如果檔名 filename 不為 NULL，那麼 sqlite3_open() 將使用這個引數值嘗試開啟資料庫檔案。如果該名稱的檔案不存在，sqlite3_open() 將建立一個新的命名為該名稱的資料庫檔案並開啟。

sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char **errmsg)

該例程提供了一個執行 SQL 命令的快捷方式，SQL 命令由 sql 引數提供，可以由多個 SQL 命令組成。

在這裡，第一個引數 sqlite3 是開啟的資料庫物件，sqlite_callback 是一個回撥，data 作為其第一個引數，errmsg 將被返回用來獲取程式生成的任何錯誤。

sqlite3_exec() 程式解析並執行由 sql 引數所給的每個命令，直到字串結束或者遇到錯誤為止。

sqlite3_close(sqlite3*)

該例程關閉之前呼叫 sqlite3_open() 開啟的資料庫連線。所有與連線相關的語句都應在連線關閉之前完成。

如果還有查詢沒有完成，sqlite3_close() 將返回 SQLITE_BUSY 禁止關閉的錯誤訊息。

2.連線資料庫

下面的 C 程式碼段顯示瞭如何連線到一個現有的資料庫。如果資料庫不存在，那麼它就會被建立，最後將返回一個資料庫物件。

#include <stdio.h>
#include <sqlite3.h>
 
int main(int argc, char* argv[])
{
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
 
   rc = sqlite3_open("test.db", &db);
 
   if( rc ){
      fprintf(stderr, "Can't open database: %sn", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "Opened database successfullyn");
   }
   sqlite3_close(db);
}

編譯命令

gcc lianjie.c -l sqlite3

執行結果

終端輸入ls -l命令發現多了個test.db檔案，如圖：

３．建立表

Ｃ語言建立表與終端建立操作差不多，只不過命令由sqlite3_exec()函數的sql引數傳入。格式如下：

sql = "CREATE TABLE COMPANY("  
         "ID INT PRIMARY KEY     NOT NULL," 
         "NAME           TEXT    NOT NULL," 
         "AGE            INT     NOT NULL," 
         "ADDRESS        CHAR(50)," 
         "SALARY         REAL );";

範例程式碼：

#include <stdio.h>
#include <stdlib.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;
   char *sql;
 
   /* Open database */
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "Can't open database: %sn", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stdout, "Opened database successfullyn");
   }
 
   /* Create SQL statement */
   sql = "CREATE TABLE COMPANY("  
         "ID INT PRIMARY KEY     NOT NULL," 
         "NAME           TEXT    NOT NULL," 
         "AGE            INT     NOT NULL," 
         "ADDRESS        CHAR(50)," 
         "SALARY         REAL );";
 
   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
   if( rc != SQLITE_OK ){
   fprintf(stderr, "SQL error: %sn", zErrMsg);
      sqlite3_free(zErrMsg);
   }else{
      fprintf(stdout, "Table created successfullyn");
   }
   sqlite3_close(db);
   return 0;
}

輸出結果：

再次ls -l:

我們可以看到，test.db檔案大小明顯變大了。

4.插入資料

與建立表類似，sql引數設為：

  sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  
         "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " 
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  
         "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "     
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" 
         "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" 
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" 
         "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";

範例程式碼：

#include <stdio.h>
#include <stdlib.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;
   char *sql;
 
   /* Open database */
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "Can't open database: %sn", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "Opened database successfullyn");
   }
 
   /* Create SQL statement */
   sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  
         "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " 
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  
         "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "     
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" 
         "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" 
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" 
         "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
 
   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
   if( rc != SQLITE_OK ){
      fprintf(stderr, "SQL error: %sn", zErrMsg);
      sqlite3_free(zErrMsg);
   }else{
      fprintf(stdout, "Records created successfullyn");
   }
   sqlite3_close(db);
   return 0;
}

輸出結果：

５．查表操作

sqlite3_exec()給我們提供了一個回撥函數，其宣告如下：

typedef int (*sqlite3_callback)(
void*,    /* Data provided in the 4th argument of sqlite3_exec() */
int,      /* The number of columns in row */
char**,   /* An array of strings representing fields in the row */
char**    /* An array of strings representing column names */
);

第一個引數：即第四個引數傳入的資料

第二個引數：行中的列數

第三個引數：表示行中欄位的字串陣列，即各行中的資料

第四個引數：表示列名的字串陣列，建立連結串列時設定的

執行流程：查表，是否還有符合條件資料。有，執行sqlite3_callback()函數；沒有，退出

用法講完了，下面看例子：

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
 
static int callback(void *data, int argc, char **argv, char **azColName){
   int i;
   fprintf(stderr, "%s: ", (const char*)data);
   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;
   char *sql;
   const char* data = "Callback function called";
 
   /* Open database */
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "Can't open database: %sn", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "Opened database successfullyn");
   }
 
   /* Create SQL statement */
   sql = "SELECT * from COMPANY";
 
   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if( rc != SQLITE_OK ){
      fprintf(stderr, "SQL error: %sn", zErrMsg);
      sqlite3_free(zErrMsg);
   }else{
      fprintf(stdout, "Operation done successfullyn");
   }
   sqlite3_close(db);
   return 0;
}

上面程式顯示瞭如何從前面建立的 COMPANY 表中獲取並顯示記錄，輸出結果如下：

這裡我沒有特殊指明查詢條件，表示查詢全部。

６．資料刪除操作

sql引數設定：

sql = "DELETE from COMPANY where ID=2; " \刪除ＩＤ等於２的行
         "SELECT * from COMPANY";        \顯示錶

這裡跟上面不同的是多了一個命令，表面sql引數可由多個命令組成。

範例程式碼：

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 
 
static int callback(void *data, int argc, char **argv, char **azColName){
   int i;
   fprintf(stderr, "%s: ", (const char*)data);
   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;
   char *sql;
   const char* data = "Callback function called";
 
   /* Open database */
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "Can't open database: %sn", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "Opened database successfullyn");
   }
 
   /* Create merged SQL statement */
   sql = "DELETE from COMPANY where ID=2; " 
         "SELECT * from COMPANY";
 
   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if( rc != SQLITE_OK ){
      fprintf(stderr, "SQL error: %sn", zErrMsg);
      sqlite3_free(zErrMsg);
   }else{
      fprintf(stdout, "Operation done successfullyn");
   }
   sqlite3_close(db);
   return 0;
}

輸出：

７．UPDATE 操作

操作範例：

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 
 
static int callback(void *data, int argc, char **argv, char **azColName){
   int i;
   fprintf(stderr, "%s: ", (const char*)data);
   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;
   char *sql;
   const char* data = "Callback function called";
 
   /* Open database */
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "Can't open database: %sn", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "Opened database successfullyn");
   }
 
   /* Create merged SQL statement */
   sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1; " 
         "SELECT * from COMPANY";
 
   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if( rc != SQLITE_OK ){
      fprintf(stderr, "SQL error: %sn", zErrMsg);
      sqlite3_free(zErrMsg);
   }else{
      fprintf(stdout, "Operation done successfullyn");
   }
   sqlite3_close(db);
   return 0;
}

輸出結果：