MySQL_Ch_4

@BCKou

MySQL_Ch_4

1.集合运算

1. – union / union all

   – 合并union 去掉重复的记录 并集运算

   –下述的两个查询结果可以作为集合的合并处理

   select ename, sal
   from emp
   where sal > 1000;
   

   select ename, sal
   from emp
   where sal <1500;
   

   ##union
   select ename, sal
   from emp
   where sal > 1000
   union	-- oralce默认按照结果集第一列进行排序,MySQL无排序
   select ename, sal
   from emp
   where sal <1500;
   
   ##union all
   select ename, sal
   from emp
   where sal > 1000
   union all	-- 包含重复的部分, 没有排序的
   select ename, sal
   from emp
   where sal <1500;
   

2. – intersect

   – MySQL取消了交集

   select ename, sal
   from emp
   where sal > 1000
   intersect
   select ename, sal
   from emp
   where sal <1500;
   

   • – 无论是交集还是并集

     • – 1) 查询的类型要一致

     • – 2) 结构要相同

     • – 3) 类型一致,逻辑同样要正确

       select ename,sal
       from emp
       where sal > 1000
       union 
       select ename,comm
       from emp
       where sal < 1500;
       

     – 类型不一致，可以执行，但是显示的数据不对的

     select ename,sal
     from emp
     where sal > 1000
     union 
     select ename,hiredate
     from emp
     where sal < 1500;
     

     – 结构不对，直接报错

     select ename,sal
     from emp
     where sal > 1000
     union 
     select ename,sal,deptno
     from emp
     where sal < 1500;
     

3. – 相关子查询

   – 特点:分组函数有比较的情况,书写时不需要进行group by

   – 查询月薪高于其部门平均月薪的员工信息

   select *
   from employees e
   where e.salary > (select avg(ee.salary) 
                     from employees ee
                     where ee.department_id = e.department_id -- 不是多表的连接,多表的连接在外部连接);
   

   – 外部查询每次查询出一行,子查询执行一次

   – 针对每一次外部查询的记录都会去计算一下其部门的平均工资,再利用自己的工资和平均工资进行比较

   select *
   from employees e, (select ee.department_id,avg(ee.salary) avgsal
                      from employees ee
                      group by ee.department_id) t
   where e.department_id = t.department_id
   and e.salary > t.avgsal;
   

   • 关键字

     • exists 存在

     • not exists 不存在

       • 作用: 验证外部查询的条件是否在主查询中存在

       • 使用前提:相关子查询

       • – 查询所有经理的员工信息(要查的是经理)

         select * from  employees e
         where e.employee_id in(select distinct manager_id 
                                from employees);
         

         select * from employees e
         where exists(select 1	#1是表示占位的,无实际意义
                                		from employees ee
                                     where ee.manager_id= e.employee_id);
         

       • –查询所有不是经理的员工信息

         select * from  employees e
         where e.employee_id not in(select distinct manager_id 
                                    from employees);
         

         逻辑上，这个SQL语句应该会返回记录，但是却一条也没返回，why?

         因为子查询的结果中有一条空值，这条空值导致主查询没有记录返回。这是因为所有的条件和空值比较结果都是空值。因此无论什么时候只要空值有可能成为子查询结果集合中的一部分，就不能使用NOT IN 运算符。

         这时可以使用not exists

         select * from  employees e
         where not exists(select 2 -- 2是表示占位，没有任何意义
                      from employees ee
                      where ee.manager_id = e.employee_id);
         

         – exists / not exists 可以避免null的问题(使用的前提是相关子查询)

         – in / not in 无法避免null

练习

•1.查询部门平均工资在2500元以上的部门名称及平均工资。

select d.dname,avg(e.sal)
from dept d, emp e
where d.deptno = e.deptno
group by d.dname
having avg(e.sal) > 2500;

•2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。

select job,avg(sal)
from emp
where job not like 'SA%'
group by job
having avg(sal) > 2500
order by avg(sal) desc;

•3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。

## 错误写法
## 因为这个写法的第一步就是只出现了三个部门,未显示没有员工的部门.
select d.dname, round(min(sal),0), round(max(sal),0)
from dept d, emp e 
where d.deptno = e.deptno
group by d.dname
having count(e.deptno) > 2;

