前言：

在MySQL操作中，CRUD是最核心、最基礎的操作，貫穿了數據管理的整個生命周期。CRUD分別對應Create（新增）、Retrieve（查詢）、Update（更新）、Delete（刪除）四個的基本操作，掌握這些操作是學好MySQL的最重要的一步。

數據庫的層級結構

在開始學習CRUD操作之前，我們需要先了解數據庫的基本層級結構：

數據庫服務器 --> 多個數據庫 --> 多張數據表 --> 多個數據行 --> 多個列（字段）

我們日常操作的主要對象是數據表中的記錄（即多個數據行），每張表由若干列（字段）組成，每個字段定義了數據的類型和屬性。理解這一結構有助于我們更好地理解CRUD操作的作用對象和范圍。

一、新增（Create）

新增操作用于向數據表中插入新的記錄。在SQL中，使用INSERT語句實現新增功能，根據插入數據的行數和指定字段的不同，可分為多種插入方式。

1. 單行數據 + 全列插入

全列插入是指在插入數據時，指定表中所有字段的值。語法如下：

insert into 表名 (字段1，字段2，...) values (值1，值2，...);

說明：
字段列表需要與數據表定義的字段名一致
values中的值需要與前面指定的字段順序一一對應
字符串類型的值必須用英文單引號包裹

例如：
向student表中插入一條完整記錄：

insert into student (id, name, age) values (1, '張三');

上述語句中，字段列表包含了student表的所有字段，values列表中的值與字段一一對應，分別為id、name字段賦值。

注意：如果字段與值的數量不匹配，會導致插入失敗并報錯
例如：字段列表有2個字段，而values列表只有2個值

如果列與值的個數據不匹配就會提示"列數不匹配"的錯誤，并且這條記錄無法寫入數據庫。

2. 單行數據的簡寫插入

當插入的數據包含表中所有字段，不用在表名后指定列名，在values列表中按表中定義字段的順序設置相對應的值，可以省略字段列表，直接使用簡寫形式：

insert into 表名 values (值1，值2，值3...);

例如，上述插入student表的語句可簡寫為：

insert into student values (1, '張三', 18, '男');

注意：這種簡寫方式要求values中的值必須嚴格按照表定義的字段順序排列，且數量必須與表中字段數量完全一致，否則會導致插入失敗。

3. 指定列插入

在實際應用中，我們經常不需要為表中所有字段賦值，這時可以使用指定列插入的方式，只為需要的字段賦值，未指定的字段將使用默認值（通常為NULL）。語法如下：

insert into 表名 (字段1, 字段2, ...) values (值1, 值2, ...);

例如，向student表中只插入name字段的值：

insert into student (name) values ('王五');

執行上述語句后，id未指定的字段將使用默認值NULL。

適用場景：

• 表中包含自增字段（如id），插入時不需要手動指定
• 某些字段有默認值，不需要手動賦值
• 只需要為部分字段設置初始值

4. 多行數據插入

當需要插入多條記錄時，可以使用多行數據插入方式，一次性插入多條記錄，提高操作效率
語法如下：

insert into 表名 [(指定列...)] values 
(值1[,值1...]), 
(值2[,值2...]), 
(值3[,值3...])...;

指定列可以指定多個列；一條insert語句在values部分，可以跟很多的values組，每一組表示要插入的一個數據行 。

例如，向student表中插入三條記錄：

insert into student (id,name) values 
(4，'趙六'),
(5，'孫七'),
(6，'王麻子');

多行插入與單行插入的效率對比：

一次插入多條數據比多次插入單條數據效率更高，主要原因如下：

1. 網絡開銷：每執行一次SQL語句都需要與數據庫服務器進行網絡通信，多次執行會增加網絡傳輸次數和時間
2. 磁盤IO開銷：寫入數據需要進行磁盤操作，批量寫入可以減少磁盤IO次數
3. 事務開銷：每執行一條SQL語句都會開啟一個事務，事務的開啟和關閉需要消耗系統資源，批量插入可以減少事務處理的開銷

