Show Menu
Cheatography
  1. Home >
  2. Cheat Sheets >

MySql Cheat Sheet (DRAFT) by

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

Basic Concepts

SHOW DATABASES;
display list of databases available
 
SHOW TABLES;
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­ble.co­lum­n> FROM <ta­ble­>;
fully qualified column.name
 
... 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
 
AND, OR, IN, NOT
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