乾貨 - Sqlite 數據庫知識必知必會（下篇）

大家好，我是 IT 共享者，人稱皮皮。

前面幾天給大家分享了 MySQL 數據庫知識，沒來得及看的小夥伴可以前往：Mysql 查詢語句進階知識集錦，一篇文章教會你進行 Mysql 數據庫和數據表的基本操作，關於數據庫的安裝可以參考：手把手教你進行 Mysql5.x 版本的安裝及解決安裝過程中的 bug。

上篇文章主要給大家介紹了 Sqlite 數據庫的安裝、圖形化管理系統、基本數據類型、基本關鍵字和數據庫的相關操作，乾貨 | Sqlite 數據庫知識必知必會（上篇），今天皮皮緊接上篇文章，繼續給大家分享數據庫知識，一起來看看吧。

前言

前面我們學習了很多數據庫，雖然它們功能非常豐富，但是佔用空間比較大，使得我們的系統負荷變大，這對於我們新手小白來說不是一個理想的選擇，於是乎，Sqlite 閃亮登場，它受人喜愛的真正原因正是因爲它足夠小，無需安裝和管理配置，簡單容易上手，支持數據庫的大小也有 2TB，足夠我們個人開發愛好者使用了。

一、數據表相關操作

1). 創建數據表

CREATE TABLE student (
ID INTEGER DEFAULT '1406061' PRIMARY KEY AUTOINCREMENT NOT NULL,
NAME NVARCHAR(100)  UNIQUE NOT NULL,
score INTEGER  NOT NULL,
time TIMESTAMP  NOT NULL
);

我們可以使用 SqliteAdmin 快速生成表，如下：

這樣我們的表就建立成功了。這裏小編在軟件中用的是 DEMO 這個數據文件，而在命令窗口中用的是 TEST 這個數據庫文件，因爲一個數據庫文件不允許在兩個位置打開的緣故。

2). 刪除表

DROP TABLE people;

3). 顯示錶名

.tables

4). 數據表的增刪改查

1)). 增加

這裏我們可以給剛創建的表添加一個數據，如下：

insert into student(ID,NAME,score,time) values(1406063,'任性的90後boy',90,'2020-07-06 12:23:32');

這裏我插入了三行數據，其中有一行報錯是因爲我使用了相同的 ID 號，而我的數據庫的 ID 號是不允許重複的，故而會報錯，改成其它的就好了，如果你把 ID 設爲自增的話，就不需要你去寫了，這裏我用到了自增，所以大家不必寫 Sqlite 會自動填充進去的。這裏也可以省掉字段名，直接寫值，如下：

我們還可以給表添加新列，如下;

alter table people add  column aa char(10);#添加列並且列名爲aa

注：Sqlite3 並不支持帶有 UNIQUE 約束的列

2)). 刪除
delete from people;#刪除表數據
delete from people where score<20#刪除people數據表score數值小於20的記錄
3)). 修改

其實就是更新操作，如下;

update people set score=100 where  age=46;#當age=46時score設置爲100
4)).查找

查找不管是在哪個數據庫中方法都是相當多的，因爲查詢操作是最常見也是最頻繁的，下面我們就來詳細說說：

1))). 查詢所有的結果
select * from student;

我們可以看到雖然結果打印出了，但是並不是那麼美觀，我們將它的打印格式化一下，如下;

這下就完美多了。雖然這種方法很不錯，但是如果數據列很多的話就無法完全顯示了，這個時候我們就需要設置列的寬度，如下：

.width 10 20 15
2))). 查詢指定結果
select NAME,score from student; #打印出NAME和score列的結果
select * from people where score<20 and age<40;#當score小於20並且age小於40的時候
select *from people where score between 10 and 30;#當score爲10~30之間時
select *from people where NAME LIKE '放大%';#當name的開始處含有放大
select *from people where NAME glob '放大*';#與上同
select *from where score not in(12,23);#當score不屬於12~23
3))). 查找排序
select * from people order by score desc; #對score降序排序
4))). 查找指定數量的數據
select * from people  limit 3 offset 2#偏移2個單位然後輸出三行數據，其實就是輸出後三行數據

