07:MYSQL----多表查询

目录

1:多表查询概述

2:多表查询分类

 3:内连接

3:外连接

4:自连接  

 5:联合查询-union，union all

6:子查询

---

1:多表查询概述

select * from emp , dept;

emp:表中有6条数据,   dept表中有5条数据只查询出来的数据为:30条

概述:指从多张表中查询数据

笛卡尔积: 笛卡尔乘积是指在数学中，两个集合A集合和 B集合的所有组合情况。(在多表查询时，需要消除无效的笛0卡尔积)

--加上where条件消除笛卡尔积

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

2:多表查询分类

内连接:相当于查询A、B交集部分数据

外连接:   

                左外连接:查询左表所有数据，以及两张表交集部分数据

                右外连接:查询右表所有数据，以及两张表交集部分数据

自连接:当前表与自身的连接查询，自连接必须使用表别名

 3:内连接

语法   [  ]是可选向,所有可以省略 :

--隐式内连接
select 字段列表 from 表1,表2  where 条件....;

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

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

-- 内连接演示
-- 1. 查询每一个员工的姓名 ， 及关联的部门的名称 隐式内连接实现
-- 表结构: emp ，dept
-- 连接条件: emp.dept_id = dept.id
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 ;
-- 在起的别名后,只可以使用别名否则会报错
-- 显示内连接 
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;

3:外连接

左外连接:

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

相当于查询表1(左表)的所有数据 包含 表1和表2交集部分的数据

右外连接:

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

相当于查询表2(右表)的所有数据 包含 表1和表2交集部分的数据

-- 左连接
select e.name, d.name from emp e left outer join dept d on e.dept_id = d.id;
select e.*, d.name from emp e left join dept d on e.dept_id = d.id; -- outer 是可选项
-- 右连接
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;
-- 一般情况下左连接使用的较多,右连接可以转换为左连接
select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;

4:自连接  

语法:

对一张表进行查询(一张表自己连接自己)

select 字段列表 from 表A 别名A join 表B 别名B on 条件....;

自连接查询，可以是内连接查询，也可以是外连接查询。

-- 自连接
-- 1. 查询员工 及其 所属领导的名字
-- 表结构: emp
select a.name , b.name from emp a , emp b where a.managerid = b.id;
-- 2. 查询所有员工 emp 及其领导的名字 emp ， 如果员工没有领导， 也需要查询出来
-- 表结构: emp a，emp b
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

 自连接时要把一张表看为两张表

 5:联合查询-union，union all

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

select 字段列表 from 表A...
union [all]
select 字段列表 from 表B...

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重

实际上就是把2次的查询结果放在一起打印出来

select * from emp where salary < 50000
union all
select * from emp  where age > 50;


select * from emp where salary < 50000
union 
select * from emp  where age > 50;

6:子查询

概念:SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

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

子查询外部语句可以是: insert    update    delete   select   的任何一个

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

A:标量子查询（子查询结果为单个值)

B:列子查询(子查询结果为一列)

C:行子查询(子查询结果为一行,可以是多列)

D:表子查询(子查询结果为多行多列)

根据子查询位置，分为where之后、from之后、select之后。

A:标量子查询（子查询结果为单个值)

-- 2．查询在“小昭”入职之后的员工信息
-- a．查询小昭的入职日期
select entrydate from emp where name ="小昭";
-- b 查询所有员工的的入职日期
select * from emp where entrydate > "2004-10-12";
-- 完整的sql
select * from emp where entrydate >(select entrydate from emp where name ="小昭");

-- -- 1．查询“研发部"的所有员工信息
-- a:研发部的部门id
select id from dept where name="研发部";
-- b:查询所有员工的信息
select * from emp where dept_id="1";
-- 完整sql
select * from emp where dept_id=(select id from dept where name="研发部");

B:列子查询(子查询结果为一列)

子查询返回的结果为一列(可以是多行),这种子查询称为列子查询

常使用的符号:in,    not in   ,any ,some ,allch

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

--列子查询
-- 1.查询“销售部"和“研发部""的所有员工信息
-- a．查询“销售部"和1“研发部"的部门ID
select id from dept where name='销售部' or name= '研发部' ;
-- b.根据部门ID，查询员工信息
select * from emp where dept_id in (select id from dept where name='销售部' or name= '研发部' );


-- 2．查询比研发部所有人工资都高的员工信息
-- a．查询研发部所有财务部人员工资
select salary from emp where dept_id =(select id from dept where name='研发部');

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

-- 3. 查询比研发部其中任意一人工资高的员工信息
select salary from emp where dept_id =(select id from dept where name='研发部');
select * from emp where salary > any (select salary from emp where dept_id =(select id from dept where name='研发部'));

C:行子查询(子查询结果为一行,可以是多列)

行子查询
 子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询常用的操作符:  = 、<>、IN 、   NOT IN

-- 行子查询
-- 1. 查询与“张无忌”的薪资及直属领导相同的员工信息 ;
select salary,managerid from emp where name='张无忌';

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

D:表子查询(子查询结果为多行多列)

子查询返回的结果是多行多列，这种子查询称为表子查询C常用的操作符:IN  -----在指定的集合范围之内,多选一

-- 1。 查询与“韦一笑”，“常巡春”的职位和薪资的员工信息
-- a:首先查询韦一笑”，“常巡春”的职位和薪资
select job,salary from emp where name='韦一笑' or name='常巡春';

select * from emp where (job,salary) in (select job,salary from emp where name='韦一笑' or name='常巡春');

-- 2. 查询入职日期是 “2001-01-01” 之后的员工信息 ， 及其部门信息
select * from emp where entrydate >'2001-01-01';

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