Oracle常用的查询与函数举例

针对scott用户查询

--1、最常用
select * from emp;
select * from dept;
select * from salgrade;
--2、查询每个员工所在的部门(内连接)
--2.1使用where连接
select e.empno,e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
--2.2使用inner join on 连接
select e.empno,e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;
--2.3使用inner join using 连接
select e.empno,e.ename,d.dname from emp e inner join dept d using(deptno);
--3、外连接
--3.1左外连接(查询出部门的员工的情况---显示所有部门)
select e.ename,d.dname from emp e left join dept d using(deptno);
--3.2左外连接用(+)
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno(+);
--3.3右外连接(查询出所有的员工的部门情况---显示所有员工)
select e.ename,d.dname from emp e right join dept d using(deptno);
--3.4右外连接(+)
select e.ename,d.dname from emp e,dept d where e.deptno(+)=d.deptno;
--4、自连接
--4.1查询出员工及他的上级
select a.ename as 员工,b.ename as 上级 from emp a,emp b where a.mgr=b.empno;
select a.ename as 员工,b.ename as 上级 from emp a inner join emp b on a.mgr=b.empno;
--5、子查询
--5.1查询工资高于平均工资的员工信息
select * from emp where sal>(select avg(sal) from emp);
--5.2使用any查询任意满足工资低于最低档工资的员工信息
select * from emp where sal<any(select losal from salgrade);
--5.3查询所有员工所属的部门
select dname from dept;
--5.4查询满足大于每个部门的最低工资的员工信息
select * from emp where sal>all(select min(sal) from emp group by deptno);
--5.5查询出每个部门中，高出本部门平均工资的员工的雇员号和姓名
select empno as 雇员号,ename as 姓名 from emp outer where sal>(
                select avg(sal) from emp inner where inner.deptno=outer.deptno);
--6、查询不在部门10的员工信息；注意子查询中的1，由于只关心子查询是否返回TRUE，使用1可以提高查询效率
--6.1EXISTS子查询效率高于IN子查询
select * from emp a where not exists (
                select 1 from emp b where a.deptno=10);
--6.2IN的效率低，但比较好理解
select * from emp where deptno not in 10;
--7、查询emp表中可以管理别的员工的员工
select ename from emp a where exists(
                select ename from emp b where a.empno=b.mgr);
--8、删除部门中重复的行
delete emp where rowid not in(select min(rowid) from emp group by deptno);
--9、查找emp表第6-10条记录
select * from (
                    select rownum m,ename,sal, deptno from emp where rownum<=10)
                    where m>5;

查询用户与表的相关信息

--查询用户和用户信息
select username,user_id,password,default_tablespace from dba_users;
select * from dba_users;
--查询用户所拥有的角色
select * from user_role_privs;--系统用户
select * from session_roles;--普通用户
--查询用户的权限
select * from users_sys_privs;--普通用户和系统用户都可以
--查看表中列的字符长度和字节长度
select length(ename),lengthb(ename) from empinfo;
--查询表的相关信息
select table_name,tablespace_name,temporary from user_tables;

函数function

--函数function
--创建一个求1!+2!+...20!的值的存储函数
create or replace function one_tw
return number
as 
value_sum number:=0;
value_loop number:=1;
begin
  for i in 1..20 loop
    value_loop:=value_loop*i;
    value_sum:=value_sum+value_loop;
  end loop;
  return value_sum;
end;
--调用函数
select one_tw() from dual;