oracle基础

[color=red]一、函数-数学函数[/color]round()
四舍五入,默认精确到个位，指定精确到小点后几位，当然还有一种特殊情况如果参数为负数表示向整数部分舍位，如下sql

select round(23.652) from dual;
select round(23.652,1) from dual;
select round(23.652,-1) from dual;

trunc()
截断小数位数，按指定的精度截断小数或整数（不进行四舍五入运算）

select trunc(25.46,1) from dual 结果为:25.4
select round (25.46,1) from dual 结果为:25.5
[color=red]select trunc(25.46,-1) from dual; 结果为:20
select round (25.46,-1) from dual; 结果为:30 [/color]

mod()
对一个数取余数
select mod(10,3) from dual ; 结果：1

[color=red]二、函数-字符函数[/color]

字符函数在实际开发也占据着重要的位置，如替换员工的姓名，截取某字段部分字符等等
lower()
将字符串转化为小写
select fname from t_person where lower(fname ) like '%y%';
upper()
将字符串转化为大写
Select fname from t_person where upper(fname ) like '%L%';
initcap()
将单词的首字母转为大写，其余字母转为小写
Select initcap ('HELLO WORLD') FROM dual; 结果为 Hello World
concat()
将字符串相连
substr()
字符串截取

length() 计算长度
replace() 字符替换
select substr('hello',3,2) 取子串,length('hello') 长度,replace('hello','l','x')from dual
chr() 将数字转化其对应的ascii码字符
select chr(65) from dual;
ascii() 将对应的字符转化为其ascii码对应的数字
select ascii('A') from dual;
to_char() 将数字或日期转化为字符串
to_char()在使用的时候要用到格式控制的符,格式控制符不区分大小写字母
年:Y,年份为四位数，故应写成:YYYY或yyyy
月:M,月份为两位数,故应写成:MM或mm
日:D，日为两位数，故应写成:DD或dd
对系统的日期格式显示方式进行转换，使其按中国日期习惯显示，即”YYYY-MM-DD”
select fname, to_char(fbirthday,'yyyy.mm.dd') from t_person

[color=red]三、函数-时间和日期函数[/color]

日期时间类型的数据也是经常用到的，比如员工出生日期、结账日期、入库日期等等，而且经常需要对这些数据进行处理，比如检索所有超过保质期的商品、将结账日期向后延迟3 天、检索所有每个月18 日的入库记录，进行这些处理就需要使用日期时间函数。SQL中提供了丰富的日期时间函数用于完成这些功能，本节将对这些日期时间函数进行详细讲解。

日期、时间、日期时间与时间戳

根据表示的类型、精度的不同，数据库中的日期时间数据类型分为日期、时间、日期时间以及时间戳四种类型。日期类型是用来表示“年-月-日”信息的数据类型，其精度精确到“日”，比如“2008-08-08”。日期类型可以用来表示“北京奥运会开幕式日期”、“王小明的出生年月日”等信息，但是无法表示“最近一次迟到的时间”、“徐总抵京时间”等精确到小时甚至分秒的数据。在数据库中，一般用Date来表示日期类型
时间类型是用来表示“小时:分:秒” 信息的数据类型，其精度精确到“秒”，其中包含了小时、分、秒三个信息，比如“19:00:00”。时间类型可以用来表示“每天《新闻联播》的播出时间”、“每天的下班时间”等信息，但是无法表示“卢沟桥事变爆发日期”、“上次结账时间”等包含“年-月-日”等信息的数据。在数据库中，一般用Time来表示时间类型

日期时间类型是用来表示“年-月-日小时:分:秒”信息的数据类型，其精度精确到“秒”，其中包含了年、月、日、小时、分、秒六个信息，比如“2008-08-08 08:00:00”。日期时间类型可以用来表示“北京奥运会开幕式准确时间”、“上次迟到时间”等信息。在数据库中，一般用DateTime 来表示日期时间类型

日期时间类型的精度精确到“秒”，这在一些情况下能够满足基本的要求，但是对于精度要求更加高的日期时间信息则无法表示，比如“刘翔跑到终点的时间”、“货物A 经过射频识别器的时间”等更高精度要求的信息。数据库中提供了时间戳类型用于表示这些对精度要求更加高的场合。
时间戳类型还可以用于标记表中数据的版本信息，比如我们想区分表中
两条记录插入表中的先后顺序，由于数据库操作速度非常快，如果用DateTime 类型记录输入插入时间的话，若两条记录插入的时间间隔非常短的话是无法区分它们的，这时就可以使用时间戳类型。在有的数据库系统中，如果对数据表中的记录进行了更新的话，数据库系统会自动更新其中的时间戳字段的值。数据库中，一般用TimeStamp 来表示日期时间类型

