MySQL Bootcamp note

This is the note of SQL Bootcamp on Udemy.

• Section 2: Getting Started
• Section 3: Creating Database and Tables
• Section 4: Inserting data
• Section 5: CRUD commands
• Section 7: The world of string function
• Section 8: Refining Our Selection
• Section 9: The Magic of Aggregate Functions
• Section 10: Data Types
• Section 11: Logical Operators
• Section 12: One to Many
• Section 13: Many to Many

Section 2: Getting Started

What Is a Database?

1. A collection of data —e.g. A phone book
2. A method for accessing and manipulating that data

Database vs. Database Management System(DMS or RB DMS)

Diagram — 很多人會把 Database management system 跟真正的資料庫混為一談。

很多被人稱為資料庫(Database)的東西，實質上都是Database management system e.g. MySQL

What Is A Database? A structured set of computerized data with an accessible interface

SQL vs. MySQL

• SQL(Structured Query Language): is the language we use to “talk” to our database. e.g. “Find all users”, “Find all users who are 18 years old”, “Delete every single user”
• Working with MySQL is primarily Writing SQL

They share this language(SQL). They are alight differences in syntax.

Two Takeways

1. Once you learn SQL, it’s pretty easy to switch to another DB that uses SQL
2. What make databases(DBMS) unique are the features they offer, not the language itself

安裝tips：推薦用 GoormIDE，在跟課時都用這個，課程結束後可以自己安裝。（但因為我用了有出問題，所以都直接裝在電腦上了）

MAC Installing

open ./.zshrc
export PATH="/Users/chaoluchiao/opt/anaconda3/bin:$PATH"
export PATH=${PATH}:/usr/local/mysql/bin/
ERROR 1045 (28000): Access denied for user ‘chaoluchiao’@’localhost’ (using password: NO) > mysql -u root -p

一些指令：

• mysql-ctl start/ stop 啟動 server
• mysql-ctl cli (head to command line interface) — 算是個入口，密碼沒有就直接按空白鍵
• exit(back to terminal) = Ctrl + C

Section 3: Database

Creating Database

Same accounts need to separate, or leading to crossover

show databases   # list the current databases that exist and SQL server
CREATE DATABASE <name>;  # 不要命名有空格的名字

Dropping Database(Delete)

DROP DATABASE <name>;

• 沒有分號程式就不會結束

Using Database

USE <database name>;

• tells MySQL which database we want to be working with.

SELECT database();

• tell you the currently used database
• 如果刪除了正在使用的資料庫，就會回傳NULL

Introduction to Tables

• A database is just a bunch of tables.(In a relational database, at least)
• Tables hold the data.

Data types

• 需要有清楚並一致的資料型態

• int — a whole number
• varchar — A variable-Length String(vs. char — 需要固定格式) e.g. varchar(50) 最多五十字

Creating table

CREATE TABLE tablename
  (
    column_name data_type,
    column_name data_type,
    ...
  );
e.g.
CREATE TABLE catsa
  (
    name VARCHAR(100),
    age INT
  );

How do we know it worked?

1. SHOW TABLES;
2. SHOW COLUMNS FROM <tablename>;
3. DESC <tablename>; (describe)

Dropping tables

• DROP TABLE <tablename>;

Section 4: Inserting Data

Multiple insert

INSERT INTO tablename
            (column_name, column_name)
VALUES (value, value)
      ,(value, value)
      ,(value, value);

Super Quick Intro To SELECT

SELECT * FROM cats; # Show data

MySQL Warnings

SHOW WARNINGS;

NULL and NOT_NULL

• NULL: The value is unknown. It doesn’t mean zero.

The NULL column ‘YES’ means it allows NULL values.

So we could:
INSERT INTO cats()  # insert nothing > NULL
VALUES ();

> How to avoid this?

CREATE TABLE cats2(
   name VARCHAR(50) NOT NULL,
   age  INT NOT NULL
)
Then, if you type:
INSERT INTO cats2(name)
VALUES ("Texas");
> It will return an error, and set the value default to 0.
> The VARCHAR type will be empty string.

