多条件查询,使用逻辑操作符
select *from emp where (sal>500 or job='MANAGER') and ename like 'J%';
使用order by
select ename, sal from emp order by sal asc;
select ename, sal from emp order by sal desc;
部门升序,工资降序排列
select *from emp order by deptno, sal desc;
使用别名排序 中文要加双引号""
select ename, sal*12 as nianxin from emp;
select ename, sal*12 as "年薪" from emp;
数据分组 (max, min, avg, sun, count), 分组函数只能出现在select,having,和order by 中,如果group by ,having, order by 同时出现,那么顺序只能是group by, having, order by。
select max(sal), min(sal), sum(sal), avg(sal), count(*) from emp;
select *from emp where sal=(select max(sal) from emp);
查询高于平均工资的员工并按薪资排序
select *from emp where sal>(select avg(sal) from emp) order by sal;
查询每个部门最高工资
select max(sal), deptno from emp group by deptno;
使用having,查询平均工资高于2000的,或者部门号=30的部门的部门号和平均工资
select avg(sal), deptno from emp group by deptno having avg(sal) > 2000 or deptno = 30 order by avg(sal);
多表查询,多表查询的条件至少有表的个数-1
select a1.ename, a1.sal, a2.dname from emp a1, dept a2 where a1.deptno = a2.deptno;