belajarkoding © 2025 BelajarKoding. All rights reserved.
SQL Cheat Sheet - BelajarKoding | BelajarKoding
SQL Cheat Sheet Referensi cepat SQL commands. Dari basic CRUD sampe advanced JOIN dan aggregation. Must-have buat backend developer.
Lanjutkan Membaca
Daftar gratis untuk akses penuh ke semua artikel dan cheat sheet. Cepat, mudah, dan tanpa biaya!
Akses Tanpa Batas
Baca semua artikel dan cheat sheet kapan pun kamu mau
Bookmark Konten
Simpan artikel dan roadmap favoritmu untuk dibaca nanti
Gratis Selamanya
Tidak ada biaya tersembunyi, 100% gratis
Dengan mendaftar, kamu setuju dengan syarat dan ketentuan kami
# Data TypesBerbagai tipe data yang tersedia dalam SQL untuk mendefinisikan kolom tabel dengan tepat.
# NumericTipe data untuk menyimpan angka dengan berbagai range dan presisi.
-- Integer INT -- -2147483648 to 2147483647 BIGINT -- Angka lebih besar SMALLINT -- -32768 to 32767 TINYINT -- 0 to 255 (MySQL)
-- Decimal DECIMAL (
10
,
2
)
-- 10 digit total, 2 di belakang koma
NUMERIC ( 10 , 2 ) -- Sama kayak DECIMAL
FLOAT -- Floating point
DOUBLE -- Double precision
Tipe data untuk menyimpan teks dan string dengan berbagai panjang.
-- Fixed length CHAR ( 10 ) -- Fixed 10 karakter
-- Variable length (hemat storage) VARCHAR ( 255 ) -- Max 255 karakter VARCHAR (MAX) -- Max size (SQL Server)
-- Text panjang TEXT -- Unlimited text MEDIUMTEXT -- 16MB max (MySQL) LONGTEXT -- 4GB max (MySQL) Tipe data untuk menyimpan tanggal dan waktu.
-- Date only DATE -- YYYY-MM-DD
-- Time only TIME -- HH:MM:SS
-- Date + Time DATETIME -- YYYY-MM-DD HH:MM:SS TIMESTAMP -- Sama, tapi auto timezone aware
-- Year only YEAR -- YYYY Tipe data khusus lainnya seperti boolean, JSON, dan binary.
-- Boolean BOOLEAN -- TRUE/FALSE (PostgreSQL) TINYINT ( 1 ) -- 0/1 (MySQL)
-- JSON JSON -- Store JSON data JSONB -- Binary JSON (PostgreSQL, faster)
-- Binary BLOB -- Binary data VARBINARY ( 255 ) -- Variable binary # Database & Table OperationsOperasi dasar untuk mengelola database dan tabel.
Perintah untuk membuat, menghapus, dan mengelola database.
-- Create database CREATE DATABASE my_database ;
-- Drop database DROP DATABASE my_database;
-- Use database USE my_database;
-- Show databases SHOW DATABASES; -- MySQL \l -- PostgreSQL Perintah untuk membuat, menghapus, dan melihat struktur tabel.
-- Create table CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR ( 100 ) NOT NULL , email VARCHAR ( 255 ) UNIQUE NOT NULL , Perintah untuk memodifikasi struktur tabel yang sudah ada.
-- Tambah kolom ALTER TABLE users ADD phone VARCHAR ( 20 );
-- Hapus kolom ALTER TABLE users DROP COLUMN phone;
-- Ubah tipe kolom ALTER TABLE users MODIFY age BIGINT Perintah SELECT untuk mengambil data dari database dengan berbagai kondisi dan filter.
Cara dasar menggunakan SELECT untuk mengambil data dari tabel.
-- Semua kolom SELECT * FROM users;
-- Kolom tertentu SELECT name , email FROM users;
-- Alias kolom SELECT name AS full_name, email AS contact FROM users;
-- Distinct values SELECT DISTINCT Menggunakan WHERE untuk memfilter data berdasarkan kondisi tertentu.
-- Basic filter SELECT * FROM users WHERE age > 18 ;
-- Multiple conditions SELECT * FROM users WHERE age > Mengurutkan hasil query berdasarkan kolom tertentu.
-- Ascending (default) SELECT * FROM users ORDER BY name ; SELECT * FROM users ORDER BY name ASC ;
-- Descending SELECT * FROM users ORDER BY created_at DESC ;
-- Group dengan aggregate SELECT city, COUNT ( * ) FROM users GROUP BY city;
-- Multiple columns SELECT city, status , COUNT ( * ) FROM users GROUP BY city, status ; -- Filter grouped data SELECT city, COUNT ( * ) as total FROM users GROUP BY city HAVING COUNT ( * ) > 100 ;
-- Multiple conditions SELECT city, -- Insert single row INSERT INTO users ( name , email, age) VALUES ( 'Andi' , 'andi@mail.com' , 25 );
-- Insert multiple rows INSERT INTO users ( name -- Update single column UPDATE users SET age = 26 WHERE id = 1 ;
-- Update multiple columns UPDATE users SET age = 26 , city = 'Jakarta' WHERE -- Delete specific row DELETE FROM users WHERE id = 5 ;
-- Delete dengan condition DELETE FROM users WHERE age < 18 ;
-- Delete semua rows (hati-hati!) DELETE FROM users;
-- Count rows SELECT COUNT ( * ) FROM users; SELECT COUNT ( DISTINCT city) FROM users;
-- Sum SELECT SUM (price) FROM products; -- Concatenate SELECT CONCAT (first_name, ' ' , last_name) as full_name FROM users; SELECT first_name || ' ' || last_name as full_name FROM -- Current date/time SELECT NOW (); -- Current timestamp SELECT CURDATE(); -- Current date (MySQL) SELECT CURRENT_DATE; -- Current date (standard) SELECT CURRENT_TIME; -- Current time -- CASE statement SELECT name , age, CASE WHEN age < 18 THEN 'Minor' WHEN age >= 18 AND age < 60 THEN -- INNER JOIN (only matching rows) SELECT users . name , orders . total FROM users INNER JOIN orders ON users -- Subquery di WHERE SELECT name FROM users WHERE id IN ( SELECT user_id FROM orders WHERE total > 1000000 );
-- Subquery di SELECT SELECT -- UNION (combine results, remove duplicates) SELECT name FROM users_2024 UNION SELECT name FROM users_2025;
-- UNION ALL (keep duplicates) SELECT name FROM users_2024 UNION ALL SELECT name FROM users_2025;
-- INTERSECT (rows in both) -- Create index CREATE INDEX idx_email ON users(email);
-- Unique index CREATE UNIQUE INDEX idx_email ON users(email);
-- Composite index CREATE INDEX idx_name_city ON users( name , city);
-- Drop index -- Primary Key CREATE TABLE users ( id INT PRIMARY KEY );
-- Foreign Key CREATE TABLE orders ( id INT PRIMARY KEY , user_id -- Start transaction START TRANSACTION ; BEGIN ; -- Alternative
-- Commit (save changes) COMMIT ;
-- Rollback (undo changes) ROLLBACK ;
-- Example START TRANSACTION ;
UPDATE accounts SET -- Create view CREATE VIEW active_users AS SELECT id, name , email FROM users WHERE status = 'active' ;
-- Query view (like table) SELECT * FROM active_users;
# Common Table Expressions (CTE)-- Basic CTE WITH active_users AS ( SELECT * FROM users WHERE status = 'active' ) SELECT * FROM active_users WHERE age > -- ROW_NUMBER SELECT name , age, ROW_NUMBER () OVER ( ORDER BY age DESC ) as rank FROM users;
-- RANK & DENSE_RANK -- Use EXPLAIN untuk analyze query EXPLAIN SELECT * FROM users WHERE email = 'test@mail.com' ;
-- Hindari SELECT * SELECT id, name , email FROM users; -- Pagination SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 0 ; -- Page 1
-- Search dengan ranking SELECT -- NEVER do this (SQL Injection risk!) -- SELECT * FROM users WHERE email = '" + userInput + "'
-- Use prepared statements (application level) -- Example di berbagai bahasa:
-- PHP PDO -- $stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?"); -- $stmt->execute([$email]);
-- Node.js (PostgreSQL) -- const result = await pool.query("SELECT * FROM users WHERE email = $1", [email]);
-- Python age INT ,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Drop table
DROP TABLE users;
-- Drop jika exist
DROP TABLE IF EXISTS users;
-- Show tables
SHOW TABLES; -- MySQL
\dt -- PostgreSQL
-- Describe table structure
DESCRIBE users; -- MySQL
\d users -- PostgreSQL
-- Rename table
ALTER TABLE users RENAME TO customers;
;
-- MySQL
ALTER TABLE users ALTER COLUMN age TYPE BIGINT ; -- PostgreSQL
-- Rename kolom
ALTER TABLE users RENAME COLUMN name TO full_name;
-- Add constraint
ALTER TABLE users ADD UNIQUE (email);
ALTER TABLE users ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
city
FROM
users;
-- Limit results
SELECT * FROM users LIMIT 10 ; -- First 10
SELECT * FROM users LIMIT 10 OFFSET 20 ; -- Skip 20, take 10
18
AND
city
=
'Jakarta'
;
SELECT * FROM users WHERE age > 18 OR age < 13 ;
-- NOT
SELECT * FROM users WHERE NOT city = 'Jakarta' ;
-- IN
SELECT * FROM users WHERE city IN ( 'Jakarta' , 'Bandung' , 'Surabaya' );
-- BETWEEN
SELECT * FROM users WHERE age BETWEEN 18 AND 30 ;
-- LIKE (pattern matching)
SELECT * FROM users WHERE name LIKE 'And%' ; -- Mulai dengan "And"
SELECT * FROM users WHERE name LIKE '%son' ; -- Berakhir dengan "son"
SELECT * FROM users WHERE name LIKE '%and%' ; -- Contain "and"
SELECT * FROM users WHERE name LIKE '_____' ; -- Exactly 5 characters
-- Case insensitive (PostgreSQL)
SELECT * FROM users WHERE name ILIKE 'john%' ;
-- NULL check
SELECT * FROM users WHERE phone IS NULL ;
SELECT * FROM users WHERE phone IS NOT NULL ;
-- Multiple columns
SELECT * FROM users ORDER BY city ASC , name DESC ;
-- Order by position
SELECT name , age FROM users ORDER BY 2 DESC ; -- Order by 2nd column (age)
-- Dengan aggregate functions
SELECT city, AVG (age) as avg_age FROM users GROUP BY city;
-- Order by aggregate
SELECT city, COUNT ( * ) as total FROM users GROUP BY city ORDER BY total DESC ;
AVG
(age)
as
avg_age
FROM users
GROUP BY city
HAVING AVG (age) > 25 AND COUNT ( * ) > 50 ;
-- WHERE vs HAVING
SELECT city, COUNT ( * ) as total
FROM users
WHERE age > 18 -- Filter rows sebelum grouping
GROUP BY city
HAVING COUNT ( * ) > 100 ; -- Filter groups setelah grouping
, email, age)
VALUES
( 'Budi' , 'budi@mail.com' , 23 ),
( 'Citra' , 'citra@mail.com' , 27 ),
( 'Doni' , 'doni@mail.com' , 30 );
-- Insert semua kolom (ga recommended)
INSERT INTO users VALUES ( 1 , 'Eko' , 'eko@mail.com' , 28 , NOW ());
-- Insert dengan default values
INSERT INTO users ( name , email) VALUES ( 'Fani' , 'fani@mail.com' );
-- Insert from SELECT
INSERT INTO users_backup SELECT * FROM users WHERE age > 30 ;
-- Insert atau update jika exist (MySQL)
INSERT INTO users (id, name , email)
VALUES ( 1 , 'Andi' , 'andi@mail.com' )
ON DUPLICATE KEY UPDATE name = 'Andi' , email = 'andi@mail.com' ;
-- Insert atau skip jika exist (PostgreSQL)
INSERT INTO users (id, name , email)
VALUES ( 1 , 'Andi' , 'andi@mail.com' )
ON CONFLICT (id) DO NOTHING;
id
=
1
;
-- Update dengan calculation
UPDATE products SET price = price * 1 . 1 WHERE category = 'electronics' ;
-- Update semua rows (hati-hati!)
UPDATE users SET status = 'active' ;
-- Update dari table lain
UPDATE users u
SET city = ( SELECT city FROM cities c WHERE c . id = u . city_id );
-- Update dengan JOIN (MySQL)
UPDATE users u
JOIN orders o ON u . id = o . user_id
SET u . total_orders = o . order_count ;
-- Delete faster (truncate)
TRUNCATE TABLE users;
-- Delete dengan subquery
DELETE FROM users WHERE id IN ( SELECT user_id FROM banned_users);
-- Delete dengan JOIN (MySQL)
DELETE u FROM users u
JOIN inactive_accounts i ON u . id = i . user_id ;
SELECT SUM (quantity * price) as total FROM orders;
-- Average
SELECT AVG (age) FROM users;
SELECT AVG (price) FROM products WHERE category = 'electronics' ;
-- Min & Max
SELECT MIN (age), MAX (age) FROM users;
SELECT MIN (price), MAX (price) FROM products;
-- Multiple aggregates
SELECT
COUNT ( * ) as total_users,
AVG (age) as avg_age,
MIN (age) as youngest,
MAX (age) as oldest
FROM users;
-- Group concat (gabungin values)
SELECT GROUP_CONCAT( name ) FROM users; -- MySQL
SELECT STRING_AGG ( name , ', ' ) FROM users; -- PostgreSQL
users;
-- PostgreSQL
-- Uppercase & Lowercase
SELECT UPPER ( name ) FROM users;
SELECT LOWER (email) FROM users;
-- Substring
SELECT SUBSTRING ( name , 1 , 3 ) FROM users; -- First 3 chars
SELECT LEFT ( name , 3 ) FROM users; -- Left 3 chars
SELECT RIGHT ( name , 3 ) FROM users; -- Right 3 chars
-- Length
SELECT LENGTH ( name ) FROM users;
SELECT CHAR_LENGTH( name ) FROM users; -- Character length
-- Trim whitespace
SELECT TRIM ( name ) FROM users;
SELECT LTRIM ( name ) FROM users; -- Left trim
SELECT RTRIM ( name ) FROM users; -- Right trim
-- Replace
SELECT REPLACE (email, '@gmail.com' , '@mail.com' ) FROM users;
SELECT CURRENT_TIMESTAMP; -- Current timestamp
-- Extract parts
SELECT YEAR (created_at) FROM users;
SELECT MONTH (created_at) FROM users;
SELECT DAY (created_at) FROM users;
SELECT HOUR (created_at) FROM users;
-- Date arithmetic
SELECT DATE_ADD(created_at, INTERVAL 7 DAY ) FROM users; -- MySQL
SELECT created_at + INTERVAL '7 days' FROM users; -- PostgreSQL
SELECT DATE_SUB(created_at, INTERVAL 1 MONTH ) FROM users; -- MySQL
SELECT created_at - INTERVAL '1 month' FROM users; -- PostgreSQL
-- Date difference
SELECT DATEDIFF ( NOW (), created_at) as days_ago FROM users; -- MySQL
SELECT NOW () - created_at as diff FROM users; -- PostgreSQL
-- Format date
SELECT DATE_FORMAT (created_at, '%Y-%m-%d' ) FROM users; -- MySQL
SELECT TO_CHAR(created_at, 'YYYY-MM-DD' ) FROM users; -- PostgreSQL
'Adult'
ELSE 'Senior'
END as age_group
FROM users;
-- Simple CASE
SELECT
name ,
CASE status
WHEN 'active' THEN 'Active User'
WHEN 'inactive' THEN 'Inactive User'
ELSE 'Unknown'
END as status_label
FROM users;
-- COALESCE (return first non-null)
SELECT COALESCE (phone, email, 'No contact' ) as contact FROM users;
-- NULLIF (return NULL if equal)
SELECT NULLIF ( status , 'unknown' ) FROM users;
-- IFNULL / ISNULL
SELECT IFNULL (phone, 'N/A' ) FROM users; -- MySQL
SELECT COALESCE (phone, 'N/A' ) FROM users; -- Standard
.
id
=
orders
.
user_id
;
-- LEFT JOIN (all from left, matching from right)
SELECT users . name , orders . total
FROM users
LEFT JOIN orders ON users . id = orders . user_id ;
-- RIGHT JOIN (all from right, matching from left)
SELECT users . name , orders . total
FROM users
RIGHT JOIN orders ON users . id = orders . user_id ;
-- FULL OUTER JOIN (all from both)
SELECT users . name , orders . total
FROM users
FULL OUTER JOIN orders ON users . id = orders . user_id ;
-- CROSS JOIN (cartesian product)
SELECT users . name , products . name
FROM users
CROSS JOIN products;
-- Self JOIN
SELECT
e1 . name as employee,
e2 . name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1 . manager_id = e2 . id ;
-- Multiple JOINs
SELECT u . name , p . title , c . content
FROM users u
INNER JOIN posts p ON u . id = p . user_id
INNER JOIN comments c ON p . id = c . post_id ;
-- JOIN dengan condition tambahan
SELECT u . name , o . total
FROM users u
LEFT JOIN orders o ON u . id = o . user_id AND o . status = 'completed' ;
name ,
( SELECT COUNT ( * ) FROM orders WHERE user_id = users . id ) as total_orders
FROM users;
-- Subquery di FROM
SELECT avg_age FROM (
SELECT city, AVG (age) as avg_age FROM users GROUP BY city
) as city_stats
WHERE avg_age > 25 ;
-- EXISTS
SELECT name FROM users u
WHERE EXISTS ( SELECT 1 FROM orders o WHERE o . user_id = u . id );
-- NOT EXISTS
SELECT name FROM users u
WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o . user_id = u . id );
-- Correlated subquery
SELECT name ,
( SELECT MAX (total) FROM orders WHERE user_id = users . id ) as max_order
FROM users;
SELECT email FROM users_active
INTERSECT
SELECT email FROM users_premium;
-- EXCEPT (rows in first but not second)
SELECT email FROM users_all
EXCEPT
SELECT email FROM users_banned;
DROP INDEX idx_email ON users; -- MySQL
DROP INDEX idx_email; -- PostgreSQL
-- Show indexes
SHOW INDEX FROM users; -- MySQL
\di -- PostgreSQL
-- Full text index (MySQL)
CREATE FULLTEXT INDEX idx_content ON posts(title, content);
INT
,
FOREIGN KEY (user_id) REFERENCES users(id)
);
-- Foreign Key dengan action
CREATE TABLE orders (
id INT PRIMARY KEY ,
user_id INT ,
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
-- NOT NULL
CREATE TABLE users (
name VARCHAR ( 100 ) NOT NULL
);
-- UNIQUE
CREATE TABLE users (
email VARCHAR ( 255 ) UNIQUE
);
-- CHECK constraint
CREATE TABLE users (
age INT CHECK (age >= 18 )
);
-- DEFAULT
CREATE TABLE users (
status VARCHAR ( 20 ) DEFAULT 'active' ,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
balance
=
balance
-
100
WHERE
id
=
1
;
UPDATE accounts SET balance = balance + 100 WHERE id = 2 ;
COMMIT ;
-- Savepoint
START TRANSACTION ;
UPDATE users SET status = 'active' WHERE id = 1 ;
SAVEPOINT sp1;
UPDATE users SET status = 'inactive' WHERE id = 2 ;
ROLLBACK TO sp1; -- Undo last update only
COMMIT ;
-- Create or replace
CREATE OR REPLACE VIEW active_users AS
SELECT id, name , email, city FROM users WHERE status = 'active' ;
-- Drop view
DROP VIEW active_users;
-- Materialized view (PostgreSQL)
CREATE MATERIALIZED VIEW user_stats AS
SELECT city, COUNT ( * ) as total FROM users GROUP BY city;
-- Refresh materialized view
REFRESH MATERIALIZED VIEW user_stats;
18
;
-- Multiple CTEs
WITH
active_users AS (
SELECT * FROM users WHERE status = 'active'
),
recent_orders AS (
SELECT * FROM orders WHERE created_at > NOW () - INTERVAL '30 days'
)
SELECT u . name , COUNT ( o . id ) as order_count
FROM active_users u
LEFT JOIN recent_orders o ON u . id = o . user_id
GROUP BY u . name ;
-- Recursive CTE
WITH RECURSIVE subordinates AS (
SELECT id, name , manager_id FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e . id , e . name , e . manager_id
FROM employees e
INNER JOIN subordinates s ON s . id = e . manager_id
)
SELECT * FROM subordinates;
SELECT
name ,
score,
RANK () OVER ( ORDER BY score DESC ) as rank,
DENSE_RANK () OVER ( ORDER BY score DESC ) as dense_rank
FROM users;
-- PARTITION BY
SELECT
city,
name ,
age,
ROW_NUMBER () OVER ( PARTITION BY city ORDER BY age DESC ) as rank_in_city
FROM users;
-- Running total
SELECT
date ,
amount,
SUM (amount) OVER ( ORDER BY date ) as running_total
FROM orders;
-- Moving average
SELECT
date ,
amount,
AVG (amount) OVER ( ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) as avg_7_days
FROM orders;
-- LAG & LEAD (prev/next row)
SELECT
date ,
amount,
LAG (amount, 1 ) OVER ( ORDER BY date ) as prev_day,
LEAD (amount, 1 ) OVER ( ORDER BY date ) as next_day
FROM orders;
-- Good
SELECT * FROM users; -- Bad
-- Use LIMIT
SELECT * FROM users LIMIT 100 ;
-- Use indexes untuk WHERE, JOIN, ORDER BY
CREATE INDEX idx_email ON users(email);
-- Avoid functions di WHERE (ga bisa pake index)
SELECT * FROM users WHERE YEAR (created_at) = 2024 ; -- Bad
SELECT * FROM users WHERE created_at >= '2024-01-01'
AND created_at < '2025-01-01' ; -- Good
-- Use EXISTS instead of IN dengan subquery
SELECT * FROM users WHERE EXISTS (
SELECT 1 FROM orders WHERE user_id = users . id
);
-- Batch inserts
INSERT INTO users ( name , email) VALUES
( 'User 1' , 'user1@mail.com' ),
( 'User 2' , 'user2@mail.com' ),
( 'User 3' , 'user3@mail.com' );
*
FROM
products
WHERE name LIKE '%laptop%' OR description LIKE '%laptop%'
ORDER BY
CASE
WHEN name LIKE '%laptop%' THEN 1
ELSE 2
END ;
-- Upsert (Insert or Update)
INSERT INTO users (id, name , email)
VALUES ( 1 , 'Andi' , 'andi@mail.com' )
ON DUPLICATE KEY UPDATE name = 'Andi' , email = 'andi@mail.com' ; -- MySQL
-- Soft delete
UPDATE users SET deleted_at = NOW () WHERE id = 1 ;
SELECT * FROM users WHERE deleted_at IS NULL ;
-- Random rows
SELECT * FROM users ORDER BY RAND () LIMIT 10 ; -- MySQL
SELECT * FROM users ORDER BY RANDOM() LIMIT 10 ; -- PostgreSQL
-- Find duplicates
SELECT email, COUNT ( * ) as count
FROM users
GROUP BY email
HAVING COUNT ( * ) > 1 ;
-- Delete duplicates (keep one)
DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1 . id > u2 . id AND u1 . email = u2 . email ;
-- cursor.execute("SELECT * FROM users WHERE email = %s", (email,))
-- Validate input di application level
-- Escape special characters
-- Use ORM yang udah handle ini (Prisma, TypeORM, Eloquent)
-- Grant minimal permissions
GRANT SELECT , INSERT , UPDATE , DELETE ON database . * TO 'app_user' @ 'localhost' ;
-- Revoke permissions
REVOKE ALL PRIVILEGES ON database . * FROM 'app_user' @ 'localhost' ;
