DEV Community

Cover image for SQL cheat sheet/crash course
Arafat
Arafat

Posted on • Updated on

SQL cheat sheet/crash course

Here is a cheatsheet for SQL:

SELECT - used to retrieve data from a database

SELECT column1, column2, ...
FROM table_name
WHERE condition;
Enter fullscreen mode Exit fullscreen mode

INSERT INTO - used to insert new rows into a table

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
Enter fullscreen mode Exit fullscreen mode

UPDATE - used to modify existing rows in a table

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Enter fullscreen mode Exit fullscreen mode

DELETE FROM - used to delete existing rows from a table

DELETE FROM table_name
WHERE condition;
Enter fullscreen mode Exit fullscreen mode

CREATE TABLE - used to create a new table

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);
Enter fullscreen mode Exit fullscreen mode

ALTER TABLE - used to modify an existing table

ALTER TABLE table_name
ADD column datatype constraint;

ALTER TABLE table_name
DROP COLUMN column;
Enter fullscreen mode Exit fullscreen mode

TRUNCATE TABLE - used to delete all data from a table

TRUNCATE TABLE table_name;
Enter fullscreen mode Exit fullscreen mode

JOIN - used to combine rows from multiple tables

SELECT column1, column2, ...
FROM table1
JOIN table2
ON table1.column = table2.column;
Enter fullscreen mode Exit fullscreen mode

INNER JOIN - returns rows that have a match in both tables. An inner join is the default type of join if no specific join type is specified.

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
Enter fullscreen mode Exit fullscreen mode

LEFT JOIN - returns all rows from the left table, and the matching rows from the right table. Unmatched rows from the right table are returned as NULL.

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
Enter fullscreen mode Exit fullscreen mode

RIGHT JOIN - returns all rows from the right table, and the matching rows from the left table. Unmatched rows from the left table are returned as NULL.

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
Enter fullscreen mode Exit fullscreen mode

FULL JOIN - returns all rows from both tables, and returns NULL for any unmatched rows.

SELECT column1, column2, ...
FROM table1
FULL JOIN table2
ON table1.column = table2.column;
Enter fullscreen mode Exit fullscreen mode

CROSS JOIN - returns the Cartesian product of both tables. This means that every row in the first table is combined with every row in the second table.

SELECT column1, column2, ...
FROM table1
CROSS JOIN table2;
Enter fullscreen mode Exit fullscreen mode

INTERSECTION - returns only the rows that are common to both tables. This can be achieved using the INTERSECT operator.

SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;
Enter fullscreen mode Exit fullscreen mode

EXCEPT - returns the rows that are in the first table, but not in the second table. This can be achieved using the EXCEPT operator.

SELECT column1, column2, ...
FROM table1
EXCEPT
SELECT column1, column2, ...
FROM table2;
Enter fullscreen mode Exit fullscreen mode

UNION - used to combine the result sets of two or more SELECT statements

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
Enter fullscreen mode Exit fullscreen mode

GROUP BY - used to group together rows that have the same values in one or more columns

SELECT column1, SUM(column2)
FROM table_name
GROUP BY column1;
Enter fullscreen mode Exit fullscreen mode

HAVING - used to filter the results of a GROUP BY clause

SELECT column1, SUM(column2)
FROM table_name
GROUP BY column1
HAVING SUM(column2) > value;
Enter fullscreen mode Exit fullscreen mode

LIMIT - used to limit the number of rows returned in a SELECT statement

SELECT column1, column2, ...
FROM table_name
LIMIT number;
Enter fullscreen mode Exit fullscreen mode

ORDER BY - used to sort the result set of a SELECT statement

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 ASC/DESC;
Enter fullscreen mode Exit fullscreen mode

BETWEEN - used to select values within a specific range

SELECT column1, column2, ...
FROM table_name
WHERE column1 BETWEEN value1 AND value2;
Enter fullscreen mode Exit fullscreen mode

LIKE - used to search for a specific pattern in a column

SELECT column1, column2, ...
FROM table_name
WHERE column1 LIKE pattern;
Enter fullscreen mode Exit fullscreen mode

IN - used to specify a list of values to compare against

SELECT column1, column2, ...
FROM table_name
WHERE column1 IN (value1, value2, ...);
Enter fullscreen mode Exit fullscreen mode

IS NULL - used to test for NULL values

SELECT column1, column2, ...
FROM table_name
WHERE column1 IS NULL;
Enter fullscreen mode Exit fullscreen mode

EXISTS - used to test for the existence of rows in a subquery

SELECT column1, column2, ...
FROM table_name
WHERE EXISTS (SELECT * FROM table_name WHERE condition);
Enter fullscreen mode Exit fullscreen mode

COUNT - used to return the number of rows in a SELECT statement

SELECT COUNT(*)
FROM table_name;
Enter fullscreen mode Exit fullscreen mode

SUM - used to return the sum of the values in a specific column

SELECT SUM(column)
FROM table_name;
Enter fullscreen mode Exit fullscreen mode

AVG - used to return the average value of a specific column

SELECT AVG(column)
FROM table_name;
Enter fullscreen mode Exit fullscreen mode

MIN - used to return the minimum value in a specific column

SELECT MIN(column)
FROM table_name;
Enter fullscreen mode Exit fullscreen mode

MAX - used to return the maximum value in a specific column

SELECT MAX(column)
FROM table_name;
Enter fullscreen mode Exit fullscreen mode

DISTINCT - used to return unique values in the output

SELECT DISTINCT column1, column2, ...
FROM table_name;
Enter fullscreen mode Exit fullscreen mode

AS - used to specify an alias for a column or table

SELECT column1 AS 'alias'
FROM table_name;

SELECT t1.column1, t2.column2
FROM table1 AS t1
JOIN table2 AS t2
ON t1.column = t2.column;
Enter fullscreen mode Exit fullscreen mode

CASE - used to create conditional statements within a SELECT statement

SELECT column1,
    (CASE
        WHEN column2 > value THEN 'A'
        WHEN column2 = value THEN 'B'
        ELSE 'C'
    END) AS 'alias'
FROM table_name;
Enter fullscreen mode Exit fullscreen mode

COALESCE - used to return the first non-NULL value in a list of values

SELECT COALESCE(column1, column2, ...)
FROM table_name;
Enter fullscreen mode Exit fullscreen mode

NULLIF - used to return NULL if two expressions are equal

SELECT NULLIF(expression1, expression2)
FROM table_name;
Enter fullscreen mode Exit fullscreen mode

IFNULL - used to return a specified value if an expression is NULL

SELECT IFNULL(expression, value)
FROM table_name;
Enter fullscreen mode Exit fullscreen mode

DATE_ADD - used to add a specified time interval to a date

SELECT DATE_ADD(date, INTERVAL value unit)
FROM table_name;
Enter fullscreen mode Exit fullscreen mode

DATE_SUB - used to subtract a specified time interval from a date

SELECT DATE_SUB(date, INTERVAL value unit)
FROM table_name;
Enter fullscreen mode Exit fullscreen mode

DATE_FORMAT - used to format a date in a specific way

SELECT DATE_FORMAT(date, 'format')
FROM table_name;
Enter fullscreen mode Exit fullscreen mode

CONCAT - used to concatenate two or more strings

SELECT CONCAT(string1, string2, ...)
FROM table_name;
Enter fullscreen mode Exit fullscreen mode

Top comments (0)