SQLite必知必會————學習筆記

文章目錄

0 背景
1 常識
2 SELECT語句

2.0 别名
2.1 篩選
2.2 函數
2.3 過濾分組

3 聯結（最重要的特性）

3.1 完全限定名
3.2 笛卡爾積（叉聯結）
3.2,等值聯結（内聯結）
3.3 自聯結和子查詢、自然聯結
3.4 外聯結
3.4 全聯結（SQLite不支援）

4 并/複合查詢

4.1 UNION（會自動去重）
4.2 UNION ALL

5 插入語句

5.1 插入檢索的資料
5.2 從一個表複制到另一個表

6 更新值
7 删除值

7.1 删除列值
7.2 删除行值
7.3 删除表

8 建立表/更新表
9 視圖
10 存儲過程（SQLite不支援）
11 事務
12 遊标/步驟（Step）
13 限制

13.1 主鍵
13.2 外鍵
13.3 唯一限制

14 索引
15 觸發器（特殊的存儲過程）
16 安全性
17 資料規範

0 背景

因為項目要涉及到大量的操作資料庫的操作，是以要熟練掌握基本的SQL（Structured Query Language）語句。其實大二的時候，學校就開設有專門的資料庫課程（使用的是SQL Server），但是沒有好好的學習，學過後，又很少接觸到涉及到這方面的項目，于是大部分知識都忘記了。這次就是通過再次系統的學習一遍SQL語句，撿起曾經學過的知識。由于項目用到的資料庫為SQLite，本文就是這次學習SQLite的一個筆記，是以可能不适用于其他資料庫。

1 常識

1，資料庫軟體是指DBMS（Data base manager system），而資料庫是指有組織的資料庫容器（一個資料庫檔案或一組檔案，如帶有db字尾的資料庫檔案）
2，市場上有很多資料庫軟體如SQL Server、Orccle、MySQL、MariaDB、PostgreSQL、SQLite等等，不同的資料庫軟體資料類型和名稱是不同的，這就造成了SQL不相容，我們學習的話一般可以學習ANSI SQL（美國标準學會SQL）的文法規範，然後再根據自己實際使用的資料庫學習對應特有的文法（如存儲過程SQLite就沒有）
3，一般使用全大寫的書寫SQL的關鍵詞，對列名和表名使用小寫，來讓代碼更易閱讀和了解，雖然SQL不區分大小寫

2 SELECT語句

SELECT 列名/(列名 ,COUNT(*) AS 别名)/(列名 || 拼接字元串) FROM 表名 
WHERE 條件 
GROUP BY 列名 HAVING COUNT(*) >/=/<=/!= 值
LIMIT 顯示的行數 OFFSET 從第幾行顯示的行數 
ORDER BY 依據的排序的列 DESC(降序)/ASC(預設升序)

混淆點：

WHERE：過濾行，在分組前過濾；HAVING：過濾分組，在分組後過濾
GROUP BY:隻能對選擇的列和表達式使用，在有聚集函數的時候必須使用；ORDER BY：對非選擇列也可以使用，對輸出順序排序（GROUP BY不排序）

2.0 别名

AS（Oracle中不使用它，直接把取得名字用于表名和列名後）

作用：給導出的列命名

2.1 篩選

WHERE：

WHERE 條件 BETWEEN 值 AND 值

2，

WHERE 條件  AND 條件

3，

AND的優先級大于OR

WHERE 條件  OR 條件

4，

OR與IN作用相等，一般推薦使用IN

WHERE 條件  IN(值1、值2)

WHERE NOT 條件

6，

通配符
%：任何字元出現任何次數，但不比對NULL
_:比對單個字元
不要過度使用通配符

WHERE 條件 LIKE 值

2.2 函數

1，文本處理函數

1,去空格

RTRIM(去空格右邊)，LIRIM(去空格左邊)，TRIM（去空格左右兩邊）
2，大小寫轉換

UPPER（轉大寫），LOWE（将字元轉轉小寫）
3，SOUNDEX

将任何文串轉換為其語音表示的字母數字模式的算法

2，時間處理函數

1 strftime(’%格式’，列名)

作用：根據第一個參數指定的格式字元串傳回格式化的日期

