SQL及關(guān)系型數(shù)據(jù)庫入門

什么是關(guān)系型數(shù)據(jù)庫及數(shù)據(jù)庫管理系統(tǒng)

數(shù)據(jù)庫(Database)是一種數(shù)據(jù)的組織和存儲方式，通常用于處理結(jié)構(gòu)化的數(shù)據(jù)。

而關(guān)系型數(shù)據(jù)庫(Relational Database)指的是創(chuàng)建在關(guān)系模型的基礎(chǔ)上的數(shù)據(jù)庫。它借助于集合代數(shù)等數(shù)學(xué)概念和方法來處理數(shù)據(jù)庫中的數(shù)據(jù)。

數(shù)據(jù)庫管理系統(tǒng)(DBMS,Database Management System),是一種專門用于存儲、修改并從數(shù)據(jù)庫提取信息的系統(tǒng)軟件。

主流的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)主要有：MySQL，ORACLE, MS ACCESS，DB2等。

其中，MySQL屬于開源軟件，而其他的主流數(shù)據(jù)庫管理系統(tǒng)基本都是商業(yè)軟件。由于很多互聯(lián)網(wǎng)公司數(shù)據(jù)庫都是采用MySQL進(jìn)行數(shù)據(jù)庫的管理，所以今天我們主要介紹MySQL的安裝、配置及其與R的交互。

什么是SQL語言

SQL(Structured Query Language)是一種專門用來與數(shù)據(jù)庫進(jìn)行溝通的語言。

使用SQL可以對數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行增、刪、查、改、權(quán)限管理等操作。

常用關(guān)鍵詞：SELECT,UPDATE,DELETE,INSERT.

管理關(guān)鍵詞：CREATE,ALTER,DROP

常用子句、關(guān)鍵詞：FROM,WHERE,GROUP BY,ORDER BY

為什么要學(xué)習(xí)SQL

很多同學(xué)可能會很疑惑：對于數(shù)據(jù)的增刪查改等需求，我們的R不是已經(jīng)有非常方便的操作了嗎？為什么還要多學(xué)一門語言呢？

R擅長的領(lǐng)域是數(shù)據(jù)分析，然而，對于數(shù)據(jù)存儲，卻存在很大的問題。一個非常明顯的缺陷是：所有數(shù)據(jù)均要讀入內(nèi)存。這就造成了R能處理的數(shù)據(jù)量存在一個瓶頸。當(dāng)我們要處理的數(shù)據(jù)觀測數(shù)達(dá)到上億級別的時候，R就顯得力所不逮了。

數(shù)據(jù)庫能解決的當(dāng)然不止這一個問題。

當(dāng)我們遇到如下情景時，數(shù)據(jù)庫就顯得非常重要了：

當(dāng)你的數(shù)據(jù)需要通過網(wǎng)站在線展示；

當(dāng)你在一個團隊中工作，你和你的協(xié)作成員要同時操作同一個數(shù)據(jù)集；

當(dāng)你需要為不同的數(shù)據(jù)用戶賦予不同的使用權(quán)限；

當(dāng)你要處理的數(shù)據(jù)量超過了你的電腦內(nèi)存；

當(dāng)你面對的數(shù)據(jù)集非常復(fù)雜，不能統(tǒng)一組織到一個數(shù)據(jù)集中時；

當(dāng)你的數(shù)據(jù)量非常龐大，但你又經(jīng)常要頻繁地獲取它的一些子集時；

當(dāng)你的幾個數(shù)據(jù)集關(guān)聯(lián)性很大，更新一個數(shù)據(jù)集需要同時更新另外一些數(shù)據(jù)集時；

當(dāng)你對數(shù)據(jù)的格式要求很嚴(yán)格時。

而如果我們經(jīng)常與數(shù)據(jù)打交道，以上的問題是必不可免的。

可見，要想成為一名優(yōu)秀的數(shù)據(jù)科學(xué)家，學(xué)習(xí)SQL還是非常有必要的。

當(dāng)然，SQL雖然是一門語言，但是它有一些非常吸引人的優(yōu)點：

幾乎所有重要的DBMS都支持SQL；

SQL語法簡明，簡單易學(xué)；

SQL非常靈活，功能強大。

所以，雖然又得多學(xué)一門語言，但是也不必苦惱。想想能夠幾天掌握一門新的語言，也是挺讓人激動的呢:)

MySQL的安裝及環(huán)境配置

