【Oracle】表的查询

查看表结构：
desc 表名;

distinct取消重复列。

set timing on;打开显示操作时间

insert into users(userid,username,userpass) select * from users;
insert into student (xh,xm,sex,birthday,sal,classid) select * from student;  复制自身原有数据


内容区分大小写

算术表达式直接用即可，当运算中存在null的列，则最终结果为空。

nvl(comm,0);如果是空则为0。


通配符
%:0-N 个字符
_:任意单个字符


select * from emp where empno in(123,456,789);in效率很高。


select max(sal),min(sal) from emp;

select * from emp where sal=(select max(sal) from emp);显示工资最高的那人的信息

select avg(sal),deptno from emp group by deptno;显示每个部门的平均工资

select avg(sal),deptno from emp group by deptno having avg(sal)>2000;  
分组函数只能出现在选择列、having和order by后，不可以出现在where后。

多表查询需要排除笛卡尔集，条件数应该不小于表的数量-1，结果才可能正确；


select * from dept,emp where dept.deptno=emp.deptno and emp.deptno=10;
select * from salgrade,emp where emp.sal between salgrade.losal and salgrade.hisal;

自连接：
select * from emp b,emp a where a.empno=b.mgr;


查询时从右到左执行。

子查询

单行子查询：只返回一条数据的子查询
如何显示与SMITH同一部门的员工？
select * from emp where deptno=(select deptno from emp where ename='SMITH');

多行子查询：
如何查询和 部门10下的员工工作相同的雇员的信息？
select * from emp where job in(select job from emp where deptno=10);

如何显示工资比部门30的所有员工的工资高的员工信息？
all
select * from emp where sal>all(select sal from emp where deptno=30);
select * from emp where sal>(select max(sal) from emp where deptno=30);本条效率高。

如何显示工资比部门30的任意一个员工的工资高的员工的信息？
any
select * from emp where sal>any(select sal from emp where deptno=30);
select * from emp where sal>(select min(sal) from emp where deptno=30);

多列子查询：
如何查询与SMITH的部门和岗位完全相同的所有雇员？
select * from emp where job=(select job from emp where ename='SMITH') and deptno=(select deptno from emp where ename='SMITH');
select * from emp where (job,deptno)=(select job,deptno from emp where ename='SMITH');多列子查询

如何查询高于 自己部门 平均工资的员工的信息？
select * from emp a,(select deptno,avg(sal) mysal from emp group by deptno) b where a.deptno=b.deptno and sal>mysal;为表指定别名的时候不可加as



分页查询

1、rownum分页：rownum为Oracle分配的行号
select * from (select a.*, rownum rn from (select * from emp) a where rownum<=10) where rn>=6;
select * from (select a.*, rownum rn from (select * from emp) a where rownum<=10) b where b.rn>=6;
如指定查询列数、排序等信息只需修改最里层的子查询。


2、rowid--执行效率最高
select * from emp where rowid in(select rid from(select rownum rn,rid from (select rowid rid,sal from emp order by sal desc) where rownum<10) where rn>5) order by sal desc;

3、按分析函数查--效率最低
select * from (select t.*,row_number() over(order by sal desc) rk from emp t) where rk<10 and rk>5;



用查询结果创建一张新表

create table mytable(ename,sal) as select ename,sal from emp;

合并查询

union:列数必须一致;取消重复行
该操作符用于取得两个结果集的并集。当使用该操作符时会自动去掉结果集里 的重复行。
select * from emp where sal>2000 union select * from emp where job='MANAGER';
select * from emp where sal>2000 or job='MANAGER';

union all：不取消重复行
intersect：取交集
minus：取差集，显示第一个结果中存在而在第二个结果中不存在的记录。