Oracle知识点(+)

最新推荐文章于 2024-07-01 10:26:34 发布

.NET跨平台

最新推荐文章于 2024-07-01 10:26:34 发布

阅读量480

点赞数

分类专栏： Oracle数据库文章标签： oracle 数据库

Oracle数据库专栏收录该内容

162 篇文章 28 订阅

订阅专栏

1.oracle中group by 和order by同时存在时

关键点：order by 的栏位必须在group by 中有；
例如：select name from TABLE group by name ,id order by id asc

2.Oracle数据库-where, group by, having, order by语句的执行顺序

    select xxx_name, count(*) as counter  
    from table_y  
    where where_condition  
    group by xxx_name  
    having having_condition  
    order by zzz

当我们看到一个类似上述的包含了where, group by, having， order by等关键字的SQL时，我们要首先要知道其执行顺序是怎样的，才能判断出其所表达的含义；

下面列出其执行顺序：

根据where子句选择行；
根据group by 子句组合行；
根据having子句筛选组；
根据order by子句中的分组函数的结果对组进行排序，order by必须使用分组函数或者使用Group by子句中指定的列；

下面看一个例子：

    select CategoryName, count(*), AVG(Rating)  
    from BOOKSHELF  
    where Rating>1  
    group by CategoryName  
    having CategoryName like 'A%'  
    order by count(*) desc

我们现在知道，其执行顺序如下：

1.基于Where Rating>1 筛选出符合条件的行；
2.基于group by CategoryName 对筛选的结果进行分组；
3.为每个CategoryName组计算Count()
4. 基于having CategoryName like 'A%'留下符合条件的组
5. 根据order by 的条件对剩下的行组进行排序，SQL中的count()也是分组函数

`oracle`日期时间型`timestamp`相关转换

1、获取系统时间的语句(ssxff6获取小数点后面六位)

select sysdate,systimestamp,to_char(systimestamp, 'yyyymmdd hh24:mi:ssxff6'),to_char(systimestamp, 'yyyymmdd hh24:mi:ss.ff6') from dual;

2、字符型转成timestamp

select to_timestamp('2011-09-14 12:52:42.123456789', 'syyyy-mm-dd hh24:mi:ss.ff') from dual;

3、timestamp转成date型

select cast(to_timestamp('2011-09-14 12:52:42.123456789', 'syyyy-mm-dd hh24:mi:ss.ff') as date) timestamp_to_date from dual;

4、date型转成timestamp

select cast(sysdate as timestamp) date_to_timestamp from dual;

5、两date的日期相减得出的是天数，而两timestamp的日期相减得出的是完整的年月日时分秒小数秒

select sysdate-sysdate,systimestamp-systimestamp from dual;

select extract(day from inter) * 24 * 60 * 60 + extract(hour from inter) * 60 * 60 + extract(minute from inter) * 60 + extract(second from inter) "seconds" from
(
select to_timestamp('2011-09-14 12:34:23.281000000', 'yyyy-mm-dd hh24:mi:ss.ff') -  to_timestamp('2011-09-14 12:34:22.984000000', 'yyyy-mm-dd hh24:mi:ss.ff') inter from dual
);

select extract(second from to_timestamp('2011-09-14 12:34:23.281000000', 'yyyy-mm-dd hh24:mi:ss.ff'))-extract(second from to_timestamp('2011-09-14 12:34:22.984000000', 'yyyy-mm-dd hh24:mi:ss.ff')) from dual;

注：所以，timestamp要算出两日期间隔了多少秒，要用函数转换一下。
to_char函数支持date和timestamp,但是trunc却不支持TIMESTAMP数据类型。

SQL的where执行顺序

1 mysql 从左到右.

一个原则，排除越多的条件放到第一个

例子：抄的。

SELECT … WHERE p.languages_id = 1 AND m.languages_id = 1 AND c.languages_id = 1 AND t.languages_id = 1 AND p.products_id IN (472,474)

这样查询需要20多秒，虽然在各个字段上都建立了索引。用分析Explain SQL一分析，发现在第一次分析过程中就返回了几万条数据：

WHERE p.languages_id = 1 ，然后再依次根据条件，缩小范围。

而我改变一下WHERE 字段的位置之后，速度就有了明显地提高：

WHERE p.products_id IN (472,474) AND
p.languages_id = 1 AND m.languages_id = 1 AND c.languages_id = 1 AND t.languages_id = 1

