Operator Relasi
Yaitu operator yang digunakan untuk menbandingkan antara dua buah nilai.
Operator | Keterangan |
= | Sama dengan |
> | Lebih besar |
< | Lebih kecil |
>= | Lebih besar sama dengan |
<= | Lebih kecil sama dengan |
<> | Tidak sama dengan |
Operator Logika
Digunakan untuk operasi logika
Operator | Keterangan |
NOT atau ! | Negasi atau kebalikan |
OR atau || | Atau |
AND atau && | Dan |
Contoh :
Operator | Keterangan |
BETWEEN | Apakah suatu nilai diantara dua batasan ? |
NOT BETWEEN | Apakah suatu nilai tidak diantara dua batasan ? |
IN | Apakah suatu nilai berada didalam pilihan yang ada ? |
NOT IN | Apakah suatu nilai tidak berada didalam pilihan yang ada ? |
IS NULL | Apakah sebuah nilai adalah NULL ? |
IS NOT NULL | Apakah sebuah nilai adalah tidak NULL ? |
LIKE | Apakah sebuah nilai sesuai dengan kriteria tertentu ? |
NOT LIKE | Apakah suatu nilai tidak sesuai dengan kriteria terterntu ? |
Contoh :
mysql> select * from barang;
+----------+-------------+----------------+--------+
| kodebrg | namabrg | satuan | Harga|
+----------+-------------+----------------+--------+
| BR001 | Rokok | Bungkus | 11000 |
| BR002 | Gula | Kg | 10000 |
| BR003 | Beras | Kg | 9000 |
| BR004 | Pepsodent | Buah | 3000 |
| BR005 | Buku | Buah | 6000 |
| BR006 | Sardent | Kaleng | 7000 |
| BR007 | Sampo | Shaset | 500 |
| BR008 | Rinso | Kg | 8000 |
| BR009 | Telur | Butir | 1000 |
| BR010 | Polpen | Buah | 4500 |
| BR011 | Teh Botol | Botol | 2000 |
| BR012 | Coca Cola | Botol | 2500 |
| BR013 | Kecap | Botol | 6000 |
| BR014 | Unibis | Bungkus | 7000 |
| BR015 | Indomie | Bungkus | 1000 |
+----------+-------------+----------------+--------+
15 rows in set (0.00 sec)
mysql> select * from barang where harga between 5000 and 10000;
+---------+--------------+----------------+--------+
| kodebrg | namabrg | satuan | Harga |
+---------+--------------+----------------+--------+
| BR002 | Gula | Kg | 10000|
| BR003 | Beras | Kg | 9000 |
| BR005 | Buku | Buah | 6000 |
| BR006 | Sardent | Kaleng | 7000 |
| BR008 | Rinso | Kg | 8000 |
| BR013 | Kecap | Botol | 6000 |
| BR014 | Unibis | Bungkus | 7000|
+----------+-------------+----------------+--------+
7 rows in set (0.47 sec)
mysql> select * from barang where harga not between 5000 and 10000;
+----------+-------------+----------------+--------+
| kodebrg | namabrg | satuan | Harga |
+----------+-------------+----------------+--------+
| BR001 | Rokok | Bungkus | 11000 |
| BR004 | Pepsodent | Buah | 3000 |
| BR007 | Sampo | Shaset | 500 |
| BR009 | Telur | Butir | 1000 |
| BR010 | Polpen | Buah | 4500 |
| BR011 | Teh Botol | Botol | 2000 |
| BR012 | Coca Cola |Botol | 2500 |
| BR015 | Indomie |Bungkus | 1000 |
+---------+--------------+----------------+--------+
8 rows in set (0.00 sec)
mysql> select * from barang where satuan in('Botol','Buah');
+---------+--------------+----------------+--------+
| kodebrg | namabrg | satuan | Harga |
+---------+--------------+----------------+--------+
| BR004 | Pepsodent | Buah | 3000 |
| BR005 | Buku | Buah | 6000 |
| BR010 | Polpen | Buah | 4500 |
| BR011 | Teh Botol | Botol | 2000 |
| BR012 | Coca Cola | Botol | 2500 |
| BR013 | Kecap | Botol | 6000 |
+---------+--------------+----------------+--------+
6 rows in set (0.03 sec)
mysql> select * from barang where satuan not in ('Botol','Buah');
+----------+-------------+----------------+--------+
| kodebrg | namabrg | satuan | Harga |
+----------+-------------+----------------+--------+
| BR001 | Rokok | Bungkus | 11000|
| BR002 | Gula | Kg | 10000|
| BR003 | Beras | Kg | 9000 |
| BR006 | Sardent | Kaleng | 7000|
| BR007 | Sampo | Shaset | 500 |
| BR008 | Rinso | Kg | 8000 |
| BR009 | Telur | Butir | 1000 |
| BR014 | Unibis | Bungkus | 7000 |
| BR015 | Indomie | Bungkus | 1000 |
+---------+--------------+----------------+--------+
9 rows in set (0.02 sec)
mysql> select * from barang where harga is null;
Empty set (0.34 sec)
mysql> select * from barang where satuan like 'Kg';
+----------+-------------+----------------+--------+
| kodebrg | namabrg | satuan | Harga |
+----------+-------------+----------------+--------+
| BR002 | Gula | Kg | 10000 |
| BR003 | Beras | Kg | 9000 |
| BR008 | Rinso | Kg | 8000 |
+----------+-------------+----------------+--------+
3 rows in set (0.00 sec)
mysql> select * from barang where namabrg like '%e%';
+----------+-------------+----------------+--------+
| kodebrg | namabrg | satuan | Harga |
+----------+-------------+----------------+--------+
| BR003 | Beras | Kg | 9000 |
| BR004 | Pepsodent | Buah | 3000|
| BR006 | Sardent | Kaleng | 7000|
| BR009 | Telur | Butir | 1000|
| BR010 | Polpen | Buah | 4500|
| BR011 | Teh Botol | Botol | 2000|
| BR013 | Kecap | Botol | 6000|
| BR015 | Indomie | Bungkus | 1000|
+---------+--------------+----------------+--------+
8 rows in set (0.00 sec)
mysql> select * from barang where namabrg not like '%e%';
+----------+-------------+----------------+--------+
| kodebrg | namabrg | satuan | Harga |
+----------+-------------+----------------+--------+
| BR001 | Rokok | Bungkus | 11000|
| BR002 | Gula | Kg | 10000|
| BR005 | Buku | Buah | 6000|
| BR007 | Sampo | Shaset | 500 |
| BR008 | Rinso | Kg | 8000|
| BR012 | Coca Cola | Botol | 2500|
| BR014 | Unibis | Bungkus | 7000|
+---------+--------------+----------------+--------+
7 rows in set (0.00 sec)
mysql>
Mengurutkan Data
mysql> select * from barang order by namabrg;
+----------+-------------+----------------+--------+
| kodebrg | namabrg | satuan | Harga |
+----------+-------------+----------------+--------+
| BR003 | Beras | Kg | 9000 |
| BR005 | Buku | Buah | 6000 |
| BR012 | Coca Cola | Botol | 2500 |
| BR002 | Gula | Kg | 10000 |
| BR015 | Indomie | Bungkus | 1000 |
| BR013 | Kecap | Botol | 6000 |
| BR004 | Pepsodent | Buah | 3000 |
| BR010 | Polpen | Buah | 4500|
| BR008 | Rinso | Kg | 8000|
| BR001 | Rokok | Bungkus | 11000|
| BR007 | Sampo | Shaset | 500 |
| BR006 | Sardent | Kaleng | 7000 |
| BR011 | Teh Botol | Botol | 2000 |
| BR009 | Telur | Butir | 1000 |
| BR014 | Unibis | Bungkus | 7000 |
+---------+--------------+----------------+--------+
15 rows in set (0.44 sec)
mysql> select * from barang order by namabrg desc;
+----------+-------------+----------------+--------+
| kodebrg | namabrg | satuan | Harga |
+----------+-------------+----------------+--------+
| BR014 | Unibis | Bungkus | 7000 |
| BR009 | Telur | Butir | 1000 |
| BR011 | Teh Botol | Botol | 2000 |
| BR006 | Sardent | Kaleng | 7000 |
| BR007 | Sampo | Shaset | 500 |
| BR001 | Rokok | Bungkus | 11000 |
| BR008 | Rinso | Kg | 8000 |
| BR010 | Polpen | Buah | 4500 |
| BR004 | Pepsodent | Buah | 3000 |
| BR013 | Kecap | Botol | 6000 |
| BR015 | Indomie | Bungkus | 1000 |
| BR002 | Gula | Kg | 10000|
| BR012 | Coca Cola | Botol | 2500 |
| BR005 | Buku | Buah | 6000 |
| BR003 | Beras | Kg | 9000 |
+---------+--------------+----------------+--------+
15 rows in set (0.00 sec)
mysql> select satuan from barang group by satuan;
+----------------+
| satuan |
+----------------+
| Botol |
| Buah |
| Bungkus |
| Butir |
| Kaleng |
| Kg |
| Shaset |
+----------------+
7 rows in set (0.19 sec)
Membatasi Penampilan Data
mysql> select * from barang limit 4;
+----------+-------------+----------------+--------+
| kodebrg | namabrg | satuan | Harga |
+----------+-------------+----------------+--------+
| BR002 | Gula | Kg | 10000 |
| BR003 | Beras | Kg | 9000 |
| BR004 | Pepsodent | Buah | 3000 |
+----------+-------------+----------------+--------+
4 rows in set (0.00 sec)
mysql> select * from barang limit 3,4;
+----------+-------------+----------------+--------+
| kodebrg | namabrg | satuan | Harga |
+----------+-------------+----------------+--------+
| BR004 | Pepsodent | Buah | 3000 |
| BR005 | Buku | Buah | 6000 |
| BR006 | Sardent | Kaleng | 7000 |
| BR007 | Sampo | Shaset | 500 |
+----------+-------------+----------------+--------+
4 rows in set (0.00 sec)
Latihan :
