MySQL Cheat Sheet

Basic SQL Commands

SELECT: Retrieve data from one or more tables. SELECT column1, column2 FROM table_name;

INSERT INTO: Insert new records into a table. INSERT INTO table_name (column1, column2) VALUES (value1, value2);

UPDATE: Update existing records in a table. UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

DELETE: Delete records from a table. DELETE FROM table_name WHERE condition;

Filtering Data

WHERE: Filter records based on a condition. SELECT * FROM table_name WHERE condition;

BETWEEN: Filter results within a specific range. SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;

IN: Filter results that match any value in a list. SELECT * FROM table_name WHERE column_name IN (value1, value2, value3);

LIKE: Search for a pattern in a column. % represents zero or more characters. _ represents a single character. SELECT * FROM table_name WHERE column_name LIKE 'pattern';

IS NULL: Filter records where a column have a NULL value. SELECT * FROM table_name WHERE column_name IS NULL;

IS NOT NULL: Filter records where a column doesn't have a NULL value. SELECT * FROM table_name WHERE column_name IS NOT NULL;

Sorting Data

ORDER BY: Sort results by one or more columns (Ascending order). SELECT * FROM table_name ORDER BY column_name ASC;

ORDER BY: Sort results by one or more columns (Descending order). SELECT * FROM table_name ORDER BY column_name DESC;

Aggregate Functions

COUNT(): Count the number of rows. SELECT COUNT(column_name) FROM table_name;

SUM(): Calculate the sum of a numeric column. SELECT SUM(column_name) FROM table_name;

AVG(): Calculate the average of a numeric column. SELECT AVG(column_name) FROM table_name;

MAX(): Find the maximum value in a column. SELECT MAX(column_name) FROM table_name;

MIN(): Find the minimum value in a column.

SELECT MIN(column_name) FROM table_name;

Grouping Data

GROUP BY: Group rows that have the same values in specified columns. SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

HAVING: Filter groups based on aggregate conditions (used with GROUP BY). SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;

Joins

INNER JOIN: Select records that have matching values in both tables. SELECT columns FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

LEFT JOIN (or LEFT OUTER JOIN): Select all records from the left table, and the matched records from the right table. SELECT columns FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

RIGHT JOIN (or RIGHT OUTER JOIN): Select all records from the right table, and the matched records from the left table. SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

FULL OUTER JOIN: Select all records when there is a match in either left or right table. SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

CROSS JOIN: Returns the Cartesian product of the two tables. SELECT * FROM table1 CROSS JOIN table2;

Subqueries

Subquery in WHERE clause. SELECT * FROM table_name WHERE column_name = (SELECT column_name FROM another_table WHERE condition);

Subquery in FROM clause. SELECT * FROM (SELECT column_name FROM table_name WHERE condition) AS alias_name;

Advanced Filtering and Sorting

LIMIT: Limit the number of rows returned. SELECT * FROM table_name LIMIT 10;

OFFSET: Skip a specific number of rows before beginning to return the rows. SELECT * FROM table_name LIMIT 10 OFFSET 5;

String Functions

CONCAT(): Concatenate two or more strings. SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

SUBSTRING(): Extract a substring from a string. SELECT SUBSTRING(column_name, start, length) FROM table_name;

REPLACE(): Replace all occurrences of a specified substring. SELECT REPLACE(column_name, 'old_value', 'new_value') FROM table_name;

LENGTH(): Get the length of a string. SELECT LENGTH(column_name) FROM table_name;

Mathematical Functions

ROUND(): Round a number to a specified number of decimal places. SELECT ROUND(column_name, 2) FROM table_name;

FLOOR(): Round down to the nearest integer. SELECT FLOOR(column_name) FROM table_name;

CEIL(): Round up to the nearest integer. SELECT CEIL(column_name) FROM table_name;

Date and Time Functions

NOW(): Return the current date and time. SELECT NOW();

CURDATE(): Return the current date. SELECT CURDATE();

DATEDIFF(): Calculate the difference in days between two dates. SELECT DATEDIFF(date1, date2);

DATE_ADD(): Add a specified time interval to a date. SELECT DATE_ADD(date_column, INTERVAL 5 DAY) FROM table_name;

DATE_FORMAT(): Format a date according to a specified format. SELECT DATE_FORMAT(date_column, '%Y-%m-%d') FROM table_name;

Case Statements

