系列文章目錄

更新ing...

1. MySQL操作全攻略：庫、表、數據、事務全面指南
2. 深入探索MySQL SELECT查詢：從基礎到高級，解鎖數據寶藏的密鑰
3. MySQL SELECT查詢實戰：練習題精選，提升你的數據庫查詢技能
4. PyMySQL：連接Python與MySQL的橋梁
5. …

---

---

前言

????在數據驅動的世界中，MySQL的SELECT查詢語句是解鎖數據寶藏的關鍵。本文將帶您領略SELECT語句的魅力，從基礎查詢到高級應用，包括條件篩選、邏輯運算、模糊與正則匹配、排序、聚合、分組過濾、分頁查詢以及連接查詢和子查詢等。此外，我們還將探討索引對查詢性能的重要性。無論您是數據庫新手還是資深開發者，這里都將是您提升查詢技能的寶貴資源。讓我們一同開啟MySQL查詢的旅程！

---

0.準備一張表先

創建一個名叫stu的表
id 主鍵 自增 非空
name 默認空
age 默認0
height 浮點型
gender 枚舉 默認保密
isdelete 默認0
create table stu (id tinyint primary key auto_increment,name varchar(5) default "",age tinyint default 0,height decimal(5,2),gender enum('男','女','中性','保密') default '保密',isdelete tinyint default 0
)添加數據
insert into stu values
(0,"小明",18,180.00,"女",0),
(0,"小月月",18,180.00,"女",1),
(0,"彭于晏",29,185.00,"男",0),
(0,"劉德華",59,175.00,"男",1),
(0,"黃蓉",38,160.00,"女",0),
(0,"劉亦菲",25,166.00,"女",0),
(0,"程坤",27,181.00,"男",1),
(0,"金星",33,162.00,"中性",0),
(0,"靜香",12,180.00,"女",1),
(0,"郭靖",12,170.00,"男",0),
(0,"周杰",34,176.00,"男",0);

1.簡單語句查詢

1.1 基本查詢

# a.查詢表中所有的字段    *代表所有的字段(列)
語法：>>> select * from 表名;
案例：>>> select * from 表名;# b.查詢指定字段
語法：>>> select 字段1，字段2,...... from 表名
案例：>>> select name,age from stu;
案例：>>> select stu.name,stu.age from stu;# c.給字段名起別名  字段名 as 別名
# 別名僅在當前這個sql語句中起效，不是真正的修改字段名
語法：select 字段名 as 別名,字段名 as 別名 ...... from 表名
案例：select name as 姓名,age as 年齡 from stu;# d.給表起別名  表名 as 別名
# 給表起了別名以后，想要使用表必須使用別名，使用原表名會報錯
語法：select 字段1,字段2...... from 表名 as 別名;
案例：select name,age from stu as s;
案例：select s.name,s.age from stu as s;# e.查看所有性別（實現去重） distinct 字段
語法：>>> select distinct 字段1 from 表名;
案例：select distinct gender from stu;

1.2 條件查詢

# 條件   where 條件# >  查詢大于18歲的信息
案例：select * from stu where age > 18;# <  查詢小于18歲的信息
案例：select * from stu where age < 18;# =  查詢年齡等于18歲的信息
案例：select * from stu where age = 18;# >=  查詢大于等于18歲的信息
案例：select * from stu where age >= 18;# <=  查詢小于等于18歲的信息
案例：select * from stu where age <= 18;# !=或者<>  查詢不等于18歲的信息    
案例：select * from stu where age != 18;
案例：select * from stu where age <> 18;

1.3 邏輯運算符 and or not

# and 查詢18-28之間的信息
select * from stu where age >= 18 and age <= 28;# 查詢18歲以上的女性 
select * from stu where age > 18 and gender = '女';
select * from stu where age > 18 and gender = 2;# or 18歲以上或者身高超過180的
select * from stu where age > 18 or height > 180;# not 不是   18歲以內的女性
select * from stu where not (age < 18 and gender = 2);# 年齡不是小于等于18的  并且是女性
select * from stu where not age <= 18 and gender = 2;

1.4 模糊查詢 like

