MySQL for Beginners


Databases

Updated Mar 14th, 2022

1. Welcome

Why use a database, and why use instead of a spreadsheet?

View data from infinite different angles. Database is the ultimate in flexibility and very fast and efficient, Even with lots and lots of data, (millions of rows).

2. Getting Started. MySQL alts: PostgreSQL, MSFt SQL Server.

Install mySQL Server, and a way to connect to it with (Workbench).

Can we use siteground? Yes, allow my IP access to the server. For this reason i am only going to install workbench.

Workbench is a program created by who? Oracle

3. Installing on window

mysql installer from dev.mysql.com is 436MB ?
The installer can be deleted, workbench itself is 145 MB.
I ran a custom install per brad to just get mysql server and worbench but don’t i only need workbench? I tried just installing workbench.

if using public server create a difficult password.

The UI for workbench can be overwhelming

A schema is another word for db.

4. Install IOS

5. Install Linux

6. Get to work

Two big db frustrations for brad, dummy data and but don’t just want to impoort either because we don’t design our own.

import “reference.sql” file from udemy resources for this chapter

Top menu > server >data import > import from self-contained file then choose db to import and click start import

petfoods db and petsfoodsreference db so we can rebuild but still be able to query on filed db

7. Workbench Note

Workbench note: Lightning icon to run command but will only run highlighted text if there is any highlighted so be aware. Also, GUI will prevent certain commands as a safeguard so go to Edit>Preferences and SQL Editor and uncheck safe updates checkbox.

8. Our First Table

Right click and insert table Click to edit

data types:

smallest data type that makes sense, size and speed. choosing can be a rabbithole.

VARCHAR(60)

255 is a safe value in place of 60.

PK stands for primary key
NN stands for Not Null

Click apply in bottom right corner

Hoever over thrid icon that looks like a calendar to see the data.

Delete text and type out new SQL command

Keywords shpould be uppercase but don’t need to be.

INSERT INTO animals (name, species) VALUES (“meosawlot”, “cat”)

Need the commas

ctrl shift enter is the shortcut in place of clicking lightning bolt.

right click db and click “default db schema” so the query runs on your desired db.

SELECT * FORMpetfoods.aniamls WHERE species = “dog”;

The * is to select all columns so update to be name so we only get the name column. may need to wrapm name in backticks. good practice to wrap column in backticks.

update
UPDATE animals SET name = “Buddy Junior” WHERE name = “Buddy”

delete
DELETE FROM animals WHERE name = “Buddy Junior”

9. What is a Primary Key?

Problems PK solves:
a. Add a another cat named meowsalot how can you differentiate the two? PK is a unique id

MySQL can come up with these values for us

When creating the table Add a column, with data type of INT industry standard to name id, and select PK and the NN box will be auto-checked. Forces uniques and will be automatically indexed.

Check the AI checkbox for auto-increment

10. Additional Data types, look at the referecne manual for MySQL

DATE

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT (checkout the maximum value signed).

TINYTEXT
TEXT 65K limit
MEDIUMTEXT 16M limit
LONGTEXT 4B limit

What does signed/unsigned mean? Refers to the negative sign in from of the number. Has nothing to do with security. Other dbs do not support unsigned integers so a lot of devs chose to use signed in case the ever move from MySQL.

INSERT INTO animals
() VALUES
()

SQL statements are not white-space sensitive so drop down as much as you like.

SELECT * FROM animals WHERE weight > 15

WHERE joindate > “2018-01-01”

quotes not nee
ded around numbers but for a date we do wrap in quotes.

11. Products table

id, name, priceusd,

If you see DECIMAL(10, 2) the 2 is how many values can come after the decimal point

INSERT INTO products (name, priceusd) VALUES
(“Apples”, “12”)

12. Relating One Table with Another

Design dbs that are flexible and scalable

create new table specificaly designed for new orders

current time (NOW()) or 2010-12-23 08:00:00

relational as in relation to other tables

SELECT * FROM orders JOIN animals ON orders.userid = animals.id

Select orders.date, animals.name, animals.email FROM orders JOIN animals ON orders.userid = animal.id WHERE animals.species = “cat”

13. Order Details Part 1

If you may have an unknown amount of something then you should start thinking of having a new table.

orderlines table that has a id, orderid, productid, quantity

INSERT INTO orderlines (orderid, productid, quantity) VALUES (1, 1, 10)

Insert multiple rows at once
with a comma and another set of parentheses.

14. Order Details Part 2

Can grab values from multiple tables for your query with JOIN

