概述

子查詢(xún)是SQL查詢(xún)中的重要一塊，是我們基于多表之間進(jìn)行數(shù)據(jù)聚合和判斷的一種手段，使得我們的處理復(fù)雜數(shù)據(jù)更加的便捷，這一節(jié)我們主要來(lái)了解一下子查詢(xún)。

先做一下數(shù)據(jù)準(zhǔn)備，這邊建立三張表：班級(jí)、學(xué)生、畢業(yè)成績(jī)表，用于后面的操作：

drop database if exists `Helenlyn_Class`;create database `Helenlyn_Class`;/*班級(jí)表*/DROP TABLE IF EXISTS `classes`;CREATE TABLE `classes` ( `classid` int primary key AUTO_INCREMENT comment ’班級(jí)id’, `classname` varchar(30) DEFAULT NULL comment ’班級(jí)名稱(chēng)’) ENGINE=InnoDB comment ’班級(jí)表’;insert into `classes`(`classname`)values (’初三一班’),(’初三二班’),(’初三三班’);/*學(xué)生表：這邊假設(shè)學(xué)生id和姓名都具有唯一性*/DROP TABLE IF EXISTS `students`;CREATE TABLE `students` ( `studentid` int primary key NOT NULL AUTO_INCREMENT comment ’學(xué)生id’, `studentname` varchar(20) DEFAULT NULL comment ’學(xué)生姓名’, `score` DECIMAL(10,2) DEFAULT NULL comment ’畢業(yè)成績(jī)’, `classid` int(4) DEFAULT NULL comment ’所屬班級(jí)id，來(lái)源于classes表的classid’) ENGINE=InnoDB comment ’學(xué)生表’;insert into `students`(`studentname`,`score`,`classid`) values(’brand’,97.5,1),(’helen’,96.5,1),(’lyn’,96,1),(’sol’,97,1),(’weng’,100,1),(’diny’,92.7,1),(’b1’,81,2),(’b2’,82,2),(’b3’,83,2),(’b4’,84,2),(’b5’,85,2),(’b6’,86,2),(’c1’,71,3),(’c2’,72.5,3),(’c3’,73,3),(’c4’,74,3),(’c5’,75,3),(’c6’,76,3);/*畢業(yè)考核分?jǐn)?shù)排名表*/DROP TABLE IF EXISTS `scores`;CREATE TABLE `scores`( `scoregrad` varchar(3) primary key comment ’等級(jí)：S、A、B、C、D’, `downset` int comment ’分?jǐn)?shù)評(píng)級(jí)下限’, `upset` int comment ’分?jǐn)?shù)評(píng)級(jí)上限’) comment ’畢業(yè)考核分?jǐn)?shù)排名表’;INSERT INTO `scores` values (’S’, 91, 100),(’A’, 81, 90),(’B’, 71, 80),(’C’, 61, 70),(’D’, 51,60);子查詢(xún)

SQL支持創(chuàng)建子查詢(xún)（ subquery） ，就是嵌套在其他查詢(xún)中的查詢(xún) ，也就是說(shuō)在select語(yǔ)句中會(huì)出現(xiàn)其他的select語(yǔ)句，我們稱(chēng)為子查詢(xún)或內(nèi)查詢(xún)。而外部的select語(yǔ)句，稱(chēng)主查詢(xún)或外查詢(xún)。

子查詢(xún)分類(lèi)按照查詢(xún)的返回結(jié)果

1、單行單列（標(biāo)量子查詢(xún)）：返回的是一個(gè)具體列的內(nèi)容，可以理解為一個(gè)單值數(shù)據(jù)；

2、單行多列（行子查詢(xún)）：返回一行數(shù)據(jù)中多個(gè)列的內(nèi)容；

3、多行單列（列子查詢(xún)）：返回多行記錄之中同一列的內(nèi)容，相當(dāng)于給出了一個(gè)操作范圍；

4、多行多列（表子查詢(xún)）：查詢(xún)返回的結(jié)果是一張臨時(shí)表；

按子查詢(xún)位置區(qū)分

select后的子查詢(xún)：僅僅支持標(biāo)量子查詢(xún)，即只能返回一個(gè)單值數(shù)據(jù)。

from型子查詢(xún)：把內(nèi)層的查詢(xún)結(jié)果當(dāng)成臨時(shí)表，供外層sql再次查詢(xún)，所以支持的是表子查詢(xún)。

where或having型子查詢(xún)：指把內(nèi)部查詢(xún)的結(jié)果作為外層查詢(xún)的比較條件，支持標(biāo)量子查詢(xún)（單列單行）、列子查詢(xún)（單列多行）、行子查詢(xún)（多列多行）。

