oracle sql常用操作
1.修改TNStnsnames.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.日期相关
selectTCH_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);
selectTCH_ASSIGN_SUC_UN,TCH_DROP_NUM,TCH_ASSIGN_SUC_UN from MOD_BADCELL_SB_DAY t
where t.start_time > trunc(sysdate);
查看版本
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;
1. 根据已有表创建表create table mod_cdt_zte_hecj as select* from mod_cdt_zte where 1=22. 查看指定表中字段的数据类型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_SCALEfrom 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_SCALEfrom 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_SCALEfrom 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如何查询表列的数量selectcount(1) from user_tab_columns where table_Name =upper(trim('mod_cdt_zte'))3. 查询表中指定行数数据oracle的ROWNUM伪列返回查询的行序号要查询表的前10条记录,可以使用select * from user_tab_columnswhere ROWNUM<=10但是要返回第11-第20条记录,尝试以下的语句select * from user_tab_columnswhere 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 MINUSselect * 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 MINUSselect * 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=999999999SQL*Loader-704:内部错误: ulconnect: OCIServerAttach 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') andtime<=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
页:
[1]