Low Orbit Flux Logo 2 F

SQL Cheat Sheet

This is our SQL cheat sheet. It is a too long/detailed to be a cheat sheet but too short to be a tutorial. We focus on MySQL but mention other DBs. We include more than what I've seen in other SQL cheat sheets.

Purpose - This is meant to be used as a reference. It is not meant to be a complete guide. It should help if you just want to remember the syntax of a statement or if you know enough to kind of figure out how it works based on an example.


Part Zero

+ - * / % & | ^ = > < >= <= <>     
+= -= *= /= %= &= ^-= |*=
ALL AND ANY BETWEEN EXISTS IN LIKE NOT OR SOME

Operators

INT(size), BLOB, VARCHAR(size)

Common MySQL types

/*Comment */
SELECT col1, /*col2,*/ col3 FROM Tab1; -- comment
Comments


Queries / Select

SELECT * FROM table1;
Entire table
SELECT col1, col2, FROM table1;
Only two columns
SELECT * FROM table1 WHERE loc='Mexico';
Anything matching search term
SELECT DISTINCT col1, col2 FROM table1;
Only distinct records
SELECT COUNT(DISTINCT Country) FROM table1;
Count of distinct records for one col
SELECT * FROM Customers ORDER BY Country ASC;
Order Ascending
SELECT * FROM Customers ORDER BY Country DESC;
Order Descending
SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;
Both


Insert / Update / Delete

INSERT INTO Customers (a, b) VALUES ('abc', 'xyz');
Insert
UPDATE Customers SET ContactName = 'Alfred', City= 'Frankfurt' WHERE CustomerID = 1;
Update
UPDATE Customers SET ContactName='Juan';
Update - NO!!!!  (sets for all rows)
DELETE FROM Customers WHERE CustomerName='Alfreds';
Delete
DELETE FROM table_name;
Delete - NO!!!! (deletes everything)
DELETE * FROM table_name;
Delete - NO!!!! (deletes everything)


Funtions and Limits

SELECT MIN(col1) from table1;
smallest value in col
SELECT MAX(col1) from table1;
largest value in col
SELECT COUNT(col1) from table1;
number in col
SELECT AVG(col1) from table1;
average value
SELECT SUM(col1) from table1;
sum of values
SELECT TOP 5 * FROM Customers;
first 5 recors ( MSSQL )
SELECT * FROM Customers LIMIT 5;
first 5 recors ( MySQL )
SELECT * FROM Customers WHERE ROWNUM <= 5;
first 5 recors ( Oracle )


Pattern Matching

SELECT * FROM table1 WHERE col1 LIKE ‘a_b%c’
WHERE City LIKE '[!bsp]%';  -- access/mssql only
Match a pattern
%
zero, one, or multiple
_
single char, mysql,   mssql/access use ? instead
.. WHERE Country IN ('Germany', 'France', 'UK');
Query Set Membership
.. WHERE Country NOT IN ('Germany', 'France', 'UK');
Query Set Membership
.. WHERE Country IN (SELECT Country FROM Suppliers);
Query Set Membership
.. WHERE Price BETWEEN 10 AND 20; -- inclusinve
Query by Ranges
.. WHERE Price NOT BETWEEN 10 AND 20;
Query by Ranges
.. WHERE Price NOT BETWEEN ‘abc’ AND ‘xyz’;
Query by Ranges
.. WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;
Query by Ranges
.. WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);
AND
SELECT CustomerID as ID, CustomerName AS Customer FROM Customers;
Multi Table Query
SELECT o.OrderID, c.CustomerName FROM Customers AS c, Orders AS o;
Multi Table Query
...WHERE col1 IS NULL;
Check for NULL
...WHERE col1 IS NOT NULL;
Check for NOT NULL


Joins and Unions

Join Types: 
(INNER) JOIN
matched from both tables
LEFT (OUTER) JOIN
all from left, matched from right
RIGHT (OUTER) JOIN
matched from the left, all from the right
FULL (OUTER) JOIN
matched in either table

SELECT column_name(s) FROM table1 
INNER JOIN table2 ON table1.column_name = table2.column_name;
Inner Join
SELECT Orders.OrderID, Customers.CustomerName, 
Shippers.ShipperName FROM ((Orders INNER JOIN Customers ON 
Orders.CustomerID = Customers.CustomerID) INNER JOIN Shippers ON 
Orders.ShipperID = Shippers.ShipperID);
Inner Join
SELECT column_name(s) FROM table1 LEFT JOIN table2 
ON table1.column_name = table2.column_name;
LEFT Join
SELECT column_name(s) FROM table1 RIGHT JOIN table2 
ON table1.column_name = table2.column_name;
RIGHT Join
SELECT column_name(s) FROM table1 FULL OUTER JOIN 
table2 ON table1.column_name = table2.column_name;
FULL OUTER Join
SELECT A.CustomerName, B.CustomerName, A.City FROM 
Customers A, Customers B WHERE A.ID <> B.ID AND A.City = B.City;
Self Join  ( all customers from same city )
SELECT col1 FROM table1 UNION SELECT col1 FROM table2;
UNION ( same cols, types, order )
SELECT col1 FROM table1 UNION ALL SELECT col1 FROM table2;
UNION include dups ( same cols, types, order )


