Belajar MySQL Database


00. Agenda

  • Pengenalan MySQL
  • Menginstall MySQL
  • Tipe Data
  • Database,Table
  • Insert, Update, Delete, Select
  • Transaction
  • Table Relationship
  • Join
  • Dan lain-lain

01. Pengenalan Sistem Basis Data

Pengenalan Database Management System
  • DBMS adalah aplikasi yang digunakan untuk me-manage data
  • Tanpa menggunakan DBMS, untuk me-manage data, seperti data produk, data customer, data penjualan, kita harus simpan dalam bentuk file (misal seperti ketika menggunakan Excel)
  • DBMS biasanya berjalan sebagai aplikasi server yang digunakan untuk me-manage data, kita hanya tinggal memberi perintah ke DBMS untuk melakukan proses manajemen datanya, seperti menambah, mengubah, menghapus atau mengambil data
  • Contoh DBMS yang populer seperti MySQL, PostgreSQL, MongoDB, Oracle, dan lain-lain

Pengenalan Relational Database
  • Ada banyak sekali jenis-jenis DBMS, seperti Relational Database, Document Database, Key-Value Database, dan lain-lain
  • Namun yang masih populer dan kebanyakan orang gunakan adalah relational database
  • Relational database cukup mudah dimengerti dan dipelajari karena kita sudah terbiasa menyimpan data dalam bentuk tabular (tabel) seperti di Microsoft Excel atau di Google Doc Spreadsheet
  • Selain itu relational database memiliki perintah standard menggunakan SQL, sehingga kita mudah ketika ingin berganti-ganti aplikasi database (seperti MySQL, Oracle, PostgreSQL dan lain-lain)

Cara Kerja DBMS


Database Client
  • Database client adalah aplikasi yang digunakan untuk berkomunikasi dengan DBMS
  • Biasanya DBMS sudah menyediakan database client sederhana yang bisa kita gunakan untuk berkomunikasi dengan DBMS agar lebih mudah
  • Atau kita bisa membuat aplikasi untuk berkomunikasi dengan DBMS, misal membuat aplikasi database client menggunakan Java, PHP atau bahasa pemrograman lainnya

Database File
  • Mayoritas DBMS menyimpan datanya di file, walaupun ada beberapa database yang hanya menyimpan datanya di memory (RAM)
  • Namun jangan berpikir file database yang disimpan berupa file seperti Excel atau CSV (Comma Separated Value), tapi jauh lebih kompleks
  • Database File akan di optimasi oleh DBMS agar mempermudah DBMS dalam manajemen datanya, seperti insert, update, delete dan select
  • Tiap DBMS biasanya memiliki cara masing-masing mengelola Database File nya, dan kita tidak perlu harus tau, karena yang kita perlu tahu hanya cara berkomunikasi ke DBMS

SQL
  • Structured Query Language
  • Merupakan bahasa yang digunakan untuk mengirim perintah ke DBMS
  • SQL adalah bahasa yang mudah karena hanya berisi instruksi untuk menyimpan, mengubah, menghapus atau mengambil data melalui DBMS
  • Secara garis besar, semua perintah SQL di Relational Database itu hampir sama, namun biasanya tiap DBMS ada improvement yang membedakan hal-hal kecil dalam perintah SQL, namun secara garis besar perintahnya tetap sama

02. Pengenalan MySQL

MySQL
  • MySQL adalah DBMS Relational OpenSource yang paling populer di dunia saat ini
  • Tidak hanya OpenSource, MySQL juga gratis untuk digunakan
  • MySQL pertama kali dibuat dan diperkenalkan tahun 1995 oleh David Axmark dan Michael Widenius
  • MySQL sangat populer sekali terutama dikalangan programmer web PHP
  • https://www.mysql.com/

Kenapa Belajar MySQL?


https://db-engines.com/en/ranking/relational+dbms

MySQL Community vs MySQL Enterprise
  • Saat kita membuka halaman website resmi MySQL, jangan sampai salah download aplikasi MySQL
  • MySQL menawarkan pilihan versi MySQL Enterprise, yaitu DBMS MySQL Yang berbayar
  • MySQL yang versi gratis adalah MySQL Community
  • MySQL Enterprise sendiri lebih ke versi improvement dari MySQL Community, biasanya menambahkan support dan monitoring
  • Jika menggunakan MySQL Community, maka kita harus tangani semuanya sendiri, dari masalah dan monitoring MySQL nya

MySQL vs MariaDB
  • Tahun 2008 MySQL di akuisisi oleh perusahaan Sun Microsystem
  • Namun Tahun 2009, Sun Microsystem diakuisisi oleh perusahaan Oracle (Pemilik DBMS Oracle)
  • Hal ini menyebabkan 2 founder MySQL keluar dari MySQL dan membuat project baru bernama MariaDB
  • MariaDB Sebenarnya fork dari MySQL, jadi apa yang bisa dilakukan di MySQL bisa dilakukan di MariaDB
  • Sehingga sekarang jangan terlalu bingung jika ada MySQL dan MariaDB, karena sebenarnya itu dari source code yang sama, mungkin ada perbedaan kecil, namun secara garis besar sebenarnya tetap sama
  • https://mariadb.org/

Cara Kerja MySQL Server


03. Menginstall MySQL

Menginstall MySQL
  • Menginstall MySQL banyak caranya, bisa download langsung dari halaman website resminya
  • Atau bisa menggunakan aplikasi yang mem-bundle MySQL seperti XAMPP yang biasa digunakan oleh programmer PHP
  • Jika sudah menginstall MySQL / MariaDB menggunakan XAMPP, tidak perlu menginstall lagi MySQL, karena jika bentrok, maka salah satu aplikasi MySQL nya tidak akan bisa jalan

Menginstall MySQL di Mac
  • Khusus untuk pengguna Mac, selain download installer MySQL di website resmi MySQL, kita juga bisa menggunakan homebrew untuk menginstall MySQL
  • Cukup gunakan perintah : brew install mysql

Menggunakan MySQL Client
  • MySQL Client adalah aplikasi berbasis terminal yang disediakan oleh MySQL untuk berkomunikasi dengan MySQL Server
  • Karena berbasis terminal, sehingga MySQL Client sangat cocok untuk kita gunakan misal ketika di server production, dimana kita menginstall MySQL di linux server yang berbasis terminal misal
  • Kita tidak perlu menginstall MySQL Client secara terpisah, karena sudah tersedia di dalam aplikasi MySQL ketika kita menginstallnya

