这里主要汇总一下常用或者遇到的比较典型的查询语句,不必对语句做过多解释,随着学习的过程不断更新……
1、Order by:
select * from emp order by deptno,sal desc;(先按部门升序,在每个部门中按工资降序)
2、别名排序:
select ename,sal*12 "年薪" from emp order by "年薪" acs/desc;
select ename,(sal+nvl(comm,0))*12 as "年薪" from emp order by "年薪"
as 可用可不用,如果是英文就不用添加引号了
3、条件组合:
select * from emp where (sal>500 or job='MANAGER') and ename like 'J%'
4、分页查询:
select max(sal),min(sal) from emp;(V)
select ename ,max(sal) from emp;(X)(所有的列要么是分组,要么都不是分组函数)
查询工资最高的员工名
select ename ,sal from emp where sal=(select max(sal) from emp);
select * from emp where sal>(sleect avg(sal) from emp);
5、group by 和having select avg(sal),max(sal) ,deptno from emp group by deptno; select avg(sal),max(sal),deptno ,job from emp group by deptno,job; select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000; select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000 order by avg(sal); 6、总结: 1.分组函数只能出现在选择列表、having 、order by 子句中 2.group by ……having …… ordery by …… 3.在选择列中,如果有列、表达式、分组函数,那么这些列和表达式必须有一个出现在group by 子句中,否则会出错。 3.select deptno ,avg(sal) ,max(sal) from emp group by deptno having avg(sal)<2000; 7、多表查询:的条件不能少于表的个数-1,否则会出现笛卡尔积的现象。 select a1.ename,a1.sal,s2.dname from emp a1,dept a2 where a1.deptno=a2.deptno;