MySQL Beginner Tutorial

MySQL Beginner Tutorial

This tutorial is written for readers with zero database experience. We will start from installing MySQL, then step by step learn to create databases, build tables, perform CRUD operations, filter with WHERE, join tables with JOIN, understand basic indexing, and import/export data. Every concept comes with runnable SQL examples — we encourage you to practice in your terminal as you read.

Version coverage: This tutorial is based on MySQL 8.0 / 8.4 LTS. All SQL examples also work on 5.7+. If you are using MariaDB 10.x, the vast majority of syntax is compatible.

1. Installing MySQL

MySQL runs on virtually every operating system. Below we cover four common installation methods.

1.1 Linux (Ubuntu / Debian)

Ubuntu and Debian use the APT package manager. Two commands are all you need:

# Update package index
sudo apt update

# Install MySQL Server (installs the latest available version)
sudo apt install mysql-server -y

# Check service status
sudo systemctl status mysql

After installation, run the security initialization script to set the root password, remove test databases, etc.:

sudo mysql_secure_installation

The script will ask you in sequence:

1. Whether to enable the password strength validation plugin (recommended: Y)
2. Set root password
3. Remove anonymous users (Y)
4. Disallow root remote login (Y)
5. Remove test database (Y)
6. Reload privilege tables (Y)

CentOS / RHEL / Rocky Linux

# Add MySQL official YUM repository (MySQL 8.0 example)
sudo rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el8-9.noarch.rpm

# Install
sudo dnf install mysql-server -y

# Start and enable on boot
sudo systemctl start mysqld
sudo systemctl enable mysqld

# Get the initial temporary password
sudo grep 'temporary password' /var/log/mysqld.log

Note: On CentOS/RHEL, MySQL generates a temporary root password written to the log. You must change it immediately on first login or you cannot execute any SQL.

1.2 macOS

On macOS, Homebrew is the recommended installation method:

# Install Homebrew (if you haven't already)
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

# Install MySQL
brew install mysql

# Start MySQL service
brew services start mysql

# Run security initialization
mysql_secure_installation

Homebrew installs MySQL with an empty root password by default. mysql_secure_installation will guide you through setting one.

1.3 Windows

Windows users should use the MySQL Installer:

1. Visit dev.mysql.com/downloads/installer
2. Download mysql-installer-community (~300MB full version)
3. Run the installer, choose Developer Default or Server only
4. Follow the wizard to set root password and port (default 3306)
5. After completion, MySQL registers as a Windows service and starts automatically

After installation, open MySQL Command Line Client or use cmd:

mysql -u root -p

1.4 Docker (Recommended for Learning)

Docker is the fastest installation method and does not affect your system environment:

# Pull the official MySQL 8.0 image
docker pull mysql:8.0

# Start a container (set root password to my-secret-pw)
docker run --name mysql-learn \
  -e MYSQL_ROOT_PASSWORD=my-secret-pw \
  -p 3306:3306 \
  -d mysql:8.0

# Connect to the MySQL command line
docker exec -it mysql-learn mysql -u root -pmy-secret-pw

Recommended: If you are just learning, Docker is the most convenient approach. When you are done, simply docker rm -f mysql-learn to clean up with no traces left.

1.5 Verify Installation

Regardless of which installation method you used, verify with:

# Check MySQL version
mysql --version
# Output example: mysql  Ver 8.0.36 for Linux on x86_64 (MySQL Community Server - GPL)

# Or after logging in
mysql -u root -p -e "SELECT VERSION();"

2. Connecting to MySQL

2.1 Command Line Client

The built-in mysql command-line client is the most common way to connect:

# Basic connection (prompts for password)
mysql -u root -p

# Specify host and port
mysql -u root -p -h 127.0.0.1 -P 3306

# Connect directly to a specific database
mysql -u root -p my_database

On successful connection, you will see the mysql> prompt, indicating you are in interactive mode.

2.2 Common Connection Parameters

