sqlite是一個很小巧的,很方便嵌入到腳本語言中的,關係型資料庫。它給powershell提供了一個免費的,處理行列整齊數據,存儲數據,統計數據的極品神器。本文分享了powershell操作sqlite的例子和技巧。
腳本經常需要處理文本,有時候是行列整齊文本。那麼powershell腳本處理行列文本有幾種方法呢?一種是excel,另外的一些是?access?sqlite?
sqlite是一個很小巧的,很方便嵌入到腳本語言中的,關係型資料庫。它給powershell提供了一個免費的,處理行列整齊數據,存儲數據,統計數據的極品神器。
只要你學會了它,上天入地皆可去得。而且以後學sql server,mysql,都類似,沒有難度。
帶著引號搜本文關鍵字: "^_^上天入地皆可去^_^"
發文初衷:世界上幾乎沒有講powershell調用sqlite的帖子。
初稿,會更新。有錯誤歡迎指正,謝謝。
powershell,sqlite,資料庫,database,powershell傳教士
------------------【sqlite 欄位類型】------------------
sqlite只有5種數據類型,用於存儲。
數據類型 占用位元組 描述
NULL
TEXT ? 使用資料庫編碼(UTF-8,UTF-16BE或者UTF-16LE)存放
INTEGER 根據值的大小以1,2,3,4,6或8位元組存放 值是有符號整形
REAL 以8位元組IEEE浮點數存放
BLOB 最多 1,000,000,000 位元組 只是一個數據塊,完全按照輸入存放(即沒有準換)存放。
使用時會將【記憶體數據類型】和【存儲數據類型】之間進行轉換。
如將布爾值,轉換成INTEEGER存儲。具體參見手冊:
http://www.sqlite.org/datatype3.html
沒有日期型,沒有布爾型,這一點要註意。
------------------【應用場景,重要!】------------------
powershell 傳教士 原創文章 始作於 2016-01-01 元旦, 改 允許轉載,但必須保留名字和出處,否則追究法律責任
下為個人整理的應用場合,如有不妥,請斧正。
1 存儲數據。中量數據。
這個中量數據是指,由於sqlite表不支持分區,所以應該在一億條以下,或者更少。
2 各種數據統計。
3 由於sqlite支持純記憶體資料庫,把所有庫放在記憶體中。可以結合上述兩種需求。即從大庫查詢(或導出)部分數據,在記憶體中進行統計。
4 缺點是:數據展示不好。不像excel、access那樣(對業務人員)有表,圖。
5 sqlite是關係型資料庫,join,group by,order by這些基本功能和mysql,sql server比不遜色。單線程性能也很好。另外單機,綠色,軟體1M以下,支持數據量極大,比access強。
------------------【圖形管理工具】------------------
英文:
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版本資料庫
navicat for sqlite http://www.xiazaiba.com/html/3350.html 推薦
Database.NET http://fishcodelib.com/database.htm 推薦
------------------【下載安裝】------------------
ps調用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
2.2 建立【C:\Users\你的用戶名\Documents\WindowsPowerShell\Modules】目錄,並解壓:
把 PSSQLite-master\PSSQLite 這個目錄複製到,你的模塊目錄中。
即modules目錄中,含有PSSQLite這個目錄。
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 及以上。如果不滿足條件,請用手動安裝方法。
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 $庫文件名 -query $查詢字元串 -as 輸出格式(PSObject,DataRow,DataTable,DataSet,SingleValue)
$記憶體庫 = New-SQLiteConnection -DataSource :MEMORY:
Invoke-SqliteQuery -SQLiteConnection $記憶體庫 -Query "CREATE TABLE OrdersToNames (OrderID INT PRIMARY KEY, fullname TEXT);" #建表語句
Invoke-SqliteQuery -SQLiteConnection $記憶體庫 -Query "INSERT INTO OrdersToNames (OrderID, fullname) VALUES (1,'Cookie Monster');" #插入語句
Invoke-SqliteQuery -SQLiteConnection $記憶體庫 -Query "SELECT xxx FROM yyy WHERE mmm=nnn;" #你想要的統計語句
問:csv如何入庫?
答:
最好用圖形工具搞,也可以用下列類似命令。
$dt1 = get-process | Out-DataTable
Invoke-SQLiteBulkCopy -DataTable $st1 -DataSource $DataSource -Table 表名
具體參見例子2
------------------【powershell操作sqlite經驗】------------------
1 不外乎查詢出行,查詢出列, 或查詢出表,然後foreach。
2 不外乎去重,合併結果,統計最大,列中數字合計。
問:乍開始我不熟悉sql語句咋辦?
答:
用拐棍!完全使用圖形程式(找傳教士要navicat for sqlite)來建表,導入數據,在圖形軟體中運行sql。
問:現在我已經會用【常用sql語句】+【圖形程式】來處理數據了。為啥還要用腳本呢?
答:
圖形程式畢竟只是根拐杖。你要處理的數據有可能要先取到一行,(比如用"_"先拆分,分成n列)預先用ps處理後再入庫。
庫里輸出的行列,也很容易(用Out-GridView進行展示等。)所以說還是要ps+sqlite共同處理的。
---------------【根據官網手冊,增減的sql教程】----------------
sqlite官網,手冊位置:
http://www.sqlite.org/lang.html
問:什麼是(關係型)資料庫索引,索引有什麼好處壞處?
答:
索引是把無序的每條數據,添加了一個從小到大的順序,變成了有序的數據。
每個欄位都可以添加一個索引。或者n個欄位公用一個索引。
索引加速了查詢速度,降低了插入速度。
問:如何快速插入數據?
答:
1 較少併發寫入,最好只有一個線程在讀寫。
2 儘量減少索引。
3 使用事務。事務是由類似【START TRANSACTION】,【COMMIT】sql關鍵字包含的命令。
事務的作用是,在所有數據插入後,即【COMMIT】後,才一次性修改所有索引,而不是插入一條,修改一次索引。
4 使用.net連接器封裝的事務。即類似sql bulkcopy 之類的方法。
問:事務相關的sql語句是?
答:
BEGIN TRANSACTION; 事務開始
COMMIT TRANSACTION; 顯示事務被提交,數據表中的數據也發生了變化。
ROLLBACK TRANSACTION; 回滾
問:為什麼傳教士也很愛sqlite?
答:
PRAGMA encoding; # 這個命令將返回庫編碼預設值,utf-8
用 中文表名,欄位名毫無問題!!!
問:打開庫文件名後,列出所有《庫名》的sql語句是?
答:
PRAGMA database_list;
問:打開sqlite庫文件後,預設的資料庫名是?
答:
main
問:列出所有表名的sql語句是?
答:
SELECT name FROM sqlite_master WHERE type='table' order by name;
問:測試表是否存在的sql語句是?
答:
SELECT name FROM sqlite_master WHERE name='表名' ;
問:列出某個表的所有索引,要用的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 #建臨時表
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 資料庫名.索引名
一個庫里,可以有多個表啊,請問誰能答?
問:什麼是sqlite數據清理?有什麼好處?
答:
刪除一條記錄時只是打上刪除標記。數據清理是徹底刪除數據。
大量的插入、更新和刪除等操作後,清理下數據,有助於讀取性能。還會歸還空間給操作系統。
sqlite的數據清理,只能對整個資料庫來操作。
問:數據清理命令是?
答:
Attached 資料庫名 連接資料庫。
vacuum 刪除已經打上刪除標記的表,記錄。
reindex 重生成索引
問:查看表結構的sql語句是?
答:
PRAGMA table_info(表名);
問:清空表的sql語句是?
答:
delete from 表名;
update sqlite_sequence SET seq = 0 where name ='表名'; #自增欄位歸零。sqlite_sequence是main庫中的特殊表。
問:插入數據的sql語句是?
答:
INSERT INTO 表名 VALUES(null,21.5,'this is a texttest.','blob WER@#$@$%^^')
問:空值是?
答:
null
http://www.sqlite.org/lang_select.html
問:查詢數據的sql語句是?
答:
SELECT '欄位名' FROM '表名' WHERE 條件
問:查詢結果去重的sql語句是?
答:
SELECT DISTINCT '欄位名'
SELECT DISTINCT '欄位名1','欄位名2','欄位名3','欄位名4' #所有欄位的值完全相同,才算重覆。
問:集合去重的sql語句是?
答:
SELECT 返回的集合1
UNION
SELECT 返回的集合2
問:查詢結果按 '欄位名' 分組輸出的sql語句是?
答:
SELECT '欄位名' FROM '表名' WHERE 條件 GROUP BY '欄位名' ;
GROUP BY 一定要放在WHERE之後,GROUP BY 一定要放在ORDER BY之前。
問:查詢結果按 '欄位名' 排序輸出的sql語句是?
答:
SELECT '欄位名' FROM '表名' WHERE 條件 ORDER BY '欄位名' ASC ;
SELECT '欄位名' FROM '表名' WHERE 條件 ORDER BY '欄位名' DESC ;
問:隱藏的自增列叫什麼?
答:
rowid或_rowid_
在沒有INTEGER類型的主鍵時,存在這個隱藏欄位。
問:sqlite有個叫sqlite3.exe的客戶端軟體。我可以用這個sqlite.exe + cmd + bat 玩轉sqlite資料庫麽?
答:
基本不行。
簡單來說powershell有內置的值類型,如小數數值,和表datetable。而bat中沒有。
powershell字元串對象長度大,bat數據超過8000左右字母不行。
------------------【例子】------------------
# 例子1 :專門給菜鳥編寫的 PowerShell 操作Sqlite的例子。大量硬編碼,快速上手。win10測試通過。 Import-Module pssqlite $sqlite庫文件名 = "a:\pscode\sqlite\test001.db" # 記憶體庫用 ':MEMORY:' $sqlite庫名 = "" #預設main $sqlite表名 = "biao001" #region sql #--- $建立_表_的sql語句 = "" $建立_索引_的sql語句 = "CREATE INDEX IF NOT EXISTS $將要建立的索引名 ON $sqlite表名($將要建立索引的欄位名)" #powershell 傳教士 原創例子 始作於 2016-01-01 元旦 允許轉載,但必須保留名字和出處,否則追究法律責任 #--- $列出所有_庫名_的sql語句 = 'PRAGMA database_list;' $列出所有_表名_的sql語句 = "SELECT name FROM sqlite_master WHERE type='table' order by name;" $列出某個_表結構_的sql語句 = "PRAGMA table_info($sqlite表名);" $列出某個_表的所有索引_的sql語句 = "PRAGMA index_list($sqlite表名); " #--- $測試_表是否存在_的sql語句 = "SELECT name FROM sqlite_master WHERE name='$sqlite表名' ; " #--- $插入一條_數據數據_的sql語句 = '' #--- $查詢_表_的sql語句 = '' #--- $清空_表_的sql語句 = "delete from $sqlite表名 ;" #--- #endregion sql # 前提1 庫文件確實存在,有【$sqlite庫文件名】。則建立連接。 # 前提2 庫文件不存在,有【$sqlite庫文件名】和【$建立_表的sql語句】。則建立連接,然後建立表。 # 前提3 有【$建立_表的sql語句】和【$sqlite庫文件名 = ':MEMORY:'】字元串。則建立連接,然後建立表。 if ($sqlite庫文件名 -eq ':MEMORY:') { Write-Verbose '新建【記憶體】sqlite庫文件' $我的Sqlite連接 = New-SQLiteConnection -DataSource $sqlite庫文件名 Invoke-SqliteQuery -query $建立_表的sql語句 -DataSource $sqlite庫文件名 } else { if (test-path $sqlite庫文件名) { Write-Verbose 'sqlite庫文件已經存在' $我的Sqlite連接 = New-SQLiteConnection -DataSource $sqlite庫文件名 } else { Write-Verbose '新建sqlite庫文件' $我的Sqlite連接 = New-SQLiteConnection -DataSource $sqlite庫文件名 #新建表 $欄位字串 = "" $建立_表_的sql語句 = @" CREATE TABLE IF NOT EXISTS '$sqlite表名' ($欄位字串) "@ Invoke-SqliteQuery -query $建立_表_的sql語句 -DataSource $sqlite庫文件名 } } # 列出庫,列出表。前提【$我的Sqlite連接】已經建立。 Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $列出所有_庫名_的sql語句 Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $列出所有_表名_的sql語句 # 建表索引。前提【$sqlite表名】,【$將要建立的索引名】,【$將要建立索引的欄位名】已經賦值。 $將要建立的索引名 = 'yyy' $將要建立索引的欄位名 = 'bbb' $建立_索引_的sql語句 = "CREATE INDEX IF NOT EXISTS $將要建立的索引名 ON $sqlite表名($將要建立索引的欄位名)" Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $建立_索引_的sql語句 # 測表是否存在,查表結構,查表索引。前提【$sqlite表名】已經賦值。 Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $測試_表是否存在_的sql語句 #返回表名 Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $列出某個_表結構_的sql語句 Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $列出某個_表的所有索引_的sql語句 $查詢_表_的sql語句 = "select * from '$sqlite表名' ;" $查詢_表_的sql語句 Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $查詢_表_的sql語句 #插入數據 $欄位1值 = 'null' $欄位2值 = '121215687' $欄位3值 = '' $插入一條_數據數據_的sql語句 = "INSERT INTO $sqlite表名 VALUES($欄位1值,$欄位2值) ;" #本例表只有2個欄位 Invoke-SqliteQuery -SQLiteConnection $我的Sqlite連接 -Query $插入一條_數據數據_的sql語句 #查詢數據 $查詢_表_的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測試通過。 Import-Module pssqlite $sqlite庫文件名 = "a:\pscode\sqlite\test006.db" # 記憶體庫用 ':MEMORY:' $sqlite庫名 = "" #預設main $sqlite表名 = "biao001" if (test-path $sqlite庫文件名) { Write-error 'sqlite庫文件已經存在' exit 1 } $點net的DataTable = get-process | Out-DataTable #數據來源 $列集合 = $點net的DataTable.Columns [array]$每個欄位名 = @() foreach ($temp001 in $列集合) { $每個欄位名 += $temp001.ColumnName } #powershell 傳教士 原創例子 始作於 2016-01-01 元旦 允許轉載,但必須保留名字和出處,否則追究法律責任 $每個欄位類型 = 'text' $temp002 = $每個欄位名[0] $建立_表_的sql語句 = @" CREATE TABLE IF NOT EXISTS '$sqlite表名' ($temp002 $每個欄位類型) "@ #Write-Warning $建立_表_的sql語句 Invoke-SqliteQuery -query $建立_表_的sql語句 -DataSource $sqlite庫文件名 for ($i = 1;$i -le $每個欄位名.Count ;$i++) { $temp003 = $每個欄位名[$i] $建立_表_的sql語句 = @" ALTER TABLE '$sqlite表名' ADD COLUMN '$temp003' $每個欄位類型 ; "@ # Write-Warning $建立_表_的sql語句 Invoke-SqliteQuery -query $建立_表_的sql語句 -DataSource $sqlite庫文件名 } Invoke-SQLiteBulkCopy -DataTable $點net的DataTable -DataSource $sqlite庫文件名 -Table $sqlite表名 -force
------------【powershell sqlite 模塊 擴展閱讀】---------------
問:sqlite模塊中的dll庫文件有哪些?哪來的?
答:
模塊中有兩個庫文件,分別是【SQLite.Interop.dll】和【System.Data.SQLite.dll】。
下載地址在:
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
問:【SQLite.Interop.dll】是個啥玩意?
答:
我說過,只要這個資料庫相容.net,就可以很容易地被powershell使用。
【SQLite.Interop.dll】是個.net用戶調用sqlite的(中介)插件。
通過它調用【System.Data.SQLite.dll】中的System.Data.SQLite類。
問:如何升級這兩個庫呢?
答:
一般來講無需升級。若要升級就要分32,64位。然後要看.net版本。還得選靜態庫,或動態庫。
選的話當然32位,64位,都要選。選靜態庫。
win7中.net 4.51已經淘汰了,所以應該選4.5.2編譯的。但是呢,沒有4.5.2的。那我們就只能選4.5.1的了。
而win10中,應選4.6的。
----------------【完】----------------