一、聚合查詢（行與行之間的計(jì)算）

1.常見(jiàn)的聚合函數(shù)有：

Select count(*) from student(統(tǒng)計(jì)行數(shù)）
Select count(1) from student(統(tǒng)計(jì)第一列數(shù)據(jù)的行數(shù)，如果有null則不算行數(shù)）
Select sum(math) from student(數(shù)學(xué)成績(jī)總分)
Select sum(math) from student where math > 60(數(shù)學(xué)大于60的總分）
Select avg(math+chinese+english) from student(統(tǒng)計(jì)平均總分）
Select max(math) from student(數(shù)學(xué)最高分）
Select min(math) from student where math >60(數(shù)學(xué)大于60的最低分）

2.group by

select 中使用 group by 子句可以對(duì)指定列進(jìn)行分組查詢。使用  group by 進(jìn)行分組查詢時(shí)，select 指定的字段必須是“分組依據(jù)字段”，其他字段若想出現(xiàn)在select 中則必須包含在聚合函數(shù)中。

select column1, sum(column2), .. from table group by column1,column3;
//示例：查詢每個(gè)角色的最高工資、最低工資和平均工資 
Select role,max(salary),min(salary),avg(salary) from emp group by role;

3.having

group by 子句進(jìn)行分組以后，需要對(duì)分組結(jié)果再進(jìn)行條件過(guò)濾時(shí)，不能使用 WHERE 語(yǔ)句，而需要用Having

//示例：顯示平均工資低于1500的角色和它的平均工資
select role，avg(salary) from emp group by role having avg(salary)<1500; 

關(guān)于分組查詢指定條件有三種情況：

1.分組之前，指定條件（先篩選，再分組）用where

2.分組之后，指定條件（先分組，再篩選）用having

3.分組之前和分組之后都指定條件（先where后having）

關(guān)于執(zhí)行順序：

二、聯(lián)合查詢（多表查詢）

多表查詢是對(duì)多張表的數(shù)據(jù)取笛卡爾積，笛卡爾積是通過(guò)排列組合算出來(lái)的，因此包含了很多無(wú)效數(shù)據(jù)，因此需要加上連接條件，用來(lái)篩選有效的數(shù)據(jù)。

1.進(jìn)行聯(lián)合查詢的步驟：

1.首先計(jì)算笛卡爾積

2.引入連接條件（如：where student.id = score.student_id）

3.再根據(jù)需求，加入必要的條件（where where student.id = score.student_id and student.name=’xxx’）

4.去掉多余的列，只保留需要關(guān)注的列（select student.name,score.score from student,score where where student.id = score.student_id and student.name=’xxx’）

2.內(nèi)連接（from，join on）

語(yǔ)法：

select 字段 from 表1 (as)別名1 [inner] join 表2 (as)別名2 on 連接條件 and 其他條件;

select 字段 from 表1 (as)別名1,表2 (as)別名2 where 連接條件 and 其他條件；

示例：查詢“張三”同學(xué)的成績(jī)

select sco.score from student as stu inner join score as sco on stu.id=sco.student_id
 
and stu.name='張三';

或者

select sco.score from student as stu, score as sco where stu.id=sco.student_id and
 
stu.name='張三';

3.外連接（left/right join on）

外連接分為左外連接和右外連接。如果聯(lián)合查詢，左側(cè)的表完全顯示我們就說(shuō)是左外連接；右側(cè)的表完全顯示我們就說(shuō)是右外連接。

語(yǔ)法：

左外連接，表1完全顯示

select 字段名  from 表名1 left join 表名2 on 連接條件;

右外連接，表2完全顯示

select 字段 from 表名1 right join 表名2 on 連接條件;

4.自連接

自連接是指在同一張表中連接自身進(jìn)行查詢。

示例：顯示所有“數(shù)學(xué)”成績(jī)比“語(yǔ)文”成績(jī)高的學(xué)生成績(jī)信息

先查詢“數(shù)學(xué)”和“語(yǔ)文”課程的id

select id,name from course where name='數(shù)學(xué)' or name='語(yǔ)文';

數(shù)學(xué)id=1；

語(yǔ)文id=2；

再查詢成績(jī)表中，“數(shù)學(xué)”成績(jī)比“語(yǔ)文”成績(jī) 好的信息

select s1.* from score s1,score s2 where s1.student_id = s2.student_id and s1.score > s2.score and s1.course_id = 1 and s2.course_id = 2;

5.子查詢

子查詢是指嵌入在其他sql語(yǔ)句中的select語(yǔ)句，也叫嵌套查詢。

單行子查詢：返回一行記錄的子查詢

select * from student where classes_id=(select classes_id from student where name='張三');

多行子查詢：返回多行記錄的子查詢

1.使用(not) in 關(guān)鍵字

使用 in 

select * from score where course_id in (select id from course where

name='語(yǔ)文' or name='英文');

使用not in

select * from score where course_id not in (select id from course where

name!='語(yǔ)文' and name!='英文');

1.使用(not) exists 關(guān)鍵字

使用 exists 

select * from score sco where exists (select sco.id from course cou

where (name='語(yǔ)文' or name='英文') and cou.id = sco.course_id);

使用 not exists

select * from score sco where not exists (select sco.id from course cou

where (name!='語(yǔ)文' and name!='英文') and cou.id = sco.course_id);

在from子句中使用子查詢，把一個(gè)子查詢當(dāng)做一個(gè)臨時(shí)表使用。(not)in是放在內(nèi)存中的，如果查詢的數(shù)據(jù)太大，內(nèi)存中放不下，此時(shí)就需要使用(not)exists。exists本質(zhì)上就是讓數(shù)據(jù)庫(kù)執(zhí)行多個(gè)查詢操作，并把結(jié)果放在磁盤(pán)中，因此對(duì)于exists來(lái)說(shuō)，執(zhí)行效率大大低于in，而且可讀性也不是很好，這種比較適合處理一些特殊的場(chǎng)景。

6.合并查詢

合并查詢本質(zhì)上就是把兩個(gè)查詢結(jié)果集合并成一個(gè)，但是要求這兩個(gè)結(jié)果集的列一樣，才能合并。即：

為了合并多個(gè)select的執(zhí)行結(jié)果，可以使用集合操作符 union，union all。使用union和union all時(shí)，前后查詢的結(jié)果集中，字段需要一致。

1.union關(guān)鍵字

用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，會(huì)自動(dòng)去掉結(jié)果集中的重復(fù)行。

示例：

select?* from?course where?id<3 union select?* from?course where?name='英文';

或者使用or來(lái)實(shí)現(xiàn)

select?* from?course where?id<3 or?name='英文'；

2.union all關(guān)鍵字

用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，不會(huì)去掉結(jié)果集中的重復(fù)行。

示例：

可以看到結(jié)果集中出現(xiàn)重復(fù)數(shù)據(jù)

select?* from?course where?id<3 union?all select?* from?course where?name='英文'；

到此這篇關(guān)于MySQL中的聚合查詢和聯(lián)合查詢的文章就介紹到這了,更多相關(guān)mysql聚合查詢和聯(lián)合查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論