因此，在實際開發中，推薦使用多行插入方式批量添加數據（在合理范圍內，避免單次插入數據量過大）。
一次提交多條數據（在一個可控范圍之內），比一次提交的一條數據效率高一點

二、查詢（Retrieve）

查詢操作是數據庫中使用最頻繁的操作，用于從數據表中獲取所需的數據。SQL提供了豐富的查詢功能，可通過SELECT語句實現各種復雜的查詢需求。

1. 全列查詢

全列查詢用于獲取表中所有記錄的所有字段。語法如下：

select * from 表名;

其中，*表示的是要查詢表中的所有的列。

示例：查詢exam表中的所有記錄：

select * from exam;

注意：全列查詢在生產環境中是非常危險的操作
原因如下：
1. 生產環境中表的數據量可能非常大（可達TB級或億級記錄），全列查詢會消耗大量的磁盤和網絡資源
2. 大量的數據傳輸和處理可能占用服務器全部資源，導致其他程序或數據庫操作就要等待當前SQL執行完之后才能繼續執行
3. 可能返回大量不需要的字段和記錄，浪費系統資源

因此，在生產環境中應避免使用不加限制的全列查詢，如需使用，應通過限制條件控制返回結果的數量。

2. 指定列查詢

指定列查詢用于獲取表中指定字段的記錄，只返回需要的字段，減少數據傳輸量。
語法如下：

select 列名1[,列名2...] from 表名;

示例：查詢exam表中的id、name和語文成績：

select id, name, chinese from exam;

這種查詢方式的優勢在于：

• 減少網絡傳輸的數據量
• 提高查詢效率
• 只返回需要的信息，便于數據處理

3. 查詢結果為表達式

在查詢時，除了直接返回表中的字段值，還可以返回基于字段的表達式結果。表達式可以是常量、單個字段的運算或多個字段的運算。

（1）不含字段的表達式

查詢時可以添加常量表達式，作為結果集中的一列。例如：

select id, name, chinese, 10 from exam;

上述語句會在查詢結果中添加一列，所有行的該列值均為10。它本身并不在我們的真實的表里；所以這一列是臨時生成的，并不存在于原表中。

（2）包含一個字段的表達式

可以對單個字段進行運算，返回運算結果。例如：

select id, name, chinese + 10 from exam;

上述語句會返回每個學生的id、name以及語文成績加10后的結果。

注意：這種運算只影響查詢結果，不會修改表中的原始數據。如果需要修改原始數據，需要使用UPDATE操作，這只是查詢方便查看某些總體的數據等

（3）包含多個字段的表達式

可以對多個列的進行運算，返回綜合結果。
例如，計算每個學生的總分：

select id, name, chinese + math + english from exam;

為了使查詢結果更易讀，可以為表達式結果指定別名，使用as關鍵字：

select id, name, chinese + math + english as 總分 from exam;

as關鍵字可以省略，直接寫別名：

select id, name, chinese + math + english 總分 from exam;

如果別名包含空格或特殊字符，需要用單引號包裹：

select id, name, chinese + math + english '總 分' from exam;

注意：通過表達式查詢生成的結果集是一個臨時表，查詢結束后臨時表會被自動刪除，不會影響原表數據。在MySQL中，所有查詢結果都會通過臨時表返回給用戶。

4. 別名

在查詢時，可以為字段或表達式指定別名，使查詢結果更易讀。語法如下：

select 列名 [as] 別名 [,列名 [as] 別名]… from 表名;

示例：

select id as 學號, name 姓名, chinese 語文成績,math 數學成績,english 英語成績
from exam;

使用別名的優勢：
使查詢結果的列名更直觀、易理解；簡化復雜表達式的顯示；便于在查詢中引用表達式結果（如排序時）

5. 去重（distinct）

去重操作用于移除查詢結果中的重復記錄，只保留唯一的記錄。使用distinct關鍵字實現，語法如下：

select distinct 列名 from 表名;

示例：查詢exam表中所有不重復的數學成績：

select distinct math from exam;

