Modifikasi Satria fu Simple: Materi 2 dan 3 Praktikum SMBD

MATERI PART 2 – Prak. BASISDATA

TABEL dan MANIPULASI DATA

LABORATORIUM 4 – BASISDATA

MENU PART INI :

1. Tabel dengan PRIMARY KEY

2. Memberikan nilai UNIQUE

3. Parameter IF NOT EXISTS

4. Menyalin tabel dengan statement SELECT

5. Membuat TEMPORARY TABLE

6. Tabel dengan FOREIGN KEY

7. Memodifikasi tabel dengan ALTER TABLE

i. Menambah kolom pada tabel

ii. Memodifikasi nama tabel

iii. Memodifikasi definisi tabel

iv. Mengganti nama kolom

v. Menghapus kolom

vi. Menambah primary key

vii. Menghapus semua data dalam tabel

8. Memasukkan data lebih dari 1 data dengan INSERT

9. INSERT menggunakan data dari tabel lain

10. Mengubah data menggunakan UPDATE

11. Mengganti data dari tabel

12. Menghapus data dari tabel

1. Tabel dengan PRIMARY KEY

PRIMARY KEY

- untuk menjadikan filed kunci agar tidak terjadi duplikasi data

CODE :

mysql> create table t_pegawai(id_peg varchar(8) primary key, nama_peg varchar(50), alamat_peg varchar(50));

Query OK, 0 rows affected (0.07 sec)

LIHAT DESKRIPSINYA…

mysql> desc t_pegawai;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

ISI TABELNYA

mysql> select*from t_pegawai;

+---------+----------+------------+

| id_peg | nama_peg | alamat_peg |

+---------+----------+------------+

| HRD-001 | Chandra | Jakarta |

| HRD-002 | Cindy | Bandung |

+---------+----------+------------+

2 rows in set (0.00 sec)

LIHAT ERRORNYA..

mysql> insert into t_pegawai values('HRD-001','Budi','Jogja');

ERROR 1062 (23000): Duplicate entry 'HRD-001' for key 'PRIMARY'

2. Table dengan nilai UNIQUE

- memberikan batasan bahwa nilai dalam sebuah kolom harus distinct (atau tidak ada data yang nilainya sama)

CODE:

mysql> create table t_parkir(id_parkir varchar(5) primary key, plat_no varchar(12), merk varchar(10), UNIQUE(plat_no));

Query OK, 0 rows affected (0.09 sec)

LIHAT DESKRIPSINYA…

mysql> desc t_parkir;

+-----------+-------------+------+-----+---------+-------+

+-----------+-------------+------+-----+---------+-------+

+-----------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

ISI TABEL

mysql> select*from t_parkir;

+-----------+------------+--------+

| id_parkir | plat_no | merk |

+-----------+------------+--------+

| 11101 | AB-1234-OP | TOYOTA |

| 11102 | B-3454-SDE | BMW |

+-----------+------------+--------+

2 rows in set (0.00 sec)

LIHAT ERRORNYA..

mysql> insert into t_parkir values('11103','AB-1234-OP','JAGUAR');

ERROR 1062 (23000): Duplicate entry 'AB-1234-OP' for key 'plat_no'

3. Parameter IF NOT EXISTS

untuk mengecek apakah sebuah tabel dengan nama yang sama sudah ada atau belum sebelum membuat tabel

mysql> show tables;

+-------------------+

| Tables_in_minggu2 |

+-------------------+

| t_parkir |

| t_pegawai |

+-------------------+

2 rows in set (0.00 sec)

CODE..

mysql> create table IF NOT EXISTS t_pegawai(id_peg varchar(8) primary key, nama_peg varchar(50), alamat_peg varchar(50));

Query OK, 0 rows affected, 1 warning (0.00 sec)

4. Menyalin tabel dengan statement SELECT

Kita juga bisa membuat tabel baru dengan mengkopy isi dari tabel yang sudah ada, caranya

Pastikan kita sudah punya tabel yang sudah ada isinya..

Contoh :

Kita gunakan table t_parkir

mysql> select*from t_parkir;

+-----------+------------+--------+

| id_parkir | plat_no | merk |

+-----------+------------+--------+

| 11101 | AB-1234-OP | TOYOTA |

| 11102 | B-3454-SDE | BMW |

+-----------+------------+--------+

2 rows in set (0.00 sec)

Lalu kita buat tabel baru dengan nama t_parkir_copy dengan mengduplikasi seluruh isi dari tabel t_parkir, caranya :

CODE..

mysql> create table t_parkir_copy as select*from t_parkir;

Query OK, 2 rows affected (0.08 sec)

Records: 2 Duplicates: 0 Warnings: 0

Lalu kita lihat hasil dari tabel t_parkir_copy :

mysql> select*from t_parkir_copy;

+-----------+------------+--------+

| id_parkir | plat_no | merk |

+-----------+------------+--------+

| 11101 | AB-1234-OP | TOYOTA |

| 11102 | B-3454-SDE | BMW |

+-----------+------------+--------+

2 rows in set (0.00 sec)

Kita bisa lihat isi dari tabel t_parkir_copy sama dengan isi tabel dari t_parkir.

5. Membuat TEMPORARY TABLE

Temporary table adalah pembuatan table secara temporary atau sementara, jadi tabel tersebut akan ada sewaktu kita buat hingga kita mematikan SQL kita.

Sebagai contoh kita akan membuat tabel t_temporary

CODE:

mysql> create temporary table t_temporary(id int(8));

Query OK, 0 rows affected (0.09 sec)

Lihat strukturnya..

mysql> desc t_temporary;

+-------+--------+------+-----+---------+-------+

+-------+--------+------+-----+---------+-------+

| id | int(8) | YES | | NULL | |

+-------+--------+------+-----+---------+-------+

1 row in set (0.01 sec)

Jika kita lihat dengan perintah SHOW TABLES maka temporary tabel tidak akan muncul

mysql> show tables;

+-------------------+

| Tables_in_minggu2 |

+-------------------+

| t_parkir |

| t_parkir_copy |

| t_pegawai |

+-------------------+

3 rows in set (0.00 sec)

Tetapi kita bisa menginputkan nilai ke temporary table, contohnya :

mysql> insert into t_temporary values('11100010');

Query OK, 1 row affected (0.03 sec)

mysql> insert into t_temporary values('11100011');

Query OK, 1 row affected (0.02 sec)

mysql> insert into t_temporary values('11100012');

Query OK, 1 row affected (0.02 sec)

mysql> insert into t_temporary values('11100013');

Query OK, 1 row affected (0.01 sec)

mysql> select*from t_temporary;

+----------+

| id |

+----------+

| 11100010 |

| 11100011 |

| 11100012 |

| 11100013 |

+----------+

4 rows in set (0.00 sec)

Tapi jika kita matikan atau tutup MySQL kita maka temporary tabel akan langsung hilang..

mysql> \q

bye

mysql> use minggu2;

Database changed

mysql> select*from t_temporary;

ERROR 1146 (42S02): Table 'minggu2.t_temporary' doesn't exist

6. Tabel dengan FOREIGN KEY

- satu atau beberapa kolom pada table yang merupakan primary key pada table satu namun diletakan pada table dimana tablenya berelasi dengan table dirinya

Misal kita punya dua tabel yang saling berkaitan contoh :