MySQL Workbench
  • MySQL Workbench adalah aplikasi MySQL Client berbasis Desktop yang disediakan oleh MySQL 
  • MySQL Workbench adalah aplikasi gratis
  • Aplikasi MySQL Workbench sangat mempermudah kita melakukan manajemen data di MySQL karena berbasis Desktop
  • https://www.mysql.com/products/workbench/

JetBrains DataGrip
  • DataGrip adalah aplikasi Database Client yang berbayar
  • DataGrip mendukung banyak sekali DBMS sehingga kita cukup menggunakan DataGrip untuk manajemen semua database yang kita gunakan
  • Selain mendukung Relational DBMS, DataGrip juga mendukung DBMS yang NoSQL seperti MongoDB, Cassandra, dan lain-lain
  • https://www.jetbrains.com/datagrip/ 

04. Database
  • Database adalah tempat kita menyimpan table di MySQL
  • Jika kita misalkan table di MySQL adalah sebuah file, maka database adalah folder nya, dimana kita bisa menyimpan banyak table di sebuah database
  • Biasanya pembuatan kita akan membuat satu database untuk satu jenis aplikasi, walaupun satu aplikasi bisa menggunakan lebih dari satu database, namun lumrahnya, satu aplikasi akan menggunakan satu database


Melihat Semua Database di MySQL
  • show databases;

Membuat Database
  • create database nama_database;

Memilih Database
  • use nama_database;

Menghapus Database
  • drop database nama_database;

05. Tipe Data
  • Saat kita membuat tabel di Excel, kita bisa menentukan tipe data apa yang kita masukkan ke tiap kolom di Excel
  • Di MySQL, kita juga bisa menentukan tipe data tiap kolom yang kita buat di sebuah tabel
  • Ada banyak sekali tipe data yang tersedia di MySQL, dari yang sederhana, sampai yang kompleks.
  • Biasanya kita akan menggunakan tipe data sesuai dengan kebutuhan kolom yang perlu kita buat

Tipe Data per Kolom


06. Tipe Data Number
  • Secara garis besar, tipe data number di MySQL ada dua jenis;
  • Integer, atau tipe number bilangan bulat
  • Floating Point, atau tipe data number pecahan

Tipe Data Integer


Note: unsigned artinya tipe data tidak bisa negatif (jika negatif digeser ke 0). Misal TINYINT kalo tipe datanya di set unsigned maka dari 0 sampai 255.

Tipe Data Floating Point



Decimal
  • Selain Integer dan Floating Point, di MySQL terdapat tipe data DECIMAL
  • Ini tipe data number khusus yang bisa ditentukan jumlah precision dan scale nya



Number Attribute


Note: 
  • Misal kita set jadi TYPE(3) dan ZEROFILL, maka angka 7 akan ditulis menjadi 007 (maksimal 3 digit karena TYPE(3) dan ada angka tambahan 0 didepannya karena ZEROFILL)

07. Tipe Data String

Tipe Data String
  • Selain number, biasanya kita sering menyimpan data di dalam tabel dalam bentuk tulisan
  • Tipe data ini namanya tipe data String atau Text
  • Ada banyak tipe data String di MySQL

CHAR dan VARCHAR
  • Pertama tipe data String di MySQL adalah CHAR dan VARCHAR
  • Kita bisa menentukan jumlah panjang maksimal karakter yang bisa ditampung oleh CHAR dan VARCHAR dengan menggunakan kurung buka lalu masukan jumlah maksimal karakter dan diakhiri kurung tutup
  • Misal, CHAR(10) atau VARCHAR(10) artinya tipe data String dengan maksimal jumlah karakternya adalah 10 karakter
  • Maksimum ukuran CHAR atau VARCHAR adalah 65535 karakter

Perbedaan CHAR dan VARCHAR


Note: kenapa value nya abcd kalo pakai char storagenya 4 bytes, sedangkan varchar 5 bytes? karena kalo varchar butuh informasinya tambahan.

TEXT
  • Selain CHAR dan VARCHAR, tipe data String yang lainnya adalah TEXT
  • Berbeda dengan CHAR dan VARCHAR yang kita bisa tentukan panjang maksimum nya, TEXT tidak sudah memiliki maksimum  panjang nya
  • Terdapat 4 tipe data TEXT
    - TINYTEXT dengan maksimum 255 karakter (~256 bytes)
    - TEXT dengan maksimum 65535 karakter ( ~64 kb)
    - MEDIUMTEXT dengan maksimum 16777215 karakter (~16MB)
    - dan LONGTEXT dengan maksimum 4294967295 karakter (~4GB)
ENUM
  • ENUM adalah tipe data String yang bisa kita tentukan pilihan pilihannya
  • Misal kita bisa membuat:
    - ENUM(‘Pria’, ‘Wanita’), artinya hanya bisa menerima data Pria atau Wanita
    - ENUM(‘Programmer’, ‘Zaman’, ‘Now’), artinya hanya bisa menerima data Programmer, Zaman atau Now

08. Tipe Data Date dan Time
  • Selain tipe data Number dan String, biasanya kadang kita sering menyimpan data waktu atau tanggal
  • Sebenarnya bisa kita gunakan String untuk menyimpan data waktu atau tanggal, namun itu tidak di rekomendasikan, karena akan menyulitkan kita ketika nanti butuh melakukan manipulasi waktu atau tanggal di MySQL

Jenis-Jenis Tipe Data Date dan Time

Note: Timestamp ada informasi tambahannya seperti created at.

09. Tipe Data Boolean
  • BOOLEAN adalah tipe data kebenaran, yang artinya datanya hanya ada dua jenis, benar atau salah
  • Benar direpresentasikan dengan data TRUE, sedangkan salah direpresentasikan dengan data FALSE

10. Tipe Data Lainnya

