# SQL學習
###### tags: `MySql`
> 簡單認識SQL
## :memo: SQL認識
:::info
:pushpin: [學習影片](https://www.youtube.com/watch?v=gvRXjsrpCHw)
:::
### 認識表格 & 鍵 (tables & keys)
- 藍色框: key (一般鍵值)
- 紅色框: primary key (主鍵)
- 綠色框: foreign key (外鍵)
#### 主鍵
具有唯一值，若主鍵內有重複地到的值，那會在拆出另一個主鍵來表示，直接該需要的數值是表格內的唯一值(圖片中Works_With部分)
#### 外鍵
表格內連結其他表主鍵的key值，連結到的一定目標所在的表格的主鍵
### 創建資料庫 (0:37)
- 藍色文字: mySql的關鍵字，一般以大寫表示
- 紅色文字: 自定義名稱，一般會加上`反引號`來區分系統關鍵字
- 閃電符號: 選取打好的的指令後，按下該符號來執行，
- 最下方視窗顯示已執行的指令，中間顯示結果(SHOW DATABASES)
### 創建表格 (0:44)
- 資料型態介紹:
1. INT: 整數型態
2. DECIMAL(m,n): 小數點數字
第一個參數代表總共幾位數，第二個參數代表小數點站了幾位，如:2.33 要傳入(3,2)
3. VARCHAR(10): 字串
括號寫入可以存取字串的數量，寫10表示最多只能存10個字串元
4. BLOB: Binary Large Object 存放二進制的資料，如:圖片、影片、檔案...等
5. DATE: 'YYYY-MM-DD' 日期 2021-10-19
6. TIMESTAMP: 'YYYY-MM-DD HH-MM-SS' 紀錄時間 2021-10-19 10-39-00
- 指令 創建表格:
```sql=
CREATE TABLE `student`(
`student_id` INT PRIMARY KEY,
`name` VARCHAR(20),
`major` VARCHAR(20),
-- 主鍵設置也可以寫成: PRIMARY KEY(`student_id
)
-- 觀看表格:
DESCRIBE `student`;
-- 刪除表格
DROP TABLE `student`;
-- 新增屬性gpa到表格--
ALTER TABLE `student` ADD `gpa` DECIMAL(3,2);
-- 刪除屬性gpa
ALTER TABLE `student` DROP COLUMN `gpa`;
```
### 儲存資料 (00:56)
```sql=
CREATE TABLE `student`(
`student_id` INT PRIMARY KEY,
`name` VARCHAR(20),
`major` VARCHAR(20)
);
-- 列出所有student資料
SELECT * FROM `student`; -- 米字號代表全部
-- 新增資料至表格
INSERT INTO `student` VALUES(3, '小綠', NULL); -- VALUES()新增順序要根據創建表格時的排序
-- 新增表格 自定義屬性順序
INSERT INTO `student`(`major`,`student_id`) VALUES('歷史', 5);
```
### 限制、約束constraint (01:04:00)
```sql=
CREATE TABLE `student`(
-- AUTO_INCREMENT 自動補上數字排序 就不用每次新增都要寫數字了
`student_id` INT AUTO_INCREMENT,
-- 不能是NULL值
`name` VARCHAR(20) NOT NULL,
-- DEFAULT 預設值
`major` VARCHAR(20) DEFAULT '歷史',
-- UNIQUE 每一個值都是唯一值
`phone` INT UNIQUE,
PRIMARY KEY(`stundent_id`)
);
-- 不用給予數字排序 會自動新增
INSERT INTO `student`(`name`,`phone`) VALUES('小王',097755777);
```
### 修改、刪除資料(01:11:00)
```sql=
-- 選擇要更新的表格
UPDATE `student`
-- SET 要更新的內容
SET `name`= '小輝', `major` = '日文'
-- WHERE 增加條件判斷
WHERE `student_id` = 1;
DELETE FROM `student`
WHERE `name` = "小輝" AND `major` = '日文'; -- AND 且
-- 設定數字範圍
DELETE FROM `student`
WHERE `sore` <= 50;
```
### select 選擇資料(01:33:00)
```sql=
SELECT *
FROM `student`
--ORDER BY 排序默認升序(ASC) 加上 DESC 為降序
ORDER BY `sore` DESC;
-- 限制輸出數
LIMIT 2;
-- 條件判斷:WHERE
-- 回傳只有主修是日文的
WHERE `major` = "日文";
-- <> 一對大小於是mysql的不等於!
WHERE `major` = '日文' OR `score` <> 66;
-- where 判斷的另一種寫法in (or的寫法)
WHERE `major` IN('統計', '歷史');
-- 就等於下面:
WHERE `major` = '統計' OR `major` = '歷史'
```
### 建立公司表格
1. 創建 員工 表格:需要先有表格與屬性才能設定外鍵
```sql=
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 -- 主管單位
);
```
2. 創建 部門 表格:外建設定
```sql=
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
);
```
3. 設定 員工 表格內的外鍵(branch_id、sup_id)
```sql=
-- 重新設定外鍵
-- 選擇要改變的表格
ALTER TABLE `employee`
-- 將原本屬性變為外鍵(屬性名)
ADD FOREIGN KEY(`branch_id`)
-- 連結的目標 `表格名`(`屬性`)
REFERENCES `branch`(`branch_id`)
ON DELETE SET NULL;
ALTER TABLE `employee`
ADD FOREIGN KEY(`sup_id`)
REFERENCES `employee`(`emp_id`)
ON DELETE SET NULL;
```
4. 創建 顧客 表格
```sql=
CREATE TABLE `client`(
`client_id` INT PRIMARY KEY,
`client_name` VARCHAR(20),
`phone` VARCHAR(20)
);
```
5. 創建 銷售金額 表格
```sql=
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
);
```
#### 設定資料
先從沒有關聯到其他表格的主鍵開始創建，不然會錯誤
1. 如果先新增員工的資料，會因為還沒有部門(branch)的資料的外鍵出錯
所以要先:
```sql=
INSERT INTO `branch` VALUES(1,'研發',null);
INSERT INTO `branch` VALUES(2,'行政',null);
INSERT INTO `branch` VALUES(3,'資訊',null);
```
2. 再來新增員工
```sql=
-- 新增員工資料
INSERT INTO `employee` VALUES(206,'小黃','1998-10-08','F',50000,1,NULL);
INSERT INTO `employee` VALUES(207,'小綠','1985-09-18','M',40000,2,206);
INSERT INTO `employee` VALUES(208,'小黑','2000-01-22','M',31000,3,206);
INSERT INTO `employee` VALUES(209,'小白','1997-03-18','F',39000,3,207);
INSERT INTO `employee` VALUES(210,'小藍','1983-05-10','F',84000,1,207);
```
3. 依序更改部門中的marnger_id
```sql=
-- 更新marnger_id 的值
UPDATE `branch`
SET `manager_id` = 208 -- 206 / 207 / 208
WHERE `branch_id` = 3; -- 1 / 2 / 3
```
4. 新增顧客資料
```sql=
INSERT INTO `client` VALUES(400,'阿狗',25468791);
INSERT INTO `client` VALUES(401,'阿福',26987451);
INSERT INTO `client` VALUES(402,'旺來',77222456);
INSERT INTO `client` VALUES(403,'阿花',54879863);
INSERT INTO `client` VALUES(404,'瓜拉',78954123);
INSERT INTO `client` VALUES(405,'艾拉斯',78400091);
```
5. 新增商品資料
```sql=
INSERT INTO `works_with` VALUES(206,400,'700000');
INSERT INTO `works_with` VALUES(207,401,'24000');
INSERT INTO `works_with` VALUES(208,402,'98000');
INSERT INTO `works_with` VALUES(208,403,'48000');
INSERT INTO `works_with` VALUES(210,404,'87400');
```
## 取得公司資料
1. 取得所有員工資料
```sql=
SELECT * FROM `employee`;
```
2. 取得所有客戶資料
```sql=
SELECT * FROM `client`;
```
3. 按薪水低到高取得員工資料
```sql=
SELECT * FROM `employee` ORDER BY `salary`;
```
4. 取得薪水前三高的員工
```sql=
SELECT * FROM `employee` ORDER BY `salary` DESC
LIMIT 3;
```
5. 取得所有員工的名字
```sql=
SELECT `name` FROM `employee`;
```
6. 取得所有員工重複的所屬單位
DISTINCT 不重複
```sql=
SELECT DISTINCT `sup_id` FROM `employee`;
```
## aggregate function 聚合函數
1. 取得員工人數
```sql=
SELECT COUNT(*) FROM `employee`;
-- 5
```
count()括號裡面可以放屬性名，計算該屬性有值的數量
```sql
SELECT COUNT(`sup_id`) FROM `employee`;
-- 輸出四，因表格內有一位為NULL
```
2. 取得所有出生於 1970-01-01 後的女性員工人數
```sql=
SELECT COUNT(*) FROM `employee`
WHERE `birth_date` > '1970-01-01' AND `sex` = 'F';
-- 3
```
3. 取得所有員工的平均薪水 AVG(屬性) 取平均
```sql=
SELECT AVG(`salary`) FROM `employee`;
```
4. 取得所有員工薪水的總和 SUM(屬性) 取總和
```sql=
SELECT SUM(`salary`) FROM `employee`;
```
5. 取得薪水最高的員工 MAX(屬性) 取最高
```sql=
SELECT MAX(`salary`) FROM `employee`;
```
6. 取得薪水最低的員工 MIN(屬性) 取最低
```sql=
SELECT MIN(`salary`) FROM `employee`;
```
## 萬用字元 wildcards
**% 代表多個字元, _ 代表一個字元**
1. 取得電話號碼尾數是 123 的客戶 LIKE 判斷是否符合下述句 %不管前面有多少個字元 只要尾數是123者
```sql=
SELECT * FROM `client` WHERE `phone` LIKE '%123';
-- 可依照不同的位置做判斷，254%: 開頭是否為254，%798%: 中間數字是否有798
SELECT * FROM `client` WHERE `phone` LIKE '254%';
-- 中間是否有798
SELECT * FROM `client` WHERE `phone` LIKE '%798%';
```
2. 取得姓"艾"的客戶
```sql=
SELECT * FROM `client` WHERE `client_name` LIKE '艾%';
```
3. 取得生日在3月的員工
五個底線 代表五個字元
```sql=
SELECT * FROM `employee` WHERE `birth_date` LIKE '_____03%';
```
## 聯集Union
1. 員工名字 union 客戶名字
**union 可讀作合併**
tips:要使用union 所有的SELECT 屬性述都要一樣; 做合併的屬性，資料型態都要一樣
```sql=
SELECT `name` FROM `employee`
UNION
SELECT `client_name` FROM `client`
UNION
SELECT `branch_name` FROM `branch`;
```
2. 員工id + 員工名字 union 客戶id + 客戶名字
加入AS後，UNION合併後輸出的屬性就為AS後面的名稱
```sql=
SELECT `emp_id` AS `total_id`, `name` AS `totoal_id` FROM `employee`
UNION
SELECT `client_id`, `client_name` FROM `client`;
```
3. 員工薪水 union 銷售金額
```sql=
SELECT `salary` AS `money` FROM `employee`
UNION
SELECT `total_sales` FROM `work_with`;
```
## JOIN 連結
可用於查詢連結相同屬性不同的表格
1. 取得所有部門經理的名字
```sql=
SELECT `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name`
FROM `employee` LEFT JOIN `branch`
-- FROM `employee` RIGHT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`; --
```
- ON 後面接符合條件
- LEFT JOIN: 在關鍵字LEFT JOIN左邊的表格會不管條件有沒有成立皆輸出 右邊則是遵守條件，若不成立會回傳NULL。
- RIGHT JOIN: 與LEFT JOIN相反
## 子查詢 subquery
在一個查詢語句中加入另一個查詢語句
1. 找出研發部門的經理名字
利用子查詢結果 經理id找出在branch哪一個
```sql=
SELECT `name`
FROM `employee`
WHERE `emp_id` = (
-- 子查詢:找到研發部門經理的id
SELECT `manager_id`
FROM `branch`
WHERE `branch_name` = '研發'
);
```
2. 找出對單一單位客戶銷售金額超過50000的員工名字 (WORKS_WITH.total_sales > 50000)
```sql=
-- 找出員工id後再找到他的名字
SELECT `name`
FROM `employee`
WHERE `emp_id` IN (
-- 子查詢:先找超過5萬
SELECT `emp_id`
FROM `works_with`
WHERE `total_sales` > 50000
);
```
這邊用 IN 是因為子查詢出來的結果不只一個所有用IN將所有可能列出。
單純只有等於會報錯，等於只能一個結果的時候用喔!
## ON DELETE
```sql=
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 SET NULL,
FOREIGN KEY (`client_id`) REFERENCES `client`(`client_id`) ON DELETE CASCADE
);
```
- ON DELETE SET NULL:
被刪除時，將此表格存留但值設為NULL
- ON DELETE CASCADE
被刪除時，將此表格通通刪掉
```sql=
-- 刪除
DELETE FROM `employee`
WHERE `emp_id` = 207;
```
會發現: 被刪除的員工ID同時也是manager_id，同時在部門manager_id被刪除了
然後設定 ON DELETE CASCADE的 員工表格
編號207被砍了
Sign in with Wallet
Connect another wallet