The Only SQL Cheat Sheet You’ll Ever Need

The Only SQL Cheat Sheet You’ll Ever Need for Everyday Tasks

When it comes to SQL, it doesn’t matter if you’re a student or system administrator; The SQL documentation is huge, and when you’re working on a time crunch you want to find that one key command quickly.

Fear not; we’ve got you covered. Whether you need a refresher on SQL commands or are new to SQL and its various flavors, this SQL query cheat sheet is for you. Any IT professional would do well to keep this SQL language cheat sheet within reach.

We have endeavored to use consistent SQL syntax across different implementations (mainly MySQL and PostgreSQL), and we’ll point out discrepancies between other SQL implementations to you wherever appropriate.

Download this SQL cheat sheet here. Without further ado, let’s dive in.

SQL Cheat Sheet Search

Search our SQL cheat sheet to find the right cheat for the term you're looking for. Simply enter the term in the search bar and you'll receive the matching cheats available.

What Is SQL?

SQL is short for Structured Query Language. Its chief function is managing structured data on a relational database management system (RDBMS), usually arranged in tables. SQL is case-insensitive, but it’s common to capitalize SQL keywords such as SELECT and FROM.

Suppose you want to execute multiple SQL statements in the same server call. In that case, some database administration tools, such as MySQL Workbench, require a semicolon (;) at the end of each SQL statement to separate them.

What Is SQL
Screenshot of MySQL Workbench in action

SQL Command Generator

Say goodbye to the hassle of trying to remember the exact syntax for your SQL commands! With our SQL Command Generator, you can simply say what you need SQL to do, and we will generate the command for you.

Basic SQL Syntax

This section is the essential SQL syntax cheat sheet. If you’re short on time, read this section first.

CommandSyntaxDescription
ALTER TABLEALTER TABLE table_name ADD column_name datatype;Add columns of a specified datatype to a table in a database
ASSELECT column_name AS 'Alias' FROM table_name;A keyword in SQL to rename a column or table using an alias name
CASESELECT column_name, CASE WHEN condition THEN 'Result_1' WHEN condition THEN 'Result_2' ELSE 'Result_3' END FROM table_name;Create different outputs inside a SELECT statement
CREATE TABLECREATE TABLE table_name (column_1 datatype, column_2 datatype, column_3 datatype);Create a new table in a database and specify the name of the table and columns of a specified datatype inside it
DELETEDELETE FROM table_name WHERE some_column = some_value;Remove the rows from a table
HAVINGSELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > value;Use it like the WHERE keyword in aggregating functions such as GROUP BY
INSERTINSERT INTO table_name (column_1, column_2, column_3) VALUES (value_1, 'value_2', value_3);Add new rows to a table with specified values
SELECTSELECT column_name FROM table_name;Fetch data from a database; the column_name can be a function applied to an existing column
SELECT DISTINCTSELECT DISTINCT column_name FROM table_name;Return unique, non-repeating values in specified columns
UPDATEUPDATE table_name SET some_column = some_value WHERE some_column = some_value;Edit rows in a table
WITHWITH temporary_name AS (SELECT * FROM table_name) SELECT * FROM temporary_name WHERE column_name operator value;Process the result of a query (SELECT * FROM table_name) stored in a temporary table referenced by the alias temporary_name
/* */ --/* multi-line comment explaining the code */ --single-line commentEnclose comments:
• For comments spanning several lines: /* */
• For comments on the same line as the command: --

Data Types in SQL

The data type of a SQL column identifies how SQL will interact with the stored data. SQL is a strongly typed language, so it’s important to tell apart various data types.

Strongly Typed Languages vs Weakly Typed Languages

In computer programming, a programming language is strongly typed if it demands the specification of data types.

In strongly typed languages, once a type is assigned to a variable at runtime or compile time, it retains that type and can’t be intermingled in expressions with other types easily. You cannot assign an integer to a string variable in a strongly typed language. Boolean variables can only hold Boolean values, and writing any other value to it may throw errors.

In weakly typed languages, once a type is assigned to a variable at runtime or compile time, it can be intermingled in expressions with other types easily. Here, an integer assigned to a string variable may get converted into the character(s) representing the integer. You can also assign a string or integer to a variable previously used to hold a Boolean value.