这样，第一次的条件是p.products_id IN (472,474)，它返回的结果只有不到10条，接下来还要根据其它的条件来过滤，自然在速度上有了较大的提升。

2 ORCAL

从右到左

sql中查询类型为int的字段，返回null的异常

项目中用mybatis3.x，用sql查询某个表类型为int的字段，那个表是空表，没有数据，结果抛异常了，原因是在对象中的属性为int，空表时,sql查询出来的是NULL，结果赋值时抛异常了。

org.apache.ibatis.binding.BindingException: Mapper method 'getSort' (xx.yy.cc.DxxDAO) attempted to return null from a method with a primitive return type (int).

抛这样的异常。

解决这个问题，是当查出来为NULL时，结一个默认值，如：0。

mysql：

SELECT IFNULL(MAX(SORT),0)AS sort FROM web_d_info

oracle:

SELECT nvl(MAX(SORT),0) as sort FROM web_d_info

Oracle的NVL函数用法

从两个表达式返回一个非 null 值。

语法

NVL(eExpression1, eExpression2)

参数

eExpression1, eExpression2

如果 eExpression1 的计算结果为 null 值，则 NVL( ) 返回 eExpression2。如果 eExpression1 的计算结果不是 null 值，则返回 eExpression1。eExpression1 和 eExpression2 可以是任意一种数据类型。如果 eExpression1 与 eExpression2 的结果皆为 null 值，则 NVL( ) 返回NULL.。

返回值类型

字符型、日期型、日期时间型、数值型、货币型、逻辑型或 null 值

说明

在不支持 null 值或 null 值无关紧要的情况下，可以使用 NVL( ) 来移去计算或操作中的 null 值。

select nvl(a.name,'空得') as name from student a join school b on a.ID=b.ID

注意：两个参数得类型要匹配

SELECT T.d_fdate,
       T.vc_zhcode,
       Nvl(SUM(T.f_fzqsz), 0)   f_price_b,
       Nvl(SUM(T.f_fzqcb), 0)   f_cost_b,
       Nvl(SUM(T.f_fgz_zz), 0)  f_gz_b,
       Nvl(SUM(T.f_fyzqsz), 0)  f_price_Y,
       Nvl(SUM(T.f_fyzqcb), 0)  f_cost_Y,
       Nvl(SUM(T.f_fygz_zz), 0) f_gz_Y,
       T.vc_source,
       SYSDATE  d_updatetime
FROM   gz_fund_gzb T

比如这样的判断就很重要啦，因为你不知道哪一行是 is not null 的，也不知道接下来是否要对这个单元格进行运算操作，因此，不能给列填 null，就给它一个 0 ，便于查看，也便于运算。

oracle `order by` 处理`null`值

适用情况：
oracle 对查询结果进行排序时，被排序的栏位存在null值，且要指定NULL值排在最前面或者最后面

关键字：Nulls First；Nulls Last

默认情况：null 默认为最大值（即：asc 升序<小–>大>，null值排在最后；desc 降序<大–>小>，null值排在最前面）

指定：

Oracle Order by支持的语法
指定Nulls first 则表示null值的记录将排在最前(不管是asc 还是desc)
指定Nulls last 则表示null值的记录将排在最后(不管是asc 还是desc)
语法举例：(Table：Tab_A 有部分空值的栏位Col_A)

select * from Tab_A order by Tab_A. Col_A (asc/desc) nulls first ------>null 值排在最前面
select * from Tab_A order by Tab_A. Col_A (asc/desc) nulls last ------>null 值排在最后面

其他方法：
在order by 的时候，用Nvl、NVL2 、Decode、case .....when....end;等函数对栏位的null值进行处理
例如：

select * from Tab_A order by NVL（Tab_A. Col_A,'abc' ） (asc/desc) ;

Oracle使用order by排序关于null值处理

select * from dual order by age desc nulls last

select * from test order by age asc nulls first

sqlserver 认为 null 最小。

升序排列：null 值默认排在最前。

要想排后面，则：order by case when col is null then 1 else 0 end ,col

降序排列：null 值默认排在最后。

要想排在前面，则：order by case when col is null then 0 else 1 end , col desc

解决oracle语句中含数字的字符串按数字排序问题

例如：

普通排序利用：order by 字段名 ASC

