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;

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

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

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

標籤：其它

上一篇：MySQL InnoDB 鎖的二三事

下一篇：他來了！袋鼠云大資料基礎平臺EasyMR正式上線