SELECT中子查询
2019-08-16 10:13:03 6 举报AI智能生成
SELECT中子查询
作者其他创作
大纲/内容
嵌套查询
进行复杂查询
查询结果集
分类
关联子查询
子查询需要执行多次，采用循环的方式，先从外部查询开始，
每次都传入子查询进行查询，然后再将结果反馈给外部的嵌套的执行方式
每次都传入子查询进行查询，然后再将结果反馈给外部的嵌套的执行方式
子查询的执行与主查询相关，需要执行多次
非关联子查询
数据结果作为主查询的条件进行执行的子查询
子查询的执行于主查询无关，只需执行一次
关键字
EXIST子查询
用来判断条件是否满足
EXIST vs IN
效率
小表驱动大表
集合比较子查询
IN
SOME
ANY
需要比较操作符一起使用
ALL
需要比较操作符一起使用
子查询作为主查询的列
作为列，需要起个别名
数据地址：https://github.com/cystanford/sql_nba_data
非关联子查询
查询哪个球员身高最高
SELECT player_name, height
FROM player
WHERE height = (SELECT MAX(height) FROM player)
关联子查询
查找球队中大于平均身高的球员有哪些，并且显示他们的球员姓名，身高以及所在球队ID
SELECT player_name, height, team_id
FROM player AS a
WHERE height > (SELECT AVG(height) FROM player AS b WHERE b.team_id = a.team_id)
查看出场过的球员都有哪些
SELECT player_id, team_id, player_name
FROM player
WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)
集合比较子查询
查询出场球员
SELECT player_id, team_id, player_name
FROM player
WHERE player_id in (SELECT player_id FROM player_score WHERE player.player_id = player_socre.player_id)
抽象模式：
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc = A.cc)
// 如果表A比表B大，效率上：IN > EXIST，
// 因为B表中如果对cc列进行了索引，IN的子查询效率会比较高
// 如果表A比表B小，效率上：EXIST > IN
// 因为A表中如果对cc列进行了索引
查询球员表中，比印第安纳步行者(team_id=1002)中任何一个球员身高高的球员的信息
并且输出球员的ID，姓名，身高
SELECT player_id, player_name, height
FROM player
WHERE height > ANY(SELECT height FROM player WHERE team_id=1002)
比印第安纳步行者(team_id=1002)中所有球员都高的球员的信息
SELECT player_id, player_name, height
FROM player
WHERE height > ALL(SELECT height FROM player WHERE team_id=1002)
子查询作为主查询的列
查询每个球队的球员数 // 作为列，需要起个别名player_num
SELECT team_name, (SELECT COUNT(*) FROM player WHERE player.team_id = team.team_id) AS player_num
FROM team
练习：
查询场均得分大于20的球员信息，包括球员的ID，姓名，球队ID的信息
SELECT player_id, player_name, team_id
FROM player
WHERE player_id IN
(SELECT player_id
FROM player_score
GROUNP BY player_score.player_id
HAVING AVG(player_score.score) > 20)
非关联子查询
查询哪个球员身高最高
SELECT player_name, height
FROM player
WHERE height = (SELECT MAX(height) FROM player)
关联子查询
查找球队中大于平均身高的球员有哪些，并且显示他们的球员姓名，身高以及所在球队ID
SELECT player_name, height, team_id
FROM player AS a
WHERE height > (SELECT AVG(height) FROM player AS b WHERE b.team_id = a.team_id)
查看出场过的球员都有哪些
SELECT player_id, team_id, player_name
FROM player
WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)
集合比较子查询
查询出场球员
SELECT player_id, team_id, player_name
FROM player
WHERE player_id in (SELECT player_id FROM player_score WHERE player.player_id = player_socre.player_id)
抽象模式：
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc = A.cc)
// 如果表A比表B大，效率上：IN > EXIST，
// 因为B表中如果对cc列进行了索引，IN的子查询效率会比较高
// 如果表A比表B小，效率上：EXIST > IN
// 因为A表中如果对cc列进行了索引
查询球员表中，比印第安纳步行者(team_id=1002)中任何一个球员身高高的球员的信息
并且输出球员的ID，姓名，身高
SELECT player_id, player_name, height
FROM player
WHERE height > ANY(SELECT height FROM player WHERE team_id=1002)
比印第安纳步行者(team_id=1002)中所有球员都高的球员的信息
SELECT player_id, player_name, height
FROM player
WHERE height > ALL(SELECT height FROM player WHERE team_id=1002)
子查询作为主查询的列
查询每个球队的球员数 // 作为列，需要起个别名player_num
SELECT team_name, (SELECT COUNT(*) FROM player WHERE player.team_id = team.team_id) AS player_num
FROM team
练习：
查询场均得分大于20的球员信息，包括球员的ID，姓名，球队ID的信息
SELECT player_id, player_name, team_id
FROM player
WHERE player_id IN
(SELECT player_id
FROM player_score
GROUNP BY player_score.player_id
HAVING AVG(player_score.score) > 20)
0 条评论
下一页
