關聯式資料庫系統中的資料是以規範化格式儲存的。 所以，為了進行統計計算，我們需要非常高階和複雜的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`-- ----------------------------DROPTABLEIFEXISTS`persons`;CREATETABLE`persons` ( `id`int(11) NOTNULL AUTO_INCREMENT, `full_name`varchar(255) NOTNULL, `date_of_birth`dateNOTNULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6DEFAULTCHARSET=utf8;-- ------------------------------ Records of persons-- ----------------------------INSERTINTO`persons`VALUES (`1`, `John Doe`, `1990-01-01`);INSERTINTO`persons`VALUES (`2`, `David Taylor`, `1989-06-06`);INSERTINTO`persons`VALUES (`3`, `Peter Drucker`, `1988-03-02`);INSERTINTO`persons`VALUES (`4`, `Lily Minsu`, `1992-05-05`);INSERTINTO`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表被刪除。