R语言--操作MySQL数据库
❝用R语言做数据分析时，常常需要从多种数据源取数据，其中数据库是非常常见的数据源。用R操作MySQL数据库，可以说是数据分析师必备的技能了，本文介绍RMySQL包，可以在R语言中对数据库进行增删改查的操作。
❞
软件版本
-
win10 64bit -
r3.6.1 -
rstudio 1.2 -
RMySQL 0.10.20
安装包
install.packages('RMySQL')
创建连接
用dbConnect函数创建连接，驱动类型设置为MySQL()，用户名user、密码password、主机host、端口port、数据库dbname这些参数需要根据实际情况修改，其中端口port默认值是3306。
# 加载包
library(RMySQL)
# 创建连接
con <- dbConnect(MySQL(),
user="root",
password="xxx",
host="192.168.1.244",
port=3306,
dbname="test")
还可以通过一些命令，查看连接的相关信息。
# 连接概述
summary(con)
## <MySQLConnection:0,5>
## User: root
## Host: 192.168.1.244
## Dbname: test
## Connection type: 192.168.1.244 via TCP/IP
##
## Results:
# 连接详情
dbGetInfo(con)
## $host
## [1] "192.168.1.244"
##
## $user
## [1] "root"
##
## $dbname
## [1] "test"
##
## $conType
## [1] "192.168.1.244 via TCP/IP"
##
## $serverVersion
## [1] "5.7.23"
##
## $protocolVersion
## [1] 10
##
## $threadId
## [1] 465075
##
## $rsId
## list()
# 列出连接下所有表
dbListTables(con)
## [1] "city" "result"
## [3] "t_json" "tb_newChiller"
创建表
dbCreateTable函数创建表
# 方法1：字符向量
dbCreateTable(con,'table1',fields =c("id"='int',"value"="float"))
# 方法2：数据框
dbCreateTable(con,'table2',fields = mtcars)
注意点：
-
设置 fields参数时，用命名的字符向量（名字为字段名，值为字段数据类型）或数据框表示 -
表只能创建一次，重复创建会报错。
sql语句创建表
运行dbSendQuery函数，调用sql语句创建表,该函数返回一个RMySQLResult类。该方法可以重复运行，不会报错。
# 方法3：sql语句
dbSendQuery(con,'create table if not exists table3(id int,value float)')
## <MySQLResult:11,5,3>
读取表
dbReadTable函数读取表
# 先设置编码（windows）
dbSendQuery(con,'set names gbk')
## <MySQLResult:11,5,4>
# 读取完整表
dbReadTable(con,'city')
## id name city_code
## 1 1 北京 000001
## 2 4 南京 000002
## 3 7 广州 000003
## 4 10 上海 000004
## 5 11 深圳 000005
## 6 13 苏州 000006
注意点：
-
返回的是整个表的数据 -
为解决中文显示问题，若运行R的操作系统为window，需要设置编码为 gbk，为Linux则需要设置为utf8 -
返回的数据格式为data.frame
sql语句读取表
通过运行dbGetQuery或dbSendQuery函数，调用sql语句读取表。dbGetQuery函数运行sql语句，返回data.frame。可以在sql语句中编写筛选语句。
# 读取完整表
dbGetQuery(con,'select * from city')
## id name city_code
## 1 1 北京 000001
## 2 4 南京 000002
## 3 7 广州 000003
## 4 10 上海 000004
## 5 11 深圳 000005
## 6 13 苏州 000006
# 读取筛选的表
dbGetQuery(con,'select id,name from city where id > 10')
## id name
## 1 11 深圳
## 2 13 苏州
dbSendQuery函数运行sql语句，返回MySQLResult类。对该类使用dbFetch函数，可以筛选行，返回data.frame。用完后需要用dbClearResult函数清理结果。
# 筛选前3行数据
res <- dbSendQuery(con,'select * from city')
dbFetch(res,3)
## id name city_code
## 1 1 北京 000001
## 2 4 南京 000002
## 3 7 广州 000003
dbClearResult(res)
## [1] TRUE
写入表
dbWriteTable函数写入表
# 写入表
dbWriteTable(con,"iris",iris,row.names=FALSE,append=TRUE)
## [1] TRUE
注意点：
-
第一次写入时，若数据库中无该表，会根据表名自动创建 -
重复写入时，需要设置写入行为：
| 参数设置 | 解释 |
|---|---|
| append=TRUE | 追加写入，新数据在原数据行末添加 |
| overwrite=TRUE | 覆盖写入，新数据覆盖原数据 |
| row.names=FALSE | 不写入数据框的行名 |
-
append和overwrite两个参数不能同时设置为TRUE。
sql语句插入数据
运行dbSendStatment函数，调用sql语句插入数据。
dbSendStatement(con,'insert into table1 values(1,12)')
## <MySQLResult:1701147206,5,12>
注意点：
-
重复运行会重复插入数据
删除表
用dbRemoveTable函数删除表。
# 删除表
dbRemoveTable(con,'table1')
## [1] TRUE
dbRemoveTable(con,'table2')
## [1] TRUE
dbRemoveTable(con,'table3')
## [1] TRUE
dbRemoveTable(con,'iris')
## [1] TRUE
完成所有的操作后，需要关闭连接。
# 关闭连接
dbDisconnect(con)
## [1] TRUE