MySQL入門筆記

MySQ 語法

---

DataBase和Table

刪掉database

drop database "database名稱";

創建 database

create database "database名稱";

使用這個 database

use "database名稱";

基本的 CRUD

導入數據

source /Users/joe/Downloads/data.sql;

data.sql
內容:

CREATE TABLE employee(
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  title VARCHAR(100) DEFAULT NULL,
  salary DOUBLE DEFAULT NULL,
  hire_date DATE NOT NULL,
  notes TEXT,
  PRIMARY KEY (id)
);

INSERT INTO employee (first_name, last_name, title, salary, hire_date) VALUES
    ('Robin', 'Jackman', 'Software Engineer', 5500, '2001-10-12'),
    ('Taylor', 'Edward', 'Software Architect', 7200, '2002-09-21'),
    ('Vivian', 'Dickens', 'Database Administrator', 6000, '2012-08-29'),
    ('Harry', 'Clifford', 'Database Administrator', 6800, '2015-12-10'),
    ('Eliza', 'Clifford', 'Software Engineer', 4750, '1998-10-19'),
    ('Nancy', 'Newman', 'Software Engineer', 5100, '2007-01-23'),
    ('Melinda', 'Clifford', 'Project Manager', 8500, '2013-10-29'),
    ('Jack', 'Chan', 'Test Engineer', 6500, '2018-09-07'),
    ('Harley', 'Gilbert', 'Software Architect', 8000, '2000-07-17');

查看這個 database 有哪些 table

show tables

+----------------+
| Tables_in_demo |
+----------------+
| employee       |
| movie          |
| test           |

描述 table 有哪些屬性 desc 表格名稱

desc movie;

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| title          | varchar(100) | NO   |     | NULL    |                |
| title_year     | int(11)      | NO   |     | NULL    |                |
| director_name  | varchar(50)  | NO   |     | NULL    |                |
| actor_1_name   | varchar(50)  | NO   |     | NULL    |                |
| actor_2_name   | varchar(50)  | NO   |     | NULL    |                |
| duration       | int(11)      | NO   |     | NULL    |                |
| country        | varchar(50)  | NO   |     | NULL    |                |
| content_rating | varchar(10)  | NO   |     | NULL    |                |
| gross          | bigint(20)   | NO   |     | NULL    |                |
| imdb_score     | float        | YES  |     | 0       |                |
+----------------+--------------+------+-----+---------+----------------+

查看表格所有數據

select * from employee;

+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  1 | Robin      | Jackman   | Software Engineer      |   5500 | 2001-10-12 | NULL  |
|  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5100 | 2007-01-23 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  |
|  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |
|  9 | Harley     | Gilbert   | Software Architect     |   8000 | 2000-07-17 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+

WHERE 語句過濾

select * from employee where last_name="Clifford";

+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+

WHERE 兩個條件過濾

 select * from employee where last_name="Clifford" and salary=6800;

+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+

新增

INSERT INTO yourTABLEname (column1,column2)VALUES(新增項目 1,新增項目 2);
範例:

INSERT INTO employee (first_name, last_name, title, salary, hire_date) VALUES
    ('Robin', 'Jackman', 'Software Engineer', 5500, '2001-10-12');

更新

update 語句更新數據(先找到要更改資料，再更改比較不會亂掉)
!!!注意 update from “table 名子”; 是對整個 table 進行操作
update employee set salary=10000,(預更改 coulmn = “更改值”) where title=”Software Architect”;

更新成功

select * from employee where title="Software Architect";

+----+------------+-----------+--------------------+--------+------------+-------+
| id | first_name | last_name | title              | salary | hire_date  | notes |
+----+------------+-----------+--------------------+--------+------------+-------+
|  2 | Taylor     | Edward    | Software Architect |  10000 | 2002-09-21 | NULL  |
|  9 | Harley     | Gilbert   | Software Architect |  10000 | 2000-07-17 | NULL  |
+----+------------+-----------+--------------------+--------+------------+-------+

刪除

delete 語句刪除數據
!!!注意 delete from “table 名子”; 是對整個 table 進行操作

刪除例句

delet from employee where title ="Software Architect";

SQL 字符串相關處理方法

