MySQL 5.7 查詢入門詳解

一、數據庫與表基礎操作

1.1 連接數據庫

mysql -u 用戶名 -p
-- 輸入密碼后進入MySQL命令行

1.2 創建數據庫

CREATE DATABASE IF NOT EXISTS test_db 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

1.3 使用數據庫

USE test_db;

1.4 創建數據表

CREATE TABLE students (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,age TINYINT UNSIGNED,gender ENUM('男','女','其他'),create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

1.5 表結構查看

DESCRIBE students; -- 查看表結構
SHOW CREATE TABLE students; -- 查看完整建表語句

---

二、SELECT基礎查詢

2.1 全列查詢

SELECT * FROM students;

2.2 指定列查詢

SELECT name, age FROM students;

2.3 別名使用

SELECT name AS 學生姓名,age + 5 AS 預測年齡
FROM students;

2.4 去重查詢

SELECT DISTINCT gender FROM students;

2.5 表達式計算

SELECT name,age,age * 0.8 AS discount_age
FROM students;

---

三、WHERE條件查詢

3.1 比較運算符

-- 查詢年齡大于18歲的學生
SELECT * FROM students 
WHERE age > 18;-- 查詢性別為女的記錄
SELECT * FROM students 
WHERE gender = '女';

3.2 邏輯運算符

-- AND運算（同時滿足）
SELECT * FROM students 
WHERE age > 18 AND gender = '男';-- OR運算（滿足其一）
SELECT * FROM students 
WHERE age < 18 OR gender = '女';-- NOT運算（取反）
SELECT * FROM students 
WHERE NOT (age > 20);

3.3 范圍查詢

-- BETWEEN...AND（閉區間）
SELECT * FROM students 
WHERE age BETWEEN 18 AND 25;-- IN運算符
SELECT * FROM students 
WHERE gender IN ('男','女');

3.4 模糊查詢

-- LIKE通配符
SELECT * FROM students 
WHERE name LIKE '張%'; -- 以張開頭SELECT * FROM students 
WHERE name LIKE '%小'; -- 以小結尾SELECT * FROM students 
WHERE name LIKE '_小%'; -- 第二個字是小

---

四、JOIN關聯查詢

4.1 內連接

-- 學生表與成績表關聯
SELECT s.name, sc.score 
FROM students s
INNER JOIN scores sc 
ON s.id = sc.student_id;

4.2 左連接

-- 包含沒有成績的學生
SELECT s.name, sc.score 
FROM students s
LEFT JOIN scores sc 
ON s.id = sc.student_id;

4.3 自連接

-- 查詢學生及其導師信息
SELECT s.name AS 學生,t.name AS 導師
FROM students s
JOIN students t 
ON s.teacher_id = t.id;

---

五、聚合函數與分組

5.1 聚合函數

-- 統計總人數
SELECT COUNT(*) AS total FROM students;-- 計算平均年齡
SELECT AVG(age) AS avg_age FROM students;-- 最大最小值
SELECT MAX(age), MIN(age) FROM students;-- 求和
SELECT SUM(age) FROM students;

5.2 GROUP BY分組

-- 按性別分組統計
SELECT gender,COUNT(*) AS count,AVG(age) AS avg_age
FROM students 
GROUP BY gender;

5.3 HAVING過濾分組

-- 查詢平均年齡大于20的分組
SELECT gender,AVG(age) AS avg_age
FROM students 
GROUP BY gender 
HAVING avg_age > 20;

---

六、子查詢與高級用法

6.1 標量子查詢

-- 查詢比平均年齡大的學生
SELECT * FROM students 
WHERE age > (SELECT AVG(age) FROM students);

6.2 IN子查詢

-- 查詢有成績的學生
SELECT * FROM students 
WHERE id IN (SELECT DISTINCT student_id FROM scores);

6.3 EXISTS子查詢

-- 查詢存在成績的學生
SELECT * FROM students s 
WHERE EXISTS (SELECT 1 FROM scores sc WHERE sc.student_id = s.id
);

---

七、索引與性能優化

7.1 創建索引

-- 普通索引
CREATE INDEX idx_name ON students(name);-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);-- 組合索引
CREATE INDEX idx_age_gender ON students(age, gender);

7.2 EXPLAIN分析

EXPLAIN SELECT * FROM students WHERE age > 18;

7.3 優化建議

1. 優先使用覆蓋索引
2. 避免在索引列上使用函數
3. 控制單表數據量（建議<500萬行）
4. 定期執行OPTIMIZE TABLE整理碎片

---

八、實用技巧與注意事項

8.1 分頁查詢

-- 每頁10條，查詢第3頁
SELECT * FROM students 
LIMIT 20, 10; -- (頁碼-1)*每頁數量

8.2 排序規則

-- 多列排序
SELECT * FROM students 
ORDER BY age DESC, name ASC;-- 修改默認排序規則
ALTER TABLE students MODIFY name VARCHAR(50) COLLATE utf8mb4_unicode_520_ci;

8.3 時間區間查詢

-- 查詢最近一個月的數據
SELECT * FROM orders 
WHERE create_time >= DATE_SUB(NOW(), INTERVAL 1 MONTH);

---

九、常見問題解決

9.1 中文亂碼處理

-- 修改連接字符集
SET NAMES utf8mb4;-- 修改配置文件（my.cnf）
[client]
default-character-set=utf8mb4[mysql]
default-character-set=utf8mb4[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

9.2 密碼重置

# 5.7版本密碼重置
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密碼';
FLUSH PRIVILEGES;

---

十、實戰案例：訂單管理系統

-- 創建訂單表
CREATE TABLE orders (order_id INT PRIMARY KEY AUTO_INCREMENT,user_id INT NOT NULL,total_amount DECIMAL(10,2),order_status ENUM('待支付','已支付','已發貨','已完成'),created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (user_id) REFERENCES users(id)
);-- 統計月度銷售額
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,SUM(total_amount) AS total_sales
FROM orders 
GROUP BY month 
ORDER BY month DESC;

---

總結

1. 基礎操作：掌握CRUD基本語法
2. 查詢核心：靈活運用WHERE、JOIN、聚合函數
3. 性能關鍵：合理使用索引和EXPLAIN分析
4. 實戰重點：理解事務、鎖機制、字符集設置

建議通過實際項目練習，逐步掌握復雜查詢、存儲過程、視圖等高級功能。可通過MySQL官方文檔（5.7版本）深入學習特定語法細節。