Antara MySQL dan Microsoft Excel

MySQL adalah suatu platform database yang saat ini banyak digunakan. Dalam database kita kenal istilah SQL atau Structure Query Language. Juga dalam database kita kenal DML(Data Manipulation Language), DDL (Data Definition Language), dan DAL(Data Access List). Tapi kita tidak akan bahas semua diatas untuk kesempatan kali ini.

Microsoft Excel adalah sebuah platform Microsoft yang biasa dipakai oleh kita dalam membuat dokumen yang berupa tabulasi atau setidaknya berbentuk tabel. Platform ini sangat terkenal di dunia, sebagai sebuah alat bantu kerja terutama bagi para pekerja yang suka sekali dengan kegiatan laporan dan hitung-menghitung. Dan kali ini pun kita tidak akan membahas pula Microsoft Excel pada kesempatan kali ini.

Lalu apa yang akan kita diskusikan pada kesempatan kali ini? Yang akan kita diskusikan pada kesempatan kali ini adalah menjadikan Microsoft Excell sebagai alat bantu SQL untuk merancang query pada database MySQL. Hmmm menarikkah..? terserah silahkan kita lihat sendiri nantinya.

Terkadang jika seorang IT Engineer terbiasa bermain-main dengan database, pasti paham bagaimana sebuah DML yang akan dieksekusi ke dalam SQL Console harus dirancang dulu sebelum mengeksekusinnya ke dalam SQL console, terutama untuk Query-Query yang tidak hanya memakai satu tabel saja. Dalam artian ada Join Table pada query yang hendak dirancangnya. Dalam pembuatan report misalnya, table a harus di join dengan table b dengan primary keynya adalah kolom z, dan lain-lain.

Ok, let’s cut this bullshit off, just go straight to the point.

..:: Menjadikan excel sheet sebagai wadah hasil query pada MySQL::..

Membuat excel sheet menjadi wadah hasil dari SQL Query yang kita lakukan pada database MySQL, dapat kita lakukan dengan cara membuang output dari hasil query tersebut kepada sebuah file xls. Perhatikan contoh berikut ini:

Katakanlah kita punya sebuah table dengan struktur seperti ini:

Kemudian kita diminta untuk membuat sebuah laporan list dari tabel ini yang LOGIN_DATE nya tertentu, misal selama bulan Desember 2009 dan menempatkan dalam sebuah file Microsoft Excel. Rancangan query yang bisa kita lakukan anggaplah seperti ini “select USERNAME, IP_ADDRESS, LOGIN_DATE, LOGIN_TIME, LOGOUT_DATE,LOGOUT_TIME from user_activity WHERE LOGIN_DATE>=’2008-12-01’ and LOGIN_DATE<=’2009-12-31’”. Setelah rancangan query ini selesai, ketika kita ingin menempatkan hasil query tersebut ke dalam sebuah file excel katakanlah nama filenya adalah web_activity-200912.xls, maka kita bisa mengeksekusi query tersebut dengan cara seperti ini:

mysql -uroot –pk4tr0k5 -H -e "select USERNAME, IP_ADDRESS, LOGIN_DATE, LOGIN_TIME, LOGOUT_DATE,LOGOUT_TIME from user_activity WHERE LOGIN_DATE>='2008-12-01' and LOGIN_DATE<='2008-12-31'" > web_activity-200912.xls

Penjelasannya:

mysql –uroot –p adalah sebuah perintah konsol pada konsol OS yang memanggil mysql client dengan paramater –u(user) dan –p(password) dimana untuk paramater usernya langsung kita masukan yaitu root dan paramater password yang juga langsung kita masukkan yaitu k4tr0k5. kemudian untuk paramater selanjutnya adalah –H, inilah yang menjadikan hasill dari query yang kita buat menjadi sebuah table, kemudian paramater selanjutnya adalah –e(execute), dimana didalam tanda kutip setelah –e inilah query yang tadi kita rancang kita letakkan. Kemudian yang terakhir adalah simbol >, sebagai sebuah perintah pada Operating System untuk melempar output dari perintah itu semua ke dalam sebuah file.

..:: Merencanakan SQL Query dengan Microsoft Excell ::..

Terkadang ketika kita ingin memasukkan atau mengedit data yang banyak jumlahnya ke atau di dalam table dalam database MySQL menjadi sebuah kegiatan yang sangat membosankan, karena kita berulang kali membuat query yang sama namun value atau nilai yang akan diisikan dalam tabel berbeda-beda. Dengan menggunakan feature Microsoft Excel, hal tersebut bisa menjadi sangat menyenangkan, sebab ketika ingin meng-copy sebuah query yang sama kita hanya menarik kursor pada kolom di Microsoft Access seperti gambar ini:

Dengan fitur ini peng-copi-an dengan 1000 baris data pun akan menjadi mudah dan menyenangkan, namun selain itu ada beberapa fitur lain dalam excel yang bisa kita gunakan untuk membantu kita merancang query yang kita butuhkan guna melakukan DML pada database MySQL. Perhatikan contoh berikut ini:

Anggaplah kita punya sebuah table dalam database dengan strukture seperti ini:

Kemudian kita ingin memasukkan data ke dalam table ini sejumlah 3000 baris, maka querynya akan menjadi seperti ini “insert into user_web values(‘….’, ’….’, ’….’);” titik-titik itu diisi dengan data sebanyak 3000 baris, bayangkan capeknya mengisi satu demi satu titik-titik tersebut hingga mencapai 3000 baris. Jika dengan menggunakan Microsoft Excell maka akan menjadi lebih mudah dan menyenangkan. Caranya adalah:

Petakanlah kolom-kolom tersebut dalam Microsoft Excel, kemudian isi data yg 3000 baris tadi sehingga menjadi seperti gambar ini:

(Dalam contoh ini saya buat sedikit sample saja, yang penting paham konsepnya he..he..he..).

Kemudian letakkan query tadi pada kolom D3 di awali dengan symbol =” dan di akhiri dengan “, kemudian ganti masing-masing titik-titik yang ada dengan kolom yang sesuai dengan nilainya, apitlah dengan tanda “&&” pada titik-titik tersebut, sehingga query yang diletakkan pada kolom D3 menjadi seperti ini:

="insert into user_web values('"&A3&"','"&B3&"','"&C3&"');"

Maka hasilnya akan terlihat seperti gambar berikut ini:

Kita bisa melihat pada gambar, panah No. 1 menunjukkan bahwa query pada cell microsoft excel seperti tersebut diatas, sementara pada panah No.2 menunjukkan hasil dari fitur fungsi yang kita pakai pada Microsoft Excel ini.

Sementara untuk langkah selanjutnya, tinggal copy-paste atau tarik kursor cell tersebut keseluruh data yang ada pada rencana data pada kolom di bawahnya. Kemudian untuk mengeksekusi query tersebut tinggal copy-paste blok cell tersebut dalam SQL Console.

Saya sendiri sering memakai trik ini dalam melakukan rencana SQL Query pada table yang ingin saya eksekusi pada pekerjaan sehari-hari saya. Ketika saya bandingkan memang hampir 85% lebih cepat dibanding dengan mengedit satu demi satu Query SQL yang saya rencanakan.

Demikianlah pembahasan kita kali ini, membuat Microsoft Excel sebagai salah satu alat bantu ketika bekerja dengan database MySQL. Kita juga bisa menggunakan alternatif Open Office Calc yang serupa tapi tak sama dengan Microsoft Excel ini. Ya terserah maunya anda pakai yang mana.

Terima kasih telah membaca tutorial dan blog ini. Semoga Bermanfaat.

Menteng, 07 Juni 2009

josescalia