select
mgr, min(sal), avg(sal) avg
from emp e
where mgr is not null and sal >= 900
group by mgr
having avg(sal) >= 1300; 加上组函数的条件,having很占用资源,组函数只能用having筛选
2. select
ename
from
emp
where ename like '_A%' _代表一位,%代表有或者无或者多 select * from user_bonnie where name like '\_a%' escape '\'; \可以换成任何字符
3. select
ename, sal
from emp
where
sal > 1200
order by
hiredate; 默认升序 sec 降序 desc
4.select
a.deptno, a.ename, a.sal, a.deptno, b.maxsal
from emp a,
(select deptno, max(Sal) maxsal
from emp
group by deptno) b -- 子查询
where a.deptno = b.deptno -- 等值连接,筛选掉不匹配的记录,关联查询
5.多表关联查询
select *
from emp,dept
where emp.empno=dept.empno; 连接条件 公共字段前面必须加前缀
select
emp.deptno, ename, dname
from emp
inner join dept -- inner可以省略
on emp.deptno = dept.deptno; 内连接,本质上和等职连接相同
6.左外连接、右外连接、全外连接
select
emp.deptno,ename,dname
from emp e
left (right、all) outer join dept d outer可以省略 别名:也具有给字段取别名 -只能在orderby 使用 表也可以取别名
on e.deptno=d.deptno; 前(后)表为驱动表,全显示 两张表全显示(全外连接)
7.子查询
select distinct job,ename distinct 去重复
from enp
where sal>(select sal
from emp
where id='10'); 执行顺序:from where groupby having select orderby
8.关联临时表
select ename,sal,d.avg_sal
from emp e
join (select
deptno,round(avg(sal)) avg_sal
from
emp
group by deptno) d
on e.deptno=d.deptno
where e.sal>d.avg_sal;
9.分页查询rownum 当前页 (Page-1)*PageSize ~Page*PageSize rownum只能从1开始 解决:添加别名,作为子查询表
select sal,ename
from emp
where rownum<=4; 前四条,无排序
--
select sal,ename,e.n
from
(select sal,ename,rownum n from emp) e
where e.n between 5 and 8; 无排序,5到8条
--
select sal,ename,e.n
from(
select sal,ename,rownum n
from(select sal,ename
from emp
order by sal desc)
)e
where e.n between 5 and 8; 按sal 降序 低5~8位
子查询小结
1.结果时单记录 where 直接比较
2.一列,多行记录-集合 any in all
3.多行多列 -作为一张临时表