sql数据库查询相关操作，SQL的应用——SQL多表连查、子查询、多行子查询

1	`SQL多表连查`

--查询员工和部门信息
select * from emp e,dept d where e.deptno=d.deptno
--查询员工姓名，部门名称
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno
--查询所有员工姓名，部门名称
select e.*, d.dname from emp e,dept d where e.deptno=d.deptno
--查询工资大于3000的员工姓名，工资和部门名称
select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno and e.sal>=3000

                          --非等值查询
--查询公司工资等级
select * from emp e,salgrands where e.sal<=s.hisal and e.sal>s.losal
select * from salagrade

                              --外链接
--左外连接
select*from emp e,dept d where e.deptno=d.deptno(+)
--右外连接
select*from emp e,dept d where e.deptno(+)=d.deptno

                       --自连接
--查询员工姓名和经理姓名
select e1.ename ,e2.ename from emp e1,emp e2 where e1.mgr=e2.deptno
--查询员工姓名、经理姓名和其他经理姓名
select e1.ename,e2.ename,e3.ename from emp e1,emp e2, emp e3 
where e1.mgr=e2.empno and e2.empno=e3.empno

1	`以上为92版仅在面试出现`

                      --SQL99   表连接
--交叉连接   cross join
select *from emp e cross join dept d
--自然连接      natural join 
select *from emp natural join dept
--using 
select e.ename,deptno from emp e join dept d using(deptno)
--on 自定义连接
select *from emp e join dept d 
on e.deptno=d.deptno
select *from emp e1 join emp e2 on e1.mgr=e2.empno
--查询员工的姓名、经理及其经理的名字
select e1.ename,e2.ename,e3.ename from emp e1 join emp e2 on e1.mgr=e2.empno join emp e3 on e2.mgr=e3.empno
--SQL99  inner join  两边都合法的数据
select*from emp e inner join dept d on e.deptno=d.deptno
--left join 以左表为主
select*from emp e left join dept d on e.deptno=d.deptno
--right join 以右表为主
select*from emp e right join dept d on e.deptno=d.deptno
--full join 全连 取两个表的所有数据
select*from emp e full join dept d on e.deptno=d.deptno
----查询员工的姓名、经理及其经理的名字和部门名字
select e1.ename, d.dname, e2.ename, d2.dname
  from emp e1
  left join dept d
    on e1.deptno = d.deptno
  left join emp e2
    on e2.mgr = e2.empno
  left join dept d2
    on d2.deptno = d2.deptno

**子查询**

--比CLARK工资高的人
select SAL from emp where ename='CLARK'
select *from emp where sal>(select SAL from emp where ename='CLARK')
--查询工资高于平均工资的雇员姓名和工资
select avg(sal) from emp 
select ename,sal from emp where sal>(select avg(sal) from emp )
--查询同SCOTT同部门的且工资比他低的员工的名字和工资
select deptno from emp where ename='SCOTT'
select sal from emp where ename='SCOTT'
select ename, sal
  from emp
 where deptno = (select deptno from emp where ename = 'SCOTT')
   and sal < (select sal from emp where ename = 'SCOTT')
--查询和'SMITH','SCOTT','CLARK'同一个部门的员工姓名
select distinct deptno from emp where ename in('SMITH','SCOTT','CLARK')
select *
  from emp
 where deptno in (select distinct deptno
                    from emp
                   where ename in ('SMITH', 'SCOTT', 'CLARK'))
--查询和'SMITH', 'SCOTT', 'CLARK'同一个部门并不包含他们三个的员工姓名
   and ename not in ('SMITH', 'SCOTT', 'CLARK')
--查询工资最高的员工名字和工资
select ename,sal from emp where sal=(select max(sal)from emp )
--查询职务和'SCOTT'相同但是比'SCOTT'雇佣时间早的雇员信息
select * from emp
 where job = (select job from emp where ename = 'SCOTT')
   and hiredate < (select hiredate from emp where ename = 'SCOTT')
--查询工资比'SCOTT'高或者雇佣时间比'SCOTT'早的雇员编号和姓名
select empno,ename from emp
 where job = (select job from emp where ename = 'SCOTT')
   and hiredate < (select hiredate from emp where ename = 'SCOTT')

1	`多行子查询`

--查询工资低于任何一个“CLERK”的工资的雇员信息
select*from emp where sal all (select sal from emp where job = 'SALESMAN')
--查询部门20中职务同部门10的雇员一样的雇员信息。
select *from emp where job in (select job from emp where deptno=10)and deptno=20
--查询在雇员中有哪些人是经理人
select *
  from emp
 where empno in (select distinct mgr
                   from emp
                  where mgr is not null
                     or mgr != '')
--找出部门编号为20的所有员工中收入最高的职员
select ename
  from emp
 where sal = (select max(sal) from emp where deptno = 20)
   and deptno = 20
</any(select>