Oracle数据库SQL相关

一、简单SQL语句查询（都是很简单常见的）
1、SQL查询 Oracle中没有left和right函数
 ①简单截取字段值一般用substr()函数
     substr(xx,1,3) 左到右 截取三个字符 (xx指数据库字段)
     substr(xx,-1)  右到左 截取一个字符（负几就是截取几个）
     substr(字段x,instr(字段x,'_',1))   截取字段x中，第一个“_”出现的之后的字符（包括_）
     substr(substr(字段x,instr(字段x,'_',1)),2,3)    截取字段x中，第一个“_”出现的位置往后数从第二 位开始截取3个字符长度
     例： xxx='fs8kwer8fs9375'
         substr(xxx,-4,4) = 9375    负几就是从右边第几位起往后截取 ，substr(xxx,-5,4)=s937

     新学到的 split()函数 
         split(字段，2) （应该是切取第二个分隔符前,第一个分隔符后的字符串的意思。分隔符：默认逗号，也可以指定字符或字符串） 
         split(handle,2) ----> 例句： select handle,split(handle,2) as handle from custom_fields

 ②截取字段值其中的一部分 结合instr()函数
     substr(xxx,instr(xxx,'_',1,1)+1,instr(xxx,'_',1,2)-instr(xxx,'_',1,1)-1)
     意为：截取字段xxx 第一个"_" 和第二个"_"之间的字符 如:字段值为 abc_ccc_de 截取字符为 ccc
    ( instr(xxx,'_',1,1)+1：字段xxx从第一位字符开始第一次出现"_"的长度+1，
      instr(xxx,' ',1,2)-instr(xxx,'_',1,1)-1 即为字符“_”之后的第一个字符开始截取字符到第二个“_”为止，但不包括第二个“_”

2、replace()函数
   replace(xxx,substr(xxx,3,2),'ww') 将字段xxx从第3位开始截取2个字符替换成 'ww'
   replce(xxx,substr(xxx,3),'ww')  将字段xxx从第3位开始之后的字符全部替换成 'ww'

3、拼接字段
    字段1||'_'||字段2  _为连接字符， 
    字段1||chr(10)字段2 使两个字段的值呈上下换行显示 （暂时只知道这些）

4、日期相关
    注意字段类型，转成date类型直接相减
　　 ceil((to_date('2019-05-12 00:00:12','yyyy-mm-dd hh24:mi:ss')-to_date('2019-05-12 00:00:00','yyyy-mm-dd hh24:mi:ss'))*24*60*60)　
    结果：12s  24*60*60得到的结果为秒
    日期+8个小时间隔
    例句：SELECT TO_CHAR(字段,'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(字段,INTERVAL+'8'HOUR,'YYYY-MM-DD HH24:MI:SS') from 表名

5、关联表查询
   左关联 LEFT JOIN 返回左表所有的行，即使右表中没有匹配的行，右表中无数据匹配默认NULL值与左表匹配
   同理，右关联 RIGHT JOIN 与之相反
　 INNER JOIN  内连接 返回左右两张表连接字段相同的行
   图解看  https://blog.csdn.net/weixin_41796956/article/details/85044152
   详细解释，看原文https://www.cnblogs.com/pcjim/articles/799302.html

6、插入、更新、删除
    插入语句
    insert into 表名 (列1,列2,...) values (值1,值2,...)  
    更新语句
    update 表名 set 列名 = '新值' where 列名 = '旧值'
    删除语句
    delete from 表名 where 列名 = '值'

7、排序和分组
   select * from 表名 order by 列名 asc(desc)     (asc 升序 ，desc降序  oracle默认null值为最大值，排序时注意) 

   select A,COUNT(B) from 表名 group by A     （使用group by  语句，列名只能是分组属性（A）+集函数（COUNT(B)））
   还有多个列属性进行分组
   select A，B,COUNT(B) from 表名 group by A,B

　　写个例子

selecT CST_PRT_CATEGORYCODE,count(*) 
from PLM_MES.PLM_MES_DXX 
where  CST_PRT_CATEGORYCODE IN ('7F','7G','BF')
group by CST_PRT_CATEGORYCODE having  count(*) > 1

　　结果：

8、oracle字段长度修改
　　alter table 表名 modify  字段 varchar2(长度);

9、dbLink相关知识
 　查询数据库表，有时需要跨数据库进行查询，此时就要用到dbLink，记录下简单用法
   ①首先查询当前数据库是否创建有dblink
　　select * from ALL_DB_LINKS where username='用户名';   --where 后username是当前用户（可不写，即查询所有用户创建了哪些dbLink）

如图是用户名为WIP创建的两个dbLink，分别为DEV、ODS，这样进行跨数据库查询时直接在跨数据库表名后+@DEV或@ODS即可

　　　举个例子：select name,age,school from A where name in (select name from B@DEV ) ---简单例子，根据实际情况自己运用　

②如果查询之后没有创建需要连跨数据库的dbLink

　　　　此时需要执行查询

　　　　select * from user_sys_privs t where t.privilege like upper('%link%

　　　　看是否有权限创建dbLink；（无权限就需要去联系管理员给你开了）

　　　　假设有：

　　　　则创建dbLink（百度一下，你就知道）

mybatis 默认字段更新插入数据为非空，解决：

10 数据库表新增一个非空字段方法（update赋值后，可以在数据库可视化工具中，直接勾选非空，保存即可。。。听说Oracle11版本之后新建非空字段可以直接保存，不知真否？）

　　先 UPDATE tablename SET 字段名=' ' where 字段名 ='非空值';

　　然后添加默认约束

ALTER TABLE 表名 MODIFY 字段名 DEFAULT '非空值' NOT NULL;

数据库相关知识（三大范式、五大约束了解下）

11 Oracle 数据库查询日期格式转换

　WHERE SOC.SITE = '2011'

AND SOC.SCHEDULE_CATEGORY = 'PCR'

AND TO_CHAR(SOC.END_DATE_TIME,'yyyy-mm-dd HH24:MI:SS') >= '2021-08-02 02:05:00'

AND TO_char(SOC.START_DATE_TIME,'yyyy-MM-DD HH24:MI:SS') <= '2021-08-12 14:30:00'

12 Oracle 设置字段值可以为NULL

ALTER TABLE 表名 MODIFY 字段 NULL

13 Oracle 数据库复制表数据

使用可视化工具DBever

先在目标数据库创建表，右击选择导入数据，选择需要数据的数据库表，开始导入即可（创建复制表时，注意表结构需一致）

---------Oracle12版本表名长度30个字符串长度，超过会报标识符长度过长错误

posted @ 2020-07-03 16:57 懵逼树下你和我阅读(26) 评论(0) 收藏举报

刷新页面返回顶部

懵逼树下你和我

Oracle数据库SQL相关

公告