Oracle 查询

    单行函数          条件表达式       聚合函数           分组查询           内连接            右外连接          左外连接          

   子查询               自连接             分页查询

scott 用户  一个初学者用户  ,可以用来练习 复杂的查询练习

必须超级管理员权限
alter user scott account unlock;      解锁账户
alter user scott identified by 123;   切换密码

单行函数 

字符查询
select upper('yes') from dual; --YES
select lower('WYC') from dual; --wyc
数字函数
select round(4.6) from dual;      --5   四舍五入
select round(4.657,2) from dual;  --4.66  指定位数
select trunc(5.9) from dual;     --5      直接截取
select mod(3,2) from dual;  --1          求余
日期函数
select sysdate+1 from dual;    --明天的此刻的时间 2019/9/30 21:06:23
select sysdate-e.hiredate from emp e;  --入职天数
select months_between(sysdate,e.hiredate) from emp e; --入职几个月,只有月有这个函数
select months_between(sysdate,e.hiredate)/12 from emp e;  --入职几年了
select (sysdate-e.hiredate)/7 from emp e;  --入职几周了

转换函数
--日期转字符
select to_char (sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;  --fm 去掉09-29的0 24小时制

--字符转date类型
select to_date(' 2019-9-29 21:11:5','fm yyyy-mm-dd hh24:mi:ss') from dual; 

通用函数
select e.sal+nvl(e.comm,0) from emp e;  --sal 薪资 comm奖金可能为null 相加后变0,所以判断

条件表达式

  等于情况

ename	名字
SMITH	哈哈
ALLEN	嘿嘿
WARD	无名
JONES	呵呵

select e.ename,
       (case e.ename
         when 'SMITH' then '哈哈'
           when 'ALLEN' then '嘿嘿'
             when 'JONES' then '呵呵'
               else '无名' 
                 end) 名字
from emp e;

如果不写 else  则没有对应的名字 为null

范围情况

sal	工资水平
800.00	低收入
1600.00	中等收入
5000.00	高收入

select e.sal,
       (case 
         when e.sal>3000  then '高收入'
           when e.sal>1500 then '中等收入'
               else '低收入' 
                 end) 工资水平           或者"工资水平 " 不能 '工资水平 '
from emp e;

聚合函数

select count(*) from emp;  底层也是count(1)

select count(1) from emp;   --总数量
select sum(sal) from emp;   --工资综合
select max(sal) from emp;   --工资最大值
select min(sal) from emp;   --工资最小值
select avg(sal) from emp;   --工资平均

分组查询 

 select 后面的 字段 必须出现在group by 后面  , 其他想要出现在select 后面  必须 使用聚合函数    列如 e.sal

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

select  e.deptno,avg(e.sal) 部门平均工资           部门平均工资 大于2000的
from emp e
group by e.deptno
having avg(e.sal)>2000;

select  e.deptno,avg(e.sal)
from emp e
where e.sal>800                员工工资大于800的 部门的 部门的平均工资
group by e.deptno;

select  e.deptno,avg(e.sal)
from emp e
where e.sal>800
group by e.deptno          员工工资大于800的 部门平均工资 大于2000的 部门的 部门的平均工资
having avg(e.sal)>2000;

 where 用于分组前   having 用于分组后

select * 
from emp e ,dept d;   笛卡尔积

内连接 

select * 
from emp e ,dept d
where e.deptno=d.deptno;
内连接效果一样
select * 
from emp e inner join dept d
on e.deptno=d.deptno;

右外连接

  部门下的所有员工     不能能查询所有员工  没有部门不显示   

select * 
from emp e right join dept d
on e.deptno=d.deptno;

Oracle专用 右外连接 
select * 
from emp e , dept d
where e.deptno(+)=d.deptno;

左外连接

  能查询所有即使 员工没有部门显示   null       员工所属部门

select * 
from emp e left join dept d     左外连接  
on e.deptno=d.deptno;


Oracle专用 左外连接 
select * 
from emp e , dept d
where e.deptno=d.deptno(+);

 

子查询

查询工资 和员工"scott" 工资一样的员工

select * from emp e where  e.sal in 
(select sal from emp e where e.ename ='SCOTT');   先查出SCOTT的工资

查询工资 和10号部门员工 工资一样的员工 

select * from emp e where  e.sal in 
(select sal from emp e where e.deptno =10);   先查出10号部门员工 工资

   以上两个返回都是单个或集合   返回结果都是只有工资一个 字段(一列)

  下面 返回结果是一个表时  ,  返回结果 有两个以上 字段( 两列 )

   查询出   每个部门最低工资,  和最低工资的员工姓名,   和员工所在部门名称,

   需要三张表  而部门最低工资表没有需要先查

select t.deptno ,t.msal ,e.ename,d.dname
from (select deptno,min(sal) msal   先查询 每个部门的最低工资
          from emp 
          group by deptno) t ,emp e, dept d
where t.deptno = e.deptno
and t.msal = e.sal
and e.deptno = d.deptno;

自连接

select e1.ename 员工,e2.ename 上司
from emp e1,emp e2
where e1.mgr = e2.empno;

  e2  的 员工id  等于别人的  e1的 上司 id  所以            e2是上司

分页查询

行号的概念  从一开始 不能跳着走 依次加一

select rownum from emp e;         14条记录
1 2 ..14 

   第一个select嵌套:  排序 会影响 rownum的顺序  所以在外面的嵌套select     

   第二个select嵌套 : rownum不能写大于一个整数   因为rownum从1开始   而where优先级最高,怎样都不满足,再嵌套一个select

select * from(
       select rownum rn, e.* from(   
              select * from emp order by sal desc   
       ) e where rownum <11
)where rn >5;