Membuat Database, Tabel, Trigger, Store Procedure, dan View Pada MariaDB

Langsung aja yaa

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

MariaDB [(none)]> select @@version
+----------------+
| @@version      |
+----------------+
| 5.5.25-MariaDB |
+----------------+

MariaDB [(none)]> create database akademik;

MariaDB [(none)]> use akademik;
 Database changed

MariaDB [akademik]> create table mahasiswa(nim char(5), nama varchar(25), alamat
 varchar(50), kode_prodi char(3), primary key(nim));

MariaDB [akademik]> create table prodi(kode_prodi char(3), nama_prodi varchar(25
 ), jurusan varchar(50), primary key(kode_prodi));

MariaDB [akademik]> insert  into  prodi  values('P01','Eks  Ilmu  Komputer','Matematika'),
 ('P02','Ilmu Komputer','Matematika'), ('P03','D3 Komsi','Matematika'),
 ('P04','D3 Rekmed','Matematika'), ('P05','D3 Ellins','Fisika');

MariaDB [akademik]> insert into mahasiswa values('00543','Muhammad','Karangmalan
 g A-50',
 -> 'P01'),('10043','Ahmad Sholihun','Karangmalang D-17','P02'),
 -> ('10041','Sugiharti','Karangmalang A-23','P02');

MariaDB [akademik]> select * from prodi;
 +------------+---------------------+------------+
 | kode_prodi | nama_prodi          | jurusan    |
 +------------+---------------------+------------+
 | P01        | Eks  Ilmu  Komputer | Matematika |
 | P02        | Ilmu Komputer       | Matematika |
 | P03        | D3 Komsi            | Matematika |
 | P04        | D3 Rekmed           | Matematika |
 | P05        | D3 Ellins           | Fisika     |
 +------------+---------------------+------------+

MariaDB [akademik]> select * from mahasiswa;
 +-------+----------------+-------------------+------------+
 | nim   | nama           | alamat            | kode_prodi |
 +-------+----------------+-------------------+------------+
 | 00543 | Muhammad       | Karangmalang A-50 | P01        |
 | 10041 | Sugiharti      | Karangmalang A-23 | P02        |
 | 10043 | Ahmad Sholihun | Karangmalang D-17 | P02        |
 +-------+----------------+-------------------+------------+

MariaDB [akademik]> create table log_mhs(kejadiaan varchar(25),waktu datetime(0)
 );

1. Trigger

-Tambah

MariaDB [akademik]> create trigger ins_mhs after insert on mahasiswa
 -> for each row insert into log_mhs values('Tambah data',now());

MariaDB [akademik]> insert into mahasiswa values('00631','Hanif','Kalasan','P01'
 );

MariaDB [akademik]> select *from log_mhs;
 +-------------+---------------------+
 | kejadiaan   | waktu               |
 +-------------+---------------------+
 | Tambah data | 2012-07-09 16:13:26 |
 +-------------+---------------------+

-Update

MariaDB [akademik]> create trigger updt_mhs after update on mahasiswa
 -> for each row insert into log_mhs values('Ubah data',now());

MariaDB [akademik]> select * from mahasiswa;
 +-------+----------------+-------------------+------------+
 | nim   | nama           | alamat            | kode_prodi |
 +-------+----------------+-------------------+------------+
 | 00543 | Moh. Riyan     | Karangmalang A-50 | P01        |
 | 00631 | Hanif          | Kalasan           | P01        |
 | 10041 | Sugiharti      | Karangmalang A-23 | P02        |
 | 10043 | Ahmad Sholihun | Karangmalang D-17 | P02        |
 +-------+----------------+-------------------+------------+

MariaDB [akademik]> select * from log_mhs;
 +-------------+---------------------+
 | kejadiaan   | waktu               |
 +-------------+---------------------+
 | Tambah data | 2012-07-09 16:13:26 |
 | Ubah data   | 2012-07-09 16:15:03 |
 +-------------+---------------------+

