sql-log

分布式事务也可以用啊，JTA
那你就用数据库中间件， cobar
---------------------oracle--------------------------------
解锁用户--alter user boxoa account unlock


CREATE TABLESPACE FPS DATAFILE  'F:\workSoft\oracle_client\workSpace\FPS.dbf' SIZE 200M autoextend on; 
drop tablespace FPS including contents                      删除表空间


CREATE USER system IDENTIFIED BY CN1001                       //用户


ALTER USER SYSTEM IDENTIFIED BY CN1002                    //修改密码


DROP USER LS CASCADE;                                                         //删除用户


GRANT CONNECT,RESOURCE,DBA TO LS;                        //受权限


REVOKE CONNECT,RESOURCE,DBA from LS;                    //删除权限


CREATE USER yongtaioa  IDENTIFIED  BY yongtaioa0 ACCOUNT UNLOCK; 
GRANT CONNECT TO yongtaioa WITH ADMIN OPTION; GRANT CONNECT TO yongtaioa WITH ADMIN OPTION; GRANT resource  TO yongtaioa WITH ADMIN OPTION; 


----------------\\\\\\\\\\\\\\\\---------
drop user boxoa cascade;
drop TABLESPACE FPS;
CREATE TABLESPACE FPS DATAFILE  'F:\workSoft\oracle_client\workSpace\FPS.dbf' SIZE 200M autoextend on; 
CREATE USER boxoa
IDENTIFIED BY a123456
DEFAULT TABLESPACE FPS; 




grant dba to boxoa;
grant connect to boxoa;
grant resource to boxoa;
-----------------------------------------


修改表名：alter table t_user rename to t_user2;


修改表中字段名：alter table t_user2 rename column username to username2;


添加字段：alter table t_user2 add checkowen char(164);


修改字段类型：alter table t_user2 modify usersex number;


[提前条件：]表中必须没有数据。
删除字段：alter table t_user2 drop column usersex;


 select * from table_name 
update table_name set user_name ='ssss' where user_id='ni000'
delete from table_name  where  user_id ='ni000'
   
添加表数据 1.  insert  into table_name (userid , name )  values("001","hh") 
                      2.insert into table_name (userid,name ) select userid,name from table_name where userid='001'
(case (SELECT M.ORDERSTATUS FROM UCP_WFCHECKHIST M WHERE M.BUSIID = A.BUSIID AND M.ORDERSTATUS IN (30,40)) when 30
 then '通过' when 40 then '不通过'  else ''  end) CHECKSTATUD
细节  1.  时间格式SQL substr(CHAR(a.close_date),1,10) =  #close_date#     
           2. case  when  的用法： CASE WHEN UE.ORGID5 IS NULL THEN 0 ELSE UE.ORGID5 END,
           3. 字符连接：  REASON_CODE,REASON_CODE||'   '||reason_notes as reason_notes
           4. 去空格：  '%'|| trim('$operatorid$')||'%'   
           5. db2序号：ROW_NUMBER() OVER() AS ROWNUM
           6.创建索引  create index "idx_supdoc_sheet" 《索引名称》  on "ds_act_supportdocupload"  《表名称》 ( "sheet_id"  《字段名称》          asc  );


###############################################################################################
 关键字  
      1、distinct   去除重复数据
      2、union all  并关系
      3、exists 
        以select * from A where exists(select * from B where A.a=B.a)为例,
        exists表示,对于A中的每一个记录,如果,在表B中有记录,其属性a的值与表A这个记录的属性a的值相同,则表A的这个记录是符合条件的记录, 
        如果是NOT exists,则表示如果表B中没有记录能与表A这个记录连接,则表A的这个记录是符合条件的记录
 连接SQL
      select   *   from   a,b   where   a.id=b.id  
  这个语法是内连接的另外一种写法，其执行结果与inner   join   一样  
内连接        只连接匹配的行
左外连接        包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行
右外连接        包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行
全外连接        包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。
(H)(theta)连接        使用等值以外的条件来匹配左、右两个表中的行
交叉连接        生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配
在INFORMIX中连接表的查询
如果FROM子句指定了多于一个表引用，则查询会连接来自多个表的行。连接条件指定各列之间（每个表至少一列）进行连接的关系。因为正在比较连接条件中的列，所以它们必须具有一致的


数据类型。


 
###############################################################################################
 
oracle 数据库
select t.table_name,t.column_name from user_tab_columns t where t.column_name like '%CURRENTSTATE%';




// 1：更改事务状态
conn.setAutoCommit(false);
conn.commit();
conn.rollback();




ResultSetMetaData metaData =  rs.getMetaData();   获取该字段的表名，字段长度，字段类型




oracle  触发器


