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