注意：
MySQL中，只有當查詢結果中所有列的值都相同時（也就是數據行與數據行之間內容完全一致），才會被視為重復記錄；去重后，重復的記錄只會保留一條

例如，查詢id和math字段時：

select distinct id, math from exam;

可以看到沒有去重成功，是因為兩條記錄的其中的id不同，id字段所在的列不重復，distinct 也就不把它們當做相當的行，導致兩條記錄沒有完全相同

只有當兩條記錄的所有查詢字段值都完全相同時，才會被去重。例如，插入兩條完全相同的記錄：

示例：
在這個表中插入一條數據：

這次再去執行查詢去重操作

只保留了一條記錄
去重時，只有查詢結果中所有的列都相同才會被認定為重復記錄
去重后，重復記錄只保留一條

6. 排序（order by）

排序操作用于對查詢結果按照我們指定的規則對結果進行排序。使用order by子句實現，語法如下：

select 列名 from 表名 order by 列名 [ASC | DESC];

其中：

• ASC表示升序排序（默認值，可省略）
• DESC表示降序排序

提示：
查看表結構用到了desc describe描述
排序中desc descend下降

示例：查詢exam表中的記錄，按語文成績升序排序：

select * from exam order by chinese;
-- 等價于
select * from exam order by chinese ASC;

按語文成績降序排序：

select * from exam order by chinese DESC;

指定了排序的列，返回的結果就是針對這個列進行排序后的結果集
沒有寫排序規則的時候就是默認是升序排序

（1）排序的注意事項

當沒有指定order by子句時，MySQL不保證返回結果的順序，永遠不要依賴默認排序返回給結果集順序，默認MySQL根據哪個字段進行排序，是不確認的；
如果需要特定的排序順序，必須明確指定order by子句

（2）NULL值的排序規則

• 在數值排序時，NULL被視為比任何數值都小
• 升序排序（ASC）時，NULL值出現在最前面
• 降序排序（DESC）時，NULL值出現在最后面

示例：查詢包含NULL值的字段并排序：

-- 升序排序，NULL值在最前面
select * from exam order by english ASC;-- 降序排序，NULL值在最后面
select * from exam order by english DESC;

（3）使用表達式及別名排序

可以使用表達式或字段的別名進行排序。例如，按總分降序排序：

-- 使用表達式排序
select id, name, chinese + math + english from exam order by chinese + math + english desc;-- 使用別名排序（推薦）
select id, name, chinese + math + english as total from exam order by total desc;

使用別名排序更簡潔、易讀，推薦使用這種方式。

注意：MySQL中，NULL與任何值進行運算的結果都是NULL。例如，上述的孫大圣的英語成績為NULL，那么計算總分（chinese + math + english）的結果也會是NULL。
NULL始終被判定為FLASE；NULL的值不是我們以前學習過的其他編程語言中的0，在MySQL中他就是NULL

（4）多字段排序

可以對多個字段進行排序，排序優先級按照字段的書寫順序確定。
每個字段可以指定不同的排序規則。語法如下：

SELECT 列名1, 列名2… FROM 表名
ORDER BY 列名1 [asc|desc], 列名2 [asc|desc], 列名3 [asc|desc]…

示例：先按數學成績降序排序，數學成績相同的再按語文成績升序排序，語文成績也相同的再按英語成績升序排序：

select id, name, chinese, math, english from exam 
order by math desc, chinese asc, english;

排序邏輯分析：

1. 首先按照第一個字段（math）的規則（降序）排序
2. 當第一個字段的值相同時，按照第二個字段（chinese）的規則（升序）排序
3. 當第二個字段的值也相同時，按照第三個字段（english）的規則（默認升序）排序
4. 以此類推
   

優先執行，按先后書寫順序，但是后續的排序是在前一次的有重復的數據時再進行排序，沒有重復的話，不會對其最終的排序產生影響，前次排序就可以確定順序

多字段排序適用于需要更精細排序規則的場景，確保結果的有序性符合業務需求。

7. 條件查詢（where）

條件查詢用于根據指定的條件過濾記錄，過濾掉不符合條件的記錄，把符合條件的記錄返回給用戶。使用where子句實現，可結合比較運算符和邏輯運算符實現復雜的條件過濾。