一般會(huì)和下面這幾種方式配合使用：

1）、in子查詢(xún)：內(nèi)層查詢(xún)語(yǔ)句僅返回一個(gè)數(shù)據(jù)列，這個(gè)數(shù)據(jù)列的值將供外層查詢(xún)語(yǔ)句進(jìn)行比較。

2）、any子查詢(xún)：只要滿(mǎn)足內(nèi)層子查詢(xún)中的任意一個(gè)比較條件，就返回一個(gè)結(jié)果作為外層查詢(xún)條件。

3）、all子查詢(xún)：內(nèi)層子查詢(xún)返回的結(jié)果需同時(shí)滿(mǎn)足所有內(nèi)層查詢(xún)條件。

4）、比較運(yùn)算符子查詢(xún)：子查詢(xún)中可以使用的比較運(yùn)算符如 >、>=、<=、<、=、 <>

exists子查詢(xún)：把外層的查詢(xún)結(jié)果（支持多行多列），拿到內(nèi)層，看內(nèi)層是否成立，簡(jiǎn)單來(lái)說(shuō)后面的返回true,外層（也就是前面的語(yǔ)句）才會(huì)執(zhí)行，否則不執(zhí)行。

下面我們一個(gè)個(gè)來(lái)測(cè)試。

select后子查詢(xún)

位于select后面，僅僅支持標(biāo)量子查詢(xún)，即只能返回一個(gè)單值數(shù)據(jù)。比如上面的學(xué)生班級(jí)表，我們查詢(xún)每個(gè)班級(jí)的學(xué)生數(shù)量，可以這么寫(xiě)：

mysql> select a.classid as 班級(jí)編號(hào),a.classname as 班級(jí)名稱(chēng),(select count(*) from students b where b.classid = a.classid) as 學(xué)生數(shù)量from classes a;+----------+----------+----------+| 班級(jí)編號(hào) | 班級(jí)名稱(chēng) | 學(xué)生數(shù)量 |+----------+----------+----------+| 1 | 初三一班 | 6 || 2 | 初三二班 | 6 || 3 | 初三三班 | 6 |+----------+----------+----------+3 rows in set

查詢(xún)學(xué)生brand 所屬的班級(jí)，可以這么寫(xiě)：

mysql> select(select classname from classes a,students b where a.classid = b.classid and b.studentname=’brand’)as 班級(jí);+----------+| 班級(jí) |+----------+| 初三一班 |+----------+1 row in setfrom后子查詢(xún)

把內(nèi)層的查詢(xún)結(jié)果當(dāng)成臨時(shí)表，提供外層sql再次查詢(xún)，支持的是表子查詢(xún)。但是必須對(duì)子查詢(xún)起別名，否則無(wú)法找到表。

查詢(xún)每個(gè)班級(jí)的平均成績(jī)：

mysql> select a.classid,avg(a.score) from students a group by a.classid;+---------+--------------+| classid | avg(a.score) |+---------+--------------+| 1 | 96.616667 || 2 | 83.500000 || 3 | 73.583333 |+---------+--------------+3 rows in set

查詢(xún)畢業(yè)考核分?jǐn)?shù)排名表：S開(kāi)始從高到低排序。

mysql> select * from scores order by upset desc;+-----------+---------+-------+| scoregrad | downset | upset |+-----------+---------+-------+| S | 91 | 100 || A | 81 | 90 || B | 71 | 80 || C | 61 | 70 || D | 51 | 60 |+-----------+---------+-------+5 rows in set

如果綜合兩個(gè)查詢(xún)結(jié)果，想查出 各個(gè)班級(jí)的平均成績(jī)是位于什么段位，就可以用from后子查詢(xún)，代碼如下：

select a.classid as 班級(jí)id,a.avgscore 平均畢業(yè)分?jǐn)?shù),b.scoregrad 分?jǐn)?shù)評(píng)級(jí) from(select classid,avg(score) as avgscore from students group by classid) as a,scores b where a.avgscore between b.downset and b.upset;+--------+--------------+----------+| 班級(jí)id | 平均畢業(yè)分?jǐn)?shù) | 分?jǐn)?shù)評(píng)級(jí) |+--------+--------------+----------+| 1 | 96.616667 | S || 2 | 83.500000 | A || 3 | 73.583333 | B |+--------+--------------+----------+3 rows in set

對(duì)于子表查詢(xún)，必須提供別名，否則會(huì)提示：Every derived table must have its own alias，可以試試。

where和having型的子查詢(xún)

