R 数据库

17 Mar 2025 | 5 分钟阅读

在关系数据库管理系统中，数据以规范化的格式存储。因此，要完成统计计算，我们需要非常高级和复杂的 SQL 查询。以表格形式存在的大量数据需要 SQL 查询来从中提取数据。

R可以轻松连接到许多关系数据库，例如 MySql、SQL Server、Oracle 等。当从这些数据库中提取信息时，默认情况下，信息以数据框的形式提取。一旦数据从数据库进入 R 环境；它将成为一个普通的 R 数据集。数据分析师可以借助所有强大的包和函数轻松分析或操作数据。

RMySQL 包

RMySQL 包是 R 中最重要的内置包之一。此包提供 R 和 MySql 数据库之间的原生连接。在 R 中，要使用 MySql 数据库，我们首先必须使用熟悉的命令安装 RMySQL 包，如下所示

当我们在 R 环境中运行上述命令时，它将开始下载 RMySQL 包。

输出

我们创建了一个数据库 employee，其中有一个表 employee_info，它有以下记录。

我们将在接下来的主题中使用我们上面提到的数据。

在 R 和 MySql 之间创建连接

要使用 MySql 数据库，需要在 R 和数据库之间创建连接对象。为了创建连接，R 提供了 dbConnect() 函数。此函数将用户名、密码、数据库名称和主机名作为输入参数。让我们看一个例子来理解如何使用 dbConnect() 函数连接到数据库。

示例

#Loading RMySQL package into R
library("RMySQL")

# Creating a connection Object to MySQL database.
# Conneting with database named "employee" which we have created befoe with the helpof XAMPP server.
mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
   host = 'localhost')

# Listing the tables available in this database.
 dbListTables(mysql_connect)

输出

R MySQL 命令

在 R 中，我们可以执行所有 SQL 命令，如插入、删除、更新等。为了对数据库执行查询，R 提供了 dbSendQuery() 函数。查询在 MySQL 中执行，并使用 R fetch () 函数返回结果集。最后，它作为数据框存储在 R 中。让我们看每个 SQL 命令的示例，以了解如何使用 dbSendQuery() 和 fetch() 函数。

创建表

R 提供了另一个函数来在数据库中创建表，即 dbWriteTable()。此函数在数据库中创建一个表；如果它不存在，则会覆盖该表。此函数将数据框作为输入。

示例

#Loading RMySQL package into R
library("RMySQL")

# Creating a connection Object to MySQL database.
# Conneting with database named "employee" which we have created befoe with the helpof XAMPP server.
mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
   host = 'localhost')

#Creating data frame to create a table 
emp.data<- data.frame(  
name = c("Raman","Rafia","Himanshu","jasmine","Yash"),  
salary = c(623.3,915.2,611.0,729.0,843.25),   
start_date = as.Date(c("2012-01-01", "2013-09-23", "2014-11-15", "2014-05-11","2015-03-27")),
dept = c("Operations","IT","HR","IT","Finance"),  
stringsAsFactors = FALSE  
)  

# All the rows of emp.data are taken inot MySql.
dbWriteTable(mysql_connect, "emp", emp.data[, ], overwrite = TRUE)

输出

选择

我们可以借助 fetch() 和 dbSendQuery() 函数简单地从表中选择记录。让我们看一个例子来理解查询是如何使用这两个函数工作的。

示例

#Loading RMySQL package into R
library("RMySQL")

# Creating a connection Object to MySQL database.
# Conneting with database named "employee" which we have created befoe with the helpof XAMPP server.
mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
   host = 'localhost')

# selecting the record from employee_info table.
record = dbSendQuery(mysql_connect, "select * from employee_info")

# Storing the result in a R data frame object. n = 6 is used to fetch first 6 rows.
data_frame = fetch(record, n = 6)
print(data_frame)

输出

带有 where 子句的 select 语句

我们可以借助 fetch() 和 dbSendQuery() 函数从表中选择特定的记录。让我们看一个例子来理解查询是如何使用 where 子句和这两个函数工作的。

示例

#Loading RMySQL package into R
library("RMySQL")

# Creating a connection Object to MySQL database.
# Conneting with database named "employee" which we have created befoe with the helpof XAMPP server.
mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
   host = 'localhost')

# selecting the specific record from employee_info table.
record = dbSendQuery(mysql_connect, "select * from employee_info where dept='IT'")

# Fetching all the records(with n = -1) and storing it as a data frame.
data_frame = fetch(record, n = -1)
print(data_frame)

输出

插入命令

我们可以借助熟悉的 dbSendQuery() 函数将数据插入表中。

示例

#Loading RMySQL package into R
library("RMySQL")

# Creating a connection Object to MySQL database.
# Conneting with database named "employee" which we have created befoe with the helpof XAMPP server.
mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
   host = 'localhost')

# Inserting record into employee_info table.
dbSendQuery(mysql_connect, "insert into employee_info values(9,'Preeti',1025,'8/25/2013','Operations')")

输出

更新命令

更新表中的记录要容易得多。为此，我们必须将更新查询传递给 dbSendQuery() 函数。

示例

#Loading RMySQL package into R
library("RMySQL")

# Creating a connection Object to MySQL database.
# Conneting with database named "employee" which we have created befoe with the helpof XAMPP server.
mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
   host = 'localhost')

# Updating the record in employee_info table.
dbSendQuery(mysql_connect, "update employee_info set dept='IT' where id=9")

输出

删除命令

下面是一个示例，我们通过将删除查询传递到 dbSendQuery() 函数中来从表中删除特定的行。

示例

#Loading RMySQL package into R
library("RMySQL")

# Creating a connection Object to MySQL database.
# Conneting with database named "employee" which we have created befoe with the helpof XAMPP server.
mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
   host = 'localhost')

# Deleting the specific record from employee_info table.
dbSendQuery(mysql_connect, "delete from employee_info where id=8")

输出

删除命令

下面是一个示例，我们通过将适当的删除查询传递到 dbSendQuery() 函数中来从数据库中删除一个表。

示例

#Loading RMySQL package into R
library("RMySQL")

# Creating a connection Object to MySQL database.
# Conneting with database named "employee" which we have created befoe with the helpof XAMPP server.
mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
   host = 'localhost')

# Dropping the specific table from the employee database.
dbSendQuery(mysql_connect, "drop table if exists emp")

输出