like 
%：匹配字符0次或無數次
_：匹配字符一次# 查詢姓名中以"小"開始的名字
select * from stu where name like '小%';# 查詢姓名中有"小"的名字
select * from stu where name like '%小%';# 查詢兩個字的名字 
select * from stu where name like '__';# 查詢有三個字的名字
select * from stu where name like '___';# 查詢至少有2個字的名字
select * from stu where name like '__%';# 查詢以杰結尾的名字
select * from stu where name like '%杰';

1.5 正則查詢 rlike

rlike.：匹配除\n以外的任意一個字符
^：以......開頭*：0次，無數次
？：0次，無數次
+：1次，無數次
# 查詢以劉開始的
select * from stu where name rlike '^劉.';# 查詢以劉開始的 以華結束的
select * from stu where name rlike '^劉.華$';

1.6 范圍查詢

in：在不連續 的區間內
not in : 不在區間內
between a and b:在a到b范圍內
not between and b:不在a-b范圍內
# 查詢年齡為18、34的信息
select * from stu where age = 18 ot age = 34;
select * from stu where age in (18,34);
# 查詢年齡不是18、34的信息
select * from stu where age not in (18,34);
select * from stu where not age in (18,34);
# 查詢年齡在18-34之間的信息
select * from stu where age >= 18 and age <= 34;
select * from stu where age between 18 and 34;# 查詢年齡不在18-34之間的信息
select * from stu where age not betwwn 18 and 24;

1.7 空判斷

is nullinsert into stu value(0,'張三',19,null,'男',1);
# 查詢身高為空的信息
select * from stu where height is null;

1.8 非空判斷

is not null# 查詢身高不為空的信息
select * from stu where height is not null;

2.排序

排序 order by

order by 字段 排序方式（升序/降序）
升序：asc（不指定排序方式時，默認升序）
降序：desc# 查詢年齡在18-34歲之間的男性
select * from stu where age between 18 and 34 and gender = 1;# 查詢18-34歲之間的男性并且身高按照由低到高排序
select * from stu where age between 18 and 34 and gender = 1 order by height asc;
select * from stu where age between 18 and 34 and gender = 1 order by height;
# 查詢年齡在18-34之間的女性，身高從小到大排序，如果身高相同的情況下按照id由高到低排序
select * from stu where age between 18 and 34 and gender = 2 order by height,id desc;# 查詢年齡在18-34之間的女性，身高從高到低排序，如果身高相同的情況下按照年齡從小到大排序，如果年齡相同按照id從大到小排序
select * from stu where age between 18 and 34 and gender = 2 order by height desc,age,id desc;# 年齡從小到大 如果年齡相同按照身高由高到低排序
select * from stu order by age,height desc;

3.聚合函數

具體種類

# max()   
# 查詢學生中最大的年齡    
select max(age) from stu;# min()  
# 查詢學生中最小的年齡
select min(age) from stu;# count()
# 查詢學生總數   
select count(*) from stu;
select count(id) from stu;# sum()  
# 查詢學生年齡總和
select sum(age) from stu;# avg()
# 查詢所有學生年齡的平均值
select avg(age) from stu;
select sum(age)/count(*) from stu;# round(小數,保留的位數)(四舍五入)
# 計算平均身高保留2位小數
select round(avg(height),2) from stu;
select round(avg(age),2) from stu;# 查找平均身高和所有人的名字 ---XXX
# 平均身高只有一個值，姓名字段11個值，前后數量不對等
select avg(height),name from stu; XX

4.分組與過濾

分組 group by

# 每種性別、不同的性別-->按性別分
# group_concat():獲取分組后的結果，不一定要與分組搭配使用# 以性別分組，獲取每種性別
select distinct gender from stu;
select gender from stu group by gender;
# 計算每種性別的人數
select gender,count(*) from stu group by gender;# 獲取每種性別的人數以及都有誰，統計名字
select gender,count(*),group_concat(name) from stu group by gender;# 按照isdelete字段進行分組，分別統計人數和名稱
select isdelete,count(*),group_concat(name) from stu group by isdelete;# 獲取每種性別的名字和id都展示出來
select gender,group_concat(name,id) from stu group by gender;# 計算男性的人數
select count(*) from stu where gender = '男';# 統計男性的人數和姓名
select count(*),group_concat(name) from stu where gender = '男';# 每種性別的平均年齡、平均身高
select gender,avg(age),avg(height) from stu group by gender;

