Modul 4
Perintah SQL Lanjutan
Perintah MySQL Lanjutan
1.
Perintah
SELECT dari Banyak Tabel
2. Pengelompokkan
Hasil Query dengan GROUP BY
3. HAVING
4. SubSELECT
5.
Menampilkan Record secara Random
Untuk mempermudah penjelasan, maka semua contoh yang
disajikan di bab ini mengacu pada pemodelan data konseptual Sistem Pemesanan (Penjualan) Barang sbb:
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 pelanggan
+--------------+-------------+------+-----+---------+-------+
| Field
| Type | Null | Key |
Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id_pelanggan | varchar(5) | NO
| PRI | | |
| nm_pelanggan | varchar(40) | NO |
| | |
| alamat
| text | NO |
| | |
| telepon
| varchar(20) | NO | |
| |
| email
| varchar(50) | NO | |
| |
+--------------+-------------+------+-----+---------+-------+
Tabel pesan
+--------------+------------+------+-----+---------+----------------+
| Field
| Type | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+----------------+
| id_pesan
| int(5) | NO | PRI | NULL | auto_increment |
| id_pelanggan | varchar(5) | NO | MUL | | |
| tgl_pesan
| date | NO |
| | |
+-----------+---------------+------+-----+---------+----------------+
Tabel produk
+-----------+---------------+------+-----+---------+-------+
| Field |
Type | Null | Key | Default |
Extra |
+-----------+---------------+------+-----+---------+-------+
| id_produk | varchar(5) | NO
| PRI | | |
| nm_produk | varchar(30) | NO
| | |
|
| satuan |
varchar(10) | NO | | |
|
| harga |
decimal(10,0) | NO | | 0
| |
| stock |
int(3) | NO |
| 0 | |
+-----------+---------------+------+-----+---------+-------+
Tabel
detil_pesan
+-----------+---------------+------+-----+---------+-------+
| Field |
Type | Null | Key | Default |
Extra |
+-----------+---------------+------+-----+---------+-------+
| id_pesan |
int(5) | NO | PRI | |
|
| id_produk | varchar(5) | NO
| PRI | | |
| jumlah |
int(5) | NO |
| 0 | |
| harga |
decimal(10,0) | NO | | 0
| |
+-----------+---------------+------+-----+---------+-------+
Tabel faktur
+------------+--------+------+-----+---------+----------------+
| Field |
Type | Null | Key | Default |
Extra |
+------------+--------+------+-----+---------+----------------+
| id_faktur |
int(5) | NO | PRI | NULL | auto_increment |
| id_pesan |
int(5) | NO | |
| |
| tgl_faktur | date
| NO | |
| |
+------------+--------+------+-----+---------+----------------+
Tabel kuitansi
+--------------+--------+------+-----+---------+----------------+
| Field
| Type | Null | Key | Default |
Extra |
+--------------+--------+------+-----+---------+----------------+
| id_kuitansi
| int(5) | NO | PRI | NULL | auto_increment |
| id_faktur
| int(5) | NO | |
| |
| tgl_kuitansi | date | NO
| | | |
+--------------+--------+------+-----+---------+----------------+
Perintah SELECT dari Banyak
Tabel dengan JOIN
Untuk menggabungkan 2 (dua) atau lebih tabel, kita
dapat menggunakan bentuk perintah JOIN.
Inner Join
Dengan inner join, tabel akan digabungkan dua
arah, sehingga tidak ada data yang NULL di satu sisi. Sebagai contoh, kita akan menggabungkan tabel
pelanggan dan pesan dimana kita akan menampilkan daftar pelanggan
yang pernah melakukan pemesanan (transaksi). Isi tabel pelanggan dan pesan
adalah sebagai berikut :
Tabel pelanggan (hanya ditampilkan id, nama dan
email).
+--------------+-----------------+--------------------+
| id_pelanggan |
nm_pelanggan | email |
+--------------+-----------------+--------------------+
| P0001
| Achmad Solichin | achmatim@gmail.com |
|
P0002 | Budianto | budi@luhur.com |
| P0003 | Hasan | hasan02@yahoo.com |
| P0004 |
Amin Riyadi | aminudin@plasa.com |
+--------------+-----------------+--------------------+
Tabel pesan :
+----------+--------------+------------+
| id_pesan | id_pelanggan
| tgl_pesan |
+----------+--------------+------------+
| 1 | P0001 | 2008-02-02 |
| 2 | P0002 | 2008-02-05 |
| 3 | P0002 | 2008-02-10 |
| 4 | P0004 | 2008-01-20 |
| 5 | P0001 | 2007-12-14 |
+----------+--------------+------------+
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:
SELECT pelanggan.id_pelanggan,
pelanggan.nm_pelanggan,
pesan.id_pesan,
pesan.tgl_pesan
FROM pelanggan, pesan
WHERE
pelanggan.id_pelanggan=pesan.id_pelanggan;
Hasilnya :
+--------------+-----------------+----------+------------+
| id_pelanggan |
nm_pelanggan | id_pesan |
tgl_pesan |
+--------------+-----------------+----------+------------+
| P0001 | Achmad Solichin | 1 | 2008-02-02 |
| P0001 | Achmad Solichin | 5 | 2007-12-14 |
| P0002 | Budianto |
2 | 2008-02-05 |
| P0002 | Budianto |
3 | 2008-02-10 |
| P0004 | Amin Riyadi |
4 | 2008-01-20 |
+--------------+-----------------+----------+------------+
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:
SELECT
pelanggan.id_pelanggan, pelanggan.nm_pelanggan,
pesan.id_pesan,
pesan.tgl_pesan
FROM
pelanggan INNER JOIN pesan
ON
pelanggan.id_pelanggan=pesan.id_pelanggan;
Hasilnya
:
+--------------+-----------------+----------+------------+
|
id_pelanggan | nm_pelanggan | id_pesan
| tgl_pesan |
+--------------+-----------------+----------+------------+
|
P0001 | Achmad Solichin | 1 | 2008-02-02 |
|
P0001 | Achmad Solichin | 5 | 2007-12-14 |
|
P0002 | Budianto |
2 | 2008-02-05 |
|
P0002 | Budianto |
3 | 2008-02-10 |
|
P0004 | Amin Riyadi |
4 | 2008-01-20 |
+--------------+-----------------+----------+------------+
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:
SELECT
pelanggan.id_pelanggan, pelanggan.nm_pelanggan,
pesan.id_pesan,
pesan.tgl_pesan
FROM
pelanggan LEFT JOIN pesan
ON
pelanggan.id_pelanggan=pesan.id_pelanggan;
Hasilnya :
+--------------+-----------------+----------+------------+
|
id_pelanggan | nm_pelanggan | id_pesan
| tgl_pesan |
+--------------+-----------------+----------+------------+
|
P0001 | Achmad Solichin | 1 | 2008-02-02 |
|
P0001 | Achmad Solichin | 5 | 2007-12-14 |
|
P0002 | Budianto |
2 | 2008-02-05 |
|
P0002 | Budianto |
3 | 2008-02-10 |
|
P0003 | Hasan |
NULL | NULL |
| P0004 | Amin Riyadi |
4 | 2008-01-20 |
+--------------+-----------------+----------+------------+
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:
SELECT
pelanggan.id_pelanggan, pelanggan.nm_pelanggan
pesan.id_pesan,
pesan.tgl_pesan
FROM
pelanggan RIGHT JOIN pesan
ON
pelanggan.id_pelanggan=pesan.id_pelanggan;
Hasilnya :
+--------------+-----------------+----------+------------+
|
id_pelanggan | nm_pelanggan | id_pesan
| tgl_pesan |
+--------------+-----------------+----------+------------+
|
P0001 | Achmad Solichin | 1 | 2008-02-02 |
|
P0002 | Budianto |
2 | 2008-02-05 |
|
P0002 | Budianto |
3 | 2008-02-10 |
|
P0004 | Amin Riyadi |
4 | 2008-01-20 |
| P0001 | Achmad Solichin | 5 | 2007-12-14 |
+--------------+-----------------+----------+------------+
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:
SELECT pesan.id_pesan,
produk.id_produk, produk.nm_produk,
detil_pesan.harga,
detil_pesan.jumlah
FROM
pesan, detil_pesan, produk
WHERE
pesan.id_pesan=detil_pesan.id_pesan AND
detil_pesan.id_produk=produk.id_produk
AND pesan.id_pesan='1'
Hasilnya :
+----------+-----------+------------+-------+--------+
| id_pesan | id_produk |
nm_produk | harga | jumlah |
+----------+-----------+------------+-------+--------+
| 1 | B0001 | Buku Tulis | 2700 |
2 |
| 1 | B0003 | Penggaris | 3000
| 3 |
| 1 | B0004 | Pensil |
2000 | 1 |
+----------+-----------+------------+-------+--------+
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 :
+----------+------------+--------+
| id_pesan |
tgl_pesan | jumlah |
+----------+------------+--------+
| 1 | 2008-02-02 | 2 |
| 1 | 2008-02-02 | 3 |
| 1 | 2008-02-02 | 1 |
| 2 | 2008-02-05 | 1 |
| 2 | 2008-02-05 | 5 |
| 2 | 2008-02-05 | 1 |
| 3 | 2008-02-10 | 5 |
| 4 | 2008-01-20 | 10 |
+----------+------------+--------+
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 :
+----------+------------+--------+
| id_pesan | tgl_pesan | jumlah |
+----------+------------+--------+
| 1 |
2008-02-02 | 6 |
| 2 |
2008-02-05 | 7 |
| 3 |
2008-02-10 | 5 |
| 4 | 2008-01-20 | 10 |
+----------+------------+--------+
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 :
+----------+------------+--------+
| id_pesan | tgl_pesan | jumlah |
+----------+------------+--------+
| 1 |
2008-02-02 | 6 |
| 2 |
2008-02-05 | 7 |
| 3 |
2008-02-10 | 5 |
| 4 |
2008-01-20 | 10 |
| NULL |
2008-01-20 | 28 |
+----------+------------+--------+
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 :
+----------+--------+
| id_pesan | jumlah |
+----------+--------+
| 1
| 3 |
| 2
| 3 |
| 3
| 1 |
| 4
| 1 |
+----------+--------+
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:
+----------+--------+
| id_pesan | jumlah |
+----------+--------+
| 1
| 1 |
| 2
| 1 |
| 3
| 1 |
| 4
| 1 |
+----------+--------+
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
Lihat hasilnya sebagai berikut:
+----------+--------+
| id_pesan | jumlah |
+----------+--------+
| 1
| 3 |
| 2
| 3 |
+----------+--------+
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:
SELECT id_pesan, jumlah FROM detil_pesan
WHERE jumlah = ( SELECT MAX(jumlah) FROM
detil_pesan);
Hasilnya sebagai berikut:
+----------+--------+
| id_pesan | jumlah |
+----------+--------+
| 4
| 10 |
+----------+--------+
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:
+--------------+-----------------+--------------------+
| id_pelanggan |
nm_pelanggan | email |
+--------------+-----------------+--------------------+
| P0004 | Amin Riyadi | aminudin@plasa.com |
| P0001
| Achmad Solichin | achmatim@gmail.com |
|
P0002 | Budianto | budi@luhur.com |
|
P0003 | Hasan | hasan02@yahoo.com |
+--------------+-----------------+--------------------+
Transaksi
MySQL
merupakan software database berbasis client-server. Hal ini berarti bahwa beberapa client dapat melakukan koneksi
ke server MySQL secara bersamaan.
Masing-masing client dapat melakukan select, insert, update, maupun delete data
di server MySQL. Hal ini tentunya dapat menjadi masalah jika terjadi bentrok
antar-client.
Sebagai contoh dalam proses
transaksi pemesanan barang. Jika terdapat 2 (dua) pelanggan melakukan transaksi
pada waktu yang sama, misalnya melakukan pemesanan barang. Keduanya akan
menambahkan data di tabel yang sama dan mungkin saja data yang dimasukkan
tertukar atau tidak valid. Hal
ini tidak akan terjadi jika pada saat satu pelanggan melakukan transaksi,
pelanggan yang lain harus menunggu sampai proses transaksi selesai.
Untuk mengatur proses query yang terjadi dalam
suatu sistem yang memiliki user banyak (multi-user-system), kita dapat
memanfaatkan dua hal di MySQL. Pertama
kita dapat mengunci tabel (table-locking).
Cara ini dapat dilakukan jika tipe tabel yang digunakan adalah MyISAM. Kedua, dapat menggunakan perintah BEGIN,
COMMIT dan ROLLBACK. Cara ini dapat dilakukan jika tipe tabel adalah tabel
transaksi, yaitu InnoDB.
Terdapat 4 (empat) prinsip dasar transaksi yang
biasa disingkat sebagai ACID, yaitu:
Atomicity.
Atom merupakan komponen terkecil dari materi, atau sesuatu yang tidak dapat
dibagi-bagi lagi. Prinsip ini berlaku
pada proses transaksi. Semua proses (perintah) yang ada di dalam satu paket transaksi
harusselesai semua atau tidak selesai sama sekali. Dengan kata lain, dalam satu transaksi tidak boleh
ada proses (perintah) yang gagal dieksekusi.
Consistency.
Bahwa kegagalan satu proses dalam
transaksi tidak akan mempengaruhi
transaksi lainnya.
Isolation. Secara sederhana, bahwa data yang sedang
digunakan dalam satu transaksi, tidak dapat digunakan oleh transaksi lainnya
sebelum seluruh proses transaksi yang pertama selesai.
Durability. Jika sebuah transaksi selesai
dieksekusi, hasilnya tetap tercatat dengan baik.
Mengunci Tabel
Mengunci tabel (table-locking) dapat dilakukan untuk membatasi akses terhadap suatu tabel jika ada user yang sedang
aktif mengakses tabel. Suatu tabel yang sedang terkunci (locked), tidak dapat
diakses dengan bebas oleh user lain. Untuk mengunci tabel di MySQL dapat
menggunakan kata kunci LOCK.
Bentuk umum perintah LOCK :
LOCK TABLE table1 locktype,
table2 locktype, ...;
Dimana tipe lock (locktype) yang dapat dipilih antara lain:
READ. Semua user MySQL dapat membaca
(mengakses) tabel, namun tabel tersebut tidak dapat diubah oleh siapapun,
termasuk user yangmengeksekusi perintah LOCK. Dengan kata lain, tabel bersifat read-only.
READ LOCAL. Sama seperti READ, tetapi perintah INSERT
dapat dilakukan selama tidak merubah record (data) yang ada.
WRITE. User yang aktif
diperbolehkan untuk membaca dan mengubah tabel (read-and-write). User yang lain
tidak dapat mengubah isi tabel, hanya dapat membaca tabel (read-only).
LOW PRIORITY WRITE. Seperti halnya WRITE, hanya saja user lain
dapat melakukan READ LOCK.
Untuk membuka tabel yang
terkunci, gunakan perintah UNLOCK TABLES;
Contoh table-locking:
> LOCK TABLES trans READ, customer WRITE;
> SELECT sum(value) FROM trans WHERE customer_id=
some_id;
> UPDATE customer SET total_value=total
WHERE customer_id=some_id;
> UNLOCK TABLES;
BEGIN, COMMIT dan ROLLBACK
BEGIN atau START TRANSACTION
digunakan untuk memulai transaksi, COMMIT untuk mengakhiri transaksi dan
menyimpan semua perubahan, sedangkan ROLLBACK digunakan untuk menghentikan
proses transaksi dan mengabaikan semua perubahan yang telah dilakukan. Pada
tipe tabel InnoDB, tidak berlaku transaksi didalam transaksi
(nested-transaction), artinya jika perintah BEGIN dieksekusi sebelum transaksi
selesai
dilakuka (perintah COMMIT), maka secara otomatis, perintah COMMIT akan dieksekusi terlebih dahulu.
Perintah transaksi diatur oleh
client. Jika pada saat proses transaksaksi berlangsung, koneksi client dengan
server terputus, maka secara otomatis MySQL akan membatalkan semua perubahan
yang sudah terjadi (seperti halnya mengeksekusi perintah ROLLBACK).
Bentuk umum perintah transaksi
:
BEGIN;
SELECT | INSERT | UPDATE | DELETE;
COMMIT;
Contoh :
> SET AUTOCOMMIT=0;
> BEGIN;
> INSERT INTO pesan VALUES(NULL,'P0001',now());
> SET @id := LAST_INSERT_ID();
> INSERT INTO detil_pesan VALUES
(@id,'B0001','2','2500');
> COMMIT;
0 komentar:
Posting Komentar