Dan Lain-Lain
11. Table
  • Data biasanya disimpan di dalam tabel di MySQL
  • Tiap tabel biasanya menyimpan satu jenis data, misal ketika kita membuat aplikasi toko online, kita akan membuat tabel barang, tabel pelanggan, tabel penjual, dan lain-lain
  • Sebelum kita bisa memasukkan data ke tabel, kita wajib terlebih dahulu membuat tabelnya terlebih dahulu
  • Dan tiap tabel yang kita buat, wajib ditentukan kolom-kolom nya, dan tipe data tiap kolom nya
  • Kita juga bisa mengubah tabel yang sudah terlanjur dibuat, seperti menambah kolom baru, mengubah kolom yang sudah ada, atau menghapus kolom

Storage Engines
  • MySQL memiliki berbagai cara melakukan pengolahan data, hal ini disebut Storage Engines.
  • Saat ini, yang biasa dan populer digunakan adalah InnoDB
  • Untuk melihat storage engines apa saja yang terdapat di MySQL, kita bisa menggunakan perintah : SHOW ENGINES; 

Daftar Storage Engines


Melihat Table
  • SHOW TABLES;

Membuat Table


Melihat Struktur Table
  • DESCRIBE nama_tabel;
  • DESC nama_tabel;
  • SHOW CREATE TABLE nama_tabel;

Mengubah Table


Null Value
  • Null adalah nilai ketika kita tidak mengisi data ke dalam kolom
  • Secara default, saat kita membuat kolom, kolom tersebut bisa bernilai NULL, jika kita tidak ingin menerima nilai NULL, kita bisa menambahkan NOT NULL ketika pembuatan kolom nya

Note: NULL artinya datanya kosong.

Default Value
  • Saat kita menyimpan data ke dalam tabel, lalu kita hanya menyimpan beberapa kolom (tidak semuanya), kolom yang tidak kita beri nilai secara default nilainya adalah NULL
  • Jika kita ingin mengubah default value nya, kita bisa menambahkan perintah DEFAULT NILAI ketika pembuatan kolom nya
  • Khusus tipe data DATETIME atau TIMESTAMP, jika kita ingin menggunakan default value dengan nilai waktu saat ini, kita bisa gunakan kata kunci CURRENT_TIMESTAMP

Membuat Ulang Table
  • TRUNCATE nama_tabel;

Note: untuk menghapus semua data dalam tabel setelah itu tabelnya dibuat ulang (jadi tabelnya masih ada).

Menghapus Table
  • DROP TABLE nama_tabel;

12. Insert Data
  • Sebelum kita meng memasukkan data kedalam tabel, tabel harus dibuat terlebih dahulu
  • Kita bisa menyebutkan kolom mana yang ingin kita isi, jika kita tidak menyebutkan kolom nya, artinya kolom tersebut tidak akan kita isi, dan secara otomatis kolom yang tidak kita isi, nilainya akan NULL, kecuali memiliki DEFAULT VALUE
  • Untuk memasukkan data kedalam tabel, kita bisa menggunakan perintah SQL yang bernama INSERT

Membuat Tabel Produk


Memasukkan Data


Memasukkan Beberapa Data Sekaligus


13. Select Data
  • Untuk mengambil data di tabel, kita bisa menggunakan SQL dengan kata kunci SELECT
  • SELECT bisa digunakan untuk mengambil semua kolom yang ada di tabel, atau sebagian kolom saja
  • Jika kita ingin mengambil semua kolom, kita bisa gunakan karakter * (bintang)
  • Jika kita hanya ingin mengambil beberapa kolom saja, kita bisa sebutkan nama-nama kolom yang ingin kita ambil datanya

Mengambil Data


14. Primary Key
  • Saat kita membuat tabel, idealnya tiap tabel memiliki Primary Key
  • Primary key adalah sebuah kolom yang kita tunjuk sebagai id dari tabel tersebut
  • Primary key adalah identitas untuk tiap baris data di dalam tabel
  • Primary key harus unik, tidak boleh ada data dengan primary key yang sama
  • Kita bisa menunjuk kolom yang akan kita jadikan primary key

Primary Key di Multiple Column
  • Kita bisa membuat primary key dengan kombinasi beberapa kolom
  • Namun disarankan untuk tetap menggunakan satu kolom ketika membuat primary key
  • Kecuali ada kasus khusus, seperti membuat tabel yang berelasi MANY TO MANY (yang nanti akan kita bahas)

Menambah Primary Key Ketika Membuat Tabel



Menambah Primary Key di Tabel


15.  Where Clause
  • Saat mengambil data menggunakan perintah SQL SELECT, kadang kita ingin melakukan pencarian data
  • Misal, kita ingin mengambil data barang yang harganya 1jt, atau mengambil data barang yang quantity nya 0 (stok nya kosong)
  • Hal ini bisa kita lakukan dengan WHERE clause setelah perintah SELECT

Mencari Data


Note: untuk string huruf case insensitive artinya huruf besar dan huruf kecil dianggap sama.

16. Update Data
  • Untuk mengubah data di tabel, kita bisa menggunakan perintah SQL UPDATE
  • Saat menggunakan SQL UPDATE, kita harus memberi tahu data mana yang akan di update dengan WHERE clause
  • Hati-hati ketika meng-update data di table, jika sampai WHERE clause nya salah, bisa-bisa kita malah meng-update seluruh data di tabel
  • Untuk update, kita harus beritahu, kolom mana yang akan di update

Menambah Kolom Kategori


Mengubah Satu Kolom


Mengubah Beberapa Kolom


Mengubah Dengan Value di Kolom


17. Delete Data
  • Setelah kita tahu cara menambah, mengubah dan mengambil data di tabel, terakhir yang perlu kita ketahui adalah menghapus data di table
  • Untuk menghapus data di table, kita bisa menggunakan perintah SQL DELETE
  • Perintah SQL DELETE sama seperti UPDATE, kita perlu memberi tahu data mana yang akan di hapus dengan WHERE clause
  • Dan hati-hati, jangan sampai salah menentukan WHERE clause, karena jika salah, bisa-bisa kita akan menghapus seluruh data di table

Menghapus Data


18. Alias
  • MySQL memiliki fitur untuk melakukan alias untuk kolom dan tabel
  • Alias berguna jika kita ingin mengubah nama kolom atau nama tabel ketika melakukan SELECT data
  • Mungkin saat ini alias untuk tabel tidak terlalu terlihat gunanya, tapi nanti ketika kita telah mempelajari tentang JOIN, maka fitur alias untuk tabel sangat berguna sekali

