• Menampilkan hasil count dengan urutan
  select kolom1,kolom2,count(kolom3) from nama_tabel where kolom4 like’%parameter1%’ and kolom5=’paramater2′ group by kolom1,kolom2 order by kolom3;
• Export result table ke format csv
  select * into outfile ‘/tmp/result.csv’ fields terminated by ‘,’ enclosed by “” escaped by ‘\\’ lines terminated by ‘\n’ from nama_tabel;
• Merubah nilai pada beberapa kolom dalam 1 tabel
  UPDATE nama_tabel SET kolom_a=”nilai_yang_baru”, kolom_b=”nilai_yang_baru” WHERE kolom_d=”nilai_yang_ada
• Mencari baris yang mengandung nilai tertentu dalam 1 tabel
  SELECT * from nama_tabel WHERE kolom_a=”nilai_yang_diinginkan”
• Mengelompokkan value dalam tabel
  SELECT DISTINCT nama_kolom from nama_tabel

Beberapa contoh query

1. Query Select Case
   SELECT akunting.no_dokumen, akunting.deskripsi, akunting.id_account, coa.nama_account,
   (SELECT CASE akunting.type_transaksi WHEN “k” THEN “kredit” ELSE “debet” END) AS type_transaksi,
   akunting.jumlah
   FROM trx_akunting akunting
   JOIN chart_of_accounts coa
   ON akunting.id_account = coa.id_account
   WHERE akunting.id_company=’3′ ORDER BY akunting.no_dokumen ASC
2. Query dengan Kombinasi Perhitungan Matematis Sederhana
   SELECT sj.create_date, spk.no_spk, konsumen.nama_depan, piutang.no_piutang, tipe.kode_tipe, stock.kode_barang,
   stock.harga_jual AS Harga_Jual_DB, tipe.harga_jual AS Master_OTR_Cab, spk.uang_muka AS UM_PO,
   tipe.harga_jual – spk.uang_muka AS “OTR – UM_PO”, stock.harga_jual – spk.uang_muka AS “Hrg_Jual_DB – UM_PO”,
   piutang.sub_total AS nilai_PK_otodis,
   ((tipe.harga_jual – spk.uang_muka) – piutang.sub_total) AS “(OTR – UM_PO) – PK”,
   partner.nama_partner_bisnis
   FROM surat_pesan_kendaraan spk
   JOIN piutang piutang
   ON spk.no_spk = piutang.referensi
   JOIN stock_barang stock
   ON spk.kode_barang = stock.kode_barang
   JOIN surat_jalan sj
   ON spk.no_spk = sj.no_spk
   JOIN konsumen konsumen
   ON spk.id_konsumen = konsumen.id_konsumen
   JOIN tipe_motor tipe
   ON spk.id_tipe_motor = tipe.kode_tipe
   JOIN partner_bisnis partner
   ON spk.leasing = partner.id_partner_bisnis
   WHERE spk.id_dealer=’5′ AND spk.status_spk=’terkirim’ AND spk.metoda_beli=’kredit’ AND tipe.id_dealer=’5′
   AND piutang.no_piutang LIKE ‘%PK%’
   ORDER BY sj.create_date
3. Query dengan variasi Sub Query
   SELECT spk.create_date, sj.create_date, spk.no_spk,
   konsumen.nama_depan, konsumen.nama_belakang, spk.id_tipe_motor, tipe.nama_motor,
   stock.no_mesin, stock.no_rangka, spk.dokumen_kredit,
   partner.nama_partner_bisnis, promo.id_program_promo, promo.nama_program_promo,
   stock.harga_jual AS harga_OTR, spk.diskon AS diskon_dealer, spk.uang_muka AS uang_muka_PO,
   (
   SELECT potongan
   FROM promo_potongan
   WHERE id_program_promo = promo.id_program_promo AND id_partner_bisnis = promo.leasing
   LIMIT 1
   ) AS subsidi_leasing,
   (
   SELECT SUM(potongan)
   FROM promo_potongan
   WHERE id_program_promo = promo.id_program_promo AND id_partner_bisnis != promo.leasing
   AND STATUS=’1′
   LIMIT 1
   ) AS claim_das
   FROM surat_pesan_kendaraan spk
   JOIN surat_jalan sj
   ON spk.no_spk = sj.no_spk
   JOIN stock_barang stock
   ON spk.kode_barang = stock.kode_barang
   JOIN tipe_motor tipe
   ON spk.id_tipe_motor = tipe.id_tipe_motor
   JOIN konsumen konsumen
   ON konsumen.id_konsumen = spk.id_konsumen
   JOIN program_promo promo
   ON spk.id_program_promo = promo.id_program_promo
   JOIN partner_bisnis partner
   ON partner.id_partner_bisnis = promo.leasing
   WHERE stock.no_mesin IN(
   ”
   )
   ORDER BY spk.no_spk
4. Query dengan Menggunakan Between
   SELECT spk.no_spk, stk.faktur_beli, stk.pelat, stk.stnk, stk.bpkb
   FROM surat_pesan_kendaraan spk
   JOIN stock_barang stk
   ON spk.kode_barang = stk.kode_barang
   join surat_jalan sj
   on spk.no_spk = sj.no_spk
   WHERE sj.create_date BETWEEN ’1270279393′ AND ’1296469548′
   ORDER BY spk.no_spk ASC
5. Query dengan Menggunakan Left Join
   Left Join berarti mengambil nilai yang sama dari 2 tabel, dengan patokan data di tabel kedua yang ditulis dalam query ON. Contohnya, ON piutang.no_piutang = trx.referensi_transaksi, berarti apabila ada data pada tabel piutang.no_piutang yang tidak ada di tabel trx.referensi_transaksi, maka tidak akan ditampilkan.
   SELECT trx.timestamp AS tgl_transaksi, trx.create_date AS tgl_buat_data, piutang.no_piutang,
   trx.referensi_transaksi, piutang.referensi, trx.no_bukti, trx.kepada_dari, trx.no_account,
   coa.nama_account, piutang.sub_total AS nilai_piutang, trx.sub_total AS nilai_pelunasan
   FROM
   piutang piutang
   LEFT JOIN transaksi_uang trx
   ON piutang.no_piutang = trx.referensi_transaksi
   JOIN chart_of_accounts coa
   ON trx.no_account = coa.id
   WHERE trx.id_dealer=’1′ ORDER BY trx.kepada_dari