標題：[MySQL初階]MySQL（4）基本查詢
@水墨不寫bug

---

本文講解MySQL數據庫的表的基本查詢操作（CURD操作）：CRUD : Create(創建), Retrieve(讀取)，Update(更新)，Delete（刪除）
在這里，我將首先創建一個數據表，然后對這個表一邊操作，一邊講解。

一. 數據表設計

首先，我們設計一個用戶管理系統，以它內部的 users 表為例，表結構如下：

CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT COMMENT '員工ID',username VARCHAR(50) NOT NULL UNIQUE COMMENT '名字',email VARCHAR(100) NOT NULL UNIQUE COMMENT '郵箱',password_hash CHAR(60) NOT NULL COMMENT '密碼',created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '創建賬戶時間',last_login DATETIME COMMENT '最后一次登錄時間',is_active BOOLEAN DEFAULT 1 COMMENT '用戶是否活躍'
);

---

二、對數據表的操作

1. Create 操作（插入數據）

插入數據語法：

• 單行數據+全列插入：

一次插入一行數據，每次全列插入

insert into users(id,username,email,password_hash,created_at,last_login,is_active) values (1,'zhangsan','123@qq.com','60個字符',NOW(),NOW(),TRUE);

• 多行數據+指定列插入：

-- 插入多行數據，指定 username、email、password_hash、is_active 列
INSERT INTO users (username, email, password_hash, is_active)
VALUES ('lisi', '000@ex.com', '$2a$10$abc...', 1),  -- 顯式指定 is_active('sunwukong', 'wukong@xe.com', '$2a$10$xyz...', 0);

---

為了方便起見，這里我們假設插入一個較小的數據表。

• 插入沖突是否更新：

insert into tb_name(name,qq) values('zhangsan','123') on duplicate key update name = 'lisi',qq = '234';

插入數據：

嘗試插入一條記錄，name 為 ‘zhangsan’，qq 為 ‘123’。

沖突處理：

如果插入的數據與表中已有的 主鍵或唯一鍵 沖突（例如 name 或 qq 字段有唯一約束），則執行 UPDATE 操作。

將沖突記錄的 name 更新為 'lisi'，qq 更新為 '234'。

• 替換

replace into tb_name (name, qq) values ('zhangsan', '123');

插入數據：

嘗試插入一條記錄，name 為 ‘zhangsan’，qq 為 ‘123’。

沖突處理：

如果插入的數據與表中已有的 主鍵或唯一鍵 沖突（例如 name 或 qq 字段有唯一約束），則會 先刪除沖突的行，再插入新行。

注意：REPLACE INTO 是 先刪除再插入，而不是更新。

查看最近受影響的行數：

select row_count();

可以查看最近受影響的行數。

2. Retrieve 操作（讀取數據）

語法：

SELECT column1, column2, ... FROM table_name [WHERE ...];

這是最基本的查詢方式，在最基本的語句之間，我們可選擇加上一些選項。比較豐富的選項可以如下所示：

select distinct(是否去重) */列名 from tb_name where 查詢條件 order by 列名稱 asc/desc(排序條件) limit(限定查詢出來的數據的條目數);

（1）基本查詢的用法：

• 全列查詢：

select * from tb_name;

一般不推薦全列查詢會輸出整張表的所有內容，對一個數據庫，數據量動輒上百萬，盲目全列查詢會占用網絡帶寬，并且導致刷屏或者機器死機，一般需要用limit指定查詢數據條目數。

• 指定列查詢：

select 列名稱 from tb_name;

指定某一列內容輸出。

• 列間關系運算

查詢的時候，可以進行列間運算

select name,math+chinese+english as total from tb_name;

顯示出來的是3列成績的綜合。其中as 代表重命名，每一列都可以重命名

select name 姓名,math 數學,english 英語,math + chinese + english 總分 from tb_name;

• 結果去重

select distinct math from tb_name;

如果查詢結果有重復，比如有多個人得分95，只顯示一次95

---

（2）where子句詳解

MySQL 的 WHERE 子句用于在查詢中篩選符合特定條件的記錄。它是 SELECT、UPDATE、DELETE 等語句的核心組成部分，select幫助你選擇了某一張表，而where則是進一步選擇表中符合要求的數據；更直觀的來說，where字句類似于if條件判斷。

---

i. 基本語法

SELECT 列名 FROM 表名 WHERE 條件;
UPDATE 表名 SET 列=值 WHERE 條件;
DELETE FROM 表名 WHERE 條件;

---

ii. 常用運算符

比較運算符

• =,<=>：等于
• 注意："="NULL不安全（無法參與NULL比較），如果想要與NULL值比較，需要用NULL安全的 <=> 例如：NULL <=> NULL 結果為TRUE。

  SELECT * FROM users WHERE age = 25;
  SELECT * FROM users WHERE address <=> NULL;
  

• <> 或 !=：不等于

  SELECT * FROM products WHERE price <>（或者!=） 100;
  

• >、<、>=、<=：關系比較

  SELECT * FROM orders WHERE sum_amount > 1000;
  

• BETWEEN ...AND...：在范圍內（閉區間）

  SELECT * FROM employees WHERE salary BETWEEN 300 AND 1000;
  

• IN：匹配列表中的任意值

  SELECT * FROM customers WHERE country IN ('China', 'Canada', 'Mexico');
  

---

邏輯運算符

• AND：同時滿足多個條件

  SELECT * FROM students WHERE age >= 18 AND grade = 'A';
  

