一、CRUD 核心操作

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

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

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

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

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

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

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

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

① drop (DDL)

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

② truncate (DDL)

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

③ delete (DML)

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

1.3 去重

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

① group by column

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

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

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

② select distinct column

示例（去重班級(jí)）：列出學(xué)生表中所有不同的班級(jí)名稱。

SELECT DISTINCT class FROM student;

1.4 查詢條件與過濾

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

① from… where…

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

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

② group by… having…

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

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

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

③ A join B on condition

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

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

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

二、CRUD 操作示例

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

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

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

2.2 創(chuàng)建表

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

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

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

創(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 '學(xué)分',
   PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='課程表';

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

2.3 增（Create）

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

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

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

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

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

2.4 刪（Delete）

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

例如：

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

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

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

示例：

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

2.5 改（Update）

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

例如：

UPDATE `student` SET `age` = 22 WHERE id = 1;           -- 更新年齡
UPDATE `student` SET `class` = '計(jì)算機(jī)3班' WHERE id = 2; -- 更新班級(jí)
UPDATE `student` SET `age` = `age` + 1;                 -- 所有學(xué)生年齡加1

2.6 查（Read）

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

例如：

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

三、SQL 執(zhí)行順序

寫 SQL 是按如下順序：

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

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

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

示例：

假設(shè)有兩張表：

• student（學(xué)生表）：id（學(xué)號(hào)）、name（姓名）、class（班級(jí)）
• score（成績(jī)表）：stu_id（關(guān)聯(lián)學(xué)生 id）、subject（科目）、score（分?jǐn)?shù)）

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

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

實(shí)際執(zhí)行順序：

① FROM 階段

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

② ON 階段

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

③ JOIN 階段

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

④ WHERE 階段

• 操作：用sc.subject = '數(shù)學(xué)'過濾行，只保留數(shù)學(xué)科目的記錄。
• 注意：此階段不能用AVG(sc.score)（聚合函數(shù)），因?yàn)樯形捶纸M。
• 結(jié)果：臨時(shí)表中僅剩下“數(shù)學(xué)”科目的學(xué)生成績(jī)記錄。

⑤ GROUP BY 階段

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

⑥ HAVING 階段

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

⑦ SELECT 階段

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

⑧ DISTINCT 階段

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

⑨ ORDER BY 階段

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

⑩ LIMIT 階段

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

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

最新評(píng)論