Here is a cheatsheet for SQL:
SELECT
- used to retrieve data from a database
SELECT column1, column2, ...
FROM table_name
WHERE condition;
INSERT INTO
- used to insert new rows into a table
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
UPDATE
- used to modify existing rows in a table
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
DELETE FROM
- used to delete existing rows from a table
DELETE FROM table_name
WHERE condition;
CREATE TABLE
- used to create a new table
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
...
);
ALTER TABLE
- used to modify an existing table
ALTER TABLE table_name
ADD column datatype constraint;
ALTER TABLE table_name
DROP COLUMN column;
TRUNCATE TABLE
- used to delete all data from a table
TRUNCATE TABLE table_name;
JOIN
- used to combine rows from multiple tables
SELECT column1, column2, ...
FROM table1
JOIN table2
ON table1.column = table2.column;
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;
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;
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;
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;
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;
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;
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;
UNION
- used to combine the result sets of two or more SELECT statements
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
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;
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;
LIMIT
- used to limit the number of rows returned in a SELECT statement
SELECT column1, column2, ...
FROM table_name
LIMIT number;
ORDER BY
- used to sort the result set of a SELECT statement
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 ASC/DESC;
BETWEEN
- used to select values within a specific range
SELECT column1, column2, ...
FROM table_name
WHERE column1 BETWEEN value1 AND value2;
LIKE
- used to search for a specific pattern in a column
SELECT column1, column2, ...
FROM table_name
WHERE column1 LIKE pattern;
IN
- used to specify a list of values to compare against
SELECT column1, column2, ...
FROM table_name
WHERE column1 IN (value1, value2, ...);
IS NULL
- used to test for NULL values
SELECT column1, column2, ...
FROM table_name
WHERE column1 IS NULL;
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);
COUNT
- used to return the number of rows in a SELECT statement
SELECT COUNT(*)
FROM table_name;
SUM
- used to return the sum of the values in a specific column
SELECT SUM(column)
FROM table_name;
AVG
- used to return the average value of a specific column
SELECT AVG(column)
FROM table_name;
MIN
- used to return the minimum value in a specific column
SELECT MIN(column)
FROM table_name;
MAX
- used to return the maximum value in a specific column
SELECT MAX(column)
FROM table_name;
DISTINCT
- used to return unique values in the output
SELECT DISTINCT column1, column2, ...
FROM table_name;
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;
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;
COALESCE
- used to return the first non-NULL value in a list of values
SELECT COALESCE(column1, column2, ...)
FROM table_name;
NULLIF
- used to return NULL if two expressions are equal
SELECT NULLIF(expression1, expression2)
FROM table_name;
IFNULL
- used to return a specified value if an expression is NULL
SELECT IFNULL(expression, value)
FROM table_name;
DATE_ADD
- used to add a specified time interval to a date
SELECT DATE_ADD(date, INTERVAL value unit)
FROM table_name;
DATE_SUB
- used to subtract a specified time interval from a date
SELECT DATE_SUB(date, INTERVAL value unit)
FROM table_name;
DATE_FORMAT
- used to format a date in a specific way
SELECT DATE_FORMAT(date, 'format')
FROM table_name;
CONCAT
- used to concatenate two or more strings
SELECT CONCAT(string1, string2, ...)
FROM table_name;
Top comments (0)