SQLite基礎語法速用大法（Flutter）

前記

（可跳過這段來自本up的羅里吧嗦。。。）

在做上一個項目的時候，需要用到本地資料庫，以前做公司項目用的是輕量級資料庫Realm，做自己小項目用的是greenDAO，大學學的是SQL server，但是在flutter中，相關插件用的資料庫是SQLite（sqflite插件），但本人還沒接觸過SQLite，問了後臺同事，他們用的也是SQLite，說大同小異，我就想著僅憑大學記憶，快速過一遍SQLite知識點，應該挺快的，想著抓點重要的就夠了。現在項目告一段落，就來寫這篇博客記錄一下（感覺有點標題黨了哈哈哈）。

正文

小重點：

1、SQLite 是不區分大小寫的，但也有一些命令是大小寫敏感的，比如 GLOB 和 glob 在 SQLite 的語句中有不同的含義。

2、註釋大法：

（1）-- 這是一句註釋

（2）/* 這是一句註釋 */

一、數值類型

用於指定任何對象的數據類型的屬性。

1、存儲類

（1）NULL：NULL值（空值）。

（2）INTEGER：帶符號的整數。

（3）REAL：浮點值。

（4）TEXT：文本字元串值。

（5）BLOB：二進位大對象，Binary Large Object。

2、親和類型（優先採用）

當數據插入時，該欄位的數據將會優先採用親緣類型作為該值的存儲方式。

（1）TEXT：數值型數據插入前先轉為文本格式再插入。

（2）NUMERIC：如果轉換操作不會導致數據丟失以及完全可逆，則會轉換為INTEGER和REAL，如果失敗則會以TEXT存儲。對於NULL和BLOB類型則不作類型轉換存儲。註意：浮點類型的文本，如"300.0"，轉為INTEGER不會丟失數值信息，則會轉換為INTEGER類型存儲。

（3）INTEGER：基本等同於NUMERIC，區別在於執行CAST表達式（轉換類型）時。

eg: 將column欄位 文本類型轉換為INTEGER類型。

SELECT * FROM Table ORDER BY CAST (column as INTEGER);

（4）REAL：基本等同於NUMERIC，區別在於不會將"300.0"這樣的文本數據轉為INTEGER類型存儲。

（5）NONE：不做轉換，以數據本身的數據類型存儲。

3、SQLite 親和類型(Affinity)及類型名稱（以下作瞭解）

（1）親和類型：INTEGER
對應數據類型：

• INT
• INTEGER
• TINYINT
• SMALLINT
• MEDIUMINT
• BIGINT
• UNSIGNED BIG INT
• INT2
• INT8

（2）親和類型：TEXT
對應數據類型：

• CHARACTER(20)
• VARCHAR(255)
• VARYING CHARACTER(255)
• NCHAR(55)NATIVE
• CHARACTER(70)
• NVARCHAR(100)
• TEXT
• CLOB

（3）親和類型：NONE
對應數據類型：

• BLOB
• no datatype specified

（4）親和類型：REAL
對應數據類型：

• REAL
• DOUBLE
• DOUBLE PRECISION
• FLOAT

（5）親和類型：NUMERIC
對應數據類型：

• NUMERIC
• DECIMAL(10,5)
• BOOLEAN
• DATE
• DATETIME

4、Boolean數據類型

SQLite 沒有單獨的 Boolean 存儲類。相反，布爾值被存儲為整數 0（false）和 1（true）。

註：在我的項目中，直接存儲為INTEGER類型，0為false，1為true。

5、Date 與 Time數據類型

SQLite 沒有一個單獨的用於存儲日期和/或時間的存儲類，但 SQLite 能夠把日期和時間存儲為 TEXT、REAL 或 INTEGER 值。

註：在我的項目中，存儲的是時間戳，要用時取出時間戳轉換為具體的時間格式 [文本字元串] 顯示。

Date 與 Time 數據類型

二、資料庫相關操作語句

1、創建、附加、分離資料庫

