أساسيات قاعدة بيانات MySQL

Database Server (e.g., MySQL)
 ├── Database "blog"          ← CREATE DATABASE blog;
 │    ├── Table "users"       ← CREATE TABLE users (...)
 │    │    ├── Row: User 1    ← INSERT INTO users ...
 │    │    ├── Row: User 2
 │    │    └── Row: User 3
 │    ├── Table "posts"
 │    └── Table "comments"
 └── Database "shop"

# Command-line connection
mysql -u root -p
# XAMPP's root has no password by default—just press Enter

-- List existing databases
SHOW DATABASES;

-- For security, set a root password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_password';

-- Create a database
CREATE DATABASE myblog;

-- Select the database
USE myblog;

-- Check the current database
SELECT DATABASE();

-- Delete a database (use with caution!)
-- DROP DATABASE myblog;

-- Create the users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    age INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- View table structure
DESCRIBE users;
-- Or
SHOW COLUMNS FROM users;

-- Insert data
INSERT INTO users (username, email, password, age) VALUES
('John', 'john@example.com', 'hashed_pw_1', 25),
('Jane', 'jane@example.com', 'hashed_pw_2', 22),
('Bob', 'bob@example.com', 'hashed_pw_3', 28);

-- Query
SELECT * FROM users;
SELECT username, email FROM users WHERE age > 23;
SELECT COUNT(*) FROM users;                    -- Count rows
SELECT * FROM users ORDER BY age DESC;         -- Sort by age descending
SELECT * FROM users LIMIT 2;                   -- Only the first 2 records

-- Update
UPDATE users SET age = 26 WHERE username = 'John';
-- ⚠️ Forgetting WHERE updates ALL rows!

-- Delete
DELETE FROM users WHERE id = 3;
-- ⚠️ Forgetting WHERE deletes ALL rows!

http://localhost/phpmyadmin

Steps:
1. Click "New" on the left → enter database name myblog → Create
2. Click the myblog database → "SQL" tab
3. Paste your CREATE TABLE statement → Execute
4. Click the users table → "Insert" tab → fill out the form to insert data
5. "Browse" tab → view your data
6. "Structure" tab → view/modify column definitions

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    status ENUM('draft', 'published') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);