Programming

The Essential SQL Commands Cheat Sheet for Beginners

Joe Coburn 29-02-2020

Structured Query Language (SQL) is the tool of choice for manipulating databases. This language lets you manipulate data like no other, and it costs nothing to start using!

Advertisement

If you’re new to SQL commands or need a reminder of some parts of this powerful tool at your disposal, then our SQL commands cheat sheet below is exactly what you need. Scroll down to see the awesomeness it contains, or download the PDF for future use. (The PDF contains examples for the given commands.)

FREE DOWNLOAD: This cheat sheet is available as a downloadable PDF from our distribution partner, TradePub. You will have to complete a short form to access it for the first time only. Download The Essential SQL Commands Cheat Sheet.

The Essential SQL Commands Cheat Sheet

CommandAction
Query Commands
SELECTBasic query building block to retrieve data.
SELECT *Using * with SELECT returns all columns.
SELECT columnSpecify exact columns with their name.
SELECT table.columnReference a column from a specific table.
FROMSpecify where to find data.
ASTemporarily alias a table name or column to a new name.
WHEREFilter results with a condition.
ANDUse multiple conditions with a WHERE clause. Results must match all conditions.
ORUse multiple conditions with a WHERE clause. Results only need to match one condition.
ORDER BYOrder the results by a column. The database chooses how to order.
ORDER BY column ASCOrder the results by a column in ascending order.
ORDER BY column DESCOrder the results by a column in descending order.
LIMITRestrict the number of results returned.
OFFSETSkip the first OFFSET number of rows. Often used with LIMIT.
SUBQUERYRun a query to retrieve data for another query.
Aggregate Functions¹
COUNTCount the number of rows that match the query.
MAXReturn the highest value in a numeric column.
MINReturn the lowest value in a numeric column.
SUMSum the values of a numeric column.
AVGCalculate the average value for a numeric column.
HAVINGUsed with aggregate functions instead of the WHERE clause.
GROUP BYUsed to refine an aggregate result.
Operators
LIKECase-sensitive search for a pattern with a wildcard operator (%).
ILIKECase-insensitive search for a pattern with a wildcard operator (%).
BETWEENSearch for a value between two values. Works with dates or numbers.
>Search for values greater than a condition.
>=Search for values greater or equal to a condition.
Search for values less than a condition.
Search for values less than or equal to a condition.
=Search for values matching a condition exactly.
Search for values not equal to a condition.
UNIONCombine two unique queries (with the same columns) into one result.
UNION ALLCombine two queries (with the same columns) into one result. Duplicates allowed.
INShorthand for WHERE. Specifies multiple OR conditions.
NOT INShorthand for WHERE. Specifies multiple OR conditions (inverted) or not equal to.
IS NULLCheck for empty values.
IS NOT NULLCheck for no empty values.
INTERSECTReturn results which match two queries.
MINUSReturn results in one query which are not in another query.¹
Joins
ONUsed to specify the column to compare and match results.
USINGShorthand for ON, used when the column name is the same in both tables.
LEFT OUTER JOINAll the results from the left table, with only the matching results from the right table.
LEFT OUTER JOIN (WITH NULL)(With null) All the results from the left table but not in the right table.
INNER JOINAll the results that match in both the left and right tables.
FULL OUTER JOINAll the results from both the left and right tables.
FULL OUTER JOIN (WITH NULL)(With null) all the results from both the left and right tables excluding results in both tables.
RIGHT OUTER JOINAll the results from the right table, with only the matching results from the left table.
RIGHT OUTER JOIN (WITH NULL)(With null) All the results from the right table but not in the left table.
Creating and Editing Tables
CREATE TABLECreate a new table.
NULLAllow empty values for this field.
NOT NULLDon't allow empty values for this field.
DEFAULTA value to populate the field with if one is not supplied.
ASCreate a new table based on the structure of an existing table. The new table will contain the data from the old table.
ALTER TABLE (ADD COLUMN)Add a new column to an existing table.
ALTER TABLE (DROP COLUMN)Remove a column from an existing table.
ALTER TABLE (ALTER COLUMN)Change the datatype of an existing column.
ALTER TABLE (RENAME COLUMN)Rename an existing column.
ALTER TABLE (RENAME TABLE)Rename an existing table.
ALTER TABLE (MODIFY NULL)Allow null values for a column.
ALTER TABLE (MODIFY NOT NULL)Prevent null values for a column.
DROP TABLEDelete a table and all its data.
TRUNCATE TABLEDelete all the data in a table, but not the table itself.
Constraints
PRIMARY KEYA value that uniquely identifies a record in a table. A combination of NOT NULL and UNIQUE.
FOREIGN KEYReferences a unique value in another table. Often a primary key in the other table.
UNIQUEEnforce unique values for this column per table.
CHECKEnsure values meet a specific condition.
INDEX (CREATE)Optimise tables and greatly speed up queries by adding an index to a column.
INDEX (CREATE UNIQUE)Create an index that does not allow duplicate values.
INDEX (DROP)Remove an index.
Creating and Editing Data
INSERT (SINGLE VALUE)Add a new record to a table.
INSERT (MULTIPLE VALUES)Add several new records to a table.
INSERT (SELECT)Add records to a table, but get the values from an existing table.
UPDATE (ALL)Modify all existing records in a table.
UPDATE (WHERE)Modify existing records in a table which match a condition.
DELETE (ALL)Remove all records from a table.
DELETE (WHERE)Remove records from a table which match a condition.
Creating and Editing Triggers¹
CREATE TRIGGERCreate a trigger.
CREATE TRIGGER (OR MODIFY)Create a trigger, or update an existing trigger if one is found with the same name.
WHEN (BEFORE)Run the trigger before the event happens.
WHEN (AFTER)Run the trigger after the event happens.
EVENT (INSERT)Run the trigger before or after an insert happens.
EVENT (UPDATE)Run the trigger before or after an update happens.
EVENT (DELETE)Run the trigger before or after a delete happens.
ONWhich table to target with this trigger.
TRIGGER_TYPE (FOR EACH ROW)Execute the trigger for every row changed.
TRIGGER_TYPE (FOR EACH STATEMENT)Execute the trigger once per SQL statement, regardless of how many rows are altered.
EXECUTEKeyword to indicate the end of the main trigger definition.
DROP TRIGGERDelete a trigger.
Creating and Editing Views
CREATE VIEWCreate a new view.
ASDefine where to retrieve the data for a view.
WITH CASCADED CHECK OPTIONEnsure any data modified through a view meets the rules defined by the rule. Apply this to any other views.
WITH LOCAL CHECK OPTIONEnsure any data modified through a view meets the rules defined by the rule. Ignore this for any other views.
CREATE RECURSIVE VIEWCreate a recursive view (one that refers to a recursive common table expression).
CREATE TEMPORARY VIEWCreate a view that exists for the current session only.
DROP VIEWDelete a view.
Common Table Expressions (CTEs)¹
WITHCreate a new common table expression.
ASSpecify the data to use in the CTE.
, (COMMA)Chain multiple CTEs.
¹Database engine implementations and support often vary.

SQL: The Most Powerful Language Around?

Keep in mind that SQL dialects vary between database engines. It’s a bit like the difference between HD DVD and Blu-ray (or VHS and Betamax). SQL is similar between databases, but the occasional complex command may not work exactly the same across all implementations. Most of the SQL commands in this cheat sheet will work across any database. The complex commands where database support varies are noted as such.

Once you know SQL, you can apply it to a variety of uses. Whether you’re dealing with a management system, programming project, or basic reporting, SQL unlocks the raw power contained in a database. Don’t forget to read our basic programmer’s guide to SQL 13 Most Important SQL Commands Any Programmer Should Know Every big or dynamic website uses a database in some way, and when combined with Structured Query Language (SQL), the possibilities for manipulating data really are endless. Read More , for an in-depth look at how to use these commands.

Advertisement

Related topics: Cheat Sheet, SQL.

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

Advertisement
Whatsapp Pinterest

Leave a Reply

Your email address will not be published. Required fields are marked *