但是遇到有中文而且类型是varchar类型的结果就是这样

政采代(甲)字第0298号

政采代(甲)字第0421号

政采代(甲)字第1098号

政采代(甲)字第1267号(延续)

政采代(甲)字第1179号(延续)

但是我们要的结果应该是这样：


政采代(甲)字第0298号

政采代(甲)字第0421号

政采代(甲)字第1098号

政采代(甲)字第1179号(延续)

政采代(甲)字第1267号(延续)

解决办法：
利用oracle函数及正则表达式进行排序

order by to_number(regexp_substr(字段名,'[0-9]*[0-9]',1))

oracle将当前系统时间戳插入`timestamp`字段

--insert records
insert into userlogin_his(usrname,logintime) values('test',to_timestamp(to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS'));
insert into userlogin_his(usrname,logintime) values('test1',to_timestamp(to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS.FF'));
insert into userlogin_his(usrname,logintime) values('test2',systimestamp);
insert into userlogin_his(usrname,logintime) values('test3',sysdate);

--query records
select usrname,to_char(logintime,'YYYY-MM-DD HH24:MI:SS.FF')  from userlogin_his;

USRNAME    TO_CHAR(LOGINTIME,'YYYY-MM-DDHH24:MI:SS.FF')

test    2017-03-30 18:09:59.000000

test1    2017-03-30 18:13:27.505000

test2    2017-03-30 18:28:16.521000

test3    2017-03-30 18:29:48.000000

Oracle 查询：一小时内、一天内、一周内等

有时，我们经常会遇到需要我们查询一小时内、一天内、一周内等时间段的数据。这时，就可以用Oracle提供的sysdate。

-- 查询一小时内的数据

select * from XXXX_Table where sysdate-1/24 <= ApplyTime

-- 查询一天内的数据

select * from XXXX_Table where sysdate-1 <= ApplyTime

-- and so on.....

ApplyTime在Oracle是以Timestamp的类型存储,也就是你要比较的日期。

ORACLE修改默认时间格式的四种方式：`NLS_DATE_FORMAT`

改变ORACLE -NLS_DATE_FORMAT中时间显示格式的显示有以下四种方式：

1.可以在用户环境变量中指定（LINUX)。
在用户的.bash_profile中增加两句：

export NLS_LANG=AMERICAN ---要注意这一句必须指定，不然下一句不生效。
export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'

2.在SQLPLUS的glogin.sql文件增加一句：

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

3.直接修改当前会话的日期格式：

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

4.修改数据库的参数，需要重启数据库后生效

SQL> alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;

oracle计算两行差值

Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。

这种操作可以代替表的自联接，并且LAG和LEAD有更高的效率。

SELECT col_1,col_2,col_2-LAG(col_2,1,0)OVER(ORDER BY B) AS C

oracle使用`instr`或`like`方法判断是否包含字符串

首先想到的就是contains，contains用法如下：

select * from students where contains(address, 'beijing')

但是，使用contains谓词有个条件，那就是列要建立索引，也就是说如果上面语句中students表的address列没有建立索引，那么就会报错。

好在我们还有另外一个办法，那就是使用instr，instr的用法如下：

select * from students where instr(address, 'beijing') > 0

另外，还有个笨办法，那就是使用like，说到这里大家应该知道怎么做了吧：

select * from students where address like '%beijing%'

`insert`批量插入数据，跳过已存在记录

insert into tree(id,name,pid) select '101','test','10' from dual 
where not exists(select 1 from tree where id='101');

insert when (not exists (select 1 from tree where id='101')) 
then into tree(id,name,pid) select '101','test','10' from dual;

ORACLE大量数据`insert into`的速度提高

ORACLE大数据insert可以使用下面hint来提高SQL的性能

insert /*+ append parallel(a, 4) nologging */ 
into target_table a 
select /*+ parallel(b, 4) */ * 
from source_table b;

APPEND的作用是在表的高水位上分配空间,不去寻找 freelist 中的free block , 直接在table HWM 上面加入数据；
nologging 会大量减少日志；
parallel 并行。

`sql`语句使用`case when`实现列排序

根据correctstatus列的三种状态值排序
直接示例代码：

select * from GIS_FEATURECORRECTION
order by case correctstatus
            when '待审核' then 1
            when '待处理' then 2
            when '已完成' then 3
            else 4
          end,
          gis_featurecorrection_pkid desc;