Alias untuk Kolom


Alias untuk Tabel



19. Where Operator
  • Sebelumnya di materi where clause kita sudah menggunakan operator = (sama dengan)
  • Sebenarnya sangat banyak sekali operator yang bisa kita gunakan ketika menggunakan where clause
  • Sekarang kita akan bahas satu per satu

Operator Perbandingan


Mencari Data dengan Operator Perbandingan


AND dan OR Operator
  • Kadang kita ingin mencari data dengan beberapa gabungan kondisi, kita bisa menggunakan operator AND dan OR
  • AND dan OR digunakan untuk menggabungkan beberapa dua operator

Hasil Operator AND


Mencari Data dengan Operator AND


Hasil Operator OR


Mencari Data dengan Operator OR


Prioritas dengan Kurung ()


Note: jika menggunakan AND dan OR yang dikerjakan AND dulu, lalu OR. Tapi kalau ada dalam kurung, berarti yang dalam kurung yang dikerjakan dulu, lalu AND, kemudian OR.

LIKE Operator
  • LIKE operator adalah operator yang bisa kita gunakan untuk mencari sebagian data dalam String
  • Ini cocok sekali ketika kita hanya ingin mencari sebagian kata dalam String
  • Namun perlu diingat, operasi LIKE itu sangat lambat, oleh karena itu, tidak disarankan jika datanya sudah terlalu besar di tabel
  • Operasi LIKE tidak case sensitive, jadi huruf besar dan kecil tidak akan berpengaruh

Hasil Operator LIKE


Mencari Menggunakan LIKE Operator


NULL Operator
  • Untuk mencari data yang berisi NULL, kita tidak bisa menggunakan operator perbandingan = NULL.
  • Ada operator khusus untuk mencari data NULL, yaitu menggunakan NULL operator
  • IS NULL, artinya mencari yang NULL
  • IS NOT NULL, artinya mencari yang tidak NULL

Mencari Menggunakan NULL Operator


BETWEEN Operator
  • Kadang kita ingin mencari data yang >= dan <= secara sekaligus
  • Misal kita ingin mencari products yang harganya antara 10000 sampai 20000
  • Untuk melakukan ini, kita bisa menggunakan WHERE price >= 10000 AND price <= 20000
  • Namun ada operator BETWEEN yang bisa kita gunakan agar lebih sederhana
  • Untuk kebalikannya, kita bisa gunakan NOT BETWEEN

Mencari Menggunakan BETWEEN Operator


IN Operator
  • Operator IN adalah operator untuk melakukan pencarian sebuah kolom dengan beberapa nilai.
  • Misal kita ingin mencari products dengan category Makanan atau Minuman, maka kita bisa menggunakan operator IN

Mencari Menggunakan IN Operator


20. Order By Clause
  • Untuk mengurutkan data ketika kita menggunakan perintah SQL SELECT, kita bisa menambahkan ORDER BY clause
  • ORDER BY clause digunakan untuk mengurutkan data berdasarkan kolom yang dipilih, dan jenis urutan (ASC atau DESC)
  • Kita juga bisa mengurutkan tidak hanya terhadap satu kolom, tapi beberapa kolom

Mengurutkan Data


21. Limit Clause
  • Mengambil seluruh data di tabel bukanlah pilihan bijak, apalagi jika datanya sudah banyak sekali
  • Kita bisa membatasi jumlah data yang diambil dalam SQL SELECT dengan LIMIT clause
  • Selain membatasi jumlah data, kita juga bisa meng-skip sejumlah data yang tidak ingin kita lihat
  • LIMIT biasanya digunakan saat melakukan paging di aplikasi kita

Membatasi Hasil Query



Skip Hasil Query


22. Select Distinct Data
  • Saat melakukan query dengan SELECT, kadang kita mendapatkan data yang duplikat
  • Misal kita ingin melihat semua kategori di tabel products, maka otomatis hasil query SELECT akan duplikat, karena banyak sekali produk dengan kategori yang sama
  • Jika kita ingin menghilangkan data-data duplikat tersebut , kita bisa menggunakan SELECT dengan tambahan DISTINCT sebelum nama kolom nya

Menghilangkan Data Duplikat


23. Numeric Function
  • MySQL memiliki banyak sekali fitur untuk manipulasi data angka
  • Hal ini memudahkan kita untuk memanipulasi data angka
  • Secara garis besar, fitur ini dibagi menjadi dua, Arithmetic Operator dan Mathematical Function

Arithmetic Operator


Menggunakan Arithmetic Operator


Mathematical Function
Menggunakan Mathematical Function


24.  Auto Increment
  • Kadang kita butuh angka yang berurut untuk membuat primary key, misal 1, 2, 3, dan seterusnya.
  • Untuk melakukan hal ini secara manual bukanlah hal bijak, apalagi jika aplikasi yang kita buat diakses oleh banyak orang secara bersamaan
  • MySQL memiliki fitur yang bernama auto increment, fitur ini bisa kita gunakan untuk menandai bahwa suatu primary key datanya diisi secara otomatis dari angka terakhir + 1
  • Dengan menggunakan auto increment, kita tidak perlu lalu memasukkan data primary key, ini akan otomatis dibuat oleh MySQL

Membuat Tabel dengan Auto Increment


Note: Auto Increment cuma bisa di Primary Key.

Memasukkan Data Tanpa Id


Melihat Id Terakhir


25. String Function
Menggunakan String Function


26. Date dan Time Function
Menambah Kolom Timestamp


27. Flow Control Function
Menggunakan Control Flow CASE


Menggunakan Control Flow IF


Menggunakan Control Flow IFNULL


28. Aggregate Function
Menggunakan Aggregate Function


29. Grouping

GROUP BY
  • Kadang saat melakukan aggregate, kita ingin datanya di grouping berdasarkan kriteria tertentu
  • Misal kita ingin melihat rata-rata harga product, tapi ingin per category
  • Atau kita ingin melihat total semua product, tapi per category
  • Hal ini bisa dilakukan di MySQL dengan menggunakan GROUP BY clause
  • GROUP BY clause ini hanya bisa digunakan jika kita menggunakan aggregate function
  • https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html

Menggunakan GROUP BY