select d.dname, round(min(sal)), round(max(sal))-- 不写,默认为0
from dept d left join emp e 
on (d.deptno = e.deptno)
group by d.dname
having count(e.deptno) > 2;

•4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。

select job, sum(sal)
from emp
where job <> 'SALESMAN'
group by job
having sum(sal) >= 2500;

•5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。

#### 错误写法 这个写法无法显示King的经理表的null值
####没有经理的KING也要显示
select e2.ename,e2.empno,min(e1.sal)
from emp e1, emp e2 -- e1 员工表 e2 经理表
where e1.mgr = e2.empno
group by e2.ename,e2.empno
having min(e1.sal) >= 3000
order by min(e1.sal) desc;

# 效率最低
select e2.ename as 经理姓名,e2.empno as 经理号码,min(e1.sal)
from emp e1 left join emp e2
on (e1.mgr = e2.empno)
group by e2.ename,e2.empno
having min(e1.sal) >= 3000
order by min(e1.sal) desc;

## 效率较高

## 效率高

## 相关子查询

6.查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。

select empno,ename,sal
from emp
where sal > (select sal 
				from emp
                where empno = 7782)
and job = (select job
				from emp
                where empno = 7369);

7.查询工资最高的员工姓名和工资。

###错误写法
select ename, max(sal)
from emp;

select ename, sal
from emp
where sal = (select ename, max(sal) from emp); 

8.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。

select e.deptno, d.dname, min(sal)
from dept d, emp e
where d.deptno = e.deptno
group by e.deptno, d.dname
having min(sal) > (select min(sal)
						from emp
                        where deptno = 10);

9.查询员工工资为其部门最低工资的员工的编号和姓名及工资。

select e.deptno, e.ename, sal
from emp e
where e.sal = (select min(ee.sal)
				from emp ee
                where ee.deptno = e.deptno);

10.显示经理是KING的员工姓名，工资。

select ename, sal
from emp
where mgr = (select empno
				from emp e
                where e.ename = 'king');

11.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。

select ename, sal, hiredate
from emp
where hiredate > (select hiredate
					from emp
					where ename = 'SMITH');

12.使用子查询的方式查询哪些职员在NEW YORK工作。

select ename
from emp e
where deptno = (select deptno
					from dept
                    where loc = 'NEW YORK')

# 相关子查询 (对表进行了一次的查询,效率相对较高)

13.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。

select ename, hiredate
from emp
where deptno = (select deptno
					from emp
                    where ename = 'SMITH')
and ename <> 'SMITH';

14.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。

select empno, ename, sal
from emp
having sal > (select avg(sal)
				from emp);

\15. 显示部门名称和人数

#错误写法(内连接的弊端)
#无法显示没有人数的部门 故采用外连接
select d.dname, count(*)
from emp e, dept d
where e.deptno = d.deptno
group by d.dname;

select d.dname, count(e.empno)
from emp e right join dept d
on( e.deptno = d.deptno)
group by d.dname;

\16. 显示每个部门的最高工资的员工

select *
from emp e
where e.sal = (select max(ee.sal)
				from emp ee
                where ee.deptno = e.deptno);

\17. 显示出和员工号7369部门相同的员工姓名，工资

select ename, sal
from emp
where deptno = (select deptno
				from emp 
                where empno = '7369');

\18. 显示出和姓名中包含“A”的员工相同部门的员工姓名

# 错误写法
# 如果子查询中的结果不是一个,那么使用in,使用等号会报错:一个对应多个,不匹配错误
# 因此通用in
select ename
from emp
where deptno = (select deptno
				from emp 
                where ename like '%A%');

1. 查询部门平均工资在2500元以上的部门名称及平均工资。

select dname, avg(sal)
from dept d,emp e
where d.deptno = e.deptno
group by dname
having avg(sal) > 2500;

select d.dname, t.avgsal
from dept d,(select deptno,avg(sal) as avgsal
            from emp group by deptno) t
where d.deptno = t.deptno
and t.avgsal > 2500;

– dept emp
– 条件：having avg(sal) > 2500
– 先查出来 avg(sal) as avgsal where t.avgsal > 2500

2. 查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。

select job, avg(sal)
from emp
where job not like 'SA%'
group by job
having avg(sal) > 2500
order by sal desc;

