SQL簡介

SQL是結構化查詢語言Structured Query Language的縮寫

維基百科

這篇只針對SQLite喔，有些語法可能不適用

每次要用SQLite語法時都還要查老半天，應該是下次要用SQLite時已經不知道過了幾百年了，所以把常用的語法整理到這頁要找比較方便

SQLite官網

資料庫介紹

資料表(Table) 可以想像成是Microsoft Excel中的一張工作表。一張資料表會由許多「欄位」(Column)和「資料列」(Row)組成，用來儲存某一類型的資料

資料庫(Database) 通常會包含多張彼此有關聯的資料表

例如：購物網站可能會有的資料表會有

Users:使用者資料
Products:商品資料
Orders:訂單資料

這些資料表組合起來，就能儲存和管理購物網站所需要的資料

主鍵(Primary key) 具有唯一性，是資料表中用來辨識每一筆資料的欄位

以上述購物網站的Users資料表為例，”會員編號”就可以作為主鍵的條件。不可重複且可辨識該使用者

因此主鍵有2個特性

唯一性：同一張資料表中，每一筆資料的主鍵值不能重複
值不可為空(NULL)：主鍵必須有值，因為每一筆資料都必須能夠被辨識

外鍵(Foreign key) 是用來建立與其他資料表有關聯的欄位

以上述購物網站的Orders資料表為例，”訂購人”的欄位可以是對應User資料表中的”會員編號”，如此便把Orders資料表與Users資料表互相關聯

總結來說：

主鍵(Primary key)：用來辨識「我是誰」
外鍵(Foreign key)：用來告訴資料庫「誰和我有關係」

.NET環境安裝SQLite套件

Visual Studio

打開NuGet套件管理視窗

搜尋：SQLite

安裝「System.Data.SQLite」

其他還有像是System.Data.SQLite.Core、System.Data.SQLite.Linq的東西可以安裝

我目前還沒用到，所以就沒裝了

.NET指令環境(終端機、VSCode)

終端機進入到專案資料夾中

輸入指令：dotnet add package System.Data.SQLite

Nuget網頁

常用指令

SQLite也有資料庫管理軟體喔

SQLiteStudio有提供建立、刪除、修改等基本功能，因此就算不是寫程式也是可以用來儲存資料。另外也有SQL語法編輯器可使用SQL指令執行，就像是個輕便而且不用安裝的MySQL呢

SQLiteStudio下載

範例資料庫，點我下載

開啟SQL編輯器的方式如下圖

"開啟SQL編輯器的方式"

SQLite資料型態

NULL
INTEGER
REAL
TEXT
BLOB：根據MySQL的語法表示，是儲存二進制文件用的。如：圖片、影片、檔案之類的
DATETIME：儲存時間的格式（yyyy-MM-DD hh:mm:ss.ms）
BOOLEAN：SQLite沒這個東西，所以要用整數0(假)或1(真)來儲存。官網有寫從2018-04-02的版本開始已經可以認得”TRUE”跟”FALSE”了 ~~，但不知道可以幹嘛~~

建立資料表

CREATE TABLE MyTable (_AI INTEGER PRIMARY KEY AUTOINCREMENT,DateTime DATETIME,Topic TEXT,Message TEXT);

可以加上IF NOT EXISTS來判斷資料表是否存在，不存在才建立

CREATE TABLE IF NOT EXISTS MyTable (_AI INTEGER PRIMARY KEY AUTOINCREMENT,DateTime DATETIME,Topic TEXT,Message TEXT);

使用 CREATE TABLE 可以建立一個新的資料表。加上 IF NOT EXISTS 的判斷，可以確保只有在該表不存在時才會執行建立操作，避免重複建立相同名稱的資料表而導致錯誤

在上述SQL指令中：

資料表名稱為MyTable
_AI：是一個Integer型態的主鍵，並套用AUTOINCREMENT屬性，表示在插入新資料時此欄位會自動遞增
DateTime：是一個DATETIME型態的欄位，可以儲存時間的資料
Topic：是一個TEXT型態的欄位，可以儲存字串資料
Message：是一個TEXT型態的欄位，可以儲存字串資料

建立表格的執行結果如下圖所示：

UPDATE sqlite_sequence SET seq=0 WHERE NAME="MyTable"

此指令可設定AUTOINCREMENT的值（正常操作下不建議使用）

取得欄位資料

PRAGMA table_info('MyTable')

上述指令可以查詢在MyTable資料表內各欄位的資料

執行結果如下：

插入資料

INSERT OR IGNORE INTO `MyTable` VALUES (NULL,DATETIME('NOW', 'LOCALTIME'),'Topic資料','Message資料')

