Cheat Sheet

1. Basic Commands

Connecting to MySQL

mysql -u username -p / mysql -u root -p

You'll be prompted to enter the password.

Show Databases

SHOW DATABASES;

Create a Database

CREATE DATABASE database_name;

Use a Database

USE database_name;

Show Tables

SHOW TABLES;

Describe a Table

DESCRIBE table_name;

Or

DESC table_name;

Drop Database

DROP DATABASE database_name;

Drop Table

DROP TABLE table_name;

---

2. Data Types

Numeric Types

• INT – Integer

• DECIMAL(p, s) – Fixed-point

• FLOAT – Floating-point

• DOUBLE – Double precision

String Types

• VARCHAR(n) – Variable-length string

• CHAR(n) – Fixed-length string

• TEXT – Large text

• BLOB – Binary Large Object

Date and Time Types

• DATE – 'YYYY-MM-DD'

• DATETIME – 'YYYY-MM-DD HH:MM:SS'

• TIMESTAMP – Automatic timestamp

• TIME – 'HH:MM:SS'

• YEAR – 'YYYY'

---

3. CRUD Operations

Create (INSERT)

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example:

INSERT INTO employees (first_name, last_name, age)
VALUES ('John', 'Doe', 30);

Read (SELECT)

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column
LIMIT number;

Examples:

• Select all columns:

  SELECT * FROM employees;

• Select with condition:

  SELECT first_name, last_name FROM employees WHERE age > 25;

• Ordering and limiting:

  SELECT * FROM employees ORDER BY last_name ASC LIMIT 10;

Update (UPDATE)

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE employees
SET age = 31
WHERE first_name = 'John' AND last_name = 'Doe';

Delete (DELETE)

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM employees
WHERE age < 20;

---

4. Advanced Queries

JOINs

INNER JOIN

Returns records with matching values in both tables.

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

LEFT JOIN (LEFT OUTER JOIN)

Returns all records from the left table and matched records from the right table.

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all records from the right table and matched records from the left table.

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

FULL OUTER JOIN

MySQL doesn’t support FULL OUTER JOIN directly, but it can be simulated:

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column
UNION
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

GROUP BY and HAVING

GROUP BY

Aggregates data based on one or more columns.

SELECT column, COUNT(*)
FROM table_name
GROUP BY column;

HAVING

Filters groups based on aggregate functions.

SELECT column, COUNT(*)
FROM table_name
GROUP BY column
HAVING COUNT(*) > 5;

ORDER BY

Sorts the result set.

SELECT columns
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

LIMIT

Restricts the number of rows returned.

SELECT columns
FROM table_name
LIMIT offset, count;

Or

SELECT columns
FROM table_name
LIMIT count OFFSET offset;

---

5. Indexes

Create an Index

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Create a Unique Index

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

Drop an Index

DROP INDEX index_name
ON table_name;

View Indexes

SHOW INDEX FROM table_name;

---

6. Constraints

Primary Key

CREATE TABLE table_name (
    id INT NOT NULL,
    name VARCHAR(50),
    PRIMARY KEY (id)
);

Foreign Key

CREATE TABLE child_table (
    id INT,
    parent_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (parent_id) REFERENCES parent_table(id)
);

Unique Constraint

CREATE TABLE table_name (
    id INT,
    email VARCHAR(100),
    UNIQUE (email)
);

Not Null Constraint

CREATE TABLE table_name (
    id INT NOT NULL,
    name VARCHAR(50) NOT NULL
);

Default Value

CREATE TABLE table_name (
    id INT,
    status VARCHAR(20) DEFAULT 'active'
);

---

7. Functions

Aggregate Functions

• COUNT(column)

• SUM(column)

• AVG(column)

• MIN(column)

• MAX(column)

Example:

SELECT COUNT(*), AVG(age)
FROM employees;

String Functions

• CONCAT(str1, str2, ...) – Concatenates strings

• SUBSTRING(str, pos, length) – Extracts a substring

• LENGTH(str) – Returns the length of a string

• UPPER(str) – Converts to uppercase

• LOWER(str) – Converts to lowercase

Example:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

Date Functions

• NOW() – Current date and time

• CURDATE() – Current date

• DATE_ADD(date, INTERVAL value unit) – Adds interval to date

• DATEDIFF(date1, date2) – Difference in days

Example:

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS next_week;

Numeric Functions

• ROUND(number, decimals) – Rounds a number

• CEIL(number) – Rounds up

• FLOOR(number) – Rounds down

Example:

SELECT ROUND(AVG(age), 2) AS average_age
FROM employees;

---

8. Views

Create a View

CREATE VIEW view_name AS
SELECT columns
FROM table_name
WHERE condition;

Select from a View

SELECT * FROM view_name;

Drop a View

DROP VIEW view_name;

---

9. Transactions

Start a Transaction

START TRANSACTION;

Or

BEGIN;

Commit a Transaction

COMMIT;

Rollback a Transaction

ROLLBACK;

---

10. User Management

Create a User

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Grant Privileges

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

Or specific privileges:

GRANT SELECT, INSERT ON database_name.table_name TO 'username'@'host';

Flush Privileges

FLUSH PRIVILEGES;

Revoke Privileges

REVOKE SELECT ON database_name.table_name FROM 'username'@'host';

Drop a User

DROP USER 'username'@'host';

---

11. Backup and Restore

Backup Database

mysqldump -u username -p database_name > backup.sql

Restore Database

mysql -u username -p database_name < backup.sql

---

12. Miscellaneous

Set Character Set

SET NAMES 'utf8mb4';

Change Password

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

Check Current Database

SELECT DATABASE();

Check Current User

SELECT USER();

---

13. Common Shortcuts

• Select all columns:

  SELECT * FROM table_name;

• Insert multiple rows:

  INSERT INTO table_name (column1, column2)
  VALUES 
    (value1a, value2a),
    (value1b, value2b),
    ...;

• Update with condition:

  UPDATE table_name
  SET column = value
  WHERE condition;

• Delete with condition:

  DELETE FROM table_name
  WHERE condition;

---

14. Performance Tips

• Use Indexes: Improve query performance by indexing columns used in WHERE, JOIN, and ORDER BY clauses.

• *Avoid SELECT : Specify only the columns you need.

• Use EXPLAIN: Analyze query execution plans.

  EXPLAIN SELECT * FROM table_name WHERE condition;

• Optimize Joins: Ensure joined columns are indexed.

• Limit Results: Use LIMIT to reduce the amount of data processed.

---

15. Security Best Practices

• Use Strong Passwords: Ensure all MySQL users have strong, unique passwords.

• Least Privilege Principle: Grant only necessary permissions to users.

• Regular Backups: Keep regular backups and store them securely.

• Keep MySQL Updated: Regularly update to the latest stable version to patch vulnerabilities.

• Use SSL/TLS: Encrypt connections between MySQL and clients.