Wednesday, October 19, 2016

Sub Query Dan Indeks

Michael Dohan October 19, 2016

A. SELECT INTO STATEMENT

SELECT INTO adalah sebuah perintah yang ada pada MySQL yang digunakan untuk memindahkan data dari satu tabel ke tabel yang lain atau dari satu database ke database yang lain. SELECT INTO sebenarnya juga dapat memilah field yang mana sajakah yang ingin kita pindahkan dengan cara menyebutkan nama fieldnya. Struktur SQL yang digunakan adalah select * into tabelBaru from tabelLama; dan bila hanya digunakan untuk memindah kolom-kolom tertentu saja maka Syntax yang digunakan adalah select namaKolom1, namaKolom2, namaKolom3 into tabelBaru from tabelLama; Berikut adalah beberapa contoh penggunaan SELECT INTO:

Membuat backup sebuah tabel

SELECT * INTO backup_transaksi FROM transaksi;

Membuat backup sebuah tabel ke dalam database yang baru

SELECT * INTO backup_trsndskdi IN ‘backup_database.mdb’ FROM transaksi;

Membuat backup beberapa kolom sebuah tabel

SELECT ID,NamaPelanggan INTO backup_transaksi FROM transaksi;

B. SUB QUERY

Sub Query adalah query nested atau sebuah query yang ada di dalam query. Sub query merupakan pernyataan SELECT yang merupakan bagian dari pernyataan INSERT, SELECT.. Query dasar dalam sub query :

SELECT namakolom FROM namatabel WHERE namakolom operator (subquery);

Beberapa kegunaan dari Sub Query adalah :

Subquery digunakan untuk menyelesaikan persoalan dimana terdapat suatu nilai yang tidak diketahui (unknown values).
Meng-copy data dari satu tabel ke tabel lain
Menerima data dari inline view
Mengambil data dari tabel lain untuk kemudian di update ke tabel yang dituju
Menghapus baris dari satu tabel berdasarkan baris dari tabel lain

Perintah ini hanya bisa menerima satu buah hasil dari sub query, jika hasil dari sub query ada lebih adari satu maka akan terjadi error. Ada beberapa bahasan terkait penggunaah Sub Query ini antara lain :

Sub query dengan IN

Jika operator ‘=’ hanya digunakan untuk hasil yang tepat satu, maka jika ingin menampilkan yang memiliki hasil lebih dari satu maka menggunakan perintah IN. Dan struktur Query yang digunakan dalam hal ini adalah
SELECT namakolom FROM namatabel WHERE kondisi opeatorperbandingan IN (subquery);

Sub query dengan ALL

Command ALL diikuti dengan operator perbandingan digunakan memiliki arti menampilkan nilai jika perbandingan bernilai benar untuk semua data. Operator perbandingan tersebut berupa ( <, >, =, != ). Berikut adalah query dasar dari sub query all
SELECT namakolom FROM namatabel WHERE kondisi opeatorperbandingan ALL (subquery);

Sub query dengan ANY

Command ANY diikuti dengan operator perbandingan memiliki arti menampilkan nilai yang sesuai dengan apapun yang dihasilkan oleh sub query. ANY berbeda dengan IN, jika IN itu semua data, sedangkan ANY hanya beberapa data. Contoh query dasar dari sub query ANY
SELECT namakolom FROM namatabel WHERE kondisi opeatorperbandingan ANY (subquery);

Sub query dengan EXISTS

Perintah EXISTS disini berguna untuk mengartur penampilan hasil query, Query Utama akan dijalankan jika Sub Query bernilai TRUE (ada hasilnya) jika hasilnya kosong maka Query utama tidak akan dijalankan. Lawan dari statement EXISTS adalah NOT EXISTS. Query yang digunakan adalah
SELECT namakolom FROM namatabel WHERE EXIST / NOT EXIST (subquery);

Insert Into Statement

Untuk meng-copy data dari suatu tabel ke tabel lain kita bisa menggunakan perintah INSERT INTO. Tetapi sebelu kita menggunakan perintah INTO INTO ini kita harus membuat tabel baru yang jumlah field dan urutannya sama dengan tabel field sebelumnya, nama dari field tidak harus sama. Query untuk perintah INSERT INTO adalah sebagai berikut :
INSERT INTO namatabel2 SELECT namakolom FROM namatabel1 ;

Kita juga bisa memberikan kondisi sesuai dengan kondisi yang kita inginkan dengan penambahan perintah WHERE.

C. INDEX

Indexes adalah suatu bagian yang special dari system database yang digunakan untuk peningkatan kemampuan menyeluruh dari proses yangada.MySQL mengijinkan antara 16 sampai 64 indexes untuk setiap table, akan tetapi ini tergantung storage engine yang digunakan.

Setiap index dapat diletakan di field (kolom) mana saja, mulai dari field 1 sampai field 15. Walaupun penggunaan beberapa field (multicolumn) index tidak terlihat nyata, akan tetapi penggunaan ini akan memberikan kemudahan untuk kemampuan pencarian pada sebuah atau banyak field (column).

