一、sqlite的安裝和使用

1、打開navicate(數(shù)據(jù)庫的軟件都可)
2、新建SQlite連接

新建sqlite

3、新建user表

image.png

4、保存user表，并手動(dòng)添加數(shù)據(jù)

user表

二、sqlite3基礎(chǔ)數(shù)據(jù)類型

存儲(chǔ)類 描述
INTEGER 值是一個(gè)帶符號(hào)的整數(shù)，根據(jù)值的大小存儲(chǔ)在 1、2、3、4、6 或 8 字節(jié)中。
REAL 值是一個(gè)浮點(diǎn)值，存儲(chǔ)為 8 字節(jié)的 IEEE 浮點(diǎn)數(shù)字。
TEXT 值是一個(gè)文本字符串，使用數(shù)據(jù)庫編碼（UTF-8、UTF-16BE 或 UTF-16LE）存儲(chǔ)。
BLOB 值是一個(gè) blob 數(shù)據(jù)，完全根據(jù)它的輸入存儲(chǔ)。

三、主鍵

PRIMARY Key 約束：唯一標(biāo)識(shí)數(shù)據(jù)庫表中的各行/記錄。

四、sql語法

假設(shè)您已經(jīng)在 testDB.db 中創(chuàng)建了 user表，如下所示：

CREATE TABLE "user" (
    "id" BIGINT NOT NULL,
    "name" VARCHAR(50) NULL,
    "account" VARCHAR(50) NULL,
    "password" VARCHAR(50) NULL,
    "token" VARCHAR(50) NULL,
    "create_time" BIGINT NULL,
    "balance" BIGINT NULL,
    PRIMARY KEY ("id")
);

五、Insert 插入語句

INSERT INTO 語句有兩種基本語法，如下所示：

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

現(xiàn)在，下面的語句將在 user表中創(chuàng)建5個(gè)記錄：

INSERT INTO `user` (`id`,`name`,`account`,`password`,`create_time`,`balance`)
VALUES (1, 'Paul', 'testaccount01', '123456', 123456,100 );

INSERT INTO `user` (`id`,`name`,`account`,`password`,`create_time`,`balance`)
VALUES (2, 'Allen', 'testaccount02', 'abcdef', 123456,200 );

INSERT INTO `user` (`id`,`name`,`account`,`password`,`create_time`,`balance`)
VALUES (3, 'Teddy', 'testaccount03', '654321', 123456,300 );

INSERT INTO `user` (`id`,`name`,`account`,`password`,`create_time`,`balance`)
VALUES (4, 'Mark', 'testaccount04', '666666', 123456,400 );

INSERT INTO `user` (`id`,`name`,`account`,`password`,`create_time`,`balance`)
VALUES (5, 'David', 'testaccount05', '123456', 123456,500 );

user表

六、Select 查詢語句

SQLite 的 SELECT 語句的基本語法：

SELECT column1, column2, columnN FROM table_name;

在這里，column1, column2...是表的字段，他們的值即是您要獲取的。如果您想獲取所有可用的字段，那么可以使用下面的語法：

SELECT * FROM table_name;

舉例：

SELECT * FROM user     //查詢user表所有的字段
SELECT `id`,`password` FROM user  //查詢user表中的id和password字段

七、where 條件查詢

您還可以使用比較或邏輯運(yùn)算符指定條件，比如 >、<、=、LIKE、NOT，等等。

//查詢user表id，name，password，balance，找出balance字段大于200的記錄
SELECT `id`,`name`,`password`,`balance` FROM `user` WHERE `balance` > 200;
//查詢user表，找出balance字段大于200，并且balance字段小于500的記錄
SELECT * FROM `user` WHERE `balance` > 200 AND `balance` < 500;
//查詢user表，找出balance等于200或者balance等于500的記錄
SELECT * FROM `user` WHERE `balance` = 200 or `balance` = 500;

BETWEEN 查詢

//查詢user表balance在200和500之間的記錄
SELECT * FROM `user` WHERE `balance` BETWEEN 200 AND 500;

模糊查詢（%的意思是匹配任何都可以）

//查詢user表name字段，a(大小寫都行)開頭的任意記錄
SELECT * FROM `user` WHERE `name` LIKE 'a%';
//查詢user表name字段，包括a(大小寫都行)的任意記錄（只要之包裹著a的都可以）
SELECT * FROM `user` WHERE `name` LIKE '%a%';

