ORACLE查询笔记(mysql)

好记性当不得烂笔头，把工作、学习中一点东西作个笔记...

Oracle10g(10.2.0)测试通过，mysql5.6

--字符过滤1

 

select translate('12c24b31a2321', '\1234567890', '\') "filter number",    
translate('12c24b31a2321', '\ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', '\') "filter character",   
translate('12c24b31a2321!@#$%我的未来不是梦', '\ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', '\') "filter number and character"    
from dual;   

 

 

--字符过滤2

Select REGEXP_REPLACE('A1234aa我们,adb23','[[:digit:]]', '') "filter number",--任何数字    
REGEXP_REPLACE('A1234aa我们,adb23','[[:alpha:]]', '') "filter character",--任何字母    
REGEXP_REPLACE('A1234aa我们,adb23','[[:upper:]]', '') "filter en u character",--任何大写字母    
REGEXP_REPLACE('A1234aa我们,adb23','[[:punct:]]', '') "filter en l character",--任何小写字母    
REGEXP_REPLACE('A1234aa我们,adb23','[[:alnum:]]', '') "filter number and character",--任何字母和数字    
REGEXP_REPLACE('A1234aa我们,adb23','[0-9a-fA-F]', '') "filter number and character" --任何英文字母和数字    
FROM dual;   
 

 

---过滤查询(也可插入数据)主要针对特殊字符

select * from tables_name where col1 like '%'||chr(37)||'%';--%    
select * from tables_name where col1 like '%'||chr(38)||'%';--&    
select * from tables_name where col1 like '%'||chr(39)||'%';--'    
select * from tables_name where col1 like '%'||chr(95)||'%';--_    
select * from tables_name where col1 like '%^_%' escape '^';--_   
 

 

---随机取5条数据

select * from (select * from scott.emp order by dbms_random.random) where rownum<6;   
select * from (select * from scott.emp sample(20));--[0.000001,100)百分比例取数

select * from scott.emp order by  sys_guid();

 

 

--获得指定年之间的周末日期 当然每一个月就类推了

select * from (select decode(to_char(trunc(sysdate,'yyyy')+level-1,'d'),7, '周六',1,'周日',null) 星期几,   
trunc(sysdate,'yyyy')+level-1 当前日期 from dual connect by 1<=1 and    
level<=add_months(trunc(sysdate,'yyyy'),12)-1- trunc(sysdate,'yyyy')+1 )bbb where bbb.星期几 is not null;   
 

 

 

---乘法口诀查询(*转)伪列的使用(www.itpub.net)

select reverse(ltrim((sys_connect_by_path(   
              reverse( rownum|| 'X' || lv || '=' || lpad(rownum * lv, 2,'0')),'   ')   
            ))) "乘法口诀"  
  from (select level lv from dual connect by level < 10)   
 where lv = 1   
connect by prior lv = lv+1;  

 

---正则表达式简易实现分隔函数效果

 select * from 
 (select regexp_substr('abc,def,ghijkl,123,654','[^,]+',1,level) split from dual connect by  level<10)
where split is not null;

select substr('abcdef',rownum,1) split from dual connect by rownum<=length('abcdef');

select substr('a1,b22,c333,d4444,e,f',
    decode(rownum,1,1,instr('a1,b22,c333,d4444,e,f', ',', 1, rownum - 1) + 1),
    decode(rownum,length('a1,b22,c333,d4444,e,f') -
           length(replace('a1,b22,c333,d4444,e,f', ',', '')) + 1,
           length('a1,b22,c333,d4444,e,f') + 1,
           instr('a1,b22,c333,d4444,e,f', ',', 1, rownum)) -
    decode(rownum,1,1,instr('a1,b22,c333,d4444,e,f', ',', 1, rownum - 1) + 1)) split
  from t1 connect by rownum <= length('a1,b22,c333,d4444,e,f') -
           length(replace('a1,b22,c333,d4444,e,f', ',', '')) + 1;

 

----比较字符串是否相等，不考虑字符串顺序(保证字符串个数相等)，存在BUG缺陷

create or replace function fn_checkString_equal(firstVar varchar2,secondVar varchar2)
return varchar2
is 
v_temp varchar2(10);
v_first number(10):=0;
v_second number(10):=0;
begin
  if nvl(length(firstVar),0)=0 or nvl(length(secondVar),0)=0
     or nvl(length(firstVar),0)<>nvl(length(secondVar),0) then
    return 'false';
  end if;
  for a in (select substr(firstVar,rownum,1) split from dual connect by rownum<=length(firstVar))  
    loop
    v_first:=v_first+ascii(a.split);
  end loop;

  for b in (select substr(secondVar,rownum,1) split from dual connect by rownum<=length(secondVar))  
    loop
    v_second:=v_second+ascii(b.split);
  end loop;
  if v_second = v_first then
     return 'true';
  end if;
  return 'false';
exception 
 when others then
return 'false';
end fn_checkString_equal;

select fn_checkString_equal('abcdefg','adcgfeb') from dual;
true
select fn_checkString_equal('r','1A') from dual;
false
select fn_checkString_equal('中华人民共和国','中华共和国人民') from dual;
false

---上面函数例子存在漏洞，下面函数进行改进

create or replace function fn_checkString_equal2(firstVar varchar2,secondVar varchar2)
return varchar2
is
v_temp varchar2(10);
v_first varchar2(1000):='';
v_second varchar2(1000):='';
begin
  if nvl(length(firstVar),0)=0 or nvl(length(secondVar),0)=0
     or nvl(length(firstVar),0)<>nvl(length(secondVar),0) then
    return 'false';
  end if;
  for a in (select substr(firstVar,rownum,1) split from dual connect by rownum<=length(firstVar) order by split)
    loop
    v_first:=v_first||a.split;
  end loop;

  for b in (select substr(secondVar,rownum,1) split from dual connect by rownum<=length(secondVar) order by split)
    loop
    v_second:=v_second||b.split;
  end loop;
  if v_second = v_first then
     return 'true';
  end if;
  return 'false';
exception
 when others then
return 'false';
end fn_checkString_equal2;

 

   

 

---进制转换例

---十六进制的转换到十进制
select to_number('abc','xxxx') from dual;
----十进制转换到十六进制
select to_char('2748','xxxx') from dual;

 

 查找某一数据在某一个表中，登记一例作为引子，抛砖引玉(里面其实要考虑几个关键因素：数据类型，字段个数，字段之间分隔符的处理)，作一函数如下：

create or replace function finddatafromtable(in_table_name varchar2,in_condition varchar2)
return VARCHAR2 as 
tempsql varchar2(2000):='SELECT DECODE(sign(COUNT(*)),0,''false'',1,''true'') as flag FROM (SELECT ';
resultStr varchar2(10):='false';
begin 
 for column_cur in (select * from user_tab_columns where table_name=upper(in_table_name)) 
  loop
     --data type deal with date varchar number
     IF column_cur.data_type='DATE' THEN
        tempsql:=tempsql||'to_char('||column_cur.column_name||',''yyyy-mm-dd'')'||'||'',''||';  
     ELSE 
        tempsql:=tempsql||column_cur.column_name||'||'',''||';
     END IF;
  end loop;
  tempsql:=tempsql||'''''';
  tempsql :=tempsql||' as larcol FROM '||in_table_name||') where 1=1 and larcol LIKE ''%'||in_condition||'%''';
  dbms_output.put_line(tempsql);
 execute immediate tempsql into resultStr;
 return resultStr;
 exception when others then
 return 'false';
 end;

 ---继续补充

 

select sysdate S1,                              --返回当前日期 时分秒
       trunc(sysdate) S2,                       --返回当前日期
       trunc(sysdate,'year') YEAR,              --返回当前年的1月1日,无时分秒
       trunc(sysdate,'month') MONTH ,           --返回当前月的1日,无时分秒
       trunc(sysdate,'day') DAY,                --返回当前星期的星期天,无时分秒
       trunc(next_day(sysdate,7)) nSunday,      --下一个最近星期天 
       trunc(last_day(sysdate)) cmlastday,      --当前月的最后一天,无时分秒
       floor(dbms_random.value(10,20)) Random,  --随机产生10-20之间的数值  10<=a<20
       add_months(trunc(sysdate,'year'),12) - floor(dbms_random.value(1, --随机获取当前年的任一天
       (add_months(trunc(sysdate,'year'),12)- trunc(sysdate,'year')+1))) RandomDay,
       dbms_random.string('X',5) randomChar      --随机产生5个数字及字符(X,U,L,P,A) 
   from dual;

 

补充一点MYSQL的日期获取样例：

SELECT concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m'),'-01'); ##上个月的第一天
SELECT date_format(LAST_DAY((concat(date_format(now(),'%Y-%m'),'-01')) - interval 1 day),'%Y-%m-%d'); ##上个月的最后一天
SELECT concat(date_format(now(),'%Y-%m'),'-01'); ##当前月的第一天
SELECT date_format(LAST_DAY(now()),'%Y-%m-%d'); ##当前月的最后一天
SELECT date_format(LAST_DAY(now()),'%Y-%m-%d') + INTERVAL 1 day; ##下个月的第一天
SELECT date_format(LAST_DAY(date_format(LAST_DAY(now()),'%Y-%m-%d') + INTERVAL 1 day),'%Y-%m-%d'); ##下个月的最后一天
SELECT concat(date_format(sysdate(),'%Y'),'-01-01'); ##当前年的第一天，也可以指定年度
SELECT concat(date_format(sysdate(),'%Y'),'-12-31'); ##当前年的最后一天，也可以指定年度
SELECT concat(year(sysdate()),'-01-01'); ##当前年的第一天，也可以指定年度
SELECT concat(year(sysdate()),'-12-31'); ##当前年的最后一天，也可以指定年度
SELECT TO_DAYS(concat(year(sysdate()),'-12-31')) - TO_DAYS(concat(year(sysdate()),'-01-01')) + 1;##计算年的天数
SELECT TO_DAYS(concat(year('2008-01-01'),'-12-31')) - TO_DAYS(concat(year('2008-01-01'),'-01-01')) + 1;##计算年的天数
select NOW(),DAYOFWEEK(sysdate()), DAYOFWEEK(sysdate() + INTERVAL 1 day); #6  7  2014-12-26  返回日期date是星期几(1=星期天,2=星期一,……7=星期六,ODBC标准)
select NOW(),WEEKDAY(sysdate()), WEEKDAY(sysdate() + INTERVAL 1 day);# 4 5  2014-12-26  返回日期date是星期几(0=星期一,1=星期二,……6= 星期天)。

 

mysql关于年度周末获取的select 语句：

select date_,case dweek when 6 then '星期六' else '星期日' end "星期几"  from (select ret1.*, DAYOFWEEK(ret1.date_) dweek from (
select (concat(year(sysdate()),'-01-01') + INTERVAL (r_-1) day ) date_,r_ from ( 
select table_name,@rownum:=@rownum+1 r_,days
	from (select * from information_schema.`COLUMNS` ) c,
			(select (TO_DAYS(concat(year(sysdate()),'-12-31')) - TO_DAYS(concat(year(sysdate()),'-01-01')) + 1) as days, @rownum:=0) a
) ret where r_<=days) ret1) ret2 where dweek in (6,7);

 

 

mysql父子节点查询（通过调用自定义函数）

 

select FIND_IN_SET(id ,getTreeList(1)),a.* from LG_PRODUCT_CATEGORY a  where  FIND_IN_SET(id ,getTreeList(1));

select id,PARENT_ID,NAME_EN,NAME_TC from LG_PRODUCT_CATEGORY where PARENT_ID=1

select * from LG_PRODUCT_CATEGORY where PARENT_ID=1

drop FUNCTION `getTreeList`;

CREATE FUNCTION `getTreeList`(rootId INT)
     RETURNS varchar(2000)
     BEGIN
       DECLARE sTemp VARCHAR(2000);
       DECLARE sTempChd VARCHAR(1000);
    
       SET sTemp = '$';
       SET sTempChd =cast(rootId as CHAR);
    
       WHILE sTempChd is not null DO
         SET sTemp = concat(sTemp,',',sTempChd);
         SELECT group_concat(id) INTO sTempChd FROM LG_PRODUCT_CATEGORY where FIND_IN_SET(parent_id,sTempChd)>0;
       END WHILE;
       RETURN sTemp;
     END