innovation for better future

Berikut beberapa catatan query MySQL yang pernah saya gynakan untuk beberapa contoh kasus. Perhatikan nama tabel dan nama kolomnya dan sesuaikan dengan kebutuhan.

Mencari yang akan naik pangkat dalam 180 hari (6 bulan) kedepan dari kolom "tmt_pangkat_next":

SELECT * FROM duk_pns WHERE tmt_pangkat_next BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 6 MONTH);

Mengecek Event Scheduler:

SHOW VARIABLES LIKE 'event_scheduler';

Menghidupkan Event Scheduler:

SET GLOBAL event_scheduler = 1;

Mematikan Event Scheduler:

SET GLOBAL event_scheduler = 0;

Menghapus Event:

DROP EVENT event_name;

Skema Event Scheduler:

CREATE EVENT event_name
ON SCHEDULE EVERY {x}
{SECOND | MINUTE | HOUR | DAY | MONTH | YEAR | WEEK}
DO
{SQL COMMAND};

Contoh:

CREATE EVENT UPDATE_GajiBerkala
ON SCHEDULE EVERY 1 DAY
DO
UPDATE duk_pns SET tmt_berkala_next=DATE_ADD(tmt_berkala_terakhir, INTERVAL 2 YEAR);

Menambahkan 2 tahun interval (Kenaikan Berkala):

UPDATE duk_pns SET tmt_berkala_next=DATE_ADD(tmt_berkala_terakhir, INTERVAL 2 YEAR);

Menambahkan 4 tahun interval (Kenaikan Pangkat):

UPDATE duk_pns SET tmt_pangkat_next=DATE_ADD(pangkat_tmt, INTERVAL 4 YEAR);

Menampilkan data hari berlalu dan hari akan datang untuk yang ULTAH bulan ini:

SELECT nama, tgl_lahir, DATEDIFF(CURDATE(), DATE_FORMAT(tgl_lahir,'2017-%m-%d')) AS usia FROM duk_pns WHERE MONTH(tgl_lahir) = MONTH(NOW());
SELECT nama, tgl_lahir, DATEDIFF(CURDATE(), DATE_FORMAT(tgl_lahir,CONCAT(YEAR(CURDATE()),'-%m-%d'))) AS usia FROM duk_pns WHERE MONTH(tgl_lahir) = MONTH(NOW());

Mencari yang ULTAH hari ini:

SELECT * FROM duk_pns WHERE MONTH(tgl_lahir) = MONTH(NOW()) AND DAY(tgl_lahir) = DAY(NOW());

Mencari yang ULTAH dalam bulan ini:

SELECT * FROM duk_pns WHERE MONTH(tgl_lahir) = MONTH(NOW());

Mencari yang akan ULTAH dalam 14 hari kedepan termasuk hari ini:

SELECT * FROM  duk_pns WHERE  DATE_ADD(tgl_lahir, 
INTERVAL YEAR(CURDATE())-YEAR(tgl_lahir) + IF(DAYOFYEAR(CURDATE()) >= DAYOFYEAR(tgl_lahir),1,0) YEAR)  
BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 14 DAY);

Mencari yang akan ULTAH dalam 14 hari kedepan TIDAK termasuk hari ini:

SELECT * FROM  duk_pns WHERE  DATE_ADD(tgl_lahir, 
INTERVAL YEAR(CURDATE())-YEAR(tgl_lahir) + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(tgl_lahir),1,0) YEAR) 
BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 14 DAY)
AND DATE_ADD(tgl_lahir, INTERVAL YEAR(CURDATE())-YEAR(tgl_lahir) YEAR) <> CURDATE();

Mencari yang akan NAIK GAJI BERKALA dalam 90 hari (3 bulan) kedepan dari kolom "tmt_berkala_next":

SELECT * FROM duk_pns WHERE tmt_berkala_next BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 3 MONTH);

Mengganti nama kelurahan yang ada di tabel data_dps dengan kode_kelurahan dari tabel kelurahan

UPDATE data_dps SET kelurahan = (SELECT kode FROM data_kelurahan WHERE nama = data_dps.kelurahan);

Mengganti nilai kolom dengan huruf besar

UPDATE table_name SET `column_name` = UPPER( `column_name` );

Mengganti data dalam kolom [kode] dengan gabungan data dari kolom [kode_kelurahan] dan [nomor]

UPDATE data_tps SET kode = CONCAT(kode_kelurahan, nomor);

Mengganti kolom TANGGAL_LAHIR dari CHAR menjadi DATE

1. Buat kolom baru sementara dgn nama 'tgl_lhr'
2. Jalankan queri:
	UPDATE data_dps SET tgl_lhr = STR_TO_DATE(tanggal_lahir,'%d-%m-%Y');
3. Hapus kolom tanggal_lahir
4. Ganti nama kolom tgl_lhr menjadi tanggal_lahir

UPDATE kolom UMUR berdasarkan TANGGAL_LAHIR
UPDATE data_dps SET umur=TIMESTAMPDIFF (YEAR, tanggal_lahir, CURDATE());
UPDATE 0data_dps SET umur=TIMESTAMPDIFF (YEAR, tanggal_lahir, '2019-04-17');

Mencari TANGGAL_LAHIR yang mengandung spasi

SELECT * FROM data_dpt WHERE tanggal_lahir like '% %';

Mengganti string (replace) spasi dalam kolom TANGGAL_LAHIR dengan tanda minus (-)

UPDATE data_dpt SET tanggal_lahir=REPLACE(tanggal_lahir,' ','-');

Mengganti (replace) string 121 dalam kolom NOMOR_NIK menjadi 108

UPDATE data_warga SET nomor_nik=REPLACE(nomor_nik,'121','108');