不同的数据库系统对日期、时间、日期时间与时间戳等数据类型的支持差异性非常大，有的数据类型在有的数据库系统中不被支持，而有的数据类型的表示精度则和其类型名称所暗示的精度不同，比如MSSQLServer 中不支持Time 类型、Oracle 中的Date 类型中包含时间信息。数据库中的日期时间函数对这些类型的支持差别是非常小的，因此在一般情况下我
们将这些类型统一称为“日期时间类型”。
Oracle 中以字符串表示的数据是不能自动转换为日期时间类型的，必须使用TO_DATE()函数来手动将字符串转换为日期时间类型的，比如TO_DATE('2008-08-08','YYYY-MM-DD HH24:MI:SS') 、TO_DATE('2008-08-08 08:00:00', 'YYYY-MM-DDHH24:MI:SS')、TO_DATE('08:00:00', 'YYYY-MM-DD HH24:MI:SS')等
Oracle 中没有提供取得当前日期时间的函数，不过我们可以到系统表DUAL 中查询SYSTIMESTAMP的值来得到当前的时间戳。如下：
SELECT SYSTIMESTAMP FROM DUAL
同样，我们可以到系统表DUAL中查询SYSDATE 的值来得到当前日期时间。如下：
SELECT SYSDATE FROM DUAL
同样，Oracle 中也没有专门提供取得当前日期、取得当前时间的函数，不过我们可以将SYSDATE 的值进行处理，这里需要借助于TO_CHAR()函数，这个函数的详细介绍稍后就会见到

[color=red]四、日期增减[/color]
有时我们需要在一个日期的基础上增加某个时间长度或者减去某个时间长度，比如我们知道每个员工的出生日期，而想计算出他出生后10000 天的日期，再如我们想计算所有合同的到期日的三月后的日期。由于存在每个月天数不同、闰月等复杂的历法规则，所以不能使用简单的数字加减法进行计算，主流的数据库系统中都提供了对日期增减的计算。
Oracle中可以直接使用加号“+”来进行日期的加法运算，其计算单位为“天”，比如date+3就表示在日期date的基础上增加三天；同理使用减号“-”则可以用来计算日期前的特定时间段的时间，比如date+3就表示在日期date的三天前的日期。比如下面的SQL语句用于计算每个人出生日期3天后以及10天前的日期：
SELECT FBirthDay,
FBirthDay+3,
FBirthDay-10
FROM T_Person
可以使用换算的方式来进行以周、小时、分钟等为单位的日期加减运算，比如下面的SQL语句用于计算每个人出生日期2小时10分钟后以及3周后的日期：
SELECT FBirthDay, FBirthDay+(2/24+10/60/24),
FBirthDay+(3*7) FROM T_Person
使用加减运算我们可以很容易的实现以周、天、小时、分钟、秒等为单位的日期的增减运算，不过由于每个月的天数是不同的，也就是在天和月之间不存在固定的换算率，所以无法使用加减运算实现以月为单位的计算，为此Oracle中提供了ADD_MONTHS()函数用于以月为单位的日期增减运算，ADD_MONTHS()函数的参数格式如下：
ADD_MONTHS(date,number)
其中参数date为待计算的日期，参数number为要增加的月份数，如果number为负数则表示进行日期的减运算。下面的SQL语句用于计算每个人的出生日期两个月后以及10个月前的日期：
SELECT FBirthDay, ADD_MONTHS(FBirthDay,2),
ADD_MONTHS(FBirthDay,-10)FROM T_Person

[color=red]五、计算日期差额[/color]
有时候我们需要计算两个日期的差额，比如计算“回款日”和“验收日”之间所差的天数或者检索所有“最后一次登录日期”与当前日期的差额大于100天的用户信息。主流的数据库系统中都提供了对计算日期差额的支持。
Oracle中可以直接使用加号“+”来进行日期的加法运算，其计算单位为“天”，比如date+3就表示在日期date的基础上增加三天；同理使用减号“-”则可以用来计算日期前的特定时间段的时间，比如date+3就表示在日期date的三天前的日期。比如下面的SQL语句用于计算每个人出生日期3天后以及10天前的日期：
SELECT FBirthDay,
FBirthDay+3,
FBirthDay-10
FROM T_Person
可以使用换算的方式来进行以周、小时、分钟等为单位的日期加减运算，比如下面的SQL语句用于计算每个人出生日期2小时10分钟后以及3周后的日期：
SELECT FBirthDay, FBirthDay+(2/24+10/60/24),
FBirthDay+(3*7) FROM T_Person
使用加减运算我们可以很容易的实现以周、天、小时、分钟、秒等为单位的日期的增减运算，不过由于每个月的天数是不同的，也就是在天和月之间不存在固定的换算率，所以无法使用加减运算实现以月为单位的计算，为此Oracle中提供了ADD_MONTHS()函数用于以月为单位的日期增减运算，ADD_MONTHS()函数的参数格式如下：
ADD_MONTHS(date,number)
其中参数date为待计算的日期，参数number为要增加的月份数，如果number为负数则表示进行日期的减运算。下面的SQL语句用于计算每个人的出生日期两个月后以及10个月前的日期：
SELECT FBirthDay, ADD_MONTHS(FBirthDay,2),
ADD_MONTHS(FBirthDay,-10)FROM T_Person

