<五> Oracle函数使用-CSDN博客

基础函数的使用：

View Code

  1 -- 取绝对值
  2      select abs( - 1) from dual;
  3
  4 -- 向上取整
  5      select ceil( 1.001) from dual; 结果是2
  6      -- s:ceiling
  7
  8 -- 向下取整
  9      select floor( 1.001) from dual;
10
11 -- 截取整数部分
12      -- S:SELECT cast(-1.002 as int) value
13      SELECT trunc( - 1.002)   from dual;
14
15 -- 四舍五入（第二个参数表示精确到小数点位数）
16      SELECT round( 1.23456, 4) value from dual
17
18 -- e为底的幂
19      select exp( 1) from dual;
20
21 -- 取e为底的对数
22      -- S： log
23      select ln( 2.71828182845905) from dual;
24
25 -- 取10为底的对数
26      SELECT log( 10, 100) value from dual;
27      -- S：log10(100)
28
29 -- 求平方
30      -- S:square(4)  --16
31      select power( 4, 2) from dual;
32
33 -- 求立方
34      select power( 4, 3) from dual;
35
36 -- 求平方根
37      select sqrt( 4) from dual;
38
39 -- 求随机数
40      -- S:rand()
41      select sys.dbms_random.value( 0, 1) from dual;
42
43 -- 取符号
44      SELECT sign( - 8) value from dual;
45      select sign( 0) from dual;
46      select sign( 8) from dual;
47
48 -- 求集合的最大值
49      -- S: max()
50      select greatest( 1, 2, 3, 5, 6) from dual;
51
52 -- 求集合最小值
53      -- S: min()
54      select least( 1, 2, 3, 5, 6) from dual;
55
56 -- 求字符对应的ASCII
57      select ascii( ' a ') from dual;
58
59 -- 求数值对应的字母
60      -- S:SELECT char(97) value
61      SELECT chr( 65) value from dual
62
63 -- 连接
64      -- s:select 'hello'+'world'
65      select ' hello ' || ' world ' from dual;
66      select concat( ' hello ', ' world ') from dual;
67      select concat(concat( ' hello ', ' world '), ' china ') from dual;
68      select ' hello ' || ' world ' || ' china ' from dual;
69
70 -- 求字串位置(位置从1开始，第三个参数表示从第几个位置开始查找)
71      -- S:SELECT CHARINDEX('s','sdsq',2) value
72      select instr( ' sdsq ', ' s ', 1) value from dual
73
74 -- 截取字符串（第二个参数表示从哪个位置开始截取，第三个参数表示截取字符串的长度）
75      -- S:SELECT substring('abcd',2,2) value
76      SELECT substr( ' abcd ', 2, 3) value from dual;
77
78 -- 字串替换
79      -- S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value
80      SELECT Replace( ' abcdef ', ' bcd ', ' ijklmn ') value from dual
81
82 -- 字串找不到的时候不会替换
83      SELECT Replace( ' abcdef ', ' bdd ', ' ijklmn ') value from dual
84
85 -- 求长度（字符长度，每个空格占一个长度）
86      -- S:len,datalength
87      select length( ' adfadfad中 ') from dual;
88
89      select length( ' adfadfad中    ') from dual; 包括空格;
90
91      select lower( ' WSPA ') from dual; -- 转化为小写
92      select upper( ' aaaa ') from dual; -- 转化为大写
93
94 -- 单词首字母大写
95      SELECT initcap( ' abcddsaf df ') value from dual
96
97 -- 左补空格(将字符整个长度控制为14，如果不够再左边补足空格)
98      -- S:SELECT  space(10)+'abcd' value
99      SELECT  length(lpad( ' abcd ', 14)) value from dual -- 在左边补10个空格
100
101
102 -- 右补空格
103      SELECT  length(rpad( ' abcd ', 14)) value from dual
104
105      select length(rpad(lpad( ' aaaa ', 14), 12)) from dual;
106
107 -- S:ltrim,rtrim
108      select ltrim( '   abc ') value from dual;
109      -- 去掉左边的空格
110      select rtrim( ' abc    ') value from dual;
111     -- 去掉右边空格
112      select trim( '   a   b   c    ') value from dual; -- 去掉两边空格
113
114 -- 求当前系统时间
115      -- S:SELECT getdate() value
116      SELECT sysdate value from dual    -- 求当前日期
117      select systimestamp from dual;   -- 求当前时间精确到毫秒
118 -- dual它是一张伪表，这个表只有一行一列，一般进行无表操作的时候使用
119
120      select * from dual; -- 我们不对它进行增删改
121
122 -- S:SELECT convert(char(10),getdate(),20) value
123      SELECT trunc(sysdate) value from dual; -- 返回年月日
124      SELECT to_char(sysdate, ' yyyy-mm-dd hh24:mi:ss ') value from dual; -- 格式化固定输出形式
125
126 -- S:SELECT convert(char(8),getdate(),108) value
127     O: SELECT to_char(sysdate, ' hh24:mi:ss ') value from dual
128      year yy, yyyy
129     quarter qq, q (季度)
130      month mm, m (m O无效)   -- o 表示oracle
131     dayofyear dy, y (O表星期)
132      day dd, d (d O无效)
133     week wk, ww (wk O无效)
134     weekday dw (O不清楚)
135     Hour hh,hh12,hh24 (hh12,hh24  S无效)
136     minute mi, n (n  O无效)
137     second ss, s (s  O无效)
138     millisecond ms (O无效)
139
140 -- 求当月的最后一天
141      select last_day(sysdate) from dual;
142      SELECT LAST_DAY( ' 01-3月-2011 ') value from dual
143
144 -- 字符串转化成日期
145      SELECT to_date( ' 2004-01-05 22:09:38 ', ' yyyy-mm-dd hh24-mi-ss ') vaule FROM DUAL;
146
147 -- 如果直接相减，返回的是天数
148      select (sysdate -to_date( ' 2011-03-07 ', ' yyyy-mm-dd ')) * 24 * 60 * 60 vaule FROM DUAL;
149
150 -- 根据差值求新的日期
151      -- S:SELECT dateadd(mi,8,getdate()) value
152      SELECT sysdate + 8 / 60 / 24 vaule FROM DUAL;   -- 在当前日期上加8分钟

--add_months(原来日期,月份数量) 结果为最终相加后的日期

--不同时区时间

    SELECT New_time(sysdate,'ydt','gmt' ) vaule FROM DUAL;
    AST ADT 大西洋标准时间
    BST BDT 白令海标准时间
    CST CDT 中部标准时间
    EST EDT 东部标准时间
    GMT 格林尼治标准时间
    HST HDT 阿拉斯加?夏威夷标准时间
    MST MDT 山区标准时间
    NST 纽芬兰标准时间
    PST PDT 太平洋标准时间
    YST YDT YUKON标准时间

--DML语句select语句

--SQL语句分类：DDL、DCL、TCL、DML

select 字段，表达式，*，函数（包括聚合函数），子查询，dinstict,伪列（rowid,rownum），别名，常量
from 表，子查询，视图（left join 、right join）
where 条件表达式 , 函数（不包括聚合函数），子查询，模糊查询条件
group by 字段
having 聚合函数表达式
order by 字段，表达式

--注意:

      --1、所有的子句的前后顺序是固定的，可以没有部分子句
      --2、我们要掌握每条子句后面能接的内容
      --3、oracle中没有top关键字

--select子句后面能接的内容如下：

View Code

1 -- 1、字段,*
2      select empno,ename from emp;
3      select * from emp;
4
5 -- 2、表达式
6      select 1234* 5678 from dual;
7
8 -- 3、聚合函数：max()   min()  avg()  count()  sum()
9                   select max(sal) from emp;
10
11 -- 4、子查询
12      select * from student;
13
14      select s.*,( select ' 取西经 '   from dual) as work from student s;
15
16 -- 5、常量
17      select s.*, ' 取西经 ' as work from student s;
18
19 -- 6、distinct
20      select distinct(deptno) from emp;
21
22 -- 7、rowid,rownum
23      select empno,ename,rowid,rownum from emp order by sal desc;
24
25 -- 8、别名( 1、方便我们理解每个字段所表述的含义， 2、自身连接查询)
26      select empno " 编号 ",ename as " 姓名 " from emp   --别名在oracle中要使用 ""
27      select * from emp e;

--模糊查询

    (1)like + 通配符
       % 任意多个字符
       _ 任意一个字符
    select * from emp where ename like '%L%';
    select * from emp where ename like 'CLAR_';

    (2)between ...and ...

    (3)in、 not in

    (4)is null 、is not null

--查询出每个员工的平均薪水大于1500的部门编号,以及平均工资
    select deptno,avg(sal)
    from emp
    group by deptno
    having avg(sal)>1500

--分析函数(排位函数)
    rank()       相同的值排位相同，序号跳跃
    row_number() 相同的值排位不相同，序号连续
    dense_rank() 想同的值排位相同，序号连续

    select * from emp;

--语法
rank() over (order by sal desc)

View Code

1      select empno,ename,sal,rank() over (order by sal desc) as rankSal
2      from emp;
3
4      select empno,ename,sal,row_number() over (order by sal desc) as rankSal
5      from emp;
6
7      select empno,ename,sal,dense_rank() over (order by sal desc) as rankSal
8      from emp;

--partition by deptno 首先按部门编号分组，类似于group by 但是不能去使用group by替代