oracle sql常用操作

Qter

1.修改TNS
tnsnames.ora文件
D:\oracle\product\10.2.0\client_1\NETWORK\ADMIN
2.查询表列
Select * from user_tab_columns where table_Name =upper(trim('mod_cdt_zte'))

Select COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE
from user_tab_columns
where table_Name =upper(trim('mod_cdt_zte'))  and (COLUMN_NAME =upper(trim('LASTPSMMACT_PHASE5'))or COLUMN_NAME =upper(trim('PILOT_STRENGTH1')))

3.日期相关
select  TCH_ASSIGN_SUC_UN,TCH_DROP_NUM,TCH_ASSIGN_SUC_UN from MOD_BADCELL_SB_DAY t
where t.start_time > trunc(sysdate)-3;

select

CASE
   WHEN SUM(TCH_ASSIGN_SUC_UN) = 0 THEN
    0
   ELSE
    ROUND(SUM(TCH_DROP_NUM) / SUM(TCH_ASSIGN_SUC_UN) * 100, 3)
END

  from MOD_BADCELL_SB_DAY t
where t.start_time > trunc(sysdate);
select  TCH_ASSIGN_SUC_UN,TCH_DROP_NUM,TCH_ASSIGN_SUC_UN from MOD_BADCELL_SB_DAY t
where t.start_time > trunc(sysdate);

Qter

查看版本
select * from v$version


查询表列
Select * from user_tab_columns where table_Name =upper(trim('mod_cdt_zte'))

Select COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE
from user_tab_columns
where table_Name =upper(trim('mod_cdt_zte'))  and (COLUMN_NAME =upper(trim('LASTPSMMACT_PHASE5'))or COLUMN_NAME =upper(trim('PILOT_STRENGTH1')))


查询通配符
%   零或者多个字符
_    单一任何字符（下划线）
\     特殊字符

需要通过escape指定转移符，如：
select * from tab1 where col1 like '%\_%' escape '\';
这样就把表tab1里字段col1里含有通配符_所有记录查出来了


导出对象（表、表空间）的数据定义语句
1.表
SELECT distinct type FROM ALL_SOURCE;
PROCEDURE
PACKAGE
PACKAGE BODY
TYPE BODY
TRIGGER
FUNCTION
JAVA SOURCE
TYPE

SELECT text FROM ALL_SOURCE where TYPE='TYPE' AND NAME ='OBJECT_NAME';

方法二：
SELECT dbms_metadata.get_ddl('TABLE','MOD_CDL_GRID') FROM dual;

2.表空间
使用dbms_metadata确实有缺陷
eg：表空间test创建时为1M,在使用过程中我们扩充到了10M,
使用select to_char(dbms_metadata.GET_DDL('TABLESPACE','TEST')) from dual;返回的脚本中，test表空间为1M



---导出数据
exp noap/uwaysoft2009@UWAY file=igpcq.dmp tables=cfg_map_dev_to_ne


--根据已有表创建新表
create table a as select * from b

create table mod_cdt_zte_hecj as select  * from mod_cdt_zte where 1=2
drop table mod_cdt_zte_hecj


--时间查询相关
select * from para_switch where start_time>=trunc(sysdate)-1  --查询前一天的数据

oracle 取当前日期时间的前一天前一小时前一分钟前一秒
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual      --当前时间
union all
select to_char(sysdate-1-1/24-1/24/60-1/24/60/60 ,'yyyy-mm-dd hh24:mi:ss') from dual


select CHINA_NAME from mod_cdl_kpi_carr where CHINA_NAME like '5_455_%' and to_char(START_TIME,'yyyy-mm-dd hh24:mi:ss') = '2012-06-01 10:00:00'

select * from mod_cdr_hw where IMSI = 460036700163298 and access_time>trunc(sysdate)-1+3/4

select trunc(sysdate) + 1/24 from dual;


select trunc(sysdate) from dual; 当前天
2012-7-19
select trunc(sysdate)-1 from dual; 前一天.
2012-7-18

