Create DB

Kalau kita mau membuat database yang baru, maka kita harus membuat pernyataan CREATE DATABASE, seperti yang saya lakukan dibawah ini:

dbExecute(MySQL, "CREATE DATABASE danpert4")

Nah, hasil dari CREATE DATABASE menunjukkan hasil angka 1, yang berarti saya berhasil membuat database yang baru.

Drop Database

Kalau tadi kita sudah membuat database yang baru, sekarang kalau kita mau hapus database memakai pernyataan DROP DATABASE, seperti yang saya lakukan dibawah ini:

dbExecute(MySQL, "DROP DATABASE danpert4")

Nah, hasil dari DROP DATABASE menunjukkan hasil angka 0, yang berarti saya berhasil menghapus (drop) database.

Insert Value

Kalau kita mau menambahkan nilai untuk semua kolom tabel, kita menggunakan sintaks INSERT INTO, sebagai berikut:

dbExecute(fac_db, "INSERT INTO
          Persons(PersonID, LastName, FirstName, Address, City)
          VALUES(20214920002, 'Brigitta', 'Naftali', 'Jl.In Ajadulu', 'Tangerang')")

Truncate Table

Kalau pernyataan TRUNCATE TABLE digunakan untuk menghapus data yang ada didalam tabel (bukan tabelnya ya), seperti berikut:

dbExecute(fac_db, "TRUNCATE TABLE Persons")

Drop Table

Kalau pernyataan DROP TABLE digunakan untuk mengdrop tabel yang ada didalam database, seperti berikut:

dbExecute(fac_db, "DROP TABLE Persons")

Write Table

Fungsi dari dbWriteTable adalah untuk menulis R data frame langsung kedalam tabel database. Nama data frame nya akan digunakan sebagai bidang tabel database, seperti berikut:

Orders      <-read.csv("C:/Users/Naftali Brigitta/Documents/Naftali/Kampus/DBS/Orders.csv")  
dbWriteTable(factory_db, "Orders", Orders, append=T)

Add Column

Pakai argumen ADD, kalau kita mau menambahkan kolom kedalam tabel:

dbExecute(fac_db, "ALTER TABLE Orders
                       ADD Email varchar(255)")

Drop Column

Pakai argumen DROP, kalau kita mau menghapus kolom dalam tabel (ada bbrp database yang mengizinkan penghapusan kolom)

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")

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 CONSTRAINT UNIQUE 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 CONSTRAINT UNIQUE, 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 CONSTRAINT UNIQUE 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:
* “Persons” table:

* “Orders” table:

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 kode 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

Kalau pernyataan CREATE INDEX untuk membuat indeks didalam tabel. Indeks biasanya digunakan untuk mengambil data dari database yg lebih cepat. Para pengguna tak dapat melihat indeks, pengguna hanya menggunakan untuk mempercepat pencarian.

dbSendQuery(fac_db, "CREATE INDEX idx_pname
                         ON Persons (LastName, FirstName)")

Catatan: Untuk perbaharui tabel menggunakan indeks akan memakan waktu lama daripada tanpa menggunakan indeks.

Auto Increment

Auto Increment atau peningkatan otomatis untuk menghasilkan nomor unik secara otomatis ketika ada catatan baru dimasukkan kedalam tabel. Kita menggunakan pernyataan SQL Personid untuk menjadi bidang kunci utama.

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))")