in查詢

//查詢user表，id包含2和5的記錄
SELECT * FROM `user` WHERE `id` IN (2,5);
//查詢user表，id不包含2和5的
SELECT * FROM `user` WHERE `id` NOT IN (2,5);

Limit 關(guān)鍵字
LIMIT 子句用于限制由 SELECT 語句返回的數(shù)據(jù)數(shù)量。

//查詢user表，返回兩條數(shù)據(jù)（返回的第一條和第二條記錄）
select * from `user` limit 2
//第一個(gè)2表示從第幾個(gè)開始，后面的2表示返回的數(shù)量（返回第三條記錄和第四條記錄）
select * from `user` limit 2,2

Order By 關(guān)鍵字
asc是指定列按升序排列,desc則是指定列按降序排列

ORDER BY 子句是用來基于一個(gè)或多個(gè)列按升序或降序順序排列數(shù)據(jù)

//查詢user表，根據(jù)id降序排列，取前三個(gè)記錄
select * from `user` order by `id` desc limit 3
//查詢user表，根據(jù)password先排序，在按照id排序，升序區(qū)前三個(gè)記錄
select * from `user` order by `password`,`id` asc limit 3

Distinct 關(guān)鍵字
DISTINCT 關(guān)鍵字與 SELECT 語句一起使用，來消除所有重復(fù)的記錄，并只獲取唯一一次記錄。

select DISTINCT `password` from `user`    //查詢user表中的password，并消除重復(fù)的記錄

八、Update 語句

帶有 WHERE 子句的 UPDATE 查詢的基本語法如下：

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

//更新user表id是1的記錄中的account字段和password字段
 UPDATE `user` SET `account` = 'admin01',`password`='888888' WHERE `id` = 1;

九、Delete 語句

帶有 WHERE 子句的 DELETE 查詢的基本語法如下：

DELETE FROM table_name
WHERE [condition];

//刪除user表中id是1的記錄
DELETE FROM `user` WHERE `id` = 1;

ps：update和delete語句，如果不加where，就會(huì)更新和刪除所有的記錄，除非是特定情況，不然務(wù)必加上where。

十、索引

索引（Index）是一種特殊的查找表，數(shù)據(jù)庫搜索引擎用來加快數(shù)據(jù)檢索。簡(jiǎn)單地說，索引是一個(gè)指向表中數(shù)據(jù)的指針。一個(gè)數(shù)據(jù)庫中的索引與一本書的索引目錄是非常相似的。

創(chuàng)建索引

CREATE [UNIQUE] INDEX index_name ON table_name (column_name,column_name);

--單列索引
CREATE INDEX test_index ON `user` (`account`);

--唯一索引
CREATE UNIQUE INDEX test_index ON `user` (`account`);

--組合索引
CREATE INDEX test_index ON `user` (`account`,`name`);

但是sqlite目前還不支持能讓你直接在表格里看到索引，所以需要你單獨(dú)去查看。
查看索引

SELECT * FROM sqlite_master WHERE type = 'index'

刪除索引

DROP INDEX test_index;

雖然索引的目的在于提高數(shù)據(jù)庫的性能，但這里有幾個(gè)情況需要避免使用索引。使用索引時(shí)，應(yīng)重新考慮下列準(zhǔn)則：
① 索引不應(yīng)該使用在較小的表上。
②索引不應(yīng)該使用在有頻繁的大批量的更新或插入操作的表上。
③索引不應(yīng)該使用在含有大量的 NULL 值的列上。
④索引不應(yīng)該使用在頻繁操作的列上。

十一、約束

約束是在創(chuàng)建表的時(shí)候，可以給表的字段添加相應(yīng)的約束，添加約束的目的是為了保證表中數(shù)據(jù)的合法性、有效性、完整性。

常見的約束有：非空約束（not null）、唯一約束（unique）、主鍵約束（primary key）

NOT NULL 約束
UNIQUE 約束
PRIMARY Key 約束

十二、常用函數(shù)

官方文檔 https://www.sqlite.org/lang_corefunc.html

