- Menampilkan Data MySQL (SELECT)
Mempersiapkan Tabel Contoh: mata_kuliah dan daftar_dosen
Pembahasan query SELECT memiliki banyak variasi yang membuatnya memiliki banyak fitur, maka akan membuat 2 buah tabel dengan nama mata_kuliah dan daftar_dosen. Tabel ini juga akan digunakan untuk beberapa tutorial selanjutnya.
Berikut adalah query yang digunakan untuk merancang tabel mata_kuliah dan daftar_dosen:
mysql> SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema || mahasiswa || mysql || performance_schema || test |+--------------------+5 rows in set (0.13 sec)mysql> USE mahasiswa;Database changedmysql> CREATE TABLE daftar_dosen (NIP CHAR(10) PRIMARY KEY,nama_dosen VARCHAR(50) NOT NULL, no_hp CHAR(13),alamat VARCHAR(100));Query OK, 0 rows affected (0.10 sec)mysql> DESC daftar_dosen;+------------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+--------------+------+-----+---------+-------+| NIP | char(10) | NO | PRI | NULL | || nama_dosen | varchar(50) | NO | | NULL | || no_hp | char(13) | YES | | NULL | || alamat | varchar(100) | YES | | NULL | |+------------+--------------+------+-----+---------+-------+4 rows in set (0.08 sec)mysql> CREATE TABLE mata_kuliah (kode_matkul CHAR(6) PRIMARY KEY,nama_matkul VARCHAR(50) NOT NULL,jumlah_SKS TINYINT UNSIGNED DEFAULT '2',semester TINYINT,NIP_dosen CHAR(10));Query OK, 0 rows affected (0.04 sec)mysql> DESC mata_kuliah;+-------------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------------+---------------------+------+-----+---------+-------+| kode_matkul | char(6) | NO | PRI | NULL | || nama_matkul | varchar(50) | NO | | NULL | || jumlah_SKS | tinyint(3) unsigned | YES | | 2 | || semester | tinyint(4) | YES | | NULL | || NIP_dosen | char(10) | YES | | NULL | |+-------------+---------------------+------+-----+---------+-------+5 rows in set (0.00 sec)
Berikutnya adalah mengisi tabel tersebut dengan data sample. Dalam contoh ini menggunakan data sample seperti pada tabel dibawah ini:
Data input untuk tabel daftar_dosen:
| NIP | Nama Dosen | No HP | Alamat |
|---|---|---|---|
| 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru |
| 0260432002 | Maya Ari Putri | 0812345234 | Palembang |
| 0275430005 | Susi Indriani | 0812656532 | Bogor |
| 0480432066 | Tia Santrini | 0812451177 | Padang |
| 0576431001 | M. Siddiq | 0812979005 | Jakarta |
| 0770435006 | Rubin Hadi | 0812567678 | Papua |
| 0869437003 | Mustalifah | 0812338877 | Aceh |
| 1080432007 | Arif Budiman | 0812456345 | Makasar |
Data input untuk tabel mata_kuliah:
| Kode Mata Kuliah | Nama Matakuliah | Jumlah SKS | Semester | Nip Dosen |
|---|---|---|---|---|
| MATDAS | Matematika Dasar | 4 | 1 | 0160436012 |
| FISDAS | Fisika Dasar | 2 | 1 | 0480432066 |
| TEKKOM | Teknik Kompilasi | 2 | 6 | 0480432066 |
| JARKOM | Jaringan Komputer | 3 | 3 | 0770435006 |
| DTBASE | Database | 4 | 4 | 0275430005 |
| SISOPR | Sistem Operasi | 2 | 4 | 0160436012 |
| MIKROP | Mikro Prosesor | 2 | 5 | 0480432066 |
Format Dasar Penulisan query SELECT
Pada saat ingin menampilkan data, biasanya dibutuh 3 hal:
- Apa saja kolom yang ingin ditampilkan.
- Nama tabel yang akan ditampilkan.
- Kondisi untuk menampilkan data.
Query SELECT pada dasarnya juga terdiri dari 3 hal tersebut. Berikut adalah format dasar penulisan query SELECT MySQL:
SELECT apa_yang_akan_ditampilkan FROM tabel_apaWHERE kondisi_apa_data_ditampilkan; |
MySQL memiliki banyak opsi yang dapat digunakan, berikut adalah format dasar query SELECT yang saya ambil dari manual MySQL:
SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]] |
Seperti yang terlihat, MySQL menyediakan banyak sekali perintah opsional yang dapat dipilih untuk menampilkan data dari database.
Cara Menampilkan Seluruh Isi Tabel MySQL
Format dasar query select untuk menampilkan seluruh isi tabel adalah sebagai berikut:
SELECT * FROM nama_tabel |
Tanda bintang (*) adalah wildcard sebagai pengganti ‘pilih semua kolom‘.
Sebagai contoh, berikut adalah query untuk menampilkan seluruh isi tabel daftar_dosen :
mysql> SELECT * FROM daftar_dosen;+------------+----------------+------------+-----------+| NIP | nama_dosen | no_hp | alamat |+------------+----------------+------------+-----------+| 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru || 0260432002 | Maya Ari Putri | 0812345234 | Palembang || 0275430005 | Susi Indriani | 0812656532 | Bogor || 0480432066 | Tia Santrini | 0812451177 | Padang || 0576431001 | M. Siddiq | 0812979005 | Jakarta || 0770435006 | Rubin Hadi | 0812567678 | Papua || 0869437003 | Mustalifah | 0812338877 | Aceh || 1080432007 | Arif Budiman | 0812456345 | Makasar |+------------+----------------+------------+-----------+8 rows in set (0.00 sec)
Cara Menampilkan Kolom Tertentu dari Tabel MySQL (SELECT … FROM)
Jika ingin menampilkan hanya sebagian kolom saja dari dalam tabel, dapat menentukan kolom mana saja yang akan ditampilkan.
Format dasarnya adalah sebagai berikut:
SELECT nama_kolom1, nama_kolom2,... FROM nama_tabel |
nama_kolom1 dan nama_kolom2 adalah nama kolom yang ingin ditampilkan. Misalnya ingin menampilkan kolom nama_dosen dan alamat dari tabel daftar_dosen, maka querynya adalah sebagai berikut:
mysql> SELECT nama_dosen, alamat FROM daftar_dosen;+----------------+-----------+| nama_dosen | alamat |+----------------+-----------+| Sabrina Sari | Pekanbaru || Maya Ari Putri | Palembang || Susi Indriani | Bogor || Tia Santrini | Padang || M. Siddiq | Jakarta || Rubin Hadi | Papua || Mustalifah | Aceh || Arif Budiman | Makasar |+----------------+-----------+8 rows in set (0.05 sec)
Jika ingin menampilkan hanya data yang memenuhi kriteria tertentu saja, maka dapat dilakukan dengan menambah perintah WHERE pada query SELECT.
Kondisi WHERE pada perintah SELECT dipakai untuk menyeleksi data yang diinginkan saja, sedangkan data yang tidak memenuhi kriteria tidak akan ditampilkan.
Format dasar query SELECT…WHERE adalah:
SELECT nama_kolom1, nama_kolom2,... FROM nama_tabel WHERE kondisi
Untuk menampilkan data dosen yang beralamat di Padang, bisa menggunakan query berikut:
mysql> SELECT NIP,nama_dosen FROM daftar_dosen WHERE alamat='Padang';+------------+--------------+| NIP | nama_dosen |+------------+--------------+| 0480432066 | Tia Santrini |+------------+--------------+1 row in set (0.00 sec)
Kondisi WHERE sangat fleksibel dan bisa menggunakan berbagai operasi kondisi seperti lebih besar (>), lebih kecil (<), tidak sama (<>), dan lain-lain.List lengkap dari penggunaan kondisi yang dapat digunakan pada SELECT…WHERE adalah:
Operasi Aritmatika
Operator Penjelasan + Penambahan – Pengurangan * Pengalian / Pembagian % Sisa hasil bagi (modulus)
Operasi Logika
Operator Penjelasan NOT atau ! Logika bukan AND atau && Logika dan OR atau || Logika atau XOR Logika bukan atau (XOR)
Operasi Perbandingan
Operator Penjelasan = Sama dengan <> atau != Tidak sama dengan <=> sama dengan (null safe) < kurang dari <= kurang dari atau sama dengan > lebih besar dari >= lebih besar atau sama dengan BETWEEN Berada pada batas tertentu IN Berada di dalam IS NULL Pengecekan apakah berisi NULL IS NOT NULL Pengecekan apakah bukan berisi NULL LIKE Pencarian menggunakan wildcard REGEXP atau RLIKE Pencarian menggunakan Regular Expression
Jika ingin menampilkan seluruh mata kuliah yang diajarkan di semester 4 ke atas, maka querynya adalah:mysql> SELECT * FROM mata_kuliah WHERE semester >= 4;+-------------+------------------+------------+----------+------------+| kode_matkul | nama_matkul | jumlah_SKS | semester | NIP_dosen |+-------------+------------------+------------+----------+------------+| DTBASE | Database | 4 | 4 | 0275430005 || MIKROP | Mikro Prosesor | 2 | 5 | 0480432066 || SISOPR | Sistem Operasi | 2 | 4 | 0160436012 || TEKKOM | Teknik Kompilasi | 2 | 6 | 0480432066 |+-------------+------------------+------------+----------+------------+4 rows in set (0.00 sec)
Penggabungan beberapa kondisi juga dapat dilakukan, misalnya ingin menampilkan seluruh mata kuliah dengan jumlah SKS=2 dan diajarkan pada semester dibawah 5, maka querynya:
mysql> SELECT * FROM mata_kuliah WHERE jumlah_SKS=2 AND semester < 5;+-------------+----------------+------------+----------+------------+| kode_matkul | nama_matkul | jumlah_SKS | semester | NIP_dosen |+-------------+----------------+------------+----------+------------+| FISDAS | Fisika Dasar | 2 | 1 | 0480432066 || SISOPR | Sistem Operasi | 2 | 4 | 0160436012 |+-------------+----------------+------------+----------+------------+2 rows in set (0.07 sec)Cara Mengurutkan hasil tampilan data MySQL (SELECT…ORDER BY)
MySQL menyediakan perintah opsional ORDER BY untuk mengurutkan data yang di hasilkan. Query dasar untuk SELECT…ORDER BY adalah:
SELECT nama_kolom1,... FROM nama_tabel WHERE kondisi ORDER BY nama_kolom_urut |
nama_kolom_urut adalah kolom yang akan diurutkan. Pengurutan bisa dari paling kecil ke besar, ataupun besar ke kecil.
Pilihan ini dapat diatur dengan penambahan instruksi ASC (singkatan dari ascending) untuk pengurutan dari kecil ke besar, dan DESC (singkatan dari descending) untuk urutan dari besar ke kecil. Jika tidak di dijelaskan, secara default bawaan MySQL perintah ORDER BY akan memakai ASC.
Jika ingin menampilkan mata kuliah dengan jumlah SKS lebih dari 2 secara berurutan dari yang paling kecil, querynya adalah:
mysql> SELECT * FROM mata_kuliah WHERE jumlah_SKS > 2 ORDER BY jumlah_SKS;+-------------+-------------------+------------+----------+------------+| kode_matkul | nama_matkul | jumlah_SKS | semester | NIP_dosen |+-------------+-------------------+------------+----------+------------+| JARKOM | Jaringan Komputer | 3 | 3 | 0770435006 || DTBASE | Database | 4 | 4 | 0275430005 || MATDAS | Matematika Dasar | 4 | 1 | 0160436012 |+-------------+-------------------+------------+----------+------------+3 rows in set (0.06 sec)
Jika ingin menampilkan seluruh dosen pada tabel daftar_dosen dan diurutan kolom alamat secara abjad, maka querynya adalah sebagai berikut:
mysql> SELECT * FROM daftar_dosen ORDER BY alamat ASC;+------------+----------------+------------+-----------+| NIP | nama_dosen | no_hp | alamat |+------------+----------------+------------+-----------+| 0869437003 | Mustalifah | 0812338877 | Aceh || 0275430005 | Susi Indriani | 0812656532 | Bogor || 0576431001 | M. Siddiq | 0812979005 | Jakarta || 1080432007 | Arif Budiman | 0812456345 | Makasar || 0480432066 | Tia Santrini | 0812451177 | Padang || 0260432002 | Maya Ari Putri | 0812345234 | Palembang || 0770435006 | Rubin Hadi | 0812567678 | Papua || 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru |+------------+----------------+------------+-----------+8 rows in set (0.00 sec)
Cara Membatasi Hasil query SELECT (SELECT…LIMIT)
MySQL menyediakan pilihan opsional LIMIT untuk membatasi hasil query SELECT. Format dasar query SELECT…LIMIT adalah sebagai berikut:
SELECT nama_kolom1 FROM nama_tabel WHERE kondisi LIMIT baris_awal, jumlah_baris
Dimana baris_awal adalah awal nomor baris yang ingin ditampilkan, dan jumlah_baris adalah jumlah baris yang diurutkan dari baris_awal. Nomor baris pada MySQL diawali dengan nomor 0.
Untuk menampilkan data 3 nama dosen paling atas yang dirutkan berdasarkan nama, maka querynya adalah:
mysql> SELECT * FROM daftar_dosen ORDER BY nama_dosen ASC LIMIT 0,3;+------------+----------------+------------+-----------+| NIP | nama_dosen | no_hp | alamat |+------------+----------------+------------+-----------+| 1080432007 | Arif Budiman | 0812456345 | Makasar || 0576431001 | M. Siddiq | 0812979005 | Jakarta || 0260432002 | Maya Ari Putri | 0812345234 | Palembang |+------------+----------------+------------+-----------+3 rows in set (0.00 sec)
Jika hanya menulis 1 angka saja di belakang instruksi LIMIT, maka MySQL menganggap bahwa angka dihitung dari baris teratas (baris ke 0). Contohnya jika kita ingin menampilkan 5 baris teratas mata kuliah yang diurutkan berdasarkan nama, maka querynya:mysql> SELECT * FROM mata_kuliah ORDER BY nama_matkul LIMIT 5;+-------------+-------------------+------------+----------+------------+| kode_matkul | nama_matkul | jumlah_SKS | semester | NIP_dosen |+-------------+-------------------+------------+----------+------------+| DTBASE | Database | 4 | 4 | 0275430005 || FISDAS | Fisika Dasar | 2 | 1 | 0480432066 || JARKOM | Jaringan Komputer | 3 | 3 | 0770435006 || MATDAS | Matematika Dasar | 4 | 1 | 0160436012 || MIKROP | Mikro Prosesor | 2 | 5 | 0480432066 |+-------------+-------------------+------------+----------+------------+5 rows in set (0.00 sec)
- Pencarian Data MySQL (Query LIKE)
mysql> SELECT * FROM daftar_dosen;+------------+----------------+------------+-----------+| NIP | nama_dosen | no_hp | alamat |+------------+----------------+------------+-----------+| 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru || 0260432002 | Maya Ari Putri | 0812345234 | Palembang || 0275430005 | Susi Indriani | 0812656532 | Bogor || 0480432066 | Tia Santrini | 0812451177 | Padang || 0576431001 | M. Siddiq | 0812979005 | Jakarta || 0770435006 | Rubin Hadi | 0812567678 | Papua || 0869437003 | Mustalifah | 0812338877 | Aceh || 1080432007 | Arif Budiman | 0812456345 | Makasar |+------------+----------------+------------+-----------+8 rows in set (0.266 sec)Pencarian Data Tabel MySQL Menggunakan query SELECT..LIKE
MySQL menyediakan query SELECT..LIKE untuk menampilkan tabel berdasarkan pencarian karakter sederhana. Format dasar struktur SELECT..LIKE adalah sebagai berikut:
SELECT nama_kolom_tampil FROM nama_tabel WHERE nama_kolom_cari LIKE keyword_pencarian
- nama_kolom_tampil adalah nama dari kolom yang akan ditampilkan, bisa semua kolom dalam tabel, atau hanya kolom tertentu saja.
- nama_tabel adalah nama tabel dimana nama_kolom_tampil berada.
- nama_kolom_cari adalah kolom yang akan digunakan untuk pencarian.
- keyword_pencarian merupakan kata kunci yang digunakan untuk pencarian.
Sebagai contoh awal, misalkan ingin menampilkan seluruh kolom dari tabel daftar_dosen dimana nama dosen adalah Rubin Hadi. Maka querynya adalah:
mysql> SELECT * FROM daftar_dosen WHERE nama_dosen LIKE 'Rubin Hadi';+------------+------------+------------+--------+| NIP | nama_dosen | no_hp | alamat |+------------+------------+------------+--------+| 0770435006 | Rubin Hadi | 0812567678 | Papua |+------------+------------+------------+--------+1 row in set (0.04 sec)
Dalam query ini, menggunakan LIKE dengan kata kunci yang sama persis dengan isi dari kolom nama_dosen.
Query LIKE ini tidak terlalu membantu karena bisa saja mengganti query tersebut dengan operator = seperti contoh berikut:
mysql> SELECT * FROM daftar_dosen WHERE nama_dosen='Rubin Hadi';+------------+------------+------------+--------+| NIP | nama_dosen | no_hp | alamat |+------------+------------+------------+--------+| 0770435006 | Rubin Hadi | 0812567678 | Papua |+------------+------------+------------+--------+1 row in set (0.00 sec)
Menampilkan seluruh kolom dari tabel daftar_dosen dimana nama_dosen diawali dengan huruf ‘S’. Untuk pencarian seperti inilah query LIKE lebih bermanfaat.
MySQL menyediakan 2 karakter khusus untuk pencarian LIKE, yaitu karakter _ dan %. Berikut penjelasannya:
_ : karakter ganti yang cocok untuk satu karakter apa saja.% : karakter ganti yang cocok untuk karakter apa saja dengan panjang karakter tidak terbatas, termasuk tidak ada karakter.
Contoh untuk pencarian nama_dosen yang diawali dengan huruf ‘S’:
mysql> SELECT * FROM daftar_dosen WHERE nama_dosen LIKE 'S%';+------------+---------------+------------+-----------+| NIP | nama_dosen | no_hp | alamat |+------------+---------------+------------+-----------+| 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru || 0275430005 | Susi Indriani | 0812656532 | Bogor |+------------+---------------+------------+-----------+2 rows in set (0.00 sec)
Menggunakan ‘S%’ karena kita ingin mencari nama_dosen yang diawal dengan S, dan diikuti oleh karakter apa saja dengan panjang tidak dibatasi.
Mengganti kata kunci hasil pencarian tersebut dengan karakter lain, sebagai contoh:
- ‘S%’ : Cocok dengan kata yang diawali dengan S, dan diikuti dengan karakter apa saja, contoh: ‘S’, ‘Sa’, ‘Si’, ‘Saaaaaa’, ‘Susi’, dan ‘Sabrina Sari’.
- ‘S_’: Cocok dengan kata yang diawali dengan S, dan diikuti dengan satu karakter apa saja, contoh: ‘Si’, ‘Sa’, ‘Su’, ‘Se’, dan ‘St’.
- ‘A__i’: Cocok dengan kata yang diawali dengan ‘A’, diikuti oleh 2 karakter bebas, namun diakhiri dengan i, contoh: ‘Andi’, ‘ardi’, ‘aaai’.
- ‘%e’: Cocok dengan seluruh kata dengan panjang berapapun yang diakhiri dengan huruf ‘e’, contoh: ‘Kece’, ‘Kue’, dan ‘mie’.
- ‘%dia%’: Cocok dengan seluruh kata yang mengandung kata ‘dia’, contoh: ‘media’, ‘kemudian’, ‘dia’, dan ‘diaaaa’.
Operasi LIKE juga bisa digabung dengan operator logika OR atau AND untuk pencarian yang lebih kompleks.
Misalkan ingin mencari kolom nama_dosen yang diakhiri huruf ‘i’ atau kolom alamat yang diawali dengan huruf ‘m’. Querynya adalah sebagai berikut:
mysql> SELECT * FROM daftar_dosen WHERE nama_dosen LIKE '%i'OR alamat LIKE 'm%';+------------+----------------+------------+-----------+| NIP | nama_dosen | no_hp | alamat |+------------+----------------+------------+-----------+| 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru || 0260432002 | Maya Ari Putri | 0812345234 | Palembang || 0275430005 | Susi Indriani | 0812656532 | Bogor || 0480432066 | Tia Santrini | 0812451177 | Padang || 0770435006 | Rubin Hadi | 0812567678 | Papua || 1080432007 | Arif Budiman | 0812456345 | Makasar |+------------+----------------+------------+-----------+6 rows in set (0.00 sec)
Perhatikan bahwa seluruh nama_dosen diakhiri dengan huruf ‘i’, kecuali Arif Budiman, dimana hasil ini didapat dari kata kunci kedua, yakni atau alamat diawali dengan huruf ‘m’ : Makasar.- Pencarian Data Regular Expression
mysql> SELECT * FROM daftar_dosen;+------------+----------------+------------+-----------+| NIP | nama_dosen | no_hp | alamat |+------------+----------------+------------+-----------+| 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru || 0260432002 | Maya Ari Putri | 0812345234 | Palembang || 0275430005 | Susi Indriani | 0812656532 | Bogor || 0480432066 | Tia Santrini | 0812451177 | Padang || 0576431001 | M. Siddiq | 0812979005 | Jakarta || 0770435006 | Rubin Hadi | 0812567678 | Papua || 0869437003 | Mustalifah | 0812338877 | Aceh || 1080432007 | Arif Budiman | 0812456345 | Makasar |+------------+----------------+------------+-----------+8 rows in set (0.266 sec)
Pengertian Regular Expression
Dalam bahasa sederhana, Regular Expression adalah kumpulan huruf atau karakter yang digunakan untuk pencocokan pola (pattern matching). Pola disini contohnya ‘pola untuk kata yang diawali dengan huruf a dan diakhiri dengan huruf j’, atau yang sedikit rumit seperti ‘pola untuk kata yang diawali huruf a,b, c dengan panjang maksimal 5 huruf, mengandung minimal sebuah angka’.
Cakupan Regular Expression cukup luas dan tidak terbatas hanya pada MySQL. Hampir semua bahasa pemrograman komputer menyediakan fungsi khusus untuk regular expression.
Jika pada query SELECT..LIKE menggunakan pola ‘s%’ sebagai kata kunci yang berarti ‘kata yang diawali dengan huruf s dan memiliki banyak huruf 1 atau lebih’ dalam regular expression, penulisannya menjadi ‘^S.*’. Terlihat sedikit rumit, tapi mari kita pelajari aturan penulisan RegExp:
- . : tanda titik dalam RegExp berarti sebuah karakter apa saja
- [ … ]: tanda kurung siku ini berarti kumpulan karakter. Misalkan [abc] akan cocok dengan ‘a’, ‘b’, atau ‘c’. kita bisa juga menggunakan jangkauan (range), contohnya [a-z] akan cocok dengan seluruh huruf, [0-9] akan cocok dengan seluruh angka.
- *: tanda bintang ini akan cocok dengan 0 atau lebih karakter sebelumnya. Misalkan ‘a*’ berarti akan cocok dengan seluruh kata yang mengandung 0 atau lebih a.
- ^: tanda pangkat atau topi ini menandakan berada di awal kata.
- $: tanda dollar ini berarti bahwa pola berada di akhir kata.
Berikut contoh penerapan dari pola RegExp ini:
- ‘ab*’: Pola ini berarti akan cocok dengan seluruh kata yang mengandung a dan diikuti oleh b atau tidak sama sekali. Contohnya: ‘a’, ‘ab’, ‘abbbbbb’, dan juga ‘kebab’, karena untuk RegExp, harus menyatakan dimana karakter itu muncul.
- ‘^ab*’: Pola ini sama artinya dengan ‘ab*’ seperti diatas, namun tanda ^ menyatakan bahwa pola ini harus berada di awal kata, sehingga ‘kebab’ tidak akan cocok.
- ‘^s..i$’: Pola ini akan cocok dengan seluruh kata yang diawali dengan s, dan diakhiri dengan i, terdiri dari 4 huruf. Contohnya: susi, sapi, dan siti.
Pencarian Data Tabel MySQL Menggunakan SELECT..REGEXP
Format dasar dari query pencarian menggunakan Regular Expression adalah:
SELECT nama_kolom_tampil FROM nama_tabel WHERE nama_kolom_cari REGEXP keyword_reguler_expression
- nama_kolom_tampil adalah nama dari kolom yang akan ditampilkan, bisa semua kolom dalam tabel, atau hanya kolom tertentu saja.
- nama_tabel adalah nama tabel dimana nama_kolom_tampil berada.
- nama_kolom_cari adalah kolom yang akan kita gunakan untuk pencarian.
- keyword_regular_expression adalah kata kunci dalam bentuk regular expression yang digunakan untuk pencarian.
Sebagai perbandingan dengan query SELECT..LIKE dengan Regular Expression pencarian nama_dosen yang diawali dengan huruf ‘s’ adalah sebagai berikut:
mysql> SELECT * FROM daftar_dosen WHERE nama_dosen REGEXP '^s.*';+------------+---------------+------------+-----------+| NIP | nama_dosen | no_hp | alamat |+------------+---------------+------------+-----------+| 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru || 0275430005 | Susi Indriani | 0812656532 | Bogor |+------------+---------------+------------+-----------+2 rows in set (0.00 sec)
Penggunaan Regular Expression diperlukan jika dibutuh pencarian yang lebih rumit. Contohnya jika ingin mencari nama_dosen yang diawali dengan huruf ‘m’ atau ’s’, dan diakhiri dengan huruf vocal.
Pencarian seperti ini bisa saja dilakukan dengan SELECT..LIKE, namun butuh operator OR yang cukup banyak. Karena untuk huruf vocal berarti nama_dosen dapat berakhir dengan huruf a, i, u, e dan o.
Dalam Regular Expression, pola huruf vokal tersebut akan berbentuk ‘^[ms].*[aiueo]$‘ seperti contoh berikut:
mysql> SELECT * FROM daftar_dosen WHERE nama_dosen REGEXP '^[ms].*[aiueo]$';+------------+----------------+------------+-----------+| NIP | nama_dosen | no_hp | alamat |+------------+----------------+------------+-----------+| 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru || 0260432002 | Maya Ari Putri | 0812345234 | Palembang || 0275430005 | Susi Indriani | 0812656532 | Bogor |+------------+----------------+------------+-----------+3 rows in set (0.00 sec)
Perhatikan bahwa dosen Mustalifah dan M. Siddiq walaupun berawalan M, namun tidak diakhiri dengan huruf vocal.- Menggabungkan Tabel (INNER JOIN)
Tabel terdiri dari tabel mata_kuliah dan tabel daftar_dosen.
Isi tabel daftar_dosen:
| NIP | Nama Dosen | No HP | Alamat |
|---|---|---|---|
| 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru |
| 0260432002 | Maya Ari Putri | 0812345234 | Palembang |
| 0275430005 | Susi Indriani | 0812656532 | Bogor |
| 0480432066 | Tia Santrini | 0812451177 | Padang |
| 0576431001 | M. Siddiq | 0812979005 | Jakarta |
| 0770435006 | Rubin Hadi | 0812567678 | Papua |
| 0869437003 | Mustalifah | 0812338877 | Aceh |
| 1080432007 | Arif Budiman | 0812456345 | Makasar |
Isi tabel mata_kuliah:
| Kode Mata Kuliah | Nama Matakuliah | Jumlah SKS | Semester | Nip Dosen |
|---|---|---|---|---|
| MATDAS | Matematika Dasar | 4 | 1 | 0160436012 |
| FISDAS | Fisika Dasar | 2 | 1 | 0480432066 |
| TEKKOM | Teknik Kompilasi | 2 | 6 | 0480432066 |
| JARKOM | Jaringan Komputer | 3 | 3 | 0770435006 |
| DTBASE | Database | 4 | 4 | 0275430005 |
| SISOPR | Sistem Operasi | 2 | 4 | 0160436012 |
| MIKROP | Mikro Prosesor | 2 | 5 | 0480432066 |
Tabel daftar_dosen berisi data dari dosen, dimana kolom NIP berisi NIP dosen. Pada tabel mata_kuliah, berisi daftar mata kuliah.
Yang perlu diperhatikan adalah kolom NIP dari kedua tabel. Kolom NIP inilah yang akan dipakai untuk menggabungkan kedua tabel.
Cara Menggabungkan Tabel MySQL dengan INNER JOIN
Tujuan dari menggabungkan tabel adalah untuk menyajikan informasi secara lebih detail.
Contohnya dari tabel daftar_dosen dan tabel mata_kuliah diatas. Ingin menyajikan informasi mata kuliah sekaligus nama dosen yang mengajar mata kuliah tersebut. Data yang ingin disajikan adalah nama mata kuliah, jumlah SKS, dan nama dosen yang mengajar.
Query INNER JOIN memiliki beberapa variasi, yaitu SELECT..INNER JOIN..ON dan SELECT..INNER JOIN..USING. Kita akan membahasnya satu persatu.
Cara Menggabungkan Tabel MySQL dengan query SELECT..INNER JOIN..ON
Format dasar dari penulisan query SELECT..INNER JOIN..ON adalah:
SELECT nama_kolom_tampil FROM nama_tabel_pertama INNER JOIN nama_tabel_kedua ON nama_kolom_join_tabel_pertama = nama_kolom_join_tabel_kedua |
- nama_kolom_tampil adalah nama dari kolom yang akan ditampilkan, bisa semua kolom dalam tabel, atau hanya kolom tertentu saja.
- nama_tabel_pertama adalah nama tabel pertama yang akan digabung.
- nama_tabel_kedua adalah nama tabel kedua yang akan digabung.
- nama_kolom_join_tabel_pertama adalah nama kolom yang akan digunakan sebagai join dari tabel pertama.
- nama_kolom_join_tabel_kedua adalah nama kolom yang akan digunakan sebagai join dari tabel kedua.
Syarat untuk INNER JOIN adalah kedua tabel harus memiliki sebuah kolom dengan nilai yang sama. Kolom tersebut yang akan digunakan dalam proses JOIN.
Pada contoh, kolom itu adalah kolom NIP dari tabel nama_dosen, dan kolom NIP_dosen dari tabel mata_kuliah. Perhatikan walaupun keduanya berisi NIP, namun nama kolomnya berbeda.
Query untuk SELECT..INNER JOIN..ON tabel mata_kuliah dengan daftar_dosen adalah:
mysql> SELECT nama_matkul,jumlah_SKS,nama_dosen FROM mata_kuliahINNER JOIN daftar_dosen ON NIP_dosen=NIP;+-------------------+------------+---------------+| nama_matkul | jumlah_SKS | nama_dosen |+-------------------+------------+---------------+| Database | 4 | Susi Indriani || Fisika Dasar | 2 | Tia Santrini || Jaringan Komputer | 3 | Rubin Hadi || Matematika Dasar | 4 | Sabrina Sari || Mikro Prosesor | 2 | Tia Santrini || Sistem Operasi | 2 | Sabrina Sari || Teknik Kompilasi | 2 | Tia Santrini |+-------------------+------------+---------------+7 rows in set (0.00 sec)
Dari hasil query SELECT diatas, menggabung tabel mata_kuliah dan daftar_dosen untuk mendapatkan tampilan seluruh mata kuliah, jumlah sks, dan nama dosen yang mengajar.
Menggunakan tabel mata_kuliah sebagai tabel pertama, dan tabel daftar_dosen sebagai tabel kedua.
Cara Menggabungkan Tabel MySQL dengan SELECT..INNER JOIN..USING
Cara JOIN kedua adalah menggunakan USING sebagai pengganti ON untuk query INNER JOIN.
Format dasar dari penulisan query SELECT..INNER JOIN..USING adalah:
SELECT nama_kolom_tampil FROM nama_tabel_pertama INNER JOINnama_tabel_kedua USING (nama_kolom_join) |
- nama_kolom_tampil adalah nama dari kolom yang akan ditampilkan, bisa semua kolom dalam tabel, atau hanya kolom tertentu saja.
- nama_tabel_pertama adalah nama tabel pertama yang akan digabung.
- nama_tabel_kedua adalah nama tabel kedua yang akan digabung.
- nama_kolom_join adalah nama kolom yang akan digunakan sebagai join.
Syarat untuk INNER JOIN..USING adalah kedua tabel harus memiliki nama kolom yang sama. Dalam contoh , kolom tersebut adalah kolom NIP.
Namun karena nama kolom NIP untuk tabel mata_kuliah adalah NIP_dosen, sedangkan pada kolom daftar_dosen kolom NIP hanya NIP saja, maka harus disamakan terlebih dahulu. Untuk hal ini saya memutuskan kedua kolom akan bernama NIP_dosen.
mysql> ALTER TABLE daftar_dosen CHANGE NIP NIP_dosen CHAR(10);Query OK, 8 rows affected (0.08 sec)Records: 8 Duplicates: 0 Warnings: 0 |
Setelah kedua kolom NIP bernama sama, saatnya menggabungkan kedua tabel ini
mysql> SELECT nama_matkul, jumlah_SKS, nama_dosenFROM mata_kuliah INNER JOIN daftar_dosen USING (NIP_dosen);+-------------------+------------+---------------+| nama_matkul | jumlah_SKS | nama_dosen |+-------------------+------------+---------------+| Database | 4 | Susi Indriani || Fisika Dasar | 2 | Tia Santrini || Jaringan Komputer | 3 | Rubin Hadi || Matematika Dasar | 4 | Sabrina Sari || Mikro Prosesor | 2 | Tia Santrini || Sistem Operasi | 2 | Sabrina Sari || Teknik Kompilasi | 2 | Tia Santrini |+-------------------+------------+---------------+7 rows in set (0.00 sec)
Hasilnya sama persis dengan SELECT..INNER JOIN..ON, hanya berbeda cara penulisan.
Di dalam MySQL, kata INNER JOIN dapat diganti dengan CROSS JOIN, atau hanya JOIN saja. Ketiga kata kunci ini memiliki fungsi yang sama. MySQL menyediakannya agar dapat kompatible dengan bahasa SQL dari RDBMS lainnya seperti ORACLE dan MS SQL.
Misalnya untuk query diatas, dapat juga ditulis menjadi
mysql> SELECT nama_matkul, jumlah_SKS, nama_dosenFROM mata_kuliah CROSS JOIN daftar_dosen USING (NIP_dosen);
Cara Penulisan Nama Kolom: Nama_Tabel.Nama_Kolom
Setelah merubah nama kolom NIP menjadi NIP_dosen pada tabel daftar_dosen, sekarang tiap tabel memiliki nama kolom yang sama, yakni NIP_dosen.
Jika dijalankan kembali query SELECT..INNER JOIN..ON dengan nama kolom ini, MySQL akan mengeluarkan error:
mysql> SELECT nama_matkul,jumlah_SKS,nama_dosen FROM mata_kuliah INNER JOIN daftar_dosen ON NIP_dosen=NIP_dosen;ERROR 1052 (23000): Column 'NIP_dosen' in on clause is ambiguous
Dari pesan error yang terjadi, MySQL ‘bingung’ dalam memilih kolom. Query ON NIP_dosen=NIP_dosen menjadi ambigu karena kedua tabel memiliki nama kolom yang sama.
Untuk mengatasi ambiguitas ini, MySQL mengharuskan secara eksplisit menyebutkan nama tabelnya. Cara penulisan ini menggunakan tanda titik sebagai pemisah:
Nama_Tabel.Nama_Kolom
Misalkan untuk merujuk pada kolom NIP_dosen pada tabel mata_kuliah, maka penulisannya menjadi:
Mata_kuliah.NIP_dosen
Sehingga query SELECT..INNER JOIN..ON yang benar adalah:
mysql> SELECT nama_matkul,jumlah_SKS,nama_dosen FROM mata_kuliahINNER JOIN daftar_dosen ON mata_kuliah.NIP_dosen=daftar_dosen.NIP_dosen;+-------------------+------------+---------------+| nama_matkul | jumlah_SKS | nama_dosen |+-------------------+------------+---------------+| Database | 4 | Susi Indriani || Fisika Dasar | 2 | Tia Santrini || Jaringan Komputer | 3 | Rubin Hadi || Matematika Dasar | 4 | Sabrina Sari || Mikro Prosesor | 2 | Tia Santrini || Sistem Operasi | 2 | Sabrina Sari || Teknik Kompilasi | 2 | Tia Santrini |+-------------------+------------+---------------+7 rows in set (0.00 sec)- Menghapus Baris (DELETE)
Isi tabel daftar_dosen:
| NIP | Nama Dosen | No HP | Alamat |
|---|---|---|---|
| 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru |
| 0260432002 | Maya Ari Putri | 0812345234 | Palembang |
| 0275430005 | Susi Indriani | 0812656532 | Bogor |
| 0480432066 | Tia Santrini | 0812451177 | Padang |
| 0576431001 | M. Siddiq | 0812979005 | Jakarta |
| 0770435006 | Rubin Hadi | 0812567678 | Papua |
| 0869437003 | Mustalifah | 0812338877 | Aceh |
| 1080432007 | Arif Budiman | 0812456345 | Makasar |
Cara Menghapus Record dengan Query DELETE
Untuk penghapusan baris (atau disebut juga dengan record) dalam MySQL, memerlukan beberapa syarat, yaitu nama tabel dimana baris tersebut berada serta kondisi untuk penghapusan.
Format dasar penulisan query DELETE adalah sebagai berikut:
DELETE FROM nama_tabel WHERE kondisi |
nama_tabeladalah nama dari tabel yang record/barisnya akan dihapus.kondisiadalah kondisi baris yang akan dihapus, misalnya hapus kolomnama_dosen = "Sabrina Sari".
Contoh menghapus record/baris untuk nama dosen Sabrina Sari maka querynya adalah:
mysql> DELETE from daftar_dosen WHERE nama_dosen = 'Sabrina Sari';Query OK, 1 row affected (1.73 sec)
Kunci dari query DELETE ada di pernyataan kondisi setelah WHERE, yaitu dimana memberitahukan MySQL syarat/kondisi yang harus dipenuhi untuk menghapus suatu record.
Kondisi WHERE ini juga mendukung logika OR atau AND untuk proses penghapusan yang lebih kompleks, seperti contoh berikut:
mysql> DELETE from daftar_dosen WHERE nama_dosen = 'Maya Ari Putri'OR nama_dosen='Tia Santrini';Query OK, 2 rows affected (0.15 sec)mysql> SELECT * FROM daftar_dosen;+------------+---------------+------------+---------+| NIP_dosen | nama_dosen | no_hp | alamat |+------------+---------------+------------+---------+| 0275430005 | Susi Indriani | 0812656532 | Bogor || 0576431001 | M. Siddiq | 0812979005 | Jakarta || 0770435006 | Rubin Hadi | 0812567678 | Papua || 0869437003 | Mustalifah | 0812338877 | Aceh || 1080432007 | Arif Budiman | 0812456345 | Makasar |+------------+---------------+------------+---------+5 rows in set (0.06 sec)
Dengan tambahan logika OR seperti dalam query diatas, satu perintah DELETE bisa dipakai untuk menghapus 2 buah baris/record sekaligus.
Dalam manual MySQL, format penulisan lengkap dari query DELETE adalah sebagai berikut :
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[WHERE where_condition][ORDER BY ...][LIMIT row_count] |
Penggunaan ORDER BY dan LIMIT sesaat lagi.
Cara Menghapus Record dengan Query DELETE..ORDER BY..LIMIT
MySQL menyediakan kondisi tambahan dengan perintah opsional ORDER BY dan LIMIT.
Perintah ORDER BY dan LIMIT ini sama dengan ORDER BY dipakai untuk mengurutkan data, sedangkan LIMIT untuk membatasi hasil record.
Perintah opsional ORDER BY dan LIMIT disediakan untuk perintah penghapusan yang lebih spesifik, seperti: hapus 10 baris terakhir dari tabel dimulai dari tanggal transaksi paling awal. Hal ini biasa dilakukan tabel sudah melebihi batas maksimum record.
Sebagai contoh untuk tabel daftar_dosen, yang ingin “menghapus 3 baris terakhir nama dosen”.
Untuk keperluan ini, tabel daftar_dosen harus diurutkan berdasarkan kolom nama_dosen secara terbalik, lalu hapus 3 record paling awal.
mysql> DELETE FROM daftar_dosen;Query OK, 5 rows affected (0.04 sec)mysql> LOAD DATA INFILE 'D:\\MySQL\\daftar_dosen.txt' -> INTO TABLE daftar_dosen -> FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';Query OK, 8 rows affected (0.04 sec)Records: 8 Deleted: 0 Skipped: 0 Warnings: 0mysql> SELECT * FROM daftar_dosen ORDER BY nama_dosen;+------------+----------------+------------+-----------+| NIP_dosen | nama_dosen | no_hp | alamat |+------------+----------------+------------+-----------+| 1080432007 | Arif Budiman | 0812456345 | Makasar || 0576431001 | M. Siddiq | 0812979005 | Jakarta || 0260432002 | Maya Ari Putri | 0812345234 | Palembang || 0869437003 | Mustalifah | 0812338877 | Aceh || 0770435006 | Rubin Hadi | 0812567678 | Papua || 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru || 0275430005 | Susi Indriani | 0812656532 | Bogor || 0480432066 | Tia Santrini | 0812451177 | Padang |+------------+----------------+------------+-----------+8 rows in set (0.00 sec)mysql> DELETE from daftar_dosen ORDER BY nama_dosen DESC LIMIT 3;Query OK, 3 rows affected (0.04 sec)mysql> SELECT * FROM daftar_dosen ORDER BY nama_dosen;+------------+----------------+------------+-----------+| NIP_dosen | nama_dosen | no_hp | alamat |+------------+----------------+------------+-----------+| 1080432007 | Arif Budiman | 0812456345 | Makasar || 0576431001 | M. Siddiq | 0812979005 | Jakarta || 0260432002 | Maya Ari Putri | 0812345234 | Palembang || 0869437003 | Mustalifah | 0812338877 | Aceh || 0770435006 | Rubin Hadi | 0812567678 | Papua |+------------+----------------+------------+-----------+5 rows in set (0.00 sec)Karena tabel daftar_dosen hanya tinggal 5 record akibat praktek sebelumnya, maka akan menghapus seluruh record dengan query DELETE FROM daftar_dosen, lalu menginputnya kembali memakai query LOAD DATA INFILE.
Proses penghapusan dosen memakai perintah DELETE from daftar_dosen ORDER BY nama_dosen DESC LIMIT 3.
Dengan membandingkan hasil SELECT setelah dan sebelum query DELETE, bisa dilihat bahwa 3 baris terakhir telah terhapus dari tabel.
Cara Menghapus Seluruh Isi dari Tabel
Untuk keperluan penghapusan seluruh isi dari tabel, cukup jalankan query DELETE tanpa menulis kondisi WHERE. Misalkan untuk menghapus keseluruhan tabel daftar_dosen, querynya adalah:
mysql> DELETE FROM daftar_dosen;Query OK, 5 rows affected (0.04 sec)Dari query diatas, MySQL juga menampilkan jumlah record/baris yang telah dihapus.
Namun jika informasi mengenai jumlah baris ini tidak dibutuhkan, MySQL menyediakan query TRUNCATE seperti contoh berikut:
mysql> TRUNCATE TABLE daftar_dosen;Query OK, 0 rows affected (0.04 sec)mysql> SELECT * FROM daftar_dosen;Empty set (0.00 sec)Secara internal, TRUNCATE dijalankan dengan cara menghapus tabel menggunakan query DROP, lalu membuat ulang tabel itu kembali.
Cara ini akan lebih cepat daripada perintah DELETE yang akan menghapus baris satu per satu. Namun perbedaan kecepatan eksekusi baru terasa jika tabel tersebut memiliki jumlah record yang sangat banyak.
- Update Tabel (UPDATE)
Mempersiapkan Tabel Sample: daftar_dosen
Isi tabel daftar_dosen:
| NIP | Nama Dosen | No HP | Alamat |
|---|---|---|---|
| 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru |
| 0260432002 | Maya Ari Putri | 0812345234 | Palembang |
| 0275430005 | Susi Indriani | 0812656532 | Bogor |
| 0480432066 | Tia Santrini | 0812451177 | Padang |
| 0576431001 | M. Siddiq | 0812979005 | Jakarta |
| 0770435006 | Rubin Hadi | 0812567678 | Papua |
| 0869437003 | Mustalifah | 0812338877 | Aceh |
| 1080432007 | Arif Budiman | 0812456345 | Makasar |
Cara Mengubah Data Tabel Menggunakan Query UPDATE
Query UPDATE dipakai untuk melakukan perubahan data pada tabel MySQL, yakni proses update baris atau record. Format dasar query UPDATE adalah sebagai berikut:
UPDATE nama_tabel SET nama_kolom = data_baru WHERE kondisi |
nama_tabeladalah nama dari tabel yang record/barisnya akan diperbaharui (update).nama_kolomadalah nama kolom dari tabel yang akan diupdate.data_baruadalah nilai data yang akan diinput sebagai nilai baru dari kolomkondisiadalah kondisi atau syarat dari baris yang akan diubah, misalnya jika kolom nama dosen= Sabrina Sari maka lakukan update.
Sebagai contoh, jika ingin mengubah no HP milik dosen Sabrina Sari menjadi 085298710065 dari tabel daftar_dosen, maka querynya adalah sebagai berikut:
mysql> SELECT * FROM daftar_dosen WHERE nama_dosen='Sabrina Sari';+------------+--------------+------------+-----------+| NIP_dosen | nama_dosen | no_hp | alamat |+------------+--------------+------------+-----------+| 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru |+------------+--------------+------------+-----------+1 row in set (0.08 sec)mysql> UPDATE daftar_dosen SET no_hp ='085298710065'WHERE nama_dosen='Sabrina Sari';Query OK, 1 row affected (0.07 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> SELECT * FROM daftar_dosen WHERE nama_dosen='Sabrina Sari';+------------+--------------+--------------+-----------+| NIP_dosen | nama_dosen | no_hp | alamat |+------------+--------------+--------------+-----------+| 0160436012 | Sabrina Sari | 085298710065 | Pekanbaru |+------------+--------------+--------------+-----------+1 row in set (0.00 sec)
Dari query diatas dapat dilihat bahwa kolom no_hp untuk nama dosen Sabrina Sari telah diupdate menjadi nomor baru.
Cara Mengupdate Lebih dari 1 Baris
Untuk query yang lebih rumit, bisa merubah beberapa kolom sekaligus. Syarat untuk kondisi juga dapat menggunakan operator logika seperti OR atau AND sekaligus:
mysql> SELECT * FROM daftar_dosen;+------------+----------------+------------+-----------+| NIP_dosen | nama_dosen | no_hp | alamat |+------------+----------------+------------+-----------+| 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru || 0260432002 | Maya Ari Putri | 0812345234 | Palembang || 0275430005 | Susi Indriani | 0812656532 | Bogor || 0480432066 | Tia Santrini | 0812451177 | Padang || 0576431001 | M. Siddiq | 0812979005 | Jakarta || 0770435006 | Rubin Hadi | 0812567678 | Papua || 0869437003 | Mustalifah | 0812338877 | Aceh || 1080432007 | Arif Budiman | 0812456345 | Makasar |+------------+----------------+------------+-----------+8 rows in set (0.00 sec) mysql> UPDATE daftar_dosen SET alamat ='Surabaya' WHEREnama_dosen='Rubin Hadi' OR NIP_dosen='1080432007'OR no_hp='0812345234';Query OK, 3 rows affected (0.16 sec)Rows matched: 3 Changed: 3 Warnings: 0 mysql> SELECT * FROM daftar_dosen;+------------+----------------+------------+-----------+| NIP_dosen | nama_dosen | no_hp | alamat |+------------+----------------+------------+-----------+| 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru || 0260432002 | Maya Ari Putri | 0812345234 | Surabaya || 0275430005 | Susi Indriani | 0812656532 | Bogor || 0480432066 | Tia Santrini | 0812451177 | Padang || 0576431001 | M. Siddiq | 0812979005 | Jakarta || 0770435006 | Rubin Hadi | 0812567678 | Surabaya || 0869437003 | Mustalifah | 0812338877 | Aceh || 1080432007 | Arif Budiman | 0812456345 | Surabaya |+------------+----------------+------------+-----------+8 rows in set (0.00 sec)
Dari query diatas terlihat bahwa 3 baris/record telah berubah alamatnya menjadi Surabaya. Dalam satu statement UPDATE, kita membuat 3 buah logika OR.
Dalam manual MySQL, format penulisan lengkap dari query UPDATE adalah :
UPDATE [LOW_PRIORITY] [IGNORE] table_referenceSET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...[WHERE where_condition][ORDER BY ...][LIMIT row_count]
Cara Mengupdate Record dengan Query UPDATE..ORDER BY..LIMIT
Sama seperti query DELETE, query UPDATE juga memiliki perintah opsional ORDER BY..LIMIT untuk pemrosesan tingkat lanjut. Perintah ORDER BY..LIMIT bisa dipakai untuk membatasi perintah UPDATE dengan batas tertentu.
Misalkan ingin mengubah alamat dari 5 dosen pertama yang diurutkan berdasarkan nama, maka querynya:
mysql> SELECT * FROM daftar_dosen ORDER BY nama_dosen;+------------+----------------+------------+-----------+| NIP_dosen | nama_dosen | no_hp | alamat |+------------+----------------+------------+-----------+| 1080432007 | Arif Budiman | 0812456345 | Surabaya || 0576431001 | M. Siddiq | 0812979005 | Jakarta || 0260432002 | Maya Ari Putri | 0812345234 | Surabaya || 0869437003 | Mustalifah | 0812338877 | Aceh || 0770435006 | Rubin Hadi | 0812567678 | Surabaya || 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru || 0275430005 | Susi Indriani | 0812656532 | Bogor || 0480432066 | Tia Santrini | 0812451177 | Padang |+------------+----------------+------------+-----------+8 rows in set (0.00 sec)mysql> UPDATE daftar_dosen SET alamat ='Bali' ORDER BY nama_dosen LIMIT 5;Query OK, 5 rows affected (0.09 sec)Rows matched: 5 Changed: 5 Warnings: 0mysql> SELECT * FROM daftar_dosen ORDER BY nama_dosen;+------------+----------------+------------+-----------+| NIP_dosen | nama_dosen | no_hp | alamat |+------------+----------------+------------+-----------+| 1080432007 | Arif Budiman | 0812456345 | Bali || 0576431001 | M. Siddiq | 0812979005 | Bali || 0260432002 | Maya Ari Putri | 0812345234 | Bali || 0869437003 | Mustalifah | 0812338877 | Bali || 0770435006 | Rubin Hadi | 0812567678 | Bali || 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru || 0275430005 | Susi Indriani | 0812656532 | Bogor || 0480432066 | Tia Santrini | 0812451177 | Padang |+------------+----------------+------------+-----------+8 rows in set (0.06 sec)
Dari hasil query tersebut kita bisa melihat 5 dosen awal alamatnya telah berubah menjadi Bali.
Cara Mengupdate Seluruh Kolom dari Tabel MySQL
Jika tidak hati-hati dan lupa memberikan kondisi pada perintah WHERE, maka query UPDATE akan merubah seluruh kolom dari tabel tersebut. Berikut contoh :
mysql> SELECT * FROM daftar_dosen;+------------+----------------+------------+-----------+| NIP_dosen | nama_dosen | no_hp | alamat |+------------+----------------+------------+-----------+| 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru || 0260432002 | Maya Ari Putri | 0812345234 | Bali || 0275430005 | Susi Indriani | 0812656532 | Bogor || 0480432066 | Tia Santrini | 0812451177 | Padang || 0576431001 | M. Siddiq | 0812979005 | Bali || 0770435006 | Rubin Hadi | 0812567678 | Bali || 0869437003 | Mustalifah | 0812338877 | Bali || 1080432007 | Arif Budiman | 0812456345 | Bali |+------------+----------------+------------+-----------+8 rows in set (0.00 sec) mysql> UPDATE daftar_dosen SET no_hp='085278790005';Query OK, 8 rows affected (0.06 sec)Rows matched: 8 Changed: 8 Warnings: 0 mysql> SELECT * FROM daftar_dosen;+------------+----------------+--------------+-----------+| NIP_dosen | nama_dosen | no_hp | alamat |+------------+----------------+--------------+-----------+| 0160436012 | Sabrina Sari | 085278790005 | Pekanbaru || 0260432002 | Maya Ari Putri | 085278790005 | Bali || 0275430005 | Susi Indriani | 085278790005 | Bogor || 0480432066 | Tia Santrini | 085278790005 | Padang || 0576431001 | M. Siddiq | 085278790005 | Bali || 0770435006 | Rubin Hadi | 085278790005 | Bali || 0869437003 | Mustalifah | 085278790005 | Bali || 1080432007 | Arif Budiman | 085278790005 | Bali |+------------+----------------+--------------+-----------+8 rows in set (0.00 sec)
Dapat dilihat dengan mengeliminasi kondisi WHERE, seluruh kolom tabel akan ter-update. Terkadang hasil seperti ini memang diharapkan. Namun seperti contoh di atas, kesalahan dalam membuat logika WHERE bisa berdampak fatal terhadap keseluruhan tabel.- Menggunakan Alias (AS)
Pengertian ALIAS (AS) dalam MySQL
MySQL menyediakan perintah tambahan AS untuk mengganti sementara nama tabel atau kolom. Disebut sementara karena pada dasarnya nama tabel tersebut tidak berubah, hanya di ganti pada saat ditampilkan dengan query SELECT.
Di dalam bahasa SQL, query AS ini lebih dikenal sebagai alias dari nama tabel yang sebenarnya. Alias ditujukan untuk mempermudah penulisan query atau mempercantik tampilan hasil query.
Sebagai tabel contoh, akan memakai tabel daftar_dosen dan mata_kuliah seperti tutorial sebelumnya. Berikut isi dari kedua tabel:
Isi tabel daftar_dosen:
| NIP | Nama Dosen | No HP | Alamat |
|---|---|---|---|
| 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru |
| 0260432002 | Maya Ari Putri | 0812345234 | Palembang |
| 0275430005 | Susi Indriani | 0812656532 | Bogor |
| 0480432066 | Tia Santrini | 0812451177 | Padang |
| 0576431001 | M. Siddiq | 0812979005 | Jakarta |
| 0770435006 | Rubin Hadi | 0812567678 | Papua |
| 0869437003 | Mustalifah | 0812338877 | Aceh |
| 1080432007 | Arif Budiman | 0812456345 | Makasar |
Isi tabel mata_kuliah:
| Kode Mata Kuliah | Nama Matakuliah | Jumlah SKS | Semester | Nip Dosen |
|---|---|---|---|---|
| MATDAS | Matematika Dasar | 4 | 1 | 0160436012 |
| FISDAS | Fisika Dasar | 2 | 1 | 0480432066 |
| TEKKOM | Teknik Kompilasi | 2 | 6 | 0480432066 |
| JARKOM | Jaringan Komputer | 3 | 3 | 0770435006 |
| DTBASE | Database | 4 | 4 | 0275430005 |
| SISOPR | Sistem Operasi | 2 | 4 | 0160436012 |
| MIKROP | Mikro Prosesor | 2 | 5 | 0480432066 |
Cara Menggunakan ALIAS untuk Mengganti Nama Tabel
Perintah alias dengan query AS bukan query SQL yang dapat berdiri sendiri seperti SELECT, UPDATE, maupun DELETE. Perintah AS dipakai sebagai penambah untuk query SQL lain.
Berikut adalah format dasar penulisan alias tabel:
…nama_tabel_asli AS nama_tabel_alias… |
nama_tabel_asliadalah nama tabel sesungguhnya yang dipakai pada saat pembuatan tabel. Contohnya adalah tabel daftar_dosen dan mata_kuliah.nama_tabel_aliasadalah alias atau nama lain tabel yang ingin dipakai, misalnya tabel daftar_dosen di-aliaskan menjadi dosen saja.
Contoh sederhana penggunaan tabel alias adalah sebagai berikut:
mysql> SELECT ds.nama_dosen, ds.no_hp, ds.alamat FROMdaftar_dosen AS ds;+----------------+------------+-----------+| nama_dosen | no_hp | alamat |+----------------+------------+-----------+| Sabrina Sari | 0812349900 | Pekanbaru || Maya Ari Putri | 0812345234 | Palembang || Susi Indriani | 0812656532 | Bogor || Tia Santrini | 0812451177 | Padang || M. Siddiq | 0812979005 | Jakarta || Rubin Hadi | 0812567678 | Papua || Mustalifah | 0812338877 | Aceh || Arif Budiman | 0812456345 | Makasar |+----------------+------------+-----------+8 rows in set (0.00 sec)
Dalam query SELECT di atas akan menampilkan kolom nama_dosen, no_hp dan alamat dari tabel daftar_dosen.
Perhatikan di akhir query terdapat tambahan perintah daftar_dosen AS ds, dalam perintah inilah nama tabel daftar_dosen dialiaskan menjadi ds. Lalu dengan format penulisan nama_tabel.nama_kolom, querynya menjadi SELECT ds.nama_dosen, ds.no_hp, ds.alamat
Query diatas tidak terlalu berguna karena bisa menggunakannya tanpa alias seperti berikut ini:
mysql> SELECT nama_dosen, no_hp, alamat FROM daftar_dosen; +----------------+------------+-----------+| nama_dosen | no_hp | alamat |+----------------+------------+-----------+| Sabrina Sari | 0812349900 | Pekanbaru || Maya Ari Putri | 0812345234 | Palembang || Susi Indriani | 0812656532 | Bogor || Tia Santrini | 0812451177 | Padang || M. Siddiq | 0812979005 | Jakarta || Rubin Hadi | 0812567678 | Papua || Mustalifah | 0812338877 | Aceh || Arif Budiman | 0812456345 | Makasar |+----------------+------------+-----------+8 rows in set (0.00 sec)
Tapi setidaknya itulah cara penulisan alias sederhana.
Perintah AS akan lebih berguna untuk query yang sedikit rumit seperti INNER JOIN.
Berikut adalah query sebelum menggunakan alias:
mysql> SELECT nama_matkul,jumlah_SKS,nama_dosen FROM mata_kuliahINNER JOIN daftar_dosen ON mata_kuliah.NIP_dosen=daftar_dosen.NIP_dosen; +-------------------+------------+---------------+| nama_matkul | jumlah_SKS | nama_dosen |+-------------------+------------+---------------+| Database | 4 | Susi Indriani || Fisika Dasar | 2 | Tia Santrini || Jaringan Komputer | 3 | Rubin Hadi || Matematika Dasar | 4 | Sabrina Sari || Mikro Prosesor | 2 | Tia Santrini || Sistem Operasi | 2 | Sabrina Sari || Teknik Kompilasi | 2 | Tia Santrini |+-------------------+------------+---------------+7 rows in set (0.05 sec)
Dan berikut setelah menggunakan alias:
mysql> SELECT nama_matkul,jumlah_SKS,nama_dosen FROM mata_kuliah AS ma INNER JOIN daftar_dosen AS da ON ma.NIP_dosen=da.NIP_dosen; +-------------------+------------+---------------+| nama_matkul | jumlah_SKS | nama_dosen |+-------------------+------------+---------------+| Database | 4 | Susi Indriani || Fisika Dasar | 2 | Tia Santrini || Jaringan Komputer | 3 | Rubin Hadi || Matematika Dasar | 4 | Sabrina Sari || Mikro Prosesor | 2 | Tia Santrini || Sistem Operasi | 2 | Sabrina Sari || Teknik Kompilasi | 2 | Tia Santrini |+-------------------+------------+---------------+7 rows in set (0.00 sec)
Query diatas akan berhasil dijalankan jika kedua kolom NIP dari tabel daftar_dosen dan mata_kuliah bernama NIP_dosen.
Perhatikan setelah perintah FROM, membuat alias untuk kedua tabel dengan perintah mata_kuliah AS ma INNER JOIN daftar_dosen AS da. Sehingga dapat menggunakannya pada perintah ON ma.NIP_dosen=da.NIP_dosen
Cara Menggunakan ALIAS untuk Mengganti Nama Kolom Tabel MySQL
Selain dipakai untuk mengubah nama tabel, MySQL juga menyediakan ALIAS untuk nama kolom. Salah satu kegunaan alias kolom ini adalah agar tampilan nama kolom menjadi lebih rapi.
Sama seperti ALIAS untuk nama tabel, alias untuk nama kolom juga merupakan perintah tambahan dari query inti seperti SELECT.
Format dasar penulisan alias kolom:
…nama_kolom_asli AS nama_kolom_alias…
nama_kolom_asli adalah nama kolom sesungguhnya yang dipakai pada saat pembuatan tabel. Contohnya nama_dosen dan no_hp.nama_kolom_alias adalah alias atau nama lain kolom yang digunakan, misalnya kolom no_hp dialiaskan menjadi nomor_handphone
Berikut contoh sederhana penggunaan kolom alias:
mysql> SELECT nama_dosen AS nama, no_hp AS nomor_handphone FROM daftar_dosen; +----------------+-----------------+| nama | nomor_handphone |+----------------+-----------------+| Sabrina Sari | 0812349900 || Maya Ari Putri | 0812345234 || Susi Indriani | 0812656532 || Tia Santrini | 0812451177 || M. Siddiq | 0812979005 || Rubin Hadi | 0812567678 || Mustalifah | 0812338877 || Arif Budiman | 0812456345 |+----------------+-----------------+8 rows in set (0.09 sec)
Perhatikan hasil dari query tersebut. Pada bagian judul kolom akan berubah seperti yang tertera pada bagian AS. Kolom nama_dosen menjadi nama, dan kolom no_hp menjadi nomor_handphone.
Perintah AS sebagai alias dari nama kolom akan lebih berguna pada saat menggunakan fungsi untuk kolom.
Sebagai contoh, MySQL menyediakan fungsi CONCAT (singkatan dari concatenates) untuk menyambung kata:
mysql> SELECT CONCAT(nama_matkul, " jumlah SKS:", jumlah_SKS) FROM mata_kuliah; +-------------------------------------------------+| CONCAT(nama_matkul, " jumlah SKS:", jumlah_SKS) |+-------------------------------------------------+| Database jumlah SKS:4 || Fisika Dasar jumlah SKS:2 || Jaringan Komputer jumlah SKS:3 || Matematika Dasar jumlah SKS:4 || Mikro Prosesor jumlah SKS:2 || Sistem Operasi jumlah SKS:2 || Teknik Kompilasi jumlah SKS:2 |+-------------------------------------------------+7 rows in set (0.24 sec)
Dalam contoh di atas, fungsi CONCAT dipakai untuk menggabung kolom nama_matkul dengan jumlah_SKS. Perhatikan juga judul dari kolom tersebut.
Untuk membuat tampilan query menjadi lebih cantik, akan mengubah judul kolom dengan menggunakan ALIAS:
mysql> SELECT CONCAT(nama_matkul, " jumlah SKS:", jumlah_SKS) AS Mata_Kuliah FROM mata_kuliah; +--------------------------------+| Mata_Kuliah |+--------------------------------+| Database jumlah SKS:4 || Fisika Dasar jumlah SKS:2 || Jaringan Komputer jumlah SKS:3 || Matematika Dasar jumlah SKS:4 || Mikro Prosesor jumlah SKS:2 || Sistem Operasi jumlah SKS:2 || Teknik Kompilasi jumlah SKS:2 |+--------------------------------+7 rows in set (0.00 sec)
Sebagai catatan, panjang dari alias dibatasi sebanyak 255 karakter. Jika ingin memakai tanda spasi, koma, atau karakter lainnya, gunakan tanda kutip backticks (“) diantara nama kolom. Karakter backticks ini ada disebelah kiri angka 1 pada keyboard standar.
mysql> SELECT nama_dosen AS Nama Dosen, no_hp AS Nomor Handphone FROM daftar_dosen; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Dosen, no_hp AS Nomor Handphone FROM daftar_dosen' at line 1 mysql> SELECT nama_dosen AS `Nama Dosen`, no_hp AS `Nomor Handphone` FROM daftar_dosen; +----------------+-----------------+| Nama Dosen | Nomor Handphone |+----------------+-----------------+| Sabrina Sari | 0812349900 || Maya Ari Putri | 0812345234 || Susi Indriani | 0812656532 || Tia Santrini | 0812451177 || M. Siddiq | 0812979005 || Rubin Hadi | 0812567678 || Mustalifah | 0812338877 || Arif Budiman | 0812456345 |+----------------+-----------------+8 rows in set (0.00 sec)
Pada query pertama mencoba membuat alias nama_dosen menjadi ‘Nama Dosen’, tetapi MySQL mengeluarkan error karena tidak dapat menerima karakter spasi sebagai nama alias, namun setelah memberi tanda kutip backtick, query tersebut sukses berjalan.- Menghapus Duplikasi (DISTINCT)
Mempersiapkan Tabel Sample: daftar_dosen
Dikarenakan keperluan contoh tabel, akan mengupdate tabel daftar_dosen dengan data yang baru. Isi tabel daftar_dosen sekarang adalah:
Tabel daftar_dosen ver.2
| NIP | Nama Dosen | No HP | Alamat |
|---|---|---|---|
| 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru |
| 0260432002 | Maya Ari Putri | 0812342342 | Palembang |
| 0275430005 | Susi Indriani | 0812656532 | Bogor |
| 0480432066 | Tia Santrini | 0812451177 | Padang |
| 0576431001 | M. Siddiq | 0812979005 | Jakarta |
| 0770435006 | Rubin Hadi | 0812567678 | Papua |
| 0869437003 | Mustalifah | 0812338877 | Aceh |
| 1080432007 | Arif Budiman | 0812456345 | Makasar |
| 0785531001 | Siswanto | 0852878006 | Padang |
| 0867221006 | Rudi Arwana | 0823987598 | Jakarta |
| 0173551078 | Aria Sulistya | 0880743523 | Jakarta |
| 0360432014 | Suci Syuhada | 0812341122 | Palembang |
Jika ingin memakai data tabel yang sama, silahkan input ke dalam tabel_daftar dosen secara manual, atau bisa juga menggunakan query LOAD DATA INFILE di bawah ini. siapkan file text yang sudah diiskan data yang mau diload.
mysql> TRUNCATE daftar_dosen;Query OK, 0 rows affected (0.05 sec)mysql> LOAD DATA INFILE 'D:\\MySQL\\daftar_dosen.txt'INTO TABLE daftar_dosenFIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';Query OK, 12 rows affected (0.04 sec)Records: 12 Deleted: 0 Skipped: 0 Warnings: 0mysql> SELECT * FROM daftar_dosen;+------------+----------------+------------+-----------+| NIP_dosen | nama_dosen | no_hp | alamat |+------------+----------------+------------+-----------+| 0160436012 | Sabrina Sari | 0812349900 | Pekanbaru || 0173551078 | Aria Sulistya | 0880743523 | Jakarta || 0260432002 | Maya Ari Putri | 0812342342 | Palembang || 0275430005 | Susi Indriani | 0812656532 | Bogor || 0360432014 | Suci Syuhada | 0812341122 | Palembang || 0480432066 | Tia Santrini | 0812451177 | Padang || 0576431001 | M. Siddiq | 0812979005 | Jakarta || 0770435006 | Rubin Hadi | 0812567678 | Papua || 0785531001 | Siswanto | 0852878006 | Padang || 0867221006 | Rudi Arwana | 0823987598 | Jakarta || 0869437003 | Mustalifah | 0812338877 | Aceh || 1080432007 | Arif Budiman | 0812456345 | Makasar |+------------+----------------+------------+-----------+12 rows in set (0.00 sec)
mysql> SELECT alamat FROM daftar_dosen ORDER BY alamat;+-----------+| alamat |+-----------+| Aceh || Bogor || Jakarta || Jakarta || Jakarta || Makasar || Padang || Padang || Palembang || Palembang || Papua || Pekanbaru |+-----------+12 rows in set (0.00 sec)Tambahkan perintah ORDER BY alamat agar tampilan lebih rapi (diurutkan berdasarkan alamat).
Dari hasil query, dapat dilihat bahwa nama kota yang sama akan tampil lebih dari sekali. Ini terjadi karena terdapat beberapa dosen yang beralamat di kota yang sama. Jika ingin setiap kota hanya muncul 1 kali saja, bisa menggunakan query DISTINCT.
Cara Penulisan Query DISTINCT MySQL
Berikut format dasar penulisan query DISTINCT:
SELECT DISTINCT nama_kolom FROM nama_tabel; |
nama_kolomadalah nama kolom yang akan ditampilkan.nama_tabeladalah nama tabel dari kolom yang akan ditampilkan.
Sehingga untuk menampilkan seluruh kota 1 kali saja bisa menggunakan query:
mysql> SELECT DISTINCT alamat FROM daftar_dosen ORDER BY alamat;+-----------+| alamat |+-----------+| Aceh || Bogor || Jakarta || Makasar || Padang || Palembang || Papua || Pekanbaru |+-----------+8 rows in set (0.00 sec)Dengan penambahan perintah DISTINCT di awal query SELECT, maka hanya data yang unik saja yang akan tampil. Seandainya hasil query terdapat data yang sama lebih dari 1 kali tampil, perintah DISTINCT hanya akan menampilkannya 1 kali saja.
Namun jika menambah kolom nama_dosen, efeknya menjadi berbeda:
mysql> SELECT DISTINCT nama_dosen,alamat FROM daftar_dosen ORDER BY alamat;+----------------+-----------+| nama_dosen | alamat |+----------------+-----------+| Mustalifah | Aceh || Susi Indriani | Bogor || Aria Sulistya | Jakarta || M. Siddiq | Jakarta || Rudi Arwana | Jakarta || Arif Budiman | Makasar || Siswanto | Padang || Tia Santrini | Padang || Maya Ari Putri | Palembang || Suci Syuhada | Palembang || Rubin Hadi | Papua || Sabrina Sari | Pekanbaru |+----------------+-----------+12 rows in set (0.06 sec)Terlihat MySQL tetap menampilkan seluruh isi tabel tanpa ada yang dieliminasi. Ini disebabkan query DISTINCT hanya mengeliminasi query yang unik secara baris per baris (per record).
Dengan mengombinasikan nama_dosen dengan alamat, maka setiap baris dianggap unik, kecuali terdapat nama dosen dan alamat yang persis sama.
