Materi Praktikum MySQL Part 1-5

MATERI MINGGU 1 – Prak. BASISDATA
PENGANTAR MY SQL
LABORATORIUM 4 – BASISDATA
Melihat dafar database di komputer :
RUMUS :
SHOW DATABASES;
HASIL :
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| webauth |
+--------------------+
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lab4 |
| mysql |
| performance_schema |
| test |
+--------------------+

5 rows in set (0.00 sec)
Membuat database baru :
RUMUS :
CREATE DATABASE(spasi)NAMA_DATABASE;
CONTOH :
mysql> create database SN4;
Query OK, 1 row affected (0.09 sec)
Memakai database yang telah kita buat
RUMUS :
USE(spasi)NAMA_DATABASE;
CONTOH :
mysql> use lab4;
Database changed
Membuat tabel baru :
RUMUS :
CREATE TABLE NAMA_TABEL
(
Nama_Kolom1(spasi)tipe_data(ukuran),
Nama_Kolom2(spasi)tipe_data(ukuran),
Nama_Kolom3(spasi)tipe_data(ukuran),
Nama_Kolom4(spasi)tipe_data(ukuran));
CONTOH :
mysql> create table t_mhs(nim varchar(10), nama_mhs varchar(50),
alamat varchar(50), no_telp varchar(12));
Query OK, 0 rows affected (0.16 sec)
Melihat hasil tabel yang kita buat :
RUMUS :
DESC(spasi)NAMA_TABEL;
CONTOH :
mysql> desc t_mhs;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| nim | varchar(10) | YES | | NULL | |
| nama_mhs | varchar(50) | YES | | NULL | |
| alamat | varchar(50) | YES | | NULL | |
| no_telp | varchar(12) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.08 sec)mysql> desc mahasiswa;
Input Data ke Dalam Tabel :
1. Menggunakan Rumus INSERT
2. Menggunakan Rumus LOAD DATA
1.Menginputkan data ke dalam tabel menggunakan perintah
INSERT
RUMUS :
Mysql >INSERT(spasi)INTO(spasi)NAMA_TABEL(spasi)VALUES
>(
>’DATA_KOLOM1’,
>’DATA_KOLOM2’,
>’DATA_KOLOM3’,
>’DATA_KOLOM4’
>);
CONTOH :
mysql> insert into t_mhs values( '111051005', 'Sholeh',
'Sleman','085785231478');
Query OK, 1 row affected (0.05 sec)
mysql> select *from t_mhs;
+-----------+----------+--------+--------------+
| nim | nama_mhs | alamat | no_telp |
+-----------+----------+--------+--------------+
| 111051005 | Sholeh | Sleman | 085785231478 |
+-----------+----------+--------+--------------+
5 rows in set (0.00 sec)
2.Menginputkan data ke dalam tabel menggunakan perintah
LOAD DATA
Persiapkan file yang berisi data yang akan diinputkan ke tabel
di database kita, sesuai dengan urutan yg kita buat di MYSQL.
Simpan dengan format .txt
CONTOH : (dibuat di MS.EXEL dan disimpan dengan nama data.txt
= format TEXT(MS-DOS) )
111051001 Uning Jogja 085786452315
111051002 Yuli Bantul 081354687923
111051003 Wanto Sleman 088869874563
111051004 Catur Bantul 081244455566
RUMUS :
LOAD(spasi)DATA(spasi)LOCAL(spasi)INFILE(spasi)
’LOKASI-FILE’(spasi)
INTO(spasi)
TABLE(spasi)
NAMA_TABEL
;
D:\PRAK SMBD_SQL\SN4
CONTOH :
mysql> load data local infile 'D://km1/data.txt' into table
t_mhs;
Query OK, 4 rows affected (0.03 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select*from t_mhs;
+-----------+----------+------------+---------------+
| nim | nama_mhs | alamat_mhs | no_telp |
+-----------+----------+------------+---------------+
|111051001 | Uning | Jogja | 085786452315
|111051002 | Yuli | Bantul | 081354687923
|111051003 | Wanto | Sleman | 088869874563
|111051004 | Catur | Bantul | 081244455566
+-----------+----------+------------+---------------+
4 rows in set (0.00 sec)
Menghapus database dan tabel
Buat dulu table baru
mysql> create table mat_kul(id_matkul varchar(10), nama_matkul
varchar(50), sks int(1), semester int(1), dosen varchar(10));
Query OK, 0 rows affected (0.02 sec)
mysql> desc mat_kul;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id_matkul | varchar(10) | YES | | NULL | |
| nama_matkul | varchar(50) | YES | | NULL | |
| sks | int(1) | YES | | NULL | |
| semester | int(1) | YES | | NULL | |
| dosen | varchar(10) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
5 rows in set (0.03 sec)
Lihat tabel yang ada
mysql> show tables;
+----------------+
| Tables_in_lab4 |
+----------------+
| mat_kul |
| t_mhs |
+----------------+
2 rows in set (0.00 sec)
Hapus TABEL
DROP(spasi)TABLE(spasi)NAMA_TABEL
mysql> drop table mat_kul;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+----------------+
| Tables_in_lab4 |
+----------------+
| t_mhs |
+----------------+
1 row in set (0.00 sec)
Buat database baru
mysql> create database coba;
Query OK, 1 row affected (0.02 sec)
Lihat database yang ada
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| coba |
| lab4 |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
HAPUS DATABASE
mysql> drop database coba;
Query OK, 0 rows affected (0.06 sec)
LATIHAN 1
Kerjakan soal – soal di bawah ini dengan teliti..!!
1. Buatlah Database baru bernama RB1_LATIHAN1
2. Buatlah Tabel baru bernama T_Buku dengan kolom sbb :
+-----------+-------------+
| Field | Type |
+-----------+-------------+
| id_buku | varchar(8) |
| nama_buku | varchar(50) |
| pengarang | varchar(50) |
| penerbit | varchar(50) |
| harga | int(6) |
+-----------+-------------+
3. Isilah tabel T_Buku dengan data sbb :
+---------+--------------------+--------------+----------+-------+
| id_buku | nama_buku | pengarang | penerbit | harga |
+---------+--------------------+--------------+----------+-------+
| AA-001 | Pemrograman Pascal | Alfred Riedl | Erlangga | 50000 |
| AA-002 | SQL Server 5.0 | Jono Ricardo | MyBook | 85000 |
| BB-001 | Obat Anti Galau | Tim Galauers | Gramedia | 40000 |
| BB-002 | Humor Indonesia | Hendi Susan | Andi Ho | 30000 |
+---------+--------------------+--------------+----------+-------+
4. Buat laporan Latihan ini di Ms.Word atau Notepad dengan nama file
RB1_NIM_Latihan1
5. Simpan di Folder masing – masing
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_mhs varchar(12) primary key, nama_peg
varchar(50), alamat_peg varchar(50));
Query OK, 0 rows affected (0.07 sec)
LIHAT DESKRIPSINYA…
mysql> desc t_pegawai;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id_mhs | varchar(12) | NO | PRI | NULL | |
| nama_mhs | varchar(50) | YES | | NULL | |
| alamat_mhs | varchar(50) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
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;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id_parkir | varchar(5) | NO | PRI | NULL | |
| plat_no | varchar(12) | YES | UNI | NULL | |
| merk | varchar(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
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;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| 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;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id_mobil | varchar(8) | NO | PRI | NULL | |
| type | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
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;
+----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| id_stok | varchar(8) | NO | PRI | NULL | |
| id_mobil | varchar(8) | YES | MUL | NULL | |
| 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;
+----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| id_stok | varchar(8) | NO | PRI | NULL | |
| id_mobil | varchar(8) | YES | MUL | NULL | |
| 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;
+----------+--------+
| 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)
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
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;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id_mobil | varchar(8) | NO | PRI | NULL | |
| type | varchar(20) | YES | | NULL | |
| harga | bigint(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
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;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id_mobil | varchar(8) | NO | PRI | NULL | |
| type | varchar(20) | YES | | NULL | |
| series | varchar(8) | YES | | NULL | |
| harga | bigint(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
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;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id_mobil | varchar(8) | NO | PRI | NULL | |
| type | varchar(20) | YES | | NULL | |
| series | varchar(8) | YES | | NULL | |
| harga | bigint(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
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;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id_mobil | varchar(8) | NO | PRI | NULL | |
| type | varchar(15) | YES | | NULL | |
| series | varchar(10) | YES | | NULL | |
| harga | bigint(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
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;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id_mobil | varchar(8) | NO | PRI | NULL | |
| type | varchar(15) | YES | | NULL | |
| series | varchar(10) | YES | | NULL | |
| harga_mobil | bigint(11) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
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;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id_mobil | varchar(8) | NO | PRI | NULL | |
| type | varchar(15) | YES | | NULL | |
| series | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
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;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id_peg | varchar(8) | NO | | NULL | |
| nama_peg | varchar(50) | YES | | NULL | |
| alamat_peg | varchar(50) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
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;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id_peg | varchar(8) | NO | PRI | NULL | |
| nama_peg | varchar(50) | YES | | NULL | |
| alamat_peg | varchar(50) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
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;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| no_plat | varchar(10) | YES | | NULL | |
| merk_kend | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
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),
alamat_peg varchar(50), jabatan varchar(20), gaji int(7));
Query OK, 0 rows affected (0.70 sec)
mysql> desc pegawai;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| nip | varchar(8) | NO | PRI | NULL | |
| nama_peg | varchar(50) | YES | | NULL | |
| alamat_peg | varchar(50) | YES | | NULL | |
| jabatan | varchar(20) | YES | | 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 'D://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;
+----------+-----------------------+-------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+----------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 |
| PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 |
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 |
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 |
| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 |
| PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 |
| PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
+----------+-----------------------+-------------+----------------+---------+
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';
+----------+----------------+------------+--------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+----------------+------------+--------------+---------+
| PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 |
| PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 |
+----------+----------------+------------+--------------+---------+
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;
+----------+----------------+------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+----------------+------------+----------------+---------+
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
+----------+----------------+------------+----------------+---------+
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 berjumlah 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;
+----------+-----------------------+-------------+-------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+-------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 |
| PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
+----------+-----------------------+-------------+-------------+---------+
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;
+----------+-----------------------+-------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+----------------+---------+
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 |
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
| PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| 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;
+----------+-----------------------+------------+---------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+------------+---------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
+----------+-----------------------+------------+---------+---------+
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;
+----------+-----------------------+-------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+----------------+---------+
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 |
| PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 |
| PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 |
| PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 |
| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 |
| PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 |
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
+----------+-----------------------+-------------+----------------+---------+
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;
+----------+-----------------------+-------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+----------------+---------+
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 |
| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
| PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 |
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 |
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 |
+----------+-----------------------+-------------+----------------+---------+
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;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| nama | varchar(20) | YES | | NULL | |
| alamat | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
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;
+----------+----------------+------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+----------------+------------+----------------+---------+
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 |
+----------+----------------+------------+----------------+---------+
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;
+----------+-----------------------+-------------+-------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+-------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 |
| PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 |
+----------+-----------------------+-------------+-------------+---------+
6 rows in set (0.00 sec)
Perintah diatas menunjukkan penampilan gaji diantara 4jt dan 9jt .
Jika perintah BETWEEN dilakukan untuk mencari data dalam range, ada juga NOT
BETWEEN untuk mencari data diluar range yang kita tentukan.
CATATAN : penginputan range diawali 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');
+----------+-----------------------+-------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+----------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 |
| PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 |
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 |
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 |
| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 |
| PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 |
| PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
+----------+-----------------------+-------------+----------------+---------+
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');
+----------+-----------------------+-------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+----------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 |
| PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 |
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 |
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 |
| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 |
| PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 |
| PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
+----------+-----------------------+-------------+----------------+---------+
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;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| 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;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id_jabatan | int(2) | NO | PRI | NULL | |
| nm_jabatan | varchar(20) | YES | | 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;
+----------+-----------------------+-------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+----------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 |
| PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 |
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 |
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 |
| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 |
| PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 |
| PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
+----------+-----------------------+-------------+----------------+---------+
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;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| nip | varchar(8) | NO | | NULL | |
| nama_peg | varchar(50) | YES | | NULL | |
| alamat_peg | varchar(50) | YES | | NULL | |
| id_jabatan | int(2) | YES | | NULL | |
| jabatan | varchar(20) | 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;
+----------+-----------------------+-------------+------------+----------------+---------+
| nip | nama_peg | alamat_peg | id_jabatan | jabatan | gaji |
+----------+-----------------------+-------------+------------+----------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | NULL | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | NULL | Manager | 8000000 |
| PEG-1003 | Olga Syahputra | Jakarta | NULL | Kepala Unit | 6000000 |
| PEG-1004 | Chelsea Olivia | Bandung | NULL | Kepala Unit | 6000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | NULL | Supervisor | 4500000 |
| PEG-1006 | Budi Drajat | Malang | NULL | Supervisor | 4500000 |
| PEG-1007 | Bambang Pamungkas | Kudus | NULL | Staff Senior | 3000000 |
| PEG-1008 | Ely Oktafiani | Yogyakarta | NULL | Staff Senior | 3000000 |
| PEG-1009 | Rani Wijaya | Magelang | NULL | Staff Senior | 3000000 |
| PEG-1010 | Rano Karno | Solo | NULL | Staff Junior | 2000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | NULL | Staff Junior | 2000000 |
| PEG-1012 | Ilham Ungara | Jakarta | NULL | Staff Junior | 2000000 |
| PEG-1013 | Endang Melati | Madiun | NULL | Staff Junior | 2000000 |
| PEG-1014 | Donny Damara | Makasar | NULL | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem | Yogyakarta | NULL | Tenaga Kontrak | 500000 |
+----------+-----------------------+-------------+------------+----------------+---------+
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;
+----------+-----------------------+-------------+------------+----------------+---------+
| nip | nama_peg | alamat_peg | id_jabatan | jabatan | gaji |
+----------+-----------------------+-------------+------------+----------------+---------+
| 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;
+----------+-----------------------+-------------+------------+----------------+---------+------------+----------------+
| nip | nama_peg | alamat_peg | id_jabatan | jabatan | gaji | id_jabatan | nama_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;
+----------+-----------------------+-------------+----------------+---------+------------+----------------+
| nip | nama_peg | alamat_peg | jabatan | gaji | id_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;
+----------+-----------------------+-------------+----------------+---------+------------+----------------+
| nip | nama_peg | alamat_peg | jabatan | gaji | id_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 | Ke pala 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.00 sec)
Kita juga bisa menggunakan INNER JOIN.. USING.. sebagai perintah Join
Berikut contohnya :
mysql> SELECT * FROM pegawai_join INNER JOIN jabatan using (id_jabatan);
+------------+----------+-----------------------+-------------+----------------+---------+----------------+
| id_jabatan | nip | nama_peg | alamat_peg | jabatan | gaji | nama_jabatan |
+------------+----------+-----------------------+-------------+----------------+---------+----------------+
| 1 | PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 | C.E.O |
| 2 | PEG-1002 | Felix Nababan | Medan | Manager | 8000000 | Manager |
| 3 | PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 | Kepala Unit |
| 3 | PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 | Kepala Unit |
| 4 | PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 | Supervisor |
| 4 | PEG-1006 | Budi Drajat | Malang | Supervisor | 45000 00 | Supervisor |
| 5 | PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 | Staff Senior |
| 5 | PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 | Staff Senior |
| 5 | PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 | Staff Senior |
| 6 | PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 | Staff Junior |
| 6 | PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 | Staff Junior |
| 6 | PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 | Staff Junior |
| 6 | PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 | Staff J unior |
| 7 | PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 | Tenaga Kontrak |
| 7 | PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 | Tenaga Kontrak |
+------------+----------+-----------------------+-------------+----------------+---------+----------------+
15 rows in set (0.00 sec)
3. Natural Join
Natural join sebenarnya mirip dengan INNER JOIN. Namun kita tidak perlu
menspesifikasikan kolom mana yang ingin kita bandingkan. Secara automatis, MySQL
akan mencari kolom pada dua buah tabel yang memiliki nilai yang sama dan
membandingkannya. Sebagai contoh, untuk tabel pegawai_join dan jabatan, yang
dibandingkan adalah kolom id_jabatan yang ada di keduanya.
mysql> select * from pegawai_join NATURAL JOIN jabatan;
+------------+----------+-----------------------+-------------+----------------+---------+----------------+
| id_jabatan | nip | nama_peg | alamat_peg | jabatan | gaji | nama_jabatan |
+------------+----------+-----------------------+-------------+----------------+---------+----------------+
| 1 | PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 | C.E.O |
| 2 | PEG-1002 | Felix Nababan | Medan | Manager | 8000000 | Manager |
| 3 | PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 | Kepala Unit |
| 3 | PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 | Kepala Unit |
| 4 | PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 | Supervisor |
| 4 | PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 | Supervisor |
| 5 | PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 | Staff Senior |
| 5 | PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 | Staff Senior |
| 5 | PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 | S taff Senior |
| 6 | PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 | Staff Junior |
| 6 | PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 | Staff Junior |
| 6 | PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 | Staff Junior |
| 6 | PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 | Staff Junior |
| 7 | PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 | Tenaga Kontrak |
| 7 | PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 | Tenaga Kontrak |
+------------+----------+-----------------------+-------------+----------------+---------+----------------+
15 rows in set (0.00 sec)
4. Left Join dan Right Join
Left join dan right join digunakan untuk menghindari data yang hilang karena mungkin ada data yang belum
direferensi oleh tabel yang lain.
Sebagai contoh, kita tambahkan data di jabatan tapi kita tidak referensikan di tabel employee_join sebagai
berikut:
mysql> insert into jabatan values (8,'Tenaga Magang');
Query OK, 1 row affected (0.05 sec)
Kemudian jika kita lakukan LEFT JOIN dengan tabel jobs yang kita sebut terlebih
dahulu (atau di sebelah kiri) maka akan muncul sebagai berikut:
mysql> select * from jabatan t1 LEFT JOIN pegawai_join t2 ON t1.id_jabatan = t2.
id_jabatan;
+------------+----------------+----------+-----------------------+-------------+------------+----------------+---------+
| id_jabatan | nama_jabatan | nip | nama_peg | alamat_peg | id_jabatan | jabatan | gaji |
+------------+----------------+----------+-----------------------+-------------+------------+----------------+---------+
| 1 | C.E.O | PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |
| 2 | Manager | PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |
| 3 | Kepala Unit | PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 |
| 3 | Kepala Unit | PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 |
| 4 | Supervisor | PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |
| 4 | Supervisor | PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |
| 5 | Staff Senior | PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |
| 5 | Staff Senior | PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 |
| 5 | Staff Senior | PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |
| 6 | Staff Junior | PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 |
| 6 | Staff Junior | PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 |
| 6 | Staff Junior | PEG-1012 | Ilham Ungara | Jakarta | 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 |
| 7 | Tenaga Kontrak | PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |
| 8 | Tenaga Magang | NULL | NULL | NULL | NULL | NULL | NULL |
+------------+----------------+----------+-----------------------+-------------+------------+----------------+---------+
16 rows in set (0.00 sec)
Meskipun pada tabel jabatan ada data yang belum direferensi, namun data tersebut tetap ditampilkan dan
diberi nilai NULL untuk nilai di tabel pegawai_join yang belum diketahui. Hal yang sama juga berlaku untuk
RIGHT JOIN, kali ini dengan tabel jabatan berada di sebelah kanan.
mysql> select * from pegawai_join t1 RIGHT JOIN jabatan t2 ON t1.id_jabatan =
t2.id_jabatan;
+----------+-----------------------+-------------+------------+----------------+---------+------------+----------------+
| nip | nama_peg | alamat_peg | id_jabatan | jabatan | gaji | id_jabatan | nama_jabatan |
+----------+-----------------------+-------------+------------+----------------+---------+------------+----------------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 | 1 | C.E.O |
| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 | 2 | Manager |
| PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 | 3 | Kepala Unit |
| PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 | 3 | Kepala Unit |
| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 | 4 | Supervisor |
| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 | 4 | Supervisor |
| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 | 5 | Staff Senior |
| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 | 5 | Staff Senior |
| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 | 5 | Staff Senior |
| PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 | 6 | Staff Junior |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 | 6 | Staff Junior |
| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff Junior | 2000000 | 6 | Staff Junior |
| PEG-1013 | Endang Melati | Madiun | 6 | Staff Junior | 2000000 | 6 | Staff Junior |
| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 | 7 | Tenaga Kontrak |
| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 | 7 | Tenaga Kontrak |
| NULL | NULL | NULL | NULL | NULL | NULL | 8 | Tenaga Magang |
+----------+-----------------------+-------------+------------+----------------+---------+------------+----------------+
16 rows in set (0.02 sec)
5. Update menggunakan Join Table
Kita juga dapat melakukan update menggunakan Join table. Bentuk sintaksnya
adalah
UPDATE <table1>, <table2> SET <column_name> = 'new_value' WHERE
<conditions>
Misal, kita ingin merubah nama belakang dari employee yang pekerjaannya Spy
menjadi 'Bono'. Kita dapat melakukannya sebagai berikut:
mysql> UPDATE pegawai_join, jabatan SET nama_peg = 'Soeharto Mangantelo' WHERE
pegawai_join.id_jabatan = jabatan.id_jabatan AND jabatan.nama_jabatan = 'C.E.O';
Query OK, 1 row affected (0.85 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT nip, nama_peg, jabatan FROM pegawai_join INNER JOIN jabatan USING
(id_jabatan) LIMIT 3;
+----------+---------------------+-------------+
| nip | nama_peg | jabatan |
+----------+---------------------+-------------+
| PEG-1001 | Soeharto Mangantelo | C.E.O |
| PEG-1002 | Felix Nababan | Manager |
| PEG-1003 | Olga Syahputra | Kepala Unit |
+----------+---------------------+-------------+
3 rows in set (0.00 sec)
6. Delete menggunakan join table
Kita juga dapat menghapus data menggunakan join table sebagai berikut:
mysql> DELETE pegawai_join FROM pegawai_join, jabatan WHERE
pegawai_join.id_jabatan = jabatan.id_jabatan AND jabatan.nama_jabatan =
'Manager';
Query OK, 1 row affected (0.05 sec)
mysql> SELECT nip, nama_peg, jabatan FROM pegawai_join INNER JOIN jabatan USING
(id_jabatan) LIMIT 3;
+----------+---------------------+-------------+
| nip | nama_peg | jabatan |
+----------+---------------------+-------------+
| PEG-1001 | Soeharto Mangantelo | C.E.O |
| PEG-1003 | Olga Syahputra | Kepala Unit |
| PEG-1004 | Chelsea Olivia | Kepala Unit |
+----------+---------------------+-------------+
3 rows in set (0.00 sec)
MATERI PART 4 – Prak. BASISDATA
VIEW dan TRIGGER
LABORATORIUM 4 – BASISDATA
MENU PART INI :
13. View
a. Algorithm Attributes
b. Updatable View
c. Klausa WITH CHECK OPTION
d. Merubah View
e. Melihat definisi pembuatan view
14. Trigger
a. Membuat trigger
b. BEFORE TRIGGER
c. AFTER TRIGGER
d. Melihat trigger yang sudah dibuat
VIEW
View adalah query tersimpan yang menghasilkan result set ketika dipanggil. View
bertindak sebagai tabel virtual. Beberapa hal yang tidak boleh digunakan pada query
yang mendefinisikan view adalah sebagai berikut:
• definisi view tidak boleh memiliki sub query di klausa FROM dari statement SQL
• variabel user, sistem, atau lokal tidak boleh digunakan dalam sebuah SQL
SELECT
• view tidak dapat merujuk ke tabel temporer
• trigger tidak dapat diasosiasikan terhadap sebuah view
• view yang dibuat di dalam stored procedure tidak dapat merujuk kepada
parameter dalam stored procedure
Pembuatan view memerlukan pendefinisian nama view dan sebuah statement SQL.
Setelah view dibuat, view dapat diquery seperti tabel biasa. Bentuk dasar pembuatan
view adalah sebagai berikut:
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name [WHERE condition]
Pembuatan view dapat menggunakan OR REPLACE untuk mengganti view yang telah ada
sebelumnya.
Berikut adalah contoh pembuatan view sederhana.
mysql> CREATE VIEW view_1 AS SELECT * FROM pegawai;
Query OK, 0 rows affected (0.06 sec)
Setelah view dibuat maka untuk melihat hasilnya kita harus memanggilnya dengan
perintah select, contohnya :
mysql> select*from view_1;
+----------+-----------------------+-------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+----------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 |
| PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 |
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 |
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 |
| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 |
| PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 |
| PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
+----------+-----------------------+-------------+----------------+---------+
15 rows in set (0.00 sec)
Dalam peamanggilannya pun kita juga bisa menentukan kondisi yang kita inginkan,
contohnya :
mysql> select*from view_1 limit 3;
+----------+-----------------------+------------+-------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+------------+-------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 |
+----------+-----------------------+------------+-------------+---------+
3 rows in set (0.00 sec)
mysql> select*from view_1 where nama_peg = 'Olga Syahputra';
+----------+----------------+------------+-------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+----------------+------------+-------------+---------+
| PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 |
+----------+----------------+------------+-------------+---------+
1 row in set (0.00 sec)
Penggunaan WHERE dalam syntax VIEW
mysql>
CREATE VIEW view_2
AS SELECT nip, nama_peg, gaji
FROM pegawai
WHERE alamat_peg='Yogyakarta';
Query OK, 0 rows affected (0.02 sec)
mysql> select * from view_2;
+----------+-----------------------+---------+
| nip | nama_peg | gaji |
+----------+-----------------------+---------+
| PEG-1001 | Soeharto Mangundirejo | 9000000 |
| PEG-1008 | Ely Oktafiani | 3000000 |
| PEG-1011 | Rahmadi Sholeh | 2000000 |
| PEG-1015 | Paijem | 500000 |
+----------+-----------------------+---------+
4 rows in set (0.00 sec)
1. Algorithm Attributes
Algorithm attributes memberikan kendali bagaimana MySQL memproses view. Atribut
ini bersifat opsional. Atribut algoritma menerima tiga buah nilai, yaitu MERGE,
TEMPTABLE, dan UNDEFINED.
Default algoritma yang dipilih adalah UNDEFINED.
Untuk algoritma MERGE, teks dari statement query yang merujuk ke sebuah view dan
definisi view digabung sedemikian sehingga bagian dari definisi view
menggantikan bagian yang bersesuaian dengan statement.
Untuk algoritma TEMPTABLE, hasil dari view diambil ke dalam temporary table,
yang kemudian digunakan untuk menjalankan statement. TEMPTABLE dipilih karena
lock pada table yang digunakan dapat langsung dilepas setelah temporary table
telah dibuat. Akibatnya, penggunaan TEMPTABLE dapat mempercepat pelepasan lock
pada table utama, sehingga klien lain yang akan menggunakan view tidak menunggu
terlalu lama.
Untuk algoritma UNDEFINED, MySQL memilih sendiri algoritma mana yang akan
digunakan. MySQL lebih memilih MERGE karena biasanya lebih efisien. Selain itu,
view yang menggunakan TEMPTABLE tidak dapat diupdate karena menggunakan
temporary table.
Berikut adalah contoh pembuatan view menggunakan atribut algoritma MERGE.
mysql> CREATE ALGORITHM = MERGE VIEW v_merge (nomor_induk_pegawai,
nama_pegawai,gaji) AS SELECT nip, nama_peg, gaji FROM pegawai WHERE gaji >
2000000;
Query OK, 0 rows affected (0.03 sec)
mysql> select*from v_merge;
+---------------------+-----------------------+---------+
| nomor_induk_pegawai | nama_pegawai | gaji |
+---------------------+-----------------------+---------+
| PEG-1001 | Soeharto Mangundirejo | 9000000 |
| PEG-1002 | Felix Nababan | 8000000 |
| PEG-1003 | Olga Syahputra | 6000000 |
| PEG-1004 | Chelsea Olivia | 6000000 |
| PEG-1005 | Tuti Wardani | 4500000 |
| PEG-1006 | Budi Drajat | 4500000 |
| PEG-1007 | Bambang Pamungkas | 3000000 |
| PEG-1008 | Ely Oktafiani | 3000000 |
| PEG-1009 | Rani Wijaya | 3000000 |
+---------------------+-----------------------+---------+
9 rows in set (0.00 sec)
Adanya tambahan syntax ALGORITHM=MERGE tersebut bertujuan untuk mendeklarasikan
Algoritma mana yang kita pilih, jika kita tidak mendeklarasikannya punjuga bisa
karena MySQL bisa memilih sendiri contoh :
mysql> CREATE VIEW v_merge2(nomor_induk_pegawai, nama_pegawai, gaji) AS SELECT n
ip, nama_peg, gaji FROM pegawai WHERE gaji > 2000000;
Query OK, 0 rows affected (0.03 sec)
mysql> select*from v_merge2;
+---------------------+-----------------------+---------+
| nomor_induk_pegawai | nama_pegawai | gaji |
+---------------------+-----------------------+---------+
| PEG-1001 | Soeharto Mangundirejo | 9000000 |
| PEG-1002 | Felix Nababan | 8000000 |
| PEG-1003 | Olga Syahputra | 6000000 |
| PEG-1004 | Chelsea Olivia | 6000000 |
| PEG-1005 | Tuti Wardani | 4500000 |
| PEG-1006 | Budi Drajat | 4500000 |
| PEG-1007 | Bambang Pamungkas | 3000000 |
| PEG-1008 | Ely Oktafiani | 3000000 |
| PEG-1009 | Rani Wijaya | 3000000 |
+---------------------+-----------------------+---------+
9 rows in set (0.00 sec)
Algoritma MERGE tidak dapat digunakan ketika view memerlukan penggunaan
konstruksi sebagai berikut:
• Fungsi aggregate, seperti SUM(), MIN(), MAX(), COUNT() dan lain-lain
• DISTINCT
• GROUP BY
• HAVING
• LIMIT
• UNION atau UNION ALL
• Subquery pada SELECT
• Referensi ke nilai literal (tidak ada tabel yang direferensi)
Ketika algoritma MERGE tidak dapat digunakan, kita bisa menggunakan algoritma
TEMPTABLE.
mysql> CREATE ALGORITHM = MERGE VIEW view_4(nomor_induk_pegawai, nama_pegawai,
gaji) AS SELECT nip, nama_peg, gaji FROM pegawai WHERE gaji > 2000000 LIMIT 5;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------+
| Warning | 1354 | View merge algorithm can't be used here for now (assumed undefined algorithm) |
+---------+------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> CREATE ALGORITHM = TEMPTABLE VIEW v_temptable(nomor_induk_pegawai,
nama_pegawai, gaji) AS SELECT nip, nama_peg, gaji FROM pegawai WHERE gaji >
2000000 LIMIT 5;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from v_temptable;
+---------------------+-----------------------+---------+
| nomor_induk_pegawai | nama_pegawai | gaji |
+---------------------+-----------------------+---------+
| PEG-1001 | Soeharto Mangundirejo | 9000000 |
| PEG-1002 | Felix Nababan | 8000000 |
| PEG-1003 | Olga Syahputra | 6000000 |
| PEG-1004 | Chelsea Olivia | 6000000 |
| PEG-1005 | Tuti Wardani | 4500000 |
+---------------------+-----------------------+---------+
5 rows in set (0.00 sec)
Ketika kita mendefinisikan VIEW menggunakan algoritma TEMPTABLE tidak muncul
pesan warning.
2. Updateable VIEW
Ada beberapa view yang dapat diupdate menggunakan statement UPDATE, DELETE, atau
INSERT.
Update juga dilakukan pada tabel yang dirujuk view tersebut. Supaya sebuah view
dapat diupdate, harus ada hubungan satu-satu antara baris dalam view dengan
baris pada tabelnya. Selain view yang dapat diupdate ada juga view yang tidak
dapat diupdate, yaitu view yang memiliki:
• fungsi aggregat, seperti SUM(), MIN(), MAX(), COUNT().
• DISTINCT
• GROUP BY
• HAVING
• sub query di SELECT
• Join-join tertentu
• Nonupdatable view di klausa FROM
• Sebuah subquery di klausa WHERE yang merujuk ke sebuah tabel dalam
klausa FROM.
• Hanya merujuk ke nilai literal (tidak merujuk ke sebuah tabel)
• Menggunakan ALGORITHM = TEMPTABLE (penggunaan temporary table membuat
view tidak dapat diupdate)
• Referensi berulangkali ke kolom manapun dari sebuah tabel.
mysql> UPDATE v_merge2 SET nama_pegawai = 'Olga Syahrini' WHERE nama_pegawai =
'Olga Syahputra';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Lalu kita lihat perubahannya pada hasil eksekusi v_merge2;
mysql> select * from v_merge2;
+---------------------+-----------------------+---------+
| nomor_induk_pegawai | nama_pegawai | gaji |
+---------------------+-----------------------+---------+
| PEG-1001 | Soeharto Mangundirejo | 9000000 |
| PEG-1002 | Felix Nababan | 8000000 |
| PEG-1003 | Olga Syahrini | 6000000 |
| PEG-1004 | Chelsea Olivia | 6000000 |
| PEG-1005 | Tuti Wardani | 4500000 |
| PEG-1006 | Budi Drajat | 4500000 |
| PEG-1007 | Bambang Pamungkas | 3000000 |
| PEG-1008 | Ely Oktafiani | 3000000 |
| PEG-1009 | Rani Wijaya | 3000000 |
+---------------------+-----------------------+---------+
9 rows in set (0.00 sec)
Update pada view v_merge2 juga berefek pada tabel sebenarnya, sehingga update
pada view bukan hanya merubah data dlm view tetapi merubah data secara
keseluruhan.
Kita bisa lihat sbb :
mysql> select * from pegawai;
+----------+-----------------------+-------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+----------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1003 | Olga Syahrini | Jakarta | Kepala Unit | 6000000 |
| PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 |
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 |
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 |
| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 |
| PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 |
| PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
+----------+-----------------------+-------------+----------------+---------+
15 rows in set (0.00 sec)
Sekarang kita coba mengupdate view yang memuat “larangan” tadi, contoh kita akan
mengupdate v_temptable yang merupakan temptable view, maka syntaxnya :
mysql> UPDATE v_temptable SET nama_pegawai = 'Chelsea' WHERE nama_pegawai=
'Chelsea Olivia';
ERROR 1288 (HY000): The target table v_temptable of the UPDATE is not updatable
3. Klausa WITH CHECK OPTION
Klausa WITH CHECK OPTION dalam pembuatan view diberikan kepada updatable view
untuk mencegah INSERT atau UPDATE pada baris data kecuali klausa WHERE pada
statement SELECT bernilai benar (true). Parameter yang dapat digunakan untuk
CHECK OPTION adalah LOCAL dan CASCADED. Parameter LOCAL berarti pengecekan hanya
dilakukan pada view yang didefinisikan. Parameter CASCADED berarti pengecekan
dilakukan pada view beserta tabel yang direferensi oleh view tersebut. Secara
default, pendefinisian CHECK OPTION bernilai CASCADED.
Berikut adalah contoh definisi view menggunakan CHECK OPTION:
mysql> CREATE VIEW v_1 AS SELECT nip, nama_peg, gaji FROM pegawai WHERE gaji
<2000000 WITH CHECK OPTION;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from v_1;
+----------+---------------+---------+
| nip | nama_peg | gaji |
+----------+---------------+---------+
| PEG-1014 | Donny Damara | 1000000 |
| PEG-1015 | Paijem | 500000 |
+----------+---------------+---------+
3 rows in set (0.00 sec)
mysql> CREATE VIEW v_2 AS SELECT * FROM v_1 WHERE gaji > 500000 WITH LOCAL CHECK
OPTION;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from v_2;
+----------+---------------+---------+
| nip | nama_peg | gaji |
+----------+---------------+---------+
| PEG-1014 | Donny Damara | 1000000 |
+----------+---------------+---------+
2 rows in set (0.00 sec)
mysql> CREATE VIEW v_3 AS SELECT * FROM v_1 WHERE gaji > 500000 WITH CASCADED
CHECK OPTION;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from v_3;
+----------+---------------+---------+
| nip | nama_peg | gaji |
+----------+---------------+---------+
| PEG-1014 | Donny Damara | 1000000 |
+----------+---------------+---------+
2 rows in set (0.00 sec)
Pada View v_1 kita membuat sebuah view untuk menampilkan data pegawai yang
memiliki gaji kurang dari 2jt, lalu kita membuat view v_2 yang berfungsi untuk
mengambil data dari hasil eksekusi view v_1 tetapi dibatasi data pegawai yang
memiliki gaji lebih dari 500rb dan juga memuat klausa WITH LOCAL CHECK OPTION,
sedangkan view_v3 fungsinya sama dengan view v_2 tetapi bedanya view v_3
menggunakan klausa WITH CASCADED CHECK OPTION. Untuk melihat perbedaannya kita
bisa melakukan masukkan data sbb :
mysql> insert into v_2 values('PEG-1018','Robert Gugu',3000000);
Query OK, 1 row affected (0.05 sec)
mysql> insert into v_3 values('PEG-1018','Robert Gugu',3000000);
ERROR 1369 (HY000): CHECK OPTION failed 'lab4.v_3'
Insert data pada view v_2 dinyatakan tak ada masalah karena v_2 hanya melihat
atau mengechek secara LOCAL atau hanya yang data lebih dari 500rb, sedangkan
untuk v_3 dinyatakan error karena dia mengecheck secara CASCADED atau
keseluruhan, mulai dari pengecheckan v_1 yang direferensikan oleh v_3, dan
karena v_1 memiliki batasan 2jt maka masukkan 3jt ke v_3 dianggap salah.
4. Merubah VIEW
View yang telah kita buat bisa kita modifikasi dengan ALTER VIEW, contohnya sbb
:
mysql> ALTER VIEW view_2(NIP, NAMA_PEGAWAI, GAJI) AS SELECT nip, nama_peg, gaji
FROM pegawai WHERE alamat_peg='Yogyakarta';
Query OK, 0 rows affected (0.02 sec)
mysql> select * from view_2;
+----------+-----------------------+---------+
| NIP | NAMA_PEGAWAI | GAJI |
+----------+-----------------------+---------+
| PEG-1001 | Soeharto Mangundirejo | 9000000 |
| PEG-1008 | Ely Oktafiani | 3000000 |
| PEG-1011 | Rahmadi Sholeh | 2000000 |
| PEG-1015 | Paijem | 500000 |
+----------+-----------------------+---------+
5 rows in set (0.02 sec)
5. Melihat Definisi Pembuatan VIEW
Kita bisa melihat kembali syntax kita dalam membuat view, kita bisa menggunakan
syntax :
SHOW CREATE VIEW<nama_view>
mysql> show create view view_2\G;
atau jika kita ingin melihat struktur viewnya kita juga bisa gunakan
perintah :
DESC <nama_view>
mysql> desc view_2;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| NIP | varchar(8) | NO | | NULL | |
| NAMA_PEGAWAI | varchar(50) | YES | | NULL | |
| GAJI | int(7) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
TRIGGER
Trigger adalah sebuah objek database yang diasosiasikan dengan sebuah tabel. Trigger
diaktifkan ketika sebuah event terjadi pada tabel yang diasosiasikan. Tabel yang
diasosiasikan dengan trigger haruslah sebuah tabel yang permanen dan bukan temporary
tabel.
1. Membuat Trigger
Trigger bisa dibuat dengan syntax sbb :
CREATE TRIGGER <trigger_name> <trigger_time> <trigger_event>
ON <table>
FOR EACH ROW <trigger_body statements>
Pada sintaks di atas, trigger_name adalah nama dari trigger yang akan kita buat.
Trigger_time adalah saat aktivasi trigger. Parameter trigger_time dapat berisi
BEFORE atau AFTER, yang menandakan apakah aktivasi trigger dilakukan sebelum
atau sesudah terjadi perubahan pada tabel.
Trigger_event menunjukkan jenis statement yang mengaktifkan trigger.
Trigger_event dapat didefinisikan sebagai salah satu dari:
 INSERT: trigger diaktifkan ketika sebuah baris baru ditambahkan ke tabel,
sebagai contoh melalui statement INSERT, LOAD DATA, atau REPLACE.
 UPDATE: trigger diaktifkan ketika sebuah baris dimodifikasi, sebagai contoh
melalui statement UPDATE.
 DELETE: trigger diaktifkan ketika sebuah baris dihapus, melalui statement
DELETE dan REPLACE. Namun demikian, DROP TABLE dan TRUNCATE TABLE tidak
mengaktifkan trigger DELETE
Tidak boleh ada dua buah trigger yang sama pada sebuah tabel yang memiliki
trigger_time dan trigger_event yang sama. Sebagai contoh, kita tidak dapat
membuat dua buah BEFORE UPDATE trigger pada satu buah tabel yang sama, namun
kita dapat membuat trigger BEFORE UPDATE dan AFTER UPDATE untuk satu tabel yang
sama.
trigger_body merupakan definisi statement yang dijalankan ketika sebuah trigger
diaktifkan. Jika ada beberapa statement yang ingin dijalankan, statementstatement
tersebut dapat didefinisikan di antara BEGIN … AND.
Keyword OLD dan NEW dapat digunakan untuk mereferensi nilai sebelum dan sesudah
trigger dilakukan. Sebagai contoh OLD.nama_kolom menunjukkan nilai kolom sebelum
data tersebut dihapus atau diupdate, sedangkan NEW.nama_kolom menunjukkan nilai
kolom sebuah data yang akan dimasukkan atau nilai kolom data setelah diupdate.
2. BEFORE TRIGGER
Berikut adalah contoh trigger yang dijalankan sebelum data dimasukkan ke dalam
sebuah tabel.
mysql> DELIMITER (spasi)#
mysql> CREATE TRIGGER sebelum_insert BEFORE INSERT ON pegawai FOR EACH ROW
BEGIN
IF NEW.gaji IS NULL OR NEW.gaji = 0
THEN
SET NEW.gaji = 500000;
ELSE
SET NEW.gaji = NEW.gaji + 100000;
END IF;
END #
Query OK, 0 rows affected (0.23 sec)
Mysql> DELIMITER(spasi);
Dalam TRIGGER diatas adalah salah satu contoh trigger yang akan dijalankan
sebelum sebuah even dipanggil (BEFORE INSERT). Dalam trigger ini akan mengganti
nilaigaji jika gaji yang di-INSERT-kan bernilai NULL atau 0 dan akan menambahkan
nilai 100rb jika kita masukkan selain NULL dan 0.
Berikut contoh masukkan data setelah didefinisikan trigger :
INPUT NILAI NULL atau 0
mysql> insert into pegawai values('PEG-1017','Astuti','Kudus','Staff Junior',0);
Query OK, 1 row affected (0.05 sec)
mysql> select * from pegawai;
+----------+-----------------------+-------------+-----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+-----------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1003 | Olga Syahrini | Jakarta | Kepala Unit | 6000000 |
| PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 |
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 |
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 |
| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 |
| PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 |
| PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
| PEG-1016 | Djono | Yogyakarta | Tenanga Kontrak | 1000000 |
| PEG-1017 | Astuti | Kudus | Staff Junior | 500000 |
+----------+-----------------------+-------------+-----------------+---------+
17 rows in set (0.00 sec)
INPUT DENGAN NILAI
mysql> insert into pegawai values('PEG-1018','Puspito','Magelang','Staff
Junior',1000000);
Query OK, 1 row affected (0.03 sec)
mysql> select * from pegawai;
+----------+-----------------------+-------------+-----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+-----------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1003 | Olga Syahrini | Jakarta | Kepala Unit | 6000000 |
| PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 |
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 |
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 |
| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 |
| PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 |
| PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
| PEG-1016 | Djono | Yogyakarta | Tenanga Kontrak | 1000000 |
| PEG-1017 | Astuti | Kudus | Staff Junior | 500000 |
| PEG-1018 | Puspito | Magelang | Staff Junior | 1100000 |
+----------+-----------------------+-------------+-----------------+---------+
18 rows in set (0.00 sec)
Namun demikian, kita tidak dapat mengupdate tabel yang sama dengan tabel yang
diasosiasikan dengan trigger menggunakan trigger.
Mysql> DELIMITER #
mysql> CREATE TRIGGER before_update BEFORE UPDATE ON pegawai FOR EACH ROW
BEGIN
UPDATE pegawai SET gaji=gaji+(NEW.gaji - OLD.gaji) WHERE
nip=NEW.nip;
END #
Query OK, 0 rows affected (0.03 sec)
mysql> DELIMITER ;
mysql> UPDATE pegawai SET gaji = 2000000 WHERE nip="PEG-1017";
ERROR 1442 (HY000): Can't update table 'pegawai' in stored function/trigger
because it is already used by statement which invoked this stored function
/trigger.
3. AFTER TRIGGER
Berikut adalah contoh trigger yang dijalankan setelah update dilakukan
terhadap tabel
Untuk mencobanya kita buat dulu table pencatat, nanti di table pencatat ini akan
kita buat syntax untuk mencatat perubahan yang terjadi.
mysql> create table pencatat(catatan varchar(50));
Query OK, 0 rows affected (0.48 sec)
mysql> delimiter #
kita buat TRIGGERnya untuk mencatat perubahan yang terjadi
mysql> CREATE TRIGGER trigger_cat AFTER UPDATE ON pegawai FOR EACH ROW
BEGIN
INSERT INTO pencatat
VALUES(concat('akun ',NEW.nip,' dari ',OLD.gaji, ' to
',NEW.gaji));
END
#
Query OK, 0 rows affected (0.17 sec)
mysql> delimiter ;
Kita rubah nilai dari gaji yang ada
mysql> UPDATE pegawai SET gaji=gaji+200000;
Query OK, 18 rows affected (0.09 sec)
Rows matched: 18 Changed: 18 Warnings: 0
mysql> select * from pencatat;
+---------------------------------------+
| catatan |
+---------------------------------------+
| akun PEG-1001 dari 9000000 to 9200000 |
| akun PEG-1002 dari 8000000 to 8200000 |
| akun PEG-1003 dari 6000000 to 6200000 |
| akun PEG-1004 dari 6000000 to 6200000 |
| akun PEG-1005 dari 4500000 to 4700000 |
| akun PEG-1006 dari 4500000 to 4700000 |
| akun PEG-1007 dari 3000000 to 3200000 |
| akun PEG-1008 dari 3000000 to 3200000 |
| akun PEG-1009 dari 3000000 to 3200000 |
| akun PEG-1010 dari 2000000 to 2200000 |
| akun PEG-1011 dari 2000000 to 2200000 |
| akun PEG-1012 dari 2000000 to 2200000 |
| akun PEG-1013 dari 2000000 to 2200000 |
| akun PEG-1014 dari 1000000 to 1200000 |
| akun PEG-1015 dari 500000 to 700000 |
| akun PEG-1016 dari 1000000 to 1200000 |
| akun PEG-1017 dari 500000 to 700000 |
| akun PEG-1018 dari 1100000 to 1300000 |
+---------------------------------------+
18 rows in set (0.00 sec)
TRIGGER diatas akan dieksekusi setelah perintah UPADATE dijalankan.
4. Melihat dan Menghapus Trigger yang Sudah Dibuat
Untuk melihat TRIGGER kita bisa menggunakan syntax
mysql> show triggers in lab4;
mysql> drop trigger <nama_trigger>;
drop trigger before_update;
MATERI PART 5 – Prak. BASISDATA
FUNCTION DAN PROSEDURE
LABORATORIUM 4 – BASISDATA
MENU PART INI :
15. Intro Stored Routine
16. Function
i. Membuat function
ii. Melihat function yang telah dibuat
iii. Menghapus function
17. Procedure
i. Membuat procedure
ii. Parameter dalam procedure
1. Parameter IN
2. Parameter OUT
3. Parameter INOUT
4. Melihat procedure yang telah dibuat
5. Menghapus procedure
18. Pemrograman di Function dan Procedure
i. Variabel
ii. Kendali Kondisional.
1. Kendali IF
2. Kendali CASE
iii. Perulangan
1. Perulangan WHILE
2. Perulangan REPEAT … UNTIL
3. Perulangan LOOP
1. Fuction
Sebuah function dapat digunakan secara langsung dalam statement SELECT,
UPDATE, dan DELETE. Hasil dari function dapat dikembalikan sebagai
output. Sebuah function hanya dapat mengembalikan sebuah nilai saja.
1.1 Membuat fuction
Sebelum kita membuat function terlebih dahulu kita siapkan sebuah
tabel terlebih dahulu
mysql> select*from pekerja;
+------------+---------------+
| nama_depan | nama_belakang |
+------------+---------------+
| John | Doe |
| Alison | Mathews |
| James | Smith |
| Celia | Rice |
| Robert | Black |
| Linda | Green |
| David | Larry |
| Hercule | Poirot |
| Lincoln | Rhyme |
| Sherlock | Holmes |
+------------+---------------+
10 rows in set (0.00 sec)
Selanjutnya kita membuat function
mysql> delimiter //
mysql> create function nama_lengkap(in_nama_depan varchar(15),
in_nama_belakang varchar(15))
-> returns varchar(35)
-> begin
-> return concat(in_nama_depan,' ',in_nama_belakang);
-> end //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>select nama_lengkap(nama_depan,nama_belakang) from pekerja;
+-----------------------------------------+
| nama_lengkap(nama_depan, nama_belakang) |
+-----------------------------------------+
| John Doe |
| Alison Mathews |
| James Smith |
| Celia Rice |
| Robert Black |
| Linda Green |
| David Larry |
| Hercule Poirot |
| Lincoln Rhyme |
| Sherlock Holmes |
+-----------------------------------------+
10 rows in set (0.00 sec)
1.2 Meelihat fuction yang telah dibuat
Function apa saja yang telah kita buat dapat dilihat menggunakan
statement SHOW FUNCTION STATUS.
mysql> show function status;
1.3 Menghapus function
Function yang telah dibuat dapat dihapus menggunakan DROP FUNCTION
nama_function.
mysql> DROP FUNCTION full_name;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW FUNCTION STATUS;
Empty set (0.00 sec)
2. Procedure
Procedure dapat berisi statement SQL (INSERT, UPDATE, DELETE, SELECT)
atau operasi lain yang disimpan dalam database. Sebuah procedure
dapat dipanggil menggunakan statement CALL nama_procedure disertai
parameter yang diperlukan.
2.1 membuat procedure
sintaks untuk membuat procedure adalah sebagai berikut;
CREATE PROCEDURE nama_procedure (parameter_procedure())routine_body
Berikut contoh pembuatan procedure:
mysql> delimiter //
mysql> create procedure show_pekerja()
-> begin
-> select*from pekerja;
-> end //
Query OK, 0 rows affected (0.00 sec)
mysql> call show_pekerja ();
+------------+---------------+--------+---------+-----------+
| nama_depan | nama_belakang | alamat | gaji | pekerjaan |
+------------+---------------+--------+---------+-----------+
| John | Doe | Sleman | 500000 | Programer |
| Alison | Mathews | Sleman | 500000 | Programer |
| James | Smith | Bantul | 1000000 | Manager |
| Celia | Rice | Klaten | 1000000 | Manager |
| Robert | Black | Klaten | 200000 | Security |
| Linda | Green | Bantul | 200000 | Security |
| David | Larry | Bantul | 500000 | Programer |
| Hercule | Poirot | Klaten | 500000 | Programer |
| Lincoln | Rhyme | Klaten | 500000 | Programer |
| Sherlock | Holmes | Sleman | 1000000 | Manager |
+------------+---------------+--------+---------+-----------+
10 rows in set (0.00 sec)
Query OK, 0 rows affected (0.13 sec)
2.2 parameter dalam procedure
Procedure menerima parameter (proc_parameter) yang didefinisikan
sebagai berikut:
proc_parameter:
[ IN | OUT | INOUT ] param_name type
Dalam parameter procedure, didefinisikan jenis parameter, yaitu IN,
OUT, atau INOUT. Bila tidak dispesifikasikan saat pembuatan, maka
default jenis parameter yang dipilih adalah IN.
 Parameter IN mengirimkan sebuah nilai ke dalam procedure.
Procedure bisa saja merubah nilai parameter ini, namun
perubahan tersebut tidak visibel terhadap pemanggil procedure
ketika procedure tersebut selesai.
 Parameter OUT mengirimkan nilai dari procedure ke pemanggil.
Nilai inisial dari parameter ini adalah NULL dan nilainya
visibel terhadap pemanggil.
 Parameter INOUT diinisialisasi oleh pemanggil, dapat
dimodifikasi oleh procedure, dan perubahan nilai parameter
visibel terhadap pemanggil ketika procedure selesai.
2.2.1parameter IN
Berikut adalah contoh penggunaan parameter IN:
mysql> delimiter $$
mysql> create procedure getPekerjaByAlamat (in namaAlamat
varchar(255))
-> begin
-> select*from pekerja where alamat LIKE namaAlamat;
-> end $$
Mysql> delimiter ;
Pada contoh di atas, dibuat procedure getPekerjaByAlamat dengan
satu parameter masukan berjenis IN bernama namaAlamat. Procedure
ini digunakan untuk menampilkan data pada tabel pekerja dengan nama
kota sesuai parameter masukan. Pemanggilan procedure ini dapat
dilihat pada contoh di bawah ini. Pada contoh ini, kita memasukkan
Sleman” sebagai parameter masukan procedure.
mysql> call getPekerjaByAlamat("Sleman");
+------------+---------------+--------+
| nama_depan | nama_belakang | Alamat |
+------------+---------------+--------+
| John | Doe | Sleman |
| Alison | Mathews | Sleman |
| Sherlock | Holmes | Sleman |
+------------+---------------+--------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
2.2.2parameter OUT
berikut adalah contoh penggunaan parameter OUT;
mysql> delimiter :)
mysql> create procedure getNumPekerja (out numPekerja int)
-> begin
-> select count(*) into numPekerja from pekerja;
-> end :)
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
Pada contoh di atas, dibuat procedure untuk menampilkan jumlah
pekerja dalam tabel. Hasil query tersebut disimpan dalam variabel
numPekerja dengan statement INTO numPekerja. Pemanggilan pekerja
dengan parameter OUT dilakukan dengan menggunakan variabel session
yang diawali dengan karakter @. Pemanggilan procedure getNumPekerja
ditunjukkan sebagai berikut:
mysql> call getNumPekerja(@num);
Query OK, 1 row affected (0.05 sec)
mysql> select @num;
+------+
| @num |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
2.2.3parameter INOUT
berikut adalah contoh penggunaan parameter INOUT:
mysql> delimiter ^^
mysql> create procedure increase(inout number int)
-> begin
-> set number = number + 15;
-> end ^^
Query OK, 0 rows affected (0.04 sec)
mysql> delimiter ;
Pada contoh di atas, kita membuat procedure increase untuk
menambahkan input dengan nilai 15. Memodifikasi nilai parameter
input dilakukan dengan menggunakan SET. Contoh berikut
memperlihatkan bagaimana memanggil procedure increase. Kita
mendefinisikan terlebih dahulu variabel session @num dengan nilai
100. Kemudian setelah pemanggilan increase, nilai @num menjadi 115.
mysql> set @num = 100;
Query OK, 0 rows affected (0.00 sec)
mysql> call increase(@num);
Query OK, 0 rows affected (0.00 sec)
mysql> select @num;
+------+
| @num |
+------+
| 115 |
+------+
1 row in set (0.00 sec)
2.2.4melihat procedure yang telah dibuat
Procedure yang telah kita buat dapat dilihat menggunakan statement
SHOW PROCEDURE STATUS sebagai berikut:
mysql> show procedure status;
2.2.5menghapus procedure
Procedure yang telah kita buat dapat dihapus menggunakan DROP
PROCEDURE.
Mysql> DROP PROCEDURE increaseGaji;
INPUT DATA DENGAN PROCEDURE
mysql> create table hoho( nama varchar(10), alamat varchar(10));
Query OK, 0 rows affected (0.13 sec)
mysql> delimiter #
mysql> create procedure isi( in nama varchar(10), alamat
varchar(10))
-> begin
-> insert into hoho values(nama, alamat);
-> end #
Query OK, 0 rows affected (0.02 sec)
call isi("Didi","Sleman");
call isi("Fera","Abarawa");
call isi("Rara","Jogja");
mysql> select * from hoho;
+------+---------+
| nama | alamat |
+------+---------+
| Fera | Abarawa |
| Rara | Jogja |
| Didi | Sleman |
+------+---------+
HAPUS DATA DENGAN PROCEDURE
mysql> delimiter #
mysql> create procedure hapus_data(in par_nama varchar(10))
-> begin
-> delete from hoho where nama=par_nama;
-> end#
Query OK, 0 rows affected (0.00 sec)
Mysql> delimiter ;
Call hapus_data(“Didi”);
mysql> select * from hoho;
+------+---------+
| nama | alamat |
+------+---------+
| Fera | Abarawa |
| Rara | Jogja |
+------+---------+
4.Pemrograman di Function dan Procedure
Di dalam function dan procedure, kita bisa memasukkan logika
pemrograman. Ada beberapa karakteristik pemrograman yang didukung oleh
MySQL. Beberapa di antaranya adalah penggunaan variabel, kendali
kondisional, dan perulangan.
4.1 Variabel
Seperti pada pemrograman pada umumnya, kita bisa menggunakan variabel
lokal pada function dan procedure. Pendeklarasian variabel memiliki
sintaks sebagai berikut:
DECLARE var_name [, var_name] ... type [DEFAULT value]
Nilai inisialisasi variabel dapat dilakukan menggunakan statement
DEFAULT. Jika statement DEFAULT tidak digunakan, maka nilai inisialisasi
variabel adalah NULL. Penamaan variabel lokal bersifat case insensitive.
Berikut adalah beberapa contoh deklarasi variabel:
DECLARE total_sale INT
DECLARE x, y INT DEFAULT 0
Pemberian nilai ke sebuah variabel dilakukan dengan menggunakan
statement SET. Hasil dari query juga dapat dimasukkan ke dalam variabel
menggunakan SELECT INTO. Berikut … adalah beberapa contoh pemberian
nilai ke variabel.
SET total_sale = 50;
SELECT COUNT(*) INTO numPekerja FROM pekerja;
Ruang lingkup variabel adalah di antara blok BEGIN … END di mana
variabel tersebut didefinisikan. Variabel dapat diakses dari blok yang
berada dalam blok di mana ia didefinisikan, kecuali pada blok yang
memiliki deklarasi nama variabel yang sama. Berikut adalah contoh
penggunaan variabel dalam function dan stored procedure.
mysql> CREATE FUNCTION addTax(gaji FLOAT(8,2))
-> RETURNS FLOAT (8,2)
-> BEGIN
-> DECLARE tax FLOAT DEFAULT 0.05;
-> RETURN gaji * (1 - tax);
-> END ^_^
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
Pada contoh di atas, dibuat sebuah function dengan variabel bernama tax.
Variabel ini diset memiliki nilai default 0.05 dan digunakan untuk
mengubah nilai gaji. Contoh di bawah ini menunjukkan penggunaan function
addTax.
mysql> select nama_depan, addTax(gaji) from pekerja;
+------------+--------------+
| nama_depan | addTax(gaji) |
+------------+--------------+
| John | 475000.00 |
| Alison | 475000.00 |
| James | 950000.00 |
| Celia | 950000.00 |
| Robert | 190000.00 |
| Linda | 190000.00 |
| David | 475000.00 |
| Hercule | 475000.00 |
| Lincoln | 475000.00 |
| Sherlock | 950000.00 |
+------------+--------------+
10 rows in set, 3 warnings (0.28 sec)
Nama variabel lokal seharusnya tidak sama dengan nama kolom dalam tabel
database. Jika pada statement SQL seperti SELECT terdapat referensi ke
kolom tabel dengan nama yang sama, MySQL mereferensikannya sebagai nama
variabel. Berikut adalah contohnya.
mysql> DELIMITER **
mysql> CREATE PROCEDURE checkScope()
-> BEGIN
-> DECLARE nama_depan VARCHAR(15) DEFAULT 'bob';
-> SELECT nama_depan FROM pekerja;
-> END **
Query OK, 0 rows affected (0.54 sec)
mysql> DELIMITER ;
mysql> call checkScope();
+------------+
| nama_depan |
+------------+
| bob |
| bob |
| bob |
| bob |
| bob |
| bob |
| bob |
| bob |
| bob |
| bob |
+------------+
10 rows in set (0.00 sec)
Pada contoh di atas, ketika kita melakukan pemilihan SELECT untuk
nama_depan, nilai yang ditampilkan adalah nilai default dari variable
nama_depan, yaitu 'bob'.
4.2 Kendali Kondisional
Seperti layaknya bahasa pemrograman, kita juga bisa mendefinisikan
kendali kondisional di dalam function dan procedure. Kendali kondisional
yang disediakan dalam MySQL adalah IF dan CASE.
4.2.1 Kendali IF
Sintaks dasar dari IF adalah sebagai berikut:
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] …
[ELSE statement_list]
END IF;
Nilai search_condition dievaluasi. Jika bernilai true, maka
statement_list setelah THEN dijalankan. Namun
jika bernilai false, maka statement_list pada ELSE yang dijalankan.
Penggunaan banyak kondisi dapat dilakukan dengan statement ELSEIF.
Berikut adalah contoh penggunaan IF:
mysql> delimiter &&
mysql> create function hideGaji(gaji float(8,2))
-> returns varchar(20)
-> begin
-> declare bayaran varchar(20);
-> if gaji <4000 then set bayaran='Gaji Rendah';
-> else set bayaran='Gaji Tinggi';
-> end if;
-> return bayaran;
-> end &&
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> select nama_depan, nama_belakang, hideGaji(gaji)from pekerja;
+------------+---------------+----------------+
| nama_depan | nama_belakang | hideGaji(gaji) |
+------------+---------------+----------------+
| John | Doe | Gaji Tinggi |
| Alison | Mathews | Gaji Tinggi |
| James | Smith | Gaji Tinggi |
| Celia | Rice | Gaji Tinggi |
| Robert | Black | Gaji Tinggi |
| Linda | Green | Gaji Tinggi |
| David | Larry | Gaji Tinggi |
| Hercule | Poirot | Gaji Tinggi |
| Lincoln | Rhyme | Gaji Tinggi |
| Sherlock | Holmes | Gaji Tinggi |
+------------+---------------+----------------+
10 rows in set, 3 warnings (0.00 sec)
4.2.2 Kendali CASE
Sintaks dari kendali CASE adalah sebagai berikut:
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
Pada sintaks di atas, case_value dibandingkan dengan semua nilai
when_value sampai ditemukan yang sesuai. Jika ditemukan, maka
statement_list pada WHEN yang bersesuaian akan dijalankan. Jika tidak
ada nilai when_value yang sesuai, maka statement_list pada ELSE yang
dijalankan (jika ada). Berikut adalah contoh penggunaan CASE:
mysql> DELIMITER ##
mysql> CREATE FUNCTION calcTax(job VARCHAR (20)))
-> RETURNS FLOAT(3,2)
-> BEGIN
-> DECLARE tax FLOAT(3,2) DEFAULT 0.05;
-> CASE job
-> WHEN 'Manager' THEN SET tax = 0.1;
-> WHEN 'Programmer' THEN set tax = 0.07;
-> WHEN 'Tester' THEN set tax = 0.06;
-> ELSE SET tax = 0.05;
-> END CASE;
-> RETURN tax;
-> END ##
Query OK, 0 rows affected (0.06 sec)
mysql> delimiter ;
mysql> SELECT nama_depan, nama_belakang, calcTax(description) FROM
employee;
+------------+-----------+----------------------+
| nama_depan | nama_belakang | calcTax(description) |
+------------+-----------+----------------------+
| John | Doe | 0.07 |
| Alison | Mathews | 0.06 |
| James | Smith | 0.06 |
| Celia | Rice | 0.10 |
| Robert | Black | 0.06 |
| Linda | Green | 0.06 |
| David | Larry | 0.10 |
| Hercule | Poirot | 0.05 |
| Lincoln | Rhyme | 0.05 |
| Sherlock | Holmes | 0.05 |
+------------+-----------+----------------------+
10 rows in set (0.00 sec)
Bentuk sintaks dari CASE yang lain adalah sebagai berikut:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
Pada sintaks di atas, search_condition di setiap klausa WHEN dievaluasi
hingga ditemukan klausa WHEN yang sesuai. Jika tidak ada klausa WHEN
yang sesuai, maka klausa ELSE yang dijalankan. Jika tidak ada klausa
ELSE ketika semua klausa WHEN tidak sesuai, maka akan terjadi Case not
found for CASE statement error. Berikut adalah contoh penggunaan sintaks
CASE … WHEN tersebut:
mysql> delimiter //
mysql> create function deskripsi(pekerjaan varchar(225))
-> returns float(3,2)
-> begin
-> declare des float(3,2) default 0.05;
-> case pekerjaan
-> when 'Manager' then set des=0.1;
-> when 'Programer' then set des=0.07;
-> when 'Security' then set des=0.06;
-> else set des =0.05;
-> end case;
-> return des;
-> end //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> select nama_depan, nama_belakang, deskripsi from pekerja;
+------------+---------------+-----------+
| nama_depan | nama_belakang | deskripsi |
+------------+---------------+-----------+
| John | Doe | NULL |
| Alison | Mathews | NULL |
| James | Smith | NULL |
| Celia | Rice | NULL |
| Robert | Black | NULL |
| Linda | Green | NULL |
| David | Larry | NULL |
| Hercule | Poirot | NULL |
| Lincoln | Rhyme | NULL |
| Sherlock | Holmes | NULL |
+------------+---------------+-----------+
10 rows in set (0.00 sec)
4.3 Perulangan
Pada function dan procedure juga disediakan perulangan. Beberapa bentuk
perulangan yang disediakan
dalam MySQL adalah WHILE,REPEAT … UNTIL, dan LOOP.
4.3.1 Perulangan WHILE
Bentuk sintaks untuk perulangan WHILE adalah sebagai berikut:
WHILE search_condition DO
statement_list
END WHILE
Statement_list yang terdapat dalam WHILE diulang selama search_condition
bernilai true.
statement_list terdiri atas satu atau lebih statement SQL, setiap
statementnya dipisahkan dengan
delimiter titik koma (;). Berikut adalah contoh penggunaan WHILE.
mysql> create procedure mod12(in number int(10))
-> begin
-> while number mod 12>0 do
-> set number = number + 1;
-> end while;
-> select number;
-> end //
Query OK, 0 rows affected (0.11 sec)
mysql> delimiter ;
mysql> call mod12(10);
+--------+
| number |
+--------+
| 12 |
+--------+
1 row in set (0.05 sec)
Query OK, 0 rows affected (0.06 sec)
mysql> call mod12(24);
+--------+
| number |
+--------+
| 24 |
+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
4.3.2 Perulangan REPEAT … UNTIL
Sintaks dari REPEAT UNTIL … adalah sebagai berikut:
REPEAT
statement_list
UNTIL search_condition
END REPEAT
Statement_list di dalam REPEAT dilakukan secara berulang hingga ekspresi
search_condition bernilai true. Oleh karena itu, sebuah REPEAT memasuki
perulangan paling sedikit sebanyak satu kali. statment_list terdiri atas
satu atau lebih statement, masing-masing dipisah dengan delimiter titik
koma (;). Berikut adalah contoh penggunaan REPEAT … UNTIL.
mysql> create procedure repeatDemo(in number int(10))
-> begin
-> repeat
-> set number = number + 1;
-> until number mod 12 = 0
-> end repeat;
-> select number;
-> end //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call repeatDemo(10);
+--------+
| number |
+--------+
| 12 |
+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
4.3.3 Perulangan LOOP
Sintaks dari perulangan LOOP adalah sebagai berikut:
[begin_label:] LOOP
statement_list
END LOOP [end_label]
LOOP merupakan bentuk perulangan sederhana. Perulangan dilakukan
terhadap statement_list, yang terdiri atas beberapa statement dengan
dipisahkan oleh tanda titik koma (;). Statement di dalam LOOP diulang
sampai LOOP berakhir. Cara mengakhiri LOOP biasanya dilakukan dengan
statement LEAVE. Tanda perulangan dilakukan menggunakan ITERATE. Berikut
adalah contoh penggunaan LOOP.
mysql> delimiter //mysql> delimiter //
mysql> create procedure iterateDemo(number int)
-> begin
-> label1: loop
-> set number = number + 1;
-> if number mod 2>0 then
-> iterate label1;
-> end if;
-> leave label1;
-> end loop label1;
-> select number;
-> end //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call iterateDemo(10)
-> ;
+--------+
| number |
+--------+
| 12 |
+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> call iterateDemo(20);
+--------+
| number |
+--------+
| 22 |
+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
TUGAS AKHIR PRAKTIKUM SMBD
Buatlah sebuah perancangan basis data untuk sebuah sistem informasi ( lihat di daftar sistem informasi yang
ada, tiap mahasiswa beda per kelas ) dengan ketentuan sbb :
1.
1. Buatlah DFD untuk sistem informasi yang anda buat.
2. Minimal memiliki tabel 4 tabel ( lebih banyak lebih baik ) dan harus ada minimal dua table
yang berelasi dengan tabel lainnya
3. Isi data minimal 12 data per tabel.
4. Sertakan definisi/struktur masing – masing tabel
5. Buatlah relasi tabel pada database yang anda buat
Setelah anda membuat perancangan databasenya maka buatlah database tersebut ke MySQL dengan
ketentuan sbb :
1.
1. Gunakan syntax procedure untuk insert dan delete data di dalam tabel.
2. Menampilkan setiap data menggunakan syntax view
3. Updatelah salah satu tabel (bebas) dengan menggunakan trigger
Format laporan
1.
1. Laporan diketik rapi dan di cetak diatas kertas HVS ukuran A4 min 70gr
2. Dijilid rapi menggunakan mika bening warna putih
3. Font : Times New Roman Ukuran : 14 untuk judul 12 untuk text biasa ( kecuali cover bebas )
4. Tuliskan script program MySQL yang anda buat secara rapi dan sertakan printscreen hasil
dari script programnya.
5. Dikumpulkan maksimal sebelum responsi.
6. Tugas Akhir adalah syarat ikut Responsi
No. Komp Judul SI No. Komp Judul SI
1 SI Terminal 11 SI Akademik (KRS)
2 SI Swalayan 12 SI Apotek
3 SI Rental Mobil 13 SI Bandara
4 SI Perpustakaan 14 SI Bimbingan Belajar
5 SI Lab Kesehatan 15 SI Biro Perjalanan
6 SI Biro Perjalanan 16 SI Lab Kesehatan
7 SI Bimbingan Belajar 17 SI Perpustakaan
8 SI Bandara 18 SI Rental Mobil
9 SI Apotek 19 SI Swalayan
10 SI Akademik (KRS) 20 SI Terminal
JUDUL SISTEM INFORMASI yang dibuat sesuai dengan Nomor komputer saat praktikum.

Share on Google Plus

About Unknown

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.