Show Menu
Cheatography
  1. Home >
  2. Programming >

MySQL Concepts Cheat Sheet (DRAFT) by

Important Concepts of MySQL

This is a draft cheat sheet. It is a work in progress and is not finished yet.

Operators in MySQL

Comparison Operators
Logical Operators
Arithmetic Operators
=: Equal to
AND: Returns true if all conditions separated by AND are true
+: Addition
<> or !=: Not equal to
OR: Returns true if any condition separated by OR is true
-: Subtra­ction
<: Less than
NOT: Reverses the value of the following condition
*: Multip­lic­ation
>: Greater than
NULL Operators
/: Division
<=: Less than or equal to
IS NULL: Checks if a value is NULL
%: Modulus (Returns the remainder of a division)
>=: Greater than or equal to
IS NOT NULL: Checks if a value is not NULL
LIKE: Used for pattern matching in strings

String Functions

Function
Explan­ation
Example
CONCAT()
Concat­enates two or more strings.
SELECT CONCAT­('Hello ', 'World') AS Concat­ena­ted­String; -- Output: Hello World
SUBSTR­ING()
Extracts a substring from a string.
SELECT SUBSTR­ING­('M­ySQL', 2, 3) AS Substr­ing­Result; -- Output: ySQ
UPPER()
Converts a string to uppercase.
SELECT UPPER(­'my­sql') AS Upperc­ase­String; -- Output: MYSQL
LOWER()
Converts a string to lowercase.
SELECT LOWER(­'MY­SQL') AS Lowerc­ase­String; -- Output: mysql
LENGTH()
Returns the length of a string.
SELECT LENGTH­('M­ySQL') AS String­Length; -- Output: 5
TRIM()
Removes leading and trailing spaces from a string.
SELECT TRIM(' MySQL ') AS Trimme­dSt­ring; -- Output: MySQL
REPLACE()
Replaces occurr­ences of a specified substring within a string.
SELECT REPLAC­E('­Hello World', 'World', 'MySQL') AS Replac­edS­tring; -- Output: Hello MySQL

Date and Time Functions

Function
Explan­ation
Example
NOW()
Returns the current date and time.
SELECT NOW() AS Curren­tDa­teTime; -- Output: Current date and time in 'YYYY-­MM-DD HH:MM:SS' format
CURDATE()
Returns the current date.
SELECT CURDATE() AS Curren­tDate; -- Output: Current date in 'YYYY-­MM-DD' format
CURTIME()
Returns the current time.
SELECT CURTIME() AS Curren­tTime; -- Output: Current time in 'HH:MM:SS' format
YEAR()
Extracts the year from a date.
SELECT YEAR('­202­4-0­3-23') AS Extrac­ted­Year; -- Output: 2024
MONTH()
Extracts the month from a date.
SELECT MONTH(­'20­24-­03-23') AS Extrac­ted­Month; -- Output: 3
DAY()
Extracts the day from a date.
SELECT DAY('2­024­-03­-23') AS Extrac­tedDay; -- Output: 23

Window Functions

Function
Explan­ation
Example
ROW_NU­MBER()
This function assigns a unique integer to each row within a partition according to the specified order. It starts from 1 for the first row and increments by 1 for each subsequent row.
SELECT name, ROW_NU­MBER() OVER (ORDER BY salary DESC) AS row_num FROM employees;
RANK()
Similar to ROW_NU­MBER(), but RANK() assigns the same rank to rows with equal values and leaves gaps in the sequence for ties.
SELECT name, RANK() OVER (ORDER BY score DESC) AS rank FROM students;
DENSE_­RANK()
DENSE_­RANK() is similar to RANK(), but it does not leave gaps in the ranking sequence for ties.
SELECT name, DENSE_­RANK() OVER (ORDER BY age) AS dense_rank FROM users;
NTILE()
This function divides the result set into a specified number of buckets and assigns a bucket number to each row. It ensures an approx­imately equal number of rows in each bucket.
SELECT name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees;
LEAD() and LAG()
LEAD() and LAG() functions allow you to access data from a subsequent or previous row in the result set, respec­tively.
SELECT name, salary, LEAD(s­alary) OVER (ORDER BY salary) AS next_s­alary, LAG(sa­lary) OVER (ORDER BY salary) AS previo­us_­salary FROM employees;

