Oracle常用基本语句查询创建等.doc

查询块 批量查询 SQLselect * from emp where in(123,456,789); //查询一个条件的多个情况的批量处理 查询某个数据行的某列为空的数据的相关数据 SQL select * from emp where mgr is null; 条件组合查询（与、或） SQLselect * from emp where (sal500 or job=’MANAGER’) and ename like ‘J%’; Order by 排序 【1】SQLselect * from emp order by sal （asc）; //从低到高[默认] 【2】SQLselect * from emp order by sal desc; //从高到低 【3】SQLselect * from emp order by deptno (asc),sal desc; //组合排序 【4】SQLselect ename,sal*12 “年薪” from emp order by “年薪” (asc); SQL select ename,(sal+nvl(comm,0))*12 as 年薪 from emp order by 年薪; 资料分组（max、min、avg、sum、count） SQLselect max(sal),min(sal) from emp; SQLselect ename,sal from emp where sal=(select max(sal) from emp); //子查询，组合查询 SQL select * from emp where sal(select avg(sal) from emp); //子查询，组合查询 SQL update emp set sal=sal*1.1 where sal(select avg(sal) from emp) and hiredate1-1月-1982; //将工资小于平均工资并且入职年限早于1982-1-1的人工资增加10% Group by 和 having 子句 //group by用于对查询出的数据进行分组统计 //having 用于限制分组显示结果 SQLselect avg(sal),max(sal),deptno from emp group by deptno; //显示每个部门的平均工资和最低工资 SQLselect avg(sal),max(sal),deptno from emp group by deptno; //显示每个部门的平均工资和最低工资 SQL select avg (sal),max(sal),deptno from emp group by deptno having avg(sal)2000; SQL select avg (sal),max(sal),deptno from emp group by deptno having avg(sal)2000 order by avg(sal); 多表查询 笛卡尔集:规定多表查询的条件是至少不能少于：表的个数-1 SQL select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno; SQL select a1.dname,a2.ename,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno and a1.deptno=10; //显示部门编号为10的部门名、员工名和工资 SQL select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal; SQL select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno; //多表排序 SQL select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno; // 自连接（多表查询的特殊情况） SQL select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename=FORD; 子查询 SQL select * from