🗄️ SQL Lesson 05 — MySQL Setup & Table Design

1. XAMPP နဲ့ MySQL Start လုပ်နည်း

2. Database Create

-- Create new database
CREATE DATABASE my_school;

-- If not already exists (safe)
CREATE DATABASE IF NOT EXISTS my_school;

-- With character set (Myanmar text support)
CREATE DATABASE my_school
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Select database to use
USE my_school;

-- Show all databases
SHOW DATABASES;

-- Delete database (careful!)
DROP DATABASE IF EXISTS test_db;

3. MySQL Data Types

4. CREATE TABLE

CREATE TABLE IF NOT EXISTS users (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(150) UNIQUE NOT NULL,
    age         INT,
    city        VARCHAR(80) DEFAULT 'Unknown',
    score       DECIMAL(5,2) DEFAULT 0.00,
    active      TINYINT(1) DEFAULT 1,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Check table structure
DESCRIBE users;
SHOW CREATE TABLE users;

5. ALTER TABLE — Structure ပြောင်း

-- Add new column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN bio TEXT AFTER email;

-- Change column type/name
ALTER TABLE users MODIFY COLUMN phone VARCHAR(15) NOT NULL;
ALTER TABLE users RENAME COLUMN age TO user_age;

-- Remove column
ALTER TABLE users DROP COLUMN bio;

-- Add index (speeds up queries)
ALTER TABLE users ADD INDEX idx_city (city);
ALTER TABLE users ADD UNIQUE (email);

-- Add foreign key
ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);

-- Rename table
RENAME TABLE users TO members;

-- Drop table
DROP TABLE IF EXISTS temp_data;

6. Complete Example — School Database

CREATE DATABASE IF NOT EXISTS school_db
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE school_db;

-- Students table
CREATE TABLE students (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(150) UNIQUE NOT NULL,
    grade      ENUM('A', 'B', 'C', 'D', 'F'),
    enrolled   DATE NOT NULL,
    active     TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Courses table
CREATE TABLE courses (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    description TEXT,
    credits     INT DEFAULT 3
);

-- Enrollments (join table)
CREATE TABLE enrollments (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    score      DECIMAL(5,2),
    enrolled   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id),
    UNIQUE (student_id, course_id)  -- can't enroll same course twice
);

-- Sample data
INSERT INTO students (name, email, grade, enrolled)
VALUES
    ('Ko Min', 'komin@mail.com', 'A', '2024-01-10'),
    ('Ma Aye', 'maaye@mail.com', 'B', '2024-01-12');