R語言連接數據庫（MySQL)

關係數據庫系統中的數據是以規範化格式存儲的。所以，爲了進行統計計算，我們需要非常高級和複雜的SQL查詢。但是R可以很容易地連接到許多關係數據庫，如：MySQL，Oracle，Sql Server等，並將它們作爲數據幀提取。當從數據庫中讀取數據到R環境中可用以後，它就成爲一個正常的R數據集，可以使用所有強大的軟件包和函數進行操作或分析。

在本教程中，我們將使用R編程語言連接到MySQL數據庫。

RMySQL包

R有一個名爲RMySQL的內置包，它提供與MySql數據庫之間的本機連接。您可以使用以下命令在R環境中安裝此軟件包。

install.packages("RMySQL")

將R連接到MySql

當安裝了軟件包(RMySQL)之後，我們在R中創建一個連接對象以連接到數據庫。它需要用戶名，密碼，數據庫名稱和主機名等數據庫連接所需要的信息。

library("RMySQL");
# Create a connection Object to MySQL database.
# We will connect to the sampel database named "testdb" that comes with MySql installation.
mysqlconnection = dbConnect(MySQL(), user = 'root', password = '123456', dbname = 'testdb',
   host = 'localhost')

# List the tables available in this database.
dbListTables(mysqlconnection)

當我們執行上述代碼時，會產生以下結果(當前數據中的所有表) -

 [1] "articles"       "contacts"       "demos"          "divisions"     
 [5] "items"          "luxuryitems"    "order"          "persons"       
 [9] "posts"          "revenues"       "special_isnull" "t"             
[13] "tbl"            "tmp"            "v1"             "vparts"

查詢表

可以使用dbSendQuery()函數查詢MySQL中的數據庫表。該查詢在MySql中執行，並使用R 的fetch()函數返回結果集,最後將此結果作爲數據幀存儲在R中。

假設要查詢的表是：persons，其創建語句和數據如下 -

/*
Navicat MySQL Data Transfer

Source Server         : localhost-57
Source Server Version : 50709
Source Host           : localhost:3306
Source Database       : testdb

Target Server Type    : MYSQL
Target Server Version : 50709
File Encoding         : 65001

Date: 2017-08-24 00:35:17
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `persons`
-- ----------------------------
DROP TABLE IF EXISTS `persons`;
CREATE TABLE `persons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `full_name` varchar(255) NOT NULL,
  `date_of_birth` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of persons
-- ----------------------------
INSERT INTO `persons` VALUES ('1', 'John Doe', '1990-01-01');
INSERT INTO `persons` VALUES ('2', 'David Taylor', '1989-06-06');
INSERT INTO `persons` VALUES ('3', 'Peter Drucker', '1988-03-02');
INSERT INTO `persons` VALUES ('4', 'Lily Minsu', '1992-05-05');
INSERT INTO `persons` VALUES ('5', 'Mary William', '1995-12-01');

將上述表導入到數據庫中，並創建以下R代碼，用來執行從數據庫的表中查詢數據 -

library("RMySQL");
# Create a connection Object to MySQL database.
# We will connect to the sampel database named "testdb" that comes with MySql installation.
mysqlconnection = dbConnect(MySQL(), user = 'root', password = '123456', dbname = 'testdb',
   host = 'localhost');
# Query the "actor" tables to get all the rows.
result = dbSendQuery(mysqlconnection, "select * from persons")

# Store the result in a R data frame object. n = 5 is used to fetch first 5 rows.
data.frame = fetch(result, n = 5)
print(data.frame)

執行上面示例代碼，得到以下結果 -

  id     full_name date_of_birth
1  1      John Doe    1990-01-01
2  2  David Taylor    1989-06-06
3  3 Peter Drucker    1988-03-02
4  4    Lily Minsu    1992-05-05
5  5  Mary William    1995-12-01

使用過濾子句查詢

我們可以傳遞任何有效的選擇查詢來獲取結果，如下代碼所示 -

library("RMySQL");
# Create a connection Object to MySQL database.
# We will connect to the sampel database named "testdb" that comes with MySql installation.
mysqlconnection = dbConnect(MySQL(), user = 'root', password = '123456', dbname = 'testdb',
   host = 'localhost');
result = dbSendQuery(mysqlconnection, "select * from persons where date_of_birth = '1990-01-01'")

# Fetch all the records(with n = -1) and store it as a data frame.
data.frame = fetch(result, n = -1)
print(data.frame)

當我們執行上述代碼時，會產生以下結果 -

  id full_name date_of_birth
1  1  John Doe    1990-01-01

更新表中的行記錄

可以通過將更新查詢傳遞給dbSendQuery()函數來更新MySQL表中的行。

dbSendQuery(mysqlconnection, "update persons set date_of_birth = '1999-01-01' where id=3")

執行上述代碼後，可以看到在MySql已經更新persons表中對應的記錄。

將數據插入到表中

參考以下代碼實現 -

library("RMySQL");
# Create a connection Object to MySQL database.
# We will connect to the sampel database named "testdb" that comes with MySql installation.
mysqlconnection = dbConnect(MySQL(), user = 'root', password = '123456', dbname = 'testdb',
   host = 'localhost');
dbSendQuery(mysqlconnection,
   "insert into persons(full_name, date_of_birth) values ('Maxsu', '1992-01-01')"
)

執行上述代碼後，可以看到向MySql的persons表中，插入一行數據。

在MySql中創建表

我們通過使用dbWriteTable()函數向MySql中創建表。它會覆蓋表，如果它已經存在並且以數據幀爲輸入。

library("RMySQL");
# Create the connection object to the testdb database where we want to create the table.
mysqlconnection = dbConnect(MySQL(), user = 'root', password = '123456', dbname = 'testdb',host = 'localhost')

# Use the R data frame "mtcars" to create the table in MySql.
# All the rows of mtcars are taken inot MySql.
dbWriteTable(mysqlconnection, "mtcars", mtcars[, ], overwrite = TRUE)

執行上述代碼後，我們可以看到在MySql數據庫中創建一個名稱爲：mtcars的表，並有填充了一些數據。

在MySql中刪除表

我們可以刪除MySql數據庫中的表，將drop table語句傳遞到dbSendQuery()函數中，就像在SQL中查詢表中的數據一樣。

dbSendQuery(mysqlconnection, 'drop table if exists mtcars')

執行上述代碼後，我們可以看到MySql數據庫中的mtcars表被刪除。

R語言教程

R數據接口

R圖表

R統計實例