Unlock MySQL Mastery: A Complete Theory & Hands‑On Guide

Introduction

MySQL is a popular open‑source relational DBMS used in web and enterprise applications.

Database and Table Basics

Creating databases and tables, basic data types, primary keys.

CREATE DATABASE school;
CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT,
  class VARCHAR(20)
);

Storage Engines

InnoDB (default, supports transactions), MyISAM, Memory, Archive.

CREATE TABLE example (id INT) ENGINE=InnoDB;

Data Types

Numeric: INT, BIGINT, FLOAT, DECIMAL

String: CHAR, VARCHAR, TEXT

Date/Time: DATE, DATETIME, TIMESTAMP

Other: ENUM, SET, BLOB

CRUD Operations

Insert single and multiple rows, update with conditions, delete with filters.

INSERT INTO students (name, age, class) VALUES ('张三', 20, '计算机科学1班');
UPDATE students SET score = 92.0 WHERE name = '张三';
DELETE FROM students WHERE score < 60;

Select Queries

Basic SELECT, WHERE, ORDER BY, GROUP BY, HAVING, LIMIT, pattern matching.

SELECT name, score FROM students WHERE class = '计算机科学1班' ORDER BY score DESC LIMIT 3;

Advanced Queries

Joins, subqueries, CTEs, window functions.

-- Inner join example
SELECT s.name, c.course_name
FROM students s
JOIN student_courses sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;

-- CTE example
WITH ClassAvg AS (
  SELECT class, AVG(score) AS avg_score FROM students GROUP BY class
)
SELECT s.name, s.score, ca.avg_score
FROM students s JOIN ClassAvg ca ON s.class = ca.class;

-- Window function example
SELECT name, score,
       RANK() OVER (PARTITION BY class ORDER BY score DESC) AS class_rank
FROM students;

Transactions

Start, commit, rollback, isolation levels, savepoints.

START TRANSACTION;
UPDATE students SET score = score + 10 WHERE id = 1;
COMMIT;

-- Set isolation level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Indexes and Performance

Creating single‑column and composite indexes, using EXPLAIN.

CREATE INDEX idx_student_name ON students(name);
CREATE INDEX idx_class_score ON students(class, score);
EXPLAIN SELECT * FROM students WHERE class = '计算机科学1班' AND score > 80;

Views

Virtual tables to simplify complex queries.

CREATE VIEW student_course_view AS
SELECT s.id, s.name, c.course_name
FROM students s
JOIN student_courses sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;

Stored Procedures and Functions

Encapsulate logic with parameters and control flow.

DELIMITER //
CREATE PROCEDURE update_student_score(IN student_id INT, IN new_score FLOAT)
BEGIN
  UPDATE students SET score = new_score WHERE id = student_id;
END //
DELIMITER ;

Triggers

Automatic actions on INSERT/UPDATE/DELETE.

CREATE TRIGGER after_student_update
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
  IF OLD.score <> NEW.score THEN
    INSERT INTO score_changes(student_id, old_score, new_score, change_date)
    VALUES (NEW.id, OLD.score, NEW.score, NOW());
  END IF;
END;

User Management and Permissions

Create users, grant and revoke privileges.

CREATE USER 'teacher'@'localhost' IDENTIFIED BY 'password123';
GRANT SELECT, UPDATE ON school.students TO 'teacher'@'localhost';
REVOKE UPDATE ON school.students FROM 'teacher'@'localhost';

Backup and Restore

Logical backup with mysqldump and restore commands.

mysqldump -u root -p school > school_backup.sql
mysql -u root -p school < school_backup.sql

Replication and High Availability

Primary‑secondary setup, async and semi‑sync modes, Group Replication.

# Master configuration (my.cnf)
server-id = 1
log_bin = mysql-bin
binlog_format = ROW

# Slave configuration
server-id = 2
relay_log = mysql-relay-bin

# Create replication user
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

# Start replication on slave
CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl',
  MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=123;
START SLAVE;

JSON Data Type

Store and query semi‑structured data.

CREATE TABLE user_profiles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  profile JSON
);
INSERT INTO user_profiles (user_id, profile)
VALUES (1, '{"name":"张三","age":25,"interests":["编程","音乐"]}');
SELECT profile->>'$.name' AS name FROM user_profiles;

Full‑Text Search

Create FULLTEXT index and perform natural‑language or boolean searches.

CREATE TABLE articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  content TEXT,
  FULLTEXT INDEX idx_ft (title, content)
) ENGINE=InnoDB;

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 基础' IN NATURAL LANGUAGE MODE);
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -高级' IN BOOLEAN MODE);

Best Practices

Keep MySQL up‑to‑date and use strong passwords.

Apply the principle of least privilege.

Enable SSL/TLS for client connections.

Monitor performance with Performance Schema and the slow‑query log.

Regularly analyze and optimize tables and indexes.

Implement reliable backup strategies (full, incremental, automated).