R语言学习笔记——RMySQL基本操作

来源：互联网发布：指纹打卡机考勤软件编辑：程序博客网时间：2024/05/16 07:59

# 建表并插入数据> 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           c1   1 a  0.988681642   2 b -0.669357703   3 c  0.277036384   4 d  1.361371565   5 e -0.702910176   6 f  1.612350887   7 g  0.176160688   8 h  0.297000179   9 i  0.1903271910 10 j -0.06222173# 插入新数据> dbWriteTable(conn, "t_demo", t_demo, append=TRUE)> dbReadTable(conn, "t_demo")   row_names  a b           c1          1  1 a  0.988681642          2  2 b -0.669357703          3  3 c  0.277036384          4  4 d  1.361371565          5  5 e -0.702910176          6  6 f  1.612350887          7  7 g  0.176160688          8  8 h  0.297000179          9  9 i  0.1903271910        10 10 j -0.0622217311         1  1 a  0.9886816412         2  2 b -0.6693577013         3  3 c  0.2770363814         4  4 d  1.3613715615         5  5 e -0.7029101716         6  6 f  1.6123508817         7  7 g  0.1761606818         8  8 h  0.2970001719         9  9 i  0.1903271920        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         c1         1 1 a 0.98868162         3 3 c 0.27703643         4 4 d 1.36137164         6 6 f 1.61235095         7 7 g 0.17616076         8 8 h 0.29700027         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         c1         1 1 a 0.98868162         3 3 c 0.27703643         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         c1 1 a 0.98868162 3 c 0.27703643 4 d 1.36137164 6 f 1.61235095 7 g 0.17616076 8 h 0.29700027 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_rmysql1           t_demo2           t_user> mysqlCloseResult(query)[1] TRUE

中文问题解决：

mysql> select * from t_user;+----+-----------+| id | user      |+----+-----------+|  1 | A1        ||  2 | B1        ||  3 | fens.me   ||  5 | 你好      ||  4 | 小朋友    ||  6 | 正确了    |+----+-----------+

在R中选择

> users <- dbGetQuery(conn, "select * from t_user");> users  id    user1  1      A12  2      B13  3 fens.me4  5      ??5  4     ???6  6     ???

解决办法：

> conn <- dbConnect(m, user = "root", password = "144230", dbname = "test", host="localhost.localdomain")> dbSendQuery(conn,'SET NAMES gbk')<MySQLResult:(9246,3,0)> > users <- dbGetQuery(conn, "select * from t_user");> users  id    user1  1      A12  2      B13  3 fens.me4  5      ??5  4     ???6  6     ???> query <- dbSendQuery(conn, "SELECT * FROM t_user")> data <- fetch(query, n = -1)> mysqlCloseResult(query)[1] TRUE> data  id               user1  1                 A12  2                 B13  3            fens.me4  5   \xc4\xe3\xba\xc35  4 С\xc5\xf3\xd3\xd16  6    \xd5\xfdȷ\xc1\xcb

0 0