单行函数 条件表达式 聚合函数 分组查询 内连接 右外连接 左外连接
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;