JOIN products ON orderline.productid = products.id

can perform math in your query and use the “as” keyword to use an alias to update the column name for your calculation.

Can add rows together (SUM is an aggregate functions)

Want the most recent order id value by using
Nested query or subquery
WHERE orderid = (SELECT id FROM orders WHERE userid = 3 LIMIT 1)

15. What is an Index?

include EXPLAIN keyword agt the start of your statement and checkout the results, specifically the “rows” result.

Avoid examining every single row.

index for a book, index take space and time but help out tremendously

Why don’t we index every column? It slows down create and update operations.

If a column likely to be searched on then an index may make sense.

16. Foreign Keys

Relationships b/w tables

Our way of official desxribing relationship to MySQL know about a join relationship.

Wrench icon

When a FK is set up, an update operation may fail if a value doesn’t exist. Ensure or guarantee integrity. Cannot delete a row or rows that is being referenced in other tables. Sometimes this is helpful, sometimes this is not. You can change the FK settings from RESTRICT, or NOACTION to CASCADE to prevent this behavior if not wanted and any updates will also update any references.

SET NULL is when a FK is delted and the references are changed to NULL

Foreign keys force indexing.

Looking up relationships with and without indexing

Will not allow

When running a JOIN the thing that you are looking for should be an indexed column.

Cannot create a foreign key on a non-indexed column or you will get an error message.

17. Housekeeping note

18. Searching for a keyword or phrase

reviews table with columns of id, productreviewed, reviewauthor, rating, description, and datereviewed

add a foreign key for productreviewed and reviewauthor

myneed to delete parentheses on TINYINT workbench may switch it to VARCHAR

INSERT INTO (column-names-here-comma-separated)
VALUES ()

Search for words that contain the word “amazing”
quick and dirty way that does a full table scan:
WHERE description LIKE ‘%amazing%’

The real way would be to click wrench on the reviews table and create new index for descrition column with a type of FULLTEXT. Now to leverage use the syntax SELECT * FROM reviews WHERE MATCH(description) AGAINST(‘amazing’)

19. Aggregate functions

formal definition adjective: Formed or calculated by the combo of separate units

formal definition noun: whole formed by combining several elements

moving to the petsfoodreference db and set as default schema

SELECT AVG(weight) FROM

other aggregate functions:
MIN, MAX, SUM,

Often don’t wan to perform agg ops on all records, just a subset. Enter the GROUP BY clause to see the avg weight for each species, not just one.

need to be careful when mixing and matching columns when using GROUP BY

The concept of what you can and can’t aks for can be confusing but just slow down and think is what we are asking for, does this makes sense.

SELECT AVG(weight) as ‘Average Weight’, species FROM animals GROUP BY species HAVING Average Weight >= 50

Note that you can’t just use the WHERE clause in the example above because it filters before the data is separated into their little subgroups. The HAVING clause filters after the placing in the subgroups.

20. Note about GROUP BY

If you find yourself trying to use GROUP BY and are receiving an error message you’ll want to pay extra attention to which columns you’re trying to select in relation to what you are grouping by.

For the most part, when we use GROUP BY we are only allowed to select either the thing we grouped by or aggregated values, however, if the thing we are grouping by is the primary key, MySQL will bend the rules and let us select any columns from that row with the primary key, since the key is guaranteed to be unique MySQL can assume it would make sense to be able to grab other non aggregated values from that same row. But if you group by something other than a unique key (like species for example) MySQL can’t make that assumption because there could be multiple rows with the species of “dog” and have different weights and birthdays, etc…

21. Left and Right Joins

can create a quick alias for fullname with a single letter.

SELECT * FROM animals a
INNER JOIN orders o ON a.id = o.userid

If no word in front of JOIN, MySQL assumes it is an INNER JOIN.

Want to include all animals, even if it never created an order? LEFT JOIN can show someone with 0 orders

Other situations require a RIGHT JOIN. Focuses on other circle in the venn diagram.

OUTER JOIN, Brad says he never uses in real life, MySQL didn’t even add it in the language although you can still accomplish the task in MySQL.

22. Full Outer Joins

Shown for academic purposes. perform each side of JOIN then combine.

UNION deletes duplicate rows but this may not always be desired so yu can use UNION ALL, (has nothing to do specifically with joins).

23. Practice and Review Part 1

Brad likes to start with SELECT * and then replace the asterisk later

Example 1 – 5 Highest Average Rated Products

SELECT * FROM reviews r

and then

JOIN products p ON r.productreviewed = p.id
Group By r.productreviewed