5))). 查找重複數據並消除

select distinct * from people where score>10;#當score大於10的時候消除重複數據
6))). 查找數據並進行分組
select NAME ,max(age) from people where score>10 group by age#返回score大於10的時候age的最大值
7))). 查找數據進行分組並過濾分組
select NAME ,max(age) from people where score>10 group by age having count(NAME)>1;#名稱計數大於1

5). 表的重命名

alter table people rename to man;#將表名people改爲man

6). 數據表的連接

這裏的連接分爲內外交叉三種連接，使用好了可以極大提高我們的工作效率。這裏我們再次創建一張表，如下：

1)). 交叉連接
select * from people cross join woman;

2)). 內連接
select * from people inner join woman;
3)). 外連接
select * from people outer join woman;

注：Sqlite3 只支持左外連接。

7). 表的複製

create table man as select * from people where 1=0;#複製表結構
create table woman as select * from people;#複製表結構和數據

8). 臨時表

有時候我們不需要存儲所有表的數據，這個時候臨時表就很有使用的必要了。如下：

#創建一個臨時表
create  temporary table temp_table(
    id int primary key,
    name varchar(50) unique not null,
    age int not null
);
#查看臨時表
temp.temp_table
#刪除臨時表
drop table temp.temp_table;

5. 索引操作

1). 創建索引

create index user on people(score);#在people表的score字段上設置索引
create unique name on people(NAME);#在people表的score字段上設置唯一索引
create index pa on people(score,age)#在people表的score和age字段上設置索引

2). 查看索引

SELECT * FROM sqlite_master WHERE type = 'index';

這裏我們之前僅僅只是創建了一個索引，那就是”user“，爲何會有兩個索引了，而且最上面的索引好像跟我們並沒有什麼關係，也不像是自己創建的，其實，這個就是隱式索引，這個隱式索引在我們創建表的時候就連帶着一起創建了的，只是爲了讓查詢更加快速而已，影響不大。

3). 使用索引

這裏涉及到兩個關鍵字請配套使用，表示索引來自於哪裏的意思。

select * from people indexed by user where score>10;

4). 刪除索引

drop index user;

注：數據量較小不應使用索引，以免給系統增壓。

6. 視圖操作

1). 創建視圖

create view name as select NAME from people;#創建NAME字段的視圖
2).使用視圖
select *from name;

這樣就可以直接輸出視圖字段的所有值了。

3). 刪除視圖

drop view name;

7. 觸發器

觸發器就是使得多個表的交互增強，互相調用，滿足條件就調用哪個表，語法格式如下：

create trigger 觸發器 after(before) insert(delete update)
on 表1
begin
insert into 表2(表2字段,表2字段，n) VALUES(表1值, 表1值，，，，n);
END;

1). 創建觸發器

#給people表創建一個觸發器
create trigger cf
after insert on people
begin
insert into woman(w_ID,NAME,SCORE,date) values(new.ID,'向前插入',100,datetime('now'));
end;

2). 查看觸發器

select * from sqlite_master where type = 'trigger'AND tbl_name='people';#查看people表的觸發器

3). 刪除觸發器

drop trigger cf;

8. 事務

有了事務可以讓我們的 Sqlite 語句工作的更加有條不紊，一般大致分爲事務開始 提交 回滾 結束，下面來看看：

begin;#開始
insert into people('gf',65,datetime('now'));
rollback; #回滾就是撤銷
COMMIT;提交保存數據
end;#結束

這裏的數據沒有插入到 “people” 表中哦，是不是超級簡單了？

二、總結

這篇文章主要介紹了 Sqlite 數據庫的數據表、索引、視圖、觸發器和事務操作知識，乾貨滿滿。

皮皮自從學過 Sqlite 之後，就果斷卸載了 Mysql 和 Mongo 了，主要是目前還用不着 Mysql 數據庫和 Mongo 那麼強大的數據，夠用就好。

我是皮皮，如果覺得文章不錯，記得三連噢，大家有問題也可以點擊下方的圖片，加我好友，交個朋友也好呀~

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/wHm3Uhlz9iUlyakWXvWbOg

猜你喜歡