Pengenalan Where Clause Di MySQL

Database MySQL

Pada tutorial MySQL kali ini, kita akan belajar tentang Pengenalan Where Clause Di MySQL. Where Clause, dapat Anda gunakan untuk menampilkan data sesuai dengan kondisi yang Anda inginkan.

Misalkan Anda ingin menampilkan data pegawai yang berjenis kelamin laki-laki, data pegawai yang mempunyai gaji diatas 3 juta, dan lain sebagainya.

Sintak Dasar

Di bawah ini adalah sintak dasar where clause di MySQL:

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]

Anda dapat menggunakan operator logika, operator perbandingan, dan operator lainnya pada condition di select statement di atas.

Contoh

Misalkan Anda telah memiliki sebuah table "tbl_pegawai" dengan data-data sebagai berikut:

mysql> select * from tbl_employee;
+-------------+---------------+------------+-------------+------------+------------+--------+
| employee_id | employee_name | birth_date | birth_place | salary     | department | gender |
+-------------+---------------+------------+-------------+------------+------------+--------+
|           1 | Ahmad Sofyan  | 1990-01-01 | Bandung     | 1750000.00 | IT         | L      |
|           2 | Ismail Saleh  | 1986-12-12 | Pandeglang  | 5000000.00 | ACC        | L      |
|           3 | Ferry Subekti | 1987-06-06 | Purwakarta  | 4500000.00 | HRD        | P      |
|           4 | Iskiyati      | 1988-07-07 | Brebes      | 7500000.00 | FIN        | P      |
|           5 | Nurul Hikmah  | 1989-08-08 | Purwokerto  | 3750000.00 | FIN        | P      |
|           6 | Riyanto       | 1983-02-02 | Subang      | 4000000.00 | IT         | L      |
|           7 | Azka Nurul    | 1984-03-03 | Bandung     | 5000000.00 | HRD        | P      |
|           8 | Venny Maya    | 1989-04-04 | Jakarta     | 6500000.00 | IT         | P      |
|           9 | Ana Fauziyah  | 1989-05-05 | Bekasi      | 3700000.00 | ACC        | P      |
|          10 | Arya Adhi     | 1980-09-09 | Magelang    | 8000000.00 | FIN        | P      |
+-------------+---------------+------------+-------------+------------+------------+--------+
10 rows in set (0.08 sec)

Dari data-data diatas,

• Tampilkan semua data pegawai yang memiliki jenis kelamin lak-laki (L)
  
  Untuk menampilkan data sesuai kriteria diatas, Anda dapat menambahkan operator "=" pada where clause seperti di bawah ini.
  
  

  mysql> SELECT * FROM tbl_employee
      -> WHERE gender = 'L';
  +-------------+---------------+------------+-------------+------------+------------+--------+
  | employee_id | employee_name | birth_date | birth_place | salary     | department | gender |
  +-------------+---------------+------------+-------------+------------+------------+--------+
  |           1 | Ahmad Sofyan  | 1990-01-01 | Bandung     | 1750000.00 | IT         | L      |
  |           2 | Ismail Saleh  | 1986-12-12 | Pandeglang  | 5000000.00 | ACC        | L      |
  |           6 | Riyanto       | 1983-02-02 | Subang      | 4000000.00 | IT         | L      |
  +-------------+---------------+------------+-------------+------------+------------+--------+
  3 rows in set (0.00 sec)
  
  

• Tampilkan semua data pegawai yang memiliki gaji lebih besar dari 5 juta
  
  Untuk menampilkan data sesuai kriteria diatas, Anda dapat menambahkan operator ">" pada where clause seperti di bawah ini.
  
  

  mysql> SELECT * FROM tbl_employee
      -> WHERE salary > 5000000;
  +-------------+---------------+------------+-------------+------------+------------+--------+
  | employee_id | employee_name | birth_date | birth_place | salary     | department | gender |
  +-------------+---------------+------------+-------------+------------+------------+--------+
  |           4 | Iskiyati      | 1988-07-07 | Brebes      | 7500000.00 | FIN        | P      |
  |           8 | Venny Maya    | 1989-04-04 | Jakarta     | 6500000.00 | IT         | P      |
  |          10 | Arya Adhi     | 1980-09-09 | Magelang    | 8000000.00 | FIN        | P      |
  +-------------+---------------+------------+-------------+------------+------------+--------+
  3 rows in set (0.00 sec)
  
  
  

• Tampilkan semua data pegawai yang memiliki departemen IT dan FIN
  
  Untuk menampilkan data sesuai kriteria diatas, Anda dapat menambahkan operator "IN" atau "OR" pada where clause seperti di bawah ini.

  mysql> SELECT * FROM tbl_employee
      -> WHERE department IN ('IT','FIN');
  +-------------+---------------+------------+-------------+------------+------------+--------+
  | employee_id | employee_name | birth_date | birth_place | salary     | department | gender |
  +-------------+---------------+------------+-------------+------------+------------+--------+
  |           1 | Ahmad Sofyan  | 1990-01-01 | Bandung     | 1750000.00 | IT         | L      |
  |           4 | Iskiyati      | 1988-07-07 | Brebes      | 7500000.00 | FIN        | P      |
  |           5 | Nurul Hikmah  | 1989-08-08 | Purwokerto  | 3750000.00 | FIN        | P      |
  |           6 | Riyanto       | 1983-02-02 | Subang      | 4000000.00 | IT         | L      |
  |           8 | Venny Maya    | 1989-04-04 | Jakarta     | 6500000.00 | IT         | P      |
  |          10 | Arya Adhi     | 1980-09-09 | Magelang    | 8000000.00 | FIN        | P      |
  +-------------+---------------+------------+-------------+------------+------------+--------+
  6 rows in set (0.00 sec)
  
  mysql> SELECT * FROM tbl_employee
      -> WHERE department = 'IT'
      -> OR department = 'FIN';
  +-------------+---------------+------------+-------------+------------+------------+--------+
  | employee_id | employee_name | birth_date | birth_place | salary     | department | gender |
  +-------------+---------------+------------+-------------+------------+------------+--------+
  |           1 | Ahmad Sofyan  | 1990-01-01 | Bandung     | 1750000.00 | IT         | L      |
  |           4 | Iskiyati      | 1988-07-07 | Brebes      | 7500000.00 | FIN        | P      |
  |           5 | Nurul Hikmah  | 1989-08-08 | Purwokerto  | 3750000.00 | FIN        | P      |
  |           6 | Riyanto       | 1983-02-02 | Subang      | 4000000.00 | IT         | L      |
  |           8 | Venny Maya    | 1989-04-04 | Jakarta     | 6500000.00 | IT         | P      |
  |          10 | Arya Adhi     | 1980-09-09 | Magelang    | 8000000.00 | FIN        | P      |
  +-------------+---------------+------------+-------------+------------+------------+--------+
  6 rows in set (0.00 sec)
  
  

Sekian tutorial singkat tentang Pengenalan Where Clause Di MySQL. Semoga bermanfaat & Happy Learning MySQL Database.

Salam,

Nursalim

Bagikan

Jangan lewatkan

Pengenalan Where Clause Di MySQL

4/ 5

Oleh Kris Kimcil

Kris Kimcil 01.57 Komentar