Joins

Join
Explan­ation
Syntax
Example
INNER JOIN
Returns records that have matching values in both tables.
SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.co­lumn;
SELECT orders.or­der_id, custom­ers.cu­sto­mer­_name FROM orders INNER JOIN customers ON orders.cu­sto­mer_id = custom­ers.cu­sto­mer_id;
LEFT JOIN (or LEFT OUTER JOIN)
Returns all records from the left table and the matched records from the right table. If there's no match, the result is NULL on the right side.
SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.co­lumn;
SELECT custom­ers.cu­sto­mer­_name, orders.or­der_id FROM customers LEFT JOIN orders ON custom­ers.cu­sto­mer_id = orders.cu­sto­mer_id;
RIGHT JOIN (or RIGHT OUTER JOIN)
Returns all records from the right table and the matched records from the left table. If there's no match, the result is NULL on the left side.
SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.co­lumn;
SELECT orders.or­der_id, custom­ers.cu­sto­mer­_name FROM orders RIGHT JOIN customers ON orders.cu­sto­mer_id = custom­ers.cu­sto­mer_id;
FULL JOIN (or FULL OUTER JOIN)
Returns all records when there's a match in either left or right table. If there's no match, the result is NULL on the unmatched side.
SELECT columns FROM table1 FULL JOIN table2 ON table1.column = table2.co­lumn;
SELECT custom­ers.cu­sto­mer­_name, orders.or­der_id FROM customers FULL JOIN orders ON custom­ers.cu­sto­mer_id = orders.cu­sto­mer_id;
CROSS JOIN
Returns the Cartesian product of the two tables, i.e., all possible combin­ations of rows.
SELECT columns FROM table1 CROSS JOIN table2;
SELECT * FROM employees CROSS JOIN depart­ments;
Self-Join
Joins a table with itself, typically used to compare rows within the same table.
SELECT columns FROM table1 alias1 INNER JOIN table1 alias2 ON alias1.column = alias2.co­lumn;
SELECT e1.emp­loy­ee_­name, e2.man­age­r_name FROM employees e1 INNER JOIN employees e2 ON e1.man­ager_id = e2.emp­loy­ee_id;

Stored Procedure

Definition
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. It's like a function in a tradit­ional progra­mming language.
Syntax
CREATE PROCEDURE proced­ure­_name (param­eters) BEGIN -- SQL statements END;
Parameters
Stored procedures can accept input parame­ters, which can be used within the proced­ure's SQL statem­ents.
Example
CREATE PROCEDURE GetEmp­loy­ee(IN emp_id INT) BEGIN SELECT * FROM employees WHERE employ­ee_id = emp_id; END;
Calling a Stored Procedure
CALL proced­ure­_na­me(­arg­ume­nts);
Example
CALL GetEmp­loy­ee(­1001);
Dropping a Stored Procedure
DROP PROCEDURE IF EXISTS proced­ure­_name;
Example
DROP PROCEDURE IF EXISTS GetEmp­loyee;
Variables
Stored procedures can declare and use variables within their code.
Example
CREATE PROCEDURE Update­Sal­ary(IN emp_id INT, IN salary DECIMA­L(10, 2)) BEGIN DECLARE emp_name VARCHA­R(50); SELECT employ­ee_name INTO emp_name FROM employees WHERE employ­ee_id = emp_id; UPDATE employees SET employ­ee_­salary = salary WHERE employ­ee_id = emp_id; END;
Control Flow
Stored procedures support control flow constructs such as IF, CASE, and LOOP.
Example
CREATE PROCEDURE CheckA­ge(IN age INT) BEGIN IF age < 18 THEN SELECT 'Minor'; ELSEIF age BETWEEN 18 AND 64 THEN SELECT 'Adult'; ELSE SELECT 'Senior'; END IF; END;
Cursors
Stored procedures can use cursors to process multiple rows returned by a query.
Example
CREATE PROCEDURE Displa­yEm­plo­yees() BEGIN DECLARE done BOOLEAN DEFAULT FALSE; DECLARE emp_name VARCHA­R(50); DECLARE emp_salary DECIMA­L(10, 2); DECLARE emp_cursor CURSOR FOR SELECT employ­ee_­name, employ­ee_­salary FROM employees; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN emp_cu­rsor; read_loop: LOOP FETCH emp_cursor INTO emp_name, emp_sa­lary; IF done THEN LEAVE read_loop; END IF; -- Process fetched data END LOOP; CLOSE emp_cu­rsor; END;

