Mysql数据库基本 *** 作（七）多表查询-子查询，表自身关联查询

Mysql数据库基本 *** 作（七）多表查询-子查询，表自身关联查询

多表查询还有前面的两块内容——内连接查询，外连接查询，希望看到这篇博客的朋友先去看看我的"Mysql数据库"专栏中Mysql数据库基本 *** 作（六）多表查询-内连接，外连接这一章博客，看完再来看这一章的内容，相信即使你已经理解内连接，外连接查询，看博客的时候也会有新的收获。除此之外，接下类要讲的子查询，表自身关联查询所用到的数据也在上一篇博客中。
Mysql数据库基本 *** 作（六）多表查询-内连接，外连接

子查询

前一篇博客中有两张表，部门表dept和员工表emp，如果让我们第一感觉随便想出一个sql语句，我相信大多数人想出的都是select* from emp where dept_id='1001';这样一个简单的sql，其实仔细观察这个sql，我们应该这样理解这句话：select是选择的意思，*代表全部字段的意思，from后面的emp代表从哪张表中查询,emp可以替换成其他的表；where后面的是条件子句。dept_id=‘1001’，其中’1001‘也可以替换成其他值。
而我们又知道，其实我们所有的sql语句的查询结果返回也是一张表，或者具体来说可能是一个列，一个行，甚至是一个值。我们可不可以将这些返回的查询结果放在普通的sql语句中可以替换的地方做一个嵌套查询？答案的确如此。因此接下来我们分别介绍子查询如果分别是一整张表，一个列，一个行，一个值的时候分别怎么做嵌套循环。

1，子查询结果为一整张表或者一个行。

查询研发部23岁以下的员工信息

select *from (select * from dept3 where name = '研发部') t1, (select *from emp3 where age <23) t2 where t1.deptno=t2.dept_id;

解释这条SQL语句，这里先从部门表里查询到了研发部得到了一行记录select * from dept3 where name = '研发部'；

又从员工表emp3中查询到了年龄小于23岁的员工，得到了一张表。

然后将这两个表利用他们之间天然的联系t1.deptno=t2.dept_id内连接起来，就得到了我们想要的结果。

而且仔细思考其实就能发现，t1，t2的笛卡尔集其实就是我们想要的结果，因为第一个子查询只返回了一行记录，不需要通过t1，t2表的内在联系t1.deptno=t2.dept_id过滤。所以sql语句其实可以不加后面的过滤条件：

select *from (select * from dept3 where name = '研发部') t1, (select *from emp3 where age <23) t2 ;

总结：子查询的结果为一个表或者一个行时，子查询结果一般在from关键字后。

2，子查询结果为一个列

查询研发部和销售部的员工信息

select * from emp3 where dept_id in (select deptno from dept3 t where name ='研发部'or name ='销售部');

在该语句中首先进行子查询select deptno from dept3 t where name ='研发部'or name ='销售部'

可以看到查询结果为一列值，可以认为是一个列表，这个结果可以替换in关键字后面的内容帮助我们查询。

总结：子查询结果为一列时，可以替换in关键字后面的内容帮助我们查询。

3，子查询结果为一个值

这个就更简单了，例如：
查询年龄最大的员工信息

select * from emp3 where age = (select max(age) from emp3);

结果为

总结：显然，当子查询结果为一个值时，一般放在where子句后面可以替换字段值

补充:

接下来再介绍几个子查询关键字，丰富我们的查询功能
1，ALL

select ...from ...where c >al1(查询语句)--等价于:
select ...from ... where c >result1 and c >result2 and c >result3;

2，ANY和SOME

select ...from ...where c >any(查询语句)--等价于:
select ...from ... where c >result1 or c >result2 or c >result3;

3，IN

select ...from ...where c in(查询语句)--等价于:
select ...from ... where c = result1 or c = result2 or c = result3;

4，EXISTS

select ...from ...where exists(查询语句)

这个关键字比较难理解，该子查询表示如果“有数据结果”(至少返回一行数据)，则该EXISTS()的结果为“true"，外层查询执行该子查询如果“没有数据结果”（没有任何数据返回)，则该EXISTS()的结果为“false"，外层查询不执行。
EXISTS后面的子查询不返回任何实际数据，只返回真或假，当返回真时where条件成立。注意，EXISTS关键字，比IN关键字的运算效率高，因此，在实际开发中，特别是大数据量时，推荐使用EXISTS关键字
例如：
–查询公司是否有大于40岁的员工，有则输出

select * from emp3 a where exists(select * from emp3 b where a.age > 40);

表自身关联查询

前面介绍的多表查询都是拿部门表和员工表这两个不同的表举例子的，实际上部门表和部门表本身也可以连接，员工表和员工表也可以连接。当然，可以连接是一回事，他们自身连接起来其实没啥意义。正是因为部门表和员工表天然有dept3.deptno=emp3.dept_id这个天然联系我们才认为这两个表有连接起来的必要，而显然，这两个表自身和自身是没什么联系的。这里再举一个表自身有关联起来的必要的例子。

create table t_sanguo (
eid int primary key ,ename varchar (20) ,manager_id int ,
foreign key (manager_id) references t_sanguo (eid)--添加自关联约束
);
--这里表自身的manager_id受限制于表本身的eid
insert into t_sanguo values(1,'刘协',NULL);
insert into t_sanguo values(2,'刘备',1);
insert into t_sanguo values (3,'关羽',2);
insert into t_sanguo values (4, '张飞',2);
insert into t_sanguo values (5,'曹 *** ',1);
insert into t_sanguo values (6,'许褚',5);
insert into t_sanguo values ( 7,'典韦',5);
insert into t_sanguo values (8,'孙权',1);
insert into t_sanguo values (9,'周瑜',8);
insert into t_sanguo values (10,'鲁肃',8);

最终得到一张表t_sanguo，第一列标识人物id，第二列标识人物的名字，第三列标识人物的领导

观察这个表，假设这个表有一个复制表t_sanguo2，我们不难发现——t_sanguo和t_sanguo2之间天然有eid与manager_id的联系，可以使用这个关系将两个表在形成笛卡尔积的基础上内连接过滤出我们想要的信息。

select t1.eid,t1.ename,t2.ename manager_name from t_sanguo t1,t_sanguo t2 where t1.manager_id=t2.eid;

最终得到了一张根据他们内在联系连接的一张连接表

接下来无论我们想要基于这张表进行其他的任何查询都很方便了。