sql基础语句2

聚合
count , max , min , avg , sum 

分组
group by
分组条件：having

子查询
如：select ename,(select dname from dept e where e.deptno=t.deptno) from emp t;  查询emp表中
rowid（物理地址），rownum（逻辑地址，只能从1开始）
如：select sal,ename from (select sal,ename,rownum rn from (select sal,ename from emp order by sal)where rownum <=5)where rn>=3;筛选出工资水平 3<=sal<=5的员工姓名和工资

多表查询
笛卡尔积
select * from emp,dept;
select * from emp e, dept d where e.deptno=d.deptno;
select * from emp e, salgrade s where e.sal >=s.losal and e.sal <= s.hisal;
join on关联    left join on左关联    right join on右关联   full outer join on全关联
select * from emp e join dept d on e.deptno = d.deptno;

集合操作
并集
union          合并
union all     不合并
差集
minus    
交集
intersect