小布老師《SQLite快速入門視頻教程》的筆記

看了小布老師的《SQLite快速入門視頻教程》後整理的筆記

 

第一講

什麼是SQLite？
為什麼要用？
如何獲得？如何安裝？
如何管理？
如何使用Perl存取SQLite的資料；
如何用C或C++；
SQLite不支援的SQL特性；

自包含，沒有伺服器，零配置，支援事務。

SQLite的資料庫就一個檔案，包括：多表、索引、觸發器、和視圖；
SQLite直接讀寫磁碟上的檔案。

事務的四個特性（ACID）atomic（原子的）、consistent（一致的）、isolated（獨立的）、durable（持久的）
實現大部分SQL92標準
整個資料庫儲存在一個跨平台的資料檔案上；
支援的資料庫大小能夠達到TB，支援GB的字串或BLOBs（二進位資訊）。
所有功能都編譯進去250KB，選擇一些特性可以縮小到150KB；
SQLite不支援並發，不是兩個進程同時訪問，獨佔式的
代碼品質高，99%的代碼都經過測試；
自包含的意思，就是不依賴外界的任何庫；

代碼可以應用在任何領域，包括商業領域；

SQLite的設計目標是一切從簡：
管理方便；
使用方便；
容易嵌套到其它程式；
維護和定製容易；

SQLite的目的不是取代ORACLE，而是用於取代fopen（）；

著名使用SQLite的客戶：
Adobe
蘋果
firefox
GE
google
微軟
麥咖啡
skype

SQLite能夠很好工作的情境；
儲存應用程式的配置資訊；
嵌入式裝置和應用程式；
小網站，原型系統。如果大網站並發性高，還是建議用mysql；
取代，隨意的混亂的磁碟檔案；
內部或臨時的資料庫；
命令列的資料分析工具；
做示範或者測試的時候；
閱讀SQLite的源碼，學習資料庫技術；
對SQL語言的新特性進行擴充；

播布客的後台系統用的是mySQL資料庫；

第二講

管理工具
源碼層級安裝
已經編譯好的可執行檔，for windows或linux；
下載源碼自己編譯；

原始碼：
amalgamation版本，把很多細小的東西合并成一個大傢伙。
linux下示範怎麼編譯；

作業系統是CentOS5.2
chmod +x sqlite3-3.6.3.bin
./sqlite3-3.6.3.bin
和windows下一樣，進入SQLite shell

tar zxvf sqlite-amalgamation-3.6.3.tar.gz

原始碼安裝的三部曲：
configure；
make；
make install；

通常都提供README和INSTALL檔案，都是文字檔；

SQLite的源碼安裝過程：
./configure --prefix=/opt/sqlite3
--prefix 表明目標檔案安裝在什麼目錄下,不指定安裝在預設目錄下/usr/local/之類的。
gcc一定要裝

輸入make開始編譯；

輸入make install，把檔案copy到目標目錄下；

安裝之後，去/opt/sqlite3下看看，就三個目錄：bin、include、lib
標準的軟體布局結構
lib下有靜態庫和動態庫，推薦用動態庫

sqlite3.exe是shell.c,2千多行原始碼實現了簡單的命令列工具；
動態庫是sqlite3.c

sqlite3工具的功能：
建立資料庫；
執行SQL命令；
查詢資料庫的表結構，或其它對象的結構；
匯入和匯出資料；
改變輸出格式；
使用在shell指令碼中；學會shell自動化指令碼編程；

建立資料庫；
sqlite3 資料庫檔案名

輸入SQL語句的時候，結束一定要用分號，這也是SQL的標準；

linux下把sqlite加入PATH路徑，這樣在任何路徑下都能夠使用sqlite3；
# echo $PATH
cp /opt/sqlite3/bin/sqlite3 /usr/local/bin

系統資料表SQLITE_MASTER，存放database schema
不能對SQLITE_MASTER表進行DROP TABLE,UPDATE,INSERT,DELETE操作

暫存資料表儲存在sqlite_temp_master中。
SQLite除了資料庫檔案之外，在運行過程中會產生一些臨時檔案。

sqlite3隻是一個殼，真正執行，還是會調用SQLite的庫；可以研究一下。
sqlite3本身的命令以“.”開始，.help；

SQL PLUS有三大類命令：
SQL命令，提交給資料庫引擎；
PL SQL 命令，提交給資料庫引擎；
SQL PLUS本身的命令，本地執行；

第三講

sqlite3本身的命令
.help

改變輸出格式的命令；
csv，以逗號分隔的記錄；
.mode命令在8種顯示格式中進行切換；
預設是list模式，預設的分隔線是“|”；
list模式特別適合用於AWK；
line模式，每列都單獨一行，列名等於列值，記錄之間有空行；
column模式，與SQL PLUS中的預設模式相同；.header on 開啟頭，.header off 關閉頭；此模式可讀性比較好；
.width 3 30,第一列最長3，第二列最長30；
insert模式，產生INSERT的SQL語句，挺有意思；.mode insert 目標表名；
html模式，XHTML table,適合做CGI處理;

