Oracle SQL语句学习汇总
select Upper('abcd') from dual;--小写转大写
select Lower('ABCD') from dual;
--大写转小写
select initcap(LOC) fromDEPT;
--将第一个字母变成大写
select concat('a','b') from dual;
--连接字符串
select substr('abcdef',-3,3) from dual;
--截取字符串函数
select length('abcdefg') from dual;
--计算字符串长度
select replace('abcd','b','B') from dual;
--字符串替换函数
select instr('ABCABCDABCDE','C',4,2) from dual;
--字符串包含函数
select lpad('mzl',10,'@#$') from dual;
--左填充函数
select rpad('mzl',10,'@#$') from dual;
--右填充函数
select trim(' txt') from dual;
--过滤首尾空格
select round(4.12,1) from dual;
--返回按指定位数进行四舍五入的数值
select mod(-20,3) from dual;
--求余函数 返回值与被除数的符号一致
select trunc(412.13,-2) from dual;
--不对指定小数前或后的部分做相应舍入选择处理,而统统截去
select months_between(sysdate,hiredate) from emp4;
--计算雇佣日期到当前时间的相差月份
select add_months(sysdate,1) from dual;
--增加月份函数
select last_day(sysdate) from dual;
--返回某个时间值的当月最后一天
select next_day(sysdate,'星期二')from dual;
--返回符合条件的下一天时间
select to_char(sysdate,'yyyy-mm-dd')from dual;
--把系统时间转换为字符串类型
select to_char(sysdate,'D') from dual;
--返回当前时间是星期几
select to_number('236')+to_number('658') from dual;
--字符类型转化为数字类型
select to_date('2011-4-13','YYYY-MM-DD') from dual;
--字符类型转化为日期类型
select nvl(comm,0) from emp4;
--如果第一个参数为空，则返回第二个参数。第二个参数表示是第一个参数的替换对象。
select nullif(6+9,8+10)from dual;
--如果表达式1和表达式2的值相等返回值为null 否则返回表达式1的值
select empno, ename, sal, comm, nvl2(comm, sal+comm, sal) total from emp4;
--如果参数表达式expr1值为NULL，则NVL2()函数返回参数表达式expr3的值；
--如果参数表达式expr1值不为NULL，则NVL2()函数返回参数表达式expr2的值。
select empno, ename, sal, comm, coalesce(sal+comm, sal, 0) from emp4;
--依次考察各参数表达式，遇到非null值即停止并返回该值
select empno,ename,job,
--case表达式
casejob
when 'CLERK' then '办事员'
when 'SALESMAN' then '售货员'
when 'ANALYST' then '统计员'
else '打字员'
end 职位
from emp4;
select empno 工号,ename 姓名,sal 薪水,
--decode表达式
decode(job,'CLERK','办事员',
'SALESMAN','销售员',
'ANALYST','统计员',
'未知'
)
职位
from emp4;
select lpad(initcap(trim(' dad')),10,'@#$') from dual;
--单行函数嵌套
select count(*)from emp4;
--统计函数
select max(sal) from emp4;
--最大值函数
select min(sal) from emp4;
--最小值函数
select avg(sal) from emp4;
--平均值函数
select deptno 部门编号, avg(nvl(mgr,0))平均值 from emp4 where mgr>7500 group by deptno;
--group by 分组函数
--出现在SELECT列表中的字段或者出现在order by 后面的字段，如果不是包含在分组函数中，那么该字段必须同时在GROUP BY子句中出现。
--包含在 GROUP BY子句中的字段则不必须出现在SELECT列表中。
--可使用where字句限定查询条件 不允许在 WHERE 子句中使用分组函数。
--可使用Order by子句指定排序方式
select deptno 部门编号, avg(nvl(mgr,0))平均值 from emp4 where mgr>7500 group by deptno having avg(mgr)>7730;
--having函数 从group by 函数中获取符合条件的值
select deptno 部门编号, avg(nvl(mgr,0))平均值 from emp4 where mgr>7500 group by deptno order by deptno desc;
--order by 函数 默认升序排列（asc）降序排列可以在其后加上desc;
select * from emp4,dept4;
--笛卡尔积
select a.ename 用户名,a.job 工作,b.dname 部门名称
from emp4 a,dept4 b
where a.deptno=b.deptno;
--等值连接查询
select a.ename 姓名,a.job 工作,a.sal 薪水
from emp4 a,dept4 b
where sal between 3000 and 5000;
--非等值连接
select a.ename,a.job,b.sal,b.hiredate
from emp4 a,emp4 b
where a.mgr=b.deptno;
--自连接
select a.deptno,a.ename,a.job
from emp4 a ,dept4 b
where a.deptno=b.deptno(+);
--左外连接
select a.deptno,a.ename,a.job
from emp4 a,dept4 b
where a.deptno(+)=b.deptno;
--右外连接
select * from emp4
union
select * from emp6;
--并集，所有的内容都查询，重复的显示一次
select * from emp4
union all
select * from emp6;
--并集，所有的内容都显示，包括重复的
select * from emp4
intersect
select * from emp6;
--交集：只显示重复的
select * from emp4
minus
select * from emp6;
--差集：只显示对方没有的（跟顺序是有关系的）
select sal from emp4
where
sal >
(
select sal
from emp4
where ename = 'JAMES'
);
--单行子查询
select * from emp4
where job in (select job from emp4 where ename = 'MARTIN' or ename = 'SMITH');
select * from emp4 where sal > all(select avg(sal) from emp4 group by deptno);
select * from emp4 where sal > any(select avg(sal) from emp4 group by deptno);
--多行子查询
select *
from emp4
where rownum1000 and exists
(
select sal
from emp4
group by deptno
);
--EXISTS：指定一个子查询，检测行的存在。
--如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。
select sal
from emp4
where sal not in
(
select sal
from emp4
where job'CLERK'
);
--如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,
Select * from (select * from emp4 order by dbms_random.value()) where rownum
页:
[1]