mysql学习——多表查询

学习黑马MySQL课程，记录笔记，用于复习。

添加外键

alter table emp add constraint fk_emp_dept_id foreign key 
(dept_id) references dept(id);

多表查询

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

笛卡尔积

select * from emp，dept;

内连接

1）隐式内连接

select 字段列表 from 表1 , 表2 where 条件 ... ;
select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;
#起别名 简化
select e.name,d.name from emp e , dept d where e.dept_id = d.id;

2）显式内连接

select 字段列表 from 表1  [ inner ] join 表2 on 条件 ... ;
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;

注意：
一旦为表起了别名，就不能再使用表名来指定对应的字段了
隐式：先做笛卡尔积，然后过滤；
显式：先匹配链接条件，再返回。性能和时间复杂度都是显式要好

外连接

1）左外连接——相当于左表的所有数据

select 字段列表 from 表1 left [ outer ] join 表2 on 条件 ... ;
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
# outer可以省略
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

2）右外连接——相当于右表的所有数据

select 字段列表 from 表1 right [ outer ] join 表2 on 条件 ... ;
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

自连接

自连接查询

自连接查询，就是把一张表连接查询多次。

select 字段列表 from 表A 别名A join 表A 别名B on 条件 ... ;
# 查询员工 及其 所属领导的名字
select a.name , b.name from emp a , emp b where a.managerid = b.id;
#  查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
# 用左外连接
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

联合查询

联合查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

select 字段列表 from 表A ...
union [ all ]
select 字段列表 from 表B ...;
# 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.
select * from emp where salary < 5000
union 
select * from emp where age > 50;

• 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
• union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

子查询

SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

select * from t1 where columnl = ( select column1 from t2 );

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

子查询类型	子查询结果
标量子查询	单个值
列子查询	一列
行子查询	一行
表子查询	多行多列

根据子查询位置不同，分为：

子查询类型
where 之后
from 之后
select 之后
标量子查询

-- 1. 查询 "销售部" 的所有员工信息
-- a. 查询 "销售部" 部门ID
select id from dept where name = '销售部';-- b. 根据销售部部门ID, 查询员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');

列子查询

操作符	描述
in	在指定的集合范围之内，多选一
not in	不在指定的集合范围之内
any	子查询返回列表中，有任意一个满足即可
some	与ANY等同，使用SOME的地方都可以使用ANY
all	子查询返回列表的所有值都必须满足

select id from dept where name in('销售部','市场部');
select * from emp where dept_id in ( select id from dept where name in('销售部','市场部'));

行子查询
常用的操作符：= 、<> 、in 、not in。用法与行子查询类似。
表子查询
常用的操作符：in
查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息
1.入职日期是 “2006-01-01” 之后的员工信息

select * from emp where entrydate > '2006-01-01';

2.查询这部分员工, 对应的部门信息

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