1
2
3
4
5
6
7

-- Contoh struktur tabel
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255),
    created_at TIMESTAMP
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

// Document database (MongoDB)
{
  "_id": ObjectId("..."),
  "name": "John Doe",
  "email": "[email protected]",
  "age": 25,
  "address": {
    "street": "123 Main St",
    "city": "New York"
  }
}

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

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Posts table
CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

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

-- One-to-Many relationship
-- One user can have many posts
SELECT u.username, p.title
FROM users u
JOIN posts p ON u.id = p.user_id;

-- Many-to-Many relationship
-- Users can have many roles, roles can have many users
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

 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

-- First Normal Form (1NF)
-- Atomic values, no repeating groups
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

-- Second Normal Form (2NF)
-- No partial dependencies
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id)
);

-- Third Normal Form (3NF)
-- No transitive dependencies
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    city_id INT,
    FOREIGN KEY (city_id) REFERENCES cities(id)
);

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

-- Basic SELECT
SELECT * FROM users;

-- Selecting specific columns
SELECT id, username, email FROM users;

-- Filtering with WHERE
SELECT * FROM users WHERE age > 18;

-- Sorting with ORDER BY
SELECT * FROM users ORDER BY created_at DESC;

-- Limiting results
SELECT * FROM users LIMIT 10;

1
2
3
4
5
6
7
8

-- Insert single row
INSERT INTO users (username, email, password_hash)
VALUES ('john_doe', '[email protected]', 'hashed_password');

-- Insert multiple rows
INSERT INTO users (username, email, password_hash) VALUES
('jane_doe', '[email protected]', 'hashed_password'),
('bob_smith', '[email protected]', 'hashed_password');

1
2
3
4
5
6
7
8
9

-- Update single row
UPDATE users 
SET email = '[email protected]'
WHERE id = 1;

-- Update multiple rows
UPDATE users 
SET last_login = CURRENT_TIMESTAMP
WHERE last_login IS NULL;

1
2
3
4
5

-- Delete specific row
DELETE FROM users WHERE id = 1;

-- Delete with condition
DELETE FROM users WHERE created_at < '2023-01-01';

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

-- INNER JOIN
SELECT u.username, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;

-- LEFT JOIN
SELECT u.username, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id;

-- RIGHT JOIN
SELECT p.title, u.username
FROM posts p
RIGHT JOIN users u ON p.user_id = u.id;

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

-- COUNT
SELECT COUNT(*) FROM users;

-- SUM
SELECT SUM(price) FROM order_items;

-- AVG
SELECT AVG(price) FROM products;

-- MAX/MIN
SELECT MAX(created_at) FROM posts;
SELECT MIN(price) FROM products;

-- GROUP BY
SELECT user_id, COUNT(*) as post_count
FROM posts
GROUP BY user_id;

1
2
3
4
5
6
7
8

-- Subquery in WHERE
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM posts);

-- Subquery in SELECT
SELECT username, 
       (SELECT COUNT(*) FROM posts WHERE user_id = users.id) as post_count
FROM users;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

┌─────────────┐    ┌─────────────┐
│    Users    │    │    Posts    │
├─────────────┤    ├─────────────┤
│ id (PK)     │    │ id (PK)     │
│ username    │    │ user_id (FK)│
│ email       │    │ title       │
│ password    │    │ content     │
└─────────────┘    └─────────────┘
       │                   │
       └───────────────────┘
          1:N Relationship

1
2
3
4
5
6
7
8

-- Single column index
CREATE INDEX idx_username ON users(username);

-- Composite index
CREATE INDEX idx_user_email ON users(username, email);

-- Unique index
CREATE UNIQUE INDEX idx_email ON users(email);

1
2
3
4
5
6
7

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INT CHECK (age >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

1
2
3
4
5
6
7
8

-- Create user
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password';

-- Grant privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'app_user'@'localhost';

-- Revoke privileges
REVOKE DELETE ON database_name.* FROM 'app_user'@'localhost';

1
2
3

-- Encrypt sensitive data
UPDATE users 
SET credit_card = AES_ENCRYPT(credit_card, 'encryption_key');

1
2
3
4
5
6
7
8

-- Use indexes
SELECT * FROM users WHERE username = 'john_doe';

-- Avoid SELECT *
SELECT id, username, email FROM users;

-- Use LIMIT
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;

1
2
3
4
5
6
7
8

-- Analyze table
ANALYZE TABLE users;

-- Optimize table
OPTIMIZE TABLE posts;

-- Check table
CHECK TABLE users;