MySQL Complete Guide

MySQL is the most widely used open-source relational database. This guide covers the SQL syntax you'll use regularly in practice. For installation and setup instructions, see the MySQL Installation and Setup Guide.

Database Management

Create / Drop Database

-- Create
CREATE DATABASE db_name;
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Drop
DROP DATABASE db_name;

-- List databases
SHOW DATABASES;

-- Select database
USE db_name;

Table Management

Creating Tables

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

Copying Tables

-- Copy structure only (includes AUTO_INCREMENT, INDEX)
CREATE TABLE new_table LIKE old_table;

-- Copy structure and data (excludes AUTO_INCREMENT)
CREATE TABLE new_table SELECT * FROM old_table;

-- Copy data only
INSERT INTO new_table SELECT * FROM old_table;

-- Copy specific columns
INSERT INTO new_table(col1, col2) SELECT col1, col2 FROM old_table;

Viewing Tables

SHOW TABLES;
DESCRIBE table_name;
SHOW CREATE TABLE table_name;

INSERT

Basic Syntax

-- Single row
INSERT INTO users (name, email, age) VALUES ('John', 'john@example.com', 25);

-- Multiple rows
INSERT INTO users (name, email, age) VALUES
    ('Alice', 'alice@example.com', 30),
    ('Bob', 'bob@example.com', 28),
    ('Charlie', 'charlie@example.com', 35);

Handling Duplicate Keys

-- Update on duplicate (UPSERT)
INSERT INTO users (id, name, email) VALUES (1, 'John', 'john@example.com')
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    email = VALUES(email);

-- Ignore on duplicate
INSERT IGNORE INTO users (name, email) VALUES ('John', 'john@example.com');

AUTO_INCREMENT Management

-- Change starting value
ALTER TABLE users AUTO_INCREMENT = 100;

-- Reset
ALTER TABLE users AUTO_INCREMENT = 0;

SELECT

Basic Syntax

-- All rows
SELECT * FROM users;

-- Specific columns
SELECT name, email FROM users;

-- With condition
SELECT * FROM users WHERE age > 25;

-- Ordering
SELECT * FROM users ORDER BY created_at DESC;

-- Limiting results
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;  -- pagination

Conditions

-- AND, OR
SELECT * FROM users WHERE age > 25 AND name LIKE 'J%';

-- IN
SELECT * FROM users WHERE id IN (1, 2, 3);

-- BETWEEN
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

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

-- LIKE pattern matching
SELECT * FROM users WHERE name LIKE 'J%';     -- starts with 'J'
SELECT * FROM users WHERE name LIKE '%son';    -- ends with 'son'
SELECT * FROM users WHERE name LIKE '%oh%';    -- contains 'oh'

Aggregate Functions

SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT MAX(age), MIN(age) FROM users;
SELECT SUM(age) FROM users;

-- GROUP BY
SELECT age, COUNT(*) as count FROM users GROUP BY age;

-- HAVING (group condition)
SELECT age, COUNT(*) as count FROM users GROUP BY age HAVING count > 5;

JOIN

-- INNER JOIN
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN
SELECT u.name, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN
SELECT u.name, o.order_date
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

UPDATE

Basic Syntax

UPDATE users SET name = 'New Name' WHERE id = 1;

-- Multiple columns
UPDATE users SET name = 'New Name', age = 30 WHERE id = 1;

Increment / Decrement

-- Increment
UPDATE users SET login_count = login_count + 1 WHERE id = 1;

-- Decrement
UPDATE products SET stock = stock - 1 WHERE id = 100;

Conditional Update

UPDATE users
SET status = CASE
    WHEN age < 20 THEN 'minor'
    WHEN age >= 20 AND age < 65 THEN 'adult'
    ELSE 'senior'
END;

DELETE

Basic Syntax

-- Delete with condition
DELETE FROM users WHERE id = 1;

-- Multiple conditions
DELETE FROM users WHERE age < 18 AND status = 'inactive';

-- Delete all rows (careful!)
DELETE FROM users;

-- Truncate (faster, resets AUTO_INCREMENT)
TRUNCATE TABLE users;

Safe Deletion

-- Verify before deleting
SELECT * FROM users WHERE status = 'inactive';

-- Then delete
DELETE FROM users WHERE status = 'inactive';

ALTER

Add / Modify / Drop Columns

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

-- Change column type
ALTER TABLE users MODIFY COLUMN age BIGINT;

-- Rename and change type
ALTER TABLE users CHANGE COLUMN old_name new_name VARCHAR(100);

-- Drop column
ALTER TABLE users DROP COLUMN phone;

Constraints

-- Add PRIMARY KEY
ALTER TABLE users ADD PRIMARY KEY (id);

-- Add UNIQUE constraint
ALTER TABLE users ADD UNIQUE (email);

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

-- Drop constraint
ALTER TABLE users DROP INDEX email;

INDEX

Indexes improve query performance but can affect INSERT/UPDATE/DELETE speed.

Creating Indexes

-- Single column index
CREATE INDEX idx_name ON users(name);

-- Composite index
CREATE INDEX idx_name_email ON users(name, email);

-- UNIQUE index
CREATE UNIQUE INDEX idx_email ON users(email);

Viewing Indexes

SHOW INDEX FROM users;

Dropping Indexes

DROP INDEX idx_name ON users;

Index Tips

Create indexes on columns frequently used in WHERE, JOIN, and ORDER BY
Indexes are most effective on high-cardinality columns (high ratio of unique values)
Composite indexes are used left to right in order

mysqladmin Commands

Administrative tasks can be performed outside the MySQL console.

# Create database
mysqladmin -u root -p create db_name

# Drop database
mysqladmin -u root -p drop db_name

# Check MySQL status
mysqladmin -u root -p status

# Check MySQL version
mysqladmin -u root -p version

# Execute SQL statement
mysqladmin -u root -p -e "SHOW DATABASES"

Useful Tips

Optimizing Bulk Inserts

-- Wrap in a transaction
START TRANSACTION;
INSERT INTO users (name) VALUES ('user1');
INSERT INTO users (name) VALUES ('user2');
-- ... many INSERTs
COMMIT;

Checking Query Execution Plans

EXPLAIN SELECT * FROM users WHERE name = 'John';

Table Optimization

OPTIMIZE TABLE users;
ANALYZE TABLE users;