The same name may map to different data types in other SQL implementations. Therefore, always consult the relevant documentation (MySQL, PostgreSQL).

MySQL Data Types (Version 8.0)

MySQL has three main data types: string, numeric, and date and time:

String

Data typeDescription
CHAR(size)A fixed-length string: can contain letters, numbers, and special characters. The size parameter specifies the column length in characters, from 0 to 255. The default is 1.
VARCHAR(size)A variable-length string: can contain letters, numbers, and special characters. The size parameter specifies the maximum string length in characters, from 0 to 65535.
BINARY(size)Equal to CHAR() but stores binary byte strings. The size parameter specifies the column length in bytes. The default is 1.
VARBINARY(size)Equal to VARCHAR() but stores binary byte strings. The size parameter specifies the maximum column length in bytes.
TINYBLOBFor BLOBs (Binary Large Objects). Max length: 255 bytes.
TINYTEXTHold a string with a maximum length of 255 characters.
TEXT(size)Hold a string with a maximum length of 65,535 bytes.
BLOB(size)For BLOBs (Binary Large Objects). Hold up to 65,535 bytes of data.
MEDIUMTEXTHold a string with a maximum length of 16,777,215 characters.
MEDIUMBLOBFor BLOBs (Binary Large Objects). Hold up to 16,777,215 bytes of data.
LONGTEXTHold a string with a maximum length of 4,294,967,295 characters.
LONGBLOBFor BLOBs (Binary Large Objects). Hold up to 4,294,967,295 bytes of data.
ENUM(val1, val2, val3, ...)A string object that can have only one value, chosen from a list of possible values. You can list up to 65535 values in an ENUM list. If you insert a value that is not in the list, you insert a blank value. SQL sorts the values in the order you enter them.
SET(val1, val2, val3, ...)A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list.

Numeric

We leave the “Alias” field blank if a data type has no alias.

Data typeAliasDescription
BIT(size)A bit-value type. The size parameter specifies the number of bits per value and can hold a value from 1 to 64. The default value for size is 1.
TINYINT(size)A tiny integer. The signed is from -128 to 127. The unsigned range is from 0 to 255. The size parameter specifies the maximum display width (which is 255).
BOOLEANBOOLZero = false, nonzero values = true.
SMALLINT(size)A small integer. The signed range is from -32768 to 32767. The unsigned range is from 0 to 65535. The size parameter specifies the maximum display width (which is 255).
MEDIUMINT(size)A medium-sized integer. The signed range is from -8388608 to 8388607. The unsigned range is from 0 to 16777215. The size parameter specifies the maximum display width (which is 255).
INTEGER(size)INT(size)A medium-sized integer. The signed range is from -2147483648 to 2147483647. The unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255).
BIGINT(size)A large integer. The signed range is from -9223372036854775808 to 9223372036854775807. The unsigned range is from 0 to 18446744073709551615. The size parameter specifies the maximum display width (which is 255).
FLOAT(size, d),DOUBLE(size, d),DOUBLE PRECISION(size, d)Floating point number. The parameter size specifies the total number of digits. The d parameter sets the number of digits.
Future MySQL versions (beyond MySQL 8.0.17) will remove this syntax.
FLOAT(p)Floating point number. MySQL uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE().
DECIMAL(size, d)DEC(size, d)Fixed-point number. The parameter size specifies the total number of digits. The d parameter sets the number of digits after the decimal point. The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0.

Note: All the numeric data types may have an extra option: UNSIGNED or ZEROFILL. If you add the UNSIGNED option, MySQL disallows negative values for the column. If you add the ZEROFILL option, MySQL automatically adds the UNSIGNED attribute to the column.

Date and time

Adding DEFAULT and ON UPDATE in the column definition helps you get automatic initialization and updating to the current date and time.

Below, the fsp (fractional seconds precision, in microseconds) value must be 0–6. For example, set fsp to 1 to encapsulate 0.1–0.9 seconds and 2 for 0.01–0.99 seconds. A value of 0 indicates the absence of a fractional part. If omitted, the default precision is 0.

