Oracle查询
条件查询？
1.select * from emp where sal>1500;
2.select * from emp where comm is not null; //可以得到奖金的人员所有信息
3.select * from emp where sal>1500 and comm is not noll; //薪水大于1500而且有奖金
4.select * from emp where not (sal>1500 and comm is not null);
5.select * from emp where sal between 1500 and 3000;
6.select * from emp where sal >=1500 and sal <=3000;
7.select * from emp where hibedate between '1-1 月-81' and '31-12月 -81' ;
8.select * from emp where ename = 'SIMITH' ; //注意oracle对字符串大小写敏感
9.select * from emp where empno=7366 or empno=7369 ;
10.select * from emp where empno in (7366,7369) ;
11.select * from emp where empno not in (7369,7366) ;
子查询?
1.查询薪水最高的员工的名子，薪水
select ename ,sal from emp where sal= (select max(sal) from emp) ;
2.高于平均值的员工
select ename,sal from emp where sal > (select avg(sak) from emp);
3.第个部门里，工资最高的员工
select ename,sal from emp jion (select max(sal) max_sal,deptno from emp group by deptno) t
on (emp.sal = t.max_sal and emp.dept.no=t.deptno) ;
4.每个部门平均工资,这个工资的等级
5.自连接
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;
表连接(连表查询)?
1.select ename,dname from emp,dept where emp.deptno=dept.deptno ; // (SQL1992)
2.select ename,dname, from emp join dept on (emp.deptno=dept.deptno); // (SQL1999)
3.select ename,dname, from emp join dept using (deptno) ; // (不推荐)
4.select ename,grade from emp e,jion salgrade s on (e.sal between s.losal and s.hisal) ;
6.select ename,dname,grade from
emp e join dept d on (e.deptno=d.deptno)
jion salgrade s on (e.sal between s.losal and s.hisal)
where ename not like ‘_A%’ ;
7.select e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno) ;(自连接)
8.select e1.ename,e2.ename from emp e1 left outer jion emp e2 on (e1.mgr=e2.empno) ;(左处连接)
9.select e1.ename,e2.ename from emp e1 right outer jion emp e2 on (e1.mgr=e2.empno) ;(右处连接)
10.select e1.ename,e2.ename from emp e1 full jion emp e2 on (e1.mgr=e2.empno) ;(左右处连接)
11.求部门中哪些人的工资最高
select ename,sal from emp
join (select max(sal) max_sal,deptno from emp group by deptno) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno)
12.求部门平均工资的等级
select deptno,avg_sal, grad from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal betwen s.losal and s.hisal);
常用系统查询命令？
1.当前用户下有多少张表,oracle默认的表user_tables· select table_name from user_tables;
· select * from tab;
2.当前用户下有多少个视图；
select view_name from user_views ;
3.查询当前数据库名；
select name from v$database;
4.查询当前数据库实例名：
select instance_name from v$instance;
5.当前用户下有多少个约束；
select constraint_name from user_constraints ;