字串拼接 SUBSTRING

字串拼接 CONCAT(“A”,”B”)

select concat(first_name,",",last_name) from employee;

+----------------------------------+
| concat(first_name,",",last_name) |
+----------------------------------+
| Robin,Jackman                    |
| Taylor,Edward                    |
| Vivian,Dickens                   |
| Harry,Clifford                   |
| Eliza,Clifford                   |
| Nancy,Newman                     |
| Melinda,Clifford                 |
| Jack,Chan                        |
| Harley,Gilbert                   |
+----------------------------------+

字串合併取名為 fullname

 select concat(first_name,",",last_name) as fullname from employee;

+------------------+
| fullname         |
+------------------+
| Robin,Jackman    |
| Taylor,Edward    |
| Vivian,Dickens   |
| Harry,Clifford   |
| Eliza,Clifford   |
| Nancy,Newman     |
| Melinda,Clifford |
| Jack,Chan        |
| Harley,Gilbert   |
+------------------+

字串合併 CONCAT_WS CONCAT_WS(“這裡輸入拼接字符串”, first_name, last_name)

select CONCAT_WS("-",first_name,last_name) from employee;

+-------------------------------------+
| CONCAT_WS("-",first_name,last_name) |
+-------------------------------------+
| Robin-Jackman                       |
| Taylor-Edward                       |
| Vivian-Dickens                      |
| Harry-Clifford                      |
| Eliza-Clifford                      |
| Nancy-Newman                        |
| Melinda-Clifford                    |
| Jack-Chan                           |
| Harley-Gilbert                      |
+-------------------------------------+

　字串 SUBSTRING

字串 SUBSTRING 可簡寫 SUBSTR

第一個字到第四

select SUBSTRING("Hello Word",1,4);

+-----------------------------+
| SUBSTRING("Hello Word",1,4) |
+-----------------------------+
| Hell                        |
+-----------------------------+

從第七個到結束

select SUBSTRING("Hello Word",7);

+---------------------------+
| SUBSTRING("Hello Word",7) |
+---------------------------+
| Word                      |
+---------------------------+

從後面數來三個

select SUBSTRING("Hello Word", -3);

+-----------------------------+
| SUBSTRING("Hello Word", -3) |
+-----------------------------+
| ord                         |
+-----------------------------+

REPLACE,REVERSE and CHAR_LENGTH

字符串取代 REPLACE

select REPLACE(“一段字串”,”想改變的字串”,”改變成甚麼”);

範例:

select REPLACE("Hello World", "World", "MySQL");

+------------------------------------------+
| REPLACE("Hello World", "World", "MySQL") |
+------------------------------------------+
| Hello MySQL                              |
+------------------------------------------+

字符反轉 REVERSE

select REVERSE(“要反轉字串”);

範例:

select REVERSE("Hello World");

+------------------------+
| REVERSE("Hello World") |
+------------------------+
| dlroW olleH            |
+------------------------+

查看字符串幾個字 CHAR_LENGTH

select CHAR_LENGTH(“字串”);

範例:

select CHAR_LENGTH("Hello World");

+----------------------------+
| CHAR_LENGTH("Hello World") |
+----------------------------+
|                         11 |
+----------------------------+

大小寫轉換 LOWER AND UPPER

字串變大寫 UPPER

select UPPER("Hello MySQL");

字串變小寫 LOWER

select LOWER("Hello MySQL");

大小寫範例:

select UPPER(first_name) as first_name, UPPER(last_name) as last_name from employee;

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| ROBIN      | JACKMAN   |
| TAYLOR     | EDWARD    |
| VIVIAN     | DICKENS   |
| HARRY      | CLIFFORD  |
| ELIZA      | CLIFFORD  |
| NANCY      | NEWMAN    |
| MELINDA    | CLIFFORD  |
| JACK       | CHAN      |
| HARLEY     | GILBERT   |
+------------+-----------+

select 結果的進一步處理

order by 對結果進行排序

將資料庫資料依據某項要求排序 order by(默認升序) + desc(降序)

select * from employee order by salary;

