Oracle数据库表的查、增、删、改SQL操作方法（二）

1. select count( )语句的优化

        一般都是直接这样写，select count(*) from tablename t，最近数据库比较忙，这样写执行速度非常慢。

       count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加1，否则不加。最后返回累计值。所以，count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

      这样写就会非常快：count(主健或者其他)，count(1)或者count(col)，统计第一个字段或者某一个字段，对于null值不统计。

select count(t.key) from tablename t

2. in、exists、not in 、not exists的区别

        exists是用loop的方式，所以，循环的次数对于exists影响大：外表记录数越少效率越高，内表就无所谓；而in用的是hash join，内表越小效率越高，若内表和外表都很大，in方式效率就会很低。

        not exists查询，外表存在空值，存在空值的那条记录最终会输出，内表存在空值对查询结果没有影响。

        not in查询，外表存在空值，存在空值的那条记录最终将被过滤，其他数据不受影响；内表存在空值将导致最终的查询结果为空。

        not in 查询，内外表都进行全表扫描，没有用到索引； 而not extsts 的子查询依然能用到表上的索引

        一般来说，not in语句比not exists语句效率差很多，尽量用not exists就好了。 not exists语句是一个简单的两表关联，内表与外表中存在空值本身就不参与关联，在CBO(基于成本的优化器)中常用的执行计划是hash join，所以它的效率很好。

        in和exists需要具体情况具体分析

-in 用法
select a.col from a where a.id in (select b.id from b )

-exists 用法
select a.col from a where exists (select b.id from b where a.id = b.id)

3.查看某字段在数据库中的内码

select dump(t.col) from table t

-以十六进制显示内码
select t.col,dump(t.col,16) from table t

4. 两表联合查询之count() 、group by 用法

        两个表A和B，查询表B中与A有关联的数据，并统计每一条关联的总数

select count(substr(t.gbcode,0,12)),substr(t.gbcode,0,12)
       from D_REGION_WG t, D_REGION_COMMUNITY b
       where b.code= substr(t.gbcode,0,12) and t.lv='6'  group by substr(t.gbcode,0,12)

5.两边联合查询之order by asc(升序)、desc（降序）用法

-升序
select b.col,a.col from D_REGION_WG a,D_REGION_COMMUNITY b 
       where b.code=substr(a.gbcode,0,12) and a.lv='6' order by to_number(b.id) asc

-降序
select b.col,a.col from D_REGION_COUNT_WG a,D_REGION_COMMUNITY b 
       where a.com_code=b.code order by to_number(b.id) desc

6.时间日期字段（date）字段的查询

select * from D_HZ_DZXZ_20180819 t 
    where t.lastupdatedtime between to_date('2018-08-01','yyyy-MM-dd') and to_date('2018-09-01','yyyy-MM-dd')

        有些日期字段是VARCHAR2类型，此时如果使用上面的语句，会报错：ORA-01861：文字与格式字符串不匹配错误解决

-时间戳修改为字符串格式

select to_char(t.timestamp,'yyyy-mm-dd hh24:mi:ss') from table t

       要先把字符串类型的日期字段修改为date类型，用to_date函数：

————针对字符串类型的日期字段,假设t.lastupdatedtime格式是varchar2(2018-07-20 10:42:53)

select * from D_HZ_DZXZ_20180819 t 
    where to_date(t.lastupdatedtime,'yyyy-MM-dd hh24:mi:ss') 
    between to_date('2018-08-01','yyyy-MM-dd') and to_date('2018-10-01','yyyy-MM-dd')

 

1.时间字段（date或者timestamp）大小的比较：
select * from  A_SYNC_1 a , A_SYNC_TIME b
       where a.create_date >= b.last_load and a.create_date < b.current_load

2.给时间字段插入系统时间：
update  A_SYNC_1 t set last_update=sysdate where t.id='3'

3.给时间字段插入任何时间：
update A_SYNC_TIME set current_load=to_date('1971-01-01 00:00:01','yyyy-mm-dd hh24:mi:ss')

4.查找最大时间戳
select max(t.timestamp) from table where condition....

7.字符串操作

select * from table where length(t.id)=30

8.对某字段求和统计

select sum(t.count) from table where conditions...

9.查询结果插入另外一张表

insert into table(col1,col2)
    select col1,col2 from table where condictions...

10.一种优化的查询方法

select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;