Oracle 数据库SQL基础总结(1)

1.将指定字符串的某个字符替换成另外的字符

select replace('JACK AND JUE','J','BL') from dual;

输出结果:BLACK AND BULE 

分析:将第一个参数中的所有第二个参数的子字串,用第三个参数进行代替

2.与1类似,可以把字符串的多个字符进行替换

select translate ('adnima','aid','sbi') from dual;

输出结果:sinbms

分析 :将第二个参数的每个字符替换成第三个参数相对应的每个字符,优先级为从左到右,如果第二个参数字符长度大于第三个字符,则没对应上的字符在第一个参数中将被删除

因为考虑到在第二个参数没对应上的字符替换成nul了可能.

3 将字符串左侧补足,补足长度为第二个参数的值,第三个参数代表补足的字符(可以是字符串)

select lpad('123',8,'0') from dual;

输出结果:00000123

分析:字符串补足长度为8,假设第三个参数为多个字符的,补足长度为第二个参数长度-第一个参数长度这么长.

select lpad('123456789',8,'0') from dual;

输出结果:12345678

分析,如果第二个参数大于第一个参数的长度,则只截取第一个参数的前n位.有点类似trunc或substr(第一个参数,1,原第二个参数)方法了.

4.截取字符串

Select substr('123456',3,2) test from dual;--第二个参数也可以是负数代表从右边开始

输出结果:34

不分析了 地球人都知道

5.截取字符串,与4类似,增加了可以选择从第几个字符查起,是第几次出现的功能

select instr('CORPORATE FLOOR','OR',3,2) "InString" from dual;

输出结果:14

分析:第一个参数从第三个字符查起,第二个参数在第一个参数中第二次出现的字符所在的位置

select INSTR('CORPORATE FLOOR','OR',-3,2) "RInString" from dual;

输出结果:2

分析:第三个参数为负数代表从右边第三位查起

6.获取字符串的长度,与数据类型有关

select length(VRULE) from mdac002 ;

分析:表中vrule的值为03,varchar(5)数据类型,输出结果为2 ,如果数据类型是char(5),则输出结果为5

7.取绝对值

select ABS(-15) from dual;

8.取大于等于该值的最小整数,结果为16;如果参数为15,那么得到的结果为15

select CEIL(15.7) from dual;

分析 :取大于等于该值的最小整数,结果为16;如果参数为15,那么得到的结果为15

9.取余

select MOD(11,4) from dual; --取余数

10.四舍五入方法,第二个参数如果为正代表取小数点后第几位;如果为负代表取小数点前第几位

select round(15.193,2)"Round" from dual;

输出结果:15.19

分析:第二个参数如果什么都不输,等效第二个参数为0

select round(15.193,-1) from dual;

分析:-第二个参数为-1,代表四舍五入到个位,则结果为20,如果第二个参数为-2,则代表四舍五入十位,则为0

11.非四舍五入截取

select trunc(15.79,1)from dual;

输出结果:15.7

分析,单纯的截取,没有四舍五入

select trunc(15.79,-2) from dual;

输出结果:0

分析:截取十位数,即百位以前清0

12.--返回正负号,1正,0为0,-1为负
select sign(2) from dual;
select sign(0) from dual;
select sign(-2) from dual;

13.返回不大于该值的最大整数(与CEIL对应),参数为12时则输出12

select floor(12.3) from dual;

分析:返回不大于该值的最大整数(与CEIL对应),参数为12时则输出12

14.幂运算

select power(999999999999999,999999999999999) from dual;--幂运算

15.返回日期d加上n个月后的日期. n可以是任意整数。

select to_char(ADD_MONTHS(sysdate,-1),'yyyy-mm-dd') from dual;

输出结果:2011-12-10(当前时间2011-1-10)

分析:add_months函数返回当前日期后n个月的同一天,如果有1月31日往后延1月后不存在2月31日的情况

则返回2月的最后一天即2月28

