--通过表找存储过程名称
select * from dba_source a where upper(a.text) like upper('%sp_kr_bimv_sms_busi_day%');
过程查看表 dgdm_kr.sh_mon_def CMD_NAME
查询表和分区信息 user_tab_partitions TABLE_NAME PARTITION_NAME
查询数据库名称 select sys_context('userenv','db_name') from dual;
左连接 select a.*,b.* from tb_tzh_test1 a left outer join tb_tzh_test2 b on a.name = b.name;
右连接 right outer join 外连接 full outer join
改变时间格式 alter session set nls_date_format = 'yyyy-mm-dd';
create table dgdm_mk.tb_mk_yjq_20101025_01_tmp5_2 tablespace tbs_mk_fact_data_2 nologging as
取最接近的一个整数(大) select ceil(12.1) from dual; 13
regexp_like的使用
select * from smsorgprs_dic where
regexp_like(priv_name,'.[^[:digit:]]5元.*');
trunc(months_between(sysdate,a.ACTIVE_TIME))
select count(distinct a||b) from c;
左连接 a=b(+) b(+)=c
select deptno,ename,sal,
sum(sal) over (order by ename) 累计, --按姓名排序,并将薪水逐个累加
分析函数
Select EMPLOYEENAME,SALARY,
RANK() OVER (Order By SALARY Desc Nulls Last) "RANK", --7 7 9
DENSE_RANK() OVER (Order By SALARY Desc Nulls Last) "DENSE_RANK", --7 7 8
ROW_NUMBER() OVER(Order By SALARY Desc Nulls Last) "ROW_NUMBER" --7 8 9
From EMPLOYEEINFO
增加一列 ALTER TABLE TABLE_AAA ADD STOCKMAN NUMBER(18)
修改一列 ALTER TABLE TABLE_AAA modify STOCKMAN NUMBER(20)
删除一列 ALTER TABLE TABLE_AAA drop column STOCKMAN
增加多列:
增加多列 alter table emp4 add (test varchar2(10),test2 number);
修改多列:alter table emp4 modify (test varchar2(20),test2 varchar2(20));
删除多列: alter table emp4 drop (test,test2);
select distinct a,b from c 只有当a 和 b 同时相等时才会被排除掉
sql笔记 20110109
最新推荐文章于 2024-05-10 14:06:22 发布