13. [ SQL ] MySQL 설치 및 RMySQL - dbConnect(), dbGetQuery(), dbDisconnect() / gsub()로 특정문자열 치환
#### Windows10 MySQL 설치를 위한 APMSETUP7 설치 ####
# APM : Apache, Php, MySQL을 한번에 설치해준다.----> localhost를 치면 웹서버가 보여야 정상.
# http://kldp.net/apmsetup/release/ 에서 최신버전 다운로드
# 3221-APMSETUP7_2010010300.exe
# 관련 사항 확인 : http://nittaku.tistory.com/375
#### MySQL monitor ####
참고페이지 : http://nittaku.tistory.com/375?category=764930
# cmd 켬
# database server 접속 : mysql -uroot -p
# database 확인 : show databases;
# database 사용 : use [해당 db명];
# 해당db속 table 확인 : show tables;
# 해당table속 모든칼럼(*) 가져오기 : select * from 테이블명;
#### 샘플 데이터 다운로드 ####
# cmd로 설명한 페이지 : http://mystyle1057.tistory.com/272
# sample db다운 : http://www.mysqltutorial.org/mysql-sample-database.aspx
# cmd에서 압축을 푼 뒤 해당폴더에 진입후 mysql monitor에 접속 : 해당폴더 > mysql -uroot -p
# cmd에서 해당폴더에 <source 파일명.sql;> 을 통해 설치 : 해당폴더 > source mysqlsampledatabase.sql
# 기본폴더에서도 목록 보이게 됨 : cmd > mysql -uroot -p > show databases;
# *** classicmodel 이라는 db에서 table 중 employees table을 불러올 것임 : use classicmodels; > show tables; > select * from employees;
#### DB 확인하기 ####
# cmd > mysql -uroot -p >
# show databases;
# use classicmodels;
# show tables;
# select * from employees;
+-#---------------+-----------+-----------+-----------+---------------------------------+------------+-----------+----------------------+
#| employeeNumber | lastName | firstName | extension | email | officeCode | reportsTo | jobTitle |
#+----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+----------------------+
#| 1002 | Murphy | Diane | x5800 | dmurphy@classicmodelcars.com | 1 | NULL | President |
#| 1056 | Patterson | Mary | x4611 | mpatterso@classicmodelcars.com | 1 | 1002 | VP Sales |
#| 1076 | Firrelli | Jeff | x9273 | jfirrelli@classicmodelcars.com | 1 | 1002 | VP Marketing |
#| 1088 | Patterson | William | x4871 | wpatterson@classicmodelcars.com | 6 | 1056 | Sales Manager (APAC) |
#| 1102 | Bondur | Gerard | x5408 | gbondur@classicmodelcars.com | 4 | 1056 | Sale Manager (EMEA) |
#| 1143 | Bow | Anthony | x5428 | abow@classicmodelcars.com | 1 | 1056 | Sales Manager (NA) |
#| 1165 | Jennings | Leslie | x3291 | ljennings@classicmodelcars.com | 1 | 1143 | Sales Rep |
#| 1166 | Thompson | Leslie | x4065 | lthompson@classicmodelcars.com | 1 | 1143 | Sales Rep |
#| 1188 | Firrelli | Julie | x2173 | jfirrelli@classicmodelcars.com | 2 | 1143 | Sales Rep |
#| 1216 | Patterson | Steve | x4334 | spatterson@classicmodelcars.com | 2 | 1143 | Sales Rep |
#| 1286 | Tseng | Foon Yue | x2248 | ftseng@classicmodelcars.com | 3 | 1143 | Sales Rep |
#| 1323 | Vanauf | George | x4102 | gvanauf@classicmodelcars.com | 3 | 1143 | Sales Rep |
#| 1337 | Bondur | Loui | x6493 | lbondur@classicmodelcars.com | 4 | 1102 | Sales Rep |
#| 1370 | Hernandez | Gerard | x2028 | ghernande@classicmodelcars.com | 4 | 1102 | Sales Rep |
#| 1401 | Castillo | Pamela | x2759 | pcastillo@classicmodelcars.com | 4 | 1102 | Sales Rep |
#| 1501 | Bott | Larry | x2311 | lbott@classicmodelcars.com | 7 | 1102 | Sales Rep |
#| 1504 | Jones | Barry | x102 | bjones@classicmodelcars.com | 7 | 1102 | Sales Rep |
#| 1611 | Fixter | Andy | x101 | afixter@classicmodelcars.com | 6 | 1088 | Sales Rep |
#| 1612 | Marsh | Peter | x102 | pmarsh@classicmodelcars.com | 6 | 1088 | Sales Rep |
#| 1619 | King | Tom | x103 | tking@classicmodelcars.com | 6 | 1088 | Sales Rep |
#| 1621 | Nishi | Mami | x101 | mnishi@classicmodelcars.com | 5 | 1056 | Sales Rep |
#| 1625 | Kato | Yoshimi | x102 | ykato@classicmodelcars.com | 5 | 1621 | Sales Rep |
#| 1702 | Gerard | Martin | x2312 | mgerard@classicmodelcars.com | 4 | 1102 | Sales Rep |
#+----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+----------------------+#
#### RMySQL 설치 및 사용 ####
# R은 아주 큰 DB분석에 좋지 않다. 미리 sql에서 추출해서 분석하는 용도로 사용하는게 좋다.
# 1. RMySQL 패미지 설치 및 사용
install.packages("RMySQL")
library(RMySQL)
# 2. database server에 연결하기
# - mysql server가 돌아가는 상태여야 한다.
mydb <- dbConnect(MySQL(), user='root', password='rkqhwk12', dbname = 'classicmodels')
mydb
# 3. 쿼리문으로 db에서 테이블들 확인하기
# - 쿼리문을 날릴 때는, 연결된 database를 첫번째 인자로 주고 날린다.
result <- dbGetQuery(mydb, 'show tables;')
result
# 4. 쿼리문으로 table속 row개수 다 세기
dbGetQuery(mydb, 'select count(*) from employees;') # 32
# 5. 쿼리문으로 db속 table 가져오기
tbl <- dbGetQuery(mydb, 'select * from employees;')
tbl
# 여기서부터는 R 문법
# 6. paste()로 2개의 문자열 컬럼을 합친 것을 새로운 칼럼으로 생성하기기***
tbl$name <- paste(tbl$firstName, tbl$lastName)
# 7. 합쳐졌던 2개 칼럼 버리기***(버릴 때는, 인덱싱에서 제외하여 새로운 df를 만들어서 버림)
# 이름을 제일 처음으로 + 2개(2, 3번칼럼) 버리기
newdf <- tbl[c( ncol(tbl), 1, 4:(ncol(tbl)-1) )]
newdf
# 8. ***gsub(, , 칼럼명)을 이용하여 특정 문자(NA 등) 치환하기***
# NA가 문자열로 포함된 reportsTo 칼럼에서 NA을 치환해준다.
newdf$reportsTo <- gsub('NA', '', newdf$reportsTo) # 문자열 NA만 있던 경우, ''로 대체하면 <NA>의 진짜 결측값을 가진다.
newdf
# 9. nuemeric/factor가 character로 되어있다면 바꿔주기 --> *** summary()에서 통계정보가 안나오기 때문에
# factor화는 factor()도 있었는데, 칼럼단위로으로... 해주려면... as.factor()*** /
# 칼럼단위라도 a$b <- factor(a$b)사용하기도 했었다. ( ggvis - http://nittaku.tistory.com/369)
str(newdf)
summary(newdf)
newdf$officeCode <- as.factor(newdf$officeCode)
str(newdf)
summary(newdf) # office의 범주중 1이 가장 많다..
# 10. 날짜 정보(2018-1-1)같은 정보가 character로 되어있다면 as.Date()로 바꿔주자.***
# 여기에는 없음
#### 11. database 서버와 연결 끊어주기 #### ****
# mysql서버와의 연결만 끊코, db객체는 살아있다.
dbDisconnect(mydb)