MySQL（二）
資料庫查詢-DQL
- DQL英文全稱是Data Query Language(資料查詢語言)，用來查詢資料庫表中的記錄。
- 關鍵字：SELECT
語法
| 關鍵字和引數 | 說明 |
|---|---|
| select 欄位列表 | 基本查詢 |
| from 表名列表 | |
| where 條件列表 | 條件查詢 |
| group by 分組欄位列表 | 分組查詢 |
| having 分組後條件列表 | |
| order by 排序欄位列表 | 排序查詢 |
| limit 分頁引數 | 分頁查詢 |
查詢多個欄位：
select 欄位1, 欄位2, 欄位3 from 表名;
查詢所有欄位（萬用字元）：
select * from 表名;
設定別名：
select 欄位1 [ as 別名1 ], 欄位2 [ as 別名2 ] from 表名;
去除重複記錄：
select distinct 欄位列表 from 表名;
注意事項
- *號代表查詢所有欄位，在實際開發中儘量少用（不直觀、效率低）。
- 別名會顯示在查詢結果的表頭中。
- 設定別名時as可省略，且如果別名中包含空格或其他特殊字元，需要用引號圍起來。
條件查詢
select 欄位列表 from 表名 where 條件列表;
select 欄位列表 from 表名 where (欄位1, 欄位2...) = (值1, 值2...); -- 指定多個欄位值查詢時也可以這樣寫
條件運算子
| 比較運算子 | 功能 |
|---|---|
| > | 大於 |
| >= | 大於等於 |
| < | 小於 |
| <= | 小於等於 |
| = | 等於 |
| <> 或 != | 不等於 |
| between ... and ... | 在某個範圍之內(含最小、最大值) |
| in(...) | 在in之後的列表中的值，多選一，用逗號分隔 |
| like 佔位符 | 模糊匹配( _ 匹配單個字元，% 匹配任意個字元) |
| is null | 是null |
| 邏輯運算子 | 功能 |
|---|---|
| and 或 && | 並且 (多個條件同時成立) |
| or 或 || | 或者 (多個條件任意一個成立) |
| not 或 ! | 非 , 不是 |
聚合函式
- 作用：將一列資料作為一個整體，進行縱向計算。
- 語法：
select 聚合函式(欄位列表) from 表名;
| 函式 | 功能 |
|---|---|
| count() | 統計數量 |
| max() | 最大值 |
| min() | 最小值 |
| avg() | 平均值 |
| sum() | 求和 |
注意事項
- null值不參與所有聚合函式運算。
- 統計數量可以使用：count(*)，count(欄位)，count(常量)，推薦使用count(*)。count(*) 的作用是統計表的行數，null也會統計；count(常量)的作用也是統計表的行數，但不包括null； count(欄位) 的作用則是統計該欄位值中不為null的行數。
分組查詢
- 語法：
select 欄位列表 from 表名 [ where 條件 ] group by 分組欄位名 [ having 分組後過濾條件 ];
- where與having區別：
- 執行時機不同：where是分組之前進行過濾，不滿足where條件，不參與分組；而having是分組之後對結果進行過濾。
- 判斷條件不同：where不能對聚合函式進行判斷，而having可以。
注意事項
- 分組之後，查詢的欄位一般為聚合函式和分組欄位，查詢其他欄位無任何意義。
- 執行順序：where > 聚合函式 > having 。
排序查詢
- 語法：
select 欄位列表 from 表名 [ where 條件列表 ] [ group by 分組欄位 [ having 分組後過濾條件 ] ] order by 欄位1 排序方式1, 欄位2 排序方式2 … ;
- 排序方式：
- ASC：升序（預設值）
- DESC：降序
注意事項
- 如果是多欄位排序，當前面所有欄位值都相同時，才會根據當前欄位進行排序。
分頁查詢
- 語法：
select 欄位列表 from 表名 limit 起始索引, 查詢記錄數;
注意事項
- 起始索引從0開始，起始索引 = （查詢頁碼 - 1）* 每頁顯示記錄數。
- 分頁查詢是資料庫的方言，不同的資料庫有不同的實現，MySQL中是LIMIT。
- 如果查詢的是第一頁資料，起始索引可以省略，直接簡寫為 limit 查詢記錄數。
流程控制函式
- if表示式：
if(表示式, tvalue, fvalue) -- 當表示式為true時，取值tvalue；當表示式為false時，取值fvalue。
- case表示式：
case expr when value1 then result1 [when value2 then value2 ...] [else result] end -- 類似於Java中的switch語句
多表設計
專案開發中，在進行資料庫表結構設計時，會根據業務需求及業務模組之間的關係，分析並設計表結構，由於業務之間相互關聯，所以各個表結構之間也存在著各種聯絡，基本上分為三種：
- 一對多(多對一)
- 多對多
- 一對一
一對多
-
例子：部門及員工的關係。
-
一對多關係實現：在資料庫表中多的一方新增欄位，來關聯一的一方的主鍵。
-
此時，一的一方也叫父表，多的一方也叫子表。
外來鍵約束
- 在資料庫層面，將父表和子表建立起關聯，以保證資料的一致性和完整性。
物理外來鍵
-
概念：使用 foreign key 定義外來鍵關聯另外一張表。
-
語法：
-- 建立表時指定
create table 表名(
欄位名 資料型別,
...
[constraint] [外來鍵名稱] foreign key (外來鍵欄位名) references 父表名(欄位名)
);
-- 建完表後，新增外來鍵
alter table 表名 add constraint 外來鍵名稱 foreign key (外來鍵欄位名) references 父表名(欄位名);
- 缺點：
- 影響增、刪、改的效率（需要檢查外來鍵關係）。
- 僅用於單節點資料庫，不適用與分散式、叢集場景。
- 容易引發資料庫的死鎖問題，消耗效能。
邏輯外來鍵
- 概念：在業務層邏輯中，解決外來鍵關聯。
- 透過邏輯外來鍵，就可以很方便的解決物理外來鍵的問題。
- 推薦使用。
一對一
-
例子：使用者與身份證資訊的關係。
-
一對一的關係，多用於單表拆分，將一張表的基礎欄位放在一張表中，其他欄位放在另一張表中，以提升操作效率。
-
實現：在任意一方加入外來鍵，關聯另外一方的主鍵，並且設定外來鍵為唯一的(UNIQUE)。
多對多
- 例子：學生與課程的關係。
- 一個學生可以選修多門課程，一門課程也可以供多個學生選擇。
- 實現：建立第三張中間表，中間表至少包含兩個外來鍵，分別關聯兩方主鍵。