【MySQL數據庫】CRUD 增刪改查超詳解，有這一篇就夠了！

?🔥個人主頁：?中草藥

🔥專欄：【MySQL】探秘：數據庫世界的瑞士軍刀

??一.CRUD

🧪二.新增（Create）

🧫1.基本操作

🧬2.使用SELECT插入

🔬3.總結

🔭三.查詢（Retrieve）?（重點）*

📡1.基本查詢

🍇2.別名

🍈3.條件查詢

3.1.比較運算符

3.2邏輯運算符

3.3案例

🍉4.排序查詢

🍊5.去重查詢

?🍋6.分頁查詢

🍌?7.聚合查詢*

7.1聚合函數

7.3 GROUP BY子句

7.4 HAVING子句

7.5 WITH ROLLUP

🥭?8.關聯查詢

8.1 關聯條件與笛卡爾積

?8.2?內連接(INNER JOIN)

8.3 外連接?

🍍9.合并查詢

9.1. UNION

9.2. UNION ALL

🍎10.子查詢（了解）

🍒四.?修改（Update）

🍓1.基本操作

🫐2. 修改表結構（ALTER TABLE）

🥝3.注意事項

🍅五. 刪除（Delete）

💐1.基本操作?

🌸2.快速清空表（TRUNCATE）

🍀3. 刪除表或數據庫對象（DROP）

🧬4.注意事項

🪥六.約束條件

🧽1.非空約束 (NOT NULL)

🧻2.唯一約束 (UNIQUE)

🪣3.主鍵約束 (PRIMARY KEY)

🫧4.外鍵約束 (FOREIGN KEY)

🛒5.檢查約束 (CHECK - MySQL 8.0.16及以上版本支持)--了解

🪤七.總結與反思

??一.CRUD

????????MySQL的CRUD操作指的是數據庫操作中的四個基本動作：Create（創建）、Read（讀取）、Update（更新）和Delete（刪除）。這些操作構成了數據庫管理系統中最核心的數據操作集合，用于管理關系型數據庫中的記錄。

????????CRUD操作是數據庫管理的基礎，它們共同構成了數據生命周期管理的閉環，從數據的產生到數據的最終廢棄，每一步都離不開這四個操作。它們的重要性在于：

靈活性：使得數據庫能夠適應不斷變化的數據需求，支持動態數據管理。
數據完整性：通過精確的增刪改查操作，維護數據庫數據的準確性與一致性。
性能優化：合理的CRUD操作策略可以提升查詢速度，減少資源消耗，優化數據庫性能。
數據安全：通過權限控制CRUD操作，確保數據的訪問和修改符合安全策略。

在MySQL中，通過優化CRUD操作的執行效率和安全性，可以極大地提升數據庫應用的性能和用戶體驗。

注釋：在SQL中可以使用“--空格+描述”來表示注釋說明

🧪二.新增（Create）

????????在MySQL中，"新增操作"通常指的是向數據庫中插入新的記錄，這主要通過INSERT語句來完成。新增操作是數據庫管理中的基本操作之一，對于維護和更新數據庫內容至關重要。以下是關于MySQL中新增操作的一些關鍵點和示例：

🧫1.基本操作

--插入單行數據
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);--插入多行數據
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...),(value4, value5, value6, ...),(...);

table_name是要插入數據的表的名稱。
(column1, column2, column3, ...)是表中的列名列表，列出你要插入數據的列。這可以省略，如果你插入所有列的值，并且順序與表定義一致。
(value1, value2, value3, ...)是你希望插入的具體值，與列名一一對應。

舉例：

-- 創建一張學生表
drop table if exist student;
create table student (id int,sn int comment '學號',name varchar(20) comment '姓名',qq_mail varchar(20) comment 'QQ郵箱'
);-- 單行數據+全列插入  插入兩條記錄，value_list 數量必須和定義表的列的數量及順序一致
insert into student values (100, 10000, '唐三藏', NULL);
insert into student values (101, 10001, '孫悟空', '11111');-- 多行數據+指定列插入  插入兩條記錄，value_list 數量必須和指定列數量及順序一致
insert into student (id, sn, name) values(102, 20001, '曹孟德'),(103, 20002, '孫仲謀');

🧬2.使用SELECT插入

你還可以從另一個查詢的結果插入數據，這在復制表數據或更新表結構時特別有用：

INSERT INTO table_name (column1, column2, column3, ...)
SELECT columnA, columnB, columnC, ...
FROM source_table
WHERE some_condition;

🔬3.總結

