【知識點】SQLite3總結

李春港發表於2021-06-27
SQLite

目錄

基本的DDL、DML

建立表，包含日期欄位

CREATE TABLE  WordTable (id  integer PRIMARY KEY autoincrement , word  TEXT ,  updatedate datetime )  

插入一行，包含日期欄位

INSERT INTO WordTable (id,word,updatedate) VALUES (6,'ceshi','2020-08-11 13:42:29');

查詢，包含日期欄位

SELECT * FROM WordTable  WHERE updatedate>='2020-08-11 10:42:29' AND updatedate<='2020-08-11 10:42:29'

查詢，按照排序以及限制條目輸出

SELECT * FROM TX WHERE updatedate LIKE '%:%' ORDER BY updatedate ASC LIMIT 1  
---》DESC:降序 ASC:升序 
---》%：匹配任意長度字串
---》_：匹配一個字元
---》LIMIT 1：限制輸出一條結果，必須在sql語句最後

刪除記錄

DELETE FROM table WHERE User='555'

更新資料

某一項數值加1

UPDATE User SET Logins=Logins+1 WHERE User='555'

更新多個欄位

UPDATE Tx SET Ip='11111',Name='2222' WHERE Uid='TTTTTTTTTTTTTTT1'

檢視

檢視（View） 是一種虛表，允許使用者實現以下幾點：

  • 使用者或使用者組查詢結構資料的方式更自然或直觀。
  • 限制資料訪問，使用者只能看到有限的資料，而不是完整的表。
  • 彙總各種表中的資料，用於生成報告。

SQLite 檢視內容是隨著實際資料表實時變化，是隻讀的，因此可能無法在檢視上執行 DELETE、INSERT 或 UPDATE 語句。但是可以在檢視上建立一個觸發器，當嘗試 DELETE、INSERT 或 UPDATE 檢視時觸發，需要做的動作在觸發器內容中定義。

//建立
CREATE VIEW my_view AS SELECT Ip FROM TX
//刪除
DROP VIEW my_view

索引

原理

  • 使用的是B-樹原理

影響

  • 提高查詢的速度
  • 建立索引需要耗費一定的時間
  • 索引需要佔用物理空間
  • 當對錶中的資料進行增加、刪除和修改的時候，索引也要動態的維護，降低了資料的維護速度

使用場景

  • 在經常需要搜尋的列上建立
  • 索引不應該使用在較小的表上。
  • 索引不應該使用在有頻繁的大批量的更新或插入操作的表上。
  • 索引不應該使用在含有大量的 NULL 值的列上。

單列索引

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);

隱式索引

隱式索引是在建立物件時，由資料庫伺服器自動建立的索引。索引自動建立為主鍵約束和唯一約束。

刪除索引

DROP INDEX index_name;

如何正確使用索引
比如通過：CREATE INDEX comp_ind ON table1(x, y, z)建立索引，那麼x,xy,xyz都是前導列，而yz，y，z這樣的就不是。在WHERE子句中，前導列必須使用等於或者IN操作，最右邊的列可以使用不等式，這樣索引才可以完全生效。同時，WHERE子句中的列不需要全建立了索引，但是必須保證建立索引的列之間沒有間隙。
例子：

//建立索引
CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z); 

//查詢語句
...WHERE a=5 AND b IN (1,2,3) AND c IS NULL AND d='hello' 
abcd四列都是有效的，因為只有等於和IN操作，並且是前導列。 

//查詢語句
... WHERE a=5 AND b IN (1,2,3) AND c>12 AND d='hello' 
那這裡只有a，b和c的索引會是有效的，d列的索引會失效，因為它在c列的右邊，而c列使用了不等式，根據使用不等式的限制，c列已經屬於最右邊。

//查詢語句
... WHERE b IN (1,2,3) AND c NOT NULL AND d='hello' 
索引將不會被使用，因為沒有使用前導列，這個查詢會是一個全表查詢。

//對於between，or，like語句，都無法使用索引

觸發器

SQLite 觸發器（Trigger） 是資料庫的回撥函式，它會在指定的資料庫事件發生時自動執行/呼叫。
關鍵字

  • new 代表剛建立的行，old剛刪除的行
  • new在before觸發器中賦值，取值；在after觸發器中取值。
  • new是新插入的資料，old是原來的資料
  • insert只會有new，代表著要插入的新記錄
  • delete只會有old，代表著要刪除的記錄

INSERT、DELETE 和 UPDATE建立觸發器的基本語法

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

UPDATE指定欄位建立觸發器語法（INSERT、DELETE沒有此特性）

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

列出觸發器

  1. 列出資料庫所有觸發器
SELECT name FROM sqlite_master
WHERE type = 'trigger';
  1. 列出特定表觸發器
SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'tablename';

刪除觸發器

DROP TRIGGER trigger_name;

例項

  1. 建立COMPANY表
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
  1. 建立AUDIT表
CREATE TABLE AUDIT(
    EMP_ID INT NOT NULL,
    ENTRY_DATE TEXT NOT NULL
);
  1. 在 COMPANY 表上建立一個觸發器
CREATE TRIGGER audit_log AFTER INSERT 
ON COMPANY
BEGIN
   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;
  1. 在 COMPANY 表中建立一個記錄，
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
  1. 結果

COMPANY表

ID NAME AGE ADDRESS SALARY
1 Paul 32 California 20000.0

AUDIT 表

EMP_ID ENTRY_DATE
1 2013-04-05 06:26:00

事務

  • 事務定義了一組SQL命令的邊界，這一組命令或者作為一個整體被全部執行，或者全部不執行，這被稱作為資料庫完整性的原子性原則。
  • 預設情況下，SQLite中每條命令都會自成一個事務，自動提交或回滾，此操作模式稱為隱式事務或者自動提交模式。
  • 事務控制命令只與 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。他們不能在建立表或刪除表時使用，因為這些操作在資料庫中是自動提交的。

開啟事務命令

  • 事務（Transaction）可以使用 BEGIN TRANSACTION 命令或簡單的 BEGIN 命令來啟動。此類事務通常會持續執行下去，直到遇到下一個 COMMIT 或 ROLLBACK 命令。不過在資料庫關閉或發生錯誤時，事務處理也會回滾。
BEGIN;
或者
BEGIN TRANSACTION

提交事務命令

  • 用於把事務呼叫的更改儲存到資料庫中的事務命令。
  • 把自上次 COMMIT 或 ROLLBACK 命令以來的所有事務儲存到資料庫。
COMMIT
或者
END TRANSACTION

回滾命令

  • 用於撤消尚未儲存到資料庫的事務的事務命令。
  • 只能用於撤銷自上次發出 COMMIT 或 ROLLBACK 命令以來的事務。
ROLLBACK

例項

  1. 刪除後再回滾，記錄還在
BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;
  1. 刪除後提交，刪除成功，記錄已被刪除
BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;

專業術語

DML（Data Manipulation Lanaguage，資料操縱語言）
DML就是我們經常用到的SELECT、INSERT、UPDATE和DELETE語句，主要是用來對資料進行CRUD：增查改刪（create, read (retrieve), update, delete）操作。
DDL（Data Definition Language，資料定義語言）
DDL就是我們在建立表的時候用到的一些語句，比如說CREATE、ALTER、DROP等。DDL主要是用在定義或改變表的結構、資料型別、表之間的連結或約束等初始化工作上。
DCL（Data Control Language，資料庫控制語言）
DCL是用來設定或更改資料庫使用者或角色許可權的語句，包括GRANT、DENY、REVOKE等語句。

相關文章