转自:http://blog.fens.me/r-mysql-rmysql/
1). RMySQL辅助操作
加载类库
> library(RMySQL)
建立本地连接
> conn <- dbConnect(MySQL(), dbname = "rmysql", username="rmysql", password="rmysql",client.flag=CLIENT_MULTI_STATEMENTS)
建立远程连接
> conn <- dbConnect(MySQL(), dbname = "rmysql", username="rmysql", password="rmysql",host="192.168.1.201",port=3306)
关闭连接
dbDisconnect(conn)
查看数据库的表
> dbListTables(conn)
[1] "t_user"
查看表的字段
> dbListFields(conn, "t_user")
[1] "id" "user"
查询MySQL信息
> summary(MySQL(), verbose = TRUE)
<MySQLDriver:(23864)>
Driver name: MySQL
Max connections: 16
Conn. processed: 3
Default records per fetch: 500
DBI API version:
# MySQL连接实例信息
> summary(conn, verbose = TRUE)
<MySQLConnection:(23864,2)>
User: root
Host: localhost
Dbname: rmysql
Connection type: localhost via TCP/IP
MySQL server version: 5.6.11
MySQL client version: 5.6.11
MySQL protocol version: 10
MySQL server thread id: 35
No resultSet available
# MySQL连接信息
> dbListConnections(MySQL())
[[1]]
<MySQLConnection:(23864,2)>
2). RMySQL数据库操作
# 建表并插入数据
> t_demo<-data.frame(
a=seq(1:10),
b=letters[1:10],
c=rnorm(10)
)
> dbWriteTable(conn, "t_demo", t_demo)
# 获得整个表数据
> dbReadTable(conn, "t_demo")
a b c
1 1 a 0.98868164
2 2 b -0.66935770
3 3 c 0.27703638
4 4 d 1.36137156
5 5 e -0.70291017
6 6 f 1.61235088
7 7 g 0.17616068
8 8 h 0.29700017
9 9 i 0.19032719
10 10 j -0.06222173
# 插入新数据
> dbWriteTable(conn, "t_demo", t_demo, append=TRUE)
> dbReadTable(conn, "t_demo")
row_names a b c
1 1 1 a 0.98868164
2 2 2 b -0.66935770
3 3 3 c 0.27703638
4 4 4 d 1.36137156
5 5 5 e -0.70291017
6 6 6 f 1.61235088
7 7 7 g 0.17616068
8 8 8 h 0.29700017
9 9 9 i 0.19032719
10 10 10 j -0.06222173
11 1 1 a 0.98868164
12 2 2 b -0.66935770
13 3 3 c 0.27703638
14 4 4 d 1.36137156
15 5 5 e -0.70291017
16 6 6 f 1.61235088
17 7 7 g 0.17616068
18 8 8 h 0.29700017
19 9 9 i 0.19032719
20 10 10 j -0.06222173
# 覆盖原表数据
> dbWriteTable(conn, "t_demo", t_demo, overwrite=TRUE)
# 1). 查询数据
> d0 <- dbGetQuery(conn, "SELECT * FROM t_demo where c>0")
> class(d0)
[1] "data.frame"
> d0
row_names a b c
1 1 1 a 0.9886816
2 3 3 c 0.2770364
3 4 4 d 1.3613716
4 6 6 f 1.6123509
5 7 7 g 0.1761607
6 8 8 h 0.2970002
7 9 9 i 0.1903272
# 2). 执行SQL脚本查询,并分页
> rs <- dbSendQuery(conn, "SELECT * FROM t_demo where c>0")
> class(rs)
[1] "MySQLResult"
attr(,"package")
[1] "RMySQL"
> mysqlCloseResult(rs)
[1] TRUE
> d1 <- fetch(rs, n = 3)
> d1
row_names a b c
1 1 1 a 0.9886816
2 3 3 c 0.2770364
3 4 4 d 1.3613716
# 3). 查看集统计信息
> summary(rs, verbose = TRUE)
row_names a b c
Length:7 Min. :1.000 Length:7 Min. :0.1762
Class :character 1st Qu.:3.500 Class :character 1st Qu.:0.2337
Mode :character Median :6.000 Mode :character Median :0.2970
Mean :5.429 Mean :0.7004
3rd Qu.:7.500 3rd Qu.:1.1750
Max. :9.000 Max. :1.6124
# 不插入row.names字段
> dbWriteTable(conn, "t_demo", t_demo,row.names=FALSE,overwrite=TRUE)
> dbGetQuery(conn, "SELECT * FROM t_demo where c>0")
a b c
1 1 a 0.9886816
2 3 c 0.2770364
3 4 d 1.3613716
4 6 f 1.6123509
5 7 g 0.1761607
6 8 h 0.2970002
7 9 i 0.1903272
# 删除表
> if(dbExistsTable(conn,'t_demo')){
+ dbRemoveTable(conn, "t_demo")
+ }
[1] TRUE
执行SQL语句,dbSendQuery
> query<-dbSendQuery(conn, "show tables")
> data <- fetch(query, n = -1)
> data
Tables_in_rmysql
1 t_demo
2 t_user
> mysqlCloseResult(query)
[1] TRUE
4). win的字符集设置
在win7中,向MySQL插入中文
mysql> INSERT INTO t_user(user) values('小朋友'),('你好'),('正确了');
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t_user;
+----+---------+
| id | user |
+----+---------+
| 1 | A1 |
| 2 | AB |
| 3 | fens.me |
| 5 | 你好 |
| 4 | 小朋友 |
| 6 | 正确了 |
+----+---------+
6 rows in set (0.07 sec)
通过RMySQL查询
> dbGetQuery(conn, "SELECT * FROM t_user")
id user
1 1 A1
2 2 AB
3 3 fens.me
4 5 ??
5 4 ???
6 6 ???
设置GKB字符集
> dbDisconnect(conn)
> conn <- dbConnect(MySQL(), dbname = "rmysql", username="root", password="",client.flag=CLIENT_MULTI_STATEMENTS)
> dbSendQuery(conn,'SET NAMES gbk')
> query<-dbSendQuery(conn, "SELECT * FROM t_user")
> data <- fetch(query, n = -1)
> mysqlCloseResult(query)
[1] TRUE
> data
id user
1 1 A1
2 2 AB
3 3 fens.me
4 5 你好
5 4 小朋友
6 6 正确了