Query Dasar Database MySQL - I

 

Query Dasar MySQL:

• Penulisan Query MySQL

Menjalankan Perintah (query) MySQL

Setiap perintah, atau disebut “query” di dalam MySQL harus diakhiri dengan tanda titik koma “;” dan akan dieksekusi setelah tombol Enter ditekan. Selama query MySQL belum diakhiri dengan “;” maka itu dianggap masih dalam satu perintah.

Ketika menjalankan sebuah perintah MySQL (query), query tersebut akan dikirim dari MySQL Client ke MySQL Server untuk di proses, setelah proses selesai, hasilnya akan dikirim kembali ke MySQL Client. Agar lebih mudah memahami query MySQL, kita akan langsung praktek beberapa query sederhana. 

Setelah masuk ke dalam MySQL Client (ditandai dengan awalan mysql> pada jendela cmd), akan mencoba beberapa perintah MySQL sederhana. Ketikkan perintah berikut: SELECT NOW(); lalu akhiri dengan Enter pada keyboard.

1 2 3 4 5 6 7 8

mysql> SELECT NOW();   +---------------------+ | NOW()               | +---------------------+ | 2014-11-13 09:21:08 | +---------------------+ 1 row in set (0.69 sec)

Query diatas adalah untuk menampilkan tanggal dan waktu saat ini dengan fungsi NOW(). Query tersebut akan menghasilkan hasil yang berbeda-beda tergantung saat menjalankannya.

Selain hasil dalam bentuk tabel, hampir setiap perintah query, MySQL juga akan menampilkan banyaknya baris yang dipengaruhi dan lamanya waktu eksekusi. 

Query lainnya mencoba untuk menampilkan nama user yang sedang aktif dan versi MySQL Server yang digunakan pada saat ini. Untuk menampilkan keterangan ini, MySQL menyediakan fungsi USER() dan VERSION()

1 2 3 4 5 6 7 8

mysql> SELECT NOW(),USER(),VERSION();   +---------------------+----------------+------------+ | NOW()               | USER()         | VERSION()  | +---------------------+----------------+------------+ | 2014-11-13 09:21:37 | root@localhost | 5.6.21-log | +---------------------+----------------+------------+ 1 row in set (0.11 sec)

Dapat dilihat dari contoh query tersebut, untuk setiap fungsi dipisahkan dengan tanda koma “,”.

Penulisan perintah (query) MySQL juga tidak harus dalam satu baris. Misalnya  bisa menjalankan query berikut:

1 2 3 4 5 6 7 8 9 10

mysql> SELECT NOW(),     -> USER(),     -> VERSION();   +---------------------+----------------+------------+ | NOW()               | USER()         | VERSION()  | +---------------------+----------------+------------+ | 2014-11-13 09:22:50 | root@localhost | 5.6.21-log | +---------------------+----------------+------------+ 1 row in set (0.00 sec)

Setelah fungsi NOW() pertama, tekan Enter untuk pindah baris, lalu ketikkan perintah sambungannya. Selama belum mengakhiri perintah tersebut dengan “;”, maka MySQL menganggap baris berikutnya adalah sambungan dari baris sebelumnya. Pemisahan perintah seperti diatas akan sangat berguna jika menuliskan query yang panjang, sehingga lebih mudah dibaca. Tanda mysql> akan berubah menjadi -> selama belum mengakhiri query tersebut dengan tanda titik koma “;”.

Jika telah membuat query MySQL, namun memutuskan untuk membatalkannya, dapat dilakukan dengan kode “\c”. Contoh querynya:

1 2 3 4 5

mysql> SELECT NOW(), -> USER(), -> \c   mysql>

Dapat dihatikan bahwa tanda -> akan kembali menjadi mysql> yang menandakan MySQL telah siap untuk perintah yang baru.

Selain menggunakan “;”, query MySQL juga akan mengeksekusi perintah juga diakhiri dengan tanda “\g”.

1 2 3 4 5 6 7 8

mysql> SELECT NOW(),USER(),VERSION()\g   +---------------------+----------------+------------+ | NOW()               | USER()         | VERSION()  | +---------------------+----------------+------------+ | 2014-11-13 09:23:19 | root@localhost | 5.6.21-log | +---------------------+----------------+------------+ 1 row in set (0.00 sec)

---

Cara Merubah Tampilan output MySQL

Untuk perintah MySQL (query MySQL) yang menghasilkan output yang panjangnya melebihi layar cmd, akan sulit dalam membacanya. MySQL menyediakan cara untuk merubah tampilan tabel menjadi baris.

Untuk merubah tampilan output MySQL menjadi baris, tambahkan tanda “\G” (huruf G besar, bukan g kecil) setelah perintah query. Berikut adalah contoh penggunaannya dalam MySQL:

1 2 3 4 5 6 7

mysql> SELECT NOW(),USER(),VERSION()\G   *************************** 1. row ***************************     NOW(): 2014-11-13 09:23:41    USER(): root@localhost VERSION(): 5.6.21-log 1 row in set (0.00 sec)

Aturan Penulisan huruf BESAR dan kecil dalam MySQL

Konsep penggunaan huruf besar dan huruf kecil dalam MySQL akan berbeda tergantung saat penggunaannya.

MySQL tidak membedakan penulisan huruf besar maupun kecil (case insensitive) dalam penulisan fungsi dan identifier. Sebagai contoh, ketiga query ini akan menghasilkan output yang sama (kecuali header dari tabel):

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

mysql> SELECT Version();   +------------+ | Version()  | +------------+ | 5.6.21-log | +------------+ 1 row in set (0.00 sec)   mysql> SELECT VERSION();   +------------+ | VERSION()  | +------------+ | 5.6.21-log | +------------+ 1 row in set (0.00 sec)   mysql> select version();   +------------+ | version()  | +------------+ | 5.6.21-log | +------------+ 1 row in set (0.00 sec)

Namun untuk penulisan nama database dan nama tabel, MySQL akan mengikuti sistem operasi dimana MySQL Server berjalan.

Untuk Sistem Operasi Windows tidak case sensitive, tapi pada Linux, kedua database tersebut case sensitive.

Karena hal ini, ada baiknya kita membiasakan menggunakan kesepakatan dalam penamaan. Disarankan untuk menggunakan selalu huruf kecil dalam penulisan database, tabel dan variabel dalam MySQL, sehingga perbedaan huruf tidak akan menjadi masalah pada kemudian hari jika MySQL Server pindah sistem operasi.

Perhatikan penggunaan tanda

Dalam beberapa kasus, sebuah query bisa saja menggunakan tanda baca selain angka dan huruf seperti : “,” (koma), “\” (forward slash/garis miring depan),”’” (tanda petik), dan “ “ (spasi). Misalnya untuk 123 dan ‘123’, 12e + 14 dan 12e+14 akan memiliki arti berbeda dalam MySQL. Jika menemui error, mungkin penggunaan “tanda” ini adalah masalahnya.

• Membuat dan Menghapus Database

Cara Membuat database MySQL

untuk membuat database, format penulisan querynya adalah:

CREATE DATABASE [IF NOT EXISTS] nama_database

Jika ingin membuat sebuah database mahasiswa, maka querynya adalah:

mysql> CREATE DATABASE mahasiswa;

Query OK, 1 row affected (0.00 sec)

Tambahan query  [IF NOT EXISTS] digunakan untuk membuat MySQL tidak menampilkan pesan error jika database tersebut telah ada sebelumnya dalam database.

Jika menjalankan lagi query untuk membuat database mahasiswa, MySQL akan menampilkan pesan error.

mysql> CREATE DATABASE mahasiswa;

ERROR 1007 (HY000): Can't create database 'mahasiswa';

database exists




Pesan error ini berguna untuk mengidentifikasi kesalahan, namun apabila  membuat kode query yang panjang untuk dieksekusi secara keseluruhan , pesan error akan menyebabkan query  berhenti diproses.

Format [IF NOT EXISTS] akan membuat database jika database itu belum ada sebelumnya. Jika sudah ada,  query CREATE DATABASE tidak akan menghasilkan apa-apa (database yang lama tidak akan tertimpa).

mysql> CREATE DATABASE IF NOT EXISTS mahasiswa;

 

Query OK, 1 row affected, 1 warning (0.00 sec)




Cara Melihat Daftar Database MySQL

Di dalam MySQL Server, biasanya akan terdapat beberapa database, tergantung kebutuhan. Untuk melihat seluruh database yang berada pada MySQL Server, gunakan query:

SHOW DATABASES; mysql> SHOW DATABASES;   +--------------------+ | Database           | +--------------------+ | information_schema | | mahasiswa          | | mysql              | | performance_schema | | test               | +--------------------+ 5 rows in set (0.00 sec) Terlihat bahwa MySQL Server telah berisi beberapa database bawaan, selain database mahasiswa yang dibuat sebelumnya. Cara Memilih dan Menggunakan Database MySQL Memilih sebuah database yang akan digunakan, format query untuk memilih database ini adalah : USE nama_database; nama_database adalah database yang akan digunakan. Jika  ingin menggunakan database mahasiswa, maka querynya adalah ; mysql> USE mahasiswa;   Database changed Selanjutnya setiap query pembuatan tabel misalnya, akan membuat sebuah tabel didalam database terpilih, dalam hal ini database mahasiswa. Cara Menghapus Database MySQL Jika database sudah tidak digunakan lagi, maka dapat menghapusnya. Proses penghapusan ini akan menghapus database, termasuk seluruh tabel dan isi dari tabel tersebut. Sebuah database yang telah dihapus tidak dapat ditampilkan kembali. Hal ini harus yakinkan bahwa database tersebut memang tidak akan digunakan lagi. Format query untuk menghapus database: DROP DATABASE [IF EXISTS] database_name; Sama seperti query pada pembuatan database, pilihan [IF EXISTS] digunakan untuk menghilangkan pesan error jika seandainya database tersebut memang tidak ada. Jika ingin menghapus database mahasiswa, contoh querynya adalah : mysql> DROP DATABASE mahasiswa;   Query OK, 0 rows affected (0.01 sec)

Jika kita berusaha menghapus database mahasiswa lagi (yang memang sudah tidak ada), akan muncul pesan error :

mysql> DROP DATABASE mahasiswa;

 

ERROR 1008 (HY000): Can't drop database 'mahasiswa';

database doesn't exist




Dengan menggunakan perintah opsional [IF EXISTS], pesan error tidak tampi :

mysql> DROP DATABASE IF EXISTS mahasiswa;

 

Query OK, 0 rows affected, 1 warning (0.00 sec)

