LK 03b (SQL - DDL, DML, Trigger, Procedure, Function) [PDF]

Citation preview

LEMBAR KERJA | DDL (Data Definition Language) Masuk ke mysql : Jika menggunakan port default dan password tertentu mysql –u root –p password Jika menggunakan port 3307 dan password tertentu Mysql –u root –port=3307 –h localhost –p password Jika menggunakan port 3307 tanpa password Mysql –u root –port=3307 –h localhost Menciptakan Database : create database pendaftaran;



Menghapus Database : drop database pendaftaran;



Mengaktifkan (membuka) Database : use pendaftaran;



Menciptakan Table : Create table data_diri( no int(3), nama varchar(35), alamat varchar(60), email varchar(40), no_telepon varchar(15), jenis_kelamin char(1));



Menampilkan Table : Show tables;



1|Page



Melihat Struktur Table : Desc data_diri; atau Describe data_diri;



Menghapus table : Drop table data_diri;



Membuat Kunci Primer (Primary Key) : Create table data_diri( no int(3) Primary Key, nama varchar(35), alamat varchar(60), email varchar(40), no_telepon varchar(15), jenis_kelamin char(1));



Membuat Kolom Unik (Unique) : Create table pribadi( 2|Page



kd_pribadi char(3), panggilan char(4), nama varchar(35), email varchar(50), jenis_kelamin char(1), Unique(kd_pribadi,panggilan));



Mengganti Nama Table : Rename table pribadi to data_pribadi;



Menambah Field pada Tabel : Alter table data_diri add gol_darah char(2);



3|Page



Menghapus Field : Alter table data_diri drop gol_darah;



LEMBAR KERJA | DML (Data Manipulation Language) Administrasi MySQL : MySQL selaku database server yang mampu berjalan pada jaringan, tertu saja harus memiliki kemampuan khusus yang berguna untuk melakukan manajemen atau mendukung system database yang bersifat client/server.



Membuat User Baru : Insert into user(host, user, password) values (‘%’, ’nama_user’, ‘password’); Contoh : Insert into user(Host, User, Password) values (‘localhost’,’gibran’,MD5(‘if290216’)); Flush privileges;



Memasukkan data pada tabel (input data) : a.



Membuka database pendaftaran : use pendaftaran;



b.



Membuat table tamu : Create table tamu( no int(3) primary key, nama varchar(35), alamat varchar(60), email varchar(40), no_telp varchar(15));



4|Page



c.



Input Data : insert into tamu(no,nama,alamat,email,no_telp) values ('1','Boi Triyono','Ujungberung','[email protected]','085613548789'); atau insert into tamu values ('2','Bray Triyana','Cibiru','[email protected]','085555222999'); insert into tamu values ('3','Aldena','Kebon Kalapa','[email protected]','085555222000'), ('4','Gibran','Cihanjuang','[email protected]','085555222555'), ('5','Elisabet','Sumedang','[email protected]','085555222666'), ('6','Fanji','Majalengka','[email protected]','085555222111');



Update data pada tabel (ubah data) : Update tamu set Nama='Irfan Nurhudin' where nama='Boi Triyono'; 5|Page



Menghapus data (delete) : delete from tamu where no='1';



Menampilkan data (Select) : 1.



Menampilkan data dari sebuah table : select * from tamu;



2.



Menampilkan sebagian data dari sebuah table : Select no, nama, alamat from tamu;



3.



Menampilkan data dengan perintah where :



6|Page



4.



Menampilkan data dengan BETWEEN Between artinya antara, between berfungsi untuk menampilkan data yang tertentu misalnya antara 2000 s.d. 5000. Untuk menampilkan data dengan between sebelumnya kita harus membuat table yang didalamnya terdapat data dengan type int (angka). create table t_barang( kode_brg char(5) primary key, nama_barang varchar(50), satuan char(10), banyak int(5), harga_brg int(11));



insert into t_barang values ('AP002','Microsoft Excel 2003','Buah',15,37500), ('AP003','Microsoft PowerPoint 2003','Buah',5,40000), ('DB002','Microsoft SQL Server','Buah',10,55000), ('AP001','Microsoft Word 2003','Buah',20,35000), ('DB001','MySQL Server','Buah',5,45000), ('DB003','Oracle 9i','Buah',10,65000);



Select * from t_barang;



7|Page



select * from t_barang where harga_brg between 37500 and 45000;



5.



Menampilkan data dengan perintah LIKE select * from t_barang where nama_barang like '%Microsoft%';



6.



Menampilkan data dengan pengurutan sorting (order by) select * from t_barang order by nama_barang desc;



select * from t_barang order by nama_barang asc; 8|Page



7.



Menampilkan data dengan pengelompokkan data (group by) select * from t_barang group by nama_barang;



