快捷導(dǎo)航

MySQL高級(jí)查詢語(yǔ)法分析

更新時(shí)間：2022年02月07日 17:06:11 作者：黑馬藍(lán)汐

在面試過(guò)程中經(jīng)常會(huì)遇到sq查詢問(wèn)題，今天小編通過(guò)本文給大家介紹下MySQL高級(jí)查詢語(yǔ)法分析，感興趣的朋友跟隨小編一起看看吧

一、排序

排序查詢語(yǔ)法：

select * from 表名?order by 列1 asc/desc [,列2 asc/desc,...]

語(yǔ)法說(shuō)明：

先按照列1進(jìn)行排序，如果列1的值相同時(shí)，則按照列2排序asc：升序排序（從小到大）desc：降序排序（從大到小）默認(rèn)是升序排序（asc）

查詢未刪除男生信息，按學(xué)號(hào)降序：

select * from students where is_del = 0 and sex = '男' order by id desc;

顯示所有學(xué)生信息，先按年齡從大到小排序，年齡相同時(shí)按身高由高到低排序：

select * from students order by age desc,height desc;

二、分頁(yè)查詢

在網(wǎng)上購(gòu)物時(shí)，瀏覽商品列表的時(shí)候，由于數(shù)據(jù)特別多，一頁(yè)顯示不完，一頁(yè)一頁(yè)的進(jìn)行顯示，這就是分頁(yè)查詢

select * from 表名 limit start,count

說(shuō)明：

limit 是分頁(yè)查詢關(guān)鍵字start 表示開(kāi)始行索引，默認(rèn)是0count 表示查詢條數(shù)

查詢前三行男生的信息：

select * from students where sex='男' limit 0,3;

可以簡(jiǎn)寫(xiě)為

select * from students where sex='男' limit 3;

每頁(yè)顯示m條數(shù)據(jù)，求第n頁(yè)顯示的數(shù)據(jù)（關(guān)鍵是求每頁(yè)的開(kāi)始行索引）

select * from students limit (n-1)*m,m;

三、聚合函數(shù)

聚合函數(shù)又叫組函數(shù)，通常是對(duì)表中的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)和計(jì)算，一般結(jié)合分組（group by）來(lái)使用，用于統(tǒng)計(jì)和計(jì)算分組數(shù)據(jù)

常用的聚合函數(shù)：

count（col）：表示求指定列的總行數(shù)max（col）：表示求指定列的最大值min（col）：表示求指定列的最小值sum（col）：表示求指定列的和avg（col）：表示指定列的平均值

求總行數(shù)：

返回非null數(shù)據(jù)的總行數(shù)
select count(height) from students;
返回總行數(shù)，包含null值記錄
select count(*) from students;

求最大值：

查詢男生編號(hào)的最大值
select max(id) from students where sex='男'；

求最小值：

查詢未刪除的學(xué)生最小編號(hào)
select min(id) from students where is_del=0;

求和：

查詢男生的總身高
select sum(height) from students where sex='男';
查詢男生的平均身高
select sum(height) / count(*) from students where sex='男';

求平均值：

求男生的平均身高，聚合函數(shù)不統(tǒng)計(jì)null值
select avg(height) from students where sex='男';
求男生的平均身高，包含身高為null的值
select avg(ifnull(height,0)) from students where sex='男';

說(shuō)明：

ifnull函數(shù)：表示判斷指定字段的值是否為null，如果為空則使用自己提供的值

聚合函數(shù)特點(diǎn)：

聚合函數(shù)默認(rèn)忽略字段為null的記錄，要想列值為null的記錄也參與計(jì)算，必須使用ifnull函數(shù)對(duì)null值做替換

四、分組查詢

分組查詢就是將查詢結(jié)果按照指定字段進(jìn)行分組，字段中數(shù)據(jù)相等的分為一組

分組查詢基本的語(yǔ)法格式：

group by 列名 [having 條件表達(dá)式] [with rollup]

說(shuō)明：

列名：是指按照指定字段的值進(jìn)行分組having 條件表達(dá)式：用來(lái)過(guò)濾分組后的數(shù)據(jù)with rollup：在所有記錄的最后加上一條記錄，顯示select查詢時(shí)聚合函數(shù)的統(tǒng)計(jì)和計(jì)算結(jié)果

group by 的使用：

group by可用于單個(gè)字段分組，也可用于多個(gè)字段分組

根據(jù)sex字段來(lái)分組
select gender from students group by sex;
根據(jù)name和sex字段來(lái)分組
select name,sex from students group by name,sex;

group by + group_concat()的使用：

group_concat(字段名)：統(tǒng)計(jì)每個(gè)分組指定字段的信息集合，每個(gè)信息之間用逗號(hào)分割

根據(jù)sex字段進(jìn)行分組，查詢sex字段和分組的name字段信息
select sex,group_concat(name) from students group by sex;

group by + 聚合函數(shù)的使用：

統(tǒng)計(jì)不同性別的人的平均年齡
select sex,avg(age) from students group by sex;
統(tǒng)計(jì)不同性別的人的個(gè)數(shù)
select sex,count(*) from students group by sex;

group by + having的使用：

having作用和where類似都是過(guò)濾數(shù)據(jù)的，但having是過(guò)濾分組數(shù)據(jù)的，只能用于group by

根據(jù)sex字段進(jìn)行分組，統(tǒng)計(jì)分組條數(shù)大于2的
select sex,count(*) from students group by sex having count(*)>2;

group by + with rollup的使用：