Karena query SQL menggunakan bahasa inggris, harus perhatikan kesalahan untuk pengetikkan kata singular (tunggal) dan plural (jamak). Misalnya untuk melihat database, querynya adalah SHOW DATABASES, tetapi jika menjalankan query SHOW DATABASE; ini akan menyebabkan error. Karena seharusnya adalah DATABASES, bukan DATABASE. Juga seharusnya adalah IF EXISTS, bukan IF EXIST (Penggunaan huruf S diakhir kata).

mysql> SHOW DATABASE;   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 'DATABASE' at line 1

• Membuat dan Menghapus Tabel

Cara Membuat Tabel MySQL

Langkah pertama sebelum membuat tabel adalah membuat dan memilih database yang akan digunakan. pada tutor ini melanjutkan menggunakan database mahasiswa. Jika belum membuat dan memilih database mahasiswa, silahkan ketik query berikut:

1 2 3 4 5

mysql> CREATE DATABASE mahasiswa; Query OK, 1 row affected (0.00 sec)   mysql> USE mahasiswa; Database changed

Format dasar query untuk membuat tabel adalah:

CREATE TABLE [IF NOT EXISTS] nama_tabel (daftar_kolom) [type=tipe_tabel]

• Query opsional  [IF NOT EXISTS] sama seperti pada query pembuatan database, digunakan untuk membuat MySQL tidak menampilkan pesan error jika tabel tersebut telah ada sebelumnya dalam database.

• Nama_tabel adalah nama dari tabel yang ingin dibuat.

• Daftar_kolom berisi definisi dari kolom-kolom yang akan buat untuk database ini. Lebih lanjut tentang jenis-jenis kolom akan dibahas dalam tutorial selanjutnya.

• Tipe_tabel adalah query opsional dimana dapat mendefinisikan tipe tabel untuk tabel yang akan digunakan, seperti MyISAM maupun InnoDB. Lebih lanjut tentang tipe tabel akan dibahas pada tutorial berikutnya.

Sebagai contoh,  akan membuat tabel berdasarkan data mahasiswa berikut ini :

NIM	Nama	Umur	Tempat Lahir	Jurusan
101401030	Alex Ferdinand	21	Padang	Ilmu Komputer
120502045	Joko Suprianto	19	Jakarta	Manajemen
110901033	Susi Sulastri	20	Medan	Kedokteran
100301021	Suparman	21	Jakarta	Teknik Elektro
121401074	Tania Mutia	19	Padang	Ilmu Komputer
110501023	Aprilia Susanti	20	Pekanbaru	Manajemen

Dari tabel tersebut  dapat simpulkan bahwa tabel akan terdiri dari 5 buah kolom:

• Kolom NIM (Nomor Induk Mahasiswa), walaupun isi tabel ini berupa angka, namun angka disini bukan dimaksudkan untuk operasi matematis, sehingga  akan lebih cocok jika menganggapnya kumpulan karakter atau huruf, maka  akan membuatnya dengan tipe tabel string. Dalam MySQL tipe string didefinisikan sebagai char.

• Kolom Nama berisi karakter huruf, maka akan dibuat dengan tipe data string.

• Kolom Umur akan berisi angka-angka, dan mungkin saja akan menggunakan umur untuk keperluan matematis, maka tipe data yang akan digunakan adalah integer.

• Kolom Tempat Lahir berisi kumpulan karakter huruf, maka akan dibuat dengan tipe data string.

• Kolom Jurusan berisi kumpulan karakter huruf, maka akan dibuat dengan tipe data string.

Sehingga query untuk membuat tabel data_mahasiswa seperti diatas adalah:

1 2 3 4

mysql> CREATE TABLE data_mahasiswa ( nim char(9), nama char(50), umur int, tempat_lahir char(50), jurusan char (30) );   Query OK, 0 rows affected (0.08 sec)

Penjelasan query:

• Dengan query diatas, telah dibuat sebuah tabel dengan nama data_mahasiswa, terdiri dari 5 kolom: nim, nama, umur, tempat_lahir, dan jurusan.

• Setelah nama kolom,  didefinisikan jenis dari kolom tersebut.

• Untuk kolom yang berisi huruf (string), dalam MySQL dapat didefinisikan sebagai char. Angka di dalam kurung adalah jumlah karakter maksimal yang dapat ditampung, misalnya untuk kolom nama,  menginstruksikan MySQL untuk menyediakan maksimal sebanyak 50 karakter untuk kolom nama.

• Untuk kolom yang berisi angka, MySQL menyediakan beberapa tipe yang dapat dipilih, untuk contoh diatas,  memilih integer, disingkat menjadi int.

• Untuk setiap kolom, dalam query CREATE TABEL, dipisahkan dengan tanda koma, dan seluruh definisi kolom harus berada diantara tanda kurung.

Cara Melihat Daftar Tabel dalam MySQL

Untuk melihat seluruh tabel yang ada pada database aktif saat ini, format querynya adalah:

SHOW TABLES;

hasil eksekusi querynya adalah sebagai berikut:

1 2 3 4 5 6 7 8

mysql> SHOW TABLES;   +---------------------+ | Tables_in_mahasiswa | +---------------------+ | data_mahasiswa      | +---------------------+ 1 row in set (0.07 sec)

Cara Melihat Struktur Tabel MySQL

Untuk melihat struktur dari sebuah tabel, seperti nama kolom yang tersedia, dan tipenya, dapat dilihat dengan format query:

DESCRIBE nama_tabel;

hasil dari DESCRIBE data_mahasiswa, adalah:

1 2 3 4 5 6 7 8 9 10 11 12

mysql> DESCRIBE data_mahasiswa;   +--------------+----------+------+-----+---------+-------+ | Field        | Type     | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+-------+ | nim          | char(9)  | YES  |     | NULL    |       | | nama         | char(50) | YES  |     | NULL    |       | | umur         | int(11)  | YES  |     | NULL    |       | | tempat_lahir | char(50) | YES  |     | NULL    |       | | jurusan      | char(30) | YES  |     | NULL    |       | +--------------+----------+------+-----+---------+-------+ 5 rows in set (0.01 sec)

Dari hasil query, dapat terlihat semua kolom serta tipe dari setiap kolom tersebut. 

Cara Menghapus Tabel MySQL

Format query untuk menghapus tabel adalah:

DROP TABLE[IF EXISTS] table_name [, table_name,...]

Query opsional [IF EXISTS] digunakan untuk menghilangkan pesan error jika tabel yang akan dihapus memang sudah tidak ada.

Dari format query DROP TABLE, dapat dilihat bahwa dalam satu query, dapat menghapus beberapa tabel sekaligus.

Untuk menghapus tabel data_mahasiswa, querynya adalah:

1 2 3

mysql> DROP TABLE data_mahasiswa;   Query OK, 0 rows affected (0.05 sec)

• Tipe Data Numerik

Tipe Data Integer MySQL: Tinyint , Smallint, Mediumint, Integer (Int), dan Bigint

Integer adalah tipe data untuk angka bulat (misalnya: 1,6, 59, -533, 1449). MySQL menyediakan beberapa tipe data untuk integer, perbedaannya lebih kepada jangkauan yang juga berpengaruh terhadap ukuran tipe data tersebut.

Jangkauan serta ukuran penyimpanan tipe data integer dalam MySQL dapat dilihat dari tabel dibawah ini:

Tipe Data	Jangkauan SIGNED	Jangkauan UNSIGNED	Ukuran
TINYINT	-128 to 127	0 to 255	1 byte
SMALLINT	-32,768 to 32,767	0 to 65,535	2 bytes
MEDIUMINT	-8,388,608 to 8,388,607	0 to 16,777,215	3 bytes
INT	-2,147,483,648 to 2,147,483,647	0 to 4,294,967,295	4 bytes
BIGINT	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	0 to 18,446,744,073,709,551,615	8 bytes

Pemilihan tipe data ini tergantung akan kebutuhan data. Misalkan untuk nomor urut absensi pada suatu kelas yang memiliki maksimal 30 orang, tipe data TINYINT sudah mencukupi. Namun jika bermaksud membuat absensi seluruh rakyat indonesia, tipe data TINYINT sampai dengan MEDIUMINT tidak akan mencukupi. maka harus memilih INT atau BIGINT.

Cara Penulisan Tipe Data Integer MySQL

Format query untuk tipe data integer adalah:

INT[(M)] [UNSIGNED] [ZEROFILL]

Setiap tipe data integer dapat di definisikan sebagai UNSIGNED, dimana mengorbankan nilai negatif, untuk mendapatkan jangkauan nilai positif yang lebih tinggi.

Dalam deklarasi tipe data integer, nilai M adalah jumlah digit yang disediakan untuk menampilkan data. Misalnya mendefiniskan suatu kolom dengan INT(5), jika diinput angka 102, maka MySQL akan menambahkan spasi sebanyak 2 buah di depan angka, agar tampilan data menjadi 5 digit (istilah pemogramannya : padding left).

Nilai M tidak mempengaruhi nilai maksimal dari integer. Jika angka yang diinputkan melebihi digit M, MySQL akan tetap menampilkan hasilnya (selama masih dalam jangkauan tipe data tersebut). Jika deklarasikan INT(4), nilai maksimal tetap 2,147,483,647 bukan 9999.

Selain opsional query UNSIGNED, pendeklarasian integer juga terdapat opsional atribut ZEROFILL. ZEROFILL ini berhubungan dengan nilai M, yang akan mengisi angka 0 pada bagian kiri nilai yang diinput alih-alih spasi.

Contoh query pembuatan tabel dengan deklarasi integer :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

mysql> CREATE TABLE contoh_int (mini TINYINT,  kecil SMALLINT UNSIGNED, sedang MEDIUMINT(4) ZEROFILL, biasa INT(4) UNSIGNED, besar BIGINT(6) UNSIGNED ZEROFILL);   Query OK, 0 rows affected (0.06 sec)   mysql> DESC contoh_int; +--------+--------------------------------+------+-----+---------+-------+ | Field  | Type                           | Null | Key | Default | Extra | +--------+--------------------------------+------+-----+---------+-------+ | mini   | tinyint(4)                     | YES  |     | NULL    |       | | kecil  | smallint(5) unsigned           | YES  |     | NULL    |       | | sedang | mediumint(4) unsigned zerofill | YES  |     | NULL    |       | | biasa  | int(4) unsigned                | YES  |     | NULL    |       | | besar  | bigint(6) unsigned zerofill    | YES  |     | NULL    |       | +--------+--------------------------------+------+-----+---------+-------+ 5 rows in set (0.21 sec)

Dari query DESC dapat melihat kolom mini dan kecil, secara otomatis akan bersisi nilai M, walaupun dalam deklarasi pembuatan tabel tidak menyatakannya secara langsung. Nilai ini didapat dari jangkauan terbesar tipe data tersebut