Pada lain hal, ada sebuah hal yang menjadikan kelemahan dari penggunaan index. Dimana dengan index kita dapat meningkatkan kecepatan dalam membaca data dari database, tetapi akan membuat lambat proses perubahan data dalam database, karena perubahan akan berpengaruh pada data yang di index. Anda akan mendapat hasil yang normal ketika hanya menampilkan data dari database jika dibandingkan anda melakukan proses tambah (insert) data atau ubah (update) data yang sudah ada.

Pengguna index, baik digunakan pada field (column) :

Ketika sering menggunakan WHERE pada bagian query.
Ketika sering menggunakan ORDER BY pada bagian query.
Ketika memiliki banyak perbedaan nilai.
Ketika sering menggunakan JOIN pada bagian query.

MySQL memiliki beberapa tipe dari Index yang berbeda, yaitu :

INDEX
UNIQUE
PRIMARY KEY.
FULLTEXT index

Contoh Penggunaan Macam Index

Indentifikasi suatu field menggunakan PRIMARY KEY
Indentifikasi suatu field harus UNIQUE

UNIQUE tidak dapat digunakan dalam jumlah banyak. Sebagian besar digunakan seperti nilai tanggal, angka, nama, kota, kode pos yang berulang, terutama sekali pada table dengan ribuan baris (record). Tetapi semua tergantung pada aplikasi yang anda buat, apakah anda hanya menggunakan sekali-kali pada field yang benar-benar harus unik, seperti alamat email, username dari sistem aplikasi registrasi (sistem login). Anda tidak dapat mendefinisikan sekaligus PRIMARY KEY dan UNIQUE dalam sebuah field, karena PRIMARY KEY sudah didesain harus mengikut aturan unik.

Identifikasi suatu field akan mendapatkan keuntungan dari sebuah INDEX

Pada rekomendasi ada adalah ketika index akan membantu dan berfikir tentang apa informasi akan disajikan. Jika anda memerlukan untuk melihat daftar dari invoices berdasarkan tanggal atau total jumlah order, maka INDEX akan membuat logical indexes. Jika sebuah table registrasi/login akan menggunakan kombinasi pada username dan password untuk verifikasi suatu proses login, maka akan dibuat index. Anda juga membuat index pada field foreign key anda.

Berikut adalah struktur SQL untuk membuat indeks pada suatu kolom
CREATE INDEX namaindex ON namatabel (nama kolom);

Sedangkan Unique index mirip dengan penggunaan indeks tetapi lebih digunakan untuk mencegah duplikasi data yang terdapat dalam tabel. Jadi dengan adanya unique index berarti pembaca tidak dapat meng-insert data yang sama dalam sebuah tabel. Berikut struktur SQL nya :
CREATE UNIQUE INDEX namaindex ON namatabel (nama kolom);

Untuk menghapus index berikut strukturnya :
DROP INDEX namaindex;

D. KOLOM UNIK

Unique berfungsi untuk menjaga agar tidak terjadinya duplikasi nilai (kesamaan data) dalam sebuah kolom, hal ini dapat ditangani dengan membuat sebuah indeks unik atau fungsi unik sendiri pada kolom yang dimaksud. Unique biasanya digunakan dalam pembuatan bukan primary key namun butuh untuk mengecek dupikasi agar tidak ada yang sama, karena dalam primary key sudah otomatis mempunyai sifat unik. Berikut Struktur SQL saat pembuatan tabel baru :
CREATE TABLE namatabel (namakolom tipedata unique);

Ketika tabel sudah ada kita bisa menggunakan ALTER TABLE. Hal ini sesuai dengan yang telah dipelajari pada bab sebelumnya. Berikut Struktur SQL
ALTER TABLE nama_tabel ADD UNIQUE (nama_kolom);
Untuk menghapus unique berikut caranya :
ALTER TABLE nama_table DROP CONSTRAINT NAMA_CONSTRAIN;

E. CHECK

Check berfungsi untuk melakukan pembatasan nilai masukan dalam sebuah kolom, sebagai contoh misalkan kita ingin agar kolom nomor telepon yang biasanya terdiri dari 12 karakter ini dapat kita setting dengan menggunakan CHECK. Dengan menggunakan CHECK maka sebuah kolom hanya bisa diisi dengan data yang memenuhi kriteria dalam CHECK. Berikut query contoh pengunaan check
db_contoh=> CREATE TABLE pelanggan (
db_contoh(> nama varchar(35),
db_contoh(> kode_area CHAR(10) CHECK (length(trim(kode_area)) = 2),
db_contoh(> umur INTEGER CHECK (umur >= 0),
db_contoh(> gender CHAR(1) CHECK (gender IN (‘L’, ‘P’)),
db_contoh(> ttl DATE CHECK (ttl BETWEEN ‘1998-01-01’ AND CURRENT_DATE),
db_contoh(> CHECK (upper(trim(nama)) != ‘nita’ OR
db_contoh(> upper(trim(nama)) != ‘jeki’)
db_contoh(> );
CREATE

F. PENGGUNAAN TRIM

Menghilangkan beberapa karakter di MySQL terkadang sangat kita perlukan. Terutama dalam mengupdate data dalam bentuk karakter/teks yang bertipe varchar, text dan sebangsanya. Contoh simplenya misalnya menghilangkan tanda tertentu seperti menghilangkan tanda koma di belakang kata “Rabu” pada ” Senin,Selasa,Rabu,”. Jika ada masalah seperti ini, kita dapat membersihkannya dengan menggunakan fungsi TRIM. Fungsi TRIM dibagi menjadi 3, yaitu TRIM, LTRIM dan RTRIM. Ketiga fungsi ini memiliki bentuk penggunaan sebagai berikut :

RTRIM : digunakan untuk membersihkan spasi kosong yang ada di bagian kanan (Right) String.
LTRIM : digunakan untuk membersihkan spasi kosong yang ada di bagian kiri (Left) String.
TRIM : digunakan untuk membersihkan spasi kosong yang ada di bagian kiri, kanan, maupun tengah String

Berikut Struktur SQL penggunaan trim :
Select trim(nama_kolom) from nama_tabel;

Ketika kita menggunakan fungsi TRIM kita memiliki tiga opsi yang bisa digunakan untuk menghilangkan atau menghapus suatu karakter string. Jadi fungsi TRIM tidak hanya bisa menghapus spasi saja tetapi kita bisa gunakan untuk menghapus karakter string suatu data. Opsinya sebagai berikut :

LEADING : merupakan opsi untuk menghilangkan karakter terpilih yang ada di sebelah kiri. Parameter Leading diartikan sebagai sufik dari karakter yang ada.
TRAILING : merupakan opsi untuk menghilangkan karakter terpilih yang ada di sebelah kanan String. Parameter Trailing diartikan sebagai sufik dari karakter yang ada.
BOTH : merupakan opsi yang dapat menangani parameter Leading maupun Trailing

Berikut Struktur SQL nya :
Select trim(LEADING ‘karakter, misal : -’ from namakolom) from nama_tabel;
Select trim(TRAILING ‘karakter, misal : -’ from namakolom) from nama_tabel;
Select trim(BOTH ‘karakter, misal : -’ from namakolom) from nama_tabel;

HASIL PRAKTIKUM

Menampilkan nama fakultas dan jumlah mahasiswa dengan ketentuan nama fakultas yang dimunculkan dengan jumlah mahasiswanya yang terkecil.
Untuk menyelesaikan soal ini, kita bisa menggunakan order by dan limit yang telah dijelaskan pada bab sebelumnya. Strukutur Query dan hasilnya adalah sebagai berikut
Menampilkan nama mahasiswa, nama fakultas, alamat dengan syarat nama fakultas sama dengan Edi (bisa disesuaikan) dan alamatnya tidak sama dengan luki (bisa disesuaikan). Dalam hal ini kita menggunakan sub query dengan operator in, Struktur dan Hasilnya adalah sebagai berikut
Membuat index di tabel mahasiswa(alamat). Kemudian buat lagi index yang bersifat unik pada tabel fakultas(fak_nama) kemudian amati perbedaannya ketika memasukkan data yang sama, Struktur dan Hasilnya adalah

Kita coba untuk insert-kan data yang sama (kecuali nim mahasiswa) pada tabel mahasiswa dan hasilnya

After that, kita buat lagi unique index pada tabel fakultas, seperti pada gambar berikut

Then, kita insert data yang sama terkecuali pada id fakultas
Menjadikan kolom nama di mahasiswa menjadi unik dan inputkan 2 data yang sama. Kemudian amati perbedaannya.
Untuk membuat kolom menjadi unik, kita bisa menjalankan perintahalter table, Hasilnya adalah

After That, kita insert-kan data nama mahasiswa yang sama

maka, apabila ada data yang sama, maka kita tidak dapat meng-insertkan data lagi
Memindahkan data dari tabel mahasiswa, fakultas ambil kolom nim, nama mahasiswa, alamat, nama fakultas ke tabel baru yang dinamai “tabel identitas”.
Untuk memindahkan data dari tabel mahasiswa dan fakultas ke tabel identitas, kita perlu membuat terlebih dahulu tabel identitas, dengan field nim, nama, alamat mahasiswa dan nama fakultas. Setelah itu kita bisa menggunakan perintah insert into statement. Hasilnya apabila sukses akan sama seperti pada gambar ;
Buatlah contoh penggunaan check pada sub bab pembahasan CHECK. Kemudian masukkan beberapa data baik yang sesuai dengan criteria check maupun yang bukan dan amati perbedaannya. Dalam mysql operator check tidak ada.
Inputkan data di tabel mahasiswa dimana pada kolom nama sebelum inputkan karakter dahulukan dengan spasi dan di akhiri dengan tanda “+” seperti berikut : “ andi cahyono++++”. kemudian munculkan seluruh data dan hilangkan spasi didepan.
Gunakan perintah TRIM untuk menyelesaikannya :
Munculkan data mahasiswa dengan hilangkan karakter “+” di akhir data dan karakter “a” di awal kata pada kolom nama. Dalam hal ini, kita akan menggunakan leading, trailing dan both