and then

SELECT p.name, AVG(r.rating) as ‘Average Score’ FROM reviews r
JOIN products p ON r.productreviewed = p.id
Group By r.productreviewed
ORDER BY Average Score DESC
LiMIT 5

Example 2 – The most commonly ordered products among dogs

SELECT * FROM orderlines

and then

SELECT p.name, COUNT(ols.id) as “The Count” FROM orderlines ols
JOIN orders o ON ols.orderid = o.id
JOIN animals a ON o.userid = a.id AND a.species = “dog”
JOIN products p ON old.productid = p.id
GROUP BY ols.productid
ORDER BY The Count DESC
LIMIT 5

24. Practice and Review Part 2

Example 3 – All email addresses for those that have every ordered oranges

SELECT a.email FROM animals a
JOIN orders o ON o.userid = a.id
JOIN orderlines ols ON o.id = ols.orderid AND ols.productid = 46

But there are duplicates so use the DISTINCT keyword

SELECT DISTINCT a.email FROM animals a
JOIN orders o ON o.userid = a.id
JOIN orderlines ols ON o.id = ols.orderid AND ols.productid = 46

Exmaple 4 – The 5 biggest spending animal users

SELECT * FROM orderlines old
JOIN orders o ON ols.orderid = o.id
JOIN aniamls a ON o.userid = a.id
JOIN products p ON ols.productid = p.id
GROUP BY a.id

But we don’t need all these columsn so

SELECT a.name, a.email, SUM(quantity * priceusd) as “Total Spent” FROM orderlines old
JOIN orders o ON ols.orderid = o.id
JOIN aniamls a ON o.userid = a.id
JOIN products p ON ols.productid = p.id
GROUP BY a.id
ORDER BY Total Spent DESC
LIMIT 5

Bonus Query – Count how many animal users start with each letter of the alphabet, (disregard the zeroes).

SELECT COUNT(id), Left(name, 1) as “First Letter” FROM animals
GROUP BY First Letter
ORDER BY First Letter

In the above example we were shown the LEFT() keyword that takes two parameters.

We shouldn’t pre-emptively memorize every function like LEFT that isavailable to us, just know where the reference material is or use google.

For example, in the first example query, If we want to round the average score. Google MySQL how to round.

AVG(r.rating) becomes
ROUND(AVG.(r.rating), 2)

25. Stored Functions

Keeping useful snippets of code in the database itself.

Stored Routines come in two flavors (Stored functions and stored procedures).

SELECT name, CONCAT(weight, ” Which is healthy”)
FROM petfoodsreference.animals

but we need a conditional so

SELECT name, CONCAT(weight, IF(weight >= 75, ” which is not healthy”, ” which is healthy”))
FROM petfoodsreference.animals

But we don’t want to re-type this all the time.

Workbench, Right Click and choose “create function” and workbench will give us a starter code snippet we can update:

DETERMINISTIC vs INTDETERMINISTIC

deterministic is basically, when given the same incoming values you expect the same outgoing result.

Example STored Function

CREATE FUNCTION weightLogic (theweight INT)
RETURNS varchar(100)
DETERMINISTIC
BEGIN
RETURN CONCAT(theweight, IF(theweight >= 75, ” which is not healthy”, ” which is healthy”))
END

Click apply on the bottom right

To leverage this we can call the function

weightLogic(weight)

A second example:

Any product that starts with the letter A should be discounted. Any product that starts with F should be free.

SELECT name, discountLogic(priceusd, name) FROM petfoodsreference

So to create the Stored Function:

CREATE FUNCTION discountLogic (theprice decimal(10,2), thename varchar(60))
RETURNS decimal(10, 2)
DETERMINISTIC
BEGIN

RETURN theprice * .2;
END

But to handle the necessary conditions we could have nested IF statement or use CASE

CREATE FUNCTION discountLogic (theprice decimal(10,2), thename varchar(60))
RETURNS decimal(10, 2)
DETERMINISTIC
BEGIN

RETURN CASE LEFT(thename, 1)
WHEN “A” THEN theprice * .5
WHEN “F” THEN 0
ELSE theprice
END;
END

26. Stored Procedures

Workbench on the left sidebar menu right click Stored Procedures and choose Create Stored Procedure

CREATE PROCEDURE Commonly Ordered (IN species varchar(100))
BEGIN
SELECT p.name, COUNT(ols.id) as “The Count” FROM orderlines ols
JOIN orders o ON ols.orderid = o.id
JOIN animals a ON o.userid = a.id AND a.species = species COLLATE utf8mb4_general_ci
JOIN products p ON old.productid = p.id
GROUP BY ols.productid
ORDER BY The Count DESC
LIMIT 5;
END

Click apply and leverage this stored procedure by clicking lightning bolt in left sidebar menu and complete pop-up if there is a parameter.

What’s the difference b/w the stored function and stored procedure? A Stored procedure doesn’t return anything it just executing the code it contains. Called in isolation.

What is a view, seen in the left sidebar of workbench? Right click and add new.

CREATE VIEW commonDogProducts AS SELECT p.name, COUNT(ols.id) as “The Count” FROM orderlines ols
JOIN orders o ON ols.orderid = o.id
JOIN animals a ON o.userid = a.id AND a.species = “dog”
JOIN products p ON old.productid = p.id
GROUP BY ols.productid
ORDER BY The Count DESC
LIMIT 5;

Save by clicking apply

In left menu of workbench, A view is sort of a virtual table, hover over third icon

In contrast a view with a stored procedure a stored procedure is way more flexible. Not limited to just SELECT.

View: Can only use SELECT and hard coded so less flexible.

An example of a more complex/useful Stored Procedure:

Creating an order is a cumbersome multiline step.

A stored procedure can expect an argument with JSON as a datatype.

CREATE PROCEDURE placeOrder(IN user INT, IN items JSON)
BEGIN
INSERT INTO orders (date, userid) VALUES (NOW(), user);
SET @thelast = LAST_INSERT_ID();
SET @i = 0;
WHILE @i < JSON_LENGTH(items) DO
INSERT INTO orderlines (productid, orderid, quantity) VALUES (JSON_EXTRACT(items, CONCAT(“$[“, @i, “].product”)), @thelast, JSON_EXTRACT(items, CONCAT(“$[“, @i, “].quantity”)));
SET @i = @i + 1;
END WHILE;
SELECT * FROM orderlines JOIN orders ON orderlines.orderid = orders.id AND orderlines.orderid = @thelast JOIN products ON orderlines.productid = products.id JOIN animals ON orders.userid = animals.id;
END

When a stored procedure can be useful.

A stored function cannot result with a full data set. It needs to boil down to a single scalar value not a bunch of columns and rows.

27. Getting the tools for the job: VsCode, Node,

28. Creating a new empty database and user

From workbench create new schema

Don’t want a user to have full root access so it can’t access other dbs.

29. Connecting the web app

Download ZIP from chapter resources on Udemy.

example-app folder in VSCode

npm install

npm start

Checkout “db.js” file to see how to connect to the database

Can see in browser at localhost:3000

Model>View>Controller project from FSJS from Scratch course. This MySQL course should focus on model files. The queries already written for us.

This project is meant to live on your local computer and not be deployed.

Bcrypt package used to hash password in the database.

30. Our Posts Table

WorkBench has a bug, FK value that points back but refernce table has agenerated virtual column then the GUI won’t let you point back to any of thise columns. The way around is to temporarily point to a different table then click apply and you before you coick apply then tweak the code with the necessary table.

All workbench is doing essentially is generating skelton code for us anyway.

Need to specify defaultschema in workbench.

Task #1 Create Post

Tweak create post to await db.execute(a, b)

We need the query to all be on a single line paste on JS string needing to be on one line

Security Note on the need for sanitizing data so not victim of SQL injection attack. Several way to solve but The answer is to create a prepared statement. Program logic separet from values. Query sent separate from values. This is why db.execute has two parameters.

Put a question mark as a placeholder in the first parameter. The second paramter is an array.

31. Reading, Updating, & Deleting Posts

Writing the queries

Task #2 – Update a Post

UPDATE posts SET title = ?, body = ? WHERE _id = ?, [incoming.title, incoming.body, incoming.requestedPostId]

Task #3 Find One Post By iD

Select * FROM posts p JOIN users u ON p.author = u._id WHERE p._id = 2

replace the asterisk

32. Solutions for Task #1 – #6 Within Post.js

1

db.execute(“INSERT INTO posts (title, body, author, createdDate) VALUES(?, ?, ?, ?)”, [incoming.title, incoming.body, incoming.author, incoming.createdDate])

2

db.execute(“UPDATE posts SET title = ?, body = ? WHERE _id = ?”, [incoming.title, incoming.body, incoming.requestedPostId])

3

db.execute(“SELECT p.title, p.body, p._id, p.author, p.createdDate, u.username, u.avatar FROM posts p JOIN users u ON p.author = u._id WHERE p._id = ?”, [id])