- tabel induk : t_mobil dengan field(id_mobil, type)

- tabel anak : t_stok dengan field(id_stok, id_mobil, stok)

Kita buat dulu tabel induknya :

mysql> create table t_mobil(id_mobil varchar(8) primary key, type varchar(20));

Query OK, 0 rows affected (0.07 sec)

mysql> desc t_mobil;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

Isi tabel :

mysql> select*from t_mobil;

+----------+--------+

| id_mobil | type |

+----------+--------+

| HON-01 | Jazz |

| TOY-01 | Avanza |

| TOY-02 | Innova |

+----------+--------+

3 rows in set (0.00 sec)

Buat tabel anak :

mysql> create table t_stok(id_stok varchar(8) primary key, id_mobil varchar(8), stok int(5), foreign key(id_mobil) references t_mobil(id_mobil));

Query OK, 0 rows affected (0.08 sec)

mysql> desc t_stok;

+----------+------------+------+-----+---------+-------+

+----------+------------+------+-----+---------+-------+

| stok | int(5) | YES | | NULL | |

+----------+------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

ISI TABEL :

mysql> select*from t_stok;

+---------+----------+------+

| id_stok | id_mobil | stok |

+---------+----------+------+

| ST-001 | TOY-01 | 1000 |

| ST-002 | TOY-02 | 521 |

| ST-003 | HON-01 | 875 |

+---------+----------+------+

3 rows in set (0.00 sec)

Kita akan buktikan apakah FOREIGN KEY kita berfungsi dengan baik, kita bisa menggunakan code :

mysql> delete from t_mobil where id_mobil='TOY-01';

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`minggu2`. t_stok`, CONSTRAINT `t_stok_ibfk_1` FOREIGN KEY (`id_mobil`) REFERENCES `t_mobil` (`id_mobil`))

Maksudnya adalah tidak dapat menghapus atau mengupdate kolom pada table induk karena bereferensi pada table t_stok.

Lalu bagaimana solusinya?

Pada MYSQL, kita harus menambahkan perintah ON DELETE [opsi] dan ON UPDATE [opsi]pada table yang mereferensikan foreign key. Opsi pada perintah tersebut jelasnya dibawah ini.

A. RESTRICT

Jika tabel anak berisi nilai dalam kolom yang mengkait yang nilainya sama dengan di kolom terkait pada tabel induk, baris dalam tabel induk tidak bisa dihapus, dan nilai di kolom terkait tidak dapat diupdate. Ini adalah opsi default jika klausa ON DELETE atau ON UPDATE tidak dispesifikasikan.

B. CASCADE

Baris-baris dalam tabel anak yang berisi nilai-nilai yang juga terdapat dalam kolom terkait dari tabel induk dihapus ketika barisbaris yang berkaitan dihapus dari tabel induk. Baris-baris dalam tabel anak yang berisi nilai-nilai yang juga terdapat dalam kolom terkait dari tabel induk diupdate ketika nilai-nilai yang berkaitan diupdate dalam tabel induk.

C. SET NULL

Nilai-nilai dalam kolom yang mengkait dari tabel anak diset ke NULL saat baris-baris dengan data terkait dalam tabel induk dihapus dari tabel induk atau ketika data terkait dalam tabel induk diupdate. Untuk menggunakan opsi ini, semua kolom-kolom yang mengkait dalam tabel anak harus mengijinkan nilai NULL.

D. NO ACTION

Tidak ada aksi yang diambil dalam tabel anak ketika baris-baris dihapus dari tabel induk atau nilai-nilai dalam kolom terkait dalam tabel induk diupdate.

E. SET DEFAULT

Nilai-nilai dalam kolom-kolom yang mengkait dari tabel anak diset ke nilai default mereka ketika baris-baris dihapus dari tabel induk atau kolom terkait dari tabel induk diupdate.

Untuk kali ini kita akan belajar tentang CASCADE, jadi data yang terdapat di tabel induk dan tabel anak akan sama – sama terhapus atau terupdate.

Langkah pertama kita ganti struktur dari tabel anak agar memuat perintah untuk mengaktifkan “fitur” CASCADE

Hapus dulu tabel t_stok.

Mysql> drop table t_stok;

Rubah struktur tabel t_stok

mysql> create table t_stok(id_stok varchar(8) primary key, id_mobil varchar(8), stok int(5), foreign key(id_mobil) references t_mobil(id_mobil) on delete cascade on update cascade);

Query OK, 0 rows affected (0.06 sec)

mysql> desc t_stok;

+----------+------------+------+-----+---------+-------+

+----------+------------+------+-----+---------+-------+

| stok | int(5) | YES | | NULL | |

+----------+------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

Isi tabelnya lagi

mysql> insert into t_stok values('ST-001','TOY-01','1000'),('ST-002','TOY-02','521'),('ST-003','HON-01','875');

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select*from t_stok;

+---------+----------+------+

| id_stok | id_mobil | stok |

+---------+----------+------+

| ST-001 | TOY-01 | 1000 |

| ST-002 | TOY-02 | 521 |

| ST-003 | HON-01 | 875 |

+---------+----------+------+

3 rows in set (0.00 sec)

Setelah itu, kita coba delete lagi :

mysql> delete from t_mobil where id_mobil='TOY-01';

Query OK, 1 row affected (0.01 sec)

Kita lihat hasilnya…

mysql> select*from t_mobil;

+----------+--------+

Jadi setelah kita rubah struktur tabel anak, maka akan bisa ter delete.

Data di tabel anak dan induk akan sama – sama ter hapus atau ter update.

Itulah contoh penggunaan on delete cascade dan on upodate cascade

| id_mobil | type |

+----------+--------+

| HON-01 | Jazz |

| TOY-02 | Innova |

+----------+--------+

2 rows in set (0.01 sec)

mysql> select*from t_stok;

+---------+----------+------+

| id_stok | id_mobil | stok |

+---------+----------+------+

| ST-002 | TOY-02 | 521 |

| ST-003 | HON-01 | 875 |

+---------+----------+------+

2 rows in set (0.00 sec)

7. Memodifikasi Tabel dengan ALTER TABLE

a. Menambah Kolom pada Tabel

Dalam menambah kolom dalam tabel kita bisa menggunakan perintah alter table, contohnya kita akan menambahkan kolom pada tabel t_mobil :

mysql> alter table t_mobil add harga bigint(11);

Query OK, 2 rows affected (0.16 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> desc t_mobil;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

Kita juga bisa mengatur letak kolom yang akan kita buat

mysql> alter table t_mobil add series varchar(8) after type;

Query OK, 2 rows affected (0.12 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> desc t_mobil;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

4 rows in set (0.01 sec)

b. Modifikasi Nama Tabel

Dalam memodifikasi definisi tabel contohnya jika kita ingin mengganti definisi dari t_mobil maka caranya :

mysql> alter table t_mobil rename to t_car;

Query OK, 0 rows affected (0.07 sec)

mysql> desc t_car;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

4 rows in set (0.04 sec)

c. Modifikasi Definisi Tabel

Dalam memodifikasi definisi tabel contohnya jika kita ingin mengganti definisi dari t_car maka caranya :

mysql> alter table t_car modify type varchar(15), modify series varchar(10);

Query OK, 2 rows affected (0.14 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> desc t_car;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

4 rows in set (0.01 sec)

d. Mengganti Nama Kolom

Dalam memodifikasi nama kolom contohnya jika kita ingin mengganti nama kolom dari t_car maka caranya :

mysql> alter table t_car change harga harga_mobil bigint(11);

Query OK, 2 rows affected (0.14 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> desc t_car;

+-------------+-------------+------+-----+---------+-------+

+-------------+-------------+------+-----+---------+-------+

+-------------+-------------+------+-----+---------+-------+

4 rows in set (0.01 sec)

e. Menghapus Kolom

Cara untuk menghapus kolom dengan perintah ALTER TABLE adalah sbb, misal kita akan menghapus salah satu kolom di t_car, maka caranya :

mysql> alter table t_car drop harga_mobil;

Query OK, 2 rows affected (0.12 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> desc t_car;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

f. Menghapus & Menambah Primary Key

Cara untuk menghapus & menambah primary key dengan perintah ALTER TABLE adalah sbb, misal kita akan menghapus & menambah primary key di suatu table, maka caranya :

Hapus primary key

mysql> alter table t_pegawai drop primary key;

Query OK, 0 rows affected (0.17 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc t_pegawai;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

Tambah primary key

mysql> alter table t_pegawai add primary key (id_peg);

Query OK, 0 rows affected (0.53 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc t_pegawai;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

g. Menghapus Semua Data dalam Tabel

Untuk menghapus seluruh isi dalam tabel menggunakan perintah TRUNCATE

Misal kita akan menghapus seluruh table dari tabel t_parkir_copy maka sytxnya sbb:

mysql> select*from t_parkir_copy;

+-----------+------------+--------+

| id_parkir | plat_no | merk |

+-----------+------------+--------+

| 11101 | AB-1234-OP | TOYOTA |

| 11102 | B-3454-SDE | BMW |

+-----------+------------+--------+

2 rows in set (0.03 sec)

Menggunakan perintah TRUNCATE dalam menghapus seluruh ISI tabel

mysql> truncate table t_parkir_copy;

Query OK, 0 rows affected (0.05 sec)

mysql> select*from t_parkir_copy;

Empty set (0.00 sec)

8. Memasukkan data lebih dari 1 data dengan INSERT

Perintah INSERT dalam memasukkan data juga melayani pemasukkan data lebih dari 1 data sekaligus, misal kita akan memasukkan data di table t_parkir maka syntax yang digunakan sbb:

mysql> insert into t_parkir values('11103','AB-5555-UX','HONDA'), ('11104','BH-36-B','SUZUKI'),('11105','N-7876-OK','KIA');

Query OK, 3 rows affected (0.05 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select*from t_parkir;

+-----------+------------+--------+

| id_parkir | plat_no | merk |

+-----------+------------+--------+

| 11101 | AB-1234-OP | TOYOTA |

| 11102 | B-3454-SDE | BMW |

| 11103 | AB-5555-UX | HONDA |

| 11104 | BH-36-B | SUZUKI |

| 11105 | N-7876-OK | KIA |

+-----------+------------+--------+

5 rows in set (0.00 sec)

9. INSERT Menggunakan Data dari Tabel Lain

Perintah ini mirip dengan perintah copy tabel SELECT tapi yang membedakan perintah ini digunakan setelah terdapat tabel yang akan digunakan untuk menyimpan hasil copyan file dan bisa diatur seluruh field atau sebagian field, syntxnya adalah sbb :

Kita siapkan dulu tabel baru, bernama table t_user

mysql> create table t_user(no_plat varchar(10), merk_kend varchar(20));

Query OK, 0 rows affected (0.05 sec)

mysql> desc t_user;

+-----------+-------------+------+-----+---------+-------+

+-----------+-------------+------+-----+---------+-------+

+-----------+-------------+------+-----+---------+-------+

Lalu kita isi t_user dengan sebagian tabel dari t_parkir

mysql> insert into t_user(no_plat, merk_kend) select plat_no, merk from t_parkir;

Query OK, 5 rows affected (0.05 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select*from t_user;

+------------+-----------+

| no_plat | merk_kend |

+------------+-----------+

| AB-1234-OP | TOYOTA |

| B-3454-SDE | BMW |

| AB-5555-UX | HONDA |

| BH-36-B | SUZUKI |

| N-7876-OK | KIA |

+------------+-----------+

5 rows in set (0.00 sec)

10. Mengubah data menggunakan UPDATE

Mengubah data dalam suatu tabel bisa dilakukan juga tanpa menggunakan perintah ALTER TABLE yaitu menggunakan perintah UPDATE, contohnya

mysql> update t_user set merk_kend='NISSAN' where no_plat='B-3454-SDE';

Query OK, 1 row affected (0.09 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select*from t_user;

+------------+-----------+

| no_plat | merk_kend |

+------------+-----------+

| AB-1234-OP | TOYOTA |

| B-3454-SDE | NISSAN |

| AB-5555-UX | HONDA |

| BH-36-B | SUZUKI |

| N-7876-OK | KIA |

+------------+-----------+

5 rows in set (0.00 sec)

11. Mengganti data dari tabel

Kali ini cara mengganti data tabel tapi menggunakan perintah REPLACE, yang membedakan perintah ini dengan yang lain adalah, jika REPLACE digunakan pada tabel yang memiliki PRIMARY KEY atau nilai UNIQUE

Contoh :

mysql> replace into t_parkir( id_parkir, plat_no , merk) values ('11104','BH-36-B','ISUZU');

Query OK, 2 rows affected (0.03 sec)

mysql> select*from t_parkir;

+-----------+------------+--------+

| id_parkir | plat_no | merk |

+-----------+------------+--------+

| 11101 | AB-1234-OP | TOYOTA |

| 11102 | B-3454-SDE | BMW |

| 11103 | AB-5555-UX | HONDA |

| 11104 | BH-36-B | ISUZU |

| 11105 | N-7876-OK | KIA |

+-----------+------------+--------+

5 rows in set (0.00 sec)

12. Menghapus data dari tabel

Menghapus data dalam basisdata bisa menggunakan perintah DELETE,

Contoh :

mysql> delete from t_parkir where id_parkir='11105';

Query OK, 1 row affected (0.16 sec)

mysql> select*from t_parkir;

+-----------+------------+--------+

| id_parkir | plat_no | merk |

+-----------+------------+--------+

| 11101 | AB-1234-OP | TOYOTA |

| 11102 | B-3454-SDE | BMW |

| 11103 | AB-5555-UX | HONDA |

| 11104 | BH-36-B | ISUZU |

+-----------+------------+--------+

4 rows in set (0.00 sec)

MATERI PART 3 – Prak. BASISDATA

Q U E R Y

LABORATORIUM 4 – BASISDATA

MENU MINGGU INI

I. SELECT Statement

1. Mengambil data dengan SELECT

2. Query menggunakan parameter kondisi WHERE

3. Query menggunakan beberapa parameter kondisional

4. Memberikan alias hasil query pada SELECT

5. Query data bertipe teks dengan pattern matching

6. Query data unik menggunakan DISTINCT

7. Membatasi hasil query dengan LIMIT

8. Mengelompokkan hasil query menggunakan GROUP BY

9. Mendapatkan jumlah anggota setiap kelompok menggunakan COUNT()

10. Parameter kondisional dengan HAVING

11. Mengurutkan hasil query menggunakan ORDER BY

12. Mengurutkan hasil query berdasarkan lebih dari satu kolom

13. Kombinasi ORDER BY dengan LIMIT

14. Operator BETWEEN

15. MAX, MIN, AVERAGE data dari tabel

II. SUB QUERY

1. Sub query dengan ALL

2. Sub query dengan ANY

3. Sub query dengan EXISTS

4. Sub query dengan IN

III. Table Join

1. Cross Join

2. Equi-Join atau Inner Join

3. Natural Join

4. Left Join dan Right Join

5. Update menggunakan Join Table

6. Delete menggunakan join table

SELECT Statement

Sebelum kita memulai pembahsan dengan SELECT terlebih dahulu kita siapkan tabel baru bernama table pegawai.

mysql> create table pegawai(nip varchar(8) primary key, nama_peg varchar(50), al

amat_peg varchar(50), jabatan varchar(20), gaji int(7));

Query OK, 0 rows affected (0.70 sec)

mysql> desc pegawai;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

| nip | varchar(8) | NO | PRI | NULL | |

| gaji | int(7) | YES | | NULL | |

+------------+-------------+------+-----+---------+-------+

5 rows in set (0.13 sec)

Lalu kita isikan dengan data yang dudah tersedia

mysql> load data local infile 'G://LAB4/BASISDATA/file.txt' into table pegawai;

Query OK, 15 rows affected (0.63 sec)

Records: 15 Deleted: 0 Skipped: 0 Warnings: 0

1. Mengambil Data dengan SELECT

Perintah select untuk mengambil data sudah sering kita pakai sebelumnya, untuk mengambil seluruh data yang ada di dalam suatu tabel kita bisa menggunakan perintah SELECT * FROM <nama_tabel>

CONTOH :

mysql> select * from pegawai;

+----------+-----------------------+-------------+----------------+---------+

+----------+-----------------------+-------------+----------------+---------+

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |

+----------+-----------------------+-------------+----------------+---------+

15 rows in set (0.00 sec)

Perintah diatas menunjukan seluruh dara dari table pegawai

Kita juga bisa mengambil seluruh data tapi hanya kolom – kolom tertentu dengan perintah dasar : SELECT <NAMA_KOLOM, NAMA_KOLOM > FROM <NAMA_TABEL>

CONTOH :

mysql> select nip, nama_peg from pegawai;

+----------+-----------------------+

| nip | nama_peg |

+----------+-----------------------+

| PEG-1001 | Soeharto Mangundirejo |

| PEG-1002 | Felix Nababan |

| PEG-1003 | Olga Syahputra |

| PEG-1004 | Chelsea Olivia |

| PEG-1005 | Tuti Wardani |

| PEG-1006 | Budi Drajat |

| PEG-1007 | Bambang Pamungkas |

| PEG-1008 | Ely Oktafiani |

| PEG-1009 | Rani Wijaya |

| PEG-1010 | Rano Karno |

| PEG-1011 | Rahmadi Sholeh |

| PEG-1012 | Ilham Ungara |

| PEG-1013 | Endang Melati |

| PEG-1014 | Donny Damara |

| PEG-1015 | Paijem |

+----------+-----------------------+

15 rows in set (0.00 sec)

2. Query menggunakan parameter kondisi WHERE

Statement WHERE disini akan digunakan untuk memfilter atau mengatur kolom dan baris mana yang akan kita munculkan atau kita ambil.

PERINTAH DASAR :

SELECT * FROM <nama_table> WHERE <kondisi>

CONTOH :

mysql> select * from pegawai where jabatan='Staff Junior';

+----------+----------------+------------+--------------+---------+

+----------+----------------+------------+--------------+---------+

+----------+----------------+------------+--------------+---------+

4 rows in set (0.44 sec)

Perintah diatas menunjukkan data pegawai yang difilter berdasarkan jabatan yg berisi staff junior

mysql> select nip, nama_peg from pegawai where jabatan='Staff Junior';

+----------+----------------+

| nip | nama_peg |

+----------+----------------+

| PEG-1010 | Rano Karno |

| PEG-1011 | Rahmadi Sholeh |

| PEG-1012 | Ilham Ungara |

| PEG-1013 | Endang Melati |

+----------+----------------+

4 rows in set (0.00 sec)

Untuk menampilkan tidak semua data atau hanya data pada kolom – kolom tertentu yg telah dituliskan dan tetap berdasarkan jabatan = junior staff

3. Query menggunakan beberapa parameter kondisional

Perintah yang digunakan disini menggunakan WHERE tapi yang membedakan perintah yang digunakan lebih menunjukkan kondisi yang diinginkan, dengan bisa menggunakan penggabungan beberapa parameter untuk menampilkan data. Penggabungan ini bisa menggunakan perintah AND atau OR, untuk lebih jelasnya bisa dilihat contoh :

mysql> select * from pegawai where alamat_peg ='Yogyakarta' AND gaji < 4000000;

+----------+----------------+------------+----------------+---------+

+----------+----------------+------------+----------------+---------+

+----------+----------------+------------+----------------+---------+

3 rows in set (0.00 sec)

Perintah diatas menunjukkan pemfilteran dengan 2 kondisi yaitu berdasarkan alamat_peg dan gaji yang kurang dari 4jt

mysql> select nip, nama_peg from pegawai where alamat_peg ='Yogyakarta' OR alamat_peg ='Jakarta';

+----------+-----------------------+

| nip | nama_peg |

+----------+-----------------------+

| PEG-1001 | Soeharto Mangundirejo |

| PEG-1003 | Olga Syahputra |

| PEG-1008 | Ely Oktafiani |

| PEG-1011 | Rahmadi Sholeh |

| PEG-1012 | Ilham Ungara |

| PEG-1015 | Paijem |

+----------+-----------------------+

6 rows in set (0.00 sec)

Jika perintah diatas menunjukkan data yang mempunyai alamat di Yogyakarta atau di Jakarta

4. Memberikan alias hasil query pada SELECT

Kita bisa menggunakan perintah SELECT untuk membuat alias pada suatu kolom dengan perintah AS.

mysql> select nama_peg AS nama_pegawai, alamat_peg AS asal from pegawai where alamat_peg='Jakarta';

+----------------+---------+

| nama_pegawai | asal |

+----------------+---------+

| Olga Syahputra | Jakarta |

| Ilham Ungara | Jakarta |

+----------------+---------+

2 rows in set (0.00 sec)

Di sini pada saat menampilkan hasil, nama kolom akan diganti dengan perintah AS

5. Query data bertipe teks dengan pattern matching

Menampilkan data dengan mencocokan (matching) dengan kondisi yang kita inginkan.

Dengan menggunakan perintah ‘LIKE’ untuk perintah ‘seperti’ dan ‘NOT LIKE’ untuk perintah ‘tidak seperti’ dan beberapa simbol matching yg ada di SQL yaitu ‘_’ dan ‘%’ untuk lebih jelasnya bisa lihat di contoh :

mysql> select nama_peg from pegawai where jabatan like 'M______';

+---------------+

| nama_peg |

+---------------+

| Felix Nababan |

+---------------+

1 row in set (0.00 sec)

Pada perintah diatas menunjukkan bahwa data yang diminta adalah data yang memuat Jabatan berawalan M dan berjumlag 7 huruf.

mysql> select nama_peg from pegawai where jabatan like 'M%';

+---------------+

| nama_peg |

+---------------+

| Felix Nababan |

+---------------+

1 row in set (0.00 sec)

Perintah di atas menunjukkan data yang diminta adalah data nama pegawai yang jabatannya berawalan huruf ‘M’

mysql> select nama_peg from pegawai where alamat_peg like '%A';

+-----------------------+

| nama_peg |

+-----------------------+

| Soeharto Mangundirejo |

| Olga Syahputra |

| Ely Oktafiani |

| Rahmadi Sholeh |

| Ilham Ungara |

| Paijem |

+-----------------------+

6 rows in set (0.00 sec)

Perintah di atas menunjukkan data yang diminta adalah data nama pegawai yang alamatnya berakhiran huruf ‘A’

6. Query data unik menggunakan DISTINCT

Perintah DISTINCT digunakan untuk menampilkan data yang unik, jadi jika ada data yang sama tidak akan ditampilkan.

mysql> select alamat_peg from pegawai;

+-------------+

| alamat_peg |

+-------------+

| Yogyakarta |

| Medan |

| Jakarta |

| Bandung |

| Jawa Tengah |

| Malang |

| Kudus |

| Yogyakarta |

| Magelang |

| Solo |

| Yogyakarta |

| Jakarta |

| Madiun |

| Makasar |

| Yogyakarta |

+-------------+

15 rows in set (0.00 sec)

mysql> select distinct alamat_peg from pegawai;

+-------------+

| alamat_peg |

+-------------+

| Yogyakarta |

| Medan |

| Jakarta |

| Bandung |

| Jawa Tengah |

| Malang |

| Kudus |

| Magelang |

| Solo |

| Madiun |

| Makasar |

+-------------+

11 rows in set (0.05 sec)

7. Membatasi hasil query dengan LIMIT

Perintah LIMIT ini akan membatasi jumlah data yang akan kita tampilkan.

mysql> select * from pegawai LIMIT 5;

+----------+-----------------------+-------------+-------------+---------+

+----------+-----------------------+-------------+-------------+---------+

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |

+----------+-----------------------+-------------+-------------+---------+

5 rows in set (0.00 sec)

8. Mengelompokkan hasil query menggunakan GROUP BY

Penampilan data dikelompokkan dengan GROUP BY, data yang ditampilkan adalah dta pertama dari kolom yang ditunjuk sebagai parameter GROP BY.

CONTOH :

mysql> select * from pegawai GROUP BY gaji;

+----------+-----------------------+-------------+----------------+---------+

+----------+-----------------------+-------------+----------------+---------+

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |

+----------+-----------------------+-------------+----------------+---------+

8 rows in set (0.41 sec)

Menunjukkan pengngelompokan data berdasarkan gaji, jika ada yang memiliki gaji yang sama maka yang ditampilkan adalah data yang pertama, contoh Rano Karno dan Rahmadi Sholeh sama – sama memiliki gaji 2juta tetapi karena Rano Karno ada diatas Rahmadi maka yang ditampilkan adalah data dari Rano Karno.

9. Mendapatkan jumlah anggota setiap kelompok menggunakan COUNT()

Perintah COUNT() ini berkaitan dengan perintah GROUP BY, yaitu untuk mengetahui jumlah dari anggota GROUP, untuk lebih jelasnya bisa melihat contoh :

mysql> select gaji, count(*) FROM pegawai GROUP BY gaji;

+---------+----------+

| gaji | count(*) |

+---------+----------+

| 500000 | 1 |

| 1000000 | 1 |

| 2000000 | 4 |

| 3000000 | 3 |

| 4500000 | 2 |

| 6000000 | 2 |

| 8000000 | 1 |

| 9000000 | 1 |

+---------+----------+

8 rows in set (0.44 sec)

10.Parameter kondisional dengan HAVING

Penggunaan HAVING sangat mirip dengan penggunaan WHERE contoh :

mysql> select * from pegawai HAVING gaji > 6000000;

+----------+-----------------------+------------+---------+---------+

+----------+-----------------------+------------+---------+---------+

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |

+----------+-----------------------+------------+---------+---------+

2 rows in set (0.00 sec)

Dari contoh diatas bisa terlihat jelas penggunaan HAVING dan WHERE sangat mirip

11.Mengurutkan hasil query menggunakan ORDER BY

Perintah ORDER BY adalah perintah untuk mengurutkan tabel secara ASC atau DESC,

CONTOH :

mysql> select * from pegawai ORDER BY nama_peg;

+----------+-----------------------+-------------+----------------+---------+

+----------+-----------------------+-------------+----------------+---------+

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |

+----------+-----------------------+-------------+----------------+---------+

15 rows in set (0.42 sec)

Jika dilihat dari perintah diatas menunjukkan bahwa perintah ORDER BY akan diurutkan secara ASC secara default.

Untuk perintah ORDER BY secara DESC maka perintahnya sbb :

mysql> select * from pegawai ORDER BY nama_peg DESC;

+----------+-----------------------+-------------+----------------+---------+

+----------+-----------------------+-------------+----------------+---------+

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |

+----------+-----------------------+-------------+----------------+---------+

15 rows in set (0.00 sec)

12.Mengurutkan hasil query berdasarkan lebih dari satu kolom

Pada perintah ini pengurutan data dilakukan tidak hanya berdasar 1 kolom, tetapi berdasar 2 kolom atau lebihpun bisa.

Agar data bisa terlihat jelas maka coba buat tabel pegawai2 dengan rincian sbb :

mysql> create table pegawai2(nama varchar(20), alamat varchar(20));

Query OK, 0 rows affected (0.47 sec)

mysql> desc pegawai2;

+--------+-------------+------+-----+---------+-------+

+--------+-------------+------+-----+---------+-------+

+--------+-------------+------+-----+---------+-------+

2 rows in set (0.11 sec)

mysql> select*from pegawai2;

+------+------------+

| nama | alamat |

+------+------------+

| Zeze | Yogyakarta |

| Zeze | Jakarta |

| Riza | Bandung |

| Riza | Aceh |

| Amir | Demak |

| Amir | Cilacap |

+------+------------+

6 rows in set (0.00 sec)

Kita sudah punya tabel baru yang menunjukkan data pada kolom nama ada yang sama, tapi di kolom alamat berbeda maka bisa diurutkan sbb :

mysql> select nama, alamat from pegawai2 ORDER BY nama, alamat;

+------+------------+

| nama | alamat |

+------+------------+

| Amir | Cilacap |

| Amir | Demak |

| Riza | Aceh |

| Riza | Bandung |

| Zeze | Jakarta |

| Zeze | Yogyakarta |

+------+------------+

6 rows in set (0.00 sec)

mysql> select * from pegawai2 ORDER BY nama, alamat desc;

+------+------------+

| nama | alamat |

+------+------------+

| Amir | Demak |

| Amir | Cilacap |

| Riza | Bandung |

| Riza | Aceh |

| Zeze | Yogyakarta |

| Zeze | Jakarta |

+------+------------+

6 rows in set (0.00 sec)

Pada tabel ini telah diurutkan nama terlebih dahulu seteleh itu baru mengurutkan berdasarkan alamat.

13.Kombinasi ORDER BY dengan LIMIT

Perintah ORDER BY juga bisa dikombinasikan dengan perintah LIMIT, contohnya

mysql> select * from pegawai ORDER BY gaji LIMIT 5;

+----------+----------------+------------+----------------+---------+

+----------+----------------+------------+----------------+---------+

+----------+----------------+------------+----------------+---------+

5 rows in set (0.00 sec)

Contoh diatas menunjukkan 5 gaji terendah di table pegawai.

14.Operator BETWEEN

Perintah BETWEEN digunakan untuk memfilter data diantara dua nilai yang dispesifikkan

mysql> select * from pegawai WHERE gaji BETWEEN 4000000 AND 9000000;

+----------+-----------------------+-------------+-------------+---------+

+----------+-----------------------+-------------+-------------+---------+

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |

+----------+-----------------------+-------------+-------------+---------+

6 rows in set (0.00 sec)

Perintah diatas menunjukkan penampilan gaji diantara 4jt dan 9jt .

Jika perintah BETWEEN dilakukan untuk mencara data dalam range, ada juga NOT BETWEEN untuk mencarai data diluar range yang kita tentukan.

CATATAN : penginputan range diwalai dengan urutan yang lebih kecil, contoh 4jt – 9jt, atau Ely O – Olga S

mysql> select nama_peg, gaji from pegawai WHERE nama_peg NOT BETWEEN "Ely Oktafi

ani" AND "Olga Syahputra";

+-----------------------+---------+

| nama_peg | gaji |

+-----------------------+---------+

| Soeharto Mangundirejo | 9000000 |

| Chelsea Olivia | 6000000 |

| Tuti Wardani | 4500000 |

| Budi Drajat | 4500000 |

| Bambang Pamungkas | 3000000 |

| Rani Wijaya | 3000000 |

| Rano Karno | 2000000 |

| Rahmadi Sholeh | 2000000 |

| Donny Damara | 1000000 |

| Paijem | 500000 |

+-----------------------+---------+

10 rows in set (0.00 sec)

Menunjukkan hasil dari NOT BETWEEN dalam menampilkan data yang bukan antara Ely Oktafiani dan Olga Syahputra

15.MAX, MIN, AVERAGE data dari tabel

Perintah untuk menunjukkan nilai maksimal atau minimal ataupun nilai rata – rata juga bisa kita lakukan di MySQL, dengan perintah SELECT diikuti oleh MIN, MAX, atau AVERAGE, untuk lebih jelasnya kita lihat contoh :

Mencari nilai MINIMAL è MIN

mysql> select MIN(gaji) from pegawai;

+-----------+

| MIN(gaji) |

+-----------+

| 500000 |

+-----------+

1 row in set (1.05 sec)

Mencari nilai MAKSIMAL è MAX

mysql> select MAX(gaji) from pegawai;

+-----------+

| MAX(gaji) |

+-----------+

| 9000000 |

+-----------+

1 row in set (0.00 sec)

Mencari nilai RATA - RATA è AVG

mysql> select AVG(gaji) from pegawai;

+--------------+

| AVG(gaji) |

+--------------+

| 3766666.6667 |

+--------------+

1 row in set (0.41 sec)

Menghitung Nilai Total è SUM

mysql> select SUM(gaji) from pegawai;

+-----------+

| SUM(gaji) |

+-----------+

| 56500000 |

+-----------+

1 row in set (0.39 sec)

SUB QUERY

Sub Query adalah penggunaan statement SELECT di dalam statement SELECT, Sub Query dideklarasikan di dalam perintah my qsl di dalam tanda (), dan biasa menggunakan salah satu dari statemen SELECT, UPDATE, SET, DELETE, atau DO

CONTOH :

mysql> select nama_peg, gaji from pegawai where gaji = (select max(gaji) from pe

gawai);

+-----------------------+---------+

| nama_peg | gaji |

+-----------------------+---------+

| Soeharto Mangundirejo | 9000000 |

+-----------------------+---------+

1 row in set (0.06 sec)

Perintah ini hanya bisa menerima satu(1) buah hasil dari sub query, jika hasil dari sub query ada lebih adari satu maka akan terjadi error.

mysql> select nama_peg, gaji from pegawai where alamat_peg = (select alamat_peg

from pegawai where alamat_peg LIKE 'J%');

ERROR 1242 (21000): Subquery returns more than 1 row

Perintah diatas ini ERROR karena hasil sub query diatas ada lebih dari satu(1)

1. Sub query dengan ALL

Command ALL diikuti dengan operator perbandingan digunakan memiliki arti menampilkan nilai jika perbandingan bernilai benar untuk semua data. Berikut adalah contoh penggunaannya.

mysql> select nama_peg, gaji from pegawai where gaji < ALL(select gaji FROM pega

wai WHERE gaji > '2500000');

+----------------+---------+

| nama_peg | gaji |

+----------------+---------+

| Rano Karno | 2000000 |

| Rahmadi Sholeh | 2000000 |

| Ilham Ungara | 2000000 |

| Endang Melati | 2000000 |

| Donny Damara | 1000000 |

| Paijem | 500000 |

+----------------+---------+

6 rows in set (0.00 sec)

Contoh diatas menunjukkan penampilan atas nama pegawai beserta gajinya yang memiliki gaji kurang dari 2,5jt

2. Sub query dengan ANY

Command ANY diikuti dengan operator perbandingan memiliki arti menampilkan nilai yang sesuai dengan apapun yang dihasilkan oleh sub query. Alias dari ANY adalah SOME. Berikut adalah contohnya:

mysql> select nama_peg, gaji from pegawai where gaji < ANY(select gaji FROM pega

wai WHERE gaji > '2000000');

+-------------------+---------+

| nama_peg | gaji |

+-------------------+---------+

| Felix Nababan | 8000000 |

| Olga Syahputra | 6000000 |

| Chelsea Olivia | 6000000 |

| Tuti Wardani | 4500000 |

| Budi Drajat | 4500000 |

| Bambang Pamungkas | 3000000 |

| Ely Oktafiani | 3000000 |

| Rani Wijaya | 3000000 |

| Rano Karno | 2000000 |

| Rahmadi Sholeh | 2000000 |

| Ilham Ungara | 2000000 |

| Endang Melati | 2000000 |

| Donny Damara | 1000000 |

| Paijem | 500000 |

+-------------------+---------+

14 rows in set (0.00 sec)

Pada perintah diatas kita perhatikan perintah select yang berada di dalam kurung (Sub Query), pada perintah itu menjelaskan bahwa penampilan daftar nama pegawai yang gajinya lebih dari 2jt, sedangkan Quert seutuhnya menampilkan nama pegawai yang gajinya lebih kecil daripada hasil sub query, Soeharto Mangundirejo tidak keluar di hasil karena tidak ada yang mempunyai gaji sebesar dirinya.

mysql> select nama_peg, gaji from pegawai where gaji < SOME(select gaji FROM peg

awai WHERE gaji > '2000000');

+-------------------+---------+

| nama_peg | gaji |

+-------------------+---------+

| Felix Nababan | 8000000 |

| Olga Syahputra | 6000000 |

| Chelsea Olivia | 6000000 |

| Tuti Wardani | 4500000 |

| Budi Drajat | 4500000 |

| Bambang Pamungkas | 3000000 |

| Ely Oktafiani | 3000000 |

| Rani Wijaya | 3000000 |

| Rano Karno | 2000000 |

| Rahmadi Sholeh | 2000000 |

| Ilham Ungara | 2000000 |

| Endang Melati | 2000000 |

| Donny Damara | 1000000 |

| Paijem | 500000 |

+-------------------+---------+

14 rows in set (0.45 sec)

3. Sub query dengan EXISTS

Perintah EXISTS disini berguna untuk mengartur penampilan hasil query, Query Utama akan dijalankan jika Sub Query bernilai TRUE (ada hasilnya) jika hasilnya kosong maka Query utama tidak akan dijalankan. Lawan dari statement EXISTS adalah NOT EXISTS

mysql> select * from pegawai where EXISTS (select * from pegawai where alamat_pe

g = 'Yogyakarta');

+----------+-----------------------+-------------+----------------+---------+

+----------+-----------------------+-------------+----------------+---------+

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |

+----------+-----------------------+-------------+----------------+---------+

15 rows in set (0.00 sec)

Dari perintah diatas menunjukkan bahwa Query utama dieksesusi karena Sub Query bernilai TRUE atau ada hasilnya

mysql> select * from pegawai where EXISTS (select * from pegawai where alamat_pe

g = 'Kebumen');

Empty set (0.00 sec)

Sedangkan perintah ini tidak dijalankan karena Sub Query bernilai FALSE atau tidak ada hasilnya.

Jika kita ganti EXISTS dengan NOT EXISTS maka hasil akan keluar seperti di bawah ini, karena memang alamat-peg tidak ada yang bernilai ‘Kebumen’

mysql> select * from pegawai where NOT EXISTS (select * from pegawai where alama

t_peg = 'Kebumen');

+----------+-----------------------+-------------+----------------+---------+

+----------+-----------------------+-------------+----------------+---------+

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |

+----------+-----------------------+-------------+----------------+---------+

15 rows in set (0.00 sec)

4. Sub query dengan IN

Jika operator ‘=’ hanya digunakan untuk hasil yang tepat satu, maka jika ingin menampilkan yang memiliki hasil lebih dari satu maka menggunakan perintah IN, berikut contohnya :

Kita buat terlebih dahulu sebuah tabel yang menunjukkan hasil yang kita inginkan, contoh tabel Job ini digunakan untuk menampilkan data yang hasil dari sub query lebih dari satu.

mysql> create table job( id int(2) primary key, job varchar(20));

Query OK, 0 rows affected (0.52 sec)

mysql> desc job;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(2) | NO | PRI | NULL | |

| job | varchar(20) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.17 sec)

mysql> select*from job;

+----+----------------+

| id | job |

+----+----------------+

| 1 | Supervisor |

| 2 | Staff Senior |

| 3 | Staff Junior |

| 4 | Tenaga Kontrak |

+----+----------------+

4 rows in set (0.00 sec)

Setelah kita buat table job, maka kita bisa terapkan perintah IN sbb :

mysql> select nip, nama_peg, jabatan from pegawai where jabatan IN (select job f

rom job);

+----------+-------------------+----------------+

| nip | nama_peg | jabatan |

+----------+-------------------+----------------+

| PEG-1005 | Tuti Wardani | Supervisor |

| PEG-1006 | Budi Drajat | Supervisor |

| PEG-1007 | Bambang Pamungkas | Staff Senior |

| PEG-1008 | Ely Oktafiani | Staff Senior |

| PEG-1009 | Rani Wijaya | Staff Senior |

| PEG-1010 | Rano Karno | Staff Junior |

| PEG-1011 | Rahmadi Sholeh | Staff Junior |

| PEG-1012 | Ilham Ungara | Staff Junior |

| PEG-1013 | Endang Melati | Staff Junior |

| PEG-1014 | Donny Damara | Tenaga Kontrak |

| PEG-1015 | Paijem | Tenaga Kontrak |

+----------+-------------------+----------------+

11 rows in set (0.00 sec)

Terlihat di dalam hasil bahwa, data pegawai yang jabatannya ada di dalam tabel job akan ditampilkan, sedangkan yang tidak ada tidak akan ditampilkan.

Table Join

Dalam basis data relasional, kita mengenal relasi antar tabel. Untuk melakukan query terhadap dua atau lebih tabel yang memiliki relasi, kita bisa menggunakan fitur table join di MySQL.

Sebelum kita mulai mempelajari Table Join kita persiapkan dulu Tabel-tabel pendukungnya :

mysql> desc jabatan;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

| id_jabatan | int(2) | NO | PRI | NULL | |

+------------+-------------+------+-----+---------+-------+

2 rows in set (0.22 sec)

mysql> select * from jabatan;

+------------+----------------+

| id_jabatan | nm_jabatan |

+------------+----------------+

| 1 | C.E.O |

| 2 | Manager |

| 3 | kepala Unit |

| 4 | Supervisor |

| 5 | Staff Senior |

| 6 | Staff Junior |

| 7 | Tenaga Kontrak |

+------------+----------------+

7 rows in set (0.00 sec)

Setelah kita punya table jabatan / job, kita buat tabel baru untuk menerapkan join, kita copykan table pegawai ke tabel pegawai_join

mysql> create table pegawai_join AS (select*from pegawai);

Query OK, 15 rows affected (0.45 sec)

Records: 15 Duplicates: 0 Warnings: 0

mysql> select * from pegawai_join;

+----------+-----------------------+-------------+----------------+---------+

+----------+-----------------------+-------------+----------------+---------+

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |

+----------+-----------------------+-------------+----------------+---------+

15 rows in set (0.00 sec)

Untuk merelasikan table pegawai_join dan table jabatan kita tambahkan kolom id_jabatan pada table pegawai_join untuk mereferensi ke dua table.

mysql> alter table pegawai_join ADD id_jabatan int(2) AFTER alamat_peg;

Query OK, 15 rows affected (0.20 sec)

Records: 15 Duplicates: 0 Warnings: 0

mysql> desc pegawai_join;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

| nip | varchar(8) | NO | | NULL | |

| id_jabatan | int(2) | YES | | NULL | |

| gaji | int(7) | YES | | NULL | |

+------------+-------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

Jika kita lihat isi tablenya kolom id_jabatan masih NULL atau kosong karena ke dua table belum berelasi

mysql> select * from pegawai_join;

+----------+-----------------------+-------------+------------+----------------+---------+

+----------+-----------------------+-------------+------------+----------------+---------+

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | NULL | C.E.O | 9000000 |

+----------+-----------------------+-------------+------------+----------------+---------+

15 rows in set (0.00 sec)

Agar data di kolom bisa masuk maka kita relasikan sbb:

Kita gunakan UPDATE untuk mengubah diskripsi dari kolom id_jabatan di table pegawai

UPDATE <nama_table1> , <nama_table2> SET <nama_table1.nama_kolom> = <nama_table1. nama_kolom2> WHERE //data yang sama// <nama_table1.nama_kolom1> = <nama_table2.nama_kolom2>

mysql> UPDATE pegawai_join, jabatan SET pegawai_join.id_jabatan = jabatan.id_jabatan WHERE pegawai_join.jabatan=jabatan.nm_jabatan;

Query OK, 15 rows affected (0.48 sec)

Rows matched: 15 Changed: 15 Warnings: 0

mysql> select * from pegawai_join;

+----------+-----------------------+-------------+------------+----------------+---------+

+----------+-----------------------+-------------+------------+----------------+---------+

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |

| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |

| PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 |

| PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 |

| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |

| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |

| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |

| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 |

| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |

| PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 |

| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff Junior | 2000000 |

| PEG-1013 | Endang Melati | Madiun | 6 | Staff Junior | 2000000 |

| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 |

| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |

+----------+-----------------------+-------------+------------+----------------+---------+

15 rows in set (0.00 sec)

1. Cross Join

Operasi tabel yang pertama adalah cross join atau disebut juga sebagai Cartesian join. Pada cross join, semua data dalam tabel yang pertama dipasangkan dengan semua data pada tabel yang kedua. Berikut adalah contohnya

SYNTAX DASAR

SELECT * FROM <nama_tabel1>, <nama_tabel2>;

mysql> select * from pegawai_join, jabatan;

mysql> select * from pegawai_join, jabatan;

+----------+-----------------------+-------------+------------+----------------+---------+------------+----------------+

+----------+-----------------------+-------------+------------+----------------+---------+------------+----------------+

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |1 | C.E.O |

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |2 | Manager |

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |3 | Kepala Unit |

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |4 | Supervisor |

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |5 | Staff Senior |

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |6 | Staff Junior |

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |7 | Tenaga Kontrak |

| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |1 | C.E.O |

| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |2 | Manager |

| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |3 | Kepala Unit |

| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |4 | Supervisor |

| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |5 | Staff Senior |

| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |6 | Staff Junior |

| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |7 | Tenaga Kontrak |

| PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 |1 | C.E.O |

| PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 |2 | Manager |

| PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 |3 | Kepala Unit |

| PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 |4 | Supervisor |

| PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 |5 | Staff Senior |

| PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 |6 | Staff Junior |

| PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 |7 | Tenaga Kontrak |

| PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 |1 | C.E.O |

| PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 |2 | Manager |

| PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 |3 | Kepala Unit |

| PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 |4 | Supervisor |

| PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 |5 | Staff Senior |

| PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 |6 | Staff Junior |

| PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 |7 | Tenaga Kontrak |

| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |1 | C.E.O |

| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |2 | Manager |

| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |3 | Kepala Unit |

| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |4 | Supervisor |

| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |5 | Staff Senior |

| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |6 | Staff Junior |

| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |7 | Tenaga Kontrak |

| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |1 | C.E.O |

| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |2 | Manager |

| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |3 | Kepala Unit |

| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |4 | Supervisor |

| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |5 | Staff Senior |

| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |6 | Staff Junior |

| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |7 | Tenaga Kontrak |

| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |1 | C.E.O |

| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |2 | Manager |

| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |3 | Kepala Unit |

| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |4 | Supervisor |

| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |5 | Staff Senior |

| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |6 | Staff Junior |

| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |7 | Tenaga Kontrak |

| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 |1 | C.E.O |

| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 |2 | Manager |

| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 |3 | Kepala Unit |

| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 |4 | Supervisor |

| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 |5 | Staff Senior |

| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 |6 | Tenaga Kontrak |

| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |1 | C.E.O |

| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |2 | Manager |

| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |3 | Kepala Unit |

| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |4 | Supervisor |

| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |5 | Staff Senior |

| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |6 | Staff Junior |

| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |7 | Tenaga Kontrak |

| PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 |1 | C.E.O |

| PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 |2 | Manager |

| PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 |3 | Kepala Unit |

| PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 |4 | Supervisor |

| PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 |5 | Staff Senior |

| PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 |6 | Staff Junior |

| PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 |7 | Tenaga Kontrak |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 |1 | C.E.O |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 |2 | Manager |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 |3 | Kepala Unit |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 |4 | Supervisor |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 |5 | Staff Senior |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 |6 | Staff Junior |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 |7 | Tenaga Kontrak |

| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff Junior | 2000000 |1 | C.E.O |

| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff Junior | 2000000 |2 | Manager |

| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff Junior | 2000000 |3 | Kepala Unit |

| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff Junior | 2000000 |4 | Supervisor |

| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff Junior | 2000000 |5 | Staff Senior |

| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff Junior | 2000000 |6 | Staff Junior |

| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff Junior | 2000000 |7 | Tenaga Kontrak |

| PEG-1013 | Endang Melati | Madiun | 6 | Staff Junior | 2000000 |1 | C.E.O |

| PEG-1013 | Endang Melati | Madiun | 6 | Staff Junior | 2000000 |2 | Manager |

| PEG-1013 | Endang Melati | Madiun | 6 | Staff Junior | 2000000 |3 | Kepala Unit |

| PEG-1013 | Endang Melati | Madiun | 6 | Staff Junior | 2000000 |4 | Supervisor |

| PEG-1013 | Endang Melati | Madiun | 6 | Staff Junior | 2000000 |5 | Staff Senior |

| PEG-1013 | Endang Melati | Madiun | 6 | Staff Junior | 2000000 |6 | Staff Junior |

| PEG-1013 | Endang Melati | Madiun | 6 | Staff Junior | 2000000 |7 | Tenaga Kontrak |

| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 |1 | C.E.O |

| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 |2 | Manager |

| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 |3 | Kepala Unit |

| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 |4 | Supervisor |

| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 |5 | Staff Senior |

| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 |6 | Staff Junior |

| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 |7 | Tenaga Kontrak |

| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |1 | C.E.O |

| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |2 | Manager |

| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |3 | Kepala Unit |

| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |4 | Supervisor |

| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |5 | Staff Senior |

| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |6 | Staff Junior |

| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |7 | Tenaga Kontrak |

+----------+-----------------------+-------------+------------+----------------+---------+------------+----------------+

105 rows in set (0.00 sec)

Pada Cros Join semua data di table pegawai akan direlasikan satu persatu dengan data di table jabatan.

Jadi ada 105 baris hasil cross join.

2. Equi-Join atau Inner Join

Inner join menggabungkan tabel dengan membandingkan nilai yang sama antara dua buah kolom. Kolom yang dibandingkan dapat kita spesifikasikan.

mysql> SELECT * FROM pegawai, jabatan WHERE pegawai.jabatan = jabatan. nama_jabatan;

mysql> SELECT * FROM pegawai, jabatan WHERE pegawai.jabatan=jabatan.nama_jabatan;

+----------+-----------------------+-------------+----------------+---------+------------+----------------+

+----------+-----------------------+-------------+----------------+---------+------------+----------------+

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 | 1 | C.E.O |

| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 | 2 | Manager |

| PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 | 3 | Kepala Unit |

| PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 | 3 | Kepala Unit |

| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 | 4 | Supervisor |

| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 | 4 | Supervisor |

| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 | 5 | Staff Senior |

| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 | 5 | Staff Senior |

| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 | 5 | Staff Senior |

| PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 | 6 | Staff Junior |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 | 6 | Staff Junior |

| PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 | 6 | Staff Junior |

| PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 | 6 | Staff Junior |

| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 | 7 | Tenaga Kontrak |

| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 | 7 | Tenaga Kontrak |

+----------+-----------------------+-------------+----------------+---------+------------+----------------+

15 rows in set (0.01 sec)

Selain Menggunakan WHERE kita juga bisa menggunakan INNER JOIN.. TO..

Berikut contoh syntaxnya :

mysql> SELECT * FROM pegawai INNER JOIN jabatan ON pegawai.jabatan = jabatan.nama_jabatan;