oracle常用语句
1、Case具有两种格式(简单Case函数和Case搜索函数)
--简单Case函数
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
2、 rownum选择指定n行
-
查询第几行的记录
select sal from emp where rownum=1; //查询得到第一行记录 //select sal from emp where rownum=5; //不可以查询到第五行记录，因为rownum总是从1开始查询的， 故这种方式不可以直接得到第几行的记录。若想得到第五行记录，应采用如下方式： select r,sal from (select rownum r,sal from emp) where r= 5; -
用来获取前几行的记录，即小于某值的记录。
例：查询前四行的记录
select rownum,sal from emp where rownum<5; -
用来获取后几行的记录，即大于某值的记录。例：查询第五行之后的记录
select r,sal from (select rownum r,sal from emp ) where r>5; -
查询一个范围内的记录。如查询第三行到底八行的记录：
select r,sal from (select rownum r,sal from emp) where r>=3 and r<=8; -
rownum 与排序
select rownum,sal from emp order by sal;从这条语句的运行结果你可以发现，rownum不是从1 一次增大，而是乱的，实际上这些rownum是指每条记录未排序之前所处的行数，当然这不是我们想要的结果，那么如何得到rownum也是顺序排列的查询结果呢？这就需要先对原纪录排序，然后从新的顺序中提取出rownum和想要的记录内容。如：
select rownum,sal from (select * from emp order by sal) ; select rownum, sal from (select * from emp order by sal ) where rownum <5; select r, sal from (select rownum r,sal from (select * from emp order by sal )) where r >5; select r, sal from (select rownum r,sal from (select * from emp order by sal )) where r >5 and r < 10;
3. nvl函数
NVL(a,b)就是判断a是否是NULL,如果不是返回a的值，如果是返回b的值。 通过查询获得某个字段的合计值，如果这个值位null将给出一个预设的默认值。
4. round 函数
语法为ROUND(number，num_digits)
其中Number是需要进行四舍五入的数字；Num_digits为指定的位数，按此位数进行四舍五入，如果 num_digits 大于 0，则四舍五入到指定的小数位，如果 num_digits 等于 0，则四舍五入到最接近的整数，如果 num_digits 小于 0，则在小数点左侧进行四舍五入。
例如：
- ROUND(2.149， 0) 将 2.149 四舍五入到一个整数结果为2。
- ROUND(2.15， 1) 将 2.15 四舍五入到一个小数位，结果为2.2。
- ROUND(2.149， 1) 将 2.149 四舍五入到一个小数位结果为2.1。
- ROUND(-1.475， 2) 将 -1.475 四舍五入到两小数位结果为-1.48)。
- ROUND(21.5， -1) 将 21.5 四舍五入到小数点左侧一位结果为20。
5. decode函数
- decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
该函数的含义如下：
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
- decode(字段或字段的运算，值1，值2，值3）
这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3。当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多。
6 日期与字符串
TO_CHAR 是把日期或数字转换为字符串
TO_DATE 是把字符串转换为数据库中得日期类型转换函数
TO_NUMBER 将字符转化为数字
TO_CHAR
使用TO_CHAR函数处理数字
TO_CHAR(number, '格式')
TO_CHAR(salary,’$99,999.99’);
使用TO_CHAR函数处理日期
TO_CHAR(date,’格式’);
TO_NUMBER
使用TO_NUMBER函数将字符转换为数字
TO_NUMBER(char, ['格式'])
TO_DATE
使用TO_DATE函数将字符转换为日期
TO_DATE(char, ['格式'])
数字格式格式
9 代表一个数字
0 强制显示0
$ 放置一个$符
L 放置一个浮动本地货币符
. 显示小数点
, 显示千位指示符
日期格式
格式控制 描述
YYYY、YYY、YY 分别代表4位、3位、2位的数字年
YEAR 年的拼写
MM 数字月
MONTH 月的全拼
MON 月的缩写
DD 数字日
DAY 星期的全拼
DY 星期的缩写
AM 表示上午或者下午
HH24、HH12 12小时制或24小时制
MI 分钟
SS 秒钟
SP 数字的拼写
TH 数字的序数词
“特殊字符” 假如特殊字符
HH24:MI:SS AM 15:43:20 PM
日期例子：
SELECT TO_DATE('2006-05-01 19:25:34', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
SELECT TO_DATE('2006-05-01 19:25', 'YYYY-MM-DD HH24:MI') FROM DUAL
SELECT TO_DATE('2006-05-01 19', 'YYYY-MM-DD HH24') FROM DUAL
SELECT TO_DATE('2006-05-01', 'YYYY-MM-DD') FROM DUAL
SELECT TO_DATE('2006-05', 'YYYY-MM') FROM DUAL
SELECT TO_DATE('2006', 'YYYY') FROM DUAL
日期说明：
当省略HH、MI和SS对应的输入参数时，Oracle使用0作为DEFAULT值。如果输入的日期数据忽略时间部分，Oracle会将时、分、秒部分都置为0，也就是说会取整到日。
同样，忽略了DD参数，Oracle会采用1作为日的默认值，也就是说会取整到月。
但是，不要被这种“惯性”所迷惑，如果忽略MM参数，Oracle并不会取整到年，取整到当前月。
注意：
- 在使用Oracle的to_date函数来做日期转换时，可能会直觉地采用“yyyy-MM-dd HH:mm: ss”的格式作为格式进行转换，但是在Oracle中会引起错误：“ORA 01810 格式代码出现两次”。
如：select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mm:ss') from dual;
原因是SQL中不区分大小写，MM和mm被认为是相同的格式代码，所以Oracle的SQL采用了mi代替分钟。
select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;
- 另要以24小时的形式显示出来要用HH24
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;//mi是分钟
select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;//mm会显示月份
7. with as语句
WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。
特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。
使用如下：
with
cte1 as
(
select * from table1 where name like 'abc%'
),
cte2 as
(
select * from table2 where id > 20
),
cte3 as
(
select * from table3 where price < 100
)
select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id
8. rank() over (partition ORDER BY) 功能
假设现在有一张学生表student，学生表中有姓名、分数、课程编号，现在我需要按照课程对学生的成绩进行排序。
select * from student
result
rank over ()可以实现对学生排名，特点是成绩相同的两名是并列，如下1 2 2 4 5
select name,
course,
rank() over(partition by course order by score desc) as rank
from student;
result
row_number这个函数不需要考虑是否并列，那怕根据条件查询出来的数值相同也会进行连续排名
select name,
course,
row_number() over(partition by course order by score desc) as rank
from student;
result
注意:
-
partition by用于给结果集进行分区。
-
partition by和group by有何区别？
-
partition by只是将原始数据进行名次排列(记录数不变)
-
group by是对原始数据进行聚合统计(记录数可能变少, 每组返回一条)
-
-
使用rank over()的时候，空值是最大的，如果排序字段为null, 可能造成null字段排在最前面，影响排序结果。可以这样：
rank over(partition by course order by score desc nulls last)
9. 序号计数器 create sequence
你首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限。
-- Create sequence
create sequence SEQ_T
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;
语义Semantics：
- INCREMENT BY：指定序列增长步长。可以为正（升序）、负整数（降序），但不能为0。最高精度28。
- START WITH： 指定序列起始数。默认为序列最小值。
- MAXVALUE ：指定序列最大值。最大28位。必须大于等于起始值且大于等于序列最小值。
- NOMAXVALUE： 无最大值（实际为10^27或-1）。default
- MINVALUE ：指定序列最小值。
- NOMINVALUE ：无最小值（实际为1或-10^26）。Default
- CYCLE ：指定序列达到最大值或最小值后继续从头开始生成。
- NOCYCLE ：不循环生成。Default.
- CACHE ：指定数据库内存中预分配的序列值个数，以便快速获取。最小cache值为2。
create sequence empseq
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;
SELECT empseq.currval FROM DUAL;
但是要注意的是：
-
第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的INCREMENT BY值，然后返回增加后的值。CURRVAL总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。一次NEXTVAL会增加一次SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。
-
如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，oracle自动再取一组到cache。 使用cache或许会跳号， 比如数据库突然不正常down掉（shutdown abort),cache中的sequence就会丢失. 所以可以在create sequence的时候用nocache防止这种情况。
