基本查询

查询数据量，取消重复数据，取别名

sql> select count(*) from users;        //查询数据量 
sql> select distinct job from table1;   //取消重复数据 
sql> select sal*12 "年工资" from table1; //取别名 

对NULL值的处理

sql> select sal*12+nvl(comm, 0) "年工资" from table1; 

对日期的查询: 注意日期的格式

sql> select ename, hiredate from table1 where hiredate > '1-1月-1982'; 

like in or and操作符: % 表示任意多个字符； _ 表示任意单个字符

sql> select ename, sal from table1 where ename like 's%'; 
sql> select ename, sal from table1 where empno in (123, 234, 456); 
sql> select ename, sal from table1 where (sal>500 or job='MANAGER') and ename like 's%'; 

order by 排序: 多重排序，使用别名排序

sql> select ename, sal from table1 order by sal asc/desc; 
sql> select ename, sal from table1 order by deptno, sal desc; //多重排序 
sql> select ename, sal*12 '年薪' from table1 order by '年薪';   //使用别名排序 

复杂查询

分组函数 max min avg sum count: 分组函数只能出现在选择列表，having, order by子句中。

sql> select max(sal) from table1; 
sql> select ename, sal from table1 where sal = (select max(sal) from emp);

案例: [每个部门的平均工资], [每个部门每种岗位的平均工资], [平均工资低于2000的部门]

sql> select avg(sal), deptno from table1 group by deptno;          //每个部门的平均工资 
sql> select avg(sal), deptno, job from table1 group by deptno, job;//每个部门每种岗位的平均工资 
sql> select avg(sal), deptno from table1 group by deptno having avg(sal) > 2000; //平均工资低于2000的部门 

多表查询

sql> select a1.ename, a2.dname from table1 a1, dept a2 where a1.deptno = a2.deptno;
sql> select a1.ename, a1.sal, a2.grade from table1 a1, salgrade a2 where a1.sal between a2.losal and a2.hisal;

自连接: 指在同一张表的连接查询

sql> select worker.ename, boss.ename from table1 worker, table1 boss where worker.mgr=boss.empno and worker.ename='FORD';

子查询

//和部门10的工作相同的雇员信息
sql> select * from table1 where job in (select distinct job from table1 where deptno=10);
//工资比部门30的员工工资都高的员工信息
sql> select * from table1 where sal > all (select sal from table1 where deptno=30);
sql> select * from table1 where sal > (select max(sal) from table1 where deptno=30);
//工资比部门30的至少一个员工工资高的员工信息
sql> select * from table1 where sal > any (select sal from table1 where deptno=30);
sql> select * from table1 where sal > (select min(sal) from table1 where deptno=30);
//与SMITH的部门和岗位相同的雇员信息
sql> select * from table1 where (deptno, job) = (select deptno, job from table1 where ename = 'SMITH'); 

分页查询

sql> select * from table1;                                                      //1 
sql> select a1.*, rownum rn from (select * from table1) a1;                     //2 
sql> select a1.*, rownum rn from (select * from table1) a1 where rownum <= 10;  //3 
sql> select * from (3) where rn >= 6; 

合并查询

union, union all, intersect, minus

sql> select ename from table1 where sal > 2500 union select ename from table1 where job = 'MANAGER';