Print bookPrint book

Tutorial MySQL Lanjutan

Site: Elearning Muhidin Saimin
Course: Web Development
Book: Tutorial MySQL Lanjutan
Printed by: Guest user
Date: Thursday, 21 November 2024, 7:28 PM

Description

Tutorial MySQL lanjutan akan membahas fitur dan fungsi lanjutan MySQL. Agar bisa mengikuti tutorial ini setidaknya anda sudah paham query dasar MySQL yang dijelaskan pada Tutorial Dasar MySQL di atas.

Table of contents

1. Menjalankan Query

Tutorial Belajar MySQL: Cara Menjalankan Query MySQL dari File Teks

Dalam sesi tutorial MySQL di Duniailkom kali ini kita akan membahas cara menjalankan query MySQL yang disimpan di dalam sebuah file teks. Query yang akan dibahas adalah perintah source MySQL. Tutorial ini juga bisa digunakan untuk menginput file hasil mysqldump.

Ketika merancang sebuah aplikasi, ada kalanya kita harus menjalankan query secara berulang atau perlu menyimpan perintah query untuk digunakan di lain waktu. Daripada mengetik manual, akan lebih praktis jika query tersebut disimpan dan dijalankan dari file teks external.

Terdapat 2 cara untuk menjalankan query MySQL dari sebuah file, yakni dari luar MySQL Client (tidak perlu login), atau dari dalam MySQL Client (menggunakan perintah source).

Mempersiapkan Query MySQL

Langsung saja kita masuk ke praktek. Kali ini saya akan menjalankan query pembuatan 1 database dengan 2 buah tabel (berserta isinya) dari sebuah file teks. Berikut adalah perintah query yang akan saya gunakan:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
DROP DATABASE IF EXISTS mahasiswa;
CREATE DATABASE mahasiswa;
USE mahasiswa;
  
DROP TABLE IF EXISTS daftar_dosen;
CREATE TABLE daftar_dosen (
            NIP CHAR(10) PRIMARY KEY,
            nama_dosen VARCHAR(50) NOT NULL,
            no_hp CHAR(13),
            alamat VARCHAR(100))
            collate utf8_general_ci;
  
INSERT INTO daftar_dosen VALUES
('0160436012','Sabrina Sari','0812349900','Pekanbaru');         
INSERT INTO daftar_dosen VALUES
('0260432002','Maya Ari Putri','0812345234','Palembang');
INSERT INTO daftar_dosen VALUES
('0275430005','Susi Indriani','0812656532','Palembang');
INSERT INTO daftar_dosen VALUES
('0480432066','Tia SariSantrini','0812451177','Padang');
INSERT INTO daftar_dosen VALUES
('0576431001','M. Siddiq','0812979005','Jakarta');
INSERT INTO daftar_dosen VALUES
('0770435006','Rubin Hadi','0812567678','Papua');
INSERT INTO daftar_dosen VALUES
('0869437003','Arif Mustalifah','0812338877','Aceh');
INSERT INTO daftar_dosen VALUES
('1080432007','Arif Budiman','0812456345','Jakarta');
  
DROP TABLE IF EXISTS mata_kuliah;
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))
            collate utf8_general_ci;
  
INSERT INTO mata_kuliah VALUES
('MATDAS','Matematika Dasar',4,1,'0160436012');
INSERT INTO mata_kuliah VALUES
('FISDAS','Fisika Dasar',2,1,'0480432066');
INSERT INTO mata_kuliah VALUES
('TEKKOM','Teknik Kompilasi',2,6,'0480432066');
INSERT INTO mata_kuliah VALUES
('JARKOM','Jaringan Komputer',3,3,'0770435006');
INSERT INTO mata_kuliah VALUES
('DTBASE','Database',4,4,'0275430005');
INSERT INTO mata_kuliah VALUES
('SISOPR','Sistem Operasi',2,4,'0160436012');
INSERT INTO mata_kuliah VALUES
('MIKROP','Mikro Prosesor',2,5,'0480432066');

Perintah query diatas cukup panjang, tetapi sebenarnya sangat sederhana. Jika anda telah mempelajari tutorial MySQL dasar di Duniailkom, tidak akan menemui kendala dalam memahami perintah diatas.

Query tersebut dapat dikelompokkan menjadi 3 bagian: pembuatan database mahasiswa, pembuatan tabel daftar_dosen beserta isinya, dan pembuatan tabel mata_kuliah beserta isinya. Database dan tabel ini merupakan contoh tabel yang saya gunakan ketika membahas Cara Menampilkan Data dari Tabel MySQL (SELECT).

Sebelum membuat database dan tabel, saya menambahkan query DROP DATABASE IF EXISTS dan DROP TABLE IF EXISTS. Kedua perintah ini berfungsi untuk mengapus database dan tabel yang bernama sama (jika sudah ada). Dengan demikian, setiap kali kita menjalankan query diatas, database dan tabel yang sudah ada akan dihapus secara otomatis. Perintah ini digunakan untuk mengatasi error yang terjadi apabila sebelumnya sudah terdapat database yang juga bernama mahasiswa.

Query collate utf8_general_ci ditambahkan untuk memastikan karakter set yang digunakan adalah utf8_general_ci. Secara sederhana, ini berfungsi agar tidak terjadi error ketika melakukan operasi perbandingan string (error: Illegal mix of collations).

Setelah pembuatan tabel, saya menggunakan query INSERT INTO untuk menginput beberapa data ke dalam tabel tersebut.

Perintah diatas sebenarnya juga bisa dijalankan baris demi baris (jika anda ingin menginputnya secara manual), namun seperti judul tutorial, kita akan mencoba menjalankan seluruh query tersebut dari sebuah file text.

Membuat File Query

Langkah selanjutnya adalah membuat file text untuk menyimpan query diatas. Silahkan gunakan text editor seperti Notepad (bawaan windows) atau Notepad++, lalu save ke dalam folder bin dimana aplikasi MySQL berada.

Sebagai contoh, jika anda mengikuti tutorial MySQL di duniailkom, file instalasi MySQL akan berada di folder D:\MySQL\bin atau D:\MySQL 5.6\bin. Namun apabila anda menggunakan setingan default pada saat menginstall MySQL, maka folder aplikasi MySQL ini akan berada di C:\Program Files\MySQL\MySQL Server 5.6.

Extension dari file ini tidak menjadi masalah, anda bisa menggunakan .txt, atau .sql (lebih umum). Kali ini saya akan menyimpannya sebagai mahasiswa.txt.

File query external MySQL

Dalam tutorial ini saya menggunakan extension .txt hanya untuk memperlihatkan bahwa nama extension tidak berpengaruh. Menggunakan nama file mahasiswa.sql memang lebih umum digunakan untuk menyimpan query MySQL.

Cara Menjalankan Query MySQL dari File Teks

Sekarang kita tinggal menjalankan file tersebut ke dalam MySQL. Terdapat 2 alternatif, dari luar MySQL, atau dari dalam MySQL Client.

Pertama, saya akan menggunakan cara dari luar MySQL. Jalankan MySQL Server  (jika belum aktif), lalu gunakan cmd windows untuk masuk ke dalam folder bin instalasi MySQL (tempat dimana file mahasiswa.txt berada). Kemudian gunakan perintah berikut:

mysql -u root < mahasiswa.txt

Dalam pertintah diatas, saya menggunakan user ‘root’ tanpa password. Jika anda menggunakan user lain dan menggunakan password, formatnya adalah sebagai berikut:

mysql -u nama_user -p password < mahasiswa.txt

Jika semuanya berjalan normal, MySQL tidak akan mengeluarkan pesan apa-apa, seperti tampilan berikut:

Cara menjalankan query external MySQL

Untuk memastikan, silahkan login ke MySQL Client, dan periksa database mahasiswa beserta tabelnya:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
D:\MySQL 5.6\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.6.22 MySQL Community Server (GPL)
  
Copyright (c) 2000, 2014, Oracle and/or its affiliates.
All rights reserved.
  
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
  
Type 'help;' or '\h' for help. Type '\c' to clear the current
input statement.
  
mysql> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| data               |
| mahasiswa          |
| mysql              |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)
  
mysql> USE mahasiswa;
Database changed
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 | Palembang |
| 0480432066 | Tia Sari Santrini | 0812451177 | Padang    |
| 0576431001 | M. Siddiq         | 0812979005 | Jakarta   |
| 0770435006 | Rubin Hadi        | 0812567678 | Papua     |
| 0869437003 | Arif Mustalifah   | 0812338877 | Aceh      |
| 1080432007 | Arif Budiman      | 0812456345 | Jakarta   |
+------------+-------------------+------------+-----------+
8 rows in set (0.00 sec)
  
mysql> SELECT * FROM mata_kuliah;
+-------------+-------------------+------------+----------+------------+
| 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 |
| SISOPR      | Sistem Operasi    |          2 |        4 | 0160436012 |
| TEKKOM      | Teknik Kompilasi  |          2 |        6 | 0480432066 |
+-------------+-------------------+------------+----------+------------+
7 rows in set (0.00 sec)

Seperti yang terlihat, database mahasiswa, tabel daftar_dosen dan tabel mata_kuliah sudah berhasil dibuat.

Cara kedua adalah menggunakan query source dari dalam MySQL Client. Untuk menjalankannya, kita harus masuk ke dalam MySQL Client. Sebagai persiapan, saya akan menghapus database mahasiswa yang berasal dari perintah sebelumnya

1
2
mysql> DROP database mahasiswa;
Query OK, 2 rows affected (0.09 sec)

Kemudian, gunakan perintah berikut:

1
mysql> source mahasiswa.txt

Seluruh query pada file akan mahasiswa.txt dijalankan seperti tampilan berikut:

Cara menjalankan query external MySQL 2

Hasil: Query OK, 1 row affected (0.02 sec) adalah feedback atau penjelasan mengenai perintah yang berjalan. Anda juga akan melihat terdapat beberapa warning seperti: Query OK, 0 rows affected, 1 warning (0.00 sec). Warning ini disebabkan perintah DROP TABLE IF EXISTS yang memang akan menghasilkan peringatan apabila tabel tersebut tidak tersedia, jadi peringatan ini boleh diabaikan.

Sebagai langkah terakhir, anda bisa memeriksa apakah database mahasiswa berserta tabelnya sudah terbentuk.

Meletakkan perintah query di dalam file teks seperti yang kita bahas disini sangat praktis untuk query yang berulang atau jika anda sering gonta-ganti komputer. File query tersebut juga bisa digunakan sebagai ‘master database’, sehingga jika kita melakukan experiment terjadi sesuatu (misalnya ada data yang terhapus), tinggal menjalankannya kembali.


2. Membuat Variabel

Tutorial Belajar MySQL: Cara Membuat Variabel di Dalam Query MySQL

Untuk pembuatan query yang cukup kompleks, kita bisa menggunakan variabel di dalam MySQL. Dalam tutorial belajar MySQL kali ini saya akan membahas tentang cara membuat variabel di dalam MySQL.

Tutorial ini menggunakan database mahasiswa yang dibuat pada tutorial Cara Menjalankan Query MySQL dari File Teks.

Pengertian Variabel dalam MySQL

Secara sederhana, variabel adalah ‘penampung nilai’. Nilai dari variabel bisa berubah-ubah dari waktu ke waktu, dan juga bisa dihasilkan ketika program sudah berjalan. Dalam matematika kita juga sering menemui istilah variabel yang sering ditulis sebagai x, y atau z.

Walaupun bukan merupakan ‘bahasa pemrograman’, MySQL menyediakan fitur untuk membuat variabel. Variabel ini bisa digunakan dalam pembuatan query yang kompleks agar lebih sederhana.

Untuk membuat variabel di dalam MySQL, format dasarnya adalah sebagai berikut:

1
SET @nama_variabel = nilai_variabel

atau

1
SET @nama_variabel := nilai_variabel

Perhatikan bahwa kita bisa menggunakan tanda sama dengan ( = ) atau titik dua sama dengan ( := ) dalam mendefinisikan variabel.

Selain itu, MySQL juga membolehkan nilai variabel berasal dari hasil query SELECT, dengan format dasar sebagai berikut:

1
SELECT @nama_variabel := ‘query select disini’.

Kita akan melihat contoh penggunaannya sesaat lagi.

Cara Membuat Variabel di dalam MySQL

Sebagai tabel praktek, saya akan menggunakan database mahasiswa yang dibuat dalam tutorial Cara Menjalankan Query MySQL dari File Teks.

Berikut contoh cara membuat variabel di dalam MySQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
D:\MySQL 5.6\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.6.22 MySQL Community Server (GPL)
  
Copyright (c) 2000, 2014, Oracle and/or its affiliates.
All rights reserved.
  
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
  
Type 'help;' or '\h' for help. Type '\c' to clear the current
input statement.
  
mysql> USE mahasiswa;
Database changed
mysql> set @kota = 'Jakarta';
Query OK, 0 rows affected (0.00 sec)
  
mysql> SELECT * FROM daftar_dosen WHERE alamat = @kota;
+------------+--------------+------------+---------+
| NIP        | nama_dosen   | no_hp      | alamat  |
+------------+--------------+------------+---------+
| 0576431001 | M. Siddiq    | 0812979005 | Jakarta |
| 1080432007 | Arif Budiman | 0812456345 | Jakarta |
+------------+--------------+------------+---------+
2 rows in set (0.00 sec)

Dalam query diatas, saya membuat sebuah variabel @kota yang di-set dengan nilai ‘Jakarta’, kemudian variabel ini digunakan di dalam kondisi WHERE dari query SELECT.

Selain karakter string (huruf), kita juga bisa mengisi nilai variabel yang berasal dari hitungan matematis, seperti berikut:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> set @jum_sks := 1+1;
Query OK, 0 rows affected (0.11 sec)
  
mysql> SELECT @jum_sks;
+----------+
| @jum_sks |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
  
mysql> SELECT * FROM mata_kuliah WHERE jumlah_SKS = @jum_sks;
+-------------+------------------+------------+----------+------------+
| kode_matkul | nama_matkul      | jumlah_SKS | semester | NIP_dosen  |
+-------------+------------------+------------+----------+------------+
| FISDAS      | Fisika Dasar     |          2 |        1 | 0480432066 |
| 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)

Kali ini saya membuat variabel @jum_sks dengan nilai 1+1. Kemudian saya melihat isi dari variabel ini menggunakan perintah SELECT @jum_sks. Terakhir saya menggunakan perintah SELECT untuk menampilkan seluruh mata kuliah yang memiliki jumlah SKS = 2.

Yang perlu diingat, variabel ini hanya bisa digunakan pada bagian expression dari sebuah query (seperti pada kondisi WHERE). Kita tidak bisa menggunakan variabel untuk konstanta seperti perintah berikut:

1
2
3
4
5
6
7
8
mysql> SET @nama_table = 'jurusan';
Query OK, 0 rows affected (0.00 sec)
  
mysql> CREATE TABLE @nama_table (nama_jur CHAR(25));
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 '@nama_table (nama_jur CHAR(25))'
at line 1

Selain menggunakan perintah SET, nilai variabel juga bisa berasal dari hasil query SELECT, seperti contoh berikut:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> SELECT @kota := alamat FROM daftar_dosen WHERE
nama_dosen = 'Maya Ari Putri';
+-----------------+
| @kota := alamat |
+-----------------+
| Palembang       |
+-----------------+
1 row in set (0.00 sec)
  
mysql> SELECT * FROM daftar_dosen WHERE alamat = @kota;
+------------+----------------+------------+-----------+
| NIP        | nama_dosen     | no_hp      | alamat    |
+------------+----------------+------------+-----------+
| 0260432002 | Maya Ari Putri | 0812345234 | Palembang |
| 0275430005 | Susi Indriani  | 0812656532 | Palembang |
+------------+----------------+------------+-----------+
2 rows in set (0.00 sec)

Pada perintah pertama, saya mencari hasil dari query SELECT alamat FROM daftar_dosen WHERE nama_dosen = ‘Maya Ari Putri’, kemudian menyimpan hasilnya kedalam variabel @kota. Dengan demikian, variabel @kota sekarang akan berisi ‘Palembang’. Perhatikan cara ‘penyisipan’ ini.

Kemudian saya menggunakan nilai variabel @kota untuk menampilkan seluruh dosen yang beralamat di Palembang.

Cara pembuatan variabel yang kita bahas disini bukanlah fitur standar bahasa SQL. Dengan demikian penerapannya mungkin berbeda di dalam RDBMS lain seperti MS SQL Server atau Oracle.

Fitur variabel di dalam MySQL mungkin tidak terlalu sering di gunakan, tetapi pada situasi tertentu, ini bisa membantu kita menyederhanakan query yang cukup kompleks.


3. Mengcopy Tabel

Tutorial Belajar MySQL: Cara Mengcopy Tabel MySQL dan Isi Tabel MySQL

Dalam tutorial belajar MySQL kali ini saya akan membahas query untuk menduplikasi tabel MySQL. Kita akan mempelajari cara mengcopy struktur tabel MySQL berserta isi tabel MySQL.

Tutorial ini menggunakan database mahasiswa yang dibuat pada tutorial Cara Menjalankan Query MySQL dari File Teks.

Cara Mengcopy Struktur Tabel MySQL

Terdapat beberapa cara untuk menduplikasi tabel di dalam MySQL, salah satunya menggunakan perintah mysqldump. Namun kali ini saya akan membahas cara mencopy tabel MySQL menggunakan query dari dalam MySQL.

Untuk membuat struktur tabel baru yang sama persis dengan tabel yang sudah ada, bisa menggunakan format query sebagai berikut:

1
CREATE TABLE nama_tabel_baru LIKE nama_tabel_lama;

Sebagai contoh, saya akan menduplikasi tabel daftar_dosen menggunakan perintah berikut ini:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> CREATE TABLE daftar_dosen_baru LIKE daftar_dosen;
Query OK, 0 rows affected (0.09 sec)
 
mysql> DESCRIBE daftar_dosen_baru;
+------------+--------------+------+-----+---------+-------+
| 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.06 sec)
  
mysql> SELECT * FROM daftar_dosen_baru;
Empty set (0.00 sec)

Pada query pertama, saya menggunakan perintah CREATE TABLE daftar_dosen_baru LIKE daftar_dosen untuk membuat tabel daftar_dosen_baru yang akan menggunakan struktur yang sama dengan tabel daftar_dosen.

Perintah DESCRIBE daftar_dosen_baru memperlihatkan bahwa strukur tabel daftar_dosen_baru sama persis dengan struktur tabel aslinya: daftar_dosen.

Bagaimana dengan isi dari tabel ini? Dari query SELECT * FROM daftar_dosen_baru, terlihat bahwa tabel ini tidak berisi data apapun.

Cara Mengcopy Isi Tabel MySQL

Untuk mengcopy isi sebuah tabel ke tabel lain di dalam MySQL kita bisa memanfaatkan gabungan query INSERT dengan query SELECT. Sebagai contoh, untuk menduplikasi isi tabel daftar_dosen ke tabel daftar_dosen_baru, saya bisa menggunakan query berikut:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> INSERT INTO daftar_dosen_baru SELECT * FROM daftar_dosen;
Query OK, 8 rows affected (0.06 sec)
Records: 8  Duplicates: 0  Warnings: 0
  
mysql> SELECT * FROM daftar_dosen_baru;
+------------+------------------+------------+-----------+
| NIP        | nama_dosen       | no_hp      | alamat    |
+------------+------------------+------------+-----------+
| 0160436012 | Sabrina Sari     | 0812349900 | Pekanbaru |
| 0260432002 | Maya Ari Putri   | 0812345234 | Palembang |
| 0275430005 | Susi Indriani    | 0812656532 | Palembang |
| 0480432066 | Tia SariSantrini | 0812451177 | Padang    |
| 0576431001 | M. Siddiq        | 0812979005 | Jakarta   |
| 0770435006 | Rubin Hadi       | 0812567678 | Papua     |
| 0869437003 | Arif Mustalifah  | 0812338877 | Aceh      |
| 1080432007 | Arif Budiman     | 0812456345 | Jakarta   |
+------------+------------------+------------+-----------+
8 rows in set (0.00 sec)

Dengan menggunakan query diatas, seluruh isi tabel daftar_dosen akan dicopy kedalam tabel daftar_dosen_baru.

Bagaimana jika saya hanya butuh sebagian data saja?

Jika kita cuma butuh mengcopy sebagian data dari tabel lama (tidak semua data), cukup memberikan tambahan perintah kondisi seperti WHERE pada query SELECT. Sebagai contoh, jika saya ingin mengcopy hanya dosen yang beralamat di Palembang, saya bisa menggunakan query berikut:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> TRUNCATE daftar_dosen_baru;
Query OK, 0 rows affected (0.14 sec)
  
mysql> SELECT * FROM daftar_dosen_baru;
Empty set (0.00 sec)
  
mysql> INSERT INTO daftar_dosen_baru SELECT * FROM
daftar_dosen WHERE alamat = 'Palembang';
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0
  
mysql> SELECT * FROM daftar_dosen_baru;
+------------+----------------+------------+-----------+
| NIP        | nama_dosen     | no_hp      | alamat    |
+------------+----------------+------------+-----------+
| 0260432002 | Maya Ari Putri | 0812345234 | Palembang |
| 0275430005 | Susi Indriani  | 0812656532 | Palembang |
+------------+----------------+------------+-----------+
2 rows in set (0.00 sec)

Query TRUNCATE daftar_dosen_baru digunakan untuk mengosongkan isi tabel daftar_dosen_baru.

Selanjutnya saya memastikan tabel daftar_dosen_baru sudah kosong dari perintah SELECT * FROM daftar_dosen_baru. Terkahir, saya mengcopy data dari tabel daftar_dosen, namun hanya dosen yang beralamat di Palembang menggunakan kondisi WHERE alamat = ‘Palembang’.

Tutorial cara mengcopy atau menduplikasi tabel MySQL ini kadang diperlukan dalam kasus dimana kita butuh membuat tabel yang sama persis. Tabel ini bisa digunakan sebagai tabel ‘percobaan’ untuk mengamankan tabel asli dari perubahan.


4. View

Tutorial Belajar MySQL: Pengertian VIEW dan Cara Penggunaan VIEW dalam MySQL

Di dalam bahasa SQL, terdapat vitur yang dinamakan VIEW. Dalam tutorial belajar MySQL kali ini saya akan membahas tentang pengertian VIEW dan cara penggunaan VIEW di dalam MySQL.

Tutorial ini menggunakan database mahasiswa yang dibuat pada tutorial Cara Menjalankan Query MySQL dari File Teks.

Pengertian VIEW MySQL

Di dalam MySQL, View dapat didefenisikan sebagai ‘tabel virtual’. Tabel ini bisa berasal dari tabel lain, atau gabungan dari beberapa tabel.

Tujuan dari pembuatan VIEW adalah untuk kenyamanan (mempermudah penulisan query), untuk keamanan (menyembunyikan beberapa kolom yang bersifat rahasia), atau dalam beberapa kasus bisa digunakan untuk mempercepat proses menampilkan data (terutama jika kita akan menjalankan query tersebut secara berulang).

Sebagai contoh, misalkan saya ingin menampilkan nama dosen yang berdomisi di Jakarta, maka saya bisa menggunakan query berikut:

1
2
3
4
5
6
7
8
9
mysql> SELECT NIP, nama_dosen, alamat FROM daftar_dosen
WHERE alamat = 'Jakarta';
+------------+--------------+---------+
| NIP        | nama_dosen   | alamat  |
+------------+--------------+---------+
| 0576431001 | M. Siddiq    | Jakarta |
| 1080432007 | Arif Budiman | Jakarta |
+------------+--------------+---------+
2 rows in set (0.00 sec)

Misalkan query tersebut akan dijalankan setiap beberapa detik (diakses dari website yang sibuk),  pada setiap permintaan data, MySQL server harus melakukan pemrosesan untuk mencari seluruh dosen yang memiliki alamat di Jakarta. Selain itu, dengan menggunakan VIEW saya bisa menyembunyikan beberapa kolom dari tabel daftar_dosen.

Cara Penggunaan VIEW di dalam MySQL

Untuk membuat View di dalam MySQL, kita tinggal menggunakan format dasar sebagai berikut:

1
CREATE VIEW nama_view AS ‘query select disini’

Selanjutnya VIEW bisa diakses seperti tabel ‘biasa’. Agar lebih jelas, langsung saja kita praktek menggunakan tabel daftar_dosen dari database mahasiswa:

1
2
3
4
5
6
7
8
9
10
11
12
mysql> CREATE VIEW dosen_jakarta AS SELECT NIP, nama_dosen, alamat FROM
daftar_dosen WHERE alamat = 'Jakarta';
Query OK, 0 rows affected (0.17 sec)
  
mysql> SELECT * FROM dosen_jakarta;
+------------+--------------+---------+
| NIP        | nama_dosen   | alamat  |
+------------+--------------+---------+
| 0576431001 | M. Siddiq    | Jakarta |
| 1080432007 | Arif Budiman | Jakarta |
+------------+--------------+---------+
2 rows in set (0.15 sec)

Dalam query diatas, saya membuat sebuah VIEW bernama dosen_jakarta. Saya juga menyembunyikan kolom no_hp dari tabel asli daftar_dosen.

Untuk mengakses data yang terdapat di VIEW, cukup menggunakan query SELECT:

1
2
3
4
5
6
7
8
mysql> SELECT * FROM dosen_jakarta;
+------------+--------------+---------+
| NIP        | nama_dosen   | alamat  |
+------------+--------------+---------+
| 0576431001 | M. Siddiq    | Jakarta |
| 1080432007 | Arif Budiman | Jakarta |
+------------+--------------+---------+
2 rows in set (0.18 sec)

Sekarang, pada setiap pemanggilan VIEW, MySQL Server tidak perlu memfilter hasil pencarian, namun cukup memanggil tabel virtual. Hal ini akan mempercepat proses tampilan data.

VIEW juga berfungsi sama seperti layaknya tabel ‘biasa’, sebagai contoh, saya bisa melakukan query berikut:

1
2
3
4
5
6
7
mysql> SELECT nama_dosen FROM dosen_jakarta WHERE NIP = '1080432007';
+--------------+
| nama_dosen   |
+--------------+
| Arif Budiman |
+--------------+
1 row in set (0.20 sec)

Bagaimana jika tabel utama di update? Mari kita coba:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> INSERT INTO daftar_dosen VALUES
('0160431112','Sarah','0812349900','Jakarta');
Query OK, 1 row affected (0.05 sec)
 
mysql> INSERT INTO daftar_dosen VALUES
('1180123464','Nurhayati Koto','0812349222','Jakarta');
Query OK, 1 row affected (0.04 sec)
  
mysql> SELECT * FROM dosen_jakarta;
+------------+----------------+---------+
| NIP        | nama_dosen     | alamat  |
+------------+----------------+---------+
| 0160431112 | Sarah          | Jakarta |
| 0576431001 | M. Siddiq      | Jakarta |
| 1080432007 | Arif Budiman   | Jakarta |
| 1180123464 | Nurhayati Koto | Jakarta |
+------------+----------------+---------+
4 rows in set (0.00 sec)

Terlihat bahwa VIEW juga otomatis diupdate.

Apakah kita bisa menambahkan data ke dalam VIEW? Berikut percobaannya:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> INSERT INTO dosen_jakarta
VALUES ('0801431222','Siti Nurbaya','Jakarta');
Query OK, 1 row affected (0.04 sec)
  
mysql> SELECT * FROM dosen_jakarta;
+------------+----------------+---------+
| NIP        | nama_dosen     | alamat  |
+------------+----------------+---------+
| 0160431112 | Sarah          | Jakarta |
| 0576431001 | M. Siddiq      | Jakarta |
| 0801431222 | Siti Nurbaya   | Jakarta |
| 1080432007 | Arif Budiman   | Jakarta |
| 1180123464 | Nurhayati Koto | Jakarta |
+------------+----------------+---------+
5 rows in set (0.00 sec)
  
mysql> SELECT * FROM daftar_dosen;
+------------+------------------+------------+-----------+
| NIP        | nama_dosen       | no_hp      | alamat    |
+------------+------------------+------------+-----------+
| 0160431112 | Sarah            | 0812349900 | Jakarta   |
| 0160436012 | Sabrina Sari     | 0812349900 | Pekanbaru |
| 0260432002 | Maya Ari Putri   | 0812345234 | Palembang |
| 0275430005 | Susi Indriani    | 0812656532 | Palembang |
| 0480432066 | Tia SariSantrini | 0812451177 | Padang    |
| 0576431001 | M. Siddiq        | 0812979005 | Jakarta   |
| 0770435006 | Rubin Hadi       | 0812567678 | Papua     |
| 0801431222 | Siti Nurbaya     | NULL       | Jakarta   |
| 0869437003 | Arif Mustalifah  | 0812338877 | Aceh      |
| 1080432007 | Arif Budiman     | 0812456345 | Jakarta   |
| 1180123464 | Nurhayati Koto   | 0812349222 | Jakarta   |
+------------+------------------+------------+-----------+
11 rows in set (0.00 sec)

Ternyata kita juga bisa melakukan update ke dalam VIEW. Namun karena tidak seluruh kolom dari tabel daftar_dosen yang tersedia di dalam VIEW dosen_jakarta, akan terdapat nilai NULL di dalam tabel daftar_dosen (kolom no_hp).

Dalam tutorial kali ini kita telah membahas tentang pengertian VIEW dan cara penggunaan VIEW dalam MySQL. Contoh yang saya gunakan memang sangat sederhana, dan mungkin tidak terlihat perubahan kecepatan akses antara VIEW dengan query biasa. Untuk query yang rumit serta melibatkan ratusan tabel, VIEW bisa memberikan pengaruh besar dalam performa MySQL Server.


5. Membackup Database

Tutorial Belajar MySQL: Cara Membackup Database MySQL Menggunakan Mysqldump

Menggunakan mysqldump merupakan cara paling praktis untuk membackup atau memindahkan tabel MySQL dari sebuah komputer ke komputer lainnya. Dalam tutorial belajar MySQL di duniailkom kali ini, saya akan membahas cara membackup database mysql menggunakan mysqldump, serta cara me-restore kembali database tersebut.

Tutorial ini menggunakan database mahasiswa yang dibuat pada tutorial Cara Menjalankan Query MySQL dari File Teks.

Cara Menggunakan Mysqldump

Mysqldump sebenarnya adalah sebuah program atau aplikasi internal yang ikut disertakan ketika kita menginstall MySQL. Anda akan menemukan file mysqldump.exe di dalam folder bin pada folder instalasi MySQL. Jika mengikuti tutorial cara menginstall MySQL di duniailkom, folder tersebut akan berada di alamat D:\MySQL\bin atau D:\MySQL 5.6\bin.

Apabila anda menggunakan setingan default pada saat menginstall MySQL, maka folder aplikasi MySQL ini berada di C:\Program Files\MySQL\MySQL Server 5.6\bin.

Lokasi folder program mysqldump MySQL

Aplikasi mysqldump biasanya digunakan untuk membuat file teks external yang berisi seluruh perintah pembuatan tabel beserta isinya. File external ini juga bisa digunakan untuk membackup database, atau untuk memindahkan database ke komputer lain, bahkan ke aplikasi database lain.

Untuk menggunakan mysqldump, kita harus masuk kedalam folder bin MySQL (folder dimana aplikasi mysqldump.exe berada). Perintah mysqldump akan dijalankan dari luar MySQL Client (tidak perlu masuk ke MySQL).

Sebagai contoh tutorial, saya akan membackup seluruh tabel yang ada di dalam database mahasiswa. Berikut perintah yang diperlukan:

1
D:\MySQL 5.6\bin>mysqldump -u root mahasiswa> mahasiswa_db.sql

Perintah diatas akan membuat sebuah file teks di dalam folder D:\MySQL 5.6\bin dengan nama mahasiswa_db.sql. Anda boleh menggunakan nama file dan extension apa saja. Extension *.sql sendiri sudah menjadi ‘standar’ untuk file teks yang berisi query MySQL.

Selain itu, perintah diatas berasumsi bahwa anda menggunakan user root tanpa password. Apabila menggunakan password, maka perintahnya akan seperti ini:

1
D:\MySQL 5.6\bin>mysqldump -u root -p password mahasiswa> mahasiswa_db.sql

Untuk memastikan, silahkan buka folder D:\MySQL 5.6\bin dan cari file mahasiswa_db.sql. File inilah yang berisi seluruh perintah MySQL untuk membuat seluruh tabel beserta isinya dari database mahasiswa. Anda bisa membuka file ini menggunakan aplikasi teks editor.

File hasil mysqldump

Cara Me-restore File hasil Mysqldump

Setelah file hasil mysqldump tersedia, kita akan mencoba menginput kembali file external ini kedalam MySQL.

Sebagai persiapan awal, kita harus menyiapkan sebuah database untuk menampung tabel yang akan diinput. Ini karena file hasil mysqldump memang tidak menyertakan pembuatan database. Sebagai contoh, saya membuat sebuah database mahasiswa_baru:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
D:\MySQL 5.6\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.6.22 MySQL Community Server (GPL)
  
Copyright (c) 2000, 2014, Oracle and/or its affiliates.
All rights reserved.
  
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
  
Type 'help;' or '\h' for help. Type '\c' to clear the current
input statement.
  
mysql> CREATE DATABASE mahasiswa_baru;
Query OK, 1 row affected (0.00 sec)

Untuk menjalankan file mahasiswa_db.sql, terdapat 2 cara: dari luar MySQL Client, atau dari dalam MySQL Client (menggunakan perintah source). Kedua cara ini telah saya bahas dalam tutorial Cara Menjalankan Query MySQL dari File Teks.

Kali ini saya akan menggunakan cara dari luar MySQL Client. Silahkan keluar dari MySQL Client (jika masih berada di dalamnya), kemudian gunakan perintah berikut:

1
D:\MySQL 5.6\bin>mysql -u root mahasiswa_baru < mahasiswa_db.sql

Perhatikan bahwa kali ini saya tidak menggunakan mysqldump, tapi hanya mysql saja. Setelah memberikan informasi tentang user (root), berikutnya adalah perintah mahasiswa_baru < mahasiswa_db.sql. Perintah ini berarti: input file mahasiswa_db.sql ke database mahasiswa_baru. Jika tidak ada pesan error yang tampil, maka tabel daftar_dosen dan mata_kulian telah sukses diinput ke database mahasiswa_baru.

Untuk memastikan, silahkan masuk ke MySQL Client dan periksa database mahasiswa_baru:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
D:\MySQL 5.6\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.6.22 MySQL Community Server (GPL)
  
Copyright (c) 2000, 2014, Oracle and/or its affiliates.
All rights reserved.
  
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
  
Type 'help;' or '\h' for help. Type '\c' to clear the current
input statement.
  
mysql> USE mahasiswa_baru;
Database changed
mysql> SHOW TABLES;
+--------------------------+
| Tables_in_mahasiswa_baru |
+--------------------------+
| daftar_dosen             |
| mata_kuliah              |
+--------------------------+
2 rows in set (0.00 sec)
  
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 | Palembang |
| 0480432066 | Tia SariSantrini | 0812451177 | Padang    |
| 0576431001 | M. Siddiq        | 0812979005 | Jakarta   |
| 0770435006 | Rubin Hadi       | 0812567678 | Papua     |
| 0869437003 | Arif Mustalifah  | 0812338877 | Aceh      |
| 1080432007 | Arif Budiman     | 0812456345 | Jakarta   |
+------------+------------------+------------+-----------+
8 rows in set (0.00 sec)

Dapat terlihat, kedua tabel telah sukses di bentuk.

Perintah mysqldump merupakan salah satu perintah paling penting di dalam MySQL. mysqldump bisa digunakan untuk melakukan backup database secara reguler untuk mengantisipasi hal-hal yang tidak diinginkan.


6. Mengubah Case

Tutorial Belajar MySQL: Cara Mengubah Case (Huruf Besar dan Kecil) MySQL

Ketika menampilkan data dari database MySQL, kadang kita harus menformat hasilnya agar sesuai dengan keinginan. Kali ini saya akan membahas fungsi UPPER() dan LOWER() yang bisa digunakan untuk mengubah case (huruf besar dan kecil) dari hasil query SELECT.

Mengubah Tampilan SELECT menjadi Huruf Besar

Untuk mengubah tampilan hasil query SELECT ke huruf besar, kita bisa menggunakan fungsi bawaan MySQL: UPPER(). Berikut contoh penggunaannya:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> SELECT nama_dosen FROM daftar_dosen;
+------------------+
| nama_dosen       |
+------------------+
| Sabrina Sari     |
| Maya Ari Putri   |
| Susi Indriani    |
| Tia SariSantrini |
| M. Siddiq        |
| Rubin Hadi       |
| Arif Mustalifah  |
| Arif Budiman     |
+------------------+
8 rows in set (0.09 sec)
  
mysql> SELECT UPPER(nama_dosen) FROM daftar_dosen;
+-------------------+
| UPPER(nama_dosen) |
+-------------------+
| SABRINA SARI      |
| MAYA ARI PUTRI    |
| SUSI INDRIANI     |
| TIA SARISANTRINI  |
| M. SIDDIQ         |
| RUBIN HADI        |
| ARIF MUSTALIFAH   |
| ARIF BUDIMAN      |
+-------------------+
8 rows in set (0.02 sec)

Query pertama, SELECT nama_dosen FROM daftar_dosen digunakan untuk menampilkan kolom nama dosen ‘apa adanya’ sesuai dengan yang tersimpan di dalam database. Sehingga kita bisa melihat hasilnya dengan penambahan fungsi UPPER() dengan query SELECT UPPER(nama_dosen) FROM daftar_dosen.

Agar tampilan judul tabel lebih cantik, kita bisa menggunakan ALIAS menggunakan query AS, seperti berikut ini:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT UPPER(nama_dosen) AS 'Nama Dosen' FROM daftar_dosen;
+------------------+
| Nama Dosen       |
+------------------+
| SABRINA SARI     |
| MAYA ARI PUTRI   |
| SUSI INDRIANI    |
| TIA SARISANTRINI |
| M. SIDDIQ        |
| RUBIN HADI       |
| ARIF MUSTALIFAH  |
| ARIF BUDIMAN     |
+------------------+
8 rows in set (0.00 sec)

Kali ini tampilan tabel lebih ‘pas’ tanpa penambahan tulisan ‘UPPER(nama_dosen)’.

Bagaimana jika ingin menampilkan kolom lain dari tabel yang sama? cukup dengan menyisipkan fungsi UPPER() ke dalam kolom yang ingin diubah:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT UPPER(nama_dosen) AS 'Nama Dosen',
UPPER(alamat) AS 'Alamat' FROM daftar_dosen;
+------------------+-----------+
| Nama Dosen       | Alamat    |
+------------------+-----------+
| SABRINA SARI     | PEKANBARU |
| MAYA ARI PUTRI   | PALEMBANG |
| SUSI INDRIANI    | PALEMBANG |
| TIA SARISANTRINI | PADANG    |
| M. SIDDIQ        | JAKARTA   |
| RUBIN HADI       | PAPUA     |
| ARIF MUSTALIFAH  | ACEH      |
| ARIF BUDIMAN     | JAKARTA   |
+------------------+-----------+
8 rows in set (0.00 sec)
Mengenai cara penggunaan query ALIAS atau AS telah dibahas pada Tutorial MySQL: Menggunakan Alias (AS)

Mengubah Tampilan SELECT menjadi Huruf Kecil

Untuk mengubah tampilan query SELECT ke dalam huruf kecil, bisa menggunakan fungsi LOWER(). Penggunaannya sama persis dengan fungsi UPPER() yang telah kita bahas. Berikut contoh penggunaannya:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
mysql> SELECT nama_dosen FROM daftar_dosen;
+------------------+
| nama_dosen       |
+------------------+
| Sabrina Sari     |
| Maya Ari Putri   |
| Susi Indriani    |
| Tia SariSantrini |
| M. Siddiq        |
| Rubin Hadi       |
| Arif Mustalifah  |
| Arif Budiman     |
+------------------+
8 rows in set (0.00 sec)
  
mysql> SELECT LOWER(nama_dosen) FROM daftar_dosen;
+-------------------+
| LOWER(nama_dosen) |
+-------------------+
| sabrina sari      |
| maya ari putri    |
| susi indriani     |
| tia sarisantrini  |
| m. siddiq         |
| rubin hadi        |
| arif mustalifah   |
| arif budiman      |
+-------------------+
8 rows in set (0.00 sec)
  
mysql> SELECT LOWER(nama_dosen) AS 'Nama Dosen' FROM daftar_dosen;
+------------------+
| Nama Dosen       |
+------------------+
| sabrina sari     |
| maya ari putri   |
| susi indriani    |
| tia sarisantrini |
| m. siddiq        |
| rubin hadi       |
| arif mustalifah  |
| arif budiman     |
+------------------+
8 rows in set (0.00 sec)
  
mysql> SELECT LOWER(nama_dosen) AS 'Nama Dosen',
LOWER(alamat) AS 'Alamat' FROM daftar_dosen;
+------------------+-----------+
| Nama Dosen       | Alamat    |
+------------------+-----------+
| sabrina sari     | pekanbaru |
| maya ari putri   | palembang |
| susi indriani    | palembang |
| tia sarisantrini | padang    |
| m. siddiq        | jakarta   |
| rubin hadi       | papua     |
| arif mustalifah  | aceh      |
| arif budiman     | jakarta   |
+------------------+-----------+
8 rows in set (0.00 sec)

Mengubah Huruf Kolom Tabel Secara Permanen

Selain digunakan dalam menampilkan data (query SELECT), kita juga bisa menggunakan fungsi UPPER dan LOWER() untuk mengubah huruf di dalam kolom tabel secara permanen. Berikut contohnya:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> UPDATE daftar_dosen SET nama_dosen = UPPER (nama_dosen);
Query OK, 8 rows affected (0.16 sec)
Rows matched: 8  Changed: 8  Warnings: 0
  
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 | Palembang |
| 0480432066 | TIA SARISANTRINI | 0812451177 | Padang    |
| 0576431001 | M. SIDDIQ        | 0812979005 | Jakarta   |
| 0770435006 | RUBIN HADI       | 0812567678 | Papua     |
| 0869437003 | ARIF MUSTALIFAH  | 0812338877 | Aceh      |
| 1080432007 | ARIF BUDIMAN     | 0812456345 | Jakarta   |
+------------+------------------+------------+-----------+
8 rows in set (0.00 sec)

Perintah UPDATE diatas akan mengubah secara permanen kolom nama_dosen menjadi huruf besar.

Fungsi bawaan MySQL UPPER() dan LOWER() yang kita bahas disini cukup sederhana, namun akan berguna dalam beberapa kasus tertentu. Jika menggunakan PHP untuk menampilkan data MySQL kita bisa memilih apakah akan menggunakan fungsi UPPER() MySQL atau menggunakan fungsi strtoupper() dari PHP.


7. Menyambung String

Tutorial Belajar MySQL: Cara Menyambung String di dalam Query MySQL (CONCAT)

Dalam tutorial belajar MySQL di Duniailkom kali ini akan membahas tentang cara menyambung string / menggabungkan string menggunakan fungsi CONCAT() dan CONCAT_WS() dari MySQL.

Tutorial ini menggunakan database mahasiswa yang dibuat pada tutorial Cara Menjalankan Query MySQL dari File Teks.

Mengenal Cara Penggunaan Fungsi CONCAT() MySQL

MySQL memiliki fungsi bawaan yang bisa digunakan untuk menyambung string atau menggabungkan string hasil query, yakni melalui fungsi CONCAT() (singkatan dari concatenating).

Menggunakan tabel mata_kuliah, misalkan saya ingin menyambung kolom kode_matkul dengan nama_matkul, berikut query yang bisa digunakan:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> SELECT * FROM mata_kuliah;
+-------------+-------------------+------------+----------+------------+
| 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 |
| SISOPR      | Sistem Operasi    |          2 |        4 | 0160436012 |
| TEKKOM      | Teknik Kompilasi  |          2 |        6 | 0480432066 |
+-------------+-------------------+------------+----------+------------+
7 rows in set (0.00 sec)
  
mysql> SELECT CONCAT(kode_matkul, nama_matkul) FROM mata_kuliah;
+----------------------------------+
| CONCAT(kode_matkul, nama_matkul) |
+----------------------------------+
| DTBASEDatabase                   |
| FISDASFisika Dasar               |
| JARKOMJaringan Komputer          |
| MATDASMatematika Dasar           |
| MIKROPMikro Prosesor             |
| SISOPRSistem Operasi             |
| TEKKOMTeknik Kompilasi           |
+----------------------------------+
7 rows in set (0.00 sec)

Query pertama digunakan untuk menampilkan seluruh kolom dari tabel mata_kuliah, dan di dalam query kedualah fungsi CONCAT() digunakan.

Bagaimana jika menggabungkan 3 atau 4 sekaligus? Kita tinggal menambahkan nama setiap kolom ke dalam fungsi CONCAT(), dan dipisahkan dengan karakter koma:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT CONCAT(kode_matkul, nama_matkul, NIP_dosen) FROM mata_kuliah;
+---------------------------------------------+
| CONCAT(kode_matkul, nama_matkul, NIP_dosen) |
+---------------------------------------------+
| DTBASEDatabase0275430005                    |
| FISDASFisika Dasar0480432066                |
| JARKOMJaringan Komputer0770435006           |
| MATDASMatematika Dasar0160436012            |
| MIKROPMikro Prosesor0480432066              |
| SISOPRSistem Operasi0160436012              |
| TEKKOMTeknik Kompilasi0480432066            |
+---------------------------------------------+
7 rows in set (0.00 sec)

Agar nama tabel lebih rapi, kita bisa mengubah nama kolom hasil query menggunakan AS (alias):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT CONCAT(kode_matkul, nama_matkul, NIP_dosen) AS 'Mata Kuliah
dan NIP Dosen' FROM mata_kuliah;
+-----------------------------------+
| Mata Kuliah dan NIP Dosen         |
+-----------------------------------+
| DTBASEDatabase0275430005          |
| FISDASFisika Dasar0480432066      |
| JARKOMJaringan Komputer0770435006 |
| MATDASMatematika Dasar0160436012  |
| MIKROPMikro Prosesor0480432066    |
| SISOPRSistem Operasi0160436012    |
| TEKKOMTeknik Kompilasi0480432066  |
+-----------------------------------+
7 rows in set (0.00 sec)

Sekarang, judul kolom tabel terlihat lebih cantik.

Fungsi CONCAT() ini bisa digunakan untuk menyambung string hasil query MySQL, namun seperti yang anda lihat, fungsi ini menyambung seluruh string tanpa ada ‘spasi’. Jika kita ingin menambahkan sedikit jarak antara string ini, bisa menggunakan fungsi CONCAT_WS()

Mengenal Cara Penggunaan Fungsi CONCAT_WS MySQL

Fungsi CONCAT_WS() adalah variasi lain dai fungsi CONCAT(), perbedaannya dengan menggunakan fungsi CONCAT_WS(), kita bisa menambahkan karakter pembatas antara kolom yang akan digabung. Tambahan singkatan WS disini berarti ‘With Separator’.

Cara penggunaan fungsi CONCAT_WS() hampir sama dengan fungsi CONCAT(), perbedaannya terletak di argumen pertama fungsi CONCAT_WS() yang meminta inputan karakter pembatas.

Sebagai contoh, untuk menggabungkan kolom kode_matkul dengan nama_matkul yang dipisahkan dengan sebuah spasi, saya bisa menggunakan query berikut ini:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT CONCAT_WS(' ',kode_matkul, nama_matkul) FROM mata_kuliah;
+-----------------------------------------+
| CONCAT_WS(' ',kode_matkul, nama_matkul) |
+-----------------------------------------+
| DTBASE Database                         |
| FISDAS Fisika Dasar                     |
| JARKOM Jaringan Komputer                |
| MATDAS Matematika Dasar                 |
| MIKROP Mikro Prosesor                   |
| SISOPR Sistem Operasi                   |
| TEKKOM Teknik Kompilasi                 |
+-----------------------------------------+
7 rows in set (0.00 sec)

Perhatikan cara penulisan CONCAT_WS(‘ ‘,kode_matkul, nama_matkul). Argumen pertama fungsi ini adalah sebuah karakter spasi yang diapit dalam tanda kutip. Kita tinggal menggantinya dengan karakter lain jika dibutuhkan, seperti contoh berikut:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT CONCAT_WS(', nama mata kuliah: ',kode_matkul, nama_matkul)
FROM mata_kuliah;
+------------------------------------------------------------+
| CONCAT_WS(', nama mata kuliah: ',kode_matkul, nama_matkul) |
+------------------------------------------------------------+
| DTBASE, nama mata kuliah: Database                         |
| FISDAS, nama mata kuliah: Fisika Dasar                     |
| JARKOM, nama mata kuliah: Jaringan Komputer                |
| MATDAS, nama mata kuliah: Matematika Dasar                 |
| MIKROP, nama mata kuliah: Mikro Prosesor                   |
| SISOPR, nama mata kuliah: Sistem Operasi                   |
| TEKKOM, nama mata kuliah: Teknik Kompilasi                 |
+------------------------------------------------------------+
7 rows in set (0.00 sec)

Kali ini saya menggunakan fungsi CONCAT_WS(‘, nama mata kuliah: ‘,kode_matkul, nama_matkul) untuk menghasilkan kolom baru. Dengan penambahan query AS, judul kolom akan lebih rapi:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT CONCAT_WS(', nama mata kuliah: ',kode_matkul, nama_matkul)
AS 'Mata Kuliah' FROM mata_kuliah;
+---------------------------------------------+
| Mata Kuliah                                 |
+---------------------------------------------+
| DTBASE, nama mata kuliah: Database          |
| FISDAS, nama mata kuliah: Fisika Dasar      |
| JARKOM, nama mata kuliah: Jaringan Komputer |
| MATDAS, nama mata kuliah: Matematika Dasar  |
| MIKROP, nama mata kuliah: Mikro Prosesor    |
| SISOPR, nama mata kuliah: Sistem Operasi    |
| TEKKOM, nama mata kuliah: Teknik Kompilasi  |
+---------------------------------------------+
7 rows in set (0.00 sec)

Perlu juga ditambahkan bahwa fungsi CONCAT_WS() akan menambahkan karakter pembatas untuk setiap penyambungan kolom, seperti contoh berikut:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT CONCAT_WS(' > ',kode_matkul, nama_matkul, jumlah_SKS,
semester) AS 'Mata Kuliah' FROM mata_kuliah;
+------------------------------------+
| Mata Kuliah                        |
+------------------------------------+
| DTBASE > Database > 4 > 4          |
| FISDAS > Fisika Dasar > 2 > 1      |
| JARKOM > Jaringan Komputer > 3 > 3 |
| MATDAS > Matematika Dasar > 4 > 1  |
| MIKROP > Mikro Prosesor > 2 > 5    |
| SISOPR > Sistem Operasi > 2 > 4    |
| TEKKOM > Teknik Kompilasi > 2 > 6  |
+------------------------------------+
7 rows in set (0.00 sec)

Query diatas digunakan untuk menyambung 4 kolom sekaligus yang dibatasi dengan karakter ‘ > ’.

Fungsi CONCAT() dan CONCAT_WS() yang kita pelajari disini sangat praktis digunakan untuk menggabungkan beberapa kolom hasil query SELECT dalam MySQL.


8. Memotong String

Tutorial Belajar MySQL: Cara Memotong String Hasil Query MySQL (SUBSTRING)

Ketika menampilkan tabel hasil query, kadang kita perlu memecah data dari sebuah kolom. MySQL menyediakan beberapa fungsi yang bisa digunakan untuk keperluan ini, seperti fungsi SUBSTRING, SUBSTR, MID, LEFT dan RIGHT. Kita akan membahas cara penggunaan kelima fungsi ini.

Cara Penggunaan Fungsi SUBSTRING, SUBSTR dan MID MySQL

MySQL meyediakan fungsi SUBSTRING untuk memotong string. Fungsi ini membutuhkan 2 argumen utama dan 1 argumen tambahan (opsional). Berikut format dasarnya:

1
SUBSTRING (nama_kolom, index_awal, jumlah_karakter)
  • nama_kolom adalah nama kolom tabel yang akan diambil karakternya (sebagai sumber string)
  • index_awal bisa diisi dengan angka yang berfungsi sebagai index awal karakter yang ingin diambil, dihitung dari index 1 pada karakter pertama, index 2 pada karakter kedua, dst. Apabila diinput dengan nilai negatif, index akan dihitung mulai dari akhir string.
  • jumlah_karakter adalah argumen opsional yang jika tidak ditulis, fungsi SUBSTRING akan mengambil seluruh karakter hingga akhir string. Jika ditulis, maka ini berfungsi sebagai batasan jumlah karakter yang akan diambil.

Agar lebih mudah dipahami, langsung saja kita masuk ke praktek. Kali ini saya menggunakan kolom NIP dari tabel daftar_dosen:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
mysql> SELECT NIP FROM daftar_dosen;
+------------+
| NIP        |
+------------+
| 0160436012 |
| 0260432002 |
| 0275430005 |
| 0480432066 |
| 0576431001 |
| 0770435006 |
| 0869437003 |
| 1080432007 |
+------------+
8 rows in set (0.00 sec)
  
mysql> SELECT SUBSTRING(NIP, 4) FROM daftar_dosen;
+-------------------+
| SUBSTRING(NIP, 4) |
+-------------------+
| 0436012           |
| 0432002           |
| 5430005           |
| 0432066           |
| 6431001           |
| 0435006           |
| 9437003           |
| 0432007           |
+-------------------+
8 rows in set (0.00 sec)
  
mysql> SELECT SUBSTRING(NIP, 4, 3) FROM daftar_dosen;
+----------------------+
| SUBSTRING(NIP, 4, 3) |
+----------------------+
| 043                  |
| 043                  |
| 543                  |
| 043                  |
| 643                  |
| 043                  |
| 943                  |
| 043                  |
+----------------------+
8 rows in set (0.00 sec)
  
mysql> SELECT SUBSTRING(NIP, -4) FROM daftar_dosen;
+--------------------+
| SUBSTRING(NIP, -4) |
+--------------------+
| 6012               |
| 2002               |
| 0005               |
| 2066               |
| 1001               |
| 5006               |
| 7003               |
| 2007               |
+--------------------+
8 rows in set (0.00 sec)
  
mysql> SELECT SUBSTRING(NIP, -4, 3) FROM daftar_dosen;
+-----------------------+
| SUBSTRING(NIP, -4, 3) |
+-----------------------+
| 601                   |
| 200                   |
| 000                   |
| 206                   |
| 100                   |
| 500                   |
| 700                   |
| 200                   |
+-----------------------+
8 rows in set (0.00 sec)

Dalam contoh diatas, saya menggunakan 4 kombinasi fungsi SUBSTRING:

  • SELECT SUBSTRING(NIP, 4) FROM daftar_dosen: ambil seluruh karakter pada kolom daftar dosen_mulai dari index (huruf) ke-4 hingga akhir string.
  • SELECT SUBSTRING(NIP, 4, 3) FROM daftar_dosen: ambil seluruh karakter pada kolom daftar_dosen mulai dari index (huruf) ke-4, dan 3 karakter berikutnya.
  • SELECT SUBSTRING(NIP, -4) FROM daftar_dosen: ambil 4 karakter terakhir dari kolom daftar dosen.
  • SELECT SUBSTRING(NIP, -4, 3) FROM daftar_dosen: ambil 4 karakter terakhir dari kolom daftar dosen, kecuali 1 huruf terakhir (4-3 = 1)

Dalam MySQL, fungsi SUBSTRING memiliki 2 alias: SUBSTR dan MID. Dengan kata lain, ketiga fungsi ini berfungsi sama:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql> SELECT SUBSTR(NIP, -4, 3) FROM daftar_dosen;
+--------------------+
| SUBSTR(NIP, -4, 3) |
+--------------------+
| 601                |
| 200                |
| 000                |
| 206                |
| 100                |
| 500                |
| 700                |
| 200                |
+--------------------+
8 rows in set (0.00 sec)
mysql> SELECT MID(NIP, -4, 3) FROM daftar_dosen;
+-----------------+
| MID(NIP, -4, 3) |
+-----------------+
| 601             |
| 200             |
| 000             |
| 206             |
| 100             |
| 500             |
| 700             |
| 200             |
+-----------------+
8 rows in set (0.00 sec)

MySQL menyediakan berbagai nama untuk fungsi yang sama agar sesuai dengan standar bahasa SQL.

Cara Penggunaan Fungsi LEFT dan RIGHT MySQL

Fungsi LEFT dan RIGHT pada dasarnya merupakan bentuk sederhana dari fungsi SUBSTRING. Keduanya membutuhkan 2 argumen yaitu nama kolom tabel dan jumlah huruf yang ingin diambil.

Fungsi LEFT akan mengambil sejumlah karakter mulai dari kiri string, dan fungsi RIGHT akan mulai dari sisi kanan string.

Berikut contoh percobaannya menggunakan kolom NIP dari tabel nama_dosen:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
mysql> SELECT NIP FROM daftar_dosen;
+------------+
| NIP        |
+------------+
| 0160436012 |
| 0260432002 |
| 0275430005 |
| 0480432066 |
| 0576431001 |
| 0770435006 |
| 0869437003 |
| 1080432007 |
+------------+
8 rows in set (0.00 sec)
  
mysql> SELECT LEFT(NIP, 5) FROM daftar_dosen;
+--------------+
| LEFT(NIP, 5) |
+--------------+
| 01604        |
| 02604        |
| 02754        |
| 04804        |
| 05764        |
| 07704        |
| 08694        |
| 10804        |
+--------------+
8 rows in set (0.00 sec)
  
mysql> SELECT RIGHT(NIP, 5) FROM daftar_dosen;
+---------------+
| RIGHT(NIP, 5) |
+---------------+
| 36012         |
| 32002         |
| 30005         |
| 32066         |
| 31001         |
| 35006         |
| 37003         |
| 32007         |
+---------------+
8 rows in set (0.00 sec)

Seperti yang terlihat, query SELECT LEFT(NIP, 5) FROM daftar_dosen akan menampilkan 5 karakter pertama dari kolom NIP, sedangkan query SELECT RIGHT(NIP, 5) FROM daftar_dosen akan mengambil 5 karakter terakhir dari kolom NIP.

Yang perlu diingat, fungsi LEFT dan RIGHT tidak mendukung angka negatif seperti fungsi SUBSTRING.

Query ‘pemotongan’ string yang kita pelajari disini cukup penting diketahui, terutama jika kita ingin menggunakan kolom yang terdiri dari kumpulan informasi. Misalkan untuk NIM mahasiswa, biasanya 2 digit pertama adalah tahun lahir, 2 digit berikutnya kode fakultas, dan 4 digit berikutnya no urut mahasiswa. Fungsi SUBSTRING bisa digunakan untuk memecah NIM ini untuk pemrosesan lebih lanjut.


9. Mengambil Nilai Tanggal

Tutorial Belajar MySQL: Cara Mengambil Nilai Tanggal (DATE dan TIMESTAMP) MySQL

Tipe data tanggal (DATE) merupakan salah satu tipe data yang cukup sulit digunakan. Selain memerlukan format tersendiri, hasil dari kolom dengan tipe data date juga ‘terbalik’, yakni dengan format tampilan YYYY-MM-DD.

Dalam tutorial belajar MySQL kali ini saya akan membahas beberapa fungsi MySQL yang bisa digunakan untuk mengambil dan menampilkan tipe data DATE.

Penjelasan cara penggunaan tipe data DATE MySQL telah dibahas pada: Tutorial MySQL Tipe Data Date (Tanggal).

Mempersiapkan Tabel belajar_date

Sebagai tabel sample, saya akan membuat tabel belajar_date. Tabel ini terdiri dari dua kolom bertipe DATE dan TIMESTAMP. Berikut query yang saya gunakan:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> CREATE TABLE belajar_date (dt DATE,ts TIMESTAMP);
Query OK, 0 rows affected (0.08 sec)
  
mysql> INSERT INTO belajar_date VALUES ('2015-08-10','2015-08-10 08:30:15');
Query OK, 1 row affected (0.03 sec)
  
mysql> INSERT INTO belajar_date VALUES ('2016-08-17','2016-08-17 10:01:01');
Query OK, 1 row affected (0.04 sec)
  
mysql> INSERT INTO belajar_date VALUES ('2017-12-31','2017-12-31 23:59:59');
Query OK, 1 row affected (0.05 sec)
  
mysql> SELECT * FROM belajar_date;
+------------+---------------------+
| dt         | ts                  |
+------------+---------------------+
| 2015-08-10 | 2015-08-10 08:30:15 |
| 2016-08-17 | 2016-08-17 10:01:01 |
| 2017-12-31 | 2017-12-31 23:59:59 |
+------------+---------------------+
3 rows in set (0.00 sec)

Seperti yang terlihat, MySQL menyimpan kolom bertipe data DATE menggunakan format YYYY-MM-DD, dan untuk tipe data TIMESTAMP dengan format YYYY-MM-DD HH:MM:SS.

Fungsi MySQL untuk Mengambil Nilai Date

MySQL menyediakan beragam fungsi untuk mengambil sebagian data dari kolom bertipe DATE dan TIMESTAMP. Sebagai contoh, kita bisa menggunakan fungsi YEAR() untuk mengambil bagian tahun, seperti query berikut ini:

1
2
3
4
5
6
7
8
9
mysql> SELECT YEAR(dt) FROM belajar_date;
+----------+
| YEAR(dt) |
+----------+
|     2015 |
|     2016 |
|     2017 |
+----------+
3 rows in set (0.00 sec)

Pada query diatas, saya menampilkan hanya nilai tahun dari kolom dt. Bagaimana dengan nilai Bulan dan Tanggal? Berikut querynya:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> SELECT MONTH(dt) FROM belajar_date;
+-----------+
| MONTH(dt) |
+-----------+
|         8 |
|         8 |
|        12 |
+-----------+
3 rows in set (0.00 sec)
  
mysql> SELECT DAY(dt) FROM belajar_date;
+---------+
| DAY(dt) |
+---------+
|      10 |
|      17 |
|      31 |
+---------+
3 rows in set (0.00 sec)

Selain fungsi YEAR(), MONTH() dan DAY() yang saya gunakan, tabel berikut merangkum beberapa fungsi lain yang bisa digunakan untuk tipe data DATE dan TIMESTAMP:

Nama FungsiHasil
YEAR()Menampilkan nilai tahun
MONTH()Menampilkan nilai bulan (1..12)
MONTHNAME()Menampilkan nama bulan (January..December)
DAYOFMONTH()Menampilkan nilai tanggal (1..31)
DAYNAME()Menampilkan nama hari (Sunday..Saturday)
DAYOFWEEK()Menampilkan nilai hari (angka 1..7)
WEEKDAY()Menampilkan nilai hari (angka 0..6)
DAYOFYEAR()Menampilkan urutan hari (1..366)
HOUR()Menampilkan nilai jam (0..23)
MINUTE()Menampilkan nilai menit (0..59)
SECOND()Menampilkan nilai detik(0..59)

Dengan menggunakan fungsi-fungsi ini kita bisa mengambil nilai yang diinginkan dari kolom DATE dan TIMESTAMP:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
mysql> SELECT HOUR(ts) FROM belajar_date;
+----------+
| HOUR(ts) |
+----------+
|        8 |
|       10 |
|       23 |
+----------+
3 rows in set (0.00 sec)
  
mysql> SELECT MINUTE(ts) FROM belajar_date;
+------------+
| MINUTE(ts) |
+------------+
|         30 |
|          1 |
|         59 |
+------------+
3 rows in set (0.00 sec)
  
mysql> SELECT SECOND(ts) FROM belajar_date;
+------------+
| SECOND(ts) |
+------------+
|         15 |
|          1 |
|         59 |
+------------+
3 rows in set (0.00 sec)
  
mysql> SELECT DAYOFYEAR(ts) FROM belajar_date;
+---------------+
| DAYOFYEAR(ts) |
+---------------+
|           222 |
|           230 |
|           365 |
+---------------+
3 rows in set (0.00 sec)
  
mysql> SELECT DAYNAME(ts) FROM belajar_date;
+-------------+
| DAYNAME(ts) |
+-------------+
| Monday      |
| Wednesday   |
| Sunday      |
+-------------+
3 rows in set (0.06 sec)
  
mysql> SELECT ts, DAYNAME(ts) FROM belajar_date;
+---------------------+-------------+
| ts                  | DAYNAME(ts) |
+---------------------+-------------+
| 2015-08-10 08:30:15 | Monday      |
| 2016-08-17 10:01:01 | Wednesday   |
| 2017-12-31 23:59:59 | Sunday      |
+---------------------+-------------+
3 rows in set (0.00 sec)

Fungsi CURDATE() dan NOW()

Masih berkaitan dengan tipe data date, MySQL menyediakan fungsi bawaan untuk menghasilkan nilai DATE dan TIMESTAMP sistem saat ini. Fungsi yang bisa digunakan adalah CURDATE() dan NOW().

Berikut contohnya:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> INSERT INTO belajar_date VALUES (CURDATE(), NOW());
Query OK, 1 row affected (0.03 sec)
  
mysql> SELECT * FROM belajar_date;
+------------+---------------------+
| dt         | ts                  |
+------------+---------------------+
| 2015-08-10 | 2015-08-10 08:30:15 |
| 2016-08-17 | 2016-08-17 10:01:01 |
| 2017-12-31 | 2017-12-31 23:59:59 |
| 2015-08-04 | 2015-08-04 15:34:14 |
+------------+---------------------+
4 rows in set (0.00 sec)

Fungsi CURDATE() akan menghasilkan nilai DATE yang ada di server dengan format YYYY-MM-DD, sedangkan fungsi NOW() akan menghasilkan nilai TIMESTAMP dengan format YYYY-MM-DD HH:MM:SS. Dalam query diatas, saya menggunakan kedua fungsi ini untuk menginput data baru ke dalam tabel belajar_date.

Dapat anda lihat bahwa kode diatas dijalankan pada tanggal 4 Agustus 2015 pukul 15:34:14, sesuai dengan sistem komputer yang saya gunakan (baris terakhir pada tabel).

Fungsi-fungsi tanggal yang kita bahas kali ini cocok untuk menformat tampilan kolom dengan tipe data DATE dan TIMESTAMP di dalam MySQL. Untuk hasil yang lebih fleksibel, bisa menggunakan fungsi DATE_FORMAT() yang akan saya bahas pada tutorial terpisah.


10. Memformat Tampilan Tanggal

Tutorial Belajar MySQL: Cara Memformat Tampilan Tanggal MySQL (DATE_FORMAT)

Secara default, MySQL menyimpan tipe data tanggal (DATE) dengan format YYYY-MM-DD dan tipe data TIMESTAMP dengan format YYYY-MM-DD HH:MM:SS. Format default ini tidak cocok untuk tampilan akhir.

Pada tutorial belajar MySQL kali ini saya akan membahas fungsi DATE_FORMAT() yang bisa digunakan untuk mengubah tampilan tipe data DATE dan TIMESTAMP.

Tutorial ini menggunakan tabel belajar_date yang dibuat pada tutorial cara mengambil nilai tanggal (DATE dan TIMESTAMP) MySQL.

Format Dasar Fungsi DATE_FORMAT() MySQL

Fungsi DATE_FORMAT() sangat fleksibel untuk menampilkan output tanggal dari sebuah tabel MySQL. Fungsi ini membutuhkan 2 buah argumen, yakni kolom tabel yang ingin di-format dan ‘string format’ tanggal.

Berikut format dasar fungsi DATE_FORMAT MySQL:

1
DATE_FORMAT(nama_kolom, ‘string format’)

Sebagai contoh, saya akan memformat kolom dt dari tabel belajar_date agar ditampilkan dengan format: hari/bulan/tahun:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> SELECT * FROM belajar_date;
+------------+---------------------+
| dt         | ts                  |
+------------+---------------------+
| 2015-08-10 | 2015-08-10 08:30:15 |
| 2016-08-17 | 2016-08-17 10:01:01 |
| 2017-12-31 | 2017-12-31 23:59:59 |
| 2015-08-04 | 2015-08-04 15:34:14 |
+------------+---------------------+
4 rows in set (0.00 sec)
  
mysql> SELECT DATE_FORMAT(dt, '%d/%m/%Y') FROM belajar_date;
+-----------------------------+
| DATE_FORMAT(dt, '%d/%m/%Y') |
+-----------------------------+
| 10/08/2015                  |
| 17/08/2016                  |
| 31/12/2017                  |
| 04/08/2015                  |
+-----------------------------+
4 rows in set (0.00 sec)

Dapat terlihat hasil query SELECT DATE_FORMAT(dt, ‘%d/%m/%Y’) FROM belajar_date akan menghasilkan kolom dt dengan tampilan yang umum kita gunakan di Indonesia.

Bagaimana cara membuat ‘string format’ ini? Kita harus merangkainya menggunakan karakter khusus sesuai dengan tabel berikut:

FormatPenjelasan
%aAbbreviated weekday name (Sun..Sat)
%bAbbreviated month name (Jan..Dec)
%cMonth, numeric (0..12)
%DDay of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%dDay of the month, numeric (00..31)
%eDay of the month, numeric (0..31)
%fMicroseconds (000000..999999)
%HHour (00..23)
%hHour (01..12)
%IHour (01..12)
%iMinutes, numeric (00..59)
%jDay of year (001..366)
%kHour (0..23)
%lHour (1..12)
%MMonth name (January..December)
%mMonth, numeric (00..12)
%pAM or PM
%rTime, 12-hour (hh:mm:ss followed by AM or PM)
%SSeconds (00..59)
%sSeconds (00..59)
%TTime, 24-hour (hh:mm:ss)
%UWeek (00..53), where Sunday is the first day of the week; WEEK() mode 0
%uWeek (00..53), where Monday is the first day of the week; WEEK() mode 1
%VWeek (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%vWeek (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%WWeekday name (Sunday..Saturday)
%wDay of the week (0=Sunday..6=Saturday)
%XYear for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%xYear for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%YYear, numeric, four digits
%yYear, numeric (two digits)
%%A literal “%” character
%xx, for any “x” not listed above

Cara Penggunaan Fungsi DATE_FORMAT() MySQL

Berdasarkan tabel diatas, kita bisa merangkai format apapun tergantung kebutuhan. Sebagai contoh, jika saya ingin membuat tampilan seperti: ‘10 – 08 – 2015’, maka string formatnya adalah: ‘%d – %m – %Y’. Atau jika saya ingin tampilannya menjadi ‘10 August 2015’, maka string formatnya menggunakan: ‘%d %M %Y’.

Berikut beberapa contoh praktek merangkai ‘string_format’ menggunakan tabel belajar_date:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> SELECT DATE_FORMAT(dt, '%d - %m - %Y') FROM belajar_date;
+---------------------------------+
| DATE_FORMAT(dt, '%d - %m - %Y') |
+---------------------------------+
| 10 - 08 - 2015                  |
| 17 - 08 - 2016                  |
| 31 - 12 - 2017                  |
| 04 - 08 - 2015                  |
+---------------------------------+
4 rows in set (0.00 sec)
mysql> SELECT DATE_FORMAT(dt, '%d %M %Y') FROM belajar_date;
+-----------------------------+
| DATE_FORMAT(dt, '%d %M %Y') |
+-----------------------------+
| 10 August 2015              |
| 17 August 2016              |
| 31 December 2017            |
| 04 August 2015              |
+-----------------------------+
4 rows in set (0.00 sec)
mysql> SELECT DATE_FORMAT(ts, '%d %M %Y, %k:%i:%s') FROM belajar_date;
+---------------------------------------+
| DATE_FORMAT(ts, '%d %M %Y, %k:%i:%s') |
+---------------------------------------+
| 10 August 2015, 8:30:15               |
| 17 August 2016, 10:01:01              |
| 31 December 2017, 23:59:59            |
| 04 August 2015, 15:34:14              |
+---------------------------------------+
4 rows in set (0.03 sec)

Pada query terakhir, saya men-format tampilan kolom ts. Kolom ini menggunakan tipe data TIMESTAMP, sehingga kita juga bisa menformat bagian time-nya.

Apabila anda familiar dengan PHP, fungsi DATE_FORMAT() MySQL ini mirip dengan fungsi date() pada PHP.

Dalam tutorial belajar MySQL kali ini kita telah membahas cara penggunaan fungsi DATE_FORMAT. Dengan fungsi ini, tipe data DATE dan TIMESTAMP MySQL bisa ditampilkan sesuai kebutuhan.