MySQL入門及其與R的交互
這篇文章同時發布與本人的個人博客:SQL及關係型資料庫入門,編輯排版更適宜閱讀.
轉載請註明出處.
***
## 什麼是關係型資料庫及資料庫管理系統
資料庫(Database)是一種數據的組織和存儲方式，通常用於處理結構化的數據。
而關係型資料庫(Relational Database)指的是創建在關係模型的基礎上的資料庫。它藉助於集合代數等數學概念和方法來處理資料庫中的數據。
資料庫管理系統(DBMS,*Database Management System*),是一種專門用於存儲、修改並從資料庫提取信息的系統軟體。
主流的關係型資料庫管理系統主要有：MySQL，ORACLE, MS ACCESS，DB2等。
其中，MySQL屬於開源軟體，而其他的主流資料庫管理系統基本都是商業軟體。由於很多互聯網公司資料庫都是採用MySQL進行資料庫的管理，所以今天我們主要介紹MySQL的安裝、配置及其與R的交互。
## 什麼是SQL語言
- SQL(Structured Query Language)是一種專門用來與資料庫進行溝通的語言。
- 使用SQL可以對資料庫中的數據進行增、刪、查、改、許可權管理等操作。
- 常用關鍵詞：`SELECT`,`UPDATE`,`DELETE`,`INSERT`.
- 管理關鍵詞：`CREATE`,`ALTER`,`DROP`
- 常用子句、關鍵詞：`FROM`,`WHERE`,`GROUP BY`,`ORDER BY`
## 為什麼要學習SQL
很多同學可能會很疑惑：對於數據的增刪查改等需求，我們的R不是已經有非常方便的操作了嗎？為什麼還要多學一門語言呢？
R擅長的領域是數據分析，然而，對於數據存儲，卻存在很大的問題。一個非常明顯的缺陷是：**所有數據均要讀入內存**。這就造成了R能處理的數據量存在一個瓶頸。當我們要處理的數據觀測數達到上億級別的時候，R就顯得力所不逮了。
資料庫能解決的當然不止這一個問題。
當我們遇到如下情景時，資料庫就顯得非常重要了：
- 當你的數據需要通過網站在線展示；
- 當你在一個團隊中工作，你和你的協作成員要同時操作同一個數據集；
- 當你需要為不同的數據用戶賦予不同的使用許可權；
- 當你要處理的數據量超過了你的電腦內存；
- 當你面對的數據集非常複雜，不能統一組織到一個數據集中時；
- 當你的數據量非常龐大，但你又經常要頻繁地獲取它的一些子集時；
- 當你的幾個數據集關聯性很大，更新一個數據集需要同時更新另外一些數據集時；
- 當你對數據的格式要求很嚴格時。
而如果我們經常與數據打交道，以上的問題是必不可免的。
可見，要想成為一名優秀的數據科學家，學習SQL還是非常有必要的。
當然，SQL雖然是一門語言，但是它有一些非常吸引人的優點：
- 幾乎所有重要的DBMS都支持SQL；
- SQL語法簡明，簡單易學；
- SQL非常靈活，功能強大。
所以，雖然又得多學一門語言，但是也不必苦惱。想想能夠幾天掌握一門新的語言，也是挺讓人激動的呢:)
## MySQL的安裝及環境配置
MySQL是一款開軟的資料庫管理系統，因此我們可以通過在官網進行軟體的自由下載安裝。
對於入門的同學來說，MySQL Community Server和MySQL Workbench CE結合起來使用是一個不錯的開始。MySQL Workbench CE是MySQL的一個開發環境，具有非常友好的交互界面。它跟MySQL的關係如同Rstudio和R的關係。
### 下載地址
- [Download MySQL Installer](MySQL :: Download MySQL Installer)
- [Download MySQL Workbench](MySQL :: Download MySQL Workbench)
### 安裝配置
MySQL的安裝配置非常簡單，一路NEXT就好。如果實在是遇到麻煩，可以用搜索引擎搜索一下安裝配置的方法,當然，官網上也有非常詳細的安裝及使用文檔：<MySQL :: MySQL Workbench Manual>.
## SQL基本操作——案例學習
安裝完畢，我們就可以啟動MySQL Workbench進行資料庫的創建等操作了。先使用root用戶身份（在安裝的過程中創建）進入管理界面。
### 建立一個資料庫
新建一個SQL腳本，即可以開始MySQL的編程了。選中某一個代碼塊，使用`CTRL+ENTER`快捷鍵即可運行代碼。
```
create database db1;
show databases;
-- 創建一個普通用戶
CREATE USER yy@localhost IDENTIFIED BY "123";
```
### 建立一個表格
```
use db1;
create table birthdays(
nameid INTEGER NOT NULL AUTO_INCREMENT,
firstname varchar(100) not null,
lastname varchar(100) not null,
birthday date,
primary key (nameid)
);
```
### 添加觀測數據
```
insert into birthdays(firstname,lastname,birthday)
values ("peter","Pascal","1991-02-01"),
("paul","panini","1992-03-02");
```
### 使用查詢語句
```
select * from birthdays;
select birthday from birthdays;
```
### 追加數據
```
insert into birthdays(nameid,firstname,lastname,birthday)
values (10,"Donald","Docker","1934-06-09");
```
## SQL與R的交互
R與SQL交互的拓展包非常豐富，不過大致可以分為三大類：
1. 依賴於`DBI`package，如`RMySQL`,`ROracle`,`RPosttgreSQL`,`RSQLite`。這種方式通過與DBMS建立原始的連接實現資料庫操作。
2. 依賴於`RODBC`package。這個包通過打開資料庫連接驅動的方式建立非直接的連接。如通過依賴於jre讀入XLS/XLSX表格的數據。
3. 通過`dplyr` package.
今天主要介紹第1種及第三種方式。
### R連接MySQL
#### 操作資料庫中的數據
下面，我們通過R來操作前面在MySQL中建立的資料庫`db1`。
```r
library(RMySQL)
# 建立一個連接
mydb <- dbConnect(MySQL(),user="root",
password = "mycode",
dbname = "db1")
#查看錶格
dbListTables(mydb)
```
```
## [1] "birthdays" "mtcars" "test"
```
```r
#查看某一列
dbListFields(mydb,"birthdays")
```
```
## [1] "nameid" "firstname" "lastname" "birthday"
```
```r
#
#dbClearResult(dbListResults(mydb)[[1]])
# 建立一個查詢
rs <- dbSendQuery(mydb,"select * from birthdays")
data<-fetch(rs,n = -1)
head(data)
```
```
## nameid firstname lastname birthday
## 1 1 peter Pascal 1991-02-01
## 2 2 paul panini 1992-03-02
## 3 10 Donald Docker 1934-06-09
```
```r
# 另一種方法：建立一個查詢
dbGetQuery(mydb,"select * from birthdays")
```
```
## nameid firstname lastname birthday
## 1 1 peter Pascal 1991-02-01
## 2 2 paul panini 1992-03-02
## 3 10 Donald Docker 1934-06-09
```
#### 將R中的`data.frame`存儲到資料庫
```r
#將一個data frame對象存儲為一個表格
dbWriteTable(mydb,name = "mtcars",value = mtcars,overwrite=TRUE)
```
```
## [1] TRUE
```
```r
#查看結果
dbListTables(mydb)
```
```
## [1] "birthdays" "mtcars" "test"
```
### 使用dplyr進行資料庫操作
`dplyr`是Hadley大神開發的一個專註於data frame類型的數據操作的一個包。它擁有非常簡潔、便於記憶、異常豐富的一系列操作函數。更吸引人的是：它支持對sqlite,mysql,postgresql等開源資料庫的操作。也就是說：你無需掌握SQL語言也能輕鬆進行資料庫操作。
當然，dplyr並不能替代全部的SQL語言。它主要用於產生分析中最頻繁使用的`SELECT`語句。
下面我們看看這是如何做到的。
```r
library(dplyr)
conDplyr<-src_mysql(dbname = "db1",user = "root",password = "mycode",host = "localhost")
mydata<-conDplyr %>%
tbl("mtcars") %>%
select(mpg,cyl,gear) %>%
filter(gear == 4) %>%
collect()
head(mydata)
```
```
## Source: local data frame [6 x 3]
##
## mpg cyl gear
## (dbl) (dbl) (dbl)
## 1 21.0 6 4
## 2 21.0 6 4
## 3 22.8 4 4
## 4 24.4 4 4
## 5 22.8 4 4
## 6 19.2 6 4
```
### dplyr中的惰性求值
- dplyr只有在必要的情況下才會執行操作
- 它在必要的情況下才會從資料庫中載入數據
- 每一個操作函數在執行的時候，並未開始真正從資料庫中請求，而是在必要的情況下，一起執行.
如以下的一系列操作並未開始執行數據提取：
```r
library(dplyr)
myDF <- tbl(conDplyr,"mtcars")
myDF1<-filter(myDF,gear == 4)
myDF2<-select(myDF1,mpg,cyl,gear)
```
直到執行以下語句，才真正開始從資料庫中提取數據。
```r
head(myDF2)
```
```
## mpg cyl gear
## 1 21.0 6 4
## 2 21.0 6 4
## 3 22.8 4 4
## 4 24.4 4 4
## 5 22.8 4 4
## 6 19.2 6 4
```
## MySQL深入學習
- 快速入門：『SQL必知必會』。這本書非常簡明概要，可以一口氣看完。
- 從入門到精通：『MySQL高效編程』。這本書涵蓋了非常豐富的學習案例。
## 參考文獻
- 『Automated Data Collection with R』第7章
- [Exploring data from database: MySQL, R and dplyr](Exploring data from database: MySQL, R and dplyr)
- [Accessing MySQL through R](http://playingwithr.blogspot.sg/2011/05/accessing-mysql-through-r.html)
______________________
推薦閱讀：