A relational database stores data in a row-based table structure (much like a spreadsheet) as opposed to a document-oriented database like MongoDB. Data from one table can relate to another, for example a table of users, and a table of cities, where you link a city from the cities table to each user in the users table.
Examples of Relational DataBase Management Systems (RDBMS):
PostgreSQL
MySQL
SQLite
Oracle DB
For this class, we will be using .
Structured Query Language (SQL)
SQL is the language you use to talk to a relational database. Most RDBMSes will include language extensions specific to them but they all implement the same standard "base" language which is what we'll focus on.
Syntax
UPDATE dogs SET pats = pats + 1 WHERE id = 42;
The snippet above is a complete SQL statement. Statements are terminated with a semicolon ; and are comprised of:
Keywords
Words defined in the language
UPDATE, SET, WHERE
Identifiers
Names of tables, columns, etc.
dogs, pats, id
Clauses
Constituent components of a full statement
UPDATE dogs, SET pats = pats, WHERE id = 42
Predicates
Specifies conditions that evaluate to a Boolean
id = 42
Expressions
Results in a value
pats + 1, 42
NOTE Even though keywords in SQL are not case sensitive, the convention is to capitalize them.
Comments start with a --
Use single quotes for string values
Connect to PostgreSQL
Make sure your PostgreSQL service is running
brew services start postgresql
# or
sudo systemctl start postgresql.service
List all databases
psql -l
If you want to create a new database
createdb learn_sql
Connect to the server and database
psql learn_sql
Delete a database
dropdb learn_sql
Create and Delete a Database from inside the psql shell
Create a new database
CREATE DATABASE test;
Connect to the new database
\c test
Delete database (NOTE: you can't drop a database you're currently connected to)
DROP DATABASE test;
PostgreSQL Data Types
The most common data types are
int
whole numbers
decimal
floats
bool
boolean
varchar(n)
small text
text
big text
timestamptz
date
Data types restrict what kind of data is allowed in a table.
Create and Delete Tables
Create a new table foo with a column name which will store text data with a maximum of 20 characters
CREATE TABLE foo ( name VARCHAR(20) );
See your new table
\dt
\d foo
Delete the table
DROP TABLE foo;
Create a better table
CREATE TABLE actors (
id SERIAL PRIMARY KEY,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20),
height DECIMAL,
weight DECIMAL,
sings BOOL,
dances BOOL DEFAULT false
);
\d actors
CRUD
CRUD is an acronym that describes the four essential database operations: Create, Read, Update, Delete.
Operation
SQL
Create
INSERT
Read
SELECT
Update
UPDATE
Delete
DELETE
Insert Data
NOTE You don't have to remember the order of the columns that you created, but you do have to match the order in the insert statement.
-- select all rows from the actors table. display all columns
SELECT * FROM actors;
-- select all rows from the actors table. display only the first_name column
SELECT first_name FROM actors;
-- select all rows from the actors table. display the first_name and last_name columns
SELECT first_name, last_name FROM actors;
-- select all rows from the actors table where the name column is set to 'Tala'
SELECT * FROM actors WHERE first_name = 'Tala';
-- select all rows from the actors table where the height column is set to 180
SELECT * FROM actors WHERE height = 180;
-- select all rows from the actors table where the sings column is set to true
SELECT * FROM actors WHERE sings = true;
-- select all rows from the actors table where the height column is greater than 165
SELECT * FROM actors WHERE height > 165;
-- select all rows from the actors table where the height column has no value
SELECT * FROM actors WHERE height IS NULL;
-- select all rows from the actors table where the height column has any value
SELECT * FROM actors WHERE height IS NOT NULL;
-- select all rows from the actors table where the name column contains 'Ma'
SELECT * FROM actors WHERE first_name LIKE '%Ma%';
Update Data
-- set the height column to 181 for every row that has the id column set to 2
UPDATE actors SET height = 181 WHERE id = 2;
-- increment Florence Pugh's height by 1
UPDATE actors SET height = height + 1 WHERE first_name = 'Florence';
-- set the height column to 170 for every row where the height column is less than 170
UPDATE actors SET height = 170 WHERE height < 170;
Delete Data
-- delete all rows from the actors table that have the id column set to 13
DELETE FROM actors WHERE id = 13;
BE CAREFUL! DON'T WRITE A DELETE STATEMENT WITHOUT A WHERE CLAUSE