Low Orbit Flux Logo 2 D

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.

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.

Operators:


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


Common MySQL types:


Comments:


/*Comment */
SELECT CustomerName, /*City,*/  Country FROM Customers; -- comment

Queries / Select:


SELECT column1, column2, FROM table_name;
SELECT * FROM table_name;
SELECT DISTINCT col1, col2 FROM table_name;
SELECT COUNT(DISTINCT Country) FROM Customers;
SELECT * FROM Customers WHERE Country='Mexico';

Order:


SELECT * FROM Customers ORDER BY Country DESC; -- or ASC
SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;

Insert:


INSERT INTO Customers (a, b) VALUES ('abc', 'xyz');

Check for NULL:


...WHERE col1 IS NULL;
...WHERE col1 IS NOT NULL;

Update:


UPDATE Customers SET ContactName = 'Alfred', City= 'Frankfurt' WHERE CustomerID = 1;
UPDATE Customers SET ContactName='Juan';  -- NO!!!!  (sets for all rows)

Delete:


DELETE FROM Customers WHERE CustomerName='Alfreds';
DELETE FROM table_name;  -- NO!!!!   (deletes everything)
DELETE * FROM table_name;  -- NO!!!! (deletes everything)

Funtions - Standard:


SELECT MIN(column_name)....
SELECT MAX(column_name)....
SELECT COUNT(column_name) ....
SELECT AVG(column_name) ....
SELECT SUM(column_name) ....

Funtions - TOP / Limit / ROWNUM


SELECT TOP 5 * FROM Customers;             -- MSSQL
SELECT * FROM Customers LIMIT 5;           -- MySQL
SELECT * FROM Customers WHERE ROWNUM <= 5; -- Oracle

Pattern Matching:


SELECT * FROM table1 WHERE col1 LIKE ‘a_b%c’
WHERE City LIKE '[!bsp]%';  -- access/mssql only
% - zero, one, or multiple
_ - single char, mysql,   mssql/access use ? instead

Query Set Membership:


WHERE Country IN ('Germany', 'France', 'UK');
WHERE Country NOT IN ('Germany', 'France', 'UK');
WHERE Country IN (SELECT Country FROM Suppliers);

Query by Ranges:


WHERE Price BETWEEN 10 AND 20; -- inclusinve
WHERE Price NOT BETWEEN 10 AND 20;
WHERE Price NOT BETWEEN ‘abc’ AND ‘xyz’;
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

AND:


WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);

Multi Table Query:


SELECT CustomerID as ID, CustomerName AS Customer FROM Customers;
SELECT o.OrderID, c.CustomerName FROM Customers AS c, Orders AS o;


Join Types:


Inner Join:


SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;



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);

LEFT / RIGHT / FULL OUTER Joins:


SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name =table2.column_name;

Self Join:


-- self join, ( all customers from same city )
SELECT A.CustomerName, B.CustomerName, A.City FROM Customers A, Customers B WHERE A.ID <> B.ID AND A.City = B.City;

UNION:

(same cols, types, order)


SELECT col1 FROM table1 UNION SELECT col1 FROM table2;
SELECT col1 FROM table1 UNION ALL SELECT col1 FROM table2; -- include dups

GROUP BY


SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;

Select records referenced in other table


SELECT SupplierName FROM Sup WHERE EXISTS (SELECT * FROM Products WHERE id = Sup.id);

Select matching records referenced in other table


SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

Copy: SELECT INTO

Copy from one table to another (good for backups).


SELECT * INTO CustomersBackup20191201 FROM Customers;
SELECT * INTO CustomersBackup20191201 IN 'Backup.mdb' FROM Customers;

Copy: using INSERT


INSERT INTO table2 SELECT * FROM table1 WHERE condition;
INSERT INTO table2 (col1, col2) SELECT col1, col2 FROM table1 WHERE col3 = 5;

NULL Functions

These work with MySQL. MSSQL, Oracle, etc. use other functions.


SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)) FROM Products 

Create Database


CREATE DATABASE testDB;
DROP DATABASE testDB;

Create Table


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

Create Table from Existing Table (cols and values)


CREATE TABLE new_table_name AS SELECT col1, col2 FROM existing_table_name;

Create Table


DROP TABLE Shippers;       -- delete table
TRUNCATE TABLE table_name; -- delete data

ALTER Table - Change Columns


ALTER TABLE table_name ADD col1 date;
ALTER TABLE table_name DROP COLUMN col1;
ALTER TABLE table_name MODIFY COLUMN col1 year;  --mysql only, others differ

Create Table - with non-null unique ID


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

Create Table - non-null ID and Last Name

This uses a multi-column constraint.


CREATE TABLE Persons (
	ID int NOT NULL,
	LastName varchar(255) NOT NULL,
    CONSTRAINT UC_Person UNIQUE (ID,LastName)
); --multi col, all dbs

ALTER Table - add/remove multi column constraint


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

Create Table - non-null Primary Key


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

Create Table - non-null, Named, Multi Col, 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

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


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

Drop Primary Key


ALTER TABLE Persons DROP PRIMARY KEY; --mysql only

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


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 (all DBs)


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

ALTER Table - add / remove foreign keys


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

Create Table - With a Constraint (MySQL only)


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 Named Constraint (all DBs)


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

ALTER Table - Checks and Constraints


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

Create Table - With a Default Value


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

Create Table - Date for Default Value


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

ALTER Table - Add / Remove Default Values


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

Create Index And Unique Index


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

Remove Index


ALTER TABLE table_name DROP INDEX index_name; -- mysql only

Create Table - Auto Increment Primary Key


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

Dates - MYSQL only:


DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
YEAR - format YYYY or YY
SELECT * FROM Orders WHERE OrderDate='2008-11-11'

Create a View


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

Query a View


SELECT * FROM [Current Product List];

Create or Replace a View


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

Drop a View


DROP VIEW view_name;