HAVING Clause
  • Kadang kita ingin melakukan filter terhadap data yang sudah kita grouping
  • Misal kita ingin menampilkan rata-rata harga per kategori, tapi yang harganya diatas 10.000 misalnya
  • Jika menggunakan WHERE di SELECT, hal ini tidak bisa dilakukan
  • Untuk memfilter hasil aggregate function, kita harus menggunakan HAVING clause

Menggunakan HAVING Clause


30. Constraint
  • Di MySQL, kita bisa menambahkan constraint untuk menjaga data di tabel tetap baik
  • Constraint sangat bagus ditambahkan untuk menjaga terjadi validasi yang salah di program kita, sehingga data yang masuk ke database tetap akan terjaga

Unique Constraint
  • Unique constraint adalah constraint yang memastikan bahwa data kita tetap unique
  • Jika kita mencoba memasukkan data yang duplikat, maka MySQL akan menolak data tersebut

Membuat Table dengan Unique Constraint


Menambah/Menghapus Unique Constraint


Check Constraint
  • Check constraint adalah constraint yang bisa kita tambahkan kondisi pengecekannya
  • Ini cocok untuk mengecek data sebelum dimasukkan ke dalam database
  • Misal kita ingin memastikan bahwa harga harus diatas 1000 misal
  • Maka kita bisa menggunakan check constraint

Membuat Table dengan Check Constraint


Menambah/Menghapus Check Constraint



31. Index
  • Secara default, MySQL akan menyimpan data di dalam disk seperti tabel biasanya
  • Hal ini menyebabkan, ketika kita mencari data, maka MySQL akan melakukan pencarian dari baris pertama sampai terakhir, yang artinya semakin banyak datanya, maka akan semakin lambat proses pencarian datanya
  • Saat kita membuat index, MySQL akan menyimpan data dalam struktur data B-Tree : https://en.wikipedia.org/wiki/B-tree 
  • Tidak hanya akan mempermudah kita saat melakukan pencarian, index juga akan mempermudah kita ketika melakukan pengurutan menggunakan ORDER BY
  • https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html

Cara Kerja Index
  • Kita bisa membuat lebih dari satu index di table, dan setiap kita membuat index, kita bisa membuat index untuk beberapa kolom sekaligus
  • Misal kita membuat index
    (col1, col2, col3)
  • Artinya kita punya kemampuan untuk mencari lebih cepat menggunakan index untuk kombinasi query di (col1), (col1, col2) dan (col1, col2, col3)

Efek Samping Membuat Index
  • Index mungkin akan mempercepat untuk proses pencarian dan query data
  • Namun, saat kita membuat index, artinya MySQL akan melakukan proses update data di index tiap kali kita menambah, mengubah atau menghapus data di table
  • Artinya Index membuat proses pencarian dan query lebih cepat, tapi memperlambat proses manipulasi data
  • Oleh karena itu, kita harus bijak saat membuat index

Tidak Perlu Index
  • Saat kita membuat PRIMARY KEY dan UNIQUE constraint, kita tidak perlu menambahkan lagi index
  • Hal ini dikarenakan MySQL secara otomatis akan menambahkan index pada kolom PRIMARY KEY dan UNIQUE constraint

Membuat Table dengan Index


Menambah/Menghapus Index


Note: 
  • Hati-hati jika mau menambah index ke table yang sudah besar karena kalo menambahkan index ditengah jalan (dan aplikasinya sedang jalan) akan memperlambat proses (karena mysql perlu membuilt ulang indexnya).

32. Full-Text Search

Masalah dengan LIKE operator
  • Kadang kita ingin mencari sebuah kata dalam tabel, dan biasanya kita akan menggunakan LIKE operator
  • Operasi yang dilakukan LIKE operator adalah dengan cara mencari seluruh data di tabel dari baris pertama sampai terakhir, hal ini membuat operasi LIKE sangat lambat
  • Menambah index di tabel juga tidak akan membantu, karena LIKE operator tidak menggunakan index
  • MySQL menyediakan fitur Full Text Search jika ada kasus kita ingin melakukan hal ini
Note:
  • Mysql bukan specialise untuk search engine

Full-Text Search
Membuat Table dengan Full-Text Search


Menambah/Menghapus Full-Text Search


Mode Full-Text Search

MySQL menyediakan beberapa mode untuk melakukan pencarian di Full-Text Search, kita bisa memilihnya sesuai kebutuhan kita

Mencari dengan Natural Language Mode


Note: hasilnya ada urutan prioritasnya (scoring)

Mencari dengan Boolean Mode


Mencari dengan Query Expansion Mode


33. Table Relationship
  • Dalam Relational DBMS, salah satu fitur andalan nya adalah table relationship. Yaitu relasi antar tabel
  • Kita bisa melakukan relasi dari satu tabel ke tabel lain.
  • Dalam kehidupan nyata pun pasti kita akan sering membuat relasi antar tabel
  • Misal, saat kita membuat aplikasi penjualan, di laporan penjualan pasti ada data barang. Jika di tabel artinya tabel penjualan akan berelasi dengan tabel barang
  • Misal dalam aplikasi kampus, tabel mahasiswa akan berelasi dengan tabel mata kuliah, dan tabel dosen
  • Dan lain-lain

Foreign Key
  • Saat membuat relasi tabel, biasanya kita akan membuat sebuah kolom sebagai referensi ke tabel lainnya
  • Misal saat kita membuat tabel penjualan, di dalam tabel penjualan, kita akan menambahkan kolom id_produk sebagai referensi ke tabel produk, yang berisi primary key di tabel produk
  • Kolom referensi ini di MySQL dinamakan Foreign Key
  • Kita bisa menambah satu satu lebih foreign key ke dalam sebuah tabel
  • Membuat foreign key sama seperti membuat kolom biasanya, hanya saja kita perlu memberi tahu MySQL bahwa itu adalah foreign key ke tabel lain

Membuat Table dengan Foreign Key


Menambah/Menghapus Foreign Key


Keuntungan Menggunakan Foreign Key
  • Foreign key memastikan bahwa data yang kita masukkan ke kolom tersebut harus tersedia di tabel reference nya
  • Selain itu saat kita menghapus data di tabel reference, MySQL akan mengecek apakah id nya digunakan di foreign key di tabel lain, jika digunakan, maka secara otomatis MySQL akan menolak proses delete data di tabel reference tersebut

