powershell玩轉(zhuǎn)sqlite數(shù)據(jù)庫(kù)詳細(xì)介紹

腳本經(jīng)常需要處理文本，有時(shí)候是行列整齊文本。那么powershell腳本處理行列文本有幾種方法呢？一種是excel，另外的一些是？access？sqlite？

sqlite是一個(gè)很小巧的，很方便嵌入到腳本語言中的，關(guān)系型數(shù)據(jù)庫(kù)。它給powershell提供了一個(gè)免費(fèi)的，處理行列整齊數(shù)據(jù)，存儲(chǔ)數(shù)據(jù)，統(tǒng)計(jì)數(shù)據(jù)的極品神器。
只要你學(xué)會(huì)了它，上天入地皆可去得。而且以后學(xué)sql server，mysql，都類似，沒有難度。

帶著引號(hào)搜本文關(guān)鍵字： "^_^上天入地皆可去^_^"
發(fā)文初衷：世界上幾乎沒有講powershell調(diào)用sqlite的帖子。

初稿，會(huì)更新。有錯(cuò)誤歡迎指正，謝謝。

------------------【sqlite 字段類型】------------------

sqlite只有5種數(shù)據(jù)類型，用于存儲(chǔ)。
數(shù)據(jù)類型 占用字節(jié) 描述
NULL 
TEXT ？ 使用數(shù)據(jù)庫(kù)編碼（UTF-8，UTF-16BE或者UTF-16LE）存放
INTEGER 根據(jù)值的大小以1,2,3,4,6或8字節(jié)存放 值是有符號(hào)整形
REAL 以8字節(jié)IEEE浮點(diǎn)數(shù)存放 
BLOB 最多 1,000,000,000 字節(jié) 只是一個(gè)數(shù)據(jù)塊，完全按照輸入存放（即沒有準(zhǔn)換）存放。

使用時(shí)會(huì)將【內(nèi)存數(shù)據(jù)類型】和【存儲(chǔ)數(shù)據(jù)類型】之間進(jìn)行轉(zhuǎn)換。
如將布爾值，轉(zhuǎn)換成INTEEGER存儲(chǔ)。具體參見手冊(cè)：

http://www.sqlite.org/datatype3.html

沒有日期型，沒有布爾型，這一點(diǎn)要注意。

------------------【應(yīng)用場(chǎng)景，重要！】------------------

powershell 傳教士 原創(chuàng)文章 始作于 2016-01-01 元旦， 改 允許轉(zhuǎn)載，但必須保留名字和出處，否則追究法律責(zé)任

下為個(gè)人整理的應(yīng)用場(chǎng)合，如有不妥，請(qǐng)斧正。

1 存儲(chǔ)數(shù)據(jù)。中量數(shù)據(jù)。
這個(gè)中量數(shù)據(jù)是指，由于sqlite表不支持分區(qū)，所以應(yīng)該在一億條以下，或者更少。
excel表存儲(chǔ)數(shù)據(jù)有個(gè)行數(shù)（記錄條數(shù)）限制，104萬左右。比如我有110萬數(shù)據(jù)，只有一列，要統(tǒng)計(jì)列最大值。這時(shí)用excel就不方便了。
excel中每單元格最大存儲(chǔ)32k字節(jié)，access2016中，每個(gè)text字段的容量存儲(chǔ)為64k字節(jié)，行數(shù)倒是夠用。

2 各種數(shù)據(jù)統(tǒng)計(jì)。
3 由于sqlite支持純內(nèi)存數(shù)據(jù)庫(kù)，把所有庫(kù)放在內(nèi)存中?？梢越Y(jié)合上述兩種需求。即從大庫(kù)查詢（或?qū)С觯┎糠謹(jǐn)?shù)據(jù)，在內(nèi)存中進(jìn)行統(tǒng)計(jì)。
4 缺點(diǎn)是：數(shù)據(jù)展示不好。不像excel、access那樣（對(duì)業(yè)務(wù)人員）有表，圖。
5 sqlite是關(guān)系型數(shù)據(jù)庫(kù)，join，group by，order by這些基本功能和mysql，sql server比不遜色。單線程性能也很好。另外單機(jī)，綠色，軟件1M以下，支持?jǐn)?shù)據(jù)量極大，比access強(qiáng)。

------------------【圖形管理工具】------------------

英文：
SQLiteStudio http://sqlitestudio.pl/
SQLiteManager http://www.sqlabs.com/
SQLite Expert http://www.sqliteexpert.com/
SQLite Developer http://www.sqlitedeveloper.com/
SQLiteSpy http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index
RazorSQL http://www.razorsql.com/

中文：
SQLite Administrator http://sqliteadmin.orbmu2k.de/ 打不開3.x版本數(shù)據(jù)庫(kù)
navicat for sqlite https://www.jb51.net/database/132499.html推薦
Database.NET http://fishcodelib.com/database.htm 推薦

------------------【下載安裝】------------------
ps調(diào)用sqlite的兩種方法。這里我們主要介紹第二種
1 https://psqlite.codeplex.com/
2 https://github.com/RamblingCookieMonster/PSSQLite
上述2種，都支持支持winxp，powershell2.0，及以上。

2.1 下載：
https://github.com/RamblingCookieMonster/PSSQLite/archive/master.zip

腳本之家下載：PSSQLite-master-jb51.zip

2.2 建立【C:\Users\你的用戶名\Documents\WindowsPowerShell\Modules】目錄，并解壓：
把 PSSQLite-master\PSSQLite 這個(gè)目錄復(fù)制到，你的模塊目錄中。
即modules目錄中，含有PSSQLite這個(gè)目錄。

2.3 查看模塊中的所有命令：
Import-Module pssqlite
get-command * -module pssqlite

返回
CommandType Name Version Source
----------- ---- ------- ------
Function Invoke-SQLiteBulkCopy 1.0.1 pssqlite
Function Invoke-SqliteQuery 1.0.1 pssqlite
Function New-SQLiteConnection 1.0.1 pssqlite
Function Out-DataTable 1.0.1 pssqlite

2.4 一鍵安裝命令：
#一鍵安裝命令依賴.net 4.5 及以上。powershell 3.0 及以上。如果不滿足條件，請(qǐng)用手動(dòng)安裝方法。
mkdir "$env:USERPROFILE\Documents\WindowsPowerShell\Modules"
Invoke-WebRequest https://github.com/RamblingCookieMonster/PSSQLite/archive/master.zip -OutFile .\master.zip
add-Type -AssemblyName 'System.IO.Compression.Filesystem'
[System.IO.Compression.ZipFile]::ExtractToDirectory("$pwd\master.zip","$pwd")
Copy-Item -Recurse PSSQLite-master\PSSQLite "$env:USERPROFILE\Documents\WindowsPowerShell\Modules"
Import-Module pssqlite
get-command * -module pssqlite

------------------【命令幫助】------------------

Invoke-SqliteQuery -DataSource $庫(kù)文件名 -query $查詢字符串 -as 輸出格式(PSObject,DataRow,DataTable,DataSet,SingleValue)

$內(nèi)存庫(kù) = New-SQLiteConnection -DataSource :MEMORY:
Invoke-SqliteQuery -SQLiteConnection $內(nèi)存庫(kù) -Query "CREATE TABLE OrdersToNames (OrderID INT PRIMARY KEY, fullname TEXT);" #建表語句
Invoke-SqliteQuery -SQLiteConnection $內(nèi)存庫(kù) -Query "INSERT INTO OrdersToNames (OrderID, fullname) VALUES (1,'Cookie Monster');" #插入語句
Invoke-SqliteQuery -SQLiteConnection $內(nèi)存庫(kù) -Query "SELECT xxx FROM yyy WHERE mmm=nnn;" #你想要的統(tǒng)計(jì)語句

