oracle学习

oracle

目录

一、oracle数据定义

二、oracle数据类型

三、oracle修改数据

四、oracle数据查询

五、oracle连接表

参考oracle学习网站：https://www.yiibai.com/oracle/

---

一、oracle数据定义

1、数据表示

返回顶部目录

---

二、oracle数据类型

1、数据表示

返回顶部目录

---

三、oracle修改数据

1、选择性插入

选择插入 INSERT INTO SELECT   https://www.yiibai.com/oracle/oracle-insert-into-select.html
注意：select的限制条件，大表需走索引避免全表扫描

合并数据
  merge into target_table A using
  (select id1,id2,id3 from source_table) B
  ON (A.id1=B.id1 and B.id2=B.id2)
   WHEN MATCHED THEN
            UPDATE SET 
            A.id3=B.id3          
   WHEN NOT MATCHED THEN
            insert(A.id1,A.id2,A.id3)
            values(B.id1,B.id2,B.id3)
            
对于目标表中的每一行，Oracle都会评估搜索条件：
1）如果结果为true，则Oracle使用源表(source_table)中的相应数据更新该行。
2）如果任何行的结果为false，则Oracle将源表(source_table)中相应的行插入到目标表(target_table)中。

ORA-30926 错误：
ON匹配时，Merge 操作要求源表和目标表之间的连接条件能够唯一匹配到对应的行，否则就无法进行稳定的行匹配。
去除ON匹配重复的办法：
1）student_id重复   + where rowid  in( SELECT MIN(rowid) FROM source_table where 1=1  GROUP BY student_id) )
2）  

返回顶部目录

---

四、oracle查询数据

1、数据排序

over partition by  例子：把每个人学生的成绩按照升序排名 （思路:根据学生姓名分组 根据每个人成绩排序）   
 select *,ROW_NUMBER() over( partition by Name order by Score )排名 from UserGrade  
          https://www.cnblogs.com/xiaohuizhenyoucai/p/10636747.html
        分组排序后，取某分组最高的（或者前几）   https://blog.csdn.net/weixin_44912883/article/details/107297736

order by 默认asc从小到大，desc从大到小  
Oracle自定义排序：order by decode(A, '2', 1, '1', 2, '4', 3, '3', 4, '0', 5);  
                 根据字段A排序，值为'2'的排序为第一位，不在这个范围内的，会显示其他

2、列转行、行转列

行表：

列表：

-- 行转列
with temp as(
select '四川省' nation ,'成都市' city,'第一' ranking from dual union all
select '四川省' nation ,'绵阳市' city,'第二' ranking from dual union all
select '四川省' nation ,'德阳市' city,'第三' ranking from dual union all
select '四川省' nation ,'宜宾市' city,'第四' ranking from dual union all
select '湖北省' nation ,'武汉市' city,'第一' ranking from dual union all
select '湖北省' nation ,'宜昌市' city,'第二' ranking from dual union all
select '湖北省' nation ,'襄阳市' city,'第三' ranking from dual
)
select * from temp
pivot (max(city) for ranking in ('第一' as 第一,'第二' AS 第二,'第三' AS 第三,'第四' AS 第四));

-- 列转行
with temp as(
select '四川省' nation ,'成都市' 第一,'绵阳市' 第二,'德阳市' 第三,'宜宾市' 第四  from dual union all
select '湖北省' nation ,'武汉市' 第一,'宜昌市' 第二,'襄阳市' 第三,'' 第四   from dual
)
select * from temp
unpivot (name for title in (第一,第二,第三,第四))

返回顶部目录

---

五、oracle连接表

1、数据表示

返回顶部目录

1）dual 是 Oracle提供的最小的工作表，只有一行一列，具有某些特殊功用，经常当做一个中间表使用
 2）Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
       Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
       https://www.cnblogs.com/yuanchaoyong/p/6213885.html
3）merge命令可以在一个SQL语句中对一个表同时执行inserts和updates操作，使用merge语句，常用于对指定的两个表执行合并操作，语法为“MEGER INTO 合并目标表 USING 合并源表 ON 条件”
      https://m.php.cn/oracle/489601.html