????????新增操作在MySQL中是數據錄入和數據庫內容管理的核心部分，它支持數據庫的動態增長和數據的實時更新。通過靈活運用INSERT語句的各種形式，開發者可以高效地實現數據的批量插入、條件插入和沖突處理，滿足不同場景下的數據管理需求。

🔭三.查詢（Retrieve）?（重點）*

????????MySQL中的查詢操作主要通過SQL語言中的SELECT語句來實現，這是數據查詢語言(DQL)的一部分，用于從數據庫中檢索數據。查詢操作是數據庫管理中最頻繁使用的功能之一，它支持從簡單的數據檢索到復雜的分析和數據匯總。以下是MySQL中查詢操作的幾個關鍵方面和示例：

📡1.基本查詢

--單表查詢
SELECT column1, column2
FROM table_name;--全列查詢
SELECT *
FROM table_name;

column1, column2是要選擇的列名。
table_name是查詢的表名。
*表示選擇所有列。

通常情況下不建議使用 * 進行全列查詢

-- 1. 查詢的列越多，意味著需要傳輸的數據量越大；

-- 2. 可能會影響到索引的使用。（索引待后面博客講解）

舉例

-- 創建考試成績表
DROP TABLE IF EXISTS exam_result;
CREATE TABLE exam_result (id INT,name VARCHAR(20),chinese DECIMAL(3,1),math DECIMAL(3,1),english DECIMAL(3,1)
);-- 插入測試數據
INSERT INTO exam_result (id,name, chinese, math, english) VALUES(1,'唐三藏', 67, 98, 56),(2,'孫悟空', 87.5, 78, 77),(3,'豬悟能', 88, 98.5, 90),(4,'曹孟德', 82, 84, 67),(5,'劉玄德', 55.5, 85, 45),(6,'孫權', 70, 73, 78.5),(7,'宋公明', 75, 65, 30);

查詢字段為表達式

-- 表達式不包含字段
SELECT id, name, 10 FROM exam_result;
-- 表達式包含一個字段
SELECT id, name, english + 10 FROM exam_result;
-- 表達式包含多個字段
SELECT id, name, chinese + math + english FROM exam_resul

🍇2.別名

為查詢結果中的列指定別名，表示返回的結果集中，以別名作為該列的名稱，語法

SELECT column [AS] alias_name [...] FROM table_name

🍈3.條件查詢

使用WHERE子句根據條件篩選數據：

SELECT column1, column2
FROM table_name
WHERE condition;

condition是篩選條件，如column1 = 'value'。

3.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 個）任意字符； _ 表示任意一個字符

3.2邏輯運算符

運算符	說明
AND	多個條件必須都為 TRUE(1)，結果才是 TRUE(1)
OR	任意一個條件為 TRUE(1), 結果為 TRUE(1)
NOT	條件為 TRUE(1)，結果為 FALSE(0)

3.3案例

AND與OR：

-- 查詢語文成績大于80分，且英語成績大于80分的同學
SELECT * FROM exam_result WHERE chinese > 80 and english > 80;-- 查詢語文成績大于80分，或英語成績大于80分的同學
SELECT * FROM exam_result WHERE chinese > 80 or english > 80;-- 觀察AND 和 OR 的優先級：
SELECT * FROM exam_result WHERE chinese > 80 or math>70 and english > 70;
SELECT * FROM exam_result WHERE (chinese > 80 or math>70) and english > 70;

?BETWEEN ... AND ...：

-- 查詢語文成績在 [80, 90] 分的同學及語文成績
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;-- 使用 AND 也可以實現
SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese 
<= 90;

?IN ：

-- 查詢數學成績是 58 或者 59 或者 98 或者 99 分的同學及數學成績
SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);-- 使用 OR 也可以實現
SELECT name, math FROM exam_result WHERE math = 58 OR math = 59 OR math 
= 98 OR math = 99;

模糊查詢：LIKE ：

-- % 匹配任意多個（包括 0 個）字符
SELECT name FROM exam_result WHERE name LIKE '孫%';-- 匹配到孫悟空、孫權-- _ 匹配嚴格的一個任意字符
SELECT name FROM exam_result WHERE name LIKE '孫_';-- 匹配到孫權

NULL 的查詢：IS [NOT] NULL

-- 查詢 qq_mail 已知的同學姓名
SELECT name, qq_mail FROM student WHERE qq_mail IS NOT NULL;-- 查詢 qq_mail 未知的同學姓名
SELECT name, qq_mail FROM student WHERE qq_mail IS NULL;

🍉4.排序查詢

使用ORDER BY子句對結果排序：

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC|DESC;

ASC升序，DESC降序，且默認為升序。

NULL 數據排序，視為比任何值都小，升序出現在最上面，降序出現在最下面