問：csv如何入庫(kù)？
答：
最好用圖形工具搞，也可以用下列類似命令。
$dt1 = get-process | Out-DataTable
Invoke-SQLiteBulkCopy -DataTable $st1 -DataSource $DataSource -Table 表名
具體參見例子2

------------------【powershell操作sqlite經(jīng)驗(yàn)】------------------
1 不外乎查詢出行，查詢出列， 或查詢出表，然后foreach。
2 不外乎去重，合并結(jié)果，統(tǒng)計(jì)最大，列中數(shù)字合計(jì)。

問：乍開始我不熟悉sql語句咋辦？
答：
用拐棍！完全使用圖形程序（找傳教士要navicat for sqlite）來建表，導(dǎo)入數(shù)據(jù)，在圖形軟件中運(yùn)行sql。

問：現(xiàn)在我已經(jīng)會(huì)用【常用sql語句】+【圖形程序】來處理數(shù)據(jù)了。為啥還要用腳本呢？
答：
圖形程序畢竟只是根拐杖。你要處理的數(shù)據(jù)有可能要先取到一行，（比如用"_"先拆分，分成n列）預(yù)先用ps處理后再入庫(kù)。
庫(kù)里輸出的行列，也很容易（用Out-GridView進(jìn)行展示等。）所以說還是要ps+sqlite共同處理的。

---------------【根據(jù)官網(wǎng)手冊(cè)，增減的sql教程】----------------
sqlite官網(wǎng)，手冊(cè)位置：

http://www.sqlite.org/lang.html

問：什么是（關(guān)系型）數(shù)據(jù)庫(kù)索引，索引有什么好處壞處？
答：
索引是把無序的每條數(shù)據(jù)，添加了一個(gè)從小到大的順序，變成了有序的數(shù)據(jù)。
每個(gè)字段都可以添加一個(gè)索引?；蛘遪個(gè)字段公用一個(gè)索引。
索引加速了查詢速度，降低了插入速度。

問：如何快速插入數(shù)據(jù)？
答：
1 較少并發(fā)寫入，最好只有一個(gè)線程在讀寫。
2 盡量減少索引。
3 使用事務(wù)。事務(wù)是由類似【START TRANSACTION】，【COMMIT】sql關(guān)鍵字包含的命令。
事務(wù)的作用是，在所有數(shù)據(jù)插入后，即【COMMIT】后，才一次性修改所有索引，而不是插入一條，修改一次索引。
4 使用.net連接器封裝的事務(wù)。即類似sql bulkcopy 之類的方法。

問：事務(wù)相關(guān)的sql語句是？
答：
BEGIN TRANSACTION; 事務(wù)開始
COMMIT TRANSACTION; 顯示事務(wù)被提交，數(shù)據(jù)表中的數(shù)據(jù)也發(fā)生了變化。
ROLLBACK TRANSACTION; 回滾

問：為什么傳教士也很愛sqlite？
答：
PRAGMA encoding; # 這個(gè)命令將返回庫(kù)編碼默認(rèn)值，utf-8
用 中文表名，字段名毫無問題?。?！

問：打開庫(kù)文件名后，列出所有《庫(kù)名》的sql語句是？
答：
PRAGMA database_list;

問：打開sqlite庫(kù)文件后，默認(rèn)的數(shù)據(jù)庫(kù)名是？
答：
main

問：列出所有表名的sql語句是？
答：
SELECT name FROM sqlite_master WHERE type='table' order by name;

問：測(cè)試表是否存在的sql語句是？
答：
SELECT name FROM sqlite_master WHERE name='表名' ;

問：列出某個(gè)表的所有索引，要用的sql語句是？
答：
PRAGMA index_list(表名);