Jika kita inputkan angka 122 kedalam seluruh kolom, hasilnya adalah sebagai berikut:

mysql> INSERT INTO contoh_int values ((122), (122), (122),

(122), (122));

 

Query OK, 1 row affected (0.07 sec)

 

mysql> SELECT * FROM contoh_int;

 

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

| mini | kecil | sedang | biasa | besar  |

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

|  122 |   122 |   0122 |   122 | 000122 |

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

1 row in set (0.00 sec)

Tipe Fixed Point MySQL: DECIMAL

Tipe data fixed point adalah tipe data angka pecahan (desimal), dimana jumlah angka pecahan (angka di belakang koma) sudah di tentukan dari awal. Format penulisan query fixed point adalah:

DECIMAL [(M[,D])] [UNSIGNED] [ZEROFILL]

DECIMAL merupakan kata kunci untuk mendefiniskan suatu kolom sebagai fixed point.

Besar dari tipe data fixed point ini tergantung dari opsional query [M,D] dimana M adalah total jumlah digit keseluruhan, dan D adalah jumlah digit dibekang koma (pecahan). Contohnya DEC [6,2] akan mendefiniskan suatu kolom agar memuat 6 digit angka, dengan 4 angka di depan koma, dan 2 digit angka di belakang koma.

Deklarasi	Jangkauan
DECIMAL (4,1)	-999,9 to 999,9
DECIMAL (6,2)	-9999,99 to 9999,99
DECIMAL (3,2)	-9,99 to 9,99
DECIMAL (8,2)	-999999,99 to 999999,99

Maksimal nilai untuk M adalah 65, dan maksimal nilai D adalah 30. Dengan syarat, nilai D tidak boleh lebih besar dari nilai M. Jika tidak menyertakan M dan D dalam mendefinisikan suatu kolom DECIMAL, maka secara sistem M akan di set 10. Dan D default 0. Tipe data DECIMAL ini cocok digunakan untuk kolom yang difungsikan untuk menampung nilai uang.

Opsional query UNSIGNED jika disertakan hanya semata-mata membuang nilai negatif dari DECIMAL, tanpa memperbesar jangkauannya. Opsional query ZEROFILL sama seperti integer, akan mengisi angka 0 pada bagian kiri angka.

Contoh pendefinisian tabel dengan kolom DECIMAL:

mysql> CREATE TABLE contoh_dec (satuan DECIMAL(3,2), puluhan DECIMAL(4,2),

ribuan DECIMAL(5,2), normal DECIMAL, cantik DECIMAL(8,2) ZEROFILL);

 

Query OK, 0 rows affected (0.13 sec)

 

mysql> DESCRIBE contoh_dec;

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

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

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

| satuan  | decimal(3,2)                   | YES  |     | NULL    |       |

| puluhan | decimal(4,2)                   | YES  |     | NULL    |       |

| ribuan  | decimal(5,2)                   | YES  |     | NULL    |       |

| normal  | decimal(10,0)                  | YES  |     | NULL    |       |

| cantik  | decimal(8,2) unsigned zerofill | YES  |     | NULL    |       |

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

5 rows in set (0.08 sec)

Perhatikan kolom normal, bahwa nilai DECIMAL di deklarasikan sebagai DECIMAL(10,0), ini adalah

default MySQL jika idak menuliskan jumlah digit.

1 2 3 4 5 6 7 8 9 10 11 12

mysql> INSERT INTO contoh_dec values ((1.3), (55.32), (523.77), (7832.3), (150000.45));   Query OK, 1 row affected, 1 warning (0.07 sec)   mysql> SELECT * FROM contoh_dec; +--------+---------+--------+--------+-----------+ | satuan | puluhan | ribuan | normal | cantik    | +--------+---------+--------+--------+-----------+ |   1.30 |   55.32 | 523.77 |   7832 | 150000.45 | +--------+---------+--------+--------+-----------+ 1 row in set (0.00 sec)

Khusus untuk kolom normal,  perhatikan karena deklarasinya adalah DECIMAL (10,0), maka seluruh digit dibelakang koma dari inputan kita akan dihilangkan.

Tipe Floating Point : FLOAT dan DOUBLE

Jika pada tipe fixed point  mendefiniskan suatu kolom dengan nilai pecahan yang tetap, untuk tipe floating point, nilai pecahan yang dapat diinput bisa berbeda-beda. Untuk tipe floating point, MySQL menyediakan 2 jenis tipe data, yaitu FLOAT dan DOUBLE. Perbedaan keduanya terletak pada presisi (ketelitian) pembulatan. FLOAT menggunakan single-precision, sedangkan DOUBLE menggunakan  double-precision.

Tipe Data	Jangkauan	Ukuran
FLOAT	-3.402823466E+38 to 3.402823466E+38	4 bytes
DOUBLE	-1.7976931348623157E+308 to 1.7976931348623157E+308	8 bytes

Untuk tipe data FLOAT dan DOUBLE, format querynya adalah:

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

Sama seperti pada tipe data DECIMAL, nilai opsional query [M, D] dimana M adalah total jumlah digit keseluruhan, dan D adalah jumlah digit dibekang koma (pecahan). Maksimal nilai M dan D akan berbeda pada tiap komputer, tergantung kemampuan prosesor mengolah tipe data floating point. FLOAT biasanya akan akurat untuk penyimpanan sekitar 7 angka desimal, sedangkan DOUBLE mencapai 15 angka desimal.

Opsional query UNSIGNED jika di nyatakan, akan menghilangkan kemampuan tipe data untuk meyimpan angka negatif.

Opsional query ZEROFILL sama seperti integer, akan mengisi angka 0 pada bagian kiri angka.

Contoh pendefinisan tipe data floating point:

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> CREATE TABLE contoh_float (satuan FLOAT(3,2), puluhan FLOAT(4,2), ribuan FLOAT(5,2), positif DOUBLE ZEROFILL UNSIGNED, cantik DOUBLE(8,2) ZEROFILL);   Query OK, 0 rows affected (1.21 sec)   mysql> DESCRIBE contoh_float; +---------+-------------------------------+------+-----+---------+-------+ | Field   | Type                          | Null | Key | Default | Extra | +---------+-------------------------------+------+-----+---------+-------+ | satuan  | float(3,2)                    | YES  |     | NULL    |       | | puluhan | float(4,2)                    | YES  |     | NULL    |       | | ribuan  | float(5,2)                    | YES  |     | NULL    |       | | positif | double unsigned zerofill      | YES  |     | NULL    |       | | cantik  | double(8,2) unsigned zerofill | YES  |     | NULL    |       | +---------+-------------------------------+------+-----+---------+-------+ 5 rows in set (0.04 sec)   mysql> INSERT INTO contoh_float values ((1.3), (55.32), (523.77), (7832.3), (150000.45));   Query OK, 1 row affected (0.15 sec)   mysql> SELECT * FROM contoh_float; +--------+---------+--------+------------------------+-----------+ | satuan | puluhan | ribuan | positif                | cantik    | +--------+---------+--------+------------------------+-----------+ |   1.30 |   55.32 | 523.77 | 00000000000000007832.3 | 150000.45 | +--------+---------+--------+------------------------+-----------+ 1 row in set (0.00 sec)

• Tipe Data Huruf (String)

MySQL menyediakan beberapa tipe data string: CHAR, VARCHAR, BINARY, VARBINARY, TEXT dan BLOB. Perbedaan dari tipe data ini adalah dari ukuran, cara penyimpanan, dan dukungan case-sensitif (perbedaan huruf besar dan kecil).

Tipe data CHAR dan VARCHAR MySQL

Tipe data CHAR dan VARCHAR adalah tipe data karakter (string) yang akan sering digunakan.

Format query tipe data CHAR dan VARCHAR:

CHAR [(M)] VARCHAR [(M)]

Dimana M adalah jumlah karakter yang akan dialokasikan oleh MySQL. Misalkan nilai M=5, maka MySQL menyediakan 5 karakter untuk kolom tersebut. Nilai maksimal M adalah 255 karakter untuk CHAR, dan 65,535 karakter untuk VARCHAR. Jika nilai M tidak di nyatakan, nilai defaultnya M=1.

Perbedaan antara CHAR dan VARCHAR adalah dari cara MySQL mengalokasikan ukuran penyimpanan data yang diinput kedalam kolom tersebut. Contohnya, jika mendefiniskan sebuah tabel dengan kolom bertipe CHAR(5), walaupun huruf atau karakter yang diinputkan hanya 1 karakter, MySQL tetap menyimpan kolom tersebut untuk 5 karakter. Namun jika didefiniskan sebagai VARCHAR(5), dan menginput data dengan jumlah karakter 2, maka ukuran penyimpanan hanya akan menggunakan 2 karakter, sehingga VARCHAR lebih fleksibel dan efisien.

Tetapi untuk fleksibilitas ini, tipe VARCHAR memerlukan proses tambahan untuk menyimpan ukuran dari masing-masing data. Sehingga VARCHAR akan sedikit lebih lambat bila dibandingkan CHAR. Walaupun demikian, proses ini hampir tidak terdeteksi, bebas menggunakan CHAR atau VARCHAR untuk tipe data string.

Contoh perbedaan antara CHAR dan VARCHAR dapat kita lihat dari tabel dibawah ini:

Data	CHAR(5)	Ukuran Peyimpanan	VARCHAR(5)	Ukuran Peyimpanan
' '	'     '	5 byte	' '	1 byte
'du'	'du    '	5 byte	'du'	3 byte
'dunia'	'dunia'	5 byte	'dunia'	6 byte
'duniailkom'	'dunia'	5 byte	'dunia'	6 byte

Dari tabel diatas, dapat melihat bahwa ukuran penyimpanan untuk VARCHAR berubah-ubah tergantung data yang diinput. Namun sebenarnya VARCHAR membutuhkan 1 byte tambahan untuk menyimpan panjang data. Jika kita definiskan VARCHAR(5) dan menginputkan ‘dunia’, maka akan membutuhkan 5+1 byte. Berbeda dengan CHAR(5) yang tidak membutuhkan tambahan byte.

Jadi, kebutuhan akan tipe data antara CHAR dan VARCHAR bergantung kepada seperti apa data yang akan diinput. Tipe data CHAR cocok digunakan untuk kolom yang memiliki jumlah karakter tetap, misalnya untuk nomor KTP, ID pelanggan dan NIM mahasiswa. Sedangkan tipe data VARCHAR akan lebih cocok digunakan untuk kolom yang berisi data yang berbeda-beda ukuran, seperti nama, alamat dan kota.

Baik CHAR maupun VARCHAR data yang diinput akan disimpan secara case insensitif, dimana huruf besar dan kecil tidak dibedakan.

Untuk tipe data CHAR, jika menginputkan data dengan spasi dibelakangnya, misalnya ‘aku   ’ (terdapat 3 spasi setelah ‘aku’), maka sewaktu menampilkan data, spasi tersebut akan otomastis dihapus oleh MySQL, menjadi‘aku’. Pertimbangkan menggunakan VARCHAR jika hal ini dibutuhkan, dimana VARCHAR akan tetap menampilkan ‘aku   ’.

Contoh query membuat tabel dengan tipe data CHAR dan VARCHAR :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

mysql> CREATE TABLE contoh_cha (cha CHAR(5), varcha VARCHAR(5)); Query OK, 0 rows affected (0.06 sec)   mysql> INSERT INTO contoh_cha values ('a  ','a  '); Query OK, 1 row affected (0.05 sec) mysql> INSERT INTO contoh_cha values ('dunia','dunia'); Query OK, 1 row affected (0.05 sec) mysql> INSERT INTO contoh_cha values ('duniailkom','duniailkom'); ERROR 1406 (22001): Data too long for column 'cha' at row 1   mysql> SELECT * FROM contoh_cha; +-------+--------+ | cha   | varcha | +-------+--------+ | a     | a      | | dunia | dunia  | +-------+--------+ 2 rows in set (0.00 sec)

Tipe Data BINARY dan VARBINARY dalam MySQL

Pada dasarnya tipe data BINARY dan VARBINARY sama dengan CHAR dan VARCHAR, perbedaannya hanya pada struktur peyimpanan dalam MySQL, dimana tipe data binary akan disimpan secara biner (bit per bit), bukan secara karakter seperti CHAR. Sederhananya, hal ini akan berefek pada case-sensitif data (perbedaan penggunaan huruf besar dan huruf kecil).

Pada tipe data CHAR, jika  menyimpan data ‘A’, maka secara internal MySQL akan menyimpannya sebagai karakter ‘A’, dimana ‘A’ akan sama dengan ‘a’. Namun untuk tipe data BINARY, ‘A’ akan disimpan sebagai bit dari ‘A’, yaitu 65, dan akan berbeda dengan ‘a’, dimana nilai binernya adalah 97.

Format query tipe data BINARY dan VARBINARY:

BINARY [(M)] VAR BINARY [(M)]

Dimana sama seperti tipe data CHAR dan VARCHAR, M adalah jumlah karakter yang akan dialokasikan oleh MySQL. Misalkan nilai M adalah 5, maka MySQL menyediakan 5 karakter untuk kolom tersebut. Nilai M maksimal 255 karakter untuk BINARY, dan 65,535 karakter untuk VARBINARY. Jika nilai M tidak di nyatakan, nilai defaultnya adalah 1.

BINARY akan disimpan dalam ukuran yang tetap, sedangkan VARBINARY akan berubah sesuai ukuran data (sama dengan perbedaan antara CHAR dan VARCHAR)

Contoh query membuat tabel dengan tipe data BINARY dan VARBINARY :

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

mysql> CREATE TABLE contoh_bin (bin BINARY(5), varbin VARBINARY(5)); Query OK, 0 rows affected (0.08 sec)   mysql> INSERT INTO contoh_bin values ('dunia','dunia'); Query OK, 1 row affected (0.04 sec)   mysql> SELECT * FROM contoh_bin; +-------+--------+ | bin   | varbin | +-------+--------+ | dunia | dunia  | +-------+--------+ 1 row in set (0.00 sec)   mysql> SELECT * FROM contoh_bin where bin='dunia'; +-------+--------+ | bin   | varbin | +-------+--------+ | dunia | dunia  | +-------+--------+ 1 row in set (0.00 sec)   mysql> SELECT * FROM contoh_bin where bin='DUNIA'; Empty set (0.00 sec)

Perhatikan bahwa pada query terakhir, tidak menampilkan hasil apa-apa, karena dalam BINARY, ‘dunia’ tidak sama dengan ‘DUNIA’.

Tipe Data TEXT MySQL

Untuk data string yang lebih besar, MySQL menyediakan tipe data TEXT, yang terdiri dari : TINYTEXT, TEXT, MEDIUMTEXT, dan LONGTEXT. Maksimal ukuran masing-masing tipe data dapat dilihat dari tabel dibawah ini:

Tipe Data	Ukuran Maksimum	Jumlah Karakter Maksimum
TINYTEXT	255 byte	255
TEXT	65.535 byte (64 KB)	6.5535
MEDIUMTEXT	16.777.215 byte (16MB)	16.777.215
LONGTEXT	4.294.967.295 (4GB)	4.294.967.295

Setiap tipe data text disimpan berdasarkan berapa data yang diinput, sama seperti VARCHAR, dimana jika mendefinisikan suatu kolom sebagai LONGTEXT, dan hanya berisi 100 karakter, ukuran penyimpanan yang digunakan sekitar 100 byte, bukan 4GB.

Contoh query membuat tabel dengan tipe data TEXT :

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

mysql> CREATE TABLE contoh_text (tin TINYTEXT, tex TEXT, lon LONGTEXT); Query OK, 0 rows affected (0.05 sec)   mysql> DESC contoh_text; +-------+----------+------+-----+---------+-------+ | Field | Type     | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | tin   | tinytext | YES  |     | NULL    |       | | tex   | text     | YES  |     | NULL    |       | | lon   | longtext | YES  |     | NULL    |       | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.09 sec)   mysql> INSERT INTO contoh_text values ('duniailkom', 'duniailkom','duniailkom.com'); Query OK, 1 row affected (0.04 sec)   mysql> SELECT * FROM contoh_text; +------------+------------+----------------+ | tin        | tex        | lon            | +------------+------------+----------------+ | duniailkom | duniailkom | duniailkom.com | +------------+------------+----------------+ 1 row in set (0.00 sec)

Tipe Data BLOB MySQL

Tipe data BLOB adalah tipe data versi binary dari TEXT, dimana karakter akan disimpan dalam bit. Dan untuk karakter huruf, huruf besar dan kecil akan dibedakan (‘A’ tidak sama dengan ‘a’). Sama seperty TEXT, BLOB juga memiliki beberapa tipe : TINY BLOB, BLOB, MEDIUM BLOB, dan LONGBLOB.

Ukuran serta jangkauan dari BLOB sama seperti TEXT, seperti pada tabel berikut ini:

Tipe Data	Ukuran Maksimum	Jumlah Karakter Maksimum
TINYBLOB	255 byte	255
BLOB	65.535 byte (64 KB)	65.535
MEDIUMBLOB	16.777.215 byte (16MB)	16.777.215
LONGBLOB	4.294.967.295 (4GB)	4.294.967.295

Karena sifatnya yang tersimpan secara binary,tipe data BLOB dapat digunakan untuk menyimpan data multimedia, seperti gambar dan musik kedalam tabel MySQL.

mysql> CREATE TABLE contoh_blob (tin TINYBLOB, blo BLOB,

lon LONGBLOB);

Query OK, 0 rows affected (0.14 sec)

 

mysql> DESCRIBE contoh_blob;

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

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

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

 | tin   | tinyblob | YES  |     | NULL    |       |

 | blo   | blob     | YES  |     | NULL    |       |

 | lon   | longblob | YES  |     | NULL    |       |

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

 3 rows in set (0.01 sec)

 

mysql> INSERT INTO contoh_blob values ('duniailkom',

'duniailkom','duniailkom.com');

Query OK, 1 row affected (0.05 sec)

 

mysql> SELECT * FROM contoh_blob;

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

 | tin        | blo        | lon            |

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

 | duniailkom | duniailkom | duniailkom.com |

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

 1 row in set (0.01 sec)

• Tipe Data Date (Tanggal)

Jenis tipe data DATE dalam MySQL

MySQL memiliki beberapa format untuk tipe data date. Format tipe data date tersebut adalah: DATE, TIME, DATETIME, TIMESTAMP, dan YEAR. Perbedaan dari tipe-tipe tersebut terletak pada format penyimpanan data. Untuk lebih lengkapnya dapat dilihat dari tabel dibawah ini:

Tipe Data	Jangkauan	Ukuran	Zero Value
DATE	‘1000-01-01’ to ‘9999-12-31’	3 byte	‘0000-00-00’
DATETIME	‘1000-’01-01 00:00:01’ to ‘9999-12-31 23:59:59’	8 byte	‘0000-00-00 00:00:00’
TIMESTAMP	‘1970-01-01 00:00:00’ to ‘2038-01-18 22:14:07’	4 byte	‘0000-00-00 00:00:00’
TIME	‘–838:59:59’ to ‘838:59:58’	3 byte	‘00:00:00’
YEAR(2)	00 to 99	1 byte	‘00’
YEAR(4)	1901 to 2155	1 byte	‘0000’

MySQL menyediakan beberapa format yang dapat digunakan untuk input tipe data tanggal, masing masing untuk tipe data yang bersesuaian.

Tipe Data	Fomat Input
DATETIME	'CCYY-MM-DD hh:mm:ss'
TIMESTAMP	'YY-MM-DD hh:mm:ss'
	'CCYYMMDDhhmmss'
	'YYMMDDhhmmss'
	CCYYMMDDhhmmss
	YYMMDDhhmmss
DATE	'CCYY-MM-DD'
	'YY-MM-DD'
	'CCYYMMDD'
	'YYMMDD'
	CCYYMMDD
	YYMMDD
TIME	'hh:mm:ss'
	'hhmmss'
	hhmmss
YEAR	'CCYY'
	'YY'
	CCYY
	YY

• CCYY : input untuk tahun, dimana YY berupa tahun 2 digit, seperti 98, 78, dan 00, sedangkan untuk CCYY adalah tahun dengan 4 digit, seperti 2001, 1987, 2012. Untuk tahun dengan 2 digit, MySQL mengkonversinya dengan aturan 70-99 menjadi 1970-1999 dan 00-69 menjadi 2000-2069.

• MM: bulan dalam format dua digit, seperti 05,07,dan 12.

• DD: tanggal dalam format dua digit, seperti 14, 06 dan 30.

• hh: jam dalam format 2 digit, seperti 06,09, dan 12.

• mm: menit, dalam format 2 digit, seperti 15, 45, dan 59.

• ss: detik, dalam format 2 digit, seperti 10, 40, dan 57.

Jika MySQL tidak dapat membaca format, atau data tidak tersedia, maka data akan diisi sesuai dengan nilai pada Zero Value.

Contoh query untuk membuat tabel dengan data DATE:

mysql> CREATE TABLE contoh_date (dat DATE, tim TIME, dattim DATETIME,

timestam TIMESTAMP, yea YEAR);

Query OK, 0 rows affected (0.06 sec)

 

mysql> DESCRIBE contoh_date;

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

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

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

| dat      | date      | YES  |     | NULL              |                             |

| tim      | time      | YES  |     | NULL              |                             |

| dattim   | datetime  | YES  |     | NULL              |                             |

| timestam | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

| yea      | year(4)   | YES  |     | NULL              |                             |

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

5 rows in set (0.01 sec)




Perhatikan pada saat mendefinisikan tipe data YEAR, pada query DESCRIBE  dapat melihat bahwa MySQL memberikan nilai YEAR(4) sebagai nilai default. dapat secara langsung mendefinisikan YEAR(2) jika menginginkan data tahun dengan 2 digit, namun hal ini tidak disarankan, karena dengan mendefinisikan tahun 26, akan menjadi ambigu, apakah itu tahun 1926 atau 2026, walaupun MySQL memiliki aturan akan mengkonversinya menjadi 2026.

MySQL menyediakan fungsi NOW() untuk memberikan tanggal server pada saat ini, dan akan menggunakannya sebagai nilai tiap kolom pada tabel contoh_date:

mysql> INSERT INTO contoh_date values (NOW(),NOW(),NOW(),NOW(),'2012');

Query OK, 1 row affected, 1 warning (0.05 sec)

 

mysql> SELECT * FROM contoh_date;

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

| dat        | tim      | dattim              | timestam            | yea  |

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

| 2012-10-20 | 19:40:45 | 2012-10-20 19:40:45 | 2012-10-20 19:40:45 | 2012 |

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

1 row in set (0.00 sec)




Khusus untuk tipe data YEAR, fungsi NOW() akan menghasilkan error, oleh karenanya, pada contoh diatas  menginput manual ‘2012’ untuk kolom YEAR.

• Tipe Data ENUM dan SET

Pengertian Tipe data ENUM dalam MySQL

Tipe data ENUM merupakan tipe data yang khusus untuk kolom dimana nilai datanya sudah ditentukan sebelumnya. Pilihan ini dapat berisi 1 sampai dengan 65,535 pilihan string. Dimana kolom yang didefinisikan sebagai ENUM hanya dapat memilih satu diantara pilihan string yang tersedia.

Contoh penggunaan ENUM misalnya untuk kolom yang berisi daftar Jurusan yang harus dipilih mahasiswa. Pilihan ini harus sudah tersedia sebelumnya, dan dapat menggunakan tipe data enum untuk memastikan bahwa jurusan yang dipilih adalah jurusan yang telah ditentukan sebelumnya dan hanya 1 jurusan.

Contoh query tipe data ENUM:

mysql> CREATE TABLE jurusan (jur ENUM('Ilmu Komputer','Ekonomi','MIPA','Kedokteran'));

Query OK, 0 rows affected (0.07 sec)

 

mysql> DESCRIBE jurusan;

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

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

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

| jur   | enum('Ilmu Komputer','Ekonomi','MIPA','Kedokteran') | YES  |     | NULL    |       |

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

1 row in set (0.01 sec)

 

mysql> INSERT INTO jurusan VALUES ('Ilmu Komputer');

Query OK, 1 row affected (0.04 sec)

 

mysql> INSERT INTO jurusan VALUES ('Kedokteran');

Query OK, 1 row affected (0.04 sec)

 

mysql> INSERT INTO jurusan VALUES ('FISIP');

ERROR 1265 (01000): Data truncated for column 'jur' at row 1

 

mysql> INSERT INTO jurusan VALUES ('Ilmu Komunikasi');

ERROR 1265 (01000): Data truncated for column 'jur' at row 1

 

mysql> SELECT * FROM jurusan;

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

| jur           |

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

| Ilmu Komputer |

| Kedokteran    |

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

2 rows in set (0.00 sec)




Pada query diatas,  mendefinisikan kolom jur pada tabel Jurusan dengan tipe ENUM yang dapat dipilih dari pilihan yang tersedia : Ilmu Komputer,Ekonomi,MIPA, dan Kedokteran. Ketika mencoba menginput data baru, seperti Fisip dan Ilmu Komunikasi, MySQL mengeluarkan error, karena pilihan ini memang tidak didefinisikan sebelumnya.

Pengertian Tipe data SET dalam MySQL

Berbeda dengan tipe data ENUM dimana pilihan yang tersedia untuk kolom hanya dapat 1 data, untuk kolom SET  dapat memilih satu atau lebih nilai yang tersedia dari 1 sampai 64 pilihan string yang tersedia.

Contoh penggunaan tipe data SET adalah untuk data tentang hobby seseorang, karena bisa aja seseorang memiliki hobi yang lebih dari 1, namun  ingin memilih dari kumpulan list yang telah dibuat sebelumnya.

Contoh query tipe data SET:

mysql> CREATE TABLE hobi (hob SET('Membaca','Menulis','Menggambar','Main Musik'));

Query OK, 0 rows affected (0.05 sec)

 

mysql> DESCRIBE hobi;

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

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

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

| hob   | set('Membaca','Menulis','Menggambar','Main Musik') | YES  |     | NULL    |       |

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

1 row in set (0.01 sec)

 

mysql> INSERT INTO hobi VALUES ('Membaca');

Query OK, 1 row affected (0.05 sec)

 

mysql> INSERT INTO hobi VALUES ('Membaca,Main Musik');

Query OK, 1 row affected (0.07 sec)

 

mysql> INSERT INTO hobi VALUES ('Menggambar,Main Musik');

Query OK, 1 row affected (0.04 sec)

 

mysql> INSERT INTO hobi VALUES ('Belajar,Main Musik');

ERROR 1265 (01000): Data truncated for column 'hob' at row 1

 

mysql> SELECT * FROM hobi;

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

| hob                   |

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

| Membaca               |

| Membaca,Main Musik    |

| Menggambar,Main Musik |

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

3 rows in set (0.00 sec)




Dari query diatas, dapat dilihat bahwa untuk menginput lebih dari 1 nilai,  memisahkan dengan tanda koma dan tetap dalam tanda kutip, juga seandainya dicoba input hobi ‘belajar’, yang memang tidak ada sebelumnya, maka MySQL akan mengeluarkan pesan error.

• Atribut Tipe Data

Pengertian Atribut tipe data MySQL

Atribut tipe data adalah aturan yang diterapkan untuk sebuah kolom. MySQL memiliki banyak atribut tipe data, namun dalam tutorial ini hanya membahas atribut tipe data yang paling umum digunakan, yakni: AUTO_INCREMENT, BINARY, DEFAULT, NOT NULL, NULL, SIGNED, UNSIGNED, dan ZEROFILL.

Atribut AUTO_INCREMENT

Atribut AUTO_INCREMENT digunakan untuk tipe data numerik (biasanya tipe data INT), dimana jika menetapkan sebuah kolom dengan atribut AUTO_INCREMENT, maka setiap kali menginputkan data, nilai pada kolom ini akan bertambah 1. Nilai pada kolom tersebut juga akan bertambah jika diinput dengan NULL  atau nilai 0.

Pada sebuah tabel, hanya 1 kolom yang dapat dikenai atribut AUTO_INCREMENT. Setiap kolom AUTO_INCREMENT juga akan dikenakan atribut NOT NULL secara otomatis. Kolom AUTO_INCREMENT juga harus digunakan sebagai KEY (biasanya PRIMARY KEY)

Atribut BINARY

Atribut BINARY digunakan untuk tipe data huruf, seperti CHAR dan VARCHAR. Tipe data CHAR, VARCHAR dan TEXT tidak membedakan antara huruf besar dan kecil (case-insensitive), namun jika diberikan atribut BINARY, maka kolom tersebut akan membedakan antara huruf besar dan kecil (case-sensitive)

Atribut DEFAULT

Atribut DEFAULT dapat digunakan pada hampir semua tipe data. Fungsinya untuk menyediakan nilai bawaan untuk kolom seandainya tidak ada data yang diinput kepada kolom tersebut.

Atribut NOT NULL

Atribut NOT NULL dapat digunakan pada hampir semua tipe data, Fungsinya untuk memastikan bahwa nilai pada kolom tersebut tidak boleh kosong. Jika menginput data, namun tidak memberikan nilai untuk kolom tersebut, akan menghasilkan error pada MySQL.

Atribut NULL

Atribut NULL berkebalikan dengan NOT NULL, dimana jika sebuah kolom didefinisikan dengan NULL, maka kolom tersebut tidak harus berisi nilai.

Atribut SIGNED

Atribut SIGNED digunakan untuk tipe data numerik. Berlawanan dengan atribut UNSIGNED, dimana atribut ini berfungsi agar kolom dapat menampung nilai negatif. Atribut SIGNED biasanya dicantumkan hanya untuk menegaskan bahwa kolom tersebut mendukung nilai negatif, karena MySQL sendiri telah menyediakan nilai negatif secara default untuk seluruh tipe numerik.

Atribut UNSIGNED

Atribut UNSIGNED digunakan untuk tipe data numerik, namun berbeda sifatnya untuk tipe data INT,DECIMAL dan FLOAT. Untuk tipe data INT, atribut UNSIGNED berfungsi mengorbankan nilai negatif, untuk mendapatkan jangkauan nilai positif yang lebih tinggi. Namun untuk tipe data DECIMAL dan FLOAT, atribut UNSIGNED hanya akan menhilangkan nilai negatif, tanpa menambah jangkauan data.

Atribut ZEROFILL

Atribut ZEROFILL digunakan untuk tipe data numerik, dimana berfungsi untuk tampilan format data yang akan mengisi nilai 0 di sebelah kanan dari data. Jika  menggunakan atribut ZEROFILL untuk suatu kolom, secara otomatis kolom tersebut juga dikenakan attribut UNSIGNED.

Contoh query untuk penggunaan attribut :

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

mysql> CREATE TABLE contoh_att (no int AUTO_INCREMENT, nama VARCHAR(30) NOT NULL,umur TINYINT UNSIGNED DEFAULT '10', kodepos CHAR(5) NULL,PRIMARY KEY(no)); Query OK, 0 rows affected (0.13 sec)   mysql> DESCRIBE contoh_att; +---------+---------------------+------+-----+---------+----------------+ | Field   | Type                | Null | Key | Default | Extra          | +---------+---------------------+------+-----+---------+----------------+ | no      | int(11)             | NO   | PRI | NULL    | auto_increment | | nama    | varchar(30)         | NO   |     | NULL    |                | | umur    | tinyint(3) unsigned | YES  |     | 10      |                | | kodepos | char(5)             | YES  |     | NULL    |                | +---------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec)   mysql> INSERT INTO contoh_att VALUES (NULL,'Joko',NULL,20155); Query OK, 1 row affected (0.00 sec)   mysql> INSERT INTO contoh_att VALUES (0,'Amir',23,27118); Query OK, 1 row affected (0.05 sec)   mysql> INSERT INTO contoh_att VALUES (6,'Thasya',24,30012); Query OK, 1 row affected (0.04 sec)   mysql> INSERT INTO contoh_att VALUES (9,NULL,32,10099); ERROR 1048 (23000): Column 'nama' cannot be null   mysql> INSERT INTO contoh_att VALUES (NULL,'Rina',21,10889); Query OK, 1 row affected (0.05 sec)   mysql> SELECT * FROM contoh_att; +----+--------+------+---------+ | no | nama   | umur | kodepos | +----+--------+------+---------+ |  1 | Joko   | NULL | 20155   | |  2 | Amir   |   23 | 27118   | |  6 | Thasya |   24 | 30012   | |  7 | Rina   |   21 | 10889   | +----+--------+------+---------+ 4 rows in set (0.00 sec)

Dapat dilihat bahwa jika memberikan nilai NULL kepada kolom nama yang telah diberikan atribut NOT NULL, MySQL akan memberikan error. Juga pada kolom no, dimana dilompat dengan memberikan nilai 6, namun untuk kolom selanjutnya, akan tetap ditambah sebanyak 1, karena dikenai atribut AUTO_INCREMENT.

• Merubah Tabel (ALTER TABLE)

Mempelajari query ALTER TABLE, akan dibuat sebuah tabel sample: daftar_jurusan. Silahkan buka MySQL client, dan gunakan perintah query berikut jika  ingin membuat tabel yang 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 29 30

mysql> SHOW DATABASES; +--------------------+ | Database           | +--------------------+ | information_schema | | mahasiswa          | | mysql              | | performance_schema | | test               | +--------------------+ 5 rows in set (0.11 sec)   mysql> USE mahasiswa; Database changed   mysql> CREATE TABLE daftar_jurusan ( kode_jurusan CHAR(5), nama_jurusan VARCHAR(20),alamat VARCHAR(100), jumlah_mahasiswa SMALLINT ); Query OK, 0 rows affected (0.05 sec)   mysql> DESC daftar_jurusan; +------------------+--------------+------+-----+---------+-------+ | Field            | Type         | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | kode_jurusan     | char(5)      | YES  |     | NULL    |       | | nama_jurusan     | varchar(20)  | YES  |     | NULL    |       | | alamat           | varchar(100) | YES  |     | NULL    |       | | jumlah_mahasiswa | smallint(6)  | YES  |     | NULL    |       | +------------------+--------------+------+-----+---------+-------+ 4 rows in set (0.03 sec)

Query tersebut berisi perintah pembuatan tabel daftar_jurusan pada database mahasiswa. 

Cara Merubah Tipe Data Kolom MySQL

Sesaat setelah membuat tabel daftar_jurusan, ingin merubah tipe data pada kolom jumlah_mahasiswa dari SMALLINT menjadi MEDIUMINT agar dapat menampung lebih banyak nilai. Perubahan ini dapat dilakukan dengan mengunakan query ALTER…MODIFY sebagai berikut:

ALTER TABLE nama_tabel MODIFY nama_kolom tipe_data_baru;

Contoh query Alter…Modify:

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> ALTER TABLE daftar_jurusan MODIFY jumlah_mahasiswa MEDIUMINT; Query OK, 0 rows affected (0.08 sec) Records: 0  Duplicates: 0  Warnings: 0 mysql> DESC daftar_jurusan; +------------------+--------------+------+-----+---------+-------+ | Field            | Type         | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | kode_jurusan     | char(5)      | YES  |     | NULL    |       | | nama_jurusan     | varchar(20)  | YES  |     | NULL    |       | | alamat           | varchar(100) | YES  |     | NULL    |       | | jumlah_mahasiswa | mediumint(9) | YES  |     | NULL    |       | +------------------+--------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)

Dari query DESC kita dapat melihat bahwa tipe data pada kolom jumlah_mahasiswa telah berubah menjadi MEDIUMINT.

Cara Merubah Nama Kolom Tabel MySQL

Untuk merubah nama kolom pada tabel yang sudah ada, dapat menggunakan perintah ALTER…CHANGE, dengan format query sebagai berikut:

ALTER TABLE nama_tabel CHANGE nama_kolom nama_kolom_baru tipe_data;

Katakan ingin merubah nama kolom alamat menjadi alamat_jurusan agar menjadi lebih spesifik, dapat dilakukan dengan query berikut:

Contoh query Alter…Change:

mysql> ALTER TABLE daftar_jurusan CHANGE alamat alamat_jurusan VARCHAR(100);

Query OK, 0 rows affected (0.06 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> DESC daftar_jurusan;

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

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

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

| kode_jurusan     | char(5)      | YES  |     | NULL    |       |

| nama_jurusan     | varchar(20)  | YES  |     | NULL    |       |

| alamat_jurusan   | varchar(100) | YES  |     | NULL    |       |

| jumlah_mahasiswa | mediumint(9) | YES  |     | NULL    |       |

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

4 rows in set (0.01 sec)




Cara Merubah Nama Tabel MySQL

Untuk beberapa kasus tertentu, mungkin saja memutuskan untuk merubah nama tabel MySQL. Untuk hal ini MySQL menyediakan query ALTER…RENAME TO dengan format query:

ALTER TABLE nama_tabel_lama RENAME TO nama_tabel_baru;

Contohkan merubah tabel daftar_jurusan menjadi tabel_jurusan, maka querynya:

Contoh query Alter…Rename To:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql> ALTER TABLE daftar_jurusan RENAME TO tabel_jurusan; Query OK, 0 rows affected (0.06 sec)   mysql> DESC daftar_jurusan; ERROR 1146 (42S02): Table 'mahasiswa.daftar_jurusan' doesn't exist   mysql> DESC tabel_jurusan; +------------------+--------------+------+-----+---------+-------+ | Field            | Type         | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | kode_jurusan     | char(5)      | YES  |     | NULL    |       | | nama_jurusan     | varchar(20)  | YES  |     | NULL    |       | | alamat_jurusan   | varchar(100) | YES  |     | NULL    |       | | jumlah_mahasiswa | mediumint(9) | YES  |     | NULL    |       | +------------------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)

Dapat lihat bahwa setelah merubah nama tabel menjadi tabel_jurusan, tabel daftar_jurusan sudah tidak terdapat lagi di dalam database mahasiswa.

Selain query ALTER…RENAME TO, terdapat juga perintah RENAME untuk merubah nama tabel,format querynya:

RENAME TABLE nama_tabel_lama TO nama_tabel_baru;

Mari kita rubah kembali nama tabel tabel_jurusan menjadi daftar_jurusan:

Contoh query Alter…Rename To:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql> RENAME TABLE tabel_jurusan TO daftar_jurusan; Query OK, 0 rows affected (0.07 sec)   mysql> DESC tabel_jurusan; ERROR 1146 (42S02): Table 'mahasiswa.tabel_jurusan' doesn't exist   mysql> DESC daftar_jurusan; +------------------+--------------+------+-----+---------+-------+ | Field            | Type         | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | kode_jurusan     | char(5)      | YES  |     | NULL    |       | | nama_jurusan     | varchar(20)  | YES  |     | NULL    |       | | alamat_jurusan   | varchar(100) | YES  |     | NULL    |       | | jumlah_mahasiswa | mediumint(9) | YES  |     | NULL    |       | +------------------+--------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)

Cara Menambahkan Kolom Baru pada Tabel MySQL

Andaikan beberapa saat kemudian berfikir untuk menambahkan kolom yang berisi nama ketua jurusan ke dalam tabel daftar_jurusan, dapat saja menghapus tabel dan membuat ulang, namun dengan menggunakan query ALTER…ADD, menambahkan kolom baru dapat dilakukan dengan lebih mudah.

ALTER TABLE nama_tabel ADD nama_kolom_baru tipe_data;

Contoh query menambahkan kolom ketua_jurusan ke dalam tabel daftar_jurusan adalah:

Contoh query Alter…Add:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> ALTER TABLE daftar_jurusan ADD ketua_jurusan VARCHAR(50); Query OK, 0 rows affected (0.02 sec) Records: 0  Duplicates: 0  Warnings: 0   mysql> DESC daftar_jurusan; +------------------+--------------+------+-----+---------+-------+ | Field            | Type         | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | kode_jurusan     | char(5)      | YES  |     | NULL    |       | | nama_jurusan     | varchar(20)  | YES  |     | NULL    |       | | alamat_jurusan   | varchar(100) | YES  |     | NULL    |       | | jumlah_mahasiswa | mediumint(9) | YES  |     | NULL    |       | | ketua_jurusan    | varchar(50)  | YES  |     | NULL    |       | +------------------+--------------+------+-----+---------+-------+ 5 rows in set (0.01 sec)

Cara Menghapus Kolom Tabel MySQL

Kebalikan dari menambahkan kolom baru, query ALTER…DROP dapat digunakan untuk menghapus sebuah kolom dari tabel MySQL.

ALTER TABLE nama_tabel DROP nama_kolom;

Contohkan dengan menghapus kolom ketua_jurusan dari tabel daftar_jurusan:

Contoh query Alter…Drop:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> ALTER TABLE daftar_jurusan DROP ketua_jurusan; Query OK, 0 rows affected (0.04 sec) Records: 0  Duplicates: 0  Warnings: 0   mysql> DESC daftar_jurusan; +------------------+--------------+------+-----+---------+-------+ | Field            | Type         | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | kode_jurusan     | char(5)      | YES  |     | NULL    |       | | nama_jurusan     | varchar(20)  | YES  |     | NULL    |       | | alamat_jurusan   | varchar(100) | YES  |     | NULL    |       | | jumlah_mahasiswa | mediumint(9) | YES  |     | NULL    |       | +------------------+--------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)

Cara Merubah Tipe Data Table (Table Engine) MySQL

MySQL menyediakan beberapa tipe data untuk beberapa keperluan, seperti tipe data (disebut juga dengan engine tabel) MyISAM untuk tabel yang dioptimalisasikan untuk keperluan pembacaan yang lebih sering, atau tipe data InnoDB untuk tabel yang memerlukan transaksi.

Perubahan tipe data dari MyISAM ke InnoDB atau sebaliknya dapat menggunakan perintah ALTER…ENGINE:

ALTER TABLE nama_tabel ENGINE = tipe_tabel;

Dalam MySQL versi 5.5 yang digunakan pada tutorial ini, default enginenya adalah InnoDB, hal ini berbeda jika dibandingkan dengan versi MySQL sebelumnya yang menggunakan tipe tabel MyISAM sebagai default.

ontoh query dibawah ini akan merubah tipe data tabel daftar_jurusan dari InnoDB menjadi MyISAM.

Contoh query Alter…Engine:

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

mysql> SHOW TABLE STATUS WHERE NAME='daftar_jurusan' \G;   ***************************1.row*************************** Name: daftar_jurusan Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 9437184 Auto_increment: NULL Create_time: 2013-04-14 21:20:02 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) ERROR: No query specified   mysql> ALTER TABLE daftar_jurusan ENGINE = MyISAM; Query OK, 0 rows affected (0.06 sec) Records: 0  Duplicates: 0  Warnings: 0   mysql> SHOW TABLE STATUS WHERE NAME='daftar_jurusan' \G;   ***************************1.row*************************** Name: daftar_jurusan Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 281474976710655 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2013-04-14 22:43:29 Update_time: 2013-04-14 22:43:29 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) ERROR: No query specified mysql>

Query SHOW TABLE STATUS digunakan untuk melihat engine apa yang digunakan dalam sebuah tabel. 

• Menambahkan data (Query INSERT)

Query INSERT untuk menambahkan data kedalam Tabel MySQL

Menambahkan sebuah data ke dalam tabel MySQL merupakan salah satu aktifitas yang paling sering dilakukan. Pembuatan database dan tabel hanya perlu dilakukan 1 kali saja, namun menginput data ke dalam tabel akan terus dilakukan sepanjang penggunaan aplikasi. Di dalam MySQL (dan juga RDBMS lainnya), kita menggunakan query INSERT untuk menambahkan data kedalam tabel.

Query INSERT memiliki banyak variasi perintah yang fleksibel tergantung kebutuhan. Termasuk pilihan ketika data yang akan diinput terdapat duplikasi, dengan opsi IGNORE dan ON DUPLICATE KEY UPDATE.

Sebagai tabel contoh untuk memahami perintah INSERT MySQL, akan membuat tabel mata_kuliah di dalam database mahasiswa dengan query sebagai berikut:

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> SHOW DATABASES; +--------------------+ | Database           | +--------------------+ | information_schema | | mahasiswa          | | mysql              | | performance_schema | | test               | +--------------------+ 5 rows in set (0.08 sec)   mysql> USE mahasiswa; Database changed   mysql> CREATE TABLE mata_kuliah (kode_matkul CHAR(5) PRIMARY KEY, nama_matkul VARCHAR(50) NOT NULL, jumlah_SKS TINYINT UNSIGNED DEFAULT '2', nama_jurusan VARCHAR(50)); Query OK, 0 rows affected (0.01 sec)   mysql> DESC mata_kuliah; +--------------+---------------------+------+-----+---------+-------+ | Field        | Type                | Null | Key | Default | Extra | +--------------+---------------------+------+-----+---------+-------+ | kode_matkul  | char(5)             | NO   | PRI | NULL    |       | | nama_matkul  | varchar(50)         | NO   |     | NULL    |       | | jumlah_SKS   | tinyint(3) unsigned | YES  |     | 2       |       | | nama_jurusan | varchar(50)         | YES  |     | NULL    |       | +--------------+---------------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)

Cara Penulisan dasar query INSERT

Mengutip manual resmi MySQL, penulisan dasar dari perintah INSERT dalam MySQL adalah sebagai berikut:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ]

MySQL menyediakan banyak pilihan opsional untuk memasukkan data ke dalam tabel. 

Cara Penggunaan Query INSERT…VALUES

Format perintah INSERT yang pertama dibahas adalah yang paling sederhana, yaitu:

INSERT INTO nama_tabel VALUES (nilai_kolom1, nilai_kolom2,...);

nama_tabel adalah nama dari tabel yang akan diinput, sedangkan nilai_kolom1 adalah nilai yang akan kita input kedalam tabel MySQL, nilai_kolom2 adalah nilai untuk kolom kedua, dan seterusnya. Perhatikan bahwa nilai_kolom harus berada dalam tanda kurung dan dipisahkan dengan koma untuk kolom-kolom berikutnya.

Contohkan untuk memasukkan sebaris data ke dalam tabel mata_kuliah:

1 2 3 4 5 6 7 8 9 10

mysql> INSERT INTO mata_kuliah VALUES ('KIMDA','Kimia Dasar',2,' Kimia'); Query OK, 1 row affected (0.06 sec)   mysql> select * from mata_kuliah; +-------------+-------------+------------+--------------+ | kode_matkul | nama_matkul | jumlah_SKS | nama_jurusan | +-------------+-------------+------------+--------------+ | KIMDA       | Kimia Dasar |          2 |  Kimia       | +-------------+-------------+------------+--------------+ 1 row in set (0.00 sec)

Jika ingin langsung memasukkan 2 baris data atau lebih dalam satu perintah query INSERT MySQL, tinggal menambahkan isi data untuk baris berikutnya dibelakang perintah dengan format penulisan sebagai berikut:

INSERT INTO nama_tabel VALUES (nilai_kolom1a, nilai_kolom2a,...), (nilai_kolom1b, nilai_kolom2b,...);

Contoh query untuk penambahan data dengan 2 baris sekaligus adalah:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> INSERT INTO mata_kuliah VALUES ('MMDAS','Matematika Dasar',4,'Matematika'), ('JRKOM','Jaringan Komputer',2,'Ilmu Komputer'); Query OK, 2 rows affected (0.01 sec) Records: 2  Duplicates: 0  Warnings: 0   mysql> select * from mata_kuliah; +-------------+-------------------+------------+---------------+ | kode_matkul | nama_matkul       | jumlah_SKS | nama_jurusan  | +-------------+-------------------+------------+---------------+ | JRKOM       | Jaringan Komputer |          2 | Ilmu Komputer | | KIMDA       | Kimia Dasar       |          2 |  Kimia        | | MMDAS       | Matematika Dasar  |          4 | Matematika    | +-------------+-------------------+------------+---------------+ 3 rows in set (0.00 sec)

Perintah INSERT… VALUES diatas cocok untuk tabel dimana kita telah mengetahui urutan dari kolom yang ada. Namun dalam situasi tidak mengetahui urutan kolom dari tabel, kita harus mendefenisikan urutan kolom yang akan diisi dengan cara penulisan query INSERT… VALUES.

Cara Penggunaan Query INSERT (nama_kolom)… VALUES

Untuk situasi dimana kolom yang akan diisi tidak diketahui urutannya, atau kita hanya akan mengisi sebagian kolom saja, maka harus mendefenisikan kolom-kolom mana saja yang akan digunakan. Untuk keperluan tersebut, MySQL menyediakan variasi query INSERT, yaitu:

INSERT INTO nama_tabel (kolom1,kolom2,...) VALUES (nilai_kolom1,nilai_kolom2,...);

kolom1 adalah nama kolom yang akan kita input dengan nilai_kolom1, dan kolom2 adalah nama kolom yang akan diisi dengan data pada nilai_kolom2. Urutan nama kolom dengan nilai yang akan diisi harus sesuai.

Sebagai contoh, kita akan menggunakan perintah INSERT (nama_kolom)… VALUES pada MySQL:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> TRUNCATE mata_kuliah; Query OK, 0 rows affected (0.05 sec)   mysql> INSERT INTO mata_kuliah (kode_matkul,nama_matkul,jumlah_SKS,nama_jurusan) VALUES ('MMDAS','Matematika Dasar',4,'Matematika'); Query OK, 1 row affected (0.05 sec)   mysql> select * from mata_kuliah; +-------------+------------------+------------+--------------+ | kode_matkul | nama_matkul      | jumlah_SKS | nama_jurusan | +-------------+------------------+------------+--------------+ | MMDAS       | Matematika Dasar |          4 | Matematika   | +-------------+------------------+------------+--------------+ 1 row in set (0.00 sec)

Bagaimana kalau urutan kolom acak? Selama perintah MySQL yang diinput sesuai  dengan urutan kolom yang ditulis, hal tersebut tidak menjadi masalah.

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> INSERT INTO mata_kuliah (nama_jurusan, kode_matkul, jumlah_SKS, nama_matkul) VALUES ('Ilmu Komputer', 'JRKOM',2,'Jaringan Komputer'); Query OK, 1 row affected (0.04 sec)   mysql> select * from mata_kuliah; +-------------+-------------------+------------+---------------+ | kode_matkul | nama_matkul       | jumlah_SKS | nama_jurusan  | +-------------+-------------------+------------+---------------+ | JRKOM       | Jaringan Komputer |          2 | Ilmu Komputer | | MMDAS       | Matematika Dasar  |          4 | Matematika    | +-------------+-------------------+------------+---------------+ 2 rows in set (0.00 sec)

Jika melihat query pada saat pembuatan tabel mata_kuliah, kolom jumlah_SKS didefinisikan dengan nilai default 2, dengan kata lain jika kolom ini tidak diisi, maka nilai default 2 akan digunakan untuk kolom jumlah_SKS.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

mysql> INSERT INTO mata_kuliah (kode_matkul,nama_matkul,nama_jurusan) VALUES ('FIDAS','Fisika Dasar','Fisika'), ('CITRA','Pengolahan CITRA','Ilmu Komputer'), ('PBASE','Pengantar Database','Ilmu Komputer'); Query OK, 3 rows affected (0.00 sec) Records: 3  Duplicates: 0  Warnings: 0   mysql> select * from mata_kuliah; +-------------+--------------------+------------+---------------+ | kode_matkul | nama_matkul        | jumlah_SKS | nama_jurusan  | +-------------+--------------------+------------+---------------+ | CITRA       | Pengolahan CITRA   |          2 | Ilmu Komputer | | FIDAS       | Fisika Dasar       |          2 | Fisika        | | JRKOM       | Jaringan Komputer  |          2 | Ilmu Komputer | | MMDAS       | Matematika Dasar   |          4 | Matematika    | | PBASE       | Pengantar Database |          2 | Ilmu Komputer | +-------------+--------------------+------------+---------------+ 5 rows in set (0.00 sec)

Dalam query diatas, sengaja hanya mengisi 3 kolom, tanpa menginput kolom jumlah_SKS, tetapi karena nilai default kolom jumlah_SKS adalah 2, maka kolom jumlah_SKS akan tetap berisi dengan nilai.

• Menambahkan data dari File

Mempersiapkan Tabel Contoh: daftar_dosen

Sebagai tabel contoh untuk mempelajari cara menambahkan data menggunakan query LOAD DATA INFILE, akan mempersiapkan tabel daftar_dosen, dengan query sebagai berikut:

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> SHOW DATABASES; +--------------------+ | Database           | +--------------------+ | information_schema | | mahasiswa          | | mysql              | | performance_schema | | test               | +--------------------+ 5 rows in set (0.08 sec)   mysql> USE mahasiswa; Database changed   mysql> 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.16 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.14 sec)

Cara Penulisan dasar query LOAD DATA INFILE

Query MySQL: LOAD DATA INFILE digunakan untuk menginput data kedalam tabel dimana data yang diinput berasal dari sebuah file. 

Query lengkap dari LOAD DATA INFILE sesuai dari manual MySQL adalah sebagai berikut:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'     [REPLACE | IGNORE]     INTO TABLE tbl_name     [CHARACTER SET charset_name]     [{FIELDS | COLUMNS}         [TERMINATED BY 'string']         [[OPTIONALLY] ENCLOSED BY 'char']         [ESCAPED BY 'char']     ]     [LINES         [STARTING BY 'string']         [TERMINATED BY 'string']     ]     [IGNORE number {LINES | ROWS}]     [(col_name_or_user_var,...)]     [SET col_name = expr,...]

Dapat dilihat bahwa MySQL menyediakan banyak pilihan opsional (dalam tanda kurung siku) yang dapat digunakan tergantung kebutuhan. Dalam tutorial MySQL ini, akan mdibahas beberapa diantaranya.

Cara Penggunaan query LOAD DATA INFILE dalam MySQL

Agar lebih gampang dipahami, langsung mencoba query LOAD DATA INFILE, dengan membuat sebuah file input_data.txt. Bebas untuk meletakkan file tersebut. Pada contoh ini, akan menempatkannya pada folder “D:\MySQL\”.

Buka notepad, lalu tuliskan data berikut ke dalam file text tersebut, dan save sebagai input_data.txt:

"0576431001","M. Siddiq","0812979005","Jakarta" "0770435006","Siswanto","0812567765","Medan" "0869437003","Andi Mulia","0812332564","Padang" "0260432002","Maya Ari Putri","0812345234","Palembang" "1080432007","Arif Budiman","0812456345","Makasar" "0275430005","Susi Indriani","0812656532","Bogor"

Berikut tampilan file input_data.txt

Pada saat membuat file, pastikan menekan Enter di akhir baris untuk membuat baris baru. Selanjutnya buka MySQL Client command prompt dan jalankan query berikut:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

mysql> LOAD DATA INFILE 'D:\\MySQL\\input_data.txt' INTO TABLE daftar_dosen FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'; Query OK, 6 rows affected (0.05 sec) Records: 6  Deleted: 0  Skipped: 0  Warnings: 0   mysql> SELECT * FROM daftar_dosen; +------------+----------------+------------+-----------+ | NIP        | nama_dosen     | no_hp      | alamat    | +------------+----------------+------------+-----------+ | 0260432002 | Maya Ari Putri | 0812345234 | Palembang | | 0275430005 | Susi Indriani  | 0812656532 | Bogor     | | 0576431001 | M. Siddiq      | 0812979005 | Jakarta   | | 0770435006 | Siswanto       | 0812567765 | Medan     | | 0869437003 | Andi Mulia     | 0812332564 | Padang    | | 1080432007 | Arif Budiman   | 0812456345 | Makasar   | +------------+----------------+------------+-----------+ 6 rows in set (0.00 sec)

Mari kita bahas tentang penulisan perintah LOAD DATA INFILE diatas:

• Hal pertama yang ditulis setelah perintah LOAD DATA INFILE adalah lokasi tempat file input_data.txt berada. Dalam query diatas, file tersebut berada pada D:\MySQL\input_data.txt. Tetapi karena didalam MySQL karakter ‘\’merupakan karakter khusus, maka penulisannya harus di-double, sehingga menjadi D:\\MySQL\\input_data.txt. Jika lokasi file anda berbeda, tinggal menyesuaikan saja.

• Setelah lokasi file, selanjutnya menambahkan perintah ke tabel mana file tersebut akan diinput. Pada contoh tabel tersebut adalah daftar_dosen, sehingga ditulis …INTO TABLE daftar_dosen…

• Perintah FIELDS TERMINATED BY ‘,’  dimaksudkan sebagai instruksi pada MySQL bahwa setiap kolom pada input_data.txt dipisahkan oleh karakter koma ‘,’. Namun tidak harus tanda koma, tanda lain seperti titik, tanda ‘|’, maupun karakter tab bisa digunakan sebagai tanda pemisah, dan instruksikan MySQL untuk menggunakan tanda tersebut sebagai penanda kolom.

• Instruksi ENCLOSED BY ‘”‘ memberitahu MySQL agar menghapus penanda kutip dua (“) dari tiap kolom. Namun jika pada daftar_dosen.txt kita tidak menambahkan tanda kutip dua diawal data, MySQL tetap akan menerima data tersebut.

• Pada akhir query, perintah …LINES TERMINATED BY ‘\r\n’ menginstruksikan MySQL agar tiap baris diakhiri dengan karakter new line dan carriage return. Karakter khusus ini terbentuk ketika menekan Enter pada keyboard untuk pindah baris pada notepad. ‘\r‘ merupakan cara penulisan untuk karakter carriage return, sedangkan ‘\n‘ adalah karakter new line.

Berbagai pilihan instruksi dari query LOAD DATA INFILE

Jika melihat format penulisan query LOAD DATA INFILE, terdapat beberapa pilihan opsi lainnya, akan dibahas beberapa diantaranya:

Penjelasan opsi LOCAL

Opsi LOCAL disini digunakan untuk menentukan lokasi dari text yang diinput. Jika menuliskan LOCAL, maka file text akan dicari dalam komputer Client MySQL. Namun jika tidak ditulis, maka lokasi file akan dicari pada komputer MySQL Server. Karena mengistall MySQL pada satu komputer, opsi LOCAL tidak akan berpengaruh, karena MySQL Server dan MySQL Client berada pada komputer yang sama, dan juga folder yang sama (file mysqld.exe adalah MySQL Server, dan file mysql.exe adalah MySQL Client). Contoh penggunaan query-nya: LOAD DATA LOCAL INFILE ‘path_to_file’

Penjelasan opsi [REPLACE | IGNORE]

Opsi [REPLACE | IGNORE] berkaitan dengan cara MySQL menangani duplikasi data pada kolom PRIMARY KEY. Jika opsi REPLACE digunakan, maka saat ditemukan data yang sama, maka data yang baru akan menimpa data lama, namun jika menggunakan opsi IGNORE, data yang baru akan diabaikan dan MySQL akan menjalankan baris berikutnya.

Penjelasan opsi LINES STARTING BY ‘string’ dan TERMINATED BY ‘string’

Opsi ini digunakan untuk menentukan awal dari data dan akhir dari data. String disini dapat berupa karakter seperti “,” atau “*”, maupun karakter new line dan carriage return.

Penggunaan karakter-karakter khusus dapat dilihat dari tabel dibawah ini:

Penulisan	Penjelasan Karakter
\0	Karakter ASCII untuk NULL (0x00)
\'	Karakter tanda kutip satu (')
\"	Karakter tanda kutip dua(")
\b	Karakter backspace
\n	Karakter newline (linefeed)
\r	Karakter carriage return
\t	Karakter untuk tab
\Z	ASCII 26 (Control+Z)
\\	Karakter untuk backslash (\)
\%	Karakter untuk tanda persen (%)
\_	Karakter untuk tanda garis bawah (_)

Penjelasan opsi IGNORE number

Opsi IGNORE dapat digunakan untuk mengistruksikan MySQL agar melompati beberapa baris dan memulai dari baris ke sekian. Hal ini berguna jika pada awal text merupakan penjelasan nama kolom. 

Contoh Cara Penggunaan query LOAD DATA INFILE dengan opsi LOCAL, REPLACE dan LINES STARTING BY

Agar memahami penggunaan LOCAL, REPLACE dan LINES STARTING BY, akan mencoba menggunakan query LOAD DATA INFILE untuk file kedua berikut.

Buat file data_lagi.txt, dengan isian sebagai berikut:

NIP                  Nama          No Hp          Kota **'0876439004'   'Mulyono'       '0812912312'  'Semarang'| **'0770435006'   'Rubin Hadi'    '0812567678'  'Papua'| **'0869437003'   'Mustalifah'    '0812338877'  'Aceh'| **'0160436012'   'Sabrina Sari'  '0812349900'  'Pekanbaru'| **'0480432066'   'Tia Santrini'  '0812451177'  'Padang'|

Berikut tampilan file input_data.txt

Perhatikan bahwa setiap baris diawali dengan tanda bintang dua kali (**), diakhiri dengan karakter pipa (|) dan setiap kolom dibatasi dengan tab. Juga pada baris ke 2 dan 3 akan terdapat duplikasi untuk kolom NIP dengan data sebelumnya. Baris pertama dari text adalah judul kolom.

Agar file diatas dapat diproses oleh MySQL, perlu menggunakan perintah tambahan. Penulisan query untuk menginput file ini adalah sebagai berikut:

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

mysql> SELECT * FROM daftar_dosen; +------------+----------------+------------+-----------+ | NIP        | nama_dosen     | no_hp      | alamat    | +------------+----------------+------------+-----------+ | 0260432002 | Maya Ari Putri | 0812345234 | Palembang | | 0275430005 | Susi Indriani  | 0812656532 | Bogor     | | 0576431001 | M. Siddiq      | 0812979005 | Jakarta   | | 0770435006 | Siswanto       | 0812567765 | Medan     | | 0869437003 | Andi Mulia     | 0812332564 | Padang    | | 1080432007 | Arif Budiman   | 0812456345 | Makasar   | +------------+----------------+------------+-----------+ 6 rows in set (0.00 sec)   mysql> LOAD DATA LOCAL INFILE 'D:\\MySQL\\data_lagi.txt' REPLACE INTO TABLE daftar_dosen FIELDS TERMINATED BY '\t' ENCLOSED BY '\'' LINES STARTING BY '**' TERMINATED BY '|' IGNORE 1 LINES; Query OK, 6 rows affected (0.02 sec) Records: 4  Deleted: 2  Skipped: 0  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 | 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)

Query diatas menambahkan 4 baris dan menghapus 2 baris, hal ini dapat dilihat setelah query dijalankan Records: 4  Deleted: 2  Skipped: 0  Warnings: 0. Hal ini dikarenakan opsi REPLACE yang menggantikan 2 baris yang memiliki NIP yang sam