Connecting MySQL
Untuk membuat koneksi antara R dengan SQL, terlebih dahulu kita harus install package dan memanggil package tersebut. Berikut adalah package yang harus di download.
#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='latihan',
host='localhost')
dbListTables(MySQL) # a list of the tables in our connection
Membuat Data Base
Disini kita akan membuat data base dengan nama `factory_db’
dbExecute(MySQL, "CREATE DATABASE f_db")
Drop Data Base
Jika kita ingin menghapus data base, kita harus melakukan seperti yang ada di bawah ini
# dbExecute(MySQL, "DROP DATABASE danpert4")
Membuat Table
Di sini kita akan membuat table baru dari sebuah data base yang telah kita buat tadi.
fac_db <- dbConnect(MySQL(),
user='root',
password='',
dbname='f_db',
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))")
Memasukkan Values
Jika kita ingin menambahkan values untuk seluruh kolom tabel, kita perlu menggunakan INSERT INTO syntax untuk menambahkan values nya. Jangan lupa masukkan values sesuai dengan urutan kolom nya.
dbExecute(fac_db, "INSERT INTO
Persons(PersonID, LastName, FirstName, Address, City)
VALUES(20214920009, 'Dhela', 'Agatha', ' Pamulang 2', 'Tangerang Selatan')")
Memotong Table
TRUNCATE TABLE digunakan untuk menghapus apa yang ada di dalam table, bukan table itu sendiri.
dbExecute(fac_db, "TRUNCATE TABLE Persons")
Drop Table
DIgunakan untuk men-drop table di database.
dbExecute(fac_db, "DROP TABLE Persons")
Write Table
Di sini kita menggunakan function dbWriteTable dimana kita dapat menulis data frame R ke database table. Nama kolom data base akan digunakan untuk database table.
Orders <-read.csv("Orders.csv")
dbWriteTable(fac_db, "Orders", Orders, append=T)
Constraints
Digunakan untuk melakukan spesifikasi rules untuk data di sebuah table. Constrains digunakan untuk membatasi tipe data yang bisa menuju ke tabel. Ini memastikan keakuratan dan keandalan data dalam tabel.
Jika ada pelanggaran antara batasan dan tindakan data, tindakan dibatalkan.
Batasan dapat berupa level kolom atau level tabel . Batasan tingkat kolom berlaku untuk kolom, dan batasan tingkat tabel berlaku untuk seluruh tabel. Batasan berikut biasanya digunakan dalam SQL :
1. NOT NULL Memastikan bahwa kolom tidak boleh memiliki nilai NULL
2. UNIQUE : Memastikan bahwa semua nilai dalam kolom berbeda
3. PRIMARY KEY : Kombinasi NOT NULL dan UNIQUE . Secara unik mengidentifikasi setiap baris dalam tabel
4. FOREIGN KEY : Secara unik mengidentifikasi baris / record di tabel lain
5. CHECK : Memastikan bahwa semua nilai dalam kolom memenuhi kondisi tertentu
6. DEFAULT : Menetapkan nilai default untuk kolom ketika tidak ada nilai yang ditentukan
7. INDEX : Digunakan untuk membuat dan mengambil data dari database dengan sangat 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))")