在此教程不作sqlite的數據操作的詳細講解，後續有時間補充。

原因：移動端的資料庫操作一般根據插件的語句來。

三、表相關操作語句

SQLite語句是以關鍵字開始，分號結束的語句。

關鍵字：SELECT、INSERT、UPDATE、DELETE、ALTER、DROP。

註： 在此提出關註 ALTER 關鍵字，本人因為把 ALTER 寫成 ALERT 導致語句執行錯誤。

1、表的創建

基本語法：（每一欄位用逗號隔開，最後一個欄位不用寫逗號）

CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype
);

eg：創建三張公司表，欄位id自增，名稱不為空，年齡不為空，工作崗位，工資。（結合Flutter中 sqflite 插件的語句寫法。）

CREATE TABLE Company(
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name TEXT NOT NULL,
   age INTEGER NOT NULL,
   job TEXT,
   salary REAL,
   hobby TEXT
);

CREATE TABLE Unimportant(
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name TEXT NOT NULL,
   age INTEGER NOT NULL,
   job TEXT,
   salary REAL,
   hobby TEXT
);

CREATE TABLE Company_Second (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name TEXT NOT NULL,
   age INTEGER NOT NULL,
   job TEXT,
   salary REAL,
   hobby TEXT
);

2、表的刪除

註：謹慎使用，一旦刪了重要的表，可能會G！因為一旦一個表被刪除，表中所有信息也將永遠丟失。3（重要事情說三遍！！！）涉及刪除操作的（無論是刪除庫、表，還是數據）一定要慎之又慎！！！*

我的項目里沒有刪除操作的語句。創建了，即使不用也留著，萬一後續又要用了呢。

基本語法：

DROP TABLE database_name.table_name;

eg：刪除不重要表。

DROP TABLE Unimportant;

3、表的數據插入

（1）基本語法：

i. 該語句適用於所有欄位均插入數據或指定欄位插入數據。

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

ii. 或者不需要指定列，即每個欄位都有值插入。但用該語句，欄位一定要一 一對應，否則數據容易插入錯誤。

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

eg：往Company表中插入幾組數據。

INSERT INTO Company(id, name, age, job, salary, hobby)
VALUES (1, 'Paul', 32, 'CEO', 20000.00, 'basketball');

INSERT INTO Company(id, name, age, job, salary, hobby)
VALUES (2, 'Allen', 25, 'COO', 15000.00, 'soccer');

INSERT INTO Company(id, name, age, job, salary)
VALUES (3, 'Teddy', 23, 'CFO', 10000.00);

INSERT INTO Company(id, name, age, job, salary, hobby)
VALUES (4, 'Mark', 25, 'CTO', 65000.00, 'badminton');

INSERT INTO Company(id, name, age, job, salary)
VALUES (5, 'David', 27, 'CIO', 25000.00);

INSERT INTO Company(id, name, age, job, salary)
VALUES (6, 'Kim', 22, 'CHO', 15000.00);

或：

INSERT INTO Company VALUES (7, 'James', 24, 'COO', 30000.00);

（2）用一個表填充另一個表：

通過在一個有一組欄位的表上使用 select 語句，填充數據到另一個表中。WHERE 引出條件，可寫可不寫。

INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];

eg：把Company表中id為1的人員的id、名字、年齡、工作崗位、工資填充到Company_Second表中。

INSERT INTO Company_Second [(id, name, age, job, salary, hobby)] 
   SELECT id, name, age, job, salary, hobby
   FROM Company
   WHERE id = 1;

4、表的數據選擇

註：選擇的返回結果是一個結果集，一般為列表類型。

（1）基本語法：

SELECT column1, column2, columnN FROM table_name;

想獲取所有的欄位，可以用：

SELECT * FROM table_name;

eg：選擇Company表中所有人的id、名稱、工作崗位。

SELECT id, name, job FROM Company;

eg2：選擇Company表中所有人的所有信息。

SELECT * FROM Company;

5、運算符

（1）算術運算符（+、-、*、/、%）

i. +、-、*、/：就是加、減、乘、除以。

ii. %：取模。被除數 除以 除數 得到的餘數。

eg：6 % 2 = 0

eg2： 5 % 2 = 1

（2）比較運算符（加粗為常用的比較運算符）
假設變數 a=10，變數 b=20，則：
比較運算符列表

（3）邏輯運算符（常用，重點掌握）

例子結合WHERE子句使用。

邏輯運算符列表

運算符	描述
AND	AND 運算符允許在一個 SQL 語句的 WHERE 子句中的多個條件的存在。
BETWEEN	BETWEEN 運算符用於在給定最小值和最大值範圍內的一系列值中搜索值。
EXISTS	EXISTS 運算符用於在滿足一定條件的指定表中搜索行的存在。
IN	IN 運算符用於把某個值與一系列指定列表的值進行比較。
NOT IN	IN 運算符的對立面，用於把某個值與不在一系列指定列表的值進行比較。
LIKE	LIKE 運算符用於把某個值與使用通配符運算符的相似值進行比較。
GLOB	GLOB 運算符用於把某個值與使用通配符運算符的相似值進行比較。GLOB 與 LIKE 不同之處在於，它是區分大小寫的。
NOT	NOT 運算符是所用的邏輯運算符的對立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定運算符。
OR	OR 運算符用於結合一個 SQL 語句的 WHERE 子句中的多個條件。
IS NULL	NULL 運算符用於把某個值與 NULL 值進行比較。
IS	IS 運算符與 = 相似。
IS NOT	IS NOT 運算符與 != 相似。
	連接兩個不同的字元串，得到一個新的字元串。
UNIQUE	UNIQUE 運算符搜索指定表中的每一行，確保唯一性（無重覆）。

（4）位運算符（不常用）
假設變數 a=10，變數 b=20，則：

位運算符列表

運算符	描述 + 實例
&	如果同時存在於兩個操作數中，二進位 AND 運算符複製一位到結果中。 (A & B) 將得到 12，即為 0000 1100
	如果存在於任一操作數中，二進位 OR 運算符複製一位到結果中。 將得到 61，即為 0011 1101
~	二進位補碼運算符是一元運算符，具有"翻轉"位效應，即0變成1，1變成0。 (~A ) 將得到 -61，即為 1100 0011，一個有符號二進位數的補碼形式。
<<	二進位左移運算符。左操作數的值向左移動右操作數指定的位數。 A << 2 將得到 240，即為 1111 0000
>>	二進位右移運算符。左操作數的值向右移動右操作數指定的位數。 A >> 2 將得到 15，即為 0000 1111

6、表達式

表達式是一個或多個值、運算符和計算值的SQL函數的組合。

（1）基本語法：

SELECT column1, column2, columnN 
FROM table_name 
WHERE [CONDITION | EXPRESSION];

（2）布爾表達式

基本語法：

SELECT column1, column2, columnN 
FROM table_name 
WHERE SINGLE VALUE MATCHING EXPRESSION;

eg：查詢Company表中 工資為15000 的人員的所有信息。

SELECT * FROM Company WHERE salary = 15000;

（3）數值表達式

基本語法：

SELECT numerical_expression as  OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

eg：以下數值表達式的結果為：ADDITION = 21

SELECT (15 + 6) AS ADDITION

註：有幾個內置的函數，比如 avg()、sum()、count()，等等，執行被稱為對一個表或一個特定的表列的彙總數據計算。

eg：查詢Company表中數據的總條數，並賦值給RECORDS，結果為：RECORDS = 7

SELECT COUNT(*) AS "RECORDS" FROM Company; 

（4）日期表達式

基本語法：以下日期表達式的結果為：datetime('now','localtime') = 2022-09-21 16:47:36

SELECT datetime('now','localtime');

7、WHERE 子句

用於指定從一個表或多個表中獲取數據的條件。

基本語法：

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]

eg：查詢Company表中 年齡大於等於25，並且工資大於等65000的人員所有信息。

