Query Database dengan MySQL

 

 

 

 

 

Berikut ini contoh-contoh perintah SQL di MySQL. Contoh kasusnya untuk membuat database penggajian sederhana dimana:

Gaji Bersih = Gaji Pokok + Tunjangan Transport + Bonus – Potongam
Tunjangan Transport = Jumlah Hari masuk * 20.000

A. MEMBUAT DATABASE β€œPAYROLLDB”

mysqladmin create payrolldb

*****************************************

B. MEMBUAT TABEL-TABEL YANG DIPERLUKAN

Membuat table β€œt_pegawai”

CREATE TABLE t_pegawai ( nip varchar (10), nama varchar (30), gapok int (8),PRIMARY KEY (nip) )

Membuat table β€œt_gajian”

CREATE TABLE t_gajian2 ( id int(11) AUTO_INCREMENT, nip varchar(10), periode varchar(6), masuk int(2), bonus float, potongan float, PRIMARY KEY (id))

*****************************************

C. MENAMBAHKAN DATA KE TABEL

Menambah data pada table β€œt_pegawai”

INSERT INTO `t_pegawai` VALUES (’090022001β€²,’Puji Hartono’,1500000);
INSERT INTO `t_pegawai` VALUES (’090022002β€²,’Mahaputra’,1750000);
INSERT INTO `t_pegawai` VALUES (’090022003β€²,’Tarmizi’,1900000);
INSERT INTO `t_pegawai` VALUES (’090022004β€²,’Iwan’,1500000);
INSERT INTO `t_pegawai` VALUES (’090022005β€²,’Agus’,2000000);
INSERT INTO `t_pegawai` VALUES (’090022006β€²,’Budi’,1100000);
INSERT INTO `t_pegawai` VALUES (’090022007β€²,’Amin’,1200000);
INSERT INTO `t_pegawai` VALUES (’090022008β€²,’Galih’,1400000);
INSERT INTO `t_pegawai` VALUES (’090022009β€²,’Irwan’,1700000);
INSERT INTO `t_pegawai` VALUES (’090022010β€²,’Miki’,1600000);

Menambah data pada table β€œt_pegawai”

INSERT INTO `t_gajian` VALUES (0,’090022061β€²,’200801β€²,20,300000,0);
INSERT INTO `t_gajian` VALUES (1,’090022062β€²,’200801β€²,19,0,0);
INSERT INTO `t_gajian` VALUES (2,’090022063β€²,’200801β€²,18,500000,300000);
INSERT INTO `t_gajian` VALUES (3,’090022064β€²,’200801β€²,20,0,0);
INSERT INTO `t_gajian` VALUES (4,’090022065β€²,’200801β€²,20,300000,500000);
INSERT INTO `t_gajian` VALUES (5,’090022066β€²,’200801β€²,18,0,0);
INSERT INTO `t_gajian` VALUES (6,’090022067β€²,’200801β€²,17,0,200000);
INSERT INTO `t_gajian` VALUES (7,’090022068β€²,’200801β€²,20,300000,300000);
INSERT INTO `t_gajian` VALUES (8,’090022069β€²,’200801β€²,20,250000,0);
INSERT INTO `t_gajian` VALUES (9,’090022070β€²,’200801β€²,18,600000,200000);

*****************************************

D. MELIHAT DATA

Menampilkan data dalam tabel t_pegawai

mysql> select * from t_pegawai;
+———–+————–+β€”β€”β€”+
| nip | nama | gapok |
+———–+————–+β€”β€”β€”+
| 090022001 | Puji Hartono | 1500000 |
| 090022002 | Mahaputra | 1750000 |
| 090022003 | Tarmizi | 1900000 |
| 090022004 | Iwan | 1500000 |
| 090022005 | Agus | 2000000 |
| 090022006 | Budi | 1100000 |
| 090022007 | Amin | 1200000 |
| 090022008 | Galih | 1400000 |
| 090022009 | Irwan | 1700000 |
| 090022010 | Miki | 1600000 |
+———–+————–+β€”β€”β€”+
10 rows in set (0.00 sec)


Menampilkan data dalam tabel t_gajian

mysql> select * from t_gajian;

+β€”-+———–+β€”β€”β€”+β€”β€”-+——–+β€”β€”β€”-+
| id | nip | periode | masuk | bonus | potongan |
+β€”-+———–+β€”β€”β€”+β€”β€”-+——–+β€”β€”β€”-+
| 0 | 090022061 | 200801 | 20 | 300000 | 0 |
| 1 | 090022062 | 200801 | 19 | 0 | 0 |
| 2 | 090022063 | 200801 | 18 | 500000 | 300000 |
| 3 | 090022064 | 200801 | 20 | 0 | 0 |
| 4 | 090022065 | 200801 | 20 | 300000 | 500000 |
| 5 | 090022066 | 200801 | 18 | 0 | 0 |
| 6 | 090022067 | 200801 | 17 | 0 | 200000 |
| 7 | 090022068 | 200801 | 20 | 300000 | 300000 |
| 8 | 090022069 | 200801 | 20 | 250000 | 0 |
| 9 | 090022070 | 200801 | 18 | 600000 | 200000 |
+β€”-+———–+β€”β€”β€”+β€”β€”-+——–+β€”β€”β€”-+

Mengurutkan data berdasar gaji pokok

mysql> select * from t_pegawai order by gapok;
+———–+————–+β€”β€”β€”+
| nip | nama | gapok |
+———–+————–+β€”β€”β€”+
| 090022006 | Budi | 1100000 |
| 090022007 | Amin | 1200000 |
| 090022008 | Galih | 1400000 |
| 090022001 | Puji Hartono | 1500000 |
| 090022004 | Iwan | 1500000 |
| 090022010 | Miki | 1600000 |
| 090022009 | Irwan | 1700000 |
| 090022002 | Mahaputra | 1750000 |
| 090022003 | Tarmizi | 1900000 |
| 090022005 | Agus | 2000000 |
+———–+————–+β€”β€”β€”+
10 rows in set (0.07 sec)

Mencari data pegawai

mysql> select * from t_pegawai where nama like β€˜%agus%’;
+———–+β€”β€”+β€”β€”β€”+
| nip | nama | gapok |
+———–+β€”β€”+β€”β€”β€”+
| 090022005 | Agus | 2000000 |
+———–+β€”β€”+β€”β€”β€”+
1 row in set (0.02 sec)

Membatasi output Query dengan limit

mysql> select * from t_pegawai limit 0,5;
+———–+————–+β€”β€”β€”+
| nip | nama | gapok |
+———–+————–+β€”β€”β€”+
| 090022001 | Puji Hartono | 1500000 |
| 090022002 | Mahaputra | 1750000 |
| 090022003 | Tarmizi | 1900000 |
| 090022004 | Iwan | 1500000 |
| 090022005 | Agus | 2000000 |
+———–+————–+β€”β€”β€”+
5 rows in set (0.00 sec)


Menjumlah sebuah field

mysql> select sum(gapok) AS Total_Gaji from t_pegawai;
+β€”β€”β€”β€”+
| Total_Gaji |
+β€”β€”β€”β€”+
| 15650000 |
+β€”β€”β€”β€”+
1 row in set (0.01 sec)

Mencari nilai field tertinggi

mysql> select max(gapok) AS Gaji_Tertinggi from t_pegawai;
+β€”β€”β€”β€”β€”-+
| Gaji_Tertinggi |
+β€”β€”β€”β€”β€”-+
| 2000000 |
+β€”β€”β€”β€”β€”-+
1 row in set (0.02 sec)

Mencari nilai field terendah

mysql> select min(gapok) AS Gaji_Terendah from t_pegawai;
+β€”β€”β€”β€”β€”+
| Gaji_Terendah |
+β€”β€”β€”β€”β€”+
| 1100000 |
+β€”β€”β€”β€”β€”+
1 row in set (0.01 sec)

Mencari nilai rata-rata dari sebuah field

