Pada pertemuan sebelumnya, sudah belajar tentang bagaimana
mengkoneksikan R ke SQL, seperti MariaDB, MySQL, dan SQLite. Lalu, untuk
kesempatan kali ini akan dikembangkan kemampuan dalam bidang database
yang dikoneksikan dengan R, yaitu tentang bagaimana dasar fundamental
operasi yang bisa dilakukan di RMySQL.
Connecting R to
MySQL
Untuk bisa membuat koneksi SQL dengan R, kita membutuhkan packages
yang bernama RMySQL. Pertama-tama kita akan memanggil
packages yang akan digunakan.
#install.packages("DBI",
# "odbc",
# "RMariaDB",
# "RMySQL",
# "RSQLite"
# )
# 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
MySQL <- dbConnect(MySQL(),
user='root',
password='',
dbname='jeki',
host='localhost')
dbListTables(MySQL) # a list of the tables in our connection
Create DB
untuk membuat database yang baru, kita dapat menggunakan perintah
CREATE DATABASE.
dbExecute(MySQL, "CREATE DATABASE jekii")
Jika, sudah ada keterangan 1, menandakan bahwa database
yang baru berhasil dibuat.
Drop DB
Setelah kita membuat database yang baru, kita juga dapat menghapus
database yang sudah dibuat dengan menggunakan perintah
DROP DATABASE.
dbExecute(MySQL, "DROP DATABASE jekii")
Create Table
Setelah berhasil untuk membuat database yang baru, kita dapat
mengisi tabel tersebut dengan berbagai variabel
fac_db <- dbConnect(MySQL(),
user='root',
password='',
dbname='jekii',
host='localhost')
Pada kasus ini, kita coba untuk membuat tabel dengan nama
Persons dan mengisi dengan variabel PersonID, LastName,
Firstname, Addresss, City
dbExecute(fac_db, "CREATE TABLE Persons(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255))")
Insert Table
Karena sudah ada tabel, tidak mungkin jika tidak kita isi dengan
sebuah informasi. Maka dari itu, kita akan mengisi informasi menggunakan
perintah INSERT INTO.
dbExecute(fac_db, "INSERT INTO
Persons(PersonID, LastName, FirstName, Address, City)
VALUES(010, 'Jimy', 'Jeki', 'Jl. Bedua Sama Kamu', 'Tangerang')")
Truncate Table
Untuk menghapus isi dari tabel yang sudah diisi dengan informasi
kita dapat menggunakan perintah TRUNCATE TABLE.
dbExecute(fac_db, "TRUNCATE TABLE Persons")
Drop Table
untuk menghapus table yang kita buat dapat digunakan perintah
DROP TABLE.
dbExecute(fac_db, "DROP TABLE Persons")
Write Table
Kita juga dapat menggunakan data yang ada di lokal storage
menggunakan dbWriteTable.
Orders <-read.csv("Orders.csv")
dbWriteTable(fac_db, "Orders", Orders, append=T)
Alter Table
Pada proses ini, kita akan menambahkan, menghapus atau bahkan bisa
mengedit/memodif isi atau informasi yang terdapat pada tabel yang sudah
kita tambahkan di database kita dan kita akan menggunakan
ALTER TABLE sebagai perintahnya untuk melakukan itu semua
dan ditambah dengan perintah lainnya sesuai dengan kondisi yang kita
inginkan.
Add Column
pada proses yang pertama ini, kita akan menambahkan kolom dengan
menggunakan sintaks ADD dan jangan lupa untuk menggunakan
sintaks ALTER TABLE.
dbExecute(fac_db, "ALTER TABLE Orders
ADD Email varchar(255)")
Akan muncul kolom baru yang bernama “email”, tetapi belum ada isinya
yang ditandai dengan keterangan “null”.
Drop Column
untuk menghapus kolom yang sudah ada atau baru dibuat kita dapat
menggunakan perintah DROP COLOUMN.
dbSendQuery(fac_db, "ALTER TABLE Orders
DROP COLUMN Email")
Modify Column
Selanjutnya, kita juga dapat menggunakan sintaks
MODIFY COLOUMN
dbSendQuery(fac_db," ALTER TABLE Orders
MODIFY COLUMN OrderDate date")
Untuk perintah digunakan untuk mengubah tipe data, di sini saya
mencoba mengubah tipe data OrderDate
Constraints
Pada proses selanjutnya ini kita akan menentukan aturan untuk data
pada tabel kita. Tujuannya jelas agar tidak jenis data yang masuk tidak
sembarangan, sehingga nantinya akan menaikkan keakuratan data kita.
- 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
Pada kali ini, kita akan mencoba untuk memastikan bahwa kolom “ID”,
“LastName”, “FirstName” tidak ada yang bernilai NULL
dbSendQuery(fac_db,"CREATE TABLE Person_NotNull (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int)")
Unique
Kolom “ID” akan dibuat menjadi unik pada table yang dibuat.
dbSendQuery(fac_db,"CREATE TABLE Persons_Unique (ID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int)")
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:
| PersonID |
LastName |
FirstName |
Age |
| 1 |
Xi |
Bakti |
28 |
| 2 |
Li |
Chong |
23 |
| 3 |
Gou |
Mei |
20 |
| OrderID |
OrderNumber |
PersonID |
|
| 1 |
77895 |
3 |
|
| 2 |
44678 |
3 |
|
Perhatikan bahwa kolom “PersonID” di tabel “Orders” menunjuk ke kolom
“PersonID” di tabel “Persons”.
Perhatikan bahwa kolom “PersonID” di tabel “Orders” menunjuk ke kolom
“PersonID” di tabel “Persons”.
- Kolom “PersonID” di tabel “Orang” adalah PRIMARY KEY di tabel
“Orang”.
- Kolom “PersonID” di tabel “Pesanan” adalah KUNCI ASING 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))")