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

Diposkan pada by Rubah_amuk

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

Diterbitkan oleh Rubah_amuk

Aku hanya seorang pemuda yang ingin tahu kehidupan dunia dan akhirat untuk mencapai titik kesuksesan dan keselamatan dunia akhirat dan penuh dengan kemilau ilmu yang bermanfaat untuk makhluk hidup, Dengan Prinsip "lebih baik kaya ilmu daripada kaya harta dunia(secukupnya aja) asal di Surga aku memilliki Harta yang banyak yang tidak dimiliki oleh penghuni surga lainnya :)

2 tanggapan untuk “Membuat Database, Tabel, Trigger, Store Procedure, dan View Pada MariaDB”

  1. Ping-balik: Aplikasi Monitoring User Sederhana « Let's Go Rubah

Tinggalkan komentar