• OR：滿足任意一個條件

  SELECT * FROM products WHERE category = 'aaa' OR price < 50;
  

• NOT：否定條件

  SELECT * FROM employees WHERE NOT department = 'HR';
  

• 注意優先級：AND 優先級高于 OR，建議用括號明確邏輯：

  SELECT * FROM table 
  WHERE (condition1 OR condition2) AND condition3;
  

---

模糊匹配（LIKE）

• %：匹配任意多個字符（包括零個）

  SELECT * FROM books WHERE title LIKE 'The%'; -- 以 "The" 開頭
  

• _：匹配單個字符

  SELECT * FROM users WHERE username LIKE 'user_'; -- 如 "user1", "userA"
  

一句話總結， % 可以代表一個或者多個字符, _ 只能代表一個字符

---

NULL 值比較

• IS NULL：檢查空值

  SELECT * FROM orders WHERE shipped_date IS NULL;
  

• IS NOT NULL：檢查非空值

  SELECT * FROM contacts WHERE phone IS NOT NULL;
  

（3）order by子句詳解

---

MySQL 的 ORDER BY 子句用于對查詢結果進行排序，可以按單列、多列或表達式排序，并支持升序（ASC）或降序（DESC）排列。它是優化數據展示和分析的重要工具。

---

i. 基本語法

SELECT 列名 
FROM 表名 
[WHERE 條件] 
ORDER BY 排序列1 [ASC|DESC], 排序列2 [ASC|DESC], ...;

• 位置：ORDER BY 必須位于 WHERE 子句之后，LIMIT 子句之前。
• 默認排序：如果不指定 ASC 或 DESC，默認為 ASC（升序）。

---

ii. 核心用法

單列排序

-- 按工資升序排列
SELECT name, salary FROM employees ORDER BY salary;-- 按入職日期降序排列
SELECT name, hire_date FROM employees ORDER BY hire_time DESC;

多列排序

按優先級依次排序，用逗號分隔：

-- 先按部門升序，再按工資降序
SELECT name, department, salary 
FROM employees 
ORDER BY department ASC, salary DESC;

表示先按照部門排序，部門一樣的再按照薪資排序。

---

（4）limit子句

MySQL 的 LIMIT 子句常與 ORDER BY 結合使用，用于在排序后的結果中篩選出特定范圍的數據（如分頁查詢、獲取前 N 條記錄）。以下是兩者的協同用法和關鍵細節：

---

i. 基本語法

SELECT 列名 
FROM 表名 
[WHERE 條件] 
ORDER BY 排序列 [ASC|DESC] 
LIMIT [偏移量,] 行數;

• 執行順序：
  WHERE → ORDER BY → LIMIT
  （先過濾數據，再排序，最后截取結果）

• 典型場景：

  • 分頁查詢（如每頁 10 條）
  • 獲取前 N 名（如銷量最高的前 5 個商品）

---

ii. 核心用法

(1) 獲取前 N 條記錄

-- 獲取工資最高的前 3 名員工
SELECT name, salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 3;

(2) 分頁查詢

-- 每頁 10 條，查詢第 3 頁（偏移量 = (頁碼-1)*每頁行數）
SELECT * 
FROM products 
ORDER BY price ASC 
LIMIT 20, 10; -- 偏移量為 20 條，取接下來的 10 條

---

3. Update 操作（更新數據）

對查詢到的結果進行列值更新
語法：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE ...
ORDER BY ... 
LIMIT ...;

實際場景：

1. 修改用戶郵箱：

   UPDATE users 
   SET email = 'new@qq.com'
   WHERE id = 1; -- 明確指定條件，避免全表更新！
   

   不指明條件導致全表更新是致命的！

2. 記錄用戶最后登錄時間：

   UPDATE users 
   SET last_login = NOW() 
   WHERE username = 'ddsm';
   

3. 批量禁用長時間未登錄用戶：

   UPDATE users 
   SET is_active = 0 
   WHERE last_login < '2020-01-01';
   

---

4. Delete 操作（刪除數據）

語法：

DELETE FROM table_name WHERE ... ORDER BY ... LIMIT ...;

實際場景：用戶注銷賬號

-- 物理刪除（謹慎操作！）
DELETE FROM users WHERE id = 1;-- 實際項目中更推薦軟刪除,如果誤刪還可以找回數據
UPDATE users SET is_active = 0 WHERE id = 1;

建議：

1. 生產環境優先使用軟刪除（通過 is_active 或 deleted_at 標記）。
2. 刪除前檢查關聯數據（如用戶訂單需級聯處理，這涉及到外鍵約束，在以后的講解中會逐漸詳解）。

---

注意：

• 沒有where子句的刪除，將刪除整張表的數據；但是表的結構不變；此外auto_increment不會歸0；
• 刪除表內數據的另一種方法是

truncate tb_name；

特點是只能對整張表進行操作，不能對部分數據操作。由于mysql不對數據操作，因而比delete更快。truncate在刪除的時候，并不會經過真正的事務，所以無法回滾。此外auto_increment會被重置。

---

三、總結

• Create：INSERT 實現數據寫入，注意唯一性約束。
• Retrieve：SELECT 靈活組合DISTINCT、 WHERE、ORDER BY、LIMIT 滿足查詢需求。
• Update：UPDATE 配合精確條件，避免誤操作。
• Delete：優先軟刪除，物理刪除需事務+備份。

---

完
轉載請注明出處