4

db.execute(“SELECT p.title, p.body, p._id, p.author, p.createdDate, u.username, u.avatar FROM posts p JOIN users u ON p.author = u._id WHERE p.author = ? ORDER BY createdDate DESC”, [authorId])

5

db.execute(“SELECT count(_id) as posts FROM posts WHERE author = ?”, [id])

6

db.execute(“DELETE FROM posts WHERE _id = ?”, [postIdToDelete])

33. Searching for Posts

create an index titlebodysearch that is of type Full text index and is a compound index (select multiple columns) so you can search for word in both fields.

Don’t want to make too many indexes.

wokrbench query

SELECT * FROM posts WHERE MATCH(title, body) AGAINST(“again”)

VSCode query

Task #7
Copy from task #3 and make some minor changes.

34. Follows Table

Don’t need auto-incremented followed id

followedid and authorid, both priamary key (this is called composite primary key).

Set up foreign keys. Bug in workbench again. Use dif table then change.

integrity checks for mySQL or programming logic.

35. Follow Queries

9 tasks for the profile page. The SQL query with JOIN are the interesting ones.

36. Solutions for Follow Queries Tasks

1. db.execute(“SELECT * FROM users WHERE username = ?”, [this.followedUsername])

2. db.execute(“SELECT * FROM follows WHERE followedId = ? AND authorId = ?”, [this.followedId, this.authorId])

3. db.execute(“INSERT INTO follows (followedId, authorId) VALUES (?, ?)”, [this.followedId, this.authorId])

4. db.execute(“DELETE FROM follows WHERE followedId = ? AND authorId = ?”, [this.followedId, this.authorId])

5. db.execute(“SELECT * FROM follows WHERE followedId = ? AND authorId = ?”, [followedId, visitorId])

6. db.execute(“SELECT username, avatar FROM follows JOIN users ON follows.authorId = users._id WHERE followedId = ?”, [id])

7. db.execute(“SELECT username, avatar FROM follows JOIN users ON follows.followedId = users._id WHERE authorId = ?”, [id])

8. db.execute(“SELECT COUNT(followedId) as followers FROM follows WHERE followedId = ?”, [id])

9. db.execute(“SELECT COUNT(followedId) as following FROM follows WHERE authorId = ?”, [id])

37. Homepage Feed (Posts from users you follow)

SELECT * FROM posts JOIN users On posts.author = users._id WHERE author = 1 OR author = 2

but we can use the IN clause instead of the OR clause so we can get a little more power and flexibility for dynamic values.

SELECT * FROM posts JOIN users On posts.author = users._id WHERE author IN (1, 2)

Can put a query inside the IN clause – mind blown

SELECT * FROM posts JOIN users On posts.author = users._id WHERE author IN (SELECT followedId FROM follows WHERE authorId = 3)

Only get columns you trule need so swap SELECT * for
SELECT posts._id, title, createdDate, username, avatar

Copy from workbench into models/Posts.js

Update db.execute

The newest posts need to be on top so add ORDER BY createdDate DESC to the end of the query

38. Create Tables Programmatically

in “db.js” file you can see there is a shapeDatabase function that automaticlaly creates a users table if it doesn’t already exist.

function shapeDatabase() {
db.query(CREATE TABLE IF NOT EXISTS users ( _id int NOT NULL AUTO_INCREMENT, username varchar(45) DEFUALT NULL, // etc... ))
}

It’s nice that MySQL really just boils down to simple text commands

A collection of SQL scripts that creates tables and rows is what powers some websites like message board, forum, wordpress blogs, etc.

The code for a user table already exists so we will add the code to create a posts and follows table

Copy/Paste crate statement from workbench and you need to delete backticks with control+d VSCODE shortcut and add IF NOT EXISTS after CREATE TABLE

Delete line ENGINE = yada yada as this is not needed.

Powerful to distribute software with this empty skeleton of db structure.

39. Your new skills are in demand and portable

Controlling MySQL is incredibly powerful and writing the code manually instead of with workbench is a thing. Workbench is a good way to learn the basic MySQL features/concepts.

Workbench is not very loved.

Taking off workbench training wheels.

mysqlsh gets you into the shell

enter “\sql” to enter sql syntax without quotes. Enter commands to view and use databases.

To instal the shell, go to mysql.com, find the link to MySQL GPL community downloads and click on the shell and choose OS.

to use go to command line and type mysqlsh

most common way people are exposed to MySQL : phpMyAdmin which is sort of a web-based version of workbench.

Importing and exporting is the most common task.