關聯式資料庫系統中的資料是以規範化格式儲存的。 所以，為了進行統計計算，我們需要非常高階和複雜的SQL查詢。但是R可以很容易地連線到許多關聯式資料庫，如：MySQL，Oracle，Sql Server等，並將它們作為資料影格提取。 當從資料庫中讀取資料到R環境中可用以後，它就成為一個正常的R資料集，可以使用所有強大的軟體包和函式進行操作或分析。
在本教學中，我們將使用R程式設計語言連線到MySQL資料庫。
R有一個名為RMySQL的內建包，它提供與MySql資料庫之間的本機連線。您可以使用以下命令在R環境中安裝此軟體包。
install.packages("RMySQL")
當安裝了軟體包(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表中，插入一行資料。
我們通過使用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資料庫中的表，將drop table語句傳遞到dbSendQuery()函式中，就像在SQL中查詢表中的資料一樣。
dbSendQuery(mysqlconnection, 'drop table if exists mtcars')
執行上述程式碼後，我們可以看到MySql資料庫中的mtcars表被刪除。