11月22日 数据库第三天 多表连接 高级查询

多表连接

  1：等值连接

     mysql下可用             select *** from  table1，table2  where table1.id=table2.id；

     sql 标准：                 select *** from table1 join table2 on （table1.id=table2.id）；

 2：不等值连接

    mysql下可用              select ***from table1,table2 where table1 between table2.low and table2.hight ;

     sql 标准：                 select *** from table1 join table2 on （table 1 between table2.low and table2.hight ）；

3 : 自连接

     mysql下可用             select *** from  table1 t1，table2  t2 where t1.studentId=t2.teacherId； 适用于一张表，多重身份查询

######################################################表别名 可用于sql语句任意地方 

4 ：外连接（左外连接 right，右外连接 left）

      select *** from table1 right/left join table2 on （table1.id=table2.id）；适用于表1中列多余表2情况

5 ：全连接

      select *** from table1 full join table2 on （table1.id=table2.id）；

高级查询（嵌套查询）

1：where 中嵌套子查询

2：from 中嵌套子查询

3：having 中嵌套子查询

4：select  中嵌套子查询

练习如下 

select *from emp where job=(select job from emp where ename='scott');
select *from emp where job=(select job from emp where ename='scott') and mgr=(select mgr from emp where ename='scott');

#查询员工姓名,职位,月薪,及其职位月薪 最高和最低
select  ename,e.job,sal,max,min
from emp e,(select job,max(sal) 'max',min(sal) 'min' from emp group by job) a
where e.job=a.job;

#月薪比自己职位平均月薪高的员工信息
select e1.* 
from  emp e1,(select job,avg(sal) 'avg'
            from emp
            group by job) e2
where e1.job=e2.job and e1.sal>e2.avg;
#1.查询部门平均工资在2500元以上的部门名称及平均工资。

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

#2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。
select e.job_id,s.avg
from employees e,(select job_id,avg(salary) 'avg' 
                    from employees 
                    group by job_id) s
where s.job_id=e.job_id and e.job_id not like 'sa%' and s.avg>2500 group by job_id order by s.avg desc;

select job_id,avg(salary) 'avg' from employees where job_id not like 'sa%'  group by job_id having avg >2500 order by avg desc  ;

#3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。
select d.department_name,s.min,s.max
from departments d,(select department_id,count(employee_id) 'c',round(min(salary),0) 'min',round(max(salary),0) 'max' 
                  from  employees group by department_id) s
where d.department_id=s.department_id and c>2;
#4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。 
select job_id,sum(salary) '工资合'
from employees 
where job_id!='salesman' 
group by job_id 
having sum(salary) >25000;
#5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，
#没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序

select s.manager_id,e1.last_name,s.min
from employees e1 left join (select manager_id,min(salary) 'min' 
                       from employees group by manager_id) s on(s.manager_id=e1.employee_id)
where s.min>3000 
group by manager_id
order by s.min desc;

#试着做出一个所有都有经理的表
select e1.manager_id,e2.first_name
from employees e1 join employees e2 on( e1.manager_id=e2.employee_id)
join (select ifnull(manager_id,0),min(salary) from employees group by manager_id);

select  e1.manager_id,e2.first_name
from employees e1,employees e2
where e1.manager_id=e2.employee_id;
#老师留的习题
#查询与NEENA同一个经理的其他员工信息
select * 
from employees
where manager_id=(select manager_id from employees where first_name='neena') and first_name!='neena';

#查询比jennifer whalen月薪高的员工信息
select *
from employees 
where salary>(select salary from employees where first_name='jennifer' and last_name='whalen');
#查询员工姓名，月薪和其部门的平均月薪 
select e.first_name,e.salary,e.department_id,avg
from employees e ,(select department_id,avg(salary) 'avg' 
                    from employees 
                    group by department_id) a
where e.department_id=a.department_id;

#查询月薪高于其部门平均月薪的员工信息 
select e.*,s.avg
from employees e,(select department_id,employee_id,avg(salary) 'avg' 
                    from employees 
                    group by department_id ) s
where e.employee_id=s.employee_id and salary > s.avg ;
#查询司龄高于部门平均司龄的员工信息
select e.*,datediff(now(),e.hire_date),s.avg
from  employees  e, (select department_id,avg(datediff(now(),e.hire_date)) 'avg' 
                    from employees e
                    group by department_id) s
where e.department_id=s.department_id and datediff(now(),e.hire_date)>s.avg;

#2018.11.22
#练习1
#1.写一个查询，显示所有员工姓名，部门编号，部门名称。
select e.first_name,d.department_id,d.department_name
from employees e,departments d
where e.department_id =d.department_id;
#2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金
select e.ename,d.loc,e.comm
from emp e,dept d 
where e.deptno=d.deptno and d.loc ='chicago' and e.comm is not null and e.comm !=0;
#3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。
select e.ename,d.loc
from emp e,dept d
where e.deptno=d.deptno and e.ename like '%a%' ;
#练习2
#1.查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。
select e.empno,e.ename,e.sal,employeesemployees;

#课后作业
#1.显示员工SMITH的姓名，部门名称，直接上级名称
select e1.ename '员工姓名',e2.ename '经理名',d.dname
from emp e1 join emp e2 on(e1.mgr=e2.empno) 
join dept d on(e1.deptno=d.deptno)
where e1.ename='smith';

select  e1.ename '员工表',e2.ename '经理表',d.dname '部门名'
from emp e1,emp e2,dept d
where e1.mgr=e2.empno  and d.deptno=e1.deptno
and e1.ename ='SMITH';
#2.显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。
select  e.first_name,d.department_name,e.salary,s.grade
from employees e join departments d using (department_id)
join salgrade s on(salary between losal and hisal) 
where grade>4;

select e.first_name,d.department_name,e.salary,s.grade 
from  employees e,departments d,salgrade s
where e.department_id=d.department_id 
and e.salary between s.losal and s.hisal and grade>4;
#3.显示员工KING和FORD管理的员工姓名及其经理姓名。
select e1.last_name '经理',e2.last_name '员工'
from employees e1 join  employees e2 on (e1.employee_id=e2.manager_id)
where e1.last_name in('KING','FORD');

select e1.last_name '经理表',e2.last_name  '员工表'
from employees e1 ,employees e2
where e1.employee_id =e2.manager_id 
and (e1.last_name='KING' or e1.first_name='FORD') ;
#4.显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。
select e1.first_name '员工',e1.hire_date '入职',e2.first_name '经理',e2.hire_date '入职'
from employees e1 join employees e2 on(e1.manager_id=e2.employee_id)
where e1.hire_date<e2.hire_date;

select e1.first_name '员工姓名',e1.hire_date '员工参加工作时间',e2.first_name '经理姓名',e2.hire_date '员工参加工作时间'
from employees e1 ,employees e2
where e1.manager_id=e2.employee_id and e1.hire_date<e2.hire_date;

#部门名称 部门经理名 包括没有部门经理的部门
select d.department_name,e.first_namesalgrade
from departments d left join employees e on (d.department_id=e.department_id);