在Oracle中，可以在两个日期类型的数据之间使用减号运算符“-”，其计算结果为两个日期之间的天数差，比如执行下面的SQL语句用于计算注册日期FRegDay和出生日期FBirthDay之间的时间间隔：
SELECT FRegDay,FBirthDay,FRegDay-FBirthDay FROM T_Person
注意通过减号运算符“-”计算的两个日期之间的天数差是包含有小数部分的，小数部分表示不足一天的部分，比如执行下面的SQL语句用于计算当前时刻和出生日期FBirthDay之间的时间间隔：
SELECT SYSDATE,FBirthDay,SYSDATE-FBirthDay FROM T_Person
取得日期的指定部分
提取日期的特定部分是非常有必要的，比如检索本年的每个月的16日的销售量、检索访问用户集中的时间段，这些都需要对日期的特定部分进行提取，在主流数据库中对这个功能都提供了很好的支持，下面分别进行介绍

[color=red]六、多表查询[/color]
[color=red]a.迪卡尔积运算[/color]
前面我们所做的查询都是基于一张表的查询，但很多时候我们要用到一张以上的表，这样的查询称为多表查询，多表查询的语法如下:
SELECT {DISTINCT}*|查询列1 别名1，查询列2 别名2……
FORM 表名称`1 别名1,表名称2 别名2,……
{WHERE 条件表达式}
{ORDER BY 排序字段 ASC|DESC,排序字段 ASC|DESC,……}
例:用多表查询查询emp表和dept表中的信息
SELECT * FROM emp,dept
发现查询出来的结果太多，[color=red]是两表记录数的乘积。[/color]这是因为没的指定条件，emp和dept进行的是迪卡尔积运算.
要想去掉迪卡尔积运算，必须使用公共字段进行连接，这种方式也叫等值连接
查看两张表，他们有一个公共的字段deptno,故连接条件为: emp.deptno=dept.deptno
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno
[color=red]b.非等值连接[/color]
等值连接是将一张或一张以上的表通过关联字段连接，我们还可以在连接中采用其他运算符，其他运算符产生的连接叫做非等值连接
在scott用户的表空间里面有一张表叫salgrade，即工资等级，查看数据：
Select * from salgrade
我们看见工资分为五个等级，每个等级都有最高和最低工资，雇员的工资可以参照该表的记录查询出相应的等级，而这两张表并没有关联的字段，富所以我们只能采用非等值连接
Select e.ename ,e.sal,s.grade from emp e,salgrade s
Where e.sal between s.losal and s.hisal
[color=red]c.外部连接[/color]
回头看这个等值连接
SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e,dept d
WHERE e.deptno=d.deptno
我们发现，在部门表中有部门编号为40的记录并没有出现在结果集中，但有时候我们又需要显示这样的信心该怎么办呢？这里先明确两个概念：类似dept的表叫做不缺乏连接信息的表，而emp叫做缺乏连接信息的表，因为他数据中没有在40部门的员工
[color=red]执行下面的语句:
SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e,dept d
WHERE e.deptno(+)=d.deptno
我们发现多出一条记录，这条记录没有emp的信息而只有部门编号为40的部门信息，这种连接叫做外连接

[/color][color=red]d.自然连接[/color]
在oracle9i后，使用natural join可以实现具有列名和数据类型相匹配的连个表的自动连接，但要满足下面条件：
1：连个表有同名字段
2：返回两个表相匹配的列中具有相同值的列
Select empno,ename,sal,deptno,loc from emp natural join dept
使用using子句创建连接（无连接就是迪卡尔积运算）
自然连接可以通过同名字段进行连接(不同名报错)，而使用using子句可以指定产生连接的列：
Select e.ename,e.sal,deptno,d.loc from emp e join dept d using(deptno)
这里要注意的是，被using所使用的列名在语句的任何地方都不能使用别名
使用on子句创建连接
自然连接基本上是自动找寻相同列名进行连接，我们可以用on子句指定任意连接列：
Select e.ename,e.sal,d.deptno,d.loc from emp e join dept d
On e.deptno=d.deptno
On子句也可以用于列名不同的情况，我们用on实现emp表的自连接：
Select e.ename,e.sal,e.mgr,m.ename from emp e join emp m
On e.mgr=m.empno
我们再来看用on连接三张表：
Select e.empno,d.loc,m.ename from emp e
Join dept d on e.deptno =d.deptno
Join emp m on e.mgr =m.empno
[color=red]e.左外连接[/color]
执行下面的语句：
Select e.ename, e.deptno,d.loc from emp e
Left join dept d
On e.deptno =d.deptno
这个查询返回了emp（左边的表）中所有的行，即使有与dept表不匹配的记录，但ward任仍然出现在我们的结果集中，左外连接就是把左边的表中所有的记录显示出来而不受on或where条件的约束
[color=red]f.右外连接[/color]
执行下面的语句：
Select e.ename, e.deptno,d.loc from emp e
right join dept d
On e.deptno =d.deptno
我们不难发现，右外连接就是将右边表（dept）的所有记录查询出来
[color=red]g.全外连接[/color]
Select e.ename, e.deptno,d.loc from emp e
full join dept d
On e.deptno =d.deptno
[color=red]所谓的全外连接其实是左连接和右连接的综合[/color]

实际的开发中经常会用到左、右连接。前面在查找雇员姓名及每一位雇员领导的时候用左、右连接更显合理些：
[color=red]SELECT e.empno,e.ename,s.empno mgr_no,s.ename mgr_name
FROM emp e,emp s
WHERE e.mgr=s.empno(+)这里的是一个左外连接，这个加号表示连接的方向.即："(+)"所在位置的另一侧为连接的方向[/color]

[color=red]七、子查询[/color]
[color=red]a.使用any运算符的多行子查询[/color]
Any运算符可以与子查询返回的结果集进行比较
Select empno,ename,job from emp
Where sal < any(
Select sal from emp where job='CLERK'
)
上面的语句是查询比某个文员工资低但本身又不是文员的数据
And job <>'CLERK‘
ANY运算符有以下几种情况
< any 表示比最大值小
> any 表示比最大值大
= any 等同于 in
[color=red]b.使用all运算符的多行子查询[/color]
Select empno,ename,job from emp
Where sal > all(
Select avg(sal) from emp group by deptno
)
上面的语句是查询大于所有部门平均工资的雇员
all运算符有以下几种情况
< all 表示比最大的大
> all 表示比最小的小
Not 运算符可以与 in ，any ，all联合使用

多列子查询
前面讲的单行和多行子查询都是只有一个列在select语句的where中比较，如果要多列比较，则需要用逻辑运算符写一个复合的where子句，多列子查询中的列比较有成对比较和不成对比较两种，所以多列子查询分两种：
1：成对比较多列子查询
2：非成对比较多列子查询
我们又要将emp中的数据进行修改：
Update emp set sal = 1600,comm=300
Where ename='SMITH';
Update emp set sal = 1500,comm=300
Where ename='CLARK';
成对比较多列子查询
Select ename,deptno,sal,comm from emp
Where (sal,NVL(COMM,-1)) IN
( Select sal,NVL(comm,-1) fromemp where dept=30 )
And deptno <> 30
非成对比较多列子查询
Select ename,deptno,sal,comm from emp
Where sal IN
( Select sal from emp where deptno = 30 )
And NVL(comm,-1) in (select NVL(comm,-1) from emp where deptno = 30 )
子查询中的空值
Select e.ename from emp e where
e.Empno not in (
Select manager.mgr from emp manager
)
这个sql试图查找没有下属的雇员，通过查询emp表我们发现应该有8条记录的，但是现在一条也没有，因为子查询里面返回了一条空值，所以无论什么时候只要有空值成为子查询的一部分，我们就不能用not in 运算，我们的解决办法就是去除空值，去除空值的方法是用NVL函数，刚才我们已经使用过了，修改sql：
Select e.ename from emp e where
e.Empno not in (
Select NVL(manager.mgr,-1) from emp manager
)
前面我们讲过的union就是集合操作中的一种，在集合操作中还有下面两种
INTERSECT：返回多个查询结果相同的部分。
MINUS：返回两个查询结果的差集。
为了演示，我们需要创建另一张表，这张表的数据是从emp中拷贝过来的
CREATE TABLE emp10 as
(SELECT * FROM emp WHERE deptno=10)
验证INTERSECT
Select * from emp10
INTERSECT
Select * from emp
验证MINUS
Select * from emp
MINUS
Select * from emp10
这里要注意的是，记录多的表要放在前面，因为MINUS 的中文意思是“减去”的意思