8.



Menampilkan data sesuai dengan Fungsi Statistik a. Fungsi COUNT select count(*) from t_barang;



b.



Fungsi SUM select sum(harga_brg) from t_barang;



c.



Fungsi AVG select AVG(harga_brg) from t_barang;



9|Page



9.



d.



Fungsi MIN select min(harga_brg) from t_barang;



e.



Fungsi MAX select max(harga_brg) from t_barang;



Menampilkan data sesuai dengan Fungsi String a. Fungsi LEFT(x,n) select * from t_barang where left(kode_brg,1)="D";



b.



Fungsi RIGHT(x,n) select * from t_barang where right(kode_brg,1)="2";



10 | P a g e



c.



Fungsi MID(x,n,z) select * from t_barang where mid(kode_brg,2,1)="P";



Menampilkan data dengan perintah JOIN



INNER JOIN (ON) adalah sebutan lain dari operasi JOIN yang sudah dipakai selama ini



SELECT A.*, B.* FROM A, B WHERE A.kolom1 = B.kolom3 sama dengan SELECT A.*, B.* FROM A INNER JOIN B ON A.kolom1 = B.kolom3 Implementasi:



11 | P a g e



Tipe join ini akan mengambil semua row dari table asal dan table tujuan dengan kondisi nilai key yang terkait saja, dan  jika tidak maka row tersebut tidak akan muncul. Kalau tidak terdapat kondisi key terkait antar table, maka semua row dari kedua table dikombinasikan. 1



SELECT * FROM karyawan INNER JOIN gaji



2



ON karyawan.karyawan_id=gaji.karyawan_id;



Record yang ditampilkan sebanyak 5 record. Karena INNER JOIN hanya memperhitungkan kondisi key yang terkait antara table karyawan dengan table gaji. Sedangkan karyawan dengan karyawan_id=’006’ tidak ditampilkan, karena tidak terkait dengan table gaji.



#2. LEFT JOIN LEFT JOIN atau biasa juga dikenal dengan LEFT OUTER JOIN merupakan perintah join untuk menampilkan semua data sebelah kiri dari table yang di joinkan dan menampilkan data sebelah kanan yang cocok dengan kondisi join. Jika tidak ditemukan kecocokan, maka akan di set NULL secara otomatis.



Contoh:



1



SELECT * FROM karyawan LEFT JOIN gaji



2



ON karyawan.karyawan_id=gaji.karyawan_id;



Query diatas akan menghasilkan output seperti berikut:



12 | P a g e



Record yang ditampilkan sebanyak 6 record. Karena LEFT JOIN akan menampilkan semua table sebelah kiri dari kondisi join yaitu table karyawan. Semua data pada table karyawan akan ditampilkan, meskipun tidak ada kecocokan key pada table gaji.  Jika dibuat diagram venn-nya akan terlihat seperti gambar berikut:



Selain kondisi diatas, LEFT JOIN juga bisa menampilkan data yang hanya kondisi key pada table tamu (foreign key) kosong (NULL). Contoh: 1



SELECT * FROM karyawan LEFT JOIN gaji



2



ON karyawan.karyawan_id=gaji.karyawan_id



3



WHERE gaji.karyawan_id IS NULL;



Query diatas akan menghasilkan output seperti berikut: 13 | P a g e



Data yang ditampilkan hanya 1 record. Hal ini dikarenakan, hanya ada satu data yang belum memiliki kecocokan key pada table tamu.Untuk mempermudah memahami perbedaan antara kedua LEFT JOIN ini coba perhatikan diagram venn berikut:



Dengan melihat perbedaan dari diagram venn tersebut, maka Anda akan mudah memahami bagaimana left join ini bekerja.Ingat LEFT JOIN ini sangat penting untuk anda pahami, karena disaat Anda mulai mengerjakan project yang cukup kompleks, maka anda akan banyak berkutat dengan left join ini.



Contoh pada kasus diatas, hanya dengan memanfaatkan left join kita bisa menampilkan semua data karyawan yang sudah ada gajinya dan siapa yang belum ada gajinya.



14 | P a g e



Selain itu anda juga dapat menampilkan semua data karyawan yang belum ada gajinya dengan fungsi LEFT JOIN WHERE NULL.



 #3. RIGHT JOIN



Kebalikan dari LEFT JOIN adalah RIGHT JOIN, atau biasa juga dikenal dengan RIGHT OUTER JOIN. RIGHT JOIN akan menampilkan semua data yang ada di table sebelah kanan dan mencari kecocokan key pada table sebelah kiri. Jika tidak ditemukan kecocokan, maka akan di set NULL secara otomatis pada table sebelah kiri. Contoh: 1



SELECT * FROM gaji RIGHT JOIN karyawan



2



