HSBC sql小总结

 SELECT id_typ, id_no FROM tbl_tmmp1
 minus
 SELECT id_typ, id_no FROM tbl_tmmp2;

 

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

Alter table EMP modify emp_id not null;.

Create table emp(
Emp_id number(10),
Emp_name varchar2(15),
City varchar2(50),
Territory number(10)
Constrain pri_emp primary key(emp_id) ,  //主键约束
Constrain for_emp foreign key(city , territory)
Reference on province(city , territory) //waijian
);
外键也可以后期加
Alter table emp
Add Constrain for_emp foreign key(city , territory)
Reference on province(city , t

oracle具有一致性。
update tablename set x =2 where y=5 ;
若更新表的时候，同时其他语句修改了y的值，oracle会重新查看y的值。

any/some 比较其中的任意值
查询工资低于平均工资的员工信息
select * from employees where salary<=any(select avg(salary) from employees)
and rownum<8 ;   //查询7行

all 比较所列出的每一值
查询工资低于平均工资的员工信息
select * from employees where salary<=all(2500,6000,10000);

[not]between  [not]exists  [not]like  is[not]null

not结果取反 select * from table where not(fname='Alyssa');

--------------------函数--------------
abs(-210) 绝对值 acos 反余弦值 asin反正弦值 atan 反正切值 ceil(n)大于或等于n的最小整数值
exp(n)e的n次幂 floor(n)小于或等于n的最大整数值 

round(21.36125,2)=21.36 <正数向右，负数向左> round(21.36125,-1)=20
trunc 和round 一样的 |
--------------------
select to_date('2012/12/20', 'yyyy/MM/dd') A,
trunc(to_date('2012/12/20', 'yyyy/MM/dd'),'YYYY') B,
add_months(trunc(to_date('2012/12/20', 'yyyy/MM/dd'),'YYYY'), -2) C
from dual;
---如果是MM，就月归零，显示01-12月-12
  如果是YYYY，就年归零，显示01-1月-12
  如果是DD，没反应！

-----------------
stddev 标准偏差？

------------多表查询--------------
union 返回多表之间不重复的记录
union all  返回所有结果值 （忽略是否重复）
intersect 用来返回前后两个查询相同的部分
minus 用来返回前面查询减去后面查询的部分
-----------创建表---------
create table emp as
select * from employee;
--增加一列
alter tbale emp add hire_date date default sysdate;
--增加多列
alter table emp add (hire_date date,
       phone   number(8));
---查看表 desc emp;
--删除列 alter table emp drop column hire_date (cascade constraints);//忽略索引或约束
--表的重命名 rename table emp to emp_new;

---------------View-----
drop view v_emp;

create or update view
as
select * from aa where ~~

------------------------full outer joiner ----------------------
select * from a full outer join b where a.no=b.no;
就会先检索出a的所有值，再去检索b的值 。

 

----------grant -- 授权--------
grant  select on  VIEW_MAPRB_HCC_XACT_WP_sid  to BSIDTMC; --select
GRANT ALL ON TABLE tbl_dwh_cus_ext_7m TO bcrehlk  ; --all

 

---------------------- to_date ---------------
TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
-------------
drop Partition :

alter table tbl_maprg_cd_stck13_cd2013bk
DROP partition TP_MAPRG_CDTCK13_CD13BK_121128 ; ---tp_maprg_cdtck13_cd13bk_121128
commit;
----------
----2012/1/17----查重复记录---
select
HCC_REF_NUM
from tbl_maprg_comcd13_w28412_tmp0b
group by HCC_REF_NUM
having count(*) >1
-----case when -----
--几个case when 就几个end

select case when status='A' then 'a'
       else case when status='R' then 'r'
       else 'c'
       end end as status
from tbl_maprg_com_cd13_appl_w
where cd_no_1='43487658367';

-------全关联-----
select * from tbl_1  FULL OUTER JOIN tbl_2
on a.xx=b.xx ;

------优先级---
select * from tbl_tmp1 where field1=1 and (field2=8 or field3=5 ) ;
select * from tbl_tmp1 where field1=1 and field2=8 or field3=5  ;
--这个相当于（field1=1 and field2=8） or field3=5
可见or应用范畴包含and

-----Lpad---------
select LPad(BR_NO,11,' ') as "branch code" from tbl_maprg_cd_stck13_ssd_dtl ; ---293-->293
select LPad(BR_NO,6,'*') as "branch code" from tbl_maprg_cd_stck13_ssd_dtl ; ---293-->***293
select LPad(BR_NO,2,' ') as "branch code" from tbl_maprg_cd_stck13_ssd_dtl ; ---293-->29
select LPad(BR_NO,11,'') as "branch code" from tbl_maprg_cd_stck13_ssd_dtl ; ---293-->null

-----ASCII--CHR---

select ascii('&') from dual ; -- 38

select chr(38) from dual ; --&

 

 

comcd12 13 maprg_comcd12  control-m  old-schedule

6.3在onhold   filewait 

map team check

----------------------
sql语句中  ' := '为赋值   ，'='是判断是否相等 。

 

-------excellent select sql -----
create table test_tmp(
 fid  varchar(1),
 val varchar(2))
 tablespace TBS_MAPG_TEMP_PARTS ;
 
 insert into  test_tmp(fid,val)
 values('A','11');
 insert into  test_tmp(fid,val)
 values('A','11');
 insert into  test_tmp(fid,val)
 values('B','11');
 insert into  test_tmp(fid,val)
 values('B','12');
 insert into  test_tmp(fid,val)
 values('B','11');
 insert into  test_tmp(fid,val)
 values('C','22');
 insert into  test_tmp(fid,val)
 values('C','23');
 COMMIT;
 select * from test_tmp ;
 
 select distinct fid,val from test_tmp
 where fid in (
 select fid
 from test_tmp
 group by fid
 having count(distinct(val))>1)
 order by fid;

------------select the count(*)>1 values -----------
select * from test_tmp tmp,(select fid from test_tmp group by fid having count(val)>1) tmp2
where tmp.fid=tmp2.fid ;

 

select floor(2.8) from dual ;  = 2
select round(2.5) from dual ;  = 3