子查询
来源:互联网 发布:在centos上运行jdk 编辑:程序博客网 时间:2024/04/29 11:11
1 子查询定义
在一个表表达中可以调用另一个表表达式,这个被调用的表表达式叫做子查询(subquery),我么也称作子选择(subselect)或内嵌选择(inner select)。子查询的结果传递给调用它的表表达式继续处理。
2 子查询分类
2.1 按返回结果集分类
子查询按返回结果集的不同分为4种:表子查询,行子查询,列子查询和标量子查询。
表子查询:返回的结果集是一个行的集合,N行N列(N>=1)。表子查询经常用于父查询的FROM子句中。
行子查询:返回的结果集是一个列的集合,一行N列(N>=1)。行子查询可以用于福查询的FROM子句和WHERE子句中。
列子查询:返回的结果集是一个行的集合,N行一列(N>=1)。
标量子查询:返回的结果集是一个标量集合,一行一列,也就是一个标量值。可以指定一个标量表达式的任何地方,都可以用一个标量子查询。
从定义上讲,每个标量子查询也是一个行子查询和一个列子查询,反之则不是;每个行子查询和列子查询也是一个表子查询,反之也不是。
2.2 按照对返回结果的调用方法
子查询按对返回结果集的调用方法,可分为where型子查询,from型子查询及exists型子查询。
where型子查询:(把内层查询结果当作外层查询的比较条件)
定义:where型的子查询就是把内层查询的结果当作外层查询的条件。
from型子查询:(把内层的查询结果供外层再次查询)
定义:from子查询就是把子查询的结果(内存里的一张表)当作一张临时表,然后再对它进行处理。
exists型子查询:(把外层查询结果拿到内层,看内层的查询是否成立)
定义:exists子查询就是对外层表进行循环,再对内表进行内层查询。和in ()差不多,但是它们还是有区别的。主要是看两个张表大小差的程度。若子查询表大则用exists(内层索引),子查询表小则用in(外层索引);
3 使用子查询原则
1.一个子查询必须放在圆括号中。
2.将子查询放在比较条件的右边以增加可读性。
子查询不包含 ORDER BY 子句。对一个 SELECT 语句只能用一个 ORDER BY 子句,并且如果指定了它就必须放在主 SELECT 语句的最后。
3.在子查询中可以使用两种比较条件:单行运算符(>, =, >=, <, <>, <=) 和多行运算符(IN, ANY, ALL)。
4 实例分析
4.1 创建测试表
CREATE
TABLE
`players` (
`PLAYERNO`
int
(11)
NOT
NULL
,
`
NAME
`
char
(15)
NOT
NULL
,
`INITIALS`
char
(3)
NOT
NULL
,
`BIRTH_DATE`
date
DEFAULT
NULL
,
`SEX`
char
(1)
NOT
NULL
,
`JOINED`
smallint
(6)
NOT
NULL
,
`STREET`
varchar
(30)
NOT
NULL
,
`HOUSENO`
char
(4)
DEFAULT
NULL
,
`POSTCODE`
char
(6)
DEFAULT
NULL
,
`TOWN`
varchar
(30)
NOT
NULL
,
`PHONENO`
char
(13)
DEFAULT
NULL
,
`LEAGUENO`
char
(4)
DEFAULT
NULL
,
PRIMARY
KEY
(`PLAYERNO`)
) ENGINE=InnoDB
DEFAULT
CHARSET=latin1;
CREATE
TABLE
`penalties` (
`PAYMENTNO`
int
(11)
NOT
NULL
,
`PLAYERNO`
int
(11)
NOT
NULL
,
`PAYMENT_DATE`
date
NOT
NULL
,
`AMOUNT`
decimal
(7,2)
NOT
NULL
,
PRIMARY
KEY
(`PAYMENTNO`)
) ENGINE=InnoDB
DEFAULT
CHARSET=latin1;
注:表players为球员信息基本表,表penalties为有过罚款记录的球员信息列表。
4.2 插入测试数据
INSERT
INTO
PLAYERS
VALUES
(2,
'Everett'
,
'R'
,
'1948-09-01'
,
'M'
, 1975,
'Stoney Road'
,
'43'
,
'3575NH'
,
'Stratford'
,
'070-237893'
,
'2411'
);
INSERT
INTO
PLAYERS
VALUES
(6,
'Parmenter'
,
'R'
,
'1964-06-25'
,
'M'
, 1977,
'Haseltine Lane'
,
'80'
,
'1234KK'
,
'Stratford'
,
'070-476537'
,
'8467'
);
INSERT
INTO
PLAYERS
VALUES
(7,
'Wise'
,
'GWS'
,
'1963-05-11'
,
'M'
, 1981,
'Edgecombe Way'
,
'39'
,
'9758VB'
,
'Stratford'
,
'070-347689'
,
NULL
);
INSERT
INTO
PLAYERS
VALUES
(8,
'Newcastle'
,
'B'
,
'1962-07-08'
,
'F'
, 1980,
'Station Road'
,
'4'
,
'6584WO'
,
'Inglewood'
,
'070-458458'
,
'2983'
);
INSERT
INTO
PLAYERS
VALUES
(27,
'Collins'
,
'DD'
,
'1964-12-28'
,
'F'
, 1983,
'Long Drive'
,
'804'
,
'8457DK'
,
'Eltham'
,
'079-234857'
,
'2513'
);
INSERT
INTO
PLAYERS
VALUES
(28,
'Collins'
,
'C'
,
'1963-06-22'
,
'F'
, 1983,
'Old Main Road'
,
'10'
,
'1294QK'
,
'Midhurst'
,
'010-659599'
,
NULL
);
INSERT
INTO
PLAYERS
VALUES
(39,
'Bishop'
,
'D'
,
'1956-10-29'
,
'M'
, 1980,
'Eaton Square'
,
'78'
,
'9629CD'
,
'Stratford'
,
'070-393435'
,
NULL
);
INSERT
INTO
PLAYERS
VALUES
(44,
'Baker'
,
'E'
,
'1963-01-09'
,
'M'
, 1980,
'Lewis Street'
,
'23'
,
'4444LJ'
,
'Inglewood'
,
'070-368753'
,
'1124'
);
INSERT
INTO
PLAYERS
VALUES
(57,
'Brown'
,
'M'
,
'1971-08-17'
,
'M'
, 1985,
'Edgecombe Way'
,
'16'
,
'4377CB'
,
'Stratford'
,
'070-473458'
,
'6409'
);
INSERT
INTO
PLAYERS
VALUES
(83,
'Hope'
,
'PK'
,
'1956-11-11'
,
'M'
, 1982,
'Magdalene Road'
,
'16A'
,
'1812UP'
,
'Stratford'
,
'070-353548'
,
'1608'
);
INSERT
INTO
PLAYERS
VALUES
(95,
'Miller'
,
'P'
,
'1963-05-14'
,
'M'
, 1972,
'High Street'
,
'33A'
,
'5746OP'
,
'Douglas'
,
'070-867564'
,
NULL
);
INSERT
INTO
PLAYERS
VALUES
(100,
'Parmenter'
,
'P'
,
'1963-02-28'
,
'M'
, 1979,
'Haseltine Lane'
,
'80'
,
'6494SG'
,
'Stratford'
,
'070-494593'
,
'6524'
);
INSERT
INTO
PLAYERS
VALUES
(104,
'Moorman'
,
'D'
,
'1970-05-10'
,
'F'
, 1984,
'Stout Street'
,
'65'
,
'9437AO'
,
'Eltham'
,
'079-987571'
,
'7060'
);
INSERT
INTO
PLAYERS
VALUES
(112,
'Bailey'
,
'IP'
,
'1963-10-01'
,
'F'
, 1984,
'Vixen Road'
,
'8'
,
'6392LK'
,
'Plymouth'
,
'010-548745'
,
'1319'
);
INSERT
INTO
PENALTIES
VALUES
(1, 6,
'1980-12-08'
,100);
INSERT
INTO
PENALTIES
VALUES
(2, 44,
'1981-05-05'
, 75);
INSERT
INTO
PENALTIES
VALUES
(3, 27,
'1983-09-10'
,100);
INSERT
INTO
PENALTIES
VALUES
(4,104,
'1984-12-08'
, 50);
INSERT
INTO
PENALTIES
VALUES
(5, 44,
'1980-12-08'
, 25);
INSERT
INTO
PENALTIES
VALUES
(6, 8,
'1980-12-08'
, 25);
INSERT
INTO
PENALTIES
VALUES
(7, 44,
'1982-12-30'
, 30);
INSERT
INTO
PENALTIES
VALUES
(8, 27,
'1984-11-12'
, 75);
4.3 表子查询实例
#获取编号小于10的男性球员的号码
#
from
型子查询
mysql>
select
playerno
from
(
select
playerno, sex
from
players
where
playerno < 10)
as
players10
where
sex=
'M'
;
4.4 行子查询实例
#获取和100号球员性别相同并且居住在同一城市的球员号码。
#
where
型子查询
mysql>
select
playerno
from
players
where
(sex, town) = (
select
sex, town
from
players
where
playerno = 100);
说明:子查询的结果是带有两个值的一行:('M','stratford')。这个值和一个行表达式(sex,town)进行比较。
4.5 标量子查询实例
可以指定一个标量表达式的任何地方,几乎都可以使用一个标量子查询。
#获取和27号球员出生在同一年的球员的号码
mysql>
select
playerno
from
players
where
year
(birth_date) =
(
select
year
(birth_date)
from
players
where
playerno = 27)
and
playerno <> 27;
4.6 列子查询实例
由于列子查询返回的结果集是 N 行一列,因此不能直接使用 = > < >= <= <> 这些比较标量结果的操作符。在列子查询中可以使用 IN、ANY(SOME)和ALL操作符:
IN:在指定项内,同 IN(项1,项2,…)。
ANY:与比较操作符联合使用,ANY关键字必须接在一个比较操作符的后面,表示与子查询返回的任何值比较为 TRUE ,则返回 TRUE 。
SOME:ANY 的别名,较少使用。
ALL:与比较操作符联合使用,ALL关键字必须接在一个比较操作符的后面,表示与子查询返回的所有值比较都为 TRUE ,则返回 TRUE 。
#获取球员性别为女的所有球员的球员号,名字及所在城市。
mysql>
select
playerno,
name
, town
from
players
where
playerno
in
(
select
playerno
from
players
where
sex =
'F'
);
#获取至少比同城的另一球员年轻的所有球员的号码,日期和居住城市。
mysql>
select
playerno, birth_date, town
from
players
as
p1
where
birth_date >
any
(
select
birth_date
from
players
as
p2
where
p1.town = p2.town);
#获取最老球员的号码,名字及生日。(即出生日期数值小于或等于所有其它球员的球员)
mysql>
select
playerno,
name
, birth_date
from
players
where
birth_date <=
all
(
select
birth_date
from
players);
4.7 exists型子查询
EXISTS关键字表示存在。使用EXISTS关键字时,内层查询语句不返回查询的记录,而是返回一个真假值,如果内层查询语句查询到满足条件的记录,只要子查询中至少返回一个值,则EXISTS语句的值就为True。就返回true,否则返回false。当返回的值为true时,外层查询语句将进行查询,否则不进行查询。NOT EXISTS刚好与之相反。exists的用法和in ()差不多,但是它们还是有区别的。主要是看两个张表大小差的程度。若子查询表大则用exists(内层索引),子查询表小则用in(外层索引);
#实例1(exists):获取那些至少支付了一次罚款的球员的名字和首字母。
mysql>
select
name
, initials
from
players
where
exists
(
select
*
from
penalties
where
playerno = players.playerno);
#实例2(
not
exists):获取那些从来没有罚款的球员的名字和首字母。
mysql>
select
name
, initials
from
players
where
not
exists
(
select
*
from
penalties
where
playerno = players.playerno);
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- 子查询
- Android Volley完全解析(一),初识Volley的基本用法
- Kafka集群部署
- Scala语法整理
- JavaScript高级程序设计(读书笔记)之函数表达式
- java已经影响了很多人,成为了热门的程序!
- 子查询
- 用IntelliJ IDEA创建Gradle项目简单入门
- 项目网络层重构总结
- LwIP协议栈源码详解—TCP定时器
- FragmentTabHost 禁止制定tab点击事件
- REmapC
- Linux计划任务(at,crontab)
- android studio 中的编码问题
- 1110. Complete Binary Tree (25) -- 完全二叉树相关性质, 求树根两种方法