Mysql之多表查询&子查询

本章内容！

一：合并结果集：要求结果集（就是查出来的表）的列数和列类型一致就可以了
注：不是表结构
UNION: 去除重复行
UNION ALL: 不去除重复行！

create table ab(
	a int,
  b varchar(20)
);

insert into ab values(1,'1');
insert into ab values(2,'2');

select * from ab;

create table cd(
	c int,
  d varchar(20)
);

insert into cd values(2,'2');
insert into cd values(3,'3');

select * from cd;

 1.1：使用union all 来合并结果集(不会消除重复的记录！)

select * from ab
union all
select * from cd;

 

 1.2：使用union 来合并结果集(会消除重复的记录！) 

select * from ab
union
select * from cd;

 

二：连接查询
内连接
   方言：表1, 表2 ....where
   标准：表1 inner join 表2 .... on [强烈推荐，如果突然换oracel了怎么办，那么标准还是适用]
自然连接（简化的一种方式）
外连接（左外连接，右外连接，全外连接：Mysql虽然不支持，但可以模拟这种效果）


在说这些连接之前先看一下什么是笛卡尔积！！
假如：x表：2行记录, y表: 3行记录
表示：{a,b},{1,2,3}
那么笛卡尔积就是 2*3 = 6条记录 ---> {a1,a2,a3,b1,b2,b3}，里面其实有很多垃圾数据，这个时候
就需要去除笛卡尔积，一般使用从表的外键 = 主表的主键 作为条件！，从表也就是（多方），主表也就是（一方）
注：多表查询之前要去除笛卡尔积

2.1：内连接：
需求：打印所有员工的姓名，工资，以及员工部门！ 使用方言和标准完成！
方言

-- 需求：打印所有员工的姓名，工资，以及员工部门！
select e.ename, e.sal, d.dname from emp e, dept d
where e.deptno = d.deptno;

我书写的习惯：
select 列
from 从表,主表 
where  从表的外键 = 主表的主键; 

注：并不是一定要有外键引用主键，才能去除笛卡尔积！！！ 

标准：

-- 标准的内连接(强烈建议使用这种！)
select * from emp e inner join dept d on e.deptno = d.deptno;

 

2.2：自然连接：如果多表查询的话，它会找这两张表中相同的字段，自动进行匹配），也可以去除笛卡尔积【不推荐】

select * from emp e natural join dept d;

 2.3：外连接：外连接有一主一次，左外连接则左边是主表,那么主表中所有的记录无论满不满足条件,都会打印出来！，当右表不满足                                条件会用NULL来补位！可以用IFNULL() 
         注：这里的主表跟之前说的一方和多方不一样！！！，更像是优先级一样的说法！

 需求：查询所有员工的姓名,工资,员工部门！
 有这么一种情况，假如emp表有一个员工"张三"的deptno没有与dept表的deptno相关联，那么使用内连接查询后的
 结果集就没有'张三'这个员工！，但是需求是查询所有员工哦！这时就可以使用外连接了
 左外连接：

select e.ename,e.sal,IFNULL(d.dname,'无对应部门')
from emp e left outer join dept d
on e.deptno = d.deptno;

效果图：

 右外连接：和左外连接差不多，换个关键字就行
 把右边的表所有的记录全部显示出来，不管满不满足条件，如果左表不满足条件用NULL补位

select e.ename,e.sal,IFNULL(d.dname,'无对应部门')
from emp e right outer join dept d
on e.deptno = d.deptno;

全外连接：就是左表不满足条件的，你出现右表NULL补位，右表不满足条件的 ，你出现左边NULL补位！
注意：Mysql 不支持全外连接 (full outer join) , 但是可以模拟！

-- 用union来模拟全连接！
select e.ename,e.sal,d.dname
from emp e left outer join dept d
on e.deptno = d.deptno;
union
select e.ename,e.sal,d.dname
from emp e right outer join dept d
on e.deptno = d.deptno;

