Data ScienceSQL

MySQL Cheat Sheet

Last Updated: 18th August, 2024

Arunav Goswami

Data Science Consultant at almaBetter

Access a comprehensive MySQL cheat sheet covering essential commands, functions, joins, aggregation and security tips, ideal for interviews and quick reference

MySQL is a powerful, open-source relational database management system widely used in various applications, from web development to data analytics. Whether you're preparing for an interview, writing SQL queries, or defending against SQL injections, having a MySQL cheatsheet can be invaluable. This guide covers essential MySQL commands, functions, and best practices to streamline your workflow and improve your database management skills.

Introduction to MySQL Cheat Sheet for Interviews

A MySQL cheat sheet is a handy reference during interviews that provides quick access to common commands, functions, and syntax used in MySQL. It serves as a quick guide for developers, database administrators, and anyone working with MySQL to execute tasks more efficiently. From basic operations to complex queries, this cheat sheet includes everything one needs to manage MySQL databases effectively.

MySQL Commands Cheat Sheet

This section lists essential MySQL commands commonly used for database management.

Basic MySQL Commands

Connecting to MySQL:

mysql -u username -p

Connect to the MySQL server using the specified username.

Create Database:

CREATE DATABASE database_name;

Creates a new database.

Select Database:

USE database_name;

Selects a database to use for subsequent operations.

Show Databases:

SHOW DATABASES;

Displays all databases available on the MySQL server.

Table Management

Create Table:

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);

Creates a new table with the specified columns and data types.

Describe table structure:

DESCRIBE table_name;

Show the structure of a table including column details.

Drop a table:

DROP TABLE table_name;

Delete a table and its data from the database.

Alter a table (add a column):

ALTER TABLE table_name ADD column_name datatype;

Add a new column to an existing table.

Rename a table:

RENAME TABLE old_table_name TO new_table_name;

Change the name of an existing table.

Show Tables:

SHOW TABLES;

Lists all tables in the currently selected database.

Data Manipulation

Insert Data:

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

Inserts a new row into the specified table.

Insert data into specific columns(extension in MySQL):

INSERT INTO table_name SET column1 = value1, column2 = value2;

Insert data into specified columns.

Select Data:

SELECT column1, column2, ...
FROM table_name;

Retrieves data from the specified table.

Select all data from a table:

SELECT * FROM table_name;

Retrieve all columns and rows from a table.

Update Data:

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

Updates existing data in a table based on a condition.

Delete Data:

DELETE FROM table_name WHERE condition;

Deletes data from a table based on a condition.

MySQL Joins Cheat Sheet

Joins are used to combine rows from two or more tables based on related columns.

Inner Join:

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

Returns rows with matching values in both tables.

Left Join (Left Outer Join):

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

Returns all rows from the left table, and the matched rows from the right table.

Right Join (Right Outer Join):

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

Returns all rows from the right table, and the matched rows from the left table.

Full Join (Full Outer Join):

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

Returns rows when there is a match in one of the tables.

Cross Join:

SELECT columns
FROM table1
CROSS JOIN table2;

Returns the Cartesian product of the two tables.

MySQL Aggregation Cheat Sheet

Aggregation in MySQL allows to summarize data across multiple rows into a single result. This is crucial for data analysis and reporting tasks.

Common Aggregation Functions

COUNT(): Returns the number of rows that match a specified condition.

SELECT COUNT(*) FROM employees;

SUM(): Returns the total sum of a numeric column.

SELECT SUM(salary) FROM employees;

AVG(): Returns the average value of a numeric column.

SELECT AVG(salary) FROM employees;

MIN(): Returns the smallest value in a set of values.

SELECT MIN(salary) FROM employees;

MAX(): Returns the largest value in a set of values.

SELECT MAX(salary) FROM employees;

Using Aggregation with GROUP BY

The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, often used with aggregation functions.

Example:

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

This query calculates the total salary for each department.

Filtering Groups with HAVING

The HAVING clause filters the result of aggregation functions, similar to WHERE, but applied after grouping.

Example:

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

This query displays departments with more than five employees.

