MySQL 學習紀錄

tags: SQL MySQL 學習紀錄

目錄

• MySQL 學習紀錄
• 目錄
• 影片進度
• 通用
• 基礎查詢
• 排序查詢
• 常用函數
• 數學函數
• 日期函數
• 流程控制函數
• 分組函數
• 分組後查詢
• 連接查詢
• SQL99語法
• 子查詢
• 分頁查詢
• 刪除ROW
• 修改編碼
• 優化資料庫的方法
  • MySQL資料庫優化的八個方式
  • 記錄一次MySQL兩千萬資料的大表優化解決過程，提供三種解決方案
  • MySQL 交易功能 Transaction 整理
  • MySQL 調優，大表處理探索
• WITH … AS …
• Laradock 的 mysql 用法
  • CMD 使用步驟
• 優化相關
• 为什么 MySQL 使用 B+ 树？
• MySQL 几万字 超硬核 的保姆式学习教程
• 深入理解MySQL索引底层数据结构
• 最强总结！MySQL 核心应用开发规范都有哪些？
• 在 Mysql 使用 json
• 在 MySQL 中搜尋 JSON Array 的特定數值
• 一文说透 MySQL JSON 数据类型（收藏）
• 一文了解 MySQL 索引機制

影片進度

• 進度

通用

• mysql -u 帳號 -p密碼
  • 密碼跟前面的-p中間是沒有空格的
• show databases;
  • 顯示目前有的資料庫
• use 資料庫名;
  • 選擇資料庫
• show tables( from 資料庫名);
  • 顯示目前(from)資料庫內的表
• select database();
  • 顯示目前資料庫名
• desc tableName;
  • 顯示該table 的結構
• ​​  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(要被轉換的日期, 轉換成的格式)(
  • 
  • 
  • 實際應用狀況
  • 可解析各種客製年月份格式
• 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;
• 非等值連接
  • ​​​​​​​ select salary, grade_level from employees e, job_grades g where salary between g.`lowest_sal` and g.`highest_sal`;
    

  • 
• 自連接
  • 
  • 
  • 

SQL99語法

-- 基本格式
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表子查詢參考影片
      • 似乎需要起別名
    • WHERE(HAVING)後面 : 最常用
      • 標量子查詢
        • 單行單列，單row單column
        • WHERE標量子查詢參考影片
      • 列子查詢
        • 多行單列，多row單column
        • WHERE列子查詢參考影片
      • 行子查詢
        • 較少用，單行多列，單row多column
        • WHERE行子查詢參考影片
      • 特點
        • 子查詢放在小括號內
        • 子查詢一般放在條件的右側
        • 標量子查詢，一般搭配著單行操作符使用
          • EX : > < >= <= <>
        • 列子查詢，一般搭配著多行操作符使用
          • EX : IN/NOT IN、ANY/SOME、ALL
        • 子查詢的執行優先於主查詢執行，主查詢的條件用到了子查詢的結果
    • EXISTS後面 : 稱為相關子查詢
      • 表子查詢
        • 參考影片
      • EXISTS(完整的查詢語句)
        • return 1 : 有資料，非null
        • return 0 : 無資料，null
  • 按結果集的行列數不同
    • 標量子查詢 : 結果集只有一行一列
    • 列子查詢 : 結果集只有一列多行
    • 行子查詢 : 結果集只有一行多列
    • 表子查詢 : 結果集一般為多行多列
  • 練習題
    • 
    • 第三題兩種解法
      

分頁查詢

• 應用場景

-- 基本格式
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;
• 參考網址

優化資料庫的方法

MySQL資料庫優化的八個方式

• 選取最適用的欄位屬性，儘可能減少定義欄位寬度，儘量把欄位設定NOTNULL，例如'省份'、'性別'最好適用ENUM
• 使用連線(JOIN)來代替子查詢
• 適用聯合(UNION)來代替手動建立的臨時表
• transaction
• 鎖定表、優化transaction
  • 史上最全MySQL鎖機制
• 適用外來鍵，優化鎖定表
• 建立索引
• 優化查詢語句

記錄一次MySQL兩千萬資料的大表優化解決過程，提供三種解決方案

• 網頁

MySQL 交易功能 Transaction 整理

• MySQL 常用的兩個資料表類型：MyISAM、InnoDB
• MyISAM 不支援Transaction，所以Transaction的整理，均是針對 InnoDB 而言。
• XYZ的筆記本
• 中國好像把Transaction叫作事務

MySQL 調優，大表處理探索

• 文章連結

WITH … AS …

• 
• mysql（多级分销）无限极数据库设计方法
• 好像是 mysql 官方文件的對 with 說明 & 簡單介紹

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) 的特性說明
• 我说MySQL每张表最好不超过2000万数据，面试官让我回去等通知？
• MySQL 是怎样运行的：从根儿上理解 MySQL
• 读多写少：MySQL 如何优化数据查询方案？
• 分布式系统中，如何回答锁的实现原理？

为什么 MySQL 使用 B+ 树？

• 
• 

MySQL 几万字 超硬核 的保姆式学习教程

• title : 花了几个月时间把 MySQL 重新巩固了一遍，梳理了一篇几万字 “超硬核” 的保姆式学习教程！

深入理解MySQL索引底层数据结构

最强总结！MySQL 核心应用开发规范都有哪些？

在 Mysql 使用 json

在 MySQL 中搜尋 JSON Array 的特定數值

一文说透 MySQL JSON 数据类型（收藏）

一文了解 MySQL 索引機制