Salin dan Bagikan
MySQL Tutorial Database Basics
Cara Membuat Database dan Tabel di MySQL: Panduan Lengkap untuk Pemula - Tutorial lengkap cara membuat database dan tabel di MySQL dengan SQL commands, data types, indexes, …

Cara Membuat Database dan Tabel di MySQL: Panduan Lengkap untuk Pemula

Cara Membuat Database dan Tabel di MySQL: Panduan Lengkap untuk Pemula

Membuat database dan tabel adalah fondasi dari semua operasi database. Setelah mengajari ratusan developer dan students, saya memahami confusion yang sering terjadi di awal perjalanan database. Artikel ini akan membahas dari nol hingga membuat struktur database yang solid.

Membuat Database

1. Create Database Basic

-- Create database sederhana
CREATE DATABASE myapp;

-- Create dengan IF NOT EXISTS (avoid error jika sudah ada)
CREATE DATABASE IF NOT EXISTS myapp;

-- Create dengan character set
CREATE DATABASE myapp
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- Verifikasi database created
SHOW DATABASES LIKE 'myapp';

-- Gunakan database
USE myapp;

2. Database dengan Options

-- Database lengkap dengan options
CREATE DATABASE IF NOT EXISTS ecommerce
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
DEFAULT ENCRYPTION='N';

3. Menghapus Database

-- Drop database (HATI-HATI!)
DROP DATABASE test_db;

-- Drop dengan IF EXISTS
DROP DATABASE IF EXISTS test_db;

Membuat Tabel

1. Create Table Basic

USE myapp;

-- Tabel sederhana
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Verifikasi
SHOW TABLES;
DESCRIBE users;

2. Data Types dalam MySQL

CREATE TABLE products (
    -- Integer types
    id INT AUTO_INCREMENT PRIMARY KEY,
    small_value TINYINT,
    medium_value SMALLINT,
    stock INT,
    big_number BIGINT,
    
    -- Decimal untuk currency
    price DECIMAL(10,2),  -- 99999999.99
    
    -- String types
    sku CHAR(20),  -- Fixed length
    name VARCHAR(255),  -- Variable length
    description TEXT,  -- Long text
    specs MEDIUMTEXT,
    
    -- Date dan time
    release_date DATE,
    available_time TIME,
    created_at DATETIME,
    updated_at TIMESTAMP,
    
    -- Boolean
    is_active BOOLEAN DEFAULT TRUE,
    
    -- Binary
    image_data BLOB,
    
    -- JSON (MySQL 5.7+)
    attributes JSON,
    
    -- Enum
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft'
);

3. Constraints dan Indexes

CREATE TABLE orders (
    -- Primary key
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Foreign key
    customer_id INT NOT NULL,
    
    -- Unique constraint
    order_number VARCHAR(20) UNIQUE,
    
    -- Not null constraint
    order_date DATE NOT NULL,
    
    -- Check constraint (MySQL 8.0.16+)
    total_amount DECIMAL(10,2) CHECK (total_amount >= 0),
    
    -- Default value
    status VARCHAR(20) DEFAULT 'pending',
    
    -- Index
    INDEX idx_customer_date (customer_id, order_date),
    INDEX idx_status (status),
    
    -- Foreign key constraint
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) 
        REFERENCES customers(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Data Types Detail

1. Integer Types

CREATE TABLE test_integers (
    tiny TINYINT,      -- -128 to 127
    small SMALLINT,    -- -32768 to 32767
    medium MEDIUMINT,  -- -8388608 to 8388607
    normal INT,        -- -2147483648 to 2147483647
    big BIGINT,        -- -9223372036854775808 to 9223372036854775807
    
    -- Unsigned (hanya positive)
    positive TINYINT UNSIGNED  -- 0 to 255
);

2. String Types

CREATE TABLE test_strings (
    -- CHAR: Fixed length, padding dengan spaces
    code CHAR(5),  -- Selalu 5 karakter
    
    -- VARCHAR: Variable length
    name VARCHAR(100),  -- Max 100 karakter
    
    -- TEXT types
    short TEXT,        -- Max 64KB
    medium MEDIUMTEXT, -- Max 16MB
    long LONGTEXT      -- Max 4GB
);

3. Date and Time

CREATE TABLE test_dates (
    -- DATE: YYYY-MM-DD
    birth_date DATE,
    
    -- TIME: HH:MM:SS
    work_start TIME,
    
    -- DATETIME: YYYY-MM-DD HH:MM:SS
    created_at DATETIME,
    
    -- TIMESTAMP: Unix timestamp, auto timezone
    modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- YEAR: YYYY
    model_year YEAR
);

Alter Table Operations

1. Menambah Kolom

-- Add single column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Add dengan position
ALTER TABLE users ADD COLUMN middle_name VARCHAR(50) AFTER first_name;
ALTER TABLE users ADD COLUMN last_login TIMESTAMP AFTER email;

-- Add multiple columns
ALTER TABLE users 
    ADD COLUMN address VARCHAR(255),
    ADD COLUMN city VARCHAR(50),
    ADD COLUMN country VARCHAR(50);

2. Mengubah Kolom

-- Modify column type
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30);