CASE: Perform conditional logic in SQL. SELECT name, CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END AS grade FROM students;

Indexes

Create Index: Improve the speed of data retrieval. CREATE INDEX index_name ON table_name (column_name);

Drop Index: Remove an index. DROP INDEX index_name ON table_name;

Views

Create View: Create a virtual table based on a SELECT query. CREATE VIEW view_name AS SELECT columns FROM table_name WHERE condition;

Drop View: Remove a view. DROP VIEW view_name;

Stored Procedures

Create Stored Procedure

DELIMITER //
CREATE PROCEDURE procedure_name()
BEGIN
  SELECT * FROM table_name;
END //
DELIMITER ;

Call Stored Procedure

CALL procedure_name();

Transactions

Start Transaction

START TRANSACTION;

Commit Transaction

COMMIT;

Rollback Transaction

ROLLBACK;

User Management

Create User

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

Grant Privileges

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

Revoke Privileges

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

Drop User

DROP USER 'username'@'localhost';

Database Creation and Deletion

Create Database

CREATE DATABASE database_name;

Drop (Delete) Database:

DROP DATABASE database_name;

Use Database

USE database_name;

Table Creation and Deletion

Create Table

CREATE TABLE table_name (
    column1_name data_type constraints,
    column2_name data_type constraints,
    ...
    PRIMARY KEY (column_name),
    FOREIGN KEY (column_name) REFERENCES other_table_name(other_column_name)
);

Example

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT,
    hire_date DATE NOT NULL,
    salary DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (employee_id),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Drop (Delete) Table

DROP TABLE table_name;

Truncate Table (Removes all data but keeps the structure)

TRUNCATE TABLE table_name;

Primary Key

Defining a Primary Key(Inline Definition)

CREATE TABLE table_name (
    column_name data_type PRIMARY KEY,
    ...
);

Defining a Primary Key(Separate Definition)

CREATE TABLE table_name (
    column1_name data_type,
    column2_name data_type,
    ...
    PRIMARY KEY (column_name)
);

Composite Primary Key

CREATE TABLE table_name (
    column1_name data_type,
    column2_name data_type,
    ...
    PRIMARY KEY (column1_name, column2_name)
);

Foreign Key

Defining a Foreign Key

CREATE TABLE table_name (
    column_name data_type,
    foreign_key_column data_type,
    ...
    FOREIGN KEY (foreign_key_column) REFERENCES other_table_name(other_column_name)
);

Foreign Key Example

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Adding a Foreign Key to an Existing Table

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (foreign_key_column) REFERENCES other_table_name(other_column_name);

Constraints

Adding Constraints

NOT NULL: Ensures that a column cannot have a NULL value. CREATE TABLE table_name ( column_name data_type NOT NULL, ... );

UNIQUE: Ensures that all values in a column are different. CREATE TABLE table_name ( column_name data_type UNIQUE, ... );

DEFAULT: Provides a default value for a column if no value is specified. CREATE TABLE table_name ( column_name data_type DEFAULT default_value, ... );

CHECK: Ensures that all values in a column satisfy a specific condition. CREATE TABLE table_name ( column_name data_type, ... CHECK (column_name > 0) );

AUTO_INCREMENT: Automatically generates a unique number when a new record is inserted. CREATE TABLE table_name ( column_name INT AUTO_INCREMENT, ... );

Adding Constraints to an Existing Table
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name);

Dropping Constraints

Drop Primary Key
ALTER TABLE table_name DROP PRIMARY KEY;

Drop Foreign Key
ALTER TABLE table_name DROP FOREIGN KEY constraint_name;

Drop Unique Constraint
ALTER TABLE table_name DROP INDEX constraint_name;

Drop Check Constraint
ALTER TABLE table_name DROP CHECK constraint_name;

Altering Tables

Add a New Column

ALTER TABLE table_name ADD column_name data_type constraints;

Drop a Column

ALTER TABLE table_name DROP COLUMN column_name;

Modify a Column's Data Type

ALTER TABLE table_name MODIFY COLUMN column_name new_data_type;

Rename a Column

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

Renaming and Copying Tables

Rename a Table

RENAME TABLE old_table_name TO new_table_name;

Copy a Table (Structure only)

CREATE TABLE new_table_name LIKE old_table_name;

Copy a Table (Structure and Data)

CREATE TABLE new_table_name AS SELECT * FROM old_table_name;

Categories

Interview Questions

Cheat Sheets