Show Menu
Cheatography
  1. Home >
  2. Programming >

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
 

Metadata

  • Languages:
  • Published: 24th July, 2019
  • Rated: 4 out of 5 stars based on 2 ratings

Favourited By

npk

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

          Web Programming Cheat Sheet
          SQL Server Cheat Sheet
          SQL Cheat Sheet