Untuk melakukan CRUD pada mysql, bisa menggunakan function : fetch() & dbSendQuery() ,
CRUD terdiri dari : Select, Insert, Updare & Delete
1. Penggunakan Command Select
#Loading RMySQL
library("RMySQL")
# Creating a connection Object to MySQL database.
mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
host = 'localhost')
# selecting the specific record from employee_info table.
record = dbSendQuery(mysql_connect, "select * from employee_info where dept='IT'")
# Fetching all the records(with n = -1) and storing it as a data frame.
data_frame = fetch(record, n = -1)
print(data_frame)
2. Penggunakan Command Insert
#Loading RMySQL
library("RMySQL")
# Creating a connection Object to MySQL database.
mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
host = 'localhost')
# Inserting record into employee_info table.
dbSendQuery(mysql_connect, "insert into employee_info values(9,'Preeti',1025,'8/25/2013','Operations'
3. Penggunakan Command Update
# Loading RMySQL
library("RMySQL")
# Creating a connection Object to MySQL database.
mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
host = 'localhost')
# Updating the record in employee_info table.
dbSendQuery(mysql_connect, "update employee_info set dept='IT' where id=9")
4. Penggunakan Command Delete
#Loading RMySQL
library("RMySQL")
# Creating a connection Object to MySQL database.
mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
host = 'localhost')
# Deleting the specific record from employee_info table.
dbSendQuery(mysql_connect, "delete from employee_info where id=8")
5. Penggunaan Command Drop
#Loading RMySQL
library("RMySQL")
# Creating a connection Object to MySQL database.
mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
host = 'localhost')
# Dropping the specific table from the employee database.
dbSendQuery(mysql_connect, "drop table if exists emp")