時間格式：
%d 日期, 01-31
%f 小數形式的秒，SS.SSS
%H 小時, 00-23
%j 算出某一天是該年的第幾天，001-366
%m 月份，00-12
%M 分鐘, 00-59
%s 從1970年1月1日到現在的秒數
%S 秒, 00-59
%w 星期, 0-6 (0是星期天)
%W 算出某一天屬于該年的第幾周, 01-53
%Y 年, YYYY
%% 百分号

2，傳回時間

date(timestring, modifier, modifier, ...):以 YYYY-MM-DD 格式傳回日期。
time(timestring, modifier, modifier, ...):以 HH:MM:SS 格式傳回時間。
datetime(timestring, modifier, modifier, ...):以 YYYY-MM-DD HH:MM:SS 格式傳回。
julianday(timestring, modifier, modifier, ...):這将傳回從格林尼治時間的公元前 4714 年 11 月 24 日正午算起的天數。

timestring:

修飾語（modifier）:

NNN days

NNN hours

NNN minutes

NNN.NNNN seconds

NNN months

NNN years

start of month

start of year

start of day

weekday N

unixepoch

localtime

utc

示例：

計算本月最後一天：

SELECT date('now','start of month','+1 month','-1 day');

由時間戳得到相對本地的日期：

SELECT datetime(1092941466, 'unixepoch', 'localtime');

由日期得到時間戳：

SELECT strftime('%s','now');

計算美國"獨立宣言"簽署以來的天數

SELECT julianday('now') - julianday('1776-07-04');

從 2004 年某一特定時刻以來的秒數：

SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');

下面是計算當年 10 月的第一個星期二的日期：

SELECT date('now','start of year','+9 months','weekday 2');

本小節的列子和圖檔摘自runoob網站

3，數值處理函數

ABS()絕對值、COS()餘弦值、TAN()正切、SIN()正弦、EXP()傳回一個數的指數、PI()傳回圓周率、SQRT()一個數的平方根、
4,聚集函數（對某些列運作的并計算出一個值）
AVG：某列平均值
COUNT：某列的行數

COUNT(*):對空值也計算
DISTINCT：隻包含不同值；隻能用于COUNT(),不用于CONT(*),必須使用列名;

SELECT AVG(DISTINCT price) AS avg_price FROM product WHERE id = '1'

ALL：對所有行執行（預設）

MAX：某列的最大值
MIN：某列的最小值
SUM：某列之和

2.3 過濾分組

1 GROUP BY（分組）

将資料分為多個邏輯組，然後對每個組進行聚集計算
2，HAVING（過濾分組）

3 聯結（最重要的特性）

3.1 完全限定名

在使用列名的時候，指名表名，例如

SELECT goods.id,price.id FROM goods,price

，用于當兩個表中有相同的列名時避免混淆；

3.2 笛卡爾積（叉聯結）

傳回兩個第一個表的行數*第二個表的行數的檢索列數

3.2,等值聯結（内聯結）

傳回兩個表得中的相等的值

SELECT name FROM students,teachers WHERE students.id = teachers.id

等同于

SELECT name FROM students JOIN teachers ON students.id = teachers.id

3.3 自聯結和子查詢、自然聯結

1，self-join（不止一次使用相同的表）

例如：

子聯結：

SELECT id FROM students WHERE name = (SELECT course FROM students WHERE  = 'math')

使用聯結：

SELECT s1.id FROM students AS s1 ,students AS s2 WHERE s1.name = s2.name AND s2.course = 'math'

一般使用聯結要比使用子查詢快許多

2，nature join（自然聯結）

自動使用所有比對的列名進行連接配接【未能證明：可以排除相同列的多次出現，使每一個列值傳回一次】

要求兩個表中要有相同的列名和屬性值，否則傳回笛卡爾積

SELECT C.*,O.* FROM Customers C  NATURAL JOIN Orders O

3.4 外聯結

下圖是外聯結中的左聯結，SQLite中不支援右聯結，但是隻要挑換FROM或WHERE中的表順序即可将左聯結轉為右聯結。

3.4 全聯結（SQLite不支援）

FULL OUTER JOIN：傳回兩個表中的所有行，并關聯可以關聯的行。

4 并/複合查詢

對不同表或者一個表進行多次查詢。

4.1 UNION（會自動去重）

