MySQL
Showing posts with label MySQL. Show all posts
Showing posts with label MySQL. Show all posts

Pengenalan Operator Logika Pada MySQL

1/01/2016 1 Comment
Pada tutorial kali ini, kita akan belajar tentang macam-macam operator logika pada MySQL beserta contohnya.

Operator Logika Pada MySQL


Macam-Macam Operator Logika


Berikut ini adalah tabel macam-macam operator logika yang terdapat pada MySQL:

Operator Deskripsi
AND Menggunakan logika AND didalam WHERE clause
OR Menggunakan logika OR didalam WHERE clause
IN Akan membandingkan data dengan list yang telah ditentukan
BETWEEN Akan membandingkan data pada range yang telah ditentukan
NOT Menggunakan logika NOT, seperti NOT IN, NOT BETWEEN, NOT LIKE, dan lain sebagainya
LIKE Membandingkan data yang sesuai dengan pola tertentu (wildcard)
IS NULL Membandingkan apakah data sama dengan null?

Contoh


Misalkan Anda memiliki sebuah table "tbl_employee" 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     | 2250000.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      | 4500000.00 | IT         | L      |
|           7 | Azka Nurul    | 1984-03-03 | Bandung     | 5000000.00 | HRD        | P      |
|           8 | Venny Maya    | 1989-04-04 | Jakarta     | 7000000.00 | IT         | P      |
|           9 | Ana Fauziyah  | 1989-05-05 | Bekasi      | 3700000.00 | ACC        | P      |
|          10 | Arya Adhi     | 1980-09-09 | Magelang    | 8000000.00 | FIN        | L      |
+-------------+---------------+------------+-------------+------------+------------+--------+
10 rows in set (0.00 sec)

Dari data diatas,
  • Tampilkan semua data yang memiliki departement 'IT' dan jenis kelamin laki-laki 

    mysql> SELECT * FROM tbl_employee
    -> WHERE department = 'IT'
    -> AND gender = 'L';
+-------------+---------------+------------+-------------+------------+------------+--------+
| employee_id | employee_name | birth_date | birth_place | salary     | department | gender |
+-------------+---------------+------------+-------------+------------+------------+--------+
|           1 | Ahmad Sofyan  | 1990-01-01 | Bandung     | 2250000.00 | IT         | L      |
|           6 | Riyanto       | 1983-02-02 | Subang      | 4500000.00 | IT         | L      |
+-------------+---------------+------------+-------------+------------+------------+--------+
2 rows in set (0.00 sec)
  • Tampilkan semua data yang memiliki departement 'IT' atau jenis kelamin laki-laki 

    mysql> SELECT * FROM tbl_employee
    -> WHERE department = 'IT'
    -> OR gender = 'L';
+-------------+---------------+------------+-------------+------------+------------+--------+
| employee_id | employee_name | birth_date | birth_place | salary     | department | gender |
+-------------+---------------+------------+-------------+------------+------------+--------+
|           1 | Ahmad Sofyan  | 1990-01-01 | Bandung     | 2250000.00 | IT         | L      |
|           2 | Ismail Saleh  | 1986-12-12 | Pandeglang  | 5000000.00 | ACC        | L      |
|           6 | Riyanto       | 1983-02-02 | Subang      | 4500000.00 | IT         | L      |
|           8 | Venny Maya    | 1989-04-04 | Jakarta     | 7000000.00 | IT         | P      |
|          10 | Arya Adhi     | 1980-09-09 | Magelang    | 8000000.00 | FIN        | L      |
+-------------+---------------+------------+-------------+------------+------------+--------+
5 rows in set (0.00 sec)
  • Tampilkan semua data yang memiliki nama dengan huruf depan 'A' 

    mysql> SELECT * FROM tbl_employee
    -> WHERE employee_name like 'A%';
+-------------+---------------+------------+-------------+------------+------------+--------+
| employee_id | employee_name | birth_date | birth_place | salary     | department | gender |
+-------------+---------------+------------+-------------+------------+------------+--------+
|           1 | Ahmad Sofyan  | 1990-01-01 | Bandung     | 2250000.00 | IT         | L      |
|           7 | Azka Nurul    | 1984-03-03 | Bandung     | 5000000.00 | HRD        | P      |
|           9 | Ana Fauziyah  | 1989-05-05 | Bekasi      | 3700000.00 | ACC        | P      |
|          10 | Arya Adhi     | 1980-09-09 | Magelang    | 8000000.00 | FIN        | L      |
+-------------+---------------+------------+-------------+------------+------------+--------+
4 rows in set (0.00 sec)
  • Tampilkan semua data yang memiliki departement di 'IT', 'FIN', dan 'HRD' 

    mysql> SELECT * FROM tbl_employee
    -> WHERE department in ('IT','FIN','HRD');