過濾 having

xxx的性別---按照性別分組,按照條件過濾
# 查詢平均年齡超過30歲的性別及這種性別中所有人的姓名
select gender,avg(age),group_concat(name) from stu group by gender having avg(age) > 30;# 查詢性別人數多2個的性別及人數
select gender,count(*) from stu group by gender having count(*) > 2;

5.分頁

分頁 limit

limit num :展示表中前 num 條數據# 展示前5條數據
select * from stu limit 5;# 顯示前三條性別為男的信息 ---> limit 應該放在 where 后
select * from stu where gender = '男' limit 3;---------------------------------------------------limit start,num
start:從哪一條數據開始 --->(頁數-1) * 每一頁的數據條數
num:取幾條數據（每一頁存放的數據條數）# select * from stu limit 0,3;
# select * from stu limit 3,3;
# select * from stu limit 6,3;# 11條數據   每一頁要求存放3條數據,獲取第三頁的數據
select * from stu limit 6,3;# 每一頁存放2條數據，獲取第四頁的數據
select * from stu limit 6,2;# 每一頁存放3條數據，獲取第三頁的數據，數據按照身高降序排序
select * from stu order by height desc limit 6,3 ;

6.關鍵字排序

關鍵字執行

select 字段/數據：要查詢的數據
from 表名：想要從哪一張表中查詢數據
where 條件：按照對應的條件獲取數據
order by 字段 排序方式：查詢到的結果要按照哪一個字段的值進行升降序排序
group by 字段 ：將數據進行分組
having 條件：對分組后的結果進行過濾
limit start,num：對獲取到的結果進行分頁查詢#關鍵字執行順序
from -->where --> group by-->having--->select-->order by-->limit#編寫順序
select 字段 from 表名 where 條件 group by 分組 having 過濾 order by 排序 limit 分頁

---

5.21

7.連接查詢

7.1 概念

當查詢結果的列來源于多張表時，需要將多張表連接成一個大的數據集，再選擇合適的列返回

7.2 分類

內連接
????普通內連接
????自關聯
外連接
????左外連接
????右外連接

7.3 案例

一張班級表
create table class(id tinyint primary key auto_increment,name varchar(5) unique
);insert into class value
(0,"A班"),
(0,"B班"),
(0,"C班");一張學生表
create table student(id tinyint primary key auto_increment,name varchar(5) unique,age tinyint,gender enum("男","女","保密") default "保密",cls_id tinyint
);insert into student value
(0,"張三",18,"男",1),
(0,"李四",16,"女",1),
(0,"王五",20,"男",1),
(0,"趙六",23,"男",2),
(0,"田七",17,"女",2),
(0,"胡八",30,"男",5);

7.4 普通內連接

語法：

select * from 表1 inner join 表2 on 連接條件
特點：如果連接表中沒有與之對應的數據，則該條數據不展示

案例：

1. 將班級表與學生表內連接--》將班級表作為表1-->班級表中數據在左側展示
>>> select * from class inner join student on cls_id = class.id;2. 將班級表與學生表內連接--》將學生表作為表1-->學生表中數據在左側展示
>>> select * from student inner join class on cls_id = class.id;3. 獲取所有的學生姓名及對應的班級名稱，將數據按照年齡從大到小進行排序
>>> select c.name,s.name from class as c inner join student as s on cls_id = c.id order by age desc;4. 查詢人數大于2人的班級，班級名稱、該班級中學生的數量及學生名稱
>>> select c.name,count(*),group_concat(s.name) from student as s inner join class as c on cls_id = c.id group by c.name having count(*) > 2;5. 查詢所有有學生的班級，獲取班級名即可
>>> select distinct class.name from class inner join student on cls_id = class.id;

7.5 左外連接

語法：

select * from 表1 left join 表2 on 連接條件
哪張表作為表1，哪張表就是主表
主表中所有的數據在左側展示，如果從表中沒有與之對應的數據則用null填充
從表中沒有對應的數據則不展示

案例：