函數(shù) & 描述
1 COUNT 函數(shù) COUNT 計(jì)算一個(gè)數(shù)據(jù)庫表中的行數(shù)。
2 MAX 函數(shù) MAX 選擇某列的最大值。
3 MIN 函數(shù) MIN 選擇某列的最小值。
4 AVG 函數(shù) AVG 計(jì)算某列的平均值。
5 SUM 函數(shù) SUM 數(shù)值列計(jì)算總和。

SELECT count(*) FROM `user`   //查詢user表的個(gè)數(shù)
SELECT MAX(`id`) FROM `user` //查詢user表id的最大值
SELECT AVG(`id`) FROM `user` //查詢user表id的平均值
SELECT UPPER(`name`),random() FROM `user` //查詢user表將name變大寫，取隨機(jī)數(shù)
SELECT AVG(`id`) AS avg_val FROM `user` //如果覺得頭比較丑，可以用AS去修改標(biāo)題名稱

十三、子查詢

子查詢或稱為內(nèi)部查詢、嵌套查詢，指的是在 SQLite 查詢中的 WHERE 子句中嵌入查詢語句

//user_log表和user表查詢id一樣的記錄
select * from `user` where `id` in (select `id` from `user_log`)
//user_log表和user表查詢,其中user_log表查詢login_times大于5的記錄
select * from `user` where `id` in (select `id` from `user_log` where `login_times` > 5)

十四、JOIN 查詢

1、CROSS JOIN
又叫笛卡爾積，匹配前一個(gè)表與后一個(gè)表的每一行和每一列，這樣得到的結(jié)果集為n*m行（n, m分別為每張表的行數(shù)），x+y列（x,
y分別為每張表的列數(shù)）?？梢?，該結(jié)果集可能會(huì)成為一個(gè)巨大的表，對(duì)內(nèi)存和后續(xù)處理都會(huì)造成巨大壓力，所以，慎用（真沒用過）。

//把兩個(gè)表根據(jù)id，id相同的記錄拼接起來
SELECT * FROM `user` CROSS JOIN `user_log` ON `user`.`id` = `user_log`.`id`

2、Inner Join
內(nèi)連接（INNER JOIN）根據(jù)連接謂詞結(jié)合兩個(gè)表（table1 和 table2）的列值來創(chuàng)建一個(gè)新的結(jié)果表。查詢會(huì)把 table1 中的每一行與 table2
中的每一行進(jìn)行比較，找到所有滿足連接謂詞的行的匹配對(duì)。當(dāng)滿足連接謂詞時(shí)，A 和 B 行的每個(gè)匹配對(duì)的列值會(huì)合并成一個(gè)結(jié)果行。

//和cross join差不多，基本上不用這個(gè)寫法
SELECT * FROM `user` JOIN `user_log` ON `user`.`id` = `user_log`.`id`
//一樣的拼接，INNER JOIN 用的多一些
SELECT * FROM `user` INNER JOIN `user_log` ON `user`.`id` = `user_log`.`id`

3、LEFT OUTER JOIN
SQLite 只支持 左外連接（LEFT OUTER JOIN）

SQLite3只支持left outer join，其結(jié)果集由不大于x + y列，n -
n*m行構(gòu)成，至少包含左側(cè)表的每一行，對(duì)于Join后不存在的字段值，則賦NULL。這樣得到的表與我們之前設(shè)計(jì)那個(gè)全集結(jié)果一樣，但數(shù)據(jù)結(jié)構(gòu)更清晰，空間占用更少。

//相當(dāng)于返回user表的所有記錄，但是匹配不到的id，就返回空的
SELECT * FROM `user` LEFT OUTER JOIN `user_log` ON `user`.`id` = `user_log`.`id`

ps:內(nèi)連接和左外連接，區(qū)別就在于，一個(gè)返回匹配對(duì)應(yīng)的記錄拼接，一個(gè)是返回主表的所有記錄，并匹配不對(duì)的記錄，就不會(huì)有值

內(nèi)連接

左外連接

十五、事務(wù)（Transaction）

事務(wù) (Transaction) 是指作為單個(gè)邏輯工作單元執(zhí)行的一系列操作，要么完全地執(zhí)行，要么完全地不執(zhí)行

事務(wù)處理可以確保除非事務(wù)性單元內(nèi)的所有操作都成功完成，否則不會(huì)永久更新面向數(shù)據(jù)的資源