MySQL是一款開軟的數(shù)據(jù)庫管理系統(tǒng)，因此我們可以通過在官網(wǎng)進(jìn)行軟件的自由下載安裝。

對于入門的同學(xué)來說，MySQL Community Server和MySQL Workbench CE結(jié)合起來使用是一個不錯的開始。MySQL Workbench CE是MySQL的一個開發(fā)環(huán)境，具有非常友好的交互界面。它跟MySQL的關(guān)系如同Rstudio和R的關(guān)系。

SQL基本操作——案例學(xué)習(xí)

安裝完畢，我們就可以啟動MySQL Workbench進(jìn)行數(shù)據(jù)庫的創(chuàng)建等操作了。先使用root用戶身份（在安裝的過程中創(chuàng)建）進(jìn)入管理界面。

建立一個數(shù)據(jù)庫

新建一個SQL腳本，即可以開始MySQL的編程了。選中某一個代碼塊，使用CTRL+ENTER快捷鍵即可運行代碼。

create database db1;
show databases;
-- 創(chuàng)建一個普通用戶
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)
);
添加觀測數(shù)據(jù)
insert into birthdays(firstname,lastname,birthday)
    values ('peter','Pascal','1991-02-01'),
            ('paul','panini','1992-03-02');
使用查詢語句
select * from birthdays;
select birthday from birthdays;
追加數(shù)據(jù)
insert into birthdays(nameid,firstname,lastname,birthday)
    values (10,"Donald","Docker","1934-06-09");
SQL與R的交互
R與SQL交互的拓展包非常豐富，不過大致可以分為三大類：
    依賴于DBIpackage，如RMySQL,ROracle,RPosttgreSQL,RSQLite。這種方式通過與DBMS建立原始的連接實現(xiàn)數(shù)據(jù)庫操作。
    依賴于RODBCpackage。這個包通過打開數(shù)據(jù)庫連接驅(qū)動的方式建立非直接的連接。如通過依賴于jre讀入XLS/XLSX表格的數(shù)據(jù)。
    通過dplyr package.
今天主要介紹第1種及第三種方式。
R連接MySQL
操作數(shù)據(jù)庫中的數(shù)據(jù)
下面，我們通過R來操作前面在MySQL中建立的數(shù)據(jù)庫db1。
library(RMySQL)

# 建立一個連接
mydb <- dbConnect(MySQL(),user="root",
                  password = "mycode",
                  dbname = "db1")

#查看表格
dbListTables(mydb)

## [1] "birthdays" "mtcars"    "test"

#查看某一列
dbListFields(mydb,"birthdays")

## [1] "nameid"    "firstname" "lastname"  "birthday"

#
#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

# 另一種方法：建立一個查詢
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存儲到數(shù)據(jù)庫
#將一個data frame對象存儲為一個表格
dbWriteTable(mydb,name = "mtcars",value = mtcars,overwrite=TRUE)

## [1] TRUE
#查看結(jié)果
dbListTables(mydb)

## [1] "birthdays" "mtcars"    "test"

使用dplyr進(jìn)行數(shù)據(jù)庫操作
dplyr是Hadley大神開發(fā)的一個專注于data frame類型的數(shù)據(jù)操作的一個包。它擁有非常簡潔、便于記憶、異常豐富的一系列操作函數(shù)。更吸引人的是：它支持對sqlite,mysql,postgresql等開源數(shù)據(jù)庫的操作。也就是說：你無需掌握SQL語言也能輕松進(jìn)行數(shù)據(jù)庫操作。
當(dāng)然，dplyr并不能替代全部的SQL語言。它主要用于產(chǎn)生分析中最頻繁使用的SELECT語句。
下面我們看看這是如何做到的。
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只有在必要的情況下才會執(zhí)行操作
    它在必要的情況下才會從數(shù)據(jù)庫中載入數(shù)據(jù)
    每一個操作函數(shù)在執(zhí)行的時候，并未開始真正從數(shù)據(jù)庫中請求，而是在必要的情況下，一起執(zhí)行.
如以下的一系列操作并未開始執(zhí)行數(shù)據(jù)提取：
library(dplyr)

myDF <- tbl(conDplyr,"mtcars")
myDF1<-filter(myDF,gear == 4)
myDF2<-select(myDF1,mpg,cyl,gear)
直到執(zhí)行以下語句，才真正開始從數(shù)據(jù)庫中提取數(shù)據(jù)。
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