ON gaji.karyawan_id=karyawan.karyawan_id;



Query diatas akan menampilkan output seperti gambar berikut:



Pada output diatas, anda dapat melihat bahwa terdapat NULL pada table sebelah kiri. Hal ini dikarenakan tidak ditemukan kecocokan key diantara kedua table. Untuk lebih mudah memahaminya, perhatikan diagram venn berikut:



15 | P a g e



Selain kondisi diatas, RIGHT JOIN juga bisa menampilkan data yang hanya kondisi key pada table tamu (foreign key) kosong (NULL). Contoh: 1



SELECT * FROM gaji RIGHT JOIN karyawan



2



ON gaji.karyawan_id=karyawan.karyawan_id



3



WHERE gaji.karyawan_id IS NULL;



Query diatas akan menghasilkan output seperti gambar berikut:



Data yang ditampilkan hanya 1 record. Hal ini dikarenakan, hanya ada satu data yang belum memiliki kecocokan key pada table tamu. Untuk mempermudah anda memahami perbedaan antara kedua RIGHT JOIN ini coba perhatikan diagram venn berikut:



Perhatikan diagram venn diatas, dan bandingkan dengan diagram venn sebelumnya.



16 | P a g e



LEMBAR KERJA | PEMBUATAN TRIGGER (1) Membuat database akademik : create database akademik;



Membuka database akademik : use akademik;



Membuat tabel mahasiswa : create table mahasiswa( nim char(5), nama varchar(25), alamat varchar(50), kode_prodi char(3), primary key(nim));



Membuat tabel prodi : create table prodi( kode_prodi char(3), nama_prodi varchar(25), jurusan varchar(20), primary key(kode_prodi));



Membuat relasi antara tabel prodi dengan tabel mahasiswa : alter table mahasiswa add foreign key(kode_prodi) references prodi(kode_prodi);



Input data prodi : 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');



Menampilkan data prodi : Select * from prodi;



17 | P a g e



Input data mahasiswa : insert into mahasiswa values ('00543','Ahmad','Karang Malang A-50','P01'), ('10043','Ahmad Sholihun','Karang Malang D-17','P02'), ('10041','Sugiharti','Karang Malang A-23','P02');



Menampilkan data mahasiswa : Select * from mahasiswa;



Membuat tabel log_mhs : create table log_mhs( kejadian varchar(25), waktu datetime);



Membuat Trigger ins_mhs : create trigger ins_mhs after insert on mahasiswa for each row insert into log_mhs values('Tambah Data',now()); Keterangan : Maksud perintah trigger diatas adalah : Apabila perintah insert dilakukan pada tabel mahasiswa maka secara otomatis tabel log_mhs akan dilakukan perintah insert Tambah data dan Waktu. Contoh : insert into mahasiswa values ('00631','Hanif','Kalasan','P01'); select * from mahasiswa;



18 | P a g e



select * from log_mhs;



Membuat Trigger updt_mhs : create trigger updt_mhs after update on mahasiswa for each row insert into log_mhs values('Ubah Data',now()); Keterangan : Maksud perintah trigger diatas adalah : Apabila perintah update dilakukan pada tabel mahasiswa maka secara otomatis tabel log_mhs akan dilakukan perintah insert Ubah Data dan waktu. Contoh : update mahasiswa set nama='Moh. Riyan' where nim='00543'; select * from log_mhs;



Membuat Trigger del_mhs : create trigger del_mhs after delete on mahasiswa for each row insert into log_mhs values('Hapus Data',now()); Keterangan : Maksud perintah trigger diatas adalah : Apabila perintah delete dilakukan pada tabel mahasiswa maka secara otomatis tabel log_mhs akan dilakukan perintah insert Hapus Data dan waktu. Contoh : Delete from mahasiswa where nim='00631'; 19 | P a g e



select * from log_mhs;



LEMBAR KERJA | PEMBUATAN TRIGGER (2) Membuat database toko : create database toko;



Membuka database toko : use toko;



Membuat tabel barang : create table barang( kd_barang varchar(5) primary key, nama varchar(100), stok int(4));



Membuat tabel penjulan : create table penjualan( kd_transaksi int(11) primary key auto_increment, tanggal datetime, kd_barang varchar(5), jumlah int(11));



Membuat relasi tabel barang dan penjulan : alter table penjualan add foreign key(kd_barang) references barang(kd_barang);



Input data barang : insert into barang values ('BRG01','Kecap',5), ('BRG02','Sampo',6), ('BRG03','Sambal',10), ('BRG04','Mentega',15);



Menampilkan data barang : Select * from barang; 20 | P a g e



Membuat Trigger kurangi_stok : create trigger kurangi_stok after insert on penjualan for each row update barang set stok=stok-new.jumlah where kd_barang=new.kd_barang;