舉個(gè)例子，A 向 B 轉(zhuǎn)賬 100 元，其實(shí)整個(gè)轉(zhuǎn)賬過程就是一個(gè)事務(wù)，要么轉(zhuǎn)賬成功了，A 的賬戶扣了 100 元，B 的賬戶增加了 100 元，要么轉(zhuǎn)賬失敗，A 還是那么多錢，B 還是沒錢，如果出現(xiàn) A 扣了
100 元，B 的賬戶卻沒增加 100 元，那是要出問題的，是不？

事務(wù)，就是用來做這件事的，用來保證要么轉(zhuǎn)賬成功，要么轉(zhuǎn)賬失敗

十六、事務(wù)屬性

事務(wù)可以用來維護(hù)數(shù)據(jù)庫的完整性，保證成批的 SQL 語句要么全部執(zhí)行，要么全部不執(zhí)行

一般來說，事務(wù)是必須滿足4個(gè)條件 ( ACID )

Atomicity(原子性)、Consistency(穩(wěn)定性)、Isolation(隔離性)、Durability(可靠性)

1、原子性
一組事務(wù)，要么成功；要么失敗回滾當(dāng)作什么事都沒發(fā)生
2、穩(wěn)定性
有非法數(shù)據(jù) (外鍵約束之類)，事務(wù)撤回
3、隔離性
事務(wù)獨(dú)立運(yùn)行。一個(gè)事務(wù)處理后的結(jié)果，影響了其它事務(wù)，那么其它事務(wù)會(huì)撤回
事務(wù)的100%隔離，需要犧牲速度
4、可靠性
軟、硬件崩潰后，SQLite 數(shù)據(jù)表驅(qū)動(dòng)會(huì)利用日志文件重構(gòu)修改

十七、SQLite 事務(wù)

SQLite 默認(rèn)設(shè)置下，事務(wù)都是自動(dòng)提交的，即執(zhí)行 SQL 語句后就會(huì)馬上執(zhí)行 COMMIT 操作

因此要顯式地開啟一個(gè)事務(wù)務(wù)須使用命令 BEGIN TRANSACTION

SQLite 中可以使用下面的命令來控制事務(wù)

命令 / 說明
BEGIN TRANSACTION 開始事務(wù)處理。
COMMIT END TRANSACTION 保存更改
ROLLBACK 回滾所做的更改
事務(wù)控制命令只與 DML 命令 INSERT、UPDATE 和 DELETE 一起使用

他們不能在創(chuàng)建表或刪除表時(shí)使用，因?yàn)檫@些操作在數(shù)據(jù)庫中是自動(dòng)提交的

1、BEGIN TRANSACTION 命令
事務(wù) ( Transaction ) 可以使用 BEGIN TRANSACTION 命令或 BEGIN命令來啟動(dòng)

BEGIN;
BEGIN TRANSACTION;

事務(wù)通常會(huì)持續(xù)執(zhí)行下去，直到遇到下一個(gè) COMMIT 或 ROLLBACK 命令

有個(gè)特殊情況是：在數(shù)據(jù)庫關(guān)閉或發(fā)生錯(cuò)誤時(shí)，事務(wù)處理會(huì)回滾

2、COMMIT 命令
COMMIT 命令用于把事務(wù)調(diào)用的更改保存到數(shù)據(jù)庫中

COMMIT;
END TRANSACTION;

3、ROLLBACK 命令
ROLLBACK 命令是用于撤消尚未保存到數(shù)據(jù)庫的事務(wù)

ROLLBACK;

舉例：

--開啟事務(wù)
BEGIN;

INSERT INTO `user` (`id`,`name`,`account`,`password`,`create_time`,`balance`)
VALUES (6, 'peter', 'testaccount01', '123456', 123456,100 );

--回滾事務(wù)
ROLLBACK;

--開啟事務(wù)
BEGIN;

INSERT INTO `user` (`id`,`name`,`account`,`password`,`create_time`,`balance`)
VALUES (7, 'hello', 'testaccount01', '123456', 123456,100 );

--提交事務(wù)
COMMIT;

ps：開啟事務(wù)之后，進(jìn)行回滾，會(huì)回滾到開啟事務(wù)之前。提交事務(wù)，相當(dāng)于執(zhí)行操作。