使用表達式及別名排序?

-- 查詢同學及總分，由高到低
SELECT name, chinese + english + math FROM exam_result ORDER BY chinese + english + math DESC;
SELECT name, chinese + english + math total FROM exam_result ORDER BY total DESC;

可以對多個字段進行排序，排序優先級隨書寫順序?

-- 查詢同學各門成績，依次按 數學降序，英語升序，語文升序的方式顯示
SELECT name, math, english, chinese FROM exam_result ORDER BY math DESC, english, chinese;

🍊5.去重查詢

使用DISTINCT關鍵字對某列數據進行去重

-- 98 分重復了
SELECT math FROM exam_result;
+--------+
| math |
+--------+
| ? ? 98 |
| ? ? 78 |
| ? ? 98 |
| ? ? 84 |
| ? ? 85 |
| ? ? 73 |
| ? ? 65 |
+--------+
7 rows in set (0.00 sec)-- 去重結果
SELECT DISTINCT math FROM exam_result;
+--------+
| math |
+--------+
| ? ? 98 |
| ? ? 78 |
| ? ? 84 |
| ? ? 85 |
| ? ? 73 |
| ? ? 65 |
+--------+
6 rows in set (0.00 sec)

?🍋6.分頁查詢

使用 LIMIT?關鍵字對某列數據進行去重

語法：

-- 起始下標為 0-- 從 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;

案例：按 id 進行分頁，每頁 3 條記錄，分別顯示第 1、2、3 頁

-- 第 1 頁
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 0;-- 第 2 頁
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 3;-- 第 3 頁，如果結果不足 3 個，不會有影響
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 6

🍌?7.聚合查詢*

7.1聚合函數

函數	說明
COUNT([DISTINCT] expr)	返回查詢到的數據的數量
SUM([DISTINCT] expr)	返回查詢到的數據的總和，不是數字沒有意義
AVG([DISTINCT] expr)	返回查詢到的數據的平均值，不是數字沒有意義
MAX([DISTINCT] expr)	返回查詢到的數據的最大值，不是數字沒有意義
MIN([DISTINCT] expr)	返回查詢到的數據的最小值，不是數字沒有意義

舉例

count?

-- 統計班級共有多少同學
SELECT COUNT(*) FROM student;-- 統計班級收集的 qq_mail 有多少個，qq_mail 為 NULL 的數據不會計入結果
SELECT COUNT(qq_mail) FROM student;

sum?

-- 統計數學成績總分
SELECT SUM(math) FROM exam_result;-- 不及格 < 60 的總分，沒有結果，返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;

avg?

-- 統計平均總分
SELECT AVG(chinese + math + english) 平均總分 FROM exam_result;

max?

-- 返回英語最高分
SELECT MAX(english) FROM exam_result;

min?

-- 返回 > 70 分以上的數學最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;

7.3 GROUP BY子句

作用：將結果集按照一個或多個列進行分組，每個組內可以應用聚合函數進行統計計算。
語法：GROUP BY column1, column2,...

舉例

-- 準備測試表及數據：職員表，有id（主鍵）、name（姓名）、role（角色）、salary（薪水）
create table emp(id int primary key auto_increment,name varchar(20) not null,role varchar(20) not null,salary numeric(11,2)
);insert into emp(name, role, salary) values
('馬云','服務員', 1000.20),
('馬化騰','游戲陪玩', 2000.99),
('孫悟空','游戲角色', 999.11),
('豬無能','游戲角色', 333.5),
('沙和尚','游戲角色', 700.33),
('隔壁老王','董事長', 12000.66);-- 查詢每個角色的最高工資、最低工資和平均工資
select role,max(salary),min(salary),avg(salary) from emp group by role;

7.4 HAVING子句

作用：對GROUP BY產生的結果進行過濾，類似于WHERE子句，但HAVING作用于分組后的數據。
語法：HAVING condition

舉例

select role,max(salary),min(salary),avg(salary) from emp group by role 
having avg(salary)<1500;

7.5 WITH ROLLUP

作用：在聚合查詢的結果集中額外添加一行，顯示所有分組的匯總信息。
語法：在GROUP BY子句末尾加上WITH ROLLUP。

舉例

mysql> select role, sum(salary) from emp group by role with rollup;+--------------+-------------+
| role         | sum(salary) |
+--------------+-------------+
| 服務員       |     1000.20 |
| 游戲角色     |     2032.94 |
| 游戲陪玩     |     2000.99 |
| 董事長       |    12000.66 |
| NULL         |    17034.79 |
+--------------+-------------+
5 rows in set (0.00 sec)

🥭?8.關聯查詢

