Oracle学习笔记（三）

小知识

oracle不支持boolean，可通过0/1代替,如果实体类的类型是boolean 数据库中的0/1会自动映射为false/true

不等于

不等于可以写成！=或者<>

||

字符串拼接

日期

oracle中mm是月份，mi是分钟和Java细微的差异

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual

select to_char(sysdate, 'yyyy') 年,
       to_char(sysdate, 'mm') 月,
       to_char(sysdate, 'DD') 日,
       to_char(sysdate, 'HH24') 时,
       to_char(sysdate, 'MI') 分,
       to_char(sysdate, 'SS') 秒,
       to_char(sysdate, 'DAY') 天,
       to_char(sysdate, 'Q') 第几季度,
       to_char(sysdate, 'W') 当月第几周,
       to_char(sysdate, 'WW') 当年第几周,
       to_char(sysdate, 'D') 当周第几天,
       to_char(sysdate, 'DDD') 当年第几天    
  from dual;

（+）外连接

oracle中的（+）是一种特殊的用法，（+）表示外连接，并且总是放在非主表的一方。

例如：

左外连接：
select A.a,B.a from A LEFT JOIN B ON A.b=B.b;
等价于:
select A.a,B.a from A,B where A.b = B.b(+);

DISTINCT

distinct用于返回唯一不同的值

DECODE

decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

如果条件等于值1就返回值1，等于值2就返回值2，都不是就返回缺省值。

CASE WHEN … THEN… END

CASE WHEN CHARGE_TYPE in(‘1’,‘2’,‘3’) THEN CHARGE_MONEY ELSE 0 END

CHARGE_TYPE如果是1或者2或者3就返回CHARGE_MONEY否则返回0

示例

SELECT CASE FRE
         WHEN '0' THEN
          '月度'
         WHEN '1' THEN
          '季度'
         ELSE
          '其他'
       END
  FROM (SELECT DISTINCT (IND.UPDATE_FRE) FRE
          FROM MEDICAL_REFORM_INDEX_TABLE IND
         WHERE IND.INDEXS_ID = 'e7f1b7d543e44b9ca3896552be2e7dfa'
         ORDER BY IND.UPDATE_FRE)
SELECT CASE 
         WHEN FRE= '0' THEN
          '月度'
         WHEN FRE in('2','3') THEN
          '季度'
         ELSE
          '其他'
       END
  FROM (SELECT DISTINCT (IND.UPDATE_FRE) FRE
          FROM MEDICAL_REFORM_INDEX_TABLE IND
         WHERE IND.INDEXS_ID = 'e7f1b7d543e44b9ca3896552be2e7dfa'
         ORDER BY IND.UPDATE_FRE)

多个case的使用可以进行 行转列的变换

BETWEEN… AND…

在什么之间，包含头尾

TO_CAHR

TO_CHAR (d|n, [fmt]) 格式化 日期 / 数值

select to_char(sysdate,'yyyy-mm-dd') from dual  

TO_DATE

TO_DATE (char ,[fmt]) 将 fmt模型格式的字符串 转换为日期型

select to_date('2019-01-12 15:05:21','yyyy-mm-dd hh24:mi:ss') from dual  
• 1

TO_TIMESTAMP

TO_TIMESTAMP(char,[fmt])将fmt转换为时间戳格式

TO_NUMBER

TO_NUMBER (char) 将 包含数字的的字符串转换为 数值型

TRUNC

TRUNC(d,[fnt]) 返回 指定日期截断为格式后的 日期值

TRIM

TRIM(str)删除字符串两边的空格

TRIM(’.’ from ‘…str…’) 删除str两边的.

INSTR

instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。

select instr('helloworld','l') from dual; --返回结果：3    默认第一次出现“l”的位置
select instr('helloworld','l',2,2) from dual;  --返回结果：4    也就是说：在"helloworld"的第2(e)号位置开始，查找第二次出现的“l”的位置

排序妙用：比如说返回的结果需要按照一定的顺序可以使用instr来做

order by instr(‘医师, 护理, 医技, 药剂, 行政后勤’, x);

