# MySQL 學習紀錄
###### tags: `SQL` `MySQL` `學習紀錄`
# 目錄
[TOC]
# 影片進度
* [進度](https://youtu.be/LiBAkcZRqS8?list=PLmOn9nNkQxJFi4x7rZ5wpUKts3u7cDx21)
# 通用
* mysql -u 帳號 -p密碼
* 密碼跟前面的-p中間是沒有空格的
* show databases;
* 顯示目前有的資料庫
* use 資料庫名;
* 選擇資料庫
* show tables( from 資料庫名);
* 顯示目前(from)資料庫內的表
* select database();
* 顯示目前資料庫名
* desc tableName;
* 顯示該table 的結構
* ```sql
create table tableName(
columnName columnType,
columnName columnType,
columnName columnType
)
```
* select version();
* 顯示資料庫版本
# 基礎查詢
* select columnName as "alias" from tableName;
* 別名用雙(單)引號包住可避免使用到關鍵字等錯誤
* selct distinct columnName from tableName;
* 去掉重複的欄位
* MySQL中+(加號)只有運算的功能
* 不能轉換成數值的會視為0(零)
* 如果其中有null，結果必為null
* select CONCAT('a欄字串','b欄字串') from tableName;
* 在MySQL中連接字串可使用==CONCAT('a欄字串','b欄字串')==
* select IFNULL(可能為null的欄位, 當為null時要變換成的值) from tableName;
* select * from tableName where columnName LIKE '%包含的字串%';
* 模糊查詢
* %代表任意多個字元，包括0個
* _(下劃線)代表單個字元
* ex:'__a_b%' 代表第三個字是a，第五個字是b，後面長度任意的字串
* 可用\轉譯
* ex:'_\_a_b' 第二個的_會被直接讀取，不會被認為是單個任意字元
* 可用ESCAPE轉譯
* ex:... LIKE '_&_a_b' ESCAPE '&'; ESCAPE後面的字元代表轉譯符號
* 字串要用''(單引號)包起來
* select * from tableName where columnName BETWEEN startValue AND endValue;
* where ... BETWEEN ... AND ...
* ~= where ... >= ... AND ... <= ...;
* select * from tableName where columnName IN('符合的字元', ...);
* ~= where ... = ... OR ... = ...;
* selct columnName from tableName where columnName IS NULL;
* NULL 用=(等號)、<>無法判斷
* 要用IS NULL、IS NOT NULL來判斷
* 僅用來判斷NULL值
* selct columnName from tableName where columnName <=> NULL;
* NULL 可用<=>(安全等於)判斷
* <=>(安全等於) 大概等於 =(等於)
* 可判斷NULL值，也可以判斷一般數值
# 排序查詢
* select * from tableName ORDER BY columnName (ASC);
* 升序
* 從低到高
* 預設ASC，可不填
* select * from tableName ORDER BY columnName DESC;
* 降序
* 從高到低
* select LENGTH(columnName) as 欄位字元長度 from employees order by LENGTH(columnName) desc;
* select LENGTH(columnName) as 欄位字元長度 from employees order by 欄位字元長度 desc;
* 可取得括號內字元長度
* select * from tableName ORDER BY column1 ASC, column2 DESC;
* 當第一個欄位相同時，依照第二個欄位降序排列
# 常用函數
* UPPER(columnName)
* 轉換成大寫
* LOWER(columnName)
* 轉換成小寫
* SUBSTR()、SUBSTRING()
* SUBSTR(要改變的字串, 要保留的起始點INT)
* index從1開始
* SUBSTR(要改變的字串, 要保留的起始點INT, 要保留的長度INT)
* INSTR(要被查找的母體字串, 要找尋的字串)
* 返回第一次找到的位置INDEX，找不到返回0
* TRIM(包含空白的字串)
* 去除左右兩邊的空白
* TRIM('字串A' FROM 左右包含字串A的母體字串)
* 去除左右兩邊的字串A
* LPAD(母體字串, 個數INT, '字串A')
* RPAD(母體字串, 個數INT, '字串A')
* 用母體字串左(右)方加上'字串A'，補齊至個數INT
* 個數INT比母體字串小時，母體字串會從個數INT後面開始砍字
* ex: LPAD('abc',2,'*') 結果會是 'ab'
* REPLACT(母體字串, 要被換的字, 換成的字);
* 母體字串中有多的要被換的字的話會全部換掉
# 數學函數
* ROUND(浮點數)
* ROUND(浮點數, 取幾位數INT)
* 四捨五入
* CEIL(浮點數)
* 向上取整數，返回>=該符點數的最小整數
* FLOOR(浮點數)
* 向下取整數，返回<=該符點數的最大整數
* TRUNCATE(浮點數, 從小數點第幾位後開始捨棄INT)
* 無條件捨去
* MOD(被除數, 用這個去除INT)
* 取餘數
* (知道就好) MOD(A,B) => A-A/B*B
# 日期函數
* NOW()
* 返回系統當前日期+時間
* CURDATE()
* 返回系統當前日期
* CURTIME()
* 返回系統當前時間
* YEAR(時間)
* 返回時間年份
* MONTH(時間)
* 返回時間月份
* MONTHNAME(時間)
* 返回時間月份英文
* DAY()
* 返回時間日期
* STR_TO_DATE(要被轉換的日期, 轉換成的格式)(
* 
* 
* [實際應用狀況](https://youtu.be/p6JJBJ-dUOk?list=PLmOn9nNkQxJHvSwmwwnH3oInxIr7HIZ8U&t=682)
* 可解析各種客製年月份格式
* DATE_FORMAT(要被轉換的日期, 轉換成的格式)
* 
* 
* DATEDIFF(日期1,日期2)
* 返回日期1-日期2的差距天數
# 流程控制函數
* IF(表達式, TRUE的返回值, FALSE的返回值)
* 類似三元運算
* 
* CASE
* 在SELECT中使用方法1
* 
* 此例中從CASE...END會對應該COLUMN WHEN、ELSE的判斷來返回處理過的COLUMN
* 在END後可用AS 取別名
* 等於在SELECT中的特定欄位可另外經過CASE...END處理
* 在SELECT中使用方法2
* 
# 分組函數
* SUM(columnName)
* AVG(columnName)
* MAX(columnName)
* MIN(columnName)
* COUNT(columnName)
* 可以搭配DISTINCT使用 ex:SUM(DISTINCT columnName)
# 分組後查詢
* GROUP BY
* select count(\*), department_id from employees GROUP BY department_id HAVING count(\*)>2;
* 在GROUP BY(分組)後的篩選要用HAVING 條件
* 在from employees後面使用where 是代表在employees這張表中查詢
* 這次的例子是要在分組後的結果中查詢，所以where不適合
* HAVING 後面接的是分組函數，是使用分組函數做判斷
# 連接查詢
* 等值連接
* SQL-92標準
* select NAME, boyName from beauty,boys where beauty.boyfriend_id=boys.id;
* 非等值連接
* ```sql
select salary, grade_level from employees e, job_grades g where salary between g.`lowest_sal` and g.`highest_sal`;
```
* 
* 自連接
* 
* 
* 
# SQL99語法
```sql=
-- 基本格式
SELECT 查詢列表
FROM 表1 別名 (連接類型)
JOIN 表2 別名
ON 連接條件
(WHERE 篩選條件)
(GROUP BY 分組)
(HAVING 篩選條件)
(ORDER BY 排序列表)
-- 分類
-- 內連接 : INNER 連接類型沒寫的話預設INNER
-- 外連接
-- 左外 : LEFT( OUTER)
-- 右外 : RIGHT( OUTER)
-- 全外 : FULL( OUTER) MySQL不支援
-- 交叉連接 : CROSS
```
* 非等值連接
* 非等值連接加篩選
* 自連接加篩選
* 外連接
* 用來查詢一個表中有、另一個表中沒有的紀錄
* 左外連接和右外連接交換順序的話可以達成一樣的結果
* 全外連接
* FULL OUTER JOIN
* MySQL不支援，但可用UNION取得相同結果
* 交叉連接
* CROSS JOIN
* 圖示
* 
* 
# 子查詢
* 定義
* 出現在其他語句內部的select語句
* 內部嵌套其他select語句的查詢，稱為外查詢或主查詢
* 位置可用的子查詢
* SELECT後面
* 僅僅支持標量子查詢
* FROM後面
* 表子查詢
* 多行多列，多row多column
* [FROM表子查詢參考影片](https://youtu.be/OEMgsHT5f34?list=PLmOn9nNkQxJHvSwmwwnH3oInxIr7HIZ8U)
* 似乎需要起別名
* WHERE(HAVING)後面 : 最常用
* 標量子查詢
* 單行單列，單row單column
* [WHERE標量子查詢參考影片](https://www.youtube.com/watch?v=wYKwNfyOdfA&list=PLmOn9nNkQxJHvSwmwwnH3oInxIr7HIZ8U&index=88)
* 列子查詢
* 多行單列，多row單column
* [WHERE列子查詢參考影片](https://www.youtube.com/watch?v=Q39wtI7BLVA&list=PLmOn9nNkQxJHvSwmwwnH3oInxIr7HIZ8U&index=89)
* 行子查詢
* 較少用，單行多列，單row多column
* [WHERE行子查詢參考影片](https://www.youtube.com/watch?v=CTVaB_vlR1A&list=PLmOn9nNkQxJHvSwmwwnH3oInxIr7HIZ8U&index=90)
* 特點
* 子查詢放在小括號內
* 子查詢一般放在條件的右側
* 標量子查詢，一般搭配著單行操作符使用
* EX : > < >= <= <>
* 列子查詢，一般搭配著多行操作符使用
* EX : IN/NOT IN、ANY/SOME、ALL
* 子查詢的執行優先於主查詢執行，主查詢的條件用到了子查詢的結果
* EXISTS後面 : 稱為相關子查詢
* 表子查詢
* [參考影片](https://youtu.be/HwWwkbJivyE?list=PLmOn9nNkQxJHvSwmwwnH3oInxIr7HIZ8U)
* EXISTS(完整的查詢語句)
* return 1 : 有資料，非null
* return 0 : 無資料，null
* 按結果集的行列數不同
* 標量子查詢 : 結果集只有一行一列
* 列子查詢 : 結果集只有一列多行
* 行子查詢 : 結果集只有一行多列
* 表子查詢 : 結果集一般為多行多列
* [練習題](https://youtu.be/dkumi0O3PCA?list=PLmOn9nNkQxJHvSwmwwnH3oInxIr7HIZ8U)
* 
* 第三題兩種解法
# 分頁查詢
* 應用場景
```sql=
-- 基本格式
SELECT 查詢列表
FROM 表1 別名 (連接類型)
JOIN 表2 別名
ON 連接條件
(WHERE 篩選條件)
(GROUP BY 分組)
(HAVING 篩選條件)
(ORDER BY 排序列表)
LIMIT (offset,)size;
--offset 要顯示條目的起始索引(起始索引從0開始)
--size 要顯示的條目個數
--LIMIT size : 一個參數的狀況下第一個參數為size
--LIMIT offset, size : 兩個參數的狀況下第一個參數為起始索引offset，第二個為個數size
```
# 刪除ROW
* DELETE (ALIAS) FROM TableName (AS ALIAS) WHERE 條件
# 修改編碼
* 修改已經存在的 database 預設編碼
* ALTER DATABASE \`example_db\` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
* 要table、column 都一起被調整編碼
* ALTER TABLE \`example_db\`.\`example_table\` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
* [參考網址](https://helloworld.pixnet.net/blog/post/47375778-%E4%BF%AE%E6%94%B9-mysql-charset-%E7%B7%A8%E7%A2%BC)
# 優化資料庫的方法
## MySQL資料庫優化的八個方式
* 選取最適用的欄位屬性，儘可能減少定義欄位寬度，儘量把欄位設定NOTNULL，例如'省份'、'性別'最好適用ENUM
* 使用連線(JOIN)來代替子查詢
* 適用聯合(UNION)來代替手動建立的臨時表
* transaction
* 鎖定表、優化transaction
* [史上最全MySQL鎖機制](https://www.ipshop.xyz/15006.html)
* 適用外來鍵，優化鎖定表
* 建立索引
* 優化查詢語句
## 記錄一次MySQL兩千萬資料的大表優化解決過程，提供三種解決方案
* [網頁](https://www.gushiciku.cn/pl/2UOl/zh-tw)
## MySQL 交易功能 Transaction 整理
* MySQL 常用的兩個資料表類型：MyISAM、InnoDB
* MyISAM 不支援Transaction，所以Transaction的整理，均是針對 InnoDB 而言。
* [XYZ的筆記本](https://xyz.cinc.biz/2013/05/mysql-transaction.html)
* 中國好像把Transaction叫作事務
## MySQL 調優，大表處理探索
* [文章連結](https://www.readfog.com/a/1725129789135753216)
# WITH ... AS ...
* 
* [mysql（多级分销）无限极数据库设计方法](https://www.phpbloger.com/article/50.html)
* [好像是 mysql 官方文件的對 with 說明 & 簡單介紹](https://dev.mysql.com/doc/refman/8.0/en/with.html)
# Laradock 的 mysql 用法
## CMD 使用步驟
* docker-compose exec --user=laradock mysql bash
* 在 laradock 路徑下使用此指令進入 CMD
* mysql -u root -proot
* 使用 root 帳密登入
* CREATE DATABASE `laravel` /*!40100 COLLATE 'utf8mb4_general_ci' */;
* 建立 laravel 資料庫，編碼是 utf8mb4_general_ci
# 優化相關
* [[Mysql進階]各Engine Type(MyISAM / InnoDB / Memory) 的特性說明](https://miggo.pixnet.net/blog/post/30855147)
* [我说MySQL每张表最好不超过2000万数据，面试官让我回去等通知？](https://juejin.cn/post/7165689453124517896)
* [MySQL 是怎样运行的：从根儿上理解 MySQL](https://relph1119.github.io/mysql-learning-notes/#/mysql/01-%E8%A3%85%E4%BD%9C%E8%87%AA%E5%B7%B1%E6%98%AF%E4%B8%AA%E5%B0%8F%E7%99%BD-%E9%87%8D%E6%96%B0%E8%AE%A4%E8%AF%86MySQL)
* [读多写少：MySQL 如何优化数据查询方案？](https://www.youtube.com/watch?v=XRQfBiRsDf8&list=PL5d0qARooeQizrhA4vJP5pJjAVwD90wo1&index=12)
* [分布式系统中，如何回答锁的实现原理？](https://www.youtube.com/watch?v=i-C_dR-0BdU&list=PL5d0qARooeQizrhA4vJP5pJjAVwD90wo1&index=8)
# [为什么 MySQL 使用 B+ 树？](https://my.oschina.net/StoneDB/blog/7610035)
* 
* 
# [ MySQL 几万字 超硬核 的保姆式学习教程](https://segmentfault.com/a/1190000043430057)
* title : 花了几个月时间把 MySQL 重新巩固了一遍，梳理了一篇几万字 “超硬核” 的保姆式学习教程！
# [深入理解MySQL索引底层数据结构](https://segmentfault.com/a/1190000043627999?utm_source=sf-similar-article)
# [最强总结！MySQL 核心应用开发规范都有哪些？](https://www.bilibili.com/video/BV1VU4y1B7cG?t=1754.6)
# [在 Mysql 使用 json](https://michael-hsu.medium.com/%E5%9C%A8-mysql-%E4%BD%BF%E7%94%A8-json-5796a65701ad)
# [在 MySQL 中搜尋 JSON Array 的特定數值](https://hackmd.io/@blueberry/HJAC58ACd/https%3A%2F%2Fhackmd.io%2F3a-Tsq43TH-MkK_aOCo8rQ)
# [一文说透 MySQL JSON 数据类型（收藏）](https://www.cnblogs.com/ivictor/p/16221712.html)
# [一文了解 MySQL 索引機制](https://www.readfog.com/a/1743814690232963072)
Sign in with Wallet
Connect another wallet