-Delete

MariaDB [akademik]> create trigger del_mhs after delete on mahasiswa
 -> for each row insert into log_mhs values('Hapus data',now());

MariaDB [akademik]> delete from mahasiswa where nim='00631';

MariaDB [akademik]> select *from log_mhs;
 +-------------+---------------------+
 | kejadiaan   | waktu               |
 +-------------+---------------------+
 | Tambah data | 2012-07-09 16:13:26 |
 | Ubah data   | 2012-07-09 16:15:03 |
 | Hapus data  | 2012-07-09 16:16:58 |
 +-------------+---------------------+

2. Store Procedure(SP)

MariaDB [akademik]> delimiter //

MariaDB [akademik]> create procedure pMhsIlkom(OUT x varchar(25))
 -> begin
 -> select nama into x from mahasiswa where kode_prodi='P01';
 -> end
 -> //

MariaDB [akademik]> call pMhsIlkom(@nama);
 -> select @nama;
 -> //
 Query OK, 1 row affected (0.00 sec)

+------------+
 | @nama      |
 +------------+
 | Moh. Riyan |
 +------------+

3. View

 MariaDB [akademik]> create view vDetailMhs as
 -> select m.nim, m.nama, m.alamat, p.nama_prodi, p.jurusan
 -> from mahasiswa m, prodi p
 -> where (m.kode_prodi=p.kode_prodi);

MariaDB [akademik]> select *from vDetailMhs;
 +-------+----------------+-------------------+---------------------+------------+
 | nim   | nama           | alamat            | nama_prodi          | jurusan
 |
 +-------+----------------+-------------------+---------------------+------------+
 | 00543 | Moh. Riyan     | Karangmalang A-50 | Eks  Ilmu  Komputer | Matematika
 |
 | 10041 | Sugiharti      | Karangmalang A-23 | Ilmu Komputer       | Matematika
 |
 | 10043 | Ahmad Sholihun | Karangmalang D-17 | Ilmu Komputer       | Matematika
 |
 +-------+----------------+-------------------+---------------------+------------+

Terus Bagaimana dengan Foxpro, OK lah, kita gunakan sintax yang diatas yaa?

-Select


PUBLIC pcsvr,pcdb,pcuid,pcp
pcsvr='localhost'
pcdb='akademik'
pcuid='root'
pcp='admin'

lcconn="Driver={MySQL ODBC 5.1 Driver};Server="+pcsvr+";Database="+pcdb+"; User="+pcuid+";Password="+pcp+";"
nch=SQLSTRINGCONNECT(lcconn)
=SQLSETPROP(nch,"Asynchronous",.T.)
=SQLSETPROP(nch,"BatchMode",.T.)
=SQLPREPARE(nch,"select *from mahasiswa order by nim","curmahasiswa")
lnr=0
DO WHILE lnr=0
lnr=SQLEXEC(nch)
IF lnr<0
AERROR(laerror)
MESSAGEBOX(laerror[1,2],0,' ')
EXIT
ENDIF
ENDDO
=SQLDISCONNECT(nch)

SELECT curmahasiswa
brow

– Insert


PUBLIC pcsvr,pcdb,pcuid,pcp
LOCAL lcnim,lcnama,lcalamat,lckode_prodi
pcsvr='localhost'
pcdb='akademik'
pcuid='root'
pcp='admin'

lcnim='1001'
lcnama='atmanegara'
lcalamat='Kandangan City'
lckode_prodi='P01'

lcconn="Driver={MySQL ODBC 5.1 Driver};Server="+pcsvr+";Database="+pcdb+"; User="+pcuid+";Password="+pcp+";"
nch=SQLSTRINGCONNECT(lcconn)
=SQLSETPROP(nch,"Asynchronous",.T.)
=SQLSETPROP(nch,"BatchMode",.T.)
=SQLPREPARE(nch,"insert into mahasiswa(nim,nama,alamat,kode_prodi) values(?lcnim,?lcnama,?lcalamat,?lckode_prodi)")
lnr=0
DO WHILE lnr=0
lnr=SQLEXEC(nch)
IF lnr<0
AERROR(laerror)
MESSAGEBOX(laerror[1,2],0,' ')
EXIT
ENDIF
ENDDO
=SQLEXEC(nch,"select *from mahasiswa","curmhs")
=SQLDISCONNECT(nch)

