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
           
          Advertisement