Pemrograman MySQL
5.1 Menciptakan
Database dan Tabel
Penggunaan SQL
yang paling utama adalah menciptakan sebuah database. Sintaksis untuk
menciptakan database baru adalah
CREATE DATABASE namadatabase
Terminologi
CREATE juga dapat dipakai untuk menciptakan tabel:
CREATE TABLE namatabel (
namakolom1 deskripsi,
namakolom2 deskripsi
…)
Menciptakan database dan tabel
1. Akses MySQL menggunakan mysql client yang Anda pilih. Pada bab ini,
hampir semua contoh akan menggunakan mysql client, tetapi hal ini juga dapat
dilakukan menggunakan phpMyAdmin atau perangkat client lain.
2. Ciptakan dan pilih database yang baru (Gambar 5.1).
CREATE DATABASE namasitusku;
USE namasitusku;
Baris pertama ini menciptakan
database (diasumsikan bahwa Anda terkoneksi dengan MYSQL sebagai user dengan
kapabilitas untuk menciptakan database baru). Baris kedua memberitahu MYSQL
bahwa Anda akan menggunakan database ini. Ingat bahwa di dalam mysql client,
Anda harus mengakhiri setiap perintah SQL dengan tanda titik-koma.
Gambar 5.1 Sebuah
database baru, namasitusku, diciptakan di dalam MySQL. Database ini kemudian
diseleksi untuk query berikutnya.
Jika eksekusi dilakukan terhadap
beberapa query di dalam phpMyAdmin, Anda juga memerlukan titik-koma untuk
memisahkan antar query. Lihat Gambar 5.2). Jika hanya ada satu query di dalam
phpMyAdmin, Anda tidak memerlukan titik-koma.
Jika Anda menggunakan MySQL pada
perusahaan hosting, maka mereka akan menciptakan database untuk Anda. Pada
kasus itu, Anda hanya perlu terhubung ke MySQL dan menyeleksi database.
Gambar 5.2
Perintah-perintah yang sama untuk menciptakan dan memilih sebuah database dapat
dijalankan dengan jendela SQL pada phpMyAdmin.
3. Ciptakan tabel pengguna (Gambar 5.3).
CREATE TABLE pengguna (
id_user MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
nama_depan VARCHAR(20) NOT NULL,
nama_belakang VARCHAR(40) NOT NULL,
email VARCHAR(60) NOT NULL,
pass CHAR(40) NOT NULL,
tanggal_registrasi DATETIME NOT NULL,
PRIMARY KEY (id_user)
);
Gambar 5.3 Perintah SQL
CREATE menciptakan tabel pengguna.
4. Konfirmasi eksistensi tabel (Gambar 5.4).
SHOW TABLES;
SHOW COLUMNS FROM pengguna;
Gambar 5.4 Konfirmasi
eksistens dari, dan kolom-kolom pada, sebuah tabel menggunakan perintah SHOW.
Dalam phpMyAdmin, tabel-tabel pada
sebuah database dicantumkan di sisi kiri jendela browser, di bawah nama
database (Gambar 5.5). Klik nama tabel untuk melihat kolom-kolomnya.
Gambar 5.5 phpMyAdmin
menampilkan definisi sebuah tabel di layar.
5.2 Menyisipkan
Rekaman
Setelah sebuah
database dan tabel-tabelnya diciptakan, Anda dapat mulai mengisinya menggunakan
perintah INSERT. Ada dua cara dalam penulisan perintah INSERT. Dengan metode
pertama, Anda dapat menamai kolom-kolom yang akan diisi:
INSERT INTO namatabel (kolom1, kolom2 …) VALUES (nilai1,
nilai2 …)
INSERT INTO namatabel (kolom4, kolom8) VALUES
(nilaiX, nilaiY)
Format kedua
untuk menyisipkan rekaman adalah dengan tidak menetapkan kolom-kolom sama
sekali tetapi dengan mencantumkan nilai untuk setiap kolom:
INSERT INTO namatabel VALUES (nilai1, NULL, nilai2,
nilai3, …)
Jika Anda
menggunakan metode ini, Anda perlu menetapkan nilai untuk tiap kolom, termasuk
NULL. Jika ada enam kolom pada tabel, Anda harus mencantumkan keenam nilai
untuk kolom-kolom yang ada. Karena alasan ini, format pertama dalam penyisipan
rekaman lebih umum digunakan dan direkomendasikan.
MySQL juga
membolehkan Anda untuk menyisipkan beberapa baris sekaligus, dengan memisahkan
tiap rekaman dengan sebuah koma:
INSERT INTO tablename (kolom1, kolom4) VALUES (nilaiA,
nilaiB),
(nilaiC, nilaiD),
(nilaiE,
nilaiF)
Metode
penyisipan rekaman ini tidak dapat diterima pada SQL standar dan oleh karena
itu tidak didukung oleh semua aplikasi database.
Menyisipkan data ke dalam tabel
1. Sisipkan satu baris data ke dalam tabel pengguna, dengan menamai semua kolom yang akan diisi (Gambar 5.6):
INSERT INTO pengguna
(nama_depan, nama_belakang, email,
pass, tanggal_registrasi)
VALUES (‘Kristof’, ‘sintong’, ‘email@contoh.com’,
SHA1(‘passku’), NOW());
Untuk kolom password dan tanggal
registrasi, dua fungsi digunakan untuk menghasilkan nilainya. Fungsi SHA1()
akan mengenkripsi password (passku pada contoh ini). Fungsi NOW() akan menetapkan
nilai dari tanggal_registrasi sebagai tanggal dan waktu sekarang.
Gambar 5.6 Query ini
menyisipkan sebuah rekaman ke dalam tabel pengguna.
Pesan 1 row affected mengindikasikan
keberhasilan penyisipan.
2. Sisipkan satu baris data ke dalam tabel pengguna, tanpa mencantumkan nama-nama kolom (Gambar 5.7).
INSERT INTO pengguna VALUES (NULL, 'Jeni', 'Isabela',
'email2@contoh.com', SHA1('sonari'), NOW());
Gambar 5.7 Satu rekaman
lain disisipkan ke dalam tabel, kali ini dengan menyediakan semua nilai untuk
tiap kolom di dalam tabel.
Pada kasus ini, setiap kolom
diberikan sebuah nilai. Kolom id_user diberikan nilai NULL, yang menyebabkan
MySQL menggunakan deskripsi AUTO_INCREMENT. Dengan kata lain, rekaman pertama
akan ditugasi nilai 1, rekaman kedua ditugasi nilai 2, dan seterusnya.
3. Sisipkan beberapa nilai ke dalam tabel pengguna (Gambar 5.8).
INSERT INTO pengguna (nama_depan,
nama_belakang, email, pass, tanggal_registrasi)
VALUES ('John', 'Lennon', 'john@beatles.com',
SHA1('sada'), NOW()),
('Paul', 'McCartney', 'paul@beatles.com',
SHA1('dua'), NOW()),
('George', 'Harrison', 'george@beatles.com',
SHA1('tolu'), NOW()),
('Ringo', 'Starr', 'ringo@beatles.com',
SHA1('opat'), NOW());
Gambar 5.8 Query ini
menyisipkan beberapa rekaman sekaligus ke dalam tabel.
4. Tab INSERT pada Perangkat phpMyAdmin pada dipakai untuk menyisipkan
rekaman menggunakan sebuah form HTML (Gambar 5.9).
Gambar 5.9 Form INSERT
pada phpMyAdmin menunjukkan kolom-kolom sebuat tabel dan menyediakan beberapa
kotak teks untuk memasukkan nilai-nilai. Menu pull-down mencantumkan
fungsi-fungsi yang dapat digunakan, seperti SHA1() untuk password atau NOW()
untuk tanggal registrasi.
5.3
Menyeleksi Data
Sekarang setelah
database memuat beberapa rekaman di dalamnya, Anda dapat membaca informasi yang
tersimpan dengan perintah SELECT. Query SELECT menghasilkan baris-baris rekaman
menggunakan sintaksis:
SELECT kolom_mana FROM tabel_mana
Query SELECT
paling sederhana:
SELECT * FROM namatabel
Asterisk
menandakan bahwa Anda ingin melihat semua kolom. Cara alternatif adalah
menggunakan kolom-kolom yang ingin ditampilkan, dengan tiap kolom dipisahkan
dengan koma:
SELECT
kolom1, kolom3 FROM namatabel
Menyeleksi data dari sebuah tabel
1. Baca data dari tabel pengguna (Gambar 5.10).
SELECT
* FROM pengguna;
Perintah SQL yang paling sederhana
ini membaca setiap kolom dari tiap baris di dalam tabel terkait.
Gambar 5.10 Query SELECT *
FROM namatabel menghasilkan setiap kolom dari tiap rekaman yang disimpan di
dalam tabel.
2. Baca kolom nama depan dan nama belakang dari tabel pengguna (Gambar
5.11).
SELECT nama_depan, nama_belakang
FROM pengguna;
Gambar 5.11 Query Hanya
dua kolom dari tiap rekaman di dalam tabel yang dihasilkan oleh query ini.
Di sini, Anda menggunakan statemen
SELECT untuk membatasi hasil hanya pada bidang-bidang yang Anda perlukan saja.
3. Pada phpMyAdmin, tab Browse menjalankan query SELECT sederhana (Gambar
5.12).
Gambar 5.12 Tab Browse
pada phpMyAdmin ekivalen dengan query SELECT * FROM namatabel;
4. Gunakan SELECT tanpa perlu mencantumkan nama tabel atau kolom. Sebagai
contoh, SELECT NOW(); (Gambar 5.13).
Gambar 5.13 Banyak query
dapat dijalankan tanpa perlu menetapkan nama database atau tabel.
5. Demonstrasikan urutan pencantuman kolom pada statemen SELECT menentukan
urutan nilai-nilai yang disajikan (Gambar 5.14).
Gambar 5.14 Urutan
pencantuman kolom pada statemen SELECT menentukan urutan nilai-nilai yang
disajikan.
5.4
Menggunakan Kondisional
Query SELECT
yang dipakai sejauh ini selalu membaca setiap rekaman dari sebuah tabel. Tetapi
Anda seringkali ingin membatasi baris-baris yang dihasilkan, berdasarkan
kriteria tertentu. Ini dapat dilakukan dengan menambahkan beberapa kondisional
pada query SELECT, dengan sintaksis:
SELECT kolom_mana FROM tabel_mana
WHERE kondisi-kondisi
Tabel 5.1
mencantumkan operator-operator yang umum dipakai di dalam sebuah kondisional.
Sebagai contoh, sebuah pemeriksaan ekualitas:
SELECT nama FROM orang WHERE
tanggal_lahir
= ‘1998-01-26’
TABEL 5.1 Operator-operator
yang sering dipakai dengan ekspresi WHERE.
Operator
|
Arti
|
=
|
Sama dengan
|
<
|
Lebih kecil dari
|
>
|
Lebih besar dari
|
<=
|
Lebih kecil dari atau sama dengan
|
>=
|
Lebih besar dari atau sama dengan
|
!= (juga <>)
|
Tidak sama dengan
|
IS NOT NULL
|
Memiliki nilai
|
IS NULL
|
Tidak memiliki nilai
|
BETWEEN
|
Di dalam sebuah rentang
|
NOT BETWEEN
|
Di luar sebuah rentang
|
IN
|
Ditemukan di dalam sebuah daftar nilai
|
OR (juga ||)
|
Dimana salah satu dari dua kondisional bernilai true
|
AND (juga &&)
|
Dimana kedua kondisional bernilai true
|
NOT (juga !)
|
Dimana kondisi tidak bernilai true
|
Operator-operator
dapat digunakan secara bersamaan, bersama dengan kurung, untuk menciptakan
ekspresi yang lebih kompleks:
SELECT * FROM item WHERE
(harga BETWEEN 1000 AND 2000) AND (kuantitas > 0)
SELECT * FROM kota WHERE
(kode_pos
= 90210) OR (kode_pos = 90211)
Untuk
mendemonstrasikan penggunaan kondisional, beberapa query SELECT akan dijalankan
pada database namasitusku.
Menggunakan kondisional
1. Seleksi semua pengguna dengan nama belakang Simanungkalit (Gambar 5.15).
SELECT * FROM pengguna
WHERE nama_belakang =
'Simanungkalit';
Gambar 5.15 Daftar
pengguna dengan nama belakang Simanungkalit.
Query sederhana ini menghasilkan setiap kolom dari tiap baris yang
memiliki nama belakang Simanungkalit.
2. Seleksi nama depan dari setiap pengguna yang memiliki nama belakang Simanungkalit (Gambar 5.16).
SELECT nama_depan FROM pengguna
WHERE nama_belakang = 'Simanungkalit';
Gambar 5.16 Hanya nama
depan dari pengguna dengan nama belakang Simanungkalit
yang dihasilkan.
3. Seleksi setiap kolom untuk setiap rekaman pada tabel pengguna yang tidak
memiliki alamat email (Gambar 5.17).
SELECT * FROM pengguna
WHERE email IS NULL;
Kondisional IS NULL sama dengan
mengatakan tidak memiliki sebuah nilai.
Ingat bahwa string kosong berbeda dari NULL dan oleh karena itu tidak memenuhi
kondisi ini. Pada kasus itu, query yang dibutuhkan adalah
SELECT * FROM pengguna
WHERE email=’’;
Gambar 5.17 Tidak ada
rekaman yang dihasilkan oleh query ini karena kolom email tidak memiliki nilai
NULL.
4. Seleksi ID user, nama depan, dan nama belakang dari semua rekaman yang
memiliki password adalah passku
(Gambar 5.18).
SELECT id_user, nama_depan, nama_belakang
FROM pengguna
WHERE pass = SHA1('passku');
Karena tiap password dienkripsi dengan fungsi SHA1(), Anda dapat melakukan
pencocokan menggunakan fungsi enkripsi yang sama di dalam sebuah kondisional
yang sama. Fungsi SHA1() bersifat case-sensitive, jadi query ini hanya dapat
dilakukan jika password (yang disimpan versus yang diquery) memiliki kecocokan.
Gambar 5.18 Kondisional
dapat menggunakan fungsi, seperti SHA1() di sini.
5. Seleksi nama-nama user dengan ID user kurang dari 10 dan lebih 20 (Gambar
5.19).
SELECT nama_depan, nama_belakang
FROM pengguna WHERE
(id_user < 10) OR (id_user > 20);
Gambar 5.19 Query
menggunakan dua kondisi dan operator OR.
Query ini dapat pula dituliskan dengan
SELECT nama_depan, nama_belakang FROM
pengguna WHERE id_user
NOT BETWEEN 10 and 20;
Atau bahkan
SELECT nama_depan, nama_belakang FROM
pengguna WHERE id_user NOT IN
(10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20);
5.5
Menggunakan LIKE dan NOT LIKE
Penggunaan
number, date, dan NULL di dalam kondisi merupakan proses yang sederhana, tetapi
penggunaan string bisa lebih rumit. Anda dapat memeriksa ekualitas string
dengan sebuah query seperti
SELECT * FROM pengguna
WHERE
nama_belakang = 'Sianipar'
Namun,
perbandingan string biasanya memerlukan operator yang lebih banyak. Jika,
misalnya, Anda ingin mencocokkan nama seseorang, yang menghasilkan Sianipar atau Simanungkalit, maka Anda memerlukan kondisional yang lebih
fleksibel. Di sinilah dimana terminologi LIKE dan NOT LIKE digunakan. Keduanya
digunakan bersama dengan dua karakter wildcard:
garis-bawah (_), yang mencocokkan karakter tunggal, dan tanda persen (%) yang
mencocokkan nol atau lebih karakter. Pada kasus nama belakang, query bisa
diberikan berupa
SELECT * FROM pengguna
WHERE nama_belakang LIKE 'Si%'
Query ini akan
menghasilkan semua baris yang memiliki nama belakang yang diawali dengan Si.
Karena wataknya case-insensitive, ia juga diterapkan pada nama-nama yang
diawali dengan si.
Menggunakan LIKE
1. Seleksi semua rekaman dimana di dalamnya nama belakang diawali dengan Si
(Gambar 5.20).
SELECT * FROM pengguna
WHERE nama_belakang LIKE 'Si%'
Gambar 5.20 Terminologi
LIKE menambah fleksibilitas pada kondisional Anda.
2. Seleksi nama untuk tiap rekaman yang memiliki alamat email tidak dalam
format sesuatu@gmail.com (Gambar 5.21).
SELECT nama_depan, nama_belakang
FROM pengguna WHERE
email NOT LIKE '%@gmail.com';
Gambar 5.21 Sebuah
kondisional NOT LIKE menghasilkan rekaman-rekaman berdasarkan pada apa nilai
yang tidak dimuat.
5.6
Mengurutkan Hasil
Secara default,
hasil dari sebuah query SELECT akan diurutkan dengan urutan yang tak-bermakna.
Untuk mengurutkannya, gunakan klausa ORDER BY.
SELECT * FROM namatabel ORDER BY kolom
Urutan default
ketika ORDER BY digunakan adalah pengurutan menaik (disingkat dengan ASC), yang
berarti bahwa angka-angka diurutkan dari kecil ke besar, tanggal-tanggal
diurutkan dari tanggal lama ke tanggal baru, dan teks diurutkan secara
alfabetikal. Anda bisa membalikkan pengurutan (menjadi pengurutan menurun)
dengan menambahkan DESC:
SELECT * FROM namatabel ORDER BY kolom DESC
Anda bahkan bisa
mengurutkan nilai-nilai yang dihasilkan oleh beberapa kolom:
SELECT * FROM namatabel
ORDER BY kolom1, kolom2
Anda dapat, dan
seringkali akan, menggunakan ORDER BY dengan klausa WHERE atau klausa-klausa
lain, dengan sintaksis:
SELECT * FROM namatabel WHERE kondisi
ORDER BY kolom
Mengurutkan data
1. Seleksi semua user yang diurutkan secara alfabetikal (Gambar 5.22).
SELECT nama_depan, nama_belakang
FROM pengguna
ORDER BY nama_belakang;
Gambar 5.22
Rekaman-rekaman diurutkan secara alfabetikal berdasarkan nama belakang.
Jika Anda membandingkan hasil ini
dengan yang ada pada Gambar 5.12, Anda akan melihat keuntungan dari penggunaan
ORDER BY.
2. Tampilkan semua user dengan tatanan alfabetikal berdasarkan nama
belakang dan kemudian berdasarkan nama depan (Gambar 5.23).
SELECT nama_depan, nama_belakang
FROM pengguna
ORDER BY nama_belakang ASC,
nama_depan ASC;
Gambar 5.23
Rekaman-rekaman diurutkan secara alfabetikal, pertama-tama berdasarkan nama
belakang, dan kemudian berdasarkan nama pertama.
Pada query ini, efeknya setiap baris
rekaman pertama-tama diurutkan berdasarkan nama_belakang, dan kemudian
berdasarkan nama_pertama.
3. Tampilkan semua pengguna tak-Sianipar yang diurutkan secara menurun berdasarkan
tanggal registrasi (Gambar 5.24).
SELECT * FROM pengguna
WHERE nama_belakang != 'Sianipar'
ORDER BY tanggal_registrasi DESC;
Anda dapat menggunakan ORDER BY pada
sembarang tipe kolom, termasuk pada number dan date. Klausa ini dapat pula
digunakan di dalam sebuah query dengan sebuah kondisional, dengan menempatkan
ORDER BY setelah WHERE.
Gambar 5.24 Semua user,
yang bukan bernama belakang Sianipar, ditampilkan secara menurun berdasarkan
tanggal registrasi.
5.7
Membatasi Hasil Query
Klausa SQL lain
yang dapat ditambahkan pada query adalah LIMIT. Pada sebuah query SELECT, WHERE
mendikte rekaman-rekaman nama yang akan dihasilkan, dan ORDER BY mendikte
bagaimana rekaman-rekaman tersebut ditampilkan, tetapi LIMIT mendikte berapa
banyak rekaman yang dihasilkan atau ditampilkan:
SELECT
* FROM namatabel LIMIT x
Pada query
semacam itu, hanya sejumlah x rekaman dari query yang akan dihasilkan. Untuk
menghasilkan hanya tiga rekaman yang cocok, gunakan:
SELECT * FROM namatabel LIMIT 3
Dengan
menggunakan format
SELECT * FROM namatabel LIMIT x, y
Anda dapat menghasilkan
sejumlah y rekaman, dimulai dari x. Untuk menghasilkan rekaman 11 sampai 20,
Anda dapat menuliskan
SELECT
* FROM namatabel LIMIT 10, 10
Seperti array
dalam PHP, hasil query dimulai dari 0 ketika menggunakan LIMIT, jadi 10 adalah
rekaman ke 11.
Anda dapat
menggunakan LIMIT dengan WHERE dan/atau ORDER BY, dimana LIMIT selalu
ditempatkan di posisi terakhir.
SELECT kolom_mana FROM namatabel
WHERE kondisi
ORDER BY kolom LIMIT x
Membatasi banyak data yang dihasilkan
1. Seleksi lima user terakhir yang telah teregistrasi (Gambar 5.25).
SELECT nama_depan, nama_belakang
FROM pengguna
ORDER BY tanggal_registrasi DESC
LIMIT 5;
Gambar 5.25 Dengan
menggunakan klausa LIMIT, sebuah query dapat menghasilkan sejumlah rekaman yang
spesifik.
2. Seleksi user kedua yang melakukan registrasi (Gambar 5.26).
SELECT nama_depan, nama_belakang
FROM pengguna
ORDER BY tanggal_registrasi ASC
LIMIT 1, 1;
Hal ini tampak aneh, tetapi
menghasilkan apa yang diminta. Pertama-tama, semua rekaman diurutkan secara
menaik berdasarkan tanggal_registrasi sehingga user pertama yang melakukan
registrasi akan ditampilkan pada posisi pertama. Kemudian, hasil dibatasi diawali
dari 1 (yang merupakan baris kedua) dan hanya satu rekaman yang dihasilkan.
Gambar 5.26 Dengan
menggunakan klausa LIMIT, sebuah query dapat menghasilkan rekaman-rekaman yang
berada ditengah sebuah grup, menggunakan format LIMIT x, y.
5.8
Memperbarui Data
Setelah tabel
diisi data, Anda bisa saja perlu mengedit rekaman-rekaman yang ada. Ini,
misalnya, diperlukan ketika informasi dimasukkan secara tidak tepat atau ketika
data berubah (seperti alamat email). Sintaksis untuk memperbarui rekaman adalah
UPDATE namatabel SET kolom=nilai
Anda dapat
mengubah beberapa kolom sekaligus, dengan memisahkan antar kolom dengan sebuah
koma:
UPDATE namatabel SET kolom1=nilaiA, kolom5=nilaiB…
Anda akan (pada
banyak kasus) menggunakan klausa WHERE untuk menetapkan baris-baris mana yang
perlu diperbarui; jika tidak, perubahan hanya diterapkan pada tiap rekaman.
UPDATE namatabel SET kolom2=nilai
WHERE kolom5=nilai
Pembaruan,
begitu juga penghapusan, rekaman merupakan salah satu alasan penggunaan kunci
primer. Nilai ini, yang tidak pernah berubah, dapat dijadikan titik referensi
pada klausa WHERE.
Memperbarui rekaman
1. Carilah kunci primer untuk rekaman yang akan diperbarui (Gambar 5.27).
SELECT id_user FROM pengguna
WHERE nama_depan = 'Rebecca'
AND nama_belakang ='Patricia';
Pada contoh ini, Anda akan mengubah
email untuk rekaman ini. Untuk melakukannya, Anda lebih dahulu mencari kunci
primer dari rekaman yang akan diperbarui.
Gambar 5.27 Sebelum
memperbarui sebuah rekaman, tentukan lebih dahulu kunci primernya untuk
digunakan di dalam klausa WHERE pada statemen UPDATE.
2. Perbarui rekaman (Gambar 5.28).
UPDATE pengguna
SET email='patricia@gmail.com'
WHERE id_user = 15;
Untuk mengubah alamat email, Anda
menggunakan sebuah query UPDATE, menggunakan kunci primer (id_user) untuk
menetapkan rekaman mana yang perlu diperbarui. MySQL akan melaporkan
keberhasilan query dan menampilkan berapa banyak baris yang terpengaruh.
Gambar 5.28 Query ini
mengubah nilai dari salah satu kolom pada satu baris rekaman.
3. Pastikan apakah perubahan telah ada atau tidak (Gambar 5.29).
SELECT * FROM pengguna
WHERE id_user=15;
Gambar 5.29 Sebagai
langkah terakhir, Anda dapat memastikan pembaruan dari rekaman yang terseleksi.
4. Untuk memperbarui sebuah rekaman pada phpMyAdmin, Anda bisa
mengklik-ganda pada rekaman yang diinginkan (Gambar 5.30).
Gambar 5.30 Anda bisa
mengedit rekaman pada phpMyAdmin.
5.9
Menghapus Data
Selain
memperbarui rekaman, langkah lain yang mungkin Anda perlukan adalah menghapus
sebuah rekaman dari database. Untuk melakukannya, gunakan perintah DELETE.
DELETE FROM namatabel
Perintah ini
akan menghapus setiap rekaman pada sebuah tabel, yang membuat tabel menjadi
kosong kembali. Begitu Anda menghapus sebuah rekaman, tidak ada cara lain untuk
mengembalikannya lagi.
Pada banyak
kasus, Anda akan menghapus baris secara individual, tidak semua baris. Untuk
melakukannya, gunakan klausa WHERE:
DELETE
FROM namatabel WHERE kondisi
Menghapus sebuah rekaman
1. Cari kunci primer untuk rekaman yang akan dihapus (Gambar 5.31).
SELECT id_user FROM pengguna
WHERE nama_depan ='Rebecca'
AND nama_belakang ='Patricia';
Sama seperti perintah UPDATE,
pertama-tama Anda perlu menentukan kunci primer untuk memilih rekaman yang akan
dihapus.
Gambar 5.31 Kolom id_user
akan dipakai untuk mengakses rekaman ini
di dalam sebuah query DELETE.
2. Tinjau-ulang apa yang akan terjadi ketika penghapusan dilakukan (Gambar
5.32).
SELECT
* FROM pengguna
WHERE
id_user = 15;
Trik yang baik agar terhindar dari
kesalahan dalam menghapus adalah dengan terlebih dahulu menjalankan query
SELECT * menggantikan DELETE. Hasil dari query ini akan merepresentasikan baris
mana yang akan terpengaruh akibat penghapusan.
Gambar 5.32 Untuk
meninjau-ulang efek dari query DELETE, pertama-tama query SELECT dijalankan.
3. Hapus rekaman (Gambar 5.33).
DELETE FROM pengguna
WHERE id_user = 15 LIMIT 1;
Gambar 5.33 Penghapusan
satu rekaman dari tabel.
Sama seperti pembaruan data, MySQL
akan melaporkan keberhasilan dari eksekusi query dan berapa banyak baris yang
dipengaruhi. Pada titik ini, tidak ada cara lain untuk mengembalikan data yang
terhapus kecuali jika Anda telah memback-up database sebelumnya.
4. Pastikan apakah perubahan telah terjadi (Gambar 5.34).
SELECT id_user, nama_depan,
nama_belakang
FROM pengguna
ORDER BY id_user ASC;
Anda memastikan perubahan yang
terjadi dengan menjalankan query pada langkah 1.
Gambar 5.34 Rekaman dengan
id_user 15 tidak lagi bagian dari tabel ini.
5.10
Menggunakan Fungsi
Untuk mengakhiri
bab ini, Anda akan belajar tentang sejumlah fungsi yang dapat dipakai pada
query MySQL. Anda telah melihat dua fungsi, SHA1() dan NOW(). Kebanyakan fungsi
yang akan Anda lihat di sini digunakan dengan query SELECT untuk memformat dan
mengubah data yang dihasilkan.
Untuk menerapkan
sebuah fungsi pada nilai-nilai sebuah kolom, query memiliki sintaksis berikut:
SELECT FUNCTION(kolom) FROM namatabel
Untuk menerapkan
sebuah fungsi pada nilai-nilai dari satu kolom, sembari menyeleksi nilai-nilai
dari kolom lain, gunakan sintaksis berikut:
·
SELECT
*, FUNCTION(kolom) FROM namatabel
·
SELECT
kolom1, FUNCTION(kolom2), kolom3 FROM namatabel
Perhatikan pula
beberapa hal berikut. Pertama, fungsi seringkali digunakan pada data yang
disimpan, tetapi dapat pula diterapkan pada nilai literal. Kedua penggunaan
fungsi UPPER() berikut adalah valid:
SELECT UPPER(nama_depan) FROM pengguna
SELECT
UPPER('string ini')
Kedua, meskipun
nama fungsi berwatak case-insesitive, buku ini tetap menggunakan nama fungsi
dengan format semua huruf besar untuk membedakannya dari nama tabel dan nama
kolom. Ketiga, Anda tidak bisa memberikan spasi di antara nama fungsi dan
kurung pembuka di dalam MySQL.
5.10.1 Fungsi teks
Kelompok fungsi
pertama yang akan didemonstrasikan adalah fungsi-fungsi untuk memanipulasi
teks. Semua fungsi ini dicantumkan pada Tabel 5.2.
TABEL 5.2 Sejumlah fungsi
MySQL yang dipakai untuk teks.
Fungsi
|
Penggunaan
|
Menghasilkan
|
CONCAT()
|
CONCAT(t1, t2, ...)
|
Sebuah string baru dengan format t1t2.
|
CONCAT_WS()
|
CONCAT(S, t1, t2, ...)
|
Sebuah string baru dengan format t1St2S…
|
LENGTH()
|
LENGTH(t)
|
Banyak karakter di dalam teks t.
|
LEFT()
|
LEFT(t, y)
|
Sejumlah y karakter paling-kiri dari teks t.
|
RIGHT()
|
RIGHT(t, x)
|
Sejumlah x karakter paling-kanan dari teks t.
|
TRIM()
|
TRIM(t)
|
Teks t dengan spasi-spasi ekstra dihapus dari awal dan
akhir.
|
UPPER()
|
UPPER(t)
|
Teks t dijadikan huruf besar semua.
|
LOWER()
|
LOWER(t)
|
Teks t dijadikan huruf kecil semua.
|
SUBSTRING()
|
SUBSTRING(t, x, y)
|
Sejumlah y karakter dari teks t diawali dari x (indeks
dari 0).
|
CONCAT(),
mungkin merupakan fungsi teks yang paling sering digunakan, yang layak
memerlukan perhatian spesial dari Anda. Fungsi CONCAT() melakukan operasi
penyambungan. Sintaksis untuk penyambungan mensyaratkan Anda menempatkan, di
dalam kurung, beberapanilai yang akan disambung, secara berurutan dan
dipisahkan dengan koma:
SELECT
CONCAT(t1, t2) FROM namatabel
Ketika Anda
menggunakan CONCAT(), Anda juga dapat menyambung strings yang diapit di dalam
tanda kutip. Sebagai contoh, untuk memformat nama seseorang sebagai Nama_depan
<SPASI> Nama_belakang, Anda menggunakan
SELECT CONCAT(nama_depan, ' ', nama_belakang)
FROM pengguna
Karena penyambungan
biasanya menghasilkan nilai dalam format baru, adalah lebih baik jika
menggunakan alias:
SELECT CONCAT(nama_depan, ' ', nama_belakang)
AS
Nama FROM pengguna
Memformat teks
1. Sambung nama-nama tanpa menggunakan sebuah alias (Gambar 5.35).
SELECT CONCAT(nama_belakang, ', ', nama_depan)
FROM pengguna;
Query ini akan mendemonstrasikan dua
hal. Pertama, nama belakang user, sebuah koma dan sebuah spasi, ditambah dengan
nama depan pengguna disambung menjadi satu string. Kedua, seperti ditampilkan
pada gambar, jika Anda tidak menggunakan alias, maka kepala kolom dari data
yang dihasilkan akan berupa pemanggilan fungsi. Pada mysql client, ini tampak
tidak rapi; ketika menggunakan PHP untuk mengkoneksi ke MySQL, hal ini akan
menjadi masalah.
Gambar 5.35 Penyambungan
sederhana ini menghasilkan setiap nama penuh dari user yang telah melakukan
registrasi. Perhatikan bagaimana kepala kolom berupa pemanggilan fungsi.
2. Sambung nama-nama berikut menggunakan sebuah alias (Gambar 5.36).
SELECT CONCAT(nama_belakang, ', ',
nama_depan)
AS Nama FROM pengguna
ORDER BY Nama;
Untuk menggunakan alias, tambahkan
AS namaalias dan tempatkan tepat setelah item yang akan dinamai-ulang. Alias
akan menjadi judul baru untuk data yang dihasilkan. Untuk membuat query lebih
menarik, alias yang sama juga digunakan pada klausa ORDER BY.
Gambar 5.36 Dengan
menggunakan alias, data yang dihasilkan berada di bawah kepala kolom Nama
(bandingkan dengan Gambar 5.35).
3. Cari nama belakang terpanjang (Gambar 5.37).
SELECT LENGTH(nama_belakang) AS L,
nama_belakang
FROM pengguna
ORDER BY L DESC LIMIT 1;
Untuk menentukan nama belakang
terpanjang mana dari semua user yang telah melakukan registrasi (memiliki
jumlah karakter terbanyak), gunakan fungsi LENGTH().
Untuk mencari nama, seleksi nilai
nama belakang dan panjang yang dikalkulasi, yang diberikan dengan alias L.
Untuk mencari nama terpanjang, urutkan semua hasil berdasarkan L, dengan
tatanan menurun. Kemudian, hanya rekaman pertama yang akan dihasilkan.
Gambar 5.37 Dengan
menggunakan fungsi LENGTH(), sebuah alias, sebuah klausa ORDER BY, dan sebuah
klausa LIMIT, query ini menghasilkan panjang dan nilai dari nama belakang
terpanjang.
5.10.2 Fungsi Numerik
Di samping
operator-operator matematika standar yang digunakan MySQL (untuk penjumlahan,
pengurangan, perkalian, dan pembagian), ada sejumlah fungsi yang bisa dipakai
untuk memformat dan melakukan perhitungan atas nilai-nilai numerik. Tabel 5.3
mencantumkan sejumlah fungsi ini.
TABEL 5.3 Sejumlah fungsi
MySQL yang bisa diterapkan terhadap angka. Sama seperti fungsi lain,
fungsi-fungsi ini dapat diterapkan pada kolom maupun pada nilai literal.
Fungsi
|
Penggunaan
|
Menghasilkan
|
ABS()
|
ABS(n)
|
Nilai absolut dari n.
|
CEILING()
|
CEILING(n)
|
Nilai integer tertinggi terdekat berdasarkan nilai n.
|
FLOOR()
|
FLOOR(n)
|
Nilai integer dari n.
|
FORMAT()
|
FORMAT(n1, n2)
|
n1 diformat sebagai sebuah angka dengan n2 dijit desimal
dan koma disisipkan untuk tiap tiga dijit.
|
MOD()
|
MOD(n1, n2)
|
Sisa dari pembagian n1 oleh n2.
|
POW()
|
POW(n1, n2)
|
n1 dipangkat dengan n2.
|
RAND()
|
RAND()
|
Sebuah angka acak antara 0 dan 1.0.
|
ROUND()
|
ROUND(n1, n2)
|
n1 dibulatkan menjadi n2 dijit desimal.
|
SQRT()
|
SQRT(n)
|
Akar kuadrat dari n.
|
Di sini akan
difokuskan untuk mendemonstrasikan tiga fungsi: FORMAT(), ROUND(), dan RAND().
Fungsi pertama mengubah sembarang angka menjadi angka dengan tata-letak
terformat. Sebagai contoh, jika Anda menyimpan harga sebuah mobil sebagai
227393.35, maka FORMAT(harga_mobil, 2) akan menghasilkan angka 227,393.20.
Fungsi ROUND()
mengambil sebuah nilai, misalnya dari sebuah kolom, dan membulatkannya menjadi
sejumlah tertentu dijit desimal. Jika tidak ada jumlah dijit desimal yang
diberikan, fungsi ini akan membulatkannya menjadi integer terdekat. Jika jumlah
dijit desimal yang diberikan lebih dari angka asli, maka hasil akan diganjal
dengan nol.
Fungsi RAND()
digunakan untuk menghasilkan angka acak (Gambar 5.38).
SELECT RAND()
Keuntungan lain
dari fungsi RAND() adalah bahwa ia dapat dipakai pada query untuk menghasilkan
hasil dengan urutan acak.
SELECT
* FROM namatabel ORDER BY RAND()
Gambar 5.38 Fungsi RAND()
menghasilkan sebuah angka acak antara 0 dan 1.0.
Menggunakan fungsi numerik
1. Tampilkan sebuah angka, yang memformat angka sebagai nilai rupiah
(Gambar 5.39).
SELECT CONCAT('$', FORMAT(3682639,
2))
AS harga;
Gambar 5.39 Query ini
menunjukkan bagaimana fungsi FORMAT() digunakan.
Dengan menggunakan fungsi FORMAT(),
sama seperti dijelaskan sebelumnya, dengan CONCAT(), Anda bisa mengubah
sembarang nilai menjadi format moneter yang formal.
2. Baca alamat email secara acak dari tabel (Gambar 5.40).
SELECT email FROM pengguna
ORDER BY RAND() LIMIT 1;
Apa yang terjadi dengan query ini
adalah: Semua alamat email diseleksi; urutannya kemudian diacak (ORDER BY RAND());
dan kemudian rekaman pertama dihasilkan. Dengan menjalankan query yang sama
beberapa kali akan menghasilkan nilai acak yang berbeda. Perhatikan bahwa Anda
tidak menetapkan sebuah kolom ketika menerapkan RAND().
Gambar 5.40 Beberapa
eksekusi atas query yang sama menghasilkan hasil yang berbeda.
5.10.3 Fungsi tanggal dan waktu
Tipe kolom date
dan time pada MySQL sangat fleksibel. Tetapi karena banyak pengguna database
tidak familier dengan fungsi-fungsi tanggal dan waktu yang tersedia, opsi-opsi
ini sering tidak dimanfaatkan secara optimal. Tabel 5.4 mencantumkan sejumlah
fungsi yang terkait dengan tanggal dan waktu.
TABEL 5.4 Sejumlah fungsi
MySQL yang dapat diterapkan terhadap tanggal dan waktu. Sama seperti fungsi
lain, fungsi-fungsi ini dapat diterapkan pada kolom maupun pada nilai literal.
Fungsi
|
Penggunaan
|
Menghasilkan
|
HOUR()
|
HOUR(dt)
|
Nilai jam dari dt.
|
MINUTE()
|
MINUTE(dt)
|
Nilai menit dari dt.
|
SECOND()
|
SECOND(dt)
|
Nilai detik dari dt.
|
DAYNAME()
|
DAYNAME(dt)
|
Nama hari untuk dt.
|
DAYOFMONTH()
|
DAYOFMONTH(dt)
|
Nilai hari numerik untuk dt.
|
MONTHNAME()
|
MONTHNAME(dt)
|
Nama bulan untuk dt.
|
MONTH()
|
MONTH(dt)
|
Nilai bulan numerik untuk dt.
|
YEAR()
|
YEAR(dt)
|
Nilai tahun untuk dt.
|
CURDATE()
|
CURDATE()
|
Tanggal sekarang.
|
CURTIME()
|
CURTIME()
|
Waktu sekarang.
|
NOW()
|
NOW()
|
Tanggal dan waktu sekarang.
|
UNIX_TIMESTAMP()
|
UNIX_TIMESTAMP(dt)
|
Jumlah detik sejak epoch sampai waktu sekarang atau sampai
tanggal tertentu yang ditetapkan.
|
Menggunakan fungsi tanggal dan waktu
1. Tampilkan tanggal dimana user terakhir melakukan registrasi (Gambar
5.41).
SELECT DATE(tanggal_registrasi)
AS Tanggal
FROM pengguna
ORDER BY tanggal_registrasi DESC
LIMIT 1;
Gambar 5.41 Fungsi-fungsi
tanggal dapat dipakai untuk mengekstrak informasi dari nilai-nilai yang
disimpan.
Fungsi DATE() menghasilkan komponen
tanggal dari sebuah nilai. Untuk melihat tanggal dimana user terakhir melakukan
registrasi, klausa ORDER BY mencantumkan semua user yang dimuali dari user yang
paling awal melakukan registrasi dan hasil ini dibatasi hanya satu rekaman.
2. Tampilkan hari dimana user pertama melakukan registrasi (Gambar 5.42).
SELECT DAYNAME(tanggal_registrasi)
AS hari FROM pengguna
ORDER BY tanggal_registrasi ASC LIMIT
1;
Ini sama dengan query pada langkah 1
tetapi hasil ditata dengan urutan menurun dan nama fungsi DAYNAME() diterapkan
pada kolom tanggal_registrasi. Fungsi ini menghasilkan Sunday, Monday, Tuesday,
dan seterusnya dari sebuah tanggal.
Gambar 5.42 Query ini
menghasilkan nama hari dari sebuah tanggal.
3. Tampilkan tanggal dan waktu sekarang, menurut MySQL (Gambar 5.43).
SELECT CURDATE(), CURTIME();
Untuk menampilkan tanggal dan waktu
sekarang, Anda dapat menggunakan fungsi CURDATE() dan CURTIME(). Ini merupakan
contoh lain dari sebuah query yang dapat dijalankan tanpa perlu pengaksesan
nama tabel tertentu.
Gambar 5.43 Query ini,
tidak dijalankan pada tabel tertentu, menghasilkan tanggal dan waktu sekarang
pada server MySQL.
4. Tampilkan hari terakhir pada bulan sekarang (Gambar 5.44).
SELECT LAST_DAY(CURDATE()),
MONTHNAME(CURDATE());
Gambar 5.44 MySQL dapat
menentukan tanggal terakhir pada sebuah bulan atau nilai nama dari sebuah
tanggal.
5.10.4 Memformat tanggal dan waktu
Ada dua fungsi
tanggal dan waktu lain yang dapat digunakan: DATE_FORMAT() dan TIME_FORMAT().
Fungsi DATE_FORMAT() dapat digunakan untuk memformat tanggal dan waktu jika
sebuah nilai memuat keduanya (misalnya, YYYY-MM-DD HH:MM:SS). Sedangkan fungsi
TIME_FORMAT() hanya dapat dipakai untuk memformat nilai waktu (misalnya,
HH:MM:SS). Sintaksisnya adalah
SELECT
DATE_FORMAT(tanggalwaktu, parameter
format)
Tabel 5.5
mencantumkan parameter-parameter pemformatan waktu dan pemformatan tanggal.
Anda dapat mengkombinasikannya.
TABEL 5.5 Parameter-parameter
format untuk fungsi DATE_FORMAT() dan TIME_FORMAT().
Fungsi
|
Penjelasan
|
Contoh
|
%e
|
Nomor hari
|
1-31
|
%d
|
Nomor hari, 2
dijit
|
01-31
|
%D
|
Hari dengan
sufiks
|
1st-31st
|
%W
|
Nama hari
|
Sunday-Saturday
|
%a
|
Nama hari,
disingkat
|
Sun-Sat
|
%c
|
Nomor bulan
|
1-12
|
%m
|
Nomor bulan, 2
dijit
|
01-12
|
%M
|
Nama bulan
|
January-December
|
%b
|
Nama bulan,
disingkat
|
Jan-Dec
|
%Y
|
Tahun
|
2014
|
%y
|
Tahun
|
02
|
%l
|
Jam
|
1-12
|
%h
|
Jam,
2 dijit
|
01-12
|
%k
|
Jam,
format 24-jam
|
0-23
|
%H
|
Jam,
format 24-jam, 2 dijit
|
00-23
|
%i
|
Menit
|
00-59
|
%S
|
Detik
|
00-59
|
%r
|
Waktu
|
8:17:02 PM
|
%T
|
Waktu,
format 24-jam
|
20:17:02
|
%p
|
AM atau PM
|
AM atau PM
|
Diasumsikan
bahwa sebuah kolom, tanggal, memiliki tanggal dan waktu 2014-04-20 11:07:45
disimpan di dalamnya, maka dapat disimpulkan bahwa
·
Waktu(11:07:45 AM)
TIME_FORMAT(tanggal,
‘%r’)
·
Waktu tanpa
detik (11:07 AM)
TIME_FORMAT(tanggal,
‘%l:%i %p)
·
Tanggal (April
20th, 2014)
DATE_FORMAT(tanggal,
‘%M %D, %Y’)
Memformat tanggal dan waktu
1. Hasilkan tanggal dan waktu sekarang sebagai Bulan DD, YYYY-HH:MM (Gambar
5.45).
SELECT DATE_FORMAT(NOW(),'%M %e, %Y - %l:%i');
Gambar 5.45 Tanggal dan
waktu sekarang, terformat.
Dengan menggunakan fungsi NOW, yang
menghasilkan tanggal dan waktu sekarang, Anda dapat mempraktekkan pemformatan
untuk melihat hasil yang diberikan.
2. Tampilkan waktu sekarang, menggunakan notasi 24-jam (Gambar 5.46).
SELECT TIME_FORMAT(CURTIME(),'%T');
Gambar 5.46 Waktu
sekarang, dalam format 24-jam.
3. Pilih alamat email dan tanggal registrasi, yang diurutkan berdasarkan
tanggal registrasi, dengan tanggal yang diformat sebagai Hari (disingkat) Bulan
(disingkat) Hari Tahun, untuk lima user yang mendaftar terakhir (Gambar 5.47).
SELECT email, DATE_FORMAT(tanggal_registrasi,
'%a %b %e %Y')
AS Tanggal FROM pengguna
ORDER BY tanggal_registrasi DESC
LIMIT 5;
Gambar 5.47 Fungsi
DATE_FORMAT() dipakai untuk memformat tanggal registrasi ketika menyeleksi
rekaman-rekaman dari tabel pengguna.
No comments:
Post a Comment