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
relational database
A database that organizes inform­ation into one or more tables.
table
A collection of data organized into rows & columns.
statement
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

operators
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

aggregates
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(column) FROM table;
SUM()
The sum of the values in a column
SELECT SUM(column) FROM table;
MAX() / MIN()
The larges­t/s­mallest value in a column
SELECT MAX(column) FROM table;
AVG()
The average (mean) of the values in a column
SELECT AVG(column) FROM table;
ROUND()
Round the values in a column
SELECT ROUND(column, 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
CLAUSE 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

 
SELECT 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;
SELECT *
FROM table1
JOIN table2
 ­ ON table1.id = table2.id;

ie.
SELECT table1.co­lumn1,
 ­ COUNT(*) AS rename­d_o­utput
 ­ FROM table1
CROSS JOIN table2
WHERE 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 previo­usQ­uer­yAlias AS (
 ­ SELECT column1,
 ­ COUNT(column2) AS rename­dOu­tpu­tColumn
 ­ FROM table1
 ­ GROUP BY column1
)
SELECT table2.co­lumn1,
 ­ previo­usQ­uer­yAl­ias.re­nam­edO­utp­utC­olumn
FROM previo­usQ­uer­yAlias
JOIN table2
 ­ ON table2.co­lumn1 = previo­usQ­uer­yAl­ias.co­lumn1;

Commands - String Functions

STRCMP("st­rin­g1",­"­str­ing­2")
compare strings
LOWER("st­rin­g")
convert to lower case
UPPER("st­rin­g")
convert to upper case
LTRIM/RTRIM("st­rin­g")
left or right trim
SUBSTRING("st­rin­g","i­nx1­"­,"in­x2")
substring of a string
CONCAT("st­rin­g1",­"­str­ing­2")
concat­enate
 

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
          SQL Cheat Sheet