国产免费高清在线精品一区,东京热之中文字幕

概述

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

先做一下數(shù)據(jù)準(zhǔn)備，這邊建立三張表：班級、學(xué)生、畢業(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 ('初三一班'),('初三二班'),('初三三班');

/*學(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è)成績',
 `classid` int(4) DEFAULT NULL comment '所屬班級id，來源于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 '等級：S、A、B、C、D',
 `downset` int comment '分?jǐn)?shù)評級下限',
 `upset` int comment '分?jǐn)?shù)評級上限'
) comment '畢業(yè)考核分?jǐn)?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語句，稱主查詢或外查詢。

子查詢分類

按照查詢的返回結(jié)果

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

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

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

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

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

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

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

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

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

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

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

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

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

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

下面我們一個個來測試。

select后子查詢

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

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

查詢學(xué)生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)層的查詢結(jié)果當(dāng)成臨時表，提供外層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è)考核分?jǐn)?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

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

select a.classid as 班級id,a.avgscore 平均畢業(yè)分?jǐn)?shù),b.scoregrad 分?jǐn)?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è)分?jǐn)?shù) | 分?jǐn)?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種方式：標(biāo)量子查詢（單行單列行子查詢）；列子查詢（單列多行子查詢）行子查詢（多行多列）；

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

1、一般用括號將子查詢包起來。

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

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

4、列子查詢，一般搭配著多行操作符使用

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

單個標(biāo)量子查詢應(yīng)用

就是where或者h(yuǎn)aving后面只跟一個標(biāo)量查詢的，比如查詢出比diny(92.7分)成績好的同學(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

多個標(biāo)量子查詢應(yīng)用

where或者h(yuǎn)aving后面只跟一個標(biāo)量查詢的，比如查詢出比diny(92.7分)成績差的同學(xué)，并且班級跟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表達(dá)式

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關(guān)鍵字進(jìn)行去重可以提高執(zhí)行效率。

列子查詢+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

列子查詢+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

列子查詢+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

行子查詢說明

查詢學(xué)生編號最小但是成績最好的同學(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 set

exists子查詢

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

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

2、exists子查詢，一般可以用in來替代，所以exists用的少。

3、和前面的那些查詢方式不同，先執(zhí)行主查詢，然后根據(jù)主查詢的結(jié)果，再用子查詢的結(jié)果來過濾。因?yàn)樽硬樵冎邪酥鞑樵冎杏玫降淖侄?，所以也叫相關(guān)子查詢。

示例，查詢所有學(xué)生的班級名稱

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語句），并將結(jié)果作為單個
查詢結(jié)果集返回。這些組合查詢通常稱為并（ union）或復(fù)合查詢（compound query）。

單表多次返回

將不同查詢條件的結(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

這邊不贅述，后面有專門的章節(jié)說到這個

總結(jié)

可以按照查詢的返回類型和語句中子查詢的位置兩個方面來學(xué)習(xí)

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

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

以上就是MySQL 子查詢和分組查詢的詳細(xì)內(nèi)容，更多關(guān)于MySQL 查詢的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章:

MySQL 分組查詢的優(yōu)化方法
MySQL 分組查詢和聚合函數(shù)
MySql中流程控制函數(shù)/統(tǒng)計(jì)函數(shù)/分組查詢用法解析
MySQL分組查詢Group By實(shí)現(xiàn)原理詳解
詳解MySQL中的分組查詢與連接查詢語句
mysql按照時間分組查詢的語句

標(biāo)簽：阿里公主嶺合肥惠州牡丹江呼和浩特沈陽天津

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL 子查詢和分組查詢》，本文關(guān)鍵詞 MySQL,子,查詢,和,分組,MySQL,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。