list模式一行一條記錄，每列豎線分隔；

linux，unix的設計思想是小而美，一個工具只幹一件事情。通過管道把命令串聯起來；

預設查詢結果是寫到螢幕上，也可以寫到一個檔案中。使用.output命令；
.output stdout，輸出又回到了螢幕上；

查詢Database Schema，就是表結構、資料庫結構；
.tables，可以看資料庫中的所有表；
.indices，可以列出一個指定表的所有索引；
.schema，建立表和索引時候的create語句，也可以在後面指明表名；
.databases,查看當前開啟的所有資料庫，通常是一個main一個temp；

第四講

把整個資料庫導成ASC文字檔的功能；
.dump命令；匯出的SQL語句，可以放到ORACLE中去執行；
一個產生資料庫歸檔copy的命令；
$ echo ".dump" | sqlite3 ex1 | gzip -c > ex1dmp.gz
重構資料庫的命令：
zcat ex1dmp.gz | sqlite3 ex2
從SQLite3中提取資料，導到其它流行的資料庫中；

.explain命令
EXPLAIN命令，就是查詢一條SQL語句最終解析出來的執行計畫。ORACLE中也有，對SQL語句調優很有用；
一條SQL語句的處理過程：解析、分析、執行，看看SQL語句的效率；
> .explain
> explain select * from t;

.timeout命令
預設逾時時間是0，查詢一張表或索引，發現表或索引被鎖定，就立刻返回，不等待；
sqlite只適合一個進程訪問，不適合多進程的並發訪問；

在SHELL中使用SQLite3的命令；
sqlite3命令的輸入和輸出都可以重新導向，常用的使用模式；
SQL語句中，除了分號結束，用GO或者/也可以執行；
# sqlite3 demo.db "select * from t"

一個簡單的shell指令碼
shell指令碼的開頭一定要有一個"#!",這是規定。
成對的EOF之間的東西都被原樣匯入sqlite中；here document技術；
shell裡面調用互動式程式的方法；

如何使用Perl指令碼訪問sqlite資料庫；
Perl，老牌語言，有20年歷史，但在走下坡路，被python或者ruby漸漸取代；
Perl在北美比較流行；
工作中使用的工具是Perl+shell；
把Perl形容為瑞士軍刀，但可讀性差；
ruby適合web快速開發；
perl對系統管理和生物資訊處理比較厲害；

windows平台下 ActiveStat Perl www.activestate.com  免費
Linux/Unix www.perl.org

DBI
運行一段小程式，確定perl是否安裝了對sqlite的支援；
perl需要sqlite的驅動，來訪問sqlite的資料庫，如果沒裝，還需要安裝一下；

第五講

本講主題，如何使用perl和C語言訪問sqlite3資料庫；

使用perl訪問sqlite資料庫
檢查perl是否安裝了sqlite驅動程式的代碼；
perl訪問資料庫都用DBI介面，在DBI之下有DBD（資料庫驅動），分別開發支援mysql，oracle等；

一個基本的插入作業碼示範；
先和資料庫建立串連；
做些準備工作；
執行語句；
判斷是否出錯；
提交；
斷開；

一個基本的查詢作業碼示範：
查詢語句執行的時候，先執行prepare；
然後執行；
提取結果集，先把每資料行繫結到變數上，然後在while迴圈中進行fetch；
完成，釋放結果集；
中斷連線；

perl處理文字檔功能強大，又能操作資料庫；

使用C語言訪問sqlite
兩個對象
資料庫連接對象：sqlite3；控制串連；
語句執行對象：sqlite3_stmt；控制SQL語句；

常用的六個介面函數，操作sqlite的核心函數：
sqlite3_open()
sqlite3_prepare()
sqlite3_step()，類似fetch功能；
sqlite3_column()
sqlite3_finalize()
sqlite3_close()，中斷連線；

sqlitedemo.c
#include

sqlite3_errmsg(db)；獲得失敗的原因；

回呼函數的作用；
執行完查詢，自動調用回呼函數，把查詢的結果列印出來。

編譯器要用到標頭檔和lib庫。這次使用靜態庫。

# gcc -v 看看有沒有gcc
編譯命令
# gcc -I/opt/sqlite3/include -L/opt/sqlite3/lib -lsqlite3 sqlite3demo.c -o dbtest
-I標頭檔搜尋路徑
-L庫檔案搜尋路徑
-l庫檔案

sqlite的局限性：
官方網站上有大小的支援；
不支援外鍵約束；
對觸發器的支援不是很完整；
對ALTER TABLE的支援不是很完整；
不支援嵌入式事務；
不支援有串連和FULL OUTER JOIN.
不支援視圖的寫操作；
不支援GRANT和REVOKE；

sqlite作為單使用者，單機版的資料庫很不錯。