Modul Sbd 2013

2013

Kesatuan Amal Mahasiswa Islam (KAMIL)

2

KATA PENGANTAR

Assalamualaikum WR.WB

Puji syukur penulis panjatkan kehadirat ALLAH S.W.T. yang telah melimpahkan

rahmat dan inayah-Nya kepada kita semua. Shalawat dan salam semoga senantiasa tercurah

kepada baginda Rasul Nabi Muhammad S.A.W. beserta keluarga, sahabat dan para

pengikutnya sampai akhir zaman. Sehingga modul untuk pembelajaran matakuliah Sistem

basis data ini dapat disusun sebagaimana mestinya.

Penyusun mengucapkan terima kasih kepada Para Pengajar Matakuliah Ini dan rekan-rekan mahasiswa yang dengan sabar mengikuti kegiatan praktikum ini, semoga amal dari menuntut ilmu ini mendapatkan berkah dari Yang Maha Kuasa. Tak lupa pula penyusun sampaikan terima kasih kepada pihak-pihak terkait sehingga terbentuknya modul belajar ini diantarannya bapak Achmad Solichin yaitu pengelola situs achmatim.net yang baik hati memposting Bahan-bahan belajar yang tekait dengan basis data diantarannya “MySQL 5 Dari

Pemula Hingga Mahir Achmad Solichin” dan bahan lain-lainnya, smoga Amal Kebaikan diterima Oleh Yang Maha Kuasa.

Akhirnya kepada ALLAH S.W.T. jualah Penyusun kembalikan semoga senantiasa

melimpahkan rahmat, hidayah dan karunia-Nya kepada mereka semua, semoga Modul yang

sederhana ini dapat bermanfaat bagi penulis khususnya dan pembaca pada umumnya, Amin