+-------------+---------------+------------+-------------+------------+------------+--------+
| employee_id | employee_name | birth_date | birth_place | salary     | department | gender |
+-------------+---------------+------------+-------------+------------+------------+--------+
|           1 | Ahmad Sofyan  | 1990-01-01 | Bandung     | 2250000.00 | IT         | 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      | 4500000.00 | IT         | L      |
|           7 | Azka Nurul    | 1984-03-03 | Bandung     | 5000000.00 | HRD        | P      |
|           8 | Venny Maya    | 1989-04-04 | Jakarta     | 7000000.00 | IT         | P      |
|          10 | Arya Adhi     | 1980-09-09 | Magelang    | 8000000.00 | FIN        | L      |
+-------------+---------------+------------+-------------+------------+------------+--------+
8 rows in set (0.00 sec)
  • Tampilkan semua data yang memiliki gaji berkisar antara Rp 4.000.000 sampai dengan Rp 7.000.000 
    mysql> SELECT * FROM tbl_employee
    -> WHERE salary BETWEEN 4000000 AND 7000000;
+-------------+---------------+------------+-------------+------------+------------+--------+
| employee_id | employee_name | birth_date | birth_place | salary     | department | gender |
+-------------+---------------+------------+-------------+------------+------------+--------+
|           2 | Ismail Saleh  | 1986-12-12 | Pandeglang  | 5000000.00 | ACC        | L      |
|           3 | Ferry Subekti | 1987-06-06 | Purwakarta  | 4500000.00 | HRD        | P      |
|           6 | Riyanto       | 1983-02-02 | Subang      | 4500000.00 | IT         | L      |
|           7 | Azka Nurul    | 1984-03-03 | Bandung     | 5000000.00 | HRD        | P      |
|           8 | Venny Maya    | 1989-04-04 | Jakarta     | 7000000.00 | IT         | P      |
+-------------+---------------+------------+-------------+------------+------------+--------+
5 rows in set (0.03 sec)

Sekian tutorial singkat tentang Pengenalan Operator Logika Pada MySQL. Semoga bermanfaat & Happy Learning MySQL Database.

Salam,

Nursalim

Pengenalan Operator Perbandingan Pada MySQL

12/27/2015 Add Comment
Pada tutorial MySQL kali ini, kita akan belajar tentang Pengenalan Operator Perbandingan Pada MySQL.


Operator Perbandingan Pada MySQL

Macam-Macam Operator Perbandingan


Berikut ini tabel macam-macam operator perbandingan pada MySQL:

Operator Deskripsi Contoh
= Operator ini akan membandingkan dua operand A dan B. Jika nilai A sama dengan B, maka akan bernilai true A=B
!= Operator ini akan membandingkan dua operand A dan B. Jika nilai A tidak sama dengan B, maka akan bernilai true A=B
<> Operator ini akan membandingkan dua operand A dan B. Jika nilai A tidak sama dengan B, maka akan bernilai true A<>B
> Operator ini akan membandingkan dua operand A dan B. Jika nilai A lebih besar dari B, maka akan bernilai true A>B
>= Operator ini akan membandingkan dua operand A dan B. Jika nilai A lebih besar atau sama dengan B, maka akan bernilai true A>=B
< Operator ini akan membandingkan dua operand A dan B. Jika nilai A lebih kecil dari B, maka akan bernilai true A<B
<= Operator ini akan membandingkan dua operand A dan B. Jika nilai A lebih kecil atau sama dengan B, maka akan bernilai true A<=B
!> Operator ini akan membandingkan dua operand A dan B. Jika nilai A tidak lebih besar B, maka akan bernilai true A!>B
!< Operator ini akan membandingkan dua operand A dan B. Jika nilai A tidak lebih kecil B, maka akan bernilai true A!<B


Contoh Program


Misalkan Anda telah memiliki sebuah table "tbl_employee" 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     | 2250000.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      | 4500000.00 | IT         | L      |
|           7 | Azka Nurul    | 1984-03-03 | Bandung     | 5000000.00 | HRD        | P      |
|           8 | Venny Maya    | 1989-04-04 | Jakarta     | 7000000.00 | IT         | P      |
|           9 | Ana Fauziyah  | 1989-05-05 | Bekasi      | 3700000.00 | ACC        | P      |
|          10 | Arya Adhi     | 1980-09-09 | Magelang    | 8000000.00 | FIN        | L      |
+-------------+---------------+------------+-------------+------------+------------+--------+
10 rows in set (0.00 sec)
Dari data diatas,
  • Tampilkan semua data yang memiliki jenis kelamin laki-laki (gender) 

    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     | 2250000.00 | IT         | L      |
