MySQL Cheatsheet

Table of Contents

1. MySQL Basics
2. Data Types
3. Database Operations
4. Table Operations
5. Data Manipulation
6. Querying Data
7. Joins
8. Functions
9. Indexes
10. Views
11. Stored Procedures
12. Triggers
13. Transactions
14. User Management
15. Backup and Restore
16. Performance Optimization
17. MySQL-Specific Features

MySQL Basics

Connecting to MySQL

mysql -u username -p

Show Databases

SHOW DATABASES;

Select Database

USE database_name;

Show Tables

SHOW TABLES;

Show Table Structure

DESCRIBE table_name;

Data Types

Numeric Types

• INT, BIGINT, SMALLINT, TINYINT
• DECIMAL(M,D), NUMERIC(M,D)
• FLOAT, DOUBLE

String Types

• CHAR(M)
• VARCHAR(M)
• TEXT, MEDIUMTEXT, LONGTEXT

Date and Time Types

• DATE
• TIME
• DATETIME
• TIMESTAMP

Other Types

• ENUM
• SET
• BOOLEAN (TINYINT(1))
• JSON (MySQL 5.7.8+)

Database Operations

Create Database

CREATE DATABASE database_name;

Drop Database

DROP DATABASE database_name;

Table Operations

Create Table

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    salary DECIMAL(10, 2)
);

Alter Table

-- Add column
ALTER TABLE employees ADD COLUMN department VARCHAR(50);

-- Modify column
ALTER TABLE employees MODIFY COLUMN department VARCHAR(100);

-- Drop column
ALTER TABLE employees DROP COLUMN department;

Drop Table

DROP TABLE employees;

Data Manipulation

Insert Data

INSERT INTO employees (first_name, last_name, email, hire_date, salary)
VALUES ('John', 'Doe', 'john@example.com', '2023-01-15', 50000.00);

Update Data

UPDATE employees
SET salary = 55000.00
WHERE id = 1;

Delete Data

DELETE FROM employees
WHERE id = 1;

Querying Data

Basic SELECT

SELECT * FROM employees;

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000
ORDER BY last_name ASC;

LIMIT and OFFSET

SELECT * FROM employees
LIMIT 10 OFFSET 20;

LIKE for Pattern Matching

SELECT * FROM employees
WHERE last_name LIKE 'S%';

IN Operator

SELECT * FROM employees
WHERE department_id IN (1, 2, 3);

BETWEEN Operator

SELECT * FROM employees
WHERE salary BETWEEN 40000 AND 60000;

Joins

INNER JOIN

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

LEFT JOIN

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

RIGHT JOIN

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

Functions

Aggregate Functions

SELECT 
    COUNT(*) as total_employees,
    AVG(salary) as average_salary,
    MAX(salary) as highest_salary,
    MIN(salary) as lowest_salary,
    SUM(salary) as total_salary
FROM employees;

String Functions

SELECT 
    CONCAT(first_name, ' ', last_name) as full_name,
    UPPER(last_name) as upper_last_name,
    LENGTH(first_name) as name_length
FROM employees;

Date Functions

SELECT 
    first_name,
    hire_date,
    YEAR(hire_date) as hire_year,
    DATEDIFF(CURDATE(), hire_date) as days_employed
FROM employees;

Indexes

Create Index

CREATE INDEX idx_last_name ON employees (last_name);

Create Unique Index

CREATE UNIQUE INDEX idx_email ON employees (email);

Drop Index

DROP INDEX idx_last_name ON employees;

Views

Create View

CREATE VIEW high_salary_employees AS
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 60000;

Query View

SELECT * FROM high_salary_employees;

Stored Procedures

Create Stored Procedure

DELIMITER //
CREATE PROCEDURE GetEmployeesByDepartment(IN dept_id INT)
BEGIN
    SELECT * FROM employees
    WHERE department_id = dept_id;
END //
DELIMITER ;

Call Stored Procedure

CALL GetEmployeesByDepartment(1);

Triggers

Create Trigger

DELIMITER //
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    SET NEW.last_modified = NOW();
END //
DELIMITER ;

Transactions

Transaction Example

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;
-- Or ROLLBACK; if there's an error

User Management

Create User

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Grant Privileges

GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

Revoke Privileges

REVOKE ALL PRIVILEGES ON database_name.* FROM 'newuser'@'localhost';

Backup and Restore

Backup Database

mysqldump -u username -p database_name > backup.sql

Restore Database

mysql -u username -p database_name < backup.sql

Performance Optimization

EXPLAIN Statement

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

Optimize Table

OPTIMIZE TABLE employees;

Show Process List

SHOW PROCESSLIST;

MySQL-Specific Features

Full-Text Search

CREATE FULLTEXT INDEX ft_idx ON articles (title, body);

SELECT * FROM articles
WHERE MATCH (title, body) AGAINST ('keyword' IN NATURAL LANGUAGE MODE);

Group by with ROLLUP

SELECT department, job_title, SUM(salary)
FROM employees
GROUP BY department, job_title WITH ROLLUP;

On Duplicate Key Update

INSERT INTO employees (id, first_name, last_name, salary)
VALUES (1, 'John', 'Doe', 50000)
ON DUPLICATE KEY UPDATE salary = salary + 1000;