Data typeDescription
DATEDate. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31'
DATETIME(fsp)Date and time combination. Format: YYYY-MM-DD hh:mm:ss.
TIMESTAMP(fsp)Timestamp. MySQL stores TIMESTAMP values as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss.
TIME(fsp)Time. Format: hh:mm:ss.
YEARYear in four-digit format. MySQL 8.0 does not support a two-digit year format.

PostGreSQL Data Types (Version 15)

We leave the “Aliases” field blank if a data type has no alias.

Data typeAliasesDescription
BIGINTINT8Signed eight-byte integer
BIGSERIALSERIAL8Auto-incrementing eight-byte integer
BIT [ (n) ]Fixed-length bit string
BIT VARYING [ (n) ]VARBIT [ (n) ]Variable-length bit string
BOOLEANBOOLLogical Boolean (true/false)
BOXRectangular box on a plane
BYTEABinary data (“byte array”)
CHARACTER [ (n) ]CHAR [ (n) ]Fixed-length character string
CHARACTER VARYING [ (n) ]VARCHAR [ (n) ]Variable-length character string
CIDRIPv4 or IPv6 network address
CIRCLECircle on a plane
DATECalendar date (year, month, day)
DOUBLE PRECISIONFLOAT8Double precision floating-point number (eight bytes)
INETIPv4 or IPv6 host address
INTEGERINT, INT4Signed four-byte integer
INTERVAL [ fields ] [ (p) ]Time span
JSONTextual JSON data
JSONBBinary JSON data, decomposed
LINEInfinite line on a plane
LSEGLine segment on a plane
MACADDRMAC (Media Access Control) address
MACADDR8MAC (Media Access Control) address (EUI-64 format)
MONEYCurrency amount
NUMERIC [ (p, s) ]DECIMAL [ (p, s) ]Exact numeric of selectable precision
PATHGeometric path on a plane
PG_LSNPostgreSQL Log Sequence Number
PG_SNAPSHOTUser-level transaction ID snapshot
POINTGeometric point on a plane
POLYGONClosed geometric path on a plane
REALFLOAT4Single precision floating-point number (four bytes)
SMALLINTINT2Signed two-byte integer
SMALLSERIALSERIAL2Auto-incrementing two-byte integer
SERIALSERIAL4Auto-incrementing four-byte integer
TEXTVariable-length character string
TIME [ (p) ] [ without time zone ]Time of day (no time zone)
TIME [ (p) ] WITH TIME ZONETIMETZTime of day, including time zone
TIMESTAMP [ (p) ] [ without time zone ]Date and time (no time zone)
TIMESTAMP [ (p) ] WITH TIME ZONETIMESTAMPTZDate and time, including time zone
TSQUERY Text search query
TSVECTOR Text search document
UUID Universally unique identifier
XML XML data

SQL Operators

This subsection is a basic SQL operators cheat sheet, where you learn to create complex Boolean expressions in SQL queries.

CommandSyntaxDescription
ANDSELECT column_name(s) FROM table_name WHERE column_1 = value_1 AND column_2 = value_2;Combine two conditions
BETWEENSELECT column_name(s) FROM table_name WHERE column_name BETWEEN value_1 AND value_2;Filter the result within a certain range
IS NULLSELECT column_name(s) FROM table_name WHERE column_name IS NULL;Check for empty values in conjunction with the WHERE clause
IS NOT NULLSELECT column_name(s) FROM table_name WHERE column_name IS NOT NULL;Check for the absence of empty values in conjunction with the WHERE clause
LIKESELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;Search for a specific pattern in a column in conjunction with the WHERE clause
ORSELECT column_name FROM table_name WHERE column_name = value_1 OR column_name = value_2;Filter the result set to contain only the rows where either condition is TRUE
UNIONSELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;Combine the results of two or more SELECT statements and select only distinct values
UNION ALLSELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;Combine the results of two or more SELECT statements, allowing duplicate values

SQL Functions

SQL functions help you compute and analyze the contents of database tables.

Here are some common SQL functions:

CommandSyntaxDescription
AVG()SELECT AVG(column_name) FROM table_name;Aggregate a numeric column and return its arithmetic mean, ignoring NULL values
CASE()CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END;The CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement).
Once a condition is true, it will stop reading and return the result. If no conditions are TRUE, it returns the value in the ELSE clause.
Without an ELSE part and with all conditions FALSE, it returns NULL.
CAST()SELECT CAST(value AS datatype);Convert a value (of any type) into the specified datatype.
CHAR_LENGTH()SELECT CHAR_LENGTH(string) AS LengthOfString;(MySQL) Return the length of a string in characters
COALESCE()SELECT COALESCE([list of values including NULL separated by commas]);Return the first non-null value in a list
COUNT()SELECT COUNT(column_name) FROM table_name;Take the name of a column as an argument and count the number of rows when the column is not NULL
FIRST()SELECT FIRST(column_name) FROM table_name;Return the first value of the selected column
LAST()SELECT LAST(column_name) FROM table_name;Return the last value of the selected column
LCASE()SELECT LCASE(column_name) FROM table_name;Convert string values in the selected column to lowercase
LEN()SELECT LEN(string);(SQL Server) Return the length of a string
MAX()SELECT MAX(column_name) FROM table_name;Take at least one column as an argument and return the largest value among them
MIN()SELECT MIN(column_name) FROM table_name;Take at least one column as an argument and return the smallest value among them
NULLIF()SELECT NULLIF(expr1, expr2);Return NULL if two expressions expr1, expr2 are equal. Otherwise, it returns the first expression.
ROUND()SELECT ROUND(column_name, integer) FROM table_name;Take the column name and an integer as an argument, and round the values in a column to the number of decimal places specified by an integer
SUBSTRING()SELECT SUBSTRING(string, start, length) AS ExtractString;Extract some characters from a string, where start is the starting position (one-indexed) and length is the number of characters to extract.
Aliases: MID(), SUBSTR()
SUM()SELECT SUM(column_name) FROM table_name;Return the sum of values from a particular column
UCASE()SELECT UCASE(column_name) FROM table_name;Convert string values in the selected column to uppercase
VAR()SELECT VAR(column_name) FROM table_name;Return the statistical variance

SQL Clauses

A SQL clause presents the results of a SQL query in a way you specify.

CommandSyntaxDescription
LIMITSELECT column_name(s) FROM table_name LIMIT number;Specify the maximum number of rows the result set must have. Some SQL implementations have SELECT TOP playing a similar role.
GROUP BYSELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;Used for aggregate functions in collaboration with the SELECT statement
ORDER BYSELECT column_name FROM table_name ORDER BY column_name ASC | DESC;Sort the result set by a particular column either numerically or alphabetically.
ASC means “in ascending order;” DESC, “descending.”
WHERESELECT column_name(s) FROM table_name WHERE column_name operator value;Filter the result set to include the rows where the condition is TRUE

SQL Joins

Combining two tables in SQL is easy:

SQL Joins
CommandSyntaxDescription
INNER JOINSELECT column_name(s) FROM table_1 JOIN table_2 ON table_1.column_name = table_2.column_name;Select records that have matching values in both tables
LEFT JOINSELECT column_name(s) FROM table_1 LEFT JOIN table_2 ON table_1.column_name = table_2.column_name;Combine all records from the left side and any matching rows from the right table.
LEFT OUTER JOIN and LEFT JOIN are the same.
RIGHT JOINSELECT column_name(s) FROM table_1 RIGHT JOIN table_2 ON table_1.column_name = table_2.column_name;Combine all rows from the right side and any matching rows from the left table.
RIGHT OUTER JOIN and RIGHT JOIN are the same.
FULL JOINSELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;Return all records whether the records in the left (table1) and right (table2) tables match.
FULL OUTER JOIN and FULL JOIN are the same.
CROSS JOINSELECT * FROM table1 CROSS JOIN table2Combine each row of the first table (table1) with each row of the second table (table2).

SQL Views

In SQL, a view is a virtual table based on the results of an SQL query. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL statements and functions to a view and present the data as if the data were coming from a single table.

Here are the most important functions for manipulating SQL views:

CommandSyntaxDescription
CREATE VIEWCREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;Create a view from the SQL query beginning with SELECT
CREATE OR REPLACE VIEWCREATE OR REPLACE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;Update a view created from the SQL query beginning with SELECT
DROP VIEWDROP VIEW view_name;Delete a view