1. 將班級表與學生表左外連接--》將學生表作為表1
>>> select * from student as s left join class as c on cls_id = c.id;2. 將班級表與學生表左外連接--》將班級表作為表1
>>> select * from class as c left join student as s on cls_id = c.id;3. 獲取所有的學生姓名及對應的班級名稱，將數據按照年齡從大到小進行排序
>>> select s.name,c.name from student as s left join class as c on cls_id = c.id order by age desc;4. 查詢沒有班級的學生
>>> select * from student as s left join class as c on cls_id = c.id where c.name is null;5. 查詢有學生的班級
>>> select * from class as c left join student as s on cls_id = c.id where cls_id is not null;

7.6 右外連接

語法：

select * from 表1 right join 表2 on 連接條件;
哪張表作為表2，哪張表就是主表
主表中所有的數據在右側展示，如果從表中沒有與之對應的數據則用null填充
從表中沒有對應的數據則不展示

案例：

1. 將班級表與學生表右外連接--》將學生表作為表1
>>> select * from student right join class on cls_id = class.id;2. 將班級表與學生表右外連接--》將班級表作為表1
>>> select * from class right join student on cls_id = class.id;

7.7 自關聯

案例：

省份表
id    name
1     河南省
2     河北省
3     山東省市級表
id    name      pid
1     鄭州市     1
2     洛陽市     1
3     石家莊市   2
4     青島市     3縣級表
id    name      pid
1     二七區     1
2     金水區     1
3     橋東區     3
4     嶗山區     4將三張表合為一張
id     name     pid
1     河南省    null
2     河北省    null
3     山東省    null
4     鄭州市     1
5     洛陽市     1
6     石家莊     2
7     青島市     3
8     二七區     4
9     金水區     4
10    橋東區     6
11    嶗山區     7create table area(
id tinyint primary key auto_increment,
name varchar(5) unique,
pid tinyint
);insert into area value
(0,"河南省",null),
(0,"河北省",null),
(0,"山東省",null),
(0,"鄭州市",1),
(0,"洛陽市",1),
(0,"石家莊",2),
(0,"青島市",3),
(0,"二七區",4),
(0,"金水區",4),
(0,"橋東區",6),
(0,"嶗山區",7);

語法：

自關聯本質就是內連接
select * from 表1 inner join 表2 on 連接條件;

案例：

1. 實現自關聯
# 自己關聯自己，表必須起別名
>>> select * from area as a1 inner join area as a2 on a1.id = a2.pid;2. 查詢所有的省
>>> select * from area where pid is null;3. 查詢河南省下面的市
>>> select * from area as a1 inner join area as a2 on a1.id = a2.pid where a1.name = "河南省";
>>> select a1.name,group_concat(a2.name) from area as a1 inner join area as a2 on a1.id = a2.pid group by a1.name having a1.name = "河南省";

---

8.子查詢

8.1 概念

在一個select語句中嵌套另外一個select語句
被嵌套的select語句被稱之為子查詢語句
起嵌套作用select語句被稱之為主查詢語句

8.2 分類

標量子查詢：子查詢的結果是一個具體的數據>>> select max(age) from stu;      56
行子查詢：子查詢的結果是一行數據/多個數據>>> select * from stu where name = "張三";>>> select avg(height),avg(age),avg(money) from stu;
列子查詢：子查詢的結果是一列數據>>> select id from stu;

8.3 案例

# 聚合函數不能作為where條件使用，可以having中直接使用1. 查詢年齡最大的這個人的信息1>. 按照年齡從大到小排序，取第一個值-->僅限于最大值只有一個>>> select * from stu order by age desc limit 1;2>. 使用子查詢語句第一步：查詢表中最大的年齡    >>> select max(age) from stu;第二步：查詢該年齡對應的學生的信息   >>> select * from stu where age = 59;綜上所述：>>> select * from stu where age = (select max(age) from stu);2. 查詢身高高于平均身高的人的信息
>>> select * from stu where height > (select avg(height) from stu);

8.4 子查詢中關鍵字的使用

算術運算符

>   <   =   >=    <=   !=# 查詢年齡低于平均年齡的人的信息
>>> select * from stu where age < (select avg(age) from stu);

in、not in

# 查詢有學生的班級
1>. 內連接>>> select distinct c.name from class as c inner join student as s on cls_id = c.id;
2>. 外連接>>> select distinct c.name from class as c left join student as s on cls_id = c.id where cls_id is not null;
3>. 子查詢第一步：從學生表中獲取cls_id    select cls_id from student;第二步：從班級中查詢哪個id再查詢到的cls_id中   select name from class where id in (1,1,1,2,2,5);>>> select name from class where id in (select cls_id from student);

