R and SQL

Copy from other's tutorial, wait to modify.

Introduction

What is SQL

SQL是什麼？SQL是一種為了關聯式資料庫所產生的語言，是一種用來查詢的語言，非常適合用於結構化的資料庫。因此可以知道SQL這個語言最重要的就是查詢，使用各種子句、運算式去做查詢。

NoSQL and SQL

NoSQL又可以稱為Not only SQL是在發展了SQL這種關聯式資料庫後，出現的一種反動。他主張不使用前面提到的SQL查詢，而既然不需使用SQL查詢，資料庫結構自然不需要是關聯式的，而是可以彈性的儲存資料。像是：Redis, MongoDB, HBase, Hive, Cassandra而NoSQL的儲存格式，可以用一個很直觀的想像就是JSON的格式。在python就像是一個list of dictionary。

Why MySQL?

在R裡面，因為我們大部人最常使用的格式是dataframe，他是一個很直觀的表格。而這種格式與MySql存進去的格式非常雷同，也因此你可以發現dplyr許多的function其實是有SQL的影子。(EX:join系列)

在開始使用SQL系列之前，如果你尋求的是輕量的開發，比較不需要server的，那你可以考慮使用SQLite，他會產生一個檔案，這個檔案就是你的DB，你可以單機的直接讀取這個DB存取檔案，別人同樣也可以透過這個檔案去開啟。但因為他沒有Server，因此用途就稍微侷限一些。為了直接使用比較多用途的DB，這邊我們要介紹的是MySQL。

Database

MySQL如同前面提到，同樣屬於是關聯式資料庫管理系統。在MySQL設計的IDE介面上，我們可以看到他有幾種要素。

  • Server Database：需要一個IP位置及電腦運作Server

  • Schema：概念跟Databse很像，用個網路上許多人的比喻，假設Database是一個大的儲藏室，Schema就像是不同的房間。因此一個Database可以有很多個Schema。

  • Table：Table顧名思義就是表格，也就是儲存資料的地方，具體樣式就像是R裡面的dataframe，而一個Schema裡面也可以有很多個table，table之間可以透過SQL的查詢指令來進行跨表查詢。

因此以一個MySQL結構，大致上就是這些東西，而每一個Sever Database，可以設定許多用戶及權限，要具有這些權限才能從Database裡面進行動作，例如新增、刪除、更新Schema、Table。

講解完MySQL的結構，我們來講講具體的操作：首先我們需要取得該MySQL的連線IP，另外我們需要一組帳號密碼。如此一來我們就可以使用該組帳號密碼進入Database去做該帳號權限能夠做的事情！而在開始透過其他程式語言(C、java、R、Python)去使用Mysql前，我們先來透過MySQL的管理介面Workbench來檢視一下。

  1. 到這個網址下載MySQL Workbench https://www.mysql.com/products/workbench/

  2. 安裝後開啟檢視檢視後應該會長這樣子，然後可以點選增加來加入你所使用的MySQL

R&MySQL

瞭解完MySQL的長相以及管理後，我們進入到最重要的R去連接MySQL這部分其實R的指令非常簡單，但背後其實許多還是需要瞭解MySQL的語法。因此這邊先上SQL語法給大家參考 。網路上其實滿多筆記的可以自己去查詢。簡單來說最基本的取法核心就是SELECT 、FROM、WHERE，配上許多條件式，或是不同的表格。首先我們需要先安裝核心的套件RMySQL，安裝完後，將其匯入程式的執行環境後就可以使用了。

# install.packages("RMySQL")
library(RMySQL)

我們可以打入db，下方跳出提示的function應該大部分都是該套件的function，可以先簡單檢視一下。首先最基礎的就是一定要連線，這部分套件也幫我們寫好了。

Connect to db

con1 <- dbConnect(RMySQL::MySQL(), 
                  dbname = "fortest", 
                  host = "100.100.100.100", 
                  user = "user", password = "password", 
                  client.flag=CLIENT_MULTI_RESULTS)

連線之後，後面我們就可以對它做各種事情了，而可以注意的是，前面的dbname，我們需要選取的是一個schema。進到這個schema後，這個con1的variable代表我們連線的狀況。

List all tables

接著我們可以做一些事情，例如看一下這個schema裡面有哪些table。這個指令只要打入連線的schema，可以看出這個schema有哪些table。

dbListTables(con1)

Create a table

那我們首先來試著透過R裡面的預設的Dataframe新增一個table。這個指令也非常的簡單，只需要輸入連線的狀況、表格名稱、要上傳的data.frame

dbWriteTable(con1, "p1", mtcars, row.names=F)
dbListTables(con1)
dbListFields(con1,"p1")

