Connecting R to MySQL
Sebelum kita mengconnectkan R ke MySQL, kita install dulu beberapa packages dibawah .
#install.packages("DBI",
# "odbc",
# "RMariaDB",
# "RMySQL",
# "RSQLite"
# )
Selanjutnya, kita install Pacman untuk meload packages yang sudah kita install sebelumnya.
# install.packages("pacman")
# pacman::p_load(DBI, # DBI package helps connecting R to DBMS
# odbc, # Connect to ODBC Compatible Databases
# RMariaDB, # Database Interface and 'MariaDB' Driver
# RMySQL, # Database Interface and 'RMySQL' Driver
# RSQLite # Database Interface and 'RSQLite' Driver
# )
## package 'odbc' successfully unpacked and MD5 sums checked
##
## The downloaded binary packages are in
## C:\Users\user\AppData\Local\Temp\RtmpaeQdjG\downloaded_packages
Lalu, kita gunakan coding dibawah untuk mengconnectkan R ke MySQL.
Note : Sebelum itu pastikan kita sudah membuka software XAMPP, lalu klik start pada Apache dan MySQL. Selanjutnya, kita klik admin untuk dapat mengakses database kita di PHPMyAdmin.
MySQL <- dbConnect(MySQL(),
user='root',
password='',
dbname='latpert4',
host='localhost')
dbListTables(MySQL) # a list of the tables in our connection
Create Database
Jika kita ingin membuat database baru, kita dapat menggunakan argumen CREATE DATABASE. Jika coding berhasil atau database yang baru berhasil dibuat maka ketika di run hasilnya akan muncul angka 1.
dbExecute(MySQL, "CREATE DATABASE danpert4")
Drop Database
Jika kita ingin menghapus database, kita dapat menggunakan argumen DROP DATABASE. Jika codingan berhasil atau database yang kita inginkan dihapus maka hasilnya akan muncul angka 0.
dbExecute(MySQL, "DROP DATABASE danpert4")
Create Table
Sebelumnya kita sudah buat database baru yaitu danpert4, jika kita ingin membuat table pada database tersebut, langkah pertama kita lakukan pengcodingan untuk mendapatkan akses ke database itu.
fac_db <- dbConnect(MySQL(),
user='root',
password='',
dbname='danpert4',
host='localhost')
Setelah mendapatkan akses ke database yang diinginkan, kita gunakan argumen CREATE TABLE untuk membuat table baru.
dbExecute(fac_db, "CREATE TABLE Persons(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255))")
Insert Value
ketika kita ingin menginputkan nilai atau value kedalam table tersebut, kita gunakan argumen INSERT INTO. Lalu, nama table berserta nama kolomnya. Misal, Persons(Nama Kolom). Lalu, masukan valuenya dengan diawali argumen VALUES(isinya).
dbExecute(fac_db, "INSERT INTO
Persons(PersonID, LastName, FirstName, Address, City)
VALUES(20214920007, 'Arya', 'Diyas', 'Jl. Raya Binong', 'Tangerang')")
Truncate Table
TRUNCATE TABLE argumen ini digunakan untuk menghapus data atau value yang ada dalam tabel tersebut.
dbExecute(fac_db, "TRUNCATE TABLE Persons")
Drop Table
kalau tadi truncate untuk menghapus value dari tabel, kalau DROP TABLE digunakan untuk menghapus tabelnya.
dbExecute(fac_db, "DROP TABLE Persons")
Write Table
Jika dirasa Create table diatas membutuhkan waktu yang lama, kita juga bisa menggunakan function dbWriteTable untuk membaca table secara instan. Misal kita punya data dalam bentuk csv kita hanya perlu menggunakan function dbWriteTable maka, data tersebut langsung terkonek ke MySQL. Sehingga kita tidak perlu menginputnya secara manual.
Orders <-read.csv("Orders.csv")
dbWriteTable(fac_db, "Orders", Orders, append=T)
Alter Table
ALTER TABLE digunakan untuk menambahkan, menghapus, atau memodifikasi type kolom yang ada ditabel. Alter table juga dapat digunakan untuk menambahkan dan menghapus variasi constraints yang ada di tabel.
Add Column
Coding dibawah ini untuk menambahkan kolom pada tabel orders.
dbExecute(fac_db, "ALTER TABLE Orders
ADD Email varchar(255)")
Drop Column
Selanjutnya untuk menghapus kolom kita gunakan argumen DROP COLUMN.
dbSendQuery(fac_db, "ALTER TABLE Orders
DROP COLUMN Email")
Modify Column
Untuk memodifiksi type data kita dapat gunakan argumen MODIFY COLUMN.
dbSendQuery(fac_db," ALTER TABLE Orders
MODIFY COLUMN OrderDate date")
Constraints
Constraints SQL digunakan untuk menentukan aturan pada data tabel. Contraints digunakan untuk membatasi jenis data yang dapat masuk ke dalam tabel. Oleh karena itu, data yang masuk ke tabel akan lebih akurat karena data yang tidak sesuai aturan tidak akan masuk ke tabel.
Batasan dapat berupa level kolom atau level tabel. Batasan kolom berlaku untuk kolom, sedangkan batasan tabel berlaku untuk seluruh tabel. Batasan SQL yang sering digunakan adalah :
- NOT NULL : memastikan kolom tidak boleh memuat nilai null.
- UNIQUE : memastikan values kolom tidak ada yang memiliki nilai yang sama.
- PRIMARY KEY : gabungan dari NOT NULL dan UNIQUE. Artinya secara unik mengidentifikasikan setiap baris dalam tabel.
- FOREIGN KEY : secara unik mengidentifikasikan baris/catatan di tabel lain.
- CHECK : memastikan semua value pada kolom sudah sesuai dengan kondisi tertentu.
- DEFAULT : menentukan nilai defalut ketika terdapat nilai yang tidak sesuai.
- INDEX : digunakan untuk membuat atau mengambil data dari database secara cepat.
Not Null
dbSendQuery(fac_db,"CREATE TABLE Person_NotNull (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int)")
Pada codingan diatas, dinyatakan bahwa kolom ID, LastName, dan FirstName tidak akan diperbolehkan memiliki nilai Null ketika “Persons_NotNull” tabel dibuat.
Unique
dbSendQuery(fac_db,"CREATE TABLE Persons_Unique (ID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int)")
Codingan diatas menjelaskan bahwa kolom ID harus memiliki nilai yang unik atau tidak memiliki kesamaan nilai pada saat tabel Persons dibuat.
Atau jika kita ingin membuat UNIQUE Constraint pada saat tabel sudah dibuat. Maka, kita dapat gunakan codingan dibawah ini.
dbSendQuery(fac_db,"ALTER TABLE Persons_Unique
ADD UNIQUE (ID)")
Jika kita menginginkan banyak kolom UNIQUE Constraint, maka kita dapat gunakan formula seperti dibawah ini.
dbSendQuery(fac_db,"ALTER TABLE Persons_Unique
ADD CONSTRAINT UNIQUE (ID,LastName)")
Atau jika kita ingin menghapus UNIQUE Constraint yang sebelumnya sudah kita setting, maka dapat kita gunakan code dibawah ini.
dbSendQuery(fac_db,"ALTER TABLE Persons_Unique
DROP INDEX ID")
Primary Key
Jika kita menggunakan PRIMARY KEY pada suatu kolom, maka kolom tersebut tidak boleh memiliki nilai NULL dan harus unik. Suatu tabel diperbolehkan memiliki satu Primary Key saja, dan di tabel, primary key dapat dimuat satu atau lebih kolom.
dbSendQuery(fac_db,"CREATE TABLE Persons_PK (ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int)")
Untuk menentukan PRIMARY KEY Constraint dengan banyak kolom ketika tabel dibuat, kita dapat gunakan formula dibawah ini.
dbSendQuery(fac_db,"CREATE TABLE Persons_PK (ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT Persons_PK PRIMARY KEY (ID,LastName))")
Namun, ketika tabel sudah dibuat dan kita ingin membuat PRIMARY KEY Constraint untuk kolom ID maka, kita gunakan code dibawah ini.
dbSendQuery(fac_db, "ALTER TABLE Persons_PK
ADD PRIMARY KEY (ID)")
Foreign Key
FOREIGN KEY digunakan untuk membuat hubungan antar dua tabel.FOREIGN KEY adalah bidang (atau kumpulan bidang) dalam satu tabel yang merujuk ke PRIMARY KEY di tabel lain. Tabel yang berisi primary key disebut tabel anak, dan tabel yang berisi foreign key disebut tabel referensi atau tabel induk.
Perhatikan dua tabel berikut:
Perhatikan bahwa kolom “PersonID” di tabel “Orders” menunjuk ke kolom “PersonID” di tabel “Persons”.
- Kolom “PersonID” di tabel “Persons” adalah PRIMARY KEY di tabel “Orang”.
- Kolom “PersonID” di tabel “Orders” adalah FOREIGN KEY di tabel “Pesanan”.
- Batasan FOREIGN KEY digunakan untuk mencegah tindakan yang akan menghancurkan link antar tabel.
- Batasan FOREIGN KEY juga mencegah data yang tidak valid dimasukkan ke dalam kolom kunci asing, karena itu harus menjadi salah satu nilai yang terkandung dalam tabel yang ditunjuknya.
- Untuk mengizinkan penamaan batasan FOREIGN KEY, dan untuk mendefinisikan batasan FOREIGN KEY pada beberapa kolom, gunakan sintaks SQL berikut:
dbSendQuery(fac_db,
"CREATE TABLE Orders (OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
CONSTRAINT FOREIGN KEY (PersonID))")
Untuk mengizinkan penamaan batasan FOREIGN KEY, dan untuk mendefinisikan batasan FOREIGN KEY pada beberapa kolom, gunakan sintaks SQL berikut:
dbSendQuery(fac_db,
"CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons_pk (PersonID))")
Untuk mengizinkan penamaan batasan FOREIGN KEY, dan untuk mendefinisikan batasan FOREIGN KEY pada beberapa kolom, gunakan sintaks SQL berikut:
dbSendQuery(fac_db,"ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)")
Check
CHECK digunakan untuk membatasi rentangan yang dapat ditempatkan ke kolom. Jika kita mennggunakan Check pada suatu kolom, maka nilai tertentu saja yang diizinkan untuk kolom tersebut. Misal, kita gunakan CHECK (Age>=18) maka, pada kolom age yang akan muncul untuk seseorang yang berusia 18 tahun keatas.
dbSendQuery(fac_db,
"CREATE TABLE Persons (ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18))")
Nah, jika kita ingin menggunakan Check untuk beberapa kolom maka, kita gunakan formula dibawah ini.
dbSendQuery(fac_db,
"CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes'))")
Jika tabel sudah dibuat dan kita ingin menambahkan Check maka gunakan code dibawah ini.
dbSendQuery(fac_db, "ALTER TABLE Persons
ADD CHECK (Age>=18)")
Code ini digunakan untuk table yang sudah dibuat dan menggunakan banyak kolom.
dbSendQuery(fac_db, "ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge
CHECK (Age>=18 AND City='Sandnes')")
Default
DEFAULT Constraint digunakan untuk memberikan defalut value untuk kolom. Misal kita set untuk City Sandness itu defalut, jadi setiap City yang memiliki value Sandness akan bernilai defalut. Koding dibawah ini digunakan ketika tabel baru dibuat.
dbSendQuery(fac_db,
"CREATE TABLE Persons_default (ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes')")
Jika kita ingin menambahkan DEFAULT Constraint pada saat tabel sudah dibuat maka, kita gunakan code dibawah ini.
dbSendQuery(fac_db,"ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes'")
Index
INDEX digunakan untuk membuat indeks suatu tabel. Indeks ini digunakan untuk mengambil data dari database secara cepat. Pengguna tidak dapat melihat indeksnya, mereka hanya bisa menggunakannya untuk mempercepat pencarian.
dbSendQuery(fac_db, "CREATE INDEX idx_pname
ON Persons (LastName, FirstName)")
Note : Update tabel dengan indeks menghabiskan banyak waktu dibandingkan update tanpa menggunakan indeks. Hal ini dikarenakan ketika menggunakan indeks, tabel asli di update maka indeks juga perlu di update.
Auto Increment
AUTO-INCREMENT memungkinkan nilai atau value unik akan dihasilkan secara otomatis ketika nilai value baru ditambahkan dalam tabel. Biasanya Auto-Increment digunakan pada primary key.
dbSendQuery(fac_db,
"CREATE TABLE Persons_ai (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid))")