with rollup的作用是：在最后記錄后面新增一行，顯示select查詢時(shí)聚合函數(shù)的統(tǒng)計(jì)和計(jì)算結(jié)果

根據(jù)sex字段進(jìn)行分組，匯總總?cè)藬?shù)
select sex,count(*) from students group by sex?with rollup;
根據(jù)sex字段進(jìn)行分組，匯總所有人年齡
select sex,group_concat(age) from students group by sex with rollup;

小結(jié)：

group by 根據(jù)指定的一個(gè)或者多個(gè)字段對(duì)數(shù)據(jù)進(jìn)行分組group_concat（字段名）函數(shù)是統(tǒng)計(jì)每個(gè)分組指定字段的信息集合聚合函數(shù)在和group by 結(jié)合使用時(shí)，聚合函數(shù)統(tǒng)計(jì)和計(jì)算的是每個(gè)分組的數(shù)據(jù)having 是對(duì)分組數(shù)據(jù)進(jìn)行條件過(guò)濾with rollup 在最后記錄后面新增一行，顯示select查詢時(shí)聚合函數(shù)的統(tǒng)計(jì)和計(jì)算結(jié)果

五、連接查詢

連接查詢可以實(shí)現(xiàn)多個(gè)表的查詢，當(dāng)查詢的字段數(shù)據(jù)來(lái)自不同的表就可以使用連接查詢來(lái)完成

連接查詢分為：

內(nèi)連接查詢左連接查詢右連接查詢自連接查詢

1. 內(nèi)連接查詢

查詢兩個(gè)表中符合條件的共有記錄（取交集）

內(nèi)連接查詢語(yǔ)法格式：

select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2

說(shuō)明：

inner join 就是內(nèi)連接查詢關(guān)鍵字on 就是連接查詢條件

使用內(nèi)連接查詢學(xué)生表與班級(jí)表：

select * from students s inner join classes c on s.c_id = c.id;

原本兩個(gè)表的內(nèi)容：

使用內(nèi)連接：

2. 左連接查詢

以左表為主根據(jù)條件查詢右表數(shù)據(jù)，如果根據(jù)條件查詢右表數(shù)據(jù)不存在則使用null值填充

左連接查詢語(yǔ)法格式：

select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2

說(shuō)明：

left join 就是左連接查詢關(guān)鍵字on 就是連接查詢條件表1 是左表表2 是右表

使用左連接查詢學(xué)生表與班級(jí)表：

select * from students s left join classes c on s.c_id = c.id;

3. 右連接查詢

以右表為主根據(jù)條件查詢左表數(shù)據(jù)，如果根據(jù)條件查詢左表數(shù)據(jù)不存在則使用null值填充

右連接查詢語(yǔ)法格式：

select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2；

說(shuō)明：

right join 就是右連接查詢關(guān)鍵字on 就是連接查詢條件表1 是左表表2 是右表

使用右連接查詢學(xué)生表與班級(jí)表：

select * from students s right join classes c on s.c_id = c.id;

4. 自連接查詢

左表和右表是同一個(gè)表，根據(jù)連接查詢條件查詢兩個(gè)表中的數(shù)據(jù)

創(chuàng)建areas表：

create table areas(
id varchar(20) not null primary key,
title varchar(30) not null,
pid varchar(20)
);

執(zhí)行sql文件給areas表導(dǎo)入數(shù)據(jù)：

source areas.sql;

sql文件內(nèi)容：

insert into areas values('11000', '北京市', null);
insert into areas values('11001', '北京市', '11000');
insert into areas values('11002', '東城區(qū)', '11001');
insert into areas values('11003', '西城區(qū)', '11001');
insert into areas values('11004', '朝陽(yáng)區(qū)', '11001');
insert into areas values('11005', '豐臺(tái)區(qū)', '11001');
insert into areas values('11006', '海淀區(qū)', '11001');
insert into areas values('12000', '河北省', null);
insert into areas values('12001', '石家莊市', '12000');
insert into areas values('12002', '長(zhǎng)安區(qū)', '12001');
insert into areas values('12003', '橋東區(qū)', '12001');
insert into areas values('12004', '橋西區(qū)', '12001');
insert into areas values('12005', '新華區(qū)', '12001');

說(shuō)明：

source 表示執(zhí)行的sql文件

自連接查詢的用法：

select c.id, c.title, c.pid, p.title from areas c inner join areas p on c.pid = p.id;

說(shuō)明：

自連接查詢必須對(duì)表起別名

六、子查詢

在一個(gè)select語(yǔ)句中，嵌入了另外一個(gè)select語(yǔ)句，那么被嵌入的select語(yǔ)句稱之為子查詢語(yǔ)句，外部的那個(gè)select語(yǔ)句則稱為主查詢

主查詢和子查詢的關(guān)系：

子查詢是嵌入到主查詢中子查詢是輔助主查詢的，要么充當(dāng)條件，要么充當(dāng)數(shù)據(jù)源子查詢是可以獨(dú)立存在的語(yǔ)句，是一條完整的select語(yǔ)句

查詢大于平均年齡的學(xué)生：

select * from students where age > (select avg(age)?from students);

查詢學(xué)生在班的所有班級(jí)名字：

select name from classes where id in (select c_id from students where c_id is not null);

查找年齡最大，身高最高的學(xué)生：

select * from students where age=(select max(age) from students) and height=(select max(height) from students);
可以簡(jiǎn)寫(xiě)為：
select * from students where (age,height) = (select max(age), max(height) from students);