MySQL 子查詢和分組查詢
概述
子查詢是SQL查詢中的重要一塊，是我們基于多表之間進行數(shù)據(jù)聚合和判斷的一種手段，使得我們的處理復雜數(shù)據(jù)更加的便捷，這一節(jié)我們主要來了解一下子查詢。
先做一下數(shù)據(jù)準備，這邊建立三張表：班級、學生、畢業(yè)成績表，用于后面的操作：
drop database if exists `Helenlyn_Class`;
create database `Helenlyn_Class`;
/*班級表*/
DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes` (
`classid` int primary key AUTO_INCREMENT comment '班級id',
`classname` varchar(30) DEFAULT NULL comment '班級名稱'
) ENGINE=InnoDB comment '班級表';
insert into `classes`(`classname`)
values ('初三一班'),('初三二班'),('初三三班');
/*學生表：這邊假設學生id和姓名都具有唯一性*/
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
`studentid` int primary key NOT NULL AUTO_INCREMENT comment '學生id',
`studentname` varchar(20) DEFAULT NULL comment '學生姓名',
`score` DECIMAL(10,2) DEFAULT NULL comment '畢業(yè)成績',
`classid` int(4) DEFAULT NULL comment '所屬班級id，來源于classes表的classid'
) ENGINE=InnoDB comment '學生表';
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è)考核分數(shù)排名表*/
DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores`(
`scoregrad` varchar(3) primary key comment '等級：S、A、B、C、D',
`downset` int comment '分數(shù)評級下限',
`upset` int comment '分數(shù)評級上限'
) comment '畢業(yè)考核分數(shù)排名表';
INSERT INTO `scores` values ('S', 91, 100),('A', 81, 90),('B', 71, 80),('C', 61, 70),('D', 51,60);
子查詢
SQL支持創(chuàng)建子查詢（ subquery） ，就是嵌套在其他查詢中的查詢 ，也就是說在select語句中會出現(xiàn)其他的select語句，我們稱為子查詢或內(nèi)查詢。而外部的select語句，稱主查詢或外查詢。
子查詢分類
按照查詢的返回結果
1、單行單列（標量子查詢）：返回的是一個具體列的內(nèi)容，可以理解為一個單值數(shù)據(jù)；
2、單行多列（行子查詢）：返回一行數(shù)據(jù)中多個列的內(nèi)容；
3、多行單列（列子查詢）：返回多行記錄之中同一列的內(nèi)容，相當于給出了一個操作范圍；
4、多行多列（表子查詢）：查詢返回的結果是一張臨時表；
按子查詢位置區(qū)分
select后的子查詢：僅僅支持標量子查詢，即只能返回一個單值數(shù)據(jù)。
from型子查詢：把內(nèi)層的查詢結果當成臨時表，供外層sql再次查詢，所以支持的是表子查詢。
where或having型子查詢：指把內(nèi)部查詢的結果作為外層查詢的比較條件，支持標量子查詢（單列單行）、列子查詢（單列多行）、行子查詢（多列多行）。
一般會和下面這幾種方式配合使用：
1）、in子查詢：內(nèi)層查詢語句僅返回一個數(shù)據(jù)列，這個數(shù)據(jù)列的值將供外層查詢語句進行比較。
2）、any子查詢：只要滿足內(nèi)層子查詢中的任意一個比較條件，就返回一個結果作為外層查詢條件。
3）、all子查詢：內(nèi)層子查詢返回的結果需同時滿足所有內(nèi)層查詢條件。
4）、比較運算符子查詢：子查詢中可以使用的比較運算符如 >、>=、<=、<、=、 <>
exists子查詢：把外層的查詢結果（支持多行多列），拿到內(nèi)層，看內(nèi)層是否成立，簡單來說后面的返回true,外層（也就是前面的語句）才會執(zhí)行，否則不執(zhí)行。
下面我們一個個來測試。
select后子查詢
位于select后面，僅僅支持標量子查詢，即只能返回一個單值數(shù)據(jù)。比如上面的學生班級表，我們查詢每個班級的學生數(shù)量，可以這么寫：
mysql> select a.classid as 班級編號,a.classname as 班級名稱, (select count(*) from students b where b.classid = a.classid) as 學生數(shù)量 from classes a; +----------+----------+----------+ | 班級編號 | 班級名稱 | 學生數(shù)量 | +----------+----------+----------+ | 1 | 初三一班 | 6 | | 2 | 初三二班 | 6 | | 3 | 初三三班 | 6 | +----------+----------+----------+ 3 rows in set
查詢學生brand 所屬的班級，可以這么寫：
mysql> select (select classname from classes a,students b where a.classid = b.classid and b.studentname='brand') as 班級; +----------+ | 班級 | +----------+ | 初三一班 | +----------+ 1 row in set
from后子查詢
把內(nèi)層的查詢結果當成臨時表，提供外層sql再次查詢，支持的是表子查詢。但是必須對子查詢起別名，否則無法找到表。
查詢每個班級的平均成績：
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
查詢畢業(yè)考核分數(shù)排名表：S開始從高到低排序。
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
如果綜合兩個查詢結果，想查出 各個班級的平均成績是位于什么段位，就可以用from后子查詢，代碼如下：
select a.classid as 班級id,a.avgscore 平均畢業(yè)分數(shù),b.scoregrad 分數(shù)評級 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; +--------+--------------+----------+ | 班級id | 平均畢業(yè)分數(shù) | 分數(shù)評級 | +--------+--------------+----------+ | 1 | 96.616667 | S | | 2 | 83.500000 | A | | 3 | 73.583333 | B | +--------+--------------+----------+ 3 rows in set
對于子表查詢，必須提供別名，否則會提示：Every derived table must have its own alias，可以試試。
where和having型的子查詢
根據(jù)我們上面提到過的內(nèi)容，where或having后面，可以使用3種方式：標量子查詢（單行單列行子查詢）；列子查詢（單列多行子查詢）行子查詢（多行多列）；
他有如下共同的特點：
1、一般用括號將子查詢包起來。
2、子查詢一般放在條件的右側。
3、標量子查詢，一般搭配著單行操作符使用，多行操作符 >、<、>=、<=、=、<>
4、列子查詢，一般搭配著多行操作符使用
5、配合 in、not in、all、any使用，in是指列表中的任意一個，any是比較列表中任意一個 score>any(60,70,80) 則 score>60即可；all 是比較列表中所有,score > (60,70,80)，score需 >80。
單個標量子查詢應用
就是where或者having后面只跟一個標量查詢的，比如查詢出比diny(92.7分)成績好的同學：
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
多個標量子查詢應用
where或者having后面只跟一個標量查詢的，比如查詢出比diny(92.7分)成績差的同學，并且班級跟diny不在同一班：
mysql> select * from students a where a.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
子查詢+分組函數(shù)
分別取出三個班級的平均成績，并篩選出低于全年級的平均成績的班級信息，使用having表達式
mysql> select a.classid,avg(a.score) as avgscore from students a group by a.classid having avgscore < (select avg(score) from students); +---------+-----------+ | classid | avgscore | +---------+-----------+ | 2 | 83.500000 | | 3 | 73.583333 | +---------+-----------+ 2 rows in set
列子查詢說明
列的子查詢需要搭配多行操作符：in(not in)、any/some、all。使用distinct關鍵字進行去重可以提高執(zhí)行效率。
列子查詢+in：所有非三班的同學
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
列子查詢+any：任意非三班的同學
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
列子查詢+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
行子查詢說明
查詢學生編號最小但是成績最好的同學：
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 set
exists子查詢
也叫做相關子查詢，就是把外層的查詢結果（支持多行多列），拿到內(nèi)層，看內(nèi)層是否成立，簡單來說后面的返回true,外層（也就是前面的語句）才會執(zhí)行，否則不執(zhí)行。
1、exists查詢結果：1或0，1為true，0為false，exists查詢的結果用來判斷子查詢的結果集中是否有值。
2、exists子查詢，一般可以用in來替代，所以exists用的少。
3、和前面的那些查詢方式不同，先執(zhí)行主查詢，然后根據(jù)主查詢的結果，再用子查詢的結果來過濾。因為子查詢中包含了主查詢中用到的字段，所以也叫相關子查詢。
示例，查詢所有學生的班級名稱
mysql> select classname from classes a where exists(select 1 from students b where b.classid = a.classid); +-----------+ | classname | +-----------+ | 初三一班 | | 初三二班 | | 初三三班 | +-----------+ 3 rows in set
使用 in 來替代（看著更簡潔）：
mysql> select classname from classes a where a.classid in(select classid from students); +-----------+ | classname | +-----------+ | 初三一班 | | 初三二班 | | 初三三班 | +-----------+ 3 rows in set
組合查詢
多數(shù)SQL查詢都只包含從一個或多個表中返回數(shù)據(jù)的單條SELECT語句。 MySQL也允許執(zhí)行多個查詢（多條SELECT語句），并將結果作為單個
查詢結果集返回。這些組合查詢通常稱為并（ union） 或復合查詢（compound query）。
單表多次返回
將不同查詢條件的結果組合在一起
select cname1,cname2 from tname where condition1 union select cname1,cname2 from tname where condition2
多表返回同結構
將同數(shù)量結構的字段組合
select t1_cname1,t1_cname2 from tname1 where condition union select t2_cname1,t_2cname2 from tname2 where condition
這邊不贅述，后面有專門的章節(jié)說到這個
總結
可以按照查詢的返回類型和語句中子查詢的位置兩個方面來學習
注意使用 in、any、some、all的用法
無論是比較還是查詢還是count，字段中有null值總會引起誤解，建議建表時字段不為空，或者提供默認值。
以上就是MySQL 子查詢和分組查詢的詳細內(nèi)容，更多關于MySQL 查詢的資料請關注腳本之家其它相關文章！
相關文章
MySQL?阻塞是指在并發(fā)訪問?MySQL?數(shù)據(jù)庫時，某個事務占用了資源并且長時間不釋放，導致其他事務無法執(zhí)行或執(zhí)行緩慢的情況，那如何排查和定位阻塞語句呢，下面來分析下吧2023-07-07
這篇文章主要介紹了MySQL如何設置某個字段的值自增，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-08-08
SQLyog錯誤號碼MySQL?plugin?caching_sha2_password?could?not?
這篇文章主要介紹了SQLyog錯誤號碼?plugin?caching_sha2_password?could?not?be?loaded的解決方法,需要的朋友可以參考下2023-06-06
MySQL insert into select 主鍵沖突解決方案
本文主要介紹了MySQL insert into select主鍵沖突解決方案,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2024-06-06
這篇文章介紹了SQL?Server的全文搜索功能，文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-04-04
在本文中我們給大家分享了關于怎么使用Mysql數(shù)據(jù)表中的蠕蟲復制的相關知識點，有興趣的朋友們學習下。2019-02-02
在項目中會遇到同一個表中保存著父子關系的數(shù)據(jù),最常見的就是處理樹形結構資源,這篇文章主要給大家介紹了關于Mysql8.0遞歸查詢的簡單用法,需要的朋友可以參考下2021-08-08
MySQL8.0連接協(xié)議及3306、33060、33062端口的作用解析
這篇文章主要介紹了MySQL8.0連接協(xié)議及3306、33060、33062端口的作用解析，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-08-08
MySQL自然連接是一種基于共同列名自動執(zhí)行的查詢方式,無需顯式指定連接條件,自然連接的結果可能會受到列名和表結構的影響,因此在使用自然連接時需謹慎,本文就來介紹一下如何使用,感興趣的可以了解一下2024-09-09
磁盤碎片是指文件在磁盤上分散存儲的情況,這可能導致磁盤讀寫速度下降、空間浪費等問題,本文主要介紹了MySQL定期整理磁盤碎片的方法實現(xiàn),感興趣的可以了解一下2024-02-02