mysql資料庫對group（mysql資料庫對比工具）

本文目錄一覽：

• 1、mysql資料庫 group by 報錯 原理是什麼？
• 2、如何對mysql的group
• 3、如何理解mysql中GROUP
• 4、MySql操作「數據查詢」-20211222

mysql資料庫 group by 報錯 原理是什麼？

在日常查詢中，索引或其他數據查找的方法可能不是查詢執行中最高昂的部分，例如：MySQL GROUP BY 可能負責查詢執行時間 90% 還多。MySQL 執行 GROUP BY 時的主要複雜性是計算 GROUP BY 語句中的聚合函數。UDF 聚合函數是一個接一個地獲得構成單個組的所有值。這樣，它可以在移動到另一個組之前計算單個組的聚合函數值。當然，問題在於，在大多數情況下，源數據值不會被分組。來自各種組的值在處理期間彼此跟隨。因此，我們需要一個特殊的步驟。

處理 MySQL GROUP BY讓我們看看之前看過的同一張table：    mysql show create table tbl G    *************************** 1. row ***************************          Table: tbl    Create Table: CREATE TABLE `tbl` (     `id` int(11) NOT NULL AUTO_INCREMENT,     `k` int(11) NOT NULL DEFAULT ‘0’,     `g` int(10) unsigned NOT NULL,     PRIMARY KEY (`id`),     KEY `k` (`k`)    ) ENGINE=InnoDB AUTO_INCREMENT=2340933 DEFAULT CHARSET=latin1    1 row in set (0.00 sec)

並且以不同方式執行相同的 GROUP BY 語句：

1、MySQL中 的 Index Ordered GROUP BY

mysql select k, count(*) c from tbl group by k order by k limit 5;

+—+—+

| k | c |

+—+—+

| 2 | 3 |

| 4 | 1 |

| 5 | 2 |

| 8 | 1 |

| 9 | 1 |

+—+—+

5 rows in set (0.00 sec)

mysql explain select k, count(*) c from tbl group by k order by k limit 5 G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tbl

partitions: NULL

type: index

possible_keys: k

key: k

key_len: 4

ref: NULL

rows: 5

filtered: 100.00

Extra: Using index

1 row in set, 1 warning (0.00 sec)

在這種情況下，我們在 GROUP BY 的列上有一個索引。這樣，我們可以逐組掃描數據並動態執行 GROUP BY（低成本）。當我們使用 LIMIT 限制我們檢索的組的數量或使用「覆蓋索引」時，特別有效，因為順序索引掃描是一種非常快速的操作。

如果您有少量組，並且沒有覆蓋索引，索引順序掃描可能會導致大量 IO。所以這可能不是最優化的計劃。

2、MySQL 中的外部排序 GROUP BY

mysql explain select SQL_BIG_RESULT g, count(*) c from tbl group by g limit 5 G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tbl

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 998490

filtered: 100.00

Extra: Using filesort

1 row in set, 1 warning (0.00 sec)

mysql select SQL_BIG_RESULT g, count(*) c from tbl group by g limit 5;

+—+—+

| g | c |

+—+—+

| 0 | 1 |

| 1 | 2 |

| 4 | 1 |

| 5 | 1 |

| 6 | 2 |

+—+—+

5 rows in set (0.88 sec)

如果我們沒有允許我們按組順序掃描數據的索引，我們可以通過外部排序（在 MySQL 中也稱為「filesort」）來獲取數據。你可能會注意到我在這裡使用 SQL_BIG_RESULT 提示來獲得這個計劃。沒有它，MySQL 在這種情況下不會選擇這個計劃。

一般來說，MySQL 只有在我們擁有大量組時才更喜歡使用這個計劃，因為在這種情況下，排序比擁有臨時表更有效（我們將在下面討論）。

3、MySQL中 的臨時表 GROUP BY

mysql explain select  g, sum(g) s from tbl group by g limit 5 G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tbl

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 998490

filtered: 100.00

Extra: Using temporary

1 row in set, 1 warning (0.00 sec)

mysql select  g, sum(g) s from tbl group by g order by null limit 5;

+—+——+

| g | s    |

+—+——+

| 0 |    0 |

| 1 |    2 |

| 4 |    4 |

| 5 |    5 |

| 6 |   12 |

+—+——+

5 rows in set (7.75 sec)

在這種情況下，MySQL 也會進行全表掃描。但它不是運行額外的排序傳遞，而是創建一個臨時表。此臨時表每組包含一行，並且對於每個傳入行，將更新相應組的值。很多更新！雖然這在內存中可能是合理的，但如果結果表太大以至於更新將導致大量磁碟 IO，則會變得非常昂貴。在這種情況下，外部分揀計劃通常更好。請注意，雖然 MySQL 默認選擇此計劃用於此用例，但如果我們不提供任何提示，它幾乎比我們使用 SQL_BIG_RESULT 提示的計劃慢 10 倍 。您可能會注意到我在此查詢中添加了「 ORDER BY NULL 」。這是為了向您展示「清理」臨時表的唯一計劃。沒有它，我們得到這個計劃：    mysql explain select  g, sum(g) s from tbl group by g limit 5 G    *************************** 1. row ***************************              id: 1     select_type: SIMPLE           table: tbl      partitions: NULL            type: ALL    possible_keys: NULL             key: NULL         key_len: NULL             ref: NULL            rows: 998490        filtered: 100.00           Extra: Using temporary; Using filesort    1 row in set, 1 warning (0.00 sec)

在其中，我們獲得了 temporary 和 filesort 「兩最糟糕的」提示。MySQL 5.7 總是返回按組順序排序的 GROUP BY 結果，即使查詢不需要它（這可能需要昂貴的額外排序傳遞）。ORDER BY NULL 表示應用程序不需要這個。您應該注意，在某些情況下 – 例如使用聚合函數訪問不同表中的列的 JOIN 查詢 – 使用 GROUP BY 的臨時表可能是唯一的選擇。

如果要強制 MySQL 使用為 GROUP BY 執行臨時表的計劃，可以使用 SQL_SMALL_RESULT 提示。

4、MySQL 中的索引基於跳過掃描的 GROUP BY前三個 GROUP BY 執行方法適用於所有聚合函數。然而，其中一些人有第四種方法。

mysql explain select k,max(id) from tbl group by k G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tbl

partitions: NULL

type: range

possible_keys: k

key: k

key_len: 4

ref: NULL

rows: 2

filtered: 100.00

Extra: Using index for group-by

1 row in set, 1 warning (0.00 sec)

mysql select k,max(id) from tbl group by k;

+—+———+

| k | max(id) |

+—+———+

| 0 | 2340920 |

| 1 | 2340916 |

| 2 | 2340932 |

| 3 | 2340928 |

| 4 | 2340924 |

+—+———+

5 rows in set (0.00 sec)

此方法僅適用於非常特殊的聚合函數：MIN() 和 MAX()。這些並不需要遍歷組中的所有行來計算值。他們可以直接跳轉到組中的最小或最大組值（如果有這樣的索引）。如果索引僅建立在 (K) 列上，如何找到每個組的 MAX(ID) 值？這是一個 InnoDB 表。記住 InnoDB 表有效地將 PRIMARY KEY 附加到所有索引。(K) 變為 (K,ID)，允許我們對此查詢使用 Skip-Scan 優化。僅當每個組有大量行時才會啟用此優化。否則，MySQL 更傾向於使用更傳統的方法來執行此查詢（如方法＃1中詳述的索引有序 GROUP BY）。雖然我們使用 MIN() / MAX() 聚合函數，但其他優化也適用於它們。例如，如果您有一個沒有 GROUP BY 的聚合函數（實際上所有表都有一個組），MySQL 在統計分析階段從索引中獲取這些值，並避免在執行階段完全讀取表：    mysql explain select max(k) from tbl G    *************************** 1. row ***************************              id: 1     select_type: SIMPLE           table: NULL      partitions: NULL            type: NULL    possible_keys: NULL             key: NULL         key_len: NULL             ref: NULL            rows: NULL        filtered: NULL           Extra: Select tables optimized away    1 row in set, 1 warning (0.00 sec)

過濾和分組

我們已經研究了 MySQL 執行 GROUP BY 的四種方式。為簡單起見，我在整個表上使用了 GROUP BY，沒有應用過濾。當您有 WHERE 子句時，相同的概念適用：    mysql explain select  g, sum(g) s from tbl where k4 group by g order by NULL limit 5 G    *************************** 1. row ***************************              id: 1     select_type: SIMPLE           table: tbl      partitions: NULL            type: range    possible_keys: k             key: k         key_len: 4             ref: NULL            rows: 1        filtered: 100.00           Extra: Using index condition; Using temporary    1 row in set, 1 warning (0.00 sec)

對於這種情況，我們使用K列上的範圍進行數據過濾/查找，並在有臨時表時執行 GROUP BY。在某些情況下，方法不會發生衝突。但是，在其他情況下，我們必須選擇使用 GROUP BY 的一個索引或其他索引進行過濾：

