MATERI PART 2 – Prak. BASISDATA
TABEL dan MANIPULASI DATA
LABORATORIUM 4 – BASISDATA
MENU PART INI :
1.
Tabel dengan PRIMARY KEY
2.
Memberikan nilai UNIQUE
3.
Parameter IF NOT EXISTS
4.
Menyalin tabel dengan statement SELECT
5.
Membuat TEMPORARY TABLE
6.
Tabel dengan FOREIGN KEY
7.
Memodifikasi tabel dengan ALTER TABLE
i. Menambah
kolom pada tabel
ii. Memodifikasi
nama tabel
iii. Memodifikasi
definisi tabel
iv. Mengganti
nama kolom
v. Menghapus
kolom
vi. Menambah
primary key
vii. Menghapus
semua data dalam tabel
8.
Memasukkan data lebih dari 1 data dengan INSERT
9.
INSERT menggunakan data dari tabel lain
10. Mengubah
data menggunakan UPDATE
11. Mengganti
data dari tabel
12. Menghapus
data dari tabel
1. Tabel dengan PRIMARY KEY
PRIMARY
KEY
-
untuk menjadikan filed kunci agar tidak terjadi duplikasi
data
CODE :
mysql>
create table t_pegawai(id_peg varchar(8) primary key, nama_peg varchar(50),
alamat_peg varchar(50));
Query
OK, 0 rows affected (0.07 sec)
LIHAT
DESKRIPSINYA…
mysql>
desc t_pegawai;
+------------+-------------+------+-----+---------+-------+
| 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.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;
+----------+--------+
Jadi
setelah kita rubah struktur tabel anak, maka akan bisa ter delete.
Data
di tabel anak dan induk akan sama – sama ter hapus atau ter update.
Itulah
contoh penggunaan on delete cascade dan on upodate cascade
|
|
| id_mobil | type |
+----------+--------+
|
HON-01 | Jazz |
|
TOY-02 | Innova |
+----------+--------+
2 rows in set (0.01 sec)
mysql> select*from t_stok;
+---------+----------+------+
|
id_stok | id_mobil | stok |
+---------+----------+------+
|
ST-002 | TOY-02 | 521
|
|
ST-003 | HON-01 | 875
|
+---------+----------+------+
2
rows in set (0.00 sec)
7.
Memodifikasi
Tabel dengan ALTER TABLE
a. Menambah Kolom pada Tabel
Dalam menambah kolom
dalam tabel kita bisa menggunakan perintah alter table, contohnya kita akan
menambahkan kolom pada tabel t_mobil :
mysql> alter table t_mobil add
harga bigint(11);
Query OK, 2 rows affected
(0.16 sec)
Records: 2 Duplicates: 0
Warnings: 0
mysql> desc t_mobil;
+----------+-------------+------+-----+---------+-------+
| 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), al
amat_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
'G://LAB4/BASISDATA/file.txt' into table pegawai;
Query OK, 15 rows affected (0.63 sec)
Records: 15 Deleted: 0
Skipped: 0 Warnings: 0
1.
Mengambil Data dengan SELECT
Perintah
select untuk mengambil data sudah sering kita pakai sebelumnya, untuk mengambil
seluruh data yang ada di dalam suatu tabel kita bisa menggunakan perintah SELECT
* FROM <nama_tabel>
CONTOH :
mysql> select * from pegawai;
+----------+-----------------------+-------------+----------------+---------+
|
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 berjumlag 7 huruf.
mysql>
select nama_peg from pegawai where jabatan like 'M%';
+---------------+
| nama_peg
|
+---------------+
| Felix Nababan |
+---------------+
1 row in set (0.00 sec)
Perintah di atas menunjukkan data yang
diminta adalah data nama pegawai yang jabatannya berawalan huruf ‘M’
mysql>
select nama_peg from pegawai where alamat_peg like '%A';
+-----------------------+
| nama_peg |
+-----------------------+
| Soeharto Mangundirejo |
| Olga Syahputra |
| Ely Oktafiani |
| Rahmadi Sholeh |
| Ilham Ungara
|
| Paijem |
+-----------------------+
6 rows in set (0.00 sec)
Perintah di atas menunjukkan data yang
diminta adalah data nama pegawai yang alamatnya berakhiran huruf ‘A’
6. Query
data unik menggunakan DISTINCT
Perintah DISTINCT digunakan untuk
menampilkan data yang unik, jadi jika ada data yang sama tidak akan
ditampilkan.
mysql>
select alamat_peg from pegawai;
+-------------+
| alamat_peg
|
+-------------+
| Yogyakarta
|
| Medan
|
| Jakarta
|
| Bandung
|
| Jawa Tengah |
| Malang
|
| Kudus
|
| Yogyakarta
|
| Magelang
|
| Solo
|
| Yogyakarta
|
| Jakarta
|
| Madiun
|
| Makasar
|
| Yogyakarta
|
+-------------+
15 rows in set (0.00 sec)
mysql>
select distinct alamat_peg from pegawai;
+-------------+
| alamat_peg
|
+-------------+
| Yogyakarta
|
| Medan
|
| Jakarta
|
| Bandung
|
| Jawa Tengah |
| Malang
|
| Kudus
|
| Magelang
|
| Solo
|
| Madiun
|
| Makasar
|
+-------------+
11 rows in set (0.05 sec)
7. Membatasi
hasil query dengan LIMIT
Perintah
LIMIT ini akan membatasi jumlah data yang akan kita tampilkan.
mysql> select * from pegawai
LIMIT 5;
+----------+-----------------------+-------------+-------------+---------+
|
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
mencara data dalam range, ada juga NOT
BETWEEN untuk mencarai data diluar range yang kita tentukan.
CATATAN
: penginputan range diwalai dengan urutan yang lebih kecil, contoh 4jt – 9jt,
atau Ely O – Olga S
mysql>
select nama_peg, gaji from pegawai WHERE nama_peg NOT BETWEEN "Ely Oktafi
ani"
AND "Olga Syahputra";
+-----------------------+---------+
|
nama_peg | gaji |
+-----------------------+---------+
|
Soeharto Mangundirejo | 9000000 |
|
Chelsea Olivia | 6000000 |
|
Tuti Wardani | 4500000 |
|
Budi Drajat | 4500000 |
|
Bambang Pamungkas | 3000000 |
|
Rani Wijaya | 3000000 |
|
Rano Karno | 2000000 |
|
Rahmadi Sholeh | 2000000 |
|
Donny Damara | 1000000 |
|
Paijem | 500000 |
+-----------------------+---------+
10 rows in set (0.00 sec)
Menunjukkan hasil dari NOT BETWEEN dalam
menampilkan data yang bukan antara Ely Oktafiani dan Olga Syahputra
15.MAX, MIN, AVERAGE data dari
tabel
Perintah untuk menunjukkan nilai
maksimal atau minimal ataupun nilai rata – rata juga bisa kita lakukan di
MySQL, dengan perintah SELECT diikuti oleh MIN, MAX, atau AVERAGE, untuk lebih
jelasnya kita lihat contoh :
Mencari nilai MINIMAL è MIN
mysql> select MIN(gaji) from pegawai;
+-----------+
|
MIN(gaji) |
+-----------+
| 500000 |
+-----------+
1 row in set (1.05 sec)
Mencari nilai MAKSIMAL è MAX
mysql> select MAX(gaji) from pegawai;
+-----------+
|
MAX(gaji) |
+-----------+
| 9000000 |
+-----------+
1 row in set (0.00 sec)
Mencari nilai RATA - RATA è AVG
mysql> select AVG(gaji) from pegawai;
+--------------+
|
AVG(gaji) |
+--------------+
|
3766666.6667 |
+--------------+
1 row in set (0.41 sec)
Menghitung Nilai Total è SUM
mysql> select SUM(gaji) from pegawai;
+-----------+
|
SUM(gaji) |
+-----------+
| 56500000 |
+-----------+
1 row in set (0.39 sec)
SUB QUERY
Sub
Query adalah penggunaan statement SELECT di dalam statement SELECT, Sub Query
dideklarasikan di dalam perintah my qsl di dalam tanda (), dan biasa
menggunakan salah satu dari statemen SELECT, UPDATE, SET, DELETE, atau DO
CONTOH :
mysql> select nama_peg, gaji from pegawai
where gaji = (select max(gaji) from pe
gawai);
+-----------------------+---------+
| nama_peg | gaji |
+-----------------------+---------+
| Soeharto Mangundirejo |
9000000 |
+-----------------------+---------+
1 row in
set (0.06 sec)
Perintah
ini hanya bisa menerima satu(1) buah hasil dari sub query, jika hasil dari sub
query ada lebih adari satu maka akan terjadi error.
mysql> select nama_peg, gaji from pegawai
where alamat_peg = (select alamat_peg
from pegawai where alamat_peg LIKE 'J%');
ERROR
1242 (21000): Subquery returns more than 1 row
Perintah
diatas ini ERROR karena hasil sub query diatas ada lebih dari satu(1)
1.
Sub query dengan ALL
Command ALL
diikuti dengan operator perbandingan digunakan memiliki arti menampilkan nilai
jika perbandingan bernilai benar untuk semua data. Berikut adalah contoh
penggunaannya.
mysql> select nama_peg, gaji from pegawai
where gaji < ALL(select gaji FROM pega
wai WHERE gaji > '2500000');
+----------------+---------+
| nama_peg | gaji
|
+----------------+---------+
| Rano Karno | 2000000 |
| Rahmadi Sholeh | 2000000 |
| Ilham Ungara | 2000000 |
| Endang Melati | 2000000 |
| Donny Damara | 1000000 |
| Paijem |
500000 |
+----------------+---------+
6 rows in set
(0.00 sec)
Contoh
diatas menunjukkan penampilan atas nama pegawai beserta gajinya yang memiliki
gaji kurang dari 2,5jt
2.
Sub query dengan ANY
Command
ANY diikuti dengan operator perbandingan memiliki arti menampilkan nilai yang
sesuai dengan apapun yang dihasilkan oleh sub query. Alias dari ANY adalah
SOME. Berikut adalah contohnya:
mysql> select nama_peg, gaji from pegawai
where gaji < ANY(select gaji FROM pega
wai WHERE gaji > '2000000');
+-------------------+---------+
| nama_peg | gaji |
+-------------------+---------+
| Felix Nababan | 8000000 |
| Olga Syahputra | 6000000 |
| Chelsea Olivia | 6000000 |
| Tuti Wardani | 4500000 |
| Budi Drajat | 4500000 |
| Bambang Pamungkas |
3000000 |
| Ely Oktafiani | 3000000 |
| Rani Wijaya | 3000000 |
| Rano Karno | 2000000 |
| Rahmadi Sholeh | 2000000 |
| Ilham Ungara | 2000000 |
| Endang Melati | 2000000 |
| Donny Damara | 1000000 |
| Paijem |
500000 |
+-------------------+---------+
14 rows
in set (0.00 sec)
Pada
perintah diatas kita perhatikan perintah select yang berada di dalam kurung
(Sub Query), pada perintah itu menjelaskan bahwa penampilan daftar nama pegawai
yang gajinya lebih dari 2jt, sedangkan Quert seutuhnya menampilkan nama pegawai
yang gajinya lebih kecil daripada hasil sub query, Soeharto Mangundirejo tidak
keluar di hasil karena tidak ada yang mempunyai gaji sebesar dirinya.
mysql> select nama_peg, gaji from pegawai
where gaji < SOME(select gaji FROM peg
awai WHERE gaji > '2000000');
+-------------------+---------+
| nama_peg | gaji |
+-------------------+---------+
| Felix Nababan | 8000000 |
| Olga Syahputra | 6000000 |
| Chelsea Olivia | 6000000 |
| Tuti Wardani | 4500000 |
| Budi Drajat | 4500000 |
| Bambang Pamungkas |
3000000 |
| Ely Oktafiani | 3000000 |
| Rani Wijaya | 3000000 |
| Rano Karno | 2000000 |
| Rahmadi Sholeh | 2000000 |
| Ilham Ungara | 2000000 |
| Endang Melati | 2000000 |
| Donny Damara | 1000000 |
| Paijem |
500000 |
+-------------------+---------+
14 rows
in set (0.45 sec)
3.
Sub query dengan EXISTS
Perintah
EXISTS disini berguna untuk mengartur penampilan hasil query, Query Utama akan
dijalankan jika Sub Query bernilai TRUE (ada hasilnya) jika hasilnya kosong
maka Query utama tidak akan dijalankan. Lawan dari statement EXISTS adalah NOT
EXISTS
mysql> select * from pegawai where EXISTS
(select * from pegawai where alamat_pe
g = 'Yogyakarta');
+----------+-----------------------+-------------+----------------+---------+
| 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 | 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.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 | 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 | 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 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)
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 | 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 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)