R数据库操作详细图解

本文概述

• RMySQL包
• 在R和MySql之间创建连接
• R MySQL命令

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

R可以轻松地与许多关系数据库(如MySql, SQL Server, Oracle等)连接。当我们从这些数据库中提取信息时, 默认情况下, 信息是以数据帧的形式提取的。一次, 数据从数据库传到R环境。它将成为普通的R数据集。数据分析师可以借助所有强大的软件包和功能轻松地分析或处理数据。

RMySQL包

RMySQL软件包是R最重要的内置软件包之一。该软件包提供R和MySql数据库之间的本机连接。在R中, 要使用MySql数据库, 我们首先必须在熟悉的命令的帮助下安装RMySQL软件包, 如下所示：

install.packages("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子句选择

我们可以借助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()函数中传递适当的drop查询来从数据库中删除表。

例子

#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")

输出