字段x会按照instr中的顺序来排序

SUBSTR

SUBSTR(表达式，位置，长度)

Oracle 无左右取串函数，但可以使用变通方式完成。

左取串： SUBSTR(‘abcdefg’, 1, 3)

右取串： SUBSTR(‘abcedfg’, LENGTH(‘abcdefg’)-3+1, 3)

COUNT

count(1)其实是对每一行数据的求和，求有几行，而sum是对整个数据的求和。

数据库查询时，两张表中出现相同字段，需要给其中一个重命名

CONCAT

用于拼接字符串，concat（first_name,last_name）

WMONCAT(column)

wm_concat(column)函数实现字段合并，必须配合group by使用

例如：WM_CONCAT ( SOU.INDEX_ID || ‘:’ || SOU.INDEX_NAME || ) SYSTEM_INFO

||用于拼接字符串

REPLACE

REPLACE (原字段，“原字段旧内容“,“原字段新内容”)

如果没有新内容就把旧内容给删除。

TRANSLATE

语法：TRANSLATE(char, from, to)

可以用来排序，配合 ORDER BY使用

若from比to字符串长，那么在from中比to中多出的字符将会被删除。

translate('abcdefga','abc','wo')

返回值为wodefgw

该语句要将'abcdefga'中的'abc'转换为'wo',
            由于'abc'中'a'对应'wo'中的'w',
            故将'abcdefga'中的'a'全部转换成'w';
            而'abc'中'b'对应'wo'中的'o',
            故将'abcdefga'中的'b'全部转换成'o';
            'abc'中的'c'在'wo'中没有与之对应的字符,
            故将'abcdefga'中的'c'全部删除;

三个参数中有一个是空，返回值也将是空值。

replace与translate都是替代函数，只不过replace针对的是字符串，而translate针对的是单个字符。

ORDER BY

ORDER BY 的列，必须是出现在GROUP BY 子句里的列

GROUP BY 和 ORDER BY一起使用时，ORDER BY要在GROUP BY的后面。

分析函数

分析函数根据一组行来计算聚合值。这些函数通常用来完成对聚集的累积排名、移动平均数和报表计算

ROW_NUMBER () OVER (…)

SELECT ROW_NUMBER() OVER(PARTITION BY ORGID ORDER BY ZHXGRQ DESC)

表示根据ORGID进行分组然后分组内部根据ZHXGRQ进行排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

ROW_NUMBER () OVER ([PARTITION BY colum] ORDER BY colum)

为有序组中的每一行返回一个唯一的排序值，序号由 ORDER BY 子句指定。

排序时从 1 开始，即使具有相等的值，排位也不同。

PARTITION BY colum 按列值进行区分，各分组内在进行排序。

例如：（1，2，3，4，5）

RANK () OVER （…）

RANK () OVER ([PARTITION BY colum] ORDER BY colum)

计算一个值在一个组中的地位，由 1 开头，具有相等值得行排位相同，序数随后跳跃相应的数值。

例如：（1，2，2，2，5）

DENSE_RANK () OVER（…）

DENSE_RANK () OVER ([PARTITION BY colum] ORDER BY colum)

计算一个值在一个组中的地位，由 1 开头，具有相等值得行排位相同（可以并列），并且排位是连续的。

例如：（1，2，2，3，3）

HAVING

HAVING 搜索条件在进行分组操作（group by）之后应用, HAVING 可以包含聚合函数,如果 HAVING 中包含多个条件，那么这些条件将通过 AND、OR 或 NOT 组合在一起。

NVL

NVL函数的格式如下NVL(eExpression1, eExpression2),如果eExpression1为null就返回eExpression2

如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

fm9990.99

fm去掉字符串前面的空格

9990.99表示4位数字，千位百位十位无数字时不显示[标识9]，个位数[标识0]永久显示，小数没有不显示[标识9]

①其9代表：如果存在数字则显示数字，不存在则显示空格

②其0代表：如果存在数字则显示数字，不存在则显示0，即占位符。

如果数字位数超过4位则返回结果为####[符号#的个数由原字符串长度决定*/