4）PreparedStatement是预编译的,对于批量处理可以大大提高效率. 也叫JDBC存储过程
     https://blog.csdn.net/nnzhuilian/article/details/86241787
5）with as的用法相，当于创建一个临时表。语法为“with temptablename([字段列表]) as (select ...)”
    https://www.php.cn/oracle/489322.html
6）listagg() WITHIN GROUP () 行转列函数的使用。listagg(列名,' 分割符号') within group(order by 列值被拼接的顺序)    如果列名里面有重复的，我们可以用  from（distinct ） 先进行查重
    https://www.cnblogs.com/mxh168/p/13962415.html
7）decode函数的使用。decode(条件，值1，返回值1，值2，返回值2，...值n,返回值n，缺省值) 。当“条件”等于“值1”的时候，显示“返回值1”，等于“值2”的时候，显示“返回值2”，依次类推，如果都不满足，则显示“缺省值”即默认值。
     https://blog.csdn.net/super_DuoLa/article/details/115197493
8）NVL函数。NVL(string1,replace_with)。当第一个参数（string1）为空时，返回第二个参数（replace_with），当第一个参数（string1)不为空时，则返回第一个参数（string1）。
     https://blog.csdn.net/qq_18335837/article/details/99676539
9）substr函数，截取字符串函数。语法为“substr(string string,int a,int b)”或“substr(string string,int a)”   a与b分别表示截取的位置、截取字符串的长度（a = 0或者1都是从第一个位置截取，如果b省略表示从a的位置截取到结束）
     substr(STRING，-2)表示从倒数第二个开始，截取到结束
     https://m.php.cn/oracle/489409.html
10）SQL部分执行。选中SQL块，F9就可以运行；断点分析法：每到一处，就select * 一次  
11）case when then 用法。第一种用法，对表中字段可能出现的值，作出对应的处理。第二种用法，对表中字段可能出现的值，做出条件判断，再做出对应的处理
       https://blog.csdn.net/baomingshu/article/details/111557873
12）Oracle数据导出为excel，点击查询出来的结果->导出->格式->选择excel 95-2003(xls)->文件选择到D盘下的non_Documents里面。然后从开发电脑复制到办公电脑小绿人上面发送。
13）oracle 中的（+）是一种特殊的用法。放在右边表示左连接。https://blog.csdn.net/qq_39564789/article/details/121795000
14）trunc函数trunc(sysdate,'HH') 是取当前系统时间，舍去分秒。+1就是加上一天、+1/24 就是加上一小时。   https://blog.csdn.net/haiross/article/details/12837033、https://blog.csdn.net/cuiyan1982/article/details/46545475


17) 几种连接举例说明：https://blog.csdn.net/weixin_49561506/article/details/127712487
     https://www.freesion.com/article/75961459882/
     多表fulljoin问题https://blog.csdn.net/qq_40772429/article/details/120188145
18）--建索引   CREATE INDEX TB_JC_MIXIN_LOG_IDX3 ON TB_JC_MIXIN_LOG (SAVE_DATE) ONLINE
    3索引：create index tb_index on tb_aa(line_one,line_two,line_three) online
19） exists 与 in 区别联系。n是先执行子查询 把查询结果返回到主查询作为主查询的条件；exists是先执行主查询，然后对每一条数据进行筛选，子查询返回的值相当于“是否”  
https://blog.csdn.net/weixin_42516051/article/details/116437929
20） lpad( string, padded_length, [ pad_string ] )；
   　string ：准备被填充的字符串；
　　padded_length：填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;
      pad_string：填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。
https://www.cnblogs.com/space-place/p/5146116.html
21) group by。 from table group by A,B，可以引入临时表先考虑A,B https://blog.csdn.net/qq_41059320/article/details/89281125

1）  java.sql.SQLException: ORA-00001: 违反唯一约束条件 (SSM.SYS_C005444)
    表中的各个字段的一些约束，如非空或唯一

问题一：
select * from (
select null a from dual
) where a in (null,‘aa’) ;

select * from (
select ‘aa’ a from dual
) where a in (null,‘aa’) ;