Oracle学习笔记5_简单查询

78345ze2zv

　　Oracle简单查询语句：
　　查看表结构：desc dept;
　　查看所有列：select * from dept;
　　查询指定列：select ename,sal,job,deptno from emp;
　　如何取消重复行：select distinct deptno,job from emp;
　　打开显示操作时间：set timing on;
　　Oracle区分大小写。
　　使用算术表达式：select sal*12,ename from emp;
　　使用别名：select sal*12 "年工资",ename from emp;
　　如何处理null值：使用nvl函数来处理
　　select sal*12+nvl(comm,0)*13 "年工资",ename,comm from emp;
　　如果comm是null，则变成0
　　如何使用连接字符串(||)：select ename || 'is a' || job from emp;
　　where:
　　查询工资高于3000的员工：select ename, sal from emp where sal >3000;
　　查询1982.1.1后入职员工：select ename,hiredate from emp where hiredate > '1-1月-1982';
　　查询工资在2000-2500之间的员工：select ename,sal from emp where sal >=2000 and sal <=2500;
　　like模糊查询：
　　%：表示任意0-多个字符
　　_: 表示任意单个字符
　　查询名字以S开头：select ename ,sal from emp where ename like 'S%';
　　查询名字第三个字符为O的员工：select ename,sal from emp where ename like '__O%';
　　where中使用in：select ename，sal from emp where empno in (1,2,3);
　　is null:select * from emp where mgr is null;
　　逻辑操作符：select * from emp where (sal>500 or job='MANAGER') and ename like 'J%';
　　order by 分组：select * from emp order by sal desc;
　　别名order by分组：select ename ,(sal+nvl(comm,0))*12 "年薪" from emp order by "年薪";
　　Oracle复杂查询：
　　数据分组：-max，min，avg，sum，count
　　avg :select * from emp where sal > (select avg(sal) from emp);
　　group by:select max(sal),avg(sal),deptno from emp group by deptno;
　　select max(sal),avg(sal),min(sal),deptno,job from emp group by deptno,job;
　　select deptno,avg(sal) from emp group by deptno having(avg(sal)<2000);
　　分组函数只能出现在选择列表、having、order by、having中，group by,having,order by不可以换顺序。
　　在选择列中如果有列，表达式和分组函数，那么这些列和表达式必须有一个出现在group by中，否则出错。
　　多表查询：
　　select dname,ename,sal from emp,dept where emp.deptno=dept.deptno;
　　select dname,ename,sal,emp.deptno from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;
　　select a1.ename, a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;
　　select a1.ename, a1.sal,a2.dname,a1.deptno from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno;
　　自连接：
　　select a1.ename,a2.ename from emp a1,emp a2 where a1.mgr=a2.empno and a1.ename='FORD';
　　子查询：
　　单行子查询：只返回一行数据的子查询语句。select * from emp where deptno = (select deptno from emp where ename='SMITH');
　　多行子查询：返回多行数据的子查询语句。select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10);
　　多行子查询中all操作符的使用：select ename,sal,deptno from emp where sal > all(select sal from emp where deptno=30);
　　效率高，等价于上句 select ename,sal,deptno from emp where sal > (select max(sal) from emp where deptno=30);
　　多行子查询中any操作符的使用：select ename,dept from emp where sal>any(select sal from emp where deptno=30);
　　效率高，等价于上句 select ename,sal,deptno from emp where sal > (select min(sal) from emp where deptno=30);
　　多列子查询：查询返回多个列数据的子查询语句。select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
　　在from子句中使用子查询：
　　select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2,(select deptno, avg(sal) mysal from emp group by deptno)a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;
　　当在from子句中使用子查询时，该子查询会被做为一个视图来对待，因此叫做内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名。
　　Oracle的分页一共有三种方式,下面来介绍一个：
　　1.rownum分页
　　先做一个子查询：select * from emp;
　　2.显示rownum(oracle分配的)
　　select a1.* ,rownum rn from (select * from emp) a1;
　　3.select a1.* ,rownum rn from (select * from emp) a1 where rownum <=10;
　　select * from (select a1.* ,rownum rn from (select * from emp) a1 where rownum <=10) a2 where a2.rn>=6;
　　select * from (select a1.* ,rownum rn from (select * from emp) a1) a2 where a2.rn>=6 and a2.rn<=10;
　　4，几个查询变化
　　a.指定查询列，只需修改最里层的子查询
　　b.如何排序？只需修改最里层的子查询
　　用查询结果创建新表：
　　create table mytable (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;
　　合并查询：
　　实际应用中，为了合并多个select语句的结果，可以使用集合操作符union，union all，intersect，minus
　　1.union 该操作用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中的重复行。
　　select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job='manager';
　　2.union all与union相似，但是它不会取消重复行，而且不会排序。
　　select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job='manager';
　　3.intersect取交集。
　　select ename,sal,job from emp where sal>2500 intersect select ename,sal,job from emp where job='manager';
　　4.minus 取得两个结果集的差集，它只会显示存在第一个集合中，而不存在于第二个集合中的数据。
　　select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job='manager';
　　创建数据库的两种方法：
　　1.通过oracle提供的向导工具。dbca[数据库配置助手]
　　2.手工步骤直接创建。