Oracle11g学习-运算符/常用函数

Oracle

1 运算符

1.1      算术运算符

+、-、*、/

1.2      比较（关系）运算符

＝、!＝、<>、< 、 >、 <= 、 >= 、 between...and... 、in 、like、is null

1.3      逻辑运算符

AND(逻辑与)，表示两个条件必须同时满足

OR(逻辑或)，表示两个条件中有一个条件满足即可

NOT(逻辑非)，返回与某条件相反的结果

1.4      连接运算符

||

【示例】 select'工号为：'|| empno ||' 的员工的姓名为：'|| ename from emp;

1.5      集合运算符

union（并集无重复）

union all（并集有重复）

intersect（交集，共有部分）

minus（减集，第一个查询具有，第二个查询不具有的数据）

【注意】：列数相关，对应列的数据类型兼容，不能含有Long类型的列，第一个select语句的列或别名作为结果标题

--union(并集将去重复) Select * from emp where deptno=10 union select * from emp where deptno=20;   --intersect(交集) 查询工资即属于1000~2000区间和1500~2500区间的工资 select ename,sal from emp where sal between 1000 and 2000 intersect select ename,sal from emp where sal between 1500 and 2500;   --minus(减集) select ename,sal from emp where sal between 1000 and 2000 minus select ename,sal from emp where sal between 1500 and 2500;

1.6        运算符优先级

优先级	运算符
1	算术运算符
2	连接符
3	比较符
4	IS[NOT]NULL, LIKE, [NOT]IN
5	[NOT] BETWEEN
6	NOT
7	AND
8	OR

可以使用括号改变优先级顺序；OR的优先级最低，算术运算符的优先级最高。

2 常用函数

2.1      数值型函数

round(x[,y])

【功能】返回四舍五入后的值 【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则四舍五入为y位小数，如果y小于0则四舍五入到小数点向左第y位。 【返回】数字 【示例】 Selectround(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666)from dual; 返回：   5555.67     ,    5600    ,    5556

trunc(x[,y])

【功能】返回x按精度y截取后的值 【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则截取到y位小数，如果y小于0则截取到小数点向左第y位，小数前其它数据用0表示。 【返回】数字 【示例】 selecttrunc(5555.66666,2.1), trunc(5555.66666,-2.6),trunc(5555.033333)from dual; 返回：5555.66                    5500               5555

2.2      字符型函数

LENGTH(c1)

【功能】返回字符串的长度; 【说明】多字节符(汉字、全角符等)，按1个字符计算 【参数】C1 字符串 【返回】数值型 【示例】 selectlength('传智播客'),length('itcast传智播客')from dual;   LENGTH('传智播客') LENGTH('ITCAST传智播客') ------------------ ------------------------                  4                       10

 LPAD(c1,n[,c2])、RPAD(c1,n[,c2])

【功能】在字符串c1的左（右）边用字符串c2填充，直到长度为n时为止 【说明】如果c1长度大于n，则返回c1左边n个字符 【参数】C1 字符串         n 追加后字符总长度         c2 追加字符串,默认为空格 【返回】字符型   【示例】 selectlpad('itcast',10,'*'),rpad('itcast',10,'*')from dual;

 REPLACE(c1,c2[,c3])

【功能】将字符表达式值中，部分相同字符串，替换成新的字符串 【参数】         c1   希望被替换的字符或变量         c2   被替换的字符串         c3   要替换的字符串，默认为空(即删除之意，不是空格) 【返回】字符型   【示例】 selectreplace('he love you','he','i')from dual;

 SUBSTR(c1,n1[,n2])

【功能】取子字符串 【说明】多字节符(汉字、全角符等)，按1个字符计算 【参数】在字符表达式c1里，从n1开始取n2个字符;若不指定n2,则从第n1个字符直到结束的字串. 【返回】字符型   【示例】 Select substr('123456789',4,4),substr('123456789',3)from dual;

2.3      日期函数

 sysdate

【功能】：返回当前日期。 【参数】：没有参数，没有括号 【返回】：日期   【示例】select sysdate from dual;

add_months(d1,n1)

【功能】：返回在日期d1基础上再加n1个月后新的日期。 【参数】：d1，日期型，n1数字型 【返回】：日期   【示例】select sysdate,add_months(sysdate,3)from dual;

 months_between(d1,d2)

【功能】：返回日期d1到日期d2之间的月数。 【参数】：d1，d2 日期型 【返回】：数字 如果d1>d2，则返回正数 如果d1<d2，则返回负数   【示例】 selectsysdate, months_between(sysdate,to_date('2015-01-01','YYYY-MM-DD'))距2015元旦, months_between(sysdate,to_date('2016-01-01','YYYY-MM-DD'))距2016元旦from dual;

 extract(c1 from d1)

