Oracle sql整理(二)
博客专区 > playgame 的博客 > 博客详情
Oracle sql整理(二)
playgame 发表于1年前
Oracle sql整理(二)
  • 发表于 1年前
  • 阅读 3
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 十分钟定制你的第一个小程序>>>   

摘要: Oracle SQL

-- 正则表达式
select * from student where regexp_like(student_name,'%[1-2]');

select * from student where regexp_like(student_name,'^[1-3]');

select * from student where regexp_like(student_name,'[^1-2]');


select * from student for update;

select * from result;
select * from grade;
select * from subject;


-- substr 和取位置
select substr(email,instr(email,'@')+1,length(email)) from student where student_id=15;

select substr(email,instr(email,'@')+1) from student where student_id=15;


select substr(email,0,instr(email,'@')-1) from student where student_id=15;

--随机数

select floor(dbms_random.value(1000,9999)) from dual;

select to_char(sysdate,'yyyymmdd') from dual;

select 'D1'||to_char(sysdate,'yyyymmdd')||floor(dbms_random.value(1000,9999))||'@test.com' from dual;

select add_months(sysdate,4) from dual;

select months_between(sysdate,to_date('2016-5-10','yyyy-mm-dd')) from dual;

select last_day(sysdate) from dual;

select next_day(sysdate,'星期一') from dual;

select extract(year from sysdate) year from dual;

select extract (year from date '2011-01-03') year from dual;

select extract (month from date '2011-01-03') month from dual;

select extract (day from  date '2011-01-03') day from dual;

select initcap('hello') from dual;

select lower('FUN GAME') from dual;

select upper('sun com') from dual;

select ltrim(' liubei ',' ') from dual;
select rtrim(' liubei ',' ') from dual;

select translate('jake','j','b') from dual;


select replace('this is a theacher tea','t','T') from dual;

select instr('this is test','test')from dual;

select substr('this is test',9,4) from dual;

select concat('Hello ','world') from dual;


select chr(116)  from dual;
select ascii('t') from dual;

select lpad('good',5,'=') str1 from dual;


select rpad('good',10,'-') from dual;


select lpad('func',15,'=') s1, rpad('func',15,'-') s2 from dual;


select * from student where student_name like '008dD[AC]G';

select * from student where regexp_like(student_name, '008dD[AC]G');

select * from student for update;

-- 删除空格
select trim('   apple    ') from dual;

select trim (both from ' apple ')  from dual;

select trim (trailing  from ' apple ')  from dual;

select trim (leading  from ' apple ')  from dual;

--trim 删除前后字符(注意:参数只允许包含一个字符,不支持多字符。)

select trim ('1' from '111111apple111111')  from dual;

select trim (both '2'  from '22222apple2222')  from dual;

select trim (trailing '2'  from '22222apple2222')  from dual;

select trim (leading '2'  from '22222apple2222')  from dual;

-- trim
select rtrim('22132apple22132','22132') from dual;
select ltrim('22132apple22132','22132') from dual;
select ltrim(rtrim('22132apple22132','22132'),'22132') from dual;

-- length
select length(1231231) from dual;
select length('this is test') from dual;

 

共有 人打赏支持
粉丝 0
博文 26
码字总数 10272
×
playgame
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: