[MySQL] MySQL 表的增刪查改

? 本篇文章對mysql表的增刪查改進行了詳細的舉例說明解釋。對表的增刪查改簡稱CRUD : Create(創建), Retrieve(讀取)，Update(更新)，Delete（刪除）。其中重點是對查詢select語句進行了詳細解釋，并且通過多個實際例子來幫助你的理解。希望本篇文章會對你有所幫助。?

文章目錄

一、表的插入

1、1 指定列插入和多行數據插入

1、2 全列插入

1、3 插入選擇更新

1、4 替換數據

二、表中的數據查詢

2、1 select 語句

2、1、1 全列查詢

2、1、2?指定列查詢

2、1、3 查詢字段為表達式

2、1、4 為查詢結果列指定別名

2、1、5 對查詢結果去重

2、2 where 語句

2、2、1 where語句簡單說明

2、2、2 實例練習

2、3 對查詢結果進行排序

2、3、1 升序排序

2、3、2 降序排序

2、3、3 按照多列進行排序

2、4 對篩選的結果進行分頁

三、表的數據更新

四、表的數據刪除

4、1 delete 刪除數據

4、2 truncate 截斷表

五、group by 與聚合函數

5、1 聚合函數

5、2 group by語句?

🙋?♂??作者：@Ggggggtm?🙋?♂?

👀?專欄：MySQL?👀

💥?標題：MySQL 表的增刪查改💥

????寄語：與其忙著訴苦，不如低頭趕路，奮路前行，終將遇到一番好風景???

一、表的插入

1、1 指定列插入和多行數據插入

? 我們向在表中指定的一行或者多行插入，怎么做呢？我們有如下表：

? 我們現在直插入name的一列，只需要指定要插入的列名稱即可。如下圖：

? 我們也可以進行多列進行插入，如下圖實例：

? 但是前提是我們必須滿足約束條件，正常插入都是可以的。

? 我們也可以一次性插入多行數據，具體示例如下：

? 從上圖也可以很好的看出來，多行插入就是將數據用 ‘，’ 進行隔開即可。

1、2 全列插入

? 上述情況中講述了指定列進行插入。當對全列進行插入時，可以不指定對應的列，此時默認的就是全列插入。具體如下圖：?

? 當然全列插入也可以顯式指定的列名稱，一般情況下都是默認省去的。

1、3 插入選擇更新

? 當我們在插入數據時，可能會遇到主鍵約束或者唯一鍵約束，從而限制了插入操作。具體如下圖：

? 上圖就是出先了主鍵約束，導致插入失敗。但是我們就想以最新的這一條為最終數據，且要求能夠插入進去，該怎么辦呢？這時可以選擇性的進行同步更新操作，語法如下：
INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...
? 我們來看一個具體的實際例子：

? 結合上述的實例，這里簡單說名一下：

UPDATE后面的 column=value，表示當插入記錄出現沖突時需要更新的列值；
當插入記錄并沒有沖突時，就不會執行update更新語句，也就是直接插入。

上述例子中，2 row affected: 表中有沖突數據，并且數據已經被更新。

我們再來插入幾條數據觀察一下結果，如下圖：

根據上圖，我們再來總結一下：

0 row affected: 表中有沖突數據，但沖突數據的值和 update 的值相等；
1?row affected: 表中沒有沖突數據，數據被插入；
2 row affected: 表中有沖突數據，并且數據已經被更新；
如產生數據沖突，且要求更新后的值不能再次產生沖突，否則會更新失敗。

1、4 替換數據

? 替換數據與插入和更新數據有點類似。我們直接來看一個實例：

? 我們再來對替換進行簡單的總結一下：

如果表中沒有沖突數據，則直接插入數據。
如果表中有沖突數據，則先將表中的沖突數據刪除，然后再插入數據。

? 從上圖中可以看到，有2 rows affected。說明表中有沖突數據，沖突數據被刪除后重新插入。我們在如下實例：

? 我們再來總結一下，可以通過受影響的數據行數來判斷本次數據的插入情況：

1 row affected：表中沒有沖突數據，數據直接被插入。
2 rows affected：表中有沖突數據，沖突數據被刪除后重新插入。

二、表中的數據查詢

2、1 select 語句

? select語法如下：
SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...
? 說明：

SQL中大寫的表示關鍵字，[ ]中代表的是可選項。
{ }中的 | 代表可以選擇左側的語句或右側的語句。

? 下面我們將會對select進行逐步分析講解。

2、1、1 全列查詢

? 全列查詢就是將表中的所有數據查詢出來。其實在上面我們也一直在用。具體也可看下圖理解：

? 上圖中的 * 代表了所有的列，也就是全列查詢。但是通常情況下不建議使用 * 進行全列查詢

查詢的列越多，意味著需要傳輸的數據量（查詢到的數據需要通過網絡從MySQL服務器傳輸到本主機）越大；
可能會影響到索引的使用。

2、1、2?指定列查詢

? 我們也可以指定列進行查詢，具體如下圖：

? 同時我們也可以指定多列，也不需要按照創建表的順序來查找。具體如下：

2、1、3 查詢字段為表達式

? select后面不僅僅可以跟字段屬性或者子句，也可以跟表達式。我們看如下圖：

? 如上圖所示，select后表達式中可以包含常量，比如數字或字符串。可以進行基本的數學運算，如加法、減法、乘法和除法。但是我們主要注意的是，NULL并不參與任何運算。

? 我們再看如下例子：

? 上圖中的english+10是對所查到的english成績進行了加10操作，這里不要理解錯誤了。我們再看如下例子：

? 上圖的SQL語句中，我們主要是對三項成績進行的相加，也就是我們平常所需的總成績。

2、1、4 為查詢結果列指定別名

? 在上述的總成績中，我們不難看出總成立的列明太長了，而且意思表達的也不明確。在我們查詢中，就可以為查詢的結果列指定別名。語法如下：
SELECT column [AS] alias_name [...] FROM table_name;
? 具體實例如下圖：

? 上圖中我們寫出了兩種方法，其實是一種：as 可以省去。我們只需要將別名跟在我們所查詢的列名后即可。

2、1、5 對查詢結果去重

? 有時候我們所查出來的數據會有大量的重復，然而我們并不需要這樣的結果。而是想直接查看去重后的結果。具體如下圖：

2、2 where 語句

2、2、1 where語句簡單說明

? 在MySQL中，select語句用于從數據庫表中檢索數據。where子句用于篩選select語句的結果集，只返回滿足特定條件的行。以下是對where子句的一些常見用法和詳解：

選擇特定列：WHERE子句通常與列名一起使用，用于篩選特定列的數據。
比較運算符：WHERE子句可以使用各種比較運算符（如等于（=）、不等于（!=）、大于（>）、小于（<）、大于或等于（>=）和小于或等于（<=））等來篩選數據。
邏輯運算符：WHERE子句還可以使用邏輯運算符（如AND、OR和NOT）來組合多個條件。
聚合函數：WHERE子句還可以與聚合函數一起使用，如COUNT、SUM、AVG等，以對數據進行計數、求和或計算平均值等操作。

2、2、2 實例練習

? 我們練習使用的表仍然是 exam_result 表，如下圖：

英語不及格的同學及英語成績 ( < 60 )

首先分析我們都需要查詢哪些信息：英語成績和同學姓名。在select查詢的列為姓名和英語成績后，再用where子句來篩選英語成績小于60即可。結果圖下圖：

語文成績在 [80, 90] 分的同學及語文成績

? 所需要查詢的列：語文成績和姓名。篩選條件：chinese >= 80 并且 chinese <= 90。這時候我們就可以用到AND進行連接。如下圖：

? 注意：sql語句中不區分大小寫。

? 我們發現兩邊都是閉區間，所以我們還可以使用between A and B來進行篩選。結果如下圖：

數學成績是 58 或者 59 或者 98 或者 99 分的同學及數學成績

? 所需查詢的列：數學成績和同學姓名。篩選條件：數學成績是 58 或者 59 或者 98 或者 99 。我們發現他們之間的關系是或，可以用 or 來進行篩選。如下圖：

? 此外，我們也可以使用in（A，B，C，D，......）。具體如下圖：

姓孫的同學及孫某同學?

? 所要查詢的列：同學姓名。篩選條件：模糊匹配性孫的同學。注意：姓孫的同學和孫某同學是不一樣的概念。姓孫的同學，姓名可以是兩個字，三個字等等。孫某代表的是姓孫，且姓名為兩個字的同學。

??可以使用like關鍵字進行模糊匹配，用于匹配特定模式的數據。like關鍵字可以與通配符配合使用，通配符包括 % 和 _ 。

% 通配符：匹配任意長度的任意字符，包括零個字符。例如，'%abc'可以匹配以abc結尾的任意字符串；'abc%'可以匹配以abc開頭的任意字符串；'%abc%'可以匹配包含abc的任意字符串。
_ 通配符：匹配單個字符。例如，'a_c'可以匹配abc、adc等。

? 那么就很好的可以匹配到我們所需要查找的姓孫的同學或者孫某同學了，具體如下圖：

? 我們再來看看對孫某同學的查詢：

語文成績好于英語成績的同學?

? 所需查詢的列：同學姓名，語文、英語成績。篩選條件：語文成績大于英語成績。在比較時，可以兩個列進行比較（條件中比較運算符兩側都是字段）。具體如下圖：

總分在 200 分以下的同學?

? 所需要查詢的列：同學姓名，三科總成績。篩選條件：總成績小于200分。具體如下圖：

? 上圖中我們對三科成績進行了指定別名，打印輸出的正是我們指定的別名。那么有的同學就想到：在where子句中也使用total去判斷篩選，這樣就會更加方便。問題是：這樣可以嗎？我們看如下圖：

? 實際上并不可以的，上述報錯中提到找不到 ‘total’ 列。為什么呢？我們不是指定別名了嗎？這就與sql語句的執行順序有關了。執行順序與下圖：

? 我們來分析一下為什么這樣執行：首先應該找到在那張表里去篩選數據，然后帶著篩選條件去查找遍歷數據，最后將找到的數據進行選擇列進行打印。那么where子句比指定別名要先執行，所以在where子句中找不到我們指定的別名。總的來說，在where子句中不能使用select中指定的別名：

查詢數據時是先根據where子句篩選出符合條件的記錄。
然后再將篩選出的數據進行打印出我們所需要的列，打印前會執行指定別名。

語文成績 > 80 并且不姓孫的同學?

? 所需要查詢的列：語文成績，同學姓名。篩選條件：語文成績大于80，并且不性孫（not like）。我們看如下圖：

孫某同學，否則要求總成績 > 200 并且語文成績 < 數學成績并且英語成績 > 80?

? 查詢的列：同學姓名，各科成績和總成績。篩選條件：孫某或者?總成績 > 200 并且語文成績 < 數學成績并且英語成績 > 80。我們看如下結果：

? 注意：當有多個與、或條件時，我們應該多加括號來保證執行順序，避免出現不必要的錯誤。

NULL 的查詢?

?NULL的查詢比較特殊。我們前面也提到了NULL不能用 ‘=’ 來判斷，是不安全的。結果如下：

? 因為NULL并不參與運算。所以結果只能是NULL。我們可以使用 ‘<=> ’來判斷NULL。結果如下：

? 然而 ‘<=> ’并不符合我們的使用習慣。所以我們也可以使用is null 或者 is not null來判斷。我們現在有如下表：

? 我們把sex為NULL的行找出來。結果如下：

? 我們再來找到sex不為null行，結果如下圖：

2、3 對查詢結果進行排序

??在MySQL中，可以使用ORDER BY子句對查詢結果進行排序。ORDER BY子句允許您按照一個或多個列的值對結果進行排序，以及是升序（ASC）還是降序（DESC）排序和默認為升序（ASC）。注意：沒有 ORDER BY 子句的查詢，返回的順序是未定義的，永遠不要依賴這個順序。

2、3、1 升序排序

同學及數學成績，按數學成績升序顯示

? 所要查詢的列：姓名，數學成績。要求：按照成績升序。結果如下圖：

2、3、2 降序排序

? 查詢同學及總分，由高到低?

? 所需要查詢的列：姓名，總成績。要求：總成績從高到低，也就是降序。結果如下圖：

? 上述結果確實是按照降序進行排序的。但是我們發現order by 子句中可以使用列的別名。這又是為什么呢？這也與其select語句的執行順序有關，如下圖：

? 我們可以理解為，先找到所需要查詢的表，然后查詢所有的指定的列的數據，最后對查找的數據進行排序。所以在order by 子句中是可以使用列的別名。

? 在這里說明一下：NULL值比任何值都要小。

查詢姓孫的同學或者姓曹的同學數學成績，結果按數學成績由高到低顯示

? 所需查詢的列：姓名和數學。篩選條件：姓孫或者姓曹。要求：成績由高到低，也就是降序。結果如下：

2、3、3 按照多列進行排序

查詢同學各門成績，依次按數學降序，英語升序，語文升序的方式顯示?

所需要查詢的列：同學姓名和各科成績。要求：依次按數學降序，英語升序，語文升序。我們看如下結果：

注意：所有行首先是按照math進行降序排序。然后再math相同的情況下再按照english進行升序排序。最后再math相同，english相同的情況下，再按照語文進行升序排序。發現越靠后的排序，其要求的條件越多。

2、4 對篩選的結果進行分頁

??在MySQL中，可以使用LIMIT和OFFSET子句對篩選結果進行分頁。LIMIT用于指定每頁返回的記錄數量，而OFFSET用于指定從第幾行開始返回結果。常用語法如下：
-- 從 0 開始，篩選 n 條結果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;-- 從 s 開始，篩選 n 條結果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;-- 從 s 開始，篩選 n 條結果，比第二種用法更明確，建議使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
? 我們結合如下實例來理解一下：

? 通常情況下，一張表的數據會很多，我們需要將這張表進行分頁顯示。就可以用到 limit 語句。具體如下圖：

? 我們將七條語句分為了3頁進行顯示。注意，當從某個偏移量開始打印n行數據時，且該表中從某個偏移量開始到最后并不夠n行，那么默認就打印到最后一行。

??建議：對未知表進行查詢時，最好加一條 LIMIT 1，避免因為表中數據過大，查詢全表數據導致數據庫卡死。或者按 id 進行分頁，每頁 3 條記錄，分別顯示第 1、2、3 .....頁

三、表的數據更新

? 在MySQL中，可以使用UPDATE語句對表進行更新。UPDATE語句用于修改表中的數據。語法如下：
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
? 簡單說明：

table_name是要更新的表的名稱。
column1,?column2, ... 是要更新的列的名稱。
value1,?value2, ... 是要設置的新值。
WHERE condition是一個可選的條件，用于指定要更新的行。如果不提供條件，則會更新表中的所有行。

? 下面我們看幾個實際例子來理解一下。下面的實例中全部是根據下表進行更新的：

將孫悟空同學的數學成績變更為 80 分?

? 我們只需要確定要更新的列和更新的條件即可。結果如下圖：

? 注意：一定要加where條件進行篩選，否則所有人的數學成績都將被更新為80。

將曹孟德同學的數學成績變更為 60 分，語文成績變更為 70 分
? ?我們先來查看一下曹孟德的成績：

? 接下來我們再修改發現思路就會很清晰了，修改結果如下圖：

將總成績倒數前三的 3 位同學的數學成績加上 30 分
? 怎么可以很好的找到倒數前三名同學呢？升序排序，然后使用limit不就行了嗎！我們先篩選出倒數前三的同學，如下：

? 那么接下來就對倒數前三名同學的數學成績加上30分的操作，具體如下：

? 我們對比后發現，他們的總成績確實增加了30。那么排名當然也會有所改變。需要注意的是：在更新數學成績時，不支持 math += 30 這種語法。

將所有同學的語文成績修改為原來的2倍

? 我們發現是所有同學，所以就不用再篩選同學了。直接看結果：

? 注意，在我們使用更新全表的語句之前，一定要先確定你是否要對所有數據進行更新！

四、表的數據刪除

4、1 delete 刪除數據

? 在MySQL中，要刪除表中的數據，可以使用DELETE語句。DELETE語句用于從表中刪除滿足特定條件的行。以下是刪除表中的數據的語法：
DELETE FROM table_name WHERE condition;
? 其中，table_name是要刪除數據的表的名稱，condition是一個可選的條件，用于指定要刪除的行。如果要刪除所有行，可以使用通配符*。下面我們結合實際例子來理解一下。

刪除孫悟空同學的考試成績
? ?我們直接看結果：

? 我們看到，孫悟空的id為2，所以那一行的數據就會被全部刪除。

刪除整張表的數據

? 當我們在刪除某個表中的數據時，并不添加任何篩選條件，相當于刪除整張表中的數據。我們先來創建一個表并且插入一些新的數據，如下圖：

? 我們在對整張表進行刪除，如下圖：

? 我們再來插入一些數據觀察一下：

? 通過上圖發現，我們新插入的值的id并不是從0開始的。而是接著原來的自增長的值進行插入的。我們再來查看一下表結構和創建表時的相關信息。如下圖：

? 通過上圖可以看到，delete刪除數據并不會刪除表的結構，同時有一個AUTO_INCREMENT=n的字段，該字段就是我們設置的自增長字段，應并不會對此產生影響。

4、2 truncate 截斷表

? 我們在使用truncate對表的數據進行刪除，如下圖：

? 再來進行插入一些數據觀察一下，如下圖：

? 通過上圖可以發現，這次的id值是從1開始了。我們再來觀察一下表結構和創建表時的相關信息，如下圖：

? 通過上圖可以發現，truncate會重置auto_increment字段的。但是，truncate只能對整表操作，不能像delete一樣針對部分數據操作。

??delete語句用于刪除指定條件下的行。它會保留表結構，并且可以使用WHERE子句來選擇要刪除的特定行。但是，delete操作可能會導致性能問題，特別是當表非常大時，因為每次刪除都需要進行磁盤I/O操作。另一方面，truncate語句用于刪除表中的所有行，包括索引和約束等對象。與delete不同，TRUNCATE不會記錄任何事務日志，因此執行速度非常快。但是，一旦執行了truncate操作，就無法恢復被刪除的數據，除非有備份。

??綜上所述，如果你需要刪除表中的某些特定行，應使用delete。而如果你需要從表中快速刪除所有數據，并且可以接受無法恢復丟失數據的風險，那么應該使用truncate。需要注意的是，在使用truncate之前，請確保已經創建了適當的備份以防萬一。

五、group by 與聚合函數

? GROUP BY語句用于根據一個或多個列的值對結果進行分組，并應用聚合函數來匯總每一組的數據。這意味著你可以按照特定的字段對數據進行分組，并且對于每個組，你都可以使用聚合函數來計算出該組的一些統計數據。

? 下面我們先來學習一下聚合函數的使用。

5、1 聚合函數

? ?常見的聚合函數包括但不限于以下幾種：

COUNT：計算指定列中的行數。
SUM：對指定列中的數值進行求和。
AVG：計算指定列的平均值。
MAX：返回指定列中的最大值。
MIN：返回指定列中的最小值。

? 我們再結合幾個實際的例子來理解一下聚合函數的使用。依據下表進行查詢：

統計班級共有多少同學
? 使用 * 號進行統計，且不受NULL的影響。結果如下圖：

? 注意，我們的exam_result 表中并沒有id=2的行，所以是一共為6行。我們也可以使用表達式進行統計，如下圖：

? 怎么理解上圖中的用表達式來統計呢？這種寫法相當于在查詢表中數據時，自行新增了一列列名為特定表達式的列，我們就是在用count函數統計該列中有多少個數據，等價于統計表中有多少條記錄。具體如下圖：