Misc

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
GROUP BY
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;
GROUP BY
SELECT SupplierName FROM Sup WHERE EXISTS (SELECT * FROM Products WHERE id = Sup.id);
Select records referenced in other table
SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
Select matching records referenced in other table
SELECT * INTO CustomersBackup20191201 FROM Customers;
Copy from one table to another (good for backups)
SELECT * INTO CustomersBackup20191201 IN 'Backup.mdb' FROM Customers;
Copy from one table to another (good for backups)
INSERT INTO table2 SELECT * FROM table1 WHERE condition;
Copy: using INSERT
INSERT INTO table2 (col1, col2) SELECT col1, col2 FROM table1 WHERE col3 = 5;
Copy: using INSERT
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products
NULL Functions MySQL only ( Oracle, etc. are different )
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)) FROM Products
NULL Functions MySQL only ( Oracle, etc. are different )


Create / Delete Database

CREATE DATABASE testDB;
Create DB
DROP DATABASE testDB;
Delete DB


Create Table

CREATE TABLE Persons (
PersonID int,
LastName varchar(255)
);

Create Table

CREATE TABLE new_table AS SELECT 
col1, col2 FROM old_table;

Create Table 
from Existing Table 
(cols and values)

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL
UNIQUE (ID)
); --mysql only

Create Table 
with non-null unique ID

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);

Create Table - non-null ID and 
Last Name.  This uses a multi-column 
constraint.  ( all DBs )

CREATE TABLE Persons (
	ID int NOT NULL,
	LastName varchar(255) NOT NULL,
    PRIMARY KEY (ID)
); -- mysql only,

Create Table - non-null Primary Key

CREATE TABLE Persons (
	ID int NOT NULL,
	LastName varchar(255) NOT NULL,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);  -- multi col, named, mysql only

Create Table - non-null, Named, Multi Col, Primary Key

CREATE TABLE Orders (
	OrderID int NOT NULL,
	PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);--mysql only

Create Table - Primary Key and Foreign Key (MySQL only)

CREATE TABLE Orders (
	OrderID int NOT NULL,
	OrderNumber int NOT NULL,
	PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);  -- named, all dbs

Create Table - Primary Key and Foreign Key (all DBs)

CREATE TABLE Persons (
	ID int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Age int,
    CHECK (Age>=18)
);  -- mysql only

Create Table - With a Constraint (MySQL only)

CREATE TABLE Persons (
	ID int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Age int,
	City varchar(255),
    CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
); -- named all dbs

Create Table - With Named Constraint (all DBs)

CREATE TABLE Persons (
	ID int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Age int,
	City varchar(255) DEFAULT 'Sandnes'
);

Create Table - With a Default Value

CREATE TABLE Orders (
	ID int NOT NULL,
	OrderNumber int NOT NULL,
	OrderDate date DEFAULT GETDATE()
);

Create Table - Date for Default Value

CREATE TABLE Persons (
    ID int NOT NULL AUTO_INCREMENT,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Age int,
    PRIMARY KEY (ID)
); -- mysql only
ALTER TABLE Persons AUTO_INCREMENT=100; -- mysql only

Create Table - Auto Increment Primary Key


Table Removal

DROP TABLE Shippers;

Drop Table

TRUNCATE TABLE table_name;

Truncate Table


Alter Table



ALTER TABLE table1 ADD col1 date;
ALTER TABLE table1 ADD email varchar(255);
ALTER TABLE table_name DROP COLUMN col1;
ALTER TABLE table_name MODIFY COLUMN col1 year;  --mysql only, others differ



ALTER Table - Change Columns


ALTER TABLE Persons ADD UNIQUE (ID);
ALTER TABLE Persons ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
ALTER TABLE Persons DROP INDEX UC_Person; --multi col constraint, mysql only
ALTER TABLE Persons DROP CONSTRAINT UC_Person; -- MSSQL, Oracle, Access 

ALTER Table - add/remove multi column constraint

ALTER TABLE Persons ADD PRIMARY KEY (ID);
ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName); -- multi col, named

ALTER Table - non-null, Named, Multi Col, Primary Key



ALTER TABLE Persons DROP PRIMARY KEY; --mysql only



Drop Primary Key




ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
ALTER TABLE Orders ADD CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
ALTER TABLE Orders DROP FOREIGN KEY FK_PersonOrder;--mysql only



ALTER Table - add / remove foreign keys




ALTER TABLE Persons ADD CHECK (Age>=18);
ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
ALTER TABLE Persons DROP CHECK CHK_PersonAge; --mysql only



ALTER Table - Checks and Constraints




ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';  -- mysql only
ALTER City DROP DEFAULT; -- mysql only



ALTER Table - Add / Remove Default Values




ALTER TABLE table_name DROP INDEX index_name; -- mysql only


Remove Index


Create Index

CREATE INDEX index_name ON table_name (column1, column2, ...); -- dups

Create Index

CREATE UNIQUE INDEX index_name  ON table_name (column1, column2, ...); -- no dups

Create Unique Index


Dates - MySQL Only

DATE
YYYY-MM-DD
DATETIME
YYYY-MM-DD HH:MI:SS
TIMESTAMP
YYYY-MM-DD HH:MI:SS
YEAR
YYYY or YY

SELECT * FROM Orders WHERE OrderDate='2008-11-11'

Query by date


Views

CREATE VIEW [Current Product List] AS
SELECT ProductID, ProductName
FROM Products
WHERE Discontinued = No;


Create a View

SELECT * FROM [Current Product List];

Query a View


CREATE OR REPLACE VIEW [Current Product List] AS
SELECT ProductID, ProductName, Category
FROM Products
WHERE Discontinued = No;


Create or Replace a View

DROP VIEW view_name;

Drop a View