Show Menu
Cheatography

SQL commands Cheat Sheet by

FYI I do not claim ownership over this information!!!! Portions have been copied directly from publicly available web sources.

Termin­ology - Basic Manipu­lation

SQL
A progra­mming language designed to manipulate & manage data stored in relational databases
relat­ional database
A database that organizes inform­ation into one or more tables.
table
A collection of data organized into rows & columns.
state­ment
A string of characters that the database recognizes as a valid command.
primary key
Column in table that is unique to each row w/ no NULL values.
foreign key
Primary key of table1 that appears in table2.

Commands - Basic Manipu­lation

SHOW DATABASES
list all available databases
USE database
use specified database
SHOW TABLES [FROM database]
list tables in database
DESCRIBE table
list column headers in table
SHOW FIELDS FROM table
list all fields
SHOW COLUMNS FROM table
list all columns (fields) + column type etc
SHOW COLUMNS FROM table
list all columns (fields) + column type etc
SHOW INDEX FROM table
list all indexes from table

Termin­ology - queries

opera­tors
Operators create a condition that can be evaluated as either true or false.

Commands - operators

=
equal to
!=
not equal to
>
greater than
<
less than
>=
greater than or equal to
<=
less than or equal to
IS NULL
is null
IS NOT NULL
is not null

Wildcards

*
Matches any number or type of charac­ter(s).
_
Matches any individual character.
%
Matches zero or more missing letters in the pattern.

Commands - queries

SELECT
Identify columns to return in query.
SELECT column FROM table;
AS
Renames a column or table using an alias.
SELECT column AS 'alias' FROM table;
DISTINCT
Used to return unique values in the output. Filters out all duplicate values in the specified column(s).
SELECT DISTINCT column FROM table;
LIKE
Operator used with WHERE clause to search for a specific pattern in a column.
WHERE column LIKE 'text'; (or NOT LIKE)
AND
Operator used to combine multiple conditions in a WHERE clause; ALL must be true.
WHERE column condition1 AND column condit­ion2;
OR
Operator used to combine multiple conditions in a WHERE clause; ANY must be true.
WHERE column condition1 OR column condit­ion2;
BETWEEN
Operator used in a WHERE clause to filter the result set within a certain range (numbers, text, or dates).
WHERE column BETWEEN 'A' AND 'B';
BETWEEN two letters is not* inclusive of the 2nd letter.
BETWEEN two numbers is* inclusive of the 2nd number.

Termin­ology - Aggregate Functions

aggre­gates
Calcul­ations performed on multiple rows of a table.
aggregate functions
Combine multiple rows together to form a single value of more meaningful inform­ation.
clause
A clause is used with aggregate functions; used in collab­¬≠or­ation with the SELECT statement.

Commands - Aggregate Functions

COUNT()
Count the number of rows
SELECT COUNT(­co­lumn) FROM table;
SUM()
The sum of the values in a column
SELECT SUM(c­olumn) FROM table;
MAX() / MIN()
The larges­t/s­mallest value in a column
SELECT MAX(c­olumn) FROM table;
AVG()
The average (mean) of the values in a column
SELECT AVG(c­olumn) FROM table;
ROUND()
Round the values in a column
SELECT ROUND(­co­lumn, integer) FROM table;
 

Clauses

1.
WHERE
Restrict the results of a query based on values of individual rows within a column.
2.
GROUP BY
A clause used with aggregate functions to combine data from one or more columns. Arrange identical data into groups.
3.
HAVING
Limit the results of a query based on an aggregate property.
4.
ORDER BY
Sort results by column.
ORDER BY column ASC/DESC
5.
LIMIT
Maximum number of rows to return.
ie.
SELECT column, AGG(co­lumn)
FROM table
CLA­USE column;

Clauses can refer to a column name, or to a column reference number (assigned by order column referred to in statem­ent).

If-then - CASE

 
SEL­ECT columns,
  CASE
 ­ ­  WHEN column condition1 THEN action1
 ­ ­  WHEN column condition2 THEN action2
 ­ ­  ELSE action3
  END AS 'renam­ed_­column'
FROM table;

Combining tables - JOIN

JOIN (inner join)
combine rows from different tables if the join condition is true; drops unmatched rows
LEFT JOIN / RIGHT JOIN
return every row in the left/­right table; if join condition not met, NULL values used to fill in columns from the right­/left table
OUTER JOIN
return unmatched rows from both tables; unmatched fields filled with NULL
CROSS JOIN
combine all rows of 1 table with all rows of another table; does NOT require joining on a specific column
UNION
stacks 1 dataset on top of another; tables must have same # columns & same data types/­order columns
SELECT * FROM table1 UNION SELECT * FROM table2;
SEL­ECT *
FROM table1
JOIN table2
 ­ ­­ON table1.id = table2.id;

ie.
SEL­ECT table1.co­lumn1,
 ­ ­­COU­NT(*) AS renam­ed_­output
 ­ ­­FROM table1
CROSS JOIN table2
WHE­RE table2.co­lumn1 <= table1.co­lumn1
 ­ ­­AND table2.co­lumn2 >= table1.co­lumn1
GROUP BY table1.co­lumn1;

Combining tables - WITH statements

FYI! MySQL prior to version 8.0 doesn't support the WITH clause.
WITH previ­ous­Que­ryA­lias AS (
 ­ ­­SEL­ECT column1,
 ­ ­­COU­NT(­­col­umn­2) AS renam­edO­utp­utC­olumn
 ­ ­­FROM table1
 ­ ­­GROUP BY column1
)
SEL­ECT table2.co­lumn1,
 ­ ­pr­evi­ous­Que­ryA­lia­s.r­ena­med­Out­put­Column
FROM previ­ous­Que­ryA­lias
JOIN table2
 ­ ­­ON table2.co­lumn1 = previo­usQ­uer­yAl­ias.co­lumn1;

Commands - String Functions

STRCM­P(­"­str­ing­1","s­tri­ng2­")
compare strings
LOWER­("s­tri­ng")
convert to lower case
UPPER­("s­tri­ng")
convert to upper case
LTRIM­/­RTR­IM­("st­rin­g")
left or right trim
SUBST­RIN­G(­"­str­ing­"­,"in­x1",­"­inx­2")
substring of a string
CONCA­T(­"­str­ing­1","s­tri­ng2­")
concat­enate

Help Us Go Positive!

We offset our carbon usage with Ecologi. Click the link below to help us!

We offset our carbon footprint via Ecologi
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          SQL Server Cheat Sheet
          Essential MySQL Cheat Sheet
          Oracle SQL Developer Keyboard Shortcuts