+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5100 | 2007-01-23 | NULL  |
|  1 | Robin      | Jackman   | Software Engineer      |   5500 | 2001-10-12 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  |
|  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  |
|  2 | Taylor     | Edward    | Software Architect     |  10000 | 2002-09-21 | NULL  |
|  9 | Harley     | Gilbert   | Software Architect     |  10000 | 2000-07-17 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+

選擇第三個 column 進行排序

select first_name, last_name , salary from employee order by 3;

+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Eliza      | Clifford  |   4750 |
| Nancy      | Newman    |   5100 |
| Robin      | Jackman   |   5500 |
| Vivian     | Dickens   |   6000 |
| Jack       | Chan      |   6500 |
| Harry      | Clifford  |   6800 |
| Melinda    | Clifford  |   8500 |
| Taylor     | Edward    |  10000 |
| Harley     | Gilbert   |  10000 |
+------------+-----------+--------+

先排二在排一

select first_name, last_name , salary from employee order by 2,1;

+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Jack       | Chan      |   6500 |
| Eliza      | Clifford  |   4750 |
| Harry      | Clifford  |   6800 |
| Melinda    | Clifford  |   8500 |
| Vivian     | Dickens   |   6000 |
| Taylor     | Edward    |  10000 |
| Harley     | Gilbert   |  10000 |
| Robin      | Jackman   |   5500 |
| Nancy      | Newman    |   5100 |
+------------+-----------+--------+

LIMIT 限制返回結果進行排序

限制數據返回 + limit 數量

select * from employee limit 5;

限制 index 幾到幾

select * from employee limit 2,4;

限制 index 多少到最後一個

select * from employee limit 2,18446744073709551615;

MySQL limit 補充連結

LIKE 進行字符串搜索過濾

LIKE 模糊字串搜尋 + LIKE “字串加%” ， %=甚麼都可

select * from employee where last_name LIKE "C%";

+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  |
|  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+

select * from employee where last_name LIKE "%n";

+----+------------+-----------+-------------------+--------+------------+-------+
| id | first_name | last_name | title             | salary | hire_date  | notes |
+----+------------+-----------+-------------------+--------+------------+-------+
|  1 | Robin      | Jackman   | Software Engineer |   5500 | 2001-10-12 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer |   5100 | 2007-01-23 | NULL  |
|  8 | Jack       | Chan      | Test Engineer     |   6500 | 2018-09-07 | NULL  |
+----+------------+-----------+-------------------+--------+------------+-------+

當知道長度為多少可以變成 + LIKE “____“;

select * from employee where last_name LIKE "____";

+----+------------+-----------+---------------+--------+------------+-------+
| id | first_name | last_name | title         | salary | hire_date  | notes |
+----+------------+-----------+-------------------+--------+------------+---+

當要找的字串包含 “%” , “_“加上\
select * from 表格名 where firstname LIKE “%\%\_“;

count 對結果進行比較

計算資料表有多少筆資料 COUNT(* 或其他 column)

select COUNT(*) from employee;

+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+

distinct 統計唯一値

計算這個 column 有幾種 DISTINCT(不同)

select distinct title from employee;

+------------------------+
| title                  |
+------------------------+
| Software Engineer      |
| Software Architect     |
| Database Administrator |
| Project Manager        |
| Test Engineer          |
+------------------------+

利用 CONUT 計算

select COUNT(distinct title) from employee;

+-----------------------+
| COUNT(distinct title) |
+-----------------------+
|                     5 |
+-----------------------+

GROUP BY 數據整合

select title,count(first_name) from employee group by title;

+------------------------+-------------------+
| title                  | count(first_name) |
+------------------------+-------------------+
| Database Administrator |                 2 |
| Project Manager        |                 1 |
| Software Architect     |                 2 |
| Software Engineer      |                 3 |
| Test Engineer          |                 1 |
+------------------------+-------------------+

MAX 和 MIN 球最大最小值

select max(salary) from employee;

+-------------+
| max(salary) |
+-------------+
|       10000 |
+-------------+

結合 group by

select title, max(salary) from employee group by title;

+------------------------+-------------+
| title                  | max(salary) |
+------------------------+-------------+
| Database Administrator |        6800 |
| Project Manager        |        8500 |
| Software Architect     |       10000 |
| Software Engineer      |        5500 |
| Test Engineer          |        6500 |
+------------------------+-------------+

