---
# System prepended metadata

title: MySQL 學習筆記

---

學習影片: <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`;

![](https://hackmd.io/_uploads/ryLYZ8Bi2.png)
++圖片來源：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>