（1）比較運算符

常用的比較運算符如下：

運算符	說明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于（NULL不安全，例如null = null的結果是NULL）
<=>	等于（NULL安全，例如NULL<=>NULL的結果是TRUE(1)）
!=, <>	不等于（兩種寫法都可以）
between a0 and a1	范圍匹配，[a0, a1]，如果a0 <= value <= a1，返回TRUE(1)
in (option, …)	如果值是option中的任意一個，返回TRUE(1)
is null	判斷是否為NULL
is not null	判斷是否不為NULL
like	模糊匹配，%表示任意多個字符（包含0個），_表示任意一個字符

說明：

• =和<=>的區別：=不能正確判斷NULL值，而<=>可以。例如，null = null的結果是NULL，而null <=> null的結果是1（TRUE）。
• between a0 and a1是閉區間，包含a0和a1兩個端點值。
• in用于判斷值是否在指定的集合中，等價于多個or條件的組合。
• like用于模糊查詢，%匹配任意長度的字符串（包括0長度），_匹配恰好一個字符。
• 在其他編程語言中判斷相等用的是==，MySQL中用的是=，以后的賦值用的也是=

對于is / is not null的補充說明：

IS NULL和IS NOT NULL都是針對于NULL的判斷，
IS NULL如果是NULL返回true，否則false
IS NOT NULL如果是NULL返回false，否則返回true

in 表示查詢的數據在in后面的數據集中是否存在，若存在則返回1，若不存在則返回0

說明：條件中的值在選項列表中存在則返回true，否則返回false，option是一個列表或是集合

like：模糊匹配

select * from exam where name like '孫%';

select * from exam where name like '孫_';

select * from exam where name like '孫__';

_表示相當于一個占位符，有幾個_就必須匹配幾個字符

（2）邏輯運算符

常用的邏輯運算符如下：

運算符	說明
and	邏輯與&&，多個條件同時滿足
or	邏輯或||，多個條件中至少一個滿足
not	邏輯非!，取反

優先級：not > and > or，可以使用括號改變運算順序。

（3）條件查詢的語法

select 列名 from 表名 where 列名/表達式 運算符 條件;

條件查詢時，首先要確定好給哪個列設置相應的條件

（4）條件查詢示例

示例1：查詢英語成績不合格的同學（<60）

select name from exam where english < 60;

注意：結果集中不會包含英語成績為NULL的記錄，因為NULL與任何值比較的結果都為NULL（視為false），所以也就是自動過濾掉了值為null的列。

示例2：查詢語文成績好于英語成績的同學

在每一行的數據中的兩個列是可以進行比較的，但是不能跨行比較

select * from exam where chinese > english;

該查詢會比較每一行的語文成績和英語成績，最終把語文成績大于英語成績的記錄加入到結果集中返回給用戶

示例3：查詢總分在200分以下的同學

select name, chinese + math + english from exam where chinese + math + english < 200;

注意：where子句中不能使用別名作為過濾條件，因為MySQL的執行順序是先執行where子句，再執行select子句生成別名。因此，必須在where子句中完整寫出表達式。

select name, chinese+math+english as '總分' from exam where (english+math+chinese) < 200 order by '總分' asc;

執行順序：
1.如果需要在數據中查某些數據，首先要確定表，先執行from
2.在查詢的過程中要根據指定的查詢條件把符合條件的數據過濾出來，這是執行的就是where子句
3.執行select后面的指定的列，這些列需要加入到最終的結果集中
4.排序操作，根據order by子句中的指定的列名和排序規則進行最后的排序

示例4：查詢語文成績大于80分且英語成績大于80分的同學

select * from exam where english > 80 and chinese > 80;

示例5：查詢語文成績大于80分或英語成績大于80分的同學

select * from exam where chinese > 80 or english > 80;

只要滿足一個條件就可以，就符合整個的查詢條件

示例6：觀察AND和OR的優先級