????????關聯查詢是MySQL中一種重要的查詢技術，它允許用戶從兩個或多個表中同時檢索數據，基于這些表之間的某種關聯關系。關聯查詢通常使用 JOIN 語句來完成，通過指定關聯條件來確保從不同表中取出的數據行是相互匹配的。下面是關聯查詢的一些關鍵概念和類型：

8.1 關聯條件與笛卡爾積

????????在進行關聯查詢之前，了解笛卡爾積是很重要的。如果在關聯查詢中沒有指定正確的關聯條件，MySQL會執行一個笛卡爾積操作，即將第一個表的每一行與第二個表的每一行進行配對，生成的結果集大小將是兩個表行數的乘積。在大多數情況下，這樣的結果是沒有意義的，因此我們需要通過關聯條件來限制結果集，使其只包含有意義的匹配行。

?8.2?內連接(INNER JOIN)

????????只返回兩個表中匹配的行。如果在一個表中的某行在另一個表中找不到匹配，那么這行不會出現在結果集中。

舉例

insert into classes(name, descri) values
('計算機系2019級1班', '學習了計算機原理、C和Java語言、數據結構和算法'),
('中文系2019級3班','學習了中國傳統文學'),
('自動化2019級5班','學習了機械自動化');insert into student(sn, name, qq_mail, classes_id, id) values
('09982','黑旋風李逵','xuanfeng@qq.com',1,1),
('00835','菩提老祖',null,1,2),
('00391','白素貞',null,1,3),
('00031','許仙','xuxian@qq.com',1,4),
('00054','不想畢業',null,1,5),
('51234','好好說話','say@qq.com',2,6),
('83223','tellme',null,2,7);insert into course values
('Java'),('中國傳統文化'),('計算機原理'),('語文'),('高階數學'),('英文');insert into score(score, student_id, course_id) values
-- 黑旋風李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素貞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 許仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想畢業
(81, 5, 1),(37, 5, 5),
-- 好好說話
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);

-- 查詢許仙同學的成績select sco.course_id,sco.score from student stu inner join score sco on stu.id=sco.student_id and stu.name='許仙';
+-----------+-------+
| course_id | score |
+-----------+-------+
|        1  |  67.0 |
|        3  |  23.0 |
|        5  |  56.0 |
|        6  |  72.0 |
+----------+-------+

 -- 成績表對學生表是多對1關系，查詢總成績是根據成績表的同學id來進行分組的
SELECT stu.sn, stu.NAME, stu.qq_mail, sum( sco.score )FROM student stu join score sco 
ON stu.id = sco.student_id GROUP BY sco.student_id;
+-------+-----------------+-----------------+------------------+
| sn    | NAME            | qq_mail         | sum( sco.score ) |
+-------+-----------------+-----------------+------------------+
|  9982 | 黑旋風李逵      | xuanfeng@qq.com |            300.0 |
|   835 | 菩提老祖        | NULL            |            119.5 |
|   391 | 白素貞          | NULL            |            200.0 |
|    31 | 許仙            | xuxian@qq.com   |            218.0 |
|    54 | 不想畢業        | NULL            |            118.0 |
| 51234 | 好好說話        | say@qq.com      |            178.0 |
| 83223 | tellme          | NULL            |            172.0 |
+-------+-----------------+-----------------+------------------+

8.3 外連接?

左連接 (LEFT JOIN) / 左外連接: 返回左表的所有行，即使在右表中沒有匹配。如果右表中沒有匹配，則結果集中右表的部分將包含NULL值。
右連接 (RIGHT JOIN) / 右外連接: 與左連接相反，返回右表的所有行，即使在左表中沒有匹配。左表中沒有匹配的行將以NULL值填充。
全連接 (FULL JOIN) / 全外連接: 返回左表和右表中的所有行。如果某一邊沒有匹配，則另一邊的對應值為NULL。需要注意的是，MySQL本身不直接支持 FULL JOIN，但可以通過 LEFT JOIN 和 UNION 或其他技巧來模擬。

-- 左外連接，表1完全顯示
select 字段名 ?from 表名1 left join 表名2 on 連接條件;-- 右外連接，表2完全顯示
select 字段 from 表名1 right join 表名2 on 連接條件;

舉例?

-- “老外學中文”同學 沒有考試成績，也顯示出來了
select * from student stu left join score sco on stu.id=sco.student_id;-- 對應的右外連接為：
select * from score sco right join student stu on stu.id=sco.student_id;+-------+-----------------+-----------------+------------+------+-------+------------+----------+
| sn    | name            | qq_mail         | classes_id | id   | score | student_id | couse_id |
+-------+-----------------+-----------------+------------+------+-------+------------+----------+
|  9982 | 黑旋風李逵      | xuanfeng@qq.com |          1 |    1 |  70.5 |          1 |        1 |
|  9982 | 黑旋風李逵      | xuanfeng@qq.com |          1 |    1 |  98.5 |          1 |        3 |
|  9982 | 黑旋風李逵      | xuanfeng@qq.com |          1 |    1 |  33.0 |          1 |        5 |
|  9982 | 黑旋風李逵      | xuanfeng@qq.com |          1 |    1 |  98.0 |          1 |        6 |
|   835 | 菩提老祖        | NULL            |          1 |    2 |  60.0 |          2 |        1 |
|   835 | 菩提老祖        | NULL            |          1 |    2 |  59.5 |          2 |        5 |
|   391 | 白素貞          | NULL            |          1 |    3 |  33.0 |          3 |        1 |
|   391 | 白素貞          | NULL            |          1 |    3 |  68.0 |          3 |        3 |
|   391 | 白素貞          | NULL            |          1 |    3 |  99.0 |          3 |        5 |
|    31 | 許仙            | xuxian@qq.com   |          1 |    4 |  67.0 |          4 |        1 |
|    31 | 許仙            | xuxian@qq.com   |          1 |    4 |  23.0 |          4 |        3 |
|    31 | 許仙            | xuxian@qq.com   |          1 |    4 |  56.0 |          4 |        5 |
|    31 | 許仙            | xuxian@qq.com   |          1 |    4 |  72.0 |          4 |        6 |
|    54 | 不想畢業        | NULL            |          1 |    5 |  81.0 |          5 |        1 |
|    54 | 不想畢業        | NULL            |          1 |    5 |  37.0 |          5 |        5 |
| 51234 | 好好說話        | say@qq.com      |          2 |    6 |  56.0 |          6 |        2 |
| 51234 | 好好說話        | say@qq.com      |          2 |    6 |  43.0 |          6 |        4 |
| 51234 | 好好說話        | say@qq.com      |          2 |    6 |  79.0 |          6 |        6 |
| 83223 | tellme          | NULL            |          2 |    7 |  80.0 |          7 |        2 |
| 83223 | tellme          | NULL            |          2 |    7 |  92.0 |          7 |        6 |
+-------+-----------------+-----------------+------------+------+-------+------------+----------+

🍍9.合并查詢

????????在MySQL中，合并查詢通常指的是使用 UNION 或 UNION ALL 操作符來組合多個 SELECT 語句的結果，從而生成一個統一的結果集。這兩種操作提供了靈活的方式來整合不同查詢的數據，適用于多種場景，比如報表生成、數據分析等。下面是對這兩種合并查詢方式的詳細說明：

9.1. UNION

UNION 操作符用于合并兩個或多個 SELECT 語句的結果集，同時自動去除結果中的重復行。使用 UNION 時，需注意以下幾點：

列數相同：所有參與合并的?SELECT?語句必須返回相同數量的列。
列類型兼容：雖然列不需要嚴格相同類型，但它們之間應能進行類型轉換，以便比較和去重。
列順序一致：各?SELECT?語句中的列順序需要保持一致。
結果排序與去除重復：MySQL 自動對最終結果進行排序并去除完全相同的行。
列名繼承：結果集的列名通常取自第一個?SELECT?語句中指定的列名。

select * from course where id<3
union
select * from course where name='英文';-- 或者使用or來實現
select * from course where id<3 or name='英文';

9.2. UNION ALL

與 UNION 類似，UNION ALL 也是用來合并多個 SELECT 語句的結果集，但不同之處在于，UNION ALL 不會去除重復的行，也不會對結果集進行排序，因此它通常比 UNION 執行得更快，尤其是在處理大量數據時。

保留所有行：包括重復行，不進行去重。
效率更高：因為省去了去重和排序的過程，所以性能通常優于?UNION。

-- 可以看到結果集中出現重復數據
select * from course where id<3
union all
select * from course where name='英文';

注意事項

性能考量：當確知結果集中不會有重復行或不需要去重時，使用?UNION ALL?可以提高查詢性能。
排序與限制：如果需要對最終結果進行排序或限制返回的行數，可以使用?ORDER BY?和?LIMIT?子句，但它們必須放在所有?UNION/UNION ALL?子句之后。
索引與優化：對于大型查詢，考慮對涉及的列添加索引，以及合理安排?JOIN?和?WHERE?條件，以進一步優化性能。

通過靈活運用 UNION 和 UNION ALL，你可以有效地整合數據，滿足復雜的數據分析和報告需求。

🍎10.子查詢（了解）

子查詢是指嵌入在其他 sql 語句中的 select 語句，也叫嵌套查詢

單行子查詢：返回一行記錄的子查詢

--查詢與“李青” 同學的同班同學
select * from student where 
class_id=(select class_id from student where name ='李青');

多行子查詢：返回多行記錄的子查詢

-- 查詢'語文'或'英語'的成績-- 使用 IN
select * from score where course_id in (select id from course where
name='語文' or name='英文');-- 使用 NOT IN
select * from score where course_id not in (select id from course where
name!='語文' and name!='英文');

盡管子查詢在MySQL中提供了一種強大的方式來處理復雜的數據檢索和分析，但它們也伴隨著一些弊端，主要體現在性能和可讀性方面：

性能問題:
- 資源消耗：執行子查詢時，MySQL可能需要創建臨時表來存儲子查詢的結果，這涉及到內存和磁盤I/O的消耗。一旦查詢完成，這些臨時表會被銷毀，這個過程會增加額外的時間成本。
- 相關子查詢的低效：對于相關子查詢（即子查詢的結果依賴于外部查詢的每一行），MySQL可能需要對外層表的每一行都執行一次子查詢，這在數據量大時會極度影響性能。
- 索引利用不足：在某些情況下，子查詢可能妨礙優化器使用有效的索引策略，導致全表掃描，特別是在子查詢的條件復雜或不明確時。
可讀性和維護困難：
- 復雜性：子查詢嵌套過多會使SQL語句變得冗長且難以理解，尤其是對于復雜的邏輯，這會影響代碼的可讀性和后續的維護工作。
- 調試困難：子查詢可能導致的性能瓶頸較難定位，因為問題可能隱藏在多層嵌套中，調試和優化變得更加困難。
優化局限性：
- MySQL的查詢優化器在處理子查詢方面可能不如處理JOIN操作高效，尤其是在處理大數據集時。優化器可能無法有效地重寫或優化復雜的子查詢結構。
替代方案：
- 在很多情況下，使用JOIN操作可能提供更好的性能和可讀性，尤其是在處理多表關聯查詢時。JOIN直接在數據行級別進行匹配，減少了臨時表的創建和銷毀過程，對于大數據量的處理更加高效。
- 對于簡單的子查詢，直接在應用程序層面進行數據處理也是一種可行的替代方案，例如先執行子查詢獲取數據，再用結果去執行主查詢，但這增加了應用程序的復雜度。

????????綜上所述，雖然子查詢在靈活性和功能上非常強大，但在設計查詢時應權衡其優缺點，根據實際需求和數據規模選擇最合適的方法。在數據量較大或性能要求較高的場景下，考慮使用JOIN或直接在應用程序中處理數據可能是更優的選擇。

🍒四.?修改（Update）

🍓1.基本操作

UPDATE命令用于更新表中的現有記錄。你可以更新單行或多行，具體取決于WHERE子句的條件。

UPDATE table_name
SET column1 = new_value1, column2 = new_value2, ...
WHERE condition;

table_name是你要修改的表的名稱。

column1 = new_value1, column2 = new_value2, ...指定了要修改的列名及其新值。

WHERE condition是可選的，用于指定哪些行需要更新。如果省略，將更新表中的所有行。

舉例：

-- 將孫悟空同學的數學成績變更為 80 分
UPDATE exam_result SET math = 80 WHERE name = '孫悟空';-- 將曹孟德同學的數學成績變更為 60 分，語文成績變更為 70 分
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';-- 將總成績倒數前三的 3 位同學的數學成績加上 30 分
UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english LIMIT
3;-- 將所有同學的語文成績更新為原來的 2 倍
UPDATE exam_result SET chinese = chinese * 2;

🫐2. 修改表結構（ALTER TABLE）

ALTER TABLE命令用于修改表的結構，包括添加/刪除列、修改列屬性、添加約束、重命名表等。

添加列:

ALTER TABLE table_name
ADD column_name column_definition [FIRST|AFTER column_name];

column_definition定義新列的類型和屬性，如INT(110)。

FIRST在表開頭添加，AFTER column_name在指定列之后添加。

刪除列:

ALTER TABLE table_name
DROP COLUMN column_name;

修改列

ALTER TABLE table_name
MODIFY column_name column_definition;

?重命名表

ALTER TABLE old_table_name RENAME TO new_table_name;

🥝3.注意事項

在進行修改操作之前，建議備份數據，以防誤操作導致數據丟失。
對于生產數據庫的修改操作，最好在低峰期進行，避免影響服務。
修改表結構可能會影響依賴于該表的應用程序，需要進行相應的代碼調整和測試。

綜上所述，MySQL中的修改操作覆蓋了數據記錄的更新以及表結構的調整，是數據庫管理與開發中不可或缺的一部分。正確理解和應用這些命令，可以有效管理數據庫的持續發展和維護。

🍅五. 刪除（Delete）

????????在MySQL中，刪除操作主要涉及從數據庫中移除數據或數據庫對象，如數據表、記錄、視圖、存儲過程等。主要通過DELETE、TRUNCATE和DROP命令來實現。以下是這些操作的詳細說明：?

💐1.基本操作?

DELETE FROM ?table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

如果省略WHERE子句，將會刪除表中的所有記錄。

condition用于指定要刪除的行的條件。

舉例：

-- 刪除孫悟空同學的考試成績
DELETE FROM exam_result WHERE name = '孫悟空';-- 刪除整張表數據
-- 準備測試表
DROP TABLE IF EXISTS for_delete;
CREATE TABLE for_delete (id INT,name VARCHAR(20)
);-- 插入測試數據
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');-- 刪除整表數據
DELETE FROM for_delete;

🌸2.快速清空表（TRUNCATE）

TRUNCATE語句用于快速刪除表中的所有數據，它比DELETE更快，因為它不記錄每個刪除行的日志，也不會觸發觸發器，且不可回滾。

TRUNCATE TABLE table_name;

這個操作會清空表，但保留表結構。

🍀3. 刪除表或數據庫對象（DROP）

刪除表：

DROP TABLE table_name;

刪除數據庫:

DROP DATABASE database_name;

🧬4.注意事項

數據安全性：DROP操作是永久性的，執行前應確保已備份重要數據。
性能差異：TRUNCATE比DELETE更快，因為它不記錄日志，也不需要逐行處理。
事務與觸發器：DELETE支持事務處理和觸發器，而TRUNCATE和DROP不支持。
權限要求：執行DROP操作通常需要更高的權限。
索引和約束：DROP TABLE會移除表及其所有索引和約束；TRUNCATE和DELETE不影響表結構。

綜上，MySQL中的刪除操作根據不同的需求和場景提供了不同的命令，使用時需謹慎考慮其對數據的影響和是否可逆性，確保數據安全和操作的正確性。

🪥六.約束條件

MySQL中的約束條件是用來確保數據庫表中數據的準確性和一致性的規則。約束條件可以防止不符合業務邏輯或數據完整性的數據被插入到表中。以下是MySQL中常見的幾種約束條件

🧽1.非空約束 (`NOT NULL`)

定義：要求該列的每一行都必須有值，不允許為空（NULL）。
用途：保證表中的某些字段總是有值，適用于必填信息。

-- 重新設置學生表結構
DROP TABLE IF EXISTS student;
CREATE TABLE student (id INT NOT NULL,sn INT,name VARCHAR(20),qq_mail VARCHAR(20)
);

🧻2.唯一約束 (`UNIQUE`)

定義：確保該列中的所有值都是唯一的，可以有一個NULL值。
用途：適合用于需要唯一標識但不作為主鍵的字段，如郵箱地址。

-- 重新設置學生表結構
DROP TABLE IF EXISTS student;
CREATE TABLE student (id INT NOT NULL,sn INT UNIQUE,name VARCHAR(20),qq_mail VARCHAR(20)
);

🪣3.主鍵約束 (`PRIMARY KEY`)

定義：一個或多個字段的組合，用于唯一標識表中的每一行記錄，且不能有NULL值。
用途：是數據庫表中最重要的約束之一，用于快速定位記錄。

-- 重新設置學生表結構
DROP TABLE IF EXISTS student;
CREATE TABLE student (id INT NOT NULL PRIMARY KEY,sn INT UNIQUE,name VARCHAR(20) DEFAULT 'unkown',qq_mail VARCHAR(20)
);

? ? 自增約束 (AUTO_INCREMENT)

定義：主要用于整數類型的列，每當插入新記錄時，該列的值自動增加。
用途：簡化主鍵的生成和管理。

-- 重新設置學生表結構
DROP TABLE IF EXISTS student;
CREATE TABLE student (id INT PRIMARY KEY auto_increment,sn INT UNIQUE,name VARCHAR(20) DEFAULT 'unkown',qq_mail VARCHAR(20)
);

🫧4.外鍵約束 (`FOREIGN KEY`)

定義：建立兩個表之間的關聯，確保一個表中的值必須參考另一個表中已經存在的值。
用途：維護數據間的引用完整性，常用于實現一對一或一對多的關系。

-- 創建班級表，有使用MySQL關鍵字作為字段時，需要使用``來標識
DROP TABLE IF EXISTS classes;
CREATE TABLE classes (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
`desc` VARCHAR(100)
);-- 重新設置學生表結構
DROP TABLE IF EXISTS student;
CREATE TABLE student (id INT PRIMARY KEY auto_increment,sn INT UNIQUE,name VARCHAR(20) DEFAULT 'unkown',qq_mail VARCHAR(20),classes_id int,FOREIGN KEY (classes_id) REFERENCES classes(id)
);

🛒5.檢查約束 (`CHECK` - MySQL 8.0.16及以上版本支持)--了解

定義：限制列中的值必須滿足特定條件。
用途：對數據的取值范圍進行更細致的控制。

drop table if exists test_user;
create table test_user (id int,name varchar(20),sex varchar(1),check (sex ='男' or sex='女')
);

🧯6.默認值約束 (DEFAULT)

定義：如果在插入數據時沒有指定該列的值，則使用默認值。
用途：簡化數據插入，為可選字段提供默認選項。

-- 重新設置學生表結構
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT 'unkown',
qq_mail VARCHAR(20)
);

這些約束條件可以在創建表時通過CREATE TABLE語句指定，也可以在表創建后通過ALTER TABLE語句添加或修改。合理使用約束條件能夠極大地提高數據庫的數據質量、維護數據的一致性和準確性。

🪤七.總結與反思

只要下定決心克服恐懼，便幾乎能克服任何恐懼。因為，請記住，除了在腦海中，恐懼無處藏身。--戴爾．卡耐基

經過一段時間對MySQL增刪查改（Insert, Delete, Select, Update）四大核心操作的學習與實踐，我深刻體會到了數據庫管理的復雜性和藝術性。以下是對這一學習過程的深入反思，旨在總結經驗，明確改進方向，不斷提升數據庫操作的效率與安全性。

增（Insert）

數據驗證的重要性：在執行插入操作時，我意識到前端或應用層的數據驗證雖是第一道防線，但數據庫層面的數據約束（如非空檢查、唯一性約束）同樣至關重要。忽視這一點可能導致數據不一致性，因此，設置合理的數據庫約束并確保其有效執行是不可或缺的。
性能考量：對于大批量數據的插入，原生的單條插入方式效率低下。未來應探索使用LOAD DATA INFILE命令或批量插入語句來提升效率，減少數據庫的I/O壓力。

刪（Delete）

安全第一：刪除操作的不可逆性讓我深刻認識到，在執行前應三思而后行，特別是在生產環境。采用DELETE FROM時務必附加精確的WHERE子句，避免誤刪整表數據。同時，考慮在執行此類操作前采取備份措施。
事務的妙用：在涉及多步驟的刪除操作中，事務的使用可以確保數據的一致性。我需要加強對事務特性的掌握，特別是在并發操作場景下，確保數據的完整性和一致性。

查（Select）

SQL優化的藝術：雖然SELECT是最基礎的操作，但其背后的優化空間巨大。我認識到，合理利用索引、避免全表掃描、精簡查詢字段、優化JOIN操作等，對于提升查詢效率至關重要。此外，學習如何解讀并利用EXPLAIN分析查詢計劃，對于理解MySQL如何執行SQL語句有著不可估量的價值。
復雜查詢的挑戰：面對多表聯合查詢、子查詢等復雜情況，我發現自己在理解與編寫高效查詢語句方面還有很大提升空間。未來，我需要通過更多實踐，加深對高級SQL特性的理解和應用，如窗口函數、分析函數等。

改（Update）

精準定位：更新操作同樣需要謹慎處理，精確的WHERE子句是避免錯誤更新的關鍵。我需要培養在執行更新前進行數據預覽的習慣，尤其是在處理影響大量數據的更新時。
性能與鎖的影響：大規模更新操作可能引發性能問題，特別是表鎖或行鎖的應用可能阻塞其他讀寫操作。學習并掌握如何最小化鎖定范圍，以及在必要時使用樂觀鎖或悲觀鎖機制，是提升并發處理能力的關鍵。

結語

????????通過對MySQL增刪查改操作的反思，我深刻認識到了理論知識與實踐操作之間的差距，以及數據庫管理中細節決定成敗的道理。未來的路途中，我將致力于深化理論學習，加強實戰演練，同時關注數據庫最新技術和最佳實踐，以期成為一名更為熟練且負責任的數據庫管理員。

🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀🍀

以上，就是本期的全部內容啦，若有錯誤疏忽希望各位大佬及時指出💐

? 制作不易，希望能對各位提供微小的幫助，可否留下你免費的贊呢🌸