Menampilkan Trigger : show triggers;



Pengujian Trigger : 1.



Tampilkan data barang (sebelum melakukan insert pada tabel penjualan : Select * from barang;



2.



Lakukan insert pada tabel penjualan, contoh : insert into penjualan(tanggal,kd_barang,jumlah) values (SYSDATE(),'BRG01',2); insert into penjualan(tanggal,kd_barang,jumlah) values (SYSDATE(),'BRG03',4); insert into penjualan(tanggal,kd_barang,jumlah) values (SYSDATE(),'BRG04',7);



3.



Tampilkan data pada table penjualan : Select * from penjualan;



4.



Tampilkan data pada tabel Barang (setelah melakukan insert pada table penjualan): Select*from barang;



21 | P a g e



LEMBAR KERJA | PEMBUATAN PROCEDURE (1) Membuka database akademik : use akademik;



Membuat Procedure dengan nama procedure : pMhsIlkom delimiter // create procedure pMhsIlkom(OUT x varchar(25)) begin select nama into x from mahasiswa where kode_prodi='P01'; end //



Memanggil Procedure dengan nama procedure : pMhsIlkom call pMhsIlkom(@Nama); select @Nama; //



22 | P a g e



LEMBAR KERJA | PEMBUATAN PROCEDURE (2) Membuka database akademik : use akademik;



Membuat Procedure dengan nama procedure : pMhs delimiter // create procedure pMhs(out x varchar(25), out y varchar(25), in z char(3)) begin select nama, alamat into x,y from mahasiswa where kode_prodi=z; end //



Memanggil Procedure dengan nama procedure : pMhs call pMhs(@Nama,@Alamat,'P01'); select @Nama,@Alamat; //



23 | P a g e



LEMBAR KERJA | PEMBUATAN FUNCTION Membuka database akademik : use akademik;



Membuat Function dengan nama Function : fcNamaMHS delimiter // create function fcNamaMHS(x char(25)) returns char(40) return concat('Nama : ', x); //



Memanggil Function dengan nama Function : fcNamaMHS call pMhs(@Nama,@Alamat,'P01'); select @Nama,@Alamat; //



24 | P a g e



LEMBAR KERJA | PEMBUATAN VIEW Membuka database akademik : use akademik;



Menampilkan data pada tabel mahasiswa : Select * from mahasiswa;



Menampilkan data pada tabel prodi : Select * from prodi;



Membuat VIEW dengan nama vDetailMhs : 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);



25 | P a g e



Menampilkan VIEW vDetailMhs : select * from vDetailMhs;



Keterangan : Dari contoh diatas dapat dijelaskan bahwa view tersebut berisi informasi mahasiswa (nim, nama, alamat) dan informasi prodi mahasiswa yang bersangkutan (nama_prodi dan jurusan). Implementasi view dalam program aplikasi adalah untuk memudahkan dalam mendesain laporan(report).



26 | P a g e



LEMBAR KERJA | Penggunaan RollBack Membuka database akademik : use akademik;



Menampilkan data mahasiswa sebelum di input (insert) : Select * from mahasiswa;



Input (insert) data pada table mahasiswa : 1.



Insert data tanpa RollBack : insert into mahasiswa (nim, nama, alamat, kode_prodi) values ('00501','Akum','Bogor','P02');



2.



Insert data dengan RollBack : START TRANSACTION; insert into mahasiswa (nim, nama, alamat, kode_prodi) values ('10502','Idoy','Bogor','P02'); ROLLBACK;



Data pada table mahasiswa setelah dilakukan 2 kali Insert :



27 | P a g e



Keterangan : Rollback adalah perintah yang di gunakan untuk mengembalikan ke posisi data sebelum START TRANSACTION, atau bisa dibilang fungsi rollback sama seperti restore data.



LEMBAR KERJA | Penggunaan Commit Membuka database akademik : use akademik;



Menampilkan data mahasiswa sebelum di input (insert) : Select * from mahasiswa;



Input (insert) data pada table mahasiswa : 1.



Insert data tanpa Commit : insert into mahasiswa (nim, nama, alamat, kode_prodi) values ('10502','Aceng','Bogor','P02');



2.



Insert data dengan Commit : START TRANSACTION; insert into mahasiswa (nim, nama, alamat, kode_prodi) values ('10503','Idoy','Bogor','P02'); COMMIT; ROLLBACK;



28 | P a g e



Data pada table mahasiswa setelah dilakukan 2 kali Insert :



Keterangan : Insert data yang ditambahkan Commit akan tetap dilakukan walaupun dibawahnya ditambahkan Rollback. Commit digunakan untuk membuat data tidak berubah kembali meskipun menggunakan perintah rollback.



29 | P a g e



30 | P a g e