select trunc(sysdate)-1+3/4 from dual;  24/4=6*3=18点
2012-7-18 18:00:00


-- 得到小时的具体数值

select trunc(sysdate) + 1/24 from dual;
select trunc(sysdate) + 7/24 from dual;


---------数字时间转换
to_number(to_char(time,'yyyymmddhhmiss'))

time是你表中的时间日期字段
yyyy代表年，mm代表月，dd代表日，hh代表时，mi代表分，ss代表秒，注意mm与mi千万不要混淆
先把日期转换为char类型，然后再把char转换为数字类型

select ACCESS_TIME ,to_number(to_char(ACCESS_TIME,'yyyymmddhhmiss')) from MOD_CDR_HW   ---不是相对时间
        2012-4-9 20:58:07        20120409085807
        2012-4-9 20:59:47        20120409085947
        2012-4-9 20:59:49        20120409085949


--------锁定要操作的行
--锁定要操作的行，以防止时间改变，提交事务(COMMIT)一下就解锁了
select t.*,rowid from clt_cfg_ilap_task t where id=70 for update


----子串查询
MOBILE_ID "substr(:MOBILE_ID,length(:MOBILE_ID)-7,8)",

select substr('00a0000032b1b05c',length('00a0000032b1b05c')-7,8) from dual;

Qter

1. 根据已有表创建表

create table mod_cdt_zte_hecj as select  * from mod_cdt_zte where 1=2

2. 查看指定表中字段的数据类型a.

Select * from user_tab_columns where table_Name =upper(trim('mod_cdt_zte'))  order by Column_ID

Select COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE

from user_tab_columns

where table_Name =upper(trim('mod_cdt_zte'))  and (COLUMN_NAME =upper(trim('LASTPSMMACT_PHASE5'))or COLUMN_NAME =upper(trim('PILOT_STRENGTH1')))

Select COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE

from user_tab_columns

where table_Name =upper(trim('mod_cdt_zte'))  and COLUMN_NAME in ('DPSMM_FIRST_STR_ZERO',

'DPSMM_FIRST_STR_ONE')

b. PL/SQL command window

输入 desc 表名

SQL> desc mod_cdt_zte

名称                                      是否为空? 类型

----------------------------------------- -------- ----------------------------

OMCID                                              NUMBER(16)

FCH_PWR_MEAS_FRAMES                                NUMBER(8)

RECVPMRM_ACTPN_STR0                                NUMBER(8,1)
DPSMM_FIRST_PN_THREE                               NUMBER

IMSI                                               VARCHAR2(16)

CALL_START_TIME                                    DATE

注意上面三种NUMBER类型的不同,不加数字限制的最后一种可以存放负数和小数

且数据长度(DATA_LENGTH)都22位，可以设置其精确位数(DATA_PRECISION)及小数位数(DATA_SCALE)

Select COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE

from user_tab_columns

where table_Name =upper(trim('mod_cdt_zte'))  and COLUMN_NAME in ('FCH_PWR_MEAS_FRAMES',

'RECVPMRM_ACTPN_STR0','DPSMM_FIRST_PN_THREE')

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml744\wps1.jpg

b. oracle如何查询表列的数量

select  count(1) from user_tab_columns where table_Name =upper(trim('mod_cdt_zte'))

3. 查询表中指定行数数据

oracle的ROWNUM伪列返回查询的行序号

要查询表的前10条记录，可以使用

  select * from user_tab_columns  where ROWNUM<=10

  但是要返回第11-第20条记录，尝试以下的语句

  select * from user_tab_columns  where ROWNUM<=20 and ROWNUM>=11;

上面语句返回0条记录。因为ROWNUM是伪列，不能用>=条件

  使用以下方法可以查询第11-第20条记录

  select * from  (select ROWNUM rn ,t.* from user_tab_columns t where ROWNUM<=20) where rn>=11;

