腳本之家服務器常用軟件

快捷導航

MySQL高級查詢語法分析

更新時間：2022年02月07日 17:06:11 作者：黑馬藍汐

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

一、排序

排序查詢語法：

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

語法說明：

先按照列1進行排序，如果列1的值相同時，則按照列2排序asc：升序排序（從小到大）desc：降序排序（從大到?。┠J是升序排序（asc）

查詢未刪除男生信息，按學號降序：

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

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

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

二、分頁查詢

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

select * from 表名 limit start,count

說明：

limit 是分頁查詢關鍵字start 表示開始行索引，默認是0count 表示查詢條數(shù)

查詢前三行男生的信息：

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

可以簡寫為

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

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

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

三、聚合函數(shù)

聚合函數(shù)又叫組函數(shù)，通常是對表中的數(shù)據(jù)進行統(tǒng)計和計算，一般結(jié)合分組（group by）來使用，用于統(tǒng)計和計算分組數(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;

求最大值：

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

求最小值：

查詢未刪除的學生最小編號
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)計null值
select avg(height) from students where sex='男';
求男生的平均身高，包含身高為null的值
select avg(ifnull(height,0)) from students where sex='男';

說明：

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

聚合函數(shù)特點：

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

四、分組查詢

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

分組查詢基本的語法格式：

group by 列名 [having 條件表達式] [with rollup]

說明：

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

group by 的使用：

group by可用于單個字段分組，也可用于多個字段分組

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

group by + group_concat()的使用：

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

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

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

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

group by + having的使用：

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

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

group by + with rollup的使用：

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

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

小結(jié)：

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

五、連接查詢

連接查詢可以實現(xiàn)多個表的查詢，當查詢的字段數(shù)據(jù)來自不同的表就可以使用連接查詢來完成

連接查詢分為：

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

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

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

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

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

說明：

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

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

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

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

使用內(nèi)連接：

2. 左連接查詢

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

左連接查詢語法格式：

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

說明：

left join 就是左連接查詢關鍵字on 就是連接查詢條件表1 是左表表2 是右表

使用左連接查詢學生表與班級表：

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

3. 右連接查詢

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

右連接查詢語法格式：

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

說明：

right join 就是右連接查詢關鍵字on 就是連接查詢條件表1 是左表表2 是右表

使用右連接查詢學生表與班級表：

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

4. 自連接查詢

左表和右表是同一個表，根據(jù)連接查詢條件查詢兩個表中的數(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表導入數(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', '朝陽區(qū)', '11001');
insert into areas values('11005', '豐臺區(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', '長安區(qū)', '12001');
insert into areas values('12003', '橋東區(qū)', '12001');
insert into areas values('12004', '橋西區(qū)', '12001');
insert into areas values('12005', '新華區(qū)', '12001');

說明：

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

自連接查詢的用法：

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

說明：

自連接查詢必須對表起別名

六、子查詢

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

主查詢和子查詢的關系：

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

查詢大于平均年齡的學生：

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

查詢學生在班的所有班級名字：

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

查找年齡最大，身高最高的學生：

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