内容

SQLの勉強でいろいろ試したいなと思ったけど、RStudio大好き芸人だからRStudioでSQLのQueryを書きたくなった話

• SQLの勉強として練習環境を作りたかった
• RStudioで書きたい (R信者)
• 備忘録です

MySQLをMacに入れる

Progateを参考にインストール

ターミナルからMySQLを立ち上げるときは、以下のコードを流せば良い。どっちでも入れる。

• $ mysql --user=root --password
• $ mysql -u root -p

MySQLに練習用のデータベースを入れる

MySQL公式サイトにも掲載されているemployeeデータをダウンロードします。

1. GitHubから、test_db-master.zipをダウンロードして解凍する
2. ターミナルで解凍したフォルダにcdして、カレントディレクトリにする
3. ターミナルで $ mysql -u ユーザー名 -p < employee.sql と唱える

mysql> show databases;で、データベースの中にemplyeesが入っているはず。

またmysql> show tables in employees;でテーブルを確認すると、以下が返ってくるはず。(もしくは、mysql> USE employees;のあとにmysql> show tables;)

Tables_in_employees

current_dept_emp departments dept_emp dept_emp_latest_date dept_manager employees salaries titles

8 rows in set (0.00 sec)

これで、練習用のデータをmysqlに入れることができました。

RからMySQLに接続

ここからは、RStudioを操作します。

まずは必要なライブラリをインストールします。今回はMySQLを使うので、{RMySQL}を使います。

pacman::p_load(RMySQL) 

RからMySQLに接続するコード。ユーザー名やパスワードを直書きするなら、こんな形になります (非推奨)。

conn <- dbConnect(
  RMySQL::MySQL(), 
  host = "localhost", # ホスト名
  port = 3306,
  user = "root", # ユーザー名
  password = "password", # パスワード 
  dbname = "employees" # DB名
)

「ホスト名やユーザー名ってなんじゃい」って人向けはWorkbenchも見たら良いと思います。

workbenchの画面からLocal instanceを右クリック > Edit Connection から、Hostname, port, usename等々チェックできます。

Workbenchの画面

RとMySQLとの接続でパスワード等をそのまま書き込むのは危険です。 ポップアップ画面で入力する形式を採用するなら、こちら。上述のようなコードに直接書くのはおすすめできません。

conn <- dbConnect(
  RMySQL::MySQL(), 
  host = "ホスト名",
  port = 3306,
  user = rstudioapi::showPrompt("UserID", "Put your userID"), 
  password = rstudioapi::askForPassword(""), 
  dbname = "db名"
)

SSLの暗号化もやったほうがいいです。ただし、ローカル環境で練習する分には、何をどうやっていても問題ないはず。今回は適当にやります。

さて、今RStudioはMySQLとつながっているので、データを確認できるはずです。employees DBに入っているtablesを確認します。

dbListTables(conn)

## [1] "current_dept_emp"     "departments"          "dept_emp"            
## [4] "dept_emp_latest_date" "dept_manager"         "employees"           
## [7] "salaries"             "titles"

SQLのqueryを流す

ここまできたら、あとはSQL構文を練習するのみです。まずはdepartmentsというテーブルの頭5行を出してみましょう。クエリを書きます。

# SQL風に書けます
firstquery <- "

SELECT * 
FROM departments 
LIMIT 5

"
# 文末のセミコロンはR表記の場合不要です

クエリを書いたら、dbGetQuery()でRにテーブルを引っ張ってこれます。

head_departments <- dbGetQuery(conn, firstquery)
head_departments

動きましたね！練習環境としては100点満点じゃないでしょうか。

おしまい

mysqlをmacに入れて、Rと接続しました。単純にSQLを練習するならIDEに入れなくてもいいけど、どうせならIDEあった方が嬉しいですよね。 RStudio芸人なのでRStudioに入れて遊びました。勉強するぞー。

そんじゃーね！