MySQL学习笔记(3)

 

多表查询

 

内连接

 

内连接查询语法：

1.隐式内连接

select 字段列表 from 表1,表2 where 条件……；

2.显式内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件;

内连接查询的是两张表交集的部分

 

例：

这里emp为员工表名，dept为部门表名

1.查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）

select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;

 

可以起别名简化代码：

select e.name, d.name form emp e,dept d where e.dept_id = d.id;

2.查询每一个员工的姓名，及关联的部门的名称（显式内连接实现）

select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;

 

外连接

 

外连接查询语法：

左外连接：（相当于查询表1的所有数据 包含 表1 和 表2 交集部分的数据）

select 字段列表 from 表1 left [outer] join 表2 on 条件…;

右外连接：（相当于查询表2的所有数据 包含 表1 和 表2 交集部分的数据）

select 字段列表 from 表1 right [outer] join 表2 on 条件…;

 

例：

1.查询emp表的所有数据，和对应的部门信息（左外连接）

select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;

2.查询dept表的所有数据，和对应的员工信息（右外连接）

select d.*,e.* from dept d right outer join emp e on e.dept_id  = d.id;

 

自连接

原理：给表起别名后自己与自己连接,自连接可以是内连接或外连接。

 

例：查询员工 及其 所属领导的名字

select a.name,b.name from emp a,emp b where a.managerid = b.id;

 

子查询（嵌套查询）

 

根据子查询结果不同可以分为：

• 标量子查询（子查询结果为单个值）
• 列子查询（子查询结果为一列）
• 行子查询（子查询结果为一行）
• 表子查询（子查询结果为多行多列）

 

标量子查询

例:

1.查询"销售部"的所有员工信息

可分为两个步骤

a.查询"销售部"部门id

select id from dept where name = '销售部';    (返回结果为4)

 

b.根据销售部部门id，查询员工信息

select * from emp where dept_id = 4;

通过子查询可合并成：

select * from emp where dept_id = (select id from dept where name = '销售部');

2.查询在"方东白"入职之后的员工信息

select * from emp where entrydate > (select entrydate from emp where name = '方东白')；

 

列子查询

例：

1.查询"销售部"和"市场部"的所有员工信息

select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部')；

2.查询比财务部所有人工资都高的员工信息

select * from emp where salary > all( select salary from emp where dept_id = (select id from dept where name = '财务部' ) )；

这里的all表示必须大于所有人

3.查询比研发部其中任意一人工资高的员工信息

select * from emp where salary > any( select salary from emp where dept_id = (select id from dept where name = '研发部' ) )；

这里的any表示大于其中任意一个即可

 

行子查询

例：

1.查询与"张无忌"的薪资及直属领导相同的员工信息

select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张无忌');

 

表子查询

例：

1.查询与"鹿杖客","宋远桥"的职位和薪资相同的员工信息

select * from emp where (job,salary) in (select job,salary from emp where name = "鹿杖客" or name = "宋远桥")；

2.查询入职日期是"2006-01-01"之后的员工信息，及其部门信息

select e.*,d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d dept_id = d.id;