使用INSERT將資料插入到MyTable的資料表中，並指定各個欄位的值。

在上述SQL指令中：

OR IGNORE：表示如果插入的資料與資料表中已有的資料重複，則忽略這條插入指令，不會導致主鍵重複的錯誤。這在避免插入重複資料時很有用
VALUES：是指定資料表內各欄位的值
(NULL, DATETIME(‘now’, ‘localtime’), ‘Topic資料’, ‘Message資料’)：這是要插入的資料值的清單，按照資料表中欄位的順序進行排列。在這個例子中，它指定了四個欄位的值。第一個值是 NULL，表示主鍵_AI會自動遞增。第二個值是DATETIME(‘now’, ‘localtime’)，表示目前的日期和時間。接下來的兩個值是字串’Topic資料’和’Message資料’，分別是Topic和Message欄位的值

修改資料

UPDATE `MyTable`
SET `Topic` = '新的Topic值', `Message` = '新的Message值'
WHERE `AI` = 1

使用 UPDATE 修改資料表中的資料，並透過 SET 指定要修改的欄位和值。

在上述 SQL 指令中：

UPDATE：指定要修改的資料表
SET：指定要修改的欄位，以及修改後的值
WHERE：指定要修改哪些資料。只有符合條件的資料才會被修改

查詢資料

列出所有資料

SELECT * FROM "Mytable"

列出MyTable中所有資料

列出特定欄位

SELECT `Topic`,`Message` FROM `MyTable`

從MyTable中列出Topic及Message欄位的所有資料

SELECT的執行結果如下圖所示：

加入條件搜尋

WHERE

SELECT * FROM 'MyTable' WHERE `Topic`='Run'

從MyTable中篩選出Topic欄位是’Run’的所有資料

SELECT-WHERE的執行結果如下圖所示：

LIKE

SELECT * FROM 'MyTable' WHERE `Message` LIKE '%測試%'

從MyTable中篩選出Message欄位中的值包含”測試”的項目

在SQL中LIKE是模糊配對的操作，可以搜尋特定格式的資料，上述例子中的%是代表萬用字元，因此可以解讀為資料中包含”測試”的所有資料

題外話

‘%測試’：”測試”在句尾
‘測試%’：”測試”在句首

SELECT-LIKE的執行結果如下圖所示：

PL/SQL

最近工作開始使用PL/SQL，之後應該會常常用到。

同樣都是SQL一份子就不開新頁了，把工作上可能會用到的語法整理在這裡。畢竟久久沒用，幾百年後要用可能也忘了。~~我也懶惰再去四處查~~

簡介

PL/SQL是Procedural Language/SQL的縮寫，是Oracle所提供的一種程式語言，可以在SQL的基礎上加入變數、條件判斷、迴圈等程式語法，讓SQL不再只是下指令，而是可以依照自己的需求寫出一個可以執行的流程

維基百科

基本架構

PL/SQL與一般SQL相比，多了程式語言的架構

基本架構會是：

DECLARE
 // 定義變數
BEGIN
 // 程式內容
EXCEPTION
 // 例外處理
END;

DECLARE：是定義變數的區塊。如果需要使用變數，都需要在這個區塊宣告
BEGIN：程式主要執行的區塊。SQL語法或程式邏輯都會在這個區塊中執行
EXCEPTION：是例外處理的區塊。如果在BEGIN區塊中執行時發生錯誤，就可以在這個區塊處理那些錯誤
END;：表示整個PL/SQL程式區塊結束

命名區塊與匿名區塊

PL/SQL的程式區塊可以分為命名區塊(Named Block)與匿名區塊(Anonymous Block)

命名區塊

命名區塊是有名字的PL/SQL程式區塊，可以儲存在Oracle資料庫中，之後需要時再執行

常見的命名區塊：

Procedure(程序)
Function(函數)
Trigger(觸發器)
Package(套件)

Procedure

CREATE OR REPLACE PROCEDURE MyProgram
AS
BEGIN
 DBMS_OUTPUT.PUT_LINE('Hello PL/SQL');
END;

我定義了一支名叫”MyProgram”的程序，日後需要時便能直接呼叫它來執行

EXEC MyProgram

※ OR REPLACE表示如果不存在該程序就建立新的，存在就覆寫

Function

Function與Procedure最大的差別是在執行完畢後，會回傳一個值

CREATE OR REPLACE FUNCTION AddNumber(num1 NUMBER,num2 NUMBER)
RETURN NUMBER
AS
BEGIN
 RETURN num1 + num2;
END;

我定義了一支叫做AddNumber的Function，並傳入兩個數字num1和num2。RETURN NUMBER表示這個Function最後會回傳一個NUMBER型別的值

匿名區塊

逆名區塊就是沒有名字的PL/SQL程式區塊。通常是寫好就直接執行，執行完就結束,不會把這個程式區塊保存下來

通常是處理臨時性、一次性的資料，或是想快速執行一段PL/SQL就會使用匿名區塊

Label/GOTO

PL/SQL也支援Label與GOTO，可以透過Label指定一個標籤，再透過GOTO跳到該標籤的位置執行

BEGIN
 GOTO MyLabel;
 
 DBMS_OUTPUT.PUT_LINE('這行會被跳過');
 
 <<MyLabel>>
 DBMS_OUTPUT.PUT_LINE('跳到這裡');
END;

※不過這種寫法和其他程式語言一樣，如果大量使用GOTO讓程式到處跳轉，會讓程式的可讀性與可維護性變差

判斷

用來比較條件的結果，決定程式接下來應採取的行動，這種功能就稱為「判斷」。PL/SQL主要可以使用IF與CASE來進行判斷

IF

IF是基本條件的判斷

IF 條件 THEN
 // 條件為true時執行
END IF;

如果要加上不成立的狀況，則可增加ELSE區塊

IF 條件 THEN
 // 條件為true時執行
ELSE
 // 條件為false時執行
END IF;

如果需要多種條件判斷，則可使用ELSEIF

IF 條件1 THEN
 // 條件1為true時執行
ELSEIF 條件2 THEN
 // 條件2為true時執行
.
.
.
ELSE
 // 條件為false時執行
END IF;

CASE

CASE主要用於多選一的判斷，當有多種條件要判斷時，使用CASE會比巢狀IF更好閱讀。~~ChatGPT講的我不知道~~

CASE 條件
 WHEN (結果1) THEN
  // 條件與結果1相符時執行
 WHEN (結果2) THEN
  // 條件與結果2相符時執行
 WHEN (結果3) THEN
  // 條件與結果3相符時執行
.
.
.
 ELSE
  // 條件與所有結果都不相符時執行
END;

迴圈

一樣的事情重複做。PL/SQL常見的迴圈是LOOP、WHILE與FOR

LOOP

LOOP是最基本的迴圈，會一直重複執行，直到使用EXIT才會離開迴圈

LOOP
 // 要重複執行的程式

 EXIT WHEN 條件;
END LOOP;

※LOOP沒有終止條件，因此如果沒有使用EXIT會變成無窮迴圈

WHILE

WHILE也是迴圈的一種，但在每次重複執行前會判斷條件是否成立，成立才會繼續重複執行

WHILE 條件 LOOP
 // 要重複執行的程式
END LOOP;

FOR

FOR迴圈包含一個迴圈計數器，適合用在知道要執行幾次的情況。不過需要注意的是PL/SQL的迴圈計數器是由系統自己管理，而不是由程式設計師設計一個增加或減少的規則。

ChatGPT跟我補充，PL/SQL的是稱為 迴圈變數 (loop variable)而不像其他程式語言的 迴圈計數器 (loop counter)，由於是系統自己管理迴圈變數的關係，PL/SQL中的FOR迴圈只支援遞增或遞減，無法像是其他語言使用i+=2之類的規則。

FOR 迴圈變數 IN 起始值 .. 結束值 LOOP
 // 要重複執行的程式
END LOOP;

游標(Cursor)

游標可以理解為”指向查詢結果中資料位置的指標”

通常使用SELECT查詢資料表時，可能會一次取出很多筆資料，如果要一筆一筆處理資料便可使用游標。使用游標前後要透過OPEN與CLOSE進行開關，再搭配FETCH讀取出一筆一筆的資料(我對游標的機制的感覺像是C#的foreach)

DECLARE
 CURSOR MyCursor IS
  SELECT Topic,Message
  FROM MyTable;
BEGIN
 FOR logData IN MyCursor LOOP
  DBMS_OUTPUT.PUT_LINE(logData.Topic);
 END LOOP;
END

※使用FOR迴圈搭配游標時，OPEN、FETCH和CLOSE會由系統自動處理，不需要自己寫

對照一下C#的foreach

foreach(var logData in MyCursor)
{
    Debug.WriteLine(logData.Topic);
}

PL/SQL分為隱含游標(Implicit Cursor)與明確游標(Explicit Cursor)

明確游標 即是由使用者自行宣告與管理的游標(上述例子中的MyCursor)

隱含游標 則是由PL/SQL系統中自動建立與管理的游標。平時執行INSERT、UPDATE、DELETE或SELECT INTO時，即使沒有宣告游標，系統仍會在背後使用游標進行處理

SQLite 筆記

常用的SQLite語法