問：建立表的sql語句是？
答：
CREATE TABLE IF NOT EXISTS '表名' ('自增' INTEGER PRIMARY KEY AUTOINCREMENT, '字段名2' REAL)
CREATE TABLE IF NOT EXISTS '表名' ('字段名1' INTEGER AUTOINCREMENT, '字段名2' REAL, '字段名3' TEXT, '字段名4' BLOB,PRIMARY KEY ('字段名1'))
CREATE TEMP TABLE #建臨時(shí)表
PRIMARY KEY #主鍵
AUTOINCREMENT #自增

問：建立，刪除索引的sql語句是？
答：
CREATE INDEX IF NOT EXISTS 索引名 ON 表名(字段名)
CREATE INDEX IF NOT EXISTS 索引名 ON 表名(字段名 ASC,字段名2 DESC)
CREATE UNIQUE INDEX IF NOT EXISTS 索引名 ON 表名(字段名 DESC)#唯一索引

刪除索引：
DROP INDEX IF EXISTS 索引名

傳教士問：
我搜遍了全世界，包括官方，也搞不清楚為啥不是DROP INDEX IF EXISTS 索引名 on 表名？而是DROP INDEX IF EXISTS 數(shù)據(jù)庫(kù)名.索引名
一個(gè)庫(kù)里，可以有多個(gè)表啊，請(qǐng)問誰能答？

問：什么是sqlite數(shù)據(jù)清理？有什么好處？
答：
刪除一條記錄時(shí)只是打上刪除標(biāo)記。數(shù)據(jù)清理是徹底刪除數(shù)據(jù)。
大量的插入、更新和刪除等操作后，清理下數(shù)據(jù)，有助于讀取性能。還會(huì)歸還空間給操作系統(tǒng)。
sqlite的數(shù)據(jù)清理，只能對(duì)整個(gè)數(shù)據(jù)庫(kù)來操作。

問：數(shù)據(jù)清理命令是？
答：
Attached 數(shù)據(jù)庫(kù)名 連接數(shù)據(jù)庫(kù)。
vacuum 刪除已經(jīng)打上刪除標(biāo)記的表，記錄。
reindex 重生成索引

問：查看表結(jié)構(gòu)的sql語句是？
答：
PRAGMA table_info(表名);

問：清空表的sql語句是？
答：
delete from 表名;
update sqlite_sequence SET seq = 0 where name ='表名'; #自增字段歸零。sqlite_sequence是main庫(kù)中的特殊表。

問：插入數(shù)據(jù)的sql語句是？
答：
INSERT INTO 表名 VALUES(null,21.5,'this is a texttest.','blob WER@#$@$%^^')

問：空值是？
答：
null

http://www.sqlite.org/lang_select.html
問：查詢數(shù)據(jù)的sql語句是？
答：
SELECT '字段名' FROM '表名' WHERE 條件

問：查詢結(jié)果去重的sql語句是？
答：
SELECT DISTINCT '字段名'
SELECT DISTINCT '字段名1','字段名2','字段名3','字段名4' #所有字段的值完全相同，才算重復(fù)。

問：集合去重的sql語句是？
答：
SELECT 返回的集合1
UNION
SELECT 返回的集合2

問：查詢結(jié)果按 '字段名' 分組輸出的sql語句是？
答：
SELECT '字段名' FROM '表名' WHERE 條件 GROUP BY '字段名' ;
GROUP BY 一定要放在WHERE之后，GROUP BY 一定要放在ORDER BY之前。

問：查詢結(jié)果按 '字段名' 排序輸出的sql語句是？
答：
SELECT '字段名' FROM '表名' WHERE 條件 ORDER BY '字段名' ASC ;
SELECT '字段名' FROM '表名' WHERE 條件 ORDER BY '字段名' DESC ;

問：隱藏的自增列叫什么？
答：
rowid或_rowid_
在沒有INTEGER類型的主鍵時(shí)，存在這個(gè)隱藏字段。