根據(jù)我們上面提到過(guò)的內(nèi)容，where或having后面，可以使用3種方式：標(biāo)量子查詢(xún)（單行單列行子查詢(xún)）；列子查詢(xún)（單列多行子查詢(xún)）行子查詢(xún)（多行多列）；

他有如下共同的特點(diǎn)：

1、一般用括號(hào)將子查詢(xún)包起來(lái)。

2、子查詢(xún)一般放在條件的右側(cè)。

3、標(biāo)量子查詢(xún)，一般搭配著單行操作符使用，多行操作符 >、<、>=、<=、=、<>

4、列子查詢(xún)，一般搭配著多行操作符使用

5、配合 in、not in、all、any使用，in是指列表中的任意一個(gè)，any是比較列表中任意一個(gè) score>any(60,70,80) 則 score>60即可；all 是比較列表中所有,score > (60,70,80)，score需 >80。

單個(gè)標(biāo)量子查詢(xún)應(yīng)用

就是where或者h(yuǎn)aving后面只跟一個(gè)標(biāo)量查詢(xún)的，比如查詢(xún)出比diny(92.7分)成績(jī)好的同學(xué)：

mysql> select * from students a where a.score >(select b.score from students b where b.studentname=’diny’);+-----------+-------------+-------+---------+| studentid | studentname | score | classid |+-----------+-------------+-------+---------+| 1 | brand | 97.5 | 1 || 2 | helen | 96.5 | 1 || 3 | lyn | 96 | 1 || 4 | sol | 97 | 1 || 5 | weng | 100 | 1 |+-----------+-------------+-------+---------+5 rows in set多個(gè)標(biāo)量子查詢(xún)應(yīng)用

where或者h(yuǎn)aving后面只跟一個(gè)標(biāo)量查詢(xún)的，比如查詢(xún)出比diny(92.7分)成績(jī)差的同學(xué)，并且班級(jí)跟diny不在同一班：

mysql> select * from students a wherea.score <(select b.score from students b where b.studentname=’diny’)and a.classid <> (select b.classid from students b where b.studentname=’diny’) ;+-----------+-------------+-------+---------+| studentid | studentname | score | classid |+-----------+-------------+-------+---------+| 7 | b1 | 81 | 2 || 8 | b2 | 82 | 2 || 9 | b3 | 83 | 2 || 10 | b4 | 84 | 2 || 11 | b5 | 85 | 2 || 12 | b6 | 86 | 2 || 13 | c1 | 71 | 3 || 14 | c2 | 72.5 | 3 || 15 | c3 | 73 | 3 || 16 | c4 | 74 | 3 || 17 | c5 | 75 | 3 || 18 | c6 | 76 | 3 |+-----------+-------------+-------+---------+12 rows in set子查詢(xún)+分組函數(shù)

分別取出三個(gè)班級(jí)的平均成績(jī)，并篩選出低于全年級(jí)的平均成績(jī)的班級(jí)信息，使用having表達(dá)式

mysql> select a.classid,avg(a.score) as avgscore from students a group by a.classidhaving avgscore < (select avg(score) from students);+---------+-----------+| classid | avgscore |+---------+-----------+| 2 | 83.500000 || 3 | 73.583333 |+---------+-----------+2 rows in set列子查詢(xún)說(shuō)明

列的子查詢(xún)需要搭配多行操作符：in(not in)、any/some、all。使用distinct關(guān)鍵字進(jìn)行去重可以提高執(zhí)行效率。

列子查詢(xún)+in：所有非三班的同學(xué)

mysql> select * from students a where a.classid in (select distinct b.classid from classes b where b.classid <3);+-----------+-------------+-------+---------+| studentid | studentname | score | classid |+-----------+-------------+-------+---------+| 1 | brand | 97.5 | 1 || 2 | helen | 96.5 | 1 || 3 | lyn | 96 | 1 || 4 | sol | 97 | 1 || 5 | weng | 100 | 1 || 6 | diny | 92.7 | 1 || 7 | b1 | 81 | 2 || 8 | b2 | 82 | 2 || 9 | b3 | 83 | 2 || 10 | b4 | 84 | 2 || 11 | b5 | 85 | 2 || 12 | b6 | 86 | 2 |+-----------+-------------+-------+---------+12 rows in set

列子查詢(xún)+any：任意非三班的同學(xué)

