一、CRUD 核心操作

數(shù)據(jù)庫中的 CRUD 指的是對表數(shù)據(jù)的增（Create）、刪（Delete）、改（Update）、查（Select）四類基本操作。為了保證數(shù)據(jù)完整性和業(yè)務約束，表設計中常用 五大約束：NOT NULL、AUTO_INCREMENT、UNIQUE、PRIMARY KEY、FOREIGN KEY，是數(shù)據(jù)庫操作的基礎核心。

1.1 數(shù)據(jù)約束

為保證數(shù)據(jù)庫中數(shù)據(jù)的完整性與一致性，存在 5 大約束：

• not null 非空約束：確保字段的值不能為空。例如，在學生表的“姓名”字段設置該約束后，插入學生記錄時必須填寫姓名。
• auto_increment 自增約束：常用于主鍵字段，使字段值在插入新記錄時自動遞增。比如學生表的“學號”字段，每次新增學生，學號會自動加 1。
• unique 唯一約束：保證字段的值在表中是唯一的，避免重復。像課程表的“課程名稱”字段，不能有重復的課程名。
• primary 主鍵約束：同時包含“非空 + 唯一”屬性，用于唯一標識表中的每條記錄，一個表只能有一個主鍵。例如學生表的“學號”字段可作為主鍵，唯一確定每個學生。
• foreign 外鍵約束：用于建立表與表之間的關聯(lián)，確保參照完整性。比如學生選課表中的“學生學號”字段可作為外鍵，關聯(lián)學生表的“學號”主鍵，保證選課記錄對應的學生存在。

1.2 刪除數(shù)據(jù)

MySQL 刪除數(shù)據(jù)的幾種方式有什么區(qū)別?

在 MySQL 中，刪除數(shù)據(jù)主要有三種方式：DROP、TRUNCATE 和 DELETE。DROP 屬于 DDL 操作，會直接刪除整張表，包括表結(jié)構、數(shù)據(jù)、索引等所有對象，執(zhí)行最快但無法回滾；TRUNCATE 也是 DDL，只清空表中所有數(shù)據(jù)，保留表結(jié)構，同時會重置自增主鍵，速度也很快，同樣無法回滾；而 DELETE 是 DML 操作，逐行刪除數(shù)據(jù)，可以通過 WHERE 條件選擇性刪除，并且支持事務回滾，但速度相對較慢。

刪除數(shù)據(jù)有 drop（DDL）、truncate（DDL）、delete（DML）三種方式，區(qū)別如下：

① drop (DDL)

• 作用：刪除整張表的結(jié)構以及表數(shù)據(jù)（包括表結(jié)構 + 表數(shù)據(jù) + 索引 + 約束 + 觸發(fā)器 等所有相關對象）。
• 速度：快（直接移除元數(shù)據(jù)，釋放空間）
• 回滾：通常不能回滾。執(zhí)行后會釋放表所占的磁盤空間。
• 例如：執(zhí)行 DROP TABLE student; 學生表的結(jié)構和所有學生數(shù)據(jù)都會被刪除。

② truncate (DDL)

• 作用：只刪除表中的數(shù)據(jù)，表的結(jié)構、索引等其他對象保留，且會將自增字段的值初始化為 1
• 速度：較快（以“頁”為單位進行刪除操作，而不是逐行刪除）
• 回滾：通常不能回滾，會釋放數(shù)據(jù)占用的空間
• 比如 ：TRUNCATE TABLE student; 后，學生表數(shù)據(jù)被清空，如果再次插入數(shù)據(jù)，自增學號從 1 開始。

③ delete (DML)

• 作用：逐行刪除符合條件的記錄，表的結(jié)構、索引等其他對象保留，可以指定 WHERE 條件進行部分刪除。
• 速度：慢（記錄逐行刪除，產(chǎn)生 undo/redo、可能觸發(fā)觸發(fā)器、影響索引、寫入 binlog）
• 回滾：支持回滾（在事務中使用 delete 可通過 rollback 恢復）。
• 例如： DELETE FROM student WHERE age > 20; 只會刪除年齡大于 20 的學生記錄。