MySQL Functions Cheat Sheet

Functions in MySQL perform operations on data and return a value.

String Functions:

CONCAT(): Concatenates two or more strings.

SELECT CONCAT('Hello', ' ', 'World');

SUBSTRING(): Extracts a substring from a string.

SELECT SUBSTRING('Hello World', 1, 5);

LENGTH(): Returns the length of a string.

SELECT LENGTH('Hello');

Numeric Functions:

ABS(): Returns the absolute value of a number.

SELECT ABS(-10);

ROUND(): Rounds a number to a specified number of decimal places.

SELECT ROUND(123.456, 2);

FLOOR(): Returns the largest integer less than or equal to a number.

SELECT FLOOR(123.456);

Date and Time Functions:

NOW(): Returns the current date and time.

SELECT NOW();

CURDATE(): Returns the current date.

SELECT CURDATE();

DATEDIFF(): Returns the difference in days between two dates.

SELECT DATEDIFF('2024-12-31', '2024-01-01');

MySQL Syntax Cheat Sheet

SQL Syntax Overview

Understanding MySQL syntax is crucial for writing effective queries. Below are some common syntax rules:

Comments:
- Single-line comment: -- comment
- Multi-line comment: /* comment */
Data Types:
- INT: Integer
- VARCHAR(size): Variable-length string
- DATE: Date (YYYY-MM-DD format)
Constraints:
- PRIMARY KEY: Uniquely identifies each record in a table.
- FOREIGN KEY: Ensures referential integrity by linking a column to another table's primary key.
- NOT NULL: Ensures that a column cannot have a NULL value.

MySQL Injection Cheat Sheet

SQL injection is a common web security vulnerability that allows attackers to interfere with the queries made to a database. Protecting the MySQL databases from such attacks is essential.

Common SQL Injection Techniques

Union-Based SQL Injection:

SELECT * FROM users WHERE id = 1 UNION SELECT username, password FROM admin;

Exploits the UNION SQL operator to extract data from another table.

Error-Based SQL Injection:

SELECT * FROM users WHERE id = 1 AND 1=1;

Injects malicious SQL that forces the database to throw an error, revealing information.

Preventing SQL Injection

Use Prepared Statements:

SELECT * FROM users WHERE username = ?;

Ensures that user inputs are treated as data, not executable code.
Sanitize Inputs:
- Validate and sanitize all user inputs before using them in SQL queries.
- Use parameterized queries and avoid dynamic SQL where possible.

Advanced MySQL Query Cheat Sheet

User Management

Create User:

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

Creates a new user with specified credentials.

Grant Privileges:

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

Grants specific privileges to a user for a database.

Show Grants:

SHOW GRANTS FOR 'username'@'host';

Displays the privileges granted to a user.

Delete a user:

DROP USER 'username'@'localhost';

Revoke Privileges:

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';

Removes privileges from a user.

Backup and Restore

Backup Database:

mysqldump -u username -p database_name > backup.sql

Creates a backup of the specified database.

Restore Database:

mysql -u username -p database_name < backup.sql

Restores a database from a backup file.

Indexes

Create an index:

CREATE INDEX index_name ON table_name(column_name);

Create an index on a column to speed up queries

Drop an index:

DROP INDEX index_name ON table_name;

Remove an index from a table.

Transactions

Start a transaction:

START TRANSACTION;

Begin a new transaction.

Commit a transaction:

COMMIT;

Save changes made during the transaction.

Rollback a transaction:

ROLLBACK;

Undo changes made during the transaction.

Miscellaneous

Show the current user:

SELECT USER();

Display the currently logged-in MySQL user.

Check the version of MySQL:

SELECT VERSION();

Show the MySQL server version.

Exit MySQL:

EXIT;

Log out from the MySQL command line.

Conclusion

Having a MySQL cheat sheet is essential for both beginners and experienced users. It not only aids in quickly referencing common commands, joins, functions, and syntax but also helps in understanding and preventing SQL injection attacks. By keeping this cheat sheet handy, one can enhance their MySQL skills, improve database management efficiency, and prepare effectively for interviews.