問：sqlite有個(gè)叫sqlite3.exe的客戶端軟件。我可以用這個(gè)sqlite.exe + cmd + bat 玩轉(zhuǎn)sqlite數(shù)據(jù)庫(kù)么？
答：
基本不行。
簡(jiǎn)單來說powershell有內(nèi)置的值類型，如小數(shù)數(shù)值，和表datetable。而bat中沒有。
powershell字符串對(duì)象長(zhǎng)度大，bat數(shù)據(jù)超過8000左右字母不行。

------------------【例子】------------------ 

# 例子1 ：專門給菜鳥編寫的 PowerShell 操作Sqlite的例子。大量硬編碼，快速上手。win10測(cè)試通過。

Import-Module pssqlite


$sqlite庫(kù)文件名 = "a:\pscode\sqlite\test001.db" # 內(nèi)存庫(kù)用 ':MEMORY:'
$sqlite庫(kù)名 = "" #默認(rèn)main
$sqlite表名 = "biao001"

#region sql
#---
$建立_表_的sql語句 = ""
$建立_索引_的sql語句 = "CREATE INDEX IF NOT EXISTS $將要建立的索引名 ON $sqlite表名($將要建立索引的字段名)"
#powershell 傳教士 原創(chuàng)例子 始作于 2016-01-01 元旦 允許轉(zhuǎn)載，但必須保留名字和出處，否則追究法律責(zé)任
#---
$列出所有_庫(kù)名_的sql語句 = 'PRAGMA database_list;'
$列出所有_表名_的sql語句 = "SELECT name FROM sqlite_master WHERE type='table' order by name;"
$列出某個(gè)_表結(jié)構(gòu)_的sql語句 = "PRAGMA table_info($sqlite表名);"
$列出某個(gè)_表的所有索引_的sql語句 = "PRAGMA index_list($sqlite表名); "

#---
$測(cè)試_表是否存在_的sql語句 = "SELECT name FROM sqlite_master WHERE name='$sqlite表名' ; "

#---
$插入一條_數(shù)據(jù)數(shù)據(jù)_的sql語句 = ''

#---
$查詢_表_的sql語句 = ''

#---
$清空_表_的sql語句 = "delete from $sqlite表名 ;"
#---
#endregion sql


# 前提1 庫(kù)文件確實(shí)存在，有【$sqlite庫(kù)文件名】。則建立連接。
# 前提2 庫(kù)文件不存在，有【$sqlite庫(kù)文件名】和【$建立_表的sql語句】。則建立連接，然后建立表。
# 前提3 有【$建立_表的sql語句】和【$sqlite庫(kù)文件名 = ':MEMORY:'】字符串。則建立連接，然后建立表。
if ($sqlite庫(kù)文件名 -eq ':MEMORY:')
{
 Write-Verbose '新建【內(nèi)存】sqlite庫(kù)文件'
 $我的Sqlite連接 = New-SQLiteConnection -DataSource $sqlite庫(kù)文件名
 Invoke-SqliteQuery -query $建立_表的sql語句 -DataSource $sqlite庫(kù)文件名 
}
else
{
 if (test-path $sqlite庫(kù)文件名)
 {
  Write-Verbose 'sqlite庫(kù)文件已經(jīng)存在'
  $我的Sqlite連接 = New-SQLiteConnection -DataSource $sqlite庫(kù)文件名
 }
 else
 {
  Write-Verbose '新建sqlite庫(kù)文件'
  $我的Sqlite連接 = New-SQLiteConnection -DataSource $sqlite庫(kù)文件名

  #新建表
  $字段字串 = ""
  $建立_表_的sql語句 = 
@"
CREATE TABLE IF NOT EXISTS '$sqlite表名' ($字段字串)
"@
  Invoke-SqliteQuery -query $建立_表_的sql語句 -DataSource $sqlite庫(kù)文件名
 }
}

# 列出庫(kù)，列出表。前提【$我的Sqlite連接】已經(jīng)建立。
Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $列出所有_庫(kù)名_的sql語句 
Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $列出所有_表名_的sql語句



