Perintah MySQL Lanjutan
- Perintah SELECT dari Banyak Tabel
- Pengelompokkan Hasil Query dengan GROUP BY
- HAVING
- SubSELECT
- Menampilkan Record secara Random
- Transaksi
Pada pembahasan kali ini akan dijelaskan beberapa perintah SQL lanjutan yang lebih
kompleks seperti join antar tabel, grouping, sub select, random search dan
transaksi (commit-and-roolback). Untuk mempermudah penjelasan, maka semua
contoh yang disajikan di bab ini mengacu pada pemodelan data konseptual
Untuk membuat tabel-tabel dari rancangan di atas, kita akan menggunakan
tipe tabel InnoDB karena nantinya kita akan menggunakan transaksi di sistem
tersebut. Dan berikut ini spesifikasi basis data dari pemodelan data konseptual
di atas:
Tabel pesan:
Cara 1 : Penggabungan dengan WHERE
Bentuk umum
SELECT tabel1., tabel2.
FROM tabel1,tabel2
WHERE tabel1.PK=tabel2.FK;
Berikut ini perintah SQL untuk menggabungkan tabel pelanggan dan pesan:
Pada hasil perintah query di atas terlihat bahwa terdapat 5 (lima) transaksi
yang dilakukan oleh 3 (tiga) orang pelanggan. Jika kita lihat kembali isi tabel
pelanggan di atas, maka terdapat satu pelanggan yang tidak ditampilkan yaitu
yang memiliki id pelanggan P0003. Pelanggan tersebut tidak ditampilkan karena
belum pernah melakukan transaksi.
Cara 2 : Penggabungan dengan INNER JOIN
Bentuk umum
SELECT tabel1., tabel2.
FROM tabel1 INNER JOIN tabel2
ON tabel1.PK=tabel2.FK;
Berikut ini perintah SQL untuk menggabungkan tabel pelanggan dan pesan:
Outer Join
Dengan outer join, tabel akan digabungkan satu arah, sehingga
memungkinkan ada data yang NULL (kosong) di satu sisi. Sebagai contoh, kita
akan menggabungkan tabel pelanggan dan pesan dimana kita akan
menampilkan daftar pelanggan yang pernah melakukan pemesanan (transaksi).
Outer Join terbagi menjadi 2 (dua) yaitu LEFT JOIN dan RIGHT. Berikut ini bentuk umum dan contohnya:
LEFT JOIN
Bentuk umum
SELECT tabel1., tabel2.
FROM tabel1 LEFT JOIN tabel2
ON tabel1.PK=tabel2.FK;
Berikut ini perintah SQL untuk menggabungkan tabel pelanggan dan pesan:
Berbeda dengan hasil sebelumnya (inner join), penggunaan left join akan
menampilkan juga data pelanggan dengan id P0003, walaupun pelanggan
tersebut belum pernah bertransaksi. Dan pada kolom id_pesan dan tgl_pesan
untuk pelanggan P0003 isinya NULL, artinya di tabel kanan (pesan) pelanggan
tersebut tidak ada.
RIGHT JOIN
Bentuk umum :
SELECT tabel1., tabel2.
FROM tabel1 RIGHT JOIN tabel2
ON tabel1.PK=tabel2.FK;
Berikut ini perintah SQL untuk menggabungkan tabel pelanggan dan pesan:
Dengan right join, tabel yang menjadi acuan adalah tabel sebelah kanan
(tabel pesan), jadi semua isi tabel pesan akan ditampilkan. Jika data pelanggan
tidak ada di tabel pelanggan, maka isi tabel pesan tetap ditampilkan.
Menggabungkan Tiga Tabel
Untuk menggabungkan tiga tabel atau lebih, pada dasarnya sama dengan
penggabungan 2 (dua) tabel. Sebagai contoh misalnya kita akan menampilkan
barang-barang yang dipesan beserta nama barang dan harganya untuk
pemesanan dengan nomor 1.
Berikut ini perintah SQL-nya:
Pengelompokkan Hasil Query dengan GROUP BY
Hasil query terkadang perlu dikelompokkan berdasarkan kriteria atau
kondisi tertentu. Misalnya kita akan menampilkan jumlah barang yang dibeli
untuk masing-masing transaksi (pemesanan).
Perhatikan perintah query berikut ini dan lihat hasilnya:
SELECT pesan.id_pesan, pesan.tgl_pesan,detil_pesan.jumlah
FROM pesan, detil_pesan
WHERE pesan.id_pesan=detil_pesan.id_pesan;
Hasilnya :
Jika kita perhatikan hasil perintah query di atas, kita akan mendapatkan
jumlah barang yang terjadi untuk setiap transaksi, namun hasil tampilannya
masih per-barang. Artinya jumlah yang ditampilkan masih berupa jumlah barang
untuk masing-masing barang.
Agar jumlah barang ditampilkan per-transaksi (pemesanan), maka kita
dapat menggunakan fungsi GROUP BY dan juga SUM untuk menjumlahkan
jumlah barang.
Berikut ini perintah query dengan group by dan count.
SELECT pesan.id_pesan, pesan.tgl_pesan,
SUM(detil_pesan.jumlah) as jumlah
FROM pesan, detil_pesan
WHERE pesan.id_pesan=detil_pesan.id_pesan
GROUP BY id_pesan;
Hasilnya :
Selain hasil di atas, kita juga dapat menggunakan tambahan WITH ROLLUP
di belakang group by untuk menampilkan jumlah total seluruh barang.
Berikut ini perintah query dan hasilnya:
SELECT pesan.id_pesan, pesan.tgl_pesan,
SUM(detil_pesan.jumlah) as jumlah
FROM pesan, detil_pesan
WHERE pesan.id_pesan=detil_pesan.id_pesan
GROUP BY id_pesan WITH ROLLUP;
Hasilnya :
HAVING
Perintah query berikut ini akan menampilkan jumlah item (jenis) barang
untuk tiap transaksi.
SELECT pesan.id_pesan, COUNT(detil_pesan.id_produk) as jumlah
FROM pesan, detil_pesan
WHERE pesan.id_pesan=detil_pesan.id_pesan
GROUP BY pesan.id_pesan
Hasilnya :
Dari hasil query di atas tampak bahwa ditampilkan jumlah item barang
untuk semua transaksi. Selanjutnya bagaimana jika kita ingin hanya
menampilkan data yang jumlah item barangnya lebih dari 2 (dua)? Mungkin kita
langsung berfikir untuk menggunakan WHERE seperti perintah query sebagai
berikut:
SELECT pesan.id_pesan, COUNT(detil_pesan.id_produk) as jumlah
FROM pesan, detil_pesan
WHERE pesan.id_pesan=detil_pesan.id_pesan
AND jumlah > 2
GROUP BY pesan.id_pesan
Hasilnya ternyata tidak sesuai yang diinginkan. Lihat hasilnya sebagai berikut:
Hal tersebut terjadi karena kondisi dalam WHERE tidak dapat diterapkan pada
fungsi agregrasi seperti COUNT, SUM, AVG dll.
Untuk menyeleksi suatu fungsi agregasi, kita tidak dapat menggunakan
WHERE, namun kita dapat menggunakan HAVING.
Berikut ini perintah query yang menggunakan HAVING:
SELECT pesan.id_pesan, COUNT(detil_pesan.id_produk) as jumlah
FROM pesan, detil_pesan
WHERE pesan.id_pesan=detil_pesan.id_pesan
GROUP BY pesan.id_pesan
HAVING jumlah > 2
SubSELECT
Mulai versi 4.1, MySQL mendukung perintah query SubSELECT dimana
memungkinkan untuk melakukan query di dalam query. Misalnya kita akan
menampilkan data yang kondisinya merupakan hasil dari query lain.
Perintah SubSELECT memiliki banyak variasi. Berikut ini beberapa variasi
bentuk perintah SubSELECT.
SELECT ... WHERE col=[ANY|ALL] (SELECT ...);
SELECT ... WHERE col [NOT] IN (SELECT ...);
SELECT ROW(val1,val2,..) =[ANY] (SELECT col1,col2,..);
SELECT ... WHERE col = [NOT] EXISTS (SELECT ...);
SELECT ... FROM (SELECT ...) AS name WHERE ...;
Dan berikut ini beberapa contoh perintah query yang menggunakan SubSELECT.
• Menampilkan daftar pelanggan yang pernah melakukan transaksi (pemesanan).
SELECT id_pelanggan, nm_pelanggan FROM pelanggan
WHERE id_pelanggan IN (SELECT id_pelanggan FROM pesan);
Hasilnya sebagai berikut:
Menampilkan data pemesanan dengan jumlah barang terbanyak.
SELECT id_pesan, jumlah FROM detil_pesan
WHERE jumlah = ( SELECT MAX(jumlah) FROM detil_pesan);
Hasilnya sebagai berikut:
Menampilkan Record secara Random
MySQL memiliki fungsi khusus yang dapat digunakan untuk menampilkan
record secara acak (random). Seperti kita ketahui bahwa pada perintah SELECT
record akan ditampilkan secara urut berdasarkan urutan saat penginputan (FIFO
= First In First Out).
Berikut ini contoh perintah query untuk menampilkan data pelanggan secara
acak (random):
SELECT id_pelanggan, nm_pelanggan, email
FROM pelanggan ORDER BY RAND()
Salah satu hasilnya sebagai berikut:
ANALISA
MySQL memiliki banyak sekali fungsi yang berhubungan dengan operasi
String. Berikut ini beberapa fungsi string yang disediakan MySQL.
CONCAT (str1, str2, ...)
Fungsi ini digunakan untuk menggabungkan dua atau lebih string (kolom).
Sebagai contoh, misalnya akan menggabungkan kolom alamat dan
telepon pada tabel pelanggan menjadi sebuah kolom
beberapa perintah SQL lanjutan yang lebih
kompleks seperti join antar tabel, grouping, sub select, random search dan
transaksi (commit-and-roolback). Untuk mempermudah penjelasan, maka semua
contoh yang disajikan di bab ini mengacu pada pemodelan data konseptual
sekian tutorial kali ini semoga bermanfaat :)
Posted on Utopian.io - Rewarding Open Source Contributors