sql笔记(201009)

赋权给所有人 grant select on sz_1152696_oce to public;
to_char(a.CHGTIME,'yyyymmdd')>='20100403'
内部连接数据库 CONNECT user_name/passwd@db_alias
取消脚本替代中的显示 SET ECHO
sqlplus帮助 sqlplus /help 或者 sqlplus -h
关闭显示查询时间 set timing off
关闭显示行数 set feedback off

SELECT TRIM (0 FROM 0009872348900) "TRIM Example" FROM DUAL; 98723489
从大表中娄出小表 SELECT department_id FROM departments d WHERE EXISTS (SELECT * FROM employees eWHERE d.department_id = e.department_id);
字段匹配选择 select decode(a.waystate,1,'在用',0,'失效','已删除') from b;
SELECT SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAL; CDEF
查oracle详细报错 oerr ora 942
取当前月份 select to_char(sysdate,'mm') from dual 08
连接两个字段 concat(firstname,lasename)

unload to /dxyyt/kefu/yyj/a.txt select srcid,max(WORKFDEALTIME) from kism.sv_sms_result where WORKFDEALTIME>to_date('2010-06-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and WORKFDEALTIME<to_date('2010-06-30 23:59:59','yyyy-mm-dd hh24:mi:ss') and REGION='SZ' group by srcid;
nvl(a.ResID,'0')

update hyyy.subs_payplan set DRAWORDER='4' where subsid in (select subsid from mgsj.sz_cm_subs_subscriber where servnumber in ('13823351446','13823266481','13823352476','13823334826','13823330546','13823334726','13823334106','13823332401') and active=1) and FEEGROUPID='ALL' and nvl(enddate,sysdate+1)>sysdate;

select count(distinct a) from b

CREATE TABLE employees_demo( last_name VARCHAR2(25) , last_nam VARCHAR2(25) NOT NULL);

赋权限 grant select on tmp_t to cx_tzh_ros 收回权限 revoke select on tmp_tzh from cx_tzh_ros
select count(*)　 from szzw.IB_WL_CUSTINTECHG_HIS where custid = ? and UPDATETIME >= to_date('2009-04-01','yyyy-mm-dd') and UPDATETIME <= sysdate;
提前一年 update szyy.subs_privilege set startdate=add_months(startdate,-12),enddate=add_months(enddate,-12) where oid=75406886684604;
unload to ./sz_air
select /*+parallel(a,3)+*/ MOBILENO,AMT,REQTIME,SRCSTREAMNO 对a加3个进程
from jpzw.IB_wl_aircz a,kisj.sz_cm_subs_subscriber b
where a.REQTIME between to_date('20100512000000','yyyymmddhh24miss') and to_date('20100519235959','yyyymmddhh24miss')
and a.TYPE='NCZ'
and a.MOBILENO=b.servnumber
and b.active=1
and b.prodid in ( select PRODID from szzw.ib_cb_realprod where DEALTYPE=1 );

select servnumber,a.subsid,MAILADDR from szyy.subscriber a,szzw.CM_CA_BILLMAIL b where a.active=1 and a.acctid=b.acctid and a.servnumber in('13823394081');
两个表关联自后如果字段不同的话 可以不用前缀

把一个表数据导入另一个表中
insert into ssyy.rec_paytype(OID,REGION,PAYTYPE,FEE,BANKID,BANKACCOUNT,POSFORMNUM,RECDATE) select '20100525094132',REGION,PAYTYPE,-288800,BANKID,BANKACCOUNT,POSFORMNUM,sysdate from szyyhis.cs_rec_paytype where oid ='75406573939785';
insert into szyy.rec_resource(RECOID,REGION,RESTYPEID,1,）from szyyhis.cs_rec_reception where oid=75406573939785;

drop table tmp_zheng2;
create table tmp_zheng2 as
select rownum as oid,b.custid,a.eboxid,servnumber,subsid,amt from tmp_zheng1 a,mgsj.sz_cm_subs_subscriber b
where a.eboxid=b.acctid;
获取表结构 creat table a as select * from b where 1=2;
不加引号的数字就是number类型 vaildcyc=201002

select order_id,mobile,brachcode,brand,DECIMAL(AMOUNT,10,2),state,dtime,helpmobile,AMOUNTCODE from webuser.T_PAYMENT_ORDERS where month
(dtime)=6 and (mobile='15220176364'or helpmobile='15220176364') with ur

插入表 insert into a select * from b

dbtool username/password@SID
sqlplus username/password@SID

提数 YW201004060119
http://cmcp.sz.gmcc.net/Modules/WorkSheets/Business/ItemDetail.aspx?action=2&app=1&proc=10040601058&task=11&ds=
备份表
create table ib_cbp_waitreq_tmp as select * from szzw.ib_cbp_waitreq where streamno='500374409';
delete from szzw.ib_cbp_waitreq where streamno='500374409';
select table_name from all_tables where rownum<5 ;显示库中的表
select distinct company from a 不显示a中重复的
select table_name from all_tables where owner='dbuser'; 查询数据库中的表
desc table_name 查询表结构
select name from v$database; 查看当前的所有数据库
select table_name from all_tables where table_name like ‘u';

看字段名与数据类型: desc talbename;
查看主键： select * from user_constraints where constraint_type='P' and TABLE_name=upper('tablename')