firemail

标题: oracle sql常用操作 [打印本页]

作者: Qter 时间: 2020-1-30 10:38
标题: oracle sql常用操作
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 时间: 2020-1-30 10:41

查看版本
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 时间: 2020-1-30 10:42
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

欢迎光临 firemail (http://firemail.wang:8088/)