R操作關(guān)系型數(shù)據(jù)庫(kù)
基本要求： R語(yǔ)言基礎(chǔ)，懂得一定SQL語(yǔ)法， 懂得使用搜索引擎
目標(biāo)： 學(xué)會(huì)使用DBI操作SQLite數(shù)據(jù)庫(kù)
簡(jiǎn)介
R本身不具備數(shù)據(jù)庫(kù)操作能力，需要額外的擴(kuò)展包--DBI(database interface）。DBI將數(shù)據(jù)庫(kù)管理分為前端和后端。用戶(hù)在前端使用對(duì)應(yīng)的API, 經(jīng)由DBI轉(zhuǎn)換成相應(yīng)的底層操作.
DBI分為三個(gè)部分：
- 數(shù)據(jù)庫(kù)驅(qū)動(dòng)， 負(fù)責(zé)與數(shù)據(jù)庫(kù)進(jìn)行交互，比如說(shuō)與SQLite數(shù)據(jù)庫(kù)交互要用到RSQLite，如果是MYSQL，則是RMYSQL
- 連接對(duì)象，建立與數(shù)據(jù)庫(kù)的連接，前端操作經(jīng)由該連接完成
- 結(jié)果， 操作的結(jié)果在這里顯示
具體操作
首先加載R包
library(DBI)
library(RSQLite)
第一步： 連接或創(chuàng)建數(shù)據(jù)庫(kù)：dbConnect ，建立連接對(duì)象，后續(xù)的操作都基于該對(duì)象。
常用命令為：
dbExecute和dbSendStatement， 前者適用于數(shù)據(jù)量不大時(shí)，后者適用數(shù)據(jù)量較大時(shí)
# 新建或連接已有的磁盤(pán)上數(shù)據(jù)庫(kù)
con <- dbConnect(RSQLite::SQLite(), 'test.db')
# 臨時(shí)在內(nèi)存中建立
con <- dbConnect(RSQLite::SQLite(), ":memory:")
參數(shù)1: 數(shù)據(jù)庫(kù)驅(qū)動(dòng)引擎(RSQLite::SQLite())；
參數(shù)2： 數(shù)據(jù)庫(kù)連接參數(shù)(如user, password, host, port, dbname)，SQLite只需要提供數(shù)據(jù)庫(kù)命名（不存在則新建），:memory: 則表示在僅保存在內(nèi)存中.
第二步： 在R上實(shí)現(xiàn)數(shù)據(jù)庫(kù)的常用操作： 增刪改查
增：建立表并增加記錄
# 導(dǎo)入已有的數(shù)據(jù)框, 參數(shù)為“連接對(duì)象， 表名，數(shù)據(jù)框”
dbWriteTable(con, 'mtcar', mtcars )
# 建立新表
dbExecute(con, 'CREATE TABLE test (name char(10), age int(10))')
dbExecute(con, 'INSERT INTO test (name, age) VALUES ("Tom", 25), ("Jelly",26)')
# 數(shù)據(jù)量過(guò)大，可以批次插入
rs <- dbSendStatement(con, 'INSERT INTO test (name, age) VALUES ("Tom", 25), ("Jelly",26)' )
dbHasCompleted(rs) # 判斷操作是否完成
dbGetRowAffected(rs) # 返回影響的行
dbClearResult(rs) # 清空內(nèi)存
查： 有兩種方法, 一類(lèi)獲取所有數(shù)據(jù)，一類(lèi)分批獲取數(shù)據(jù)
# 方法1
dbGetQuery(con, 'SELECT * FROM test')
# 方法2
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars)
rs <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4;")
dbFetch(rs, n=10) # 獲取10行
dbFetch(rs, n=10) # 再獲取10行
dbClearResult(rs) # 清空rs句柄中的內(nèi)容
dbDisconnect(con)
刪： 刪除記錄
# 刪除記錄前，要先查詢(xún)
dbGetQuery(con, 'SELECT * FROM mtcar WHERE mpg > 30')
# 刪除記錄
dbExecute(con, 'DELETE FROM mtcar WHERE mpg > 30')
# 刪除所有記錄，謹(jǐn)慎操作
dbExecute(con, 'DROP TABLE test')
dbRemoveTable(con, 'test')
改： 修改記錄
# 修改記錄前也是先查找
dbGetQuery(con, 'SELECT * FROM mtcar WHERE am ==1')
# 然后我們將mpg=21,且qsec=16.46 記錄 的mpg修改為21.1
dbExecute(con, 'UPDATE mtcar SET mpg = 21.1 WHERE mpg =21.0 AND qsec =16.46')
# 事后驗(yàn)證
dbGetQuery(con, 'SELECT * FROM mtcar WHERE mpg = 21.1')
一般而言，增查改刪操作最好能夠有反悔藥。dbBegin(con)會(huì)記錄一系列操作，直到你呈交修改，dbCommit(con)或 回滾修改dbRollback(con)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "cash", data.frame(amount = 100))
# 測(cè)試dbCommit
dbBegin(con)
withdrawal <- 300
dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal))
dbCommit(con)
# 測(cè)試回滾
dbBegin(con)
withdrawl <- 5000
dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal))
## 查看結(jié)果
dbGetQuery(con,'SELECT * FROM cash')
## 回滾
dbRollback(con)
## 檢查結(jié)果
dbGetQuery(con,'SELECT * FROM cash')
所有操作完畢，結(jié)束連接dbDisconnect(con)
下面一些命令也比較常用，用法比較簡(jiǎn)單。
- dbWriteTable(連接對(duì)象, 表名, 數(shù)據(jù)框): 將數(shù)據(jù)框保存為表
- dbReadTable(連接對(duì)象, 表名 ): 讀取指定表中所有內(nèi)容
- dbListTable(連接對(duì)象)： 列出數(shù)據(jù)庫(kù)中包含的表格
- dbListFields(連接對(duì)象, 表名)： 列出制定表格列名
- dbReadTable(連接對(duì)象, 表名): 將指定表讀取為數(shù)據(jù)框
最后感言：感謝自己大學(xué)時(shí)候看過(guò)幾本SQL語(yǔ)法書(shū)，雖然忘得差不多了。
