mysql 复杂查询有哪些详细版 数据库查询复杂度

前面我们讲的都是简单的查询，也就是只包含一条 SELECT 语句，并且只查询一张表的数据。SQL 允许我们进行更复杂的查询，也就是我们今天要讲的复杂查询。它包括：连接查询，联合查询和子查询。

一、连接查询

在设计表的时候，为了避免数据的冗余，我们往往会将数据分散到多个表中。因此，在我们查询数据的时候，需要连接多个表进行查询。
SQL92 和 SQL99 连表查询的语法有很大的不同。建议大家采用 SQL99 标准，因为它的层次性更强，可读性也更高。我们也以 SQL99 标准进行讲解。
比较常见的连接查询有：交叉连接，等值连接，非等值连接，外连接和自连接。接下来，我们就分别讲解下这 5 种连接方式。

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

# X = {1, 2}
# Y = {a, b, c}
# X x Y = {(1, a),(1, b),(1, c),(2, a),(2, b),(2, c)}

在 SQL99 中，我们可以通过 CROSS JOIN 获取多张表的笛卡尔乘积。

select * from player cross join team;

2.等值连接
等值连接就是对多张表中相同的字段进行等值判断。在 SQL99 中可以有多种方式表示等值连接。
自然连接
NATURAL JOIN 会自动帮你查询两张连接表中所有相同的字段，然后进行等值连接。

select * from player natural join team;

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

select * from player join team using(team_id);

ON 连接
ON 表示我们想要连接的条件，我们也可以用 ON 来实现等值连接。（可以是非同名字段进行等值连接）

select * from player join team on player.team_id = team.team_id;

3.非等值连接
连接两张表的条件如果是相等判断，那就是等值连接，否则就是非等值连接。
比如说：我们想查询每个球员的身高级别。

select player_id, player_name, height, height_level
from player join height_grades on height between height_lowest and height_highest;

4.外连接
外连接除了查询满足条件的记录以外，还可以查询某一方不满足条件的记录。两张表做外连接，会有一张表是主表，另一张表是从表。
左外连接
左外连接，就是左边的表是主表，需要显示左边表的全部行。右边表是从表，只显示满足条件的行。关键字为 LEFT OUTER JOIN 。

create table t_boys(
	bid int primary key,
    name varchar(255) not null,
    gid int
);
insert into t_boys values (1, 'Aden', 1);
insert into t_boys values (2, 'Bob', 2);
insert into t_boys values (3, 'Clark', 100);
insert into t_boys values (4, 'David', null);

create table t_girls(
	gid int primary key,
    name varchar(255) not null,
    bid int
);
insert into t_girls values (1, 'Allen', 1);
insert into t_girls values (2, 'Beyonce', 2);
insert into t_girls values (3, 'Cindy', 100);
insert into t_girls values (4, 'Diana', null);

# 左外连接 left [outer] join
# 查询所有女生的恋爱情况 
# select * from t_girls join t_boys using(bid);
select * from t_girls left join t_boys using(bid);

右外连接
右外连接，就是右边的表是主表，需要显示右边表的全部行。左边表是从表，只显示满足条件的行。关键字为 RIGHT OUTER JOIN 。

# 右外连接 right [outer] join
# 查询所有男生的恋爱情况 
select * from t_girls right join t_boys using(gid);

全外连接
两张表都是主表，都需要显示全部行。但是MySQL不支持全外连接。关键字为 FULL OUTER JOIN 。

# 全外连接 full [outer] join 
# 查询所有男生和女生的恋爱情况 
select * from t_girls full join t_boys using(bid);
# 注意事项：MySQL不支持全外连接

5.自连接
我们可以连接不同的表，也可以对同一张表进行连接，这样的连接我们称之为自连接。
比如我们想要查看比布雷克-格里芬高的球员都有谁？

select t1.player_name, t1.height from player as t1 join player as t2
on t2.player_name = '布雷克-格里芬' and t1.height > t2.height;

二、联合查询

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

# 2. 联合查询 
# 练习：查询所有男生和女生的信息 
insert into t_girls values (5, 'Eye', null);
insert into t_boys values (5, 'Eye', null);
select * from t_girls;
select * from t_boys;

select * from t_girls # 主查询
union 
select * from t_boys; # 子查询

select * from t_girls # 主查询
union all
select * from t_boys; # 子查询
# 注意事项：union会去重,union不会去重

三、子查询

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

子查询可以分为关联子查询和非关联子查询。

如果子查询只执行一次，然后子查询的结果集会作为外部查询的条件进行判断，那么这样的子查询叫做
非关联子查询。
比如：我们想要查询哪个球员的身高最高，最高身高是多少？

select player_name,height from player 
where height = (
	select max(height) from player # 该子查询称之为标量子查询或者非关联子查询
);

如果子查询依赖于外部查询，通常情况下是因为子查询用到了外部查询的某些字段。因此，每执行一次
外部查询，子查询都要重新执行一次，这样的子查询叫做关联子查询。
比如：我们想要查询每个球队中
大于平均身高的球员有哪些，并显示球员姓名，身高以及所在球队 ID。

select player_name, height, team_id from player as t1
where height > (
	select avg(height) from player as t2 
    where t2.team_id = t1.team_id # 关联子查询
);

1.EXISTS 子查询
关联子查询可能会搭配 EXISTS 关键字一起使用。 EXISTS 用来判断子查询的结果集是否为空集。如果
不为空集返回 True ，如果为空集返回 False 。
举个列子：查询出过场的球员都有哪些，并显示他们的球员ID，球员姓名，球队ID。

select player_id, player_name, team_id from player
where exists(
	select player_id from player_score where player_id = player.player_id
);

那么， NOT EXISTS 自然就是不存在的意思。
比如：查询没出过场的球员都有哪些，并显示他们的球员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
);

2.集合比较子查询

集合比较子查询的作用是与外部查询的结果集进行比较。主要有以下几个关键字：IN, SOME (ANY),ALL。他们的含义如下：

还是上面那个例子：查询出过场的球员都有哪些，并显示他们的球员ID，球员姓名，球队ID。我们可以采用 IN 来进行操作。

select player_id, player_name,team_id from player
where player_id in(
	select distinct player_id from player_score
);

了解了 IN 关键字后，我们接下来看下 SOME 和 ALL 。它们都需要和比较操作符一起使用，这些比较操作符包括： > , = , < , >= , <= , 和 <> 。
举个例子：我们要查询其他球队中比印第安纳步行者 (team_id=1002) 中某个球员身高高的球员有哪些，显示它们的球员ID，球员姓名和球员身高。

select player_id, player_name, height from player
where team_id != 1002 and height > some(
	select height from player where team_id = 1002
);
# 等价于
select player_id, player_name, height from player
where team_id != 1002 and height > (
	select min(height) from player where team_id = 1002
);

同样，如果我们想查询比印第安纳步行者 (team_id=1002) 中所有球员身高都高的球员有哪些，显示它们的球员ID，球员姓名和球员身高。

select player_id, player_name, height from player
where team_id != 1002 and height > ALL(
	select height from player where team_id = 1002
);
# 等价于
select player_id, player_name, height from player
where team_id != 1002 and height > (
	select max(height) from player where team_id = 1002
);

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

3.子查询作为计算字段
子查询甚至可以作为计算字段存在。举个例子：查询每个球队的名称，和它们的球员数。

# 方式一
select *, (select count(*) from player where player.team_id = team.team_id ) as teanm_nums from team;
# 方式二
select team_name,team_nums 
from team left join (
	select team_id,count(*) as team_nums from player group by team_id
) as t using(team_id);

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