R - 資料庫

---

---

關係資料庫系統中的資料以規範化的格式儲存。因此，為了進行統計計算，我們需要非常高階和複雜的 SQL 查詢。但是 R 可以輕鬆連線到許多關係資料庫，例如 MySql、Oracle、Sql server 等，並將其中的記錄作為資料框提取。一旦資料在 R 環境中可用，它就成為一個普通的 R 資料集，可以使用所有強大的包和函式對其進行操作或分析。

在本教程中，我們將使用 MySql 作為連線到 R 的參考資料庫。

RMySQL 包

R 有一個名為“RMySQL”的內建包，它提供與 MySql 資料庫的原生連線。您可以使用以下命令在 R 環境中安裝此包。

install.packages("RMySQL")

將 R 連線到 MySql

安裝包後，我們在 R 中建立一個連線物件以連線到資料庫。它以使用者名稱、密碼、資料庫名稱和主機名作為輸入。

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

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

執行上述程式碼時，會產生以下結果：

 [1] "actor"                      "actor_info"                
 [3] "address"                    "category"                  
 [5] "city"                       "country"                   
 [7] "customer"                   "customer_list"             
 [9] "film"                       "film_actor"                
[11] "film_category"              "film_list"                 
[13] "film_text"                  "inventory"                 
[15] "language"                   "nicer_but_slower_film_list"
[17] "payment"                    "rental"                    
[19] "sales_by_film_category"     "sales_by_store"            
[21] "staff"                      "staff_list"                
[23] "store"                     

查詢表

我們可以使用函式dbSendQuery()查詢 MySql 中的資料庫表。查詢在 MySql 中執行，結果集使用 R 的fetch()函式返回。最後將其儲存為 R 中的資料框。

# Query the "actor" tables to get all the rows.
result = dbSendQuery(mysqlconnection, "select * from actor")

# 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.fame)

執行上述程式碼時，會產生以下結果：

        actor_id   first_name    last_name         last_update
1        1         PENELOPE      GUINESS           2006-02-15 04:34:33
2        2         NICK          WAHLBERG          2006-02-15 04:34:33
3        3         ED            CHASE             2006-02-15 04:34:33
4        4         JENNIFER      DAVIS             2006-02-15 04:34:33
5        5         JOHNNY        LOLLOBRIGIDA      2006-02-15 04:34:33

帶過濾子句的查詢

我們可以傳遞任何有效的 select 查詢來獲取結果。

result = dbSendQuery(mysqlconnection, "select * from actor where last_name = 'TORN'")

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

執行上述程式碼時，會產生以下結果：

        actor_id    first_name     last_name         last_update
1        18         DAN            TORN              2006-02-15 04:34:33
2        94         KENNETH        TORN              2006-02-15 04:34:33
3       102         WALTER         TORN              2006-02-15 04:34:33

更新表中的行

我們可以透過將 update 查詢傳遞給 dbSendQuery() 函式來更新 MySql 表中的行。

dbSendQuery(mysqlconnection, "update mtcars set disp = 168.5 where hp = 110")

執行上述程式碼後，我們可以看到 MySql 環境中已更新的表。

將資料插入表中

dbSendQuery(mysqlconnection,
   "insert into mtcars(row_names, mpg, cyl, disp, hp, drat, wt, qsec, vs, am, gear, carb)
   values('New Mazda RX4 Wag', 21, 6, 168.5, 110, 3.9, 2.875, 17.02, 0, 1, 4, 4)"
)

執行上述程式碼後，我們可以看到 MySql 環境中已插入表中的行。

在 MySql 中建立表

我們可以使用函式dbWriteTable()在 MySql 中建立表。如果表已存在，則會覆蓋該表，並以資料框作為輸入。

# Create the connection object to the database where we want to create the table.
mysqlconnection = dbConnect(MySQL(), user = 'root', password = '', dbname = 'sakila', 
   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 環境中已建立的表。

刪除 MySql 中的表

我們可以透過將 drop table 語句傳遞給 dbSendQuery()（與我們用於從表中查詢資料的方式相同）來刪除 MySql 資料庫中的表。

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

執行上述程式碼後，我們可以看到 MySql 環境中已刪除的表。