SUM 和 AVG 求和平均值

select sum(salary), avg(salary) from employee;

+-------------+-------------------+
| sum(salary) | avg(salary)       |
+-------------+-------------------+
|       63150 | 7016.666666666667 |
+-------------+-------------------+

使用 max, min, sum, avg 不可這樣寫
select sum(salary), title from employee;
會報錯

HAVING

（✗）select title,count() from employee group by title where title=”Test Engineer;
(◯) select title,count() from employee where title=”Test Engineer group by title;

如果要想在聚合(group by)後過濾，要使用 HAVING

select title,count(*) from employee group by title having title="Test Engineer";

+---------------+----------+
| title         | count(*) |
+---------------+----------+
| Test Engineer |        1 |
+---------------+----------+

DATA TYPE 之數值類型

Numeric Tyoe

1. 整數(絕對值) Integer Types (Exact Value) - INTEGER,INT,SMALLINT,TINYINT,MEDIUMINT,BIGINT

如果要使用到 UNSIGNED 要在新增時加入，莫認為 Signed
範例:

create table test(a TINYINT UNSIGNED , b SMALLINT);

2. 定點數(小數點後位數固定) Fixed-Point Types(Exact Value) - DECIMAL,NUMERIC

salary DECIMAL(5,2)第一個參數是總共幾位數，第二個參數是指小數點後有幾位，小數點超過會四捨五入
範例: -999.99 ~ 999.99

3. 浮點數(無法精確表示) Floating-Point Types(Approximate Value) - FLOAT(4 bytes),DOUBLE(8 bytes)

float(range,小數點後位數)，BIT(M),BIT 由 0 和 1 組成,M can range from 1 to 64。

4. 二進位數位 Bit-Value Type - BIT

範例:

create table test6(a BIT(3));

insert into test6 values(1);

insert into test6 values(2);

--因為輸出是十進位所以不會顯示
select * from test6;
+------+
| a    |
+------+
| 
    |
|     |
+------+

--用加0的方式出現
select a+0 from test6;
+------+
| a+0  |
+------+
|    1 |
|    2 |
+------+


--這樣子就可以輸出二進制
--bin二進制，oct八進制，hex十六進制。
select bin(a+0) from test6;
+----------+
| bin(a+0) |
+----------+
| 1        |
| 10       |
+----------+

Data and Time Type

1. DATE : “YYYY-MM-DD”

2. TIME : “HH:MM:SS” “12:10”默認為 12 小時 10 分，”14”默認為 14 秒，”1210”默認為 12 分 10 秒

3.YEAR : 4-digit string number in the range 1901 to 2155

4. DATETIME(8bytes) : “YYYY-MM-DD HH:MM:SS”

5. TIMESTAMP(4bytes) : range “1907-01-01 00:00:01”UTC to “2038-01-19 03:14:07”UTC 依照時區改變，會隨著現在時間變化，可用來記錄資料修改時間，可更改 TIMEZONE。

此時間是幾號

select DAYOFMONYH(參數) from tablename;

此時間是星期幾

select DAYOFWEEK(參數) from tablename;

此時間是此年第幾天

select DAYOFYEAR(參數) from tablename;

此時間是幾月

select MONTHNAME(b) from demo;

現在日期

select CURDATE();

現在時間

select CURTIME();

現在日期+時間

select NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2020-10-21 15:57:02 |
+---------------------+

資料參考

小練習:

select CONCAT(first_name," ",last_name,"was hired on",DATE_FORMAT(hire_date,"%D %M %Y")) as title from employee;
+-----------------------------------------------+
| title                                         |
+-----------------------------------------------+
| Robin Jackmanwas hired on12th October 2001    |
| Taylor Edwardwas hired on21st September 2002  |
| Vivian Dickenswas hired on29th August 2012    |
| Harry Cliffordwas hired on10th December 2015  |
| Eliza Cliffordwas hired on19th October 1998   |
| Nancy Newmanwas hired on23rd January 2007     |
| Melinda Cliffordwas hired on29th October 2013 |
| Jack Chanwas hired on7th September 2018       |
| Harley Gilbertwas hired on17th July 2000      |
+-----------------------------------------------+

String Type

1. CHAR and VARCHAR

VAR(可變的)

CHAR length string(0~255)
char 的 storage 是固定的，bytes 是固定的

VARCHAR variable-length strings(0~65535)
varchar 的 storage 是可變的，字符也算一個 bytes

2. BINARY and VARBINARY

MySQL 文檔

3. BLOB and TEXT

通常用來存取大文檔，要排序大文檔通常比較費時，可通過

set max_sort_length = 2000;

更改 sql 最大文字長度。

MySQL 文檔

4. ENUM

選擇的數據類型

範例:

CREATE TABLE shirt(
size ENUM("x-small","small","medium","large","x-large")
);

補充連結

插入時可直接打索引值，速度比 varchar 快。

5. SET

最多八個 column(2 的 8 次方)

修改數據類型，使用 ALTER

create table user(username VARCHAR(10), password VARCHAR(20));

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | YES  |     | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

ALTER table user MODIFY username VARCHAR(15);

desc user;

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(15) | YES  |     | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

成功更改。

SQL 邏輯操作符

Equal 和 NOT Equal 等於不等於

--Equal範例

select * from employee where salary = 8000;

--NOT Equal範例

select * from employee where salary != 8000;

select * from employee where salary NOT 8000;

LIKE 和 NOT LIKE

--LIKE範例

--LIKE就是模糊搜尋

select * from employee where first_name like "H%";

--NOT LIKE範例

select * from employee where first_name NOT like "H%";

--嚴格搜尋大小寫BINARY，也可以在創建時加入BINARY

select * from employee where first_name like BINARY "H%";

Greate Than 和 Less Than 大於小於

--" > "可用運算符號替換

select * from employee where salary > 6000;

AND 和 OR

--AND範例，條件同時符合
select * from employee where salary>6000 and first_name LIKE"H%";

--OR範例，條件其一符合或都符合選項
select * from employee where salary>6000 or first_name LIKE"H%";

BETWEEN

尋找一個 range 之間

--尋找薪水在6000到8000之間
select * from employee where betweeen 6000 and 8000

IN 和 NOT IN

比較精確地找尋

select * from employee where salary in (5000,6000,7000,8000);

CASE Statement

select *,
    case
          when salary >=7000 then "high"
          else "low"
    end as tag
from employee order by desc;

MySQL 內建函數

1. 字符函數

字串拼接:CONCAT()，CONCAT_WS。

字串大小寫:UPPER()，LOWER()。

從左右邊數來:LEFT()，RIGHT()。

字串長度:LENGTH()。

去除特定字符:LTRIM()，RTRIM()，TRIM()

字串替換:REPLACE()

獲取特定字串:SUBSTRING(特定字串,起,尾,)

2. 數值函數

無條件進位和捨去:CEIL()，FIOOR()

除數跟餘數:DIV()，MOD()

平方:POW()

四捨五入:ROUND()

3. 日期時間函數

當前時間:NOE()，CURDATE()，CURTIMEE()

格式化時間:DATE_FORNAT()

日期增加減少:DASTE_ADD()

時間差:DATEDIFF(第一個時間,第二個時間)

4. 訊息函數

獲取當前進程 ID:CONNECTION_ID()

當前 DATABASE 名稱:DATABASE()

最後一次生成的 ID:LAST_INSERT_ID()

當前用戶:USER()

當前版本:VERSION()

5. 聚合函數

平均值 AVG()

計数 COUNT()

最大值 MAX()

最小值 MIN()

求和 SUM()

6. 加密函數

更改密碼:PASSWORD()

存取密碼如果資料密要加密時用:MD5()

RELATIONSHIP 之 ONE TO MANY(一對多)

先新增兩個 table

CREATE TABLE customers(
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100)
);

INSERT INTO customers(first_name, last_name, email) VALUES
    ('Robin', 'Jackman', 'roj@gmail.com'),
    ('Taylor', 'Edward', 'taed@gmail.com'),
    ('Vivian', 'Dickens', 'vidi@gmail.com'),
    ('Harley', 'Gilbert', 'hgi@gmail.com');

CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(8,2),
    customer_id INT
);

INSERT INTO orders(order_date, amount, customer_id) VALUES
    ('2001-10-12', 99.12, 1),
    ('2001-09-21', 110.99, 2),
    ('2001-10-13', 12.19, 1),
    ('2001-11-29', 88.09, 3),
    ('2001-11-11', 205.01, 4);

使用 id 連結兩個 table

select * from orders where customer_id = (select id from customers where email="roj@gmail.com");

+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
|  1 | 2001-10-12 |  99.12 |           1 |
|  3 | 2001-10-13 |  12.19 |           1 |
+----+------------+--------+-------------+

使用 Foreign Key 約束關聯

CREATE TABLE customers(
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100)
);

INSERT INTO customers(first_name, last_name, email) VALUES
    ('Robin', 'Jackman', 'roj@gmail.com'),
    ('Taylor', 'Edward', 'taed@gmail.com'),
    ('Vivian', 'Dickens', 'vidi@gmail.com'),
    ('Harley', 'Gilbert', 'hgi@gmail.com');

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
    ('2001-10-12', 99.12, 1),
    ('2001-09-21', 110.99, 2),
    ('2001-10-13', 12.19, 1),
    ('2001-11-29', 88.09, 3),
    ('2001-11-11', 205.01, 4);

Inner Join 結合兩張 table

取 table 重複部分

select * from customers inner join orders where customers.id=orders.customer_id;

+----+------------+-----------+----------------+----+------------+--------+-------------+
| id | first_name | last_name | email          | id | order_date | amount | customer_id |
+----+------------+-----------+----------------+----+------------+--------+-------------+
|  1 | Robin      | Jackman   | roj@gmail.com  |  1 | 2001-10-12 |  99.12 |           1 |
|  1 | Robin      | Jackman   | roj@gmail.com  |  3 | 2001-10-13 |  12.19 |           1 |
|  2 | Taylor     | Edward    | taed@gmail.com |  2 | 2001-09-21 | 110.99 |           2 |
|  3 | Vivian     | Dickens   | vidi@gmail.com |  4 | 2001-11-29 |  88.09 |           3 |
|  4 | Harley     | Gilbert   | hgi@gmail.com  |  5 | 2001-11-11 | 205.01 |           4 |
+----+------------+-----------+----------------+----+------------+--------+-------------+

ieft join

過濾條件式寫在 ON 後面，代表是先針對連結前的指定資料表進行過濾

過濾條件寫在 WHERE 後面，代表是對連結後取得的資料集合再進行過濾

--取得自己以及重複部分
select * from customers left join orders on customers.id=orders.customer_id;

--如果總和為null換成0
SELECT first_name, last_name,
case
    when SUM(amount) is NULL then 0
    else SUM(amount)
end as total_amount
from customers left join orders on customers.id=orders.customer_id group by customers.id;

--第二種null換成0寫法
SELECT first_name, last_name, IFNULL(SUM(amount), 0)
from customers left join orders on customers.id=orders.customer_id group by customers.id;

rigth join

left join 的相反邊

ON DELETE

刪除被 FOREIGN KEY 約束的資料

--刪除使用者資料，會連帶買的商品一起刪除
--建立資料表同時使用foreign key 和on delete

CREATE TABLE customers(
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100)
);

INSERT INTO customers(first_name, last_name, email) VALUES
    ('Robin', 'Jackman', 'roj@gmail.com'),
    ('Taylor', 'Edward', 'taed@gmail.com'),
    ('Vivian', 'Dickens', 'vidi@gmail.com'),
    ('Harley', 'Gilbert', 'hgi@gmail.com');

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)
        ON DELETE CASCADE
);

INSERT INTO orders(order_date, amount, customer_id) VALUES
    ('2001-10-12', 99.12, 1),
    ('2001-09-21', 110.99, 2),
    ('2001-10-13', 12.19, 1),
    ('2001-11-29', 88.09, 3),
    ('2001-11-11', 205.01, 4);

補充

--兩種方法取小數點後精度

CONVERT(AVG(imdb_score), DECIMAL(2,1))

--ROUND方法去取精度

 select ROUND(7.83232);
+----------------+
| ROUND(7.83232) |
+----------------+
|              8 |
+----------------+
1 row in set (0.00 sec)

mysql> select ROUND(7.83232,1);
+------------------+
| ROUND(7.83232,1) |
+------------------+
|              7.8 |
+------------------+
1 row in set (0.00 sec)

mysql> select ROUND(7.57232,1);
+------------------+
| ROUND(7.57232,1) |
+------------------+
|              7.6 |
+------------------+
1 row in set (0.00 sec)

RELATIONSHIP 之 MANY to MANY(多對多)

--DATA
CREATE TABLE reviewers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100)
);

CREATE TABLE books(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    released_year YEAR(4) NOT NULL,
    language VARCHAR(100) NOT NULL,
    paperback INT NOT NULL
);

CREATE TABLE reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    rating DECIMAL(2,1),
    book_id INT,
    reviewer_id INT,
    FOREIGN KEY(book_id) REFERENCES books(id),
    FOREIGN KEY(reviewer_id) REFERENCES reviewers(id)
);

INSERT INTO books(title, released_year, language, paperback) VALUES
    ('Fifty Shades of Grey Series', 2012, 'English', 514),
    ('Civilian Publishing Alif Baa Taa: Learning My Arabic Alphabet', 2018, 'Arabic', 30),
    ('The Hunger Games (Book 3)', 2014, 'English', 400),
    ('Santo Remedio', 2017, 'Spanish', 240),
    ('The Fault in Our Stars', 2012, 'English', 318),
    ('Harry Potter Und der Stein der Weisen (German Edition)', 2005, 'German', 334),
    ('Collection Folio, no. 2', 1971, 'French', 185),
    ('Santo remedio: Ilustrado y a color', 2018, 'Spanish', 216),
    ('Splatoon 2', 2017, 'Japanese', 384),
    ('Minna No Nihongo: Beginner 1, 2nd Edition', 2012, 'Japanese', 210);


INSERT INTO reviewers (first_name, last_name) VALUES
    ('Thomas', 'Stoneman'),
    ('Wyatt', 'Skaggs'),
    ('Kimbra', 'Masters'),
    ('Domingo', 'Cortes'),
    ('Colt', 'Steele'),
    ('Pinkie', 'Petit'),
    ('Marlon', 'Crafford');

INSERT INTO reviews(book_id, reviewer_id, rating) VALUES
    (1,1,8.0),(1,2,7.5),(1,3,8.5),(1,4,7.7),(1,5,8.9),
    (2,1,8.1),(2,4,6.0),(2,3,8.0),(2,6,8.4),(2,5,9.9),
    (3,1,7.0),(3,6,7.5),(3,4,8.0),(3,3,7.1),(3,5,8.0),
    (4,1,7.5),(4,3,7.8),(4,4,8.3),(4,2,7.6),(4,5,8.5),
    (5,1,9.5),(5,3,9.0),(5,4,9.1),(5,2,9.3),(5,5,9.9),
    (6,2,6.5),(6,3,7.8),(6,4,8.8),(6,2,8.4),(6,5,9.1),
    (7,2,9.1),(7,5,9.7),
    (8,4,8.5),(8,2,7.8),(8,6,8.8),(8,5,9.3),
    (9,2,5.5),(9,3,6.8),(9,4,5.8),(9,6,4.3),(9,5,4.5),
    (10,5,9.9);

練習題

找出評論者，評論分數最小,最大,平均,次數，如果次數等於 0 設為 INACTIVE，大於 0 設為 ACTIVE

--case語句也能用if判斷式
SELECT
    first_name,
    last_name,
    COUNT(rating) as COUNT,
    MIN(IFNULL(rating, 0)) as MIN,
    MAX(IFNULL(rating, 0)) as MAX,
    CONVERT(IFNULL(AVG(rating), 0), DECIMAL(3,2)) AS AVG,
    IF(COUNT(rating)>0, 'ACTIVE', 'INACTIVE') AS NEW_STATUS,
    CASE
        WHEN COUNT(rating)>0 THEN 'ACTIVE'
        ELSE "INACTIVE"
    END AS STATUS
FROM reviewers
LEFT JOIN reviews
    ON reviewers.id=reviews.reviewer_id
GROUP BY reviewers.id
ORDER BY AVG DESC;

---