2.3 GUI Clients

If you prefer a graphical interface, here are some popular free MySQL clients:

• MySQL Workbench — Official MySQL GUI, fully featured (Windows / Mac / Linux)
• DBeaver — Open-source, cross-platform, supports dozens of databases
• phpMyAdmin — Web-based admin tool, ideal with LAMP stacks
• Navicat — Commercial software with 14-day trial

2.4 Exiting MySQL

-- Any of these three commands will exit
EXIT;
QUIT;
\q

3. Creating a Database

3.1 List Existing Databases

SHOW DATABASES;

After installation, you will see these system databases:

• mysql — Stores user privileges and system configuration
• information_schema — Metadata views (read-only)
• performance_schema — Performance monitoring data
• sys — Human-readable views built on performance_schema

3.2 Create a New Database

-- Create a database (recommended: specify character set)
CREATE DATABASE shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Safe version with IF NOT EXISTS
CREATE DATABASE IF NOT EXISTS shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Why utf8mb4? MySQL's utf8 encoding actually only supports up to 3-byte characters and cannot store emojis or other 4-byte characters. utf8mb4 is true UTF-8 and is the default charset in MySQL 8.0. All new projects should use utf8mb4.

3.3 Select a Database

-- Switch to the shop database
USE shop;

-- Check which database is currently selected
SELECT DATABASE();

3.4 Drop a Database

-- Drop a database (irreversible!)
DROP DATABASE shop;

-- Safer version
DROP DATABASE IF EXISTS shop;

Dangerous operation: DROP DATABASE deletes all tables and data in the database and cannot be undone. Always back up first in production.

4. Creating Tables

4.1 CREATE TABLE Syntax

Let's create a users table and an orders table. All subsequent examples will use these two tables:

USE shop;