16.取月份的最后一天

select sysdate,last_day(sysdate),last_day(sysdate)-sysdate from dual;--last_day

结果:2012-1-10 16:43:26,2012-1-31 16:43:26,21

分析:取当月最后一天，日期之间是可以相减的

组合起来用 ,查询当前月份往后5个月的那个月的最后一天是哪天

select last_day(add_months(sysdate,5)) from dual;
结果:2012-6-30 16:44:11

17.计算date1,date2之间月数。如果date1 date2，这两个日期的日分量是相同。或者都分别是所在月的最后一天。则返回整数，否则为小数，小数为剩余天数除以31
select months_between(to_date('2011-4-2','yyyy-mm-dd'),to_date('2011-5-1','yyyy-mm-dd'))*31 from dual--取两个日期间隔的月数
--如果两个日期的日变量是相同,或者为自己当月的最后一天,则返回的是一个整数;否则会返回一个小数,小数为剩余天数除以31.
--结果再 *31就能查出两个日期差多少个天
结果:-30

18。查看数据库所使用的日期字符集
select * from v$nls_parameters where parameter ='NLS_DATE_LANGUAGE'--查看数据库所使用的日期字符集

19.取D下一周的日期
select NEXT_DAY(SYSDATE,'星期二') "NEXTDAY" from dual--取第一个参数的下一周的第二个参数那天的日期,其中第二个参数与数据库日期字符集有关,
--我这里是简体中文

--通用写法是使用数字代表星期,1-7对应星期日-星期六

结果为:2012-1-17 16:44:58  当前日期为2012-1-10周二

20.取日期的四舍五入，按照第二个参数的format格式，如果没有第二个参数默认是'DD'

select  round(sysdate,'yyyy') from dual --取日期四舍五入值,如果第二个参数不输,则默认为DD格式
select round(to_date('2012-08-16','yyyy-mm-dd'),'MM') from dual--因为当前日为16号,所以四舍五入到9月
select round(sysdate,'DD') from dual --当前日期为2012-1-10 15:41 因为过了12点 则输出结果为2012-1-11

21.同20类似，截取日期不四舍五入
select  trunc(sysdate,'yyyy') from dual; --截取值,如果第二个参数不输,则默认为DD格式,与round相对应,不四舍五入
select trunc(sysdate,'MM') from dual;
select trunc(sysdate,'DD') from dual; --当前日期为2012-1-10 15:41 

22.转换函数

select to_char(9999,'9999,999') from dual;--将number型转换成字符串

结果为:9,999

select to_char(9999,'9999.99') from dual;

结果为:9999.00

select to_char(1234,'$9999.00') from dual;

结果为$1234.00

23.获取参数中最小值

select least(1,2,3) from dual--比较参数的大小,参数类型以第一个参数的类型为准,对应的比较参数的最大值 Greatest


24.判断代码是否是3XX41的形式两种方法
--Like方法: 
select * from emp where code like  '3__41';
--正则表达式法
select * from emp where regexp_like (code,'3..41')--like方法相对简单
--XX部分必须是数字型则增则表达法更简单
select * from emp where regexp_like(code,'3[0-9]{2}41');

25.正则表达式函数
select regexp_instr(code,'3..41',1,1) from emp;--从头查询3XX41字符串第一次出现的位置


select regexp_substr(address,'http[0-9a-zA-Z/:.]+') from emp--从参数1中截取符合参数2的字符串,例如截取网址信息


select regexp_replace(address,'http[0-9a-zA-Z/:.]+','www.163.com') from dual; --将所有的网址换成www.163.com




select id,row_number() over(Partition by id order by id) from mdac006d1--按照partition by 的值进行分组,然后每个分组按照order by进行排序,编号按照此顺序


select id,row_number() over(order by id) from mdac006d1--如果不加partition by 说明不按任何列进行分组,然后直接加一列进行排序 
--rank() over()和row_number() over()相似,但是可以支持并列第二名的情况