R：关系型数据库管理

R操作关系型数据库

基本要求： R语言基础，懂得一定SQL语法， 懂得使用搜索引擎
目标： 学会使用DBI操作SQLite数据库

简介

R本身不具备数据库操作能力，需要额外的扩展包—DBI(database interface）。DBI将数据库管理分为前端和后端。用户在前端使用对应的API, 经由DBI转换成相应的底层操作.
DBI分为三个部分：

• 数据库驱动， 负责与数据库进行交互，比如说与SQLite数据库交互要用到RSQLite，如果是MYSQL，则是RMYSQL

• 连接对象，建立与数据库的连接，前端操作经由该连接完成

• 结果， 操作的结果在这里显示

具体操作

首先加载R包

library(DBI) library(RSQLite)

第一步： 连接或创建数据库：dbConnect ，建立连接对象，后续的操作都基于该对象。

常用命令为：dbExecute和dbSendStatement， 前者适用于数据量不大时，后者适用数据量较大时

# 新建或连接已有的磁盘上数据库 con <- dbConnect(RSQLite::SQLite(), 'test.db') # 临时在内存中建立 con <- dbConnect(RSQLite::SQLite(), ":memory:")

参数1: 数据库驱动引擎(RSQLite::SQLite())；
参数2： 数据库连接参数(如user, password, host, port, dbname)，SQLite只需要提供数据库命名（不存在则新建），\:memory\: 则表示在仅保存在内存中.

第二步： 在R上实现数据库的常用操作： 增删改查

增：建立表并增加记录

# 导入已有的数据框, 参数为“连接对象， 表名，数据框” 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)') # 数据量过大，可以批次插入 rs <- dbSendStatement(con, 'INSERT INTO test (name, age) VALUES ("Tom", 25), ("Jelly",26)' ) dbHasCompleted(rs) # 判断操作是否完成 dbGetRowAffected(rs) # 返回影响的行 dbClearResult(rs) # 清空内存

查： 有两种方法, 一类获取所有数据，一类分批获取数据

# 方法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句柄中的内容 dbDisconnect(con)

删：  删除记录

# 删除记录前，要先查询 dbGetQuery(con, 'SELECT * FROM mtcar WHERE mpg > 30') # 删除记录 dbExecute(con, 'DELETE FROM mtcar WHERE mpg > 30') # 删除所有记录，谨慎操作 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') # 事后验证 dbGetQuery(con, 'SELECT * FROM mtcar WHERE mpg = 21.1')

一般而言，增查改删操作最好能够有反悔药。dbBegin(con)会记录一系列操作，直到你呈交修改，dbCommit(con)或 回滚修改dbRollback(con)

con <- dbConnect(RSQLite::SQLite(), ":memory:") dbWriteTable(con, "cash", data.frame(amount = 100)) # 测试dbCommit dbBegin(con) withdrawal <- 300 dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal)) dbCommit(con) # 测试回滚 dbBegin(con) withdrawl <- 5000 dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal)) ## 查看结果 dbGetQuery(con,'SELECT * FROM cash') ## 回滚 dbRollback(con) ## 检查结果 dbGetQuery(con,'SELECT * FROM cash')

所有操作完毕，结束连接dbDisconnect(con)

下面一些命令也比较常用，用法比较简单。

• dbWriteTable(连接对象, 表名, 数据框): 将数据框保存为表

• dbReadTable(连接对象, 表名 ): 读取指定表中所有内容

• dbListTable(连接对象)： 列出数据库中包含的表格

• dbListFields(连接对象, 表名)： 列出制定表格列名

• dbReadTable(连接对象, 表名): 将指定表读取为数据框

最后感言：感谢自己大学时候看过几本SQL语法书，虽然忘得差不多了。