###### tags: `SQL` `Lesson Notes`

# SQL Notes:
---
All queries will deal with 1 of the 4 operations that our database can do. Here are the 4 commands.
CRUD:
- C - Create
- R - Read
- U - Update
- D - Delete

All queries to the data base will have a basic format.
- OPERATION table conditions/values;

## Create
- Add new content to our database.
- This operation in SQL is called INSERT.

INSERT INTO table (column1, column2, column3) VALUES ('value1', value2, value3);

- It is important that when adding the values they are in the same order we put the column names.

## Read
- This is to pull information from the database. Typically to display on the webpage.
- There is not 1 set way to write this query as it can get complicated depending on the information we are trying to get.

SELECT * FROM table;

- This is the most basic form - where we will pull all rows in the table selected. However we can narrow it down in many different ways

SELECT * FROM table WHERE firstName='Jane';

- Here we are saying get every entry in the database that has a first name of Jane
- You can also chose more than 1 column and say where column1 and column2 or even column1 or colum2

### Join Statements
- This is where Read gets complicated.
- Say you want to get all the pets that 1 owner has. Pets are in one table while the owners are in another.
- Join Statements allow you to pull data from multiple tables where they intersect.
- More on this later

## Update
- Here just as it sounds we are going to update some information that is in our database.
- Be careful with this one as you might end up updating more than you planned to if you word your query wrong

UPDATE table SET column1=value1 WHERE condition;

- The condition is where you can get into trouble. If I put to updated the firstName to Bob where the firstName = Jane I just changed every entry in the database that has a firstname of Jane to Bob.
- You can update more than 1 column at a time just like more than 1 row at a time

## Delete
- Just like the last one this one is what it sounds like. Removing data from the database
- Just like update you can get in trouble with this one with the Where clause

DELETE FROM table WHERE condition;

- Like before if I say delete from the database where firstname = Jane. Well there goes all the Janes that have used our database.

# Ok Now what
- So you think thats all you need to know right? WRONG!
- Here are some other things you should know...

AND
OR
NOT
ORDER BY
MIN
MAX
COUNT
AVG
SUM
LIKE
WHILECARDS
BETWEEN
ALIASES
LEFT JOIN
RIGHT JOIN
JOIN
INNER JOIN

- That's just a few.
- Some I am sure you can take a guess as to what they mean or do others might be a little harder

# What else should we know?
- So 1 important thing you need to know about databases is id
- id is a unique way of identifying 1 row in the database.
- Every new database starts with the id of 1 (not 0) however there may not always be an entry that has an id of 1

## Wait what?
- I know I know how can that be if you remove someting from an array everything shifts right? Sure of course it does. But the database isn't an array. Yes it returns it's data as an array but it isn't one.
- when we add data to the database we actually physically assign a number to that row. Just like we all have unique SSN's so does our data in our database.
- So just because we delete entry 1 we can't reuse that number.

Ok so every row has an id so what? Well this is important. We don't want to delete every Jane in our database just the one that left the company. So that means we need to specify the only guarenteed unique part of the Jane that left.

So why not use the SSN? Well what if we didn't collect that? Or better yet in most cases that is part of another table.

Ok now I am telling you that 1 users information can be in more than 1 table? Yup that I am. That is where this amazing thing called relationships come in. No not boyfriend and girlfriend. Although.....

I mean you can look at them that way if you want... but there are 3 kinds of relationships.

1:1
1:M
M:M

I already know what you are going to say... what the heck does that greek mean right?

1:1 or in English one to one is the type of relationship you have with your SSN. Or as a student once said husband and wife. 1 husband 1 wife. 1 person 1 SSN

1:M or in English one to many. This is as the same student said is the cheater. 1 boyfriend many girlfriends. (Rude I know but makes a point) Or in nicer terms 1 owner many pets. The Pet doesn't have multiple owners but the owner can have many pets.

M:M is as I am sure you have guessed already is Many to Many to many. This one is a little harder to visuallize. So lets think back to highschool (for some it may be harder to think that far back). Students have many classes and classes have many students.

Basically what these relationships mean is how are their respective tables connected. So 1 user can have many posts but those posts can only belong to 1 user. However that 1 post can have many comments from many users. but each user has their own profile picture.

Ok so why is this important. Well rarely will we have a database with onky 1 table. The tables are going to interact in some way. In comes relationships and when gathering the data to display our join statements.

SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column_1 WHERE table1.column1 = a;

What that says is gather all the entries in table 1 where the specificed columns have matching information. Don't worry if this sounds difficult, it can be.

More to follow