Ketika Menghapus Data Berelasi
  • Seperti yang sebelumnya dibahas, ketika kita menghapus data yang berelasi, maka secara otomatis MySQL akan menolak operasi delete tersebut
  • Kita bisa mengubah fitur ini jika kita mau, ada banyak hal yang bisa dilakukan ketika data berelasi dihapus, defaultnya memang akan ditolak (RESTRICT)

Behavior Foreign Key



Note:
  • Misal, tabel wishlist referensinya ke tabel products. Jika data di tabel products dihapus, maka data di tabel wishlist juga terhapus. Jika data di tabel products diupdate, maka data di tabel wishlist juga terupdate. Ini kalo kita pake mode cascade.
  • Penjelasan mode lainnya sesuai dengan yang ditabel.
  • Defaultnya mode restrict.
Mengubah Behavior Menghapus Relasi


34. Join 
  • MySQL mendukung query SELECT langsung ke beberapa tabel secara sekaligus
  • Namun untuk melakukan itu, kita perlu melakukan JOIN di SQL SELECT yang kita buat
  • Untuk melakukan JOIN, kita perlu menentukan tabel mana yang merupakan referensi ke tabel lain
  • Join cocok sekali dengan foreign key, walaupun di MySQL tidak ada aturan kalau JOIN harus ada foreign key
  • Join di MySQL bisa dilakukan untuk lebih dari beberapa tabel
  • Tapi ingat, semakin banyak JOIN, maka proses query akan semakin berat dan lambat, jadi harap bijak ketika melakukan JOIN
  • Idealnya kita melakukan JOIN jangan lebih dari 5 tabel, karena itu bisa berdampak ke performa query yang lambat

Melakukan JOIN Table


Membuat Relasi ke Table Customers


Melakukan JOIN Multiple Table


35. One to One Relationship

Jenis-Jenis Relasi Tabel
  • Sekarang kita sudah tau untuk melakukan relasi antar tabel, kita bisa menggunakan FOREIGN KEY
  • Dan untuk melakukan SELECT beberapa tabel, kita bisa menggunakan JOIN
  • Dalam konsep relasi, ada banyak jenis-jenis relasi antar tabel
  • Sekarang kita akan bahas dari yang pertama yaitu One to One relationship

One to One Relationship
  • One to One relationship adalah relasi antar tabel yang paling sederhana
  • Artinya tiap data di sebuah tabel hanya boleh berelasi ke maksimal 1 data di tabel lainnya
  • Tidak boleh ada relasi lebih dari 1 data
  • Contoh misal, kita membuat aplikasi toko online yang terdapat fitur wallet, dan 1 customer, cuma boleh punya 1 wallet

Diagram One to One Relationship


Membuat One to One Relationship
  • Cara membuat One to One relationship cukup mudah
  • Kita bisa membuat kolom foreign key, lalu set kolom tersebut menggunakan UNIQUE KEY, hal ini dapat mencegah terjadi data di kolom tersebut agar tidak duplikat
  • Atau cara lainnya, kita bisa membuat tabel dengan primary key yang sama, sehingga tidak butuh lagi kolom untuk FOREIGN KEY

Membuat Table Wallet


36. One to Many Relationship
  • One to many relationship adalah relasi antar tabel dimana satu data bisa digunakan lebih dari satu kali di tabel relasinya
  • Berbeda dengan one to one yang cuma bisa digunakan maksimal 1 kali di tabel relasinya, one to many tidak ada batasan berapa banyak data digunakan
  • Contoh relasi antar tabel categories dan products, dimana satu category bisa digunakan oleh lebih dari satu product, yang artinya relasinya nya one category to many products
  • Pembuatan relasi one to many sebenarnya sama dengan one to one, yang membedakan adalah, kita tidak perlu menggunakan UNIQUE KEY, karena datanya memang bisa berkali-kali ditambahkan di tabel relasi nya

Diagram One to Many Relationship



Membuat Table Category


Mengubah Tabel Product


37. Many to Many Relationship
  • Many to Many adalah table relationship yang paling kompleks, dan kadang membingungkan untuk pemula
  • Many to Many adalah relasi dimana ada relasi antara 2 tabel dimana table pertama bisa punya banyak relasi di table kedua, dan table kedua pun punya banyak relasi di table pertama
  • Ini memang sedikit membingungkan, bagaimana caranya bisa relasi kebanyakan secara bolak balik, sedangkan di table kita cuma punya 1 kolom?
  • Contoh relasi many to many adalah relasi antara produk dan penjualan, dimana setiap produk bisa dijual berkali kali, dan setiap penjualan bisa untuk lebih dari satu produk

Diagram Many to Many Relationship


Bagaimana Implementasi Many to Many?
  • Sekarang pertanyaannya, bagaimana implementasi many to many?
  • Apakah kita harus menambahkan id_order di table products? atau id_product di table orders?

Id Product di Table Order
  • Jika kita menambahkan id_product di table orders, artinya sekarang sudah benar, bahwa 1 product bisa dijual berkali-kali
  • Namun masalahnya adalah, berarti 1 order hanya bisa membeli 1 product, karena cuma ada 1 kolom untuk id_product
  • Oke kalo gitu kita tambahkan id_product1, id_product2, dan seterusnya. Solusi ini bisa dilakukan, tapi tidak baik, artinya akan selalu ada maksimal barang yang bisa kita beli dalam satu order

Id Order di Table Product
  • Jika kita tambahkan id_order di table products, artinya sekarang 1 order bisa membeli lebih dari 1 product, oke sudah benar
  • Tapi sayangnya masalahnya terbalik sekarang, 1 product cuma bisa dijual satu kali, tidak bisa dijual berkali-kali, karena kolom id_order nya cuma 1
  • Kalupun kita tambah id_order1, id_order2 dan seterusnya di table product, tetap ada batasan maksimal nya
  • Lantai bagaimana solusinya untuk relasi many to many?

Membuat Table Relasi
  • Solusi yang biasa dilakukan jika terjadi relasi many to many adalah, biasanya kita akan menambah 1 tabel ditengahnya
  • Tabel ini bertugas sebagai jembatan untuk menggabungkan relasi many to many
  • Isi table ini akan ada id dari table pertama dan table kedua, dalam kasus ini adalah id_product dan id_order
  • Dengan demikian, kita bisa menambahkan beberapa data ke dalam tabel relasi ini, sehingga berarti satu product bisa dijual beberapa kali di dalam table order, dan satu order bisa membeli lebih dari satu product