通常与to_char一起使用：例如to_char(‘2342.89’,‘fm9999990.9999’)

ROUND

ROUND(m, n) 小数点后精度四舍五入

DUAL

DUAL是一张只有一个字段,一行记录的表他的存在,是为了操作上的方便.因为select 都是要有特定对象的.

ROWNUM

ROWNUM是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列,都是从1开始的，可以用来做分页。

SELECT ROWNUM FROM dual CONNECT BY ROWNUM <= 3

CONNECT BY PRIOR

CONNECT BY prior：一般用来查找存在父子关系的数据，也就是树形结构的数据

select * from table [start with condition1] connect by [prior] id=parentid

prior可以在等号左边也可以在右边，通过父亲找儿子，或者儿子找父亲

nulls first和nulls last

Nulls first和nulls last是Oracle Order by支持的语法把空值置前或者置后

如果Order by 中指定了表达式Nulls first则表示null值的记录将排在最前(不管是asc 还是 desc)

如果Order by 中指定了表达式Nulls last则表示null值的记录将排在最后 (不管是asc 还是 desc)

exists和not exists的用法

exists （sql 返回结果集，为真） 相当于in 通常用于两个表求交集

not exists (sql 不返回结果集，为真） 相当于not in 通常用于两个表求差集

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

表A（小表），表B（大表）

• select * from A where cc in (select cc from B)

效率低，用到了A表上cc列的索引；

• select * from A where exists(select cc from B where cc=A.cc)

效率高，用到了B表上cc列的索引。

start with connect by prior

select org_id,
       org_name,
       level,--层级
       CONNECT_BY_ROOT(org.org_id) ROOT,
       CONNECT_BY_ROOT(ORG.org_name) rootname
  from sys_org org
 start with org_id = '7244'
connect by prior org_id = parent_id

start with connect by prior是根据条件递归查询"树"，分为四种使用情况：

CONNECT_BY_ROOT(field) 返回当前节点的最顶端节点的某个字段

PRIOR表示上一条记录

• 查询结果自己所有的后代节点（包括自己）

start with 子节点ID=’…’ connect by prior 子节点ID = 父节点ID

• 查询结果自己所有的前代节点（包括自己）

start with 子节点ID=’…’ connect by 子节点ID = prior 父节点ID

• 查询结果自己所有的后代节点（不包括自己）

start with 父节点ID=’…’ connect by prior 子节点ID = 父节点ID

• 查询结果自己的第一代后节点和所有的前代节点

start with 父节点ID=’…’ connect by 子节点ID = prior 父节点ID

高级分组函数（rollup、cube、grouping sets）

https://blog.csdn.net/qq_34745941/article/details/82316682

group by (A, B) 是 对 A，B 共同 进行分组

group by grouping sets(A, B) 是对 A，B 单独 进行分组

group by rollup(A, B)，首先对 （A，B）进行 group by，然后对（A）进行 group by，最后对全表进行 group by

group by cube(A, B)，首先对 （A，B）进行 group by，然后对（A）进行 group by，然后对（B）,进行 group by，最后对全表进行 group by

GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。

创建索引

CREATE [UNIQUE] INDEX index_name ON table_name(column_name[,column_name…])

语法解析：

1. UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引。
2. index_name：指定索引名。
3. tabl_name：指定要为哪个表创建索引。
4. column_name：指定要对哪个列创建索引。我们也可以对多列创建索引;这种索引称为组合索引。

生成某月日期

SELECT--按日期
    TO_CHAR( TO_DATE( '2018-12-01', 'YYYY-MM-DD' ) + ROWNUM - 1, 'YYYY-MM-DD' ) resultDate 
  FROM
    DUAL CONNECT BY ROWNUM <= TO_DATE( '2018-12-31', 'YYYY-MM-DD' ) + 1 - TO_DATE( '2018-12-01', 'YYYY-MM-DD' ) 

生成指定年份（当前年的前几年）

select to_char(to_date('2019-10-11', 'yyyy-MM-dd'), 'yyyy') + rownum - 5 date_t
        from dual
        connect by rownum < 5