接著可以檢查一下是否成功新增！以及透過ListFields檢查有哪些欄位！

Delete tables

接著我們同樣可以將表格進行刪除，透過他幫妳寫好的dbRemoveTable，我們可以直接透過表格名稱去指定刪除。

dbRemoveTable(con1,"p1")
dbListTables(con1)

接著為了後續的運作，我們先將表格寫回去。基本的表格新增與刪除都知道了以後，我們還需要知道的是表格的構造，如同我們在R裡面會使用str()來看dataframe裡面的欄位屬性。在MySQL裡面欄位屬性同樣重要，不對的欄位屬性會沒辦法將資訊達到最佳的儲存。我們先使用簡單的例子來演示這個function大家可以看到我使用SELECT X AS X 前面會是value，而後面會是他的column name因此就可以看到 a 和 b 這兩個變數的性質。欄位屬性部分可以參考這個

res <- dbSendQuery(con1,"SELECT '1' AS a, 24 AS b")
dbColumnInfo(res)

接著我們來試著從MySQL上面要一下資料首先介紹一下最關鍵的function：dbSendQuery(）這個function讓我們可以透過R，使用SQL的語法對MySQL database提出query，不只是要資料，更改設定等等都要透過這個function喔！那我們就來使用一下萬惡的SELECT X FROM X

#* means all columns
res <- dbSendQuery(con1,"SELECT * FROM p1")
#-1 means all
test <- dbFetch(res,n=-1)

SELECT * FROM `TABLE NAME` 可以說是最經典的SQL語法。這邊的 *代表的意義就是取出所有的欄位。因此你可以用column name去取代 * 可以試著取出某些你想要的欄位

有設定Primary Key，跟沒有設定的差異？Insert into後 有設定的，會留下原本的，忽略後來的沒有設定的話就會直接疊下去

Try it，設定主Key後

mtcars1 <- cbind(mtcars,id=paste0("v",1:32))
dbWriteTable(con1, "p1", mtcars1, row.names=F)
dbSendQuery(con1, 'ALTER TABLE p1 CHANGE COLUMN `id` `id` VARCHAR(70) NULL DEFAULT NULL')
dbSendQuery(con1, 'ALTER TABLE p1 ADD PRIMARY KEY (id);')
vars1 <- names(mtcars1)
dbtable <- sprintf('%s',"p1")
dbtable <- dbQuoteIdentifier(con1,dbtable)
if(length(vars1) > 1) { 
  pastedvars1 <- paste("'", apply(mtcars1[, vars1], 1, paste, collapse="', '"), "'", sep="") 
} else { 
  pastedvars1 <- paste("'", mtcars1[, vars1], "'", sep="") 
} 

varlist <- paste(dbtable, "(", paste(names(mtcars1), collapse=", "), ")", sep="") 

datastring <- paste("(", paste(paste(pastedvars1, sep=", "), collapse="), ("), ")", sep="") 

toupdate <- paste(paste(vars1, "=VALUES(", vars1, ")", sep=""), collapse=",")        
sqlstring <- paste("INSERT INTO", varlist, "VALUES", datastring, "ON DUPLICATE KEY UPDATE", toupdate)

設定主Key後，再將一模一樣的data Insert，會發現DATA一樣是32個，重複的後來的會取代原本的，但因為都一樣所以就不會變。 如果不想讓後來取代原本的，可以把ON DUPLICATE KEY UPDATE拿掉！ 試試這個，我們將id為v1的data，以新的值ON DUPLICATE KEY UPDATE

sqlstring <- paste("INSERT INTO", varlist, "VALUES", "('22.0', '700000', '160.0', '110', '3.90', '2.620', '16.46', '0', '1', '4', '4', 'v1')", "ON DUPLICATE KEY UPDATE", toupdate) 
dbSendQuery(con1, sqlstring)

結果會發現v1值被後來這個取代囉！ 然後今天忘記跟大家說的，dbwritetable，如果你有指定append= TRUE，同樣也可以把東西往下貼喔！但就是不會有上面講到的那些效果。

Other Tips

今天有人提到的，writetable的問題，原則上因為dbwriteTable這個function不知道怎麼寫設定編碼的部分。我的建議是先在schema設定，直接設定成utf-8，這樣後續的table都會是utf-8接著dbwritetable，先寫出一個有欄位名稱但沒有列的dataframe，接著就透過上面insert的方式，這樣進去的東西就會是utf-8，下載下來的話windows記得sendquery之前先set成big5。可以參考我在fortest創立的al47 table，中文顯示就是正常！

Previous機器學習簡介NextAS#04 yt-comment visualization

Last updated

Was this helpful?