-- and優先級高于or
select * from exam where chinese > 80 or math > 70 and english > 70;-- 使用括號改變優先級
select * from exam where (chinese > 80 or math > 70) and english > 70;

-- 優先級：
NOT > AND > OR

建議使用括號明確指定運算順序，提高代碼可讀性，避免因優先級問題導致的錯誤。

示例7：查詢語文成績在[80, 90]分的同學

-- 使用between
select * from exam where chinese between 80 and 90;-- 等價于
select * from exam where chinese >= 80 and chinese <= 90;

對數據進行范圍過濾，用這兩種方式都可以注意，左右閉區間

示例8：查詢數學成績是58、59、98或99分的同學

-- 使用in
select name, math from exam where math in (58, 59, 98, 99);-- 等價于
select name, math from exam where math = 58 or math = 59 or math = 98 or math = 99;

兩種方式都可以實現需求，在實際工作中根據當時的情況選一個合適就行

示例9：模糊查詢
% 匹配任意多個（包括 0 個）字符{匹配字符沒有限制}
_ 匹配嚴格的一個任意字符

-- 查詢姓孫的同學（孫后面可以跟任意多個字符）
select * from exam where name like '孫%';-- 查詢名字為兩個字且姓孫的同學
select * from exam where name like '孫_';-- 查詢名字中包含"小"字的同學
select * from exam where name like '孫__';

示例10：查詢NULL值

-- 查詢英語成績為NULL的同學
select * from exam where english is null;-- 或者使用<=>
select * from exam where english <=> null;-- 查詢英語成績不為NULL的同學
select * from exam where english is not null;

注意：判斷NULL值時，不能使用=或!=，必須使用is null、is not null或<=>。

8. 分頁查詢（limit）

分頁查詢用于限制查詢結果的數量，只返回部分記錄；
分頁查詢在項目中運行的非常多，只要查詢的是一個記錄的集合（多條記錄）都在使用分頁查詢；使用limit子句實現，可有效減少數據庫服務器的壓力，提高查詢效率。

通過分頁查詢可以有效地控制一次查詢出來的結果集中的記錄的條數，可以有效的減少數據庫服務器的壓力，同時對于用戶也比較友好

（1）分頁查詢的語法

-- 從0開始，返回n條結果
select ... from 表名 [where ...] [order by ...] limit n;-- 從s開始，返回n條結果
select ... from 表名 [where ...] [order by ...] limit s, n;-- 從s開始，返回n條結果（推薦）
select ... from 表名 [where ...] [order by ...] limit n offset s;

其中：

• n表示要返回的記錄數
• s表示起始位置（從0開始計數）
• offset表示偏移量，即從第s條記錄開始

（2）分頁查詢示例

示例1：返回前2條記錄

select * from exam limit 2;
-- 等價于
select * from exam limit 2 offset 0;

示例2：從第2條記錄開始，返回3條記錄

select * from exam limit 2, 3;
-- 等價于
select * from exam limit 3 offset 2;

示例3：分頁查詢公式

在實際應用中，分頁查詢通常需要根據頁碼和每頁記錄數計算起始位置。公式如下：

起始位置 s = (當前頁碼 - 1) * 每頁記錄數

例如，每頁顯示10條記錄：

• 第1頁：s = (1-1)*10 = 0，查詢語句：limit 10 offset 0
• 第2頁：s = (2-1)*10 = 10，查詢語句：limit 10 offset 10
• 第3頁：s = (3-1)*10 = 20，查詢語句：limit 10 offset 20
  

注意：如果起始位置超出了結果集的范圍，查詢會返回空結果集，不會報錯。

三、修改（Update）

修改操作用于更新表中已存在的記錄。使用UPDATE語句實現，可以更新單個字段或多個字段的值。

1. 修改操作的語法

update table_name set column1 = expr1 [, column2 = expr2 ...]
[where ...] [order by ...] [limit ...]

其中：

• table_name是要修改的表名
• column = expr表示要更新的字段和對應的新值，可以同時更新多個字段，用逗號分隔
• where子句用于指定修改的條件，只更新符合條件的記錄
• order by子句用于指定更新的順序
• limit子句用于限制更新的記錄數量