mysql> select avg(gapok) AS Gaji_Rata_Rata from t_pegawai;
+β€”β€”β€”β€”β€”-+
| Gaji_Rata_Rata |
+β€”β€”β€”β€”β€”-+
| 1565000.0000 |
+β€”β€”β€”β€”β€”-+
1 row in set (0.02 sec)

Menghitung jumlah record

mysql> select count(nama) as Total from t_pegawai;
+β€”β€”-+
| Total |
+β€”β€”-+
| 10 |
+β€”β€”-+
1 row in set (0.00 sec)

*****************************************

E. MENGUBAH DATA

mysql> update t_pegawai set nama=’Iwan S’ where nip=’090022004β€²;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> select * from t_pegawai;
+———–+————–+β€”β€”β€”+
| nip | nama | gapok |
+———–+————–+β€”β€”β€”+
| 090022001 | Puji Hartono | 1500000 |
| 090022002 | Mahaputra | 1750000 |
| 090022003 | Tarmizi | 1900000 |
| 090022004 | Iwan S | 1500000 | <<= Data yang telah diubah
| 090022005 | Agus | 2000000 |
| 090022006 | Budi | 1100000 |
| 090022007 | Amin | 1200000 |
| 090022008 | Galih | 1400000 |
| 090022009 | Irwan | 1700000 |
| 090022010 | Miki | 1600000 |
+———–+————–+β€”β€”β€”+
10 rows in set (0.01 sec)

*****************************************

F. MENGHAPUS DATA

mysql> delete from t_pegawai where nip=’090022001β€²;
Query OK, 1 row affected (0.02 sec)mysql> select * from t_pegawai;
+———–+———–+β€”β€”β€”+
| nip | nama | gapok |
+———–+———–+β€”β€”β€”+
| 090022002 | Mahaputra | 1750000 |
| 090022003 | Tarmizi | 1900000 |
| 090022004 | Iwan S | 1500000 |
| 090022005 | Agus | 2000000 |
| 090022006 | Budi | 1100000 |
| 090022007 | Amin | 1200000 |
| 090022008 | Galih | 1400000 |
| 090022009 | Irwan | 1700000 |
| 090022010 | Miki | 1600000 |
+———–+———–+β€”β€”β€”+
9 rows in set (0.00 sec)

*****************************************

G. QUERY BEBERAPA TABEL

Terakhir, kita akan membuat penghitungan gaji berdasarkan:
– Jumlah hari masuk
– Bonus
– Potongan

Gaji Bersih = Gaji Pokok + (Jumlah Hari Masuk * 20.000) + Bonus – Potongan

mysql> select t_gajian.periode, t_pegawai.nip, t_pegawai.nama, gapok+ (masuk*20000) + bonus-potongan as β€˜Gaji Bersih’ from t_pegawai,t_gajian where t_pegawai.nip=t_gajian.nip;

+β€”β€”β€”+———–+————–+β€”β€”β€”β€”-+
| periode | nip | nama | Gaji Bersih |
+β€”β€”β€”+———–+————–+β€”β€”β€”β€”-+
| 200801 | 090022001 | Puji Hartono | 2200000 |
| 200801 | 090022002 | Mahaputra | 2130000 |
| 200801 | 090022003 | Tarmizi | 2460000 |
| 200801 | 090022004 | Iwan S | 1900000 |
| 200801 | 090022005 | Agus | 2200000 |
| 200801 | 090022006 | Budi | 1460000 |
| 200801 | 090022007 | Amin | 1340000 |
| 200801 | 090022008 | Galih | 1800000 |
| 200801 | 090022009 | Irwan | 2350000 |
| 200801 | 090022010 | Miki | 2360000 |
+β€”β€”β€”+———–+————–+β€”β€”β€”β€”-+
10 rows in set (0.00 sec)

*****************************************

H. MEMBACKUP/MERESTOR DATA

Membackup
mysqldump –u root –p payrooldb > backup.sql

Merestore
Mysql –u root –p payrooldb < backup.sql

 

sumber

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s