SQL

1、自连接

2、单行子查询

3、多行子查询

in相当于多个=

1、选取job不是salesman和manager并且工资不在3000-7000之间的选项
select * from emp WHERE empno in (select empno from emp where (sal NOT BETWEEN 3000 and 7000) and not job in('SALESMAN','MANAGER'));

2、工资比部门30的所有员工的工资高的员工的姓名，工资和部门号码
all操作符
select ename,sal,deptno from emp where emp.sal> all(select sal from emp where emp.deptno=30);
或
max函数
select ename,sal,deptno from emp where emp.sal>(select max(sal) from emp where emp.deptno=30);
此句效率更高

3、工资比部门30任何一个员工的工资高的员工的姓名、工资和部门号
any操作符
select ename,sal,deptno from emp where sal>any(select sal from emp where emp.deptno=30);
或
min函数
select ename,sal,deptno from emp where sal>(select min(sal) from emp where emp.deptno=30);

4、查询与smith的部门和岗位完全相同的所有雇员
多列子查询
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
或者select * from emp WHERE deptno = (SELECT deptno FROM emp WHERE ename='SMITH') and job =(SELECT job FROM emp WHERE ename='SMITH');

5、查询出高于自己部门平均工资的员工的信息
Oracle数据库中，数据表别名不能加as，列的别名可以加as也可以不加as
select * from emp a1,(SELECT deptno, AVG(sal) mysal FROM emp GROUP BY deptno) a2 WHERE a1.deptno = a2.deptno and a1.sal>a2.mysal;
说明：在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此也叫做内嵌视图，必须给子查询指定别名。

6、薪资大于2500或者工作职位是管理者的人员的姓名，薪资，职位
union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行
select ename,sal,job from emp where sal>2500
union
select ename,sal,job from emp where job='MANAGER';

union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中重复行。
select ename,sal,job from emp where sal>2500
union all
select ename,sal,job from emp where job='MANAGER';

7、薪资大于2500并且工作职位是管理者的人员的姓名，薪资，职位
intersect
使用该操作符用于取得两个结果集的交集
select ename,sal,job from emp where sal>2500
INTERSECT
select ename,sal,job from emp where job='MANAGER';

 

8、薪资大于2500且排除工作职位是管理者的人员的姓名，薪资，职位
minus
使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据，前者减后者
select ename,sal,job from emp where sal>2500
minus
select ename,sal,job from emp where job='MANAGER';

聚合函数
count(*|列名) 统计行数
avg(数值类型列名) 统计平均值
sum(数值类型列明) 求和
max(列名)最大值
min(列名)最小值
除count外，其它函数都会忽略null值

9、一条sql查出每个经理（MGR）手下有多少个员工，以及每个经理手下员工的平均薪资（sal）

自连接或者inner join

select e2.ename,count(*) 手下员工数量,avg(e1.sal) 平均薪资 FROM emp e1,emp e2 where e1.mgr= e2.empno GROUP by e2.ename;
select e.mgr,e1.ename,count(1),avg(e.SAL) from emp e inner join emp e1 on e.mgr=e1.empno group by e.MGR,e1.ename;