MySQL复杂查询 mysql数据库复杂查询educoder

MySql的复杂查询

• 一、连接查询

• 1.1、CROSS JOIN（交叉连接）
• 1.2、等值连接

• 1.2.1、NATURAL JOIN（自然连接）
• 1.2.2、USING 连接
• 1.2.3、ON 连接

• 1.3、非等值连接
• 1.4、外链接

• 1.4.1、LEFT OUTER JOIN （左外连接）
• 1.4.2、RIGHT OUTER JOIN（右外连接）
• 1.4.2、FULL OUTER JOIN（全外连接）

• 1.5、自连接

• 二、联合查询
• 三、子查询

• 3.1、EXISTS 子查询
• 3.2、集合比较子查询
• 3.3、子查询作为计算字段

一、连接查询

1.1、CROSS JOIN（交叉连接）

交叉连接也叫笛卡尔乘积。wikipedia 对笛卡尔乘积的定义如下:
    在数学中，两个集合 X 和 Y 的笛卡儿积（英语：Cartesian product），又称直积，在集合论中表示为 X x Y ，是所有可能的有序对组成的集合，其中有序对的第一个对象是 X 的成员，第二个对象是 Y 的成员。

SELECT * FROM player;                     # 见图一
 SELECT * FROM team;                       # 见图二
 SELECT * FROM player CROSS JOIN team;     # 见图三

1.2、等值连接

等值连接就是对多张表中相同的字段进行等值判断。

1.2.1、NATURAL JOIN（自然连接）

会自动帮你查询两张连接表中所有相同的字段（比如两张表中相同的数据项) ，然后进行等值连接。

SELECT * FROM player NATURAL JOIN team;

1.2.2、USING 连接

当然我们还可以用 USING 来指定用哪些同名字段进行等值连接。

SELECT * FROM player JOIN team USING(team_id);

1.2.3、ON 连接

ON 表示我们想要连接的条件，我们也可以用 ON 来实现等值连接。

SELECT player_id, player.team_id, player_name, height, team_name 
 FROM player JOIN team ON player.team_id = team.team_id;

1.3、非等值连接

连接两张表的条件如果是相等判断，那就是等值连接，否则就是非等值连接。

-- 我们想查询每个球员的身高级别。
 SELECT player_name,height,height_level
 from player JOIN height_grades
 on height BETWEEN height_lowest AND height_highest;

1.4、外链接

外连接除了查询满足条件的记录以外，还可以查询某一方不满足条件的记录。两张表做外连接，会有一张表是主表，另一张表是从表。

1.4.1、LEFT OUTER JOIN （左外连接）

左外连接，就是左边的表是主表，需要显示左边表的全部行。右边表是从表，只显示满足条件的行。

-- girls 左连接是主表，需要显示左边表的全部行。右边表是从表，只显示满足条件的行。
 select girls.gid, gname, bname
 from girls left join boys
 on girls.bid = boys.bid;

1.4.2、RIGHT OUTER JOIN（右外连接）

右外连接，就是右边的表是主表，需要显示右边表的全部行。左边表是从表，只显示满足条件的行。

-- girls 右连接是主表，需要显示右边表的全部行。左边表是从表，只显示满足条件的行。
 select boys.bid, bname, gname
 from girls right join boys
 on girls.bid = boys.bid;

1.4.2、FULL OUTER JOIN（全外连接）

两张表都是主表，都需要显示全部行。但是MySQL不支持全外连接。

1.5、自连接

对同一张表进行连接查询，这样的连接我们称之为自连接。

-- 查看比布雷克-格里芬高的球员姓名及身高
 SELECT b.player_name, b.height 
 FROM player as a JOIN player as b 
 ON a.player_name = '布雷克-格里芬' and a.height < b.height;

二、联合查询

我们可以用 UNION 关键字，将多个结果集合并成一个结果集，这样的查询我们叫联合查询。例如：要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时。
注意事项： a. 列数一致
                  b. 对应的数据最好一致
                  c. UNION会去重, UNION ALL不会去重

-- 联合查询 union, union all 
  use friend;
  insert into boys values (5, "ET", null);
  insert into girls values (5, "ET", null);
  
  # 查询男生和女生的基本信息
  select * from boys
  union all select * from girls;     #会将所有查询结果显示出来不会进行去重

三、子查询

子查询其实就是嵌套在查询中的查询。这样做的好处是：我们可以进行更加复杂的查询，更容易理解查询的过程。很多情况下，我们无法直接从数据表中得到我们想要的结果。往往需要先进行一次查询，然后在这次查询的基础上，再次进行查询。

子查询可以分为关联子查询和非关联子查询。
-- 如果子查询只执行一次，然后子查询的结果集会作为外部查询的条件进行判断，那么这样的子查询叫做非关联子查询。
# 比如：我们想要查询哪个球员的身高最高，最高身高是多少？
# 1) 查询最高身高是多少？
  select max(height) from player;
# 2) 查询身高等于最高身高的球员信息。
  select * 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 a.team_id = b.team_id);

3.1、EXISTS 子查询

关联子查询可能会搭配 EXISTS 关键字一起使用。EXISTS 用来判断子查询的结果集是否为空集。
    如果不为空集返回 True ，如果为空集返回 False 。

-- 练习1：查询出过场的球员都有哪些，并显示他们的球员ID，球员姓名，球队ID。
 select player_id, player_name, team_id
 from player
 where exists (
   select player_id from player_score where player_score.player_id = player.player_id
 );
 
 -- 练习2：：查询没出过场的球员都有哪些，并显示他们的球员ID，球员姓名，球队ID。
 select player_id, player_name, team_id
 from player
 where not exists (
  select player_id from player_score where player_score.player_id = player.player_id
 );

3.2、集合比较子查询

关键字	含义
IN	判断是否在子查询的结果集中
SOME (ANY)	需要与比较操作符一起使用，与子查询结果集中的某个值进行比较
ALL	需要与比较操作符一起使用，与子查询结果集中的所有值进行比较

SOME 和 ALL都需要和比较操作符一起使用，这些比较操作符包括： > , = , < , >= , <= , 和 <> 。

in -- ：查询出过场的球员都有哪些，并显示他们的球员ID，球员姓名，球队ID
 select player_id, player_name, team_id
 from player
 where player_id in (select distinct player_id from player_score);



 some -- 比印第安纳步行者 (team_id=1002) 中所有球员身高都高的球员有哪些，显示它们的球员ID，球员姓名和球员身高（相当于大于最小值）。
 select player_id, player_name, height
 from player
 where height > some(select height from player where team_id = 1002);

 select * from player 
 where height > some(select height from player where team_id = 1002) and team_id <> 1002;

 select * from player 
 where height > (select min(height) from player as a where a.team_id = 1002)and team_id <> 1002;



all-- 想查询比印第安纳步行者 (team_id=1002) 中所有球员身高都高的球员有哪些，显示他们的球员ID，球员姓名和球员身高（大于最大值）。
 select player_id, player_name, height
 from player
 where height > all(select height from player where team_id = 1002);

 select * from player
 where height > all(select height from player where team_id = 1002);

 select * from player
 where height > (select max(height) from player where team_id = 1002);

最后，再强调下 SOME 和 ALL 必须要与一个比较操作符一起使用，不然起不到集合比较的作用。

3.3、子查询作为计算字段

子查询可以作为计算字段存在。我们通常会给这个计算字段起个别名，因为子查询实在
太长，别名更容易理解。

 -- 查询每个球队的名称，和它们的球员数。
 select team_name, 
 (select count(*) from player where player.team_id = team.team_id) AS player_num 
 from team;