【功能】：日期/时间d1中，参数(c1)的值 【参数】：d1日期型(date)/日期时间型(timestamp),c1为字符型(参数) 【参数表】：c1对应的参数表详见示例 【返回】：字符   【示例】 select  extract(YEARfrom timestamp '2015-5-1 12:26:18')年, extract(MONTH from timestamp '2015-5-1 12:26:18 ')月, extract(DAY from timestamp '2015-1-5 12:26:18 ')日, extract(hour from timestamp '2015-5-1 12:26:18 ')小时, extract(minute from timestamp '2015-5-1 12:26:18')分钟, extract(second from timestamp '2015-5-1 12:26:18 ')秒 from dual;   select extract(YEAR from date '2015-5-1')from dual;  select sysdate 当前日期, extract(YEAR from sysdate)年, extract(MONTH from sysdate)月, extract(DAY from sysdate)日 from dual;   --如下语句也可获取年份、月份等 select to_number(to_char(sysdate,'yyyy'))from dual;

2.4      转换函数

TO_CHAR(x[[,c2],C3])

【功能】将日期或数据转换为char数据类型 【参数】        x是一个date或number数据类型。        c2为格式参数        c3为NLS设置参数 【返回】varchar2字符型 【示例】 select to_char（sysdate,'YYYY-MM-DD HH24:MI:SS'）FROM dual; select to_char（1210.7,'$9,999.00'）FROM dual;

 TO_DATE(X[,c2[,c3]])

【功能】将字符串X转化为日期型 【参数】c2,c3,字符型，参照to_char() 【返回】字符串 如果x格式为日期型(date)格式时，则相同表达：date x 如果x格式为日期时间型(timestamp)格式时，则相同表达：timestamp x   【示例】 select to_date('201212','yyyymm'), to_date('2012.12.20','yyyy.mm.dd'), (date'2012-12-20') XXdate, to_date('2012-12-20 12:31:30','yyyy-mm-dd hh24:mi:ss'), to_timestamp('2012-12-20 12:31:30','yyyy-mm-dd hh24:mi:ss'), (timestamp'2012-12-20 12:31:30') XXtimestamp from dual;

 TO_NUMBER(X[[,c2],c3])

【功能】将字符串X转化为数字型 【参数】c2,c3,字符型 【返回】数字串   【示例】 select TO_NUMBER('201212')+3,TO_NUMBER('450.05')+1from dual; --等同上述结果 Select '201212'+3 from dual;

2.5      聚合函数

 sum:求和

avg:求平均数

count：计数

max：求最大值

min：求最小值

2.6      分析函数

分析函数中了解rank()/dense_rank()/row_number()的使用：

--查询部门的员工工种情况，并在部门内重新进行排序；PARTITION BY类似group by,根据ORDER BY排序字段的值重新由1开始排序。   --RANK 使用相同排序排名一样，后继数据空出排名；即有2个排序为1的，那么接下来的排序号则为 select deptno,ename,job,rank()over(partition by deptno order by job)as myRank from emp e;   --DENSE_RANK使用，使用相同排序排名一样，后继数据不空出排名；即有2个排序为1的，那么接下来的排序号则为2 select deptno,ename,job,dense_rank()over(partitionby deptno orderby job)as myDenseRank from emp e;   --ROW_NUMBER使用，不管排名是否一样，都按顺序排名；即有2个排序为1的，那么排序号不会重现重复 select deptno,ename,job,row_number()over(partitionby deptno orderby job)as myRowNumber from emp e;

 2.7      其它函数

NVL()/NVL2()

【语法】NVL (expr1, expr2) 【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。注意两者的类型要一致 【示例】将员工的奖金如果是空的话则设置为0 select ename,sal,comm,nvl(comm,0)from emp;     【语法】NVL2 (expr1, expr2, expr3) 【功能】expr1不为NULL，返回expr2；expr2为NULL，返回expr3。     expr2和expr3类型不同的话，expr3会转换为expr2的类型 【示例】 select ename,job,nvl2(job,'job有值','job无值')from emp;

 decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

【功能】根据条件返回相应值 【参数】c1, c2, ...,cn,字符型/数值型/日期型，必须类型相同或null 注：值1……n 不能为条件表达式,这种情况只能用case when then end解决   含义解释：　　 　　decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)　　 　　该函数的含义如下：　　 　　IF 条件=值1 THEN 　　RETURN(翻译值1) 　　ELSIF 条件=值2 THEN 　　RETURN(翻译值2) 　　...... 　　ELSIF 条件=值n THEN 　　RETURN(翻译值n)　　 　　ELSE 　　RETURN(缺省值) 　　END IF   【示例】根据员工的部门号，条件判断找到对应的部门名称 select ename,deptno,decode(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES','无部门')from emp;