SELECT curmhs
brow

hasil

– Update

<pre>PUBLIC pcsvr,pcdb,pcuid,pcp
LOCAL lcnim,lcnama,lcalamat,lckode_prodi
pcsvr='localhost'
pcdb='akademik'
pcuid='root'
pcp='admin'

lcnim='1001'
lcnama='Arief Atmanegara'
lcalamat='Kandangan  City Dodol '
lckode_prodi='P01'

lcconn="Driver={MySQL ODBC 5.1 Driver};Server="+pcsvr+";Database="+pcdb+"; User="+pcuid+";Password="+pcp+";"
nch=SQLSTRINGCONNECT(lcconn)
=SQLSETPROP(nch,"Asynchronous",.T.)
=SQLSETPROP(nch,"BatchMode",.T.)
=SQLPREPARE(nch,"update mahasiswa set nama=?lcnama,alamat=?lcalamat,kode_prodi=?lckode_prodi where nim=?lcnim")
lnr=0
DO WHILE lnr=0
lnr=SQLEXEC(nch)
IF lnr<0
AERROR(laerror)
MESSAGEBOX(laerror[1,2],0,' ')
EXIT
ENDIF
ENDDO
=SQLEXEC(nch,"select *from mahasiswa","curmhs")
=SQLDISCONNECT(nch)

SELECT curmhs
brow

hasil

Deleted

PUBLIC pcsvr,pcdb,pcuid,pcp
LOCAL lcnim,lcnama,lcalamat,lckode_prodi
pcsvr='localhost'
pcdb='akademik'
pcuid='root'
pcp='admin'

lcnim='1001'
lcconn="Driver={MySQL ODBC 5.1 Driver};Server="+pcsvr+";Database="+pcdb+"; User="+pcuid+";Password="+pcp+";"
nch=SQLSTRINGCONNECT(lcconn)
=SQLSETPROP(nch,"Asynchronous",.T.)
=SQLSETPROP(nch,"BatchMode",.T.)
=SQLPREPARE(nch,"delete from mahasiswa where nim=?lcnim")
lnr=0
DO WHILE lnr=0
lnr=SQLEXEC(nch)
IF lnr<0
AERROR(laerror)
MESSAGEBOX(laerror[1,2],0,' ')
EXIT
ENDIF
ENDDO
=SQLEXEC(nch,"select *from mahasiswa","curmhs")
=SQLDISCONNECT(nch)

SELECT curmhs
brow

hasil

Tetap pada kesimpulan :

– Tidak ada perubahan mendasar antara Mysql da MariaDB

– Untuk Menghubungkan Database MariaDB, saya tetap menggunakan Konektor ODBC Mysql 5.1🙂

Demikaian hasil tester saya, maklum masih belajar, mohon maaf jika ada salah, inilah pengalaman saya dari coba-coba😀

*saya pribadi belum memakai secara live dalam aplikasi saya🙂 maklum ikut orang juga dan saya masih skripsi🙂

Semoga sukses untuk kalian semua😀 semoga manfaat

2 Balasan ke Membuat Database, Tabel, Trigger, Store Procedure, dan View Pada MariaDB

  1. […] dalam tabel baik transaksi ato dalam tabel master, bagaimana membuatnya anda bisa mempelajari trigger menurut saya tabel log ini sangat penting karena bisa menjadi refersinsi jika terjadi kecurangan […]

  2. ahmad mengatakan:

    sekedar ino grup MariaDB INDONESIA
    http://www.facebook.com/groups/mariadb/

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: