MySQL高階查詢語句與視圖實戰指南

在日常的數據庫操作中，基礎的增刪改查（CRUD）往往無法滿足復雜的業務需求。比如需要對查詢結果排序、篩選特定區間數據、簡化多表查詢，或者控制數據訪問權限等。今天這篇文章，就帶大家深入學習MySQL的高階查詢語句和視圖的應用，結合實際案例，讓你輕松應對復雜場景。

一、常用高階查詢技巧

首先，我們先搭建一個測試環境，創建一張info表并插入學生數據，后續案例都會基于這張表展開：

-- 創建info表
create table info (id int,name varchar(10) primary key not null,score decimal(5,2),address varchar(20),hobbid int(5)
);-- 插入測試數據
insert into info values
(1,'liuyi',80,'beijing',2),
(2,'wangwu',90,'shengzheng',2),
(3,'lisi',60,'shanghai',4),
(4,'tianqi',99,'hangzhou',5),
(5,'jiaoshou',98,'laowo',3),
(6,'hanmeimei',10,'nanjing',3),
(7,'lilei',11,'nanjing',5);

1. 按關鍵字排序（ORDER BY）

類比Windows任務管理器的排序功能，ORDER BY可以對查詢結果按單個或多個字段排序，默認升序（ASC），降序需顯式指定（DESC）。

基礎用法

• 按分數升序排列（默認ASC，可省略）：

select id,name,score from info order by score;

• 按分數降序排列：

select id,name,score from info order by score desc;

進階用法：多字段排序+條件過濾

當第一個排序字段值相同時，會按第二個字段排序；還可結合WHERE篩選數據。比如篩選地址為“杭州”的學生，按分數降序排列：

select name,score from info where address='hangzhou' order by score desc;

再比如，先按興趣ID（hobbid）降序，相同興趣的學生按ID升序排列：

select id,name,hobbid from info order by hobbid desc, id;

2. 區間判斷與去重（AND/OR + DISTINCT）

區間判斷：AND/OR

• 篩選分數大于70且小于等于90的學生：

select * from info where score >70 and score <=90;

• 篩選分數大于70或小于等于90的學生（注意：邏輯或會包含大部分數據，需謹慎使用）：

select * from info where score >70 or score <=90;

• 多條件嵌套：篩選分數大于70，或分數在75-90之間的學生：

select * from info where score >70 or (score >75 and score <90);

去重查詢：DISTINCT

當某個字段存在重復值時，用DISTINCT只保留唯一值。比如查詢所有不重復的興趣ID：

select distinct hobbid from info;

3. 結果分組（GROUP BY + 聚合函數）

GROUP BY通常與聚合函數搭配使用，常用聚合函數有：

• COUNT()：計數
• SUM()：求和
• AVG()：求平均
• MAX()/MIN()：求最大/最小值

基礎分組

按hobbid分組，統計每個興趣的學生人數：

select count(name) as student_count, hobbid from info group by hobbid;

進階分組：條件篩選+排序

篩選分數大于等于80的學生，按hobbid分組，再按學生人數升序排列：

select count(name) as student_count, hobbid from info where score>=80 group by hobbid order by student_count asc;

4. 限制結果條數（LIMIT）

LIMIT是高頻使用的語句，用于只返回部分結果，語法為：

SELECT 字段 FROM 表名 LIMIT [偏移量,] 條數;

• 偏移量可選，默認從第0行開始（即第一條數據）；
• 條數：要返回的記錄數。

常用場景

• 顯示前3條數據：

select * from info limit 3;

• 從第4行開始（偏移量為3），顯示3條數據：

select * from info limit 3,3;

• 按ID降序，顯示最后3條數據（常用于獲取最新數據）：

select id,name from info order by id desc limit 3;

5. 簡化查詢：設置別名（AS）

當表名或字段名較長時，用AS設置別名（AS可省略），增強SQL可讀性。

字段別名

將name改為“姓名”，score改為“成績”：

select name as 姓名, score as 成績 from info;

表別名

查詢時給表設置別名，簡化多表查詢（后續視圖會用到）：

select i.name as 姓名, i.score as 成績 from info as i;

特殊用法：用別名創建表

將info表的查詢結果作為新表t1的結構和數據（注意：原表的主鍵約束可能無法完全復制）：

create table t1 as select * from info;

6. 模糊查詢（通配符 + LIKE）

通配符用于匹配部分字符，常與LIKE搭配，常用通配符：

• %：匹配0個、1個或多個字符；
• _：匹配單個字符。

常用案例

• 查詢名字以“c”開頭的學生：

select id,name from info where name like 'c%';

• 查詢名字中包含“g”的學生：

select id,name from info where name like '%g%';

• 查詢名字格式為“c_ic_i”的學生（比如“caicai”）：

