干貨|Sqlite數(shù)據(jù)庫(kù)知識(shí)必知必會(huì)（下篇）

大家好，我是IT共享者，人稱(chēng)皮皮。

前面幾天給大家分享了MySQL數(shù)據(jù)庫(kù)知識(shí)，沒(méi)來(lái)得及看的小伙伴可以前往：Mysql查詢(xún)語(yǔ)句進(jìn)階知識(shí)集錦，一篇文章教會(huì)你進(jìn)行Mysql數(shù)據(jù)庫(kù)和數(shù)據(jù)表的基本操作，關(guān)于數(shù)據(jù)庫(kù)的安裝可以參考：手把手教你進(jìn)行Mysql5.x版本的安裝及解決安裝過(guò)程中的bug。

上篇文章主要給大家介紹了Sqlite數(shù)據(jù)庫(kù)的安裝、圖形化管理系統(tǒng)、基本數(shù)據(jù)類(lèi)型、基本關(guān)鍵字和數(shù)據(jù)庫(kù)的相關(guān)操作，干貨|Sqlite數(shù)據(jù)庫(kù)知識(shí)必知必會(huì)（上篇），今天皮皮緊接上篇文章，繼續(xù)給大家分享數(shù)據(jù)庫(kù)知識(shí)，一起來(lái)看看吧。

前言

前面我們學(xué)習(xí)了很多數(shù)據(jù)庫(kù)，雖然它們功能非常豐富，但是占用空間比較大，使得我們的系統(tǒng)負(fù)荷變大，這對(duì)于我們新手小白來(lái)說(shuō)不是一個(gè)理想的選擇，于是乎，Sqlite閃亮登場(chǎng)，它受人喜愛(ài)的真正原因正是因?yàn)樗銐蛐?，無(wú)需安裝和管理配置，簡(jiǎn)單容易上手，支持?jǐn)?shù)據(jù)庫(kù)的大小也有2TB，足夠我們個(gè)人開(kāi)發(fā)愛(ài)好者使用了。

一、數(shù)據(jù)表相關(guān)操作

1).創(chuàng)建數(shù)據(jù)表

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這個(gè)數(shù)據(jù)文件，而在命令窗口中用的是TEST這個(gè)數(shù)據(jù)庫(kù)文件，因?yàn)橐粋€(gè)數(shù)據(jù)庫(kù)文件不允許在兩個(gè)位置打開(kāi)的緣故。

2).刪除表

DROP TABLE people;

3).顯示表名

.tables

4).數(shù)據(jù)表的增刪改查

1)).增加

這里我們可以給剛創(chuàng)建的表添加一個(gè)數(shù)據(jù)，如下：

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

這里我插入了三行數(shù)據(jù)，其中有一行報(bào)錯(cuò)是因?yàn)槲沂褂昧讼嗤腎D號(hào)，而我的數(shù)據(jù)庫(kù)的ID號(hào)是不允許重復(fù)的，故而會(huì)報(bào)錯(cuò)，改成其它的就好了，如果你把ID設(shè)為自增的話(huà)，就不需要你去寫(xiě)了，這里我用到了自增，所以大家不必寫(xiě)Sqlite會(huì)自動(dòng)填充進(jìn)去的。這里也可以省掉字段名，直接寫(xiě)值，如下：

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

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

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

2)).刪除

delete from people;#刪除表數(shù)據(jù)delete from people where score<20#刪除people數(shù)據(jù)表score數(shù)值小于20的記錄

3)).修改

其實(shí)就是更新操作，如下;

update people set score=100 where  age=46;#當(dāng)age=46時(shí)score設(shè)置為100

4)).查找

查找不管是在哪個(gè)數(shù)據(jù)庫(kù)中方法都是相當(dāng)多的，因?yàn)椴樵?xún)操作是最常見(jiàn)也是最頻繁的，下面我們就來(lái)詳細(xì)說(shuō)說(shuō)：

1))).查詢(xún)所有的結(jié)果

select * from student;

我們可以看到雖然結(jié)果打印出了，但是并不是那么美觀，我們將它的打印格式化一下，如下;

這下就完美多了。雖然這種方法很不錯(cuò)，但是如果數(shù)據(jù)列很多的話(huà)就無(wú)法完全顯示了，這個(gè)時(shí)候我們就需要設(shè)置列的寬度，如下：

.width 10 20 15

2))).查詢(xún)指定結(jié)果

select NAME,score from student; #打印出NAME和score列的結(jié)果select * from people where score<20 and age<40;#當(dāng)score小于20并且age小于40的時(shí)候select *from people where score between 10 and 30;#當(dāng)score為10~30之間時(shí)select *from people where NAME LIKE '放大%';#當(dāng)name的開(kāi)始處含有放大select *from people where NAME glob '放大*';#與上同select *from where score not in(12,23);#當(dāng)score不屬于12~23

3))).查找排序

select * from people order by score desc; #對(duì)score降序排序

4))).查找指定數(shù)量的數(shù)據(jù)

select * from people  limit 3 offset 2#偏移2個(gè)單位然后輸出三行數(shù)據(jù)，其實(shí)就是輸出后三行數(shù)據(jù)