|           2 | Ismail Saleh  | 1986-12-12 | Pandeglang  | 5000000.00 | ACC        | L      |
|           6 | Riyanto       | 1983-02-02 | Subang      | 4500000.00 | IT         | L      |
|          10 | Arya Adhi     | 1980-09-09 | Magelang    | 8000000.00 | FIN        | L      |
+-------------+---------------+------------+-------------+------------+------------+--------+
4 rows in set (0.00 sec)
  • Tampilkan semua data yang memiliki jenis kelamin bukan laki-laki (gender) 

    mysql> SELECT * FROM tbl_employee
    -> WHERE gender != 'L';
+-------------+---------------+------------+-------------+------------+------------+--------+
| employee_id | employee_name | birth_date | birth_place | salary     | department | gender |
+-------------+---------------+------------+-------------+------------+------------+--------+
|           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      |
|           7 | Azka Nurul    | 1984-03-03 | Bandung     | 5000000.00 | HRD        | P      |
|           8 | Venny Maya    | 1989-04-04 | Jakarta     | 7000000.00 | IT         | P      |
|           9 | Ana Fauziyah  | 1989-05-05 | Bekasi      | 3700000.00 | ACC        | P      |
+-------------+---------------+------------+-------------+------------+------------+--------+
6 rows in set (0.00 sec)
  • Tampilkan semua data yang memiliki gaji kurang dari Rp 5.000.000 

    mysql> SELECT * FROM tbl_employee
    -> WHERE salary < 5000000;
+-------------+---------------+------------+-------------+------------+------------+--------+
| employee_id | employee_name | birth_date | birth_place | salary     | department | gender |
+-------------+---------------+------------+-------------+------------+------------+--------+
|           1 | Ahmad Sofyan  | 1990-01-01 | Bandung     | 2250000.00 | IT         | L      |
|           3 | Ferry Subekti | 1987-06-06 | Purwakarta  | 4500000.00 | HRD        | P      |
|           5 | Nurul Hikmah  | 1989-08-08 | Purwokerto  | 3750000.00 | FIN        | P      |
|           6 | Riyanto       | 1983-02-02 | Subang      | 4500000.00 | IT         | L      |
|           9 | Ana Fauziyah  | 1989-05-05 | Bekasi      | 3700000.00 | ACC        | P      |
+-------------+---------------+------------+-------------+------------+------------+--------+
5 rows in set (0.00 sec)
  • Tampilkan semua data yang memiliki gaji lebih dari Rp 5.000.000 

    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     | 7000000.00 | IT         | P      |
|          10 | Arya Adhi     | 1980-09-09 | Magelang    | 8000000.00 | FIN        | L      |
+-------------+---------------+------------+-------------+------------+------------+--------+
3 rows in set (0.00 sec)

Sekian tutorial singkat tantang Pengenalan Operator Perbandingan Pada MySQL. Semoga bermanfaat & Happy Learning MySQL Database.

Salam,

Nursalim

Pengenalan Operator Aritmatika Pada MySQL

12/25/2015 Add Comment
Pada tutorial MySQL kali ini, kita akan belajar tentang Pengenalan Operator Aritmatika. Operator Aritmatika digunakan untuk melakukan operasi matematika. Operator aritmatika dapat Anda gunakan pada Select statement, Update statement, Insert statement, dan Delete statement.

Operator Aritmatika Di MySQL


Macam-Macam Operator Aritmatika


Berikut ini tabel macam-macam operator aritmatika yang terdapat pada MySQL:

Operator Deskripsi Contoh
+ Operator penambahan 30 + 20
- Operator pengurangan 30 - 20
* Operator perkalian 30 * 20
/ Operator pembagian 30 / 20
% Operator modulus (sisa bagi) 30 % 20


Contoh


Berikut ini contoh implementasi operator aritmatika pada Select statement:

Contoh #1

mysql> SELECT 30+20;
+-------+
| 30+20 |
+-------+
|    50 |
+-------+
1 row in set (0.04 sec)

mysql> SELECT 30-20;
+-------+
| 30-20 |
+-------+
|    10 |
+-------+
1 row in set (0.06 sec)

mysql> SELECT 30*20;
+-------+
| 30*20 |
+-------+
|   600 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT 30/20;
+--------+
| 30/20  |
+--------+
| 1.5000 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT 30%20;
+-------+
| 30%20 |
+-------+
|    10 |
+-------+
1 row in set (0.00 sec)

Contoh #2

Misalkan Anda memiliki sebuah table "tbl_employee" 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.00 sec)
Dari data diatas, untuk pegawai yang berada di department 'IT', tambahkan gajinya sebesar 500 ribu. dan Tampikan kembali data dari table diatas.

Untuk menyelesaikan soal diatas, Anda dapat menggunakan operator aritmatika "+" pada Update statement seperti berikut ini:

mysql> UPDATE tbl_employee
    -> SET salary = salary + 500000
    -> WHERE department = 'IT';
Query OK, 3 rows affected (0.10 sec)
Rows matched: 3  Changed: 3  Warnings: 0



mysql> SELECT * FROM tbl_employee;
+-------------+---------------+------------+-------------+------------+------------+--------+
| employee_id | employee_name | birth_date | birth_place | salary     | department | gender |
+-------------+---------------+------------+-------------+------------+------------+--------+
|           1 | Ahmad Sofyan  | 1990-01-01 | Bandung     | 2250000.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      | 4500000.00 | IT         | L      |
|           7 | Azka Nurul    | 1984-03-03 | Bandung     | 5000000.00 | HRD        | P      |
|           8 | Venny Maya    | 1989-04-04 | Jakarta     | 7000000.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.00 sec)

Sekian tutorial singkat tentang Pengenalan Operator Aritmatika Pada MySQL. Semoga bermanfaat & Happy Learning MySQL Database.

Salam,

Nursalim

Pengenalan Where Clause Di MySQL

12/25/2015 Add Comment
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.

Where Clause Di MySQL


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

Pengenalan Dasar Select Statement Pada MySQL

12/18/2015 Add Comment
Pada tutorial MySQL kali ini, kita akan belajar tentang Dasar Dasar Select Statement Di MySQL. Select statement digunakan untuk menampilkan data (data retrieving) dari sebuah table.

Select Statement In MySQL

Sintak Dasar


Di bawah ini adalah sintak dasar Select statement di MySQL:

SELECT column_name1, column_name2, ..., column_nameN 
  FROM table_name;

Penjelasan sintak:
  • column_name1, column_name2, dan seterusnya adalah kolom-kolom yang akan ditampilkan pada select statement.
  • table_name adalah nama table yang datanya akan ditampilkan.

Sintak diatas digunakan jika Anda ingin menampilkan kolom tertentu saja sesuai dengan kebutuhan. Jika Anda ingin menampilkan semua data di kolom, Anda dapat menggunakan tanda bintang '*' pada select statement seperti sintak dasar berikut ini:

SELECT * FROM table_name;

Contoh


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

+-------------+---------------+------------+-------------+------------+------------+--------+
| 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      |
+-------------+---------------+------------+-------------+------------+------------+--------+
Dari data table diatas,
  • Tampilkan data employee_id, employee_name, dan salary dari table "tbl_employee"

    Jawaban

    mysql> SELECT employee_id, employee_name, salary
    -> FROM tbl_employee;
+-------------+---------------+------------+
| employee_id | employee_name | salary     |
+-------------+---------------+------------+
|           1 | Ahmad Sofyan  | 1750000.00 |
|           2 | Ismail Saleh  | 5000000.00 |
|           3 | Ferry Subekti | 4500000.00 |
|           4 | Iskiyati      | 7500000.00 |
|           5 | Nurul Hikmah  | 3750000.00 |
|           6 | Riyanto       | 4000000.00 |
|           7 | Azka Nurul    | 5000000.00 |
|           8 | Venny Maya    | 6500000.00 |
|           9 | Ana Fauziyah  | 3700000.00 |
|          10 | Arya Adhi     | 8000000.00 |
+-------------+---------------+------------+
10 rows in set (0.00 sec)
  • Tampilkan data employee_name, birth_date, birth_place, dan gender dari table "tbl_employee"

    Jawaban

    mysql> SELECT employee_name, birth_date, birth_place, gender
    -> FROM tbl_employee;
+---------------+------------+-------------+--------+
| employee_name | birth_date | birth_place | gender |
+---------------+------------+-------------+--------+
| Ahmad Sofyan  | 1990-01-01 | Bandung     | L      |
| Ismail Saleh  | 1986-12-12 | Pandeglang  | L      |
| Ferry Subekti | 1987-06-06 | Purwakarta  | P      |
| Iskiyati      | 1988-07-07 | Brebes      | P      |
| Nurul Hikmah  | 1989-08-08 | Purwokerto  | P      |
| Riyanto       | 1983-02-02 | Subang      | L      |
| Azka Nurul    | 1984-03-03 | Bandung     | P      |
| Venny Maya    | 1989-04-04 | Jakarta     | P      |
| Ana Fauziyah  | 1989-05-05 | Bekasi      | P      |
| Arya Adhi     | 1980-09-09 | Magelang    | P      |
+---------------+------------+-------------+--------+
10 rows in set (0.00 sec)
  • Tampilkan semua data dari table "tbl_employee"

    Jawaban

    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.00 sec)

Sekian tutorial singkat tentang Pengenalan Dasar Select Statement Pada MySQL. Semoga bermanfaat & Happy Learning MySQL Database.

Salam,

Nursalim
Subscribe to: Posts (Atom)