《JAVA从入门到？？》MySQL篇DAY03

– MySQL高级查询 – 多表
– 需求：查询雇员名称、雇员工作、雇员工资、雇员所在的部门。
select ename,job,sal,dname from emp; --错的
select ename,job,sal,dname from emp,dept; – 笛卡尔积 14 * 4
– 什么是子查询？
– 嵌套在主查询中的查询——子查询。
– 也就是说子查询是嵌套在查询语句里面的查询语句，就像语句块里嵌套语句块类似。
select ename,job,sal,(select dname from dept) from emp; – 错得 Subquery returns more than 1 row 不满足单行
select ename,job,sal,(select d.dname,d.loc from dept as d where d.deptno = e.deptno) as dname from emp as e; --Operand should contain 1 column(s) 不满足单列

 -- 在哪里嵌套子查询
   -- 在完整的SQL结构中能嵌套子查询的位置分别是select、from、where、group by 、having、order by
   --在select子句中嵌套子查询，注意：嵌套在select语句中sql语句要求查询的值只能是单行和单列。
   select ename,job,sal,(select d.dname from dept as d where d.deptno = e.deptno) as dname from emp as e;
   -- 在from中嵌套，注意：from里的子查询可以是任意查询语句，然后将其结果作为外部查询的表
   -- 需求：查询雇员信息表中第1页(10条)雇员薪资大于1500的雇员信息。
   select * from (select * from emp limit 0,10) as new_emp where new_emp.sal > 1500;
   -- 需求：查询雇员薪资大于1500的雇员信息显示第一页
   select * from emp where sal > 1500 limit 0,10;
   -- 在WHERE中嵌套
   -- SELECT 列1,列2 FROM 表 WHERE 列=(子查询)
   -- 查询部门名称accounting的雇员信息
    select * from emp where deptno = (select deptno from dept where dname = 'accounting');

– 在WHERE中嵌套的子查询根据不同的运算符有不同的分类：
– 比较运算符（>、<、=、>=、<=、!=）
– 需求：查询部门编号大于部门为research的部门编号的所有雇员信息。 – 查询research的部门编号，查询雇员信息表中部门编号大于它的编号的所有雇员信息。
–1显示雇员信息
–2查询部门名称为research的部门编号。
–雇员信息表中编号大于2的所有雇员信息
select * from emp where deptno != (select deptno from dept where dname = ‘research’);
– 注意：使用嵌套在where后的子查询，如果用了如上的比较运算符，一定要保证子查询出来的结果是单行单列的值。

— in 和not in运算符
– 预热 select * from emp where empno in(值集合);
– 查询部门名称为’research’和’sales’的部门编号
select deptno from dept where dname = ‘research’ or dname = ‘sales’;
– 查询部门名名称为’research’和’sales’的雇员信息。
select * from emp where deptno in (select deptno from dept where dname = ‘research’ or dname = ‘sales’); //20 30
等价于
select * from emp where deptno = 20;
or
select * from emp where deptno = 30;
– 查询不是’research’和’sales’部门的雇员信息。
select * from emp where deptno not in (select deptno from dept where dname = ‘research’ or dname = ‘sales’);

– 子查询运算符（all、any、 exists）
– 查询没有商品的商品类别
select * from category where cat_id not in (select cat_id from goods); – 可以做出来
select * from category where not exists (select cat_id from goods where goods.cat_id = category.cat_id); – 效率更高
– 查询学生中存在3班的学生
– 查询雇员信息表中存在部门为research和sales的雇员
select * from emp where exists (select deptno from dept where dname = ‘research’ or dname = ‘sales’);
SELECT * FROM emp WHERE EXISTS (SELECT * FROM dept WHERE emp.deptno = dept.deptno AND (dname = ‘research’ OR dname = ‘sales’));

-- 查询工资大于雇员部门编号为30的雇员最高工资的所有雇员
 雇员部门编号30的雇员最高工资  
 工资大于（）所有雇员   -- 雇员大于所有部门编号为30的雇员信息的薪资
-- select * from emp where sal > (select max(sal) from emp where deptno = 30); -- 可以，但是效率不是最高、描述题意思不是很贴切。
 
 select * from emp where sal > all (select sal from emp where deptno = 30); 
 等价于
 select * from emp where sal > all (1600,1250,1250,2850,1500); 
 等价于
 select * from emp where sal > 1600 and sal > 1250 and sal > 1250 and sal > 2850 and sal > 1500
 等价于
 select * from emp where sal > 2850;

-- 查询薪水低于任一一个部门平均薪水的所有雇员
-- select * from emp where sal < (select avg(sal) as sal from emp group by deptno order by sal desc limit 0,1) -- 可以
等价
-- select * from emp where sal < 3000 or sal < 2916 or sal < 1690
等价
select * from emp where sal < any (select avg(sal) from emp group by deptno);

select avg(sal) from emp group by deptno; -- 2916  3000 1690

表连接

– 为什么需要表连接查询
因为：查询雇员的姓名、工作、工资、部门名称
select ename,job,sal,(select dname from dept where dept.deptno = emp.deptno) as dname from emp; – 这种子查询效率低

-- 什么是表关联：如果数据来自多个表，那么可以采用联接查询的方式来实现。

select ename,job,sal,dname from emp,dept; --笛卡尔乘积 : 笛卡尔乘积是指将两张表的所有数据相连，最后联接的结果数为两张表数量的乘积。

-- 简单表联接
语法： select 列1,列2 from 表1 join 表2 on 表1.列=表2.列
-- 因为：查询雇员的姓名、工作、工资、部门名称
select ename,job,sal,dname from emp join dept on emp.deptno = dept.deptno;  -- 可以
-- 普通表联接写法 
select ename,job,sal,dname from emp,dept where emp.deptno = dept.deptno; --可以

– 表联接分类
– 内联接 :
内联接的标准语法是INNER JOIN，INNER可以省略
– 需求：查询雇员的姓名、工作、工资、部门名称
select ename,job,sal,dname from emp as e inner join dept as d on e.deptno = d.deptno;
– 外联接
– 左外联接 用法：left outer join 或 left join 是以左表为基准，先把左边全部内容显示出来。再将右边和左边建立关系，如果右边中没有与左表进行对应，就不显示，左表中没有找到对应的，右表显示null。
– 需求：查询所有雇员信息,包含有部门信息和无部门信息都查询出来。
select * from emp as e left join dept as d on e.deptno = d.deptno;
– 右外联接 用法：right outer join 或 right join
select * from emp as e right join dept as d on e.deptno = d.deptno;
– 全联接 用法：full outer join 或 full join – mysql中不支持全联接，要想用全联接功能，使用union
select * from emp as e full join dept as d on e.deptno = d.deptno; – 错的，不支持。
select * from emp as e left join dept as d on e.deptno = d.deptno
union
select * from emp as e right join dept as d on e.deptno = d.deptno;
– 自联接
需求：查询雇员信息表中每个雇员对应的上级领导(名称)
select e.ename as ename,m.ename as mgr_name from emp as e join emp as m on e.mgr = m.empno;