[TOC]
# CODE JARVIS: 從0開始的自耕碼農生活(二) - 初探 MySQL
Notice
===
1. SQL 語法中句尾的分號 ; 是必須的，系統完全依賴分號判別語法是否結束，不像 JS 能在大多情況下忽略。
2. SQL 語法中有許多保留字，為了避免命名與保留字衝突，有一派做法習慣將所有命名用 \`\` 包起來，以保證百分之百不會出現衝突問題。 ex: ``` `classmate` ```
DATABASE
===
### 創建 Database (CREATE)
``` sql
-- CREATE DATABASE 資料庫名稱;
CREATE DATABASE `sql_tutorial_company`;
```
### 顯示 當前 DB server 所有的 database (SHOW)
``` sql
-- 注意 DATABASES 最後有個複數 "S"
SHOW DATABASES;
```
TABLE
===
### 創建表 (CREATE)
``` sql
-- CREATE TABLE 表名稱(鍵值對);
CREATE TABLE `branch`(
`branch_id` INT,
`branch_name` VARCHAR(10),
`manager_id` INT,
PRIMARY KEY(`branch_id`), -- 定義 Primary Key
FOREIGN KEY (`manager_id`) REFERENCES `employee`(`emp_id`) ON DELETE SET NULL -- 定義 Foreign Key
);
```
- #### 鍵值對有三種
- Primary Key(主鍵)
~ 用於辨別每筆獨立資料，因此必須是**唯一值**
- Attribute(一般屬性)
~ 就一般屬性
- Foreign Key(外部鍵)
~ 關聯其他表的鍵值，因此做關聯時必須在其他表已建立(CREATE)的前提下才能關聯，否則須等關聯的表建立(CREATE)完成後再另行下語法做關聯
``` sql
ALTER TABLE `employee`
ADD FOREIGN KEY (`branch_id`)
REFERENCES `branch`(`branch_id`)
ON DELETE SET NULL; -- 若關聯資料消失，則將鍵值設為 NULL
-- 另有 ON DELETE CASCADE; 表示若關聯資料消失，則刪除整筆對應資料
```
### 查看表定義 (DESCRIBE)
``` sql
-- DESCRIBE 表名;
DESCRIBE `employee`;
```
### 刪除表 (DROP)
``` sql
-- DROP TABLE 表名;
DROP TABLE `employee`;
```
### 儲存資料(INSERT)
- 預設按照表的定義順序儲存資料
``` sql
-- INSERT INTO 表名 VALUES(值1, 值2, 值3);
INSERT INTO `branch`
VALUES(1, '研發', NULL);
-- 若還沒有對應的外部 key, 先設 NULL, 等對應資料儲存進來後再回頭把 NULL 設回來
```
- 可自定義儲存資料的順序
``` sql
-- INSERT INTO 表名(屬性2, 屬性3, 屬性1) VALUES(值2, 值3, 值1);
INSERT INTO `branch`(`branch_name`, `manager_id`, `branch_id`)
VALUES('研發', NULL, 1);
```
### 更新資料(UPDATE)
``` sql
UPDATE `employee`
SET `sub_id` = 207
WHERE `emp_id` IN(209, 210); -- 設置條件，類似 JS 的 if
```
### 查看資料 (SELECT...FROM...)
- 全查
``` sql
SELECT * FROM `branch`;
```
- 排序(ASC 正序小到大, DESC 倒序大到小)
``` sql
SELECT * FROM `employee` ORDER BY `salary` ASC;
```
- 限制查詢筆數，同時排序
``` sql
SELECT * FROM `employee`
ORDER BY `salary` DESC
LIMIT 3; -- 結果會是排序過後的前三筆
```
- 指定查詢鍵值(key)
``` sql
SELECT `name` FROM `employee`;
```
- 指定查詢鍵值並去重(duplicate)
``` sql
SELECT DISTINCT `sex` FROM `employee`;
-- 若同時查詢多個欄位，則必須兩筆資料中的所有欄位的所有值都相同才會視為重複
```
聚合函數( Aggregate function )
===
- 取得一張表的總筆數
``` sql
SELECT COUNT(*) FROM `employee`;
```
- 取得一張表中指定 key 的總筆數（會忽略NULL）
``` sql
SELECT COUNT(`sup_id`) FROM `employee`;
```
- 取得符合條件後的總筆數 (WHERE)
``` sql
SELECT COUNT(*) FROM `employee`
WHERE `birth_date` > '1970-01-01' AND `sex` = 'F';
```
> 條件之間可以用 AND、OR 關鍵字
> 設定條件時"不等於"這樣寫： <>
> ex: ``` `sex` <> 'F' ```
#### 計算總筆數(COUNT)
```COUNT(*)```
#### 計算平均(AVG)
``` AVG(`salary`) ```
#### 計算總和(SUM)
``` SUM(`salary`) ```
#### 取最大值(MAX)
``` MAX(`salary`) ```
#### 取最小值(MIN)
``` MIN(`salary`) ```
萬用字元查詢(LIKE) - 多個字元%｜一個字元_
===
- 取得電話號碼開頭是 123 的客戶
``` sql
SELECT * FROM `client` WHERE `phone` LIKE '123%';
```
- 取得性艾的客戶
``` sql
SELECT * FROM `client` WHERE `client_name` LIKE '艾%';
```
- 取得型別是 DATE 時(yyyy-mm-dd)，生日在 12 月的員工
```sql
SELECT * FROM `employee` WHERE `birth_date` LIKE '_____12%';
```
聯集查詢（UNION)
===
#### 合併鍵值欄位，合併時型別要一樣；合併多個時數量要一樣；
``` sql
SELECT `name` FROM `employee`
UNION
SELECT `client_name` FROM `client`;
```
- 合併多個
``` sql
SELECT `emp_id`, `name` FROM `employee`
UNION
SELECT `client_id`, `client_name` FROM `client`;
```
連接(JOIN)
===
#### 把兩張表合為一張表，並設置條件來決定兩張表的資料怎樣才算是同一筆
- 取得所有部門經理的 員工id, 名字, 所屬部門名字
``` sql
SELECT `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name`
FROM `employee` JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;
```
#### 關鍵字 RIGHT、LEFT 可以強制指定要完全顯示的那張表(就算不符合)
- 取得所有部門經理的 員工id, 名字, 所屬部門名字（包含顯示沒有經理的部門）
``` sql
SELECT `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name`
FROM `employee` RIGHT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;
```
子查詢
===
- 找出研發部門的經理名字
``` sql
SELECT `name` FROM `employee`
WHERE `emp_id` = (
SELECT `manager_id` FROM `branch`
WHERE `branch_name` = '研發'
);
```
- 找出對單一位客戶銷售金額超過 50000 的員工名字
``` sql
SELECT `name` FROM `employee`
WHERE `emp_id` IN(
SELECT `emp_id` FROM `work_with`
WHERE `total_sales` > 50000
);
```
> 用 IN 語法是因為查詢結果可能為一個以上
Sign in with Wallet
Connect another wallet