sql_basics_cheatsheet.md

Introduction to SQL and Postgres Cheatsheet

shell commands

Command	Function
psql	Interact with Postgres
... -d $name	Connect to a database
createdb $name	Create a database
dropdb $name	Destroy a database
pg_dump	Utility for backing up and restoring

psql commands

General

Command	Function
\?	List psql commands
\h	List help topics
... $topic	View help for a specific topic
\l	List databases
\c $name	Connect to a database
\q	Quit

In a Database

Command	Function
\d	Describe relations
... $relation	Describe a specific relation, e.g. a table schema
\dt	List tables in the database

SQL commands

Table/schema operations

Command	Function
CREATE TABLE	Create a table
ALTER TABLE $table	Make a change to a table schema
... RENAME TO $name	Rename a table
... ADD COLUMN $name [$options]	Add a column
... RENAME COLUMN $name TO $new_name	Rename a column
... ALTER COLUMN $name $attribute	Change a column
... DROP COLUMN $name	Destroy a column
DROP TABLE $name	Destroy a table

Data operations

Command	Function
INSERT INTO $table ...	Add data
SELECT $query	Select data to operate on
... FROM $table	Narrow selection to a particular table
... WHERE $criteria	Narrow selection by attributes
... ORDER BY $field [ASC|DESC]	Order the returned data
UPDATE $table SET ...	Change data
... WHERE $criteria	Narrow update to specific rows
DELETE	Delete data
... FROM $table	Narrow deletion to a particular table
... WHERE $criteria	Narrow deletion by attributes

Joins

Command	Function
SELECT $columns FROM $table...	Select columns to display after joining tables. $columns can be in the format $table.column or $other_table.column
... INNER JOIN $other_table ON $condition	Return data which corresponds to an entry in both tables
... LEFT JOIN $other_table ON $condition	Return all rows in $table. For those that lack corresponding data in $other_table, leave the column from $other_table blank
... RIGHT JOIN $other_table ON $condition	Return all rows in $other_table. For those that lack corresponding data in $table, leave $table's columns blank
... CROSS JOIN $other_table	Return all the combinations of rows from two tables