三：子查询：查询中有查询（查看select关键字的个数！）
子查询出现在两个位置：
from后(作为表格),
where后(作为条件)
条件分为：一旦子查询的结果集是多行多列，一般是用在from 后面作为表格（二次查询），其他的一般就是放在where后面作为条件！

(***)单行单列：SELECT * FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件)
(**)多行单列：SELECT * FROM 表1 别名1 WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件)
(*)单行多列：SELECT * FROM 表1 别名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 别名2 WHERE 条件)
(***)多行多列：SELECT * FROM 表1 别名1 , (SELECT ....) 别名2 WHERE 条件

3.1：子查询的结果集之单行单列
需求：查询本公司工资最高的员工信息！
错误示范：

-- 错误：就是你没有使用group by empno分组,所以查询列的时候不行， In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'exam.emp.empno'; this is incompatible with sql_mode=only_full_group_by
select empno,max(sal) from emp; 

-- Invalid use of group function
select * from emp where sal = MAX(sal); 

select * from emp where sal = MAX(sal);  其实这样思想是正确的，但是由于聚合函数不能放着
select * from emp where sal  = 最高工资
那么我最高工资可以查出来吧  select max(sal) from emp;
那么最终，将这条查询语句，替换最高工资的位置就行！！

 -- 再将子查询的结果集作为条件！
select * from emp where sal = (select max(sal) from emp);

 3.2：子查询的结果集是单行单列：select max(sal) from emp

子查询的结果集之多行单列
需求：打印高于30部门最高工资的所有人
注：大于30部门所有人的工资，换总说法不就是大于30部门的最高工资吗

select * from emp where sal >  (select sal from emp where deptno=30) -- Subquery returns more than 1 row
select * from emp where sal > ALL (select sal from emp where deptno=30) -- 子查询的结果集是多行单列,

//也可以这样实现，主要是演示子查询的结果集是多行单列
select * from emp where sal >  (select max(sal) from emp where deptno=30)



-- 工资大于任意一个经理的员工的信息！(其实也可以理解为大于工资最低的那个经理)
select * from emp where sal > ANY (select sal from emp where job='经理') -- 子查询的结果集是多行单列 -- "集合"
select * from emp where sal > (select min(sal) from emp where job='经理')

 

3.3：子查询的结果集之单行多列
需求：查询工作,工资,部门编号 和‘殷天正’ 信息完全一样的员工！

-- 查询工作,工资,部门编号 和‘殷天正’ 信息完全一样的员工！
select e.ename,e.sal,d.dname
from emp e inner join dept d
on e.deptno = d.deptno where sal = (select sal from emp where ename='殷天正') -- 12500
and d.dname = (select dname from dept where dname = '销售部') -- 是要在你不知道它是销售部的前提下！

//正确示范
select * from emp where(job,sal,deptno) in (select job,sal,deptno from emp where ename = '殷天正') -- 子查询的结果集是单行多列：可以理解一个对象, 对象和对象之间的比较

 

 3.4：子查询的结果集之多行多列

-- 子查询后面记得带个别名！这里是e
select e.ename, e.sal
from (select * from emp where deptno=30) e

子查询：select * from emp where deptno=30，查出来是一张表，然后把它当成一个表二次查询！！

 注：子查询的结果集是多行多列的话，一般放在from重新作为一张表查询！

四：子查询结果集的形式：
单行单列 （基本数据类型的变量）
单行多列 （对象）
多行单列（数组）
多行多列（对象集合）
4.1：单行单列 ：像一个基本数据类型的变量！

select max(sal) from emp

 

4.2：单行多列：像一个对象，empno,ename都是相当于类的属性吗!!

select * from emp where ename = '关羽'

4.3：多行单列：像一个数组，String ename  = {"甘宁","黛绮丝"....}

select ename from emp

4.4：多行多列：像一个集合，一条记录就是一个对象吗

select  * from emp

最后想获取精美脑图和sql数据的，自己去我的网盘下载：
链接：https://pan.baidu.com/s/1KLBqq_u9wEYCaFUbe9JKbg 
提取码：2s33

来自：虽然帅,但是菜的cxy