1.3 去重

去重用于獲取表中不重復的數(shù)據(jù)，主要有以下兩種方式：

① group by column

通過對指定列進行分組，將相同值的行歸為一組，從而實現(xiàn)去重效果。常與聚合函數(shù)（COUNT, SUM, AVG 等）配合用于統(tǒng)計或去重。

示例（統(tǒng)計每個班級人數(shù)）：

SELECT class, COUNT(*) AS cnt
FROM student
GROUP BY class;

② select distinct column

示例（去重班級）：列出學生表中所有不同的班級名稱。

SELECT DISTINCT class FROM student;

1.4 查詢條件與過濾

條件判斷用于在數(shù)據(jù)庫操作中根據(jù)特定條件篩選數(shù)據(jù)或執(zhí)行操作，常見的有以下幾種場景：

① from… where…

• 在 SELECT、UPDATE、DELETE 操作中，通過 WHERE 子句設置條件，篩選出符合要求的數(shù)據(jù)。
• 通常比 having 更早執(zhí)行
• 只能引用行級列或表達式，不能引用聚合結(jié)果。

SELECT * FROM student WHERE age > 19;
DELETE FROM student WHERE id = 3;
UPDATE student SET age = age + 1 WHERE class = '計算機1班';

② group by… having…

• 先通過 GROUP BY 對列進行分組，再用 HAVING 子句對分組后的結(jié)果設置條件進行篩選。
• 可以引用聚合函數(shù)（例如 HAVING COUNT(*) > 1）。

示例：篩選出平均年齡大于 20 的班級及其平均年齡

SELECT class, AVG(age) FROM student GROUP BY class HAVING AVG(age) > 20;

③ A join B on condition

• 用于多表連接查詢，通過 ON 指定連接條件，將兩個或多個表中相關聯(lián)的數(shù)據(jù)組合在一起。
• WHERE 可繼續(xù)對連接后的結(jié)果集進行過濾。

SELECT s.name, c.name
FROM student s
JOIN course c ON s.id = c.student_id
WHERE s.age > 18;

假設課程表有 student_id 外鍵關聯(lián)學生表 id，獲取學生姓名和所選課程名稱。

二、CRUD 操作示例

2.1 創(chuàng)建數(shù)據(jù)庫

• 創(chuàng)建數(shù)據(jù)庫：CREATE DATABASE 數(shù)據(jù)庫名 DEFAULT CHARACTER SET utf8;，用于創(chuàng)建新的數(shù)據(jù)庫并設置字符集為 utf8，例如 CREATE DATABASE school DEFAULT CHARACTER SET utf8;。
• 刪除數(shù)據(jù)庫：DROP DATABASE 數(shù)據(jù)庫名;，會刪除指定數(shù)據(jù)庫及其所有對象，如 DROP DATABASE school;。
• 選擇數(shù)據(jù)庫：USE 數(shù)據(jù)庫名;，用于指定當前要操作的數(shù)據(jù)庫，如 USE school;。

CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8;
USE `mydb`;
DROP DATABASE `mydb`;

2.2 創(chuàng)建表

創(chuàng)建表：使用 CREATE TABLE 語句創(chuàng)建表，可指定字段、類型、約束等。

例如創(chuàng)建學生表：

