MySQL Crash Course Notes

- deprecation of mysql query browser for mysql workbench
- use of mycli
- p65 tips for using wildcards, avoid wildcard at start of string - performance
- p91 SOUDNEX, never heard of this before
-
- p102 COUNT(*) all rows, COUNT(column) only rows with NON-NULL values for column
- p113 "WITH ROLLUP"

- p114 * WHERE does not work with groups (WHERE doesn't know what a group is)
  - HAVING <-- this works on groups, I didn't previously know this
  - WHERE filters ROWS, HAVING filters GROUPSA
    - p115 WHERE filters before data is grouped, HAVING filters after data is grouped

# Get me all the customers that have at least 2 orders
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

---
p115 book suggests using WHERE clause to get all orders past 6 months, but doesn't provide example.
So I figured it out myself...
# Found this documentation on DATE_SUB/SUBDATE function
# https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_subdate
- The DATE_SUB function is not listed in the functions on page 93
- According to W3 schools it's bene around since MySQL 4
`SELECT DATE_SUB(CURDATE(), INTERVAL 9 MONTH) AS 9_months_ago`
# Getting the date 9 months ago
```
$ date
Fri Jan 31 19:16:15 CST 2020
$ echo "SELECT DATE_SUB(CURDATE(), INTERVAL 9 MONTH) AS 9_months_ago;" | mysql -u root -ppassword -h 127.0.0.1 crashcourse 2>/dev/null
9_months_ago
2019-04-30
```
# The Query I came up with (example not provided)
# Since all the example data orders are from 2005, I can't use
# 9 months so I use 20 years
```
SELECT cust_id, COUNT(*) AS orders
FROM orders
WHERE DATE(order_date) > DATE_SUB(CURDATE(), INTERVAL 20 YEAR)
GROUP BY cust_id
HAVING COUNT(*) >= 2;
```
---

p117 has good comparison table of ORDER BY and GROUP BY
RULE: anytime GROUP BY, use ORDER BY

---
p123
```
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
                    FROM orderitems
                    WHERE prod_id = 'TNT2');
```
This returns:
cust_id
10001
10004
My thought here was, what if customer 10001 had 2 orders that
had the item TNT2. To test this I looked at the sample data and
saw customer 10001 has another order, 20009, in `orders`. That
order doesn't contain an orderitem of TNT2 but we can pretend it does:
```
SELECT cust_id
FROM orders
WHERE order_num IN (20005,20007,20009); //20005, 20007 belong to 10001 and 10004 respectively
```
returns:
cust_id
10001
10001
10004

is the solution to use DISTINCT? Will the book mention this in the next few pages?
Interestingly, when using this as a subquery to a SELECT on customers, customer data
is not duplicated
```
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (10001,10001,10004);

cust_name       cust_contact
Coyote Inc.     Y Lee
Yosemite Place  Y Sam
```

p129 shows using subquery to calculate a field, mentions this might not always
be post efficient. Notes later chapters will discuss.

---
p 143 aliases for table names, example uses `AS`: tablename AS tn
  - I never really used this, I wonder if dropping AS has always been supported
three other joins, self join, natural join, outer join -- this is where my need
for review comes in





p145 To think about self join, I literally printed out two copies of the table,
placed them side by side, and worked my way down the line with a pen to my screen
testing like the DBMS
ps <3 heredoc
````
cat <<- EOF | mysql -u root -ppassword -h 127.0.0.1 -v --table crashcourse
SELECT prod_id, vend_id, prod_name, prod_id, vend_id, prod_name
FROM products;
EOF
```
--------------
SELECT prod_id, vend_id, prod_name, prod_id, vend_id, prod_name FROM products
--------------

+---------+---------+----------------+---------+---------+----------------+
| prod_id | vend_id | prod_name      | prod_id | vend_id | prod_name      |
+---------+---------+----------------+---------+---------+----------------+
| ANV01   |    1001 | .5 ton anvil   | ANV01   |    1001 | .5 ton anvil   |
| ANV02   |    1001 | 1 ton anvil    | ANV02   |    1001 | 1 ton anvil    |
| ANV03   |    1001 | 2 ton anvil    | ANV03   |    1001 | 2 ton anvil    |
| DTNTR   |    1003 | Detonator      | DTNTR   |    1003 | Detonator      |
| FB      |    1003 | Bird seed      | FB      |    1003 | Bird seed      |
| FC      |    1003 | Carrots        | FC      |    1003 | Carrots        |
| FU1     |    1002 | Fuses          | FU1     |    1002 | Fuses          |
| JP1000  |    1005 | JetPack 1000   | JP1000  |    1005 | JetPack 1000   |
| JP2000  |    1005 | JetPack 2000   | JP2000  |    1005 | JetPack 2000   |
| OL1     |    1002 | Oil can        | OL1     |    1002 | Oil can        |
| SAFE    |    1003 | Safe           | SAFE    |    1003 | Safe           |
| SLING   |    1003 | Sling          | SLING   |    1003 | Sling          |
| TNT1    |    1003 | TNT (1 stick)  | TNT1    |    1003 | TNT (1 stick)  |
| TNT2    |    1003 | TNT (5 sticks) | TNT2    |    1003 | TNT (5 sticks) |
+---------+---------+----------------+---------+---------+----------------+
when the book started touching on self joins this is when I paused to start
looking at youtube videos and blog posts talking about visualizing self joins
# https://www.mysqltutorial.org/mysql-self-join/ 
great tutorial from MySQL

typo on page 147 (OI)

Went to bed, woke up again (Sunday morning) re-reviewed self-joins and somehow
I feel like it makes a little bit more sense now.

```
SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.ved_id
  AND p2.prod_id = 'DTNTR'; <-- there will be only 1 record from p2 compared to every row in p1*
```

p 157 haven't written a UNION/UNION ALL query in a long time...
* UNION ALL does what can't be done w/ multiple WHERE clauses

----
page 167 example is outdated
example query suggests aliasing generated column to 'rank' -- in MySQL 8 'RANK' is a reserved word
# https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
"Window functions.  MySQL now supports window functions that, for each row from
a query, perform a calculation using rows related to that row. These include
functions such as RANK(), LAG(), and NTILE(). In addition, several existing
aggregate functions now can be used as window functions (for example, SUM() and
AVG()). For more information, see Section 12.21, “Window Functions”."


p181 INSERT LOW_PRIORITY INTO <-- low priority interesting

p184 INSERT SELECT is interesting, how can I grab just 1 value from a table and insert it with
other app provided values

p191 could use mention of logical deletes

p200 book says default value can't be an expression/function -- this is no longer the case in MySQL 8
# https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html

p201 book says default engine is MyISAM -- after 5.5 it's InnoDB
* important to note that foreign keys can't span engines

p210 views seem interesting, obvious use case is to limit access to certain database columns
does the book ever mention: "SHOW CREATE VIEW ___ "

P216 lists all the things that will make a view non-updatable

p226 seems strange to introduce DECIMAL(8,2) without explaining precision and scale

p299 SHOW PROCEDURE STATUS

p236, cursors seem interesting. No company I've ever worked at has bothered to use
stored procedures or cursors. (I've seen views in the wild)
`DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET DONE=1;` oof that's stupidly complex
  - On my sidequest, I found: https://www.mysqltutorial.org/mysql-cursor/
  - "asensitive" vs "insensitive" -- mysql is asensitive
  - web uses `NOT FOUND` instead of `SQLSTATE '02000'`
  - website example creates a single semicolon separated string of emails from a table of emails

p242 https://www.mysqltutorial.org/mysql-triggers.aspx
statement level vs row level triggers, mysql only row level
BEFORE INSERT triggers can modify data about the be inserted

p244 example trigger doesn't work, error 1415
Can kinda get around it using a variable
```
   CREATE TRIGGER neworder AFTER INSERT ON orders
   FOR EACH ROW SELECT NEW.order_num INTO @somevar;

   later...
   SELECT @somevar;
```

\G; at end of queries to see things vertically is great

p255 SET autocommit=0; - book does not make it clear if this is required to use transactions in general...
# https://www.wired.com/2010/02/manage_transactions_in_mysql_-_lesson_2/
Blog post makes it clear it is required