Ya Robbal `Alamin. “Jazakumullahu khairan”

Wassalamualaikum warahmatullahi wabarakatuh

4

Bismillahirrohmaanirrohiim...

5

MATERI PERTAMA

Memulai Pelajaran

1. Jalankan aplikasi XAMPP anda. Kemudian Start untuk service MySql sampai muncul

kata Running atau Tombol Start berubah menjadi Stop.

Gambar 1. Tampilan XAMPP Control Panel

2. Setelah itu buka command prompt anda atau klik tombol windows+R, kemudian

ketikkan cmd untuk membuka command prompt.

Sebelum anda melangkah lebih jauh, ada baiknya ada melihat di direktori mana

aplikasi XAMPP anda ter-install, pada tampilan xampp control panel, anda bisa mengetahui

dimana letak direktori xampp anda terinstall yaitu terlihat pada kalimat :

XAMPP Installation Directory: “c:\xampp\” (Lihat Gambar 1).

3. kemudian, dengan mengetahui dimana letak dimana xampp anda terinstall, untuk

masuk ke area mysql, cara cepatnya ketikkan perintah seperti dibawah ini :

cd \xampp\mysql\bin --- Enter

mysql –u root --- Enter

“Catatan”

Defaultnya, XAMPP tidak mempunyai password untuk user dengan nama root

6

--------------(Materi DDL)----------------

Membuat database baru

Gunakan perintah "CREATE DATABASE" untuk membuat sebuah

database.

mysql> create database latihan1 ;

Query OK, 1 row affected (0.02 sec)

Lihat Database yang dibuat dengan perintah "SHOW DATABASES;".

mysql> show databases ;

+--------------------+

| Database |

+--------------------+

| information_schema |

| latihan1 |

| latihan2 |

| mysql |

| test |

+--------------------+

5 rows in set (0.00 sec)

Menghapus database

Kita tidak memerlukan database latihan2, maka kita dapat menghapusnya

dengan perintah “DROP DATABASE NAMA_DB”.

mysql> drop database latihan2 ;


Memilih sebuah database yg akan digunakan

Sekarang kita pilih database "latihan1" dan kita buka dengan perintah

"USE NAMA_DB".

mysql> use latihan1 ;

Database change

Membuat tabel baru

Untuk membuat sebuah tabel baru dengan menggunakan perintah

Create Table Nama Tabel (Kolom1 type_data atribut, Kolom2 type_data atribut );

7

Contohnya sebagai berikut..

mysql> create table karyawan(

-> nopeg INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

-> nama VARCHAR(50) NOT NULL );

Query OK, 0 rows affected (0.14 sec)

Penjelasan: Kolom pertama adalah NOPEG dengan jenis data

bilangan bulat (INTeger), tanpa tanda negatif (UNSIGNED), yang akan

bertambah nilainya secara otomatis (AUTO_INCREMENT), dan kolom

NOPEG adalah kolom utama (PRIMARY KEY). Kemudian pada kolom

kedua, NAMA akan menampung nama karyawan, dengan jenis data

VARiabel CHARacter, lebar datanya dapat menampung maksimal 50

karakter, dan tidak boleh dikosongkan (NOT NULL).

Melihat Tabel dari Sebuah Database

Untuk melihat apa isi (table) dari sebuah database, kita gunakan

perintah "SHOW TABLES;". Maka akan muncul nama table yg ada

didalam database tersebut.

Melihat struktur tabel

Untuk melihat struktur sebuah tabel dapat menggunakan perintah

"DESC nama_tabel" atau bisa juga menggunakan perintah "SHOW

COLUMNS FROM nama_tabel".

Contoh menggunakan desc nama_tabel.

mysql> desc karyawan ;

+-------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------------+------+-----+---------+----------------+

| nopeg | int(10) unsigned | NO | PRI | NULL | auto_increment |

| nama | varchar(50) | NO | | | |

+-------+------------------+------+-----+---------+----------------+

8


Atau menggunakan perintah "SHOW COLUMNS FROM..."

mysql> show columns from karyawan ;

+-------+------------------+------+-----+---------+----------------+


+-------+------------------+------+-----+---------+----------------+

| nopeg | int(10) unsigned | NO | PRI | NULL | auto_increment |

| nama | varchar(50) | NO | | | |

+-------+------------------+------+-----+---------+----------------+


Buat contoh tabel lagi dengan nama contoh1.

mysql> create table contoh1

-> (noid INT)

-> ;


Sekarang kita lihat berapa tabel yang ada di dalam database latihan1:

mysql> show tables ;

+--------------------+

| Tables_in_latihan1 |

+--------------------+

| contoh1 |

| karyawan |

+--------------------+


Menghapus tabel

Perintah untuk menghapus sebuah tabel dalam MySQL adalah :

"DROP TABLE NAMA_TABEL".

Contoh : drop table mahasiswa;

Mengubah struktur sebuah tabel

9

Ada saatnya kita perlu mengubah struktur tabel yang pernah kita

buat sebelumnya. Pengubahan struktur bisa dalam hal penambahan

kolom (ADD), pengubahan lebar dan jenis kolom (MODIFY), atau bisa

saja penghapusan kolom dan indeks (DROP), penggantian nama kolom

(CHANGE), pengantian nama tabel (RENAME), dan sebagainya.

Perintahnya : ALTER TABLE nama_tabel alter_options;

Option yang bisa digunakan, beberapa di antaranya sebagai berikut :

» ADD definisi_field_baruOption ini digunakan untuk menambahkan field baru dengan “definisi_field_baru” (nama field, tipe dan option lain).

» ADD PRIMARY KEY (field_kunci)Option untuk menambahkan primary key pada tabel

» CHANGE field_yang_diubah definisi_field_baruOption untuk mengubah field_yang_diubah menjadi definisi_field_baru

» MODIFY definisi_fieldOption untuk mengubah suatu field menjadi definisi_field

» DROP nama_fieldOption untuk menghapus field nama_field

» RENAME TO nama_tabel_baruOption untuk mengganti nama tabel

Contoh, tambahkan beberapa kolom baru, yaitu kolom jenis kelamin, kota,

tanggal lahir dan kodepos pada tabel karyawan.

mysql> alter table karyawan

-> ADD jenkelamin CHAR(2) NOT NULL,

-> ADD kota VARCHAR(25) NOT NULL,

-> ADD kodepos CHAR(5) NOT NULL,

-> ADD tgllahir DATE

-> ;


Records: 0 Duplicates: 0 Warnings: 0

10

lihat hasilnya:

mysql> describe karyawan ;

+------------+-------------+------+-----+---------+----------------+


+------------+-------------+------+-----+---------+----------------+

| nopeg | int(10) | NO | PRI | NULL | auto_increment |

| nama | varchar(50) | NO | | | |

| jenkelamin | char(2) | YES | | NULL | |

| kota | varchar(25) | NO | | | |

| kodepos | char(5) | NO | | | |

| tgllahir | date | YES | | NULL | |

+------------+-------------+------+-----+---------+----------------+


Mungkin ada baiknya kalau nama kolom nopeg kita ubah aja

menjadi noid. Begitupun dengan nama kolom jenkelamin, kita ubah

namanya menjadi jenkel saja. Dalam pengubahan kolom ini sebaiknya

'sifat-sifat' kolom yang asli tetap ditulis ulang. Misal bila kolom nopeg

memiliki sifat 'auto_increment', maka selama sifat itu tetap

dipertahankan, maka dia (auto_increment) harus ditulis ulang.


-> change nopeg noid int(10) auto_increment

-> ;



Mengubah kolom jenkelamin menjadi jenkel, sekaligus mengubah jenis

datanya dari CHAR(2) menjadi CHAR(1):


-> change jenkelamin jenkel char(1) ;



Mengubah Nama Tabel

Mengubah Nama Tabel karyawan menjadi tabel pegawai? Silakan dicoba

11

dibawah ini:


-> rename pegawai ;


Kita lihat lagi hasilnya:


+--------------------+


+--------------------+

| pegawai |

+--------------------+

1 row in set (0.00 sec)

Sekarang kita kembalikan lagi nama tabel pegawai menjadi karyawan.

Tetapi dengan perintah yang

berbeda, yaitu "RENAME TABLE".

mysql> rename table pegawai

-> to karyawan

-> ;


Jangan lupa untuk memeriksa hasilnya:


+--------------------+


+--------------------+

| karyawan |

+--------------------+

1 row in set (0.00 sec)

12

Latihan 1 (Membuat Database dan Tabel)

Buatlah sebuah database dan table sesuai dengan yg dibawah ini :

Nama Database Db_akademik

Tabel Mhs Nim [varchar(11)] primary key not null

Nama_mhs [varchar(35)]

Jurusan [varchar(20)]

Progstudi [char(3)]

Semester [int(2)]

Matkul Kd_matkul [varchar(10)] primary key not null

Nama_matkul[varchar(40)]

Jml_sks [int(2)]

Dosen Kd_dosen [varchar(10)] primary key not null

Nama_dosen [varchar(35)]

Jabatan [varchar(40)]

----------------(Materi DML)----------------

Materi selanjutnya..

Menambah Record dengan INSERTBentuk umum perintah SQL untuk menambahkan record atau data ke dalam

suatu tabel adalah sebagai berikut :

1.) INSERT INTO nama_tabel VALUES (‘nilai1’,’nilai2’,...);atau dapat dengan bentuk sebagai berikut :

2.) INSERT INTO nama_tabel(field1,field2,...) VALUES (‘nilai1’,’nilai2’,...);atau dapat juga dengan bentuk sebagai berikut :

3.) INSERT INTO nama_tabelSET field1=’nilai1’, field2=’nilai2’,...;

Sebagai contoh, kita akan menambahkan sebuah record ke dalam tabel pelanggan yang telah kita buat sebelumnya. Berikut ini perintah SQL untuk menambahkan sebuah record ke dalam tabel pelanggan :

13

INSERT INTO pelanggan VALUES ('P0001', 'Achmad Solichin','Jakarta Selatan', '0217327762', '[email protected]');

Jika perintah SQL di atas berhasil dieksekusi maka akan ditampilkan pesan sebagai berikut :


Setelah perintah SQL di atas berhasil dieksekusi, maka record atau data

dalam tabel pelanggan akan bertambah. Jalankan perintah berikut ini untuk melihat

isi tabel pelanggan ! SELECT * FROM pelanggan;

Tugas Untuk Anda

isi data pada tiap-tiap tabel yang dibuat tadi didalam db_akademik minimal 10 data

contoh:

tabel mhs

NIM Nama_mhs Jurusan Progstudi Semester

1212520173 Andi Informatika S1 II

tabel matkul

Kode_matkul Nama_matku

l

Jml_sks

KD001 SBD 2

tabel dosen

Kd_dosen Nama_dosen jabatan

DS001 Suriyati Dosen

Merubah Isi Record/data pada tabel

“Upadate nama_table set kolom=’ ’ where kolom2=’ ’;

Contoh : Upddate mhs set jurusan=’Manajamen Informatika’ where

nim=’1010520177’;

14

Menghapus isi Record

“Delete From nama_table where kolom=’ ’;

Contoh: Delete from mhs where nim=’1010520177’;

Latihan 2.

Isi Data pada masing-masing tabel berikut seperti gambar dibawah ini :

Tabel “mhs”

Table “Dosen”

15

Tabel “mk”

Soal :

1. Ubahlah nama mahasiswa yang namanya m.karmin menjadi Muhammad

Karmin.

2. Ubahlah jurusan mahasiswa menjadi manajemen informatika yang

jurusannya=D3 dan nimnya=1010520184

3. Hapus mahasiswa yang semester 6 dan jurusannya D3

4. Hapus dosen yang jabatannya karyawan yang memiliki kode DS003

5. Tampilkan nama matakuliah yang jumlah sksnya 1

6. Tampilkan matakuliah,jml_sks yg jumlah sksnya dibawah 3 sks

7. Tampilkan matakuliah yang jmlah sksnya 3 dan kodenya KD018

Jawaban :

1. mysql> update mhs set nama_mhs="Muhammad Karmin" where

nim="1010520167";

2. mysql> update mhs set jurusan="Manajemen Informatika" where

progstudi="D3" and nim="1010520184";

3. mysql> delete from mhs where semester="6" and progstudi="D3";

16

4. mysql> delete from dosen where jabatan="karyawan" and

kd_dosen="DS003";

5. mysql> select * from mk where jml_sks=1;

6. mysql> select nama_matkul,jml_sks from mk where jml_sks<3;

7. mysql> select * from mk where jml_sks=3 and kode_matkul="KD018";

Fungsi Penghubung (Operator) dan Order By

1. Menampilkan Data menggunakan Operator

Berikut ini operator perbandingan yang dapat digunakan untuk membandingkan dua buah

nilai dalam MySQL :

Operator =, akan bernilai TRUE jika nilai yang dibandingkan sama.

Operator != atau <>, akan bernilai TRUE jika nilai yang dibandingkan

TIDAK SAMA (berbeda).

Operator >, akan bernilai TRUE jika nilai yang pertama lebih besar dari

nilai kedua.

Operator >=, akan bernilai TRUE jika nilai yang pertama lebih besar atau

sama dengan nilai kedua.

Operator <, akan bernilai TRUE jika nilai yang pertama lebih kecil dari

nilai kedua.

2. Menampilkan Data Secara Terurut (Order By)

Menampilkan data secara terurut itu ada 2 macam : yaitu secara Ascending / Descending

Order by Asc : dari kecil besar | a z

Order by Desc : dari besar kecil | z a

Perintah untuk mengurutkan data :

“select field1,field2,field3 from nama_table where kondisi order by asc” (Ascending)

“select field1,field2,field3 from nama_table where kondisi order by desc” (Descending)

Latihan 3.

1. Tampilkan matakuliah yg kodenya dari KD012 sampai KD015.

2. Tampilkan dosen yang jabatannya bukan karyawan.

17

3. Tampilkan mhs yg jurusannya Teknik Informatika tapi bukan D3, dan

urutkan secara ascending.

4. Tampilkan mahasiswa yang jurusannya manajamen informatika,

progstudinya D3 tapi bukan semester 2.

5. Urutkan nama_mahasiswa,jurusan,semester yang progstudinya D3

secara descending.

Penggunaan Fungsi Like pada Query

Menampilkan isi record dengan menggunakan sebagian dari isi sebuah data. Secara

fungsi LIKE berfungsi sama seperti WHERE (=).

Query : SELECT * FROM nama_tabel WHERE nama_field LIKE ‘kondisi’;

Ada terdapat 3 kondisi di dalam like :

a. ‘%data%’ (data berada di antara)

Persen didepan dibelakang menunjukan bahwa record yang ingin ditampilkan adalah

semua record yang mengandung “data” pada sebuah field. “data” bisa berada diawal,

tengah ataupun akhir.

b. ‘%data’ (data berada di akhir)

Persen didepan menunjukan bahwa record yang ingin ditampilkan adalah semua record

yang mengandung “data” pada sebuah field dan “data” harus berada diakhir.

c. ‘data%’ (data harus berada di awal)

Persen dibelakang menunjukan bahwa record yang ingin ditampilkan adalah semua

record yang mengandung “data” pada sebuah field dan “data” harus berada di awal.

Fungsi IN not NOT IN

Digunakan untuk pencarian data menggunakan lebih dari satu filter pada perintah Where.

select field1,field2 from tabelnya where kolom in(‘record’,’record’,’record’);

Contoh : Select * from barang where kd_barang in(‘BR001’,’BR004’,’BR007’);

select field1,field2 from tabelnya where kolom not in(‘record’,’record’,’record’);

18

Contoh : Select * from barang where kd_barang in(‘BR001’,’BR004’,’BR007’);

Fungsi Between dan Not Between

Digunakan untuk menentukan jangkauan pencarian.

Penggunaan Between :

select field1,field2 from tabelnya where kolom between ‘nilai1’ and ‘nilai2’;

mysql> select * from barang where harga between '250000' and '4900000';+-----------+----------+---------+---------+| kd_barang | jenis | merk | harga |+-----------+----------+---------+---------+| BR003 | Harddisk | Seagate | 450000 || BR002 | Memory | V-Gen | 250000 || BR005 | Laptop | Compaq | 4900000 |+-----------+----------+---------+---------+3 rows in set (0.00 sec)

Penggunaan not Between :

select field1,field2 from tabelnya where kolom not between ‘nilai1’ and

‘nilai2’;

mysql> select * from barang where harga not between '250000' and '4900000';+-----------+--------+---------+---------+| kd_barang | jenis | merk | harga |+-----------+--------+---------+---------+| BR001 | Laptop | Thosiba | 5200000 || BR004 | Mouse | Compaq | 50000 |+-----------+--------+---------+---------+2 rows in set (0.00 sec)

19

Fungsi – Fungsi dalam mysql

Fungsi Aggregate

disebut juga dengan fungsi group atau fungsi ringkasan dimana fungsi yang digunakan untuk

menghasilkan nilai berdasarkan jumlah data atau record dalam kolom . dalam MySQL

terdapat beberapa fungsi agregat bawaan yang dapat kita gunakan untuk mengelola data2

yang ada di dalam database sesuai dengan kebutuhan dan penggunaan fungsi tersebut.

Dibawah ini beberapa fungsi dari agregat

Buatlah Database dengan Nama : Jual, dengan nama tabelnya penjualan.

20

Kemudian isikan record ke tabel tersebut. Agar tampilannya seperti gambar dibawah :

Contoh Kasus :

1. Mencari Rata-rata dari ekpresi atau field yg di inginkan (yg recordnya berupa nilai)

Cara : select avg(harga) from penjualan;

2. Mencari berapa jumlah data.

Cara : select count(*) from penjualan;

3. Mencari nilai paling besar dari filed yg ditentukan.

Cara : select max(harga) from penjualan;

4. Mencari nilai paling kecil dari field yg ditentukan.

Cara : select min(harga) from penjualan;

5. Menghitung jumlah nilai dari data2 dari field yg ditentukan.

Cara : select sum(harga) from penjualan;

6. Hitung jumlah harga yang jenisnya rokok.

7. Cari nilai tertinggi dari harga yg berjenis makanan.

8. Hitung berapa rata-rata harga barang yg harganya lebih besar dari 1000.

9. Hitung rata-rata harga dari barang yg jenisnya selain rokok.

10. Jumlahkan harga makanan yg harganya antara 1000 sampai 3500.

21

Fungsi Aritmatika

Fungsi aritmatika ini digunakan untuk menghitung record2 dari field secara aritmatik.

Beberapa contoh dari operator aritmatik didalam SQL.

Contoh Kasus lagi....

1. Tampilkan nama,harga dan setiap harga dikali dengan 100 pada table penjualan.

Cara : select nama,harga,harga*100 from penjualan;

2. Tampilkan nama,harga,dan setiap harga dikali dengan 1500 yang harga

barang=3500 pada table pejualan

Cara : select nama,harga,harga*1500 where harga=’3500’;

3. Tampilkan nama,harga,dan setiap harga dibagi dengan 5 pada table pejualan

Cara : select nama,harga,harga/5 from penjualan;

4. Tampilkan harga,dan setiap harga ditambah 50 pada table pejualan

5. Tampilkan harga dan setiap harga ditambah 50 dimana harga = 11000

6. Tampilkan harga,dan setiap harga dikurang 150 pada table pejualan.

7. Berapa jumlah Bayar Jika Diskon 10 % dari harga..........?

8. Diskon 15% diberikan jika harga jual besar dari 10000, brapa harga setelah

diskon......?

22

Relasi Antar Tabel Dalam MySQL

Relasi 2 tabel dalam mysql.

Penjualan

+kd_jual+nama+jenis+harga+kd_pembeli

pembeli

+kd_pembeli+nama_pembeli+Alamat

Tabel 1 & Tabel 2

Contoh :

Tampilkan nama_pembeli,alamat,nama barang,harganya dari tabel pembeli dan penjualan

Syntaxnya:

Select pembeli.nama_pemb,pembeli.alamat,penjualan.nama,penjualan.harga

From penjualan,pembeli where penjualan.kd_pembeli=pembeli.kd_pembeli;

Select tabel2.field_tbl2,tabel2.field_tbl2,tabel1.field_tbl1,tabel1.field_tbl1

from tabel1,tabel2 where tabel1.field_kunci=tabel2.field_kunci;

Cara lainnya :

Menggunakan NATURAL JOIN

Penggunaan join ini tidak perlu memakai kolom kunci.

Contoh :


From penjualan natural join pembeli;

Menggunakan INNER JOIN

Penggunaan join ini perlu memakai kolom kunci seperti relasi biasa.

Contoh:


23

From penjualan inner join pembeli

On pembeli.kd_pembeli=penjualan.kd_pembeli;

Tugas Soal Latihan (Menggunakan Database dengan nama “Jual”

dengan 2 Tabel, “Penjualan & Pembeli”).

Tabel Penjualan

Table pembeli

1). Tampilkan data pembeli yg membeli barang jenis makanan dan pasta gigi.

2). Tampilkan data pembeli yang membeli barang dengan harga antara 1000 sampai 3000.

3). Tampilkan data pembeli yang membeli barang dengan harga antara 1000 sampai 5000

dan diberi diskon 10% untuk yg berjenis pasta gigi.

4.) tampilkan data pembeli yg memiliki kd_jual=’KD02’,’KD05’,’KD07’ .

24

5.) tampilkan data pembeli dan jumlahkan total pembelian yg memiliki alamat pembeli di

mataram.

-------------------------( Review Materi dengan Contoh Latihan 4)-----------------------

Buatlah database dengan nama Penjualan.

1. Kemudian buatlah table-table dalam database (Penjualan_new) tsb dengan nama :Gunakan Perintah “Create Table”a. T_Pembeli

Kolom Tipe dataKd_pembeli Varchar(4) not null primary keyNama_pembeli Varchar(25) not nullAlamat TextStatus Varchar(7)

b. T_BarangKolom Tipe dataKd_barang Varchar(5) not null primary keyNama_barang Varchar(25) not nullJenis Varchar(10)Harga Int

c. T_TransaksiKolom Tipe dataTgl_trx Varchar(10) not nullKd_pembeli Varchar(4) not nullKd_barang Varchar(5) not nullJumlah_beli IntTotal Int

2. Ubah kolom nama_pembeli pada table T_Pembeli menjadi nama_pemb varchar(30). (Gunakan Perintah Alter table -> change)

3. Hapus kolom Total pada Tabel T_Transaksi. (Gunakan Perintah Alter table -> drop).4. Isikan data pada masing-masing table seperti dibawah ini :

Ada 3 cara dalam melakukan insert data ke table :

1. Insert into t_pembeli(kd_pembeli,nama_pemb,alamat,status) values(‘P001’,’Ali’,’Ampenan’,’Member’);

2. Insert into t_pembeli values(‘P001’,’Ali’,’Ampenan’,’Member’);

25

3. Insert into t_pembeli set kd_pembeli=’P001’,nama_pemb=’Ali’,alamat=’Ampenan’,status=’Member’;

T_PembeliKd_Pembeli Nama_pemb Alamat StatusP001 Ali Ampenan MemberP002 Hasan Mataram BiasaP003 Badu Cakranegara Biasa

T_BarangKd_Barang Nama_barang Jenis HargaBR001 Indomi Rasa Baso Makanan 2000BR002 Pilus Garuda Makanan 5000BR003 Pepsodent Sensitive Pasta Gigi 2500BR004 Close Up Pasta Gigi 3000BR005 Sensodyne Pasta Gigi 3500BR006 Lifebuoy Shampo Shampo 4000BR007 Clear Man Shampo 5000BR008 Head n Shoulder Shampo 4500BR009 Viva Cosmetic Kosmetik 7000BR010 Wardah Cosmetic Kosmetik 9000

T_TransaksiTgl_trx Kd_Pembeli Kd_Barang Jumlah_beli01-03-2014 P001 BR001 501-03-2014 P001 BR004 201-03-2014 P001 BR006 102-03-2014 P003 BR010 302-03-2014 P002 BR001 302-03-2014 P002 BR002 5

5. Ubah alamat menjadi Cakra untuk pembeli dengan nama Badu.6. Hapus barang dengan kode BR008.7. Tampilkan nama_pemb, alamat, nama_barang dan jumlah_beli.8. Tampilkan tgl_trx, nama_barang, jenis dan nama_pemb yang jenis nya Pasta Gigi.9. Tampilkan tgl_trx, nama_pemb, nama_barang, jenis, harga, jumlah_beli dan

total_harga yg didapat dengan harga*jumlah.

26

-------------------------( Kunci Jawaban Untuk Latihan 4)-----------------------

#Membuat Database baru dengan nama “Penjualan”.

mysql> create database penjualan;Query OK, 1 row affected (0.06 sec)

#Menggunakan Database yg telah dibuat yakni “Penjualan”.

mysql> use penjualan;Database changed ---- Pesan yg muncul ketikan berhasil

#Membuat Table dengan nama t_pembeli.

mysql> create table t_pembeli( -> kd_pembeli varchar(4) not null primary key, -> nama_pembeli varchar(25) not null, -> alamat text, -> status varchar(7));Query OK, 0 rows affected (0.14 sec)

#Membuat Table dengan nama t_barang.

mysql> create table t_barang( -> kd_barang varchar(5) not null primary key, -> nama_barang varchar(25) not null, -> jenis varchar(10), -> harga int);Query OK, 0 rows affected (0.14 sec)

#Membuat Table dengan nama t_transaksi.

mysql> create table t_transaksi(

27

-> tgl_trx varchar(10) not null, -> kd_pembeli varchar(25) not null, -> kd_barang varchar(25) not null, -> jumlah_beli int, -> total int);Query OK, 0 rows affected (0.14 sec)

#Mengubah kolom nama_pembeli menjadi nama_pemb.

mysql> alter table t_pembeli -> change nama_pembeli nama_pemb varchar(30);Query OK, 0 rows affected (0.20 sec)Records: 0 Duplicates: 0 Warnings: 0

#Menghapus kolom total dari table t_transaksi.

mysql> alter table t_transaksi -> drop total;Query OK, 0 rows affected (0.23 sec)Records: 0 Duplicates: 0 Warnings: 0

#Mengisi data/record pada table t_pembeli.

mysql> insert into t_pembeli values -> ('P001','Ali','Ampenan','Member'), -> ('P002','Hasan','Mataram','Biasa'), -> ('P003','Badu','Cakranegara','Biasa');Query OK, 3 rows affected (0.06 sec)Records: 3 Duplicates: 0 Warnings: 0

#Melihat isi data.record dari table t_pembeli.

mysql> select * from t_pembeli;+------------+-----------+-------------+--------+| kd_pembeli | nama_pemb | alamat | status |+------------+-----------+-------------+--------+| P001 | Ali | Ampenan | Member || P002 | Hasan | Mataram | Biasa || P003 | Badu | Cakranegara | Biasa |+------------+-----------+-------------+--------+3 rows in set (0.02 sec)

28

#Mengisi data/record pada table t_barang.

mysql> insert into t_barang values -> ('BR001','Indomi Rasa Baso','Makanan','2000'), -> ('BR002','Pilus Garuda','Makanan','5000'), -> ('BR003','Pepsodent Sensitive','Pasta Gigi','2500'), -> ('BR004','Close Up','Pasta Gigi','3000'), -> ('BR005','Sensodyne','Pasta Gigi','3500'), -> ('BR006','Lifebuoy Shampo','Shampo','4000'), -> ('BR007','Clear Man','Shampo','5000'), -> ('BR008','Head n Shoulder','Shampo','4500'), -> ('BR009','Viva Cosmetic','Kosmetik','7000'), -> ('BR010','Wardah Cosmetic','Kosmetik','9000');Query OK, 10 rows affected (0.03 sec)Records: 10 Duplicates: 0 Warnings: 0

#Mengisi data/record pada table t_transaksi.

mysql> insert into t_transaksi values -> ('01-03-2014','P001','BR001','5'), -> ('01-03-2014','P001','BR004','2'), -> ('01-03-2014','P001','BR006','1'), -> ('02-03-2014','P003','BR010','3'), -> ('02-03-2014','P002','BR001','3'), -> ('02-03-2014','P002','BR002','5');Query OK, 6 rows affected (0.05 sec)Records: 6 Duplicates: 0 Warnings: 0

#Mengubah record/data alamat menjadi cakra untuk nama pembeli “Badu”.

mysql> update t_pembeli set alamat='Cakra' where nama_pemb='Badu';Query OK, 1 row affected (0.06 sec)Rows matched: 1 Changed: 1 Warnings: 0

#Menampilkan hasil perubahannya.

mysql> select * from t_pembeli;+------------+-----------+---------+--------+

29

| kd_pembeli | nama_pemb | alamat | status |+------------+-----------+---------+--------+| P001 | Ali | Ampenan | Member || P002 | Hasan | Mataram | Biasa || P003 | Badu | Cakra | Biasa |+------------+-----------+---------+--------+

#Menghapus data barang dengan kode barang “BR008”.

mysql> delete from t_barang where kd_barang='BR008';Query OK, 1 row affected (0.06 sec)

#Menampilkan hasil perubahannya.

mysql> select * from t_barang;+-----------+---------------------+------------+-------+| kd_barang | nama_barang | jenis | harga |+-----------+---------------------+------------+-------+| BR001 | Indomi Rasa Baso | Makanan | 2000 || BR002 | Pilus Garuda | Makanan | 5000 || BR003 | Pepsodent Sensitive | Pasta Gigi | 2500 || BR004 | Close Up | Pasta Gigi | 3000 || BR005 | Sensodyne | Pasta Gigi | 3500 || BR006 | Lifebuoy Shampo | Shampo | 4000 || BR007 | Clear Man | Shampo | 5000 || BR009 | Viva Cosmetic | Kosmetik | 7000 || BR010 | Wardah Cosmetic | Kosmetik | 9000 |+-----------+---------------------+------------+-------+9 rows in set (0.01 sec)

#Menampilkan nama_pemb, alamat, nama_barang dan jumlah_beli dari table t_pembeli, t_barang dan t_transaksi (relasi table).

mysql> select t_pembeli.nama_pemb,t_pembeli.alamat, -> t_barang.nama_barang,t_transaksi.jumlah_beli -> from t_pembeli,t_barang,t_transaksi -> where t_pembeli.kd_pembeli=t_transaksi.kd_pembeli -> and t_barang.kd_barang=t_transaksi.kd_barang;+-----------+---------+------------------+-------------+| nama_pemb | alamat | nama_barang | jumlah_beli |+-----------+---------+------------------+-------------+| Ali | Ampenan | Indomi Rasa Baso | 5 |

30

| Ali | Ampenan | Close Up | 2 || Ali | Ampenan | Lifebuoy Shampo | 1 || Badu | Cakra | Wardah Cosmetic | 3 || Hasan | Mataram | Indomi Rasa Baso | 3 || Hasan | Mataram | Pilus Garuda | 5 |+-----------+---------+------------------+-------------+

#Menampilkan tgl_trx, nama_barang, jenis dan nama_pemb yang jenis nya Pasta Gigi dari table t_pembeli, t_barang dan t_transaksi (relasi table).

mysql> select t_transaksi.tgl_trx,t_barang.nama_barang,t_barang.jenis, -> t_pembeli.nama_pemb -> from t_pembeli,t_barang,t_transaksi -> where t_pembeli.kd_pembeli=t_transaksi.kd_pembeli -> and t_barang.kd_barang=t_transaksi.kd_barang -> and t_barang.jenis='Pasta Gigi';+------------+-------------+------------+-----------+| tgl_trx | nama_barang | jenis | nama_pemb |+------------+-------------+------------+-----------+| 01-03-2014 | Close Up | Pasta Gigi | Ali |+------------+-------------+------------+-----------+1 row in set (0.00 sec)

#Menampilkan tgl_trx, nama_pemb, nama_barang, jenis, harga, jumlah_beli dan total_harga yg didapat dengan harga*jumlah dari table t_pembeli, t_barang dan t_transaksi (relasi table).

mysql> select t_transaksi.tgl_trx,t_pembeli.nama_pemb,t_barang.nama_barang, -> t_barang.jenis,t_barang.harga,t_transaksi.jumlah_beli, -> (t_barang.harga*t_transaksi.jumlah_beli) as total_harga -> from t_pembeli,t_barang,t_transaksi -> where t_pembeli.kd_pembeli=t_transaksi.kd_pembeli -> and t_barang.kd_barang=t_transaksi.kd_barang;+------------+-----------+------------------+------------+-------+-------------+-------------+| tgl_trx | nama_pemb | nama_barang | jenis | harga | jumlah_beli | total_harga |+------------+-----------+------------------+------------+-------+-------------+-------------+| 01-03-2014 | Ali | Indomi Rasa Baso | Makanan | 2000 | 5 | 10000 || 01-03-2014 | Ali | Close Up | Pasta Gigi | 3000 | 2 | 6000 || 01-03-2014 | Ali | Lifebuoy Shampo | Shampo | 4000 | 1 | 4000 || 02-03-2014 | Badu | Wardah Cosmetic | Kosmetik | 9000 | 3 | 27000 |

31

| 02-03-2014 | Hasan | Indomi Rasa Baso | Makanan | 2000 | 3 | 6000 || 02-03-2014 | Hasan | Pilus Garuda | Makanan | 5000 | 5 | 25000 |+------------+-----------+------------------+------------+-------+-------------+-------------+6 rows in set (0.00 sec)

-------------------------------------( KUIS SBD PASCA UTS )------------------------------------

Gunakan data seperti pada materi sebelumnya atau database “jual” dengan tabel penjualan

dan pembeli. Kerjakan dengan Santai n Tenang...

Jangan Lupa Berdoa, agar di mudahkan (^_^)

Soal !

1. Tampilkan nama_pemb,alamat, dan total penjualan untuk yang

berjenis makanan.

2. Tampilkan nama_pmbeli, alamat, dan totalkan penjualan yg

kode_pembelinya KP01

3. Tampilkan data penjualan dan totalkan harga berdasarkan jenis

barang.

4. Tampilkan nama pembeli,nama barang, harga, diskon 7% dan

harga_bersih untuk pembeli yg pembelinya barang jenis makanan.

5. Jumlahkan harga barang yg memiliki nama barang akhiran ‘a’.

Jawaban:

1). select pembeli.nama_pemb,pembeli.alamat,sum(harga) as totalfrom pembeli,penjualan

where pembeli.kd_pembeli=penjualan.kd_pembeli andpenjualan.jenis="makanan";

atau cara ke 2

SELECT pembeli.nama_pemb, pembeli.alamat, sum( harga ) AS totalFROM pembeli, penjualan

WHERE pembeli.kd_pembeli = penjualan.kd_pembeli

32

GROUP BY penjualan.jenisHAVING penjualan.jenis = "makanan"

2). SELECT pembeli.nama_pemb, pembeli.alamat, sum( harga ) AS totalFROM pembeli, penjualan

WHERE pembeli.kd_pembeli = penjualan.kd_pembeliAND pembeli.kd_pembeli = 'KP01'

Cara ke 2

SELECT pembeli.nama_pemb, pembeli.alamat, sum( harga ) AS totalFROM pembeli, penjualan

WHERE pembeli.kd_pembeli = penjualan.kd_pembeliGROUP BY pembeli.kd_pembeli

HAVING pembeli.kd_pembeli = "KP01"

3). SELECT penjualan . * , sum( penjualan.harga ) FROM pembeli, penjualan

WHERE pembeli.kd_pembeli = penjualan.kd_pembeliGROUP BY penjualan.jenis

4). select pembeli.nama_pemb,pembeli.alamat,penjualan.harga,(penjualan.harga*0.07) as diskon,(penjualan.harga-(penjualan.harga*0.07)) as harga_bersih

from pembeli,penjualanwhere pembeli.kd_pembeli=penjualan.kd_pembeli and

penjualan.jenis="makanan";

5). SELECT penjualan.nama, sum( penjualan.harga ) FROM penjualan

WHERE penjualan.nama LIKE "%a"

Atau cara lain

SELECT group_concat(penjualan.nama separator ',') as nama, sum( penjualan.harga )FROM penjualan

WHERE penjualan.nama LIKE "%a"

33

Materi Tambahan (GROUP BY dan HAVING)Salah satu fitur dari MySQL adalah dukungan terhadap berbagai fungsi agregat GROUP BY

dan HAVING untuk pengelompokkan data.

Sebelum mencoba fungsi GROUP BY dan HAVING, perlu diketahui bahwa keduanya sangat

berkaitan dengan fungsi agregat.

Fungsi agregat merupakan kelompok fungsi di MySQL yang memungkinkan untuk memiliki

parameter berupa kelompok data. Sebagai contoh dari fungsi agregat adalah fungsi SUM() yang akan

menjumlahkan seluruh nilai yang menjadi parameternya. Contoh lain adalah fungsi MAX() yang akan

menghasilkan nilai terbesar dari keseluruhan nilai yang menjadi parameternya.

Contoh Kasus.(Gunakan database Jual dari materi sebelumnya)

1. Tampilkan nama,jenis barang,harga kelompokkan berdasarkan harga.

34

Caranya : select nama,jenis from penjualan group by harga;

2. Tampilkan nama,jenis, harga yg harganya tertinggi dan kelompokkan berdasarkan

jenis barang.(

Caranya : select nama,jenis,harga, max(harga) from penjualan group by jenis;

3. Tampilkan nama, jenis, harga, dan dikelompokkan berdasarkan jenis dan jenisnya

kecuali rokok.

Caranya : select nama,jenis,harga from penjualan group by jenis having

jenis<>”rokok”;

Latihan.

1. Tampilkan harga dan setiap harga dikurang 50 dimana harga >= 3000

Select harga,harga-50 from penjualan where harga >=’3000’;

2. Tampilkan harga,sisa bagi harga dari 1250 pada table pejualan

3. Berapa jumlah Bayar Jika Diskon 10 % dari harga..........?

Select nama,jenis,harga,harga-(harga*0.1) from penjualan;

4. Diskon 15% diberikan jika harga jual besar dari 10000......?

Select nama,jenis,harga,harga-(harga*0.15) from penjualan where harga>=’10000’;

5. Tampilkan nama, jenis, harga dan urutkan harganya dari yg paling murah

Select nama,jenis,harga from penjualan order by harga asc; .

6. Tampilkan nama,jenis,harga dan jumlahkan harga barang berdasarkan jenisnya

Select nama,jenis,harga,sum(harga) from penjualan group by jenis;

7. Tampilkan nama, jenis barang, dan harganya, lalu kelompokkan berdasarkan jenis

setelah dijumlahkan harga tiap jenisnya dan tentukan rata-rata harga tiapnya.

Select nama,jenis,harga,sum(harga),avg(harga) from penjualan group by jenis;

35

IZIN AKSES USER PADA MYSQL

Konfigurasi Izin Akses User

Sebelum membahas masalah izin dan akses data, berikut macam-macam izin akses yang

diperlukan oleh user sesuai kebutuhannya:

columns_priv Columns Menyimpan hak akses user terhadap kolom

tables_priv Tables menyimpaninformasimengenaihakaksesuserterhadaptabel

Priviliges type dalam mysql

36

Membuat User

1. Create user [nama user] identified by ‘[password]’;

Menghapus User

Drop user [nama_user];

* jangan mengikutkan tanda [ ].

Melihat User yang ada di Mysql

select user,password from mysql.user;

MENGGUNAKAN GRANT

Grant merupakan perintah untuk memberikan hak izin akses bagi user agar dapat

mengakses database, tabel dan kolom. Terdapat 4 pilihan perintah grant:

1) IZIN AKSES PENUH

Digunakan untuk mengakses seluruh database yang berada pada server,

konfigurasi ini terletak pada tabel user. Untuk memberikan izin akses penuh pada

user, anda terlebih dahulu harus memilih database mysql sebagai database aktif

dengan perintah use. Selanjutnya gunakan perintah berikut:

grant all privileges on *.* to [nama_user] identified by ‘[passwordnya]’

with grant option


Perintah di atas akan memberikan izin akses penuh setara dengan root pada user.

Perintah identified by password user yg dibuat, akan menerapkan user tersebut agar

tetap memasukkan password saat mengakses database.

Pernyataan with grant option akan memberikan hak penuh pada user sehingga

user tersebut juga mampu melakukan perintah grant tertentu pada user lainnya.

37

2) IZIN AKSES DATABASE

Digunakan untuk mengakses table-tabel yang berada pada database yang telah

ditentukan.Untuk memberikan izin akses database pada user, lakukan perintah

berikut:

grant all privileges on [nama database].* to [nama_user] identified by ‘[passwordnya]’

with grant option


3) IZIN AKSES TABEL

Digunakan untuk mengatur izin akses terhadap semua kolom yang terdapat pada

table yang ditentukan. Izin ini terdiri dari SELECT, INSERT, UPDATE,

DELETE, CREATE, DROP, GRANT, REFERENCES, INDEX, dan ALTER.

Untuk mencoba hak di atas, terlebih dahulu buat database dengan nama tes.

GRANT [priviliges_type] ON nama_db.nama_table to user_name;

Contohnya :

Grant select on tes.contoh to lita;


4) IZIN AKSES KOLOM

Digunakan untuk mengatur izin akses pada kolom yang ditentukan saja. Hak

Aksesnya(privilege type) meliputi: SELECT, INSERT, UPDATE, dan REFERENCES.

Contoh :

Grant [privilege type](nama_field) on nama_db.nm_tabel to nm_user identified by ‘nm_pasword_usernya’;


5) MENAMPILKAN IZIN AKSES

Dengan menggunakan perintah:

SHOW GRANTS FOR nama_user;

38

6) FLUSH PRIVILEGES

Setiap melakukan konfigurasi hak akses agar hak akses yg diberikan berfungsi.

7) MENGHAPUS IZIN AKSES

revoke privilege_type on nm_db.nm_tbl from nm_user;

opsi : privilege_type = jenis izin akses yg diberikan

nm_db = nama data bases yg di pakai ( bias menggunakan * yang berarti untuk semua DB)

nm_tbl = nama table yg di pakai ( bias menggunakan * yang brarti semua table yg di gunakan)

LATIHAN HAK AKSES

1. Buat 3 buah user dengan spesifikai sebagai berikut :

a. Nama user : indri identified by (password) indri0212

b. Nama user : anton identified by (password ) antonaja

c. Nama user : Rini identified by (password) Rini2d

2. Buat 3 buah database dengan spesifikasi sebagai berikut :

a. Database akademik terdiri dari tabel tabel berikut :

Tbl_mahasiswa, tbl_jurusan, tbl_kelas, tbl_matakuliah dan tbl_dosen

b. Database penjualan terdiri dari tabel-tabel sebgai berikut :

Tbl_jenis_brg, tbl_barang, tbl_suplayer, tbl_customer, tbl_pembelian, tbl_pembelian

c. Database penggajian terdiri dari tabel – tabel sebagai berikut :

Tbl_karyawan, tbl_golongan, tbl_jabatan, tbl_rekap_gaji

3. Atur akses dari ke 3 user tersebut menjadi seperti dibawah ini :

a. Atur hak akses indri sebagai administrator dan dia bisa melakukan semua aktivitas dalam semua database yang ada dalam database server diantaranya insert data, edit data, hapus data dan melihat semua data dalam semua database.

39

b. Atur hak akses anton supaya dia bisa menambahkan data, dan melihat data dalam semua tabel di semua database

c. Atur hak akses rini supaya dia hanya bisa melihat data pada tbl_karyawan, tbl_golongan dan tbl_jabatan, pada database penggajian

DAFTAR PUSTAKA

Buku Komputer Gratis Dari Achmatim.Net

MySQL 5 Dari Pemula Hingga Mahir Achmad Solichin

* Mohon maaf bila ada kekurangan…Smoga Bermanfaat,,, Salam Super ^^

from “Para Pencari Ilmu”

Modul Sbd 2013

Documents

Transcript of Modul Sbd 2013