5))).查找重復(fù)數(shù)據(jù)并消除

select distinct * from people where score>10;#當(dāng)score大于10的時(shí)候消除重復(fù)數(shù)據(jù)

6))).查找數(shù)據(jù)并進(jìn)行分組

select NAME ,max(age) from people where score>10 group by age#返回score大于10的時(shí)候age的最大值

7))).查找數(shù)據(jù)進(jìn)行分組并過(guò)濾分組

select NAME ,max(age) from people where score>10 group by age having count(NAME)>1;#名稱(chēng)計(jì)數(shù)大于1

5).表的重命名

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

6).數(shù)據(jù)表的連接

這里的連接分為內(nèi)外交叉三種連接，使用好了可以極大提高我們的工作效率。這里我們?cè)俅蝿?chuàng)建一張表，如下：

1)).交叉連接

select * from people cross join woman;

2)).內(nèi)連接

select * from people inner join woman;

3)).外連接

select * from people outer join woman;

注：Sqlite3只支持左外連接。

7).表的復(fù)制

create table man as select * from people where 1=0;#復(fù)制表結(jié)構(gòu)create table woman as select * from people;#復(fù)制表結(jié)構(gòu)和數(shù)據(jù)

8).臨時(shí)表

有時(shí)候我們不需要存儲(chǔ)所有表的數(shù)據(jù)，這個(gè)時(shí)候臨時(shí)表就很有使用的必要了。如下：

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

5.索引操作

1).創(chuàng)建索引

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

2).查看索引

SELECT * FROM sqlite_master WHERE type = 'index';

這里我們之前僅僅只是創(chuàng)建了一個(gè)索引，那就是”user“，為何會(huì)有兩個(gè)索引了，而且最上面的索引好像跟我們并沒(méi)有什么關(guān)系，也不像是自己創(chuàng)建的，其實(shí)，這個(gè)就是隱式索引，這個(gè)隱式索引在我們創(chuàng)建表的時(shí)候就連帶著一起創(chuàng)建了的，只是為了讓查詢(xún)更加快速而已，影響不大。

3).使用索引

這里涉及到兩個(gè)關(guān)鍵字請(qǐng)配套使用，表示索引來(lái)自于哪里的意思。

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

4).刪除索引

drop index user;

注：數(shù)據(jù)量較小不應(yīng)使用索引，以免給系統(tǒng)增壓。

6.視圖操作

1).創(chuàng)建視圖

create view name as select NAME from people;#創(chuàng)建NAME字段的視圖

2).使用視圖

select *from name;

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

3).刪除視圖

drop view name;

7.觸發(fā)器

觸發(fā)器就是使得多個(gè)表的交互增強(qiáng)，互相調(diào)用，滿(mǎn)足條件就調(diào)用哪個(gè)表，語(yǔ)法格式如下：

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

1).創(chuàng)建觸發(fā)器

#給people表創(chuàng)建一個(gè)觸發(fā)器create trigger cfafter insert on peoplebegininsert into woman(w_ID,NAME,SCORE,date) values(new.ID,'向前插入',100,datetime('now'));end;

2).查看觸發(fā)器

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

3).刪除觸發(fā)器

drop trigger cf;

8.事務(wù)

有了事務(wù)可以讓我們的Sqlite語(yǔ)句工作的更加有條不紊，一般大致分為事務(wù)開(kāi)始 提交 回滾 結(jié)束，下面來(lái)看看：

begin;#開(kāi)始insert into people('gf',65,datetime('now'));rollback; #回滾就是撤銷(xiāo)COMMIT;提交保存數(shù)據(jù)end;#結(jié)束

這里的數(shù)據(jù)沒(méi)有插入到“people”表中哦，是不是超級(jí)簡(jiǎn)單了？

二、總結(jié)

這篇文章主要介紹了Sqlite數(shù)據(jù)庫(kù)的數(shù)據(jù)表、索引、視圖、觸發(fā)器和事務(wù)操作知識(shí)，干貨滿(mǎn)滿(mǎn)。

皮皮自從學(xué)過(guò)Sqlite之后，就果斷卸載了Mysql和Mongo了，主要是目前還用不著Mysql數(shù)據(jù)庫(kù)和Mongo那么強(qiáng)大的數(shù)據(jù)，夠用就好。

我是皮皮，如果覺(jué)得文章不錯(cuò)，記得三連噢，大家有問(wèn)題也可以點(diǎn)擊下方的圖片，加我好友，交個(gè)朋友也好呀~

看完本文有收獲？請(qǐng)轉(zhuǎn)發(fā)分享給更多的人

IT共享之家

入群請(qǐng)?jiān)谖⑿藕笈_(tái)回復(fù)【入群】

------------------- End -------------------

往期精彩文章推薦：

• 盤(pán)點(diǎn)4大下載神器，教你分分鐘搞定文件下載
  

• 安利一款神奇——教你輕松下載百度網(wǎng)盤(pán)超大文件
  

• 說(shuō)說(shuō)Python集合的那些事兒
  

• 一篇文章教會(huì)你進(jìn)行Mysql數(shù)據(jù)庫(kù)和數(shù)據(jù)表的基本操作