Setting Default Values

The column “Default” means default value

CREATE TABLE cat3(
  name VARCHAR(100) DEFAULT 'unnamed',
  age  INT DEFAULT 99
);

We could still manually set something to NULL.

A Primer On Primary Keys — Unique Identifier

CREATE TABLE unique_cats (cat_is INT NOT NULL
                       ,name VARCHAR(100)
                       ,age INT
                       ,PRIMARY KEY(cat_id));

PRI: Primary key

auto_increment: 可以自動分配ID

Section 5: CRUD command — Create, Read, Update, Delete

SELECT — What columns do you want?

WHERE — Like IF statement

SELECT * FROM cats WHERE age = 4;

Aliases

SELECT cat_id AS 'id', name AS 'cat_name' FROM cats;

UPDATE Command(How do we alter existing data?)

UPDATE cats SET breed = 'Shorthair'
WHERE breed = 'Tabby';
UPDATE cats SET age = 14
WHERE name = 'Misty';

• Make sure you target the data before update(Try SELECTing before UPDATE)

DELETE

DELETE FROM cats WHERE name = 'Egg';  # ID 不會更動
DELETE FROM cats;   # DELECT all element, but not drop the table

Section 7: The World of String Function

Running SQL Files

source file_name.sql      # 執行檔案，注意你的位置

CONCAT — Combine Data for Cleaner Output

SELECT
CONCAT(author_fname, ' ', author_lname) AS 'FULL NAME'
FROM books;

CONCAT_WS — CONCAT with separator

SELECT
CONCAT_WS(‘ — ‘, title, author_fname, author_lname)
FROM books;

SUBSTRING — Works with part of string = SUBSTR()

SELECT SUBSTRING('Hello World', 1, 4)    # 從1開始數，而不是0
>>> Hell
SELECT SUBSTRING('Hello World', 7)
>>> World
# 有時候遇到字串，可以用 double quote
SELECT CONCAT(SUBSTRING(title, 1, 10), '...') AS 'short title' FROM books;

REPLACE — REPLACE part of string

SELECT REPLACE(‘Hello world’, ‘l’, ‘7’)   # 替換掉所有的 l
# case sensitive

REVERSE

SELECT REVERSE('Word')

CHAR_LENGTH

SELECT CHAR_LENGTH('Hello world')
>>> 11

UPPER() and LOWER() — Change A string case

SELECT UPPER('Hello World')
>>> HELLO WORLD

Reminder

This works:

SELECT UPPER(CONCAT(author_fname, ‘ ‘, author_lname)) AS “full name in caps” FROM books;

While this does not:

SELECT CONCAT(UPPER(author_fname, ‘ ‘, author_lname)) AS “full name in caps” FROM books;

UPPER only takes one argument and CONCAT takes two or more arguments, so they can’t be switched in that way.

Section 8: Refining Our Selection

DISTINCT

SELECT DISTINCT author_lname FROM books;   # 選取欄位但避免重複值，文字或數字都可以
SELECT DISTINCT CONCAT(author_fname,' ', author_lname) FROM books;  # 全名
SELECT DISTINCT author_fname, author_lname FROM books;    # 不同欄位

ORDER BY

SELECT author_lname FROM books ORDER BY author_lname;
SELECT author_lname FROM books ORDER BY author_lname DESC;   # 反向排序
# 不管數字或是字母都可以
SELECT title, author_fname, author_lname FROM books ORDER BY 2;   # 由第二個元素(author_fname)去進行排序
SELECT authot_fname, author_lname FROM books ORDER BY author_lname, author_fname;   # 先排序last name，再來first name

LIMIT

SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 0, 5;     # 選前五條，從0開始數
SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT ㄅ10, 1;  # 從第十條開始，選一條
SELECT * FROM tbl FROM 95, 1844582749823749;    # 從九十五條開始選，一路選到結尾（用一個很大的數字代替）

LIKE — Better searching

• WHERE: exact matches
• LIKE: contain

WHERE author_fname LIKE '%da%'  # fname中「包含」da的所有字串
# %: wildcard
WHERE author_fname LIKE 'da%'  # fname中由da開頭的所有字串
WHERE stock_quantity LIKE '____'      # 四個下底線，想要找到stock_quantity有四碼長的（才會有四個下底線）, for counting digits
如果要搜尋書本名有"%"的呢？
WHERE title LIKE '%\%%'

Some code

SELECT CONCAT(title, ' - ', released_year) AS summary FROM books ORDER BY released_year DESC LIMIT 3 
SELECT UPPER(CONCAT('my favorite author is ', author_lname)) FROM books  ORDER BY author_lname;

Section 9: The Magic of Aggregate Functions

COUNT — e.g. How many books?

SELECT COUNT(*) FROM books;
SELECT COUNT(DISTINCT author_fname) FROM books;    # 如果有重複的first name可以這樣數
SELECT COUNT(DISTINCT author_lname, author_fname) FROM books;  # 如果有重複的名字可以這樣數
SELECT COUNT(*) FROM books WHERE title LIKE '%the%'

GROUP BY — summarizes or aggregates identical data into single rows

SELECT title, author_lname FROM books

SELECT title, author_lname FROM books 
GROUP BY author_lname;

SELECT author_lname, COUNT(*)       # 不會數所有rows，而是分組下的rows有多少
FROM books GROUP BY author_lname

SELECT title, author_fname, author_lname, COUNT(*) 
FROM books GROUP BY author_lname;
# 會造成資料搜尋的錯誤，如果first name不一樣但last name一樣會被分到同組，看起來就像同一個作者寫了兩本書
SELECT CONCAT('In ', released_year,' ', COUNT(*), ' book(s) released') AS year FROM books GROUP BY released_year;
# 這樣回傳的數據就會是正確的

MIN / MAX

SELECT MIN(released_year) FROM books;
# What if we want to know the longest books of title?
SELECT Max(pages), title FROM books;     # 錯誤的值！
  
解決方法：SUBQURIES
SELECT title, pages FROM books
WHERE pages = (SELECT Min(pages) FROM books);
# 小括弧的會優先執行，但執行效率會偏低，因為他是個別執行
SELECT * FROM books
ORDER BY pages ASC LIMIT 1;    # ascending
# 用這個效率會比較高，同上面一樣效果

MIN / MAX with GROUP BY

E.g. Find the year each author published their first book
SELECT author_fname, author_lname, Min(released_year) FROM books
GROUP BY author_lname, author_fname;    # 這樣可以區分出所有作者的名字
E.g. find the longest page count for each author
SELECT 
  CONCAT(author_fname, ' ', author_lname) AS author,
  MAX(pages) AS 'longest book'
FROM books
GROUP BY author_lname, author_fname;

SUM — Add things together

SELECT SUM(pages) FROM books;
e.g. sum all pages each author has written
SELECT author_fname, author_lname, Sum(pages) FROM books
GROUP BY author_lname, author_fname;

AVG — Average

SELECT AVG(pages) FROM books;
e.g. calculate the average stock quantity for books released in the same year
SELECT released_year, AVG(stock_quantity) FROM books
GROUP BY released_year;
SELECT author_fname, author_lname, AVG(pages) FROM books
GROUP BY author_lname, author_fname

Some code

SELECT released_year AS year, COUNT(*) as 'books', AVG(pages) AS 'avg books' FROM books
GROUP BY released_year;

Section 10: Data Types

CHAR and VARCHAR — Text or string

CHAR

• Fixed length, longest is 255.
• CHAR(3) -> Only 3 characters allowed
• faster for fixed length text, e.g. SEX: M/F, Yes/No Flags: Y/N
• 如果字數不足會自動在右側補上空白，過多則會砍掉

VARCHAR

char 無論如何都是固定尺寸的 bytes

DECIMAL

DECIMAL(Total Number of Digits, Digits After Decimal)
e.g. DECIMAL(5, 2)    >>> Maximum number: 999.99
Warning: VALUE(2348739)  >> 會顯示這個位數的最大值(999.99)
Warning: VALUE(1.9999)   >> 會顯示 2.00（四捨五入）

DECIMAL vs. FLOAT or DOUBLE

FLOAT and DOUBLE

• Store larger numbers using less space, but it comes at the cost of precision
• 盡量都用 DECIMAL，除非是大數字且你知道精確度不是那麼重要

8877665544.45 >> 8877670000

DATE, TIME and DATETIME

DATE: YYYY-MM-DD

TIME: HH:MM:SS

DATETIME: YYYY-MM-DD HH:MM:SS, the most use

CURDATE, CURTIME, and NOW

CURDATE() - gives current date
CURTIME() - gives current time
NOW() - give current datetime
# 前面要加 SELECT

Formatting dates

SELECT name, DAY(birthdate) FROM people;    # extract the day
SELECT name, DAYNAME(birthdate) FROM people;    # extract the day name(e.g. monday)
SELECT name, DAYOFYEAR(birthtime) FROM people;    # NULL
SELECT name, MONTHNAME(birthdt) FROM people;   # extract month name(e.g. april)
"2017-04-21"   >  "April 21st 2017"
e.g. CONCAT(MONTHNAME(birthdate), ' ', DAY(birthdate), ' ', YEAR(birthdate))
Better way:
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
>> 'Sunday October 2009'

Date Math

SELECT DATEDIFF('2007-12-31', '2007-12-30')     # Date difference
>> 1
SELECT DATEADD('2000-12-31 23:59:59', INTERVAL 1 SECOND);
>> '2001-01-01 00:00:00'
or birthdt +/- INTERVAL 1 SECOND

TIMESTAMPS — Storing metadata about when something is created or updated

Datetime vs. Timestamp

• Timestamp 的 range 較小，但位元數也比 Datetime 小
• TIMESTAMP is used for things like meta-data about when something is created or updated.

CREATE TABLE comment2(
    content VARCHAR(100),
    changed_at TIMESTAMP DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP
);       
# 可以追蹤這個物件的時間（比如說更新這個物件他的時間就會被更新）

Section 11: Logical Operators

• Not equal: !=
• NOT LIKE

SELECT title FROM books
WHERE title NOT LIKE 'W%';

• Greater than: >
• Greater than or equal to: >=
• Less than: <
• Less than or equal to: <=

SELECT 99 > 1;
# 得到 1 為回傳值，True 的意思SELECT 1 > 1;
>> 0(False)SELECT 'b' > 'a';
>> 1SELECT 'a' > 'b';
>> 0SELECT 'A' > 'a';
>> 0SELECT 'A' >= 'a';
>> 1
# In SQL, 'A' is equal to 'a'SELECT 'h' < 'p';
>> 1

• AND / &&
• OR / ||

SELECT * FROM books
WHERE (author_lname='Eggers' AND released_year > 2010) OR title LIKE '%novel%';-- AND can be replaced with &&, but it'll be removed in the future version
-- OR can be also replaced with ||, but it'll be removed in the future version
-- 可以串接多個條件

• BETWEEN x AND y
• NOT BETWEEN x AND y

SELECT title, released_year FROM books
WHERE released_year BETWEEn 2004 AND 2015
-- This AND isn't equal to &&

• A note about comparing dates

CAST('2017-05-02' AS DATETIME);
>> '2017-05-02 00:00:00'
-- 但其實SQL足夠聰明，能夠直接判斷，不一定要casting

• IN

SELECT title, author_lname FROM books
WHERE author_lname IN ('Carver', 'Smith', 'Lahiri');same as SELECT title, author_lname FROM books
WHERE author_lname='Carver' OR
      author_lname='Lahiri' OR
      author_lname='Smith');

• NOT IN

SELECT title, released_year FROM books
WHERE released_year >= 2000
AND released_year % 2 != 0;

• Case statement(conditional statement)

SELECT title, released_year,
       CASE
         WHEN released_year = 2000 THEN 'Modern Lit'
         ELSE '20th Century Lit'
       END AS GENRE
FROM books;

上面code的結果

SELECT title, stock_quantity,
       CASE
         WHEN stock_quantity BETWEEN 0 AND 50 THEN '*'
         WHEN stock_quantity BETWEEN 51 AND 100 THEN '**'
         ELSE '***'
       END AS GENRE
FROM books;

Some code

SELECT author_fname, author_lname,
       CASE
          WHEN COUNT(*) = 1 THEN '1 book'
          ELSE CONCAT(COUNT(*), ' books')
       END AS COUNT
FROM books
GROUP BY author_lname, author_fname;

Section 12: One to Many

• Real world data is messy and interrelated.

Types of Data Relationship

1. One to One Relationship — e.g. customers and orders
2. One to Many Relationship — e.g. books and reviews
3. Many to Many Relationship — e.g. books and authors

One to Many — E.g. Customers and Orders

Not a good way

customer_id/ order_id: primary key — unique

Foreign Keys

Foreign key

CREATE TABLE customers(
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100)
);
CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(8,2),
    customer_id INT,
    FOREIGN KEY(customer_id) REFERENCES customers(id)
);-- INSERT INTO orders(order_date, amount, customer_id)
-- VALUES('2012/12/21', 99.99, 98)
-- 不會運作，因為不存在98號ID

Cross JOIN

-- Find the id of the customers where last name is George
SELECT * FROM customers WHERE last_name='George'
SELECT * FROM orders WHERE customer_id = 1same asSELECT * FROM orders WHERE
    (
        SELECT id FROM customers
        WHERE last_name='George'
    ) -- equal to 1-- join: take the data from one, and take the data from another and stick them together-- Cross Join Craziness(Useless)
SELECT * FROM customers, orders-- 交叉連接為兩個資料表間的笛卡兒乘積 (Cartesian product)，兩個資料表在結合時，不指定任何條件，即將兩個資料表中所有的可能排列組合出來，以下例而言 CROSS JOIN 出來的結果資料列數為 3×5=15 筆，因此，當有 WHERE、ON、USING 條件時不建議使用。

INNER JOIN — What’s actually relevant

-- Where user id is equal to customer id
-- dot means in that table-- IMPLICIT INNER JOIN
SELECT * FROM customers, orders 
WHERE customer.id = orders.customer_id-- EXPLICIT INNER JOIN (more conventional, do this)
-- 沒有寫 INNER 就是INNER JOIN
SELECT * FROM customers
INNER JOIN orders
  ON customer.id = orders.customer_id-- ARBITRARY JOIN - don't do this!
SELECT * FROM customers
JOIN orders ON customers.id = orders.id

LEFT JOIN

-- LEFT JOIN
SELECT first_name, last_name, order_date, IFNULL(SUM(amount), 0)
FROM customers
LEFT JOIN orders
  ON customer.id = orders.customer_id
GROUP BY customer.id;

GROUP BY customer.id

IFNULL(SUM(amount), 0)

RIGHT JOIN

B: orders table

-- RIGHT JOIN
-- These code is same as inner join, because we don't have any order that doesn't match customer id
SELECT * FROM customers
RIGHT JOIN orders
  ON customers.id = orders.customer_id

• RIGHT JOIN and LEFT JOIN are actually the same.
• Some environment doesn’t support the RIGHT JOIN.
• 在刪除時要從有 foreign keys(orders) 的 table 開始刪除，否則會無法刪除

Section 13: Many to Many

Basics

• Some example: books, authors / blog post, tags / students, classes

TV shows reviews

Example

Section 14: Instagram clone project

Relationship

CASE 的另一種寫法
IF(condition, True value, False, value) AS title