3. 查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。

select dname,round(min(sal)),round(max(sal))
from dept d left join emp e
on(d.deptno = e.deptno)
group by dname
having count(*) > 2;

– round 只写一个要四舍五入的值，默认是以整数位四舍五入

4. 查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。

select job, sum(sal)
from emp
where job <> 'SALESMAN'
group by job
having sum(sal) >= 2500;

5. 显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。

select t.mgr as 经理号码, e2.ename as 经理姓名, t.minsal 
from emp e2 right join (select mgr, min(sal) as minsal
                        from emp
                        group by mgr) t -- e1 员工表  e2 经理表
on(t.mgr = e2.empno)
group by t.mgr
having t.minsal >= 3000
order by t.minsal desc;

select t.mgr as 经理号码, e2.ename as 经理姓名, t.minsal 
from emp e2 right join (select mgr, min(sal) as minsal
                        from emp
                        group by mgr
                        having min(sal) >= 3000) t -- e1 员工表  e2 经理表
on(t.mgr = e2.empno)
order by t.minsal desc;


select e1.mgr as 经理号码, e2.ename as 经理姓名, min(e1.sal)
from emp e1 left join emp e2
on( e1.mgr = e2.empno )
group by e1.mgr,e2.ename
having min(e1.sal) >= 3000
order by min(e1.sal) desc;

– 思考：相关子查询

6. 查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。

select empno, ename, sal
from emp
where sal > (select sal from emp where empno = 7782)
and job = (select job from emp where empno =7369);

7. 查询工资最高的员工姓名和工资。

select ename,sal
from emp
where sal =(select max(sal) from emp);

8. 查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。

select d.deptno, d.dname, t.minsal
from dept d join (select deptno,min(sal) as minsal
                  from emp group by deptno) t
on(t.deptno = d.deptno)
where t.minsal > (select min(sal) from emp where deptno = 10);

9. 查询员工工资为其部门最低工资的员工的编号和姓名及工资。

select empno,ename,sal
from emp e, (select deptno,min(sal) as minsal
             from emp
             group by deptno) t
where e.deptno = t.deptno
and e.sal = t.minsal;             

10. 显示经理是KING的员工姓名，工资。

select e.ename, e.sal -- , t.ename as 经理姓名
from emp e,(select empno -- , ename
		    from emp
            where ename = 'KING') t
where (e.mgr = t.empno);


select e.ename, e.sal -- , t.ename as 经理姓名
from emp e
where e.mgr = (select empno 
		       from emp
               where ename = 'KING');

– 单个查询–> where下子查询（单行单列，单行多列…）
– > having (分组函数) --> 相关子查询 —> from子查询

– sql语句优化

11. 显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。

select ename,sal,hiredate
from emp
where hiredate > (select hiredate from emp where ename = 'SMITH');

– 子查询，全表查询一次，得到SMITH的结果
– 主查询，再做一次全表查询，根据之前的结果筛选出最终结论

select e.ename,e.sal,e.hiredate
from emp e, emp d
where e.hiredate > d.hiredate
and d.ename = 'SMITH';

– 相关子查询的不等值多表连接

12. 使用子查询的方式查询哪些职员在NEW YORK工作。
    – 相关子查询

select *
from emp
where exists(select 1 from dept 
             where deptno = emp.deptno
             and loc = 'NEW YORK');

13. 写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。

select ename, hiredate 
from emp
where deptno = (select deptno 
                from emp 
				where ename = 'SMITH')
and ename <> 'SMITH';                

14. 写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。

select empno, ename
from emp
where sal > (select avg(sal) from emp);

15. 显示部门名称和人数

select d.dname, count(e.empno)
from dept d left join emp e
on(e.deptno = d.deptno)
group by d.dname;

16. 显示每个部门的最高工资的员工

select *
from emp 
where sal in (select max(sal) from emp group by deptno);

17. 显示出和员工号7369部门相同的员工姓名，工资

select ename,sal 
from emp
where deptno = (select deptno from emp where empno = 7369);

18. 显示出和姓名中包含“W”的员工相同部门的员工姓名

select ename from emp
where deptno in (select deptno from emp where ename like '%W%')

– 子查询如果不确定是否是一个结果，那么使用in