--------------
1.。。。。。。
create or replace trigger process_instance_trigger  //创建触发器名称
after update on bpm_rtm_process_instance            //触发条件（bpm_rtm_process_instance表操作update的时候出发）
for each row
begin
  if :NEW.instance_status='ACH' then
     delete from WFWORKITEM_ING1 where PROCESSINSTID=:OLD.PROCESSINSTID;
    end if;
end;
2.。。。。。。。
create or replace trigger workitm_trigger
after insert or update or delete on WFWORKITEM
for each row
begin
if inserting then
   insert into WFWORKITEM_ING1 (WORKITEMID, WORKITEMNAME, WORKITEMTYPE, WORKITEMDESC, CURRENTSTATE, PARTICIPANT, PARTINAME, PRIORITY, ISTIMEOUT, LIMITNUM, 


LIMITNUMDESC, CREATETIME, STARTTIME, ENDTIME, FINALTIME, REMINDTIME, ACTIONURL, PROCESSINSTID, ACTIVITYINSTID, STATESLIST, TIMEOUTNUM, TIMEOUTNUMDESC, PROCESSINSTNAME, 


ACTIVITYINSTNAME, PROCESSDEFID, PROCESSDEFNAME, PROCESSCHNAME, ACTIVITYDEFID, ASSISTANT, ASSISTANTNAME, BIZSTATE, ALLOWAGENT, ROOTPROCINSTID, ACTIONMASK, URLTYPE, 


EXTEND1, EXTEND2, EXTEND3, EXTEND4, EXTEND5, EXTEND6, EXTEND7)
   values(:NEW.WORKITEMID,:NEW.WORKITEMNAME, :NEW.WORKITEMTYPE, :NEW.WORKITEMDESC, :NEW.CURRENTSTATE, :NEW.PARTICIPANT, :NEW.PARTINAME, :NEW.PRIORITY, :NEW.ISTIMEOUT, 


:NEW.LIMITNUM, :NEW.LIMITNUMDESC, :NEW.CREATETIME, :NEW.STARTTIME, :NEW.ENDTIME, :NEW.FINALTIME, :NEW.REMINDTIME, :NEW.ACTIONURL, :NEW.PROCESSINSTID, 


:NEW.ACTIVITYINSTID, :NEW.STATESLIST, :NEW.TIMEOUTNUM, :NEW.TIMEOUTNUMDESC, :NEW.PROCESSINSTNAME, :NEW.ACTIVITYINSTNAME, :NEW.PROCESSDEFID, :NEW.PROCESSDEFNAME, 


:NEW.PROCESSCHNAME, :NEW.ACTIVITYDEFID, :NEW.ASSISTANT, :NEW.ASSISTANTNAME, :NEW.BIZSTATE, :NEW.ALLOWAGENT, :NEW.ROOTPROCINSTID, :NEW.ACTIONMASK, :NEW.URLTYPE, 


:NEW.EXTEND1, :NEW.EXTEND2, :NEW.EXTEND3, :NEW.EXTEND4, :NEW.EXTEND5, :NEW.EXTEND6, :NEW.EXTEND7);
elsif deleting then
  delete from WFWORKITEM_ING1 where WORKITEMID=:OLD.WORKITEMID;
else
    update WFWORKITEM_ING1 set WORKITEMNAME=:NEW.WORKITEMNAME,CURRENTSTATE=:NEW.CURRENTSTATE where WORKITEMID=:OLD.WORKITEMID;


end if;
end;


--------------
11、oracle 导入导出
     imp boxoa/a123456@orcl full =y file=f:\boxoa.dmp
     imp boxoa/a123456@orcl file=f:\boxoa.dmp tables=(wfworkitem,bpm_rtm_process_instance,bpm_mdl_activity)  
     exp boxoa/a123456@orcl file=f:\boxoa.dmp tables=(wfworkitem,bpm_rtm_process_instance,bpm_mdl_activity)  


exp/imp已经很好用了，但是唯一的确定是速度太慢，如果1张表的数据有个百千万的，常常导入导出就长时间停在这个表 这，但是从Oracle 10g开始提供了称为数据


泵新的工具expdp/impdp，它为Oracle数据提供高速并行及大数据的迁移。
  imp/exp可以在客户端调用，但是expdp/impdp只能在服务端，因为在使用expdp/impdp以前需要在数据库中创建一个 Directory
create directory test as 'D:\bank\20120113';
grant read, write on directory  test to boxoa
然后就可以开始导入导出
expdp boxoa/123456@orcl directory=test dumpfile=user.dmp  导出用户的数据
expdp piner/piner directory=dump_test dumpfile=table.dmp tables=test1,test2 导出表数据
impdp boxoa/123456 directory=test dumpfile=oa_20120113_after.DMP 导入该用户数据
impdp piner/piner directory=dump_test dumpfile=table.dmp  导出表数据