Diagram Many to Many Relationship



Membuat Table Order


Membuat Table Order Detail


Membuat Foreign Key


Melihat Data Order, Detail dan Product-nya


38. Jenis-Jenis Join

Jenis-Jenis Join

Sebelumnya kita sudah bahas tentang JOIN table, tapi sebenarnya ada banyak sekali jenis-jenis JOIN table di MySQL, diantaranya :
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • CROSS JOIN

Inner Join
  • INNER JOIN adalah mekanisme JOIN, dimana terdapat relasi antara tabel pertama dan tabel kedua
  • Jika ada data di tabel pertama yang tidak memiliki relasi di table kedua ataupun sebaliknya, maka hasil INNER JOIN tidak akan ditampilkan
  • Ini adalah default JOIN di MySQL
  • Jika kita menggunakan JOIN seperti yang sudah kita praktekan sebelumnya, sebenarnya itu akan melakukan INNER JOIN

Inner Join Diagram


Melakukan Inner Join


Left Join
  • LEFT JOIN adalah mekanisme JOIN seperti INNER JOIN, namun semua data di table pertama akan diambil juga
  • Jika ada yang tidak memiliki relasi di table kedua, maka hasilnya akan NULL

Left Join Diagram


Melakukan Left Join


Right Join
  • RIGHT JOIN adalah mekanisme JOIN seperti INNER JOIN, namun semua data di table kedua akan diambil juga
  • Jika ada yang tidak memiliki relasi di table pertama, maka hasilnya akan NULL

Right Join Diagram


Melakukan Right Join


Cross Join
  • CROSS JOIN adalah salah satu JOIN yang sangat jangan sekali digunakan
  • CROSS JOIN adalah melakukan join dengan cara mengkalikan data di tabel pertama dengan data di table kedua
  • Artinya jika ada 5 data di tabel pertama, dan 5 data di tabel kedua, akan menghasilkan 25 kombinasi data (5 x 5)
  • Sekali lagi perlu diingat, ini adalah JOIN yang sangat jarang sekali digunakan

Menggunakan Cross Join


Membuat Tabel Perkalian


Cross Join Tabel Perkalian


39. Subqueries

Subquery di WHERE
  • MySQL mendukung pencarian data menggunakan WHERE dari hasil SELECT query
  • Fitur ini dinamakan Subquery
  • Contoh, kita ingin mencari products yang harganya diatas harga rata-rata, artinya kita akan melakukan SELECT dengan WHERE price > harga rata, dimana harga rata-rata perlu kita hitung menggunakan query SELECT lainnya menggunakan aggregate function AVG

Melakukan Subquery di WHERE Clause


Subquery di FROM
  • Selain di WHERE clause, Subquery juga bisa dilakukan di FROM clause
  • Misal kita ingin mencari data dari hasil query SELECT, itu bisa kita lakukan di MySQL

Melakukan Subquery di FROM Clause


40. Set Operator

MySQL mendukung operator Set, dimana ini adalah operasi antara hasil dari dua SELECT query. Ada banyak jenis operator Set, yaitu :
  • UNION
  • UNION ALL
  • INTERSECT, dan
  • MINUS

Membuat Table Guest Book


UNION
  • UNION adalah operasi menggabungkan dua buah SELECT query, dimana jika terdapat data yang duplikat, data duplikatnya akan dihapus dari hasil query

Diagram UNION



Melakukan Query UNION


UNION ALL
  • UNION ALL adalah operasi yang sama dengan UNION, namun data duplikat tetap akan ditampilkan di hasil query nya

Diagram UNION ALL



Melakukan Query UNION ALL


INTERSECT
  • INTERSECT adalah operasi menggabungkan dua query, namun yang diambil hanya data yang terdapat pada hasil query pertama dan query kedua
  • Data yang tidak hanya ada di salah satu query, kan dihapus di hasil operasi INTERSECT 
  • Data nya muncul tidak dalam keadaan duplikat
  • Sayangnya, MySQL tidak memiliki operator INTERSECT, dengan demikian untuk melakukan operasi INTERSECT, kita harus lakukan secara manual menggunakan JOIN atau SUBQUERY

Diagram INTERSECT


Melakukan Query INTERSECT


MINUS
  • MINUS adalah operasi dimana query pertama akan dihilangkan oleh query kedua
  • Artinya jika ada data di query pertama yang sama dengan data yang ada di query kedua, maka data tersebut akan dihapus dari hasil query MINUS
  • Sayang nya, di MySQL juga tidak ada operator MINUS, namun hal ini bisa kita lakukan menggunakan JOIN

Diagram MINUS


Melakukan Query MINUS


41. Transaction

Kenapa Butuh Transaction?
  • Saat membuat aplikasi berbasis database, jarang sekali kita akan melakukan satu jenis perintah SQL per aksi yang dibuat aplikasi
  • Contoh, ketika membuat toko online, ketika customer menekan tombol Pesan, banyak yang harus kita lakukan, misal:
    - Membuat data pesanan di tabel order
    - Membuat data detail pesanan di tabel order detail
    - Menurunkan quantity di tabel produk
    - Dan yang lainnya
  • Artinya, bisa saja dalam satu aksi, kita akan melakukan beberapa perintah sekaligus
  • Jika terjadi kesalahan di salah satu perintah, harapannya adalah perintah-perintah sebelumnya dibatalkan, agar data tetap konsisten

Database Transaction
  • Database transaction adalah fitur di DBMS dimana kita bisa memungkinan beberapa perintah dianggap menjadi sebuah kesatuan perintah yang kita sebut transaction
  • Jika terdapat satu saja proses gagal di transaction, maka secara otomatis perintah-perintah sebelumnya akan dibatalkan
  • Jika sebuah transaction sukses, maka semua perintah akan dipastikan sukses

Membuat Transaction


Membatalkan Transaction


Transaction di MySQL


