Membuat Database 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

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