PENDAHULUAN Latar Belakang
Basis data adalah kumpulan informasi yang disimpan di dalam komputer secara sistematik sehingga dapat diperiksa menggunakan suatu program komputer untuk memperoleh informasi dari basis data tersebut. Perangkat lunak yang digunakan untuk mengelola dan memanggil kueri (query) basis data disebut sistem manajemen basis data (database management system, DBMS). Sistem basis data dipelajari dalam ilmu informasi. Dengan didukung perkembangan teknologi, kesulitan tersebut dapat diatasi dengan melalui MySQL. Dengan MySQL ini kita dapat mengubah sebuah tabel dengan menggunakan Structured Query Language (SQL). Sebuah database MySQl disusun oleh beberapa tabel yang didalam setiap tabel terdapat beberapa kolom Tujuan Mahasiswa mampu menyelesaikan permasalahan yang ada pada studi kasus menggunakan perintah-perintah SQL dengan benar dan dapat menampilkan hasil yang benar.
1
TEORI STRUCTURED QUERY LANGUAGE (SQL) SELECT DENGAN OPERATOR PERBANDINGAN, LOGIKA DAN CLAUSA LIKE 1. OPERATOR PERBANDINGAN Berikut adalah beberapa operator perbandingan yang digunakan pada sintax mysql: a. =, >, <, >=, <=, <> Penjelasan:
Nama = > < >= <= <>
Deskripsi Sama dengan Lebih besar Lebih kecil Lebih besar atau sama dengan Lebih kecil atau sama dengan Tidak sama dengan
Format penulisan: SELECT [nama kolom1], [nama kolom2], dst FROM [nama tabel] WHERE [nama kolom] [operator perbandingan] [nilai]
Contoh: table_buku ISBN judul 1111 Dunia Sophie 2222 The Lost Symbol 3333 A Morcking Bird 4444 The Secret 5555 Piano di Tepi Pantai a.
harga 10000 10000 10000 11000 10000
penerbit Mizan Gramedia Gramedia Gramedia Andi
SELECT judul, harga FROM tabel_buku WHERE harga <> 10000; Output: judul The Secret
b.
Jml_hal 765 867 685 687 859
Harga 11000
SELECT * FROM tabel_buku WHERE harga = 10000;
2
Output: ISBN judul 1111 Dunia Sophie 2222 The Lost Symbol 3333 A Morcking Bird 5555 Piano di Tepi Pantai
Jml_hal 765 867 685 859
harga 10000 10000 10000 10000
penerbit Mizan Gramedia Gramedia Andi
b. BETWEEN Melakukan select berdasarkan rentang nilai tertentu. Format Penulisan:
SELECT [nama kolom1], [nama kolom2], dst FROM [nama tabel] WHERE [nama kolom] BETWEEN [nilai1] AND [nilai2] Contoh: table_buku ISBN judul 1111 Dunia Sophie 2222 The Lost Symbol 3333 A Morcking Bird 4444 The Secret 5555 Piano di Tepi Pantai
Jml_hal 765 867 685 687 859
harga 10000 10000 10000 11000 10000
penerbit Mizan Gramedia Gramedia Gramedia Andi
SELECT * FROM tabel_buku WHERE harga BETWEEN 9000 AND 11000;
Output: ISBN judul 1111 Dunia Sophie 2222 The Lost Symbol 4444 The Secret
Jml_hal 765 867 687
harga 10000 10000 11000
penerbit Mizan Gramedia Gramedia
2. OPERATOR LOGIKA Berikut adalah beberapa operator logika yang digunakan pada sintax mysql: AND, OR, XOR XOR
Contoh: table_buku ISBN judul 1111 Dunia Sophie 2222 The Lost Symbol 3333 A Morcking Bird 4444 The Secret 5555 Piano di Tepi Pantai
Jml_hal 765 867 685 687 859
harga 10000 10000 10000 11000 10000
penerbit Mizan Gramedia Gramedia Gramedia Andi
3
1.
SELECT * FROM tabel_buku WHERE harga = 10000 AND penerbit = ‘gramedia’;
Output: ISBN judul 2222 The Lost Symbol 3333 A Morcking Bird
2.
Jml_hal 867 685
harga 10000 10000
penerbit Gramedia Gramedia
SELECT * FROM tabel_buku WHERE harga = 10000 OR penerbit = ‘gramedia’;
Output: ISBN judul 1111 Dunia Sophie 2222 The Lost Symbol 3333 A Morcking Bird 5555 Piano di Tepi Pantai
3.
Jml_hal 765 867 685 859
harga 10000 10000 10000 10000
penerbit Mizan Gramedia Gramedia Andi
SELECT * FROM tabel_buku WHERE harga = 10000 XOR penerbit = ‘gramedia’;
Output: ISBN judul 1111 Dunia Sophie 2222 The Lost Symbol 3333 A Morcking Bird 5555 Piano di Tepi Pantai
Jml_hal 765 867 685 859
harga 10000 10000 10000 10000
penerbit Mizan Gramedia Gramedia Andi
3. Clausa Like Berikut adalah beberapa klausa yang digunakan pada sintax mysql: “%” dan” _”
Penjelasan: Nama % _
Deskripsi Mencocokkan dengan berapapun jumlah karakter Mencocokkan hanya dengan satu karakter
Penggunaan LIKE: Penggunaan Deskripsi “Prak%” Mencari string yang diawali dengan karakter “Prak” “%Prak” Mencari string yang diakhiri dengan karakter “Prak” “%Prak%” Mencari string yang mengandung karakter “Prak” Mencari string yang diawali dengan karakter “Prak” dan “Prak_” diakhiri 1 karakter Mencari string yang diakhiri dengan karakter “Prak” dan “_Prak” diawali 1 karakter
4
“_Prak_”
Mencari string yang mengandung karakter “Prak” yang diawali dan diakhiri dengan 1 karakter
Format Penulisan: SELECT [nama kolom1], [nama kolom2], dst FROM [nama tabel] WHERE [nama kolom] LIKE [klausa LIKE]
Contoh: table_buku ISBN judul 1111 Dunia Sophie 2222 The Lost Symbol 3333 A Morcking Bird 4444 The Secret 5555 Piano di Tepi Pantai
Jml_hal 765 867 685 687 859
harga 10000 10000 10000 11000 10000
penerbit Mizan Gramedia Gramedia Gramedia Andi
SELECT * FROM tabel_buku WHERE judul LIKE ‘Worl%’;
Output: ISBN judul 1111 Dunia Sophie 4444 The Secret
Jml_hal 765 687
harga 10000 11000
penerbit Mizan Gramedia
5
STRUCTURED QUERY LANGUAGE (SQL) SELECT, ORDER BY, ALIASING, KALKULASI FIELD DAN CLAUSA BETWEEN
Data yang diambil dari satu atau beberapa table kadang membutuhkan pengurutan, baik itu menaik maupun menurun. Perintah SQL yang memungkinkan untuk mengurutkan data menggunakan ORDER BY. Selain pengurutan data, terkadang dibutuhkan suatu field baru yang merupakan hasil perhitungan dari field-field lainnya. Field hasil perhitungan tersebut disebut dengan field kalkulasi. Pengambilan data kadang-kadang juga dilakukan pada field tertentu, misalnya dari tahun 2000 sampai tahun 2002 dan lain-lain. Pengambilan data seperti itu bias dengan mudah dilakukan dengan menggunakan perintah BETWEEN. 1. ORDER BY Merupakan perintah yang digunakan untuk mengurutkan data berdasarkan field tertentu. Sintaks untuk menambahkan record: SELECT <`field1`, `field2`, …, `fieldn`>
FROM <`table name`> [WHERE expression] ORDER BY <`field1` [ASC/DESC],… `fieldn` [ASC/DESC]>
[LIMIT a,b]
Perintah ORDER BY akan mengurutkan data berdasarkan field tertentu. Pengurutan bisa dilakukan berdasarkan satu atau beberapa field. Untuk setiap field tersebut, bisa diurutkan menaik ASC ataupun menurun DESC. Penggunaan perintah ORDER BY tanpa kata kunci ASC/DESC akan dianggap sebagai ASC(menaik). Misalnya terdapat table Barang sebagai berikut: Field Tipe Data Keterangan kode_barang INTVARCHAR[30] PRIMARY KEY nama_barang VARCHAR[30] Harga INT Stock TINYINT Perintah untuk menampilkan data barang yang diurutkan berdasarkan harga mulai dari harga tertinggi, kemudian untuk barang dengan harga yang sama akan diurutkan berdasarkan nama dari A-Z: SELECT * FROM Barang ORDER BY Harga DESC, nama_barang ASC;
6
2. BETWEEN Perintah ini digunakan untuk mengambil data pada interval tertentu. Perintah ini akan mengikuti klausa WHERE. SELECT <`field1`, `field2`, …, `fieldn`>
FROM <`table name`> [WHERE <`field`> BETWEEN AND ] [LIMIT a,b]
Contoh perintah untuk mengambil data barang dengan harga antara 2000 sampai 10000 adalah sebagai berikut: SELECT * FROM Barang WHERE Harga BETWEEN 2000 AND 10000
3. FIELD KALKULASI Kadangkala kita ingin menampilkan sebuah data yang dihasilkan dari kombinasi beberapa field. Field kalkulasi memungkinkan semua itu. Field Kalkulasi adalah sebuah field yang dihasilkan dari kombinasi field-field yang ada dalam table. Field hitungan tidak benar-benar ada dalam table database, hanya bersifat on-the fly dalam statement SELECT. Field yang dihasilkan tidak mempunyai nama, tetapi dapat diberikan alternative nama dengan pemberian alias. Misalnya jika ingin mengetahui berapakah uang yang akan diterima untuk masing-masing barang jika terjual semua, maka perintah SQL-nya adalah sebagai berikut: SELECT nama_barang, (harga*stock) FROM Barang
Perintah di atas akan menghasilkan sebuah field kalkulasi tanpa nama, sehingga kurang informative. Agar field tersebut memiliki nama, maka dapat digunakan alias, seperti perintah di bawah ini: SELECT nama_barang, (harga*stock) AS totalUang FROM Barang
Urutan perintah untuk keseluruhan klausa adalah sebagai berikut: SELECT <`field1`, `field2`, …, `fieldn`>
FROM <`table name`> [WHERE expression] ORDER BY <`field1` [ASC/DESC],… `fieldn` [ASC/DESC]>
[LIMIT a,b]
7
STRUCTURED QUERY LANGUAGE (SQL) FUNGSI AGREGASI DAN CLAUSA HAVING
Dalam beberapa kasus sering dibutuhkan perhitungan kelompok data seperti mencari jumlah total data (baris) dalam sebuah table, mencari berapa nilai maksimum dari sebuah kolom pada table, atau bahkan mencari nilai ratarata sebuah kolom pada table. Hal tersebut dimungkinkan dalam menggunakan perintah SQL yang disebut dengan Fungsi Agregasi. Fungsi agregasi digunakan untuk melakukan operasi pada kelompok-kelompok baris data, fungsi ini akan menghasilkan satu baris data untuk setiap kelompok baris data yang ada. Yang termasuk fungsi agregasi adalah: Average : AVG Minimum : MIN Maximum : MAX Total : SUM Count : COUNT Fungsi Agregasi dapat diterapkan pada seluruh data (baris) pada sebuah table menjadi satu himpunan ataupun dibagi menjadi beberapa kelompok himpunan pada tabel tersebut. Agar dapat dikelompokkan menjadi beberapa himpunan maka digunakan Klausa GROUP BY. Adanya Klausa GROUP BY memungkinkan mkeluaran eksekusi perintah SQL tersebut menghasilkan satu atau lebih data (baris). Dengan banyaknya baris yang kemungkinan dihasilkan maka data keluaran tersebut juga dapat diseleksi kembali menggunakan Klausa HAVING. 1. Perintah Average (AVG) Fungsi AVG digunakan untuk memperoleh nilai rata-rata dari seluruh nilai pada suatu kolom. Sintaks: SELECT AVG(nama_kolom) FROM nama_tabel;
2. Perintah Minimum (MIN) Digunakan untuk memperoleh nilai minimum (yang terkecil) dari suatu kolom. Sintaks: SELECT MIN(nama_kolom) FROM nama_tabel;
3. Perintah Maximum (MAX) Digunakan untuk memperoleh nilai maksimum (yang terbesar) dari suatu kolom. Sintaks: SELECT MAX(nama_kolom) FROM nama_tabel;
8
4. Perintah Total (SUM) Digunakan untuk memperoleh nilai penjumlahan seluruh baris pada suatu kolom. Sintaks: SELECT SUM(nama_kolom) FROM nama_tabel;
5. Perintah Count (COUNT) Fungsi agregasi count sering digunakan untuk menghitung jumlah baris dalam tabel. Sintaks: SELECT COUNT(nama_kolom) FROM nama_tabel;
6. Fungsi Agregasi dengan Klausa GROUP BY Jika fungsi agregasi nomor 1 sampai 5 ingin diterapkan pada kelompok himpunan baris data, maka digunakan klausa GROUP BY. Baris-baris data yang memiliki nilai yang sama pada satu kolom jika diterapkan klausa group by maka akan dimasukkan dalam satu kelompok. Sintaks: SELECT nama_kolom_group, COUNT(nama_kolom) FROM nama_tabel GROUP BY nama_kolom_group;
7. Fungsi Agregasi dengan Klausa HAVING Klausa Having hamper sama dengan WHERE, hanya klausa ini diterapkan pada SQL yang menggunakan Klausa Group By. Klausa Having digunakan untuk menyeleksi hasil kelompok baris yang dihasilkan oleh Klausa Group By, dalam kasus agregasi dapat diterapkan seleksi pada kolom agregasi. Sintaks: SELECT nama_kolom_group, Fungsi_Agregat(nama_kolom) FROM nama_tabel GROUP BY nama_kolom_group Having kondisi;
9
STRUCTURED QUERY LANGUAGE (SQL) SELECT DENGAN MENGGUNAKAN JOIN
Perintah JOIN pada MySQL digunakan untuk menghubungkan dua atau lebih tabel. Tipe-tipe join yang biasa digunakan dalam query adalah INNER, LEFT, RIGHT dan OUTER. Untuk lebih memudahkan dalam memahami perbedaan dari masing-masing tipe JOIN maka digunakan dua table berikut. Table tb_mahasiswa id 1 2 3 4 5
nama ADI IKA BUDI WATI DODI
mata_kuliah 1 1 2 5 NULL
Dari table tb_mahasiswa di atas dapat dilihat bahwa field mata_kuliah mengacu ke table lain (dalam hal ini table matakuliah) Table matakuliah id 1 2 3 4 5
mata_kuliah Basis Data Logika Pemrograman Sistem Informasi Struktur Data Otomata
Untuk menampilkan nama mahasiswa beserta nama mata kuliah yang diambilnya dapat digunakan query SELECT dengan perintah JOIN untuk menggabungkan kedua table di atas. Hasil dari query yang dijalankan tergantung dari tipe JOIN yang digunakan. 1. INNER JOIN Tipe ini merupakan tipe JOIN yang paling sering digunakan. Hasil dari query SELECT dengan tipe INNER JOIN adalah set dari record yang memenuhi syarat yang disebutkan pada klausa ON. Sebagai contoh: tb_mahasiswa.nama, tb_matakuliah.nama_matakuliah FROM tb_mahasiswa INNER JOIN tb_matakuliah ON tb_mahasiswa.mata_kuliah=tb_matakuliah.id; SELECT
Perintah SELECT bertujuan untuk menampilkan “nama” dari table “tb_mahasiswa” dan “nama_matakuliah” dari tabe “tb_matakuliah”. Karena kedua field yang ingin ditampilkan berasal dari dua table yang berbeda, maka kedua table tersebut harus digabungkan. Perintah FROM di atas menggabungkan table “tb_mahasiswa” dengan table “tb_matakuliah” secara INNER JOIN. Hubungan kedua table itu didefinisikan melalui perintah ON
10
yaitu dengan menyamakan field “mata_kuliah” pada table “tb_mahasiswa” dengan field “id” pada table “tb_matakuliah”. Hasil dari perintah di atas adalah sebagai berikut: nama ADI IKA BUDI WATI
mata_kuliah Basis Data Basis Data Logika Pemrograman Otomata
Hasil dari query di atas tidak menampilkan nama DODI karena nilai field “mata_kuliah” pada table “tb_mahasiswa” untuk mahasiswa DODI adalah NULL, dimana nilai NULL ini tidak dapat ditemukan padanannya pada field “id” di table “tb_matakuliah”. Hasil query SELECT menggunakan INNER JOIN, jika digambarkan menggunakan notasi himpunan adalah sebagai berikut:
2. LEFT JOIN Bagaimana jika hasil dari query yang diinginkan adalah untuk menampilkan semua nama mahasiswa beserta mata kuliah yang diambil walaupun mahasiswa itu belum mengambil mata kuliah apapun? Tipe LEFT JOIN dapat digunakan untuk mendapatkan hasil yang diinginkan. LEFT JOIN akan menghasilkan suatu set record yang menampilkan semua baris dari table yang terletak di kiri (dalam hal ini “tb_mahasiswa”) tanpa memperhatikan apakah baris-baris tersebut memiliki padanan pada table sebelah kanan (“tb_matakuliah”). tb_mahasiswa.nama, tb_matakuliah.nama_matakuliah FROM tb_mahasiswa LEFT JOIN tb_matakuliah ON tb_mahasiswa.mata_kuliah=tb_matakuliah.id; SELECT
Hasil query di atasa adalah sebagai berikut: nama mata_kuliah ADI Basis Data IKA Basis Data BUDI Logika Pemrograman WATI Otomata DODI (NULL)
11
Hasil dari LEFT JOIN jika digambarkan menggunakan notasi himpunan adalah sebagai berikut:
3. RIGHT JOIN Perintah RIGHT JOIN akan menampilkan semua entry dari table sebelah kanan dari perintah JOIN walaupun ada baris pada table sebelah kanan yang tidak memiliki padanannya pada tabel sebelah kiri. Sebagai contoh: missal hasil dari query yang diinginkan adalah untuk menampilkan semua mata kuliah yang ada walaupun tidak ada mahasiswa yang mengambil mata kuliah tersebut. Perintah query untuk mendapatkan hasil tersebut adalah: tb_mahasiswa.nama, tb_matakuliah.nama_matakuliah FROM tb_mahasiswa RIGHT JOIN tb_matakuliah ON tb_mahasiswa.mata_kuliah=tb_matakuliah.id; SELECT
Hasil query di atas adalah: nama ADI IKA BUDI (NULL) (NULL) WATI
mata_kuliah Basis Data Basis Data Logika Pemrograman Struktur Data Sistem Informasi Otomata
Jika diperlihatkan menggunakan himpunan, hasil query tersebut adalah:
12
4. OUTER JOIN Perintah OUTER JOIN akan menampilkan seluruh isi dari kedua table tanpa memperhatikan apakah masing baris pada kedua table memiliki pasangan pada table lainnya atau tidak. Ketika tidak ditemukan padanannya maka nilai dari field tersebut akan diisi dengan NULL. Perintah OUTER JOIN ini tidak terlalu bermanfaat dibandingkan dengan INNER, LEFT ataupun RIGHT. OUTER JOIN ini tidak diimplementasikan di MySQL, namun hasil yang sama dapat diperoleh menggunakan perintah UNION serta LEFT dan RIGHT JOIN. tb_mahasiswa.nama, tb_matakuliah.nama_matakuliah FROM tb_mahasiswa LEFT JOIN tb_matakuliah ON tb_mahasiswa.mata_kuliah=tb_matakuliah.id SELECT
UNION
tb_mahasiswa.nama, tb_matakuliah.nama_matakuliah FROM tb_mahasiswa RIGHT JOIN tb_matakuliah ON tb_mahasiswa.mata_kuliah=tb_matakuliah.id; SELECT
Hasil perintah query di atas adalah sebagai berikut: nama ADI IKA BUDI (NULL) (NULL) WATI DODI
mata_kuliah Basis Data Basis Data Logika Pemrograman Struktur Data Sistem Informasi Otomata (NULL)
Jika diperlihatkan menggunakan himpunan, hasil query tersebut adalah:
Catatan: Jika nama field yang digunakan pada klausa ON adalah sama pada kedua tabel, maka untuk menghindari penggunaan nama table dan nama field untuk referensi dapat digunakan perintah USING.
13
Tabel tb_mahasiswa id nama id_kuliah 1 ADI 1 2 IKA 1 3 BUDI 2 4 WATI 5 5 DODI NULL Tabel tb_matakuliah Id_kuliah mata_kuliah 1 Basis Data 2 Logika Pemrograman 3 Sistem Informasi 4 Struktur Data 5 Otomata Dari kedua table di atas dapat dilihat bahwa field yang digunakan untuk menghubungkan table-tabel tersebut adalah “id_kuliah”. Karena nama field referensi pada kedua table adalah sama (“id_kuliah”) maka query INNER JOIN pada halaman 2 dapat diubah menjadi: tb_mahasiswa.nama, tb_matakuliah.nama_matakuliah FROM tb_mahasiswa RIGHT JOIN tb_matakuliah ON tb_mahasiswa.mata_kuliah=tb_matakuliah.id; SELECT
14
STRUCTURED QUERY LANGUAGE (SQL) SELECT DENGAN OPERATOR UNION DAN IN 1. UNION MySQL Union adalah statemen yang mengkombinasikan dua buah atau lebih resultset dari multi sql statemen SELECT sehingga menjadi satu buah resultset. Union Statemen memiliki beberapa ketentuan sebagai berikut: a. Jumlah kolom/field dari setiap statemen SELECT harus sama. b. Tipe data kolom/field dari setiap statemen SELECT harus kompatibel. Secara default Statemen UNION akan menghapus semua record duplikat dari resultset. Apabila Anda ingin record duplikat tetap ditampilkan maka pada resultset tuliskan secara explicit UNION ALL.
Format Penulisan: SELECT [nama_kolom1],[nama_kolom2],dst FROM [nama_tabel1] UNION SELECT [nama_kolom1],[nama_kolom2],dst FROM [nama_tabel2]
Contoh: a. tabel_pelanggan id_pelan nama_pelang alamat ggan gan 1 Isaraja Jalan Ponegoro 2 Mikkha Jalan Ponegoro 3 Novita Dewi Jalan Patimura 4 Fatin Jalan Teuku Umar 5 Seena Jalan Hasanudin b. tabel_suplier id_suplier nama_suplier 1 Paloma 2 Gandaria 3 Metro 4 Global 5 MNC
alamat Jalan Angkasa Jalan Patimura Jalan Teuku Umar Jalan Salwana Jalan Werdapura
kota
provinsi
Medan Bandung Badung Surabaya padang
Sumatra Utara Jawa Barat Bali Jawa Timur Sumatra Barat
kota Denpasar Sibolga Bandung Bukittinggi Denpasar
provinsi Bali Sumatra Utara Jawa Barat Sumatra Barat Bali
Query: SELECT kota FROM tabel_pelanggan UNION SELECT kota FROM tabel suplier
15
Output: kota Medan Bandung Badung Surabaya Padang Denpasar Silaboga Bukittinggi 2. IN
Operator IN memungkinkan Anda untuk menentukan beberapa nilai dalam WHERE clause. Format Penulisan: SELECT [nama_kolom1],[nama_kolom2],dst FROM [nama_tabel] WHERE [nama_kolom] IN ([nilai1,[nilai2],…);
Contoh: c. tabel_buku ISBN judul 1111 Dunia Sophie 2222 The Lost Symbol 3333 A Morcking Bird 4444 The Secret 5555 Piano di Tepi Pantai
Jml_hal 765 867 685 687 859
harga 9000 10000 30000 11000 20000
penerbit Mizan Gramedia Gramedia Gramedia Andi
Query: SELECT * FROM tabel_buku WHERE penerbit IN (Gramedia, Andi);
Output: ISBN 2222 3333 4444 5555
judul The Lost Symbol A Morcking Bird The Secret Piano di Tepi Pantai
Jml_hal 867 685 687 859
harga 10000 30000 11000 20000
penerbit Gramedia Gramedia Gramedia Andi
16
STRUCTURED QUERY LANGUAGE (SQL) VIEW
DBMS menyediakan suatu mekanisme untuk „menyembunyikan‟ detil data tertentu yang disebut dengan VIEW. View merupakan sebuah tabel semu / tabel lojik, dimana datanya berasal dari satu atau lebih tabel lain yang disebut sebagai tabel sumber. View dibuat untuk memudahkan pengguna menampilkan data. View dapat dibuat (create), diedit (alter), dan dihapus (drop). Pemanggilan sebuah view dalam perintah SQL sama dengan memanggil tabel fisik, karena view adalah tabel lojik (semu), yang dianggap sama oleh DBMS. adapun perintah-perintah yang berhubungan dengan VIEW, adalah: 1. Membuat VIEW (CREATE) CREATE VIEW v AS Dimana: v adalah nama VIEW adalah ekspresi query yang keluarannnya akan menjadi tabel semu / lojik dari view tersebut, biasanya menggunakan perintah SELECT. Query expression ini bias dari satu tabel fisik atau lebih, dan dapat menangani semua perintah-perintah SQL. 2. Mengedit VIEW (ALTER) ALTER VIEW v AS 3. Menghapus VIEW (DROP) DROP VIEW [IF EXIST] v [,vn ] Dimana: vn adalah nama view lainnya jika ingin dihapus lebih dari satu view 4. Memanggil / menggunakan VIEW dalam SQL Memanggil View sama dengan memanggil tabel fisik dalam MySQL, yaitu menggunakan perintah: SELECT atribut,[atribut] FROM v [WHERE p] Dimana: v adalalah nama view, atribut nama view tersebut sama adalah atribut yang dipanggil perintah yang terdapat dalam view tersebut (query expression).
17
STRUCTURED QUERY LANGUAGE (SQL) STORED PROCEDURE DAN CURSOR 1. Deklarasi Stores Procedure Definisi dari stored procedure adalah suatu bagian dari deklarasi kode SQL yang disimpan di catalog database dan dapat dipanggil/dijalankan oleh suatu program, trigger ataupun stored procedure lainnya. Stored procedure juga dapat memanggil dirinya sendiri (recursive) akan tetapi tidak semua versi MySQL mendukung stored procedure yang recursive. Beberapa keuntungan dari stored procedure adalah: 1. Stored procedure meningkatkan performa aplikasi. Setelah selesai dibuat maka stored procedure akan dikompilasi dan disimpan pada catalog database. Stored procedure berjalan lebih cepat dibandingkan perintah SQL yang belum dikompilasi yang dikirim oleh aplikasi.
2. Stored procedure mengurangi traffic dari database dan aplikasi karena tidak perlu mengirimkan perintah SQL yang belum dikompilasi. Yang perlu dikirimkan oleh aplikasi ke database server adalah nama dari stored procedurenya dan hasilnya akan dikirim balik oleh database server ke aplikasi pemanggil. 3. Stored procedure dapat digunakan berulang-ulang dan transparan terhadap semua aplikasi yang ingin menggunakannya. 4. Stored procedure adalah aman karena database administrator dapat memberikan akses yang sesuai ke tiap stored procedure tanpa memberikan akses langsung ke tabel. Akan tetapi, stored procedure juga memiliki kekurangan sebagai berikut: 1. Stored procedure membuat database server memiliki beban yang tinggi dalam hal memori dan prosesor. 2. Stored procedure hanya mengandung perintah-perintah deklarasi MySQL sehingga sangat sulit untuk menulis prosedur yang kompleks yang dibutuhkan oleh aplikasi. 3. Stored procedure tidak dapat di-debug. Contoh dari stored procedure yang menampilkan semua produk yang ada pada tabel “Produk” adalah sebagai berikut: DELIMITER // CREATE PROCEDURE AmbilSemuaProduk() BEGIN SELECT * FROM Produk; END // DELIMITER;
Perintah DELIMITER digunakan untuk mengubah delimiter standar (“;”) menjadi yang lain, dalam hal ini tanda “//”. Fungsi delimiter ini adalah untuk memisahkan antara satu perintah SQL dengan yang lainnya. Pembuatan stored procedure dimulai dengan perintah CREATE PROCEDURE yang diikuti dengan nama procedure-nya. Isi dari stored procedure berupa
18
perintah-perintah deklarasi SQL diletakkan di antara BEGIN dan END. Stored procedure dapat dijalankan menggunakan perintah: CALL nama_stored_procedure();
Untuk memanggil stored procedure AmbilSemuaProduk dilakukan dengan perintah: CALL AmbilSemuaProduk();
Keluaran dari perintah di atas adalah semua data yang ada pada tabel Produk. 2. Deklarasi Variabel Variabel digunakan pada stored procedure untuk menyimpan hasil secara langsung. Untuk mendeklarasikan suatu variabel perintah yang digunakan adalah: DECLARE nama_variabel tipe_data(size) DEFAULT nilai_default
Mengikuti perintah DECLARE adalah nama dari variabel. Nama variabel ini harus mengikuti konvensi penamaan variabel dan tidak boleh sama dengan nama tabel atau kolom pada database. Selanjutnya adalah tipe data yang dapat berupa semua tipe yang disediakan oleh MySQL seperti INT, VARCHAR, DATETIME dan lain sebagainya. Disamping tipe data juga perlu didefinisikan ukuran (size) dari tipe data tersebut. Ketika mendeklarasikan variabel maka nilai awal dari variabel tersebut adalah NULL. Nilai default NULL ini dapat diubah dengan mendefinisikan perintah DEFAULT pada bagian akhir dari deklarasi varibel tersebut. Sebagai contoh untuk mendeklarasikan variabel total_penjualan dengan tipe data INT yang nilai defaultnya adalah 0 maka digunakan perintah: DECLARE x,y DEFAULT 0;
Setelah melakukan deklarasi variabel maka untuk mengisikan nilai ke variabel tersebut dapat dilakukan menggunakan perintah SET. Sebagai contoh: SET total_penjualan = 10000;
Pengisian nilai variabel ini juga dapat dilakukan menggunakan perintah SELECT … INTO. DECLARE total_penjualan INT DEFAULT 0; SELECT SUM(harga_jual) INTO total_penjualan FROM produk;
Pada contoh di atas variabel total_penjualan dideklarasikan dengan tipe data INT dan nilai default 0, kemudian total_penjualan diisikan nilai yang merupakan penjumlahan dari harga_jual pada tabel produk. Ruang lingkup suatu variabel tergantung dari deklarasinya. Jika variabel tersebut dideklarasikan di dalam stored procedure maka ruang lingkupnya adalah sampai dengan perintah END dari stored procedure itu. Jika dideklarasikan di dalam suatu blok BEGIN/END maka variabel itu ruang lingkupnya hanya di 19
dalam blok tersebut. Suatu variabel yang dimulai dengan tanda „@‟ merupakan variabel session. Variabel tersebut akan ada sampai session-nya berakhir. 3. Parameter Stored procedure juga dapat menerima parameter. Pada MySQL mode dari suatu parameter dapat berupa IN, OUT dan INOUT. 1. IN, merupakan mode default dari parameter pada MySQL. IN mengindikasikan bahwa suatu parameter merupakan masukan bagi stored procedure namun semua perubahan di dalam stored procedure tidak akan mengubah parameter.
2. OUT, mode ini mengindikasikan bahwa stored procedure dapat mengubah nilai dari parameter dan mengirimkan balik ke program yang memanggil. 3. INOUT, mode ini merupakan kombinasi dari IN dan OUT. Sintaks untuk mendefinisikan suatu parameter pada stored procedure adalah: MODE nama_param tipe_param(size_param)
nama_param merupakan nama dari parameter. Nama ini tidak boleh sama dengan nama kolom ataupun tabel dan mengikuti tata cara penamaan variabel. Setelah nama parameter diikuti dengan tipe data dari parameter dan ukurannya. Masing-masing parameter dipisahkan dengan tanda “,” kalau stored procedure tersebut memiliki lebih dari satu parameter. Contoh di bawah adalah untuk menampilkan semua kota pada satu negara: DELIMITER // CREATE PROCEDURE AmbilKotaPerNegara(IN namaNegara VARCHAR(255) BEGIN SELECT namaKota, telpon FROM Kota WHERE Negara = namaNegara; END // DELIMITER;
Seperti dilihat di atas, stored procedure menerima paramtere namaNegara dengan tipe VARCHAR yang panjang maksimumnya adalah 255. Hasil dari stored procedure ini adalah menampilkan semua namaKota dan telpon dari tabel Kota yang negara-nya sama dengan parameter yang diberikan. Sebagai contoh: untuk menampilkan semua kota di Negara Indonesia maka digunakan perintah: CALL AmbilKotaPerNegara(‘INDONESIA’);
Contoh berikut digunakan untuk menampilkan jumlah record penjualan yang status penjualannya merupakan suatu parameter.
20
DELIMITER $$ CREATE PROCEDURE TotalOrder(IN orderStatus VARCHAR(25), OUT total INT) BEGIN SELECT count(orderNumber) INTO total FROM orders WHERE status = orderStatus; END$$ DELIMITER
Misalnya diinginkan untuk mendapatkan jumlah order yang statusnya adalah “Dikapalkan” maka perintahnya adalah: CALL TotalOrder(‘Dikapalkan’,@total);
4. Perulangan dengan Cursor MySQL mendukung penggunaan Cursor pada stored procedure, function dan trigger. Cursor digunakan untuk melakukan iterasi pada himpunan record dari suatu tabel yang dihasilkan oleh suatu query dan melakukan proses untuk masing-masing record tersebut. Cursor pada MySQL memiliki beberapa sifat sebagai berikut: 1. Read Only: artinya cursor tidak dapat diupdate.
2. Non-scrollable: cursor hanya dapat berjalan satu arah, tidak bias dilompati maupun disuruh maju atau mundur pada result set. 3. Asensitive: update pada tabel yang diacu oleh cursor tidak disarankan karena dapat memberikan hasil yang tidak diinginkan. Untuk mendeklarasikan cursor digunakan perintah berikut: DECLARE nama cursor CURSOR FOR perintah select;
Setelah itu cursor harus dibuka menggunakan perintah OPEN: OPEN nama cursor;
Selanjutnya untuk mendapatkan baris berikutnya danmemindahkan cursor ke baris tersebut dilakukan menggunakan perintah FETCH: FETCH nama cursor INTO daftar variabel;
Pada akhirnya ketika baris yang dibaca telah habis maka cursor harus ditutup menggunakan perintah CLOSE: CLOSE nama_cursor;
Satu hal yang perlu diingat ketika menggunakan cursor adalah untuk mendeklarasikan handler NOT FOUND agar tidak terjadi pesan kesalahan ketika tidak ada data. Berikut adalah contoh penggunaan cursor pada stored procedure:
21
DELIMITER $$ DROP PROCEDURE IF EXISTS CursorProc$$ CREATE PROCEDURE CursorProc() BEGIN DECLARE no_more_products, quantity_in_stock INT DEFAULT 0; DECLARE prd_code VARCHAR(255); DECLARE cur_product CURSOR FOR SELECT productCode FROM products; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_products = 1; /* for logging information */ CREATE TABLE infologs ( id int(11) NOT NULL AUTO_INCREMENT, Msg varchar(255) NOT NULL, PRIMARY KEY (id) ); OPEN cur_product; FETCH cur_product INTO prd_code; REPEAT SELECT quantityInStock INTO quantity_in_stock FROM products WHERE productCode = prd_code; IF quantity_in_stock < 100 THEN INSERT INTO infologs(msg) VALUES (prd_code); END IF; FETCH cur_product INTO prd_code; UNTIL no_more_products = 1 END REPEAT; CLOSE cur_product; SELECT * FROM infologs; DROP TABLE infologs; END$$ DELIMITER;
Stored procedure di atas adalah satu contoh sederhana dimana hasil yang sama bias didapatkan melalui query biasa. Pada contoh di atas digunakan suatu cursor pada tabel “products” untuk membaca semua kode produk (“productCode”) yang ada. Setelah itu dilakukan pemeriksaan untuk masing-masing kode produk. Jika jumlah stock yang dimiliki (“quantity_in_stock”) untuk suatu kode produk adalah kurang dari 100 maka kode produk tersebut dicatat pada suatu tabel sementara (“infologs”). Setelah proses looping selesai dilakukan maka hasil dari tabel sementara ini ditampilkan ke layar menggunakan perintah SELECT.
22
STRUCTURED QUERY LANGUAGE (SQL) TRIGGER
Trigger merupakan sekumpulan perintah atau sintaks yang akan secara otomatis dijalankan jika terjadi operasi tertentu dalam tabel atau view. Trigger digunakan untuk memanggil satu atau beberapa perintah SQL secara otomatis sebelum atau sesudah terjadi proses INSERT, UPDATE atau DELETE dari suatu tabel. Sebagai contoh misalnya kita ingin menyimpan id pelanggan secar otomatis ke tabel „log‟ sebelum menghapus data di tabel pelanggan. Adapun jenis perubahan yang dijalankan dalam trigger adalah: a) BEFORE INSERT on TABEL1: menjalankan proses di dalamnya pada saat sebelum melakukan operasi INSERT di TABEL1 b) AFTER INSERT on TABEL1: menjalankan proses di dalamnya pada saat sesudah melakukan operasi INSERT di TABEL1 c) BEFORE UPDATE on TABEL1: menjalankan proses di dalamnya pada saat sebelum melakukan operasi UPDATE di TABEL1 d) AFTER UPDATE on TABEL1: menjalankan proses di dalamnya pada saat sesudah melakukan operasi UPDATE di TABEL1 e) BEFORE DELETE on TABEL1: menjalankan proses di dalamnya pada saat sebelum melakukan operasi DELETE di TABEL1 f) AFTER DELETE on TABEL1: menjalankan proses di dalamnya pada saat sesudah melakukan operasi DELETE di TABEL1 Format Penulisan: DELIMITER $$ CREATE TRIGGER [nama trigger] [BEFORE/AFTER] [INSERT/UPDATE/DELETE] ON [nama tabel] FOR EACH ROW BEGIN [SQL STATEMENT] END$$ DELIMITER;
Refernsi “OLD dan “NEW”
Karena trigger digunakan pada saat terjadi perubahan row data, maka kita perlu referensi ke row sebelum dan sesudah perubahan. Untuk ini ada dua alias yang berfungsi untuk hal tersebut yaitu OLD dan NEW. Sesuai namanya, OLD digunakan untuk referensi sebelum perubahan dan NEW untuk referensi sesudah perubahan. Contoh: Berikut ini contoh trigger yang akan mencatat aktivitas ke tabel log setiap terjadi proses insert ke tabel pelanggan:
23
DELIMITER $$ CREATE TRIGGER after_insert AFTER INSERT ON penjualan FOR EACH ROW BEGIN INSERT INTO log (deskripsi, waktu_jual, kode_user) VALUES('Insert data ke tabel pelanggan', now(), NEW. kode_user); END$$ DELIMITER;
24
HASIL DAN PEMBAHASAN 1. Hasil Percobaan 1. Mengimport database “northwind” ke dalam SQLyog. Langkah-langkahnya sebagai berikut: Buka file northwind.sql kemudian copy query tersebut ke dalam SQL yog. Screenshot:
Setelah semua query pada northwind.sql di copy ke SQL yog, langkah selanjutnya yaitu eksekusi semua query yang telah di copy.
Setelah berhasil dieksekusi, database northwind telah berhasil dibuat Screenshot:
25
2. Analisis Hasil Percobaan
Skema Basis Data Penjualan (Northwind)
26
STUDI KASUS
1. Menampilkan ProductName dan UnitPrice dari produk yang di supply oleh perusahaan yang berawalan G dengan kategori Produce, Seafood atau Dairy Product. View Hanya menampilkan product yang UnitInStock tidak lebih dari 10. CREATE VIEW ProductPrice AS SELECT ProductName,UnitPrice FROM products p INNER JOIN suppliers s ON p.SupplierID=s.SupplierID INNER JOIN categories c ON p.CategoryID=c.CategoryID WHERE s.CompanyName LIKE "G%" AND (c.CategoryName = 'Produce' OR c.CategoryName ='Seafood' OR c.CategoryName ='Dairy Products') AND p.UnitsInStock<11;
Screenshot:
2. Menampilkan Nama category dan jumlah barang (bukan jumlahan dari stock, 1 id barang dihitung 1, berapapun stocknya) di dalam kategori tersebut. Apabila ada kategori yang jumlah barangnya nol, harus tetap ditampilkan. CREATE VIEW CategoryStock AS SELECT CategoryName,COUNT(ProductID) AS stock_barang FROM products p LEFT JOIN categories c ON p.CategoryID=c.CategoryID GROUP BY CategoryName;
Screenshot:
3. Menampilkan namapegawai (FirstName dan LastName menjadi satu kolom), jumlah order yang ditangani dari pegawai yang menangani order terbanyak di urutan ke-5 CREATE VIEW EmployeesOrder AS SELECT CONCAT(FirstName,' ',LastName) AS nama_pegawai, COUNT(OrderID) AS jumlah_order FROM employees e INNER JOIN orders o ON o.EmployeeID=e.EmployeeID GROUP BY e.EmployeeID ORDER BY jumlah_order DESC LIMIT 4,1;
Screenshot:
27
4. Menampilkan data OrderID, ProductName, UnitPrice, Quantity, Discount, SubTotal dari customer yang perusahaan customernya bernama QuickStop dan pegawai dengan last name Fuller dan menggunakan jasa pengiriman selain Speedy Express. Anggap diskon sudah fix(bukan dalam persen lagi). CREATE VIEW DetailCustomer AS SELECT o.OrderID,p.ProductName,od.UnitPrice,od.Quantity,od.D iscount, (od.Quantity*od.UnitPrice(od.Quantity*od.UnitPrice*od.Discount)) AS SubTotal FROM products p INNER JOIN orderdetails od ON p.ProductID=od.ProductID INNER JOIN orders o ON o.OrderID=od.OrderID INNER JOIN employees e ON e.EmployeeID=o.EmployeeID INNER JOIN customers c ON c.CustomerID=o.CustomerID INNER JOIN shippers s ON o.ShipVia=s.ShipperID WHERE c.CompanyName='Quick-Stop' AND e.LastName='Fuller' AND s.CompanyName<>'Speedy Express';
Screenshot:
5. Menampilkan data Nama Pegawai (FirstName dan LastName digabung) dan nama Supervisornya (ReportsTo) dari semua pegawai. CREATE VIEW DetailEmployees AS SELECT CONCAT(FirstName,' ',LastName) AS nama_pegawai, ReportsTo AS Supervisor FROM employees;
Screenshot:
6. Buatlah stored procedure untuk menyisipkan data ke tabel Shippers
28
DELIMITER $$ CREATE PROCEDURE InsertDataShippers(IN CompanyName VARCHAR(40),IN telp VARCHAR(24)) BEGIN INSERT INTO shippers(CompanyName,Phone) VALUES(CompanyName,telp); END $$ DELIMITER; SET @CompanyName='Cv. Gabeng'; SET @telp=92253; CALL InsertDataShippers(@CompanyName,@telp);
Screenshot:
7. Buatlah stored procedure untuk menampilkan data ProductName dan Golongan Harga setiap Product. Dimana field golongan harga ada 3 yaitu: jika harga di bawah 5 berarti murah, 5 sampai 10 termasuk golongan menengah, selain itu mahal.
29
DELIMITER $$ DROP PROCEDURE IF EXISTS GolonganProduct $$ CREATE PROCEDURE GolonganProduct() BEGIN DECLARE flag INT; DECLARE kode INT; DECLARE nama VARCHAR(40); DECLARE harga DECIMAL; DECLARE cur_product CURSOR FOR SELECT ProductID FROM products; DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1; CREATE TABLE temp( NamaProduct VARCHAR(40), golongan VARCHAR(10)); OPEN cur_product; FETCH cur_product INTO kode; REPEAT SELECT ProductName INTO nama FROM products WHERE ProductID=kode; SELECT UnitPrice INTO harga FROM products WHERE ProductID=kode; IF harga<5 THEN INSERT INTO temp VALUES(nama,'murah'); ELSEIF harga BETWEEN 5 AND 10 THEN INSERT INTO temp VALUES(nama,'menengah'); ELSE INSERT INTO temp VALUES(nama,'mahal'); END IF; FETCH cur_product INTO kode; UNTIL flag=1 END REPEAT; CLOSE cur_product; SELECT * FROM temp; DROP TABLE temp; END $$ DELIMITER; CALL GolonganProduct();
30
Screenshot:
8. Buatlah stored procedure untuk menampilkan country dari semua employees. Country yang sama ditampilkan hanya sekali. Cara menampilkan country adalah ditampilkan ke dalam 1 baris, setiap country dipisahkan tanda semicolon(;). Misalnya : USA;UK. DELIMITER $$ DROP PROCEDURE IF EXISTS EmployeesCountry $$ CREATE PROCEDURE EmployeesCountry() BEGIN DECLARE flag INT; DECLARE kode INT; DECLARE negara VARCHAR(15); DECLARE country_employees_temp,country_employees VARCHAR(15); DECLARE cur_employees CURSOR FOR SELECT EmployeeID FROM employees; DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1; SELECT country INTO country_employees_temp FROM employees WHERE EmployeeID=1; OPEN cur_employees; FETCH cur_employees INTO kode; REPEAT SELECT country INTO negara FROM employees WHERE EmployeeID=kode; IF negara<>country_employees_temp THEN SELECT CONCAT(country_employees_temp,';',negara) INTO country_employees; END IF; FETCH cur_employees INTO kode; UNTIL flag=1 END REPEAT; CLOSE cur_employees; SELECT country_employees; END $$ DELIMITER; CALL EmployeesCountry();
31
Screenshot:
9. Buatlah trigger untuk mengurangi nilai stock pada product ketika orderdetails ditambah. DELIMITER$$ CREATE TRIGGER updateStok AFTER INSERT ON orderdetails FOR EACH ROW BEGIN UPDATE products SET UnitsInStock=UnitsInStock-new.Quantity WHERE ProductID=new.ProductID; END$$ DELIMITER ;
Screenshot:
10. Buatlah tabel log_shipper dengan field yang sama dengan shippers. Kemudian buatlah trigger untuk menambahkan data pada log_shipper ketika ada data yang dihapus dari shippers - Membuat tabel log_shipper CREATE TABLE log_shipper ( ShipperID INT(11) PRIMARY KEY, CompanyName VARCHAR(40), Phone VARCHAR(24));
Screenshot:
-
trigger untuk menambahkan data pada log_shipper ketika ada data yang dihapus dari shippers DELIMITER $$ CREATE TRIGGER insert_log
32
AFTER DELETE ON shippers FOR EACH ROW BEGIN INSERT INTO log_shipper VALUES(old.ShipperID,old.CompanyName,old.Phon e); END$$ DELIMITER;
Screenshoot
Data tabel pada tabel shipper delete data
dan log_shipper sebelum terjadi
Selanjutnya data pada tabel shipper akan dilakukan delete data dengan shipperID=4. DELETE FROM shippers WHERE ShipperID=4;
Berikut screenshot dari tabel shipper dan log_shipper setelah ShipperID=4 dihapus
33
KESIMPULAN
Dari pembahasan mengenai Studi kasus di atas dapat kita tarik kesimpulan:
1. Structured Query Language (SQL) sangat berperan penting dalam menyelesaikan suatu permasalahan yang berhubungan dengan Basis Data.
34