# 建表索引。前提【$sqlite表名】，【$將要建立的索引名】，【$將要建立索引的字段名】已經(jīng)賦值。
$將要建立的索引名 = 'yyy'
$將要建立索引的字段名 = 'bbb'
$建立_索引_的sql語句 = "CREATE INDEX IF NOT EXISTS $將要建立的索引名 ON $sqlite表名($將要建立索引的字段名)"
Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $建立_索引_的sql語句



# 測(cè)表是否存在，查表結(jié)構(gòu)，查表索引。前提【$sqlite表名】已經(jīng)賦值。
Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $測(cè)試_表是否存在_的sql語句 #返回表名 
Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $列出某個(gè)_表結(jié)構(gòu)_的sql語句 
Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $列出某個(gè)_表的所有索引_的sql語句



$查詢_表_的sql語句 = "select * from '$sqlite表名'  ;"
$查詢_表_的sql語句
Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $查詢_表_的sql語句



#插入數(shù)據(jù)
$字段1值 = 'null'
$字段2值 = '121215687'
$字段3值 = ''
$插入一條_數(shù)據(jù)數(shù)據(jù)_的sql語句 = "INSERT INTO $sqlite表名 VALUES($字段1值,$字段2值) ;" #本例表只有2個(gè)字段 
Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $插入一條_數(shù)據(jù)數(shù)據(jù)_的sql語句



#查詢數(shù)據(jù)
$查詢_表_的sql語句 = "select * from '$sqlite表名' ;"
$查詢_表_的sql語句
Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $查詢_表_的sql語句



#清空
$清空_表_的sql語句 = "delete from $sqlite表名 ;"
Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $清空_表_的sql語句
Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $查詢_表_的sql語句

例子2：專門給菜鳥編寫的 PowerShell 操作Sqlite bulkcopy的例子。大量硬編碼，快速上手。win10測(cè)試通過。

Import-Module pssqlite

$sqlite庫(kù)文件名 = "a:\pscode\sqlite\test006.db" # 內(nèi)存庫(kù)用 ':MEMORY:'
$sqlite庫(kù)名 = "" #默認(rèn)main
$sqlite表名 = "biao001"

if (test-path $sqlite庫(kù)文件名)
{
 Write-error 'sqlite庫(kù)文件已經(jīng)存在'
 exit 1
}

$點(diǎn)net的DataTable = get-process | Out-DataTable #數(shù)據(jù)來源

$列集合 = $點(diǎn)net的DataTable.Columns
[array]$每個(gè)字段名 = @()
foreach ($temp001 in $列集合)
{
 $每個(gè)字段名 += $temp001.ColumnName
}

#powershell 傳教士 原創(chuàng)例子 始作于 2016-01-01 元旦 允許轉(zhuǎn)載，但必須保留名字和出處，否則追究法律責(zé)任

$每個(gè)字段類型 = 'text'
$temp002 = $每個(gè)字段名[0]
$建立_表_的sql語句 = 
@"
CREATE TABLE IF NOT EXISTS '$sqlite表名' ($temp002 $每個(gè)字段類型)
"@
#Write-Warning $建立_表_的sql語句
Invoke-SqliteQuery -query $建立_表_的sql語句 -DataSource $sqlite庫(kù)文件名

for ($i = 1;$i -le $每個(gè)字段名.Count ;$i++)
{
 $temp003 = $每個(gè)字段名[$i]
 $建立_表_的sql語句 = 
@"
ALTER TABLE '$sqlite表名' ADD COLUMN '$temp003' $每個(gè)字段類型 ;
"@
# Write-Warning $建立_表_的sql語句
 Invoke-SqliteQuery -query $建立_表_的sql語句 -DataSource $sqlite庫(kù)文件名
}

Invoke-SQLiteBulkCopy -DataTable $點(diǎn)net的DataTable -DataSource $sqlite庫(kù)文件名 -Table $sqlite表名 -force

文章到這就結(jié)束了，需要的朋友可以多看看實(shí)例。