? 如果是要統計具體的某一個列的字段個數，那么NULL并不會計算在內。

統計本次考試的數學成績分數個數

? 這個跟統計總人數區別不大，我們直接看結果：

? 如果我們想統計去重后的數學成績的個數呢？如下圖：

統計數學成績總分
? 我們直接用SUM聚合函數就可以將篩選出來的成績相加到一起。如下圖：

? 再加上篩選條件，查看數學成績低于60分的總成績。如下圖：

? 正如上圖所示，如果有低于60分的會統計出來。如果沒有的話，會輸出一個NULL。

統計平均總分
? 我們只需要將所有的成績加起來，在用AVG聚合函數求平均分即可。具體如下圖：

返回英語最高分
? 直接使用MAX聚合函數即可。如下圖：

返回 > 70 分以上的數學最低分
? 首先篩選出數學成績大于70分的，在使用聚合函數MIN即可。如下圖：

5、2 group by語句?

? 在select中使用group by 子句可以對指定列進行分組查詢，語法如下：
select column1, column2, .. from table group by column;
? 下面我們結合實際例子來理解一下group by 的使用。

? 我們現在有如下三張表：

? 其中員工表（emp）的表結構和表中的內容如下：

??部門表（dept）的表結構和表中的內容如下：

? 工資等級表（salgrade）的表結構和表中的內容如下：

顯示每個部門的平均工資和最高工資
? 首先，我們肯定是要對每個部分進行分類的。相同的部門歸到一類在進行統計。結果如下圖：

? 簡單解釋一下：上述SQL語句會先將表中的數據按照部門號進行分組，然后各自在組內做聚合查詢得到每個組的平均工資和最高工資。

顯示每個部門的每種崗位的平均工資和最低工資
? ?注意，我們需要按照部們和崗位進行分組，然后在使用AVG和MIN聚合函數。具體如下圖：

? ?上述語句中，我們是先按照部分進行分類。如果部門號相同時，再按照job進行分類。其分類字段用逗號隔開。?

顯示平均工資低于2000的部門和它的平均工資
? 首先我們應該根據部門進行分類篩選出平均工資，如下圖：

? 然后再篩選出平均工資低于2000的。結果如下圖：

? 細心的同學發現了，我們在分組篩選時，用的having而不是where。他們有什么區別呢？

在MySQL中，HAVING和WHERE都是用于過濾查詢結果的SQL語句，它們在功能上有些相似，但也有一些關鍵的區別。

適用場景：WHERE用于在SELECT語句中基于列的值進行過濾，而HAVING用于在GROUP BY查詢中過濾聚合函數的結果。換句話說，WHERE用于單個行的過濾，而HAVING用于組級別的過濾。
數據類型：WHERE子句通常用于過濾數據表中的單個行，而HAVING子句通常用于過濾聚合函數的結果集（如COUNT、SUM等）。
使用頻率：WHERE通常在大多數查詢中使用，因為它可以更直接地過濾單個行。另一方面，HAVING在需要基于聚合函數的結果進行過濾時使用較少，因為它需要處理聚合數據。
返回結果：WHERE和HAVING返回的結果集可能不同。WHERE通常返回單個行或行的組合，而HAVING通常返回一個結果集，其中包含所有符合條件的組的數據。

? 我們也發現了，where子句中不能使用聚合函數和別名，而having子句中可以使用聚合函數和別名。這就涉及到了分組聚合的SQL語句的執行順序了。含有having子句的SQL如下：
SELECT ... FROM table_name [WHERE ...] [GROUP BY ...] [HAVING ...] [order by ...] [LIMIT ...];
? SQL中各語句的執行順序為：where、group by、select、having、order by、limit。

??總的來說，WHERE和HAVING在功能上有所不同，適用于不同的查詢場景。在大多數情況下，WHERE子句應該足夠滿足大多數查詢需求，除非你需要在GROUP BY查詢中使用聚合函數并需要對其進行過濾。