Yang Tidak Bisa Menggunakan Transaction
  • Perintah DDL (Data Definition Language) tidak bisa menggunakan fitur transaction
  • DDL adalah perintah-perintah yang digunakan untuk merubah struktur, seperti membuat tabel, menambah kolom, menghapus tabel, menghapus database, dan sejenisnya
  • Transaction hanya bisa dilakukan pada perintah DML (Data Manipulation Language), seperti operasi INSERT, UPDATE dan DELETE

42. Locking
  • Locking adalah proses mengunci data di DBMS
  • Proses mengunci data sangat penting dilakukan, salah satunya agar data benar-benar terjamin konsistensinya
  • Karena pada kenyataannya, aplikasi yang akan kita buat pasti digunakan oleh banyak pengguna, dan banyak pengguna tersebut bisa saja akan mengakses data yang sama, jika tidak ada proses locking, bisa dipastikan akan terjadi RACE CONDITION, yaitu proses balapan ketika mengubah data yang sama
  • Contoh saja, ketika kita belanja di toko online, kita akan balapan membeli barang yang sama, jika data tidak terjaga, bisa jadi kita salah mengupdate stock karena pada saat yang bersamaan banyak yang melakukan perubahan stock barang

Locking Record
  • Saat kita melakukan proses TRANSACTION, lalu kita melakukan prose perubahan data, data yang kita ubah tersebut akan secara otomatis di LOCK
  • Hal ini membuat proses TRANSACTION sangat aman
  • Oleh karena itu, sangat disarankan untuk selalu menggunakan fitur TRANSACTION ketika memanipulasi data di database, terutama ketika perintah manipulasinya lebih dari satu kali
  • Locking ini akan membuat sebuah proses perubahan yang dilakukan oleh pihak lain akan diminta untuk menunggu 
  • Data akan di lock sampai kita melakukan COMMIT atau ROLLBACK transaksi tersebut

Locking Record Manual
  • Selain secara otomatis, kadang saat kita membuat aplikasi, kita juga sering melakukan SELECT query terlebih dahulu sebelum melakukan proses UPDATE misalnya.
  • Jika kita ingin melakukan locking sebuah data secara manual, kita bisa tambahkan perintah FOR UPDATE di belakang query SELECT
  • Saat kita lock record yang kita select, maka jika ada proses lain akan melakukan UPDATE, DELETE atau SELECT FOR UPDATE lagi, maka proses lain diminta menunggu sampai kita selesai melakukan COMMIT atau ROLLBACK transaction

Deadlock
  • Saat kita terlalu banyak melakukan proses Locking, hati-hati akan masalah yang bisa terjadi, yaitu DEADLOCK
  • Deadlock adalah situasi ada 2 proses yang saling menunggu satu sama lain, namun data yang ditunggu dua-duanya di lock oleh proses lainnya, sehingga proses menunggunya ini tidak akan pernah selesai.

Contoh Deadlock
  • Proses 1 melakukan SELECT FOR UPDATE untuk data 001
  • Proses 2 melakukan SELECT FOR UPDATE untuk data 002
  • Proses 1 melakukan SELECT FOR UPDATE untuk data 002, diminta menunggu karena di lock oleh Proses 2
  • Proses 2 melakukan SELECT FOR UPDATE untuk data 001, diminta menunggu karena di lock oleh Proses 1
  • Akhirnya Proses 1 dan Proses 2 saling menunggu
  • Deadlock terjadi

Locking Table
  • MySQL mendukung proses locking terhadap sebuah tabel
  • Jika kita me lock table, artinya satu seluruh data di tabel tersebut akan di lock
  • Ada 2 jenis lock table, yaitu READ dan WRITE
  • Cara melakukan locking table adalah dengan perintah:
    LOCK TABLES nama_table READ;
    LOCK TABLES nama_Table WRITE
  • Setelah selesai melakukan lock table, kita bisa melakukan unlock dengan perintah : UNLOCK TABLES;

Behaviour Lock Tables


Locking Instance
  • Salah satu fitur lock lainnya di MySQL adalah lock instance
  • Lock instance adalah perintah locking yang akan membuat perintah DDL (data definition language) akan diminta menunggu sampai proses unlock instance
  • Biasanya proses locking instance ini terjadi ketika misal kita ingin melakukan backup data, agar tidak terjadi perubahan terhadap struktur tabel misalnya, kita bisa melakukan locking instance
  • Setelah proses backup selesai, baru kita unlock lagi instance nya
  • Untuk melakukan locking instance, kita bisa gunakan perintah :
    LOCK INSTANCE FOR BACKUP;
  • Untuk melakukan unlock instance, kita bisa gunakan perintah :
    UNLOCK INSTANCE;

Note: perintah DDL itu CRUD.

43. User Management

Root User
  • Secara default, mysql membuat root user sebagai super administrator
  • Namun best practice nya, saat kita menjalankan MySQL dengan aplikasi yang kita buat, sangat disarankan tidak menggunakan user root
  • Lebih baik kita buat user khusus untuk tiap aplikasi, bahkan kita bisa batasi hak akses user tersebut, seperti hanya bisa melakukan SELECT, dan tidak boleh melakukan INSERT, UPDATE atau DELETE

Hak Akses dan User
  • Dalam user management MySQL, kita akan mengenal istilah Hak Akses dari User

Daftar Hak Akses
Membuat/Menghapus User


Menambah/Menghapus Hak Akses ke User


Mengubah Password untuk User


44. Backup Database
  • Saat membuat aplikasi menggunakan database, ada baiknya kita selalu melakukan backup data secara reguler
  • Untungnya MySQL mendukung proses backup database
  • Untuk melakukan backup database, kita tidak menggunakan perintah SQL, melainkan MySQL menyediakan sebuah aplikasi khusus untuk melakukan backup database, namanya adalah mysqldump
  • https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html 

Melakukan Backup Database



45. Restore Database
  • Selain melakukan backup database, di MySQL juga kita bisa melakukan proses restore data dari file hasil backup
  • Untuk melakukan restore database, kita bisa menggunakan aplikasi mysql client atau menggunakan perintah SOURCE di MySQL

Melakukan Import Database


Import Database Menggunakan SQL


46. Materi Selanjutnya
  • Studi Kasus Database Design
  • Belajar Bahasa Pemrograman
  • MySQL Tuning
  • MySQL Scalability

Selesai.

Sumber: Youtube Programmer Zaman Now