select * from (select ROW_NUMBER() OVER (ORDER BY Column_ID) rn,t.* from user_tab_columns t)   where rn between 11 and 20;

select * from mod_cdt_zte_hecj where ROWNUM<=20 MINUS  select * from mod_cdt_zte_hecj where ROWNUM<11;

总结：

如何实现分页提取记录

方法1：oracle的ROWNUM伪列返回查询的行序号。

  例如要查询表的前10条记录，可以使用

  select * from tablename where ROWNUM<=10

  但是要返回第11-第20条记录，尝试以下的语句

  select * from tablename where ROWNUM<=20 and ROWNUM>=11;

  这个人报错。返回0条记录。因为ROWNUM是伪列，不能用>=条件

  使用以下方法可以查询第11-第20条记录

  select * from

  (select ROWNUM rn ,t.* from tablename t where ROWNUM<=20) where rn>=11;

方法2：使用分析函数ROW_NUMBER实现分页

  select * from (select ROW_NUMBER() OVER (ORDER BY id) rn,t.* from tablename t)

    where rn between 11 and 20;

方法3：使用集合运算MINUS实现分页

  select * from tablename where ROWNUM<=20 MINUS

  select * from tablename where ROWNUM<11;

点评：方法1在查找前几页时速度很快。但在数据量很大时，最后几页速度比较慢。

方法2查询效率比较稳定，是推荐使用的方法。

方法3只适合查询结果在200行以内的情况，记录数很多时会导致oracle错误，需谨慎使用。

4.Tnsnames.ora文件

# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\client_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

UWAY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.180)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = UWAY)

    )

  )

5.sqlldr错误

sqlldr userid=noap/uwaysoft2009@HUBNOAP rows=500 bindsize=20971520 readsize=20971520 control=import_pollute_cell_1x.ctl errors=999999999

SQL*Loader-704:  内部错误: ulconnect: OCIServerAttach [0]

ORA-12154: TNS: 无法解析指定的连接标识符

改下本地配置的数据库

确认userid=scott/scott@orcl表示的是:用户名/密码@数据库，看看你的用户名/密码是否正确，用户是否锁定。如果不是这个问题，你需要给你的用户分配访问数据库的权限。

SQL*Loader-941:  在描述表 MOD_CDL_PILOT_CELL_1X 时出错

ORA-04043: 对象 MOD_CDL_PILOT_CELL_1X 不存在

改下用户名

6.日期相关

select * from para_switch where start_time >=to_date('2012-03-14 23:59:59','yyyy-mm-dd hh24:mi:ss')

　Oracle数据库日期范围查询有两种方式:to_char方式和to_date方式,接下来我们通过一个实例来介绍这一过程.我们假设要查询2011-05-02到2011-05-30之间的数据,实现方式如下:

　　to_date方式:

　　select * from tablename where time>=

　　to_date('2011-05-02','yyyy-mm-dd')

　　and  time<=to_date('2011-05-30','yyyy-mm-dd')

　　运行的结果是:可以显示05-02的数据,但是不能显示05-30的数据.

　　所有可以得出结论:

　　①如果想显示05-30的数据可以<to_date('2011-05-31','yyyy-mm-dd'),这样就能显示30号的了.

　　②如果想要显示05-30的数据可以<=to_date('2011-05-30 23:59:59','yyyy-mm-dd hh24:mi:ss')也是可以查出来的.

　　to_char方式:

　　同样查询上面两个日期

　　select * from tablename where to_char(time,'yyyy-mm-dd')>='2011-05-02'

　　and to_char(time,'yyyy-mm-dd')<='2011-05-30'

　　查询结果:可以同时显示05-02和05-30的数据.

　　关于Oracle数据库日期范围查询的两种实现方式:to_date方式和to_char方式的相关知识就介绍到这里了,希望本次的介绍能够对您有所收获!

7．空字段类型的查询

select count(1) from PARA_ADJ_HW_1XSF where bsc_id = 2 and city_id = 931 and NEI_NE_SYS_ID is null