Indexing

Indexing
Indexing is a way to optimize database perfor­mance by quickly locating rows in a table. It allows for faster retrieval of data by creating a sorted reference to the data in a table.
Types
Single Column Index, Composite Index, Unique Index, Primary Key, and Foreign Key
Single Column Index
Index created on a single column.
Composite Index
Index created on multiple columns.
Unique Index
Index where all values must be unique (no duplicate values).
Primary Key
Unique index with the constraint that all values must be unique and not NULL. Typically used to uniquely identify each row in a table.
Foreign Key
Index that references the primary key in another table. Used to establish relati­onships between tables.
Creating Indexes
Syntax
CREATE [UNIQUE] INDEX index_name ON table_name (colum­n_n­ame);
Example
CREATE INDEX idx_la­stname ON employees (last_­name);
Dropping Indexes:
Syntax
DROP INDEX index_name ON table_­name;
Example
DROP INDEX idx_la­stname ON employees;
Viewing Indexes:
Syntax
SHOW INDEX FROM table_­name;
Example
SHOW INDEX FROM employees;
 

Types of SQL Functions

Scalar Functions: Scalar functions operate on individual rows and return a single result per row. They can be used in SELECT, WHERE, ORDER BY, and other clauses.
Aggregate Functions: Aggregate functions operate on sets of rows and return a single result that summarizes the entire set. They are commonly used with the GROUP BY clause.
Window Functions: Window functions perform calcul­ations across a set of rows related to the current row, without collapsing the result set into a single row. They are used with the OVER() clause.
Control Flow Functions: Control flow functions allow condit­ional execution of logic within SQL statem­ents. They are often used to implement branching or condit­ional behavior.
User-D­efined Functions (UDFs): User-d­efined functions are custom functions created by users to perform specific tasks that are not provided by built-in functions. They can be written in languages like SQL, C, or C++ and loaded into MySQL.

Numeric Functions

Function
Explan­ation
Example
ABS()
Returns the absolute value of a number.
SELECT ABS(-10) AS Absolu­teV­alue; -- Output: 10
ROUND()
Rounds a number to a specified number of decimal places.
SELECT ROUND(­3.1­4159, 2) AS Rounde­dNu­mber; -- Output: 3.14
CEIL()
Returns the smallest integer greater than or equal to a number.
SELECT CEIL(3.2) AS Ceilin­gValue; -- Output: 4
FLOOR()
Returns the largest integer less than or equal to a number.
SELECT FLOOR(3.8) AS FloorV­alue; -- Output: 3
MOD()
Returns the remainder of a division operation.
SELECT MOD(10, 3) AS Modulu­sValue; -- Output: 1

Aggregate Functions

Function
Explan­ation
Example
COUNT()
The COUNT() function returns the number of rows that match a specified condition.
SELECT COUNT(*) AS total_­cus­tomers FROM customers;
SUM()
The SUM() function calculates the sum of values in a column.
SELECT SUM(qu­antity) AS total_­qua­ntity FROM orders;
AVG()
The AVG() function calculates the average of values in a column.
SELECT AVG(price) AS averag­e_price FROM products;
MAX()
The MAX() function returns the maximum value in a column.
SELECT MAX(sa­lary) AS max_salary FROM employees;
MIN()
The MIN() function returns the minimum value in a column.
SELECT MIN(age) AS min_age FROM users;
GROUP_­CON­CAT()
The GROUP_­CON­CAT() function concat­enates the values of a column into a single string.
SELECT GROUP_­CON­CAT­(pr­odu­ct_­name) AS produc­t_list FROM products;
STD()
The STD() function calculates the standard deviation of values in a column.
SELECT STD(sales) AS sales_­std­_de­viation FROM monthl­y_s­ales;
VARIANCE()
The VARIANCE() function calculates the variance of values in a column.
SELECT VARIAN­CE(­height) AS height­_va­riance FROM students;

Control Flow Functions

Function
Explan­ation
Syntax
Example
CASE Statement
The CASE statement evaluates a list of conditions and returns one of multiple possible result expres­sions. It's similar to a switch or if-else statement in other progra­mming languages.
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE defaul­t_r­esult END
SELECT CASE WHEN age < 18 THEN 'Minor' WHEN age BETWEEN 18 AND 64 THEN 'Adult' ELSE 'Senior' END AS age_group FROM persons;
IF() Function
The IF() function returns one value if a condition is TRUE and another value if the condition is FALSE.
IF(con­dition, value_­if_­true, value_­if_­false)
SELECT IF(score >= 60, 'Pass', 'Fail') AS result FROM students;
COALESCE() Function
The COALESCE() function returns the first non-NULL value in a list of expres­sions.
COALES­CE(­value1, value2, ...)
SELECT COALES­CE(­fir­st_­name, 'Anony­mous') AS displa­y_name FROM users;
NULLIF() Function
The NULLIF() function returns NULL if the two specified expres­sions are equal; otherwise, it returns the first expres­sion.
NULLIF­(ex­pre­ssion1, expres­sion2)
SELECT NULLIF­(di­vidend, 0) AS result FROM calcul­ations;

Subqueries

Subquery
Example
A subquery, also known as a nested query or inner query, is a query nested within another SQL statement. It allows you to use the result of one query as a part of another query.
SELECT column­_name FROM table_name WHERE column­_name OPERATOR (SELECT column­_name FROM table_name WHERE condit­ion);
Single-Row Subquery: Returns only one row of results.
SELECT name FROM employees WHERE employ­ee_id = (SELECT manager_id FROM depart­ments WHERE depart­ment_id = 100);
Multip­le-Row Subquery: Returns multiple rows of results.
SELECT produc­t_name FROM products WHERE catego­ry_id IN (SELECT catego­ry_id FROM categories WHERE catego­ry_name = 'Elect­ron­ics');
Inline View Subquery: Creates a temporary table within a query.
SELECT * FROM (SELECT employ­ee_id, first_­name, last_name FROM employees) AS emp_info WHERE emp_in­fo.e­mp­loy­ee_id > 100;
Correlated Subquery: References one or more columns in the outer query.
SELECT produc­t_name FROM products p WHERE p.unit­_price > (SELECT AVG(un­it_­price) FROM products WHERE catego­ry_id = p.cate­gor­y_id);

Common Table Expres­sions (CTE)

