mentalaxis
SQL Quick Reference

This guide is a very basic reference to standard SQL (ANSI) these commands and syntax should work with any implementation of SQL. However, if in the remote chance that your SQL product doesn't support them... it wasn't our fault ok! (and is also very very unlikely)

There are many ommissions, ie. there are no Create or Drop Database command syntax... this guide is simple for the benefit of those who need to quickly create simple SELECT queries, if you need to create a database, I hope you have been looking at the official documentation for your particular SQL implementation... If not, well then shame on you.

A very good set of comprehensive references for SQL are available at: MySQL (http://www.mysql.com/) and PostGres (http://www.postgresql.org/)

Functions
Name Purpose & Example
SUM Total of the values in a field.
AVG Average of the values in a field.
MIN Lowest value in a field.
MAX Highest value in a field.
COUNT Number of non-null values in a field. (i.e. where the field/column is not blank)
Predictates
Name Description & Example
BETWEEN ... AND Compares a value to a range formed by two values.
IN Determines whether a value exists in a list of values or a table.
LIKE Compares, in part or in whole, one value with another.
JOIN Joins two tables.
Statements
Name Syntax Description & Example
SELECT

SELECT [ DISTINCT ] * | LIST OF COLUMNS, FUNCTIONS, CONSTANTS
FROM LIST OF TABLES OR VIEWS
[ WHERE CONDITION(S) ]
[ ORDER BY ORDERING COLUMN(S) [ ASC | DESC ] ]
[ GROUP BY GROUPING COLUMN(S) ]
[ HAVING CONDITION(S) ]

 Query a set of records.
DELETE

DELETE FROM TABLE NAME
[ WHERE CONDITION(S) ]

 Delete all the records in a table (or those which match the condition(s))
INSERT INSERT INTO TABLE NAME
[ (COLUMN LIST) ]
VALUES (VALUE LIST) 		Insert records into a table
UPDATE UPDATE TABLE NAME
SET COLUMN NAME = VALUE
[ WHERE CONDITION ]		 Modify a column/field value in a table, (or in records in the table which match the condition(s)
CREATE TABLE

CREATE TABLE TABLE_NAME
( COLUMN_NAME DATA_TYPE [(SIZE)] COLUMN_CONSTRAINT,
[, other column definitions,...]
[, primary key constraint]
)

 Create a new table
CREATE VIEW CREATE VIEW VIEW_NAME AS QUERY_NAME Create a new view
ALTER ALTER TABLE TABLE_NAME ADD | DROP | MODIFY
( COLUMN_NAME DATA_TYPE [(SIZE)] COLUMN_CONSTRAINT,
[, other column definitions,...]
)		 Alter table columns, remove/modify/add columns from (or to) a table.
DROP TABLE DROP TABLE TABLE_NAME Delete an entire table
DROP INDEX DROP INDEX INDEX_NAME ON TABLE_NAME Delete a table index
CONSTRAINT CONSTRAINT CONSTRAINT_NAME
{PRIMARY KEY | UNIQUE | NOT NULL |
REFERENCES FOREIGN_TABLE [(FIELD_LIST)]} 		Create a table constraint, (with references from another table)

(cc) creative commons 2004 mentalaxis
corrections and comments to jason@mentalaxis.com