java常用的数据库_JAVA常用知识总结（十一）——数据库（一）

最新推荐文章于 2024-04-16 11:22:30 发布

Camellia Yang

最新推荐文章于 2024-04-16 11:22:30 发布

阅读量226

点赞数

文章标签： java常用的数据库

本文链接：https://blog.csdn.net/weixin_33050117/article/details/114711017

版权

项目中用到的不常见sql语法

1：空值不在前的排序

select a.* from WZX_SCZY A order by SCZY_START_TIME desc nulls last (不加nulls last的时候空值会在前面) --oracle

2：Oracle数据库查询两个表中差异值的优秀解法(MINUS)

表A中记录为1 2 3 9

表B中记录为2 3 4 5

现在要查询出1 9，可以用下面三个语句

(1)select a.num from A a where a.num not in (select b.num from B b); -- 注意：如果 not in 里面的值为null 则返回空

(2)select a.num from A a minus select b.num from B b -- 注意：(这里a.num和b.num前不需要加distinct也会自动列出不重复的数据，要求两个查询出的列数相同，字段类型相同)

(3)select a.num from A a where not exists (select b.num from B b where b.num=a.num) -- 用时最短

SELECT A.Pipe_No AS GX_TDCLBH FROM WZX_JC_FFCPSD A GROUP BY A.Pipe_No

MINUS

select a.gx_tdclbh from WZX_GX_GX a

3：两个ID相同时间，不同的数据,怎么取出时间最大的那个值

优秀解法：

select a.* from 表名 a,

(select id,max(time) time from 表名 group by id) b

where a.id=b.id and a.time=b.time;

4：如果该字段前面两位是DE 就去掉

select replace(a.pipe_diameter,'DE','') from risk_pe_line a where substr(a.pipe_diameter,0,2) = 'DE';

ps:instr(a.pipe_diameter,'DE')>0 意思是包含DE 但不能确保DE就在前两位

5:oracle 的行转列

unpivot 是oracle 11g 后推出的功能

select sum(decode(PIPE_ADDR,'正常',1,0))管道位置异常,sum(decode(PIPE_ZX,'正常',1,0))管道走向异常,

sum(decode(PIPE_ZX,'正常',1,0))管道埋深异常,sum(decode(GDLL_DESC,'有',1,0))管道裸露,

sum(decode(PIPE_ZX,'有',1,0))施工破坏,sum(decode(PIPE_ZX,'有',1,0))高后果区 from (#MAIN_SQL#))A

unpivot (DATA_RESULT for ZLSC in(管道位置异常,管道走向异常,管道埋深异常,管道裸露,施工破坏,高后果区)

其中 DATA_RESULT 和 ZLSC都是列名，ZLSC对应 in 后面的数据 DATA_RESULT对应sum后的值

6:oracle的add_months函数

oracle add_months(time,months)函数可以得到某一时间之前或之后n个月的时间

如 select add_months(sysdate,-6) from dual;

该查询的结果是当前时间半年前的时间

7:sql把特定数据行排在最前面

第一法】

select * from table where name='D'

UNION ALL

select * from table where name<>'D'

第二法】

SELECT CASE WHEN [name]='D' THEN 0 ELSE 1 END FLAG,* FROM TABLE

order by flag asc

第三法】

不改变原sql的前提下，自定义的排序后面加上

order by case when RISK_APP_STATE =5 then 1 else 2 end asc

order by decode(col,'一',1,'二',2,'三',3,'四',4,'五',5,'六',6,'七',7,'八',8,'九',9,'零',0) (此方法最好)

8:PARTITION:分割

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组(此处的分组是多个的分组。切记)，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的) partition 分割，划分

与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码．

---查询所有姓名，如果同名，则按年龄降序

SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC) FROM TEST_Y;

----通过上面的语句可知，ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)中是按照NAME字段分组，按AGE字段排序的。

----如果只需查询出不重复的姓名即可，则可使用如下的语句

SELECT * FROM (SELECT NAME,AGE,DETAILS ,ROW_NUMBER() OVER( PARTITION BY NAME ORDER BY AGE DESC)RN FROM TEST_Y )WHERE RN= 1;

9：for update nowait 与 for update 的目的和区别

目的：

锁定表的所有行，排斥其他针对这个表的写操作。确保只有当前事务对指定表进行写操作。

区别：

别的事务要对这个表进行写操作时，是等待一段时间还是马上就被数据库系统拒绝而返回.制定采用nowait方式来进行检索，所以当发现数据被别的session锁定中的时候，就会迅速返回ORA-00054错误，内容是资源正忙, 但指定以 NOWAIT 方式获取资源。所以在程序中我们可以采用nowait方式迅速判断当前数据是否被锁定中，如果锁定中的话，就要采取相应的业务措施进行处理。

如何理解上面的话.

开启一会话 (就是开一个sqlwindow)

select empno,ename from emp where empno='7369' for update nowait ;

得到下面结果集:

empno ename

7369 smith

开启另一会话

select empno,ename from emp where empno='7369' for update nowait ;

返回RA-00054错误，内容是资源正忙, 但指定以 NOWAIT 方式获取资源

上面会话都提交commit;

10：start with connect by prior 递归查询用法

SELECT a.*,level