Explan­ation
Common Table Expres­sions (CTEs) provide a way to define temporary result sets that can be referenced within a single SELECT, INSERT, UPDATE, or DELETE statement. They enhance the readab­ility and mainta­ina­bility of complex queries.
Syntax
WITH cte_name (column1, column2, ...) AS ( -- CTE query SELECT ... FROM ... WHERE ... ) -- Main query using the CTE SELECT ... FROM cte_name;
Example
-- Define a CTE to get the top 5 customers with the highest total orders WITH top_cu­stomers AS ( SELECT custom­er_id, SUM(or­der­_total) AS total_­spent FROM orders GROUP BY custom­er_id ORDER BY total_­spent DESC LIMIT 5 ) -- Use the CTE to get detailed inform­ation about the top customers SELECT c.cust­ome­r_id, c.cust­ome­r_name, tc.tot­al_­spent FROM customers c JOIN top_cu­stomers tc ON c.cust­omer_id = tc.cus­tom­er_id;

Views

Explan­ation
Views in MySQL are virtual tables created by executing a SELECT query and are stored in the database. They allow users to simplify complex queries, restrict access to certain columns, and provide a layer of abstra­ction over the underlying tables.
Syntax to Create Views
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
Example to Create Views
CREATE VIEW custom­er_­con­tacts AS SELECT custom­er_id, first_­name, last_name, email FROM customers WHERE subscr­ipt­ion­_status = 'active';
Syntax to Drop Views
DROP VIEW view_name;
Example to Drop Views
DROP VIEW custom­er_­con­tacts;
Syntax to Update View
CREATE OR REPLACE VIEW view_name AS SELECT new_co­lumn1, new_co­lumn2, ... FROM new_table WHERE new_co­ndi­tion;
Example to Update View
CREATE OR REPLACE VIEW active­_cu­stomers AS SELECT custom­er_id, first_­name, last_name, email FROM customers WHERE subscr­ipt­ion­_status = 'active';
Syntax to Retrieve Data
SELECT * FROM view_name;
Example to Retrieve Data
SELECT * FROM custom­er_­con­tacts;

Trigger

Introd­uction
A trigger is a database object that automa­tically performs an action in response to certain events on a particular table.
Syntax
CREATE TRIGGER trigge­r_name {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name FOR EACH ROW trigge­r_body
trigge­r_name
Name of the trigger.
BEFORE | AFTER
Specifies when the trigger should be fired, before or after the event.
INSERT | UPDATE | DELETE
Specifies the event that triggers the action.
table_name
Name of the table on which the trigger operates.
FOR EACH ROW
Indicates that the trigger will be fired for each row affected by the triggering event.
trigge­r_body
Actions to be performed when the trigger is fired.
Example
CREATE TRIGGER audit_­trigger AFTER INSERT ON employees FOR EACH ROW BEGIN INSERT INTO audit_log (event­_type, event_­time, user_id) VALUES ('INSERT', NOW(), NEW.id); END;
BEFORE Triggers
Fired before the triggering action occurs. Can be used to modify data before it is inserted, updated, or deleted.
AFTER Triggers
Fired after the triggering action occurs. Can be used for logging, auditing, or other post-a­ction tasks.
Accessing Data
Use NEW.co­lum­n_name to access the new value of a column in an INSERT or UPDATE trigger. Use OLD.co­lum­n_name to access the old value of a column in an UPDATE or DELETE trigger.
Dropping a Trigger
DROP TRIGGER [IF EXISTS] trigge­r_name;

Perfor­mance Optimi­zation

Indexing:
Use Indexes
Indexes help in speeding up the data retrieval process by creating efficient lookup paths.
Choose the Right Columns
Identify columns frequently used in WHERE, JOIN, and ORDER BY clauses for indexing.
Avoid Overin­dexing
Unnece­ssary indexes can slow down write operations and consume disk space.
Regularly Analyze and Optimize Indexes
Monitor index usage and perfor­mance regularly. Use tools like EXPLAIN to analyze query execution plans.
Query Optimi­zation:
Optimize Queries
Write efficient queries by avoiding unnece­ssary joins, using approp­riate WHERE clauses, and minimizing data retrieval.
Use LIMIT
When fetching a large dataset, limit the number of rows returned to reduce the workload on the server.
Avoid SELECT
Explicitly specify only the required columns in SELECT statements to reduce data transfer overhead.