學習影片: <https://www.youtube.com/watch?v=gvRXjsrpCHw>
### 創建資料庫(create database)
==CREATE DATABASE==
創建資料庫
- CREATE DATABASE `sql_tutorial`;
使用反引號避免命名的名稱跟關鍵字(例如:CREATE)衝突

==HOW DATABASES==
列出所有的資料庫
- SHOW DATABASES;

==DROP DATABASE==
刪除資料庫
- DROP DATABASE `sql_tutorial`;

---

### 創建表格(create table)

==USE==
使用哪一個資料庫
- USE `sql_tutorial`;

==資料型態==
1. INT -- 整數
2. DECIMAL(m,n) -- 有小數點的數(m: 有幾位數，n: 小數點的部分占了幾位)
3. VARCHAR(n) -- 字串(n: 存放幾個字元)
4. BLOB -- (Binary Large Object)圖片 影片 檔案...
5. DATE -- 'YYYY-MM-DD' 日期
6. TIMESTAMP -- 'YYYY-MM-DD HH:MM:SS' 紀錄時間

==CREATA TABLE==
建立表格
- CREATE TABLE `student`(
`student_id` INT PRIMARY KEY,
`name` VARCHAR(20),
`major` VARCHAR(20)
);

==DESCRIBE==
檢查表格
- DESCRIBE `student`;

==DROP TABLE==
刪除表格
- DROP TABLE `student`;

==TRUNCATE TABLE==
刪除表內的數據(但不刪除表本身)
- TRUNCATE `student`;

==ALTER TABLE `表格名稱` ADD `屬性` `資料型態`==
新增屬性
- ALTER TABLE `student` ADD gpa DECIMAL(3,2);

==ALTER TABLE `表格名稱` DROP COLUMN `屬性`==
刪除屬性
- ALTER TABLE `student` DROP COLUMN gpa;

---

### 儲存資料(insert)
==INSERT INTO `表格名稱` VALUES()==
存入資料
(根據表格創建的資料順序做填入，中間用逗號隔開)
- INSERT INTO `student` VALUES(1, '小白', '歷史');
(也可以自訂順序)
- INSERT INTO `student`(`name`, `major`, `student_id`) VALUES('小綠', '英語', 4);

==SELECT * FROM==
列出所有資料
*：取得所有資料
- SELECT * FROM `student`;

---

### 限制、約束(constraint)
==限制屬性==
- CREATE TABLE `student`(
`student_id` INT,
`name` VARCHAR(20) NOT NULL,
`major` VARCHAR(20) UNIQUE,
PRIMARY KEY(`student_id`)
);
DROP TABLE `student`;
SELECT * FROM `student`;

INSERT INTO `student` VALUES(1, '小白', '英語');

==DEFAULT==
預設
- CREATE TABLE `student`(
`student_id` INT,
`name` VARCHAR(20) NOT NULL,
`major` VARCHAR(20) DEFAULT '歷史',
PRIMARY KEY(`student_id`)
);
DROP TABLE `student`;
SELECT * FROM `student`;

INSERT INTO `student` (`student_id`, `name`) VALUES(1, '小白');

==AUTO_INCREMENT==
新增資料時自動加1
- CREATE TABLE `student`(
`student_id` INT AUTO_INCREMENT,
`name` VARCHAR(20),
`major` VARCHAR(20),
PRIMARY KEY(`student_id`)
);
DROP TABLE `student`;
SELECT * FROM `student`;

INSERT INTO `student` (`name`, `major`) VALUES('小黑', '英語');

---

### 修改、刪除資料(update&delete)
==UPDATE
SET
WHERE==
更新資料
- SET SQL_SAFE_UPDATES = 0;
CREATE TABLE `student`(
`student_id` INT PRIMARY KEY,
`name` VARCHAR(20),
`major` VARCHAR(20),
`score` INT
);
DROP TABLE `student`;
SELECT * FROM `student`;

INSERT INTO `student` VALUES(1, '小白', '英語', '50');
INSERT INTO `student` VALUES(2, '小黄', '生物', '90');
INSERT INTO `student` VALUES(3, '小綠', '歷史', '70');
INSERT INTO `student` VALUES(4, '小藍', '英語', '80');
INSERT INTO `student` VALUES(5, '小黑', '化學', '20');

UPDATE `student`
SET `major` = '生化'
WHERE `major` = '生物' OR `major` = '化學';

/*(將`student`這個資料表裡面的'生物'或是'化學'改成'生化')*/

SET `name` = '小灰', `major` = '物理'
WHERE `student_id` = 1;

/*將`student`這個資料表裡`student_id`等於1的資料名字改成'小灰'、`major`改成'物理'*/

==DELETE FROM
WHERE==
刪除資料
- DELETE FROM `student`
WHERE `name` = '小灰' AND `major` = '物理';;

/*將`student`這個資料表裡`name`等於'小灰'且`major`等於'物理'的資料刪除*/

---

### 取得資料(select)
==SELECT `要取得的屬性` FROM `表格名稱`==
取得
- SELECT `name`, `major` FROM `student`;
- SELECT * FROM `student` WHERE `major` = '英語' OR `score` <> 20;
/*<>：不等於*/

==ORDER BY==
排序
- SELECT * FROM `student` ORDER BY `score` ASC;
/*由低到高 (預設是ASC，所以可不加)*/
- SELECT * FROM `student` ORDER BY `score` DESC;
/*由高到低*/
- SELECT * FROM `student` ORDER BY `score`, `student_id`;
/*先根據`score`屬性排序，若相同，再根據`student_id`屬性排序*/

==LIMIT==
回傳資料的比數
- SELECT * FROM `student` LIMIT 3;
/*回傳前3筆資料*/
- SELECT * FROM `student` ORDER BY `score` DESC LIMIT 2;
/*回傳`score`前2高的資料*/
- SELECT * FROM `student` WHERE `major` = '英語' OR `score` <> 70 LIMIT 2;

==WHERE `屬性` IN==
判斷
- WHERE `major` IN('歷史', '英語', '生物');
= WHERE `major` = '歷史' OR `major` = '英語'OR `major` = '生物';

==CASE==
like an if-then-else statement
- CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;

==HAVING==
HAVING 子句是用來取代 WHERE 搭配聚合函數 (aggregate function) 進行條件查詢，因為 WHERE 不能與聚合函數一起使用
- SELECT `column_name(s)`, `aggregate_function`(`column_name`)
FROM `table_name`
WHERE `column_name` `operator value`
GROUP BY `column_name1`, `column_name2`
HAVING `aggregate_function`(`column_name`) operator value;

==DISTINCT==
一列通常包含許多重複值；有時只想列出不同的值
- SELECT DISTINCT `column1`, `column2` FROM `table_name`;

---

### 創建公司資料庫
- CREATE DATABASE `sql_tutorial`;
SHOW DATABASES;
DROP DATABASE `sql_tutorial`;
USE `sql_tutorial`;

/*創建員工表格*/
CREATE TABLE `employee`(
`emp_id` INT PRIMARY KEY,
`name` VARCHAR(20),
`birth_date` DATE,
`sex` VARCHAR(1),
`salary` INT,
`branch_id` INT,
`sup_id` INT
);

/*創建部門表格*/
CREATE TABLE `branch`(
`branch_id` INT PRIMARY KEY,
`branch_name` VARCHAR(20),
`manager_id` INT,
FOREIGN KEY (`manager_id`) REFERENCES `employee` (`emp_id`) ON DELETE SET NULL
);

/*在`employee`表格的`branch_id`屬性新增FOREIGN KEY，他是對應到`branch`表格的`branch_id`屬性*/
ALTER TABLE `employee`
ADD FOREIGN KEY(`branch_id`)
REFERENCES `branch` (`branch_id`)
ON DELETE SET NULL;

/*在`employee`表格的`sup_id`屬性新增FOREIGN KEY，他是對應到`employee`表格的`emp_id`屬性*/
ALTER TABLE `employee`
ADD FOREIGN KEY(`sup_id`)
REFERENCES `employee` (`emp_id`)
ON DELETE SET NULL;

/*創建客戶表格*/
CREATE TABLE `client`(
`client_id` INT PRIMARY KEY,
`client_name` VARCHAR(20),
`phone` VARCHAR(20)
);

/*創建works_with的表格*/
CREATE TABLE `works_with`(
`emp_id` INT,
`client_id` INT,
`total_sales` INT,
PRIMARY KEY(`emp_id`, `client_id`),
FOREIGN KEY(`emp_id`) REFERENCES `employee` (`emp_id`) ON DELETE CASCADE,
FOREIGN KEY(`client_id`) REFERENCES `client` (`client_id`) ON DELETE CASCADE
);

/*新增部門資料*/
INSERT INTO `branch` VALUES(1, '研發', NULL);
INSERT INTO `branch` VALUES(2, '行政', NULL);
INSERT INTO `branch` VALUES(3, '資訊', NULL);
INSERT INTO `branch` VALUES(4, '偷懶', NULL);

/*新增員工資料*/
INSERT INTO `employee` VALUES(206, '小黃', '1998-10-08', 'F', 50000, 1, NULL);
INSERT INTO `employee` VALUES(207, '小綠', '1985-09-16', 'M', 29000, 2, 206);
INSERT INTO `employee` VALUES(208, '小黑', '2000-12-19', 'M', 35000, 3, 206);
INSERT INTO `employee` VALUES(209, '小白', '1997-01-22', 'F', 39000, 3, 207);
INSERT INTO `employee` VALUES(210, '小藍', '1925-11-10', 'F', 84000, 1, 207);

/*更改`branch_id`*/
UPDATE `branch`
SET `manager_id` = 206
WHERE `branch_id` = 1;

UPDATE `branch`
SET `manager_id` = 207
WHERE `branch_id` = 2;

UPDATE `branch`
SET `manager_id` = 208
WHERE `branch_id` = 3;

/*新增客戶資料*/
INSERT INTO `client` VALUES(400, '阿狗', '254354335');
INSERT INTO `client` VALUES(401, '阿貓', '25633899');
INSERT INTO `client` VALUES(402, '旺來', '45354345');
INSERT INTO `client` VALUES(403, '露西', '54354365');
INSERT INTO `client` VALUES(404, '艾瑞克', '18783783');

/*新增銷售資料*/
INSERT INTO `works_with` VALUES(206, 400, '70000');
INSERT INTO `works_with` VALUES(207, 401, '24000');
INSERT INTO `works_with` VALUES(208, 402, '9800');
INSERT INTO `works_with` VALUES(208, 403, '24000');
INSERT INTO `works_with` VALUES(210, 404, '87940');

---

### 聚合函數(aggregate functions)
==COUNT(`屬性`)==
算幾筆
- SELECT COUNT(*) FROM `employee`;
- SELECT COUNT(`sup_id`) FROM `employee`;
- SELECT COUNT(*)
FROM `employee`
WHERE `birth_date` > '1970-01-01' AND `sex` = 'F';

==AVG(`屬性`);==
算平均
- SELECT AVG(`salary`) FROM `employee`;

==SUM(`屬性`);==
算總和
- SELECT SUM(`salary`) FROM `employee`;

==MAX(`屬性`;==
算最大
- SELECT MAX(`salary`) FROM `employee`;

==MIN(`屬性`);==
算最小
- SELECT MIN(`salary`) FROM `employee`;

==GROUP BY==
組成群組
(欄位內的資料若有不只一筆名稱相同的資料的話，就會把它們作為群組)
- SELECT `orderID` AS `訂單編號` , SUM(total) AS `總金額`
FROM `Orderdetial` GROUP BY `orderID`;

==CONCAT==
合併多個欄位的值
- WHERE `capital` = CONCAT(`name`, ' City');

==UPPER==
將字串轉換成大寫

==SUBSTR==
從輸入字串中的位置開始提取具有指定長度的子字串
- SUBSTRING(input_string, start, length);

==LOWER==
將字串轉換成小寫

---

### 萬用字元(wildcards)
- %代表多個字元
- _代表一個字元

==%==
多個字元
- SELECT *
FROM `client`
WHERE `phone` LIKE '%335';
/*找電話號碼尾數是335的客戶*/
- SELECT *
FROM `client`
WHERE `phone` LIKE '254%';
/*找電話號碼開頭是254的客戶*/
- SELECT *
FROM `client`
WHERE `phone` LIKE '%354&';
/*找電話號碼中間有354的客戶*/

==_==
一個字元
- SELECT *
FROM `employee`
WHERE `birth_date` LIKE '_____12%';
/*找生日在12月的員工*/

### 聯集(union)
- 使用union，屬性數目要一樣
- SELECT `name`
FROM `employee`
UNION
SELECT `client_name`
FROM `client`
UNION
SELECT `branch_name`
FROM `branch`;
- 要合併的屬性資料型態也要一樣
- SELECT `emp_id`, `name`
FROM `employee`
UNION
SELECT `client_id`, `client_name`
FROM `client`;

---

### 連接(join)
==JOIN==
- SELECT *
FROM `employee`
JOIN `branch`
ON `emp_id` = `manager_id`;
- SELECT `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name`
FROM `employee`
JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;
/*`表格`.`屬性`*/
- SELECT `table_column1`, `table_column2`
FROM `table_name1`
INNER JOIN `table_name2`
USING (`column_name`);

==LEFT JOIN==
左邊的表格不管條件有沒有成立都會回傳所有資料給我們，右邊的表個要條件成立才會回傳
- SELECT `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name`
FROM `employee` LEFT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;

==RIGHT JOIN==
右邊的表格不管條件有沒有成立都會回傳所有資料給我們，左邊的表個要條件成立才會回傳
- SELECT `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name`
FROM `employee` RIGHT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;

++圖片來源：w3school++

---

### 子查詢(subquery)
- SELECT `name`
FROM `employee`
WHERE `emp_id` = (SELECT `manager_id` FROM `branch` WHERE `branch_name` = '研發');

- SELECT `name`
FROM `employee`
WHERE `emp_id` IN (SELECT `emp_id` FROM `works_with` WHERE `total_sales` > 50000);

---

### on delete
==ON DELETE SET NULL==
對應不到，設成NULL
- FOREIGN KEY (`manager_id`) REFERENCES `employee` (`emp_id`) ON DELETE SET NULL
/*當屬性`emp_id`被刪除時，把`manager_id`設成NULL*/

==ON DELETE CASCADE==
對應不到，直接把整筆資料刪除
- FOREIGN KEY(`emp_id`) REFERENCES `employee` (`emp_id`) ON DELETE CASCADE
/*當`employee`表格的屬性`emp_id`被刪除時， `works_with`的`emp_id`也一起刪除*/
---

### 其他函式
==MOD==
MOD(a, b) a除以b的餘數
- MOD(id, 2) = 1 -> id 是奇數
- MOD(id, 2) = 0 -> id 是偶數

==LENGTH==
字串長度
- LENGTH(`column_name`)

---

### python 接 MySQL
<https://github.com/hibyby/GrandmaCan_sql_tutorial>