any、some

some是any別名
any:任意一個
# 查詢身高高于  (未成年的人)的身高  任意一個的人的信息
1. 獲取未成年的人的身高
>>> select height from stu where age < 18;2. 查詢身高高于這一部分身高的人的信息
>>> select * from stu where height > (180,170);3. 子查詢
>>> select * from stu where height > any(select height from stu where age < 18);

all

all：全部
# 查詢身高高于  (未成年的人)的身高   全部的人的信息
1. 獲取未成年的人的身高
>>> select height from stu where age < 18;2. 查詢身高高于這一部分身高的人的信息
>>> select * from stu where height > (180,170);3. 子查詢
>>> select * from stu where height > all(select height from stu where age < 18);

exists

exists：存在
如果子查詢語句有結果，則執行主查詢語句
如果子查詢語句沒有結果，則不執行主查詢語句子查詢：select * from stu where height > 190;
主查詢：select * from stu;
>>> select * from stu where exists (select * from stu where height > 190);

9.索引

9.1 概念

定義：索引是一種特殊的文件(InnoDB數據表上的索引是表空間的一個組成部分)，它們包含著對數據表里所有記錄的引用指針。更通俗的說，數據庫索引好比是一本書前面的目錄，能加快數據庫的查詢速度
目的：加快查詢速度，提高查詢效率
原理：通過不斷的縮小想要獲得數據的范圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件

9.2 分類

1.普通索引index
2.唯一索引uniqueprimary key --> 唯一 + 非空
3.聯合索引unique(id,name)
4.全文索引fulltext
5.空間索引spatical 了解

9.3 創建索引

9.3.1 在創建表時直接創建索引

# 如果想要給字符串類型的數據加索引，要求字段后需要帶長度
# 特點：索引名與字段名一致語法：>>> create table 表名(字段 類型 約束,字段 類型 約束,索引類型(字段)
);案例：>>> create table aa(id int,name varchar(10),index(name(10))
);案例：>>> create table aa(id int,name varchar(10),unique(id)
);

9.3.2 給已經創建好的表增加索引

# 特點：自定義索引名語法：>>> create 索引類型 index 索引名 on 表名(字段);
案例：>>> create index a1 on aa(id);
案例：>>> create unique index a1 on aa(name(10));

9.3.3 給已經創建好的表增加索引

# 特點：索引名與字段名一致語法：>>> alter table 表名 add 索引類型(字段);
案例：>>> alter table aa add index(id);
案例：>>> alter table aa add unique(name(10));

9.4 查詢表中索引

語法：>>> show index from 表名;
案例：>>> show index from aa;注意：出現Empty，說明當前表中沒有索引。出現Key_name就是索引名字
+-------+------------+----------+
| Table | Non_unique | Key_name |
+-------+------------+----------+
| aa    |          1 | name     | 
+-------+------------+----------+

9.5 刪除索引

語法：>>> drop index 索引名稱 on 表名;
案例：>>> drop index name on aa;

9.6 索引實戰

9.6.1 pymysql往表中增加一萬條數據

import pymysql
# pymysql實現往表中增加數據# 1.連接數據庫
con = pymysql.connect(host="localhost",port=3306,user="root",password="123456",database="test01",charset="utf8")# 2.獲取游標對象
c1 = con.cursor()for i in range(1,10001):# 3.編寫sql語句sql = f"""insert into aa value({i},'value-{i}')"""# 4.游標對象執行sql語句c1.execute(sql)# 5.提交事務
con.commit()# 6.關閉
con.close()

9.6.2查詢sql執行時間

初始：表中沒有索引# 第一步：開啟時間檢測
>>> set profiling=1;# 第二步：通過id查詢第10000條數據（沒有索引的查詢）
>>> select * from aa where id=10000;# 第三步：給表中id字段增加索引
>>> alter table aa add index(id);# 第四步：通過id查詢第10000條數據（有索引的查詢）
>>> select * from aa where id=10000;# 第五步：查詢所有的執行時間
>>> show profiles;