SQL Indexes

Indexes are for speeding up data retrieval from a database. The users cannot see the indexes. Updating a table with indexes takes longer than updating a table without (because the indexes also need an update). So, only create indexes on the columns against which users frequently search.

CommandSyntaxDescription
CREATE INDEXCREATE INDEX index_name ON table_name (column1, column2, ...);Creates a unique index on a table, allowing duplicate values
CREATE UNIQUE INDEXCREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);Create a unique index on a table, forbidding duplicate values
DROP INDEX/* MS Access */DROP INDEX index_name ON table_name;
/* SQL Server */DROP INDEX table_name.index_name;/* DB2/Oracle */DROP INDEX index_name;
/* MySQL */ALTER TABLE table_name DROP INDEX index_name;
Delete an index in a table

SQL Constraints

Constraints are for specifying rules for data in a table. Use them with the CREATE TABLE statement for a new table or the ALTER TABLE statement for an existing table.

The syntax is:

[CREATE|ALTER] TABLE table_name (

column1 datatype constraint,

column2 datatype constraint,

column3 datatype constraint,

...

);

The table below lists common constraints in SQL:

CommandDescription
NOT NULLEnsure that a column cannot have a NULL value
UNIQUEEnsure that all values in a column are different 
PRIMARY KEYA combination of NOT NULL and UNIQUE: uniquely identifies each row in a table.
FOREIGN KEYPrevent actions that would destroy links between tables
CHECKEnsure that the values in a column satisfy a specific condition 
DEFAULTSet a default value for a column that contains no specified value
AUTO_INCREMENTAllow the automatic generation of a unique number when inserting a new record into a table.

SQL Transactions

A transaction is the propagation of one or more changes to the database. For example, you perform a transaction if you perform create, update, and delete operations on a table.

Below we list the top SQL transactional commands:

CommandSyntaxDescription
COMMITCOMMIT;Save changes invoked by a transaction to the database
ROLLBACKROLLBACK;
/* Roll back a given SAVEPOINT */ROLLBACK TO SAVEPOINT_NAME;
Undo transactions not yet saved to the database
SAVEPOINTSAVEPOINT SAVEPOINT_NAME;
/* remove a SAVEPOINT that you have created */RELEASE SAVEPOINT SAVEPOINT_NAME;
Roll the transaction back to a certain point without rolling back the entire transaction
SET TRANSACTIONSET TRANSACTION [ READ WRITE | READ ONLY ];Initiate a database transaction, and specify characteristics for the transaction that follows.
For example, you can specify a transaction to be READ ONLY or READ WRITE.

SQL Performance Tuning Tips

As this article is a SQL basics cheat sheet, we present the following SQL performance optimization tips without elaboration.

  • Add missing indexes and check for unused indexes
  • Use SELECT fields instead of SELECT *
  • Avoid SELECT DISTINCT
  • Avoid using multiple OR in the FILTER predicate
  • Create joins with INNER JOIN (not WHERE)
  • Avoid too many JOINs
  • Use WHERE instead of HAVING to define filters
  • Use wildcards at the end of a phrase only
  • Use TOP and LIMIT to sample query results
  • Minimize the usage of query hints
  • Minimize large write operations
  • Run the query during off-peak hours and analyze wait statistics

Conclusion

This SQL command cheat sheet covers most SQL database tasks. We hope it has helped you solve your problems at hand. Bookmark the documentation links for your SQL implementation, such as MySQL or PostgreSQL. Remember to check out our articles on SQL and our beginner-friendly cyber security courses, which cover SQL injection attacks:

Frequently Asked Questions

Level Up in Cyber Security: Join Our Membership Today!

vip cta image
vip cta details
  • Cassandra Lee

    Cassandra is a writer, artist, musician, and technologist who makes connections across disciplines: cyber security, writing/journalism, art/design, music, mathematics, technology, education, psychology, and more. She's been a vocal advocate for girls and women in STEM since the 2010s, having written for Huffington Post, International Mathematical Olympiad 2016, and Ada Lovelace Day, and she's honored to join StationX. You can find Cassandra on LinkedIn and Linktree.

>