sql that usually used

向数据表中插入一年的日期:

insert into T_HOLIDAYselect SEQ_T_HOLIDAY.NEXTVAL xh,to_date(20160101,'YYYYMMDD')+level-1 date_time,0 date_type from dual connect by level <367;

将日期为周六周日的更新为非工作日:

--星期日为1,星期六为7,工作日为2~6update t_holiday set date_type=1 where to_char(date_time,'D') in (1,7);

备份数据表:

create table t_ad_item_bk160831 as select * from t_ad_item

删除重复数据,留一条:

delete from table a where (a.gh,a.cdate) in (select gh,cdate from table group by gh,cdate having count(*) > 1) and rowid not in (select min(rowid) from table group by gh,cdate having count(*)>1)

--字符串截取SUBSTR(string,start_position,[length])substr("ABCDEFG", 0);--返回：ABCDEFG，截取所有字符 substr("ABCDEFG", 2);--返回：CDEFG，截取从C开始之后所有字符 substr("ABCDEFG", 0, 3);--返回：ABC，截取从A开始3个字符--返回ABCDEFG,100超出字符串长度,但不影响结果,系统按预处理字符串最大数量返回substr("ABCDEFG", 0, 100);--返回EFG，为负值时表示从尾部开始算起，字符串排列位置不变substr("ABCDEFG", -3);

将表中某一列串接为一个字符串:

select WMSYS.WM_CONCAT(t.representid) from DEPT_INFO_EXTEND t;--指定;分隔select replace(WMSYS.WM_CONCAT(t.representid),',',':') from DEPT_INFO_EXTEND t;

将用户密码有效期默认180改为无限期

--进入sqlplus模式sqlplus / as sysdba;--查看用户密码的有效期设置(一般默认的配置文件是DEFAULT，注意大小写)SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';--将密码有效期改成无限期，修改后自动生效ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED ;--帐户再改一次密码alter user 用户名 identified by 原密码;--使用修改后的用户登录，如果报"ORA-28000:用户已被锁"，解锁alter user db_user account unlock;commit;