12、decode(a.smctag,0,'缺货',1,'有贷') smctag






13、查询数据库连接池回话


select * from v$session
select a.SID,a.STATUS, b.sql_text  from v$session a,v$sqlarea b where a.sql_hash_value=b.hash_value 
exec p_kill_user_sid_session(807)
select a.sid,a.STATUS,b.SQL_TEXT,a.LAST_CALL_ET,b.FIRST_LOAD_TIME,b.ELAPSED_TIME,b.PARSE_CALLS  
from v$session a,v$sqlarea b where a.SQL_HASH_VALUE= b.HASH_VALUE


select * from v$session


select a.sid,a.STATUS,b.SQL_TEXT from v$session a,v$sqlarea b where a.SQL_HASH_VALUE= b.HASH_VALUE


exec P_KILL_USER_SESSION(85)


14.  数据库 转码
 oracle: select a.*, decode(userid,'120164','a') auserid from ac_operator a 


 db2 : select case userid when '120164' then 'a' else userid end as auserid from ac_operator(oracle 也可以用)
15. 
从Root往树末梢递归
select * from TBL_TEST
 start with id=1
 connect by prior id = pid
 order by pid
 
从末梢往树ROOT递归
select * from TBL_TEST
 start with id=5
 connect by prior pid = id
 order by id


16、   报表


select du.s_date,du.pa ,count(q.pawncode),nvl(sum(CHARGE),0) from (
select '典当' as typename, pa.branch_name_, pa.applydate,pa.pawncode,pa.SUMCHARGE as CHARGE
  from bpm_biz_pa_pawnapproval pa, wfprocessinst u
 where u.processinstid = pa.processinstid_
   and u.currentstate = 7
   and pa.data_type_ = 'new'
union all
select '续当' as typename, cpa.branch_name_, cpa.applydate,cpa.pawncode,cpa.CONTINUECHARGE as charge
  from bpm_biz_pa_conpawnapproval cpa, wfprocessinst u
 where u.processinstid = cpa.processinstid_
   and u.currentstate = 7
   and cpa.data_type_ = 'new'
)q  
 right join (select da.s_date,a.pa from (
select to_date('201209','yyyymm')+(rownum-1) s_date from dual   
connect by rownum<=last_day(to_date('201209','yyyymm')) - to_date('201209','yyyymm')+1
) da ,(select '典当' as pa from dual
union 
select '续当' as pa from dual
union 
select '赎当' as pa from dual) a ) du on du.s_date=q.applydate and du.pa=q.typename
where s_date>=to_date('2012-9-20','yyyy-mm-dd')
group by du.s_date,du.pa order by du.s_date,du.pa;






17 oracle 显示一个月的所有天数


效率高：


Sql代码  
select to_date('200809','yyyymm')+(rownum-1) s_date from dual   
connect by rownum<=last_day(to_date('200809','yyyymm')) - to_date('200809','yyyymm')+1  
select to_date('200809','yyyymm')+(rownum-1) s_date from dual
connect by rownum<=last_day(to_date('200809','yyyymm')) - to_date('200809','yyyymm')+1 


效率低：


Sql代码  
select  
to_date('200902','yyyymm')+(rownum-1) s_date     
 from all_tables   
 where rownum <=   
(   
 select last_day(to_date('200902','yyyymm'))sf_date('200902','yyyymm')+1 from dual   
 ) 


 18 sql语句