select id,name from info where name like 'c_ic_i';

7. 嵌套查詢（子查詢）

子查詢（內查詢）是嵌套在主查詢中的SQL語句，先執行子查詢，結果作為主查詢的條件。

基礎用法：IN關鍵字

查詢分數大于80的學生姓名和成績（子查詢先獲取符合條件的ID，主查詢根據ID篩選數據）：

select name,score from info where id in (select id from info where score>80);

進階用法：子查詢在INSERT/UPDATE/DELETE中

• 插入數據：將info表中ID在ky11表中的記錄插入t1：

insert into t1 select * from info where id in (select * from ky11);

• 更新數據：將ky11表中ID=2對應的學生分數改為50：

update info set score=50 where id in (select * from ky11 where id=2);

• 刪除數據：刪除分數大于80的學生：

delete from info where id in (select id from info where score>80);

特殊用法：EXISTS關鍵字

EXISTS判斷子查詢結果是否為空，不為空則返回TRUE，否則FALSE。比如判斷是否存在分數等于80的學生，若存在則統計info表總記錄數：

select count(*) from info where exists(select id from info where score=80);

8. NULL值處理

NULL表示“缺失值”，與0、空字符串（''）不同：

• 空字符串長度為0，不占空間；
• NULL長度未知，占用空間；
• COUNT()會忽略NULL，但包含空字符串。

常用操作

• 查詢addr字段為NULL的記錄：

select * from info where addr is NULL;

• 查詢addr字段不為NULL的記錄：

select * from info where addr is not null;

二、視圖：簡化查詢+數據安全

1. 什么是視圖？

視圖是數據庫中的虛擬表，不存儲真實數據，只保存對真實表的查詢邏輯（類似“鏡子”，動態映射真實數據）。

2. 視圖的作用

• 簡化查詢：將復雜的多表查詢封裝為視圖，后續直接查詢視圖即可；
• 數據安全：給不同用戶提供不同視圖，隱藏敏感字段（比如不給普通用戶看學生的身份證號）；
• 靈活適配：同一批真實數據，可根據需求生成多個視圖。

3. 視圖與表的區別和聯系

維度	視圖（View）	表（Table）
數據存儲	不存儲真實數據，只存查詢邏輯	存儲真實物理數據
編譯狀態	已編譯的SQL語句	未編譯，動態執行
空間占用	不占物理空間	占用物理空間
安全性	可隱藏表結構，權限更精細	直接暴露表結構
修改影響	修改視圖可能影響原表	修改表直接影響數據

聯系：視圖基于表存在，一個視圖可對應一個或多個表；視圖的結構和數據來自表。

4. 視圖實戰案例

案例1：單表創建視圖

創建一個視圖，只顯示分數大于等于80的學生（后續原表數據更新，視圖會同步變化）：

-- 創建視圖
create view v_score as select * from info where score>=80;-- 查詢視圖
select * from v_score;

案例2：多表創建視圖

先創建test01表存儲學生年齡：

create table test01 (id int,name varchar(10),age char(10));
insert into test01 values(1,'zhangsan',20),(2,'lisi',30),(3,'wangwu',29);

創建視圖，關聯info和test01，顯示學生ID、姓名、分數和年齡：

create view v_info(id,name,score,age) as 
select info.id,info.name,info.score,test01.age 
from info,test01 
where info.name=test01.name;-- 查詢視圖
select * from v_info;

案例3：通過視圖修改原表

視圖不僅能查，還能修改原表數據（前提是視圖字段對應原表字段，無函數計算）。比如修改v_score視圖中“tianqi”的分數為120：

update v_score set score='120' where name='tianqi';-- 驗證原表數據
select * from info where name='tianqi';

5. 視圖的注意事項

• 不建議用視圖做增刪改：復雜視圖（如多表關聯、含聚合函數）無法增刪改，容易報錯；
• 視圖不保存數據：查詢視圖時，本質是執行底層SQL，性能取決于原表索引；
• 刪除視圖不影響原表：drop view v_score;只會刪除視圖，不會刪除原表數據。

三、總結

本文介紹的MySQL高階功能，覆蓋了日常開發中90%以上的復雜查詢場景：

1. 排序與篩選：ORDER BY + WHERE實現精準排序；
2. 分組統計：GROUP BY + 聚合函數搞定數據匯總；
3. 簡化操作：LIMIT限制結果、AS設置別名、DISTINCT去重；
4. 復雜查詢：子查詢解決嵌套邏輯，視圖封裝多表關聯；
5. 細節處理：NULL值判斷、通配符模糊查詢。

這些技巧需要結合實際業務多練，比如用視圖封裝報表查詢、用子查詢篩選復雜條件，慢慢就能熟練掌握。如果有疑問，歡迎在評論區交流！