[color=#362e2b]--打开查询时间
[color=#362e2b]set timing on;
[color=#362e2b]--将查询出来的数据在插入进去
[color=#362e2b]insert into student (id,name) select *from student;
[color=#362e2b]--删除表 级联删除（就是把用户里面的表和数据一起删掉）
[color=#362e2b]drop user liu_1 cascade
[color=#362e2b]--处理为值得空
[color=#362e2b]select ename,sal,comm,sal+nvl(comm,0) from emp;
[color=#362e2b]--查询1982年1月1人后入职的员工
[color=#362e2b]select *from emp where hiredate>'1-1月-1982';
[color=#362e2b]--多条件查询
[color=#362e2b]select *from emp where sal>1000 and sal<5000; [color=#362e2b]--首字母为大写S的员工 [color=#362e2b]select *from emp where ename like 'S%'; [color=#362e2b]--第三个字符为O的人 [color=#362e2b]select *from emp where ename like '__O%'; [color=#362e2b]--查询员工号为 123、456,789； [color=#362e2b]select *from emp where empno in(123,465,798); [color=#362e2b]--如何显示没有上级的人(就是直列段为空) [color=#362e2b]select *from emp where mgr is null; [color=#362e2b]--工作该高于五百，岗位为manager，同时首之母为大写的J [color=#362e2b]select *from emp where (sal>500 or job='MANAGER') and ename like 'J%';
[color=#362e2b]--使用order by desc(高到低) asc(低到高)
[color=#362e2b]select *from emp order by sal [desc];
[color=#362e2b]-- 按照部门号升序 ，员工工资降序
[color=#362e2b]select *from emp order by deptno asc,sal desc;
[color=#362e2b]--使用列的别名排序
[color=#362e2b]select ename,(sal+nvl(comm,0))*12 "年薪" from emp order by "年薪";
[color=#362e2b]--复杂查询(子查询)
[color=#362e2b]select *from emp where sal in (select max(sal) from emp);
[color=#362e2b]--最高工资，最低工资
[color=#362e2b]select *from emp where sal in (select max(sal) from emp,select min(sal) from emp);
[color=#362e2b]--group by 和 having
[color=#362e2b]select avg(sal),max(sal),deptno from emp group by deptno;
[color=#362e2b]select avg(sal),max(sal),job,deptno from emp group by deptno,job;
[color=#362e2b]--平均工资小于两千的人员信息
[color=#362e2b]select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000;
[color=#362e2b]--笛卡尔积
[color=#362e2b]select ename ,job,sal,comm,hiredate,loc,dname from emp,dept;
[color=#362e2b]--自连接查询（显示某员工的上级信息）
[color=#362e2b]select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename='FORD';
[color=#362e2b]--单行子查询(与某人同一部门的员工)
[color=#362e2b]select *from emp where deptno=(select deptno from emp where ename='SMITH')
[color=#362e2b]--显示工资比三十号部门都要大的的人员信息
[color=#362e2b]select ename,sal,deptno from emp where sal> all(select sal from emp where deptno=30)
[color=#362e2b]--与三十号工资相同的人员信息
[color=#362e2b]select ename,sal,deptno from emp where sal in (select sal from emp where deptno=30)
[color=#362e2b]--多行子查询
[color=#362e2b]select ename,sal,deptno from emp where sal> any (select sal from emp where deptno=30)
[color=#362e2b]--部门岗位相同的员工
[color=#362e2b]select *from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
[color=#362e2b]--如何查询高于自己部门平均工资员工的信息（查询不要用as指定一个表）
[color=#362e2b]select a2.empno,a2.ename,a2.job,a2.sal,mysal from emp a2,(select avg(sal) mysal ,deptno from emp group by deptno)a1
[color=#362e2b]where a1.deptno=a2.deptno and a2.sal>a1.mysal;
[color=#362e2b]--分页查询(rownum 行号,是变化的，是oracle分配的)
[color=#362e2b]select rownum,emp.* from emp;
[color=#362e2b]--把查询结果当成一张表 复杂的查询
[color=#362e2b]---创建数据库dbca