Oracle数据库练习

数学函数

1. 数学函数接受数字输入并返回数值结果

--绝对值 abs

select abs(-15) from dual;

--取整(四舍五入)

select ceil(44.875) from dual; 

--余弦函数

select cos(180) from dual;

--取整

select floor(100.55） from dual;

--平方

select power(4,2) from dual;

--余数

select mod(10,3) from dual;

--保留两位小数(不四舍五入)

select round (100.256,2) from dual;

--保留两位小数(四舍五入)

select trunc 100.256,2) from dual;

--开平方

select sqrt(4) from dual;

--符号 当x>0，sign(x)=1;当x=0，sign(x)=0; 当x<0， sign(x)=-1；

select sign(-30) from dual;

字符函数

• concat(s1,s2)：
  • 连接字符串s1和s2，返回连接后的字符串；

• substr(s,m,[n])：
  • 取子串函数，从s串中第m个字符起，取长度为n的一个子串，如果n省略，一直到s串的尾部；

• length(s)：
  • 返回字符串s的长度；

• lower(s)：
  • 将字符串s中的所有字母变为小写；

• upper(s)：
  • 将字符串s中的所有字母变为大写；

• ltrim(s)：
  • 移走左边的空格字符；

• rtrim(s)：
  • 移走右边的空格字符；

• replace(s1,s2,[s3])：
  • 字符串s1中查找s2字符串，并用s3字符串代替，如果s3省略，删除s1中所有的s2串；

• instr(s1,s2)：
  • s1串中查找s2串出现的位置，并作为函数返回值，如果未查到返回0；

字符函数接受字符并返回字符或数值

--首字母大写

select initcap ('hello'）from dual;

--全部小写

select lower ('FUN') from dual;

--全部大写

select upper ('sun') from dual;

--字符长度

select length('sun') from dual;

--截取xyz后面的所有字符

select ltrim('xyzadms','xyz') from dual;

--截取ams前面的所有字符

select rtrim('xyzadams','ams') from dual;

--替换（把 j 替换为 b ）

--translate(str,src,dest)

--语法同replace()有点类似,但该替换是按照字符来替换的,

--意思是将str中含src中的字符替换成dest的字符.

--因为该函数是针对字符的,所以src与dest的有效字符是一一对应的.

--(即:有效长度是一样的),也就是通常要求src的长度=dest的长度

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

--替换（把 j 替换为 bl）

--replace(str,oldStr,newStr)

--该函数时将str中存在的oldStr替换成newStr,

select replace('jack and jue','j','bl')  from dual;

--查找字符串的位置（从1 开始数）

select instr('worldwide','d') from dual; 

--截取字符串（从第3个开始截取2个字符）

select substr('abcdefg',3,2) from dual; 

--截取字符串（从倒数第4个开始往后截取3个字符）

select substr('abcdefg',-4,3) from dual; 

--连接字符

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

日期函数

名称	说明
sysdate	获取日期和时间
months_between	获取两个日期之间的月份间隔
add_months(date,n)	在指定日期上添加月份，返回新日期
next_day(date,n)	获取指定日期所在周的第n天的日期。周日是一个星期的第一天
last_day(date)	每月的最后一天

select months_between(to_date('2004/02/12','yyyy/mm/dd') ,

to_date('2000/01/01','yyyy/mm/dd') ) as  "月份间隔" from dual;

转换函数

• 转换函数主要有三种：

• to_char(date,m):将日期转换为m所指定的格式

• to_date(m,n):将字符串m转换为日期类型，n用于描述字符串m的格式

• to_number(m,n):将字符串m转换为数值，n用于描述字符串m的格式

to_char函数

• to_char函数在使用时，经常结合Oracle中自带的日期元素，例如：

select to_char(sysdate,'yyyy-mm-dd dy am hh:mi:ss')

from dual;

执行结果为：

TO_CHAR(SYSDATE,'YYYY-MM-DDDYAMHH:MI:SS')

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

2013-05-20 星期一 下午 02:43:12

日期格式元素

元素	用　　途
YYYY YY	年的表示：YYYY表示4位数字，YY表示2位数字。
MM MONTH MON	月份表示：MM月份的数字表示,MONTH有为月的名称，MON为月名称的缩写,在中文环境下，MONTH,MON都是“X月” 的形式
DD DDD	DD：当月的第几天;DDD：当年的第几天
D DY DAY	表示星期几:D-数字表示；DAY-英文表示，DY-缩写，在中文环境下，DAY和DY都是“星期X”形式
HH HH24 SS MI	时间表示：HH为小时、SS为秒、MI为分。HH24为24小时形式
AM　PM	表示时间的上午和下午
-/,.;:	在结果中插入起分隔作用的标点符号

to_date函数

• to_date(m,n)函数用于将字符串转为日期类型，第二个参数用来描述第一个参数的格式，以告诉sqlplus字符串m中哪部分是年份，哪部分是月份，哪部分是日期等等。
  • 例如：
    • select to_date('2013-05-14','yyyy-mm-dd') from dual;
    • select to_date('14-05-2013','dd-mm-yyyy') from dual;
    • select to_date('05-14-2013','mm-dd-yyyy') from dual;

to_number函数

• to_number(m,n)函数用于将字符串m转换为数值，参数n用以说明m的格式。
  • 例如，将字符串'$12,345.6'转换为数值:

select to_number('$12,345.6','$99,999.9') from dual;

其结果是:12345.6。如果将‘￥12,345.6’转换为数值：

select to_number('￥12,345.6','L99,999.99') from dual;

其结果也是12345.6

数字格式元素

数字格式	规则
9	每一个9代表一个字符
0	先导符号0
.	在指定的位置插入小数点
,	在制定的位置插入逗号
$	匹配先导的$符号
L	匹配先导的￥符号

select to_number('0023','0099') from dual;结果是23

select to_number('03.14','09.99') from dual;结果是3.14

其他

NVL函数

select ename, nvl(sar,0) from emp;

• 利用伪列ruwnum查询当前的纪录
  • select rownum,d.* from dept d where rownum between 1 and 3

高级子查询

• 成对比较，即在查询时，可以使用两个列同时和子查询的结果进行比较，子查询的结构也是两个列组成。
  • 例:查询和jacky在同一部门且性别相同的员工信息:

select * from emp

where (sex,did) in(select sex,did from emp where ename='jacky');

标量子查询

• case语句用于标量子查询，例如查询员工信息，将m替换成“男”，f替换成“女”

select eid,ename,

case sex

when 'm' then '男'

when 'f' then '女'

else sex

end

from emp;

exists

• exists用于存在性测试，通常在子查询中，需要将子、父查询的表相连接作为查询条件.
• 例如查询存在员工的部门
• select d.* from dept d where exists
• (select e.* from emp e where e.did=d.did);
  • 或查询不存在员工的部门：
• select d.* from dept d where not exists
• (select e.* from emp e where e.did=d.did);

With语句

• with语句定义一个变量来表示一个语句，通过引用变量来引用子句，实现重用，提高效率。例如，改写上一章中的查询工资比其所在部门平均工资高的员工信息
• SQL> with a as(select eid,sar,did from emp),
•   2  b as(select did,avg(sar) sar from emp group by did)
•   3  select * from emp where eid in(
•   4  select a.eid from a,b where a.did=b.did and a.sar>b.sar);