SQL
Structured Query Language
But
First,
Databases
PostgreSQL
- Specific variety of Database (DB)
- Specific syntax to get data
We use a database to store our data in tables and rows
Similar to an excel spreadsheet
Schema
A schema is a blueprint for our database.
CREATE TABLE <table name> (
<column name> <data type> [<constraint>],
<column name> <data type> [<constraint>]
);
We can name our table and columns whatever we want, but it should be descriptive
Common Column Data Types
- NULL - an empty value
- Numeric
- INTEGER - whole number
- DECIMAL - using decimals
- FLOAT - can only have 15 decimal places
- SERIAL - auto incrementing
- Characters
- TEXT - unlimited characters
- VARCHAR(count) - characters limited to count
But
How,
Jonathan?
Can we actually get data
SQL has many different commands, or queries, that will narrow down our search for data in our DB
Think about searching for a name in a phone book, you wouldn't just search over all of data, you would look by last name first
Each query has at least three parts, sometimes more
- What are we doing?
- What are we selecting?
- Where do we want to look?
select * from users;
What we are doing
what we are selecting
where we are searching
Queries should follow a few rules:
- Always end in a semi-colon
- Only use single quotes, double quotes searches for a column by that name
- Be consistent. Doesn't matter if you capitalize keywords or not, just be consistent.
WHERE clause
Not looking at where something is but where something matches a specific clause or stipulation
WHERE clause
- > - greater than
- < - less than
- >= - greater than or equal to
- != - does not equal
Comparison Operators
WHERE clause
- AND
- OR
Logical Operators
select * from users
where first_name = 'Bryan'
and age = 29;
WHERE clause
- IS NULL
- IS NOT NULL
Null check Operators
select * from users
where favorite_color IS NOT NULL;
WHERE clause
Between Operators
select * from users
where age BETWEEN 18 AND 109;
LIMIT clause
Sometimes your table may have thousands and thousands of rows. We don't always need everything so we can limit it.
select * from users
limit 50;
ORDER BY clause
You can organize the data you get back from your query. Two of the most common ways are ASCending and DESCending
select * from users
order by age desc;
Aggregate Functions
-
min()
-
max()
-
sum()
-
avg()
-
count()
select avg(age) as average_user_age from users;
WHERE Partial Matching
select * from users
where name like '%rya%';
SQL1
By jonmcd
SQL1
- 153