多表查询

1.笛卡尔积查询

select *from 表1,表n
查询结果是
将坐标中的每条记录与右表中的每条记录都关联一遍
因为他不知道什么样的对应关系是正确只能帮你都对一遍
a表有m条记录 b表有n条记录
笛卡尔积结果为m * n 记录

create table emp1 (id int,name
char(10),sex char,dept_id int);
insert emp1 values(1,"大黄","m",1);
insert emp1 values(2,"老王","m",2);
insert emp1 values(3,"老李","w",30);

create table dept (id int,name char(10));
insert dept values(1,"市场");
insert dept values(2,"财务");
insert dept values(3,"行政");

笛卡尔积的形式

select * from emp1,dept;

+----+------+-----+---------+----+------+
| id | name | sex | dept_id | id | name |
+----+------+-----+---------+----+------+
| 1 | 大黄 | m | 1 | 1 | 市场 |
| 2 | 老王 | m | 2 | 1 | 市场 |
| 3 | 老李 | w | 30 | 1 | 市场 |
| 1 | 大黄 | m | 1 | 2 | 财务 |
| 2 | 老王 | m | 2 | 2 | 财务 |
| 3 | 老李 | w | 30 | 2 | 财务 |
| 1 | 大黄 | m | 1 | 3 | 行政 |
| 2 | 老王 | m | 2 | 3 | 行政 |
| 3 | 老李 | w | 30 | 3 | 行政 |
+----+------+-----+---------+----+------+

需要自己筛选出正确的关联关系

 select * from emp1,dept where emp1.id = dept.id;

+----+------+-----+---------+----+------+
| id | name | sex | dept_id | id | name |
+----+------+-----+---------+----+------+
| 1 | 大黄 | m | 1 | 1 | 市场 |
| 2 | 老王 | m | 2 | 2 | 财务 |
| 3 | 老李 | w | 30 | 3 | 行政 |
+----+------+-----+---------+----+------+

使用内连接来确定笛卡尔积

select * from emp1 inner join dept on emp1.id = dept.id;

内连接的基本用法：

　　inner join

左链接

　　left join

select * from emp1 left join dept on emp1.id = dept.id;

右连接

　　right join

select * from emp1 right join dept on emp1.id = dept.id;

全连接

select * from emp1 left join dept on emp1.id = dept.id
union
select * from emp1 right join dept on emp1.id = dept.id;

子查询

什么是子查询?

当一个查询是另一个查询的条件时，这个查询称之为子查询（内层查询）

什么时候用？

当查询需求比较复杂，一次性查询无法得到结果，需要多次查询时，

例如：给出一个部门名称，需要获得该部门所有的员工信息。

　　需要先确定部门的id，然后才能通过id确定员工解决问题的方式是把一个复杂的问题拆分为若干个简单的问题。

如何用？

　　首先明确子查询就是一个普通的查询，当一个查询需要作为子查询使用时，用括号包裹即可。

数据准备

create table emp2 (id int,name char(10),sex char,age int,dept_id int,job
char(10),salary double);
insert into emp2 values
(1,"刘备","男",26,1,"总监",5800),
(2,"张飞","男",24,1,"员工",3000),
(3,"关羽","男",30,1,"员工",4000),
(4,"孙权","男",25,2,"总监",6000),
(5,"周瑜","男",22,2,"员工",5000),
(6,"小乔","女",31,2,"员工",4000),
(7,"曹操","男",19,3,"总监",10000),
(8,"司马懿","男",24,3,"员工",6000);

create table dept(id int primary key,name char(10));
insert into dept values(1,"市场"),(2,"行政"),(3,"财务");

查询平均年年龄大于25的部门名称

select dept_id from emp2 group by dept_id having avg(age)>25

 select name from dept2 where id in (select dept_id from emp2 group by dept_id having avg(age)>25);

+------+
| name |
+------+
| 市场 |
| 行政 |
+------+

多表查询方式:
先把数据拼接到一起在加以筛选

 select dept2.name from emp2 inner join dept2 on emp2.dept_id = dept2.id group by dept2.name having avg(age)>25;

查询每个部门工资最高的员工信息

select id from emp2 group by dept_id having max(salary);

mysql> select * from emp2 inner join (select dept_id,max(salary) as m from emp2 group by dept_id having max(salary)) as t2
    -> on emp2.dept_id = t2.dept_id
    -> where emp2.salary = t2.m;

+----+------+-----+-----+---------+------+--------+---------+-------+
| id | name | sex | age | dept_id | job | salary | dept_id | m |
+----+------+-----+-----+---------+------+--------+---------+-------+
| 1 | 刘备 | 男 | 26 | 1 | 总监 | 5800 | 1 | 5800 |
| 4 | 孙权 | 男 | 25 | 2 | 总监 | 6000 | 2 | 6000 |
| 7 | 曹操 | 男 | 19 | 3 | 总监 | 10000 | 3 | 10000 |
+----+------+-----+-----+---------+------+--------+---------+-------+