-- Change column name dan type
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(30);

-- Add constraint
ALTER TABLE users MODIFY COLUMN email VARCHAR(100) NOT NULL UNIQUE;

3. Menghapus Kolom

-- Drop column
ALTER TABLE users DROP COLUMN temporary_field;

-- Drop multiple
ALTER TABLE users 
    DROP COLUMN old_field1,
    DROP COLUMN old_field2;

4. Index Operations

-- Add index
ALTER TABLE orders ADD INDEX idx_order_date (order_date);

-- Add unique index
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- Add composite index
ALTER TABLE orders ADD INDEX idx_customer_status (customer_id, status);

-- Drop index
ALTER TABLE orders DROP INDEX idx_order_date;

Best Practices Pembuatan Tabel

1. Primary Key Design

-- Auto-increment integer (recommended untuk kebanyakan kasus)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    -- ...
);

-- UUID untuk distributed systems (MySQL 8.0)
CREATE TABLE distributed_data (
    id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    -- ...
);

-- Composite key untuk junction tables
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)
);

2. Normalization Basics

-- 1NF: No repeating groups
-- Good
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT
);

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT
);

-- 2NF: No partial dependencies
-- Pisahkan data produk ke tabel sendiri
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

3. Index Strategy

-- Index untuk foreign keys (selalu!)
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    -- ...
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Index untuk frequently queried columns
CREATE TABLE users (
    email VARCHAR(100),
    status VARCHAR(20),
    -- ...
    INDEX idx_email (email),
    INDEX idx_status_created (status, created_at)
);

Praktik Membuat Database Lengkap

1. E-Commerce Database Structure

-- Create database
CREATE DATABASE ecommerce_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ecommerce_db;

-- Users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- Categories table
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(id),
    INDEX idx_slug (slug)
) ENGINE=InnoDB;

-- Products table
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(200) UNIQUE,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock_quantity INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id),
    INDEX idx_category (category_id),
    INDEX idx_slug (slug),
    INDEX idx_price (price),
    FULLTEXT INDEX idx_description (description)
) ENGINE=InnoDB;

-- Orders table
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_number VARCHAR(20) UNIQUE,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- Order items table
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_order (order_id),
    INDEX idx_product (product_id)
) ENGINE=InnoDB;

-- Verify
SHOW TABLES;

Kesimpulan

Membuat database dan tabel yang baik memerlukan:

  • Perencanaan data types yang tepat
  • Normalization untuk reduce redundancy
  • Indexes untuk query performance
  • Constraints untuk data integrity

Dengan fondasi yang solid, development menjadi lebih mudah dan scalable.

Artikel Terkait

Link Postingan : https://www.tirinfo.com/cara-membuat-database-tabel-mysql-panduan-pemula/

Hendra WIjaya
Tirinfo
5 minutes.
3 February 2026