CREATE TABLE IF NOT EXISTS `student` (
   `id` INT UNSIGNED AUTO_INCREMENT COMMENT '學號',
   `name` VARCHAR(40) NOT NULL COMMENT '姓名',
   `age` TINYINT UNSIGNED COMMENT '年齡',
   `class` VARCHAR(20) COMMENT '班級',
   PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='學生表';

創(chuàng)建課程表：

CREATE TABLE IF NOT EXISTS `course` (
   `id` INT UNSIGNED AUTO_INCREMENT COMMENT '課程ID',
   `name` VARCHAR(40) NOT NULL COMMENT '課程名稱',
   `teacher` VARCHAR(20) NOT NULL COMMENT '教師',
   `credit` TINYINT UNSIGNED COMMENT '學分',
   PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='課程表';

• 刪除表：DROP TABLE 表名;，會刪除表及其所有數(shù)據(jù)，如 DROP TABLE student;。

2.3 增（Create）

插入數(shù)據(jù)：使用 INSERT INTO 語句向表中插入數(shù)據(jù)

例如向?qū)W生表插入數(shù)據(jù)：

INSERT INTO `student` (`name`, `age`, `class`) VALUES 
('張三', 20, '計算機1班'),
('李四', 19, '計算機2班'),
('王五', 21, '軟件工程1班');

向課程表插入數(shù)據(jù)：

INSERT INTO `course` (`name`, `teacher`, `credit`) VALUES 
('數(shù)據(jù)庫原理', '張老師', 3),
('操作系統(tǒng)', '李老師', 4),
('計算機網(wǎng)絡', '王老師', 3);

2.4 刪（Delete）

刪除表與清空數(shù)據(jù)：使用 DELETE FROM 語句刪除表中數(shù)據(jù)，可通過 WHERE 子句設置條件，

例如：

DELETE FROM `student` WHERE id = 3;           -- 刪除ID為3的學生
DELETE FROM `student` WHERE name = '李四';    -- 刪除姓名為李四的學生
DELETE FROM `student` WHERE age > 20;         -- 刪除年齡大于20的學生

清空數(shù)據(jù)表：

• TRUNCATE TABLE 表名;，如 TRUNCATE TABLE student;，清空學生表數(shù)據(jù)，自增字段置 1。
• DELETE FROM 表名;，如 DELETE FROM student;，逐行清空學生表數(shù)據(jù)，自增字段從之前的值繼續(xù)。

示例：

DROP TABLE `student`;           -- 刪除表結(jié)構與數(shù)據(jù)
TRUNCATE TABLE `student`;       -- 清空數(shù)據(jù)（重置自增）
DELETE FROM `student`;          -- 逐行刪除（可加 WHERE）

2.5 改（Update）

更新數(shù)據(jù):使用 UPDATE 語句更新表中數(shù)據(jù)，通過 SET 子句設置新值，WHERE 子句設置條件

例如：

UPDATE `student` SET `age` = 22 WHERE id = 1;           -- 更新年齡
UPDATE `student` SET `class` = '計算機3班' WHERE id = 2; -- 更新班級
UPDATE `student` SET `age` = `age` + 1;                 -- 所有學生年齡加1

2.6 查（Read）

查詢數(shù)據(jù)：使用 SELECT 語句查詢表中數(shù)據(jù)，可進行簡單查詢、條件查詢、排序查詢、統(tǒng)計查詢等

例如：

SELECT * FROM `student`;                              -- 查詢所有學生
SELECT `name`, `age` FROM `student`;                  -- 查詢指定列
SELECT * FROM `student` WHERE age > 19;               -- 條件查詢
SELECT * FROM `student` WHERE class LIKE '計算機%';    -- 模糊查詢
SELECT * FROM `student` ORDER BY age DESC;            -- 排序查詢
SELECT COUNT(*) as total FROM `student`;              -- 統(tǒng)計數(shù)量

三、SQL 執(zhí)行順序

寫 SQL 是按如下順序：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...

但實際執(zhí)行順序并不是這樣！
數(shù)據(jù)庫在執(zhí)行 SQL 時，是按照邏輯執(zhí)行步驟從底層一層層向上處理的：

• 書寫順序是“SELECT在前”，但實際執(zhí)行時SELECT在中間階段（第7步），因此 SELECT 中定義的別名不能在 WHERE 中使用，但可在ORDER BY中使用。
• WHERE和HAVING的區(qū)別：WHERE過濾行（未分組時），HAVING過濾組（已分組后），且HAVING可使用聚合函數(shù)。
• WHERE盡早過濾數(shù)據(jù)，減少后續(xù)分組/排序的計算量。

示例：

假設有兩張表：

• student（學生表）：id（學號）、name（姓名）、class（班級）
• score（成績表）：stu_id（關聯(lián)學生 id）、subject（科目）、score（分數(shù)）

查詢每個班級中數(shù)學平均分≥80 分的學生，顯示班級、學生姓名、數(shù)學分數(shù)，結(jié)果按分數(shù)降序排列，只看前 2 名。

SELECT 
  s.class, 
  s.name, 
  sc.score 
FROM 
  student s 
JOIN 
  score sc ON s.id = sc.stu_id  -- 關聯(lián)條件
WHERE 
  sc.subject = '數(shù)學'  -- 只看數(shù)學成績
GROUP BY 
  s.class, s.name, sc.score  -- 按班級、學生、分數(shù)分組
HAVING 
  AVG(sc.score) ≥ 80  -- 篩選平均分≥80的組
ORDER BY 
  sc.score DESC  -- 按分數(shù)降序
LIMIT 2;  -- 只取前2條

實際執(zhí)行順序：

① FROM 階段

• 操作：確定數(shù)據(jù)源，將student（別名s）和score（別名sc）作為初始表。
• 結(jié)果：生成兩張表的笛卡爾積（所有可能的行組合，暫未過濾）。

② ON 階段

• 操作：使用ON s.id = sc.stu_id過濾笛卡爾積，只保留學生id匹配的行（關聯(lián)有效數(shù)據(jù)）。
• 結(jié)果：得到“學生-成績”的有效關聯(lián)記錄（例如：學生id=1對應其所有科目成績）。

③ JOIN 階段

• 操作：執(zhí)行JOIN合并，此時結(jié)果集僅包含student和score中id匹配的記錄（內(nèi)連接效果）。
• 結(jié)果：合并后的臨時表包含字段：s.id、s.name、s.class、sc.stu_id、sc.subject、sc.score。

④ WHERE 階段

• 操作：用sc.subject = '數(shù)學'過濾行，只保留數(shù)學科目的記錄。
• 注意：此階段不能用AVG(sc.score)（聚合函數(shù)），因為尚未分組。
• 結(jié)果：臨時表中僅剩下“數(shù)學”科目的學生成績記錄。

⑤ GROUP BY 階段

• 操作：按 s.class, s.name, sc.score分組（此處分組字段包含分數(shù)，實際可簡化為按班級+學生分組，此處為演示）。
• 結(jié)果：相同班級、相同學生、相同分數(shù)的記錄被歸為一組（為后續(xù)聚合計算做準備）。

⑥ HAVING 階段

• 操作：用AVG(sc.score) ≥ 80篩選分組，只保留數(shù)學平均分≥80的組。
• 注意：此階段可使用聚合函數(shù)（已分組）。
• 結(jié)果：僅保留符合條件的組（例如：班級“高一1班”的學生“張三”數(shù)學平均分85分）。

⑦ SELECT 階段

• 操作：從分組后的結(jié)果中選擇需要的列：s.class、s.name、sc.score。
• 結(jié)果：臨時表字段精簡為這三列。

⑧ DISTINCT 階段

• 操作：這里沒有用DISTINCT，如果有在這階段對SELECT的結(jié)果去重。

⑨ ORDER BY 階段

• 操作：按sc.score DESC對結(jié)果排序（分數(shù)從高到低）。
• 結(jié)果：排序后的臨時表（例如：分數(shù)95→90→85…）。

⑩ LIMIT 階段

• 操作：用LIMIT 2只保留前2條記錄。
• 最終結(jié)果：

到此這篇關于MySQL 增刪改查操作與 SQL 執(zhí)行順序詳解的文章就介紹到這了,更多相關mysql增刪改查內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！