2. 修改操作示例

示例1：將孫悟空同學的數學成績變更為80分

update exam set math = 80 where name = '孫悟空';

如果表中有多條姓名為"孫悟空"的記錄，上述語句會更新所有符合條件的記錄。

注意：如果不加where條件，會更新表中的所有記錄，這是非常危險的操作，可能導致大量數據被誤修改，務必謹慎操作。

-- 危險！會更新表中所有記錄的math字段
update exam set math = 100;

示例2：將曹孟德同學的數學成績變更為60分，語文成績變更為70分

update exam set math = 60, chinese = 70 where name = '曹孟德';

同時更新多個字段時，字段之間用逗號分隔。

示例3：將總成績倒數前三的同學的數學成績減去30分

update exam set math = math - 30 
order by chinese + math + english asc 
limit 3;

該語句先按總成績升序排序（即倒數順序），然后取前3名，將他們的數學成績減去30分。

注意：MySQL中不支持+=、-=、*=、/=等自增自減運算符，必須使用column = column + value的形式。

示例4：將語文成績小于50的同學的語文成績更新為原來的2倍

update exam set chinese = chinese * 2 where chinese < 50;

執行該語句后，只有語文成績小于50且更新后的值發生變化的記錄會被實際修改。如果原始值為0，更新后仍為0，則不會被視為修改。

只要是實際的值發生了改變，那么磁盤的值就會發生改變，如果未發生改變，那么就不會發生改變

四、刪除（Delete）

刪除操作用于從表中移除記錄。使用DELETE語句實現，可以刪除符合條件的記錄或清空表。

1. 刪除操作的語法

delete from table_name [where ...] [order by ...] [limit ...]

其中：

• table_name是要刪除記錄的表名
• where子句用于指定刪除的條件，只刪除符合條件的記錄
• order by子句用于指定刪除的順序
• limit子句用于限制刪除的記錄數量

2. 刪除操作示例

示例1：刪除孫悟空同學的考試成績

delete from exam where name = '孫悟空';

原數據：
修改完成的：

該語句會刪除所有姓名為"孫悟空"的記錄。

示例2：刪除英語成績倒數前三的同學的所有考試成績

delete from exam 
order by english asc 
limit 3;

再觀察被操作后的數據內容：

該語句先按英語成績升序排序（即倒數順序），然后刪除前3名的記錄。

注意：如果刪除時不加where條件，會刪除表中的所有記錄（清空表），這是非常危險的操作，可能導致數據丟失。

-- 危險！會刪除表中所有記錄
delete from exam;

雖然刪除的數據可以通過數據庫日志恢復，每一條執行的SQL都會被記錄到日志中，把日志中記錄的操作，在執行一遍基本上就可以完成恢復；但恢復過程復雜且耗時，因此應盡量避免執行此類操作。

3. 生產環境中的刪除策略

在生產環境中，一般不推薦使用DELETE語句直接刪除數據，而是采用邏輯刪除的方式：

1. 在表中添加一個標記字段（如delete_state），用于表示記錄是否被刪除
2. 0表示記錄正常（未刪除），1表示記錄已刪除
3. 刪除操作實際上是更新該標記字段的值，而不是物理刪除記錄

例如：

-- 添加標記字段
alter table exam add column delete_state tinyint(1) default 0;-- 邏輯刪除操作（更新標記字段）
update exam set delete_state = 1 where name = '孫悟空';-- 查詢時過濾已刪除的記錄
select * from exam where delete_state = 0;

總結:

CRUD操作是數據庫的基礎，掌握這些操作是進行數據庫開發和管理的前提。這期內容介紹了新增（Create）、查詢（Retrieve）、更新（Update）、刪除（Delete）四種操作的語法、使用場景和注意事項等
在實際應用中，應根據業務需求選擇合適的操作方式，同時注意操作的安全性，避免誤操作導致的數據丟失或損壞。通過不斷練習和實踐，才能熟練掌握這些基礎操作，為更復雜的數據庫操作打下堅實的基礎。