在 R 中连接 MySQL 数据库并执行 SQL 查询是非常常见的操作，特别是对于需要从数据库中提取或操作数据的分析任务。R 提供了多个包来实现与 MySQL 的连接，常用的包包括 RMariaDB 和 RMySQL。

1. RMariaDB 包

RMariaDB 包是用于连接 MariaDB（MySQL 的一个分支）和 MySQL 数据库的现代 R 包。它的性能和稳定性较好，且是当前推荐使用的包。

安装并加载 RMariaDB 包

# 安装 RMariaDB 包
install.packages("RMariaDB")

# 加载 RMariaDB 包
library(RMariaDB)

连接到 MySQL 数据库

# 使用 dbConnect() 函数连接 MySQL 数据库
con <- dbConnect(
  RMariaDB::MariaDB(),  # 选择 MariaDB 连接
  user = "your_username",  # 用户名
  password = "your_password",  # 密码
  dbname = "your_database",  # 数据库名称
  host = "localhost",  # 数据库主机（可以是 IP 地址）
  port = 3306  # 默认 MySQL 端口
)
  • user: 你的 MySQL 用户名
  • password: 你的 MySQL 密码
  • dbname: 你想连接的数据库名称
  • host: 数据库的主机名（localhost 表示本地数据库）
  • port: MySQL 的端口号（默认是 3306）

执行查询

# 执行 SQL 查询，返回结果
query_result <- dbGetQuery(con, "SELECT * FROM your_table")

# 查看查询结果
head(query_result)
  • dbGetQuery() 函数用于执行查询，并返回查询结果作为数据框。

断开连接

# 断开与数据库的连接
dbDisconnect(con)

2. RMySQL 包

RMySQL 包是另一个常用的连接 MySQL 数据库的包，它与 RMariaDB 包类似，但 RMariaDB 包在新版本中更为常用。

安装并加载 RMySQL 包

# 安装 RMySQL 包
install.packages("RMySQL")

# 加载 RMySQL 包
library(RMySQL)

连接到 MySQL 数据库

# 使用 dbConnect() 函数连接 MySQL 数据库
con <- dbConnect(
  RMySQL::MySQL(),  # 选择 MySQL 连接
  user = "your_username",  # 用户名
  password = "your_password",  # 密码
  dbname = "your_database",  # 数据库名称
  host = "localhost",  # 数据库主机
  port = 3306  # 默认端口
)

执行查询

# 执行 SQL 查询并获取结果
query_result <- dbGetQuery(con, "SELECT * FROM your_table")

# 查看结果
head(query_result)

断开连接

# 断开与数据库的连接
dbDisconnect(con)

✅ 三、常见操作

1. 列出所有表

使用 dbListTables() 函数列出当前数据库中的所有表。

# 列出所有表
tables <- dbListTables(con)

# 查看表名
tables

2. 查看表结构

使用 dbListFields() 函数查看某个表的字段（列）。

# 查看表的字段
fields <- dbListFields(con, "your_table")

# 查看字段名
fields

3. 插入数据

你可以使用 dbWriteTable() 函数将数据框写入 MySQL 数据库中的表。

# 创建一个数据框
data_to_insert <- data.frame(
  column1 = c("A", "B", "C"),
  column2 = c(1, 2, 3)
)

# 将数据框写入数据库
dbWriteTable(con, "your_table", data_to_insert, append = TRUE, row.names = FALSE)
  • append = TRUE 表示将数据附加到现有表中，而不是覆盖表。
  • row.names = FALSE 表示不将行名写入表中。

4. 更新数据

你可以使用 SQL 语句来更新数据库中的数据：

# 执行 UPDATE SQL 查询
dbExecute(con, "UPDATE your_table SET column1 = 'New Value' WHERE column2 = 1")

5. 删除数据

使用 SQL 语句删除数据：

# 执行 DELETE SQL 查询
dbExecute(con, "DELETE FROM your_table WHERE column2 = 3")

🧪 四、小练习题

  1. 使用 RMariaDB 包连接到 MySQL 数据库并查询表 employees 中所有的员工信息。
  2. 创建一个数据框，包含学生姓名、年龄、分数，并将其插入到名为 students 的数据库表中。
  3. 使用 SQL 查询从表 products 中筛选出价格大于 100 的所有产品。

总结

操作示例
连接数据库 (RMariaDB 包)con <- dbConnect(RMariaDB::MariaDB(), user = "user", password = "password", dbname = "db_name")
执行 SQL 查询query_result <- dbGetQuery(con, "SELECT * FROM table_name")
插入数据dbWriteTable(con, "table_name", data_frame, append = TRUE)
更新数据dbExecute(con, "UPDATE table_name SET column = 'new_value' WHERE condition")
删除数据dbExecute(con, "DELETE FROM table_name WHERE condition")
断开连接dbDisconnect(con)

通过 RMariaDB 或 RMySQL 包，R 提供了与 MySQL 数据库进行交互的功能，包括查询、插入、更新和删除数据。希望这些信息能帮助你顺利与 MySQL 数据库进行连接并进行操作。如果你有任何问题，随时告诉我！😊