MySQL Complete Tutorial

Overview

Tutorial Features

• Progressive - From basic to advanced
• Bilingual - English and Chinese side-by-side
• Practical - Runnable examples
• Useful - Real-world scenarios
• Complete - Covers 35+ core topics

Tutorial Structure

Part 1: Basics

Part 2: Core Operations

• create-table.md - Create Table
• drop-table.md - Drop Table
• insert-data.md - Insert Data
• select-data.md - Select Data
• where-clause.md - WHERE Clause
• update.md - UPDATE
• delete.md - DELETE
• like-clause.md - LIKE Clause
• union.md - UNION
• order-by.md - ORDER BY
• group-by.md - GROUP BY
• joins.md - Joins
• null-values.md - NULL Values

Part 3: Advanced Topics

• regex.md - Regex
• transactions.md - Transactions
• alter.md - ALTER
• indexes.md - Indexes
• temporary-tables.md - Temporary Tables
• clone-tables.md - Clone Tables
• metadata.md - Metadata
• sequences.md - Sequences
• duplicates.md - Duplicates
• sql-injection.md - SQL Injection

Part 4: Data Operations

• export-data.md - Export Data
• import-data.md - Import Data
• functions.md - Functions
• operators.md - Operators
• commands.md - Commands
• quiz.md - Quiz
• programming.md - Programming
• resources.md - Resources

Quick Start

Install MySQL

# Windows: Download from dev.mysql.com
# macOS: brew install mysql
brew install mysql@8.0

# Linux (Ubuntu/Debian)
sudo apt update
sudo apt install mysql-server

# Start MySQL service
sudo systemctl start mysql

Connect to MySQL

mysql -u root -p

CREATE DATABASE myapp;
USE myapp;

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (name, email) VALUES 
    ('John Doe', 'john@example.com'),
    ('Jane Smith', 'jane@example.com');

SELECT * FROM users;

SHOW DATABASES;

CREATE DATABASE database_name;

USE database_name;

DROP DATABASE database_name;

Common Commands

SHOW TABLES;

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    PRIMARY KEY (column1)
);

DESCRIBE table_name;

DROP TABLE table_name;

CRUD

-- Create (Insert)
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');

-- Read (Select)
SELECT * FROM users;

-- Update
UPDATE users SET name = 'Jane Smith' WHERE name = 'John Doe';

-- Delete
DELETE FROM users WHERE name = 'Jane Smith';

|------|------|------|------| | TINYINT | 1 byte | -128 to 127 | | | INT | 4 bytes | -2147483648 to 2147483647 | | | BIGINT | 8 bytes | -9.2e+18 to 9.2e+18 | |

|------|----------|----------| | TEXT | 65535 | | | MEDIUMTEXT | 16777215 | | | LONGTEXT | 4294967295 | |

|------|------|------|------| | DATE | 3 bytes | 1000-01-01 to 9999-12-31 | | | TIME | 3 bytes | -838:59:59 to 838:59:59 | | | TIMESTAMP | 4 bytes | 1970-01-01 00:00:01 to 2038-01-19:03:14:07:19:03:14:07 | | | YEAR | 1 byte | 1901 to 2155 | |

2. UPDATEDELETE
3. UNION
4. NULL

• MySQL: https:

• W3Schools MySQL: https:

• MySQLTutorial: https://www.mysqltutorial.org/

• DigitalOcean MySQL: https:

• MySQL: https:

• Stack Overflow: https://stackoverflow.com/questions/tagged/mysql

• Reddit MySQL: https://www.reddit.com/r/MySQL/

CREATE TABLE user_accounts;     -- Recommended
CREATE TABLE UserAccounts;   -- Avoid

CREATE TABLE users;          -- Common practice

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_user'@'localhost';

-- Prevent SQL injection
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
SET @id = 1;
EXECUTE stmt USING @id;

CREATE INDEX idx_users_email ON users(email);

-- Use EXPLAIN to analyze queries
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

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

---