mysql alter table tbl add key(g);

Query OK, 0 rows affected (4.17 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql explain select  g, sum(g) s from tbl where k1 group by g limit 5 G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tbl

partitions: NULL

type: index

possible_keys: k,g

key: g

key_len: 4

ref: NULL

rows: 16

filtered: 50.00

Extra: Using where

1 row in set, 1 warning (0.00 sec)

mysql explain select  g, sum(g) s from tbl where k4 group by g limit 5 G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tbl

partitions: NULL

type: range

possible_keys: k,g

key: k

key_len: 4

ref: NULL

rows: 1

filtered: 100.00

Extra: Using index condition; Using temporary; Using filesort

1 row in set, 1 warning (0.00 sec)

根據此查詢中使用的特定常量，我們可以看到我們對 GROUP BY 使用索引順序掃描（並從索引中「放棄」以解析 WHERE 子句），或者使用索引來解析 WHERE 子句（但使用臨時表來解析 GROUP BY）。根據我的經驗，這就是 MySQL GROUP BY 並不總是做出正確選擇的地方。您可能需要使用 FORCE INDEX 以您希望的方式執行查詢。

如何對mysql的group

例子 aa表 a b

123 10

123 12

1234 11

1234 14

首先 group 是用來分組的 不是過濾重複項的。重複項刪除語句 DISTINCT用這個 。 select DISTINCT（a） from aa

結果就是 a

123

1234

group by用來分組的

select a, sum(b) from aa group by a

sum意思是總和。結果就是

a b

123 22

1234 25

語句的目的是以a為目標 需要知道 相同名字的物品 在b列一共有多少數量總和

select a,count(b) from aa group by a

count 意思行數總和 結果就是

a b

123 2

1234 2

語句目的是 相同名字的物品 一共有幾行

如何理解mysql中GROUP

就是給數據分類匯總，比如group by name，就是按name分類，相同名字的數據都匯總到這個名字中

MySql操作「數據查詢」-20211222

# SELECT 數據查詢

## 基礎

顯示如何使用簡單的`select`語句查詢單個表中的數據 使用`SELECT`語句從表或視圖獲取數據。

表由行和列組成，如電子表格。 通常，我們只希望看到子集行，列的子集或兩者的組合。

SELECT語句的結果稱為結果集，它是行列表，每行由相同數量的列組成。

select 語法

SELECT語句由以下列表中所述的幾個子句組成：

1. SELECT 之後是逗號分隔列或星號(*)的列表，表示要返回所有列。

2. FROM 指定要查詢數據的表或視圖。

3. JOIN 根據某些連接條件從其他表中獲取數據。

4. WHER E過濾結果集中的行。

5. GROUP BY將一組行組合成小分組，並對每個小分組應用聚合函數。

6. HAVING 過濾器基於GROUP BY子句定義的小分組。

7. ORDER BY 指定用於排序的列的列表。

8. LIMIT 限制返回行的數量。

語句中的`SELECT`和`FROM`語句是必須的，其他部分是可選的。

`SELECT`語句允許通過在`SELECT`子句中指定逗號分隔列的列表來查詢表的部分數據

建議顯式獲取數據的列，原因如下：

1. 使用星號(*)可能會返回不使用的列的數據。 它在MySQL資料庫伺服器和應用程序之間產生不必要的I/O磁碟和網路流量。

2. 如果明確指定列，則結果集更可預測並且更易於管理。 想像一下，當您使用星號(*)並且有人通過添加更多列來更改表格數據時，將會得到一個與預期不同的結果集。

3. 使用星號(*)可能會將敏感信息暴露給未經授權的用戶

格式 `select 列篩選 form table where 行篩選`

還有一些有用的運算符可以在WHERE子句中使用來形成複雜的條件，例如：

BETWEEN 選擇在給定範圍之內的值。

LIKE 匹配基於模式匹配的值。

IN 指定值是否匹配列表中的任何值。

IS NULL 檢查該值是否為NULL。

## SELECT 子查詢

在一個查詢過程中 嵌套另一個查詢，子查詢的結果作為外部查詢的條件或者數據範圍來使用。

分為 3 類：

1. where 型

– `select展示列名 from 表名 where 列名 運算符[in…] (select 對應列名 from …)`

– 這個列名 和 對應列名 應該做到類型相同

– 如果不加入運算符 也可使用IN 這些類似的符號 – `select 展示列名 from 表名 where 列名 in (select 對應列名 from …)`

– ex:

`select 展示列名 from 表名 where 列名 ALL（select 對應列名 from ….)`;

比子查詢的值都大

`select 展示列名 from 表名 where 列名 ANY（select 對應列名 from ….);`

比子查詢的任意一個值大

2. from 型

– `select 展示列名 from 表名 inner join (select 列名 from …) 臨時表名 on 條件；`

– 其中，select的子查詢所得的表 為臨時表，後跟臨時表名，可在條件判斷中指代

3. exist 型

– `select 展示列 from 表名 where exists （select 列名 from 表名 where 條件）;`

– 將主查詢的結果帶入子查詢進行條件判斷和匹配，如果查詢出結果即保留。

## 去重 DISTINCT

SELECT 語句執行簡單的數據查詢時，返回的是所有匹配的記錄。`distinct` 實現查詢不重複的數據

**DISTINCT 關鍵字的主要作用就是對數據表中一個或多個欄位重複的數據進行過濾，只返回其中的一條數據給用戶。**

使用 `DISTINCT` 關鍵字時需要注意以下幾點：

– `DISTINCT` 關鍵字只能在 `SELECT` 語句中使用。

– 在對一個或多個欄位去重時，`DISTINCT` 關鍵字必須在所有欄位的最前面。

– 如果 `DISTINCT` 關鍵字後有多個欄位，則會對多個欄位進行組合去重，也就是說，只有多個欄位組合起來完全是一樣的情況下才會被去重。

## 指定別名 AS

### 1. 為表指定別名

1. 當表名很長的時候 或者 執行了一些特殊的查詢的時候，為方便操作，可以為表指定一個別名，用以替代原來的名稱

2. 語法.

3. `表名 as 別名` – 含義： – `表名` : 資料庫中存儲的數據表名稱。

– `別名` : 查詢的時候指定的新的名稱。

– `as` : 此關鍵字 可以 省略，省略之後要將 `表名`與`別名`用 `空格` 分開

** *注意：表的別名不能與該資料庫的其它表同名。欄位的別名不能與該表的其它欄位同名。在條件表達式中不能使用欄位的別名，否則會出現「ERROR 1054 (42S22): Unknown column」這樣的錯誤提示信息。* **

*** ex1:

***

### 2. 為欄位指定別名

1. 在使用 SELECT 語句查詢數據時，MySQL 會顯示每個 SELECT 後面指定輸出的欄位。有時為了顯示結果更加直觀，我們可以為欄位指定一個別名。

2. 語法：

3. `欄位名 [AS] 別名`

– 含義：

– `欄位名`：為數據表中欄位定義的名稱。

– `欄位別名`：欄位新的名稱。

– `AS` 關鍵字可以省略，省略後需要將欄位名和別名用空格隔開

** *注意：表別名只在執行查詢時使用，並不在返回結果中顯示。而欄位定義別名之後，會返回給客戶端顯示，顯示的欄位為欄位的別名* ** ***

ex2:

***

## 限制查詢條數 LIMIT

1. LIMIT 關鍵字有 3 種使用方式，即

– `指定初始位置`、

– `不指定初始位置`

– `OFFSET 組合`使用

（。。。。。 我之前一直不知道， 只會使用 `limit 200`。。。。。）

### 指定初始位置

1. 語法

– `LIMIT 初始位置，記錄數`

– 初始位置」表示從哪條記錄開始顯示；第一條記錄的位置是 0，第二條記錄的位置是 1。後面的記錄依次類推。

– 「記錄數」表示顯示記錄的條數。

– *LIMIT 後的兩個參數必須都是正整數。

* ex：tb_students_info 表中，使用 LIMIT 子句返回從第 4 條記錄開始的行數為 5 的記錄，SQL 語句和運行結果如下。

### 不指定初始位置

記錄從第一條記錄開始顯示。顯示記錄的條數由 LIMIT 關鍵字指定。

1. 語法

– `LIMIT 5`

– `SELECT * FROM tb_students_info LIMIT 15;`

### LIMIT 和 OFFSET 組合使用

1. 語法

– `LIMIT 記錄數 OFFSET 初始位置`

– 參數和 LIMIT 語法中參數含義相同，「初始位置」指定從哪條記錄開始顯示；「記錄數」表示顯示記錄的條數。

該語句返回的是從第 4 條記錄開始的之後的 5 條記錄。即「 LIMIT 5 OFFSET 3 」意思是獲取從第 4 條記錄開始的後面的 5 條記錄，和「 LIMIT 3 ， 5 」返回的結果相同。

*** 2021-12-22 今天先學到這裡 明天繼續 MySql 的查詢 學習