SELECT * FROM Company WHERE Age >= 25 AND Salary >= 65000;

eg2：查詢Company表中年齡大於等於25，或者工資大於等65000的人員所有信息。

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

eg3：查詢Company表中 name 欄位以 'Ja' 開始，'Ja' 之後的字元不做限制 的所有人員的所有信息。

SELECT * FROM Company WHERE name LIKE 'Ja%';

或：

SELECT * FROM Company WHERE name GLOB 'ja*';

eg4：查詢Company表中 age 的值為 25 或 27 的所有人員的所有信息。

SELECT * FROM Company WHERE age IN (25, 27);

eg5：查詢Company表中 age 的值既不是 25 也不是 27 的所有人員的所有信息。

SELECT * FROM Company WHERE age NOT IN (25, 27);

eg6：查詢Company表中 age 的值在 25 和 27 之間 的所有人員的所有信息。

SELECT * FROM Company WHERE age BETWEEN 25 AND 27;

eg7：查詢Company表中 工資大於55000 的人員的年齡。

SELECT age FROM Company 
        WHERE EXISTS (SELECT age FROM Company WHERE salary > 55000);

解析：查詢從內往外剝，就是從最小的條件語句開始看起（最後一個條件看起），即 WHERE salary > 55000，查詢存在的工資大於55000人員的年齡，查詢結果集是age列。

eg8：查詢Company表中 年齡大於【工資大於55000 的人員的年齡】的所有人員的所有信息。

SELECT * FROM Company 
        WHERE age > (SELECT age FROM Company WHERE salary > 55000);

解析：同理，從內往外剝，最底層的查詢條件為小括弧中的 SELECT age FROM Company WHERE salary > 55000;，即查詢工資大於55000的年齡，假設查詢結果為年齡 25。接著繼續上一層的條件查詢：age > (...)，即為 age > 25，所以最後一層的查詢可以簡化為：SELECT * FROM Company WHERE age > 25;， 結果就是查詢 年齡大於25 的所有員工的所有信息。

8、AND 和 OR 運算符

允許在一個 SQL 語句的 WHERE 子句中的多個條件的存在。

（1）AND 運算符

使用 AND 運算符時，只有當所有條件都為真（true）時，整個條件為真（true）。例如，只有當 condition1 和 condition2 都為真（true）時，[condition1] AND [condition2] 為真（true）。

基本語法：

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

eg：查詢Company表中 age大於等於25 並且 salary大於等於65000 的所有員工的所有信息。

SELECT * FROM Company WHERE age >= 25 AND salary >= 65000;

（2）OR 運算符

使用 OR 運算符時，只要當條件中任何一個為真（true）時，整個條件為真（true）。例如，只要當 condition1 或 condition2 有一個為真（true）時，[condition1] OR [condition2] 為真（true）。

基本語法：

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

eg：查詢Company表中 age大於等於 25 或salary大於等於 65000 的所有員工的所有信息。

SELECT * FROM Company WHERE age >= 25 OR salary >= 65000;

9、UPDATE 語句

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

基本語法：

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

eg：更新Company表中 id為6的員工的hobby為Football 。

UPDATE Company SET hobby = 'Football' WHERE id = 6;

eg2：更新Company表中 所有hobby 和 salary 列的值。

UPDATE Company SET hobby = 'Badminton', salary = 20000.00;

執行以上語句，Company表中hobby 和 salary 列的值全部都為Badminton和20000.00。

10、DELETE 語句

用於刪除表中已有的記錄。（輕易不使用！！！若要刪除數據，可使用軟刪除，即用一個欄位來標識該數據是否被刪除）

基本語法：

DELETE FROM table_name
WHERE [condition];

eg：刪除Company表中 id為7 的員工數據。

DELETE FROM Company WHERE id = 7;

eg2：刪除Company表中 所有 數據。

DELETE FROM Company;

11、LIKE 子句（不區分大小寫）

