- SQLite 教程
- SQLite - 首頁
- SQLite - 概述
- SQLite - 安裝
- SQLite - 命令
- SQLite - 語法
- SQLite - 資料型別
- SQLite - 建立資料庫 (CREATE Database)
- SQLite - 附加資料庫 (ATTACH Database)
- SQLite - 分離資料庫 (DETACH Database)
- SQLite - 建立表 (CREATE Table)
- SQLite - 刪除表 (DROP Table)
- SQLite - INSERT 查詢
- SQLite - SELECT 查詢
- SQLite - 運算子
- SQLite - 表示式
- SQLite - WHERE 子句
- SQLite - AND & OR 子句
- SQLite - UPDATE 查詢
- SQLite - DELETE 查詢
- SQLite - LIKE 子句
- SQLite - GLOB 子句
- SQLite - LIMIT 子句
- SQLite - ORDER By 子句
- SQLite - GROUP By 子句
- SQLite - HAVING 子句
- SQLite - DISTINCT 關鍵字
- 高階 SQLite
- SQLite - PRAGMA
- SQLite - 約束
- SQLite - 連線 (JOINS)
- SQLite - UNION 子句
- SQLite - NULL 值
- SQLite - 別名語法 (ALIAS Syntax)
- SQLite - 觸發器 (Triggers)
- SQLite - 索引 (Indexes)
- SQLite - INDEXED BY 子句
- SQLite - ALTER 命令
- SQLite - TRUNCATE 命令
- SQLite - 檢視 (Views)
- SQLite - 事務 (Transactions)
- SQLite - 子查詢 (Subqueries)
- SQLite - AUTOINCREMENT
- SQLite - 注入 (Injection)
- SQLite - EXPLAIN
- SQLite - VACUUM
- SQLite - 日期和時間 (Date & Time)
- SQLite - 有用函式
- SQLite 有用資源
- SQLite 快速指南
- SQLite 有用資源
- SQLite - 討論
SQLite 快速指南
SQLite - 概述
本章幫助您瞭解什麼是 SQLite，它與 SQL 的區別，為什麼需要它以及它處理應用程式資料庫的方式。
SQLite 是一個軟體庫，它實現了一個自包含的、無伺服器的、零配置的、事務性 SQL 資料庫引擎。SQLite 是增長最快的資料庫引擎之一，但這指的是流行程度的增長，與它的規模無關。SQLite 的原始碼是公共領域的。
什麼是 SQLite？
SQLite 是一個程序內庫，它實現了一個自包含的、無伺服器的、零配置的、事務性 SQL 資料庫引擎。它是一個零配置的資料庫，這意味著與其他資料庫不同，您不需要在系統中配置它。
SQLite 引擎不像其他資料庫那樣是一個獨立的程序，您可以根據需要靜態或動態地將其連結到您的應用程式。SQLite 直接訪問其儲存檔案。
為什麼選擇 SQLite？
SQLite 不需要單獨的伺服器程序或系統來執行（無伺服器）。
SQLite 是零配置的，這意味著不需要設定或管理。
完整的 SQLite 資料庫儲存在一個單一的跨平臺磁碟檔案中。
SQLite 非常小巧輕便，完全配置後不到 400KiB，省略可選功能後不到 250KiB。
SQLite 是自包含的，這意味著沒有外部依賴項。
SQLite 事務完全符合 ACID 規範，允許從多個程序或執行緒安全訪問。
SQLite 支援 SQL92 (SQL2) 標準中大多數查詢語言功能。
SQLite 使用 ANSI-C 編寫，並提供簡單易用的 API。
SQLite 可用於 UNIX（Linux、Mac OS-X、Android、iOS）和 Windows（Win32、WinCE、WinRT）。
SQLite 簡史
2000 年 - D. Richard Hipp 為無需管理即可執行程式的目的設計了 SQLite。
2000 年 - 8 月，SQLite 1.0 與 GNU 資料庫管理器一起釋出。
2011 年 - Hipp 宣佈向 SQLite 資料庫新增 UNQl 介面並開發 UNQLite（面向文件的資料庫）。
SQLite 限制
SQLite 中有一些 SQL92 的不支援的功能，列在下面的表格中。
| 序號 | 功能及描述 |
|---|---|
| 1 | RIGHT OUTER JOIN 僅實現 LEFT OUTER JOIN。 |
| 2 | FULL OUTER JOIN 僅實現 LEFT OUTER JOIN。 |
| 3 | ALTER TABLE 支援 ALTER TABLE 命令的 RENAME TABLE 和 ADD COLUMN 變體。不支援 DROP COLUMN、ALTER COLUMN、ADD CONSTRAINT。 |
| 4 | 觸發器支援 支援 FOR EACH ROW 觸發器，但不支援 FOR EACH STATEMENT 觸發器。 |
| 5 | 檢視 (VIEWs) SQLite 中的檢視是隻讀的。您不能對檢視執行 DELETE、INSERT 或 UPDATE 語句。 |
| 6 | GRANT 和 REVOKE 唯一可以應用的訪問許可權是底層作業系統的普通檔案訪問許可權。 |
SQLite 命令
與關係資料庫互動的標準 SQLite 命令類似於 SQL。它們是 CREATE、SELECT、INSERT、UPDATE、DELETE 和 DROP。這些命令可以根據其操作性質分為幾組：
DDL - 資料定義語言
| 序號 | 命令及描述 |
|---|---|
| 1 | CREATE 在資料庫中建立一個新表、表的檢視或其他物件。 |
| 2 | ALTER 修改現有的資料庫物件，例如表。 |
| 3 | DROP 刪除資料庫中的整個表、表的檢視或其他物件。 |
DML - 資料操縱語言
| 序號 | 命令及描述 |
|---|---|
| 1 | INSERT 建立記錄 |
| 2 | UPDATE 修改記錄 |
| 3 | DELETE 刪除記錄 |
DQL - 資料查詢語言
| 序號 | 命令及描述 |
|---|---|
| 1 | SELECT 從一個或多個表中檢索某些記錄 |
SQLite - 安裝
SQLite 以其出色的零配置功能而聞名，這意味著不需要複雜的設定或管理。本章將引導您完成在 Windows、Linux 和 Mac OS X 上設定 SQLite 的過程。
在 Windows 上安裝 SQLite
**步驟 1** - 前往 SQLite 下載頁面，從 Windows 部分下載預編譯的二進位制檔案。
**步驟 2** - 下載 sqlite-shell-win32-*.zip 和 sqlite-dll-win32-*.zip 壓縮檔案。
**步驟 3** - 建立一個資料夾 C:\>sqlite 並將上述兩個壓縮檔案解壓到此資料夾中，這將為您提供 sqlite3.def、sqlite3.dll 和 sqlite3.exe 檔案。
**步驟 4** - 將 C:\>sqlite 新增到您的 PATH 環境變數中，最後轉到命令提示符併發出 sqlite3 命令，這應該顯示以下結果。
C:\>sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>
在 Linux 上安裝 SQLite
如今，幾乎所有 Linux 作業系統都自帶 SQLite。因此，您只需發出以下命令即可檢查您的機器上是否已安裝 SQLite。
$sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>
如果您沒有看到以上結果，則表示您的 Linux 機器上沒有安裝 SQLite。以下是安裝 SQLite 的步驟：
**步驟 1** - 前往 SQLite 下載頁面，從原始碼部分下載 sqlite-autoconf-*.tar.gz。
**步驟 2** - 執行以下命令：
$tar xvfz sqlite-autoconf-3071502.tar.gz $cd sqlite-autoconf-3071502 $./configure --prefix=/usr/local $make $make install
以上命令將在您的 Linux 機器上完成 SQLite 的安裝。您可以按照上面說明的方法進行驗證。
在 Mac OS X 上安裝 SQLite
儘管最新版本的 Mac OS X 預裝了 SQLite，但如果您沒有安裝，只需按照以下步驟操作：
**步驟 1** - 前往 SQLite 下載頁面，從原始碼部分下載 sqlite-autoconf-*.tar.gz。
**步驟 2** - 執行以下命令：
$tar xvfz sqlite-autoconf-3071502.tar.gz $cd sqlite-autoconf-3071502 $./configure --prefix=/usr/local $make $make install
上述過程將在您的 Mac OS X 機器上完成 SQLite 的安裝。您可以透過發出以下命令來驗證：
$sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>
最後，您將擁有 SQLite 命令提示符，您可以在其中為您的練習發出 SQLite 命令。
SQLite - 命令
本章將引導您瞭解 SQLite 程式設計師使用的簡單且有用的命令。這些命令稱為 SQLite 點命令，除了這些命令之外，它們不應該以分號 (;) 結尾。
讓我們從在命令提示符下鍵入簡單的 **sqlite3** 命令開始，這將為您提供 SQLite 命令提示符，您可以在其中發出各種 SQLite 命令。
$sqlite3 SQLite version 3.3.6 Enter ".help" for instructions sqlite>
要列出可用的點命令，您可以隨時輸入“.help”。例如：
sqlite>.help
以上命令將顯示各種重要的 SQLite 點命令列表，這些命令列在下表中。
| 序號 | 命令及描述 |
|---|---|
| 1 | .backup ?DB? FILE 將資料庫 (預設為“main”) 備份到 FILE |
| 2 | .bail ON|OFF 遇到錯誤後停止。預設為 OFF |
| 3 | .databases 列出已附加資料庫的名稱和檔案 |
| 4 | .dump ?TABLE? 以 SQL 文字格式轉儲資料庫。如果指定了 TABLE，則只轉儲與 LIKE 模式 TABLE 匹配的表 |
| 5 | .echo ON|OFF 開啟或關閉命令回顯 |
| 6 | .exit 退出 SQLite 提示符 |
| 7 | .explain ON|OFF 開啟或關閉適合 EXPLAIN 的輸出模式。無引數時，它開啟 EXPLAIN |
| 8 | .header(s) ON|OFF 開啟或關閉標題的顯示 |
| 9 | .help 顯示此訊息 |
| 10 | .import FILE TABLE 將資料從 FILE 匯入 TABLE |
| 11 | .indices ?TABLE? 顯示所有索引的名稱。如果指定了 TABLE，則僅顯示與 LIKE 模式 TABLE 匹配的表的索引 |
| 12 | .load FILE ?ENTRY? 載入擴充套件庫 |
| 13 | .log FILE|off 開啟或關閉日誌記錄。FILE 可以是 stderr/stdout |
| 14 | .mode MODE 設定輸出模式，其中 MODE 為以下之一：
|
| 15 | .nullvalue STRING 用 STRING 代替 NULL 值 |
| 16 | .output FILENAME 將輸出傳送到 FILENAME |
| 17 | .output stdout 將輸出傳送到螢幕 |
| 18 | .print STRING... 列印文字字串 |
| 19 | .prompt MAIN CONTINUE 替換標準提示符 |
| 20 | .quit 退出 SQLite 提示符 |
| 21 | .read FILENAME 執行 FILENAME 中的 SQL |
| 22 | .schema ?TABLE? 顯示 CREATE 語句。如果指定了 TABLE，則只顯示與 LIKE 模式 TABLE 匹配的表的 CREATE 語句 |
| 23 | .separator STRING 更改輸出模式和 .import 使用的分隔符 |
| 24 | .show 顯示各種設定的當前值 |
| 25 | .stats ON|OFF 啟用或停用統計資訊 |
| 26 | .tables ?PATTERN? 列出與 LIKE 模式匹配的表名 |
| 27 | .timeout MS 嘗試開啟被鎖定的表，持續時間為 MS 毫秒 |
| 28 | .width NUM NUM 設定“列”模式下的列寬 |
| 29 | .timer ON|OFF 啟用或停用 CPU 計時器測量 |
讓我們嘗試使用 .show 命令檢視 SQLite 命令提示符的預設設定。
sqlite>.show
echo: off
explain: off
headers: off
mode: column
nullvalue: ""
output: stdout
separator: "|"
width:
sqlite>
確保 sqlite> 提示符和點命令之間沒有空格，否則將無法正常工作。
格式化輸出
您可以使用以下一系列點命令來格式化輸出。
sqlite>.header on sqlite>.mode column sqlite>.timer on sqlite>
上述設定將產生以下格式的輸出。
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 CPU Time: user 0.000000 sys 0.000000
sqlite_master 表
主表儲存有關資料庫表的關鍵資訊，其名稱為 sqlite_master。您可以按如下方式檢視其模式：
sqlite>.schema sqlite_master
這將產生以下結果。
CREATE TABLE sqlite_master ( type text, name text, tbl_name text, rootpage integer, sql text );
SQLite - 語法
SQLite 遵循一組獨特的規則和指南，稱為語法。本章列出了所有基本的 SQLite 語法。
大小寫敏感性
需要注意的重要一點是，SQLite **不區分大小寫**，但是有一些命令區分大小寫，例如 **GLOB** 和 **glob** 在 SQLite 語句中具有不同的含義。
註釋
SQLite 註釋是您可以新增到 SQLite 程式碼中的額外說明，以提高其可讀性，它們可以出現在任何地方；允許出現空格，包括表示式內部和其它 SQL 語句的中間，但它們不能巢狀。
SQL 註釋以兩個連續的“-”字元（ASCII 0x2d）開頭，一直延伸到下一個換行符（ASCII 0x0a）或輸入結束，以先到者為準。
您也可以使用 C 樣式註釋，它們以“/*”開頭，一直延伸到下一個“*/”字元對或輸入結束，以先到者為準。C 樣式註釋可以跨多行。
sqlite> .help -- This is a single line comment
SQLite 語句
所有 SQLite 語句都以 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等關鍵字中的任何一個開頭，並且所有語句都以分號 (;) 結尾。
SQLite ANALYZE 語句
ANALYZE; or ANALYZE database_name; or ANALYZE database_name.table_name;
SQLite AND/OR 子句
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;
SQLite ALTER TABLE 語句
ALTER TABLE table_name ADD COLUMN column_def...;
SQLite ALTER TABLE 語句（重新命名）
ALTER TABLE table_name RENAME TO new_table_name;
SQLite ATTACH DATABASE 語句
ATTACH DATABASE 'DatabaseName' As 'Alias-Name';
SQLite BEGIN TRANSACTION 語句
BEGIN; or BEGIN EXCLUSIVE TRANSACTION;
SQLite BETWEEN 子句
SELECT column1, column2....columnN FROM table_name WHERE column_name BETWEEN val-1 AND val-2;
SQLite COMMIT 語句
COMMIT;
SQLite CREATE INDEX 語句
CREATE INDEX index_name ON table_name ( column_name COLLATE NOCASE );
SQLite CREATE UNIQUE INDEX 語句
CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...columnN);
SQLite CREATE TABLE 語句
CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) );
SQLite CREATE TRIGGER 語句
CREATE TRIGGER database_name.trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN stmt1; stmt2; .... END;
SQLite CREATE VIEW 語句
CREATE VIEW database_name.view_name AS SELECT statement....;
SQLite CREATE VIRTUAL TABLE 語句
CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log ); or CREATE VIRTUAL TABLE database_name.table_name USING fts3( );
SQLite COMMIT TRANSACTION 語句
COMMIT;
SQLite COUNT 子句
SELECT COUNT(column_name) FROM table_name WHERE CONDITION;
SQLite DELETE 語句
DELETE FROM table_name
WHERE {CONDITION};
SQLite DETACH DATABASE 語句
DETACH DATABASE 'Alias-Name';
SQLite DISTINCT 子句
SELECT DISTINCT column1, column2....columnN FROM table_name;
SQLite DROP INDEX 語句
DROP INDEX database_name.index_name;
SQLite DROP TABLE 語句
DROP TABLE database_name.table_name;
SQLite DROP VIEW 語句
DROP INDEX database_name.view_name;
SQLite DROP TRIGGER 語句
DROP INDEX database_name.trigger_name;
SQLite EXISTS 子句
SELECT column1, column2....columnN FROM table_name WHERE column_name EXISTS (SELECT * FROM table_name );
SQLite EXPLAIN 語句
EXPLAIN INSERT statement...; or EXPLAIN QUERY PLAN SELECT statement...;
SQLite GLOB 子句
SELECT column1, column2....columnN
FROM table_name
WHERE column_name GLOB { PATTERN };
SQLite GROUP BY 子句
SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name;
SQLite HAVING 子句
SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name HAVING (arithematic function condition);
SQLite INSERT INTO 語句
INSERT INTO table_name( column1, column2....columnN) VALUES ( value1, value2....valueN);
SQLite IN 子句
SELECT column1, column2....columnN FROM table_name WHERE column_name IN (val-1, val-2,...val-N);
SQLite Like 子句
SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };
SQLite NOT IN 子句
SELECT column1, column2....columnN FROM table_name WHERE column_name NOT IN (val-1, val-2,...val-N);
SQLite ORDER BY 子句
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};
SQLite PRAGMA 語句
PRAGMA pragma_name; For example: PRAGMA page_size; PRAGMA cache_size = 1024; PRAGMA table_info(table_name);
SQLite RELEASE SAVEPOINT 語句
RELEASE savepoint_name;
SQLite REINDEX 語句
REINDEX collation_name; REINDEX database_name.index_name; REINDEX database_name.table_name;
SQLite ROLLBACK 語句
ROLLBACK; or ROLLBACK TO SAVEPOINT savepoint_name;
SQLite SAVEPOINT 語句
SAVEPOINT savepoint_name;
SQLite SELECT 語句
SELECT column1, column2....columnN FROM table_name;
SQLite UPDATE 語句
UPDATE table_name SET column1 = value1, column2 = value2....columnN=valueN [ WHERE CONDITION ];
SQLite VACUUM 語句
VACUUM;
SQLite WHERE 子句
SELECT column1, column2....columnN FROM table_name WHERE CONDITION;
SQLite - 資料型別
SQLite 資料型別是一個屬性，它指定任何物件的數 據型別。SQLite 中的每個列、變數和表示式都具有相關的資料型別。
建立表時會使用這些資料型別。SQLite 使用更通用的動態型別系統。在 SQLite 中，值的數 據型別與其自身相關聯，而不是與其容器相關聯。
SQLite 儲存類
儲存在 SQLite 資料庫中的每個值都具有以下儲存類之一：
| 序號 | 儲存類和說明 |
|---|---|
| 1 | NULL 該值為 NULL 值。 |
| 2 | INTEGER 該值為帶符號整數，儲存在 1、2、3、4、6 或 8 位元組中，具體取決於值的量級。 |
| 3 | REAL 該值為浮點值，儲存為 8 位元組 IEEE 浮點數。 |
| 4 | TEXT 該值為文字字串，使用資料庫編碼（UTF-8、UTF-16BE 或 UTF-16LE）儲存。 |
| 5 | BLOB 該值為資料塊，按輸入時的原樣儲存。 |
SQLite 儲存類略微比資料型別更通用。例如，INTEGER 儲存類包含 6 種不同長度的不同整數資料型別。
SQLite 親和型別
SQLite 支援列上的**型別親和性**概念。任何列仍然可以儲存任何型別的資料，但是列的首選儲存類稱為其**親和性**。SQLite3 資料庫中的每個表列都分配有以下型別親和性之一：
| 序號 | 親和性和說明 |
|---|---|
| 1 | TEXT TEXT |
| 2 | NUMERIC 此列可以包含使用所有五種儲存類的值。 |
| 3 | INTEGER INTEGER |
| 4 | REAL 其行為與具有 NUMERIC 親和性的列相同，但 CAST 表示式中除外。 |
| 5 | REAL 其行為類似於具有 NUMERIC 親和性的列，但它會將整數值強制轉換為浮點表示形式。 |
NONE
親和性為 NONE 的列不偏向任何儲存類，並且不會嘗試將資料從一種儲存類強制轉換為另一種儲存類。
| SQLite 親和性和型別名稱 | 下表列出了建立 SQLite3 表時可以使用的各種資料型別名稱及其對應的應用親和性。 |
|---|---|
|
INTEGER |
|
TEXT |
|
REAL |
|
REAL |
|
NUMERIC |
DATE
DATETIME
布林資料型別
SQLite 沒有單獨的布林儲存類。布林值儲存為整數 0（false）和 1（true）。
| 序號 | 日期和時間資料型別 |
|---|---|
| 1 | TEXT SQLite 沒有單獨的儲存類來儲存日期和/或時間，但是 SQLite 能夠將日期和時間儲存為 TEXT、REAL 或 INTEGER 值。 |
| 2 | REAL 儲存類和日期格式 |
| 3 | INTEGER TEXT |
類似“YYYY-MM-DD HH:MM:SS.SSS”的日期格式
SQLite - 建立資料庫 (CREATE Database)
REAL
公元前 4714 年 11 月 24 日中午以來的天數
INTEGER
$sqlite3 DatabaseName.db
自 1970-01-01 00:00:00 UTC 以來的秒數
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
在 SQLite 中，使用sqlite3命令建立新的 SQLite 資料庫。建立資料庫不需要任何特殊許可權。
$sqlite3 testDB.db SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>
語法
以下是用於建立資料庫的 sqlite3 命令的基本語法：
sqlite>.databases seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db
資料庫名稱在 RDBMS 中始終應該是唯一的。
sqlite>.quit $
示例
如果要建立一個新的資料庫 <testDB.db>，則 SQLITE3 語句如下：
$sqlite3 testDB.db .dump > testDB.sql
上述命令將在當前目錄中建立一個名為testDB.db的檔案。SQLite 引擎將使用此檔案作為資料庫。您可能已經注意到，在建立資料庫時，sqlite3 命令在成功建立資料庫檔案後將提供一個sqlite>提示符。
$sqlite3 testDB.db < testDB.sql
建立資料庫後，您可以使用以下 SQLite .databases 命令在資料庫列表中驗證它。
SQLite - 附加資料庫 (ATTACH Database)
您可以使用 SQLite .quit 命令退出 sqlite 提示符，如下所示：
公元前 4714 年 11 月 24 日中午以來的天數
.dump 命令
ATTACH DATABASE 'DatabaseName' As 'Alias-Name';
您可以使用.dump點命令將完整資料庫匯出到文字檔案，方法是在命令提示符下使用以下 SQLite 命令。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
上述命令會將testDB.db資料庫的全部內容轉換為 SQLite 語句，並將其轉儲到 ASCII 文字檔案testDB.sql中。您可以簡單地按如下方式從生成的 testDB.sql 中執行恢復：
sqlite> ATTACH DATABASE 'testDB.db' as 'TEST';
目前您的資料庫是空的，因此您可以在資料庫中新增一些表和資料後再嘗試上述兩個過程。現在，讓我們繼續下一章。
sqlite> .database seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db 2 test /home/sqlite/testDB.db
假設您有多個數據庫可用，並且您希望一次使用其中的一個。SQLite ATTACH DATABASE 語句用於選擇特定資料庫，此命令之後，所有 SQLite 語句都將在附加的資料庫下執行。
sqlite> ATTACH DATABASE 'testDB.db' as 'TEMP'; Error: database TEMP is already in use sqlite> ATTACH DATABASE 'testDB.db' as 'main'; Error: database TEMP is already in use
SQLite - 分離資料庫 (DETACH Database)
以下是 SQLite ATTACH DATABASE 語句的基本語法。
如果資料庫是記憶體資料庫或臨時資料庫，則資料庫將被銷燬，其內容將丟失。
公元前 4714 年 11 月 24 日中午以來的天數
以下是 SQLite DETACH DATABASE '別名' 語句的基本語法。
DETACH DATABASE 'Alias-Name';
這裡，'別名' 是您在使用 ATTACH 語句附加資料庫時使用的相同別名。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
假設您有一個數據庫，您在上一章中建立了它，並使用 'test' 和 'currentDB' 附加了它，我們可以使用.database命令檢視。
sqlite>.databases seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db 2 test /home/sqlite/testDB.db 3 currentDB /home/sqlite/testDB.db
讓我們嘗試使用以下命令從 testDB.db 中分離 'currentDB'。
sqlite> DETACH DATABASE 'currentDB';
現在，如果您檢查當前附件，您會發現 testDB.db 仍然與 'test' 和 'main' 連線。
sqlite>.databases seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db 2 test /home/sqlite/testDB.db
SQLite - 建立表 (CREATE Table)
SQLite 的CREATE TABLE語句用於在任何給定的資料庫中建立一個新表。建立基本表包括命名錶和定義其列以及每列的資料型別。
公元前 4714 年 11 月 24 日中午以來的天數
以下是 CREATE TABLE 語句的基本語法。
CREATE TABLE database_name.table_name( column1 datatype PRIMARY KEY(one or more columns), column2 datatype, column3 datatype, ..... columnN datatype );
CREATE TABLE 是告訴資料庫系統建立一個新表的關鍵字。表的唯一名稱或識別符號位於 CREATE TABLE 語句之後。可以選擇性地，您可以指定database_name以及table_name。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
以下是一個示例，它建立一個 COMPANY 表，其中 ID 是主鍵，NOT NULL 是約束條件，表示在該表中建立記錄時這些欄位不能為 NULL。
sqlite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
讓我們再建立一個表，我們將在後續章節的練習中使用它。
sqlite> CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL );
您可以使用 SQLite 命令.tables命令驗證您的表是否已成功建立，該命令將用於列出附加資料庫中的所有表。
sqlite>.tables COMPANY DEPARTMENT
在這裡，您可以看到 COMPANY 表兩次，因為它顯示了主資料庫的 COMPANY 表和為您的 testDB.db 建立的 'test' 別名的 test.COMPANY 表。您可以使用以下 SQLite.schema命令獲取有關表的完整資訊。
sqlite>.schema COMPANY CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
SQLite - 刪除表 (DROP Table)
SQLite 的DROP TABLE語句用於刪除表定義以及與該表關聯的所有資料、索引、觸發器、約束和許可權規範。
使用此命令時必須小心，因為一旦表被刪除，表中所有可用資訊也將永遠丟失。
公元前 4714 年 11 月 24 日中午以來的天數
以下是 DROP TABLE 語句的基本語法。您可以選擇性地指定資料庫名稱以及表名，如下所示：
DROP TABLE database_name.table_name;
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
讓我們首先驗證 COMPANY 表，然後將其從資料庫中刪除。
sqlite>.tables COMPANY test.COMPANY
這意味著 COMPANY 表存在於資料庫中，因此讓我們將其刪除，如下所示：
sqlite>DROP TABLE COMPANY; sqlite>
現在，如果您嘗試使用 .TABLES 命令，則將不再找到 COMPANY 表。
sqlite>.tables sqlite>
它什麼也沒有顯示，這意味著資料庫中的表已成功刪除。
SQLite - INSERT 查詢
SQLite 的INSERT INTO語句用於向資料庫中的表中新增新的資料行。
公元前 4714 年 11 月 24 日中午以來的天數
以下是 INSERT INTO 語句的兩種基本語法。
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);
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
假設您已經在您的 testDB.db 中建立了 COMPANY 表，如下所示：
sqlite> 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 );
所有上述語句都將在 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 語句將資料填充到表中，前提是另一個表具有一組欄位，這些欄位是填充第一個表所需的。以下是語法：
INSERT INTO first_table_name [(column1, column2, ... columnN)] SELECT column1, column2, ...columnN FROM second_table_name [WHERE condition];
目前，您可以跳過上述語句。首先，讓我們學習將在後續章節中介紹的 SELECT 和 WHERE 子句。
SQLite - SELECT 查詢
SQLite 的SELECT語句用於從 SQLite 資料庫表中提取資料，該資料以結果表的形式返回資料。這些結果表也稱為結果集。
公元前 4714 年 11 月 24 日中午以來的天數
以下是 SQLite SELECT 語句的基本語法。
SELECT column1, column2, columnN FROM table_name;
這裡，column1、column2...是表的欄位，您要提取其值。如果要提取欄位中所有可用的欄位，則可以使用以下語法：
SELECT * FROM table_name;
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮具有以下記錄的 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 語句提取和顯示所有這些記錄的示例。這裡，前三個命令已用於設定格式正確的輸出。
sqlite>.header on sqlite>.mode column sqlite> SELECT * FROM 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
如果只想提取 COMPANY 表的選定欄位，則使用以下查詢：
sqlite> SELECT ID, NAME, SALARY FROM COMPANY;
上述查詢將產生以下結果。
ID NAME SALARY ---------- ---------- ---------- 1 Paul 20000.0 2 Allen 15000.0 3 Teddy 20000.0 4 Mark 65000.0 5 David 85000.0 6 Kim 45000.0 7 James 10000.0
設定輸出列寬
有時，在.mode column的情況下，您會遇到與輸出被截斷相關的問題，這是因為要顯示的列的預設寬度造成的。您可以做的是，可以使用.width num, num....命令設定列的可顯示列寬，如下所示：
sqlite>.width 10, 20, 10 sqlite>SELECT * FROM COMPANY;
上述.width命令將第一列寬度設定為 10，第二列寬度設定為 20，第三列寬度設定為 10。最後，上述 SELECT 語句將給出以下結果。
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
模式資訊
由於所有點命令都可在 SQLite 提示符下使用，因此在使用 SQLite 程式設計時，您將使用以下 SELECT 語句和sqlite_master表列出資料庫中建立的所有表。
sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table';
假設您的 testDB.db 中只有 COMPANY 表，這將產生以下結果。
tbl_name ---------- COMPANY
您可以列出有關 COMPANY 表的完整資訊，如下所示：
sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';
假設您的 testDB.db 中只有 COMPANY 表，這將產生以下結果。
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL )
SQLite - 運算子
什麼是 SQLite 中的運算子？
運算子是一個保留字或字元，主要用於 SQLite 語句的 WHERE 子句中執行操作，例如比較和算術運算。
運算子用於在 SQLite 語句中指定條件，並用作語句中多個條件的連線詞。
- 算術運算子
- 比較運算子
- 邏輯運算子
- 位運算子
SQLite 算術運算子
假設變數a持有 10，變數b持有 20，則 SQLite 算術運算子將按如下方式使用：
| 運算子 | 描述 | 您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。 |
|---|---|---|
| + (加法) | 將運算子兩側的值相加 | a + b 將得到 30 |
| - (減法) | 從左運算元中減去右運算元 | a - b 將得到 -10 |
| * (乘法) | 將運算子兩側的值相乘 | a * b 將得到 200 |
| / (除法) | 將左運算元除以右運算元 | b / a 將得到 2 |
| % (取模) | 將左運算元除以右運算元並返回餘數 | b % a 將得到 0 |
SQLite 比較運算子
假設變數a持有 10，變數b持有 20，則 SQLite 比較運算子將按如下方式使用
| 運算子 | 描述 | 您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。 |
|---|---|---|
| == | 檢查兩個運算元的值是否相等，如果相等，則條件變為真。 | (a == b) 為假。 |
| = | 檢查兩個運算元的值是否相等，如果相等，則條件變為真。 | (a = b) 為假。 |
| != | 檢查兩個運算元的值是否相等，如果不相等，則條件變為真。 | (a != b) 為真。 |
| <> | 檢查兩個運算元的值是否相等，如果不相等，則條件變為真。 | (a <> b) 為真。 |
| > | 檢查左運算元的值是否大於右運算元的值，如果是，則條件變為真。 | (a > b) 為假。 |
| < | 檢查左運算元的值是否小於右運算元的值，如果是，則條件變為真。 | (a < b) 為真。 |
| >= | 檢查左運算元的值是否大於或等於右運算元的值，如果是，則條件變為真。 | (a >= b) 為假。 |
| <= | 檢查左運算元的值是否小於或等於右運算元的值，如果是，則條件變為真。 | (a <= b) 為真。 |
| !< | 檢查左運算元的值是否不小於右運算元的值，如果是，則條件變為真。 | (a !< b) 為假。 |
| !> | 檢查左運算元的值是否不大於右運算元的值，如果是，則條件變為真。 | (a !> b) 為真。 |
SQLite 邏輯運算子
以下是 SQLite 中所有可用邏輯運算子的列表。
| 序號 | 運算子和說明 |
|---|---|
| 1 | AND AND 運算子允許 SQL 語句的 WHERE 子句中存在多個條件。 |
| 2 | BETWEEN BETWEEN 運算子用於搜尋在給定最小值和最大值的一組值內的值。 |
| 3 | EXISTS EXISTS 運算子用於搜尋指定表中滿足某些條件的行是否存在。 |
| 4 | IN IN 運算子用於將值與已指定的文字值列表進行比較。 |
| 5 | NOT IN IN 運算子的反義詞，用於將值與已指定的文字值列表進行比較。 |
| 6 | LIKE LIKE 運算子用於使用萬用字元運算子將值與類似的值進行比較。 |
| 7 | GLOB GLOB 運算子用於使用萬用字元運算子將值與類似的值進行比較。此外，與 LIKE 不同，GLOB 區分大小寫。 |
| 8 | NOT NOT 運算子反轉與其一起使用的邏輯運算子的含義。例如 NOT EXISTS、NOT BETWEEN、NOT IN 等。這是否定運算子。 |
| 9 | OR OR 運算子用於在 SQL 語句的 WHERE 子句中組合多個條件。 |
| 10 | IS NULL NULL 運算子用於將值與 NULL 值進行比較。 |
| 11 | IS IS 運算子的工作方式與 = 相同 |
| 12 | IS NOT IS 運算子的工作方式與 != 相同 |
| 13 | || 將兩個不同的字串相加並建立一個新的字串。 |
| 14 | UNIQUE UNIQUE 運算子搜尋指定表中的每一行以查詢唯一性（無重複項）。 |
SQLite 位運算子
位運算子對位進行運算並執行逐位運算。以下是&和|的真值表。
| p | q | p & q | p | q |
|---|---|---|---|
| 0 | 0 | 0 | 0 |
| 0 | 1 | 0 | 1 |
| 1 | 1 | 1 | 1 |
| 1 | 0 | 0 | 1 |
假設A = 60；和B = 13，則以二進位制格式，它們將如下所示：
A = 0011 1100
B = 0000 1101
-----------------
A&B = 0000 1100
A|B = 0011 1101
~A = 1100 0011
SQLite語言支援的位運算子列在下面的表格中。假設變數A的值為60，變數B的值為13，則：
| 運算子 | 描述 | 您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。 |
|---|---|---|
| & | 二進位制與運算子：如果一個位同時存在於兩個運算元中，則將其複製到結果中。 | (A & B) 將得到 12，其二進位制表示為 0000 1100 |
| | | 二進位制或運算子：如果一個位存在於任一運算元中，則將其複製到結果中。 | (A | B) 將得到 61，其二進位制表示為 0011 1101 |
| ~ | 二進位制非運算子：這是一元運算子，其作用是“翻轉”位。 | (~A) 將得到 -61，由於是帶符號二進位制數，因此其二進位制補碼形式為 1100 0011 |
| << | 二進位制左移運算子：左運算元的值向左移動由右運算元指定的位數。 | A << 2 將得到 240，其二進位制表示為 1111 0000 |
| >> | 二進位制右移運算子：左運算元的值向右移動由右運算元指定的位數。 | A >> 2 將得到 15，其二進位制表示為 0000 1111 |
SQLite - 表示式
表示式是由一個或多個值、運算子和SQL函式組合而成，最終計算出一個值。
SQL表示式就像公式一樣，它們是用查詢語言編寫的。你也可以用它來查詢資料庫中的特定資料集。
公元前 4714 年 11 月 24 日中午以來的天數
考慮SELECT語句的基本語法如下：
SELECT column1, column2, columnN FROM table_name WHERE [CONDITION | EXPRESSION];
以下是不同型別的SQLite表示式。
SQLite - 布林表示式
SQLite布林表示式根據匹配單個值來獲取資料。語法如下：
SELECT column1, column2, columnN FROM table_name WHERE SINGLE VALUE MATCHTING EXPRESSION;
考慮具有以下記錄的 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
以下是一個簡單的例子，展示了SQLite布林表示式的用法：
sqlite> SELECT * FROM COMPANY WHERE SALARY = 10000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 James 24 Houston 10000.0
SQLite - 數值表示式
這些表示式用於在任何查詢中執行任何數學運算。語法如下：
SELECT numerical_expression as OPERATION_NAME [FROM table_name WHERE CONDITION] ;
這裡，numerical_expression 用於數學表示式或任何公式。以下是一個簡單的例子，展示了SQLite數值表示式的用法。
sqlite> SELECT (15 + 6) AS ADDITION ADDITION = 21
有一些內建函式，例如avg()、sum()、count()等，用於對錶或特定表列執行所謂的聚合資料計算。
sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY; RECORDS = 7
SQLite - 日期表示式
日期表示式返回當前系統日期和時間值。這些表示式用於各種資料操作。
sqlite> SELECT CURRENT_TIMESTAMP; CURRENT_TIMESTAMP = 2013-03-17 10:43:35
SQLite - WHERE 子句
SQLite 的WHERE子句用於在從一個或多個表中獲取資料時指定條件。
如果給定的條件滿足，即為真，則它將返回表中的特定值。你必須使用WHERE子句來過濾記錄，只獲取必要的記錄。
WHERE子句不僅用於SELECT語句，也用於UPDATE、DELETE語句等，這些將在後續章節中介紹。
公元前 4714 年 11 月 24 日中午以來的天數
以下是帶有WHERE子句的SQLite SELECT語句的基本語法。
SELECT column1, column2, columnN FROM table_name WHERE [condition]
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
你可以使用比較運算子或邏輯運算子，例如>、<、=、LIKE、NOT等來指定條件。考慮具有以下記錄的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
以下是一個簡單的例子，展示了SQLite邏輯運算子的用法。以下SELECT語句列出了所有年齡大於或等於25並且工資大於或等於65000.00的記錄。
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
以下SELECT語句列出了所有年齡大於或等於25或者工資大於或等於65000.00的記錄。
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
以下SELECT語句列出了所有年齡不為NULL的記錄，這意味著所有記錄，因為沒有記錄的年齡等於NULL。
sqlite> SELECT * FROM COMPANY WHERE AGE IS NOT NULL; 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語句列出了所有名稱以“Ki”開頭的記錄，不管“Ki”之後是什麼。
sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%'; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 6 Kim 22 South-Hall 45000.0
以下SELECT語句列出了所有名稱以“Ki”開頭的記錄，不管“Ki”之後是什麼。
sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*'; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 6 Kim 22 South-Hall 45000.0
以下SELECT語句列出了年齡為25或27的所有記錄。
sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 ); ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
以下SELECT語句列出了年齡既不是25也不是27的所有記錄。
sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 ); ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
以下SELECT語句列出了年齡在25到27之間的所有記錄。
sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
以下SELECT語句使用了SQL子查詢，子查詢查詢SALARY > 65000的AGE欄位的所有記錄，然後WHERE子句與EXISTS運算子一起使用，列出外部查詢中的AGE存在於子查詢返回的結果中的所有記錄：
sqlite> SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000); AGE ---------- 32 25 23 25 27 22 24
以下SELECT語句使用了SQL子查詢，子查詢查詢SALARY > 65000的AGE欄位的所有記錄，然後WHERE子句與>運算子一起使用，列出外部查詢中的AGE大於子查詢返回的結果中的年齡的所有記錄。
sqlite> SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000); ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0
SQLite - AND & OR 運算子
SQLite 的AND & OR 運算子用於組合多個條件，以縮小SQLite語句中選擇的資料範圍。這兩個運算子被稱為連線運算子。
這些運算子提供了一種方法，可以在同一個SQLite語句中使用不同的運算子進行多個比較。
AND 運算子
AND運算子允許在SQLite語句的WHERE子句中存在多個條件。使用AND運算子時，只有當所有條件都為真時，整個條件才被認為是真。例如，[condition1] AND [condition2] 只有當condition1和condition2都為真時才為真。
公元前 4714 年 11 月 24 日中午以來的天數
以下是帶有WHERE子句的AND運算子的基本語法。
SELECT column1, column2, columnN FROM table_name WHERE [condition1] AND [condition2]...AND [conditionN];
你可以使用AND運算子組合N個條件。對於SQLite語句要執行的操作，無論是事務還是查詢，所有由AND分隔的條件都必須為TRUE。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮具有以下記錄的 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語句列出了所有年齡大於或等於25並且工資大於或等於65000.00的記錄。
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
OR 運算子
OR運算子也用於在SQLite語句的WHERE子句中組合多個條件。使用OR運算子時，只要至少有一個條件為真，整個條件就被認為是真。例如，[condition1] OR [condition2] 如果condition1或condition2為真，則為真。
公元前 4714 年 11 月 24 日中午以來的天數
以下是帶有WHERE子句的OR運算子的基本語法。
SELECT column1, column2, columnN FROM table_name WHERE [condition1] OR [condition2]...OR [conditionN]
你可以使用OR運算子組合N個條件。對於SQLite語句要執行的操作，無論是事務還是查詢，只有任何一個由OR分隔的條件必須為TRUE。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮具有以下記錄的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語句列出了所有年齡大於或等於25或者工資大於或等於65000.00的記錄。
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
SQLite - UPDATE 查詢
SQLite 的UPDATE查詢用於修改表中現有的記錄。你可以將WHERE子句與UPDATE查詢一起使用以更新選定的行，否則將更新所有行。
公元前 4714 年 11 月 24 日中午以來的天數
以下是帶有WHERE子句的UPDATE查詢的基本語法。
UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];
你可以使用AND或OR運算子組合N個條件。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮具有以下記錄的 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
以下是一個例子，它將更新ID為6的客戶的地址。
sqlite> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
現在，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 Texas 45000.0 7 James 24 Houston 10000.0
如果你想修改COMPANY表中所有ADDRESS和SALARY列的值，則不需要使用WHERE子句，UPDATE查詢將如下所示：
sqlite> UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00;
現在，COMPANY表將具有以下記錄：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 Texas 20000.0 2 Allen 25 Texas 20000.0 3 Teddy 23 Texas 20000.0 4 Mark 25 Texas 20000.0 5 David 27 Texas 20000.0 6 Kim 22 Texas 20000.0 7 James 24 Texas 20000.0
SQLite - DELETE 查詢
SQLite 的DELETE查詢用於從表中刪除現有記錄。你可以將WHERE子句與DELETE查詢一起使用以刪除選定的行，否則將刪除所有記錄。
公元前 4714 年 11 月 24 日中午以來的天數
以下是帶有WHERE子句的DELETE查詢的基本語法。
DELETE FROM table_name WHERE [condition];
你可以使用AND或OR運算子組合N個條件。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮具有以下記錄的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
以下是一個例子，它將刪除ID為7的客戶。
sqlite> DELETE FROM COMPANY WHERE ID = 7;
現在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
如果你想從COMPANY表中刪除所有記錄，則不需要在DELETE查詢中使用WHERE子句，它將如下所示：
sqlite> DELETE FROM COMPANY;
現在，COMPANY表沒有任何記錄，因為所有記錄都已被DELETE語句刪除。
SQLite - LIKE 子句
SQLite 的LIKE運算子用於使用萬用字元將文字值與模式匹配。如果搜尋表示式可以與模式表示式匹配，則LIKE運算子將返回true，即1。與LIKE運算子一起使用的萬用字元有兩個：
- 百分號 (%)
- 下劃線 (_)
百分號代表零個、一個或多個數字或字元。下劃線代表單個數字或字元。這些符號可以組合使用。
公元前 4714 年 11 月 24 日中午以來的天數
以下是%和_的基本語法。
SELECT FROM table_name WHERE column LIKE 'XXXX%' or SELECT FROM table_name WHERE column LIKE '%XXXX%' or SELECT FROM table_name WHERE column LIKE 'XXXX_' or SELECT FROM table_name WHERE column LIKE '_XXXX' or SELECT FROM table_name WHERE column LIKE '_XXXX_'
你可以使用AND或OR運算子組合N個條件。這裡，XXXX可以是任何數值或字串值。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
下表列出了幾個示例，這些示例顯示了WHERE部分具有使用'%'和'_'運算子的不同LIKE子句。
| 序號 | 語句 & 說明 |
|---|---|
| 1 | WHERE SALARY LIKE '200%' 查詢以200開頭的任何值 |
| 2 | WHERE SALARY LIKE '%200%' 查詢任何位置包含200的任何值 |
| 3 | WHERE SALARY LIKE '_00%' 查詢在第二位和第三位有00的任何值 |
| 4 | WHERE SALARY LIKE '2_%_%' 查詢以2開頭且至少有3個字元的任何值 |
| 5 | WHERE SALARY LIKE '%2' 查詢以2結尾的任何值 |
| 6 | WHERE SALARY LIKE '_2%3' 查詢第二位有2且以3結尾的任何值 |
| 7 | WHERE SALARY LIKE '2___3' 查詢五位數中以2開頭並以3結尾的任何值 |
讓我們來看一個真實的例子，考慮具有以下記錄的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
以下是一個例子，它將顯示COMPANY表中AGE以2開頭的所有記錄。
sqlite> SELECT * FROM COMPANY WHERE AGE LIKE '2%';
這將產生以下結果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 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
以下是一個例子，它將顯示COMPANY表中ADDRESS包含連字元(-)的所有記錄。
sqlite> SELECT * FROM COMPANY WHERE ADDRESS LIKE '%-%';
這將產生以下結果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 6 Kim 22 South-Hall 45000.0
SQLite - GLOB 子句
SQLite 的GLOB運算子用於僅使用萬用字元將文字值與模式匹配。如果搜尋表示式可以與模式表示式匹配，則GLOB運算子將返回true，即1。與LIKE運算子不同，GLOB區分大小寫，並且遵循UNIX的語法來指定以下萬用字元。
- 星號 (*)
- 問號 (?)
星號 (*) 代表零個或多個數字或字元。問號 (?) 代表單個數字或字元。
公元前 4714 年 11 月 24 日中午以來的天數
以下是* 和? 的基本語法。
SELECT FROM table_name WHERE column GLOB 'XXXX*' or SELECT FROM table_name WHERE column GLOB '*XXXX*' or SELECT FROM table_name WHERE column GLOB 'XXXX?' or SELECT FROM table_name WHERE column GLOB '?XXXX' or SELECT FROM table_name WHERE column GLOB '?XXXX?' or SELECT FROM table_name WHERE column GLOB '????'
你可以使用AND或OR運算子組合N個條件。這裡，XXXX可以是任何數值或字串值。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
下表列出了一些示例，這些示例顯示了 WHERE 部分具有使用 '*' 和 '?' 運算子的不同 LIKE 子句。
| 序號 | 語句 & 說明 |
|---|---|
| 1 | WHERE SALARY GLOB '200*' 查詢以200開頭的任何值 |
| 2 | WHERE SALARY GLOB '*200*' 查詢任何位置包含200的任何值 |
| 3 | WHERE SALARY GLOB '?00*' 查詢在第二位和第三位有00的任何值 |
| 4 | WHERE SALARY GLOB '2??' 查詢以2開頭且至少有3個字元的任何值 |
| 5 | WHERE SALARY GLOB '*2' 查詢以2結尾的任何值 |
| 6 | WHERE SALARY GLOB '?2*3' 查詢任何在第二位是 2 且以 3 結尾的值 |
| 7 | WHERE SALARY GLOB '2???3' 查詢任何以 2 開頭並以 3 結尾的五位數 |
讓我們來看一個真實的例子，考慮一個包含以下記錄的 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
以下是一個示例，它將顯示 COMPANY 表中 AGE 以 2 開頭的所有記錄。
sqlite> SELECT * FROM COMPANY WHERE AGE GLOB '2*';
這將產生以下結果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 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
以下是一個示例，它將顯示 COMPANY 表中 ADDRESS 包含連字元 (-) 的所有記錄：
sqlite> SELECT * FROM COMPANY WHERE ADDRESS GLOB '*-*';
這將產生以下結果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 6 Kim 22 South-Hall 45000.0
SQLite - LIMIT 子句
SQLite 的LIMIT 子句用於限制 SELECT 語句返回的資料量。
公元前 4714 年 11 月 24 日中午以來的天數
以下是包含 LIMIT 子句的 SELECT 語句的基本語法。
SELECT column1, column2, columnN FROM table_name LIMIT [no of rows]
以下是 LIMIT 子句與 OFFSET 子句一起使用時的語法。
SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num]
SQLite 引擎將從給定 OFFSET 的下一行開始返回行，如下面的最後一個示例所示。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮具有以下記錄的 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
以下是一個示例，它根據您想要從表中提取的行數來限制表中的行。
sqlite> SELECT * FROM COMPANY LIMIT 6;
這將產生以下結果。
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
但是，在某些情況下，您可能需要從特定偏移量處提取一組記錄。這是一個示例，它從第 3 個位置開始提取 3 條記錄。
sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2;
這將產生以下結果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
SQLite - ORDER BY 子句
SQLite 的ORDER BY 子句用於根據一個或多個列對資料進行升序或降序排序。
公元前 4714 年 11 月 24 日中午以來的天數
以下是 ORDER BY 子句的基本語法。
SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];
您可以在 ORDER BY 子句中使用多個列。確保您用於排序的任何列都應在列列表中可用。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮具有以下記錄的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
以下是一個示例，它將結果按 SALARY 降序排序。
sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC;
這將產生以下結果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 7 James 24 Houston 10000.0 2 Allen 25 Texas 15000.0 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 6 Kim 22 South-Hall 45000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
以下是一個示例，它將結果按 NAME 和 SALARY 降序排序。
sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;
這將產生以下結果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 5 David 27 Texas 85000.0 7 James 24 Houston 10000.0 6 Kim 22 South-Hall 45000.0 4 Mark 25 Rich-Mond 65000.0 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0
以下是一個示例，它將結果按 NAME 降序排序。
sqlite> SELECT * FROM COMPANY ORDER BY NAME DESC;
這將產生以下結果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 3 Teddy 23 Norway 20000.0 1 Paul 32 California 20000.0 4 Mark 25 Rich-Mond 65000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 5 David 27 Texas 85000.0 2 Allen 25 Texas 15000.0
SQLite - GROUP BY 子句
SQLite 的GROUP BY 子句與 SELECT 語句一起使用，用於將相同的資料排列成組。
GROUP BY 子句位於 SELECT 語句中的 WHERE 子句之後，ORDER BY 子句之前。
公元前 4714 年 11 月 24 日中午以來的天數
以下是 GROUP BY 子句的基本語法。GROUP BY 子句必須遵循 WHERE 子句中的條件，如果使用 ORDER BY 子句，則必須位於其之前。
SELECT column-list FROM table_name WHERE [ conditions ] GROUP BY column1, column2....columnN ORDER BY column1, column2....columnN
您可以在 GROUP BY 子句中使用多個列。確保您用於分組的任何列都應在列列表中可用。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮具有以下記錄的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
如果您想知道每個客戶的工資總額，那麼 GROUP BY 查詢如下：
sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
這將產生以下結果：
NAME SUM(SALARY) ---------- ----------- Allen 15000.0 David 85000.0 James 10000.0 Kim 45000.0 Mark 65000.0 Paul 20000.0 Teddy 20000.0
現在，讓我們使用以下 INSERT 語句在 COMPANY 表中建立三個更多記錄。
INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00 ); INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00 ); INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00 );
現在，我們的表包含以下具有重複名稱的記錄。
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 8 Paul 24 Houston 20000.0 9 James 44 Norway 5000.0 10 James 45 Texas 5000.0
再次，讓我們使用相同的語句，使用 NAME 列對所有記錄進行分組，如下所示：
sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;
這將產生以下結果。
NAME SUM(SALARY) ---------- ----------- Allen 15000 David 85000 James 20000 Kim 45000 Mark 65000 Paul 40000 Teddy 20000
讓我們結合使用 ORDER BY 子句和 GROUP BY 子句，如下所示：
sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;
這將產生以下結果。
NAME SUM(SALARY) ---------- ----------- Teddy 20000 Paul 40000 Mark 65000 Kim 45000 James 20000 David 85000 Allen 15000
SQLite - HAVING 子句
HAVING 子句使您可以指定條件，以篩選最終結果中顯示的組結果。
WHERE 子句對選定的列設定條件，而 HAVING 子句對 GROUP BY 子句建立的組設定條件。
公元前 4714 年 11 月 24 日中午以來的天數
以下是 HAVING 子句在 SELECT 查詢中的位置。
SELECT FROM WHERE GROUP BY HAVING ORDER BY
HAVING 子句必須位於查詢中的 GROUP BY 子句之後，如果使用 ORDER BY 子句，也必須位於其之前。以下是包含 HAVING 子句的 SELECT 語句的語法。
SELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮具有以下記錄的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 8 Paul 24 Houston 20000.0 9 James 44 Norway 5000.0 10 James 45 Texas 5000.0
以下是一個示例，它將顯示名稱計數少於 2 的記錄。
sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;
這將產生以下結果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000 5 David 27 Texas 85000 6 Kim 22 South-Hall 45000 4 Mark 25 Rich-Mond 65000 3 Teddy 23 Norway 20000
以下是一個示例，它將顯示名稱計數大於 2 的記錄。
sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;
這將產生以下結果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 10 James 45 Texas 5000
SQLite - DISTINCT 關鍵字
SQLite 的DISTINCT 關鍵字與 SELECT 語句一起使用，用於消除所有重複記錄並僅獲取唯一記錄。
您可能遇到表中有多個重複記錄的情況。在提取此類記錄時，提取唯一記錄比提取重複記錄更有意義。
公元前 4714 年 11 月 24 日中午以來的天數
以下是用於消除重複記錄的 DISTINCT 關鍵字的基本語法。
SELECT DISTINCT column1, column2,.....columnN FROM table_name WHERE [condition]
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮具有以下記錄的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 8 Paul 24 Houston 20000.0 9 James 44 Norway 5000.0 10 James 45 Texas 5000.0
首先，讓我們看看以下 SELECT 查詢如何返回重複的工資記錄。
sqlite> SELECT name FROM COMPANY;
這將產生以下結果。
NAME ---------- Paul Allen Teddy Mark David Kim James Paul James James
現在，讓我們在上面的 SELECT 查詢中使用DISTINCT 關鍵字，並檢視結果。
sqlite> SELECT DISTINCT name FROM COMPANY;
這將產生以下結果，其中沒有重複條目。
NAME ---------- Paul Allen Teddy Mark David Kim James
SQLite - PRAGMA
SQLite 的PRAGMA 命令是一個特殊命令，用於控制 SQLite 環境中的各種環境變數和狀態標誌。可以讀取 PRAGMA 值，也可以根據需要設定 PRAGMA 值。
公元前 4714 年 11 月 24 日中午以來的天數
要查詢當前的 PRAGMA 值，只需提供 pragma 的名稱。
PRAGMA pragma_name;
要為 PRAGMA 設定新值，請使用以下語法。
PRAGMA pragma_name = value;
設定模式可以是名稱或整數等效項，但返回值始終為整數。
auto_vacuum Pragma
auto_vacuum pragma 獲取或設定自動 vacuum 模式。以下是簡單的語法。
PRAGMA [database.]auto_vacuum; PRAGMA [database.]auto_vacuum = mode;
其中mode 可以是以下任何一個：
| 序號 | Pragma 值和說明 |
|---|---|
| 1 | 0 或 NONE 停用自動 vacuum。這是預設模式，這意味著資料庫檔案的大小永遠不會縮小，除非使用 VACUUM 命令手動 vacuum。 |
| 2 | 1 或 FULL 啟用自動 vacuum 且完全自動，允許資料庫檔案在資料從資料庫中刪除時縮小。 |
| 3 | 2 或 INCREMENTAL 啟用自動 vacuum，但必須手動啟用。在此模式下，將維護參考資料，但空閒頁面將簡單地放在空閒列表中。這些頁面可以使用incremental_vacuum pragma隨時恢復。 |
cache_size Pragma
cache_size pragma 可以獲取或臨時設定記憶體中頁面快取的最大大小。以下是簡單的語法。
PRAGMA [database.]cache_size; PRAGMA [database.]cache_size = pages;
pages 值表示快取中的頁面數。內建頁面快取的預設大小為 2000 個頁面，最小大小為 10 個頁面。
case_sensitive_like Pragma
case_sensitive_like pragma 控制內建 LIKE 表示式的區分大小寫。預設情況下，此 pragma 為 false，這意味著內建 LIKE 運算子會忽略字母大小寫。以下是簡單的語法。
PRAGMA case_sensitive_like = [true|false];
無法查詢此 pragma 的當前狀態。
count_changes Pragma
count_changes pragma 獲取或設定資料操作語句（如 INSERT、UPDATE 和 DELETE）的返回值。以下是簡單的語法。
PRAGMA count_changes; PRAGMA count_changes = [true|false];
預設情況下，此 pragma 為 false，這些語句不返回任何內容。如果設定為 true，則每個提到的語句都將返回一個單列、單行表，其中包含一個整數數值，表示操作影響的行數。
database_list Pragma
database_list pragma 用於列出所有附加的資料庫。以下是簡單的語法。
PRAGMA database_list;
此 pragma 將返回一個三列表，每行一個開啟或附加的資料庫，給出資料庫序列號、其名稱和關聯的檔案。
encoding Pragma
encoding pragma 控制字串如何在資料庫檔案中編碼和儲存。以下是簡單的語法。
PRAGMA encoding; PRAGMA encoding = format;
format 值可以是UTF-8、UTF-16le 或UTF-16be 之一。
freelist_count Pragma
freelist_count pragma 返回一個整數，指示當前有多少資料庫頁面被標記為空閒且可用。以下是簡單的語法。
PRAGMA [database.]freelist_count;
format 值可以是UTF-8、UTF-16le 或UTF-16be 之一。
index_info Pragma
index_info pragma 返回有關資料庫索引的資訊。以下是簡單的語法。
PRAGMA [database.]index_info( index_name );
結果集將為索引中包含的每一列包含一行，給出列序列、表內的列索引和列名。
index_list Pragma
index_list pragma 列出與表關聯的所有索引。以下是簡單的語法。
PRAGMA [database.]index_list( table_name );
結果集將為每個索引包含一行，給出索引序列、索引名稱和指示索引是否唯一的標誌。
journal_mode Pragma
journal_mode pragma 獲取或設定日誌模式，該模式控制日誌檔案的儲存和處理方式。以下是簡單的語法。
PRAGMA journal_mode; PRAGMA journal_mode = mode; PRAGMA database.journal_mode; PRAGMA database.journal_mode = mode;
下表列出了五種受支援的日誌模式。
| 序號 | Pragma 值和說明 |
|---|---|
| 1 | DELETE 這是預設模式。在此模式下，事務結束時，將刪除日誌檔案。 |
| 2 | TRUNCATE 日誌檔案將被截斷為零位元組長度。 |
| 3 | PERSIST 日誌檔案保留在原處，但標頭將被覆蓋以指示日誌不再有效。 |
| 4 | MEMORY 日誌記錄儲存在記憶體中，而不是磁碟上。 |
| 5 | OFF 不保留日誌記錄。 |
max_page_count Pragma
max_page_count pragma 獲取或設定資料庫允許的最大頁面數。以下是簡單的語法。
PRAGMA [database.]max_page_count; PRAGMA [database.]max_page_count = max_page;
預設值為 1,073,741,823，即十億頁，這意味著如果預設頁面大小為 1 KB，則允許資料庫增長到一太位元組。
page_count Pragma
page_count pragma 返回資料庫中當前的頁面數。以下是簡單的語法：
PRAGMA [database.]page_count;
資料庫檔案的大小應為 page_count * page_size。
page_size Pragma
page_size pragma 獲取或設定資料庫頁面的大小。以下是簡單的語法。
PRAGMA [database.]page_size; PRAGMA [database.]page_size = bytes;
預設情況下，允許的大小為 512、1024、2048、4096、8192、16384 和 32768 位元組。更改現有資料庫頁面大小的唯一方法是設定頁面大小，然後立即 VACUUM 資料庫。
parser_trace Pragma
parser_trace pragma 控制在解析 SQL 命令時列印除錯狀態。以下是簡單的語法。
PRAGMA parser_trace = [true|false];
預設情況下，它設定為 false，但如果透過將其設定為 true 來啟用它，則 SQL 解析器將在解析 SQL 命令時列印其狀態。
recursive_triggers Pragma
recursive_triggers pragma 獲取或設定遞迴觸發器功能。如果未啟用遞迴觸發器，則觸發器操作不會觸發另一個觸發器。以下是簡單的語法。
PRAGMA recursive_triggers; PRAGMA recursive_triggers = [true|false];
schema_version Pragma
schema_version pragma 獲取或設定儲存在資料庫標頭中的模式版本值。以下是簡單的語法。
PRAGMA [database.]schema_version; PRAGMA [database.]schema_version = number;
這是一個 32 位有符號整數，用於跟蹤模式更改。每當執行更改模式的命令（例如，CREATE... 或 DROP...）時，此值都會遞增。
secure_delete Pragma
secure_delete pragma 用於控制如何從資料庫中刪除內容。以下是簡單的語法。
PRAGMA secure_delete; PRAGMA secure_delete = [true|false]; PRAGMA database.secure_delete; PRAGMA database.secure_delete = [true|false];
安全刪除標誌的預設值通常為關閉，但這可以透過 SQLITE_SECURE_DELETE 構建選項更改。
sql_trace Pragma
sql_trace pragma 用於將 SQL 跟蹤結果轉儲到螢幕上。以下是簡單的語法。
PRAGMA sql_trace; PRAGMA sql_trace = [true|false];
SQLite 必須使用 SQLITE_DEBUG 指令編譯才能包含此 pragma。
synchronous Pragma
同步 pragma 獲取或設定當前磁碟同步模式，該模式控制 SQLite 將資料寫入物理儲存的積極程度。以下是簡單的語法。
PRAGMA [database.]synchronous; PRAGMA [database.]synchronous = mode;
SQLite 支援下表列出的同步模式。
| 序號 | Pragma 值和說明 |
|---|---|
| 1 | 0 或 OFF 完全不同步 |
| 2 | 1 或 NORMAL 在每次關鍵磁碟操作序列後同步 |
| 3 | 2 或 FULL 在每次關鍵磁碟操作後同步 |
temp_store Pragma
temp_store pragma 獲取或設定臨時資料庫檔案使用的儲存模式。以下是簡單的語法。
PRAGMA temp_store; PRAGMA temp_store = mode;
SQLite 支援以下儲存模式。
| 序號 | Pragma 值和說明 |
|---|---|
| 1 | 0 或 DEFAULT 使用編譯時預設值。通常為 FILE。 |
| 2 | 1 或 FILE 使用基於檔案的儲存。 |
| 3 | 2 或 MEMORY 使用基於記憶體的儲存。 |
temp_store_directory Pragma
temp_store_directory pragma 獲取或設定臨時資料庫檔案使用的位置。以下是簡單的語法。
PRAGMA temp_store_directory; PRAGMA temp_store_directory = 'directory_path';
user_version Pragma
user_version pragma 獲取或設定儲存在資料庫頭中的使用者定義版本值。以下是簡單的語法。
PRAGMA [database.]user_version; PRAGMA [database.]user_version = number;
這是一個 32 位有符號整數，開發人員可以將其設定為版本跟蹤目的。
writable_schema Pragma
writable_schema pragma 獲取或設定修改系統表的能力。以下是簡單的語法。
PRAGMA writable_schema; PRAGMA writable_schema = [true|false];
如果設定了此 pragma，則可以建立和修改以 sqlite_ 開頭的表，包括 sqlite_master 表。使用 pragma 時要小心，因為它可能導致資料庫完全損壞。
SQLite - 約束
約束是對錶中資料列強制執行的規則。這些規則用於限制可以進入表的資料型別。這確保了資料庫中資料的準確性和可靠性。
約束可以是列級或表級。列級約束僅應用於一列，而表級約束應用於整個表。
以下是 SQLite 中常用的約束。
NOT NULL 約束 - 確保列不能具有 NULL 值。
DEFAULT 約束 - 在未指定值時為列提供預設值。
UNIQUE 約束 - 確保列中的所有值都不同。
PRIMARY KEY - 唯一標識資料庫表中的每一行/記錄。
CHECK 約束 - 確保列中的所有值都滿足某些條件。
NOT NULL 約束
預設情況下，列可以包含 NULL 值。如果您不希望列具有 NULL 值，則需要在此列上定義此約束，指定該列現在不允許 NULL。
NULL 與沒有資料不同，而是表示未知資料。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
例如，以下 SQLite 語句建立一個名為 COMPANY 的新表並新增五列，其中三列 ID、NAME 和 AGE 指定不接受 NULL。
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
DEFAULT 約束
DEFAULT 約束在 INSERT INTO 語句未提供特定值時為列提供預設值。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
例如，以下 SQLite 語句建立一個名為 COMPANY 的新表並新增五列。這裡，SALARY 列預設設定為 5000.00，因此，如果 INSERT INTO 語句未為此列提供值，則此列將預設設定為 5000.00。
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL DEFAULT 50000.00 );
UNIQUE 約束
UNIQUE 約束可以防止兩條記錄在特定列中具有相同的值。例如，在 COMPANY 表中，您可能希望防止兩個人擁有相同的年齡。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
例如，以下 SQLite 語句建立一個名為 COMPANY 的新表並新增五列。這裡，AGE 列設定為 UNIQUE，因此您不能有兩條具有相同年齡的記錄 -
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL UNIQUE, ADDRESS CHAR(50), SALARY REAL DEFAULT 50000.00 );
PRIMARY KEY 約束
PRIMARY KEY 約束唯一標識資料庫表中的每一行記錄。可以有多個 UNIQUE 列，但表中只有一個主鍵。設計資料庫表時，主鍵非常重要。主鍵是唯一的 ID。
我們使用它們來引用錶行。在建立表之間的關係時，主鍵在其他表中成為外部索引鍵。由於“長期存在的編碼疏忽”，主鍵在 SQLite 中可以為 NULL。其他資料庫並非如此。
主鍵是表中一個欄位，它唯一標識資料庫表中的每一行/記錄。主鍵必須包含唯一值。主鍵列不能具有 NULL 值。
一個表只能有一個主鍵，它可以由單個或多個欄位組成。當多個欄位用作主鍵時，它們稱為組合鍵。
如果表在任何欄位上定義了主鍵，則您不能有兩條記錄具有該欄位的相同值。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
您已經在上面的各種示例中看到我們建立了以 ID 作為主鍵的 COMPANY 表。
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
CHECK 約束
CHECK 約束啟用一個條件來檢查輸入記錄的值。如果條件計算結果為假，則記錄違反約束，不會輸入到表中。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
例如，以下 SQLite 建立一個名為 COMPANY 的新表並新增五列。這裡，我們在 SALARY 列新增一個 CHECK，這樣您就不能有任何為零的 SALARY。
CREATE TABLE COMPANY3( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL CHECK(SALARY > 0) );
刪除約束
SQLite 支援 ALTER TABLE 的有限子集。SQLite 中的 ALTER TABLE 命令允許使用者重命名錶或向現有表新增新列。無法重新命名列、刪除列或向表新增或刪除約束。
SQLite - 連線 (JOINS)
SQLite 的連線子句用於組合資料庫中兩個或多個表中的記錄。連線是一種透過使用每個表共有的值來組合兩個表中的欄位的方法。
SQL 定義了三種主要的連線型別 -
- 交叉連線 (CROSS JOIN)
- 內連線 (INNER JOIN)
- 外連線 (OUTER JOIN)
在我們繼續之前，讓我們考慮兩個表 COMPANY 和 DEPARTMENT。我們已經看到用於填充 COMPANY 表的 INSERT 語句。所以讓我們假設 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
另一個表是 DEPARTMENT，其定義如下 -
CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL );
以下是用於填充 DEPARTMENT 表的 INSERT 語句列表 -
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );
最後，DEPARTMENT 表中可用的記錄列表如下 -
ID DEPT EMP_ID ---------- ---------- ---------- 1 IT Billing 1 2 Engineering 2 3 Finance 7
交叉連線 (CROSS JOIN)
CROSS JOIN 將第一個表的每一行與第二個表的每一行匹配。如果輸入表分別具有 x 和 y 行，則結果表將具有 x*y 行。由於 CROSS JOIN 有可能生成非常大的表，因此必須小心謹慎，僅在適當的時候使用它們。
以下是 CROSS JOIN 的語法 -
SELECT ... FROM table1 CROSS JOIN table2 ...
基於上面的表，您可以編寫 CROSS JOIN 如下 -
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
上面的查詢將產生以下結果 -
EMP_ID NAME DEPT ---------- ---------- ---------- 1 Paul IT Billing 2 Paul Engineering 7 Paul Finance 1 Allen IT Billing 2 Allen Engineering 7 Allen Finance 1 Teddy IT Billing 2 Teddy Engineering 7 Teddy Finance 1 Mark IT Billing 2 Mark Engineering 7 Mark Finance 1 David IT Billing 2 David Engineering 7 David Finance 1 Kim IT Billing 2 Kim Engineering 7 Kim Finance 1 James IT Billing 2 James Engineering 7 James Finance
內連線 (INNER JOIN)
INNER JOIN 透過根據連線謂詞組合兩個表（table1 和 table2）的列值來建立一個新的結果表。查詢比較 table1 的每一行與 table2 的每一行，以查詢滿足連線謂詞的所有行對。當滿足連線謂詞時，A 和 B 的每一對匹配行的列值將組合到結果行中。
INNER JOIN 是最常見且預設的連線型別。您可以選擇使用 INNER 關鍵字。
以下是 INNER JOIN 的語法 -
SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...
為了避免冗餘並使措辭更簡短，可以使用USING表示式宣告 INNER JOIN 條件。此表示式指定一個或多個列的列表。
SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...
NATURAL JOIN 類似於JOIN...USING，只是它會自動測試兩個表中每個都存在的列的值是否相等 -
SELECT ... FROM table1 NATURAL JOIN table2...
基於上面的表，您可以編寫 INNER JOIN 如下 -
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
上面的查詢將產生以下結果 -
EMP_ID NAME DEPT ---------- ---------- ---------- 1 Paul IT Billing 2 Allen Engineering 7 James Finance
外連線 (OUTER JOIN)
OUTER JOIN 是 INNER JOIN 的擴充套件。儘管 SQL 標準定義了三種類型的 OUTER JOIN：LEFT、RIGHT 和 FULL，但 SQLite 只支援LEFT OUTER JOIN。
OUTER JOIN 具有與 INNER JOIN 相同的條件，使用 ON、USING 或 NATURAL 關鍵字表示。初始結果表以相同的方式計算。一旦計算出主 JOIN，OUTER JOIN 將獲取一個或兩個表中的任何未連線的行，用 NULL 填充它們，並將它們附加到結果表。
以下是 LEFT OUTER JOIN 的語法 -
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
為了避免冗餘並使措辭更簡短，可以使用 USING 表示式宣告 OUTER JOIN 條件。此表示式指定一個或多個列的列表。
SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...
基於上面的表，您可以編寫外連線如下 -
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
上面的查詢將產生以下結果 -
EMP_ID NAME DEPT
---------- ---------- ----------
1 Paul IT Billing
2 Allen Engineering
Teddy
Mark
David
Kim
7 James Finance
SQLite - UNION 子句
SQLite 的UNION子句/運算子用於組合兩個或多個 SELECT 語句的結果，而不返回任何重複的行。
要使用 UNION，每個 SELECT 必須具有相同數量的列、相同數量的列表達式、相同的資料型別，並且順序相同，但它們不必具有相同的長度。
公元前 4714 年 11 月 24 日中午以來的天數
以下是UNION的基本語法。
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
這裡給定的條件可以是基於您需求的任何給定表示式。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮以下兩個表，(a) COMPANY 表如下 -
sqlite> select * from 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
(b) 另一個表是 DEPARTMENT 表如下 -
ID DEPT EMP_ID ---------- -------------------- ---------- 1 IT Billing 1 2 Engineering 2 3 Finance 7 4 Engineering 3 5 Finance 4 6 Engineering 5 7 Finance 6
現在讓我們使用 SELECT 語句以及 UNION 子句將這兩個表連線起來，如下所示 -
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
這將產生以下結果。
EMP_ID NAME DEPT ---------- -------------------- ---------- 1 Paul IT Billing 2 Allen Engineering 3 Teddy Engineering 4 Mark Finance 5 David Engineering 6 Kim Finance 7 James Finance
UNION ALL 子句
UNION ALL 運算子用於組合兩個 SELECT 語句的結果，包括重複的行。
應用於 UNION 的相同規則也適用於 UNION ALL 運算子。
公元前 4714 年 11 月 24 日中午以來的天數
以下是UNION ALL的基本語法。
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION ALL SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
這裡給定的條件可以是基於您需求的任何給定表示式。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
現在，讓我們在 SELECT 語句中連線上述兩個表，如下所示 -
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION ALL
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
這將產生以下結果。
EMP_ID NAME DEPT ---------- -------------------- ---------- 1 Paul IT Billing 2 Allen Engineering 3 Teddy Engineering 4 Mark Finance 5 David Engineering 6 Kim Finance 7 James Finance 1 Paul IT Billing 2 Allen Engineering 3 Teddy Engineering 4 Mark Finance 5 David Engineering 6 Kim Finance 7 James Finance
SQLite - NULL 值
SQLite 的NULL用於表示缺失值。表中的 NULL 值是指欄位中看起來為空的值。
具有 NULL 值的欄位是沒有值的欄位。務必理解，NULL 值不同於零值或包含空格的欄位。
公元前 4714 年 11 月 24 日中午以來的天數
以下是建立表時使用NULL的基本語法。
SQLite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
這裡，NOT NULL表示該列應始終接受給定資料型別的顯式值。有兩列我們沒有使用 NOT NULL，這意味著這些列可以為 NULL。
具有 NULL 值的欄位是在記錄建立期間留空的值。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
NULL 值在選擇資料時可能會導致問題，因為當將未知值與任何其他值進行比較時，結果始終是未知的，並且不包含在最終結果中。考慮以下表格，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
讓我們使用 UPDATE 語句將一些可為空的值設定為 NULL，如下所示：
sqlite> UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);
現在，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 7 James 24
接下來，讓我們看看IS NOT NULL運算子的用法，以列出 SALARY 不為 NULL 的所有記錄。
sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM COMPANY
WHERE SALARY IS NOT NULL;
上面的 SQLite 語句將產生以下結果：
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
以下是IS NULL運算子的用法，它將列出 SALARY 為 NULL 的所有記錄。
sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM COMPANY
WHERE SALARY IS NULL;
上面的 SQLite 語句將產生以下結果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 6 Kim 22 7 James 24
SQLite - 別名語法 (ALIAS Syntax)
您可以透過給表或列另一個名稱來臨時重新命名它們，這稱為別名 (ALIAS)。使用表別名意味著在一個特定的 SQLite 語句中重新命名一個表。重新命名是一個臨時更改，實際的表名不會在資料庫中更改。
列別名用於為了特定 SQLite 查詢的目的而重命名錶的列。
公元前 4714 年 11 月 24 日中午以來的天數
以下是表 (table)別名的基本語法。
SELECT column1, column2.... FROM table_name AS alias_name WHERE [condition];
以下是列 (column)別名的基本語法。
SELECT column_name AS alias_name FROM table_name WHERE [condition];
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮以下兩個表，(a) COMPANY 表如下所示：
sqlite> select * from 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
(b) 另一個表是 DEPARTMENT 表如下 -
ID DEPT EMP_ID ---------- -------------------- ---------- 1 IT Billing 1 2 Engineering 2 3 Finance 7 4 Engineering 3 5 Finance 4 6 Engineering 5 7 Finance 6
現在，以下是表別名 (TABLE ALIAS)的用法，我們使用 C 和 D 分別作為 COMPANY 和 DEPARTMENT 表的別名：
sqlite> SELECT C.ID, C.NAME, C.AGE, D.DEPT
FROM COMPANY AS C, DEPARTMENT AS D
WHERE C.ID = D.EMP_ID;
上面的 SQLite 語句將產生以下結果：
ID NAME AGE DEPT ---------- ---------- ---------- ---------- 1 Paul 32 IT Billing 2 Allen 25 Engineering 3 Teddy 23 Engineering 4 Mark 25 Finance 5 David 27 Engineering 6 Kim 22 Finance 7 James 24 Finance
考慮一個列別名 (COLUMN ALIAS)用法的示例，其中 COMPANY_ID 是 ID 列的別名，COMPANY_NAME 是 name 列的別名。
sqlite> SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT
FROM COMPANY AS C, DEPARTMENT AS D
WHERE C.ID = D.EMP_ID;
上面的 SQLite 語句將產生以下結果：
COMPANY_ID COMPANY_NAME AGE DEPT ---------- ------------ ---------- ---------- 1 Paul 32 IT Billing 2 Allen 25 Engineering 3 Teddy 23 Engineering 4 Mark 25 Finance 5 David 27 Engineering 6 Kim 22 Finance 7 James 24 Finance
SQLite - 觸發器 (Triggers)
SQLite觸發器 (Triggers)是資料庫回撥函式，當指定的資料庫事件發生時會自動執行/呼叫。以下是關於 SQLite 觸發器的重要幾點：
可以指定 SQLite 觸發器在特定資料庫表的 DELETE、INSERT 或 UPDATE 發生時，或者在表的指定列之一或多個列上發生 UPDATE 時觸發。
目前，SQLite 只支援 FOR EACH ROW 觸發器，不支援 FOR EACH STATEMENT 觸發器。因此，顯式指定 FOR EACH ROW 是可選的。
WHEN 子句和觸發器操作都可以訪問正在插入、刪除或更新的行中的元素，使用 NEW.column-name 和 OLD.column-name 形式的引用，其中 column-name 是觸發器關聯的表的列的名稱。
如果提供了 WHEN 子句，則只有在 WHEN 子句為 true 的行上才會執行指定的 SQL 語句。如果沒有提供 WHEN 子句，則會對所有行執行 SQL 語句。
BEFORE 或 AFTER 關鍵字確定相對於關聯行的插入、修改或刪除操作何時執行觸發器操作。
觸發器在與其關聯的表被刪除時會自動刪除。
要修改的表必須存在於與附加觸發器的表或檢視相同的資料庫中，並且必須只使用tablename，而不是database.tablename。
可以在觸發器程式中使用特殊的 SQL 函式 RAISE() 來引發異常。
公元前 4714 年 11 月 24 日中午以來的天數
以下是建立觸發器的基本語法。
CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name ON table_name BEGIN -- Trigger logic goes here.... END;
這裡，event_name可以是提到的表table_name上的INSERT、DELETE和UPDATE資料庫操作。您可以在表名後選擇性地指定 FOR EACH ROW。
以下是針對表的一個或多個指定列上的 UPDATE 操作建立觸發器的語法。
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name ON table_name BEGIN -- Trigger logic goes here.... END;
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
讓我們考慮一個案例，我們想為 COMPANY 表中插入的每條記錄保留審計跟蹤，我們新建如下（如果您已經擁有它，請刪除 COMPANY 表）。
sqlite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
為了保留審計跟蹤，我們將建立一個名為 AUDIT 的新表，每當在 COMPANY 表中為新記錄建立條目時，日誌訊息將被插入到其中。
sqlite> CREATE TABLE AUDIT( EMP_ID INT NOT NULL, ENTRY_DATE TEXT NOT NULL );
這裡，ID 是 AUDIT 記錄 ID，EMP_ID 是將來自 COMPANY 表的 ID，DATE 將保留在 COMPANY 表中建立記錄的時間戳。現在讓我們在 COMPANY 表上建立觸發器，如下所示：
sqlite> CREATE TRIGGER audit_log AFTER INSERT
ON COMPANY
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;
現在，我們將開始實際工作，讓我們開始在 COMPANY 表中插入記錄，這應該會導致在 AUDIT 表中建立一個審計日誌記錄。在 COMPANY 表中建立一個記錄，如下所示：
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );
這將在 COMPANY 表中建立一個記錄，如下所示：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0
同時，將在 AUDIT 表中建立一個記錄。此記錄是我們在 COMPANY 表的 INSERT 操作上建立的觸發器的結果。類似地，您可以根據您的需求在 UPDATE 和 DELETE 操作上建立觸發器。
EMP_ID ENTRY_DATE ---------- ------------------- 1 2013-04-05 06:26:00
列出觸發器
您可以從sqlite_master表中列出所有觸發器，如下所示：
sqlite> SELECT name FROM sqlite_master WHERE type = 'trigger';
上面的 SQLite 語句將只列出以下一個條目：
name ---------- audit_log
如果您想列出特定表上的觸發器，請使用包含表名的 AND 子句，如下所示：
sqlite> SELECT name FROM sqlite_master WHERE type = 'trigger' AND tbl_name = 'COMPANY';
上面的 SQLite 語句也將只列出以下一個條目：
name ---------- audit_log
刪除觸發器
以下是 DROP 命令，可用於刪除現有觸發器。
sqlite> DROP TRIGGER trigger_name;
SQLite - 索引 (Indexes)
索引是資料庫搜尋引擎可用於加快資料檢索速度的特殊查詢表。簡單地說，索引是指向表中資料的指標。資料庫中的索引與書後面的索引非常相似。
例如，如果您想參考書中討論某個主題的所有頁面，您首先參考索引，該索引按字母順序列出所有主題，然後參考一個或多個特定頁碼。
索引有助於加快 SELECT 查詢和 WHERE 子句的速度，但會減慢資料輸入（使用 UPDATE 和 INSERT 語句）的速度。建立或刪除索引不會影響資料。
建立索引涉及 CREATE INDEX 語句，該語句允許您命名索引，指定表以及要索引的列或列，並指示索引是升序還是降序。
索引也可以是唯一的，類似於 UNIQUE 約束，因為它可以防止在存在索引的列或列組合中插入重複項。
CREATE INDEX 命令
以下是CREATE INDEX的基本語法。
CREATE INDEX index_name ON table_name;
單列索引
單列索引是僅基於一個表列建立的索引。基本語法如下：
CREATE INDEX index_name ON table_name (column_name);
唯一索引
唯一索引不僅用於效能，還用於資料完整性。唯一索引不允許將任何重複值插入表中。基本語法如下：
CREATE UNIQUE INDEX index_name on table_name (column_name);
複合索引
複合索引是表中兩列或多列上的索引。基本語法如下：
CREATE INDEX index_name on table_name (column1, column2);
無論建立單列索引還是複合索引，都要考慮在查詢的 WHERE 子句中經常用作過濾條件的列。
如果只有一列被使用，則單列索引應該是最佳選擇。如果在 WHERE 子句中經常使用兩列或多列作為過濾器，則複合索引是最佳選擇。
隱式索引
隱式索引是在建立物件時由資料庫伺服器自動建立的索引。主鍵約束和唯一約束會自動建立索引。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
以下是一個示例，我們將為COMPANY表中的 salary 列建立一個索引：
sqlite> CREATE INDEX salary_index ON COMPANY (salary);
現在，讓我們使用.indices命令列出 COMPANY 表中所有可用的索引，如下所示：
sqlite> .indices COMPANY
這將產生以下結果，其中sqlite_autoindex_COMPANY_1是在建立表本身時建立的隱式索引。
salary_index sqlite_autoindex_COMPANY_1
您可以列出資料庫範圍內的所有索引，如下所示：
sqlite> SELECT * FROM sqlite_master WHERE type = 'index';
DROP INDEX 命令
可以使用 SQLite DROP 命令刪除索引。刪除索引時應謹慎，因為效能可能會變慢或變快。
以下是基本語法：
DROP INDEX index_name;
您可以使用以下語句刪除之前建立的索引。
sqlite> DROP INDEX salary_index;
何時應避免使用索引？
儘管索引旨在增強資料庫的效能，但在某些情況下應避免使用它們。以下準則指示何時應重新考慮使用索引。
不應在以下情況下使用索引：
- 小型表。
- 經常進行大量批次更新或插入操作的表。
- 包含大量 NULL 值的列。
- 經常被操作的列。
SQLite - INDEXED BY 子句
"INDEXED BY index-name" 子句指定必須使用命名的索引才能查詢前面的表上的值。
如果 index-name 不存在或不能用於查詢，則 SQLite 語句的準備將失敗。
"NOT INDEXED" 子句指定在訪問前面的表時不應使用任何索引，包括由 UNIQUE 和 PRIMARY KEY 約束建立的隱式索引。
但是，即使指定了 "NOT INDEXED"，仍然可以使用 INTEGER PRIMARY KEY 來查詢條目。
公元前 4714 年 11 月 24 日中午以來的天數
以下是 INDEXED BY 子句的語法，它可以與 DELETE、UPDATE 或 SELECT 語句一起使用。
SELECT|DELETE|UPDATE column1, column2... INDEXED BY (index_name) table_name WHERE (CONDITION);
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮表COMPANY，我們將建立一個索引並使用它執行INDEXED BY操作。
sqlite> CREATE INDEX salary_index ON COMPANY(salary); sqlite>
現在，從COMPANY表中選擇資料，您可以按如下方式使用INDEXED BY子句：
sqlite> SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;
這將產生以下結果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 7 James 24 Houston 10000.0 2 Allen 25 Texas 15000.0 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 6 Kim 22 South-Hall 45000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
SQLite - ALTER TABLE命令
SQLite 的ALTER TABLE命令修改現有表，無需執行資料的完整轉儲和重新載入。您可以使用ALTER TABLE語句重命名錶，並可以使用ALTER TABLE語句在現有表中新增其他列。
除了重命名錶和在現有表中新增列之外，SQLite中的ALTER TABLE命令不支援其他操作。
公元前 4714 年 11 月 24 日中午以來的天數
以下是用於重新命名現有表的ALTER TABLE的基本語法。
ALTER TABLE database_name.table_name RENAME TO new_table_name;
以下是用於在現有表中新增新列的ALTER TABLE的基本語法。
ALTER TABLE database_name.table_name ADD COLUMN column_def...;
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮具有以下記錄的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
現在，讓我們嘗試使用ALTER TABLE語句重新命名此表，如下所示：
sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY;
上述SQLite語句將COMPANY表重新命名為OLD_COMPANY。現在，讓我們嘗試在OLD_COMPANY表中新增一個新列，如下所示：
sqlite> ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);
COMPANY表現在已更改，SELECT語句將輸出以下內容。
ID NAME AGE ADDRESS SALARY SEX ---------- ---------- ---------- ---------- ---------- --- 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
需要注意的是，新新增的列填充了NULL值。
SQLite - TRUNCATE TABLE命令
不幸的是，SQLite中沒有TRUNCATE TABLE命令，但是您可以使用SQLite的DELETE命令刪除現有表中的所有資料，儘管建議使用DROP TABLE命令刪除整個表並再次重新建立它。
公元前 4714 年 11 月 24 日中午以來的天數
以下是DELETE命令的基本語法。
sqlite> DELETE FROM table_name;
以下是DROP TABLE的基本語法。
sqlite> DROP TABLE table_name;
如果您使用DELETE TABLE命令刪除所有記錄，建議使用VACUUM命令清除未使用的空間。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮具有以下記錄的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
以下是截斷上述表的示例：
SQLite> DELETE FROM COMPANY; SQLite> VACUUM;
現在，COMPANY表已完全截斷，SELECT語句將沒有任何輸出。
SQLite - 檢視 (Views)
檢視只不過是儲存在資料庫中並具有關聯名稱的SQLite語句。它實際上是以預定義的SQLite查詢形式組成的表。
檢視可以包含表的全部行或一個或多個表中的選定行。檢視可以從一個或多個表建立，這取決於用於建立檢視的編寫的SQLite查詢。
作為虛擬表的檢視允許使用者：
以使用者或使用者類別認為自然或直觀的方式構造資料。
限制對資料的訪問，以便使用者只能檢視有限的資料，而不是完整的表。
彙總來自各個表的資料，可用於生成報表。
SQLite檢視是隻讀的，因此您可能無法在檢視上執行DELETE、INSERT或UPDATE語句。但是，您可以為檢視建立一個觸發器，該觸發器會在嘗試DELETE、INSERT或UPDATE檢視時觸發，並在觸發器的正文中執行您需要的操作。
建立檢視
SQLite檢視使用CREATE VIEW語句建立。SQLite檢視可以從單個表、多個表或其他檢視建立。
以下是CREATE VIEW的基本語法。
CREATE [TEMP | TEMPORARY] VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];
您可以像在普通的SQL SELECT查詢中使用它們一樣，在SELECT語句中包含多個表。如果存在可選的TEMP或TEMPORARY關鍵字，則檢視將建立在臨時資料庫中。
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮具有以下記錄的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
以下是如何從COMPANY表建立檢視的示例。此檢視將僅用於獲取COMPANY表中的幾列。
sqlite> CREATE VIEW COMPANY_VIEW AS SELECT ID, NAME, AGE FROM COMPANY;
您現在可以像查詢實際表一樣查詢COMPANY_VIEW。以下是一個示例：
sqlite> SELECT * FROM COMPANY_VIEW;
這將產生以下結果。
ID NAME AGE ---------- ---------- ---------- 1 Paul 32 2 Allen 25 3 Teddy 23 4 Mark 25 5 David 27 6 Kim 22 7 James 24
刪除檢視
要刪除檢視，只需使用帶有view_name的DROP VIEW語句即可。DROP VIEW的基本語法如下：
sqlite> DROP VIEW view_name;
以下命令將刪除我們在上一節中建立的COMPANY_VIEW檢視。
sqlite> DROP VIEW COMPANY_VIEW;
SQLite - 事務 (Transactions)
事務是對資料庫執行的工作單元。事務是按邏輯順序完成的工作單元或序列，無論是使用者手動完成，還是某種資料庫程式自動完成。
事務是將一個或多個更改傳播到資料庫。例如，如果您正在建立、更新或刪除表中的記錄，那麼您正在對錶執行事務。控制事務以確保資料完整性和處理資料庫錯誤非常重要。
實際上，您會將許多SQLite查詢組合成一個組，並將它們一起作為事務的一部分執行。
事務的屬性
事務具有以下四個標準屬性，通常用首字母縮寫詞ACID表示。
原子性 - 確保工作單元中的所有操作都成功完成；否則，事務將在失敗點中止，之前的操作將回滾到其先前狀態。
一致性 - 確保資料庫在成功提交的事務後正確更改狀態。
隔離性 - 使事務能夠彼此獨立且透明地執行。
永續性 - 確保已提交事務的結果或效果在系統故障的情況下仍然存在。
事務控制
以下是用於控制事務的命令
BEGIN TRANSACTION - 開始事務。
COMMIT - 儲存更改，或者您可以使用END TRANSACTION命令。
ROLLBACK - 回滾更改。
事務控制命令僅與DML命令INSERT、UPDATE和DELETE一起使用。在建立或刪除表時不能使用它們，因為這些操作會自動提交到資料庫。
BEGIN TRANSACTION命令
可以使用BEGIN TRANSACTION或簡單的BEGIN命令啟動事務。此類事務通常持續到遇到下一個COMMIT或ROLLBACK命令為止。但是，如果資料庫關閉或發生錯誤，事務也將回滾。以下是啟動事務的簡單語法。
BEGIN; or BEGIN TRANSACTION;
COMMIT命令
COMMIT命令是用於將事務呼叫的更改儲存到資料庫的事務命令。
COMMIT命令自上次COMMIT或ROLLBACK命令以來將所有事務儲存到資料庫。
以下是COMMIT命令的語法。
COMMIT; or END TRANSACTION;
ROLLBACK命令
ROLLBACK命令是用於撤消尚未儲存到資料庫的事務的事務命令。
ROLLBACK命令只能用於撤消自發出上次COMMIT或ROLLBACK命令以來的事務。
以下是ROLLBACK命令的語法。
ROLLBACK;
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮具有以下記錄的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
現在，讓我們啟動一個事務並刪除表中age = 25的記錄。然後，使用ROLLBACK命令撤消所有更改。
sqlite> BEGIN; sqlite> DELETE FROM COMPANY WHERE AGE = 25; sqlite> ROLLBACK;
現在，如果您檢查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
讓我們啟動另一個事務並刪除表中age = 25的記錄，最後我們使用COMMIT命令提交所有更改。
sqlite> BEGIN; sqlite> DELETE FROM COMPANY WHERE AGE = 25; sqlite> COMMIT;
如果您現在檢查COMPANY表，它仍然具有以下記錄：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
SQLite - 子查詢 (Subqueries)
子查詢或內部查詢或巢狀查詢是在另一個SQLite查詢中的查詢，並嵌入在WHERE子句中。
子查詢用於返回將在主查詢中用作條件的資料，以進一步限制要檢索的資料。
子查詢可以與SELECT、INSERT、UPDATE和DELETE語句以及=、<、>、>=、<=、IN、BETWEEN等運算子一起使用。
子查詢必須遵循一些規則：
子查詢必須用括號括起來。
除非子查詢的多個列用於比較其選擇的列，否則子查詢的SELECT子句中只能有一列。
雖然主查詢可以使用ORDER BY，但在子查詢中不能使用ORDER BY。GROUP BY可用於執行與子查詢中的ORDER BY相同的功能。
返回多行的子查詢只能與多值運算子一起使用，例如IN運算子。
BETWEEN運算子不能與子查詢一起使用；但是，BETWEEN可以在子查詢中使用。
帶有SELECT語句的子查詢
子查詢最常與SELECT語句一起使用。基本語法如下：
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮具有以下記錄的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語句的子查詢。
sqlite> SELECT *
FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY
WHERE SALARY > 45000) ;
這將產生以下結果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
帶有INSERT語句的子查詢
子查詢也可以與INSERT語句一起使用。INSERT語句使用子查詢返回的資料插入到另一個表中。子查詢中的選擇資料可以使用任何字元、日期或數字函式進行修改。
以下是基本語法：
INSERT INTO table_name [ (column1 [, column2 ]) ] SELECT [ *|column1 [, column2 ] FROM table1 [, table2 ] [ WHERE VALUE OPERATOR ]
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮一個與COMPANY表具有相似結構的COMPANY_BKP表，可以使用相同的CREATE TABLE語句建立，使用COMPANY_BKP作為表名。要將完整的COMPANY表複製到COMPANY_BKP，語法如下：
sqlite> INSERT INTO COMPANY_BKP
SELECT * FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY) ;
帶有UPDATE語句的子查詢
子查詢可以與UPDATE語句一起使用。使用子查詢與UPDATE語句時，可以更新表中的單個或多個列。
以下是基本語法：
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
假設我們有可用的COMPANY_BKP表，它是COMPANY表的備份。
以下示例更新COMPANY表中所有年齡大於或等於27的客戶的SALARY，乘以0.50。
sqlite> UPDATE COMPANY
SET SALARY = SALARY * 0.50
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE >= 27 );
這將影響兩行，最後COMPANY表將具有以下記錄：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 10000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 42500.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
帶有DELETE語句的子查詢
子查詢可以與DELETE語句一起使用，就像上面提到的任何其他語句一樣。
以下是基本語法：
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
假設我們有可用的COMPANY_BKP表，它是COMPANY表的備份。
以下示例刪除COMPANY表中所有年齡大於或等於27的客戶的記錄。
sqlite> DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27 );
這將影響兩行，最後COMPANY表將具有以下記錄：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 42500.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
SQLite - AUTOINCREMENT
SQLite 的AUTOINCREMENT是一個關鍵字，用於自動遞增表中欄位的值。我們可以使用AUTOINCREMENT關鍵字在建立具有特定列名的表時自動遞增欄位值。
AUTOINCREMENT關鍵字只能與INTEGER欄位一起使用。
公元前 4714 年 11 月 24 日中午以來的天數
AUTOINCREMENT關鍵字的基本用法如下：
CREATE TABLE table_name( column1 INTEGER AUTOINCREMENT, column2 datatype, column3 datatype, ..... columnN datatype, );
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮如下建立的COMPANY表：
sqlite> CREATE TABLE COMPANY( ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
現在，將以下記錄插入COMPANY表：
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'James', 24, 'Houston', 10000.00 );
這將向COMPANY表中插入7個元組，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
SQLite - 注入 (Injection)
如果您透過網頁獲取使用者輸入並將其插入SQLite資料庫，則您可能已讓自己容易受到稱為SQL注入的安全問題的影響。在本章中，您將學習如何幫助防止這種情況的發生，並幫助您保護指令碼和SQLite語句。
注入通常發生在您向用戶索取輸入（例如他們的姓名）時，他們給您的不是姓名，而是一個您會在不知情的情況下在資料庫上執行的SQLite語句。
永遠不要信任使用者提供的資料，只有在驗證後才能處理此資料；通常，這是透過模式匹配完成的。在以下示例中，使用者名稱被限制為字母數字字元加上下劃線，長度在8到20個字元之間——根據需要修改這些規則。
if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)){
$db = new SQLiteDatabase('filename');
$result = @$db->query("SELECT * FROM users WHERE username = $matches[0]");
} else {
echo "username not accepted";
}
為了演示這個問題，請考慮以下摘錄：
$name = "Qadir'; DELETE FROM users;";
@$db->query("SELECT * FROM users WHERE username = '{$name}'");
函式呼叫應該從使用者表中檢索一條記錄，其中name列與使用者指定的名稱匹配。在正常情況下，$name 只包含字母數字字元和空格，例如字串ilia。但是，在這種情況下，透過將一個全新的查詢附加到$name，對資料庫的呼叫變成了災難：注入的DELETE查詢刪除了users表中的所有記錄。
有些資料庫介面不允許查詢堆疊或在單個函式呼叫中執行多個查詢。如果嘗試堆疊查詢，則呼叫會失敗，但SQLite和PostgreSQL會愉快地執行堆疊查詢，在一個字串中執行所有提供的查詢，從而造成嚴重的安全問題。
防止SQL注入
您可以在PERL和PHP等指令碼語言中巧妙地處理所有跳脫字元。PHP程式語言提供函式sqlite_escape_string() 來轉義對SQLite來說是特殊字元的輸入字元。
if (get_magic_quotes_gpc()) {
$name = sqlite_escape_string($name);
}
$result = @$db->query("SELECT * FROM users WHERE username = '{$name}'");
雖然編碼使得插入資料安全，但它會使包含二進位制資料的列中簡單的文字比較和LIKE子句不可用。
注意 − addslashes() 不應該用於為SQLite查詢引用字串；這會在檢索資料時導致奇怪的結果。
SQLite - EXPLAIN
SQLite語句可以以關鍵字“EXPLAIN”或短語“EXPLAIN QUERY PLAN”開頭，用於描述表的詳細資訊。
任何一種修改都會導致SQLite語句的行為像一個查詢，並返回有關在省略EXPLAIN關鍵字或短語的情況下SQLite語句將如何操作的資訊。
EXPLAIN和EXPLAIN QUERY PLAN的輸出僅用於互動式分析和故障排除。
輸出格式的細節可能會在SQLite的各個版本之間發生變化。
應用程式不應使用EXPLAIN或EXPLAIN QUERY PLAN，因為它們的精確行為是可變的，並且只有部分文件。
公元前 4714 年 11 月 24 日中午以來的天數
EXPLAIN語法如下：
EXPLAIN [SQLite Query]
EXPLAIN QUERY PLAN語法如下：
EXPLAIN QUERY PLAN [SQLite Query]
您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。
考慮具有以下記錄的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語句檢查以下子查詢：
sqlite> EXPLAIN SELECT * FROM COMPANY WHERE Salary >= 20000;
這將產生以下結果。
addr opcode p1 p2 p3 ---------- ---------- ---------- ---------- ---------- 0 Goto 0 19 1 Integer 0 0 2 OpenRead 0 8 3 SetNumColu 0 5 4 Rewind 0 17 5 Column 0 4 6 RealAffini 0 0 7 Integer 20000 0 8 Lt 357 16 collseq(BI 9 Rowid 0 0 10 Column 0 1 11 Column 0 2 12 Column 0 3 13 Column 0 4 14 RealAffini 0 0 15 Callback 5 0 16 Next 0 5 17 Close 0 0 18 Halt 0 0 19 Transactio 0 0 20 VerifyCook 0 38 21 Goto 0 1 22 Noop 0 0
現在，讓我們使用SELECT語句檢查以下Explain Query Plan：
SQLite> EXPLAIN QUERY PLAN SELECT * FROM COMPANY WHERE Salary >= 20000; order from detail ---------- ---------- ------------- 0 0 TABLE COMPANY
SQLite - VACUUM
VACUUM命令透過將其內容複製到臨時資料庫檔案並從副本重新載入原始資料庫檔案來清理主資料庫。這消除了空閒頁面，使表資料連續對齊，並以其他方式清理資料庫檔案結構。
VACUUM命令可能會更改沒有顯式INTEGER PRIMARY KEY的表中條目的ROWID。VACUUM命令僅適用於主資料庫。無法VACUUM附加的資料庫檔案。
如果存在活動事務，VACUUM命令將失敗。對於記憶體資料庫，VACUUM命令是一個空操作。由於VACUUM命令從頭開始重建資料庫檔案，因此VACUUM也可以用於修改許多資料庫特定的配置引數。
手動VACUUM
以下是從命令提示符發出整個資料庫的VACUUM命令的簡單語法：
$sqlite3 database_name "VACUUM;"
您也可以從SQLite提示符執行VACUUM，如下所示：
sqlite> VACUUM;
您還可以對特定表執行VACUUM，如下所示：
sqlite> VACUUM table_name;
自動VACUUM
SQLite自動VACUUM的作用與VACUUM不同，它只將空閒頁面移動到資料庫的末尾，從而減小資料庫的大小。這樣做可能會嚴重碎片化資料庫，而VACUUM確保碎片整理。因此，自動VACUUM只保持資料庫較小。
您可以透過在SQLite提示符下執行以下pragma來啟用/停用SQLite自動真空：
sqlite> PRAGMA auto_vacuum = NONE; -- 0 means disable auto vacuum sqlite> PRAGMA auto_vacuum = FULL; -- 1 means enable full auto vacuum sqlite> PRAGMA auto_vacuum = INCREMENTAL; -- 2 means enable incremental vacuum
您可以從命令提示符執行以下命令來檢查自動真空設定：
$sqlite3 database_name "PRAGMA auto_vacuum;"
SQLite - 日期和時間 (Date & Time)
SQLite支援以下五種日期和時間函式：
| 序號 | 函式 | 您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。 |
|---|---|---|
| 1 | date(timestring, modifiers...) | 這將返回此格式的日期：YYYY-MM-DD |
| 2 | time(timestring, modifiers...) | 這將返回時間為HH:MM:SS |
| 3 | datetime(timestring, modifiers...) | 這將返回YYYY-MM-DD HH:MM:SS |
| 4 | julianday(timestring, modifiers...) | 這將返回公元前4714年11月24日中午以來的天數。 |
| 5 | strftime(timestring, modifiers...) | 這將根據指定為第一個引數的格式字串返回日期，格式如下所示。 |
以上五個日期和時間函式都將時間字串作為引數。時間字串後面可以跟零個或多個修飾符。strftime()函式也以格式字串作為其第一個引數。下一節將詳細介紹不同型別的時間字串和修飾符。
時間字串
時間字串可以採用以下任何格式：
| 序號 | 時間字串 | 您可以選擇以任何這些格式儲存日期和時間，並使用內建的日期和時間函式在格式之間自由轉換。 |
|---|---|---|
| 1 | YYYY-MM-DD | 2010-12-30 |
| 2 | YYYY-MM-DD HH:MM | 2010-12-30 12:10 |
| 3 | YYYY-MM-DD HH:MM:SS.SSS | 2010-12-30 12:10:04.100 |
| 4 | MM-DD-YYYY HH:MM | 30-12-2010 12:10 |
| 5 | HH:MM | 12:10 |
| 6 | YYYY-MM-DDTHH:MM | 2010-12-30 12:10 |
| 7 | HH:MM:SS | 12:10:01 |
| 8 | YYYYMMDD HHMMSS | 20101230 121001 |
| 9 | now | 2013-05-07 |
您可以使用“T”作為分隔日期和時間的文字字元。
修飾符
時間字串後面可以跟零個或多個修飾符，這些修飾符將更改上述五個函式返回的日期和/或時間。修飾符從左到右應用。
SQLite中提供以下修飾符：
- NNN天
- NNN小時
- NNN分鐘
- NNN.NNNN秒
- NNN個月
- NNN年
- 月初
- 年初
- 一日之初
- 星期幾N
- unixepoch
- localtime
- utc
格式化程式
SQLite提供了一個非常方便的函式strftime()來格式化任何日期和時間。您可以使用以下替換來格式化日期和時間。
| 替換 | 描述 |
|---|---|
| %d | 月份中的天數，01-31 |
| %f | 秒的小數部分，SS.SSS |
| %H | 小時，00-23 |
| %j | 一年中的天數，001-366 |
| %J | 儒略日數，DDDD.DDDD |
| %m | 月份，00-12 |
| %M | 分鐘，00-59 |
| %s | 自1970-01-01以來的秒數 |
| %S | 秒，00-59 |
| %w | 星期幾，0-6（0是星期日） |
| %W | 一年中的星期數，01-53 |
| %Y | 年份，YYYY |
| %% | %符號 |
示例
現在讓我們嘗試使用SQLite提示符使用各種示例。以下命令計算當前日期。
sqlite> SELECT date('now');
2013-05-07
以下命令計算當前月份的最後一天。
sqlite> SELECT date('now','start of month','+1 month','-1 day');
2013-05-31
以下命令計算給定UNIX時間戳1092941466的日期和時間。
sqlite> SELECT datetime(1092941466, 'unixepoch'); 2004-08-19 18:51:06
以下命令計算給定UNIX時間戳1092941466的日期和時間，並補償您的本地時區。
sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime'); 2004-08-19 13:51:06
以下命令計算當前UNIX時間戳。
sqlite> SELECT strftime('%s','now');
1393348134
以下命令計算自美國《獨立宣言》簽署以來的天數。
sqlite> SELECT julianday('now') - julianday('1776-07-04');
86798.7094695023
以下命令計算自2004年特定時刻以來的秒數。
sqlite> SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
295001572
以下命令計算當前年份10月份第一個星期二的日期。
sqlite> SELECT date('now','start of year','+9 months','weekday 2');
2013-10-01
以下命令計算自UNIX紀元以來的秒數（類似於strftime('%s','now')，但包含小數部分）。
sqlite> SELECT (julianday('now') - 2440587.5)*86400.0;
1367926077.12598
要在格式化日期時在UTC和本地時間值之間轉換，請使用utc或localtime修飾符，如下所示：
sqlite> SELECT time('12:00', 'localtime');
05:00:00
sqlite> SELECT time('12:00', 'utc');
19:00:00
SQLite - 有用函式
SQLite有許多內建函式可以對字串或數字資料進行處理。以下是幾個有用的SQLite內建函式的列表，所有這些函式都不區分大小寫，這意味著您可以使用這些函式的小寫形式、大寫形式或混合形式。有關更多詳細資訊，您可以檢視SQLite的官方文件。
| 序號 | 函式和描述 |
|---|---|
| 1 | SQLite COUNT函式 SQLite COUNT聚合函式用於計算資料庫表中的行數。 |
| 2 | SQLite MAX函式 SQLite MAX聚合函式允許我們為某個列選擇最高（最大）值。 |
| 3 | SQLite MIN函式 SQLite MIN聚合函式允許我們為某個列選擇最低（最小）值。 |
| 4 | SQLite AVG函式 SQLite AVG聚合函式選擇某個表列的平均值。 |
| 5 | SQLite SUM函式 SQLite SUM聚合函式允許選擇數字列的總和。 |
| 6 | SQLite RANDOM函式 SQLite RANDOM函式返回-9223372036854775808和+9223372036854775807之間的偽隨機整數。 |
| 7 | SQLite ABS函式 SQLite ABS函式返回數字引數的絕對值。 |
| 8 | SQLite UPPER函式 SQLite UPPER函式將字串轉換為大寫字母。 |
| 9 | SQLite LOWER函式 SQLite LOWER函式將字串轉換為小寫字母。 |
| 10 | SQLite LENGTH函式 SQLite LENGTH函式返回字串的長度。 |
| 11 | SQLite sqlite_version函式 SQLite sqlite_version函式返回SQLite庫的版本。 |
在我們開始對上述函式進行示例之前，請考慮包含以下記錄的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
SQLite COUNT函式
SQLite COUNT聚合函式用於計算資料庫表中的行數。以下是一個示例：
sqlite> SELECT count(*) FROM COMPANY;
上述SQLite SQL語句將產生以下結果。
count(*) ---------- 7
SQLite MAX函式
SQLite MAX聚合函式允許我們為某個列選擇最高（最大）值。以下是一個示例：
sqlite> SELECT max(salary) FROM COMPANY;
上述SQLite SQL語句將產生以下結果。
max(salary) ----------- 85000.0
SQLite MIN函式
SQLite MIN聚合函式允許我們為某個列選擇最低（最小）值。以下是一個示例：
sqlite> SELECT min(salary) FROM COMPANY;
上述SQLite SQL語句將產生以下結果。
min(salary) ----------- 10000.0
SQLite AVG函式
SQLite AVG聚合函式選擇某個表列的平均值。以下是一個示例：
sqlite> SELECT avg(salary) FROM COMPANY;
上述SQLite SQL語句將產生以下結果。
avg(salary) ---------------- 37142.8571428572
SQLite SUM函式
SQLite SUM聚合函式允許選擇數字列的總和。以下是一個示例：
sqlite> SELECT sum(salary) FROM COMPANY;
上述SQLite SQL語句將產生以下結果。
sum(salary) ----------- 260000.0
SQLite RANDOM函式
SQLite RANDOM函式返回-9223372036854775808和+9223372036854775807之間的偽隨機整數。以下是一個示例：
sqlite> SELECT random() AS Random;
上述SQLite SQL語句將產生以下結果。
Random ------------------- 5876796417670984050
SQLite ABS函式
SQLite ABS函式返回數字引數的絕對值。以下是一個示例：
sqlite> SELECT abs(5), abs(-15), abs(NULL), abs(0), abs("ABC");
上述SQLite SQL語句將產生以下結果。
abs(5) abs(-15) abs(NULL) abs(0) abs("ABC")
---------- ---------- ---------- ---------- ----------
5 15 0 0.0
SQLite UPPER函式
SQLite UPPER函式將字串轉換為大寫字母。以下是一個示例：
sqlite> SELECT upper(name) FROM COMPANY;
上述SQLite SQL語句將產生以下結果。
upper(name) ----------- PAUL ALLEN TEDDY MARK DAVID KIM JAMES
SQLite LOWER函式
SQLite LOWER函式將字串轉換為小寫字母。以下是一個示例：
sqlite> SELECT lower(name) FROM COMPANY;
上述SQLite SQL語句將產生以下結果。
lower(name) ----------- paul allen teddy mark david kim james
SQLite LENGTH函式
SQLite LENGTH函式返回字串的長度。以下是一個示例：
sqlite> SELECT name, length(name) FROM COMPANY;
上述SQLite SQL語句將產生以下結果。
NAME length(name) ---------- ------------ Paul 4 Allen 5 Teddy 5 Mark 4 David 5 Kim 3 James 5
SQLite sqlite_version函式
SQLite sqlite_version函式返回SQLite庫的版本。以下是一個示例：
sqlite> SELECT sqlite_version() AS 'SQLite Version';
上述SQLite SQL語句將產生以下結果。
SQLite Version -------------- 3.6.20
SQLite - C/C++
在本章中，您將學習如何在C/C++程式中使用SQLite。
安裝
在開始在C/C++程式中使用SQLite之前，您需要確保機器上已設定SQLite庫。您可以檢視SQLite安裝章節以瞭解安裝過程。
C/C++介面API
以下是重要的C/C++ SQLite介面例程，這些例程足以滿足您從C/C++程式使用SQLite資料庫的要求。如果您正在尋找更復雜的應用程式，那麼您可以檢視SQLite官方文件。
| 序號 | API和描述 |
|---|---|
| 1 | sqlite3_open(const char *filename, sqlite3 **ppDb) 此例程開啟到SQLite資料庫檔案的連線，並返回一個數據庫連線物件，供其他SQLite例程使用。 如果filename引數為NULL或':memory:'，sqlite3_open()將在RAM中建立一個僅在會話期間持續存在的記憶體資料庫。 如果檔名不為NULL，sqlite3_open()將嘗試使用其值開啟資料庫檔案。如果不存在該名稱的檔案，sqlite3_open()將使用該名稱開啟一個新的資料庫檔案。 |
| 2 | sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char **errmsg) 此例程提供了一種快速簡便的方法來執行由sql引數提供的SQL命令，該引數可以包含多個SQL命令。 這裡，第一個引數sqlite3是一個開啟的資料庫物件，sqlite_callback是一個回撥函式，data是其第一個引數，errmsg將被返回以捕獲例程引發的任何錯誤。 SQLite3_exec()例程解析並執行sql引數中給出的每個命令，直到到達字串結尾或遇到錯誤。 |
| 3 | sqlite3_close(sqlite3*) 此例程關閉先前透過呼叫sqlite3_open()開啟的資料庫連線。所有與連線關聯的預處理語句都應在關閉連線之前完成。 如果仍有未完成的查詢，sqlite3_close()將返回SQLITE_BUSY，並顯示錯誤訊息“由於未完成的語句而無法關閉”。 |
連線到資料庫
以下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: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
sqlite3_close(db);
}
現在，讓我們編譯並執行上述程式，在當前目錄中建立我們的資料庫test.db。您可以根據需要更改路徑。
$gcc test.c -l sqlite3 $./a.out Opened database successfully
如果您要使用C++原始碼，則可以按如下方式編譯程式碼：
$g++ test.c -l sqlite3
在這裡，我們將程式與sqlite3庫連結，以便為C程式提供必要的函式。這將在您的目錄中建立一個數據庫檔案test.db，您將得到以下結果。
-rwxr-xr-x. 1 root root 7383 May 8 02:06 a.out -rw-r--r--. 1 root root 323 May 8 02:05 test.c -rw-r--r--. 1 root root 0 May 8 02:06 test.db
建立表
以下C程式碼段將用於在先前建立的資料庫中建立表：
#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 = %s\n", 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: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stdout, "Opened database successfully\n");
}
/* 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: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Table created successfully\n");
}
sqlite3_close(db);
return 0;
}
編譯並執行上述程式後，它將在您的test.db中建立COMPANY表，檔案的最終列表如下：
-rwxr-xr-x. 1 root root 9567 May 8 02:31 a.out -rw-r--r--. 1 root root 1207 May 8 02:31 test.c -rw-r--r--. 1 root root 3072 May 8 02:31 test.db
INSERT操作
以下C程式碼段顯示瞭如何在上面示例中建立的COMPANY表中建立記錄：
#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 = %s\n", 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: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
/* 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: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Records created successfully\n");
}
sqlite3_close(db);
return 0;
}
編譯並執行上述程式後，它將在COMPANY表中建立給定的記錄，並將顯示以下兩行：
Opened database successfully Records created successfully
SELECT操作
在繼續實際的提取記錄示例之前，讓我們詳細瞭解一下我們在示例中使用的回撥函式。此回撥提供了一種從SELECT語句中獲取結果的方法。它具有以下宣告：
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 */ );
如果在sqlite_exec()例程中將上述回撥作為第三個引數提供，則SQLite將為SQL引數中執行的每個SELECT語句中處理的每條記錄呼叫此回撥函式。
以下C程式碼段顯示瞭如何從上面示例中建立的COMPANY表中提取和顯示記錄：
#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 = %s\n", 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: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
/* 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: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Operation done successfully\n");
}
sqlite3_close(db);
return 0;
}
編譯並執行上述程式後，將產生以下結果。
Opened database successfully Callback function called: ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 20000.0 Callback function called: ID = 2 NAME = Allen AGE = 25 ADDRESS = Texas SALARY = 15000.0 Callback function called: ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 Callback function called: ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
UPDATE操作
以下C程式碼段顯示瞭如何使用UPDATE語句更新任何記錄，然後從COMPANY表中提取和顯示更新後的記錄。
#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 = %s\n", 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: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
/* 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: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Operation done successfully\n");
}
sqlite3_close(db);
return 0;
}
編譯並執行上述程式後，將產生以下結果。
Opened database successfully Callback function called: ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 25000.0 Callback function called: ID = 2 NAME = Allen AGE = 25 ADDRESS = Texas SALARY = 15000.0 Callback function called: ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 Callback function called: ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
DELETE操作
以下C程式碼段顯示瞭如何使用DELETE語句刪除任何記錄，然後從COMPANY表中提取和顯示剩餘的記錄。
#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 = %s\n", 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: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
/* 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: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Operation done successfully\n");
}
sqlite3_close(db);
return 0;
}
編譯並執行上述程式後，將產生以下結果。
Opened database successfully Callback function called: ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 20000.0 Callback function called: ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 Callback function called: ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
SQLite - Java
本章將學習如何在Java程式中使用SQLite。
安裝
在開始在Java程式中使用SQLite之前，您需要確保已在計算機上安裝SQLite JDBC驅動程式和Java。您可以檢視Java教程，瞭解如何在您的計算機上安裝Java。現在，讓我們檢查如何設定SQLite JDBC驅動程式。
從sqlite-jdbc儲存庫下載最新版本的sqlite-jdbc-(VERSION).jar。
將下載的jar檔案sqlite-jdbc-(VERSION).jar新增到您的類路徑中，或者您可以將其與-classpath選項一起使用，如下例所示。
以下部分假設您對Java JDBC概念略知一二。如果您不瞭解，建議您花半小時時間學習JDBC教程，以便更好地理解以下內容。
連線到資料庫
以下Java程式顯示瞭如何連線到現有資料庫。如果資料庫不存在，則將建立它，最後返回一個數據庫物件。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Opened database successfully");
}
}
現在，讓我們編譯並執行上述程式，在當前目錄中建立我們的資料庫test.db。您可以根據需要更改路徑。我們假設當前版本的JDBC驅動程式sqlite-jdbc-3.7.2.jar位於當前路徑中。
$javac SQLiteJDBC.java $java -classpath ".:sqlite-jdbc-3.7.2.jar" SQLiteJDBC Open database successfully
如果您要使用Windows機器，則可以按如下方式編譯和執行程式碼：
$javac SQLiteJDBC.java $java -classpath ".;sqlite-jdbc-3.7.2.jar" SQLiteJDBC Opened database successfully
建立表
以下Java程式將用於在先前建立的資料庫中建立表。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "CREATE TABLE COMPANY " +
"(ID INT PRIMARY KEY NOT NULL," +
" NAME TEXT NOT NULL, " +
" AGE INT NOT NULL, " +
" ADDRESS CHAR(50), " +
" SALARY REAL)";
stmt.executeUpdate(sql);
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Table created successfully");
}
}
編譯並執行上述程式後，它將在您的test.db中建立COMPANY表，檔案的最終列表如下：
-rw-r--r--. 1 root root 3201128 Jan 22 19:04 sqlite-jdbc-3.7.2.jar -rw-r--r--. 1 root root 1506 May 8 05:43 SQLiteJDBC.class -rw-r--r--. 1 root root 832 May 8 05:42 SQLiteJDBC.java -rw-r--r--. 1 root root 3072 May 8 05:43 test.db
INSERT操作
以下Java程式顯示瞭如何在上面示例中建立的COMPANY表中建立記錄。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (1, 'Paul', 32, 'California', 20000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
stmt.executeUpdate(sql);
stmt.close();
c.commit();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Records created successfully");
}
}
編譯並執行上述程式後，它將在COMPANY表中建立給定的記錄，並將顯示以下兩行：
Opened database successfully Records created successfully
SELECT操作
以下Java程式顯示瞭如何從上面示例中建立的COMPANY表中提取和顯示記錄。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}
編譯並執行上述程式後，將產生以下結果。
Opened database successfully ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 20000.0 ID = 2 NAME = Allen AGE = 25 ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
UPDATE操作
以下Java程式碼顯示瞭如何使用UPDATE語句更新任何記錄，然後從COMPANY表中提取和顯示更新後的記錄。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
stmt.executeUpdate(sql);
c.commit();
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}
編譯並執行上述程式後，將產生以下結果。
Opened database successfully ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 25000.0 ID = 2 NAME = Allen AGE = 25 ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
DELETE操作
以下Java程式碼顯示瞭如何使用DELETE語句刪除任何記錄，然後從我們的COMPANY表中提取和顯示剩餘的記錄。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "DELETE from COMPANY where ID=2;";
stmt.executeUpdate(sql);
c.commit();
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}
編譯並執行上述程式後，將產生以下結果。
Opened database successfully ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 25000.0 ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
SQLite - PHP
本章將學習如何在PHP程式中使用SQLite。
安裝
從PHP 5.3.0開始，SQLite3擴充套件預設啟用。可以使用--without-sqlite3在編譯時停用它。
Windows使用者必須啟用php_sqlite3.dll才能使用此擴充套件。從PHP 5.3.0開始，此DLL包含在Windows版本的PHP中。
有關詳細的安裝說明，請檢視我們的PHP教程及其官方網站。
PHP介面API
以下是重要的PHP例程，可以滿足您從PHP程式使用SQLite資料庫的要求。如果您正在尋找更復雜的應用程式，則可以檢視PHP官方文件。
| 序號 | API和描述 |
|---|---|
| 1 | public void SQLite3::open ( filename, flags, encryption_key ) 開啟SQLite 3資料庫。如果構建包含加密，則它將嘗試使用金鑰。 如果將filename指定為':memory:'，則SQLite3::open()將在RAM中建立一個記憶體資料庫，該資料庫僅在會話期間存在。 如果檔名是實際的裝置檔名，則SQLite3::open()嘗試使用其值開啟資料庫檔案。如果不存在該名稱的檔案，則將建立一個同名的新資料庫檔案。 可選標誌用於確定如何開啟SQLite資料庫。預設情況下，open使用SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE。 |
| 2 | public bool SQLite3::exec ( string $query ) 此例程提供了一種快速簡便的方法來執行sql引數提供的SQL命令，該引數可以包含多個SQL命令。此例程用於對給定資料庫執行無結果查詢。 |
| 3 | public SQLite3Result SQLite3::query ( string $query ) 此例程執行SQL查詢，如果查詢返回結果，則返回一個SQLite3Result物件。 |
| 4 | public int SQLite3::lastErrorCode ( void ) 此例程返回最近一次失敗的SQLite請求的數字結果程式碼。 |
| 5 | public string SQLite3::lastErrorMsg ( void ) 此例程返回描述最近一次失敗的SQLite請求的英文文字。 |
| 6 | public int SQLite3::changes ( void ) 此例程返回由最近的SQL語句更新、插入或刪除的資料庫行的數量。 |
| 7 | public bool SQLite3::close ( void ) 此例程關閉先前透過呼叫SQLite3::open()開啟的資料庫連線。 |
| 8 | public string SQLite3::escapeString ( string $value ) 此例程返回已正確轉義以安全包含在SQL語句中的字串。 |
連線到資料庫
以下PHP程式碼顯示瞭如何連線到現有資料庫。如果資料庫不存在，則將建立它，最後返回一個數據庫物件。
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
?>
現在，讓我們執行上述程式，在當前目錄中建立我們的資料庫test.db。您可以根據需要更改路徑。如果資料庫成功建立，則將顯示以下訊息：
Open database successfully
建立表
以下PHP程式將用於在先前建立的資料庫中建立表。
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);
EOF;
$ret = $db->exec($sql);
if(!$ret){
echo $db->lastErrorMsg();
} else {
echo "Table created successfully\n";
}
$db->close();
?>
執行上述程式後，它將在您的test.db中建立COMPANY表，並將顯示以下訊息：
Opened database successfully Table created successfully
INSERT操作
以下PHP程式顯示瞭如何在上面示例中建立的COMPANY表中建立記錄。
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB();
if(!$db){
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
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 );
EOF;
$ret = $db->exec($sql);
if(!$ret) {
echo $db->lastErrorMsg();
} else {
echo "Records created successfully\n";
}
$db->close();
?>
執行上述程式後，它將在COMPANY表中建立給定的記錄，並將顯示以下兩行。
Opened database successfully Records created successfully
SELECT操作
以下PHP程式顯示瞭如何從上面示例中建立的COMPANY表中提取和顯示記錄：
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
SELECT * from COMPANY;
EOF;
$ret = $db->query($sql);
while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
echo "ID = ". $row['ID'] . "\n";
echo "NAME = ". $row['NAME'] ."\n";
echo "ADDRESS = ". $row['ADDRESS'] ."\n";
echo "SALARY = ".$row['SALARY'] ."\n\n";
}
echo "Operation done successfully\n";
$db->close();
?>
執行上述程式後，將產生以下結果。
Opened database successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 20000 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
UPDATE操作
以下PHP程式碼顯示瞭如何使用UPDATE語句更新任何記錄，然後從COMPANY表中提取和顯示更新後的記錄。
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
UPDATE COMPANY set SALARY = 25000.00 where ID=1;
EOF;
$ret = $db->exec($sql);
if(!$ret) {
echo $db->lastErrorMsg();
} else {
echo $db->changes(), " Record updated successfully\n";
}
$sql =<<<EOF
SELECT * from COMPANY;
EOF;
$ret = $db->query($sql);
while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
echo "ID = ". $row['ID'] . "\n";
echo "NAME = ". $row['NAME'] ."\n";
echo "ADDRESS = ". $row['ADDRESS'] ."\n";
echo "SALARY = ".$row['SALARY'] ."\n\n";
}
echo "Operation done successfully\n";
$db->close();
?>
執行上述程式後，將產生以下結果。
Opened database successfully 1 Record updated successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 25000 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
DELETE操作
以下PHP程式碼顯示瞭如何使用DELETE語句刪除任何記錄，然後從COMPANY表中提取和顯示剩餘的記錄。
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
DELETE from COMPANY where ID = 2;
EOF;
$ret = $db->exec($sql);
if(!$ret){
echo $db->lastErrorMsg();
} else {
echo $db->changes(), " Record deleted successfully\n";
}
$sql =<<<EOF
SELECT * from COMPANY;
EOF;
$ret = $db->query($sql);
while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
echo "ID = ". $row['ID'] . "\n";
echo "NAME = ". $row['NAME'] ."\n";
echo "ADDRESS = ". $row['ADDRESS'] ."\n";
echo "SALARY = ".$row['SALARY'] ."\n\n";
}
echo "Operation done successfully\n";
$db->close();
?>
執行上述程式後，將產生以下結果。
Opened database successfully 1 Record deleted successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 25000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
SQLite - Perl
本章將學習如何在Perl程式中使用SQLite。
安裝
可以使用Perl DBI模組將SQLite3與Perl整合，該模組是Perl程式語言的資料庫訪問模組。它定義了一組方法、變數和約定，這些方法、變數和約定提供標準的資料庫介面。
以下是在Linux/UNIX機器上安裝DBI模組的簡單步驟：
$ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz $ tar xvfz DBI-1.625.tar.gz $ cd DBI-1.625 $ perl Makefile.PL $ make $ make install
如果需要為DBI安裝SQLite驅動程式，則可以按如下方式安裝：
$ wget http://search.cpan.org/CPAN/authors/id/M/MS/MSERGEANT/DBD-SQLite-1.11.tar.gz $ tar xvfz DBD-SQLite-1.11.tar.gz $ cd DBD-SQLite-1.11 $ perl Makefile.PL $ make $ make install
DBI介面API
以下是重要的DBI例程，可以滿足您從Perl程式使用SQLite資料庫的要求。如果您正在尋找更復雜的應用程式，則可以檢視Perl DBI官方文件。
| 序號 | API和描述 |
|---|---|
| 1 | DBI->connect($data_source, "", "", \%attr) 建立到請求的$data_source的資料庫連線或會話。如果連線成功，則返回資料庫控制代碼物件。 資料來源具有以下形式：DBI:SQLite:dbname = 'test.db'，其中SQLite是SQLite驅動程式名稱，test.db是SQLite資料庫檔案的名稱。如果檔名指定為':memory:'，它將在RAM中建立一個記憶體資料庫，該資料庫僅在會話期間存在。 如果檔名是實際的裝置檔名，則它嘗試使用其值開啟資料庫檔案。如果不存在該名稱的檔案，則將建立一個同名的新資料庫檔案。 您可以將第二個和第三個引數保留為空字串，最後一個引數用於傳遞各種屬性，如下例所示。 |
| 2 | $dbh->do($sql) 此例程準備並執行單個SQL語句。返回受影響的行數或錯誤時的未定義值。返回值-1表示行數未知、不適用或不可用。這裡，$dbh是由DBI->connect()呼叫返回的控制代碼。 |
| 3 |
$dbh->prepare($sql) 此例程準備由資料庫引擎稍後執行的語句，並返回對語句控制代碼物件的引用。 |
| 4 |
$sth->execute() 此例程執行執行已準備語句所需的任何處理。如果發生錯誤，則返回未定義值。成功的執行始終返回true，而不管受影響的行數是多少。這裡，$sth是由$dbh->prepare($sql)呼叫返回的語句控制代碼。 |
| 5 |
$sth->fetchrow_array() 此例程提取下一行資料，並將其作為包含欄位值的列表返回。空欄位在列表中返回為未定義值。 |
| 6 |
$DBI::err 這等效於$h->err，其中$h是任何控制代碼型別，例如$dbh、$sth或$drh。這將返回上次呼叫的驅動程式方法的本機資料庫引擎錯誤程式碼。 |
| 7 |
$DBI::errstr 這等效於$h->errstr，其中$h是任何控制代碼型別，例如$dbh、$sth或$drh。這將返回上次呼叫的DBI方法的本機資料庫引擎錯誤訊息。 |
| 8 |
$dbh->disconnect() 此例程關閉先前透過呼叫 DBI->connect() 開啟的資料庫連線。 |
連線到資料庫
下面的 Perl 程式碼演示如何連線到現有資料庫。如果資料庫不存在，則會建立它，最後返回一個數據庫物件。
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
現在，讓我們執行上面的程式在當前目錄中建立我們的資料庫 test.db。您可以根據需要更改路徑。將上面的程式碼儲存在 sqlite.pl 檔案中，並按如下所示執行它。如果資料庫成功建立，則會顯示以下訊息：
$ chmod +x sqlite.pl $ ./sqlite.pl Open database successfully
建立表
下面的 Perl 程式用於在先前建立的資料庫中建立表。
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL););
my $rv = $dbh->do($stmt);
if($rv < 0) {
print $DBI::errstr;
} else {
print "Table created successfully\n";
}
$dbh->disconnect();
執行上述程式後，它將在您的 test.db 中建立 COMPANY 表，並將顯示以下訊息：
Opened database successfully Table created successfully
注意 - 如果在任何操作中看到以下錯誤：
DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 398
在這種情況下，開啟 DBD-SQLite 安裝中提供的dbdimp.c 檔案，找到sqlite3_prepare() 函式，將其第三個引數從0更改為-1。最後，使用make安裝 DBD::SQLite，並執行make install以解決問題。
INSERT操作
下面的 Perl 程式演示如何在上面示例中建立的 COMPANY 表中建立記錄。
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 ));
my $rv = $dbh->do($stmt) or die $DBI::errstr;
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ););
$rv = $dbh->do($stmt) or die $DBI::errstr;
print "Records created successfully\n";
$dbh->disconnect();
執行上述程式後，它將在 COMPANY 表中建立給定的記錄，並將顯示以下兩行：
Opened database successfully Records created successfully
SELECT操作
下面的 Perl 程式演示如何從上面示例中建立的 COMPANY 表中提取和顯示記錄。
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt );
my $rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
print "ID = ". $row[0] . "\n";
print "NAME = ". $row[1] ."\n";
print "ADDRESS = ". $row[2] ."\n";
print "SALARY = ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();
執行上述程式後，將產生以下結果。
Opened database successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 20000 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
UPDATE操作
下面的 Perl 程式碼演示如何使用 UPDATE 語句更新任何記錄，然後從 COMPANY 表中提取和顯示更新後的記錄。
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ) {
print $DBI::errstr;
} else {
print "Total number of rows updated : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
print "ID = ". $row[0] . "\n";
print "NAME = ". $row[1] ."\n";
print "ADDRESS = ". $row[2] ."\n";
print "SALARY = ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();
執行上述程式後，將產生以下結果。
Opened database successfully Total number of rows updated : 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 25000 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
DELETE操作
下面的 Perl 程式碼演示如何使用 DELETE 語句刪除任何記錄，然後從 COMPANY 表中提取和顯示剩餘的記錄：
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(DELETE from COMPANY where ID = 2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ) {
print $DBI::errstr;
} else {
print "Total number of rows deleted : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
print "ID = ". $row[0] . "\n";
print "NAME = ". $row[1] ."\n";
print "ADDRESS = ". $row[2] ."\n";
print "SALARY = ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();
執行上述程式後，將產生以下結果。
Opened database successfully Total number of rows deleted : 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 25000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
SQLite - Python
本章將學習如何在 Python 程式中使用 SQLite。
安裝
SQLite3 可以使用 sqlite3 模組與 Python 整合，該模組由 Gerhard Haring 編寫。它提供了一個符合 PEP 249 中描述的 DB-API 2.0 規範的 SQL 介面。您無需單獨安裝此模組，因為它預設隨 Python 2.5.x 及更高版本一起提供。
要使用 sqlite3 模組，必須首先建立一個表示資料庫的連線物件，然後可以選擇建立一個遊標物件，這將有助於執行所有 SQL 語句。
Python sqlite3 模組 API
以下是重要的 sqlite3 模組例程，足以滿足您從 Python 程式中使用 SQLite 資料庫的需求。如果您正在尋找更復雜的應用程式，則可以檢視 Python sqlite3 模組的官方文件。
| 序號 | API和描述 |
|---|---|
| 1 | sqlite3.connect(database [,timeout ,其他可選引數]) 此 API 開啟到 SQLite 資料庫檔案的連線。您可以使用“:memory:”開啟到駐留在 RAM 中而不是磁碟上的資料庫的資料庫連線。如果成功開啟資料庫，則返回一個連線物件。 當多個連線訪問資料庫時，如果其中一個程序修改了資料庫，則 SQLite 資料庫將被鎖定，直到該事務提交為止。timeout 引數指定連線應該等待鎖消失多長時間，然後再引發異常。timeout 引數的預設值為 5.0（五秒）。 如果給定的資料庫名稱不存在，則此呼叫將建立資料庫。如果要建立資料庫在當前目錄之外的其他位置，則也可以指定帶有所需路徑的檔名。 |
| 2 | connection.cursor([cursorClass]) 此例程建立一個遊標，它將在使用 Python 進行資料庫程式設計的過程中使用。此方法接受單個可選引數 cursorClass。如果提供，則必須是擴充套件 sqlite3.Cursor 的自定義遊標類。 |
| 3 | cursor.execute(sql [, 可選引數]) 此例程執行 SQL 語句。SQL 語句可以是引數化的（即佔位符而不是 SQL 字面量）。sqlite3 模組支援兩種佔位符：問號和命名佔位符（命名樣式）。 例如 - cursor.execute("insert into people values (?, ?)", (who, age)) |
| 4 | connection.execute(sql [, 可選引數]) 此例程是遊標物件提供的上述 execute 方法的快捷方式，它透過呼叫 cursor 方法建立一箇中間遊標物件，然後使用給定的引數呼叫遊標的 execute 方法。 |
| 5 | cursor.executemany(sql, seq_of_parameters) 此例程針對在序列 sql 中找到的所有引數序列或對映執行 SQL 命令。 |
| 6 | connection.executemany(sql[, parameters]) 此例程是一個快捷方式，它透過呼叫 cursor 方法建立一箇中間遊標物件，然後使用給定的引數呼叫 cursor.s executemany 方法。 |
| 7 | cursor.executescript(sql_script) 此例程以指令碼形式執行多個 SQL 語句。它首先發出 COMMIT 語句，然後執行它作為引數獲得的 SQL 指令碼。所有 SQL 語句都應該用分號 (;) 分隔。 |
| 8 | connection.executescript(sql_script) 此例程是一個快捷方式，它透過呼叫 cursor 方法建立一箇中間遊標物件，然後使用給定的引數呼叫遊標的 executescript 方法。 |
| 9 | connection.total_changes() 此例程返回自開啟資料庫連線以來已修改、插入或刪除的資料庫行的總數。 |
| 10 | connection.commit() 此方法提交當前事務。如果您不呼叫此方法，則自上次呼叫 commit() 以來所做的任何操作都無法從其他資料庫連線中看到。 |
| 11 | connection.rollback() 此方法回滾自上次呼叫 commit() 以來對資料庫所做的任何更改。 |
| 12 | connection.close() 此方法關閉資料庫連線。請注意，這不會自動呼叫 commit()。如果您在首先呼叫 commit() 之前關閉資料庫連線，則您的更改將會丟失！ |
| 13 | cursor.fetchone() 此方法提取查詢結果集的下一行，返回單個序列，或者當沒有更多資料可用時返回 None。 |
| 14 | cursor.fetchmany([size = cursor.arraysize]) 此例程提取查詢結果的下一組行，返回一個列表。當沒有更多行可用時，將返回一個空列表。該方法嘗試提取 size 引數指示的儘可能多的行。 |
| 15 | cursor.fetchall() 此例程提取查詢結果的所有（剩餘）行，返回一個列表。當沒有行可用時，將返回一個空列表。 |
連線到資料庫
下面的 Python 程式碼演示如何連線到現有資料庫。如果資料庫不存在，則會建立它，最後返回一個數據庫物件。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
在這裡，您還可以將資料庫名稱提供為特殊名稱:memory: 以在 RAM 中建立資料庫。現在，讓我們執行上面的程式在當前目錄中建立我們的資料庫test.db。您可以根據需要更改路徑。將上面的程式碼儲存在 sqlite.py 檔案中，並按如下所示執行它。如果資料庫成功建立，則會顯示以下訊息。
$chmod +x sqlite.py $./sqlite.py Open database successfully
建立表
下面的 Python 程式將用於在先前建立的資料庫中建立表。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute('''CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
print "Table created successfully";
conn.close()
執行上述程式後，它將在您的test.db中建立COMPANY表，並將顯示以下訊息：
Opened database successfully Table created successfully
INSERT操作
下面的 Python 程式演示如何在上面示例中建立的 COMPANY 表中建立記錄。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (1, 'Paul', 32, 'California', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");
conn.commit()
print "Records created successfully";
conn.close()
執行上述程式後，它將在 COMPANY 表中建立給定的記錄，並將顯示以下兩行：
Opened database successfully Records created successfully
SELECT操作
下面的 Python 程式演示如何從上面示例中建立的 COMPANY 表中提取和顯示記錄。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
print "Operation done successfully";
conn.close()
執行上述程式後，將產生以下結果。
Opened database successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 20000.0 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
UPDATE操作
下面的 Python 程式碼演示如何使用 UPDATE 語句更新任何記錄，然後從 COMPANY 表中提取和顯示更新後的記錄。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit()
print "Total number of rows updated :", conn.total_changes
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
print "Operation done successfully";
conn.close()
執行上述程式後，將產生以下結果。
Opened database successfully Total number of rows updated : 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 25000.0 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
DELETE操作
下面的 Python 程式碼演示如何使用 DELETE 語句刪除任何記錄，然後從 COMPANY 表中提取和顯示剩餘的記錄。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("DELETE from COMPANY where ID = 2;")
conn.commit()
print "Total number of rows deleted :", conn.total_changes
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
print "Operation done successfully";
conn.close()
執行上述程式後，將產生以下結果。
Opened database successfully Total number of rows deleted : 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 20000.0 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully