Fungsi Lanjutan MySQL
Mempelajari fitur-fitur canggih MySQL untuk manipulasi data yang lebih kompleks
1. Fungsi String
MySQL menyediakan berbagai fungsi untuk memanipulasi string:
Fungsi Dasar
-- CONCAT: Menggabungkan string
SELECT CONCAT(nama_depan, ' ', nama_belakang) AS nama_lengkap FROM users;
-- LENGTH: Panjang string (dalam byte)
SELECT nama, LENGTH(nama) AS panjang FROM siswa;
-- CHAR_LENGTH: Panjang string (dalam karakter)
SELECT nama, CHAR_LENGTH(nama) AS panjang_karakter FROM siswa;
-- UPPER/LOWER: Mengubah huruf besar/kecil
SELECT UPPER(nama) FROM siswa;
SELECT LOWER(email) FROM users;
-- TRIM: Menghapus spasi di awal dan akhir
SELECT TRIM(' Hello ') AS hasil; -- Hasil: 'Hello'
-- SUBSTRING: Mengambil bagian dari string
SELECT SUBSTRING(nama, 1, 3) AS inisial FROM siswa;
Fungsi Pencarian dan Penggantian
-- REPLACE: Mengganti substring
SELECT REPLACE(alamat, 'Jalan', 'Jl.') FROM siswa;
-- LOCATE/INSTR: Mencari posisi substring
SELECT nama, LOCATE('a', nama) AS posisi_a FROM siswa;
-- LEFT/RIGHT: Mengambil n karakter dari kiri/kanan
SELECT LEFT(nama, 1) AS inisial FROM siswa;
2. Fungsi Numerik
-- ABS: Nilai absolut
SELECT ABS(-10); -- Hasil: 10
-- ROUND: Pembulatan
SELECT ROUND(4.567, 1); -- Hasil: 4.6
-- CEIL/FLOOR: Pembulatan ke atas/bawah
SELECT CEIL(4.2); -- Hasil: 5
SELECT FLOOR(4.9); -- Hasil: 4
-- MOD: Modulus/sisa bagi
SELECT MOD(10, 3); -- Hasil: 1
-- POW: Pangkat
SELECT POW(2, 3); -- Hasil: 8
-- RAND: Angka acak
SELECT RAND(); -- Angka acak antara 0 dan 1
3. Fungsi Tanggal dan Waktu
-- NOW: Tanggal dan waktu sekarang
SELECT NOW(); -- Format: 'YYYY-MM-DD HH:MM:SS'
-- CURDATE/CURTIME: Tanggal/waktu sekarang
SELECT CURDATE(), CURTIME();
-- DATE_FORMAT: Formatting tanggal
SELECT DATE_FORMAT(NOW(), '%W, %d %M %Y'); -- Contoh: 'Monday, 15 May 2023'
-- DATEDIFF: Selisih hari antara dua tanggal
SELECT DATEDIFF('2023-12-31', NOW()) AS hari_menuju_tahun_baru;
-- DATE_ADD/DATE_SUB: Menambah/mengurangi waktu
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);
SELECT DATE_SUB(NOW(), INTERVAL 1 WEEK);
-- EXTRACT: Mengambil bagian dari tanggal
SELECT EXTRACT(YEAR FROM NOW()) AS tahun;
4. Control Flow Functions
-- IF: Kondisi sederhana
SELECT nama, IF(nilai >= 75, 'Lulus', 'Tidak Lulus') AS status FROM nilai_siswa;
-- CASE: Kondisi kompleks
SELECT nama, nilai,
CASE
WHEN nilai >= 90 THEN 'A'
WHEN nilai >= 80 THEN 'B'
WHEN nilai >= 70 THEN 'C'
WHEN nilai >= 60 THEN 'D'
ELSE 'E'
END AS grade
FROM nilai_siswa;
-- IFNULL/COALESCE: Menangani NULL
SELECT nama, IFNULL(alamat, 'Alamat tidak diketahui') FROM siswa;
SELECT COALESCE(alamat, alamat_orangtua, 'Alamat tidak diketahui') FROM siswa;
5. Subquery
Query di dalam query lain, bisa digunakan di SELECT, FROM, WHERE, dll.
Subquery di WHERE
-- Siswa dengan nilai di atas rata-rata
SELECT nama
FROM siswa
WHERE id IN (
SELECT siswa_id
FROM nilai
WHERE nilai > (SELECT AVG(nilai) FROM nilai)
);
Subquery di FROM
-- Rata-rata nilai per kelas
SELECT k.nama_kelas, AVG(n.rata_nilai) AS rata_kelas
FROM kelas k
JOIN (
SELECT siswa_id, kelas_id, AVG(nilai) AS rata_nilai
FROM nilai n
JOIN siswa s ON n.siswa_id = s.id
GROUP BY siswa_id, kelas_id
) n ON k.id = n.kelas_id
GROUP BY k.id;
Subquery di SELECT
-- Menampilkan jumlah siswa di setiap kelas
SELECT k.nama_kelas,
(SELECT COUNT(*) FROM siswa s WHERE s.kelas_id = k.id) AS jumlah_siswa
FROM kelas k;
6. View
View adalah query yang disimpan sebagai tabel virtual.
-- Membuat view
CREATE VIEW view_siswa_kelas AS
SELECT s.nama, k.nama_kelas, k.wali_kelas
FROM siswa s
JOIN kelas k ON s.kelas_id = k.id;
-- Menggunakan view
SELECT * FROM view_siswa_kelas WHERE nama_kelas = '10 IPA';
-- Mengubah view
ALTER VIEW view_siswa_kelas AS
SELECT s.id, s.nama, k.nama_kelas, k.wali_kelas
FROM siswa s
JOIN kelas k ON s.kelas_id = k.id;
-- Menghapus view
DROP VIEW view_siswa_kelas;
Keuntungan View:
- Menyederhanakan query kompleks
- Keamanan data (bisa membatasi kolom yang bisa diakses)
- Konsistensi logika bisnis
7. Stored Procedures
Kumpulan pernyataan SQL yang disimpan di database.
-- Membuat stored procedure
DELIMITER //
CREATE PROCEDURE sp_get_siswa_by_kelas(IN kelas_name VARCHAR(10))
BEGIN
SELECT s.nama, s.alamat
FROM siswa s
JOIN kelas k ON s.kelas_id = k.id
WHERE k.nama_kelas = kelas_name;
END //
DELIMITER ;
-- Memanggil stored procedure
CALL sp_get_siswa_by_kelas('10 IPA');
-- Menghapus stored procedure
DROP PROCEDURE IF EXISTS sp_get_siswa_by_kelas;
8. Trigger
Kode yang dijalankan otomatis ketika event tertentu terjadi di tabel.
-- Contoh: Mencatat perubahan nilai
CREATE TABLE log_perubahan_nilai (
id INT AUTO_INCREMENT PRIMARY KEY,
siswa_id INT,
mapel_id INT,
nilai_lama DECIMAL(5,2),
nilai_baru DECIMAL(5,2),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
changed_by VARCHAR(50)
);
DELIMITER //
CREATE TRIGGER after_nilai_update
AFTER UPDATE ON nilai
FOR EACH ROW
BEGIN
IF OLD.nilai != NEW.nilai THEN
INSERT INTO log_perubahan_nilai
(siswa_id, mapel_id, nilai_lama, nilai_baru, changed_by)
VALUES
(OLD.siswa_id, OLD.mapel_id, OLD.nilai, NEW.nilai, CURRENT_USER());
END IF;
END //
DELIMITER ;
9. Indexing untuk Optimasi
Index mempercepat query tetapi memperlambat INSERT/UPDATE/DELETE.
-- Membuat index
CREATE INDEX idx_siswa_nama ON siswa(nama);
-- Index unique
CREATE UNIQUE INDEX idx_siswa_email ON siswa(email);
-- Composite index
CREATE INDEX idx_nilai_siswa_mapel ON nilai(siswa_id, mapel_id);
-- Menampilkan index
SHOW INDEX FROM siswa;
-- Menghapus index
DROP INDEX idx_siswa_nama ON siswa;