>>>>>>>1、 (to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') -decode(bcpa.enddate,null,bpa.enddate,bcpa.enddate) ) as timesum,




>>>>>>>2、(case when (to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') -decode(bcpa.enddate,null,bpa.enddate,bcpa.enddate) )>0 then (to_date(to_char(sysdate,'yyyy-


mm-dd'),'yyyy-mm-dd') -decode(bcpa.enddate,null,bpa.enddate,bcpa.enddate) ) else 0 end) as timesum,
 
19. mysql 导入 netstat -ln | grep mysql  --socket=/var/lib/mysql/mysql.sock
---------------------------------------------------------
cd mysql/bin
mysql -u baomro -p baomro0 
mysql>use baomro(database_name) 
mysql>source d:\datafilename.sql 


mysql-导出


mysqldump -h  -u baomro -p -v baomro（数据库名称） > baomro.20120903.sql 


mv baomro_yanshi.20130105.sql /ftp/samba/temp/


mv jiahuacmssp20130227.sql /ftp/samba/temp/baomro_data/


mysql 创建  数据库

root用户操作： CREATE DATABASE IF NOT EXISTS baomro default charset utf8 COLLATE utf8_general_ci;
insert into mysql.user(Host,User,Password) values("localhost","baomro",password("baomro0"));
flush privileges;
        grant all PRIVILEGES  on crcm.* to crcm@'localhost' identified by 'crcm0';
   
               grant all PRIVILEGES  on cnsdb.* to crcm@'%' identified by 'crcm0'; 
      GRANT Select ON samp_db.member TO bill@localhost INDETIFIED BY "rock"


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


CREATE DATABASE IF NOT EXISTS mpspider default charset utf8 COLLATE utf8_general_ci; grant all PRIVILEGES  on mpspider.* to mpspider@'%' identified by 


'mpspider0';grant all PRIVILEGES  on mpspider.* to mpspider@'localhost' identified by 'mpspider0'; 




20、exists  的用法
select a.* from jc_shop_diss a where  exists ( select 1 from jc_shop_product c where c.product_id=a.product_id and  c.merch_key=2)


21. 在java 实体前添加 【transient】 hibernate不会对此实体做操作。






22,表的主键被其他表引用查询
-----mysql
select * from KEY_COLUMN_USAGE
 where 
 COLUMN_NAME='xx_id'; 
------oracle
SELECT
    a.table_name, a.column_name
FROM
    user_cons_columns a,
    user_constraints b
WHERE
    a.constraint_name = b.constraint_name AND
    b.constraint_type = 'R' AND
    a.column_name = 'ID' 






23、mysql 替换 语句
update questions set qtitle = replace(qtitle,'"+oldWord+"','"+newWord+"'),qtext = replace(qtext,'"+oldWord+"','"+newWord+"')


24、mysql 拼接字符


update sys_organizationalstructure b, sys_organizationalstructure as a
set a.pathorg =concat( b.pathorg,'.',a.id)
 where a.parent=b.id and a.level=5


26、select * from sys_stores where sqrt(
    (
     ((121.50817640000002-longitude)*PI()*12656*cos(((31.2991177+latitude)/2)*PI()/180)/180)
     *
     ((121.50817640000002-longitude)*PI()*12656*cos (((31.2991177+latitude)/2)*PI()/180)/180)
    )
    +
    (
     ((31.2991177-latitude)*PI()*12656/180)
     *
     ((31.2991177-latitude)*PI()*12656/180)
    )


)<2


 
25、数据库多行合并一行


----oracle 9i 一下


SELECT    n_sec_code, TRANSLATE (LTRIM (text, '/'), '*/', '*,') researcherList
 
     FROM (SELECT ROW_NUMBER () OVER (PARTITION BY n_sec_code ORDER BY n_sec_code,
 
                   lvl DESC) rn,
 
                  n_sec_code, text
 
             FROM (SELECT      n_sec_code, LEVEL lvl,
 
                              SYS_CONNECT_BY_PATH (c_researcher_code,'/') text
 
                         FROM (SELECT    n_sec_code, c_researcher_code as c_researcher_code,
 
                                        ROW_NUMBER () OVER (PARTITION BY n_sec_code ORDER BY n_sec_code,c_researcher_code) x
 
                                   FROM m_researcher_stock_rel
 
                               ORDER BY n_sec_code, c_researcher_code) a
 
                   CONNECT BY n_sec_code = PRIOR n_sec_code AND x - 1 = PRIOR x))
 
    WHERE rn = 1
 
ORDER BY n_sec_code;


-----oralce 10G以上


select 'create or replace view as select '|| wm_concat(column_name) || ' from dept'from user_tab_columns where table_name='DEPT'




---mysql 
select id,group_concat(name) from aa group by id;








26、Oracle Group By 用法之 —— Rollup


---
elect job,deptno,sum(sal) total_sal from emp group by rollup(job,deptno);




JOB              DEPTNO     TOTAL_SAL


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


ANALYST        20              6000


ANALYST                          6000


CLERK           10              1300


27、Oracle Group By 用法之 —— Cube






ORACLE中的KEEP()使用方法






28、 mysql 数据库读写分离
主服务器上进行的操作
授权给从数据库服务器从机192.168.10.131
mysql> GRANT REPLICATION SLAVE ON *.* to 'fa'@'192.168.199.249' identified by ‘fa123!@#’;


查询主数据库状态
Mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 261 | | |
+------------------+----------+--------------+------------------+


注释：
没有开启二进制日志
mysql>show variables like '%log_bin%';   如果是off表示你没有开启


开启日志服务:     
在my.ini 或my.cnf 文件添加log-bin 行  linux可能为my.cnf,其实只需要在[mysqld]项，把log-bin 前面的# 注释掉即可


从机：
执行同步SQL语句
mysql> change master to
master_host=’192.168.10.130’,
master_user=’rep1’,
master_password=’password’,
master_log_file=’mysql-bin.000005’,
master_log_pos=261;


正确执行后启动Slave同步进程
mysql> start slave;
注释：SET GLOBAL server_id=149
主从同步检查
mysql> show slave status\G


其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。