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
相关推荐
hsbc statement template
HSBC笔试\SHL试题合集,超级全的笔试总结和基础
The Classic 42 questions for HSBC shl verbal testing.
HSBC业务目标的核心是发展一套行之有效的客户关系管理(CRM)系统基础设施。目前,该银行已经实施了e-CRM计划,并取得了显著成果。HSBC之所以在该领域的研究中选择了和IBM共同工作,主要是因为后者在这一专业领域...
Behind HSBC is Citi, the US bank which enjoys a second placed ranking for the second year in a row, while Bank of China retains its third spot. Consistency of service appears to be well-established ...
HSBC-全球交通运输业-电动垂直起降飞机:梦幻般的,未来的,就在你身边的天空-2021.11-66页.pdf
HSBC-中国虚拟现实行业专题研究报告 :终于起飞了!(英)-2021.11-72页.pdf
汇丰银行HSBC-201406-HSBC in mainland China-Investor roadshow_By_CEO.pdf
HSBC.p12
hsbc-中国投资策略:中国自动化的雄心-2021.2-75页.pdf.pdf
HSBC-全球颠覆性科技行业-智慧农业世界(英).pdfHSBC-全球颠覆性科技行业-智慧农业世界(英).pdf
HSBC-中国金属和采矿业-中国铝:走向绿色之挑战多于机遇-2021.12.2-48页.pdf
HSBC-CEM案例分析.pdf
HSBC全球自动化行业数据驱动的工业5.0的上升潜力2021.7.2(32页).pdf
hsbc
HSBC颜色示例
湾HSBC汇丰银行媒介预算提案.pptx
HSBC全球交通运输与物流业HSBC第15次交通运输与物流大会:再也无法忽视它了2021.6.18(30页).pdf
汇丰Java-Day2 汇丰Java培训第二天