Show Menu

MySql Cheat Sheet (DRAFT) by

This is a draft cheat sheet. It is a work in progress and is not finished yet.

Basic Concepts

display list of databases available
display list of tables in a selected database
SHOW COLUMNS FROM <ta­ble­_na­me>;
show columns in a table
SELECT <co­lum­n,l­ist> FROM <ta­ble­_na­me>;
retrieve data in columns of a table
SELECT * FROM <ta­ble­_na­me>;
list all data in a table
SELECT DISTINCT <co­lum­n,n­ame­s> FROM <ta­ble­_na­me>;
avoid duplicates and return unique ones
... LIMIT 5;
limit number of results
... LIMIT 3,4;
from array index of 3 return 4 items
SELECT <ta­­lum­n> FROM <ta­ble­>;
fully qualified
... ORDER BY <co­lum­n_n­ame­>;
sort alphab­eti­cally of numeri­cally default ascending order
... ORDER BY <co­l1,­col­2>;
first order 1 col then form 2nd col

Join, Table Operations

SELECT custom­ers.ID, custom­ers.Name, orders.Name, orders.Amount
FROM customers, orders
WHERE custom­ers.ID­=or­der­s.C­ust­omer_ID
ORDER BY custom­ers.ID;
joining tables
SELECT ct.ID, ct.Name, ord.Name, ord.Amount
FROM customers AS ct, orders AS ord
WHERE ct.ID=­ord.Cu­sto­mer_ID ORDER BY ct.ID;
renaming tables

Filtering, Functions, Subqueries

... WHERE <co­ndi­tio­n>;
ex; ID = 7; name = 'John'/ ='can''t'
=, !=, >, <, >=, <=, BETWEEN
comparison operators
... ... BETWEEN <va­l1> AND <va­l2;>
returns val1 to val2
logical operators
... ... AND (val1=00 OR val2=88);
combining logics
... ... IN ('val1', 'val2', 'val3');
multiple OR altern­ative
... ... NOT IN ('val1', 'val2', 'val3');
exclude list items
SELECT CONCAT­(col1, ', ' , col2) FROM <ta­ble­>;
returns the concat­enating string.
SELECT CONCAT­(col1, ', ' , col2) AS new_col FROM <ta­ble­>;
concat results in new col
... Salary+500 AS Salary FROM <ta­ble­>;
do operation on filed value
... val(UPPER) AS new_val ...
change to uppercase
LOWER(­val), SQRT(val), MIN(col), AVG(col), SUM(col), POWER(val)
math / str functions
... ... ORDER BY <co­l> DESC
order by desc or ASC
... WHERE Salary > (SELECT AVG(Sa­lary) FROM employees) ...
sub quaries
... ... LIKE 'A%';
pattern = _ single char, % multiple char