深入淺出SQL

04-26

相比於前四關，這一部分斷斷續續的花了近20天時間，經歷了認識資料庫—>認識MySQL—>MySQL基礎操作—>MySQL複雜操作—>MySQL實踐—>MySQL資料庫設計。

首先需要安裝MySQL和SQL的可視化平台workbench。安裝過程的話可以很方便地在百度上搜索到，照著教程一步一步做的話，一般情況下不會遇到什麼問題。

學習過程涉及的平台或資料如下：

入門：Codecademy

最初入門階段是在codecademy上學習基本語法。平台語言為英語，都是比較基礎的一些介紹和辭彙，學習過程中不會造成多少障礙。

學習界面分成三個部分，學習問題的背景和SQL語法介紹，代碼區，結果輸出區。初學者能夠非常清楚每個問題的產生背景，解決該問題需要學習的SQL語法，語法的詳細解釋，現場實踐，實踐信息反饋，在下一章節前半部分還包括對上一章節的回顧。

初識SQL——深入淺出SQL

正如這本書的作者所預期的那樣，書中大量的生動圖片和詼諧的語言，加上豐富而貼近生活的實例，富有啟發式的循序漸進的講解方式，讓我在不斷學習的過程中收穫著一點一滴的快樂，並不會像其他的學習過程伴隨著艱深晦澀的語言。整本書的行文風格如同小說一般，用了幾個虛構的（也可能是真的發生過的）事例將所有的章節串聯起來，比如如何婚姻介紹人如何通過SQL將手頭上單身男女青年的信息整合起來，並且可以方便快捷的查找等等。全書第一部分通過一個的具體實例，講解了資料庫的產生原因，MySQL中資料庫和表的新建，SQL表中數據添加和一些常規情況；第二部分講解了MySQL數據查詢操作；第三部分是MySQL中的修改操作；第四部分，也是我認為收穫最大的部分，介紹了如何規範化資料庫設計。系統介紹了資料庫設計需要遵循的3範式。第五部分是介紹MySQL中統計函數的使用。第六部分介紹MySQL中表格的連接方式。

MySQL簡單操作——W3school

W3school平台上的SQL教程更加面向MySQL資料庫中的實際操作，可以按照基礎教程-高級教程-函數這樣粗略的進行分模塊，每個模塊又包含了MySQL實際操作過程中常見的語句函數，順著教程完整碼了一遍（裡面很多例子需要自己新建TABLE），對於《深入淺出SQL》這本書相當於是一個實踐複習。學習完成之後有20道測試題，全部通關~

MySQL複雜操作——sqlzoo

這個平台學習包括9個模塊，每個模塊還包含各自的擴展學習內容，學習之後有相應的測試模塊，需要掌握了W3school中基本操作之後才會有能力完成。裡面的題目難度和複雜度不斷加深，更加面向實戰操作。把9個模塊全部完成下來，感覺對於MySQL的語句函數操作的認識度上升了一個層次。

R語言鏈接MySQL

導入RMySQL包

> install.packages("RMySQL")> library(RMySQL)#載入需要的程輯包：DBI> library(DBI)> library(RMySQL)

鏈接資料庫

con <-dbConnect(MySQL(), host=127.0.0.1, user=root, password=jude@123, dbname=flightsinfo)

我安裝的MySQL Workbench中找不到word這個資料庫，就導入了其他的資料庫代替。

Host資料庫伺服器地址（例子中127.0.0.1是指本地資料庫）一般來說提供資料庫的人都會告訴你訪問資料庫的IP地址和埠號。
user，password分別是我們登錄資料庫的用戶名和密碼（如果是本地安裝，這個是我們安裝資料庫的時候的用戶名和密碼）。
dbname顧名思義，那就是資料庫名稱

R中進行SQL語句操作——查詢數據

fligtsdata <-dbGetQuery(con,"SELECT year,month,day,dep_delay,arr_delay, distance,dest FROM flights")

關閉RMySQL鏈接

dbDisconnect(con)

第四關項目重構

安裝RMySQL

打開util文件夾中的packageMange.R，在該R腳本中的最上面添加下面紅框中的代碼。這樣添加的目的是，每次項目運行都會判斷RMySQL是否安裝過，如果沒有安裝會安裝該包，如果已安裝不會重新執行安裝了。其他安裝包也可以參用這樣的方法。

packages <-c("RMySQL")if (length(setdiff(packages, rownames(installed.packages())))>0){ install.packages(setdiff(packages,rownames(installed.packages())))}library("RMySQL")

載入資料庫模塊

library(RMySQL)findFlights <- function(){ #連接資料庫 con <- dbConnect(MySQL(), user=root, password=jude@123456, host=127.0.0.1, dbname=flightsinfo)flightsdata <- dbGetQuery(con,"select year,month,day,dep_delay,arr_delay,distance,destfrom flights")#關閉資料庫連接dbDisconnect(con) return (flightsdata)}

業務模塊代碼

library(dplyr)#library(nycflights13)# for data#當前項目運行根路徑projectPath <- getwd()#db.R路徑dbPath <- str_c(projectPath, "db", "db.R", sep="/")#編譯db.R文件source(dbPath)#航班航行距離與延誤時間的關係disDelay <- function(){ #從資料庫中獲取數據 myFlights <-findFlights() #列名重命名，等號左邊是新列名，右邊是就列名 myFlights <- rename(myFlights, destination = dest) #刪除缺失數據，這裡的空值NA代表航班取消 myFlights <- filter(myFlights, !is.na(dep_delay), !is.na(arr_delay)) #數據排序 myFlights <- arrange(myFlights, desc(dep_delay)) #數據計算：航班航行距離與延誤時間的關係 delay <- myFlights %>% group_by(destination) %>% summarise( count = n(), dist = mean(distance, na.rm = TRUE), delay = mean(arr_delay, na.rm = TRUE) ) %>% filter(count > 20) return(delay)}