用來匹配通配符指定模式的文本值。LIKE 運算符將返回真（true），也就是 1。以下兩個通配符與 LIKE 運算符一起使用（這兩個符號可以被組合使用）。

（1）百分號 （%）：表示零個、一個或多個數字或字元。

（2）下劃線 （_）：表示一個單一的數字或字元。

基本語法：

SELECT column_list 
FROM table_name
WHERE column LIKE 'XXXX%'

or 

SELECT column_list 
FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT column_list 
FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT column_list 
FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT column_list 
FROM table_name
WHERE column LIKE '_XXXX_'

eg：SELECT * FROM Company + (以下語句)

12、GLOB子句（區分大小寫）

用來匹配通配符指定模式的文本值。GLOB 運算符將返回真（true），也就是 1。以下兩個通配符與 GLOB 運算符一起使用（這兩個符號可以被組合使用）。

（1）星號 （*）：表示零個、一個或多個數字或字元。

（2）問號 （?）：表示一個單一的數字或字元。

基本語法：

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 '????'

eg：SELECT * FROM Company + (以下語句)

13、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]

eg：從Company表中提取 6名員工 的所有信息。

SELECT * FROM Company LIMIT 6;

eg2：從Company表中的 第二位開始提取 3 名員工 的所有信息。

SELECT * FROM Company LIMIT 3 OFFSET 2;

14、ORDER BY 子句

用來基於一個或多個列按升序或降序順序排列數據。

關鍵詞：ASC | DESC

（1）ASC：從小到大，升序排列，預設值ASC。

（2）DESC：從大到小，降序排列。

基本語法：

SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

可以使用多個列排序：

SELECT
   select_list
FROM
   table
ORDER BY
    column_1 ASC,
    column_2 DESC;

column_1 與 column_2 如果後面不指定排序規則，預設為 ASC 升序，以上語句按 column_1 升序，column_2 降序讀取。

SELECT
   select_list
FROM
   table
ORDER BY
    column_1,
    column_2 DESC;

eg：查詢Company表中 按照工資升序排序 的所有員工的所有信息。

SELECT * FROM Company ORDER BY salary ASC;

eg2：查詢Company表中 按 name 和 salary 升序排序 的所有員工的所有信息。

SELECT * FROM Company ORDER BY name, salary ASC;

eg3：查詢Company表中 按 NAME 降序排序 的所有員工的所有信息。

SELECT * FROM Company ORDER BY name DESC;

15、GROUP BY 子句

用於對相同的數據進行分組。

註：在 SELECT 語句中，GROUP BY 子句放在 WHERE 子句之後，放在 ORDER BY 子句之前。

基本語法：

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

eg：查詢Company表中 每個客戶的工資總額。

SELECT name, SUM(salary) FROM Company GROUP BY name;

eg2：查詢Company表中 按 name 列進行分組的、按 name 列 升序 的每個客戶的工資總額。

SELECT name, SUM(salary) FROM Company GROUP BY name ORDER BY name;

以上也是把 ORDER BY 子句與 GROUP BY 子句一起使用的例子。

16、HAVING 子句

指定條件來過濾將出現在最終結果中的分組結果。WHERE 子句在所選列上設置條件，而HAVING 子句在由 GROUP BY 子句創建的分組上設置條件。

註：HAVING 子句必須放在 GROUP BY 子句之後，必須放在 ORDER BY 子句之前。

基本語法：

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

eg：查詢Company表中 按名稱計數小於 2 的所有記錄。

SELECT * FROM Company GROUP BY name HAVING count(name) < 2;

17、DISTINCT 關鍵字

用來消除所有重覆的記錄，並只獲取唯一一次記錄。

基本語法：

SELECT DISTINCT column1, column2,.....columnN 
FROM table_name
WHERE [condition]

eg：查詢Company表中 所有不重覆的name。

SELECT DISTINCT name FROM Company;

---------------------------------------------------------分割線---------------------------------------------------------------

以上，後續會再補充。各位小伙伴，歡迎提出問題~

我的學習文檔是 菜鳥教程，致謝。