mysql> select * from students a where a.classid = any (select distinct b.classid from classes b where b.classid <3);+-----------+-------------+-------+---------+| studentid | studentname | score | classid |+-----------+-------------+-------+---------+| 1 | brand | 97.5 | 1 || 2 | helen | 96.5 | 1 || 3 | lyn | 96 | 1 || 4 | sol | 97 | 1 || 5 | weng | 100 | 1 || 6 | diny | 92.7 | 1 || 7 | b1 | 81 | 2 || 8 | b2 | 82 | 2 || 9 | b3 | 83 | 2 || 10 | b4 | 84 | 2 || 11 | b5 | 85 | 2 || 12 | b6 | 86 | 2 |+-----------+-------------+-------+---------+12 rows in set

列子查詢(xún)+all：等同于 not in

mysql> select * from students a where a.classid <> all (select distinct b.classid from classes b where b.classid <3);+-----------+-------------+-------+---------+| studentid | studentname | score | classid |+-----------+-------------+-------+---------+| 13 | c1 | 71 | 3 || 14 | c2 | 72.5 | 3 || 15 | c3 | 73 | 3 || 16 | c4 | 74 | 3 || 17 | c5 | 75 | 3 || 18 | c6 | 76 | 3 |+-----------+-------------+-------+---------+6 rows in set行子查詢(xún)說(shuō)明

查詢(xún)學(xué)生編號(hào)最小但是成績(jī)最好的同學(xué)：

mysql> select * from students a where (a.studentid, a.score) in (select max(studentid),min(score) from students);+-----------+-------------+-------+---------+| studentid | studentname | score | classid |+-----------+-------------+-------+---------+| 19 | lala | 51 | 0 |+-----------+-------------+-------+---------+1 row in setexists子查詢(xún)

也叫做相關(guān)子查詢(xún)，就是把外層的查詢(xún)結(jié)果（支持多行多列），拿到內(nèi)層，看內(nèi)層是否成立，簡(jiǎn)單來(lái)說(shuō)后面的返回true,外層（也就是前面的語(yǔ)句）才會(huì)執(zhí)行，否則不執(zhí)行。

1、exists查詢(xún)結(jié)果：1或0，1為true，0為false，exists查詢(xún)的結(jié)果用來(lái)判斷子查詢(xún)的結(jié)果集中是否有值。

2、exists子查詢(xún)，一般可以用in來(lái)替代，所以exists用的少。

3、和前面的那些查詢(xún)方式不同，先執(zhí)行主查詢(xún)，然后根據(jù)主查詢(xún)的結(jié)果，再用子查詢(xún)的結(jié)果來(lái)過(guò)濾。因?yàn)樽硬樵?xún)中包含了主查詢(xún)中用到的字段，所以也叫相關(guān)子查詢(xún)。

示例，查詢(xún)所有學(xué)生的班級(jí)名稱(chēng)

mysql> select classname from classes a where exists(select 1 from students b where b.classid = a.classid);+-----------+| classname |+-----------+| 初三一班 || 初三二班 || 初三三班 |+-----------+3 rows in set

使用 in 來(lái)替代（看著更簡(jiǎn)潔）：

mysql> select classname from classes a where a.classid in(select classid from students);+-----------+| classname |+-----------+| 初三一班 || 初三二班 || 初三三班 |+-----------+3 rows in set組合查詢(xún)

多數(shù)SQL查詢(xún)都只包含從一個(gè)或多個(gè)表中返回?cái)?shù)據(jù)的單條SELECT語(yǔ)句。 MySQL也允許執(zhí)行多個(gè)查詢(xún)（多條SELECT語(yǔ)句），并將結(jié)果作為單個(gè)查詢(xún)結(jié)果集返回。這些組合查詢(xún)通常稱(chēng)為并（ union） 或復(fù)合查詢(xún)（compound query）。

單表多次返回

將不同查詢(xún)條件的結(jié)果組合在一起

select cname1,cname2 from tname where condition1 union select cname1,cname2 from tname where condition2多表返回同結(jié)構(gòu)

將同數(shù)量結(jié)構(gòu)的字段組合

select t1_cname1,t1_cname2 from tname1 where condition union select t2_cname1,t_2cname2 from tname2 where condition

這邊不贅述，后面有專(zhuān)門(mén)的章節(jié)說(shuō)到這個(gè)

總結(jié)

可以按照查詢(xún)的返回類(lèi)型和語(yǔ)句中子查詢(xún)的位置兩個(gè)方面來(lái)學(xué)習(xí)

注意使用 in、any、some、all的用法

無(wú)論是比較還是查詢(xún)還是count，字段中有null值總會(huì)引起誤解，建議建表時(shí)字段不為空，或者提供默認(rèn)值。

以上就是MySQL 子查詢(xún)和分組查詢(xún)的詳細(xì)內(nèi)容，更多關(guān)于MySQL 查詢(xún)的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！