1，兩條及以上的SELECT語句使用；
2，每個查詢包含相同的清單達式、聚集函數；
3，隻允許使用一條ORDER BY語句。

4.2 UNION ALL

不取消重複行，

5 插入語句

INSERT INTO/IGNORE 表名(列名) VALUES(值1，值2，。。。)

IGNORE：如果中已經存在相同的記錄，則忽略目前新資料，并忽略錯誤；INTO：如果有相同資料則報錯

5.1 插入檢索的資料

INSERT INTO 表名(列名) SELECT 列名 FROM 表名

5.2 從一個表複制到另一個表

SELECT 列名 INTO 表名 FROM 表名

DB2不支援上述文法

CREATE TABLE 表名 AS SELECT 列名 FROM 表名

6 更新值

更改值前，先用WHERE确定一遍WHERE過濾的值是否正确

UPDATE 表名 SET 列名 = 值 WHERE 過濾條件

7 删除值

7.1 删除列值

UPDATE 表名 SET 列名 = 值 WHERE 過濾條件

設值為NULL，即可删除列值

7.2 删除行值

删除行值：

DELETE FROM 表名 過濾條件

删除所有行一般使用：

TRUNCATE TABLE 表名

SQLite沒有上述文法，一般使用

DELETE FROM 表名

7.3 删除表

DROP TABLE 表名

8 建立表/更新表

建立表

CRETAE TABLE表名(列名 類型 是否為空 DEFAULT 預設值,...列名 類型 是否為空 DEFAULT 預設值)

更新表

ALTER TABLE 表名 (ADD 列名 屬性)/(RENAME TO 新表名)

SQLite不支援ALTER TABLE定義主鍵和外鍵

9 視圖

SQLite支援可讀視圖

1，把查詢包裝成一個虛拟表
2，重用SQL語句，使用表的一部分，保護資料、更改資料格式和表示

建立視圖：

CREATE VIEW 表名 AS 别名 語句

删除視圖：

DROP VIEW 表名

10 存儲過程（SQLite不支援）

關鍵詞：PROCEDURE

作用：為以後使用而儲存一條而多條SQL語句

11 事務

建立事務：

BEGIN TRANSACTION/BEGIN -- 開始事務
..事務
COMMIT/END TRANSACTION  -- 送出

回退（不能回退CRETAE、DROP、SELECT操作）：

ROLLBACK;

保留點：事務處理的臨時占位符，可對它釋出回退

12 遊标/步驟（Step）

作用：操作結果集的行

用于：

1，滾動螢幕上的資料，并對資料左出遊覽或更改；
2，一旦聲明，就必須打開遊标以供使用，結束使用時，必須關閉遊标

13 限制

作用：管理插入或處理資料庫的規則

13.1 主鍵

1，任意兩行的主鍵值不同
2，每行隻有一個主鍵且唯一，且不為NULL
3，主鍵列不修改、不更新
4，主鍵不重複

關鍵詞：PRIMARY KEY

SQLite不允許使用ALTER TABLE定義主鍵

13.2 外鍵

含義：表中一列，其值為另一個表中的主鍵

1，防止意外删除（有些DBMS可用級聯删除）

關鍵詞：REFERENCES

13.3 唯一限制

1，不能用于定義外鍵
2，差別與外鍵，它可以是多個、可包含NULL、可修改、可更新、可重複使用

關鍵詞：UNIQUE

檢查限制：CHECK

14 索引

作用：恰當的排序

1，改善檢查操作的性能，降低了資料插入、删除、修改的性能
2，占用大量的存儲空間
3，盡量用于資料過濾或排序的列
4，可在索引定義多個列
5，定期檢查索引并調整

15 觸發器（特殊的存儲過程）

作用：在特定的資料活動發生時出發

1，資料通路權：INSERT/UPDATE/DELETE 所有資料庫
2，常見用途：保證資料一緻，基于表的變動在其他表執行活動，進行額外的驗證并根據需要回退；計算值或更新時間戳

限制比出發器更快，應盡量使用限制

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- 觸發器邏輯....
END;

event_name：INSERT/UPDATE 等

16 安全性

GRANT/REVOKE：管理通路

17 資料規範

1，變長性能遠低于鼎昌的
2，值在單引号内
3，計算的值存在資料類型，否則存在字元串（如0123，在數值中可能會丢值）