-- Users table
CREATE TABLE users (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'User ID',
    username    VARCHAR(50)  NOT NULL UNIQUE           COMMENT 'Username',
    email       VARCHAR(100) NOT NULL                  COMMENT 'Email',
    age         TINYINT UNSIGNED                       COMMENT 'Age',
    balance     DECIMAL(10,2) NOT NULL DEFAULT 0.00    COMMENT 'Account balance',
    status      ENUM('active','inactive','banned')
                NOT NULL DEFAULT 'active'              COMMENT 'Status',
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Registration time',
    updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP            COMMENT 'Last update'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Users table';

-- Orders table
CREATE TABLE orders (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 'Order ID',
    user_id     INT UNSIGNED NOT NULL                  COMMENT 'User ID',
    product     VARCHAR(100) NOT NULL                  COMMENT 'Product name',
    quantity    INT UNSIGNED NOT NULL DEFAULT 1         COMMENT 'Quantity',
    price       DECIMAL(10,2) NOT NULL                 COMMENT 'Unit price',
    order_date  DATE NOT NULL                          COMMENT 'Order date',
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Orders table';

4.2 Key Constraints Explained

4.3 Inspecting Table Structure

-- List all tables
SHOW TABLES;

-- View table structure
DESCRIBE users;
-- Or short form
DESC users;

-- View full CREATE TABLE statement
SHOW CREATE TABLE users\G

4.4 Modifying Tables (ALTER TABLE)

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

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

-- Rename a column
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(30);

-- Drop a column
ALTER TABLE users DROP COLUMN mobile;

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

-- Rename a table
ALTER TABLE users RENAME TO customers;
-- Rename back
ALTER TABLE customers RENAME TO users;

5. Common Data Types

5.1 Numeric Types

Never use FLOAT/DOUBLE for money! Floating-point numbers have precision issues. For example, 0.1 + 0.2 may not equal 0.3 in FLOAT. Always use DECIMAL for monetary values.

5.2 String Types

5.3 Date and Time Types

DATETIME vs TIMESTAMP: DATETIME uses 8 bytes, is not affected by time zones, and ranges to the year 9999. TIMESTAMP uses 4 bytes and auto-converts to UTC on storage (converts back on read based on session time zone), but overflows in 2038. For new projects, prefer DATETIME.

6. Inserting Data (INSERT)

6.1 Insert a Single Row

INSERT INTO users (username, email, age, balance, status)
VALUES ('alice', '[email protected]', 28, 1000.00, 'active');

6.2 Insert Multiple Rows

INSERT INTO users (username, email, age, balance, status) VALUES
  ('bob',     '[email protected]',     32, 500.50,  'active'),
  ('charlie', '[email protected]', 22, 200.00,  'active'),
  ('diana',   '[email protected]',   45, 3500.00, 'active'),
  ('eve',     '[email protected]',     19, 50.00,   'inactive'),
  ('frank',   '[email protected]',   38, 800.00,  'active'),
  ('grace',   '[email protected]',   27, 1200.00, 'active'),
  ('henry',   '[email protected]',   55, 0.00,    'banned');

6.3 Insert Order Data

INSERT INTO orders (user_id, product, quantity, price, order_date) VALUES
  (1, 'iPhone 15',        1, 7999.00, '2024-01-15'),
  (1, 'Phone Case',       2, 29.90,   '2024-01-15'),
  (2, 'MacBook Pro 14',   1, 14999.00,'2024-02-20'),
  (3, 'Mechanical KB',    1, 599.00,  '2024-03-10'),
  (3, 'Mouse Pad',        1, 39.90,   '2024-03-10'),
  (4, '27" Monitor',      2, 2499.00, '2024-01-08'),
  (5, 'USB Cable',        3, 19.90,   '2024-04-01'),
  (6, 'AirPods Pro',      1, 1799.00, '2024-03-22'),
  (7, 'Webcam',           1, 299.00,  '2024-02-14'),
  (1, 'iPad Air',         1, 4599.00, '2024-04-05');

6.4 Common INSERT Variants

-- Insert or ignore (skip on PK/unique key conflict)
INSERT IGNORE INTO users (username, email, age)
VALUES ('alice', '[email protected]', 28);

-- Insert or update (UPSERT)
INSERT INTO users (username, email, age, balance)
VALUES ('alice', '[email protected]', 29, 1100.00)
ON DUPLICATE KEY UPDATE age = VALUES(age), balance = VALUES(balance);

-- Insert from a query result
INSERT INTO user_archive (id, username, email)
SELECT id, username, email FROM users WHERE status = 'banned';

7. Querying Data (SELECT)

7.1 Basic Queries

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT id, username, email, balance FROM users;

-- Using aliases
SELECT
    username AS 'User',
    balance  AS 'Balance',
    CONCAT(username, ' (', email, ')') AS 'User Info'
FROM users;

7.2 DISTINCT (Deduplicate)

-- View all unique status values
SELECT DISTINCT status FROM users;

-- View users who have placed orders (deduplicated)
SELECT DISTINCT user_id FROM orders;

7.3 Computed Columns

-- Calculate total amount for each order line
SELECT
    id,
    product,
    quantity,
    price,
    quantity * price AS total
FROM orders;

8. WHERE, ORDER BY, and LIMIT

8.1 WHERE Filtering

-- Equals
SELECT * FROM users WHERE status = 'active';

-- Not equals
SELECT * FROM users WHERE status != 'banned';
-- Or
SELECT * FROM users WHERE status <> 'banned';

-- Greater than / Less than
SELECT * FROM users WHERE age > 30;
SELECT * FROM users WHERE balance <= 500;

-- BETWEEN (inclusive on both ends)
SELECT * FROM users WHERE age BETWEEN 20 AND 35;

-- IN (match any value in a list)
SELECT * FROM users WHERE username IN ('alice', 'bob', 'charlie');

-- LIKE (pattern matching)
SELECT * FROM users WHERE email LIKE '%@example.com';
SELECT * FROM users WHERE username LIKE 'a%';      -- starts with 'a'
SELECT * FROM users WHERE username LIKE '_____';    -- exactly 5 characters

-- IS NULL / IS NOT NULL
SELECT * FROM users WHERE age IS NOT NULL;

-- AND / OR combinations
SELECT * FROM users
WHERE status = 'active'
  AND age >= 25
  AND balance > 500;

SELECT * FROM users
WHERE status = 'banned' OR balance = 0;

8.2 ORDER BY (Sorting)

-- Sort by balance descending
SELECT * FROM users ORDER BY balance DESC;

-- Sort by age ascending (ASC is default)
SELECT * FROM users ORDER BY age ASC;

-- Multi-column sort: by status first, then by balance descending
SELECT * FROM users ORDER BY status ASC, balance DESC;

8.3 LIMIT and Pagination

-- Get the top 3 rows
SELECT * FROM users ORDER BY balance DESC LIMIT 3;

-- Pagination: skip 3, take 3 (OFFSET is 0-based)
SELECT * FROM users ORDER BY id LIMIT 3 OFFSET 3;
-- Equivalent syntax
SELECT * FROM users ORDER BY id LIMIT 3, 3;

Pagination performance tip: With large OFFSETs (e.g., LIMIT 10 OFFSET 100000), MySQL still scans and discards the first 100,000 rows. For large-table pagination, use keyset pagination (cursor-based): SELECT * FROM users WHERE id > last_seen_id ORDER BY id LIMIT 10;

8.4 Aggregate Functions

-- Total count
SELECT COUNT(*) AS total_users FROM users;

-- Active user count
SELECT COUNT(*) AS active_count FROM users WHERE status = 'active';

-- Average balance
SELECT AVG(balance) AS avg_balance FROM users;

-- Max / Min balance
SELECT MAX(balance) AS max_bal, MIN(balance) AS min_bal FROM users;

-- Sum of balances
SELECT SUM(balance) AS total_balance FROM users WHERE status = 'active';

8.5 GROUP BY

-- Group by status, count users and average balance
SELECT
    status,
    COUNT(*)       AS user_count,
    AVG(balance)   AS avg_balance,
    SUM(balance)   AS total_balance
FROM users
GROUP BY status;

-- Total order amount per user
SELECT
    user_id,
    COUNT(*)               AS order_count,
    SUM(quantity * price)  AS total_amount
FROM orders
GROUP BY user_id
ORDER BY total_amount DESC;

8.6 HAVING (Filter After Grouping)

-- Find users with total order amounts exceeding 5000
SELECT
    user_id,
    SUM(quantity * price) AS total_amount
FROM orders
GROUP BY user_id
HAVING total_amount > 5000
ORDER BY total_amount DESC;

WHERE vs HAVING: WHERE filters raw rows before grouping; HAVING filters aggregated results after grouping. You cannot use aggregate functions in WHERE, but you can in HAVING.

9. Updating and Deleting Data

9.1 UPDATE

-- Update a single row
UPDATE users SET balance = 1500.00 WHERE id = 1;

-- Update multiple columns
UPDATE users
SET age = 29, email = '[email protected]'
WHERE username = 'alice';

-- Calculated update (give all active users a 10% balance boost)
UPDATE users
SET balance = balance * 1.10
WHERE status = 'active';

-- Preview which rows will be affected before updating
SELECT * FROM users WHERE status = 'active';

Always include WHERE! An UPDATE without WHERE modifies every row in the table. This is one of the most common beginner mistakes. Before running an UPDATE, run a SELECT with the same WHERE clause to confirm.

9.2 DELETE

-- Delete a single row
DELETE FROM users WHERE id = 8;

-- Delete rows matching a condition
DELETE FROM users WHERE status = 'banned';

-- Delete all data (keep table structure)
DELETE FROM orders;
-- Or use TRUNCATE (faster, resets AUTO_INCREMENT, not rollback-safe)
TRUNCATE TABLE orders;

DELETE vs TRUNCATE: - DELETE removes rows one by one, logs undo entries, supports rollback and WHERE clauses - TRUNCATE releases data pages directly, extremely fast, but cannot be rolled back, does not fire triggers, and resets AUTO_INCREMENT

10. JOIN Queries

Real-world data is usually spread across multiple tables. JOINs let you combine tables in a single query.

10.1 INNER JOIN

INNER JOIN returns only rows that have matching entries in both tables. Users with no orders will not appear in the result.

-- Query all orders with corresponding usernames
SELECT
    o.id        AS order_id,
    u.username,
    o.product,
    o.quantity,
    o.price,
    o.quantity * o.price AS total,
    o.order_date
FROM orders o
INNER JOIN users u ON o.user_id = u.id
ORDER BY o.order_date DESC;

10.2 LEFT JOIN

LEFT JOIN returns all rows from the left table. If there is no match in the right table, the right columns are NULL.

-- Query all users and their order counts (including users with no orders)
SELECT
    u.id,
    u.username,
    u.status,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.quantity * o.price), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.status
ORDER BY total_spent DESC;

COALESCE: When a user has no orders, SUM returns NULL. COALESCE(expr, 0) replaces NULL with 0 for cleaner results.

10.3 RIGHT JOIN

RIGHT JOIN is the opposite of LEFT JOIN — it returns all rows from the right table. In practice it is rarely used because you can swap the table order and use LEFT JOIN instead, which is more readable.

-- Equivalent to the LEFT JOIN above (tables swapped)
SELECT
    u.id,
    u.username,
    COUNT(o.id) AS order_count
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id
GROUP BY u.id, u.username;

10.4 CROSS JOIN (Cartesian Product)

-- Generate all user-product combinations (useful for test data)
SELECT u.username, o.product
FROM users u
CROSS JOIN (SELECT DISTINCT product FROM orders) o
LIMIT 20;

10.5 Self Join

-- Given an employees table, find each employee and their manager
-- CREATE TABLE employees (id INT, name VARCHAR(50), manager_id INT);

SELECT
    e.name  AS employee,
    m.name  AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

10.6 Multi-Table JOIN

-- Three-table join example (assuming a products table exists)
-- SELECT o.id, u.username, p.product_name, o.quantity
-- FROM orders o
-- JOIN users u ON o.user_id = u.id
-- JOIN products p ON o.product_id = p.id;

JOIN performance tip: Ensure JOIN condition columns (e.g., user_id, id) are indexed. JOINs without indexes on large tables are extremely slow because MySQL must scan rows one by one.

11. Index Basics

An index is like a book's table of contents. Without indexes, MySQL must scan the entire table (full table scan) to find data. With an index, MySQL can jump directly to the target rows.

11.1 Creating Indexes

-- Single-column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (multi-column)
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Using ALTER TABLE
ALTER TABLE users ADD INDEX idx_age (age);

11.2 Viewing Indexes

SHOW INDEX FROM users;
SHOW INDEX FROM orders;

11.3 When Do You Need an Index?

11.4 Using EXPLAIN to View Execution Plans

-- Add EXPLAIN before SELECT
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- Key columns in the output:
-- type: const / ref / range / ALL
-- possible_keys: indexes that could be used
-- key: index actually used
-- rows: estimated rows scanned

-- Compare with and without an index

-- With index (using idx_users_email)
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- type=ref, rows=1  ← great

-- Column without index
EXPLAIN SELECT * FROM users WHERE age = 28;
-- type=ALL, rows=8  ← full table scan

-- Add index and check again
ALTER TABLE users ADD INDEX idx_age (age);
EXPLAIN SELECT * FROM users WHERE age = 28;
-- type=ref, rows=1  ← improved

11.5 Dropping Indexes

DROP INDEX idx_age ON users;
-- Or
ALTER TABLE users DROP INDEX idx_age;

Go deeper: Indexing is the single most important topic in MySQL performance optimization. To learn about B+Tree internals, composite index leftmost prefix rules, covering indexes, 12 index failure scenarios, and more, read the Index Optimization Guide.

12. Importing and Exporting Data

12.1 Exporting with mysqldump

mysqldump is MySQL's built-in logical backup tool that exports a database as a SQL file.

# Export an entire database
mysqldump -u root -p shop > shop_backup.sql

# Export a single table
mysqldump -u root -p shop users > users_backup.sql

# Export multiple tables
mysqldump -u root -p shop users orders > users_orders_backup.sql

# Export schema only (no data)
mysqldump -u root -p --no-data shop > shop_schema.sql

# Export data only (no CREATE TABLE statements)
mysqldump -u root -p --no-create-info shop > shop_data.sql

# Export all databases
mysqldump -u root -p --all-databases > all_databases.sql

12.2 Importing SQL Files

# Method 1: Command line import
mysql -u root -p shop < shop_backup.sql

# Method 2: Inside the MySQL prompt
mysql> USE shop;
mysql> SOURCE /path/to/shop_backup.sql;

12.3 Exporting to CSV

-- Export to CSV from within MySQL
SELECT * FROM users
INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- Note: INTO OUTFILE writes to the MySQL server's filesystem
-- If you lack permissions, export from the client side:
mysql -u root -p -e "SELECT * FROM shop.users" | tr '\t' ',' > users.csv

12.4 Importing from CSV

-- Using LOAD DATA INFILE (fastest method)
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;   -- skip header row

-- If the CSV is on the client machine (not the server)
LOAD DATA LOCAL INFILE '/path/on/client/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

LOAD DATA is 20x+ faster than INSERT: When importing large volumes of data (tens of thousands of rows or more), strongly prefer LOAD DATA INFILE over row-by-row INSERT. It reduces disk I/O and redo log overhead via bulk writes.

12.5 Practical Export Tips

# Compress during export (essential for large databases)
mysqldump -u root -p shop | gzip > shop_backup.sql.gz

# Import compressed file
gunzip < shop_backup.sql.gz | mysql -u root -p shop

# Include timestamp in filename (handy for backup management)
mysqldump -u root -p shop > "shop_$(date +%Y%m%d_%H%M%S).sql"

Frequently Asked Questions (FAQ)

Q: What is the difference between MySQL and MariaDB? Which should I choose?

MariaDB is a fork of MySQL created by MySQL's original author Monty after Oracle acquired Sun/MySQL. The two are highly compatible in basic SQL syntax. Differences: MariaDB has some unique features (Aria engine, sequences, system-versioned tables), while MySQL has its own (InnoDB Cluster, Group Replication, HeatWave). Recommendation: If you are a beginner, either works fine. For enterprise projects, choose based on team experience and ecosystem. Most cloud platforms (AWS, GCP, Azure) provide MySQL by default.

Q: I forgot the root password. How do I reset it?

You can reset the password by starting MySQL with grant tables skipped: # 1. Stop MySQL sudo systemctl stop mysql # 2. Start with grant tables skipped sudo mysqld --skip-grant-tables --skip-networking & # 3. Log in without password mysql -u root # 4. Reset password (MySQL 8.0+) FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword'; # 5. Restart MySQL sudo systemctl restart mysql

Q: Does VARCHAR(50) vs VARCHAR(255) affect performance?

Storage-wise, the difference is minimal — VARCHAR only uses the actual content length + 1-2 bytes for the length prefix. But there are differences in these scenarios: - Sort buffer allocation: When MySQL performs ORDER BY / GROUP BY, it may allocate MAX length of memory per row. VARCHAR(255) allocates more temporary memory than VARCHAR(50). - InnoDB row format: If the combined MAX lengths of multiple VARCHAR columns in a row exceed 8,126 bytes, InnoDB may store some columns in overflow pages, increasing I/O. Recommendation: Set lengths based on actual data. Use VARCHAR(50) for usernames, VARCHAR(100) for emails, VARCHAR(2048) for URLs. Avoid using VARCHAR(255) everywhere.

Q: When should I use CHAR vs VARCHAR?

CHAR(N): Fixed-length, ideal for data with constant length (country codes CHAR(2), MD5 hashes CHAR(32), UUIDs CHAR(36)). VARCHAR(N): Variable-length, ideal for data with varying lengths (usernames, addresses, titles). In modern InnoDB, the performance difference between CHAR and VARCHAR is very small. When in doubt, use VARCHAR.

Q: What happens when AUTO_INCREMENT reaches its limit?

INT UNSIGNED has a maximum of ~4.29 billion. If you are concerned about running out, use BIGINT UNSIGNED (max ~1.8 x 10^19). If AUTO_INCREMENT reaches its limit, INSERT will fail with ERROR 1062 (23000): Duplicate entry. Solutions: - Change column type: ALTER TABLE t MODIFY id BIGINT UNSIGNED AUTO_INCREMENT; - This operation may take significant time on large tables (MySQL 8.0 supports Instant DDL to speed up certain ALTER operations)

*Q: Why should I avoid SELECT ?

This tutorial uses SELECT * for brevity, but you should avoid it in production code because: - Wastes network bandwidth: Transmits columns you do not need - Cannot leverage covering indexes: SELECT * usually requires a table lookup for the full row - Poor maintainability: When the table schema changes (columns added/removed), SELECT * results change too, potentially breaking your application - Large field problem: If the table has TEXT/BLOB columns, SELECT * reads those large fields even if you do not need them Recommendation: Always explicitly list the column names you need.

Q: How do I create a new user and grant permissions?

-- Create a user (localhost only) CREATE USER 'myapp'@'localhost' IDENTIFIED BY 'StrongPassword123!'; -- Grant SELECT, INSERT, UPDATE, DELETE on the shop database GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'myapp'@'localhost'; -- Allow connections from any IP (not recommended for production) CREATE USER 'myapp'@'%' IDENTIFIED BY 'StrongPassword123!'; GRANT ALL PRIVILEGES ON shop.* TO 'myapp'@'%'; -- Reload privileges FLUSH PRIVILEGES; -- Check user privileges SHOW GRANTS FOR 'myapp'@'localhost';

Q: How do I check MySQL's current running status?

-- View current connections and running queries SHOW PROCESSLIST; -- View global status variables SHOW GLOBAL STATUS LIKE 'Threads_connected'; SHOW GLOBAL STATUS LIKE 'Queries'; SHOW GLOBAL STATUS LIKE 'Slow_queries'; -- View system variables SHOW VARIABLES LIKE 'max_connections'; SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; -- View database sizes SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' FROM information_schema.TABLES GROUP BY table_schema ORDER BY SUM(data_length + index_length) DESC;

Q: What are the noteworthy new features in MySQL 8.0?

• Window Functions: ROW_NUMBER(), RANK(), LAG(), LEAD() — no more complex subqueries - CTE (Common Table Expressions): WITH clause, supports recursive queries, cleaner code - JSON improvements: JSON_TABLE() converts JSON to relational tables, JSON_ARRAYAGG() aggregate function - Invisible Indexes: Test the effect of dropping an index without actually dropping it - Atomic DDL: CREATE TABLE / DROP TABLE operations are atomic, safer crash recovery - Default charset is utf8mb4: No more manual charset specification needed - Descending Indexes: Native descending indexes for ORDER BY ... DESC queries

Q: What should I learn next?

After mastering this tutorial, we recommend the following learning path: 1. Index Optimization — Index Optimization Guide 2. EXPLAIN Analysis — EXPLAIN Analyzer 3. Query Optimization — Query Optimization 4. Transactions & Locks — Locks & Deadlocks 5. InnoDB Architecture — InnoDB Internals Deep Dive